Retention¶
Retention says how long a row must be kept before it may be deleted.
CREATE TABLE statements (id bigint, body text)
USING vault
WITH (permissions = 'insert', retention = 2555); -- seven years
retention is a whole number of days, minimum 1.
The _$purge_ts column¶
When you set retention, the extension adds a column called _$purge_ts to your table. You do not create it and you cannot leave it out.
It holds the moment the row becomes eligible for deletion — not when the row arrived. So a row inserted today into a table with retention = 2555 gets a _$purge_ts seven years from now.
You can query it like any other column. The odd-looking name is deliberate — it keeps out of the way of your own column names, and PostgreSQL accepts it unquoted.
What happens if you try to delete too early¶
ERROR: row in vault table "statements" is still within its retention period
DETAIL: The table retains rows for 2555 days after insertion.
HINT: Restrict the statement to eligible rows, for example WHERE _$purge_ts < now().
It is an error, not a silent skip. You will know it did not happen.
To delete only the rows that are old enough:
That never raises — it simply matches nothing if nothing has expired yet. This is exactly what the purge routine does for you; see Purging Expired Rows.
Nothing can move the deadline¶
This is the point of the whole feature, so it is worth being explicit. _$purge_ts cannot be changed by:
- An
INSERTthat names the column — the value is overwritten. - A
COPYthat supplies it — same. - An
UPDATE— the original value is carried across unchanged. - A trigger that sets it — the extension overrules it.
It is written once, when the row is inserted, and stays put.
Two rules to know¶
Retention cannot be added later. Because ALTER TABLE is refused and the column would not exist, you cannot turn retention on for a table that did not start with it, and you cannot change the number of days. Decide up front.
delete and retention together mean something specific. You can grant both. But delete permits removal at any time, so on such a table retention no longer protects anything from a direct DELETE — it only governs what the purge routine removes.
That is a deliberate option, for clearing unimportant rows ahead of a long retention period. But if the table must genuinely hold its rows for the full period, grant retention and not delete.
| What you want | What to declare |
|---|---|
| Rows must survive N days, no exceptions | retention = N, without delete |
| Rows must survive N days, but some may be cleared early by hand | permissions = '...,delete' with retention = N |
| The table may be emptied wholesale | permissions = '...,truncate', without retention |
Copying a retained table: preserve the deadlines explicitly
_$purge_ts is ordinary column data, and the insert path recomputes it unless told not to. Copy a retained table without restore_mode and every row silently gets a fresh deadline, measured from the moment of the copy.
That is not merely untidy. Migrating a table from retention = 2555 to retention = 365 with a naive copy took a deadline of 2033 down to 2027 in testing — six years of retention destroyed, and those rows become purgeable six years early. Copying into a table with the same period pushes every deadline out instead, which is safe from early deletion but records a date that is simply wrong.
Set pg_vault_tables.restore_mode for the copy, and name the column:
CREATE TABLE ledger_v2 (id int, amount numeric) USING vault
WITH (permissions = 'insert', retention = 2555);
SET pg_vault_tables.restore_mode = on; -- superuser only
INSERT INTO ledger_v2 (id, amount, _$purge_ts)
SELECT id, amount, _$purge_ts FROM ledger_v1;
SET pg_vault_tables.restore_mode = off;
Then check, before you rely on it:
SELECT count(*) FROM ledger_v1 a JOIN ledger_v2 b USING (id)
WHERE a._$purge_ts <> b._$purge_ts; -- must be 0
CREATE TABLE ... AS SELECT cannot be used for a retained table at all. It refuses retention, because the deadline column cannot be added to a table whose columns come from a query. Use the two-statement form above.
If you also grant log
SET UNLOGGED stops the table's contents being written to the write-ahead log, and an unlogged table is emptied on crash recovery. Retained rows can be lost that way without any DELETE being involved.
The deadlines themselves are safe: a SET LOGGED/SET UNLOGGED round trip leaves every _$purge_ts exactly as it was. It is the rows that depend on the server staying up.
One more thing worth weighing up
Granting drop alongside retention is allowed, and it does mean the whole table — every retained row with it — can be removed before any deadline has passed. DROP removes the table outright rather than deleting rows, so retention never gets a say.
That is often exactly right: retention governs the life of the rows, and dropping the table is a deliberate decision to retire the whole thing. But if a table must be certain to hold its rows for the full period, consider leaving drop out as well, and accept that retiring it later means dropping the database it lives in.