How to delete millions of records from table

Question

I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success.

Query to delete all records for a specific camera:

DELETE FROM snapshots where camera_id = 1248

Another query to delete recodes between snapshot_id:

DELETE from snapshots where snapshot_id >= '1248_201511010000000' and <= '1248_20151130235959000'

Suggest me what will be the fastest way of deleting records from 700+ millions records.

Answer 1

Other than breaking the operation into chunks, I don't think there's a silver bullet method. This answer may come in handy

Answer 2

You can use something like pg_repack to keep the table clustered by camera_id, or snapshot_id whichever one is responsible is more problematic. This will make the marking of the rows as dead a much faster operation. It's probably better to use snapshot_id especially if that is chronological, and you're not inserting old snapshots.

On the other operation, you can use an index.

Answer 3

Sometimes it is faster to create a new table and insert only the records you would like to keep.