2

I have a fairly niche use case of a (AWS Aurora) PostgreSQL database and we are running into performance issues that I have discovered a "clever hack" (not actually clever) to work around and we are wondering if there is a better way to do things.

We have a write master that ideally gets hourly updates, the updates are managed by a single batch job, nothing else writes to the DB. Fundamentally the DB is a single large (4 billion rows) table - let's call this table Target. On the scheduled update time some data is loaded into staging tables and then used to update, add or delete rows from the main table.

My problem comes with the deletes and PostgreSQL's lack of parallelism.

Both the Target and Staging tables have a column structure of a,b,c,d,e. The Target table has a unique index on (a,b,c,d). Looking at just the combination of (a,b) there are approx 17 million unique keys. In the case of the Staging_Delete table, the data in it will be a strict subset of data in the Target table. We, unfortunately, get cases where we have deletes of approx 17 million rows where we touch on almost every single possible (a,b) in the table. We know that there is no magic that can make this quick but we have been surprised at just how slow slow is due to the lack of parallelism.

If I do a SELECT on the Target table joined against Staging_Delete to select only those rows that matchup with Staging_Delete then Postgres will happily spin up multiple worker threads to perform that join.

However, if I do a DELETE USING with exactly the same conditions then I don't get the benefits of parallelism.

Obviously, I understand why in the general case parallelism and DELETE/UPDATE would be bad times but in my specific case, it would be perfectly safe to parallel read the data into the shared buffer before performing single threaded updating of the data.

To give an idea of the performance difference:

  1. DELETE 1 million rows takes approx 30 minutes.
  2. SELECT 1 million rows takes 6 minutes.

This leads us to the "clever hack" - Nothing else is using the database apart from the one batch job. If we first run the SELECT and then run the DELETE we get all the data pages loaded into the shared buffer with the benefits of parallelism and then running the DELETE on the same rows takes 1 minute for an overall runtime of 7 minutes. Vastly faster than the raw DELETE USING by itself.

Once again we understand that in the general case this wouldn't work - another query might come along and knock out our cached data but in our case there is only 1 query running, we are in complete control.

However, this feels very unsatisfying - are there any Postgres settings we can play with that give us the benefits of the parallel data load without having to hack around the default behaviour?

3
  • 2
    You can run several DELETE statements in parallel, for example for different values of a. Jan 9, 2023 at 23:42
  • I wish to perform the DELETE within a single transaction. Is there a way to run statements in parallel in Postgres controlled by a single COMMIT/ROLLBACK? Jan 10, 2023 at 8:24
  • You can use two-phase commit for that: first PREPARE TRANSACTION, and if all succeed, COMMIT. Jan 10, 2023 at 11:04

1 Answer 1

1

Increasing "effective_io_concurrency" might work to get parallel IO without full parallelization. It is only going to be helpful if the join uses a bitmap scan, which we don't know if it does or not based on this info. We would need to see an EXPLAIN or an EXPLAIN (ANALYZE, BUFFERS) to know that.

But in general, I see nothing wrong with your current approach. It isn't elegant, but it probably doesn't need to be.

But it does seem like you are deleting a pretty major chunk of the table. 17 million rows randomly scattered from among 4 billion is probably going to mean something like 1/3 of the pages are going to get hit (depending on the size of the rows of course). If that many pages can stay cached between the select and the delete, then why don't they just stay cached all the time? Maybe you could use pg_prewarm to get the whole table cached. Or the reverse, maybe you could use partitioning to pre-organize the data that will get deleted together so it is stored together--concentrated into fewer table pages.

3
  • Ah yes, one thing I missed in my description of how my approach works, I We can't read the entire 17 million rows in a single SELECT and get it all to stay in the shared_buffer (defeating the purpose of the approach!) - I have to break it down it a series of smaller SELECT-then-DELETES. We are currently trying to optimise this select size as there is a non-linear relationship between rows to select and number of workers assigned. For example 1 million rows gets me 3 workers but 10 million rows "only" gets me 5 making it more efficient to do a series of smaller selects-then-deletes. Jan 10, 2023 at 22:01
  • You can use ALTER TABLE to set the degree of parallelization which it should use, to override the default decision (which is based on log of the table size, I think). But for the batch issue, Yes, that is ugly. But I don't think there is much you can do about that, other than try my other idea to get it to use a bitmap scan (and set a high level of effective_io_concurrency, but you should probably do that part anyway).
    – jjanes
    Jan 12, 2023 at 18:05
  • Ah, I had forgot to set the parallel_workers value on the Staging table(s)! It is already set to a high value on the Target table but thanks to you I now realise it should be set on the Staging table as well. Thank you, this has been super helpful - it will probably allow us to use bigger staging tables as well which should be more efficient. Jan 13, 2023 at 19:09

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.