PostgreSQL Table Design

When to Use

• Designing a new PostgreSQL schema, or reviewing one before it ships.
• Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.
• Deciding whether and how to partition a large table, or how to store semi-structured data.
• Planning a schema change on a live database without downtime.

The rules and decision points for a PostgreSQL schema. The full data-type catalog, workload patterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing, and worked DDL examples are in references/details.md; open it when a section below points there.

Core Rules

• Define a PRIMARY KEY for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer BIGINT GENERATED ALWAYS AS IDENTITY; use UUID only when global uniqueness/opacity is needed.
• Normalize first (to 3NF) to eliminate data redundancy and update anomalies; denormalize only for measured, high-ROI reads where join performance is proven problematic.
• Add NOT NULL everywhere it is semantically required; use DEFAULTs for common values.
• Create indexes for access paths you actually query: PK/unique (auto), FK columns (manual!), frequent filters/sorts, and join keys.
• Prefer TIMESTAMPTZ for event time; NUMERIC for money; TEXT for strings; BIGINT for integers; DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic).

PostgreSQL Gotchas

• Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names; use snake_case.
• Unique + NULLs: UNIQUE allows multiple NULLs. Use UNIQUE NULLS NOT DISTINCT (...) (PG15+) to restrict to one NULL.
• FK indexes: PostgreSQL does not auto-index FK columns. Add them.
• No silent coercions: length/precision overflows error out (no truncation). Inserting 999 into NUMERIC(2,0) fails, unlike databases that silently truncate or round.
• Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions leave gaps (1, 2, 5, 6...).
• Heap storage: no clustered PK by default; CLUSTER is a one-off reorganization, not maintained on later inserts.
• MVCC: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.

Data Types

• IDs: BIGINT GENERATED ALWAYS AS IDENTITY; UUID for distributed or opaque IDs, generated with uuidv7() (PG18+) or gen_random_uuid().
• Numbers: BIGINT unless storage is critical; DOUBLE PRECISION over REAL; NUMERIC(p,s) for money and exact decimals.
• Strings: TEXT, with CHECK (LENGTH(col) <= n) when a limit is needed; BYTEA for binary. Case-insensitive lookups: expression index on LOWER(col), or CITEXT when a constraint must be case-insensitive.
• Time: TIMESTAMPTZ, DATE, INTERVAL. now() is transaction start; clock_timestamp() is wall clock.
• Booleans: BOOLEAN NOT NULL unless tri-state is required.
• Enums: CREATE TYPE ... AS ENUM only for small, stable sets; evolving business values get TEXT + CHECK or a lookup table.
• JSONB over JSON, indexed with GIN, for optional/semi-structured attributes only.
• Arrays, ranges, network, geometric, full-text, domain, composite, and vector types, plus TOAST storage and collation control: see references/details.md.

Types to avoid

Constraints

• PK: implicit UNIQUE + NOT NULL; creates a B-tree index.
• FK: specify ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Index the referencing column. Use DEFERRABLE INITIALLY DEFERRED for circular dependencies checked at commit.
• UNIQUE: creates a B-tree index; allows multiple NULLs unless NULLS NOT DISTINCT (PG15+). Prefer NULLS NOT DISTINCT unless duplicate NULLs are wanted.
• CHECK: row-local; NULL passes (three-valued logic). Combine with NOT NULL: price NUMERIC NOT NULL CHECK (price > 0).
• EXCLUDE: prevents overlaps with operators, e.g. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) stops double-booking. Needs a GiST-capable type.

Indexing

…