ALTER MATERIALIZED VIEW SET EXPIRE

Sets, replaces, or removes an EXPIRE ROWS row-retention policy on a materialized view (designed for passthrough views — see the concept page). Expired rows are hidden from queries immediately and reclaimed on disk in the background.

Syntax​

ALTER MATERIALIZED VIEW viewName SET EXPIRE ROWS
  { WHEN predicate
  | KEEP LATEST [ ON timestampColumn ] PARTITION BY col [, col ...]
  | KEEP [ N ] ( HIGHEST | LOWEST ) col [ PARTITION BY col [, col ...] ] }
  [ CLEANUP EVERY duration ]

ALTER MATERIALIZED VIEW viewName DROP EXPIRE

Parameters​

ParameterDescription
viewNameName of the passthrough materialized view to modify
WHEN predicatePer-row (or window) predicate; a row expires when it evaluates TRUE
KEEP LATESTKeep the latest row per PARTITION BY key, by the designated timestamp
KEEP [N] HIGHEST|LOWEST colKeep the rows at the max/min of col per group, or the top N
CLEANUP EVERYBackground reclamation cadence (e.g. 30m, 1h). Defaults to 1h if omitted

For the full description of each mode and its semantics, see the Expiring rows concept page.

When to use​

  • Add a retention policy to a passthrough view created without one
  • Switch a view between modes (e.g. from a value predicate to KEEP LATEST)
  • Tune the CLEANUP EVERY cadence
  • Remove a policy with DROP EXPIRE so the view keeps all rows again

How it works​

SET EXPIRE ROWS validates the new policy against the view's columns first (compiling the predicate / checking the key columns), so an invalid predicate or an unknown column is rejected immediately rather than breaking later reads. Once set, queries against the view are filtered to the kept rows immediately; physical reclamation follows in the background. See How it works.

Examples​

Per-row predicate, with a tighter cleanup cadence
ALTER MATERIALIZED VIEW trades_mirror
  SET EXPIRE ROWS WHEN amount < 1.5 CLEANUP EVERY 30m;
Keep the latest row per symbol
ALTER MATERIALIZED VIEW trades_mirror
  SET EXPIRE ROWS KEEP LATEST PARTITION BY symbol;
Keep the highest-priced row per symbol
ALTER MATERIALIZED VIEW trades_mirror
  SET EXPIRE ROWS KEEP HIGHEST price PARTITION BY symbol;
Keep the 2 highest-priced rows per symbol
ALTER MATERIALIZED VIEW trades_mirror
  SET EXPIRE ROWS KEEP 2 HIGHEST price PARTITION BY symbol;
Remove the policy
ALTER MATERIALIZED VIEW trades_mirror DROP EXPIRE;

Behavior​

AspectDescription
Passthrough recommendedAn aggregating view is accepted with a logged advisory: a later refresh can regenerate reclaimed rows, so align base-table retention with the expiry horizon
No dependent viewsRejected when other materialized views derive from this view (they would copy expired rows on refresh)
ValidationThe policy is checked against the view's columns before it is applied
Immediate effectReads are filtered to the kept rows as soon as the policy is set
ReplicationThe policy and the reclamation it drives replicate as normal WAL traffic

Permissions (Enterprise)​

Changing the policy requires the ALTER MATERIALIZED VIEW permission:

Grant alter permission
GRANT ALTER MATERIALIZED VIEW ON trades_mirror TO user1;

Errors​

ErrorCause
materialized view does not existView with the specified name doesn't exist
cannot set an EXPIRE ROWS policy on '...': it is the base of N materialized view(s), which would copy expired rows on refreshOther materialized views derive from this view
EXPIRE ROWS KEEP LATEST ON must name the designated timestamp ...ON names a column other than the designated timestamp
invalid EXPIRE ROWS KEEP LATEST column: ...A PARTITION BY key column does not exist
EXPIRE ROWS KEEP / window retention cannot be used on a view with a column named '__qdb_re_keep'The view exposes a column named like the reserved keep column
invalid EXPIRE ROWS predicate: ...The predicate does not parse, bind, or type-check against the view's columns
permission deniedMissing ALTER MATERIALIZED VIEW permission (Enterprise)

See also​