On Tue, 14 Apr 2009 13:26:24 -0700
Steve Crawford <scrawford@pinpointresearch.com> 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