Re: Compressed binary field - Mailing list pgsql-general

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;

relkind	oid	relfilenode	reltoastrelid	relpages	reltuples
r	312470	1043546	312492	29321	639571
r	312585	1043643	0	22732	1.80617e+06
r	312522	1043578	312527	19769	724210
r	312749	1043773	312753	14307	928538
r	312758	1043763	0	10488	917134
r	312498	1043525	0	7689	640572
r	312802	1043804	312810	7670	172789
r	312964	1044076	0	7586	385833
i	1041923	1043648	0	6958	1.80617e+06
r	312815	1043825	312819	6684	715081

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

8192

Have you checked the level of bloat yet?  (Perhaps autovacuum needs
to be made more aggressive.)

Besides autocacuum, I usually run Vacuum Full on weekly basis. My calculations came after Vacuum Full.

Edson.

-Kevin