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.

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.
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.
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.
There are three common triggers:
Refreshing from a trigger on every row change is almost always a mistake, since each refresh recomputes the whole query.
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.
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.
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:
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.
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.
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.
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.
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.
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.