Materialized views

Backend Fundamentals · Databases · 9 min read

1. What problem does this solve?

If materialized views are not used, systems often resort to:

• Recomputing aggregates on every request (inefficient).
• Caching at the application layer (requires custom invalidation logic).
• Using denormalized tables (increases write complexity).

Materialized views provide a database‑level, declarative way to store pre‑computed results that can be refreshed on demand or automatically, giving predictable performance and consistency.

2. Mental model

Analogy: The “Pre‑Cooked Meal”

Imagine you’re a chef who needs to serve a large crowd of diners. Instead of cooking a full meal from scratch for each order, you prepare a pre‑cooked dish (e.g., a lasagna) in advance. When a diner orders lasagna, you just plate it and serve it instantly. The pre‑cooked dish is the materialized view: a ready‑made result that can be served quickly, while the kitchen (the underlying tables) keeps cooking new ingredients.

Engineering model

• Base tables: Raw data (e.g., orders, order_items).
• Materialized view: A table that stores the result of a query over the base tables (e.g., daily_sales).
• Refresh: Re‑run the query to update the view, either on demand (REFRESH MATERIALIZED VIEW) or automatically (e.g., using triggers or a scheduler).
• Query: The application reads from the materialized view instead of the base tables, gaining speed.

3. Deep explanation

Internals (PostgreSQL example)

1. Creation

   CREATE MATERIALIZED VIEW daily_sales AS
   SELECT
     DATE(order_date) AS day,
     SUM(total_amount) AS revenue
   FROM orders
   GROUP BY 1;
   

   PostgreSQL creates a new table and populates it with the query result.

2. Storage
   The view is stored as a regular table on disk. It has its own indexes, statistics, and can be queried like any other table.

3. Refresh

   • Full refresh (REFRESH MATERIALIZED VIEW) rebuilds the entire view.
   • Concurrent refresh (REFRESH MATERIALIZED VIEW CONCURRENTLY) keeps the old data available while rebuilding, but requires a unique index.

4. Dependencies
   PostgreSQL tracks dependencies. If a base table changes, the view is not automatically refreshed; you must trigger it yourself.

Important terminology

Trade‑offs

Failure modes

• Stale data: If refresh is delayed, consumers see outdated results.
• Refresh lock contention: A full refresh locks the view, blocking reads.
• Index bloat: Frequent refreshes can cause index fragmentation.
• Dependency drift: Schema changes to base tables may break the view if not updated.

Limitations

• Not supported in all RDBMS (e.g., MySQL before 8.0.13).
• Cannot be used for real‑time streaming updates without external orchestration.
• No built‑in support for incremental refresh in PostgreSQL (requires manual logic or extensions like pg_partman).

Where it fits in a backend architecture

┌───────────────────────┐
│  Application Layer    │
│  (Next.js / FastAPI)  │
└─────────────┬─────────┘
              │
              ▼
┌───────────────────────┐
│  Query Layer           │
│  (PostgreSQL)          │
│  ├─ Base tables        │
│  ├─ Materialized view │
│  └─ Indexes            │
└─────────────┬─────────┘
              │
              ▼
┌───────────────────────┐
│  Cache Layer (Redis)   │
└───────────────────────┘

The application queries the materialized view for fast reads. A background job (e.g., a cron job or Kafka consumer) triggers REFRESH MATERIALIZED VIEW to keep data fresh.

4. How it works

1. Define the view – Write a SELECT that aggregates or joins base tables.
2. Create the view – PostgreSQL stores the result in a new table.
3. Add indexes – Create indexes on the view to speed up downstream queries.
4. Schedule refresh – Use a scheduler (cron, pg_cron, Airflow) or triggers to run REFRESH MATERIALIZED VIEW.
5. Query the view – Application reads from the view as if it were a regular table.
6. Handle staleness – Decide on a refresh cadence that balances freshness and load.

sequenceDiagram
    participant App as Application
    participant DB as PostgreSQL
    participant Scheduler as Scheduler

    App->>DB: SELECT * FROM daily_sales
    DB-->>App: Return cached aggregates

    Scheduler->>DB: REFRESH MATERIALIZED VIEW daily_sales
    DB->>DB: Rebuild view table
    DB-->>Scheduler: Done

5. Practical implementation

Node.js example (TypeScript)

// src/db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// src/materializedView.ts
import { pool } from './db';

export async function refreshDailySales() {
  // Full refresh – blocks reads until complete
  await pool.query('REFRESH MATERIALIZED VIEW daily_sales');
  console.log('daily_sales refreshed');
}

// src/server.ts
import express from 'express';
import { pool } from './db';
import { refreshDailySales } from './materializedView';

