Re: Compressed binary field

Em 11/09/2012 14:59, Kevin Grittner escreveu:

Edson Richter <edsonrichter@hotmail.com> wrote:

Em 11/09/2012 14:34, Kevin Grittner escreveu:

Edson Richter <edsonrichter@hotmail.com> wrote:
  

For storage, du -h --max-depth 1 on data directory gives me the
amount of data.

  

Biggest objects are just the tables with files.

  

I've 2 tables that held all these objects. Structure is

create table MYTABLE (id bigint not null primary key, mimetype
varchar(100) null, bytea datafile null)

   Could you show the results of this query?:
   SELECT relkind, oid, relfilenode, reltoastrelid,
         relpages, reltuples
    FROM pg_class
    ORDER BY relpages DESC
    LIMIT 10;

 

[biggest relation was a table heap with 29321 pages]

 

Also, just to be sure that all calculations are based on your
actual build, can you show the results of?:
   SHOW block_size;

8192

  So your biggest table is actually 229 MB.  Something is not adding
up.  I can't see any way to reconcile your previous statements with
this number.  There also hasn't been any real explanation for the
statement that you have 250000 files.  There must be something which
matters here which hasn't yet been mentioned.  Any ideas?
  -Kevin

I don't know why, look result of the following query (arquivo is the bytea field):

select count(*) from notafiscalarq where arquivo is not null;
 count
--------
 715084


Strange, huh?

Edson.

Look at the size (5100MB) of this table alone (got after Vacuum with PgAdmin 14):


This table has no deletes or updates, only inserts. It relates to the one above (notafiscal) in a 1:1 relationship.

Regards,

Edson.