Storage parameters
Storage parameters
(also known as "relopts"/"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.2 |
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 16
- PostgreSQL 14
parallel_insert_enabled
added (commit c8f78b61)
- PostgreSQL 9.6
ALTER TABLE ... SET (fillfactor = ...)
can be performed atSHARE UPDATE EXCLUSIVE
rather thanACCESS EXCLUSIVE
lock level (commit fcb4bfdd)ALTER TABLE ... SET (autovacuum* = ...)
can be performed atSHARE UPDATE EXCLUSIVE
rather thanACCESS EXCLUSIVE
lock level (commit 47167b79)
- PostgreSQL 9.4
user_catalog_table
added (commit 66abc260)
- PostgreSQL 9.2
log_autovacuum_min_duration
added (commit 9d3b5024)
- 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