Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features

PostgreSQL Table Design Use this skill when Designing a schema for PostgreSQL Selecting data types and constraints Planning indexes, partitions, or RLS policies Reviewing tables for scale and maintainability Do not use this skill when You are targeting a non PostgreSQL database You only need query tuning without schema changes You require a DB agnostic modeling guide Instructions 1. Capture entities, access patterns, and scale targets (rows, QPS, retention). 2. Choose data types and constraints that enforce invariants. 3. Add indexes for real query paths and validate with EXPLAIN . 4. Plan partitioning or RLS where required by scale or access control. 5. Review migration impact and apply changes safely. Safety Avoid destructive DDL on production without backups and a rollback plan. Use migrations and staging validation before applying schema changes. 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 DEFAULT s 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) = 'user@example.com' . 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 partitioned tables not supported; use triggers. Special Considerations Update Heavy Tables Separate hot/cold columns —put frequently updated columns in separate table to minimize bloat. Use fillfactor=90 to leave space for HOT updates that avoid index maintenance. Avoid updating indexed columns —prevents beneficial HOT updates. Partition by update patterns —separate frequently updated rows in a different partition from stable data. Insert Heavy Workloads Minimize indexes —only create what you query; every index slows inserts. Use COPY or multi row INSERT instead of single row inserts. UNLOGGED tables for rebuildable staging data—much faster writes. Defer index creation for bulk loads— drop index, load data, recreate indexes. Partition by time/hash to distribute load. TimescaleDB automates partitioning and compression of insert heavy data. Use a natural key for primary key such as a (timestamp, device id) if enforcing global uniqueness is important many insert heavy tables don't need a primary key at all. If you do need a surrogate key, Prefer BIGINT GENERATED ALWAYS AS IDENTITY over UUID . Upsert Friendly Design Requires UNIQUE index on conflict target columns— ON CONFLICT (col1, col2) needs

postgresql