pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedToast Problems
3+ messages / 2 participants
[nested] [flat]
* Toast Problems
@ 2007-04-18 18:23 David Hinkle <hinkle@cipafilter.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Hinkle @ 2007-04-18 18:23 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Toast Problems
@ 2007-04-18 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Hinkle <hinkle@cipafilter.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-04-18 19:08 UTC (permalink / raw)
To: David Hinkle <hinkle@cipafilter.com>; +Cc: pgsql-admin
"David Hinkle" <hinkle@cipafilter.com> writes:
> Is there any way I can tell for sure if the messages from this column
> are being stored compressed?
Not in 8.0, but in more recent versions pg_column_size() would help.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Toast Problems
@ 2007-04-18 19:35 David Hinkle <hinkle@cipafilter.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: David Hinkle @ 2007-04-18 19:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
I have Postgres on almost 400 boxes, some of which push over 2 million
transactions a day. It's really been a wonderful product for us.
All of them use the same version of postgress (8.0.3), embedded onto a
64 meg flash chip. So, doing a upgrade is a very big deal.
Does anybody know if 8.0.3 had a problem with using compression on TOAST
fields? Or where I could find changelogs to look through?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 18, 2007 2:09 PM
To: David Hinkle
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Toast Problems
"David Hinkle" <hinkle@cipafilter.com> writes:
> Is there any way I can tell for sure if the messages from this column
> are being stored compressed?
Not in 8.0, but in more recent versions pg_column_size() would help.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-18 19:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-18 18:23 Toast Problems David Hinkle <hinkle@cipafilter.com>
2007-04-18 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-18 19:35 ` David Hinkle <hinkle@cipafilter.com>
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