PostgreSQL with Django

What we're doing: Index a JSONB column for fast "contains key" lookups, and confirm a plain B-tree index would not help this query shape.

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- now fast:
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

1

USING GIN is required here — a default B-tree index on a JSONB column can only accelerate exact whole-column equality, not "does this document contain this key/value," which is what @> (containment) actually needs.

Why this works: A B-tree index on attributes would only speed up queries comparing the ENTIRE JSONB value for equality — the containment query (@>, "does this document have color=red among possibly many other keys") needs an index that understands the document's internal structure, which is exactly what GIN provides for JSONB/array columns.

What we're doing: Diagnose a slow query with EXPLAIN ANALYZE, spot the missing-index signal, and confirm the fix with a second EXPLAIN ANALYZE.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
-- Seq Scan on orders (actual time=0.045..2500.000 rows=5000 loops=1)
--   Rows Removed by Filter: 995000

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
-- Index Scan using idx_orders_customer_id (actual time=0.051..125.000 rows=5000 loops=1)

1

995,000 rows removed by filter, for only 5,000 matches, is the plan directly reporting that 99.5% of the scanned work was wasted — the clearest possible signal that an index on customer_id would help.

4

ANALYZE after CREATE INDEX ensures the planner has fresh statistics reflecting the new index — without it, the planner might still choose the old Seq Scan plan out of habit until statistics catch up on their own.

Why this works: Guessing at performance fixes without EXPLAIN ANALYZE risks solving the wrong problem entirely — the "before" plan makes the actual bottleneck (a full scan of 1M rows) undeniable, and re-running EXPLAIN ANALYZE after the fix (actual time 2500ms → 125ms) is the only real confirmation the index actually helped, rather than assuming it did.

What we're doing: Use Serializable isolation for a transaction where a subtle read/write conflict with a concurrent transaction must never silently corrupt the result, and handle the resulting error case.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- if COMMIT fails with "could not serialize access due to read/write dependencies",
-- the entire transaction must be retried from BEGIN

2

SERIALIZABLE is set explicitly — PostgreSQL's default (Read Committed) would allow this exact transfer to interleave with a concurrent one in a way that could produce a subtly wrong final balance under some access patterns.

Why this works: A balance transfer is exactly the case where "each statement sees whatever is currently committed" (Read Committed) is not always strong enough — Serializable guarantees the final result is equivalent to running every concurrent transaction one at a time, at the cost of the application needing to catch and retry the occasional serialization failure.

What we're doing: Prevent a deadlock in a funds-transfer function by always locking the two accounts in a fixed order, regardless of which account is the source and which is the destination.

def transfer(from_id, to_id, amount):
    first_id, second_id = sorted([from_id, to_id])
    with transaction.atomic():
        Account.objects.select_for_update().filter(id__in=[first_id, second_id]).order_by("id")
        Account.objects.filter(id=from_id).update(balance=F("balance") - amount)
        Account.objects.filter(id=to_id).update(balance=F("balance") + amount)

2

sorted([from_id, to_id]) guarantees the SAME two accounts are always locked in the same order, regardless of whether this specific call transfers 1→2 or 2→1.

3

Locking both accounts in that fixed order — before either update — means a concurrent transfer(2, 1, ...) call locks them in the identical order, so the two transactions can never form the circular-wait shape a deadlock requires.

Why this works: Without a fixed lock order, transfer(1, 2, 50) running concurrently with transfer(2, 1, 30) can lock account 1 then wait for account 2 in one transaction, while the other locks account 2 then waits for account 1 — the exact deadlock shape. Sorting the ids first ensures both transactions always approach the two accounts in the identical order, making the circular wait structurally impossible.

What we're doing: Refresh a table's statistics after a large bulk data load, so the query planner's next decisions are based on current data rather than stale pre-load statistics.

-- after loading a large batch of new rows via COPY or bulk INSERT
COPY orders FROM '/tmp/new_orders.csv' WITH (FORMAT csv);

ANALYZE orders;   -- statistics now reflect the newly-loaded data

3

ANALYZE here specifically refreshes the planner's row-count/distribution statistics — without it, the planner keeps using pre-load statistics that no longer describe the table's actual current size and data distribution, which can lead it to pick a worse plan for subsequent queries.

Why this works: A large bulk load can change a table's size and data distribution dramatically in one operation — autovacuum will eventually catch up on its own schedule, but running ANALYZE explicitly right after a known-large load avoids a window where every query against the freshly-loaded data is planned against stale, pre-load assumptions.

What we're doing: Partition an events table by month so old data can be purged nearly instantly and queries scoped to a date range skip irrelevant partitions entirely.

CREATE TABLE events (id bigint, occurred_at date, payload jsonb)
    PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

SELECT * FROM events WHERE occurred_at >= '2026-01-15' AND occurred_at < '2026-01-20';
-- partition pruning: only events_2026_01 is scanned, every other month's partition is skipped

1

PARTITION BY RANGE (occurred_at) makes events a purely logical container — no rows are stored in the parent table itself, only in its partitions.

5

The query planner recognizes the date range falls entirely within the January partition and skips every other month's partition without even considering it — the more partitions exist, the bigger this pruning win becomes.

Why this works: A single unpartitioned events table with years of history means every query, even one scoped to a single day, has to contend with a much larger index/table than necessary — partitioning by month means a query for "the last week" only ever touches one or two small partitions, and purging data older than a retention window becomes DROP TABLE (near-instant) instead of a DELETE that has to find and remove millions of individual rows.