How to delete lots of rows from PostgreSQL?

Question

We have a table with lots of rows (100m) hosted in AWS RDS.

How can we efficiently delete 50% of rows if the table has constant read, write and update queries?
How to delete 1% of data on daily basis?

Table structure:

created_at
user_id
other data

We tried deleting day by day, but then autovacuum started and queue depth grew.

user_205608 · Answer

I guess you want to delete lots of rows based on their created_at values. And, I guess your table has an id column which is the unique primary key.

What you do is delete a batch with a limited row count at a time, like this.

DELETE FROM yourtable
 WHERE id IN (
     SELECT id
       FROM yourtable
      WHERE created_at < '2020-11-01'
      LIMIT 1000
    )

(In my example we're deleting all the rows in the table created anytime before the end of October 2020.)

This deletes a batch of 1000 rows. You keep running this query until it deletes no rows.

This works because it doesn't take long to delete each batch, and each batch won't interfere too much with your production workload or your vacuum maintenance. It will be especially efficient if you have an index on the created_at column.

Delaying a few hundred milliseconds between batches is also wise, because you're even less likely to interfere with your production workflow.

Deleting a quarter-billion rows a thousand at a time will take a quarter-million batches. But that's OK, it's why programming was invented. This batch approach has worked very well for the places I have worked, for tables that weren't originally designed for easy cleanup.

Once you've deleted your huge backlog of old rows, then keeping up with it every day is much easier.

If you have to delete a huge number of rows every day, though, partitions are the way to go (as mentioned in the comments). But I suspect you'll need downtime to convert your table layout to use them. It's not a small job.

How to delete lots of rows from PostgreSQL?

1 Answers