PostgreSQL — Core

Indexing — the highest-leverage skill

• Index every foreign key, and any column used in WHERE, JOIN, ORDER BY, or GROUP BY.
• Composite index column order matters. An index on (a, b) serves queries filtering on a or a + b, but not b alone. Put the most selective / most-equality-filtered column first.
• Partial indexes for skewed predicates: CREATE INDEX ... WHERE status = 'active' is smaller and faster when you almost always query active rows.
• Covering indexes with INCLUDE let an index-only scan return columns without touching the heap.
• Indexes cost write throughput and disk. Don't index blindly — index for the queries you actually run.

Read EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

• Seq Scan on a large table in a hot path = missing index (usually).
• Watch for a big gap between estimated and actual rows → stale statistics; run ANALYZE.
• Nested Loop over many rows is often a sign the planner chose wrong or an index is missing.

Transactions & isolation

• Default isolation is READ COMMITTED. Use REPEATABLE READ or SERIALIZABLE when you need consistent multi-statement reads, and be ready to retry on serialization failures.
• Keep transactions short. A transaction held open across a slow external call holds locks and bloats the DB.

Locking & concurrency

• SELECT ... FOR UPDATE to lock rows you're about to modify (e.g. decrementing inventory).
• SELECT ... FOR UPDATE SKIP LOCKED is the classic pattern for a Postgres-backed job queue: each worker grabs the next unlocked row.
• Beware deadlocks from inconsistent lock ordering — always acquire locks in the same order across code paths.

Data modeling

• Use the right types: timestamptz (never naive timestamps), numeric for money (never float), uuid for external IDs, jsonb for flexible/semi-structured data.
• jsonb is powerful but not a schema replacement — index specific paths with expression or GIN indexes if you query into it.
• Enforce invariants in the DB: NOT NULL, CHECK, UNIQUE, FKs. The database is your last line of defense against bad data.