DEV Community: Son Tran

PostgreSQL serial vs identity: Which to Use, and How to Convert Old serial Columns

Son Tran — Mon, 05 Oct 2026 04:37:38 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Use GENERATED ALWAYS AS IDENTITY for new tables. serial is a shortcut for a separate sequence plus a default, so the column and its counter drift apart: an explicit id breaks the next insert, a widened key still stops at 2,147,483,647, and a copied table shares the counter. An existing serial key converts to identity in a few milliseconds, with no table rewrite.

For a new PostgreSQL table, use an identity column: id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY. serial still works, but it is not a type. It is a shortcut that creates a separate sequence and a column default, and the two can drift apart in ways an identity column does not allow.

Many production schemas still have serial keys, because the frameworks that created them used it, and two of the big ones still do. Below are the five ways serial misbehaves, each run on PostgreSQL 18.3 in a throwaway container, and the conversion, which is cheaper than most people expect.

What serial actually creates

The PostgreSQL manual says the serial types "are not true types, but merely a notational convenience". id serial becomes three things: a sequence users_id_seq AS integer, a column id integer NOT NULL DEFAULT nextval('users_id_seq'), and an OWNED BY link so the sequence is dropped with the column. Identity columns arrived in PostgreSQL 10, described in the release notes as "similar to SERIAL columns, but are SQL standard compliant".

The framework you use decides which one you have:

Framework	What it creates for an auto-increment key on PostgreSQL
Django 4.1 and later	Identity column, `GENERATED BY DEFAULT`. The 4.1 release notes: `AutoField`, `BigAutoField` and `SmallAutoField` "are now created as identity columns rather than serial columns with sequences"
Rails (Active Record)	`bigserial primary key`, the PostgreSQL adapter's default primary key type in the current source
Prisma	`SERIAL`, `SMALLSERIAL` or `BIGSERIAL` for `@default(autoincrement())`, in the Postgres renderer of its migration engine

\d users tells you which one a table has. A serial key shows nextval('users_id_seq'::regclass) as its default. An identity key shows generated always as identity or generated by default as identity.

Serial vs identity in Postgres: which should you use?

Identity, for every new table. The differences only show up when someone does something slightly unusual, which is why serial lasts so long in old schemas. Each row below was reproduced on 18.3:

What happens	`serial`	`GENERATED ALWAYS AS IDENTITY`
An `INSERT` supplies `id = 1`	Accepted. The next generated id is also 1 and fails with `duplicate key value violates unique constraint "users_pkey"`	Refused: `cannot insert a non-DEFAULT value into column "id"`, with the hint `Use OVERRIDING SYSTEM VALUE to override`
An app role has `INSERT` on the table only	`permission denied for sequence users_id_seq`	The insert works
`CREATE TABLE copy (LIKE users INCLUDING ALL)`	The copy's default calls `users_id_seq`, so both tables draw from one counter	The copy gets its own sequence
The key is widened to `bigint`	The sequence stays `AS integer` and fails at 2,147,483,647	The sequence becomes `bigint` with the column
Someone tries to remove the default	`DROP DEFAULT` works and the column stops numbering	Refused: `Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead`

GENERATED BY DEFAULT AS IDENTITY is the in-between form. It fixes the permissions, copy and widening rows, but accepts a supplied id the way serial does, and the same duplicate key value error came back in the same test. Use ALWAYS unless a loader must write ids, and when one must, INSERT ... OVERRIDING SYSTEM VALUE says so in the statement. pg_dump handles it already: its --inserts output writes INSERT INTO ... OVERRIDING SYSTEM VALUE VALUES (...), and its default COPY output loads explicit ids into an ALWAYS column without complaint.

The serial overflow that survives the bigint migration

The widening row is the one that costs an outage. A table outgrows integer, someone runs the usual schema migration, ALTER TABLE users ALTER COLUMN id TYPE bigint, and the column now holds values up to 9,223,372,036,854,775,807. The sequence does not. serial created it AS integer, the ALTER did not touch it, and on 18.3 the next insert after 2,147,483,647 failed with:

ERROR:  nextval: reached maximum value of sequence "users_id_seq" (2147483647)

The fix is one more statement, ALTER SEQUENCE users_id_seq AS bigint, and it is easy to miss because \d users shows bigint and looks done. An identity column needs nothing: after the same ALTER, pg_sequences showed its sequence as bigint with a maximum of 9,223,372,036,854,775,807, and the insert at 2,147,483,648 succeeded.

The column change itself is the expensive part either way. integer to bigint rewrites the table: 761 ms for 1,000,000 rows here, under an ACCESS EXCLUSIVE lock, and every foreign key column pointing at it needs the same change before ids pass 2,147,483,647. If a view reads the key, PostgreSQL refuses the ALTER outright until the view is dropped and created again.

A foreign key declared as serial fills itself in

The same shortcut causes a quieter bug in child tables. Copy the parent's column type into a foreign key and you get this:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id serial REFERENCES users (id),
    note text
);

user_id now has its own sequence and a nextval default. An INSERT INTO orders (note) VALUES ('forgot user_id') should fail on the NOT NULL that serial implies. Instead it succeeded and returned user_id = 1, and the foreign key passed because user 1 exists. Each later insert that forgets the column attaches the order to user 2, then 3, until it reaches an id with no user and the foreign key finally fails. A foreign key column takes the plain type under the parent's key, integer for serial and bigint for bigserial, with NOT NULL when the relationship is mandatory. Never the serial itself.

How do you convert a serial column to identity?

Without rewriting the table. The conversion swaps the default for an identity property and carries the old counter over, so it touches the catalogue, not the rows. Here on an invoices table created with id serial:

BEGIN;
ALTER TABLE invoices ALTER COLUMN id DROP DEFAULT;
ALTER SEQUENCE invoices_id_seq RENAME TO invoices_id_seq_old;
ALTER TABLE invoices ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval('invoices_id_seq', last_value, is_called) FROM invoices_id_seq_old;
DROP SEQUENCE invoices_id_seq_old;
COMMIT;

On a 1,000,000-row table whose top ten rows had been deleted, the transaction took about 3 ms, the table's data file was the same one before and after, and the next insert got id = 1000001. Copying last_value rather than max(id) is deliberate: max(id) would hand out the deleted ids 999,991 to 1,000,000 again, and anything that still refers to them, a log line, an export, another system, would now point at a new row. The setval names the new sequence directly, because pg_get_serial_sequence can still return the renamed old one, which stays owned by the column until it is dropped. The new sequence takes the old name, invoices_id_seq.

Four things to check first:

The lock. The transaction holds ACCESS EXCLUSIVE on the table. It is short, but it waits behind every running query, and everything else waits behind it. Set lock_timeout so a long report cannot turn a 3 ms change into a queue.
A shared sequence. If a LIKE ... INCLUDING ALL copy or another table also calls the sequence, DROP SEQUENCE fails with cannot drop sequence invoices_id_seq_old because other objects depend on it, its DETAIL names every default using it, and the transaction rolls back. That is how you find the copies.
Grants on the sequence. They are dropped with the old sequence. Inserts no longer need them, but a role that calls currval('invoices_id_seq') gets permission denied for sequence invoices_id_seq until you grant it again. INSERT ... RETURNING id avoids currval altogether.
Writers that supply ids. Seed scripts and fixtures that insert explicit ids fail against ALWAYS. Add OVERRIDING SYSTEM VALUE to them, or convert to BY DEFAULT instead.

Converting does not change the column's type, so an integer key converted to identity still overflows at 2,147,483,647. If the table is heading there, widen it separately, and plan for the rewrite.

How Schemity handles serial and identity keys

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you connect to PostgreSQL, each column's default is drawn on the canvas, so every serial column shows nextval beside it and an identity column shows nothing. That makes the legacy keys easy to see, and the bug above easier still: a nextval on a foreign key column is a serial that should have been an integer.

When you design the next one, a table drawn with a single integer primary key and no default is created as an identity column. The planned SQL reads "id" INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, or BIGINT with a bigint key. Drawing a relationship never copies the parent key's default into the new foreign key column, and a parent typed SERIAL or BIGSERIAL in a design gives the column INTEGER or BIGINT, so the diagram does not reproduce the self-filling foreign key.

Widening a serial key in the diagram from INTEGER to BIGINT plans the ALTER COLUMN ... TYPE BIGINT and, after it, ALTER SEQUENCE users_id_seq AS BIGINT, so the counter is widened in the same migration as the column. Impact analysis reports the change before anything runs: the table is rewritten, reads and writes are held back while it happens, and the tables whose foreign keys reference the key are listed, since their columns need widening too. Until they are, lint reports each one as "Foreign key type does not match the column it references".

Which to use, in one list

New table: bigint GENERATED ALWAYS AS IDENTITY.
A loader must write ids: GENERATED BY DEFAULT AS IDENTITY, or keep ALWAYS and use OVERRIDING SYSTEM VALUE in the loader.
Existing serial key: convert it in one short transaction, after checking for shared sequences and scripts that insert ids.
Widening a serial key to bigint: widen the sequence too, or convert to identity first.
Foreign key column: the plain integer type under the parent key, NOT NULL when the relationship is mandatory, never serial.

Whether the key should be a number at all is the question in UUID vs bigint primary keys in Postgres. What a new NOT NULL column with a volatile default costs on a large table is in how to add a NOT NULL column to a large table.

Database MCP Server: Should an AI Agent Run SQL or Only Read the Schema?

Son Tran — Thu, 01 Oct 2026 01:25:24 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: For schema work an AI agent needs the structure, not the rows, so give it a database MCP server that exposes the schema and no SQL tool. When a task does need rows, guard it with a database role that owns nothing. A READ ONLY transaction alone is not a guard: the reference Postgres MCP server relied on one, and a single COMMIT; DROP TABLE sent as one query ended it.

An AI agent doing schema work needs the structure of your database, not its rows, so the safest database MCP server for it is one that exposes the schema and has no SQL tool at all. When a task really does need data, the guard has to be the database's own permissions: a login role that owns nothing. A read-only transaction around the agent's SQL looks like the same guard, and it is not.

The difference is easy to show. The reference Postgres MCP server, which the Model Context Protocol project published as an example, runs every query inside BEGIN TRANSACTION READ ONLY. Every test below was run against PostgreSQL 18.3 in a throwaway container, with that tool's handler reproduced on the same pg driver (version 8.23.0) and a customers table holding emails and card digits.

What can a database MCP server let an agent do?

The options differ in what the agent sees and in what stops a write:

What the MCP server offers	What the agent can read	What stops a write
A SQL tool inside a `READ ONLY` transaction, as your usual login	Every row	The transaction flag, which the agent's own SQL can end
A SQL tool, logged in as a role with `SELECT` only	Every row it has `SELECT` on	The role's privileges
A SQL tool, logged in as a role with no table grants	Structure only, from `pg_catalog`	The role's privileges
No SQL tool, only schema tools	Structure, plus whatever counts the tools compute	There is no statement to write with

The first row is the one most "read-only" database MCP servers start from, and it is the only one where the agent can undo the guard.

Is a read-only Postgres MCP server safe?

Not when the transaction is the only guard. The archived reference server's source handles a query in three calls: client.query("BEGIN TRANSACTION READ ONLY"), then client.query(sql) with the agent's text, then ROLLBACK. With no parameters, node-postgres sends that text over the simple query protocol, which accepts several statements in one string. Sent through the same three calls:

SELECT email, card_last4 FROM customers returned every customer's email and card digits.
INSERT INTO customers ... failed with cannot execute INSERT in a read-only transaction, as intended.
COMMIT; DROP TABLE customers returned COMMIT and DROP. The COMMIT ended the read-only transaction, the DROP ran in autocommit, and the next query failed with relation "customers" does not exist.

This is not a new finding: Datadog Security Labs described the same escape in "MCP vulnerability case study: SQL injection in the Postgres MCP server" on August 21, 2025. The repository was archived on May 29, 2025, and its README now says "No security updates or bug fixes will be provided" for these servers. Other Postgres MCP servers are separate code, so read how yours runs the agent's SQL: a prepared statement accepts only one statement, while a raw query string passed through as it arrives accepts several.

Even without the escape, the first result is the larger problem for most teams. Whatever a tool returns becomes part of the agent's context, so with a cloud-hosted model the emails and card digits are now with the model provider too.

Why a database role is the guard that holds

The database checks privileges on every statement, whatever transaction it runs in, so writes to your tables are refused however the agent's SQL is shaped. The same three calls, logged in as a role with pg_read_all_data and a default timeout:

CREATE ROLE agent_ro LOGIN PASSWORD 'change-me';
GRANT pg_read_all_data TO agent_ro;
ALTER ROLE agent_ro SET statement_timeout = '5s';

COMMIT; DROP TABLE customers failed with must be owner of table customers, and the table stayed.
SELECT pg_sleep(10) failed after 5 seconds with canceling statement due to statement timeout, but only because the SQL did not change it. SET LOCAL statement_timeout = 0; SELECT pg_sleep(3) ran to the end: a role's timeout is a default, and any role can override it. A hard limit has to sit where the agent's SQL cannot reach, in the MCP server or a connection pooler.
SELECT email, card_last4 FROM customers still returned the rows, because reading them is exactly what this role is for.
COMMIT; CREATE TEMP TABLE t (x int) succeeded, because PUBLIC may create temporary tables by default. That touches none of your data; revoke TEMP on the database if it matters.

So a SELECT-only role fixes the write escape but not the data exposure. For an agent that works on the schema rather than the data, go one step further: a role with CONNECT on the database, USAGE on the schema and no table grants reads the entire structure from pg_catalog and is refused every row. Only from pg_catalog, though: information_schema hides every table the role has no privilege on, so a SQL tool that lists tables through it shows that role an empty database. That setup, and what the catalogue still reveals, is in a PostgreSQL role that reads the schema but not the data.

What does an agent need to understand your schema?

Most of what an agent is asked to do with a database is structural: explain how tables relate, find where a module's tables are, propose a new table or a column change, or check what a migration will break. None of that needs a row. It needs table and column names, types, nullability, keys, check constraints, indexes and comments, and for judging a change, row estimates and table sizes, which the catalogue holds too.

That is what to understand your schema means for an agent, and it is also the least sensitive part of the database to send to a model. A diagram of customers with an email column tells the model the column exists. A SELECT tells it every address.

How Schemity's database MCP server works

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. It is also a local database MCP server with 16 tools, and none of them runs SQL the agent writes: analyze_migration_file accepts a migration file to analyse, and it is parsed, never executed. The agent reads the schema Schemity already holds and never receives the database credentials. Connecting Claude Code takes one command, and other MCP hosts take a config entry.

What the tools give an agent:

get_schema returns the tables and views with their columns, keys, constraints, indexes and relations. get_dependencies lists the relations into and out of one table, get_context_views the domains the tables are grouped into, and get_data_dictionary the same schema as a document with its descriptions.
propose_changes edits the diagram. The edits land as unsaved changes you review, and nothing is written to the database.
analyze_impact reports what the pending migration would cost, and returns the SQL only when asked, with a parameter description telling the agent that Schemity never runs it.
count_rows returns one number, such as how many rows of a column are NULL before it becomes NOT NULL. It takes a probe object rather than SQL and refuses the one kind of probe built from free text, so its query is assembled from quoted table and column names with no text from the agent in it. That, not the transaction, is what keeps agent SQL out; the read-only transaction and the 60-second timeout it also runs under on PostgreSQL are set by Schemity, where the agent cannot change them. It is a full table scan, so ask your agent to run it only when you want the number.

Every transport listens on 127.0.0.1 only and needs a token, and the HTTP one also checks the request's origin. Applying a migration stays a human action in the app. Connect the diagram with the no-grants role from the post above and count_rows is refused too, which leaves the agent with nothing but structure.

Which database MCP setup to choose

Designing, reviewing or documenting a schema: a schema-only MCP server, or a SQL one logged in as a role with no table grants.
Answering questions about the data: a SQL server logged in as a role with SELECT on the tables it needs and owning nothing, a time limit the agent cannot unset, and only where sending those rows to your model provider is acceptable.
Never: your application's login, an owner role, or a READ ONLY transaction as the only protection.

For what the agent should be allowed to change once it has read the schema, see should AI agents write database migrations. Using the agent to sort a large legacy schema into domains is covered in grouping a legacy database by domain.

Changing a Column Type in SQLite: The Table Rebuild and What It Deletes

Son Tran — Thu, 01 Oct 2026 01:24:38 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: SQLite cannot change a column's type in place, so you copy the table into a new one with the right type, drop the old one and rename. Done carelessly, that rebuild deletes child rows through ON DELETE CASCADE when PRAGMA foreign_keys = OFF is run inside the transaction, drops every trigger and index on the table, and leaves values that do not convert stored as text in the new numeric column.

SQLite cannot change a column's type with ALTER TABLE. The only way is a table rebuild: create a new table with the column typed the way you want, copy the rows across, drop the old table and rename the new one into its place. It takes four statements, and done in the obvious order it can delete rows from other tables, drop triggers and indexes, and leave some values unconverted without an error.

Every statement below was run with the sqlite3 shell that ships with macOS, Apple's own build of SQLite, which reports version 3.54.0 although the newest release on sqlite.org is 3.53.4, against an orders table whose total was created as TEXT and needs to become REAL, with an order_items child table, an index, a trigger and a view on it:

PRAGMA foreign_keys = ON;
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total TEXT NOT NULL,
    updated_at TEXT
);
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    sku TEXT NOT NULL
);
CREATE INDEX orders_updated_at_idx ON orders (updated_at);
CREATE TRIGGER orders_touch AFTER UPDATE OF total ON orders
BEGIN
    UPDATE orders SET updated_at = datetime('now') WHERE id = NEW.id;
END;
CREATE VIEW big_orders AS SELECT id, total FROM orders WHERE total > 100;
INSERT INTO orders (id, total) VALUES (1, '150.00'), (2, '19.90'), (3, 'n/a');
INSERT INTO order_items (order_id, sku) VALUES (1, 'A'), (1, 'B'), (2, 'C');

Which ALTER TABLE changes SQLite can do in place

Before rebuilding, check whether you need to. The SQLite ALTER TABLE documentation lists what it does directly:

Change	In place?	Since
Rename a table or a column	Yes	3.25.0 for columns
Add a column	Yes, with restrictions on defaults and constraints	3.2.0
Drop a column	Yes, unless it is in a primary key, unique constraint, index or foreign key, or used by a check, generated column, view or trigger	3.35.0
Set or drop `NOT NULL`	Yes, `ALTER TABLE ... ALTER COLUMN ... SET NOT NULL`	3.53.0
Add a `CHECK` constraint, or drop a named one	Yes, `ALTER TABLE ... ADD CONSTRAINT ... CHECK (...)`	3.53.0
Change a column's type	No, rebuild the table
Add or change a foreign key, primary key or unique constraint	No, rebuild the table

The 3.53.0 release notes describe the newest addition as permitting "adding and removing NOT NULL and CHECK constraints". Both check the existing rows: SET NOT NULL on a column with a NULL failed with constraint failed, and so did adding CHECK (total > 0) to a table with a negative total. Anything older than 3.53.0, including the SQLite your driver may bundle, still needs a rebuild for those two.

Two things a SQLite table rebuild deletes, and one it gets wrong

Here is the rebuild as it is usually written, run in the same session as the setup so foreign keys are on, with the switch to turn them off placed inside the transaction:

BEGIN;
PRAGMA foreign_keys = OFF;
CREATE TABLE orders__new (id INTEGER PRIMARY KEY, total REAL NOT NULL, updated_at TEXT);
INSERT INTO orders__new SELECT id, total, updated_at FROM orders;
DROP TABLE orders;
ALTER TABLE orders__new RENAME TO orders;
COMMIT;

It succeeds, and afterwards SELECT count(*) FROM order_items returns 0. All three order items are gone.

Child rows, through the cascade. The foreign key documentation explains both halves. With enforcement on, DROP TABLE "performs an implicit DELETE to remove all rows from the table before dropping it", which "may invoke foreign key actions", and ON DELETE CASCADE is one. The PRAGMA that was meant to prevent it did nothing, because changing foreign key enforcement inside a transaction "does not return an error; it simply has no effect". Running PRAGMA foreign_keys; inside the transaction still printed 1. Without a cascade the same drop fails with FOREIGN KEY constraint failed instead, which is the safer way to find out.

Every trigger and index on the table. After the rebuild, sqlite_schema lists the view and both tables, and no longer lists orders_touch or orders_updated_at_idx. DROP TABLE removes what is attached to the table, and nothing in the rebuild puts them back. The missing index shows up as a slow query. The missing trigger shows up as an updated_at that stops changing.

Values that do not convert. The copy did not fail on 'n/a'. REAL in SQLite is a type affinity, not a check, and the datatype documentation says a text value that is not a well-formed number "is stored as TEXT". The new column holds 150.0 and 19.9 as real numbers and 'n/a' as text. Before the rebuild, big_orders compared text with text and returned all three orders, 19.90 included. After it, 19.90 correctly drops out, but order 3 stays in the view as a big order, because SQLite sorts any text above any number. A table declared STRICT would have refused the value with cannot store TEXT value in REAL column.

How do you change a column type in SQLite?

Follow the 12-step procedure from the ALTER TABLE documentation. For this table it is:

PRAGMA foreign_keys = OFF;
BEGIN;
SELECT type, name, sql FROM sqlite_schema WHERE tbl_name = 'orders' AND type <> 'table';
CREATE TABLE orders__new (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    updated_at TEXT
);
INSERT INTO orders__new (id, total, updated_at) SELECT id, total, updated_at FROM orders;
SELECT id, total FROM orders__new WHERE typeof(total) <> 'real';
-- stop here if that returned rows: ROLLBACK, fix them, start again
DROP TABLE orders;
ALTER TABLE orders__new RENAME TO orders;
CREATE INDEX orders_updated_at_idx ON orders (updated_at);
CREATE TRIGGER orders_touch AFTER UPDATE OF total ON orders
BEGIN
    UPDATE orders SET updated_at = datetime('now') WHERE id = NEW.id;
END;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

What each addition does:

PRAGMA foreign_keys = OFF comes before BEGIN, so it takes effect and the drop cascades into nothing. All three order items survived.
The first SELECT prints the CREATE INDEX and CREATE TRIGGER statements to run again after the rename.
The typeof check lists rows whose value did not convert. Here it returned 3|n/a. A script pasted in one go does not stop there, so run the steps one at a time, and if the check returns anything, ROLLBACK, fix those rows, and start again.
PRAGMA foreign_key_check lists rows that break a foreign key. With enforcement off, nothing else would.

The view needed no attention. It names orders, and after the rename orders exists again. A view that selects a column you renamed or dropped does need recreating, as step 9 of the documented procedure says.

Rebuild step	Written the usual way	Written the safe way
`PRAGMA foreign_keys = OFF`	Inside the transaction, ignored	Before `BEGIN`, applied
Rows in `order_items` afterwards	0 of 3	3 of 3
Index and trigger on `orders`	Gone	Recreated from `sqlite_schema`
`'n/a'` in the `REAL` column	Copied as text, no error	Listed by the `typeof` check, so you can stop before the drop

How Schemity plans a SQLite rebuild

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. On a SQLite connection it alters directly for adding, renaming and dropping columns, and plans a rebuild for the rest: a type, nullability or default change, and adding or dropping a primary key, unique, check or foreign key constraint. That includes the NOT NULL and CHECK changes SQLite 3.53 can make in place.

Its rebuild follows the documented order. PRAGMA foreign_keys = OFF runs on the connection before the transaction starts, PRAGMA foreign_key_check runs before the commit and rolls the whole migration back if any row breaks a key, and foreign keys are switched back on whether the migration succeeded or not. The rebuilt table gets its indexes and its triggers back, keeps AUTOINCREMENT only if the table was declared with it, and computes generated columns again rather than copying them, since SQLite refuses an insert into one. To see the impact of every change before it runs, impact analysis reports this one as "Changes the type of orders.total, values may not survive the cast", which is the 'n/a' case, and "Rewrites orders to change total", with a row count when the database has been analysed with ANALYZE. It also lists orders_touch as a trigger that depends on orders, and big_orders as a view that mentions it.

The triggers come back from their own SQL, as read from sqlite_schema, the same way the script above recreates orders_touch. SQLite does not check a trigger's body when the trigger is created, so a trigger that names a column the same change renames or drops would be created and then fail when it fires. Schemity refuses that save instead, naming the table and the column, so the trigger can be changed first. The migration SQL diff shows every statement of the planned rebuild, the recreated trigger included, before you apply it.

What to check before any SQLite rebuild

Your SQLite version: on 3.53.0 or later, NOT NULL and CHECK changes no longer need a rebuild.
PRAGMA foreign_keys = OFF before BEGIN, never after it.
The table's triggers and indexes, saved from sqlite_schema and recreated after the rename.
A typeof check on the new table before the old one is dropped.
PRAGMA foreign_key_check before COMMIT.

Schema changes on PostgreSQL have their own traps, and the ones that lock or rewrite a large table are in adding a NOT NULL column to a large Postgres table. What ON DELETE CASCADE reaches, and why a diagram should show it, is in ON DELETE CASCADE is invisible in your ERD. For a migration written by an ORM or an AI agent rather than by hand, see should AI agents write database migrations.

ON DELETE SET NULL vs CASCADE vs RESTRICT in PostgreSQL: Which to Use

Son Tran — Thu, 01 Oct 2026 01:24:01 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Pick the action by what the child row means once its parent is gone: CASCADE when it means nothing, SET NULL when it stays valid with the link removed, and RESTRICT or NO ACTION when it is a record that must not change behind anyone's back. SET NULL is the one PostgreSQL accepts and then refuses at delete time, if the column is NOT NULL, a check needs the value, or the key is composite and nulls a tenant column along with it.

A foreign key's ON DELETE action should follow from one question: what does the child row mean once its parent is gone? If it means nothing, CASCADE deletes it with the parent. If it is still a valid row with the link removed, SET NULL keeps it. If it is a record someone relies on, RESTRICT refuses the delete and makes you decide.

SET NULL needs the most care, because PostgreSQL accepts it in cases where it can never succeed and only says so when someone deletes a parent row in production. Every statement below was run on PostgreSQL 18.3 in a throwaway container, with a support desk schema: agents as the parent and tickets as the child, and for the timing tests 10,000 users and 2,000,000 tickets.

ON DELETE SET NULL vs CASCADE: which should you use?

Decide per foreign key, from the child's point of view:

After the parent row is deleted, the child row is	Action	Example
Meaningless	`CASCADE`	Order lines of an order, sessions of a user
Still valid, with the link removed	`SET NULL`	A ticket whose assignee left, a post whose editor's account was deleted
A record that must not change silently	`RESTRICT` or `NO ACTION`	An invoice for a customer, a payment for an order
Still valid, pointing at a known fallback row	`SET DEFAULT`	Tickets moved to an "unassigned" queue row

