pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Help with TOAST Compression 2+ messages / 2 participants [nested] [flat]
* Help with TOAST Compression @ 2007-04-17 21:13 David Hinkle <hinkle@cipafilter.com> 0 siblings, 1 reply; 2+ messages in thread From: David Hinkle @ 2007-04-17 21:13 UTC (permalink / raw) To: pgsql-performance 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? David ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with TOAST Compression @ 2007-04-23 08:45 Michael Fuhr <mike@fuhr.org> parent: David Hinkle <hinkle@cipafilter.com> 0 siblings, 0 replies; 2+ messages in thread From: Michael Fuhr @ 2007-04-23 08:45 UTC (permalink / raw) To: David Hinkle <hinkle@cipafilter.com>; +Cc: pgsql-performance On Tue, Apr 17, 2007 at 04:13:36PM -0500, David Hinkle wrote: > 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. How are you measuring the toast table and database sizes? Have you taken indexes and uncompressible data and metadata into account? The database compresses only certain data, whereas when you pipe a dump into gzip you get compression on the entire dump. Some of the space might be taken up by dead rows and unused item pointers. How often do you vacuum? What does "VACUUM VERBOSE tablename" show? > Is there any way I can tell for sure if the messages from this column > are being stored compressed? You could look at a hex/ascii dump of the base and toast tables -- you might see runs of legible text but it should be obvious where the data is compressed. See the TOAST section in the documentation for more information about how and when data is compressed: http://www.postgresql.org/docs/8.2/interactive/storage-toast.html Note that "The TOAST code is triggered only when a row value to be stored in a table is wider than BLCKSZ/4 bytes (normally 2 kB)." And I'm no expert at compression algorithms but it's possible that the "fairly simple and very fast member of the LZ family of compression techniques" isn't as space-efficient as the algorithm that gzip uses (LZ77 according to its manual page). Maybe one of the developers can comment. > 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? You could query pg_attribute.attstorage: http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html -- Michael Fuhr ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-04-23 08:45 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-04-17 21:13 Help with TOAST Compression David Hinkle <hinkle@cipafilter.com> 2007-04-23 08:45 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox