Обсуждение: Why is DEFAULT much faster than UPDATE?

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

On 8/10/22 16:02, André Hänsel wrote:
> 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?

 From here:

https://www.postgresql.org/docs/current/sql-altertable.html

"When a column is added with ADD COLUMN and a non-volatile DEFAULT is 
specified, the default is evaluated at the time of the statement and the 
result stored in the table's metadata. That value will be used for the 
column for all existing rows. If no DEFAULT is specified, NULL is used. 
In neither case is a rewrite of the table required."

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com