Storage parameters
Storage parameters (also known as "relopts" or "reloptions"/"relation options") 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.
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_factor |
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_insert_enabled |
14 |
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
- PostgreSQL 14
parallel_insert_enabledadded (commit c8f78b61)
- PostgreSQL 9.6
ALTER TABLE ... SET (fillfactor = ...)can be performed atSHARE UPDATE EXCLUSIVErather thanACCESS EXCLUSIVElock level (commit fcb4bfdd)ALTER TABLE ... SET (autovacuum* = ...)can be performed atSHARE UPDATE EXCLUSIVErather thanACCESS EXCLUSIVElock level (commit 47167b79)
- PostgreSQL 8.2
- added (initial commit 277807bd)
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)
References
- PostgreSQL documentation: CREATE TABLE: Storage Parameters
- PostgreSQL documentation: ALTER TABLE
- PostgreSQL documentation: CREATE INDEX: Storage Parameters
- PostgreSQL documentation: ALTER INDEX