Sample Header Ad - 728x90

Most efficient way of bulk deleting rows from postgres

45 votes
2 answers
118613 views
I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions + deletions) into a table. There could be thousands, potentially millions of rows to delete. I have a file of primary keys, one per line. The two options I was thinking of were along the lines of the below, but I don't know/understand enough of the internals of PostgreSQL to make an informed decision which would be best. - Execute a DELETE query for each row in the file, with a simple WHERE on primary key (or group the deletes in batches of n using an IN() clause) - Import the primary keys into a temporary table using the COPY command and then deleting from the main table using a join Any suggestions will be much appreciated!
Asked by tarnfeld (639 rep)
Feb 16, 2013, 05:43 PM
Last activity: May 26, 2022, 08:16 PM