I need to delete about 400 million rows from a 1.3 billion row table based on an indexed date field.

The table is about 800 GB in size.

What is the most efficient way to delete the data without causing adverse effects? The table is under heavy use, meaning lots of inserts and updates (which don't affect the rows to be deleted).

There is a maintenance window, which would be fastest, but I may not be able to get a big enough maintenance window. On the flip side, I can take my time with the delete, so there is no rush.


3 Answers 3


Figured a better way to do this

  1. Insert into new_tbl select * from old_tbl where start_date < now -INTERVAL '2 years'
  2. During downtime window:

    insert into new_tbl select * from old_tbl where id not in (select id from new_tbl ) and id not in ( select id from old_tbl)
  3. Rename old_tbl to old_tbl_drop and new_tbl to old_tbl.

  4. Drop old_tbl_drop.
  • 1
    This would possibly introduce the old data again, because in Step 2. you are basically inserting all data not yet in your new_table from the old_table. You might want to add an AND clause omitting the data you no longer need.
    – John K. N.
    Mar 14, 2018 at 14:11

Community wiki answer:

Write yourself a batch script that does, say, 10.000 deletes and commits, next 10,000 and so on.

You should choose a number that can be deleted and committed quickly, so that you don't interfere too long with other processes, but still be worthwhile to keep things moving.

Assuming you are able to find the records to be deleted, in a timely manner.


If you need to delete 400 M from a table that is 1.2 B. That means you have to mark dead 400 M rows. That's substantially faster than writing anew 3 times that. So the most efficient way on PostgreSQL is a simple


BTW, deleting a billion that many rows really isn't a big deal.. Try it.


  SELECT x::bigint
  FROM generate_series(1,1200000000) AS gs(x);

WHERE x < 400000000;

See also

  • My concern is that it will create huge WALs and bloat. This database is replicated.
    – sharadov
    Mar 6, 2018 at 22:00
  • 3
    I disagree. Creating 9m rows will be faster than deleting 4m. You have no indexes on your 'try it' example. Mar 6, 2018 at 22:39
  • @sharadov it will create no bloat, other than what is already there because you are storing data you don't actually want. It will create as much WAL as it needs, but all safe methods will do that--most alternatives will need to create even more.
    – jjanes
    Mar 14, 2018 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.