Postgresql – Is PostgreSQL 9.2 autovacuum sufficient for a busy table

database-designpostgresqlvacuum

I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database:

CREATE TABLE ch02.markers  
(
  ff_id SERIAL PRIMARY KEY,
  col1 boolean,
  col2 smallint,
  created_at timestamp,
  lat double precision,
  lon double precision,
  geog geography(POINT,4326)
);

CREATE INDEX idx_markers_geog ON ch02.markers USING gist(geog);

This table is having records, probably thousands but potentially tens of thousands per hour, inserted and deleted 24/7.

I am wondering if the default autovacuum settings will sufficiently manage the table?

Otherwise I presume I write a SP function that is called by cron. However, what should I use? VACUUM ANALYZE? I am wary of using any anything that locks the table (CLUSTER, REINDEX). Do these even work for an index on a geography point column?

I am not used to such active tables and I am completely new to Postgres, so I have absolutely no feel for this. Any help appreciated.

Best Answer

Before you fiddle with cron jobs and other curiosities you can adjust the autovacuum default settings just inside PostgreSQL. This can be done globally or individually for each table.