SET NULL fits when NULL has an honest meaning in that column, such as "nobody is assigned". It is the wrong choice when NULL would erase something the business needs, such as who handled a ticket that later goes to an audit. There, keep the parent row, mark it inactive, and let RESTRICT stop the delete.

RESTRICT and NO ACTION both refuse a delete that would leave orphans. NO ACTION is the default, and the PostgreSQL documentation on constraints gives the one difference: RESTRICT "does not allow the check to be deferred until later in the transaction". On a key declared DEFERRABLE INITIALLY DEFERRED, or deferred with SET CONSTRAINTS ALL DEFERRED, NO ACTION lets the same transaction insert a replacement parent or delete the dangling children before the check runs at commit. DEFERRABLE alone is not enough: the check is still immediate, and the delete fails on the spot.

Three ways SET NULL is accepted and then fails

PostgreSQL checks that the referenced columns exist and are unique when you create the key. It does not check that the child column can hold NULL. The failure arrives with the first delete of a parent that has children.

The column is NOT NULL. This key is created without complaint:

CREATE TABLE tickets (
    id bigint PRIMARY KEY,
    assignee_id bigint NOT NULL REFERENCES agents (id) ON DELETE SET NULL
);

Deleting an agent who has a ticket then fails with ERROR: null value in column "assignee_id" of relation "tickets" violates not-null constraint, and the agent stays. The other engines refuse the definition instead, with one catch on MySQL. MySQL 8.4 parses an inline REFERENCES like the one above and ignores it, so that CREATE TABLE succeeds with no foreign key at all. Written as a table-level FOREIGN KEY (assignee_id) REFERENCES agents (id) ON DELETE SET NULL, it is rejected with ERROR 1830 (HY000): Column 'assignee_id' cannot be NOT NULL: needed in a foreign key constraint 'tickets_ibfk_1' SET NULL. SQL Server 2022 rejects it with Msg 1761: Cannot create the foreign key ... with the SET NULL referential action, because one or more referencing columns are not nullable.

A check constraint needs the value. A ticket in the assigned status must have an assignee:

CREATE TABLE tickets (
    id bigint PRIMARY KEY,
    status text NOT NULL,
    assignee_id bigint REFERENCES agents (id) ON DELETE SET NULL,
    CHECK (status <> 'assigned' OR assignee_id IS NOT NULL)
);

The column is nullable, so the key looks fine, but deleting the agent of an assigned ticket fails with new row for relation "tickets" violates check constraint "tickets_check". SET NULL writes a new version of the child row, and every check on that row runs again. The fix is a decision rather than a syntax change: either the check allows it, or the application reassigns the tickets before the agent goes.

The key is composite. In a multi-tenant schema the key usually includes the tenant, so a ticket cannot point at another tenant's agent:

CREATE TABLE tickets (
    tenant_id bigint NOT NULL,
    id bigint,
    assignee_id bigint,
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, assignee_id) REFERENCES agents (tenant_id, id) ON DELETE SET NULL
);

Plain SET NULL sets every column of the key, so the delete fails with null value in column "tenant_id" of relation "tickets" violates not-null constraint. Since PostgreSQL 15, whose release notes describe the change as letting ON DELETE SET actions "affect only specified columns", you can name the column:

FOREIGN KEY (tenant_id, assignee_id) REFERENCES agents (tenant_id, id) ON DELETE SET NULL (assignee_id)

With that key the same delete succeeds, and the ticket keeps tenant_id = 7 with assignee_id empty.

What SET NULL does besides writing NULL

SET NULL is an UPDATE of every child row, not a quiet unlink. A BEFORE UPDATE trigger on tickets that stamps updated_at fired when the agent was deleted and moved the ticket's updated_at to the time of the delete, so a "recently changed" list will show every ticket the agent had. Each updated row is a new row version, with the same vacuum work as any other update.

SET DEFAULT has its own trap: the default has to exist in the parent. With assignee_id bigint DEFAULT 0 and no agent 0, deleting an agent failed with insert or update on table "tickets" violates foreign key constraint, because the fallback row the design assumed was never inserted.

Does ON DELETE need an index on the child column?

For correctness, no. For speed, yes, and it matters for SET NULL, CASCADE and RESTRICT alike, because each has to find the children of the deleted parent. PostgreSQL does not create that index for you. With 2,000,000 tickets across 10,000 users:

Delete	No index on `assignee_id`	With an index
One user	71 ms	1.8 ms
100 users in one statement	7,489 ms	98 ms

Without the index, each deleted user costs one full scan of tickets, 158 MB with its primary key, so a cleanup job that deletes 1,000 users scans the table 1,000 times however few tickets they had.

Changing the action later is not an ALTER. ALTER TABLE ... ALTER CONSTRAINT does not accept ON DELETE, so the key is dropped and added again, and adding it checks every existing row. On the 2,000,000-row table that re-check took 300 ms while holding back writes to both tickets and users. On a large table, drop the old key and add the new one NOT VALID in the same transaction, so there is no moment without a key (0.9 ms), then run VALIDATE CONSTRAINT separately (243 ms here), which lets writes carry on.

How Schemity handles ON DELETE SET NULL

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you design the next one, the delete behaviour is part of the relationship: ON DELETE and ON UPDATE are set in the relation dialog, and choosing SET NULL there makes the foreign key's columns nullable in the same edit and draws the relationship as optional. A primary key column cannot become nullable, so it is left alone.

The lint rules cover what the dialog cannot fix on its own:

SET NULL on a column that is NOT NULL. Reported for a key read from a database or imported from SQL, on every engine: PostgreSQL accepts the key and fails the delete, MySQL and SQL Server refuse the key.
Foreign key has no index on its columns. Reported for tickets.assignee_id above, since every parent delete scans the child without it.

Changing the action on a connected diagram plans the drop and the re-add, and impact analysis reports it before anything runs: "Drops and recreates a foreign key on tickets", and "Checking the new key holds back writes to tickets while it reads ~200K rows, 16 MB on disk". The finding names the child table only; on PostgreSQL the referenced table waits too. The planned migration is the plain drop and add, run in one transaction, not the NOT VALID version, so on a large table write that one yourself.

The current release stores the action without a column list, so a PostgreSQL 15 key such as ON DELETE SET NULL (assignee_id) reads back as plain SET NULL, and lint reports its tenant_id column as NOT NULL. For that key, keep the column-list definition in your migration file.

Which ON DELETE action to choose

The child has no meaning without the parent: CASCADE, and index the foreign key.
The child stays valid without the link: SET NULL, on a nullable column, with no check that requires the value, and with a column list if the key includes a tenant column.
The child is a record people rely on: RESTRICT or NO ACTION, and deactivate the parent instead of deleting it.
A known fallback row exists: SET DEFAULT, after inserting that row.

The reach of a cascade across several tables, and why MySQL does not log it, is covered in ON DELETE CASCADE is invisible in your ERD. Whether to declare the foreign key at all is the question in should you use foreign key constraints, and the way a composite key with a NULL column stops being checked is in unique constraints and nullable columns.

A PostgreSQL Role That Reads the Schema but Not the Data: What to Grant

Son Tran — Thu, 01 Oct 2026 01:23:26 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Grant CONNECT on the database and USAGE on the schema, and nothing on the tables: the role can then read the whole structure from pg_catalog while every SELECT on a table fails with permission denied. The catch is that information_schema hides everything the role has no privilege on, so tools built on it show an empty schema, and the catalogue still shows view definitions, function bodies, comments and row estimates.

A PostgreSQL role can read a database's entire structure without being able to read a single row: grant CONNECT on the database and USAGE on the schema, and grant nothing on the tables. Everything a diagram, a data dictionary or a migration review needs is in pg_catalog, which PostgreSQL lets any connected role read. The rows stay behind permission denied.

That matters because the usual answer to "give the diagram tool a login" is GRANT SELECT ON ALL TABLES, or the predefined pg_read_all_data role, and both hand over the customer data along with the schema. Every statement below was run on PostgreSQL 18.3 in a throwaway container, against a shop database with an app schema holding 5,000 customers, 50,000 orders, a view, a function and an enum.

How to give a Postgres user access to the schema but not the data

Three statements, run as a superuser, or as a role with CREATEROLE that owns the database and the schema:

CREATE ROLE schema_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE shop TO schema_reader;
GRANT USAGE ON SCHEMA app TO schema_reader;

PUBLIC already has CONNECT on a new database, so the second statement only matters where it has been revoked, as it is on hardened servers. The third is the one that counts. Connected as schema_reader, \d app.orders in psql prints every column, the identity default, the primary key, the foreign key to customers, and the Referenced by line for a table added after the grants were made. Then:

SELECT * FROM app.orders LIMIT 1;
-- ERROR:  permission denied for table orders

Because no table is granted anything, there are no default privileges to keep in step. A table created tomorrow shows up in the catalogue for this role at once, and its rows are refused the same way.

The role cannot change the schema either. ALTER TABLE app.customers ADD COLUMN x int fails with must be owner of table customers, because schema changes need ownership, not a grant, and CREATE TABLE app.t (id int) fails with permission denied for schema app. One exception to check for: PUBLIC can execute functions by default, so a SECURITY DEFINER function in the schema runs with its owner's rights. A test function doing SELECT count(*) FROM app.orders returned 50,000 to this role. Revoke EXECUTE on such functions from PUBLIC if they touch data.

What each grant lets the role see

The same checks, run after each step:

Role has	Tables it can describe in `pg_catalog`	Rows in `information_schema.columns` for `app`	`SELECT` on `app.orders`
`CONNECT` only	All of them, but `SET search_path TO app` resolves to nothing	0	`permission denied for schema app`
`CONNECT` and `USAGE` on `app`	All of them, and after `SET search_path TO app`, `current_schema()` is `app`	0	`permission denied for table orders`
`pg_read_all_data`	All of them	12	50,000 rows

The first row is the surprising one. Without USAGE, the catalogue still lists the schema's tables, since pg_class is readable by everyone, but the search_path documentation says a schema "for which the user does not have USAGE permission, is silently ignored". current_schema() came back NULL, so any tool that filters its catalogue queries by the current schema reads nothing and reports no error.

Why does information_schema show no tables for a read-only user?

Because the SQL-standard views filter by privilege and the catalogue does not. The information_schema.columns page says it: "Only those columns are shown that the current user has access to (by way of being the owner or having some privilege)." USAGE on a schema is a privilege on the schema, not on the tables in it, so information_schema.tables, .columns and .table_constraints all returned 0 rows for app, while pg_attribute returned all 12 columns and pg_constraint returned every key and check.

So the grant is only half the answer. The other half is which catalogue your tool reads. A client built on information_schema shows this role an empty database, and the tempting fix, a SELECT grant, is the thing you were trying not to give. psql's \d reads pg_catalog, which is why it worked above.

What a no-data role can still learn from the catalogue

The structure is not a secret from anyone who can connect, and it carries more than table and column names. As schema_reader, with no table privileges:

Check constraints and defaults, as written: CHECK (((discount_pct >= (0)::numeric) AND (discount_pct <= (30)::numeric))).
Column comments: "Negotiated discount, capped at 30 by finance".
Enum labels in order: pending, paid, shipped.
View definitions, through pg_get_viewdef, including the business threshold inside one: HAVING (sum(total) > (10000)::numeric).
Function bodies, in pg_proc.prosrc: UPDATE app.orders SET total = total * 0.9 WHERE customer_id = p_customer.
Table sizes and row estimates: reltuples read 50,000 for orders and pg_total_relation_size read 4,096 kB.

What it did not get was anything from the rows. pg_stats is limited to "rows of pg_statistic that correspond to tables the user has permission to read", and it returned 0 rows for app. With pg_read_all_data granted, the same view returned statistics whose most_common_vals hold real values from the table, which is one more reason that role is the wrong one for a schema tool. If a view or function body embeds something you would not show this role, such as a customer id or a secret, the fix is to move it out of the definition, because no grant hides it.

Which credential should the role log in with?

A password for a role that can read production's structure is still a production credential sitting somewhere. On AWS RDS the alternative is IAM database authentication, where the password is a token that, per the RDS documentation, "has a lifetime of 15 minutes" and "is only used for authentication and doesn't affect the session after it is established". The role is created without a password and given the rds_iam role:

CREATE USER schema_reader;
GRANT rds_iam TO schema_reader;

The token comes from aws rds generate-db-auth-token --hostname <endpoint> --port 5432 --region <region> --username schema_reader, and AWS's own psql example connects with sslmode=verify-full against its global-bundle.pem certificate bundle. One caveat from the IAM overview page: once rds_iam is granted, "IAM authentication takes precedence over password authentication", so give it to a dedicated role like this one rather than to a shared login.

Credential	Where it lives	How long it works
Password	The client's store, or a config file	Until someone rotates it
RDS IAM token	Generated on demand from your AWS session	15 minutes to connect
Vault or password manager output	Fetched on demand	Whatever the secret store allows

How Schemity reads the schema with this role

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. It builds the diagram from pg_catalog, not information_schema: tables, views and materialized views from pg_class, columns from pg_attribute, keys and checks from pg_constraint, indexes, enum labels, comments, the objects that depend on each table, and the row estimates and sizes that impact analysis uses. Every one of those queries was run as schema_reader above and returned the full app schema, so the role can reverse engineer the database into a diagram and understand your schema without a single table grant.

To connect with it, set the schema to app in the PostgreSQL connection form; Schemity reads that schema through search_path, so the USAGE grant is what makes it appear. For RDS, set Credential to Command and paste the generate-db-auth-token line: the password command runs through your login shell, its output is kept in memory for five minutes, well inside the token's 15, and it is never written to disk or the keychain. Schemity asks you to approve the exact command for that connection before its first run. Set Encryption to Verify full with the RDS bundle as the root CA.

Two things this role cannot do in Schemity, by design of the grant. Count exactly in the impact analysis drawer runs a real count on the table, so without SELECT it reports "Count failed", with the database's reason, permission denied for table orders; the finding's catalogue estimate, ~50K rows and 4.0 MB here, still shows. And Apply on a migration fails at its first statement with a permission error, must be owner of table for a change to an existing table or permission denied for schema app for a new one, which is the point of a reading role: the diagram, the findings and the migration SQL are yours to review, and the change is applied by a role that owns the tables.

Which role to give a schema tool

Diagram, data dictionary, migration review: CONNECT and USAGE on each schema, no table grants, and a tool that reads pg_catalog. The same applies to an AI agent's database MCP server.
Counting rows or sampling data as well: add SELECT on the specific tables, not pg_read_all_data.
Applying migrations: a separate role that owns the tables, used from your deploy pipeline or deliberately from the migration dialog.

The broader case for pointing a diagram at production is in documenting a production database safely, and once the schema is on the canvas, grouping a legacy database by domain is the next step. The comments this role can read come from column comments written without a migration.

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

Son Tran — Thu, 01 Oct 2026 01:22:37 +0000

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 be computed per row, add the column as nullable, backfill it in batches, and prove it NOT NULL with a constraint added NOT VALID and validated separately, so the full scan never holds a lock that blocks reads. Set lock_timeout on every one of these statements, because even the 10 ms version waits behind any open transaction that has touched the table and stalls every query that arrives after it.

How long it takes to add a NOT NULL column to a large PostgreSQL table depends almost entirely on the default. With a constant, the statement changes the catalogue and returns in milliseconds, whatever the table's size. With a value that has to be computed for each row, it rewrites the table while nothing else can read it, and the safe version is three statements instead of one.

The same ALTER TABLE looks equally harmless in a migration file either way. Every statement and timing below was run on PostgreSQL 18.3 in a throwaway container, against an orders table of 5,000,000 rows and 356 MB.

How to add a NOT NULL column to a large table in Postgres

Pick the migration by what the new column's value is for the rows that already exist:

Existing rows get	Migration	Time on 5M rows	Lock held
The same constant	`ADD COLUMN status text NOT NULL DEFAULT 'new'`	9.6 ms	`ACCESS EXCLUSIVE`, for the catalogue change only
The time of the migration	`ADD COLUMN imported_at timestamptz NOT NULL DEFAULT now()`	1.8 ms	`ACCESS EXCLUSIVE`, catalogue only
A new value per row	`ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid()`	10.4 s	`ACCESS EXCLUSIVE` for the whole rewrite
Nothing	`ADD COLUMN channel text NOT NULL`	Fails at once	`ACCESS EXCLUSIVE` until the error
A value you compute	Nullable column, batched backfill, then prove `NOT NULL`	Seconds of scanning, none of it blocking reads	See below

The first two rows are fast because of a PostgreSQL 11 change, which the release notes describe as allowing ALTER TABLE "to add a column with a non-null default without doing a table rewrite". The ALTER TABLE documentation gives the rule: a non-volatile default "is evaluated at the time of the statement and the result stored in the table's metadata", while a volatile one "will cause the entire table and its indexes to be rewritten". The table's file on disk kept its pg_relation_filenode through the constant default and got a new one through gen_random_uuid(), growing from 356 MB to 473 MB.

now() is stable rather than volatile, so it takes the fast path too, which also means every existing order gets the same imported_at: the time the migration's transaction started. That is correct for an import timestamp and wrong for anything that was meant to record when each row happened.

On a table that has rows, a column with no default and NOT NULL fails on the first one, with ERROR: column "channel" of relation "orders" contains null values. That is the safe failure. The rewrite is the dangerous one, because it succeeds.

What to do when every row needs its own value

Split it into steps, each of which is either instant or does its slow work without blocking reads. Say each order takes its region from its customer. Adding the column as nullable is a catalogue change (0.7 ms):

ALTER TABLE orders ADD COLUMN region text;

Backfill in batches rather than one statement. A single UPDATE joining all 5,000,000 orders to a 100,000-row customers table took 19.6 seconds and kept a row lock on every order it had touched until it committed, so any other transaction updating one of those orders waited for the whole backfill. Batches of 50,000 took about 220 ms each, which keeps each lock short:

UPDATE orders o SET region = c.region FROM customers c
WHERE c.id = o.customer_id AND o.id BETWEEN 1 AND 50000 AND o.region IS NULL;
-- repeat for the next range, committing between batches

Then make the column NOT NULL. The obvious statement is the one to avoid on a big, busy table: ALTER TABLE orders ALTER COLUMN region SET NOT NULL holds ACCESS EXCLUSIVE while it scans every row for a NULL. On a freshly vacuumed table it took 367 to 380 ms over three runs. Straight after the one-statement backfill, when the scan also had to step over 5,000,000 dead row versions and set their hint bits, it took 1.9 seconds, and for all of that time a primary key lookup on orders could not run.

Does SET NOT NULL lock the table, and how do you avoid the scan?

It does, and the way around it is to prove the column has no NULL before SET NOT NULL runs. Since PostgreSQL 12, SET NOT NULL skips its scan when a validated check constraint already proves the same thing:

ALTER TABLE orders ADD CONSTRAINT orders_region_nn_check
  CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn_check;
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_nn_check;

Adding the constraint NOT VALID checks only new and updated rows, so it is instant. VALIDATE CONSTRAINT does the full scan, 534 ms here, but under SHARE UPDATE EXCLUSIVE, a lock that lets reads and writes carry on. The SET NOT NULL afterwards took 3.6 ms, and with client_min_messages at debug1 PostgreSQL says why: existing constraints on column "orders.region" are sufficient to prove that it does not contain nulls. The check constraint has done its job and can be dropped. Give it a name other than orders_region_not_null: on PostgreSQL 18 that is the name SET NOT NULL wants for its own constraint, and a clash leaves you with orders_region_not_null1.

PostgreSQL 18 removes the detour. It stores NOT NULL as a named constraint in pg_constraint, and its release notes add the ability "to set the NOT VALID attribute of NOT NULL constraints":

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null NOT NULL region NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

The first statement took 0.75 ms and already rejects inserts and updates that leave region NULL. The second scanned for 478 ms under SHARE UPDATE EXCLUSIVE. If a NULL slipped through the backfill, validation fails with the same contains null values error and nothing changes. On PostgreSQL 17 the first statement is a syntax error, so use the check-constraint version there.

Approach	Blocking lock held during the scan	Works on
`SET NOT NULL`	`ACCESS EXCLUSIVE`, reads and writes wait	Every version
`CHECK (... IS NOT NULL) NOT VALID`, validate, `SET NOT NULL`	None, the scan runs under `SHARE UPDATE EXCLUSIVE`	PostgreSQL 12 and later
`NOT NULL ... NOT VALID`, validate	None, the scan runs under `SHARE UPDATE EXCLUSIVE`	PostgreSQL 18 and later

Why a 10 ms ALTER TABLE can still stall every query

Every ALTER TABLE here except VALIDATE CONSTRAINT takes ACCESS EXCLUSIVE, at least for an instant, and it has to wait for that lock like anything else. While it waits, it sits in the lock queue, and every query that arrives after it waits behind it, reads included. The constant-default ADD COLUMN that took 9.6 ms on its own showed this on a 100,000-row copy of the table: one session held a transaction open on orders for 8 seconds, the ALTER TABLE took 6,986 ms because it was waiting for that transaction, and a SELECT total FROM orders WHERE id = 1 issued a second later took 5,995 ms. pg_blocking_pids() showed the chain: the SELECT blocked by the ALTER TABLE, and the ALTER TABLE blocked by the open transaction.

An application's pool drains the same way, behind a long report query or a forgotten BEGIN; SELECT ... FROM orders in someone's psql session. The fix is to let the migration give up rather than hold the queue, which the default does not do, since lock_timeout defaults to zero: "A value of zero (the default) disables the timeout."

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';
COMMIT;

With the same open transaction in front of it, this failed after 2 seconds with ERROR: canceling statement due to lock timeout, and the queue behind it cleared. SET LOCAL ends with the transaction, so the setting does not stay behind on a pooled connection. A timeout aborts the whole transaction, so retry it from BEGIN a few times from the deploy script and the migration lands in a quiet moment. No static check can see which transaction will be open when the migration runs, so this one is yours to write in every migration that touches a hot table.

How Schemity shows what the migration will cost

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Adding a column to orders on a connected diagram is how you see the impact of every change before it becomes a migration: press F7 and impact analysis reads the pending schema migration against the database's own catalogue.

A new column drawn NOT NULL with no default is reported as "Adds orders.channel NOT NULL without a default, fails on ~5M existing rows". Preview changes shows it next to the table and the statement that would fail:

Making an existing column NOT NULL gives two findings, "Sets orders.region NOT NULL, fails if any of ~5M rows is NULL" and "Checking region for NULL holds back reads and writes to orders while it reads ~5M rows, 356 MB on disk", which is the lock described above:

In the Impact drawer, Count exactly runs a read-only count of the NULL values before you decide. The row counts are catalogue estimates, so no finding scans the table. Tables under 10,000 rows are left out of the lock findings, since that lock is over before anyone waits on it. In the current release every SET NOT NULL is reported as a scan, including one that a validated check constraint has already proven, so read that finding against the steps above.

When the migration comes from Rails, Django, Prisma or an AI agent instead, press Shift+F7 and paste the file into the SQL migration drawer: the same findings are reported for its statements, BEGIN, COMMIT and a SET LOCAL lock_timeout line are skipped as transaction control and a setting rather than schema changes, and nothing in the file is executed. A file that adds channel and sets it NOT NULL with no backfill in between is caught before it runs:

The migration dialog names the host, database, schema and environment it will run against, and on a connection tagged Production, Apply stays disabled until you type the database name, so the 10-second version cannot land on the wrong server by a misclick. The option is per connection, described in connections. The same findings are repeated above the SQL:

Which migration to write

The value is the same for every existing row: add the column NOT NULL with a constant default, in one statement.
The value differs per row: nullable column, batched backfill, then NOT NULL ... NOT VALID and VALIDATE CONSTRAINT on PostgreSQL 18, or the check-constraint version on 12 to 17.
A volatile default on a big table: only when a rewrite under ACCESS EXCLUSIVE is acceptable, which on 5,000,000 rows was 10.4 seconds of nobody reading orders.
Every one of them: SET LOCAL lock_timeout first, and retry the transaction on failure.

An AI agent or an ORM will write whichever version is shortest, and for a per-row value the shortest is the one that rewrites. The case for reviewing agent-written migrations covers the other lines that look this harmless, such as a rename generated as a drop and an add, and schema linting vs migration linting explains which of these a migration linter catches from the file alone. For another default that decides whether a table is rewritten, see the stored column measurements in generated column vs trigger.

PostgreSQL numeric vs double precision vs money: Which Type to Use for Prices

Son Tran — Mon, 28 Sep 2026 03:21:11 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Store prices and other amounts of money as numeric with a declared scale, such as numeric(12,2). A double precision column cannot hold 0.10 exactly, and in a 1,000,000-row test its sum() gave a different answer on each parallel run. The money type is exact but ties its fractional digits and output to the server's lc_monetary locale, stores no currency, and truncates on division.

For prices, totals and balances in PostgreSQL, reach for numeric(12,2) or another bounded numeric. It is the only one of the three types that is both exact and portable across servers, and it does arithmetic the way an accountant expects. double precision is fast and approximate, and money is exact but carries enough surprises that the PostgreSQL wiki's list of things not to do includes it by name.

The choice is usually made for you by a generator. Prisma's Float maps to double precision and its Decimal to decimal(65,30), which allows 35 digits before the point and 30 after. Django's DecimalField refuses to run without max_digits and decimal_places, so a Django schema at least had someone pick a number. Every statement and timing below was run on PostgreSQL 18.3 in a throwaway container.

What is the difference between numeric, double precision and money in PostgreSQL?

All three accept 12.34. They differ in how they store it, and so in what comes back:

	`numeric(12,2)`	`double precision`	`money`
Storage in a row	Variable: 7 bytes for 1234.56, 11 for 123456789.12	8 bytes	8 bytes
Exact for decimal amounts	Yes	No, binary approximation	Yes, to the locale's fractional digits
`0.1 + 0.2`	`0.3`	`0.30000000000000004`	`$0.30`
Too many decimals	Rounded to the scale	Kept, approximately	Rounded to the locale's digits
Too large	Error: `numeric field overflow`	Kept, with fewer exact digits	Error above 92233720368547758.07
Carries a currency	No, add a column	No	No, and prints the server's symbol
`sum()` over 1,000,000 rows	26 to 37 ms	20 to 22 ms	21 to 22 ms

The timings are three runs each of SELECT sum(...) on a laptop, over one table holding the same random amounts between 0 and 1,000 in all three types. numeric is the slowest, by a fifth to three quarters depending on the run, which is still milliseconds on a million rows. It is rarely the reason a query is slow.

Why does a float sum give a different answer each time?

Because floating point addition is not associative, and PostgreSQL does not always add in the same order. The same table's double precision column, summed seven times with the default parallel plan (two workers plus the leader), returned seven different totals:

500018482.179999
500018482.18000245
500018482.1800022
500018482.1799948
500018482.1799991
500018482.17999697
500018482.1800002

The numeric column returned 500018482.18 every time. Each worker sums its share of the rows, the leader adds the partial sums, and which rows land in which share varies from run to run. With max_parallel_workers_per_gather = 0 the float sum became repeatable, at 500018482.18000865, which is still not the right answer.

The error per value is tiny, which is why it survives code review. It shows up as a reconciliation that compares two totals for equality and fails, a report that rounds a half cent the other way on a rerun, or a test that fails once a week. The PostgreSQL numeric types documentation says it plainly: "If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead."

double precision is the right type for measurements, where the input was approximate to begin with: a temperature, a latitude, a sensor reading, a score. It is the wrong type for anything that someone will add up and compare against an invoice.

Postgres money type vs numeric: which should I use?

Use numeric. The money type looks made for the job, and it is exact: sum() over the same million rows returned $500,018,482.18 every run, as fast as the float. But three of its properties are hard to live with.

Its digits and its output come from the server. The money type documentation says the fractional precision "is determined by the database's lc_monetary setting", and warns that since the output is locale-sensitive, "it might not work to load money data into a database that has a different setting of lc_monetary". A dump taken on one server can fail to restore, or read differently, on another.

It stores no currency. SELECT 12.34::money prints $12.34 on a server whose lc_monetary is en_US, whatever currency the row was in. A multi-currency table needs a currency column either way, and at that point money adds nothing.

Integer division truncates. The same documentation says that dividing money by an integer truncates "the fractional part towards zero". SELECT 10::money / 3 returns $3.33, and multiplying it back gives $9.99. numeric returns 3.3333333333333333 and leaves the rounding to you, which is where it belongs, because splitting a bill is a business rule.

The PostgreSQL wiki's Don't Do This page gives the narrow case where money is fine: a single currency, no fractions of a cent, and only addition and subtraction. It also cannot be cast to double precision at all: 12.34::money::float8 fails with cannot cast type money to double precision, so a conversion has to go through numeric. numeric has none of these limits, so there is little reason to accept them.

What precision and scale should a numeric price column have?

Pick the scale first: it is the smallest unit you ever store. Two decimal places fit most currencies, three fit the Kuwaiti dinar and the Bahraini dinar, and a unit price, a tax rate or an exchange rate often needs four to eight. Then pick the precision so that precision minus scale covers the largest amount the column will ever hold.

The two limits fail differently, and the manual spells out both: a value with more decimals than the scale "will round the value to the specified number of fractional digits", while a value with too many digits before the point raises an error. So numeric(12,2) quietly stores 1.005 as 1.01 and refuses 12345678901.00 with numeric field overflow. The rounding is the one to think about, because nothing tells you it happened.

An unconstrained numeric is a legitimate choice too. It keeps every digit you give it, which suits an intermediate calculation or an exchange rate, but it also accepts 0.333333... in a column the application believes holds cents. For a column people read as money, declare the scale so the database enforces it, and do it early: adding the bound later rewrites the table and rounds every stored value.

What does migrating a price column to numeric cost?

Changing a price column's type is where the choice turns into a schema migration, and PostgreSQL treats the variations very differently. Timed on a 1,000,000-row, 42 MB table with a bigint primary key, three runs each:

Change	Rewrites the table	Time	Can fail or change values
`double precision` to `numeric(12,2)`	Yes	1.2 to 1.9 s	Rounds every value to 2 places; rejects anything that rounds to 10,000,000,000 or more
`numeric(12,2)` to `numeric(14,2)`	No	about 1 ms	No
`numeric(12,2)` to unconstrained `numeric`	No	about 1 ms	No
Unconstrained `numeric` back to `numeric(12,2)`	Yes	not timed	Rounds every value to 2 places; rejects anything too large
`numeric(14,2)` to `numeric(14,4)`	Yes	0.6 to 1.3 s	Fails if any value has more than 10 integer digits

The cheap rows come from PostgreSQL 9.2, whose release notes say that "increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite". Raising the scale does not share that exemption and always rewrites. At the same precision it can also fail: numeric(12,2) to numeric(12,4) leaves room for only 8 integer digits, so a stored 1234567890.12 fails the whole statement with numeric field overflow, and the detail line says A field with precision 12, scale 4 must round to an absolute value less than 10^8.

A rewrite holds an ACCESS EXCLUSIVE lock for its whole duration, so reads and writes on the table wait. Two seconds on a laptop becomes minutes on a table a hundred times the size, which is why the float-to-numeric fix is worth doing while the table is small.

How Schemity finds float price columns and shows what the fix costs

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

When you connect a PostgreSQL database, each entity draws a numeric column with its precision and scale, so list_price reads NUMERIC(12,2) rather than a bare NUMERIC, and the number that decides what the column can hold is on the canvas where you design the next one. A double precision column is drawn as FLOAT.

Schema lint carries a rule called money-as-float. It flags a column stored as FLOAT, DOUBLE or REAL whose name ends in a money or quantity word (price, amount, total, cost, balance, fee, salary, qty, quantity, or anything ending in total, like subtotal), and marks it on the exact field row with the explanation that totals drift as rows accumulate. It matches the last word only, so coffee_temperature and costume_size stay quiet, and so does a measurement like weight_kg, which is a fair use of a float. It sits in the same Costs group as the rule behind the timestamp an ORM chose for you: the schema works, it just keeps charging you.

When you change the column's type in the diagram, impact analysis reads the pending migration before you apply it. For unit_price going from FLOAT to NUMERIC(12,2) on the million-row table it reports three things: values may not survive the cast, the change rewrites products, and it holds back reads and writes while it does, each with the row count and the 87 MB the table takes on disk. It tells the cheap changes apart from the expensive ones too: raising NUMERIC(12,2) to NUMERIC(14,2) reports no rewrite, while NUMERIC(12,2) to NUMERIC(12,4) is flagged as a change that can lose values, because it takes away two integer digits.

The same analysis runs on a migration file your ORM or an AI agent wrote. An agent that reads the schema over MCP gets each column's full type from get_schema, so a field it copies comes back as NUMERIC(12,2) and not as a numeric with the scale lost.

Choosing a type for money in PostgreSQL

Use	When
`numeric(p,s)`	Prices, totals, balances, invoices, anything someone adds up and compares against a ledger
Unconstrained `numeric`	Exchange rates and intermediate results where you want every digit
`double precision`	Measurements that were approximate to begin with; never money
`money`	A single-currency system with no fractions of a cent that only adds and subtracts, and never moves between servers with different locales
`bigint` in minor units	When every amount has a fixed number of decimals and the application does all rounding; store the currency and its exponent beside it

The same kind of decision shows up across the schema: whether a varchar limit is a rule or a habit, whether a status belongs in an enum, a check constraint or a lookup table, and whether a derived total should be a generated column or a trigger. In each case the type is picked once, usually by a generator, and then copied into every table that follows.

PostgreSQL Generated Column vs Trigger: Which to Use for a Derived Column

Son Tran — Sun, 27 Sep 2026 07:10:10 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Use a generated column when the value comes from other columns in the same row through immutable functions, because the database guarantees it and nobody can write to it. Use a trigger only when the value needs another table, the current time, or a function PostgreSQL does not call immutable. On PostgreSQL 18 a generated column is virtual unless you write STORED, and a virtual one cannot be indexed directly.

A PostgreSQL generated column is the better choice for any derived value it can express, because the database computes it on every write and refuses to let anyone set it by hand. A trigger is the fallback for the values a generated column is not allowed to compute: anything that reads another table, the clock, or a function PostgreSQL does not mark immutable.

The choice matters more than it looks, and it matters twice. Once when you write the table, because the two differ in cost, in what they allow, and in how they fail. And again when someone else inherits it: a generated column says what it is in the table definition, while a trigger-maintained column looks exactly like a column the application writes. Every PostgreSQL statement below was run on PostgreSQL 18.3 in a throwaway container.

What is the difference between a generated column and a trigger in PostgreSQL?

Take an order_lines table where line_total is unit_price * quantity. As a generated column it is one line:

