Regular Maintenance - PostgreSQL 9 Administration - page 213

which will turn off autovacuuming of the "toast" table.
Note that autovacuuming of the toast table is performed completely separately from the main
table, even though you can't ask for an explicit include/exclude of the toast table yourself
when running VACUUM.
Use the following query to display the reloptions for tables and their toast tables:
postgres=# SELECT n.nspname, c.relname,
pg_catalog.array_to_string(c.reloptions || array(
select 'toast.' ||
x from pg_catalog.unnest(tc.reloptions) x),', ')
as relopts
FROM pg_catalog.pg_class c
LEFT JOIN
pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) JOIN
pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND nspname NOT IN ('pg_catalog', 'information_schema');
nspname | relname | relopts
---------+------------------+------------------------------
public | pgbench_accounts | fillfactor=100,
autovacuum_enabled=on,
autovacuum_vacuum_cost_delay=20
public | pgbench_tellers | fillfactor=100
public | pgbench_branches | fillfactor=100
public | pgbench_history |
public | text_archive | toast.autovacuum_enabled=off
VACUUM allows inserts, updates, and deletes while it runs, though it prevents actions such as
ALTER TABLE and CREATE INDEX . Autovacuum can detect if a user requests a conflicting
lock on the table while it runs, and will cancel itself if it is getting in the user's way.
Note that VACUUM does not shrink a table when it runs, unless there is a large run of space
at the end of a table, and nobody is accessing the table when we try to shrink it. To properly
shrink a table, you need VACUUM FULL . That locks up the whole table for a long time, and
should be avoided, if possible. VACUUM FULL will literally rewrite every row of the table, and
completely rebuild all indexes. That process is faster in 9.0 than it used to be, though it's still a
long time for larger tables.