2

I have one database in linux server which has huge number of data's. Due to the updation and deletion operation it has consumed most of the from my hard disk. The main reason is that the version which we are using ( 8.1 ) does not have periodical autovaccum process. To over come this issue i have put the vaccum full to specific table, the vaccum command was running more than 15 hours and keeps on running. So I just stopped the vaccum execution since other process was not able to access that table for 15 hours.

Is there any way to overcome this issue and get back the memory without disturbing other process.

Thanks

1

3 Answers 3

2

Version 8.1 is End Of Life as of November 2010. You need an upgrade to a newer version, 8.3 or later. Version 8.2 will be EOL within a few months as well.

VACUUM FULL needs a table lock, did it get the lock or was it just waiting for 15 hours to get it? VACUUM FULL is the only way to reclaim diskspace.

Version 8.1 does have auto_vacuum, it was introduced in this version:

Beginning in PostgreSQL 8.1, there is a separate optional server process called the autovacuum daemon, whose purpose is to automate the execution of VACUUM and ANALYZE commands.

Turn it on, or do a manual VACUUM (without FULL) on a time interval.

4
  • yes, I want to upgrade the database to new version. before that how can i get rid of this issue...any other work around is there ..?
    – Pavunkumar
    Sep 21, 2011 at 7:50
  • Stop all processes on that table on a quiet moment and do a VACUUM FULL. Afterwards, you need a REINDEX for this table as well, VACUUM FULL might inflate your indexes. VACUUM FULL will always disturb other processes, that's why you need auto_vacuum to prevent the need for a VACUUM FULL. Sep 21, 2011 at 7:55
  • In that case you might consider CLUSTER instead, followed by ANALYZE. Optimizes best and all indexes as well. You could chose an index for that which sorts data according to typical queries - another benefit on the side. Sep 21, 2011 at 18:30
  • Another option is to create another table, copy over the contents, delete the original table and then rename the new table to the old name.
    – redgeoff
    Jun 12, 2020 at 0:42
3

You can give CLUSTER (doc here) a try - it will rewrite and compact a complete table. While VACUUM FULL does this in place, CLUSTER does its job by writing into a second file and switches the files after the jobs.

This has some advantages and some disadvantages:

  • It is much faster than VACUUM FULL,
  • it needs more storage while running (second table file),
  • it requires an EXCLUSIVE lock on the table.

You might start with several smaller tables to get a feeling for it.

And please lookup the PostgreSQL mailinglists for more details.

1

If the VACUUM debt is really that huge, restoring from a pg_dump backup would also be an option. And that comes tentatively close to upgrading to 8.4 or higher.

1
  • If you're upgrading anyway, then dump restore is the way to go. Note that you can pg_dump -t tablename just the one table, truncate it an restore it, or do something like select * into holding_table from bloated_table;truncate bloated_table;insert into bloated_table select * from holding_table; and things like that.
    – Scott Marlowe
    Sep 22, 2011 at 22:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .