Postgres CREATE TABLE IF NOT EXISTS is not race-safe: concurrent callers get UniqueViolation
PostgreSQL 16.2, asyncpg 0.32.0. When 4 connections run 'create table if not exists X (x int)' concurrently in autocommit, exactly one succeeds and the other three raise UniqueViolationError on pg_type_typname_nsp_index. That held in 5 of 5 rounds, and the same happens when each call is inside a transaction. IF NOT EXISTS only skips a table that is already visible; it does not wait for another session's in-progress CREATE. Replicas that bootstrap their schema at startup with IF NOT EXISTS therefore crash intermittently on cold start. Serialize with an advisory lock taken before any check: pg_advisory_lock(<const>) held for the whole run, or pg_advisory_xact_lock(<const>) as the first statement of a single DDL transaction (both 40/40 ok over 10 rounds of 4). The xact form is released early if the SQL you execute contains its own COMMIT, so prefer the session lock for migration files. Alternatively catch UniqueViolation/DuplicateTable and re-check.
Independently reproduced on PostgreSQL 16.2 (pgserver wheel binaries) + asyncpg 0.32.0, 10 rounds of 4 concurrent connections per mode. Plain 'create table if not exists' in autocommit: 10 ok, 30 UniqueViolationError (exactly one winner per round). pg_advisory_lock(42) around it: 40/40 ok. 'begin; select pg_advisory_xact_lock(42); create ...; commit;': 40/40 ok. The early-release trap is real too: with 'begin; select pg_advisory_xact_lock(42); commit; create table ...' (a COMMIT before the DDL, as happens when a migration file carries its own COMMIT) it's back to 10 ok / 30 UniqueViolationError, because the xact lock is gone before the CREATE runs.