CREATE TABLE order_lines (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit_price numeric(12,2) NOT NULL,
    quantity   integer NOT NULL,
    line_total numeric(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

As a trigger it is a plain column plus a function and a trigger that keeps it up to date (drop the first table before running this one, since both are called order_lines):

CREATE TABLE order_lines (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit_price numeric(12,2) NOT NULL,
    quantity   integer NOT NULL,
    line_total numeric(12,2)
);

CREATE FUNCTION set_line_total() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.line_total := NEW.unit_price * NEW.quantity;
    RETURN NEW;
END $$;

CREATE TRIGGER order_lines_total BEFORE INSERT OR UPDATE OF unit_price, quantity
    ON order_lines FOR EACH ROW EXECUTE FUNCTION set_line_total();

Both produce 29.97 for a price of 9.99 and a quantity of 3. The differences are everywhere else:

	Generated column	Trigger-maintained column
What it can read	Columns of the same row, through immutable functions	Anything: other tables, `now()`, any function
Can be written by hand	No, `INSERT` and `UPDATE` are rejected	Yes, and the value stays wrong until the next trigger run
Visible in the table definition	Yes, `GENERATED ALWAYS AS (...)`	No, it is a plain column
Insert 1,000,000 rows (plain table: 1.58 s)	1.83 s stored, 1.52 s virtual	2.72 s
Skipped by `session_replication_role = replica`	No	Yes
Drop a column it reads	Refused	Refused only for columns in `UPDATE OF`; otherwise allowed, and the next write fails
Can be indexed	Stored yes; virtual only through an index on its expression	Yes

The insert timings are the average of three runs each of one INSERT ... SELECT over generate_series(1, 1000000) on a laptop, so read them as ratios: a row-level PL/pgSQL trigger added about 72% to the plain insert, the stored generated column about 16%, and the virtual one nothing measurable, because it writes nothing.

Should I use a generated column or a trigger for a derived column?

Use a generated column when every input is in the same row and every function is immutable. That covers arithmetic like line_total, normalised copies like lower(email), and extracted values like a tsvector built with to_tsvector('english', body) or a field pulled out of a jsonb document. The one-argument to_tsvector(body) is rejected, because it depends on a server setting. PostgreSQL enforces the rules itself when you create the column:

GENERATED ALWAYS AS (now()) STORED fails with generation expression is not immutable.
A subquery fails with cannot use subquery in column generation expression.
A generated column that reads another generated column fails with cannot use generated column "b" in column generation expression.

The third one has an easy way round: repeat the other column's expression inline. The first two are the cases where a trigger is the right answer. The common ones are an updated_at set to now() on every update, an orders.total that sums order_lines, and a denormalised copy of a parent's value, such as a customer's name stamped onto an invoice. None of them can be a generated column, and each of them is a trigger or a query.

If a generated column can express the value, the trigger buys nothing but risk. The table above shows three kinds of it.

A trigger can be bypassed. With the trigger declared BEFORE INSERT OR UPDATE OF unit_price, quantity, running UPDATE order_lines SET line_total = 5 succeeds and leaves line_total at 5.00 against a price of 9.99 and a quantity of 3, because the statement touched neither column the trigger listens to. Leave out UPDATE OF and the trigger fires on every update, which fixes this and costs a function call on updates that never touch the price.

A trigger can be switched off. SET session_replication_role = replica, which the PostgreSQL documentation says logical replication systems set while applying changes, stops ordinary triggers from firing. An insert under it left line_total NULL. Setting it needs superuser or a granted SET privilege, and ALTER TABLE ... DISABLE TRIGGER switches a trigger off the same way. A generated column is computed regardless.

A trigger fails late. PostgreSQL records that the trigger depends on the columns named in UPDATE OF, so dropping unit_price is refused. But it does not read the function body. Renaming unit_price to price succeeds, and the next insert fails with record "new" has no field "unit_price". The migration passed; the application broke on its next write.

Stored or virtual: which generated column does PostgreSQL 18 give you?

PostgreSQL 12 introduced generated columns as stored only. PostgreSQL 18 added virtual ones and made them the default, so GENERATED ALWAYS AS (unit_price * quantity) with no keyword now computes the value when a row is read and stores nothing. The generated columns documentation lists what a virtual column gives up: it cannot use user-defined functions or types, and logical replication can publish only stored ones. Creating an index on one fails with indexes on virtual generated columns are not supported.

The difference shows most when you add the column to a table that already has data. On the same 1,000,000-row table:

Adding `line_total` to an existing table	Time	What it does
`ADD COLUMN ... GENERATED ALWAYS AS (...) VIRTUAL`	about 1 ms	Catalogue change only, no rewrite
`ADD COLUMN ... GENERATED ALWAYS AS (...) STORED`	630 ms	Rewrites the table under `ACCESS EXCLUSIVE`; reads and writes wait
Plain `ADD COLUMN`, then `UPDATE order_lines SET line_total = unit_price * quantity` to backfill before the trigger takes over	under 1 ms, then 2.9 s	Writes a new version of every row; reads continue, writes to those rows wait

Each timing is the same in three runs to within a few percent. The stored column took the table from 50 MB to 58 MB on disk and the virtual one left it at 50 MB, while the backfill doubled it to 107 MB until vacuum reclaims the old row versions. Changing the expression later is ALTER TABLE ... ALTER COLUMN line_total SET EXPRESSION AS (...), available since PostgreSQL 17. For a stored column it rewrites the table again under the same lock; for a virtual one it is a catalogue change.

A virtual column cannot be indexed itself, but that matters less than it sounds: PostgreSQL expands it when it plans a query, so in the same container an index on the expression itself served a WHERE on the virtual column. So the rule inside the rule: virtual when the value is cheap to compute, stored when computing it on every read costs more than storing it, or when a logical replication subscriber needs the value.

How do I find the computed columns in a database I did not write?

A generated column is in the catalogue, so one query finds all of them:

SELECT a.attrelid::regclass AS table_name, a.attname,
       CASE a.attgenerated WHEN 's' THEN 'stored' ELSE 'virtual' END AS kind,
       pg_get_expr(d.adbin, d.adrelid) AS expression
FROM pg_attribute a
JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attgenerated <> '' AND NOT a.attisdropped;

A trigger-maintained column is not. The catalogue knows which triggers exist and which columns their UPDATE OF names, but not which column a function writes, so the only way to find line_total is to list the triggers and read each function body:

SELECT tg.tgrelid::regclass AS table_name, tg.tgname, p.proname, p.prosrc
FROM pg_trigger tg
JOIN pg_proc p ON p.oid = tg.tgfoid
WHERE NOT tg.tgisinternal;

That asymmetry is the strongest argument for the generated column. To understand your schema, you need to know which values the application writes and which the database derives, and only one of the two approaches tells you without reading code.

How Schemity shows generated columns and the triggers behind a column

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

When you connect a PostgreSQL database, Schemity reads its generated columns, as it does on MySQL, SQLite, and SQL Server, where they are called computed columns. On the canvas, a generated column's default cell shows = and its expression next to its type, so line_total reads = unit_price * (quantity)::numeric, the expression as PostgreSQL stores it. When Schemity sizes an entity to fit its content, it makes room for the first 24 characters of an expression, so one long expression does not stretch the table; widen the entity and the rest appears. SVG export draws it the same way.

Open the column and the field dialog shows the full expression and says whether the database stores the value or computes it when read. The name and the description stay editable. Everything else is greyed out, including the type, the default, precision and scale, and the key and nullable checkboxes, because the expression and what follows from it belong to the database.

The diagram does not draw triggers, so a trigger-maintained line_total looks like a plain column there, the same as in psql: a nullable NUMERIC(12,2) with NULL in its default cell, like any nullable column with no default.

Triggers appear where they cost you something, in impact analysis. Rename quantity in a diagram of either version of the table and the report lists every object that depends on it and what PostgreSQL will do to each:

For the generated column: "generated column line_total depends on order_lines.quantity. The database updates it to match."
For the trigger: "trigger order_lines_total depends on order_lines.quantity. It fails the next time it runs if it uses what changed."

Here is the generated column's version in Preview changes. The canvas still shows the expression as the database holds it today, (quantity), because PostgreSQL only rewrites it to qty when the rename runs; a re-sync afterwards shows the new text.

The same rename on the trigger version gets a different verdict. PostgreSQL follows the rename in the trigger's UPDATE OF list, but not inside the function body, so the finding says the trigger may fail on its next run rather than that anything is updated to match.

Dropping a column that a generated column reads is reported as refused, since PostgreSQL, MySQL, SQL Server and SQLite all refuse it. The drop still shows as losing the column's data, and the second finding says the statement will not get that far:

Apply it anyway and PostgreSQL returns cannot drop column quantity of table order_lines because other objects depend on it, with the detail line column line_total of table order_lines depends on column quantity of table order_lines, and the transaction rolls back with both columns still in place.

Changing its type is refused on PostgreSQL and SQL Server, and goes through on MySQL, and on SQLite through the table rebuild a type change needs there; the report says which. The same report runs on a migration file written by Django, Rails, Prisma or an AI agent before it is applied. If you inherited the schema, write down what each trigger maintains as a column description in the diagram, where the next reader will see it.

PostgreSQL Unique Constraint vs Unique Index: Which to Use, and How to Add One Without Locking the Table

Son Tran — Sat, 26 Sep 2026 13:31:21 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Use a unique constraint when the rule covers every row and plain columns, because only a constraint can be DEFERRABLE or named in ON CONFLICT ON CONSTRAINT, though not both at once. Use a unique index when the rule needs a WHERE clause or an expression such as lower(email). On a large table, add either one by building the index with CREATE UNIQUE INDEX CONCURRENTLY first, because a plain ALTER TABLE ... ADD CONSTRAINT ... UNIQUE holds back reads and writes until it has checked every row.

In PostgreSQL a unique constraint and a unique index enforce uniqueness the same way. The constraint is implemented by a unique index that PostgreSQL creates for you. Where they differ is what you can attach to them, and how much of the table each one locks while it is being added. On a table with a million rows, the lock is the part you will notice.

The choice is usually made by the framework before anyone thinks about it. On an existing table, Django's unique=True and a plain UniqueConstraint emit ALTER TABLE ... ADD CONSTRAINT ... UNIQUE, and switch to CREATE UNIQUE INDEX only when the constraint has a condition or an expression. Rails' add_index :users, :email, unique: true always emits CREATE UNIQUE INDEX. Both look like one harmless line in a migration file, and on a large table the two statements lock it in different ways.

What is the difference between a unique constraint and a unique index?

Everything below was run on PostgreSQL 18.3:

	Unique constraint	Unique index
How it is enforced	A unique B-tree index created for it	Itself
Can be the target of a foreign key	Yes	Yes, unless it is partial
`ON CONFLICT (email)`	Yes, unless it is `DEFERRABLE`	Yes (a partial index needs the matching `WHERE` too)
`ON CONFLICT ON CONSTRAINT name`	Yes, unless it is `DEFERRABLE`	No, the constraint does not exist
Partial, e.g. `WHERE deleted_at IS NULL`	No	Yes
On an expression, e.g. `lower(email)`	No, syntax error	Yes
`DEFERRABLE`	Yes	No, syntax error
`NULLS NOT DISTINCT` (PostgreSQL 15+)	Yes	Yes
Can be built `CONCURRENTLY`	Not directly	Yes
Lock while being added	`ACCESS EXCLUSIVE`: reads and writes wait	`SHARE`: writes wait, reads continue

Two rows are worth a sentence. DEFERRABLE is the reason some tables need a constraint: with UNIQUE (pos) DEFERRABLE, UPDATE t SET pos = pos + 1 succeeds, because uniqueness is checked at the end of the statement rather than row by row (INITIALLY DEFERRED moves the check to commit). The same update against a unique index, or against a constraint that is not deferrable, fails partway with duplicate key value violates unique constraint, even though the final state is valid. The price is ON CONFLICT: PostgreSQL refuses a deferrable constraint as its arbiter, in either form. The partial and expression rows are the reason some rules can only be an index, and they are the rules most tables actually have: an email unique among live accounts, or unique regardless of case.

Should I use a unique constraint or a unique index in PostgreSQL?

Default to the constraint and switch to the index when the rule needs something only an index can express:

The rule covers every row, on plain columns: use a unique constraint. It is recorded in pg_constraint as a constraint, which is where schema tools read uniqueness rules from, and \d users labels it UNIQUE CONSTRAINT rather than listing a bare index. It also keeps DEFERRABLE or ON CONFLICT ON CONSTRAINT available, one or the other.
The rule has a condition (unique among rows that are not soft-deleted): use a partial unique index. No constraint can say it. The trade-off is that no foreign key can point at it, and every ON CONFLICT has to repeat its WHERE.
The rule is on an expression (lower(email)): use a unique index on the expression, or store the normalised value in a generated column and put a constraint on that.
You need to reorder or swap values in one statement: use a DEFERRABLE constraint, and give up ON CONFLICT on that key.

The one thing that should not decide it is the lock, because the lock problem has the same answer for both.

Will adding a unique constraint lock the table?

Yes, and harder than the unique index. In a throwaway database, with ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email) held open in one session, a plain SELECT count(*) FROM users in a second session timed out on its lock. With CREATE UNIQUE INDEX held open instead, the same SELECT returned at once and only the INSERT waited. pg_locks shows why: the ALTER TABLE takes ACCESS EXCLUSIVE, and the index build takes SHARE.

How long they hold it grows with the table. On 2,000,000 rows, on a laptop, the ADD CONSTRAINT took 2.9 seconds, and for those 2.9 seconds nothing could read users. The build is a sort, so the time grows faster than the row count, and faster again once the sort no longer fits in maintenance_work_mem: a table fifty times larger holds the lock for minutes, and every query that arrives in the meantime queues behind it.

CREATE UNIQUE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, which lets both reads and writes through while it scans the table twice. It has two rules of its own. It cannot run inside a transaction block, so migration frameworks need their per-migration transaction turned off: disable_ddl_transaction! with algorithm: :concurrently in Rails, and atomic = False in Django. Django's AddIndexConcurrently cannot help here, because its Index has no unique option, so a concurrent unique index in Django is a RunSQL statement. And if it fails, it leaves the index behind.

How do I add a unique constraint to a large table without blocking?

Build the index concurrently, then promote it. The ALTER TABLE documentation describes exactly this for "situations where a new constraint needs to be added without blocking table updates for a long time":

CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users (email);

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_idx;

The second statement still takes ACCESS EXCLUSIVE, but it does not scan anything: on the same 2,000,000 rows it took 0.8 milliseconds, against 2.9 seconds for the direct ADD CONSTRAINT. PostgreSQL renames the index to the constraint name as it goes. The short lock still has to be granted, though, and it waits behind any open transaction that has touched the table, while every query that arrives after it waits too. Run SET lock_timeout = '2s' before the ALTER TABLE and retry if it times out, so a long-running report cannot turn a 0.8 millisecond step into an outage. The index must be valid, non-partial, not on an expression, and a B-tree with default sort order, so this route only ends in a constraint for a rule that could have been a constraint anyway. For a partial or expression rule, stop after the first statement.

Before either statement, check for duplicates, because both fail on them:

SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1;

A failed plain ADD CONSTRAINT rolls back cleanly with could not create unique index "users_email_key" and a detail line naming the first duplicated key. A failed CONCURRENTLY build reports the same error but leaves users_email_idx in place with indisvalid = false. The CREATE INDEX documentation is plain about the cost: it is ignored for queries "however it will still consume update overhead". Drop it, fix the data, and build again.

How Schemity shows the cost before the migration runs

The point of reading the lock table above is to see the impact of every change before it reaches production, and a migration file does not show you any of it. CREATE UNIQUE INDEX and ADD CONSTRAINT ... UNIQUE are each one line, and nothing in the text says which one stops reads, or whether the data will let either succeed.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

When a pending change adds uniqueness to a connected PostgreSQL table, impact analysis reports it twice. Under Can fail on existing data it says "Adds a UNIQUE on users, fails if duplicates exist among ~2M rows", and for a rule on plain columns it adds a Count exactly button that runs one read-only duplicate count under a timeout. Under Holds back other statements it names which lock you will get: "Checking the new key holds back reads and writes to users while it reads ~2M rows" for a constraint, or "Building the index holds back writes to users while it reads ~2M rows" for a unique index, with the table's size on disk. The lock finding is skipped below 10,000 rows, where the lock is over before anyone waits on it. The same findings come up for a migration file written by Django, Rails, Prisma or an AI agent when you analyse it against the connected database without running it.

Preview changes shows the same two findings next to the statement that causes them, so the reader sees the ALTER TABLE and its cost together:

Add the same rule as a unique index instead and the duplicate finding stays, but the lock finding drops "reads and": the index build holds back writes only.

The limits are worth stating. Schemity's own migration SQL never uses CONCURRENTLY, so on a big table the finding is your cue to run the two-statement recipe above in your migration tool instead. Schemity reads that recipe too: paste it into the SQL migration drawer (Shift+F7) and both statements are analysed against the connected database, with nothing in the file executed.

The report keeps the finding that matters and drops the one that no longer applies. The concurrent build can still fail on the duplicate, and Count exactly finds exactly one row. There is no lock finding, because the build lets reads and writes through and the promotion reads no rows.

On the canvas, a column covered by a constraint or a plain unique index carries the same U marker, as described in check constraints and composite unique. The diagram does not read a partial index's WHERE clause, so a partial unique index shows as unique on its columns without its condition, and an index on an expression such as lower(email) does not appear at all.

Polymorphic Associations in PostgreSQL: One commentable_id Column or a Foreign Key per Table?

Son Tran — Fri, 25 Sep 2026 03:30:54 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: A polymorphic association stores a type name and an id in two columns, and PostgreSQL has no foreign key that can point at a different table on each row, so nothing stops orphans or typos. For a small fixed set of parents, use one nullable foreign key per parent with a CHECK that exactly one is set; for many parents, use a shared supertype table. Keep the polymorphic pair only when the set of parents is open-ended, and document it in the ERD as virtual relations, which is how Schemity draws it.

In PostgreSQL, a polymorphic association is the one design where the database cannot help you: a foreign key names exactly one table, so a commentable_id that points at posts on one row and photos on the next is checked by nothing. If the set of parents is small and fixed, use one foreign key per parent with a CHECK that exactly one is set; if it is large, use a shared supertype table; keep the polymorphic pair only when the list of parents is genuinely open.

Most schemas get a polymorphic association without anyone deciding on one. Rails has belongs_to :commentable, polymorphic: true, Laravel has morphTo, and Django has GenericForeignKey, and each makes it one line of model code. The Rails guide to polymorphic associations shows the migration it produces: an id column and a type column, with no foreign key between them and any other table.

What does a polymorphic association look like in PostgreSQL?

Two columns on the child table, one holding a table or class name and one holding a primary key value from that table:

CREATE TABLE comments (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    commentable_type text   NOT NULL,  -- 'Post' or 'Photo'
    commentable_id   bigint NOT NULL,
    content          text   NOT NULL
);

CREATE INDEX ON comments (commentable_type, commentable_id);

Django spells the same idea as content_type_id (a real foreign key to django_content_type) plus object_id, and Laravel uses the *_type / *_id pair exactly as Rails does. In every version, the column that actually points at a parent row, commentable_id or object_id, carries no constraint.

Why can't PostgreSQL put a foreign key on commentable_id?

Because REFERENCES takes one table. commentable_id REFERENCES posts (id) would reject every comment on a photo, and there is no syntax for "the table named in the other column". Everything a foreign key normally does for you is gone:

Orphans. Deleting a post leaves its comments behind, because there is no ON DELETE CASCADE to fire. The cleanup lives in a model callback, which a bulk DELETE in a console or a second service never runs.
Dangling ids. Nothing checks that post 4812 exists when a comment claims to belong to it.
Bad type names. 'Post', 'post' and a class renamed in a refactor are all just text. After a rename, every old row points at a type the application no longer knows. A CHECK (commentable_type IN ('Post', 'Photo')) closes the typo half of this, since PostgreSQL then rejects 'post', but it still says nothing about whether the id exists.
Invisible structure. Every tool that reads relationships from the catalog, from ERD software to BI join suggestions, sees comments as connected to nothing.

What are the alternatives to a polymorphic association?

There are three, and each one gives the database back a real foreign key.

One foreign key per parent (an exclusive arc). Each possible parent gets its own nullable column, and a CHECK enforces that exactly one is set. PostgreSQL has had num_nonnulls since 9.6, which makes the check one line:

CREATE TABLE comments (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id  bigint REFERENCES posts (id)  ON DELETE CASCADE,
    photo_id bigint REFERENCES photos (id) ON DELETE CASCADE,
    content  text NOT NULL,
    CHECK (num_nonnulls(post_id, photo_id) = 1)
);

A supertype table. Create commentables (id bigint PRIMARY KEY), give posts and photos a primary key that is also a foreign key to it, and point comments.commentable_id at commentables. One foreign key, enforced, however many kinds of parent you add.

A comment table per parent. post_comments and photo_comments, each with an ordinary foreign key. It is the simplest schema and the right one when comments on different parents carry different columns anyway.

	Polymorphic type + id	One FK per parent (exclusive arc)	Supertype table	Table per parent
Reference enforced by PostgreSQL	No	Yes	Yes	Yes
`ON DELETE CASCADE` works	No	Yes	Yes, via the supertype	Yes
Wrong type name rejected	Only with a `CHECK` on the type column	No type column to get wrong	No type column to get wrong	No type column to get wrong
Adding a new parent type	New type value, plus a `CHECK` change if you added one	New column plus a `CHECK` change	New table referencing the supertype	New comment table
Query "all comments on this row"	Filter on two columns	Filter on one column	Filter on one column	Query one table
Nullable columns	None	All but one per row	None	None

Should I use polymorphic associations in Postgres?

Rarely, and on purpose when you do. A workable rule:

Two to five fixed parents: use the exclusive arc. The nullable columns are cheap, the CHECK is one line, and every reference is enforced.
Many parents, or you need to list "everything that can be commented on": use the supertype table.
Children that differ per parent: use a table per parent.
An open-ended set of parents, such as plugins, audit logs or activity feeds that attach to any table in the system: the polymorphic pair is a reasonable trade, as long as the application owns the integrity and the schema says so. Give the type column a CHECK listing the allowed values, so at least a misspelled type cannot reach the table.

That last condition is the one teams skip. A polymorphic column that nobody has documented is a relationship that exists only in model code, and the next engineer reading the database finds a bigint column that joins to nothing.

How do you draw a polymorphic association in an ERD?

When you design the next one, the diagram should show both kinds of reference honestly: the ones the database enforces and the ones it cannot. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

A real relation in Schemity only draws what the database would accept, so there is no way to draw commentable_id as a foreign key to two tables. What you draw instead is a virtual relation, and it takes four steps:

Drag a relation from posts to comments, exactly as you would for a real foreign key.
In the relation dialog, switch to the Virtual relation tab.
Pick the existing commentable_id column on the comments side. Nothing new is created: a virtual relation points at a column you already have.
Type a description such as "Post comments" and click Save.

Repeat it from photos to the same commentable_id column with "Photo comments". The diagram now shows comments depending on both parents, with a dashed line for each, and neither line ever reaches a generated migration or a DBML export. Schemity draws each description along its line, so a reader knows what each dashed line means without opening a dialog. If the type column has a CHECK (commentable_type IN ('Post', 'Photo')), the column is underlined as an enum-like field and the entity footer counts the constraint, so the one guard the database does provide is visible too. Virtual relations survive a re-sync from the database, and the cardinality dialog hides ON DELETE and ON UPDATE for them, because nothing performs those actions.

If you choose the exclusive arc instead, it draws as what it is: post_id and photo_id as real foreign keys, each with the green N badge that marks a nullable column, and each parent end drawn as optional, because any single comment belongs to only one of them, and the num_nonnulls CHECK counted in the entity footer. Schema lint then flags either key if it has no index, which matters here because each parent's delete looks up its children through that column. Either way, the reader sees the real shape of the model, and the choice between enforced and documented is visible on the canvas rather than buried in a model file.

Column Comments in PostgreSQL and MySQL: How to Document Columns Without a Migration

Son Tran — Fri, 21 Aug 2026 03:21:45 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: The database has a built-in place to document a column - COMMENT ON COLUMN in PostgreSQL, the COMMENT attribute in MySQL - and almost nobody fills it in, because a sentence of prose has to travel the same path as a schema change: a migration file, a review, a deploy. Schemity keeps field descriptions in the diagram instead, where editing one generates no SQL, reads existing database comments in on import, and exports the result as a data dictionary.

You can document a database column without touching the database: write the description in the model rather than in the schema. That sounds like a dodge until you price the alternative. The database's own mechanism for column documentation, COMMENT ON COLUMN in PostgreSQL and the COMMENT attribute in MySQL, sends a sentence of prose down exactly the same path as a change to how data is stored - a migration file, a code review, an approval, a deploy window - and on MySQL it does something worse than that. Schemity keeps field descriptions in the diagram, where editing one produces no SQL at all.

This is why so many production schemas have thousands of columns and almost no comments. Not because nobody wanted to write them. Because writing one costs a deploy.

How do I document a database column without running a migration?

Keep the description in the model rather than in the storage engine. A field description is a fact about what the column means to your team; it changes no type, no constraint, no index, and nothing about what the database will accept. When it lives in the diagram, editing it is like editing a comment in a code file: you change it, review it in the same pull request as everything else, and nothing has to run against production for it to take effect.

The moment that description is a column comment, it stops being prose and becomes DDL. Now it needs a migration file, and the migration needs a reviewer, and the reviewer is looking at an ALTER TABLE against a live table. Everyone in that chain is correct to be careful, which is the problem: the care is proportionate to a schema change, and this is not one.

Why a column comment is priced like a schema change

PostgreSQL gets this closest to right. COMMENT ON COLUMN invoices.voided_at IS 'set when finance reverses an invoice' is a standalone statement that touches only the catalog. It is still a migration in every workflow where migrations own schema changes, but it is a cheap and safe one.

MySQL has no equivalent. The comment is an attribute inside the column definition, so changing it means ALTER TABLE ... MODIFY, and MySQL's own manual states the trap plainly: "Attributes present in the original definition but not specified for the new definition are not carried forward." The manual's example is a column defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column', modified with the intention of changing only the type:

ALTER TABLE t1 MODIFY col1 BIGINT;

Attribute	In the original definition	After that statement
Data type	`INT`	`BIGINT`
`UNSIGNED`	present	dropped
`DEFAULT 1`	present	dropped
`COMMENT 'my column'`	present	dropped

Three attributes vanish and the statement is perfectly valid. There is no error, no warning, and no comment-only syntax to reach for. To attach one sentence of documentation to a MySQL column, the migration has to restate the column's entire definition correctly, which means the documentation change is now capable of altering how data is stored. That is the reverse of what anyone wanted.

Why ORMs keep declining to support column comments

The obvious escape is to let the ORM manage comments alongside everything else, and the ORMs have been declining for years. Drizzle has three separate open requests for it - issues 886, 1840 and 5203, the last opened on 1 January 2026 - and the newest one argues from an angle that did not exist when the first was filed: "In modern projects, database comments are no longer only for humans - they are increasingly important machine-readable context for AI-powered tooling." node-db-migrate's request, issue 558, has been open since March 2018 carrying the labels "Nice to have" and "Not Planned". Doctrine's migrations have their own long-running report of comments disappearing from generated migrations.

None of these maintainers are wrong. From inside a migration tool, column comments genuinely are niche: they are the only part of a column definition that no query result depends on. The pattern that emerges from eight years of open issues is not neglect, it is a category error. Documentation keeps being filed as a schema feature, gets ranked against schema features, and loses every time.

Where a field description belongs instead

In Schemity, a field carries a description of its own, the same way entities and legends already carry markdown descriptions. It is a text box in the field editor, next to the type and the default, and the sentence it holds is the one a MySQL migration would have had to restate a whole column definition to attach.

That description is the kind nobody ever writes into a column comment, because it is the reasoning behind a type choice rather than a definition of the column, and it would need a deploy. It also answers the question a reader of content TEXT actually has.

A documented field is then visible without opening anything: a bar on the leading edge of the row marks any field that has one, so which parts of a table are documented is a glance down a column rather than an audit, and the bar is drawn into SVG exports too.

One marked row out of nine is the honest picture of most schemas, and it is readable at a glance precisely because the mark is absence-shaped: you are looking for the fields that have no bar.

The important property is what does not happen. Descriptions live in the diagram's JSON file and never reach the database, so writing one produces no migration to review and no statement to run. This was a deliberate reversal on our side rather than a design we got right first time. Schemity used to write descriptions back as column comments, and that produced exactly the migration described above - on MySQL, the worst-shaped statement in the whole product, restating an entire column definition from the diagram just to attach a sentence. It also lost work: because comments came back from the database on every re-sync, a description could be silently overwritten, invisibly on PostgreSQL and MySQL and permanently on SQL Server and SQLite.

Reading still goes one way. Importing an already documented schema arrives documented, because comments that exist in the database are read in. They are simply never written back.

When the comment really does belong in the database

There is a real case on the other side, and the Drizzle issue names it: if the reader is a program that introspects the live database - an agent connecting to a schema it has never seen, a catalog crawler, a BI tool reading the information schema - then the comment has to be in the database, because that is the only place the reader looks. A description in a diagram file it cannot open is worth nothing to it.

If that is your goal, write the comments as migrations and treat them as schema changes, deliberately. What you should not do is adopt that cost by accident for documentation that only people will ever read, which is the situation almost every team is actually in.

	Comment in the database	Description in the model
Read by	Anything that introspects the catalog	People, and any export you generate
Cost of an edit	Migration, review, deploy	Save the file
MySQL cost of an edit	Restating the full column definition	The same as any other engine
Survives a schema refresh	Yes, it is the source	Yes, it is not overwritten
Reviewed in the pull request	As DDL	As a diff in the diagram JSON

Getting the documentation to the people who need it

A description nobody can reach is not documentation, which is the fair objection to keeping it out of the database. The answer is export rather than storage: the diagram exports as a data dictionary in HTML, Markdown and Excel, covering every column with its type, key role, nullability, default and description alongside the constraints and relationships. The export follows the active view, and a context view is a saved, focused subset of the schema showing only some entities and the relationships between them, so documenting one context produces a document about that context rather than the whole database.

It also ends by counting what is not written down yet: how many entities and fields carry a description, and the names of those that do not. It states the numbers and stops there, which is the only honest way to report on documentation coverage.

That closing count is the part that changes behaviour, because the reason columns go undocumented was never that people did not care. It was that the cheapest place to write it down had a deploy attached, so the note went into a wiki page instead and drifted. Take the deploy off the description and the note goes where the schema is - and then the data dictionary is a document you generate rather than one you maintain.

Circular Foreign Keys: Why the First Row Cannot Be Inserted

Son Tran — Wed, 19 Aug 2026 01:48:01 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: When foreign keys form a cycle and every column in it is NOT NULL, each insert needs a row that does not exist yet, so an empty database can never take its first row. The cycle is a property of the whole graph rather than of any one relationship, so nobody spots it by looking at the diagram - it surfaces at seed time on a fresh environment. Schemity's fk-cycle-all-not-null lint rule computes it from the open ERD offline and marks the entities involved in the margin, and the fix is a modelling decision: make one side nullable, defer the check on an engine that can, or move the reference into a third table.

A cycle of foreign keys is legal to create and impossible to populate. If departments.manager_id is NOT NULL and references employees, and employees.department_id is NOT NULL and references departments, the schema is valid, the diagram is tidy, the migration applies cleanly, and the database will never accept a single row. Each insert needs a row that does not exist yet.

That failure has a specific arrival time, and it is not review. It arrives the first time somebody points the seed script at an empty database: a new staging environment, a contributor's local machine, a fresh tenant, the disaster-recovery rehearsal. Production is fine, because production was populated years ago by whoever fought through it once. The defect sat in the schema the whole time and only the empty case exposes it.

It is also old. On 22 June 2000, Eric Du asked the PostgreSQL mailing list why he could not create two tables that were foreign keys for each other - his INITIALLY DEFERRED attempt failed at CREATE TABLE with ERROR: Relation 't2' does not exist, because deferral postpones checking data, not the existence of a table that has not been created yet. Twenty-six years later the modelling version of the same knot is still being written up: a clear description of it puts the problem in one sentence, that running two independent inserts will not work because you cannot insert into department without a manager_id and cannot insert into employee without a department_id.

Can two tables have foreign keys to each other?

Yes, and this is worth separating from the insert problem, because the two get conflated constantly.

Creating the pair is a DDL ordering question. The first CREATE TABLE cannot reference a table that does not exist, so the second constraint is added afterwards with ALTER TABLE ... ADD CONSTRAINT. That is a mechanical detail, it works on every engine, and once both constraints exist the catalog is perfectly happy. Nothing about the shape is rejected.

Inserting into the pair is a different question with a different answer, and the answer depends entirely on one thing: whether every foreign key column in the cycle is NOT NULL.

If one of them is nullable, there is no problem at all. Insert the department with manager_id NULL, insert the employee pointing at it, update the department. Two statements and an update, done once, at seed time.

If all of them are NOT NULL, there is no ordering that works, because ordering is not the difficulty. The set of rows the schema demands is self-referential, and no sequence of statements produces a set that contains itself.

Which databases let you defer the foreign key check?

The escape hatch is deferral: tell the engine to check the constraint at COMMIT rather than after each statement, insert both rows inside one transaction, and let the two halves validate each other at the end. Whether you have that hatch is decided by the engine, not by the model.

Engine	Deferrable foreign keys	What a NOT NULL cycle means here
PostgreSQL	Yes, `DEFERRABLE INITIALLY DEFERRED`	Both inserts in one transaction, if you can supply the key values yourself
Oracle	Yes, for constraints other than NOT NULL	Same
SQLite	Yes, with `PRAGMA foreign_keys = ON` and an explicit transaction	Same, and outside an explicit transaction deferred constraints behave as immediate
MySQL and MariaDB	No	No transactional escape hatch at all
SQL Server	No	No transactional escape hatch at all

MySQL's own manual is blunt about it, and states in the foreign key constraints page that because MySQL does not support deferred constraint checking, NO ACTION is treated as RESTRICT. SQL Server has no deferrable constraints either. On those engines a cycle of NOT NULL foreign keys is not a puzzle to solve in the transaction, it is a schema you cannot use.

And deferral is narrower than it first appears even where it exists. The PostgreSQL CREATE TABLE documentation is explicit that only UNIQUE, PRIMARY KEY, EXCLUDE and REFERENCES constraints accept the clause, and that NOT NULL and CHECK constraints are not deferrable. So the NOT NULL on department.manager_id is still checked immediately. You are not allowed to insert the department with no manager and fix it at commit - you have to insert it pointing at an employee id that does not exist yet, which means generating the key yourself from a sequence or as a client-side UUID before either row is written. Deferral does not remove the chicken and egg. It relocates it into your insert code, where it becomes a requirement that the application knows both keys in advance.

The three shapes a foreign key cycle takes

Only one of these is visible by eye, which is the whole difficulty.

One table. A self-reference: categories.parent_id NOT NULL referencing categories.id. The root category has no parent and cannot be written. This one is obvious in hindsight and still ships regularly, usually because parent_id was made NOT NULL for the honest reason that most rows do have a parent.

Two tables. The mutual pair - departments and employees, organizations and owners, carts and checkouts. Visible on a diagram if the two entities happen to sit next to each other, invisible if they are eighty tables apart on a large canvas.

Three or more. A ring: A references B, B references C, C references A. Nobody drew this and nobody can see it. Each of the three relationships is individually reasonable, each was added in a different quarter, and the cycle exists only in the graph they form together. No amount of staring at the ERD finds it, because there is no place on the diagram where the defect is located - it is a property of the whole model, in exactly the sense that a foreign key whose type does not match the key it references is a fact about two tables at once rather than about either one.

How do you find a cycle you cannot see?

You compute it. Schema lint in Schemity ships seventeen rules, and fk-cycle-all-not-null is one of the three in the group that fails at runtime: a cycle of foreign keys in which every column is NOT NULL, with a table referencing itself as the simplest case. It walks the relationships in the open diagram, so the three-table ring is found on exactly the same terms as the self-reference - the number of hops makes no difference to a graph traversal and all the difference to a person.

table3 > table2 and table5 > table6, each reporting that every foreign key in the cycle is NOT NULL so no row can be inserted, beside a canvas where table1, table2 and table3 form a ring of three ordinary-looking relationships" width="800" height="556">

Both invisible shapes are sitting in that one panel. table5 > table6 is the mutual pair and table1 > table3 > table2 is the ring, and the rule reports them identically because to a traversal they are one defect at two lengths. Look at the canvas underneath and the ring is three unremarkable relationships, each drawn between a different pair of tables, none of which says cycle by itself. The finding names the hops in order - table1 → table3 → table2 → table1 - says what it costs, that each INSERT needs a row that does not exist yet, and states both fixes in the same breath: make one of these foreign keys nullable, or declare them DEFERRABLE on PostgreSQL and insert the rows in one transaction.

Two properties of how that finding is reported matter more than the check itself.

It runs against the model, not the server. Every input the rule needs - which relationships exist, which columns are nullable - is already written down in the diagram, so the check needs the schema rather than a connection, in exactly the way a rule reading which date columns are missing their time zone needs nothing but the types in front of it. You get the answer while designing, on a plane, before the migration exists, rather than at seed time on an environment that does not exist yet.

And it lands on the diagram. Every finding carries a Show on canvas link, and taking it draws a colored strip in the margin beside each entity in the cycle, at the exact field row concerned, rather than leaving you with a list to translate back into the picture - the orange strip visible on the entity at the top of that canvas is the same mechanism reporting the unrelated change_histories.action_type finding. Nullability is already legible there: a nullable field carries a green N badge on the entity, so once you break the cycle, the thing that broke it is visible on the canvas at a glance instead of being a fact you have to remember. Clicking the relationship highlights both ends, the foreign key field on one entity and the primary key it points at on the other, which is how you trace a ring back through its hops.

If the cycle is deliberate and you have solved it with deferral, ignore that single finding. The ignore is saved in the diagram's JSON file and travels with it, so the decision is recorded once for the team rather than re-dismissed by each person who opens the file.

One honest limit: Schemity has no DEFERRABLE toggle on a relationship. It models cardinality, ON DELETE and ON UPDATE, and its answer to a cycle is the modelling decision rather than a constraint flag - so a deferrable constraint is something you add in the migration and record in the ignore, not something the ERD holds for you.

Which side should be nullable?

Assuming you are not deferring, one column in the cycle has to accept NULL, and the choice is not arbitrary.

Pick the side where absence is a real state of the world, not the side that is easier to change. A department genuinely can exist before its manager is appointed, so departments.manager_id being nullable describes reality. An employee who belongs to no department is usually a data error, so making employees.department_id nullable to fix an insert ordering problem quietly legalises a row nobody wants. Both choices unblock the insert. Only one of them is still true a year later.

The reason this matters beyond taste is that NULL then means something, and every query has to handle it. A nullable foreign key is also a nullable column in every unique constraint it participates in, where NULLs compare as distinct and the constraint stops enforcing what it appears to enforce. Choosing the wrong side to relax buys one insert and pays for it in every read.

The third option is to remove the cycle rather than survive it. If both directions are genuinely required and neither absence is real, the reference that does not belong to the entity moves into its own table: a department_managers table holding (department_id, employee_id) with a unique constraint on department_id says one manager per department, in the same shape a junction table uses for the many-to-many case, and both original tables now point one way only. The cost is a join. The benefit is a schema with no cycle in it at all, which is also a schema whose rows can be inserted in any order, on any engine, forever.

What a cycle does to deletes

The insert is the loud failure. The delete is the quiet one.

Referential actions are evaluated in the same graph, so a cycle carrying ON DELETE CASCADE describes a delete that travels back to where it started. Engines guard against the infinite case, but the guard varies - SQL Server refuses to create cyclic cascade paths outright, MySQL and PostgreSQL accept them and resolve the traversal at runtime - and the practical result is that the blast radius of one DELETE in a cycle is genuinely hard to reason about from the SQL. Schemity draws a bold crow's foot at the child end of any relationship whose foreign key cascades, so the rows a parent delete takes with it are visible on the canvas without opening a dialog, and a cascade inside a cycle reads as a bold ring rather than as three unrelated decisions.

At the architecture scale the same question repeats between groups of tables rather than between tables. A context view is a focused subset of the main diagram - one domain's entities, arranged for reading, with the main view still holding the schema. On the Context Map, each context view becomes a node and arrows carry the count of foreign keys flowing in each direction, with a curved arrow rather than a straight one where two contexts depend on each other - so a mutual dependency between two bounded contexts is a shape you scan for rather than a thing you audit. Indirect cycles across three or more contexts are the same invisible case one level up, which is why the AI chat reads the map to answer that question directly instead of asking you to trace arrows.

The one-line version

A foreign key cycle where every column is NOT NULL is a schema that compiles and cannot run. Deferral helps on PostgreSQL, Oracle and SQLite and does not exist on MySQL or SQL Server, and even where it exists it hands the ordering problem to your insert code rather than removing it. The durable fixes are both modelling decisions: make the side where absence is real nullable, or move the reference into its own table.

What you should not rely on is seeing it. Two of the three shapes are invisible on a canvas, which is the general property of a schema defect that involves more than one object at a time - the same reason a link table with nothing enforcing uniqueness over its pair looks exactly like one that is correct, and why the ERD is the right place to check the model rather than the migration that only ever shows you the delta. Draw the relationships, then let something walk them.

DEV Community: Son Tran

PostgreSQL serial vs identity: Which to Use, and How to Convert Old serial Columns

What serial actually creates

Serial vs identity in Postgres: which should you use?

The serial overflow that survives the bigint migration

A foreign key declared as serial fills itself in

How do you convert a serial column to identity?

How Schemity handles serial and identity keys

Which to use, in one list

Database MCP Server: Should an AI Agent Run SQL or Only Read the Schema?

What can a database MCP server let an agent do?

Is a read-only Postgres MCP server safe?

Why a database role is the guard that holds

What does an agent need to understand your schema?

How Schemity's database MCP server works

Which database MCP setup to choose

Changing a Column Type in SQLite: The Table Rebuild and What It Deletes

Which ALTER TABLE changes SQLite can do in place

Two things a SQLite table rebuild deletes, and one it gets wrong

How do you change a column type in SQLite?

How Schemity plans a SQLite rebuild

What to check before any SQLite rebuild

ON DELETE SET NULL vs CASCADE vs RESTRICT in PostgreSQL: Which to Use

ON DELETE SET NULL vs CASCADE: which should you use?

Three ways SET NULL is accepted and then fails

What SET NULL does besides writing NULL

Does ON DELETE need an index on the child column?

How Schemity handles ON DELETE SET NULL

Which ON DELETE action to choose

A PostgreSQL Role That Reads the Schema but Not the Data: What to Grant

How to give a Postgres user access to the schema but not the data

What each grant lets the role see

Why does information_schema show no tables for a read-only user?

What a no-data role can still learn from the catalogue

Which credential should the role log in with?

How Schemity reads the schema with this role

Which role to give a schema tool

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

How to add a NOT NULL column to a large table in Postgres

What to do when every row needs its own value

Does SET NOT NULL lock the table, and how do you avoid the scan?

Why a 10 ms ALTER TABLE can still stall every query

How Schemity shows what the migration will cost

Which migration to write

PostgreSQL numeric vs double precision vs money: Which Type to Use for Prices

What is the difference between numeric, double precision and money in PostgreSQL?

Why does a float sum give a different answer each time?

Postgres money type vs numeric: which should I use?

What precision and scale should a numeric price column have?

What does migrating a price column to numeric cost?

How Schemity finds float price columns and shows what the fix costs

Choosing a type for money in PostgreSQL

PostgreSQL Generated Column vs Trigger: Which to Use for a Derived Column

What is the difference between a generated column and a trigger in PostgreSQL?

Should I use a generated column or a trigger for a derived column?

Stored or virtual: which generated column does PostgreSQL 18 give you?

How do I find the computed columns in a database I did not write?

How Schemity shows generated columns and the triggers behind a column

Related reading

PostgreSQL Unique Constraint vs Unique Index: Which to Use, and How to Add One Without Locking the Table

What is the difference between a unique constraint and a unique index?

Should I use a unique constraint or a unique index in PostgreSQL?

Will adding a unique constraint lock the table?

How do I add a unique constraint to a large table without blocking?

How Schemity shows the cost before the migration runs

Related reading

Polymorphic Associations in PostgreSQL: One commentable_id Column or a Foreign Key per Table?

What does a polymorphic association look like in PostgreSQL?

Why can't PostgreSQL put a foreign key on commentable_id?

What are the alternatives to a polymorphic association?

Should I use polymorphic associations in Postgres?

How do you draw a polymorphic association in an ERD?

Related reading

Column Comments in PostgreSQL and MySQL: How to Document Columns Without a Migration

How do I document a database column without running a migration?

Why a column comment is priced like a schema change

Why ORMs keep declining to support column comments

Where a field description belongs instead

When the comment really does belong in the database

Getting the documentation to the people who need it