I have a table where I store email, the bodies are mostly kept in a toast table. The toast table is 940 Meg in size. The whole database is about 1.2 Gig in size. When I back the database up using pg_dump in custom output mode, I pipe the output into gzip. My backups are only about 600 meg in size. From this, I assume the that toe toast table isn’t getting compressed.
I am keeping the bodies in a column of type “bytea”.
Is there any way I can tell for sure if the messages from this column are being stored compressed? I know I can set the compression settings using the “ALTER TABLE ALTER SET STORAGE” syntax, but is there a way I can see what this value is currently set to?
I’ve tried creating two tables with the body column in one table’s storage mode set to EXTENDED and the other set to EXTERNAL. I did an insert from the first table into each of these, then did a VACUUM FULL. The sizes of both table’s toast data was the same, which again leads me to believe something isn’t working. I also tried creating a table with the body column of type text, and still it was the same size.
I am using postgresql 8.0.3. If anybody could shed some light on this situation I would appreciate it.
David