Postgresql – Why does adding a simple CHECK constraint take so long on Postgres

check-constraintsperformancepostgresqlpostgresql-9.6

I'm using PostgreSQL 9.6.1 on Amazon RDS

I'm adding a CHECK constraint on an already indexed column named timestamp_ of a table called event_click that contains about 1 million rows.

ALTER TABLE event_click_2016_8
ADD CONSTRAINT click_2016_8_chk CHECK ( timestamp_ >= '2016-8-01' AND timestamp_ < '2016-9-01' );

The above takes 15 minutes to complete. Why is it so slow? What specifically is holding up this operation? There is nothing else running on the DB and no locks being held by other processes.

r3.large instance (2 vCPU, 15 GiB RAM)
maintenance_work_mem = 256147
work_mem = 1024000

Best Answer

when creating the constraint, check pg_locks for granted is false or pg_stat_activity for wait_event_type and wait_event where pid = <pid that adds the check> (note that these two columns are new in 9.6 - in earlier versions you see only the fact that it is waiting).

Most probably your ALTER TABLE was waiting for other process to release a lock before it could acquire exclusive one.

Related Question