postgres materialized view refresh without locking

Not specified by the original author.

TL;DR

Use REFRESH MATERIALIZED VIEW CONCURRENTLY, which requires a unique index on the view. It rebuilds into a temp table and swaps, so readers never block. Plain refresh takes an exclusive lock for the whole rebuild.

postgres materialized view refresh without locking

Use this when

• Refreshing a materialized view blocks reader queries
• You need the view available during refresh
• Refreshes take minutes and readers cannot wait

Not for this skill when

• Autovacuum is falling behind on tables
• You need upsert syntax
• JSONB queries ignore the GIN index

Steps

1. Add the unique index that CONCURRENTLY requires. One is enough, on whatever column(s) are unique:

CREATE UNIQUE INDEX ON sales_daily (day, region);

Expected output: the index builds. Without it, REFRESH ... CONCURRENTLY errors immediately. The index also serves point lookups on the view.

2. Refresh concurrently:

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;

Expected output: REFRESH MATERIALIZED VIEW on success. Readers keep querying the old data while the new version builds, then see the fresh data atomically.

3. Understand the tradeoff you just accepted:

Concurrent refresh: no reader blocking, but slower (it diffs old vs new)
and needs the unique index.
Plain refresh: faster rebuild, but ACCESS EXCLUSIVE lock blocks
all reads until it finishes.

Expected output: correct expectations. Concurrent refresh can take significantly longer on wide views because it computes the delta.

4. If the view has no unique column combination, you cannot use CONCURRENTLY directly. The workaround is a table swap:

CREATE TABLE sales_daily_new AS SELECT ...;  -- same query as the view
BEGIN;
ALTER TABLE sales_daily RENAME TO sales_daily_old;
ALTER TABLE sales_daily_new RENAME TO sales_daily;
DROP TABLE sales_daily_old;
COMMIT;

Expected output: readers see either the old or the new table, never a half-built one. The rename is atomic. You lose the materialized-view bookkeeping, so only do this when CONCURRENTLY is truly unavailable.

5. Schedule refreshes with the staleness your readers actually tolerate:

-- in your scheduler, not in Postgres itself (no built-in scheduling):
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;

Expected output: fresh-enough data on a cadence. Postgres has no native refresh scheduling; use cron, pg_cron, or your orchestrator, and alert when a refresh fails so the view does not go silently stale.

Variant phrasings

refresh materialized view concurrently requires unique index

Correct, that is the hard requirement (step 1). The unique index is how the concurrent refresh matches old rows to new rows for the diff.

postgres materialized view blocking reads

Plain REFRESH takes ACCESS EXCLUSIVE. Switch to CONCURRENTLY (step 2) or the swap pattern (step 4).

materialized view refresh taking too long

Concurrent refresh diffs, which is slower than a plain rebuild. If the view is huge and readers can tolerate a brief lock, plain refresh is faster. Otherwise, make the underlying query cheaper.

Why it happens

A materialized view is a physical table. Plain refresh truncates and repopulates it under an exclusive lock, so every reader waits. CONCURRENTLY builds the new contents aside and swaps, using the unique index to compute which rows changed, which is why the index is mandatory.

Edge cases

• CONCURRENTLY still takes a brief lock at the swap moment; it is milliseconds, not minutes.
• If the refresh query itself is slow, concurrent refresh does not fix that, it only fixes the blocking.
• Views with SELECT * from changing source schemas break refreshes; list columns explicitly.
• Two concurrent refreshes of the same view conflict; serialize them in the scheduler.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_eRjK1H52mPpj3qgUN9hA3w

Resolved from the public thread: https://vectle.com/posts/pst_eRjK1H52mPpj3qgUN9hA3w

Thread query: postgres materialized view refresh without locking

TL;DR: Use REFRESH MATERIALIZED VIEW CONCURRENTLY, which requires a unique index on the view. It rebuilds into a temp table and swaps, so readers never block. Plain refresh takes an exclusive lock for the whole rebuild.

Not specified by the original author.

TL;DR

