Scheduling the purge¶

Rows past their retention deadline are not removed automatically. Something has to call purge_vault.

CALL pgvault_tables.purge_vault();

Using pg_cron¶

If you already run pg_cron:

SELECT cron.schedule(
    'vault-purge',
    '15 2 * * *',                        -- 02:15 every day
    $$CALL pgvault_tables.purge_vault()$$
);

Using system cron¶

15 2 * * * postgres psql -d prod -c "CALL pgvault_tables.purge_vault()" >> /var/log/vault-purge.log 2>&1

Which role should run it¶

purge_vault is not callable by ordinary roles by default — it deletes data, so it is restricted to the extension owner.

If you want a dedicated maintenance role to run it:

GRANT EXECUTE ON PROCEDURE pgvault_tables.purge_vault(name, name) TO vault_maintenance;

That role also needs ordinary DELETE privilege on the tables being purged. This extension sits underneath PostgreSQL's permission system, not instead of it — both have to allow the operation.

On the primary only¶

The purge deletes rows, so it cannot run on a standby. If your scheduler runs on several nodes, either pin it to the primary or let it fail harmlessly on the others — a read-only transaction error does no damage.

With pg_cron, scheduling in the primary's database handles this naturally, since the schedule only runs where the database is writable.

How often¶

Daily is normal and plenty. Retention periods are measured in days, so running more often gains nothing except load.

Consider timing it away from your backup window. A large purge on a big table generates dead rows and WAL like any other bulk delete.

Checking it is working¶

The procedure reports what it did:

NOTICE:  purged 1204 row(s) from archive.statements
NOTICE:  purge_vault removed 1204 row(s) in total

If you redirect that to a log, you have a record. To check independently, look for rows that should have gone:

SELECT count(*) FROM archive.statements WHERE _$purge_ts < now();

Anything other than zero shortly after a purge run means the purge is not running, or not reaching that table.

What it will not do¶

purge_vault only ever touches tables that set retention, and only removes rows whose deadline has passed. Tables without retention are never visited at all.

You cannot use it to clear out an ordinary table, and it cannot be talked into removing anything early.