Skip to content

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.

SELECT id, _$purge_ts FROM statements;

 id |          _$purge_ts
----+-------------------------------
  1 | 2033-08-16 09:14:22.104+10

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

DELETE FROM statements WHERE id = 1;

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:

DELETE FROM statements WHERE _$purge_ts < now();

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 INSERT that names the column — the value is overwritten.
  • A COPY that 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