Nov 20, 20265 min read

Postgres Materialized Views: Refresh Strategies for APIs

When a materialized view beats a cache or a summary table, how REFRESH and REFRESH CONCURRENTLY differ, the unique index requirement, scheduling refreshes, and permission details that trip up application roles.

Illustration for Postgres Materialized Views: Refresh Strategies for APIs

A dashboard endpoint that aggregates a few million order rows on every request works in staging and times out in production. A materialized view stores the result of that query as a table, and the endpoint then reads precomputed rows in milliseconds, with the data only as fresh as the last refresh, so how and when the view refreshes decides whether the trade works.

How it works

A materialized view is a query whose result is written to disk when it is created and replaced on each refresh, as the materialized views docs describe:

CREATE MATERIALIZED VIEW daily_sales AS SELECT date_trunc('day', created_at)::date AS day, store_id, count(*) AS orders, sum(total_cents) AS revenue_cents FROM orders WHERE status = 'paid' GROUP BY 1, 2; CREATE UNIQUE INDEX daily_sales_day_store ON daily_sales (day, store_id);

It behaves like a table for reads, including indexes, but it cannot be written to directly. Every refresh reruns the full query; Postgres has no built-in incremental maintenance.

REFRESH vs REFRESH CONCURRENTLY

REFRESHREFRESH ... CONCURRENTLY
LockACCESS EXCLUSIVE, blocks readsEXCLUSIVE, reads continue
RequirementNoneA unique index on plain columns, no WHERE, covering all rows
SpeedFaster, swaps in new contentsSlower, computes a diff and applies it
BloatNoneUpdates and deletes rows, needs vacuum

The REFRESH reference documents both, and for anything an API reads, use CONCURRENTLY, since a plain refresh of a view that takes 30 seconds to compute means 30 seconds of blocked requests. Only one refresh of a view can run at a time, so a schedule that fires while the previous run is still going waits.

Scheduling refreshes

There are three common triggers:

  • On a schedule. A job every 5 or 15 minutes is the simplest, and the staleness is bounded by the interval plus the refresh time.
  • After batch loads. An import or nightly sync refreshes once at the end.
  • On demand. An admin action or webhook triggers a refresh, usually debounced so a burst of events causes one refresh.

Refreshing from a trigger on every row change is almost always a mistake, since each refresh recomputes the whole query.

The permission detail

Only the owner of a materialized view can refresh it (Postgres 17 adds a MAINTAIN privilege that can be granted). Applications often connect with a role that does not own the objects that migrations created, so the refresh fails with a permission error in production while working locally as a superuser. A small SECURITY DEFINER function, owned by the migration role, gives the application exactly one capability:

CREATE FUNCTION refresh_daily_sales() RETURNS void LANGUAGE sql SECURITY DEFINER SET search_path = public AS $$ REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales; $$;

Setting search_path on a security definer function is the precaution the CREATE FUNCTION docs recommend.

When something else fits better

A materialized view suits aggregates over large tables where minutes of staleness are fine. When the result must be current, a summary table updated in the same transaction as the source rows is better, at the cost of write contention on hot summary rows. When the expensive part is one query per user rather than one global aggregate, a cache keyed by user is usually cheaper than a view holding every user's result.

Best fit: dashboards, leaderboards and reporting endpoints over large tables that can tolerate a refresh interval.

Materialized views in Encore

Encore

Encore is a backend framework with built-in infrastructure, so the view, its refresh schedule and the endpoint that reads it live in one service: the view and the refresh function are a migration, and the schedule is a cron job declared in Go:

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

package reports import ( "context" "time" "encore.dev/cron" "encore.dev/storage/sqldb" ) // Migrations create the orders table, the daily_sales view, // its unique index and refresh_daily_sales(). var db = sqldb.NewDatabase("reports", sqldb.DatabaseConfig{Migrations: "./migrations"}) var _ = cron.NewJob("refresh-daily-sales", cron.JobConfig{ Title: "Refresh daily sales view", Every: 15 * cron.Minute, Endpoint: RefreshDailySales, }) //encore:api private func RefreshDailySales(ctx context.Context) error { _, err := db.Exec(ctx, `SELECT refresh_daily_sales()`) return err } type DaySales struct { Day time.Time `json:"day"` Orders int64 `json:"orders"` RevenueCents int64 `json:"revenue_cents"` } type SalesResponse struct { Days []DaySales `json:"days"` } //encore:api auth method=GET path=/stores/:storeID/sales func StoreSales(ctx context.Context, storeID int64) (*SalesResponse, error) { rows, err := db.Query(ctx, ` SELECT day, orders, revenue_cents FROM daily_sales WHERE store_id = $1 AND day >= current_date - 30 ORDER BY day`, storeID) if err != nil { return nil, err } defer rows.Close() resp := &SalesResponse{} for rows.Next() { var d DaySales if err := rows.Scan(&d.Day, &d.Orders, &d.RevenueCents); err != nil { return nil, err } resp.Days = append(resp.Days, d) } return resp, rows.Err() }

The cron job is managed by the Encore platform in deployed environments and does not run locally or in preview environments, so during development encore run starts Postgres with the migrations applied and you call RefreshDailySales from the local development dashboard to populate the view. The databases docs cover the query API.

An AI coding agent adding a new report can create the view, its unique index, the refresh function and the schedule in one change, and the reviewer sees the staleness interval written next to the endpoint that serves it.

Best fit: services serving dashboards or reports from Postgres that want the refresh schedule versioned with the query.

How to approach it

Create the view with a unique index from the start so concurrent refresh works, wrap the refresh in a security definer function, and schedule it at the staleness your users accept. Measure the refresh time as data grows, and move to a summary table when the refresh takes longer than the interval.

Frequently asked questions

Does REFRESH MATERIALIZED VIEW block reads?

Yes. A plain REFRESH takes an ACCESS EXCLUSIVE lock, so queries against the view wait until the refresh finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY lets reads continue against the old contents while the new ones are computed.

Why does REFRESH MATERIALIZED VIEW CONCURRENTLY fail?

It requires at least one unique index on the materialized view that uses only column names, with no WHERE clause, and covers all rows. Without one, Postgres refuses the concurrent refresh. It also cannot be used on a view that has never been populated.

Does Postgres refresh materialized views automatically?

No. Postgres has no automatic or incremental refresh. You run REFRESH from a scheduled job, a trigger or the application, and every refresh recomputes the whole query.

How do I refresh a materialized view on a schedule in Encore?

Create the view and a SECURITY DEFINER refresh function in a migration, then declare a cron job whose endpoint runs SELECT refresh_daily_sales(). Cron jobs are managed by the Encore platform in deployed environments and do not run locally, where you call the endpoint by hand.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders