allow_system_table_mods

Definition PG 18 manual

Allows modification of the structure of system tables as well as certain other risky actions on system tables. This is otherwise not allowed even for superusers. Ill-advised use of this setting can cause irretrievable data loss or seriously corrupt the database system. Only superusers and users with the appropriate SET privilege can change this setting.

How it works

allow_system_table_mods removes protections against changing system-catalog structure and permits other normally forbidden catalog actions. Superuser status alone does not normally grant these operations.

Catalog definitions are coupled to PostgreSQL code, cache descriptors, bootstrap data, WAL, and upgrade assumptions. A superficially valid ALTER can create irreversible corruption or data loss.

The parameter exists for PostgreSQL development, bootstrap, and tightly controlled tooling. It does not provide a supported extension mechanism; extensions must use published catalog and hook interfaces. Its superuser context permits an authorized session change without a server restart.

Operational considerations

Leaving allow_system_table_mods enabled after the bounded diagnostic or recovery task.

Running the experiment on the only copy of production data.

Underestimating log, core-file, temporary-file, WAL, CPU, or connection-slot amplification.

Treating a server that merely starts or completes a query as proof that data and behavior are correct.

Workload guidance

OLAP: Workload type does not reduce the catalog-corruption risk from allow_system_table_mods; keep it off on analytical systems too.

OLTP: Never use allow_system_table_mods for performance tuning. Restrict it to PostgreSQL development or a documented, disposable maintenance procedure with a verified backup and exact rollback path.

SMALL: Keep allow_system_table_mods=off. Lack of a staging system is not permission to experiment on the only copy of the data.