-1

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.

Improve this question
3
  • Are there any foreign keys pointing at the snapshots table? Can you post an EXPLAIN ANALYZE of a slow DELETE?
    – Josh Kupershmidt
    Commented Dec 21, 2015 at 14:59
  • 3
    "no success" is neither a valid error message nor a useful description of the problem. Start by providing relevant details for your performance question as advised here: dba.stackexchange.com/tags/postgresql-performance/info
    – Erwin Brandstetter
    Commented Dec 21, 2015 at 15:09
  • There is no foreign key on table. It take too much time to executing query and at the end it gave message connection timeout.
    – azharmalik3
    Commented Dec 22, 2015 at 8:52

3 Answers 3

Reset to default
0

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

Improve this answer
1
  • My answer you refer to is only partly applicable since, in this case, only a small percentage of the whole table is deleted.
    – Erwin Brandstetter
    Commented Dec 21, 2015 at 15:12
0

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.

Improve this answer
-1

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

Improve this answer
2
  • 3
    This is unlikely to be faster when keeping ~691 million of ~700 million rows.
    – David Spillett
    Commented Dec 21, 2015 at 14:50
  • It is true but maybe it will be easier to monitor the progress of insert
    – alonk
    Commented Dec 21, 2015 at 15:11

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.