Re: Why is DEFAULT much faster than UPDATE?

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(dot)klaver(at)aklaver(dot)com