pg_toast table growth out of control - Mailing list pgsql-general

From Jeffrey W. Baker
Subject pg_toast table growth out of control
Date
Msg-id 1015873209.828.14.camel@heat
Whole thread Raw
Responses Re: pg_toast table growth out of control
List pgsql-general
(resent -- mailing lists seem horked up today)

I have a pg_toast table that is using up my entire storage device.  When
I vacuum the database, it does not get smaller.  This is driving me
crazy, since I have to dump and reload the data to shrink the database.

The table is simple:

rupert=# \d resp_body
      Table "resp_body"
 Column |  Type   | Modifiers
--------+---------+-----------
 resp   | integer | not null
 body   | text    |
Unique keys: resp_body_resp_idx

The vacuum shows the problem:

rupert=# vacuum verbose resp_body;
NOTICE:  --Relation resp_body--
NOTICE:  Pages 1008: Changed 42, Empty 0; Tup 35101: Vac 0, Keep 0,
UnUsed 10292.
        Total CPU 0.08s/0.03u sec elapsed 0.79 sec.
NOTICE:  --Relation pg_toast_2947588--
NOTICE:  Pages 1759978: Changed 555, Empty 0; Tup 197417: Vac 0, Keep 0,
UnUsed 6979775.
        Total CPU 164.66s/9.69u sec elapsed 589.04 sec.
VACUUM

Hey, only 7 million unused records taking 15GB of disk, no problem!?!?

Help!

-jwb



pgsql-general by date:

Previous
From: Frank_Lupo Frank_Lupo
Date:
Subject: If possible create a table (not temp) for every user using equal name?
Next
From: "Peter Darley"
Date:
Subject: Stale temp tables