Hi,
I'm maintaining a fairly large online database, and am trying to free
up disk space. Its got to 98% full.
I am certain that the postgresql data files are responsible for more
than 97% of this partition's usage.
The WAL logs for example are stored elsewhere.
The largest tables in this database are only inserted, not updated.
There are about 6 inserts per second. Its all time-stamped, and I am
deleting old rows.
There are 5 such tables, each 3 times as large as the previous.
On the 2 smallest tables, I have already done a create table ... (like
...), a re-insert of everything after a certain date, a vaccuum
analyse, and recreated the indexes. But they are relatively small, so
no real gains.
On the larger tables though, I have deleted old rows, and am now
running a (plain) vacuum.
The 3rd largest table's vacuum has completed. No space gain at all.
The other two (largest) table's vacuums are still in progress (still
running since last evening). I have shut down part of the service so
that its no longer inserting data to the tables, but rather caching it
for later insertion.
I suspect I need to run vacuum full, and drop indexes. Then re-create
the indexes...
But is there something I'm missing, e.g. that although the database
disk is 98% full, postgresql sees the database as having large blocks
of free space that it can write into? A vacuum full is going to take
an age, and I'm not sure if I can afford to have the database offline
for that period...
I will appreciate your help.
Thanks
Brian
--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/