ALTER MATERIALIZED VIEW SET REFRESH LIMIT

Sets a time limit for incremental refresh, preventing old data from overwriting existing aggregations in a materialized view.

Syntax​

ALTER MATERIALIZED VIEW viewName SET REFRESH LIMIT n timeUnit

Where timeUnit is: HOURS | DAYS | WEEKS | MONTHS | YEARS (or shorthand: h, d, w, M, y)

Parameters​

ParameterDescription
viewNameName of the materialized view to modify
nNumber of time units
timeUnitTime unit for the limit

When to use​

Set a refresh limit when:

  • You want to protect historical aggregations from late-arriving data
  • Base table receives out-of-order inserts with old timestamps
  • You need predictable refresh behavior for auditing

How it works​

When a refresh limit is configured, incremental refresh ignores base table rows with timestamps older than the limit. This protects already-computed aggregations from being recalculated.

Example scenario:

Set 1-week refresh limit
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH LIMIT 1 WEEK;

If the current time is 2025-05-02T12:00:00Z and you insert:

Insert rows with various timestamps
INSERT INTO trades VALUES
  ('2025-03-02T12:00:00Z', 'BTC-USD', 39269.98, 0.042),  -- 2 months old, ignored
  ('2025-04-02T12:00:00Z', 'BTC-USD', 39170.01, 0.042),  -- 1 month old, ignored
  ('2025-05-02T12:00:00Z', 'BTC-USD', 38450.10, 0.042);  -- current, processed

Only the third row (within the 1-week limit) triggers an incremental refresh.

note

The limit only applies to incremental refresh. A REFRESH MATERIALIZED VIEW FULL command processes all base table rows regardless of the limit.

Time units​

UnitSingularPluralShorthand
HoursHOURHOURSh
DaysDAYDAYSd
WeeksWEEKWEEKSw
MonthsMONTHMONTHSM
YearsYEARYEARSy

Fixed vs calendar-based:

  • HOURS, DAYS, WEEKS: Fixed durations (1 week = 7 days exactly)
  • MONTHS, YEARS: Calendar-based (1 month from Jan 15 → Feb 15)

Examples​

Set limit to 1 day
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH LIMIT 1 DAY;
Set limit to 8 hours (shorthand)
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH LIMIT 8h;
Set limit to 2 weeks
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH LIMIT 2 WEEKS;

Refreshing data outside the limit​

To refresh data older than the limit, use range refresh:

Refresh a specific time range
REFRESH MATERIALIZED VIEW trades_hourly
  RANGE FROM '2025-03-01T00:00:00Z' TO '2025-03-02T00:00:00Z';

Permissions (Enterprise)​

Changing refresh limit requires the ALTER MATERIALIZED VIEW permission:

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

Errors​

ErrorCause
materialized view does not existView with specified name doesn't exist
invalid time unitUnrecognized time unit
permission deniedMissing ALTER MATERIALIZED VIEW permission (Enterprise)

See also​