A simple UPDATE on a table with millions of rows takes forever
UPDATE on millions of rows runs for many minutesShort 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.
Fix it fast
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_table2Match what you see, then jump to its fix
- 1
The reported row count equals the table size, though few values differThe update touches rows that need no changeFix - 2
The job holds locks for a long time and the table growsOne huge statement causes bloat and lock pressureFix - 3
The 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
Later queries on the table are slower and the table is larger than beforeDead row versions are not cleaned upFix
- 1
What does the error look like?
UPDATE on millions of rows runs for many minutesThe 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
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
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_table2Fix
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 onConfirmed by postgresql.org
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix