DEV Community

Cover image for Optimizing Materialized View Refresh to Minimize Locks in PostgreSQL
Divyansh Gupta
Divyansh Gupta

Posted on

Optimizing Materialized View Refresh to Minimize Locks in PostgreSQL

Optimizing Materialized View Refresh to Minimize Locks in PostgreSQL
This article explores an enhancement to a dual‑DB PostgreSQL setup that dramatically reduces lock contention by selectively using concurrent refresh on high‑dependency materialized views. We’ve also added architecture diagrams to clarify data flows and lock behavior.

1. Architectural Overview

Image description

  • Trans DB: Runs non‑concurrent refreshes on most MVs, leveraging local raw data where compute is fastest.

  • Cache DB: Defines FDW-based foreign tables on Trans DB’s MVs and refreshes its own MVs concurrently, ensuring UI queries never block.

2. Problem: Lock Explosion on High‑Dependency MV

When mvw_test (which depends on ~90% of other MVs) refreshed non‑concurrently, PostgreSQL acquires exclusive locks on each base relation. This causes:

Image description

Consequences:

  1. Widespread Contention: All other MVs and queries stall.

  2. Connection Saturation: Waiting sessions accumulate, exhausting slots.

  3. Memory Spikes: Queued locks consume RAM → OOM errors.

  4. Service Restarts: Cache DBOOM crashes, interrupting UI.

3. Solution: Selective Concurrent Refresh

Only the high‑impact MV uses CONCURRENTLY, dramatically reducing locking scope:

 -- Step 1: Ensure unique index CREATE UNIQUE INDEX CONCURRENTLY idx_test_pk ON mvw_test (key_column); -- Step 2: Refresh concurrently REFRESH MATERIALIZED VIEW CONCURRENTLY mvw_test;
Enter fullscreen mode Exit fullscreen mode

Why Concurrent?

  • Lightweight Locks: Shared locks only on the MV itself, not on its dependencies.

  • No Blocking: Other MVs and queries continue normally.

Scheduling:

  • Run at off‑peak, e.g., 3 AM daily via cron: 
 0 3 * * * psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mvw_test;"
Enter fullscreen mode Exit fullscreen mode
  • Monitor with: 
 SELECT pid, relation::regclass, mode, granted FROM pg_locks WHERE relation::regclass = 'mvw_test';
Enter fullscreen mode Exit fullscreen mode

4. Before vs. After: Lock Footprint

Image description

5. Benefits

  1. Reduced Contention: Dependencies remain unlocked.

  2. Stable Connections: Fewer waiters preserve slots.

  3. Better Memory Profile: No queue‑induced OOMs.

  4. Higher Availability: Cache DB stays up, UI never blocks.

  5. Focused Overhead: Only one MV pays the cost of concurrency.

6. Recommendations & Extensions

  • Index Health: Periodically REINDEX CONCURRENTLY the unique index.

  • Expand to Others: Identify other MVs with >50% dependencies for similar treatment.

  • Advanced: Consider incremental MVs (REFRESH MATERIALIZED VIEW ... WITH DATA) or logical replicas for ultra‑low‑lock reporting.

Top comments (0)