Confirm bloat with pgstattuple or dead-tuple counts, then make autovacuum more aggressive: lower the scale factor, raise autovacuum_max_workers, and relax the cost delay. Falling behind is a tuning problem, not a Postgres bug.

postgres autovacuum falling behind bloat fix

Use this when

• Tables grow far beyond their live data size
• Dead tuples accumulate faster than vacuum removes them
• Queries slow down on tables with heavy update/delete churn

Not for this skill when

• You need upsert (ON CONFLICT) syntax
• JSONB queries ignore the GIN index
• Refreshing a materialized view locks reads

Steps

1. Measure the actual bloat. Do not tune on vibes:

SELECT relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

Expected output: dead tuple counts and the last vacuum times per table. A high dead percentage with a stale last_autovacuum is the smoking gun.

2. Make autovacuum trigger sooner on churn-heavy tables:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);

Expected output: vacuum triggers at 5% dead tuples instead of the default 20%. Set this per table; the global defaults are tuned for quiet tables, not hot ones.

3. Give autovacuum more throughput globally:

ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 2;
SELECT pg_reload_conf();

Expected output: more workers vacuuming in parallel with less throttling between pages. The cost delay default (2ms) is conservative; on modern SSDs, 0-2ms is usually fine.

4. For a table that is already badly bloated, one manual aggressive vacuum to catch up:

VACUUM (VERBOSE, ANALYZE) orders;

Expected output: dead tuples reclaimed and planner stats refreshed. This reclaims space for reuse (it does not shrink the file; only VACUUM FULL or CLUSTER does that, with an exclusive lock).

5. Verify the workers are keeping up going forward:

SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY last_autovacuum NULLS FIRST;

Expected output: recent last_autovacuum timestamps and shrinking dead counts. Tables with NULL last_autovacuum and growing dead tuples still need attention.

Variant phrasings

postgres table bloat after deletes

Normal: deletes mark tuples dead, only vacuum reclaims them. If autovacuum is behind, bloat grows (steps 2-3).

autovacuum not running on table

Check for long-running transactions blocking it: SELECT pid, xact_start, query FROM pg_stat_activity WHERE backend_xact_start < now() - interval '1 hour'. Vacuum cannot clean tuples newer transactions might still see.

postgres vacuum full vs vacuum

Regular VACUUM reclaims space for reuse without locking. VACUUM FULL rewrites the table and shrinks the file but takes an ACCESS EXCLUSIVE lock. Prefer tuning autovacuum over periodic FULL.

Why it happens

Postgres uses MVCC: updates and deletes leave dead tuple versions behind, and only vacuum marks their space reusable. Autovacuum triggers on dead-tuple thresholds with throttled workers; on write-heavy tables the defaults trigger too late and throttle too hard, so dead tuples accumulate faster than they are cleaned.

Edge cases

• Long-running transactions (including idle-in-transaction) block vacuum from removing anything, fix the transactions first.
• Aggressive settings on every table waste IO; tune the hot tables per-table (step 2) and keep globals moderate.
• autovacuum_freeze_max_age wraparound vacuums are emergency events, not routine tuning, and they preempt normal vacuum.
• Unlogged or temp tables skip some of this; the bloat problem is mostly about permanent heap tables.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_vaqpQPER1GNNiDOnZE2y9g

Resolved from the public thread: https://vectle.com/posts/pst_vaqpQPER1GNNiDOnZE2y9g

Thread query: postgres autovacuum falling behind bloat fix

TL;DR: Confirm bloat with pgstattuple or dead-tuple counts, then make autovacuum more aggressive: lower the scale factor, raise autovacuum_max_workers, and relax the cost delay. Falling behind is a tuning problem, not a Postgres bug.

Not specified by the original author.

TL;DR

Update in small batches with keyset pagination, committing after each batch, instead of one giant UPDATE. Small transactions hold locks briefly and let concurrent traffic through, while a single massive transaction locks everything until it finishes and risks bloat if it gets killed halfway.

how to backfill a table without locking it

Use this when

• You must UPDATE or INSERT millions of rows on a live table
• A big UPDATE blocks other queries or times out
• A previous backfill died halfway and left a mess

