PostgreSQL with Django

Key features

• B-tree is the default (CREATE INDEX ON table (column) — no USING clause needed) — correct for the vast majority of equality/range queries
• GIN (Generalized Inverted Index) is for composite/multi-valued data — arrays, JSONB, full-text search — answering "does this contain X" queries
• GiST (Generalized Search Tree) supports geometric data and nearest-neighbor (ORDER BY ... <-> ...) queries
• BRIN (Block Range Index) is a tiny, cheap index for huge tables whose column values correlate with physical row order (e.g. a monotonically increasing id or timestamp)
• Hash indexes support only equality (=) — narrower than B-tree, rarely the right choice since B-tree already handles equality plus everything else

Command

CREATE INDEX idx_name ON table_name (column_name);            -- B-tree
CREATE INDEX idx_name ON table_name USING GIN (jsonb_column);  -- GIN

Key features

• Read Committed is PostgreSQL's default — each statement (not the whole transaction) sees data committed as of when that statement began
• Repeatable Read gives the whole transaction one consistent snapshot from its start — prevents non-repeatable reads, but concurrent conflicting writes raise a serialization error
• Serializable is the strictest — behaves as if every transaction ran one at a time, detecting even indirect read/write conflicts between concurrent transactions
• Repeatable Read and Serializable BOTH require the application to catch a serialization failure and retry the transaction — this is expected, normal behavior at those levels, not a bug
• PostgreSQL never allows dirty reads (reading another transaction's uncommitted changes) at ANY isolation level, even though the SQL standard's loosest level (Read Uncommitted) technically permits it

Command

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- statements here
COMMIT;

Key features

• UPDATE, DELETE, and SELECT ... FOR UPDATE all take row-level locks automatically — the calling code does not opt in explicitly for ordinary writes
• A deadlock is a CIRCULAR wait: A holds what B wants, B holds what A wants — neither can proceed without the other releasing first
• PostgreSQL automatically detects deadlocks and aborts one of the involved transactions so the other(s) can continue — the aborted transaction receives an error and must be retried
• Which transaction gets aborted in a deadlock is difficult to predict and explicitly should not be relied upon, per PostgreSQL's own documentation
• The documented best defense is prevention: ensure every code path acquires locks on multiple objects in the SAME, consistent order — if that can't be guaranteed, retry transactions that fail due to deadlock

Command

-- always lock/update rows in the SAME order across every code path
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);

Key features

• EXPLAIN shows estimated cost/rows without executing the query — safe to run on anything, including a query that would modify data (though the modification would still occur unless wrapped in a ROLLBACK)
• EXPLAIN ANALYZE actually executes the query and reports real measured numbers — use inside a transaction you intend to ROLLBACK for a modifying query, to see the plan without committing the change
• A Seq Scan on a large table, where a WHERE clause could plausibly use an index instead, is one of the most common signals of a missing or unused index
• "Rows Removed by Filter" being large relative to the final result means the database scanned far more rows than it needed to — the filtering condition should ideally be handled by an index instead
• A large gap between "estimated rows" and "actual rows" (visible only in EXPLAIN ANALYZE) usually means table statistics are stale — running ANALYZE on the table often fixes the planner's next decision

Command

EXPLAIN SELECT ...;           -- estimated plan only, safe, doesn't execute
EXPLAIN ANALYZE SELECT ...;   -- actually runs the query, adds real timing

Key features

• VACUUM reclaims dead-tuple space for REUSE within the table — it rarely shrinks the file on disk or returns space to the OS
• VACUUM FULL actually rewrites and compacts the table, returning space to the OS — but takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for its duration
• autovacuum (a background daemon, on by default) runs VACUUM and ANALYZE automatically based on how much a table has changed — disabling it is strongly discouraged except for extremely predictable workloads
• ANALYZE updates the query planner's statistics (via sampling, not a full scan) — stale statistics are a common cause of the query planner choosing a bad plan
• Every table must be vacuumed at least once every ~2 billion transactions to prevent transaction ID wraparound — a scenario PostgreSQL's own docs describe as leading to catastrophic data loss if truly ignored

Command

VACUUM ANALYZE table_name;   -- routine maintenance, safe to run alongside traffic
ANALYZE table_name;           -- refresh planner statistics only, no space reclamation

Key features

• max_connections defaults to 100 and can only be set at server start — PostgreSQL pre-allocates shared memory sized to this value, so raising it has a real memory cost even for connections that sit idle
• A connection pooler (PgBouncer is the common choice) lets many application connections share a much smaller pool of real PostgreSQL connections — the standard fix instead of raising max_connections arbitrarily
• A hot standby (replica) can serve read-only queries while staying synced from the primary; a warm standby cannot accept any connections until promoted
• Asynchronous replication (the default) has minimal performance impact but risks losing recent transactions on failover; synchronous replication guarantees no data loss but slows every commit down
• Range/list/hash partitioning split one logical table into physical pieces — partition pruning lets the planner skip partitions a query cannot match, and dropping/detaching a whole partition is far faster than deleting the same rows individually

Command

CREATE TABLE t (...) PARTITION BY RANGE (col);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (x) TO (y);