postgresql — Skillopedia

PostgreSQL Table Design Use this skill when - Designing a schema for PostgreSQL - Selecting data types and constraints - Planning indexes, partitions, or RLS policies - Reviewing tables for scale and maintainability Do not use this skill when - You are targeting a non-PostgreSQL database - You only need query tuning without schema changes - You require a DB-agnostic modeling guide Instructions 1. Capture entities, access patterns, and scale targets (rows, QPS, retention). 2. Choose data types and constraints that enforce invariants. 3. Add indexes for real query paths and validate with . 4. P…

)` 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\n### Do not use the following data types\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\n\n## Table Types\n\n- **Regular**: default; fully durable, logged.\n- **TEMPORARY**: normally session-scoped and dropped at session end; ON COMMIT can alter lifecycle, and data is not WAL-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())`. The `current_user_id()` function is an application-specific placeholder, not built in. Define a trusted identity function, test policies as non-owner roles and consider owner/BYPASSRLS bypass and FORCE ROW LEVEL SECURITY.\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)`, while a predicate only on b may still use a scan or version-dependent skip scan; inspect the actual plan). 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 with heap visits avoided only where MVCC visibility permits.\n- **Partial**: for hot subsets (`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`). The planner must prove the query predicate implies the index predicate; generic parameterized plans and cost choices can prevent its use.\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- **Partition pruning** uses declarative partition bounds and can occur at planning/execution time. Constraint exclusion uses CHECK constraints and is a separate mechanism.\n- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.\n- **Limitations**: no global UNIQUE constraints—include partition key in PK/UNIQUE. Modern PostgreSQL supports foreign keys on partitioned tables; check the target version and constraint rules, rather than replacing integrity with ad hoc 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 unique indexes can be inferred when the conflict target includes a compatible WHERE predicate).\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., `gen_random_uuid()`; `now()` is STABLE, not VOLATILE) 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—also supports supported JSONPath operators `@?` and `@@`, but not key-existence operators\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\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\n## Limitations\n\nDesign defaults are tradeoffs, not universal type bans: timestamp without time zone can model deliberate wall times and varchar can enforce length. Indexes, partitioning and extensions require workload/version validation, not fixed row thresholds or mandatory vendor extensions. pgAudit logs depend on configuration and are not a guarantee of all activity. Email regex examples are deliberately incomplete; do not use them to prove deliverability. Rehearse migrations with lock budgets and backups; rollback does not undo every external effect.","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":"Use this skill when","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Designing a schema for PostgreSQL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Selecting data types and constraints","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Planning indexes, partitions, or RLS policies","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Reviewing tables for scale and maintainability","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Do not use this skill when","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You are targeting a non-PostgreSQL database","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You only need query tuning without schema changes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"You require a DB-agnostic modeling guide","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Instructions","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Capture entities, access patterns, and scale targets (rows, QPS, retention).","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Choose data types and constraints that enforce invariants.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Add indexes for real query paths and validate with ","type":"text"},{"text":"EXPLAIN","type":"text","marks":[{"type":"code_inline"}]},{"text":".","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Plan partitioning or RLS where required by scale or access control.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Review migration impact and apply changes safely.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Safety","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid destructive DDL on production without backups and a rollback plan.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use migrations and staging validation before applying schema changes.","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 referencing FK columns. Assess indexes for joins and parent updates/deletes against write costs.","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No silent coercions","type":"text","marks":[{"type":"strong"}]},{"text":": some length/precision overflows error out, while numeric scale coercion can round and explicit character casts can truncate. 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. Heap physical order is not guaranteed and changes with updates and maintenance; query order requires ORDER BY.","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. TOAST decisions depend on total tuple size, compressibility and storage settings, not an unconditional per-value 2KB threshold. 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":": ","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":" for 2D spatial data. Index with ","type":"text"},{"text":"GiST","type":"text","marks":[{"type":"strong"}]},{"text":". Consider ","type":"text"},{"text":"PostGIS","type":"text","marks":[{"type":"strong"}]},{"text":" for advanced 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":"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":": normally session-scoped and dropped at session end; ON COMMIT can alter lifecycle, and data is not WAL-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":". The ","type":"text"},{"text":"current_user_id()","type":"text","marks":[{"type":"code_inline"}]},{"text":" function is an application-specific placeholder, not built in. Define a trusted identity function, test policies as non-owner roles and consider owner/BYPASSRLS bypass and FORCE ROW LEVEL SECURITY.","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":", while a predicate only on b may still use a scan or version-dependent skip scan; inspect the actual plan). 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 with heap visits avoided only where MVCC visibility permits.","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":"). The planner must prove the query predicate implies the index predicate; generic parameterized plans and cost choices can prevent its use.","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":"Partition pruning","type":"text","marks":[{"type":"strong"}]},{"text":" uses declarative partition bounds and can occur at planning/execution time. Constraint exclusion uses CHECK constraints and is a separate mechanism.","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. Modern PostgreSQL supports foreign keys on partitioned tables; check the target version and constraint rules, rather than replacing integrity with ad hoc 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 unique indexes can be inferred when the conflict target includes a compatible WHERE predicate).","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":"gen_random_uuid()","type":"text","marks":[{"type":"code_inline"}]},{"text":"; ","type":"text"},{"text":"now()","type":"text","marks":[{"type":"code_inline"}]},{"text":" is STABLE, not VOLATILE) 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—also supports supported JSONPath operators ","type":"text"},{"text":"@?","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"@@","type":"text","marks":[{"type":"code_inline"}]},{"text":", but not key-existence operators","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"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Limitations","type":"text"}]},{"type":"paragraph","content":[{"text":"Design defaults are tradeoffs, not universal type bans: timestamp without time zone can model deliberate wall times and varchar can enforce length. Indexes, partitioning and extensions require workload/version validation, not fixed row thresholds or mandatory vendor extensions. pgAudit logs depend on configuration and are not a guarantee of all activity. Email regex examples are deliberately incomplete; do not use them to prove deliverability. Rehearse migrations with lock budgets and backups; rollback does not undo every external effect.","type":"text"}]}]},"metadata":{"date":"2026-11-02","name":"postgresql","risk":"critical","author":"@skillopedia","source":{"stars":49040,"repo_name":"agentic-awesome-skills","origin_url":"https://github.com/sickn33/agentic-awesome-skills/blob/HEAD/skills/postgresql/SKILL.md","repo_owner":"sickn33","body_sha256":"05e7be9be59ff37140c25b7640879c1026159fcc4dc2133b3b8bf87b93e16474","cluster_key":"20fa52880b3a346d615389f2be3aaf4ee99d79252acc5a0ade837e2362023479","clean_bundle":{"format":"clean-skill-bundle-v1","source":"sickn33/agentic-awesome-skills/skills/postgresql/SKILL.md","text_bytes":0,"source_commit":"b4bd5a153dd1e0a66789b6c971f731cdcf492a46","skills_sh_rank":16223,"attachment_count":0,"text_attachments":0,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/postgresql/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","date_added":"2026-02-27","import_tag":"skills-sh-top20k-2026-11","description":"Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features"}},"renderedAt":1793661509488}

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