Database Reference
In-Depth Information
How it works...
In PostgreSQL 9.0, when we create a temporary table, we insert entries into the catalog tables
pg_class and pg_attribute. These catalog tables and their indexes begin to grow and to bloat,
an issue covered in later recipes. To control that growth, you can either VACUUM those tables
manually, or set
autovacuum
=
on
in
postgreql.conf
. You cannot run
ALTER
TABLE
against
system tables, so it is not possible to set specific autovacuum settings for any of these tables.
If you VACUUM the system catalog tables manually, make sure you get all of the system tables.
You can get the full list of tables to VACUUM using the following query:
postgres=# SELECT relname, pg_relation_size(oid)
FROM pg_class
WHERE relkind in ('i','r') and relnamespace = 11
ORDER BY 2 DESC;
relname | pg_relation_size
---------------------------------+------------------
pg_proc | 450560
pg_depend | 344064
pg_attribute | 286720
pg_depend_depender_index | 204800
pg_depend_reference_index | 204800
pg_proc_proname_args_nsp_index | 180224
pg_description | 172032
pg_attribute_relid_attnam_index | 114688
pg_operator | 106496
pg_statistic | 106496
pg_description_o_c_o_index | 98304
pg_attribute_relid_attnum_index | 81920
pg_proc_oid_index | 73728
pg_rewrite | 73728
pg_class | 57344
pg_type | 57344
pg_class_relname_nsp_index | 40960
...(partial listing)
The preceding values are for a newly created database. These tables can get very large if
not properly maintained, with values of 11 GB of for one index being witnessed at one
unlucky installation.
