On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
> Weird. The pgstattuple data shows that the tables are essentially the same, the
> only difference being the dead tuples, as expected, on the production table. The
> TOAST size information shows approximately a doubling in size of the TOASTed
> data on the new machine. By all accounts compression or the lack thereof would
> be the culprit. At this point I am at a loss for another explanation.
An apt summary, thank you.
> One more request for information. What is the data being stored in the table?
For the main part, it's an XML file, we store it in the third field.
Our XML files are
13KB - 48 KB in length.
The other fields are relatively short.
Table "tablename"
Column | Type | Modifiers
-----------------+--------------------------+-----------
uuid | character varying | not null
filename | character varying |
XML_file | character varying |
another_id | character varying |
record_generation_timestamp | timestamp with time zone |
another_uuid | character varying |
timestamp_start | character varying |
timestamp_end | character varying |
30_byte_text_id | character varying |
yet_another_text_id | character varying |
Indexes:
"tablename_pkey" PRIMARY KEY, btree (uuid)
"tablename_column6_index" btree (another_uuid)
"tablename_column9_index" btree (30_byte_text_id)
"tablename_column8_index" btree (timestamp_end)
"tablename_column7_index" btree (timestamp_start)
I ran char_length() on each XML file, the total is 38 GB, which is pretty close
to the 40 GB size of the pg_dump of the table in text format.
From my observation, when the table was under 30 GB in size,
TOAST compression worked fine; when it got above 35 GB in size,
now TOAST compression is not working.
Yours truly,
-at