Autovacuum Tuning

PostgreSQL has a built-in feature called called autovacuum to check for tables that have had a large number of changed tuples, and trims the table for fast access.

We use this feature in the IBM FHIR Server, and have documented it in FHIRPerformanceGuide. We configure it per the Postgres Configuration and create our tables WITH Autovacuum.

To check the settings for autovacuum, you can use your schema and table name to get the current table settings:

select relname, (reloptions)::VARCHAR
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname = LOWER('STRUCTUREMAP_LOGICAL_RESOURCES')
and pg_namespace.nspname = LOWER('TEST12345');

 structuremap_logical_resources {autovacuum_vacuum_scale_factor=0.01,
autovacuum_vacuum_threshold=1000,autovacuum_vacuum_cost_limit=2000}