Best way to delete millions of rows by ID

by

It all depends …

  • Assuming no concurrent write access to involved tables or you may have to lock tables exclusively or this route may not be for you at all.

  • Delete all indexes (possibly except the ones needed for the delete itself).
    Recreate them afterwards. That’s typically much faster than incremental updates to indexes.

  • Check if you have triggers that can safely be deleted / disabled temporarily.

  • Do foreign keys reference your table? Can they be deleted? Temporarily deleted?

  • Depending on your autovacuum settings it may help to run VACUUM ANALYZE before the operation.

  • Some of the points listed in the related chapter of the manual Populating a Database may also be of use, depending on your setup.

  • If you delete large portions of the table and the rest fits into RAM, the fastest and easiest way may be this:

BEGIN; -- typically faster and safer wrapped in a single transaction

SET LOCAL temp_buffers="1000MB"; -- enough to hold the temp table

CREATE TEMP TABLE tmp AS
SELECT t.*
FROM   tbl t
LEFT   JOIN del_list d USING (id)
WHERE  d.id IS NULL;      -- copy surviving rows into temporary table
-- ORDER BY ?             -- optionally order favorably while being at it

TRUNCATE tbl;             -- empty table - truncate is very fast for big tables

INSERT INTO tbl
TABLE tmp;        -- insert back surviving rows.

COMMIT;

This way you don’t have to recreate views, foreign keys or other depending objects. And you get a pristine (sorted) table without bloat.

Read about the temp_buffers setting in the manual. This method is fast as long as the table fits into memory, or at least most of it. The transaction wrapper defends against losing data if your server crashes in the middle of this operation.

Run VACUUM ANALYZE afterwards. Or (typically not necessary after going the TRUNCATE route) VACUUM FULL ANALYZE to bring it to minimum size (takes exclusive lock). For big tables consider the alternatives CLUSTER / pg_repack or similar:

For small tables, a simple DELETE instead of TRUNCATE is often faster:

DELETE FROM tbl t
USING  del_list d
WHERE  t.id = d.id;

Read the Notes section for TRUNCATE in the manual. In particular (as Pedro also pointed out in his comment):

TRUNCATE cannot be used on a table that has foreign-key references
from other tables, unless all such tables are also truncated in the
same command. […]

And:

TRUNCATE will not fire any ON DELETE triggers that might exist for
the tables.

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. Any downsides of using data type “text” for storing strings?
  3. How to delete duplicate entries?
  4. Way to try multiple SELECTs till a result is available?
  5. Finding similar strings with PostgreSQL quickly
  6. Postgres not using index when index scan is much better option
  7. How do I speed up counting rows in a PostgreSQL table?
  8. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  9. Refactor a PL/pgSQL function to return the output of various SELECT queries
  10. Index for finding an element in a JSON array
  11. Query for array elements inside JSON type
  12. Calculating Cumulative Sum in PostgreSQL
  13. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  14. Is it possible to make a recursive SQL query?
  15. Split given string and prepare case statement
  16. Column ‘mary’ does not exist
  17. Get top row(s) with highest value, with ties
  18. PostgreSQL: how to convert from Unix epoch to date?
  19. SQL update fields of one table from fields of another one
  20. How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table
  21. Postgres – Function to return the intersection of 2 ARRAYs?
  22. How to store array or multiple values in one column
  23. convert Postgres geometry format to WKT
  24. COPY with dynamic file name
  25. Table or column name cannot start with numeric?
  26. Count cumulative total in Postgresql
  27. PostgreSQL column ‘foo’ does not exist
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment