The Schema & Constraints Blueprint
A PostgreSQL schema is more than a list of tables and columns.
It is a set of promises about what data is allowed to exist, and constraints are the mechanism that keeps those promises true even when application code has bugs.
This page builds the mental model behind schema design and constraints before you touch the how-to pages in this section on keys, generated columns, soft deletes, or DDL transactions.
Understanding this model matters because every later decision, from choosing a primary key strategy to picking a CHECK constraint over an application check, flows from the same underlying question: where should the source of truth for correctness live?
Summary
- Core Idea: A schema defines the shape of your data, and constraints define which instances of that shape are valid.
- Why It Matters: Bugs, race conditions, and half-finished migrations will eventually try to write invalid data, and only the database can guarantee it never sticks.
- Key Concepts: normalization, primary key, foreign key, CHECK constraint, UNIQUE constraint, DDL.
- When to Use: Every table you create needs this thinking, but it matters most for financial data, multi-tenant systems, and anything with cross-table relationships.
- Limitations / Trade-offs: Strict constraints add write-time overhead and can make some migrations harder to land without downtime.
- Related Topics: primary and foreign keys, generated and identity columns, DDL transactions, soft deletes.
Foundations
A schema in the design sense is the set of tables, columns, types, and relationships that model your business domain.
This is distinct from a PostgreSQL "schema" the namespace object, though the two share a name for historical reasons.
Designing a schema starts with normalization, the practice of splitting data into tables so that each fact is stored in exactly one place.
A normalized orders and order_lines split, for example, avoids repeating a customer's shipping address on every line item.
Normalization reduces update anomalies, where changing one fact requires touching many rows, but it also means more joins at query time.
Most production schemas land somewhere between fully normalized and deliberately denormalized, and that landing point is a design decision, not an accident.
Constraints are the rules that make a schema more than a suggestion.
A NOT NULL constraint says a value is always required, a CHECK constraint says a value must satisfy a boolean expression, and a UNIQUE constraint says no two rows may share a value.
A PRIMARY KEY combines NOT NULL and UNIQUE into a single column or column set that identifies a row.
A FOREIGN KEY ties a column in one table to a key in another, so that a reference can never point at a row that does not exist.
Think of a schema without constraints as a filing cabinet with no labels, anyone can put anything anywhere, and think of constraints as the labels that make retrieval and trust possible.
The simplest illustration is a single table with a primary key and a check constraint working together.
CREATE TABLE app.order_lines (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL,
qty int NOT NULL CHECK (qty > 0)
);This one statement encodes three separate promises: every row has a stable identity, every line belongs to an order, and no line can ever claim a non-positive quantity.
Mechanics & Interactions
Constraints are not passive documentation, they are enforced on every write by the executor before a transaction is allowed to commit.
When you insert or update a row, PostgreSQL checks NOT NULL and CHECK constraints immediately as part of that statement.
UNIQUE and PRIMARY KEY constraints are backed by an index, so uniqueness is enforced by looking up the new value in that index before the row is allowed to land.
FOREIGN KEY constraints are checked by a system trigger that queries the referenced table, which is why an unindexed parent key can make foreign key checks slow.
By default, most constraints are checked at the end of each statement, but DEFERRABLE constraints can be pushed to the end of the transaction instead.
This deferral matters when you need to insert rows that reference each other, since a strictly immediate foreign key check would reject the first row before the second one exists.
DDL, the statements that create or alter these objects, has its own mechanics worth understanding early.
In PostgreSQL, most DDL statements are transactional, meaning a CREATE TABLE or ALTER TABLE inside a BEGIN/COMMIT block can be rolled back like any other change.
This is unusual compared to many other relational databases, and it is a genuine advantage when a multi-step migration needs to be all-or-nothing.
Adding a constraint to an existing table, however, is not free at the moment you add it.
ALTER TABLE ... ADD CONSTRAINT typically takes an ACCESS EXCLUSIVE lock while it validates every existing row against the new rule.
On a small table this validation is instantaneous, but on a large, busy table it can block reads and writes for the duration of the scan.
PostgreSQL offers a two-step pattern for this exact problem: add the constraint as NOT VALID first, which only blocks new writes, then run VALIDATE CONSTRAINT separately to check existing rows with a much lighter lock.
Generated and identity columns extend this same enforcement model into computed values.
An identity column guarantees a server-generated, gap-tolerant surrogate key without the application ever choosing a value, while a generated column derives its value from other columns and cannot be written directly.
Both remove an entire category of bugs where two code paths disagree about how to compute the same thing.
Advanced Considerations & Applications
At scale, the interesting schema questions move from "what constraints do I need" to "how do I change them without an outage."
Adding a NOT NULL constraint to a large table used to require a full table rewrite in older PostgreSQL versions, but modern PostgreSQL can validate it as a fast check against an existing CHECK constraint instead, so backfilling and constraining are often best done as separate, sequenced steps.
Multi-tenant schemas raise a related question about where tenant isolation lives, whether as a tenant_id column with row-level security, a constraint-enforced foreign key, or fully separate schemas per tenant.
Exclusion constraints, built on the same infrastructure as unique indexes but generalized to any operator, solve problems unique constraints cannot, such as preventing overlapping date ranges for the same resource.
Domains offer another layer of reuse, letting you define a named type with a built-in CHECK constraint once and apply it to many columns consistently, though they are less flexible than table-level constraints when a rule needs to reference multiple columns.
The deepest architectural question is where validation logic should live at all, and that question has no universally correct answer, only trade-offs.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Database constraints (CHECK, UNIQUE, FK) | Enforced for every writer, including scripts and other services | Migration and validation cost on large tables | Invariants that must never be violated, regardless of caller |
| Application-layer validation | Fast to iterate, rich error messages, easy to test in isolation | Bypassed by direct SQL, batch jobs, or a second service | User-facing input shaping and business rules that change often |
| Database triggers | Can enforce cross-row and cross-table rules constraints cannot express | Harder to reason about, can hide logic from readers of the schema | Rare invariants too complex for a declarative constraint |
| ORM-level validation | Convenient, colocated with model code | Only as strong as the one code path that uses the ORM | A second line of defense layered on top of real constraints |
The pattern that holds up best in production is layering: application validation for user experience, database constraints for the invariant that must never break, and triggers reserved for the rare case neither can express cleanly.
Schema evolution also has an observability dimension, since a constraint violation in production is a signal, not just an error, and tracking which constraints reject the most rows over time often points at a mismatch between what the application assumes and what actually happens upstream.
Common Misconceptions
- "Application validation is enough if the code is careful." Every additional writer, script, ETL job, or future service that touches the table bypasses application code entirely, so only a database constraint is guaranteed to apply everywhere.
- "Normalization is always the correct default." Heavy normalization can turn simple reads into expensive multi-way joins, and a deliberately denormalized column is sometimes the right trade-off for a hot read path.
- "DDL is instantaneous, so I can run migrations anytime." Many DDL operations take strong locks that block concurrent reads and writes, and the safe pattern is often to split a change into a lock-light step followed by a validation step.
- "Foreign keys always slow down writes too much to bother." The real cost is usually a missing index on the referencing column, not the foreign key itself, and an indexed foreign key adds only a small, predictable overhead.
- "NULL means the same thing as an empty string or zero." NULL represents unknown or not applicable, and treating it as equivalent to a default value causes silent bugs in comparisons and aggregates.
FAQs
What is the difference between a PostgreSQL "schema" and a database schema design?
- A PostgreSQL schema (as in
CREATE SCHEMA app) is a namespace that groups tables, views, and functions. - Schema design as a discipline refers to how you model tables, keys, and constraints for your domain.
- The two concepts share a name but operate at completely different levels.
Should every table have a surrogate primary key?
- A surrogate key such as an identity column is usually the safer default for join stability.
- A natural key still deserves a UNIQUE constraint even when a surrogate key exists.
- Composite natural keys work well for pure reference or lookup tables that rarely get referenced by many child tables.
Why would I use a CHECK constraint instead of validating in application code?
A CHECK constraint is enforced for every writer against that table, including scripts, migrations, and other services, while application validation only protects the one code path that runs it.
Does adding a foreign key always lock the table for a long time?
- The lock itself is brief, but validating existing rows against the new key can take time proportional to table size.
- Indexing the referencing column beforehand keeps that validation fast.
- The
NOT VALIDplusVALIDATE CONSTRAINTpattern minimizes blocking on large, busy tables.
What is the practical difference between UNIQUE and PRIMARY KEY?
A PRIMARY KEY is a UNIQUE constraint combined with NOT NULL, and a table may have only one primary key but many separate unique constraints.
When should I reach for an EXCLUSION constraint instead of UNIQUE?
An exclusion constraint generalizes uniqueness to any operator, which makes it the right tool when you need to prevent overlapping ranges, such as two bookings for the same resource sharing any overlapping time window.
Are generated columns slower to write than regular columns?
- A generated column adds a small computation cost on every insert or update that touches its inputs.
- That cost is almost always cheaper than the bugs caused by two code paths computing the same derived value differently.
- Stored generated columns also add a small amount of extra storage compared to computing the value at read time.
Is PostgreSQL DDL really transactional?
Most DDL statements, including CREATE TABLE and ALTER TABLE, participate fully in transactions and roll back cleanly if the surrounding transaction is aborted, which is a genuine advantage over databases where DDL auto-commits.
How do I add a NOT NULL constraint to a huge table without downtime?
- Add a CHECK constraint as
NOT VALIDthat mirrors the NOT NULL rule first. - Validate it separately with
VALIDATE CONSTRAINT, which takes a much lighter lock. - Modern PostgreSQL can then derive the NOT NULL guarantee from that validated CHECK without a second full table scan.
What is the difference between a domain and a table-level CHECK constraint?
A domain packages a base type with a built-in constraint so it can be reused consistently across many columns and tables, while a table-level CHECK constraint applies only to that one table and can reference multiple columns at once.
Why does normalization matter if storage is cheap?
Normalization is not primarily about storage cost, it is about avoiding update anomalies where the same fact stored in multiple places can drift out of sync.
Should soft deletes be enforced with a constraint?
Soft deletes typically rely on a nullable deleted_at column paired with a partial index or partial unique constraint, so that uniqueness rules only apply to the still-active rows.
What is the biggest schema design mistake teams make early on?
Treating constraints as optional documentation rather than as the actual enforcement mechanism is the most common mistake, since it leaves correctness dependent on every future writer behaving perfectly.
Related
- Schema Design Basics - hands-on examples of keys and naming conventions
- Primary & Foreign Keys - referential integrity and ON DELETE behavior in practice
- CHECK and UNIQUE Constraints - row-level and set-level validation rules
- Generated & Identity Columns - server-computed and server-generated values
- DDL Transactions - how schema changes participate in transactions and locking
- Soft Deletes vs Hard Deletes - deletion strategy and its constraint implications
Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17 also supported).