Contents

Storage parameters

Relation-specific user-definable attributes which influence how PostgreSQL interacts with that relation

Storage parameters are relation-specific parameters which modify how PostgreSQL interacts with that relation, e.g. fillfactor, which determines how much space to leave empty on a table's page for future updates, or define custom autovacuum settings.

Storage parameters were introduced in PostgreSQL 8.2 (initial commit: 277807bd). They are also referred to as "relopts" or "reloptions" ("relation options").

Note that between PostgreSQL 8.1 and PostgreSQL 8.3, per-table autovacuum settings were stored in the system catalogue table pg_autovacuum.

Table storage parameters and version when introduced

Parameter From
autovacuum_enabled, toast.autovacuum_enabled 8.4
autovacuum_analyze_scale_factor 8.4
autovacuum_analyze_threshold 8.4
autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age 8.4
autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age 8.4
autovacuum_freeze_table_age, toast.autovacuum_freeze_table_ag 8.4
autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age 9.3
autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age 9.3
autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age 9.3
autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay 8.4
autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit 8.4
autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_facto 8.4
autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold 8.4
fillfactor 8.2
log_autovacuum_min_duration, toast.log_autovacuum_min_duration 9.6
parallel_workers 9.6
toast_tuple_target 11
user_catalog_table 9.4
vacuum_index_cleanup, toast.vacuum_index_cleanup 12
vacuum_truncate, toast.vacuum_truncate 12

Change history

Examples

Storage parameters can be set when creating a table with e.g.:

CREATE TABLE foo (id INT) WITH (fillfactor=75, log_autovacuum_min_duration=10)

and altered with e.g.

ALTER TABLE foo SET (log_autovacuum_min_duration=50)

The relation's storage parameters are recorded in the reloptions column (type TEXT[]) in its pg_class entry and can be displayed in psql with \d+ relname:

postgres=# \d+ foo
                                    Table "public.foo"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              | 
Access method: heap
Options: fillfactor=75, log_autovacuum_min_duration=50

or examined directly with e.g.

postgres=# SELECT UNNEST(reloptions) FROM pg_class WHERE oid = 'foo'::REGCLASS;
             unnest
--------------------------------
 fillfactor=75
 log_autovacuum_min_duration=50
(2 rows)

Alternatively use the function pg_options_to_table() (PostgreSQL 9.1 and later):

postgres=# SELECT option_name, option_value FROM pg_class c, pg_options_to_table(c.reloptions) WHERE c.oid = 'foo'::REGCLASS;
         option_name         | option_value 
-----------------------------+--------------
 log_autovacuum_min_duration | 50
 fillfactor                  | 75
(2 rows)

Categories

DDL, Performance, Storage