How to update one column of table with 8 million rows of data in PostgreSQL

Question

I have a table with about 20 columns. It currently holds 8 million rows of data. I need to update one NULL column for all 8 million rows, it is a GUID column and a FK for another table column.

Currently, I am running simple update query in a loop. But it is taking way too long.

DO $$
BEGIN
   FOR counter IN 1..1000 LOOP

        UPDATE "Shops"
         SET "Country" = '410006e1-ca4e-4502-a9ec-e54d922d2c00'
         FROM (SELECT "Id"
                  FROM "Shops"
                   WHERE "Country" IS NULL LIMIT 1000) 
         AS "part"
         WHERE "Shops"."Id" = "part"."Id";

         COMMIT;
      RAISE NOTICE 'Counter: %', counter;

   END LOOP;
END; $$

Updating 8 million rows shouldn't take 6 hours. Most probably your UPDATE was waiting for a lock due to other concurrent modifications. Doing that in batches won't make those locks go away and is typically slower than one large transaction — user330315
– user330315, Commented Dec 28, 2020 at 10:17
Completely unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Dec 28, 2020 at 10:17
How slow is it? Do an explain (analyze, buffers) on your query to see why it is so slow. — jjanes
– jjanes, Commented Dec 28, 2020 at 19:49

Gordon Linoff · Accepted Answer · 2020-12-28 13:10:52Z

1

Updating 8 million rows should not take 6 hours. But it can take a long time. It is often faster to just repopulate the table:

create table temp_shops as
    select . . . ,
           '410006e1-ca4e-4502-a9ec-e54d922d2c00' as country,
            . . . 
    from shops
    where country is null;

Once you have tested this to be sure it does what you want, you can truncate the table and insert the values:

truncate table shops;  -- be careful.  This empties the table but you have a copy!

insert into shops ( . . . )
    select *
    from temp_shops;

answered Dec 28, 2020 at 13:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update one column of table with 8 million rows of data in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related