On Tue, 14 Apr 2009 13:26:24 -0700 Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote: > Ivan Sergio Borgonovo wrote: > > I still have to investigate if the tables are getting really > > larger... but at a first guess there shouldn't be any good > > reason to see tables getting so large so fast... so I was > > wondering if anything could contribute to make a backup much > > larger than it was other than table containing more records? > > > > The only thing that should have been really changed is the > > number of concurrent connections during a backup. > > > Can we assume that by backup you mean pg_dump/pg_dumpall? If so, > then the change is likely due to increasing data in the database. > I have a daily report that emails me a crude but useful estimate > of table utilization based on this query: > > select > relname as table, > to_char(8*relpages, '999,999,999') as "size (kB)", > (100.0*relpages/(select sum(relpages) from pg_class where > relkind='r'))::numeric(4,1) as percent > from > pg_class > where > relkind = 'r' > order by > relpages desc > limit 20; Thanks, very useful. May I place it on my site as a reference, giving credits of course? Still puzzled... The first and second largest table make up for 70% of the overall DB size (1st 53%, 2nd 16.1%) The second one have very few small fields but ~2x the number of records of the first. Comparatively a row of the first one is at least 10x larger than a row in the second one. The first has 1M records. All the others following with a size larger than 1% grow as 1x the number of records of the first one. I had an increment of less than 10% of the number of records of the first table but an increment of roughly 80% of the size of backup. Maybe it is due to compression. The table that grew more can't be shrunk too well. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
