7

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
sharadov
  • 342
  • 1
  • 4
  • 14

3 Answers3

2

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.
sharadov
  • 342
  • 1
  • 4
  • 14
  • 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 '18 at 14:11
1

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
0

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

DELETE FROM foo WHERE ...

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

\timings

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

DELETE FROM foo
WHERE x < 400000000;

See also

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
  • My concern is that it will create huge WALs and bloat. This database is replicated. – sharadov Mar 06 '18 at 22:00
  • 3
    I disagree. Creating 9m rows will be faster than deleting 4m. You have no indexes on your 'try it' example. – Gerard H. Pille Mar 06 '18 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 '18 at 17:22