postgresql-table-design

PostgreSQL Table Design

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. Premature denormalization creates maintenance burden.
• Add NOT NULL everywhere it’s 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 integer values, DOUBLE PRECISION for floats (or NUMERIC for exact decimal arithmetic).

PostgreSQL “Gotchas”

• Identifiers: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use snake_case for table/column names.
• 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). Example: inserting 999 into NUMERIC(2,0) fails with error, unlike some databases that silently truncate or round.
• Sequences/identity have gaps (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.
• Heap storage: no clustered PK by default (unlike SQL Server/MySQL InnoDB); CLUSTER is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.
• MVCC: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.

Data Types

• IDs: BIGINT GENERATED ALWAYS AS IDENTITY preferred (GENERATED BY DEFAULT also fine); UUID when merging/federating/used in a distributed system or for opaque IDs. Generate with uuidv7() (preferred if using PG18+) or gen_random_uuid() (if using an older PG version).
• Integers: prefer BIGINT unless storage space is critical; INTEGER for smaller ranges; avoid SMALLINT unless constrained.
• Floats: prefer DOUBLE PRECISION over REAL unless storage space is critical. Use NUMERIC for exact decimal arithmetic.
• Strings: prefer TEXT; if length limits needed, use CHECK (LENGTH(col) <= n) instead of VARCHAR(n); avoid CHAR(n). Use BYTEA for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: PLAIN (no TOAST), EXTENDED (compress + out-of-line), EXTERNAL (out-of-line, no compress), MAIN (compress, keep in-line if possible). Default EXTENDED usually optimal. Control with ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy and ALTER TABLE tbl SET (toast_tuple_target = 4096) for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on LOWER(col) (preferred unless column needs case-insensitive PK/FK/UNIQUE) or CITEXT.
• Money: NUMERIC(p,s) (never float).
• Time: TIMESTAMPTZ for timestamps; DATE for date-only; INTERVAL for durations. Avoid TIMESTAMP (without timezone). Use now() for transaction start time, clock_timestamp() for current wall-clock time.
• Booleans: BOOLEAN with NOT NULL constraint unless tri-state values are required.
• Enums: CREATE TYPE ... AS ENUM for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.
• Arrays: TEXT[], INTEGER[], etc. Use for ordered lists where you query elements. Index with GIN for containment (@>, <@) and overlap (&&) queries. Access: arr[1] (1-indexed), arr[1:3] (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: '{val1,val2}' or ARRAY[val1,val2].
• Range types: daterange, numrange, tstzrange for intervals. Support overlap (&&), containment (@>), operators. Index with GiST. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer [) (inclusive/exclusive) by default.
• Network types: INET for IP addresses, CIDR for network ranges, MACADDR for MAC addresses. Support network operators (<<, >>, &&).
• Geometric types: POINT, LINE, POLYGON, CIRCLE for 2D spatial data. Index with GiST. Consider PostGIS for advanced spatial features.
• Text search: TSVECTOR for full-text search documents, TSQUERY for search queries. Index tsvector with GIN. Always specify language: to_tsvector('english', col) and to_tsquery('english', 'query'). Never use single-argument versions. This applies to both index expressions and queries.
• Domain types: CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') for reusable custom types with validation. Enforces constraints across tables.
• Composite types: CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) for structured data within columns. Access with (col).field syntax.
• JSONB: preferred over JSON; index with GIN. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.
• Vector types: vector type by pgvector for vector similarity search for embeddings.

Do not use the following data types

• DO NOT use timestamp (without time zone); DO use timestamptz instead.
• DO NOT use char(n) or varchar(n); DO use text instead.
• DO NOT use money type; DO use numeric instead.
• DO NOT use timetz type; DO use timestamptz instead.
• DO NOT use timestamptz(0) or any other precision specification; DO use timestamptz instead
• DO NOT use serial type; DO use generated always as identity instead.

Table Types

• Regular: default; fully durable, logged.
• TEMPORARY: session-scoped, auto-dropped, not logged. Faster for scratch work.
• UNLOGGED: persistent but not crash-safe. Faster writes; good for caches/staging.

Row-Level Security

Enable with ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Create policies: CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()). Built-in user-based access control at the row level.

Constraints

• PK: implicit UNIQUE + NOT NULL; creates a B-tree index.
• FK: specify ON DELETE/UPDATE action (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use DEFERRABLE INITIALLY DEFERRED for circular FK dependencies checked at transaction end.
• UNIQUE: creates a B-tree index; allows multiple NULLs unless NULLS NOT DISTINCT (PG15+). Standard behavior: (1, NULL) and (1, NULL) are allowed. With NULLS NOT DISTINCT: only one (1, NULL) allowed. Prefer NULLS NOT DISTINCT unless you specifically need duplicate NULLs.
• CHECK: row-local constraints; NULL values pass the check (three-valued logic). Example: CHECK (price > 0) allows NULL prices. Combine with NOT NULL to enforce: price NUMERIC NOT NULL CHECK (price > 0).
• EXCLUDE: prevents overlapping values using operators. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) prevents double-booking rooms. Requires appropriate index type (often GiST).

Indexing

• B-tree: default for equality/range queries (=, <, >, BETWEEN, ORDER BY)
• Composite: order matters—index used if equality on leftmost prefix (WHERE a = ? AND b > ? uses index on (a,b), but WHERE b = ? does not). Put most selective/frequently filtered columns first.
• Covering: CREATE INDEX ON tbl (id) INCLUDE (name, email) - includes non-key columns for index-only scans without visiting table.
• Partial: for hot subsets (WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Any query with status = 'active' can use this index.
• Expression: for computed search keys (CREATE INDEX ON tbl (LOWER(email))). Expression must match exactly in WHERE clause: WHERE LOWER(email) = '[email protected]'.
• GIN: JSONB containment/existence, arrays (@>, ?), full-text search (@@)
• GiST: ranges, geometry, exclusion constraints
• BRIN: very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after CLUSTER).

Partitioning

• Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).
• Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically
• RANGE: common for time-series (PARTITION BY RANGE (created_at)). Create partitions: CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). TimescaleDB automates time-based or ID-based partitioning with retention policies and compression.
• LIST: for discrete values (PARTITION BY LIST (region)). Example: FOR VALUES IN ('us-east', 'us-west').
• HASH: for even distribution when no natural key (PARTITION BY HASH (user_id)). Creates N partitions with modulus.
• Constraint exclusion: requires CHECK constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).
• Prefer declarative partitioning or hypertables. Do NOT use table inheritance.
• Limitations: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partiti

Content truncated.