Re: database is bigger after dump/restore - why? (60 GB to 109 GB)

On Sunday, February 27, 2011 12:49:48 am Aleksey Tsalolikhin wrote:
> so looks like it's something low-level, something about how the data is
> stored.
>
> i've installed the "pageinspect" contrib module as Tomas suggested but
> I don't know what to do with it or what to look at. I looked at the
> manual for it but it's totally over my head right now.
>
> What sections of the manual should I read to be able to use this
> module? (there are 2167 pages in the whole Postgres 8.4 manual and I
> don't have time to study the whole thing. :( I just need to study
> enough to understand how to use pageinspect.)
>
> (I'm not a Postgres DBA but I am having to become one to support our
> database.)
>
> Thanks again for the help. I'll be at SCALE 9x tomorrow helping Joe
> Conway and Bruce M and Richard B and company get the word out about
> Postgres.

Well it looks like the problem is not with the raw data. So far you have been
using pg_total_relation_size to look at the table. It might be time to look at
the individual components by using the other pg_* functions:

http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

pg_table_size(regclass)
pg_indexes_size(regclass)
pg_relation_size(relation regclass, fork text)

In other words try to narrow down where the bloat is occurring.

>
> Best,
> Aleksey

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com