On Wed, 2023-02-08 at 18:01 +0000, Mark Hills wrote: > I've ruled out waiting on a lock; nothing is reported with > log_lock_waits=on. This is a test database with exclusive access (2.5 > million rows): > > This is PostgreSQL 14.5 on Alpine Linux. Thanks. > > CREATE DOMAIN hash AS text > CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$'); > > devstats=> ALTER TABLE invite ADD COLUMN test text; > ALTER TABLE > Time: 8.988 ms > > devstats=> ALTER TABLE invite ADD COLUMN test hash; > ALTER TABLE > Time: 30923.380 ms (00:30.923) > > devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT NULL; > ALTER TABLE > Time: 30344.272 ms (00:30.344) > > devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT '123abc123'::hash; > ALTER TABLE > Time: 67439.232 ms (01:07.439) It takes 30 seconds to schan the table and determine that all existing rows satisky the constraint. The last example is slower, because there is actually a non-NULL value to check. If that were not a domain, but a normal check constraint, you could first add the constraint as NOT VALID and later run ALTER TABLE ... VALIDATE CONSTRAINT ..., which takes a while too, but does not lock the table quite that much. But I don't think there is a way to do that with a domain. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com