14

I have a table with approximately one million rows.

It is being used in production and I ran an UPDATE which covers ~95% of those rows.

After five hours I cancelled the request because it was taking so long.

The table has an auto-incrementing ID column, so I tried extending the WHERE condition of my query to include id BETWEEN 1 AND 500.

This update completed in approximately two seconds. I then manually iterated up through the id in batches of 500, e.g. BETWEEN 500 AND 1000, then BETWEEN 1000 AND 1500.

At this rate it will require 2000 batches of 500 to update the whole table.

Updating 2000 batches at 2 seconds each is just over an hour.

My questions are:

  1. What reasons are there for this difference?
  2. I don't care about transaction isolation, so is there a way to simulate this 'update in batches', without having to script the 2000 updates to run separately.
Improve this question
5
  • 1
    Did you look at disk swapping? AFAIK, Postgres will try to hold the changed data in memory... If you're trying to update so much data that you don't have enough memory for it, it'll swap to virtual memory and make everything run exceedingly slow.
    – Joishi Bodio
    Dec 8, 2015 at 23:00
  • Maybe the big update was just waiting for a lock held by another session. If you can reproduce it next time check out the queries at wiki.postgresql.org/wiki/Lock_Monitoring
    – Daniel Vérité
    Dec 9, 2015 at 17:39
  • Also you could try EXPLAIN of the query and compare with and without the BETWEEN clause. See how the execution plan differs and if the plan without BETWEEN is reasonable or not.
    – Daniel Vérité
    Dec 9, 2015 at 17:51
  • Without seeing the table definition (including triggers, constraints, and indexes) and the update command, and a EXPLAIN of that command, there is not enough information to say much of anything.
    – jjanes
    Dec 9, 2015 at 17:57
  • Oh Postgres....not to mention you could thread it for more gain! :)
    – rogerdpack
    Aug 10, 2020 at 17:03

1 Answer 1

Reset to default
10

I'll try my best to answer your question in brief, but since I'm not really aware of your level of comfort with PostgreSQL, and I don't have a lot of time to go into an in-depth explanation anyways, I'll keep the answers simple, and you can ask for clarification if you'd like more info.

1) Why is it faster in batches?

Due to the structure of PostgreSQL's write ahead log, the amount of shared buffer space in RAM, and the attempt to perform the entire UPDATE in a single transaction, my guess is that you simply don't have enough computing resources to efficiently handle the update to nearly a million records in a single transaction.

PostgreSQL has a well-built concurrency control system, essentially meaning that it has to keep the old copies of your pre-UPDATE rows available during your UPDATE operation. This is so that, in case another client tries to access these rows while you're updating, in case the update fails, or in case you cancel the update, you don't lose the old information.

If you perform a large enough UPDATE, PostgreSQL will load pages into memory and modify them, but will eventually run out of memory to work with, so it is forced to immediately copy these pages temporarily to disk if it wants to be able to load further pages and continue the transaction.

Rather than being able to amortize the disk writes over a period of time, you've just forced your database into a bottleneck.

2) Scripting the updates

You absolutely can script the updates, by creating a function in PL/pgSQL. There's a lot to learn about PL/pgSQL, including a lot I probably don't know, but generally speaking, you could do something like this

CREATE OR REPLACE FUNCTION mini_batch_update()
  RETURNS void AS
$BODY$
DECLARE

id_val integer;

BEGIN

  FOR id_val IN 0..2000
  LOOP

  **UPDATE GOES HERE**
  WHERE id > (500 * id_val) AND id <= 500 * (id_val + 1);

  END LOOP;


END
$BODY$
LANGUAGE plpgsql;

I didn't take a lot of time to make this batch function in tip-top form; what I mean is that I simply hard-coded several of the numerical values for simplicity's sake. In your case, you may want to get more detailed and include: 1) Something that checks for the maximum id value so that you set your bounds appropriately, and 2) even though I hard-coded batches of 500, you could easily make this a function input parameter.

Sorry I don't have time to test this or make sure it really works well. Good luck!

Improve this answer
3
  • Sorry for the negativity, but I think both #1 and #2 are completely wrong. #1 because the update mostly happens on disk, not in memory (in fact it's a strong point of PG compared to others dbs to be able to do large updates without needing much resources). #2 because a function runs inside a single transaction so any cost associated to a transaction would be the same anyway.
    – Daniel Vérité
    Dec 9, 2015 at 17:48
  • It's fine. I've been wrong before, and I may be wrong here. As for #1, I don't know what you mean that an update happens mostly on disk. If you update a given piece of data, since Postgres is not doing an upsert, it must seek and then read the relevant rows, and then of course modify the rows. To the best of my knowledge, this happens by doing a page read, and then editing the page in the shared buffer cache. After completion of the transaction, I don't think the page is immediately rewritten, but is taken care of by the background writer/checkpoint process.
    – Chris
    Dec 9, 2015 at 18:43
  • Continued... For these cases, I do think Postgres is efficient, and needs a linear increase in resources compared to the update size, but for large updates I'd think you'd hit a wall, because rather than allowing the bgwriter to amortize the writes, you are forced to interrupt your read process to immediately write. My unscientific and anecdotal experience tells me this, but I do admit I haven't tested this in a scientific manner. I may be wrong. As for #2, you may be totally right; my "solution" might not lead to the any benefit. I simply don't have time to verify through testing. :(
    – Chris
    Dec 9, 2015 at 18:46

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.