What still works normally¶
Most of this guide is about what a vault table refuses, because that is the part that is unusual. Read end to end, it can leave the impression that a vault table is awkward to live with.
It is not. This extension protects the data. It does not stand between a DBA and their job.
A vault table is an ordinary PostgreSQL table that has had five row-modifying operations wrapped in a permission check. Everything else is PostgreSQL's own code, unchanged, because the extension delegates to it rather than reimplementing it.
Everything below needs no permission and always works¶
| Reading | SELECT, joins, views, materialised views, CTEs. This extension has nothing to do with reading |
| Indexes | CREATE INDEX, CREATE INDEX CONCURRENTLY, DROP INDEX, REINDEX, ALTER INDEX — indexes are never intercepted at any point |
| Keys and constraints | Primary keys, unique constraints, foreign keys in both directions, CHECK constraints, ADD CONSTRAINT after creation |
| Triggers | Creating and dropping triggers, and triggers firing — including ones that write to the table itself |
| Maintenance | VACUUM, VACUUM FULL, ANALYZE, CLUSTER, REINDEX |
| Column tuning | SET STORAGE, SET COMPRESSION, SET STATISTICS, SET/RESET (n_distinct) |
| Physical layout | CLUSTER ON, SET WITHOUT CLUSTER |
| Replication | REPLICA IDENTITY in every form; physical and logical replication both work |
| Ownership and access | ALTER TABLE ... OWNER TO, GRANT, REVOKE |
| Documentation | COMMENT ON TABLE, COMMENT ON COLUMN |
| Backups | pg_dump and pg_restore, including the declaration and retention deadlines |
| Large values | TOAST, exactly as for any other table |
| Every data type | No restrictions of any kind |
Autovacuum treats a vault table like any other. Nothing needs configuring.
The three things a DBA does need to know¶
1. Storage parameters go in CREATE TABLE.
ALTER TABLE ... SET (fillfactor = 70) is refused, so per-table settings — fillfactor, autovacuum thresholds, TOAST settings — must be given when the table is created:
CREATE TABLE t (id int) USING vault
WITH (permissions = 'insert', fillfactor = 70,
autovacuum_vacuum_scale_factor = 0.01);
This is a limitation of how the extension stores its own settings rather than a deliberate restriction, and it is the reason nothing can tamper with those settings later. Column-level tuning is unaffected.
2. Structural changes need a permission, or a new table.
If the table sets retention, read Copying a Retained Table before copying anything — a naive copy silently rewrites every row's deadline.
ADD COLUMN needs addcolumn. DROP COLUMN, ALTER COLUMN TYPE, RENAME and SET SCHEMA are refused outright — those change or hide what has already been recorded, which is the thing the table exists to prevent.
3. A vault table's own definition cannot be edited by hand.
Its declaration, storage and columns are verified independently of whatever statement was run, and a transaction that would leave any of them altered is refused. Anything belonging to an ordinary table is untouched, so this only appears if you were editing a vault table's own definition, which is not something a normal procedure does.
4. A table that does not grant drop cannot be removed.
This is the one that genuinely constrains operations, and it is deliberate. Read Uninstalling before you create anything.
The short version¶
If your work is about how the table performs, how it is indexed, how it is backed up or how it is replicated, nothing changes.
If your work is about changing rows that are already there, that is exactly what the table was created to refuse.