Design Optimal PostgreSQL Table Schemas

PostgreSQL Table Design covers schema design, data type selection, constraints, indexing, partitioning, and RLS for PostgreSQL specifically (not a DB-agnostic guide). Core rules: use BIGINT GENERATED ALWAYS AS IDENTITY for primary keys on reference tables (UUID only when global uniqueness/opacity is needed), normalize to 3NF first and denormalize only for measured, high-ROI read problems, and manually index every foreign-key column since Postgres never auto-indexes them. A worked example applying these rules:

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);

Use it for PostgreSQL schema design, data type/constraint selection, index/partition/RLS planning, or reviewing tables for scale - not for query tuning alone or non-Postgres targets. It documents six PostgreSQL-specific gotchas: unquoted identifiers lowercase (use snake_case), UNIQUE allows multiple NULLs unless NULLS NOT DISTINCT (PG15+), overflow errors rather than silent truncation, identity sequence gaps are normal and shouldn't be "fixed," there's no clustered PK by default (row order is insertion order unless explicitly CLUSTERed), and MVCC leaves dead tuples that vacuum must clean up. Six data types are explicitly banned in favor of alternatives: timestamp (use timestamptz), char(n)/varchar(n) (use text with a CHECK length constraint), money (use numeric), timetz, precision-specified timestamptz(0), and serial (use generated always as identity).

Index types map to use case: B-tree for equality/range, Composite indexes where column order matters (leftmost-prefix rule), Covering indexes (CREATE INDEX ON tbl (id) INCLUDE (name, email)) for index-only scans, Partial indexes for hot subsets (WHERE status = 'active'), Expression indexes for computed search keys like LOWER(email), GIN for JSONB containment/array/full-text search, GiST for ranges/geometry/exclusion constraints, and BRIN for very large naturally-ordered time-series data. An EXCLUDE constraint (EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)) prevents overlapping values, such as double-booking a room. Beyond the primary type guidance, the skill also covers enum, array, range, network (INET/CIDR/MACADDR), geometric/PostGIS, domain, and composite types for their respective specialized use cases. Generated columns (... GENERATED ALWAYS AS (<expr>) STORED, with PG18+ adding read-time VIRTUAL columns) provide computed, indexable fields without application-side denormalization. Partitioning (RANGE, LIST, HASH) applies to tables over 100M rows filtered consistently on the partition key, using declarative partitioning or TimescaleDB hypertables rather than table inheritance - with the limitation that global UNIQUE constraints and foreign keys from partitioned tables aren't supported. JSONB guidance defaults to a GIN index accelerating containment/key-existence/path queries, switching to the jsonb_path_ops opclass for smaller/faster containment-only indexes, or extracting a scalar field into a generated column with a B-tree index for equality/range queries on it. Row-Level Security is enabled per table (ALTER TABLE tbl ENABLE ROW LEVEL SECURITY) with per-row CREATE POLICY rules.

Workload-specific guidance covers update-heavy tables (fillfactor=90 for HOT updates, separating hot/cold columns), insert-heavy workloads (minimize indexes, use COPY/multi-row inserts, UNLOGGED staging tables, defer index creation for bulk loads), and upsert-friendly design (ON CONFLICT requires an exact unique index match, EXCLUDED.column to reference would-be values). Safe schema evolution notes that most DDL is transactional and rollback-testable, CREATE INDEX CONCURRENTLY avoids blocking writes, and volatile column defaults (now(), gen_random_uuid()) trigger a full table rewrite while non-volatile defaults don't. Recommended extensions include pgcrypto for hashing, pg_trgm for fuzzy text search, timescaledb for time-series, postgis for geospatial, and pgvector for embedding similarity search.

Backend and database engineers designing or reviewing PostgreSQL schemas who need concrete, Postgres-specific rules on data types, indexing, partitioning, and safe migrations rather than generic relational-database advice.