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

On Monday, February 28, 2011 9:51:10 pm Aleksey Tsalolikhin wrote:
> On Sun, Feb 27, 2011 at 2:52 AM, Alban Hertroys
> <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> Thank you for your kind replies.
>
> > I noticed in your table definition that you seem to store timestamps in
> > text-fields. Restoring those from text-fields shouldn't make any
> > difference, but perhaps your locales are set up differently between the
> > machines and cause some type of conversion to take place?
>
> OK, Alban, I'm game. How would I check how locales are set up?
>
> Adrian, I found pg_indexes_size() is only in 9 (I have 8.4) but I got
> the same information from a query based on
> http://www.issociate.de/board/post/478501/How_much_space_do_database_object
> s_take_up_in_data_files.html

Sorry about that, I was not paying attention. FYI 8.4 does have
pg_relation_size() which can be applied against individual indexes.

>
>
> Here is what I see:
>
>
>
> nspname | relname | tablesize
>
> | indexsize | toastsize | toastindexsize
>
> ------------------------+----------------------------------+------------+--
> ----------+------------+---------------- public | big
> | 744 MB
>
> | 737 MB | 48 GB | 278 MB
>
> public | big | 503 MB
>
> | 387 MB | 99 GB | 278 MB
>
> Check out that toastsize delta. What makes up TOAST? How can I
> compare the two TOAST tables in detail?

TOAST is best explained here:
http://www.postgresql.org/docs/8.4/interactive/storage-toast.html

Looks like the TOAST compression is not working on the second machine. Not sure
how that could come to be. Further investigation underway:)

>
> Thanks,
> Aleksey

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