Posts Tagged ‘ALTER TABLE’

Table level auto-vacuum settings

May 31, 2010 Leave a comment

While working with users on custom auto-vacuum settings, one of the real problems I saw with PostgreSQL 8.3 and below was making them understand the ways to update pg_autovacuum as sometimes little mistakes could cause you a lot of trouble.

I had some annoying experiences with those column values for pg_autovacuum at times as well, like once I had mistakenly autovacuum_freeze_min_age value set to zero while autovacuum_enabled set to false the auto-vacuum thread still kept doing vacuuming on that table. After doing a little bit of research found out that it had to be set to -1 so it can use the system-wide values and then it would all work. This was all a little confusing for the new users and then the other problem was as you a dump and restore of the database, all those settings are just gone so you will have to do it all over again.

I had been using pg_autovacuum with version 8.3 and just today while I was writing an upgrade plan for a client upgrading databases to 8.4, I just came across this improved custom auto-vacuum feature which lets you simply do a CREATE/ALTER TABLE command and set anything as you like for auto-vacuuming per table using the WITH option and it will also copy all these settings in the dump file (pg_dump output) as well which makes my life so easy while moving databases.

An example of it can be:

postgres=# \d test
Table "public.test"
Column |       Type        | Modifiers
a      | integer           | not null
b      | character varying |
"test_pkey" PRIMARY KEY, btree (a)
postgres=# ALTER table test SET (autovacuum_vacuum_threshold=50, 
-- PostgreSQL database dump
a integer NOT NULL,
b character varying
WITH (autovacuum_vacuum_threshold=50, autovacuum_analyze_threshold=50);
-- PostgreSQL database dump complete

You can find details for the storage settings that can be set with the CREATE/ALTER TABLE at

Shoaib Mir