Archive

Posts Tagged ‘CREATE 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 |
Indexes:
"test_pkey" PRIMARY KEY, btree (a)
postgres=# ALTER table test SET (autovacuum_vacuum_threshold=50, 
autovacuum_analyze_threshold=50);
ALTER TABLE
--
-- PostgreSQL database dump
--
CREATE TABLE test (
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 http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS


Shoaib Mir
shoaibmir[@]gmail.com