4

I've deleted ~65 million rows from a PostgreSQL table with ~75 million rows. As soon as the deletion query completed, the CPU plummeted to 100% for about five minutes.

The table from which rows were deleted has multiple indexes and was in heavy use during the deletion and after it. Unfortunately I don't have a way to reproduce this, since it happened on production environment.

Is it likely that the autovacuum kicked in and if so, could it drive a database with 32 CPU cores to 100% CPU usage? If so, is there a way to limit the intake of autovacuum so it doesn't degrade the database performance after massive delete queries?

I'm using PostgreSQL version 14.8.

Improve this question
13
  • 2
    "so it doesn't degrade the database performance after massive delete queries?" - How often do you plan on deleting 65 million rows at a time? Commented Aug 10, 2023 at 12:19
  • I will certainly make it so that it doesn't happen automatically. Mainly, I intend to understand the behavior, so I know what to avoid / expect. Commented Aug 10, 2023 at 12:28
  • 1
    @AlexGordon "...some sort throttling mechanics to block background jobs from driving the CPU to 100%." - Why wouldn't you want to use the entirety of the hardware resources you're paying for already? The way a database system is designed is for the purpose of having its own server (not to share its resources with other systems) because of the typical critical importance of performance at that layer. If you meant, you were hoping it balances how it utilizes those resources internally, then yes it does even under high load, but the total consumption from internal processes will still = 100%. Commented Aug 10, 2023 at 13:57
  • 2
    If this is a routine thing, I'd recommend partitioning the table. Detach the partition you no longer need, and then drop it when you are ready to drop it. Much, much faster and easier than trying to rip a bunch of rows out of a big table. Commented Aug 10, 2023 at 15:24
  • 1
    @AlexGordon Btw, Partitioning is one example of what I meant by "There's other ways to prioritize queries also.", I just ran out of comment room lol. But yea database and query implementation are important for helping things run more efficiently and smooth. If you ran into this same kind of problem again and didn't have Partitioning already setup, you would probably find it quicker and less overhead to insert the minority of rows you want to keep into a new table, drop the old table (which is rather immediate of a change), and then rename the new table to the old one. Commented Aug 10, 2023 at 18:35

1 Answer 1

Reset to default
5

This sounds like an issue I've run into before (or some variation of it), but without access to your servers or the ability to reproduce it, I can't be sure.

If you have a lot of queries which select the minimum/maximum value from an indexed column in that table, they will normally be satisfied instantly by consulting the end points of the index. But when many rows at the end have been deleted, it needs to walk back until it finds the extreme point which is still live. This can take a while when you have so many deleted rows that need to be walked past. Once the deleted tuples are "dead to all" (all transactions open at the time of the DELETE have gone away) you should be able to set hint bits on the tuples themselves and on the index entries ('microvacuum' or 'killed items' or 'index hint bits') which solve or at least ameliorate the problem, and then it should finally be completely resolved by vacuum which should remove not just the index entries, but also index pages which are full of nothing but dead entries. But until all long-live transaction/snapshots go away, none of these mechanism will work, so make sure you don't have open transactions.

In summary, autovacuum isn't causing the problem. Rather, it is trying to fix the problem and just hasn't finished yet, or has been frustrated by open snapshots.

In older version, the same thing can happen when doing cost estimates for mergejoins. The planning process did the same kind of end-point probing, and suffered the same problem. I think that that issue had been fixed by v14, though.

Improve this answer
3
  • 1
    It's interesting how this part of the PostgreSQL engine differs from other database systems, specifically the way snapshot versioning and vacuuming function. I only recently came across an article that discussed the history of their transaction handling and how it influenced the way the engine was designed which resulted in both positives and negatives on the whole. Commented Aug 10, 2023 at 18:41
  • @user253751 It was probably about about 3-4 months ago when I read about it, so I'll have to do some digging. But this is a pretty good RedGate article that discusses the uniqueness of vacuuming due to the implementation of MVCC in PostgreSQL, as opposed to other commonplace database systems. I find it extra interesting because SQL Server also offers a form of MVCC (though not on by default) but it must be implemented differently because vacuuming isn't necessary with it. Commented Aug 11, 2023 at 3:22
  • In fact, there were many concurrent requests (load test) at about 1500 rps that were doing a massive query on that massive table, that among other things sorted by a column in a multi-column index. I would imagine that these transactions must have been closed, at least with some client timeout, but something else might have been blocking the autovacuum. Or maybe it just took a long time to do it's thing, during which ongoing requests worked extra hard and drove the CPU to 100% which didn't help to move things along. No way to check now, so I'll accept this as the answer. Commented Aug 11, 2023 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.