Adding a NOT NULL Column to a Large PostgreSQL Table: Constant Default, Backfill or NOT VALID

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: On PostgreSQL 11 and later, ADD COLUMN ... NOT NULL DEFAULT 'new' is a catalogue change that took 10 ms on 5,000,000 rows, but a volatile default such as gen_random_uuid() rewrote the whole table in 10.4 seconds. When the value has to... Weiterlesen: Adding a NOT NULL Column to a Large PostgreSQL Table: Constant Defaul…