public-postgres-expert-baselisted

# PostgreSQL Knowledge Base > **Source**: Adapted from [planetscale/database-skills](https://github.com/planetscale/database-skills) (PostgreSQL skill). ## Schema Design - Prefer `BIGSERIAL` or `BIGINT GENERATED ALWAYS AS IDENTITY` for PKs. - Use `TIMESTAMPTZ` (with timezone) over `TIMESTAMP`. - `TEXT` and `VARCHAR` have similar performance in PostgreSQL — prefer `TEXT` unless length constraint is meaningful. - Use `NUMERIC` for money, never `FLOAT`/`REAL`. - `JSONB` (binary) over `JSON` (text) for structured data. ## Indexing | Type | Use for | |------|---------| | B-Tree (default) | Equality, range, sorting | | GIN | JSONB, fulltext, arrays, containment | | GiST | Geometry, range types, nearest-neighbor | | BRIN | Sequential data (timestamps, IDs) on large tables | | Hash | Equality only (rarely needed) | - Partial indexes: `CREATE INDEX ... WHERE condition` — index only relevant rows. - Expression indexes: `CREATE INDEX ... ON t (lower(name))`. - Covering indexes: `INCLUDE (col1, col2)` for index-only scans. - `CONCURRENTLY` for creating indexes without blocking writes. ## JSONB - Store structured, semi-structured, or variable-schema data. - Index with GIN: `CREATE INDEX ... USING GIN (data jsonb_path_ops)`. - Query: `data->>'key'` (text), `data->'key'` (jsonb), `data @> '{"key": "val"}'` (containment). - Generated columns for frequently queried JSONB paths. ## Partitioning - Declarative partitioning (range, list, hash) for large tables. - Partition key must be part