I have a PostgreSQL SQL UPDATE statement I need to run when I am migrating tables to a new structure.

The statement itself is simple and copying data from one table to another over FOREIGN KEY JOIN:

    SET denorm_value = b.value
    FROM block AS b
    WHERE block_id = b.id;

The problem is that UPDATE is touching billions of rows, making it impractical to be run in a single commit.

Does PostgreSQL itself offer any built-in functionality or tricks to break an UPDATE like this neatly to several commits? I can easily break down UPDATE to smaller chunks using id primary key.

  • Why is it impractical to use a single commit? PostgreSQL's implementation of MVCC makes it possible to do large UPDATEs in one commit - MySQL's does not. Maybe you are thinking of MySQL and transposing its behaviour onto PostgreSQL?
    – Vérace
    Commented May 27, 2022 at 19:16
  • 1
    No, this took 2 hours for 25 million row sample. I am not going to lock my database to a COMMIT for several days. Commented May 27, 2022 at 19:21
  • Was your database not operational for those 02:25? Could you not query other tables? Could you not query the one being UPDATEd? Write to other tables? I don't think your database is going to be "locked" for that time! What does the pg_lock table tell you?
    – Vérace
    Commented May 27, 2022 at 19:28
  • Thank you. I already made a Python script that finished the same operation in few minutes using 100,000 rows commit buffer, but would like to learn if there PostgreSQL native way to do this. Commented May 27, 2022 at 19:30
  • 1
    Please let me know your findings! PostgreSQL's MVCC architecture maintains a snapshot of the swap table before the UPDATE begins, so you should be able to work with the database, even during an UPDATE!
    – Vérace
    Commented May 27, 2022 at 19:56


Your Answer

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

Browse other questions tagged or ask your own question.