postgresql

Start by capturing entities, access patterns, and scale targets, then choose types like BIGINT GENERATED ALWAYS AS IDENTITY for primary keys, TIMESTAMPTZ for timestamps, and TEXT for strings. Add NOT NULL and CHECK constraints, then create indexes only for actual query paths and validate with EXPLAIN.

Prefer BIGINT GENERATED ALWAYS AS IDENTITY for most tables. Use UUID only when global uniqueness or opaque IDs are needed, generating them with gen_random_uuid() or uuidv7() on PG18+. Avoid the legacy serial type.

No, PostgreSQL does not auto-index foreign key columns. You must manually add indexes on referencing columns to speed up joins and prevent locking issues during parent table deletes or updates.

Keep core relational data in typed columns and use JSONB only for optional or semi-structured attributes. Index JSONB with GIN for containment and key-existence queries, and extract frequently filtered scalar fields into generated columns with B-tree indexes.

Partition tables exceeding roughly 100 million rows when queries consistently filter on the partition key, typically a time or date column. Use declarative RANGE, LIST, or HASH partitioning, and remember that unique constraints must include the partition key.

Adding a NOT NULL column with a volatile default like now() or gen_random_uuid() forces a full table rewrite. Use non-volatile defaults for fast metadata-only changes, or add the column nullable and backfill values separately.