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 that making autovacuum more aggressive is unlikely to solve your problem, however. Also note that the default autovacuum settings are based on running over 200 test runs using DBT2 seeking an optimal combination of settings, so the defaults should be assumed to be good unless you have a solid reason to think otherwise, or unless your database is significantly outside the mainstream for OLTP databases (e.g. a tiny database which gets 10K updates per second, or a 3TB data warehouse).
First, turn on logging so you can check up on whether autovacuum is doing what you think it is:
log_autovacuum_min_duration = 0
Then let's make more autovac workers and have them check tables more often:
autovacuum_max_workers = 6
autovacuum_naptime = 15s
Let's lower the thresholds for auto-vacuum and auto-analyze to trigger sooner:
autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05
Then let's make autovacuum less interruptable, so it completes faster, but at the cost of having a greater impact on concurrent user activity:
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000
There's your full program for generically aggressive autovacuum, which might be apppropriate for a small database getting a very high rate of updates, but might have too great of an impact on concurrent user activity.
Also, note that autovacuum parameters can be adjusted per table, which is almost always a better answer for needing to adjust autovacuum's behavior.
Again, though, it's unlikely to address your real problem.
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 have a very well defined workload on your database and you know exactly what you are doing. But, naturally, you could do additional VACUUM
and/or ANALYZE
runs.
Before considering additional VACUUM
runs, I would check how autovacuum keeps up. You can check whether any tables are beyond the autovacuum threshold by querying pg_stat_user_tables
and pg_class
. I posted such a query on another thread, that might be of interest: Aggressive Autovacuum on PostgreSQL.
Unfortunately, it is not as easy (i.e. not possible at the moment) to do a similar check for autoanalyze thresholds. However, autoanalyze kicks in long before autovacuum by default and it is much cheaper. So, basically if your database can keep up with autovacuum, it will probably be fine with autoanalyze too. The last autoanalyze dates can also be queried from pg_stat_user_tables
.
Some parts of the (most excellent) PostgreSQL documentation, that I found helpful:
Best Answer
Here's a short concise answer.
Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now).
Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear.
For instance, the template0 database is frozen because it never changes (by default you cannot connect to it.)
Every so often the autovacuum daemon will check a database and its tables to see what needs to be vacuumed. If a table is vacuum freeze'd and then never updated, the autovacuum daemon will simply pass it by. Also the "wrap around" protection in postgresql will never kick in on that table either.
tl;dr freezing marks a table as not needing any autovac maintenance. The next update will unfreeze it.