Why is DEFAULT much faster than UPDATE?

From: André Hänsel <andre(at)webkr(dot)de>
To: <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Why is DEFAULT much faster than UPDATE?
Date: 2022-08-10 23:02:13
Message-ID: 0d4101d8ad0d$3af0cf70$b0d26e50$@webkr.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This question is out of curiosity, just to learn more about the internals of
PostgreSQL.

The goal was to add a not null bool column filled with "false", but with
"true" as the default for new rows.

The naïve approach would be:
ALTER TABLE foo ADD COLUMN slow bool NOT NULL DEFAULT true;
UPDATE foo SET slow = false;

This takes a certain, non-negligible amount of time.

This on the other hand achieves the same result and is almost instant:
ALTER TABLE foo ADD COLUMN fast bool NOT NULL DEFAULT false;
ALTER TABLE foo ALTER COLUMN fast SET DEFAULT true;

Where does the difference come from, how are those handled internally?

Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=56595e8ee397a5bc48b84277da3133
a9

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-08-10 23:11:11 Re: Why is DEFAULT much faster than UPDATE?
Previous Message Adrian Klaver 2022-08-10 19:18:25 Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"