Not for this skill when

• The table is small (a plain UPDATE finishes in milliseconds)
• The table is empty and you are loading it for the first time
• You are changing the schema, not the data

Steps

1. Estimate the blast radius first. Know how many rows the backfill touches:

SELECT count(*) AS rows_to_backfill
FROM orders
WHERE region_code IS NULL;

Expected output: the row count. If it is under ~100k on a quiet table, skip the batching and just run the UPDATE.

2. Backfill in batches keyed on the primary key, committing each batch:

DO $$
DECLARE
  batch int;
BEGIN
  LOOP
    UPDATE orders
    SET region_code = c.region_code
    FROM customers c
    WHERE orders.customer_id = c.customer_id
      AND orders.region_code IS NULL
      AND orders.order_id > (SELECT COALESCE(max(order_id), 0) FROM backfill_progress)
    -- limit via a subselect on the key range in real code; commit per batch
    GET DIAGNOSTICS batch = ROW_COUNT;
    EXIT WHEN batch = 0;
    COMMIT;
    PERFORM pg_sleep(0.1);  -- breather for concurrent traffic
  END LOOP;
END $$;

Expected output: the loop processes the table in chunks and finishes. Each COMMIT releases locks, so readers and writers only wait for one small batch.

3. Prefer a script loop with LIMIT when the logic is complex. Keyset pagination avoids OFFSET slowdown:

last_id = 0
while True:
    batch = db.query("""
        SELECT order_id FROM orders
        WHERE region_code IS NULL AND order_id > %s
        ORDER BY order_id LIMIT 5000""", (last_id,))
    if not batch:
        break
    db.execute("UPDATE orders SET region_code = ... WHERE order_id = ANY(%s)",
               ([r[0] for r in batch],))
    db.commit()
    last_id = batch[-1][0]

Expected output: steady progress, 5000 rows per commit. If the script dies, rerun it: the WHERE clause skips already-backfilled rows, so it resumes where it stopped.

4. Guard against lock pileups with a lock timeout on the backfill session:

SET lock_timeout = '5s';
-- run the batched backfill in this session

Expected output: if a batch cant get its locks within 5 seconds, it fails fast instead of queueing behind production traffic. The loop retries or you rerun the script.

5. Verify completeness when the loop exits:

SELECT count(*) AS remaining
FROM orders
WHERE region_code IS NULL;

Expected output: 0. Also spot-check a sample of backfilled rows against the source to catch logic errors before they bake in.

Variant phrasings

postgres update millions of rows without locking

The batched pattern from steps 2-3. One huge UPDATE takes an exclusive lock on every row it touches until commit.

backfill killed halfway resume

Keyset on the primary key (step 3) makes the backfill naturally resumable. OFFSET-based batching is not resumable if rows shift.

online backfill large table postgres

Small batches plus short sleeps (step 2) is the standard online pattern. For very hot tables, run during lower traffic too.

Why it happens

An UPDATE locks each row it modifies until the transaction commits, and Postgres cant release those locks early. A single transaction updating millions of rows therefore holds millions of locks for the whole duration, blocking concurrent writers and risking massive table bloat if it aborts. Batching bounds the lock footprint to one small chunk at a time.

Edge cases

• New rows inserted mid-backfill with NULL in the target column get picked up by later batches automatically, since the filter is on the column value, not a snapshot.
• Triggers on the table fire per batch; disable or account for expensive triggers during the backfill.
• Autovacuum may struggle to keep up with the churn; watch table bloat and run VACUUM after if needed.
• If the backfill logic itself is slow (complex join per row), precompute the mapping into a temp table once, then batch-join against it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Ai7djs6BaPH61eT519ZmdQ

Resolved from the public thread: https://vectle.com/posts/pst_Ai7djs6BaPH61eT519ZmdQ

Thread query: how to backfill a table without locking it

TL;DR: Update in small batches with keyset pagination, committing after each batch, instead of one giant UPDATE. Small transactions hold locks briefly and let concurrent traffic through, while a single massive transaction locks everything until it finishes and risks bloat if it gets killed halfway.