Deterministic rules, documented in full.

Every foreign key constraint implies a join. When Postgres executes that join, it needs to find matching rows in the referencing table quickly. Without an index on the FK column, Postgres falls back to a sequential scan of the entire referencing table for every row in the referenced table—a classic nested loop that scales as O(n×m).

When Postgres sorts rows for ORDER BY, GROUP BY, or merge joins and the intermediate result exceeds work_mem, it spills to disk as a temporary file. Disk I/O is orders of magnitude slower than memory—a sort that spills is often 10-100x slower than one that fits in work_mem. Temp file spills also consume disk space and increase I/O pressure on all concurrent queries.

Hash joins build an in-memory hash table from the smaller of two relations being joined. When the hash table exceeds work_mem, Postgres splits it into multiple batches and reads both input relations multiple times from disk—a hash spill. Hash batches > 1 in EXPLAIN ANALYZE output indicates a spill occurred.

Postgres writes all dirty shared_buffers pages to disk at each checkpoint. Checkpoints happen on a schedule (checkpoint_timeout, default 5 min) or when WAL fills max_wal_size. Frequent checkpoints—especially those triggered by WAL fill (checkpoints_req) rather than by time—cause bursty I/O that slows all concurrent write activity and can saturate disk bandwidth.

The Postgres query planner uses column statistics (histograms, most-common-values, null fractions) from pg_statistic to estimate row counts and choose optimal query plans. Stale statistics cause the planner to generate bad plans—for example, choosing a sequential scan when an index would be faster, or choosing a nested loop join where a hash join would be better.

Postgres tracks every time an index is used via pg_stat_user_indexes.idx_scan. An index with zero scans since the last statistics reset is a candidate for removal—it is consuming disk space, slowing every INSERT/UPDATE/DELETE on the table, and increasing checkpoint write pressure without providing any read benefit.

Duplicate indexes are pairs where one index's key columns are a prefix of another on the same table. The shorter index is made redundant by the longer one for range scans, but both indexes are maintained on every write. This doubles (or more) write amplification and wastes storage.

Postgres uses MVCC (Multi-Version Concurrency Control) to serve reads without blocking writes. Every UPDATE creates a new row version; the old version becomes a dead tuple. VACUUM reclaims those dead tuples so the space can be reused. When VACUUM can't keep up with the write rate—due to long transactions, aggressive autovacuum thresholds, or high write volume—dead tuples accumulate and the table grows larger than its live data requires.

Autovacuum is Postgres's background maintenance daemon. It reclaims dead tuples, updates statistics, and prevents transaction ID wraparound. It should be running continuously for any active table. When autovacuum is disabled (via reloptions) or hasn't run in days despite accumulating dead tuples, the table is headed toward bloat and eventually XID wraparound—a condition where Postgres freezes all writes until an emergency VACUUM FREEZE completes.

Postgres allocates a backend process per connection. Each backend uses ~5–10 MB of memory and a file descriptor. At high connection counts, Postgres spends increasing time managing locks, shared memory, and backend overhead rather than executing queries. Above ~200 connections, performance typically degrades even without lock contention.

Postgres SERIAL and BIGSERIAL columns use sequences that increment monotonically. An INTEGER (int4) sequence maxes out at 2,147,483,647 (2.1 billion). A BIGINT (int8) sequence maxes out at 9.2 x 10^18. When a sequence is exhausted, any INSERT that triggers a nextval() fails with "integer out of range"—a hard outage.

Long-running transactions hold row-level locks, prevent VACUUM from reclaiming dead tuples behind the transaction's xmin horizon, and accumulate WAL that cannot be recycled until the transaction closes. A single stalled transaction can cause table bloat across every table touched by that transaction—even tables the transaction never wrote to.

A session in state 'idle in transaction' has an open transaction but is not executing any query. The application opened a BEGIN, ran some work, and then paused—perhaps waiting for user input, a network call, or a bug that leaked the connection without a COMMIT or ROLLBACK. The transaction holds all its acquired locks and pins the VACUUM xmin horizon for the entire duration.

Replication lag measures how far behind a standby server is from the primary. For streaming replication, insightral tracks write_lag, flush_lag, and replay_lag. For logical replication slots, it tracks the volume of WAL retained on disk waiting to be consumed. Both types of lag matter: streaming lag means reads from the replica are stale, while slot lag means the primary cannot recycle WAL, eventually filling the disk.

Lock contention occurs when one session holds a lock that another session needs before it can proceed. In Postgres, the most common form is a long-running query holding a row-level or table-level lock while DDL (ALTER TABLE, CREATE INDEX without CONCURRENTLY, VACUUM FULL) tries to acquire an AccessExclusiveLock. The DDL blocks, and all subsequent queries that need even a weaker lock queue behind it—creating a thundering-herd pile-up.