public-postgres-expert-base

PostgreSQL Knowledge Base

Source: Adapted from 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 of every unique/PK constraint.
• ATTACH PARTITION / DETACH PARTITION for maintenance.
• Partition pruning: queries automatically skip irrelevant partitions.

Extensions

• pg_trgm: trigram similarity for fuzzy search.
• pgvector: vector similarity search (AI/embeddings).
• pg_stat_statements: query performance analysis.
• uuid-ossp: UUID generation.
• postgis: geospatial data.

Connection Management

• Use connection pooling (PgBouncer) — PostgreSQL connections are expensive (~5-10 MB each).
• Pool sizing: start with 2 * CPU cores + 1.
• Use prepared_statements for parameterized queries.
• Set statement_timeout to prevent runaway queries.