pg_toast table growth out of control

From: "Jeffrey W(dot) Baker" <jwb(at)saturn5(dot)com>
To: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: pg_toast table growth out of control
Date: 2002-03-11 19:00:09
Message-ID: 1015873209.828.14.camel@heat
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Darley 2002-03-11 19:30:08 Stale temp tables
Previous Message Frank_Lupo Frank_Lupo 2002-03-11 18:52:08 If possible create a table (not temp) for every user using equal name?