Postgresql – Why is ALTER faster than UPDATE (for full table rewrites)

alter-tablepostgresqlpostgresql-12update

Let's create a garbage table..

CREATE TABLE foo
  AS SELECT x::text FROM generate_series(1,20e6) AS t(x); 
Time: 14077.654 ms (00:14.078)

Now, let's look at boring UPDATE,

UPDATE foo SET x = 'foo';
Time: 22239.050 ms (00:22.239)

Compared with ALTER,

ALTER TABLE foo
  ALTER COLUMN x
  SET DATA TYPE text
  USING 'foo';
Time: 9480.027 ms (00:09.480)

It's approximately twice as fast.

Best Answer

Both do rather different things under the hood.

ALTER TABLE takes an exclusive lock on the table and rewrites it. The manual:

The rewriting forms of ALTER TABLE are not MVCC-safe. After a table rewrite, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the rewrite occurred. See Section 13.5 for more details.

UPDATE only takes a write lock on affected rows (all in this case) and adds new row versions to the table, roughly doubling its physical size (unless it can reuse dead space). Concurrent readers can proceed happily.

Table-rewriting forms of ALTER TABLE do not write to the WAL. UPDATE, OTOH, also has to write to the WAL first. That's also why UNLOGGED tables can UPDATE in roughly half the time: no WAL by definition.

The fiddle demonstrates some effective differences:

db<>fiddle here

Related Question