const app = express();
const PORT = process.env.PORT ?? 3000;

// Endpoint that reads from the materialized view
app.get('/api/daily-sales', async (req, res) => {
  const { rows } = await pool.query(
    'SELECT day, revenue FROM daily_sales ORDER BY day DESC LIMIT 30'
  );
  res.json(rows);
});

// Endpoint to trigger manual refresh (for dev/testing)
app.post('/api/refresh-sales', async (req, res) => {
  await refreshDailySales();
  res.send('Refreshed');
});

app.listen(PORT, () => console.log(`🚀 Server listening on ${PORT}`));

How to schedule automatic refresh

# Using cron (runs every hour)
0 * * * * /usr/bin/psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;"

Or use pg_cron extension:

SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales');

Adding an index

CREATE UNIQUE INDEX daily_sales_day_idx ON daily_sales(day);

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

Architecture decisions

• Refresh strategy: Manual vs. scheduled vs. trigger‑based.

  • Manual: fine for low‑frequency updates.
  • Scheduled: good for predictable workloads.
  • Trigger‑based: requires careful design to avoid excessive refreshes.

• Concurrent vs. full refresh: Choose CONCURRENTLY when reads must remain available. Requires a unique index.

• Partitioning: For very large views, partition by time or key to improve maintenance and query performance.

• Cache layer: Combine materialized views with Redis caching for ultra‑fast reads.

Trade‑offs

• Freshness vs. load: Higher refresh frequency → more load on the database.
• Storage vs. speed: More indexes speed reads but increase write cost during refresh.

Bottlenecks

• Refresh lock: Even CONCURRENTLY can lock the view for a short period.
• CPU spikes: Large refreshes can consume significant CPU, impacting other queries.
• Disk I/O: Writing the refreshed table can saturate disk bandwidth.

Failure scenarios

• Refresh crash: View remains stale; need a fallback strategy.
• Index corruption: Can cause query errors; monitor index health.
• Schema drift: Adding a column to a base table may break the view; enforce migrations.

Monitoring

• Prometheus metrics: materialized_view_last_refresh, materialized_view_refresh_duration_seconds.
• Alerting: Trigger if refresh fails or exceeds threshold.
• Logging: Store refresh logs with timestamps.

Cost/performance considerations

• Compute cost: Frequent refreshes increase CPU usage.
• Storage cost: View tables consume disk; consider compression.
• Network cost: If the view is queried across services, consider colocating it.

When NOT to use

• Real‑time data: If you need millisecond latency, materialized views are too slow.
• Highly volatile data: If data changes every second, the refresh overhead outweighs benefits.
• Simple queries: If a query is already fast, adding a view adds unnecessary complexity.

9. Interview questions

10. Hands‑on task

Goal: Build a small Node.js service that exposes an endpoint /api/top-products returning the top 10 products by sales for the last 30 days. Use a materialized view to store the aggregated data and refresh it every hour.

Steps

1. Create a PostgreSQL table orders and order_items.
2. Write a CREATE MATERIALIZED VIEW top_products AS SELECT product_id, SUM(quantity) AS qty FROM order_items GROUP BY product_id;.
3. Add an index on qty DESC to speed up the top‑10 query.
4. Implement a cron job (or pg_cron) that runs REFRESH MATERIALIZED VIEW CONCURRENTLY top_products; every hour.
5. Create an Express endpoint that queries the view and returns the top 10 rows.
6. Verify that the endpoint returns fast results and that the view updates correctly after new orders are inserted.

Deliverables

• SQL migration scripts for tables and view.
• Node.js code for the API and refresh job.
• Documentation of the refresh schedule and any index creation.

11. Quick revision

• Materialized view = pre‑computed, stored result set.
• Created with CREATE MATERIALIZED VIEW.
• Requires manual or scheduled REFRESH.
• CONCURRENTLY keeps reads available but needs a unique index.
• Storing the view consumes disk and requires index maintenance.
• Ideal for heavy read, low‑write or batch‑updated data.
• Not suitable for real‑time or highly volatile data.
• Use with caching (Redis) for ultra‑fast reads.
• Monitor refresh duration, staleness, and failure rates.
• Avoid over‑refreshing; balance freshness vs. load.
• Keep view definition simple; add indexes on query columns.
• Test refresh logic in staging before production.

12. References

• PostgreSQL Documentation – Materialized Views: https://www.postgresql.org/docs/current/sql-creatematerializedview.html
• PostgreSQL Documentation – Refreshing Materialized Views: https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
• pg_cron Extension: https://github.com/citusdata/pg_cron
• Node.js pg library: https://node-postgres.com/
• Express.js: https://expressjs.com/
• Mermaid Live Editor: https://mermaid.live/