Re: Compressed Backup too big

On Thu, 15 Nov 2007 20:35:35 +0200
"Andrus" <kobruleht2(at)hot(dot)ee> wrote:

> "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)"
> Database size in disk returned by pg_database_size() is 210 MB
>
> Database compressesed backup file size is now 125 MB.
> This seems too much. I expect compression to decrease size 10 times, also
> indexes are not backed up. A year ago compressed backup size was 9 MB only.
>
> I created query returning biggest tables with and without indexes and found:
>
> 1 pg_toast_22185 95 MB 96 MB
> 2 rid 21 MB 27 MB
> 3 klient 13 MB 19 MB
> 4 mailbox 10 MB 11 MB
> 5 dok 7640 kB 12 MB
> 6 desktop 8080 kB 8200 kB
> 7 strings 5536 kB 6584 kB
> 8 pg_toast_22338 5232 kB 5368 kB
>
> ...
>
> Questions:
>
> 1. Tables are relatively small and thus cannot create 125 MB compressed
> backup file.
> Why backup file sis so big ?
I assume that "mailbox" contains e-mail messages as the name suggest... One can assume that those emails also have attachments like pdf, jpg and other mostly incompressible file format.

In my understanding of postgresql, when you a have a variable length field (like text or bytea), pg will only stores the first bytes (hundreds or so) of that field in the main table and then put the rest in an "hidden[1]" TOAST table.

> 2. How to determine what data is containing in pg_toast_22185 ?
If you have PGAdmin at hand or you can install it, its quite easy, just enable "Show System Objects" under the "View" menu [2]. Based on my assumptions, I guess it's the TOAST of the field which contains the body of your emails...

Enough wild guesses,
HTH,
--
MaXX

[1] hidden: transparent from an user point of view
[2] you can also obtain those information from the information schema IIRC