Purging expired rows

Once rows pass their retention deadline, something has to remove them. That is what purge_vault is for.

CALL pgvault_tables.purge_vault();

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

---

What it does

For every table that sets retention, it runs:

DELETE FROM <table> WHERE _$purge_ts < now();

That is all. It removes rows whose deadline has passed and leaves everything else alone.

Note it never issues a bare DELETE. A bare delete would hit the first row still inside its retention period and raise an error — the purge is careful to ask only for rows it is allowed to have.

---

Tables it will not touch

Tables without retention are never visited at all. Not skipped after being examined — never approached. If a table has no retention period, the purge has no business with it and does not go near it.

This is a guarantee, not a convention: a purge never removes a row from a table that did not ask for retention.

---

Limiting the scope

CALL pgvault_tables.purge_vault();                       -- everything
CALL pgvault_tables.purge_vault('archive');              -- one schema
CALL pgvault_tables.purge_vault('archive', 'statements'); -- one table

You can also name the arguments, which reads better in a script:

CALL pgvault_tables.purge_vault(schema_name => 'archive', table_name => 'statements');

---

When it complains

Naming a table that has no retention raises an error, before anything is deleted:

CALL pgvault_tables.purge_vault('public', 'audit_log');

ERROR:  "public.audit_log" is not a retention-gated vault table

That is on purpose. If you asked for a specific table by name, you meant that table, and quietly doing nothing would hide the mistake.

Naming a schema with nothing to purge does not raise. An empty schema is unremarkable:

CALL pgvault_tables.purge_vault('reporting');

NOTICE:  purge_vault removed 0 row(s) in total

Naming a table without a schema raises straight away:

CALL pgvault_tables.purge_vault(NULL, 'statements');

ERROR:  purge_vault requires a schema when a table is named

---

Running it regularly

Most sites run it nightly from cron, pg_cron, or whatever scheduler they already use. Your DBA will set this up — see Scheduling the Purge.

It runs on the primary, not on a standby, because it deletes rows.