Tag Info

I see nothing in your question that auto-vacuum would not take care of. It largely depends on the pattern or your writing activities. You mention 3 million new rows per week, but INSERT (or COPY) is largely irrelevant for VACUUM. UPDATE and DELETE are relevant, especially if they pick random rows (as opposed to bulks of data most likely occupying pages ...

VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs. These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there ...

There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version 9.1. 1.) As has been mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2. 2.) You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs as @Milen ...

Eelke is almost certainly correct that your locking is blocking autovacuum. Autovacuum is designed to give way to user activity, deliberately. If those tables are locked, autovacuum cannot vacuum them. For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it. Note ...

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs). If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s). Do you expect the table to not get any new ...

Yes it is a locking issue. According to this page (non full) VACUUM needs SHARE UPDATE EXCLUSIVE access which is blocked by the lock level you are using. Are you certain you need this lock? PostgreSQL is ACID compliant so concurrent writes are in most cases not a problem as PostgreSQL will abort one of the transactions if a serialization violation would ...

The space should be freed as soon as the index is dropped. Possible explanations for what you show above are: The index is in a tablespace on a different filesystem from the one you're checking. The OS/filesystem has some lag in providing up-to-date free space information. Something else is eating the free space as soon as it becomes available. Something ...

This is very hard to determine. You can tune autovacuuming to be more agressive or to be milder. But when set to mild and it is lagging behind and the base I/O load is too high, it can happen that it never reaches a proper vacuumed state - then you see the process running and running and running. Furthermore, later PostreSQL editions have much improved ...

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would: Select the (relatively few) surviving rows into a temporary table. Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer: Optimizing bulk update ...

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the ...

Just to see which tables qualify for autovacuum at all, the following query may be used (based on http://www.postgresql.org/docs/current/static/routine-vacuuming.html). Note however, that the query does not look for table specific settings: SELECT psut.relname, to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum, ...

The key words here are: "heavily updated" "in the table for 2-3 hours". Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...

Increasing the number of autovacuum processes and reducing the naptime will probably help. Here is the configuration for a PostgreSQL 9.1 that I use on a server that stores backup information and as a result gets a lot of insert activity. http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html autovacuum_max_workers = 6 # ...

Because you haven't specified your PostgreSQL version it's hard to answer. VACUUM has been improved in many ways on newer versions. Presuming you're on 9.0 or 9.1 - so you don't need a max_fsm_pages setting, you have the improved VACUUM FULL, you have the visibility map, etc - then subsequent VACUUM operations should be quite fast. However, you should not ...

In current versions of PostgreSQL, you can look at the *pg_stat_activity* view to find autovacuum tasks. They will have *current_query* fields that start with "Mark autovacuum entries in pg_stat_activity that look like this: autovacuum: VACUUM t A query to count how many of those you have might look like this: SELECT count(*) FROM pg_stat_activity WHERE ...

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis. 1 Backup all of the databases on your postgresql server ...

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only. Yet, two things seem important to me: There seems to be a consensus, that autovacuum should never be switched off, unless you ...

For the general settings use: select * from pg_settings where name like 'autovacuum%' for table specific settings, check out the column reloptions in pg_class: select relname, reloptions from pg_class (you probably want to join that to pg_namespace to limit this to a specific schema).

1) I would have tried cluster followed by analyze. My only hesitation is I am not 100% sure what happened here. Is it possible there was some index corruption as well? Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID? 2) I have no idea. I have never seen ...

Autovacuum should pretty well cover it, unless you mis-configured something. Other answers cover that already. There is one clearly defined case for manual VACUUM (and more importantly: manual ANALYZE) though: temporary tables, they are not considered by the autovacuum demon. I quote the manual on CREATE TABLE here: The autovacuum daemon cannot access ...

There is no short answer to that as it depends on a lot of factor. Is the system slow? Is the auto-vacuum actually touching this table? etc. Here are some good links on this subject: PostgreSQL performance considerations Performance Optimization (PostgreSQL wiki) Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT (same source) Interpreting pg_stat ...

First, take a long, careful look at Aggressive Autovacuum on PostgreSQL as it may very well describe your problem, and check pg_locks to see if there is a locking issue on the table (there may well be one) and also look carefully at Josh Berkus's recommended autovacuum settings for aggressively autovacuuming. To recover from a bloated table you can run the ...