Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A simple UPDATE on a table with millions of rows takes forever

ErrorUPDATE on millions of rows runs for many minutes

Short answer

PostgreSQL does not change a row in place: each updated row gets a new version, and indexes and foreign keys must follow it. An UPDATE that touches millions of rows writes millions of versions. Update only rows that need it, work in batches, drop extra indexes during the job, then run VACUUM and ANALYZE.

  • Applies to PostgreSQL 16
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    See how many rows the statement really changes, and which indexes the table carries.

    SELECT count(*) FROM my_table WHERE status IS DISTINCT FROM 'done';
    \d my_table
  2. 2Match what you see, then jump to its fix

    1. 1The reported row count equals the table size, though few values differThe update touches rows that need no changeFix
    2. 2The job holds locks for a long time and the table growsOne huge statement causes bloat and lock pressureFix
    3. 3The table has many indexes or foreign keys and the update is slower than the row count suggestsIndexes and foreign keys are updated for every rowFix
    4. 4Later queries on the table are slower and the table is larger than beforeDead row versions are not cleaned upFix

What does the error look like?

  • UPDATE on millions of rows runs for many minutes
  • The table is much larger on disk after the update

Cause 1 of 4

The update touches rows that need no change

1Check

The count UPDATE reports includes matched rows whose values did not change. Every matched row is still written as a new version.

SELECT count(*) FROM my_table;

2Fix

Narrow the WHERE clause to rows that really differ.

UPDATE my_table
SET status = 'done'
WHERE status IS DISTINCT FROM 'done';

3Verify

The reported count now matches the rows that needed change.

SELECT count(*) FROM my_table WHERE status IS DISTINCT FROM 'done';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 4

One huge statement causes bloat and lock pressure

1Check

The manual warns that updates affecting many rows can cause table bloat, replica lag and lock contention, and suggests smaller batches with a VACUUM between them.

SELECT count(*) FROM my_table WHERE status IS DISTINCT FROM 'done';

2Fix

Update in slices and repeat until no rows remain.

UPDATE my_table
SET status = 'done'
WHERE ctid IN (
  SELECT ctid FROM my_table
  WHERE status IS DISTINCT FROM 'done'
  LIMIT 50000
);
VACUUM my_table;

3Verify

Repeat the statement until it reports UPDATE 0.

SELECT count(*) FROM my_table WHERE status IS DISTINCT FROM 'done';

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 4

Indexes and foreign keys are updated for every row

1Check

The manual says that adding large amounts of data may go faster if indexes are dropped first and recreated afterward, and that foreign keys can be checked in bulk more efficiently than row by row.

\d my_table

2Fix

For a one-off job, drop indexes you do not need during the update and recreate them afterwards. Raising maintenance_work_mem for the session helps the rebuilds.

DROP INDEX my_table_old_idx;
-- run the update
SET maintenance_work_mem = '1GB';
CREATE INDEX my_table_old_idx ON my_table (status);

3Verify

The same update now finishes faster.

\timing on

Confirmed by postgresql.org

Did this fix it?

Cause 4 of 4

Dead row versions are not cleaned up

1Check

Old row versions stay until VACUUM reclaims their space. Plain VACUUM makes it reusable but does not return it to the operating system. A table with huge dead space needs VACUUM FULL.

SELECT pg_size_pretty(pg_total_relation_size('my_table'));

2Fix

Run VACUUM and ANALYZE once the job ends. Use VACUUM FULL only when the space must go back to the operating system.

VACUUM (ANALYZE) my_table;

3Verify

Query plans use fresh statistics and the size stops growing.

SELECT pg_size_pretty(pg_total_relation_size('my_table'));

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Add WHERE conditions so unchanged rows are skipped.
  • Plan big data fixes as batched jobs, not one transaction.
  • Leave autovacuum on.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.