postgresql-table-design

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 ; open it when a section below poin…

)` for reusable custom types with validation. Enforces constraints across tables.\n- **Composite types**: `CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)` for structured data within columns. Access with `(col).field` syntax.\n- **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.\n- **Vector types**: `vector` type by `pgvector` for vector similarity search for embeddings.\n\n## Table types\n\n- **Regular**: default; fully durable, logged.\n- **TEMPORARY**: session-scoped, auto-dropped, not logged. Faster for scratch work.\n- **UNLOGGED**: persistent but not crash-safe. Faster writes; good for caches/staging.\n\n## Row-level security\n\nEnable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (owner = current_user)` when database roles map to users, or compare against an app-set setting such as `current_setting('app.user_id')::bigint`. Built-in user-based access control at the row level.\n\n## Workload patterns\n\n### Update-heavy tables\n\n- **Separate hot/cold columns**—put frequently updated columns in separate table to minimize bloat.\n- **Use `fillfactor=90`** to leave space for HOT updates that avoid index maintenance.\n- **Avoid updating indexed columns**—prevents beneficial HOT updates.\n- **Partition by update patterns**—separate frequently updated rows in a different partition from stable data.\n\n### Insert-heavy workloads\n\n- **Minimize indexes**—only create what you query; every index slows inserts.\n- **Use `COPY` or multi-row `INSERT`** instead of single-row inserts.\n- **UNLOGGED tables** for rebuildable staging data—much faster writes.\n- **Defer index creation** for bulk loads—>drop index, load data, recreate indexes.\n- **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data.\n- **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.\n- If you do need a surrogate key, **Prefer `BIGINT GENERATED ALWAYS AS IDENTITY` over `UUID`**.\n\n### Upsert-friendly design\n\n- **Requires UNIQUE index** on conflict target columns—`ON CONFLICT (col1, col2)` needs exact matching unique index (partial indexes don't work).\n- **Use `EXCLUDED.column`** to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.\n- **`DO NOTHING` faster** than `DO UPDATE` when no actual update needed.\n\n### Safe schema evolution\n\n- **Transactional DDL**: most DDL operations can run in transactions and be rolled back—`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing.\n- **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions.\n- **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast.\n- **Drop constraints before columns**: `ALTER TABLE DROP CONSTRAINT` then `DROP COLUMN` to avoid dependency issues.\n- **Function signature changes**: `CREATE OR REPLACE` with different arguments creates overloads, not replacements. DROP old version if no overload desired.\n\n## Constraint and index notes\n\n- **FK**: an explicit index on the referencing column speeds up joins and prevents locking issues on parent deletes/updates.\n- **UNIQUE**: standard behavior allows both `(1, NULL)` and `(1, NULL)`; with `NULLS NOT DISTINCT` (PG15+) only one `(1, NULL)` is allowed.\n- **Partial index**: `CREATE INDEX ON tbl (user_id) WHERE status = 'active'` serves any query that includes `status = 'active'`.\n- **Expression index**: the expression must match the WHERE clause exactly: `WHERE LOWER(email) = '[email protected]'`.\n- **BRIN**: effective when row order on disk correlates with the indexed column (insertion order or after `CLUSTER`).\n- **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); row order on disk is insertion order unless explicitly clustered, and `CLUSTER` is not maintained on later inserts.\n\n## Partitioning details\n\n- Use for very large tables (>100M rows) where queries consistently filter on the partition key (often time/date), or for tables whose maintenance (pruning, periodic bulk replacement) follows a key.\n- **RANGE**: `PARTITION BY RANGE (created_at)`; create partitions with `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.\n- **LIST**: `PARTITION BY LIST (region)` with `FOR VALUES IN ('us-east', 'us-west')`.\n- **HASH**: `PARTITION BY HASH (user_id)` creates N partitions by modulus, for even distribution when there is no natural key.\n- **Constraint exclusion**: requires `CHECK` constraints on partitions for the planner to prune; auto-created for declarative partitioning (PG10+).\n\n## Generated columns\n\n- `... GENERATED ALWAYS AS (\u003cexpr>) STORED` for computed, indexable fields. PG18+ adds `VIRTUAL` columns (computed on read, not stored).\n\n## Extensions\n\n- **`pgcrypto`**: `crypt()` for password hashing.\n- **`uuid-ossp`**: alternative UUID functions; prefer `pgcrypto` for new projects.\n- **`pg_trgm`**: fuzzy text search with `%` operator, `similarity()` function. Index with GIN for `LIKE '%pattern%'` acceleration.\n- **`citext`**: case-insensitive text type. Prefer expression indexes on `LOWER(col)` unless you need case-insensitive constraints.\n- **`btree_gin`/`btree_gist`**: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).\n- **`hstore`**: key-value pairs; mostly superseded by JSONB but useful for simple string mappings.\n- **`timescaledb`**: essential for time-series—automated partitioning, retention, compression, continuous aggregates.\n- **`postgis`**: comprehensive geospatial support beyond basic geometric types—essential for location-based applications.\n- **`pgvector`**: vector similarity search for embeddings.\n- **`pgaudit`**: audit logging for all database activity.\n\n## JSONB guidance\n\n- Prefer `JSONB` with **GIN** index.\n- Default: `CREATE INDEX ON tbl USING GIN (jsonb_col);` → accelerates:\n - **Containment** `jsonb_col @> '{\"k\":\"v\"}'`\n - **Key existence** `jsonb_col ? 'k'`, **any/all keys** `?\\|`, `?&`\n - **Path containment** on nested docs\n - **Disjunction** `jsonb_col @> ANY(ARRAY['{\"status\":\"active\"}', '{\"status\":\"pending\"}'])`\n- Heavy `@>` workloads: consider opclass `jsonb_path_ops` for smaller/faster containment-only indexes:\n - `CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);`\n - **Trade-off**: loses support for key existence (`?`, `?|`, `?&`) queries—only supports containment (`@>`)\n- Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):\n - `ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;`\n - `CREATE INDEX ON tbl (price);`\n - Prefer queries like `WHERE price BETWEEN 100 AND 500` (uses B-tree) over `WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500` without index.\n- Arrays inside JSONB: use GIN + `@>` for containment (e.g., tags). Consider `jsonb_path_ops` if only doing containment.\n- Keep core relations in tables; use JSONB for optional/variable attributes.\n- Use constraints to limit allowed JSONB values in a column e.g. `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')`\n\n## Examples\n\n### Users\n\n```sql\nCREATE TABLE users (\n user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n email TEXT NOT NULL UNIQUE,\n name TEXT NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE UNIQUE INDEX ON users (LOWER(email));\nCREATE INDEX ON users (created_at);\n```\n\n### Orders\n\n```sql\nCREATE TABLE orders (\n order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n user_id BIGINT NOT NULL REFERENCES users(user_id),\n status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),\n total NUMERIC(10,2) NOT NULL CHECK (total > 0),\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE INDEX ON orders (user_id);\nCREATE INDEX ON orders (created_at);\n```\n\n### JSONB\n\n```sql\nCREATE TABLE profiles (\n user_id BIGINT PRIMARY KEY REFERENCES users(user_id),\n attrs JSONB NOT NULL DEFAULT '{}',\n theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED\n);\nCREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":12094,"content_sha256":"fac8b43c75ed30d95f2ab12886021f26bb8b03f8fed0cea2929b8de18953023a"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"PostgreSQL Table Design","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When to Use","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Designing a new PostgreSQL schema, or reviewing one before it ships.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Deciding whether and how to partition a large table, or how to store semi-structured data.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Planning a schema change on a live database without downtime.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"references/details.md","type":"text","marks":[{"type":"code_inline"}]},{"text":"; open it when a section below points there.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Core Rules","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Define a ","type":"text"},{"text":"PRIMARY KEY","type":"text","marks":[{"type":"strong"}]},{"text":" for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer ","type":"text"},{"text":"BIGINT GENERATED ALWAYS AS IDENTITY","type":"text","marks":[{"type":"code_inline"}]},{"text":"; use ","type":"text"},{"text":"UUID","type":"text","marks":[{"type":"code_inline"}]},{"text":" only when global uniqueness/opacity is needed.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Normalize first (to 3NF)","type":"text","marks":[{"type":"strong"}]},{"text":" to eliminate data redundancy and update anomalies; denormalize ","type":"text"},{"text":"only","type":"text","marks":[{"type":"strong"}]},{"text":" for measured, high-ROI reads where join performance is proven problematic.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"strong"}]},{"text":" everywhere it is semantically required; use ","type":"text"},{"text":"DEFAULT","type":"text","marks":[{"type":"strong"}]},{"text":"s for common values.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create ","type":"text"},{"text":"indexes for access paths you actually query","type":"text","marks":[{"type":"strong"}]},{"text":": PK/unique (auto), ","type":"text"},{"text":"FK columns (manual!)","type":"text","marks":[{"type":"strong"}]},{"text":", frequent filters/sorts, and join keys.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Prefer ","type":"text"},{"text":"TIMESTAMPTZ","type":"text","marks":[{"type":"strong"}]},{"text":" for event time; ","type":"text"},{"text":"NUMERIC","type":"text","marks":[{"type":"strong"}]},{"text":" for money; ","type":"text"},{"text":"TEXT","type":"text","marks":[{"type":"strong"}]},{"text":" for strings; ","type":"text"},{"text":"BIGINT","type":"text","marks":[{"type":"strong"}]},{"text":" for integers; ","type":"text"},{"text":"DOUBLE PRECISION","type":"text","marks":[{"type":"strong"}]},{"text":" for floats (or ","type":"text"},{"text":"NUMERIC","type":"text","marks":[{"type":"code_inline"}]},{"text":" for exact decimal arithmetic).","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"PostgreSQL Gotchas","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Identifiers","type":"text","marks":[{"type":"strong"}]},{"text":": unquoted → lowercased. Avoid quoted/mixed-case names; use ","type":"text"},{"text":"snake_case","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Unique + NULLs","type":"text","marks":[{"type":"strong"}]},{"text":": UNIQUE allows multiple NULLs. Use ","type":"text"},{"text":"UNIQUE NULLS NOT DISTINCT (...)","type":"text","marks":[{"type":"code_inline"}]},{"text":" (PG15+) to restrict to one NULL.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"FK indexes","type":"text","marks":[{"type":"strong"}]},{"text":": PostgreSQL ","type":"text"},{"text":"does not","type":"text","marks":[{"type":"strong"}]},{"text":" auto-index FK columns. Add them.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No silent coercions","type":"text","marks":[{"type":"strong"}]},{"text":": length/precision overflows error out (no truncation). Inserting 999 into ","type":"text"},{"text":"NUMERIC(2,0)","type":"text","marks":[{"type":"code_inline"}]},{"text":" fails, unlike databases that silently truncate or round.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Sequences/identity have gaps","type":"text","marks":[{"type":"strong"}]},{"text":" (normal; don't \"fix\"). Rollbacks, crashes, and concurrent transactions leave gaps (1, 2, 5, 6...).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Heap storage","type":"text","marks":[{"type":"strong"}]},{"text":": no clustered PK by default; ","type":"text"},{"text":"CLUSTER","type":"text","marks":[{"type":"code_inline"}]},{"text":" is a one-off reorganization, not maintained on later inserts.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MVCC","type":"text","marks":[{"type":"strong"}]},{"text":": updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Data Types","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"IDs","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"BIGINT GENERATED ALWAYS AS IDENTITY","type":"text","marks":[{"type":"code_inline"}]},{"text":"; ","type":"text"},{"text":"UUID","type":"text","marks":[{"type":"code_inline"}]},{"text":" for distributed or opaque IDs, generated with ","type":"text"},{"text":"uuidv7()","type":"text","marks":[{"type":"code_inline"}]},{"text":" (PG18+) or ","type":"text"},{"text":"gen_random_uuid()","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Numbers","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"BIGINT","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless storage is critical; ","type":"text"},{"text":"DOUBLE PRECISION","type":"text","marks":[{"type":"code_inline"}]},{"text":" over ","type":"text"},{"text":"REAL","type":"text","marks":[{"type":"code_inline"}]},{"text":"; ","type":"text"},{"text":"NUMERIC(p,s)","type":"text","marks":[{"type":"code_inline"}]},{"text":" for money and exact decimals.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Strings","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"TEXT","type":"text","marks":[{"type":"code_inline"}]},{"text":", with ","type":"text"},{"text":"CHECK (LENGTH(col) \u003c= n)","type":"text","marks":[{"type":"code_inline"}]},{"text":" when a limit is needed; ","type":"text"},{"text":"BYTEA","type":"text","marks":[{"type":"code_inline"}]},{"text":" for binary. Case-insensitive lookups: expression index on ","type":"text"},{"text":"LOWER(col)","type":"text","marks":[{"type":"code_inline"}]},{"text":", or ","type":"text"},{"text":"CITEXT","type":"text","marks":[{"type":"code_inline"}]},{"text":" when a constraint must be case-insensitive.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Time","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"TIMESTAMPTZ","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"DATE","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"INTERVAL","type":"text","marks":[{"type":"code_inline"}]},{"text":". ","type":"text"},{"text":"now()","type":"text","marks":[{"type":"code_inline"}]},{"text":" is transaction start; ","type":"text"},{"text":"clock_timestamp()","type":"text","marks":[{"type":"code_inline"}]},{"text":" is wall clock.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Booleans","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"BOOLEAN NOT NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless tri-state is required.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Enums","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE TYPE ... AS ENUM","type":"text","marks":[{"type":"code_inline"}]},{"text":" only for small, stable sets; evolving business values get ","type":"text"},{"text":"TEXT","type":"text","marks":[{"type":"code_inline"}]},{"text":" + ","type":"text"},{"text":"CHECK","type":"text","marks":[{"type":"code_inline"}]},{"text":" or a lookup table.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"JSONB","type":"text","marks":[{"type":"strong"}]},{"text":" over JSON, indexed with GIN, for optional/semi-structured attributes only.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Arrays, ranges, network, geometric, full-text, domain, composite, and vector types, plus TOAST storage and collation control: see ","type":"text"},{"text":"references/details.md","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Types to avoid","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Avoid","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use instead","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"timestamp","type":"text","marks":[{"type":"code_inline"}]},{"text":" (without time zone)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"timestamptz","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"char(n)","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"varchar(n)","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"text","type":"text","marks":[{"type":"code_inline"}]},{"text":" (+ ","type":"text"},{"text":"CHECK","type":"text","marks":[{"type":"code_inline"}]},{"text":" on length if needed)","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"money","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"numeric","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"timetz","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"timestamptz","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"timestamptz(0)","type":"text","marks":[{"type":"code_inline"}]},{"text":" or any precision","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"timestamptz","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"serial","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"generated always as identity","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Constraints","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PK","type":"text","marks":[{"type":"strong"}]},{"text":": implicit UNIQUE + NOT NULL; creates a B-tree index.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"FK","type":"text","marks":[{"type":"strong"}]},{"text":": specify ","type":"text"},{"text":"ON DELETE/UPDATE","type":"text","marks":[{"type":"code_inline"}]},{"text":" (","type":"text"},{"text":"CASCADE","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"RESTRICT","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"SET NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"SET DEFAULT","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Index the referencing column. Use ","type":"text"},{"text":"DEFERRABLE INITIALLY DEFERRED","type":"text","marks":[{"type":"code_inline"}]},{"text":" for circular dependencies checked at commit.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"UNIQUE","type":"text","marks":[{"type":"strong"}]},{"text":": creates a B-tree index; allows multiple NULLs unless ","type":"text"},{"text":"NULLS NOT DISTINCT","type":"text","marks":[{"type":"code_inline"}]},{"text":" (PG15+). Prefer ","type":"text"},{"text":"NULLS NOT DISTINCT","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless duplicate NULLs are wanted.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CHECK","type":"text","marks":[{"type":"strong"}]},{"text":": row-local; NULL passes (three-valued logic). Combine with ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":": ","type":"text"},{"text":"price NUMERIC NOT NULL CHECK (price > 0)","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"EXCLUDE","type":"text","marks":[{"type":"strong"}]},{"text":": prevents overlaps with operators, e.g. ","type":"text"},{"text":"EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)","type":"text","marks":[{"type":"code_inline"}]},{"text":" stops double-booking. Needs a GiST-capable type.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Indexing","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"B-tree","type":"text","marks":[{"type":"strong"}]},{"text":": default for equality/range (","type":"text"},{"text":"=","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"\u003c","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":">","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"BETWEEN","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"ORDER BY","type":"text","marks":[{"type":"code_inline"}]},{"text":").","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Composite","type":"text","marks":[{"type":"strong"}]},{"text":": leftmost-prefix rule (","type":"text"},{"text":"WHERE a = ? AND b > ?","type":"text","marks":[{"type":"code_inline"}]},{"text":" uses ","type":"text"},{"text":"(a,b)","type":"text","marks":[{"type":"code_inline"}]},{"text":"; ","type":"text"},{"text":"WHERE b = ?","type":"text","marks":[{"type":"code_inline"}]},{"text":" does not). Most selective columns first.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Covering","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE INDEX ON tbl (id) INCLUDE (name, email)","type":"text","marks":[{"type":"code_inline"}]},{"text":" for index-only scans.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partial","type":"text","marks":[{"type":"strong"}]},{"text":": hot subsets, ","type":"text"},{"text":"CREATE INDEX ON tbl (user_id) WHERE status = 'active'","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Expression","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE INDEX ON tbl (LOWER(email))","type":"text","marks":[{"type":"code_inline"}]},{"text":"; the query must use the same expression.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":": JSONB containment/existence, arrays, full-text search. ","type":"text"},{"text":"GiST","type":"text","marks":[{"type":"strong"}]},{"text":": ranges, geometry, exclusion constraints.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"BRIN","type":"text","marks":[{"type":"strong"}]},{"text":": large, naturally ordered data (time-series) at minimal storage cost; effective when disk order correlates with the indexed column.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Partitioning","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use for large tables (>100M rows) whose queries consistently filter on the partition key, or where maintenance (pruning, bulk replacement) follows a key.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"RANGE","type":"text","marks":[{"type":"strong"}]},{"text":" for time-series (","type":"text"},{"text":"PARTITION BY RANGE (created_at)","type":"text","marks":[{"type":"code_inline"}]},{"text":"; ","type":"text"},{"text":"TimescaleDB","type":"text","marks":[{"type":"strong"}]},{"text":" automates it with retention and compression), ","type":"text"},{"text":"LIST","type":"text","marks":[{"type":"strong"}]},{"text":" for discrete values, ","type":"text"},{"text":"HASH","type":"text","marks":[{"type":"strong"}]},{"text":" for even distribution without a natural key.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Constraint exclusion","type":"text","marks":[{"type":"strong"}]},{"text":": the planner prunes partitions through their ","type":"text"},{"text":"CHECK","type":"text","marks":[{"type":"code_inline"}]},{"text":" constraints; declarative partitioning (PG10+) creates them for you.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Prefer declarative partitioning or hypertables. Do NOT use table inheritance.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Limitations","type":"text","marks":[{"type":"strong"}]},{"text":": no global UNIQUE constraints—include the partition key in PK/UNIQUE. FKs from partitioned tables need PG11+, FKs referencing a partitioned table need PG12+; on older versions, use triggers.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE TABLE users (\n user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n email TEXT NOT NULL UNIQUE,\n name TEXT NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE UNIQUE INDEX ON users (LOWER(email));\nCREATE INDEX ON users (created_at);","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE TABLE orders (\n order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n user_id BIGINT NOT NULL REFERENCES users(user_id),\n status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),\n total NUMERIC(10,2) NOT NULL CHECK (total > 0),\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE INDEX ON orders (user_id);\nCREATE INDEX ON orders (created_at);","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- JSONB attributes with a generated, indexable scalar\nCREATE TABLE profiles (\n user_id BIGINT PRIMARY KEY REFERENCES users(user_id),\n attrs JSONB NOT NULL DEFAULT '{}',\n theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED\n);\nCREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Going deeper","type":"text"}]},{"type":"paragraph","content":[{"text":"references/details.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" holds the material this file only names:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"The full data-type catalog: TOAST storage, collations, arrays, ranges, network, geometric, text search, domains, composites, vectors.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Table types (","type":"text"},{"text":"TEMPORARY","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"UNLOGGED","type":"text","marks":[{"type":"code_inline"}]},{"text":") and row-level security.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Constraint and index notes, and partitioning DDL for RANGE, LIST, and HASH.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Workload patterns: update-heavy, insert-heavy, upsert design, safe schema evolution.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Generated columns and extensions (","type":"text"},{"text":"pg_trgm","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"citext","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"timescaledb","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"postgis","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"pgvector","type":"text","marks":[{"type":"code_inline"}]},{"text":", and more).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"JSONB indexing strategies, including ","type":"text"},{"text":"jsonb_path_ops","type":"text","marks":[{"type":"code_inline"}]},{"text":" and extracted B-tree columns.","type":"text"}]}]}]}]},"metadata":{"date":"2026-11-02","name":"postgresql-table-design","author":"@skillopedia","source":{"stars":42106,"repo_name":"agents","origin_url":"https://github.com/wshobson/agents/blob/HEAD/plugins/database-design/skills/postgresql-table-design/SKILL.md","repo_owner":"wshobson","body_sha256":"1e27b0fac6d88a710cd925b831608ca202c40ade4f4e285a8e027dcf2f9a44ea","cluster_key":"f815e1a1cd147ec4bb23fa84cd9a0599859f5de71801159e5ca2809efba32a8a","clean_bundle":{"format":"clean-skill-bundle-v1","source":"wshobson/agents/plugins/database-design/skills/postgresql-table-design/SKILL.md","text_bytes":12094,"source_commit":"7e2fb559cf4b824b27f1fec49fe88f7511066d26","skills_sh_rank":1240,"attachment_count":1,"text_attachments":1,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"plugins/database-design/skills/postgresql-table-design/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"data-analytics","import_tag":"skills-sh-top20k-2026-11","description":"Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features"}},"renderedAt":1793665983327}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.