design-postgres-tables

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 ; use 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…

)` 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### Do not use the following data types\n\n- DO NOT use `timestamp` (without time zone); DO use `timestamptz` instead.\n- DO NOT use `char(n)` or `varchar(n)`; DO use `text` instead.\n- DO NOT use `money` type; DO use `numeric` instead.\n- DO NOT use `timetz` type; DO use `timestamptz` instead.\n- DO NOT use `timestamptz(0)` or any other precision specification; DO use `timestamptz` instead\n- DO NOT use `serial` type; DO use `generated always as identity` instead.\n- DO NOT use `POINT`, `LINE`, `POLYGON`, `CIRCLE` built-in types, DO use `geometry` from postgis extension instead.\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 (user_id = current_user_id())`. Built-in user-based access control at the row level.\n\n## Constraints\n\n- **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index.\n- **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.\n- **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.\n- **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)`.\n- **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).\n\n## Indexing\n\n- **B-tree**: default for equality/range queries (`=`, `\u003c`, `>`, `BETWEEN`, `ORDER BY`)\n- **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.\n- **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` - includes non-key columns for index-only scans without visiting table.\n- **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.\n- **Expression**: for computed search keys (`CREATE INDEX ON tbl (LOWER(email))`). Expression must match exactly in WHERE clause: `WHERE LOWER(email) = '[email protected]'`.\n- **GIN**: JSONB containment/existence, arrays (`@>`, `?`), full-text search (`@@`)\n- **GiST**: ranges, geometry, exclusion constraints\n- **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`).\n\n## Partitioning\n\n- Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).\n- Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically\n- **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.\n- **LIST**: for discrete values (`PARTITION BY LIST (region)`). Example: `FOR VALUES IN ('us-east', 'us-west')`.\n- **HASH**: for even distribution when no natural key (`PARTITION BY HASH (user_id)`). Creates N partitions with modulus.\n- **Constraint exclusion**: requires `CHECK` constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).\n- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.\n- **Limitations**: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.\n\n## Special Considerations\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## 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```","attachment_filenames":[],"attachments":[],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"PostgreSQL Table Design","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. Premature denormalization creates maintenance burden.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"strong"}]},{"text":" everywhere it’s 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 integer values, ","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. Convention: use ","type":"text"},{"text":"snake_case","type":"text","marks":[{"type":"code_inline"}]},{"text":" for table/column names.","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). Example: inserting 999 into ","type":"text"},{"text":"NUMERIC(2,0)","type":"text","marks":[{"type":"code_inline"}]},{"text":" fails with error, unlike some 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 create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Heap storage","type":"text","marks":[{"type":"strong"}]},{"text":": no clustered PK by default (unlike SQL Server/MySQL InnoDB); ","type":"text"},{"text":"CLUSTER","type":"text","marks":[{"type":"code_inline"}]},{"text":" is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.","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":" preferred (","type":"text"},{"text":"GENERATED BY DEFAULT","type":"text","marks":[{"type":"code_inline"}]},{"text":" also fine); ","type":"text"},{"text":"UUID","type":"text","marks":[{"type":"code_inline"}]},{"text":" when merging/federating/used in a distributed system or for opaque IDs. Generate with ","type":"text"},{"text":"uuidv7()","type":"text","marks":[{"type":"code_inline"}]},{"text":" (preferred if using PG18+) or ","type":"text"},{"text":"gen_random_uuid()","type":"text","marks":[{"type":"code_inline"}]},{"text":" (if using an older PG version).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Integers","type":"text","marks":[{"type":"strong"}]},{"text":": prefer ","type":"text"},{"text":"BIGINT","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless storage space is critical; ","type":"text"},{"text":"INTEGER","type":"text","marks":[{"type":"code_inline"}]},{"text":" for smaller ranges; avoid ","type":"text"},{"text":"SMALLINT","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless constrained.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Floats","type":"text","marks":[{"type":"strong"}]},{"text":": prefer ","type":"text"},{"text":"DOUBLE PRECISION","type":"text","marks":[{"type":"code_inline"}]},{"text":" over ","type":"text"},{"text":"REAL","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless storage space is critical. Use ","type":"text"},{"text":"NUMERIC","type":"text","marks":[{"type":"code_inline"}]},{"text":" for exact decimal arithmetic.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Strings","type":"text","marks":[{"type":"strong"}]},{"text":": prefer ","type":"text"},{"text":"TEXT","type":"text","marks":[{"type":"code_inline"}]},{"text":"; if length limits needed, use ","type":"text"},{"text":"CHECK (LENGTH(col) \u003c= n)","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead of ","type":"text"},{"text":"VARCHAR(n)","type":"text","marks":[{"type":"code_inline"}]},{"text":"; avoid ","type":"text"},{"text":"CHAR(n)","type":"text","marks":[{"type":"code_inline"}]},{"text":". Use ","type":"text"},{"text":"BYTEA","type":"text","marks":[{"type":"code_inline"}]},{"text":" for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: ","type":"text"},{"text":"PLAIN","type":"text","marks":[{"type":"code_inline"}]},{"text":" (no TOAST), ","type":"text"},{"text":"EXTENDED","type":"text","marks":[{"type":"code_inline"}]},{"text":" (compress + out-of-line), ","type":"text"},{"text":"EXTERNAL","type":"text","marks":[{"type":"code_inline"}]},{"text":" (out-of-line, no compress), ","type":"text"},{"text":"MAIN","type":"text","marks":[{"type":"code_inline"}]},{"text":" (compress, keep in-line if possible). Default ","type":"text"},{"text":"EXTENDED","type":"text","marks":[{"type":"code_inline"}]},{"text":" usually optimal. Control with ","type":"text"},{"text":"ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"ALTER TABLE tbl SET (toast_tuple_target = 4096)","type":"text","marks":[{"type":"code_inline"}]},{"text":" for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on ","type":"text"},{"text":"LOWER(col)","type":"text","marks":[{"type":"code_inline"}]},{"text":" (preferred unless column needs case-insensitive PK/FK/UNIQUE) or ","type":"text"},{"text":"CITEXT","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Money","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"NUMERIC(p,s)","type":"text","marks":[{"type":"code_inline"}]},{"text":" (never float).","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":" for timestamps; ","type":"text"},{"text":"DATE","type":"text","marks":[{"type":"code_inline"}]},{"text":" for date-only; ","type":"text"},{"text":"INTERVAL","type":"text","marks":[{"type":"code_inline"}]},{"text":" for durations. Avoid ","type":"text"},{"text":"TIMESTAMP","type":"text","marks":[{"type":"code_inline"}]},{"text":" (without timezone). Use ","type":"text"},{"text":"now()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for transaction start time, ","type":"text"},{"text":"clock_timestamp()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for current wall-clock time.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Booleans","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"BOOLEAN","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" constraint unless tri-state values are 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":" 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.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Arrays","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"TEXT[]","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"INTEGER[]","type":"text","marks":[{"type":"code_inline"}]},{"text":", etc. Use for ordered lists where you query elements. Index with ","type":"text"},{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":" for containment (","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"\u003c@","type":"text","marks":[{"type":"code_inline"}]},{"text":") and overlap (","type":"text"},{"text":"&&","type":"text","marks":[{"type":"code_inline"}]},{"text":") queries. Access: ","type":"text"},{"text":"arr[1]","type":"text","marks":[{"type":"code_inline"}]},{"text":" (1-indexed), ","type":"text"},{"text":"arr[1:3]","type":"text","marks":[{"type":"code_inline"}]},{"text":" (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: ","type":"text"},{"text":"'{val1,val2}'","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"ARRAY[val1,val2]","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Range types","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"daterange","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"numrange","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"tstzrange","type":"text","marks":[{"type":"code_inline"}]},{"text":" for intervals. Support overlap (","type":"text"},{"text":"&&","type":"text","marks":[{"type":"code_inline"}]},{"text":"), containment (","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":"), operators. Index with ","type":"text"},{"text":"GiST","type":"text","marks":[{"type":"strong"}]},{"text":". Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer ","type":"text"},{"text":"[)","type":"text","marks":[{"type":"code_inline"}]},{"text":" (inclusive/exclusive) by default.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Network types","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"INET","type":"text","marks":[{"type":"code_inline"}]},{"text":" for IP addresses, ","type":"text"},{"text":"CIDR","type":"text","marks":[{"type":"code_inline"}]},{"text":" for network ranges, ","type":"text"},{"text":"MACADDR","type":"text","marks":[{"type":"code_inline"}]},{"text":" for MAC addresses. Support network operators (","type":"text"},{"text":"\u003c\u003c","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":">>","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"&&","type":"text","marks":[{"type":"code_inline"}]},{"text":").","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Geometric types","type":"text","marks":[{"type":"strong"}]},{"text":": avoid ","type":"text"},{"text":"POINT","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"LINE","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"POLYGON","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"CIRCLE","type":"text","marks":[{"type":"code_inline"}]},{"text":". Index with ","type":"text"},{"text":"GiST","type":"text","marks":[{"type":"strong"}]},{"text":". Consider ","type":"text"},{"text":"PostGIS","type":"text","marks":[{"type":"strong"}]},{"text":" for spatial features.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Text search","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"TSVECTOR","type":"text","marks":[{"type":"code_inline"}]},{"text":" for full-text search documents, ","type":"text"},{"text":"TSQUERY","type":"text","marks":[{"type":"code_inline"}]},{"text":" for search queries. Index ","type":"text"},{"text":"tsvector","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":". Always specify language: ","type":"text"},{"text":"to_tsvector('english', col)","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"to_tsquery('english', 'query')","type":"text","marks":[{"type":"code_inline"}]},{"text":". Never use single-argument versions. This applies to both index expressions and queries.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Domain types","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+

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

)","type":"text","marks":[{"type":"code_inline"}]},{"text":" for reusable custom types with validation. Enforces constraints across tables.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Composite types","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)","type":"text","marks":[{"type":"code_inline"}]},{"text":" for structured data within columns. Access with ","type":"text"},{"text":"(col).field","type":"text","marks":[{"type":"code_inline"}]},{"text":" syntax.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"JSONB","type":"text","marks":[{"type":"strong"}]},{"text":": preferred over JSON; index with ","type":"text"},{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":". Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Vector types","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"vector","type":"text","marks":[{"type":"code_inline"}]},{"text":" type by ","type":"text"},{"text":"pgvector","type":"text","marks":[{"type":"code_inline"}]},{"text":" for vector similarity search for embeddings.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Do not use the following data types","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"timestamp","type":"text","marks":[{"type":"code_inline"}]},{"text":" (without time zone); DO use ","type":"text"},{"text":"timestamptz","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"char(n)","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"varchar(n)","type":"text","marks":[{"type":"code_inline"}]},{"text":"; DO use ","type":"text"},{"text":"text","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"money","type":"text","marks":[{"type":"code_inline"}]},{"text":" type; DO use ","type":"text"},{"text":"numeric","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"timetz","type":"text","marks":[{"type":"code_inline"}]},{"text":" type; DO use ","type":"text"},{"text":"timestamptz","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"timestamptz(0)","type":"text","marks":[{"type":"code_inline"}]},{"text":" or any other precision specification; DO use ","type":"text"},{"text":"timestamptz","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"serial","type":"text","marks":[{"type":"code_inline"}]},{"text":" type; DO use ","type":"text"},{"text":"generated always as identity","type":"text","marks":[{"type":"code_inline"}]},{"text":" instead.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOT use ","type":"text"},{"text":"POINT","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"LINE","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"POLYGON","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"CIRCLE","type":"text","marks":[{"type":"code_inline"}]},{"text":" built-in types, DO use ","type":"text"},{"text":"geometry","type":"text","marks":[{"type":"code_inline"}]},{"text":" from postgis extension instead.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Table Types","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Regular","type":"text","marks":[{"type":"strong"}]},{"text":": default; fully durable, logged.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"TEMPORARY","type":"text","marks":[{"type":"strong"}]},{"text":": session-scoped, auto-dropped, not logged. Faster for scratch work.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"UNLOGGED","type":"text","marks":[{"type":"strong"}]},{"text":": persistent but not crash-safe. Faster writes; good for caches/staging.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Row-Level Security","type":"text"}]},{"type":"paragraph","content":[{"text":"Enable with ","type":"text"},{"text":"ALTER TABLE tbl ENABLE ROW LEVEL SECURITY","type":"text","marks":[{"type":"code_inline"}]},{"text":". Create policies: ","type":"text"},{"text":"CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())","type":"text","marks":[{"type":"code_inline"}]},{"text":". Built-in user-based access control at the row level.","type":"text"}]},{"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":" action (","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":"). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use ","type":"text"},{"text":"DEFERRABLE INITIALLY DEFERRED","type":"text","marks":[{"type":"code_inline"}]},{"text":" for circular FK dependencies checked at transaction end.","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+). Standard behavior: ","type":"text"},{"text":"(1, NULL)","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"(1, NULL)","type":"text","marks":[{"type":"code_inline"}]},{"text":" are allowed. With ","type":"text"},{"text":"NULLS NOT DISTINCT","type":"text","marks":[{"type":"code_inline"}]},{"text":": only one ","type":"text"},{"text":"(1, NULL)","type":"text","marks":[{"type":"code_inline"}]},{"text":" allowed. Prefer ","type":"text"},{"text":"NULLS NOT DISTINCT","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless you specifically need duplicate NULLs.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CHECK","type":"text","marks":[{"type":"strong"}]},{"text":": row-local constraints; NULL values pass the check (three-valued logic). Example: ","type":"text"},{"text":"CHECK (price > 0)","type":"text","marks":[{"type":"code_inline"}]},{"text":" allows NULL prices. Combine with ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" to enforce: ","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 overlapping values using operators. ","type":"text"},{"text":"EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)","type":"text","marks":[{"type":"code_inline"}]},{"text":" prevents double-booking rooms. Requires appropriate index type (often GiST).","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 queries (","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":": order matters—index used if equality on leftmost prefix (","type":"text"},{"text":"WHERE a = ? AND b > ?","type":"text","marks":[{"type":"code_inline"}]},{"text":" uses index on ","type":"text"},{"text":"(a,b)","type":"text","marks":[{"type":"code_inline"}]},{"text":", but ","type":"text"},{"text":"WHERE b = ?","type":"text","marks":[{"type":"code_inline"}]},{"text":" does not). Put most selective/frequently filtered 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":" - includes non-key columns for index-only scans without visiting table.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partial","type":"text","marks":[{"type":"strong"}]},{"text":": for hot subsets (","type":"text"},{"text":"WHERE status = 'active'","type":"text","marks":[{"type":"code_inline"}]},{"text":" → ","type":"text"},{"text":"CREATE INDEX ON tbl (user_id) WHERE status = 'active'","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Any query with ","type":"text"},{"text":"status = 'active'","type":"text","marks":[{"type":"code_inline"}]},{"text":" can use this index.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Expression","type":"text","marks":[{"type":"strong"}]},{"text":": for computed search keys (","type":"text"},{"text":"CREATE INDEX ON tbl (LOWER(email))","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Expression must match exactly in WHERE clause: ","type":"text"},{"text":"WHERE LOWER(email) = '[email protected]'","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":": JSONB containment/existence, arrays (","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"?","type":"text","marks":[{"type":"code_inline"}]},{"text":"), full-text search (","type":"text"},{"text":"@@","type":"text","marks":[{"type":"code_inline"}]},{"text":")","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"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":": very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after ","type":"text"},{"text":"CLUSTER","type":"text","marks":[{"type":"code_inline"}]},{"text":").","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 very large tables (>100M rows) where queries consistently filter on partition key (often time/date).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"RANGE","type":"text","marks":[{"type":"strong"}]},{"text":": common for time-series (","type":"text"},{"text":"PARTITION BY RANGE (created_at)","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Create partitions: ","type":"text"},{"text":"CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')","type":"text","marks":[{"type":"code_inline"}]},{"text":". ","type":"text"},{"text":"TimescaleDB","type":"text","marks":[{"type":"strong"}]},{"text":" automates time-based or ID-based partitioning with retention policies and compression.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"LIST","type":"text","marks":[{"type":"strong"}]},{"text":": for discrete values (","type":"text"},{"text":"PARTITION BY LIST (region)","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Example: ","type":"text"},{"text":"FOR VALUES IN ('us-east', 'us-west')","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"HASH","type":"text","marks":[{"type":"strong"}]},{"text":": for even distribution when no natural key (","type":"text"},{"text":"PARTITION BY HASH (user_id)","type":"text","marks":[{"type":"code_inline"}]},{"text":"). Creates N partitions with modulus.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Constraint exclusion","type":"text","marks":[{"type":"strong"}]},{"text":": requires ","type":"text"},{"text":"CHECK","type":"text","marks":[{"type":"code_inline"}]},{"text":" constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).","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 partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Special Considerations","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Update-Heavy Tables","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Separate hot/cold columns","type":"text","marks":[{"type":"strong"}]},{"text":"—put frequently updated columns in separate table to minimize bloat.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text","marks":[{"type":"strong"}]},{"text":"fillfactor=90","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":" to leave space for HOT updates that avoid index maintenance.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid updating indexed columns","type":"text","marks":[{"type":"strong"}]},{"text":"—prevents beneficial HOT updates.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partition by update patterns","type":"text","marks":[{"type":"strong"}]},{"text":"—separate frequently updated rows in a different partition from stable data.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Insert-Heavy Workloads","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Minimize indexes","type":"text","marks":[{"type":"strong"}]},{"text":"—only create what you query; every index slows inserts.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text","marks":[{"type":"strong"}]},{"text":"COPY","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":" or multi-row ","type":"text","marks":[{"type":"strong"}]},{"text":"INSERT","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":" instead of single-row inserts.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"UNLOGGED tables","type":"text","marks":[{"type":"strong"}]},{"text":" for rebuildable staging data—much faster writes.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Defer index creation","type":"text","marks":[{"type":"strong"}]},{"text":" for bulk loads—>drop index, load data, recreate indexes.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partition by time/hash","type":"text","marks":[{"type":"strong"}]},{"text":" to distribute load. ","type":"text"},{"text":"TimescaleDB","type":"text","marks":[{"type":"strong"}]},{"text":" automates partitioning and compression of insert-heavy data.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use a natural key for primary key","type":"text","marks":[{"type":"strong"}]},{"text":" such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"If you do need a surrogate key, ","type":"text"},{"text":"Prefer ","type":"text","marks":[{"type":"strong"}]},{"text":"BIGINT GENERATED ALWAYS AS IDENTITY","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":" over ","type":"text","marks":[{"type":"strong"}]},{"text":"UUID","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":".","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Upsert-Friendly Design","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Requires UNIQUE index","type":"text","marks":[{"type":"strong"}]},{"text":" on conflict target columns—","type":"text"},{"text":"ON CONFLICT (col1, col2)","type":"text","marks":[{"type":"code_inline"}]},{"text":" needs exact matching unique index (partial indexes don't work).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text","marks":[{"type":"strong"}]},{"text":"EXCLUDED.column","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":" to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"DO NOTHING","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":" faster","type":"text","marks":[{"type":"strong"}]},{"text":" than ","type":"text"},{"text":"DO UPDATE","type":"text","marks":[{"type":"code_inline"}]},{"text":" when no actual update needed.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Safe Schema Evolution","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Transactional DDL","type":"text","marks":[{"type":"strong"}]},{"text":": most DDL operations can run in transactions and be rolled back—","type":"text"},{"text":"BEGIN; ALTER TABLE...; ROLLBACK;","type":"text","marks":[{"type":"code_inline"}]},{"text":" for safe testing.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Concurrent index creation","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE INDEX CONCURRENTLY","type":"text","marks":[{"type":"code_inline"}]},{"text":" avoids blocking writes but can't run in transactions.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Volatile defaults cause rewrites","type":"text","marks":[{"type":"strong"}]},{"text":": adding ","type":"text"},{"text":"NOT NULL","type":"text","marks":[{"type":"code_inline"}]},{"text":" columns with volatile defaults (e.g., ","type":"text"},{"text":"now()","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"gen_random_uuid()","type":"text","marks":[{"type":"code_inline"}]},{"text":") rewrites entire table. Non-volatile defaults are fast.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Drop constraints before columns","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"ALTER TABLE DROP CONSTRAINT","type":"text","marks":[{"type":"code_inline"}]},{"text":" then ","type":"text"},{"text":"DROP COLUMN","type":"text","marks":[{"type":"code_inline"}]},{"text":" to avoid dependency issues.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Function signature changes","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"CREATE OR REPLACE","type":"text","marks":[{"type":"code_inline"}]},{"text":" with different arguments creates overloads, not replacements. DROP old version if no overload desired.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Generated Columns","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"... GENERATED ALWAYS AS (\u003cexpr>) STORED","type":"text","marks":[{"type":"code_inline"}]},{"text":" for computed, indexable fields. PG18+ adds ","type":"text"},{"text":"VIRTUAL","type":"text","marks":[{"type":"code_inline"}]},{"text":" columns (computed on read, not stored).","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Extensions","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"pgcrypto","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": ","type":"text"},{"text":"crypt()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for password hashing.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"uuid-ossp","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": alternative UUID functions; prefer ","type":"text"},{"text":"pgcrypto","type":"text","marks":[{"type":"code_inline"}]},{"text":" for new projects.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"pg_trgm","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": fuzzy text search with ","type":"text"},{"text":"%","type":"text","marks":[{"type":"code_inline"}]},{"text":" operator, ","type":"text"},{"text":"similarity()","type":"text","marks":[{"type":"code_inline"}]},{"text":" function. Index with GIN for ","type":"text"},{"text":"LIKE '%pattern%'","type":"text","marks":[{"type":"code_inline"}]},{"text":" acceleration.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"citext","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": case-insensitive text type. Prefer expression indexes on ","type":"text"},{"text":"LOWER(col)","type":"text","marks":[{"type":"code_inline"}]},{"text":" unless you need case-insensitive constraints.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"btree_gin","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":"/","type":"text","marks":[{"type":"strong"}]},{"text":"btree_gist","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"hstore","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": key-value pairs; mostly superseded by JSONB but useful for simple string mappings.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"timescaledb","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": essential for time-series—automated partitioning, retention, compression, continuous aggregates.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"postgis","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": comprehensive geospatial support beyond basic geometric types—essential for location-based applications.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"pgvector","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": vector similarity search for embeddings.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"pgaudit","type":"text","marks":[{"type":"code_inline"},{"type":"strong"}]},{"text":": audit logging for all database activity.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"JSONB Guidance","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Prefer ","type":"text"},{"text":"JSONB","type":"text","marks":[{"type":"code_inline"}]},{"text":" with ","type":"text"},{"text":"GIN","type":"text","marks":[{"type":"strong"}]},{"text":" index.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Default: ","type":"text"},{"text":"CREATE INDEX ON tbl USING GIN (jsonb_col);","type":"text","marks":[{"type":"code_inline"}]},{"text":" → accelerates:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Containment","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"jsonb_col @> '{\"k\":\"v\"}'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Key existence","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"jsonb_col ? 'k'","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"any/all keys","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"?\\|","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"?&","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Path containment","type":"text","marks":[{"type":"strong"}]},{"text":" on nested docs","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Disjunction","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"jsonb_col @> ANY(ARRAY['{\"status\":\"active\"}', '{\"status\":\"pending\"}'])","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Heavy ","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":" workloads: consider opclass ","type":"text"},{"text":"jsonb_path_ops","type":"text","marks":[{"type":"code_inline"}]},{"text":" for smaller/faster containment-only indexes:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Trade-off","type":"text","marks":[{"type":"strong"}]},{"text":": loses support for key existence (","type":"text"},{"text":"?","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"?|","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"?&","type":"text","marks":[{"type":"code_inline"}]},{"text":") queries—only supports containment (","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":")","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"CREATE INDEX ON tbl (price);","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Prefer queries like ","type":"text"},{"text":"WHERE price BETWEEN 100 AND 500","type":"text","marks":[{"type":"code_inline"}]},{"text":" (uses B-tree) over ","type":"text"},{"text":"WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500","type":"text","marks":[{"type":"code_inline"}]},{"text":" without index.","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Arrays inside JSONB: use GIN + ","type":"text"},{"text":"@>","type":"text","marks":[{"type":"code_inline"}]},{"text":" for containment (e.g., tags). Consider ","type":"text"},{"text":"jsonb_path_ops","type":"text","marks":[{"type":"code_inline"}]},{"text":" if only doing containment.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Keep core relations in tables; use JSONB for optional/variable attributes.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use constraints to limit allowed JSONB values in a column e.g. ","type":"text"},{"text":"config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Users","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":"heading","attrs":{"level":3},"content":[{"text":"Orders","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":"heading","attrs":{"level":3},"content":[{"text":"JSONB","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE 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"}]}]},"metadata":{"date":"2026-11-02","name":"design-postgres-tables","author":"@skillopedia","source":{"stars":1881,"repo_name":"pg-aiguide","origin_url":"https://github.com/timescale/pg-aiguide/blob/HEAD/skills/design-postgres-tables/SKILL.md","repo_owner":"timescale","body_sha256":"a4822cefdc283979e8a75f312780cb00aad83fb03b994f72e1675dc04a8eb252","cluster_key":"d35100a362ed77db336029cab09ecce48035aa7ffa8f6f6882e825181a809e6e","clean_bundle":{"format":"clean-skill-bundle-v1","source":"timescale/pg-aiguide/skills/design-postgres-tables/SKILL.md","text_bytes":0,"source_commit":"5d3b76b5c986693b30753828b404aa66ba34c31a","skills_sh_rank":17133,"attachment_count":0,"text_attachments":0,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/design-postgres-tables/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"license":"Apache-2.0","version":"v1","category":"data-analytics","metadata":{"author":"tigerdata"},"import_tag":"skills-sh-top20k-2026-11","description":"Use this skill for general PostgreSQL table design.\n\n**Trigger when user asks to:**\n- Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones.\n- Choose data types, constraints, or indexes for PostgreSQL\n- Create user tables, order tables, reference tables, or JSONB schemas\n- Understand PostgreSQL best practices for normalization, constraints, or indexing\n- Design update-heavy, upsert-heavy, or OLTP-style tables\n\n\n**Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security\n\nComprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.\n"}},"renderedAt":1793661401491}

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