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@solfertje.student.utwente.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.klaver@gmail.com