Routine maintenance¶

Vault tables need the same care as any other table, and take it in the same way.

VACUUM and ANALYZE¶

All forms work normally:

VACUUM archive.statements;
ANALYZE archive.statements;
VACUUM FULL archive.statements;
CLUSTER archive.statements USING statements_pkey;
REINDEX TABLE archive.statements;

Autovacuum handles vault tables like any other. Nothing special to configure.

Vacuum is maintenance, not a data operation, so it is not gated by permissions. A table granting only insert is still vacuumable — which matters, because tables that never allow deletes still accumulate dead rows from updates and from purges.

VACUUM FULL and CLUSTER rewrite the table completely. The rows, the access method, the permissions and every retention deadline all survive that rewrite unchanged.

Indexes¶

Ordinary DDL, and unrestricted:

CREATE INDEX CONCURRENTLY statements_body_idx ON archive.statements (body);
DROP INDEX statements_body_idx;
REINDEX INDEX statements_body_idx;

Indexes are not part of the table's protected definition, so you can add and remove them freely. That is deliberate — index changes do not alter data, and needing to plan every index up front would make vault tables impractical.

Triggers¶

Work normally, including triggers that write back to the table they are on.

A trigger cannot do anything the table forbids. If an AFTER INSERT trigger tries to update a table granting only insert, the update is refused and the original insert fails with it.

A trigger cannot move a retention deadline either. Setting _$purge_ts in a BEFORE UPDATE trigger has no effect — the original value is carried forward.

Storage and bloat¶

Vault tables store data exactly as ordinary tables do, so they bloat the same way and are reclaimed the same way.

The one thing to watch: on a table with a long retention period and no delete, rows accumulate for the whole period and are then removed in a batch by the purge. If seven years of data arrives at a steady rate, expect a large delete on the day the first batch expires, and the dead space that follows it. Autovacuum will deal with it, but you may want to time your purge accordingly.

Checking the estate¶

Occasionally worth reviewing what exists:

SELECT * FROM pgvault_tables.view_vault_tables()
 ORDER BY schema_name, table_name;

Two queries worth running now and then:

-- tables where retention is not actually protecting anything,
-- because delete is granted as well
SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE retention_days IS NOT NULL AND permissions LIKE '%delete%';

-- tables that can be dropped
SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE permissions LIKE '%drop%';

Neither is wrong in itself. Both are worth knowing about.

Upgrading PostgreSQL¶

The extension supports PostgreSQL 16, 17 and 18, so a major upgrade within that range needs only the matching build installed.

pg_upgrade needs shared_preload_libraries set in the new cluster's configuration before it runs, as does any tool that starts a temporary postmaster.