4

Have such a problem - table with over 20mln rows.

When i add new column with default - postgresql lock table for over 40 minutes so my application stop working for this time.

So instead of

ALTER TABLE "test" ADD COLUMN "field" boolean DEFAULT True NOT NULL;

I do

ALTER TABLE "test" ADD COLUMN "field" boolean NULL;
ALTER TABLE "test" ALTER COLUMN "field" SET DEFAULT true;

after which every new row will be with true by default, so now i need to update 20mln current rows. I update them in batches:

WITH cte AS (
SELECT id as pk
FROM "test"
WHERE  "field" is null
LIMIT  10000
)
UPDATE "test" table_
SET "field" = true
FROM   cte
WHERE  table_.id = cte.pk

after which i do

ALTER TABLE "test" ALTER COLUMN "field" SET NOT NULL;

And everything is allwright but step when i update rows is so much slow. Can you give my some advice about improving speed of update?

Currently it update 10000 in about 2 minutes. I try decreasing size to 1000 - it was better (3.5 minutes for 10000) but it still to slow. I also try create index before update, but it doesnt give better results (as i understand it will give better result but when huge path of table will be updated).

7
  • What index do you build? What does the EXPLAIN (ANALYZE, BUFFERS) for the update look like after the index is built? What about if you set enable_seqscan=off after the index build and before the update? – jjanes Jul 4 '18 at 13:32
  • @jjanes i build index create index sometest_nll1 on table (test_fields) where test_fields is null; this explain without it explain.depesz.com/s/HOQ1 with index explain.depesz.com/s/au80 with index and set enable_seqscan=off explain.depesz.com/s/Vdlj – Vova Jul 5 '18 at 9:21
  • Do you have a lot of other indexes on the table? I thought it would be spending a lot of time locating the 10000 tuples, but it is the actual update once found that is slow. And reads 38 buffers per tuple updated. – jjanes Jul 5 '18 at 11:29
  • @jjanes i currently have 12 indexes. – Vova Jul 5 '18 at 11:59
  • If you need to keep all of those indexes, you might be better off just biting the bullet and scheduling the maintenance window in order to do the table-rewriting ALTER command. – jjanes Jul 5 '18 at 14:03
8

This problem is solved in PostgreSQL 11, which unfortunately isn't released yet at this moment. The release is planned for the third quarter of 2018.

There is a more detailed explanation in this blog post, but the short version is that in Version 11 the table won't have to be rewritten to add a default value to a column. So adding a column with a default should be pretty much instant.

1
  • 1
    thats great news, but i need fast (or not just slow) solution now – Vova Jul 3 '18 at 10:24
2

All you can do now is,

  1. Create a column that is NULLABLE
  2. ALTER the column to give it the DEFAULT
  3. UPDATE in batches to rewrite the rows (row-level locking)
  4. When the rows are updated, add the NOT NULL constraint (which should be pretty quick just a seq scan.

For more information, see this answer

2
  • 1
    thats exactly that i do for now (see question body), question was on how to speed it up. – Vova Aug 29 '18 at 10:44
  • 1
    You simply can't there is nothing else you can do. You can magically will features from future releases into older versions. Just mark Mad's answer as correct. – I Support The Boycott Aug 29 '18 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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