Databases & Redis

EXPLAIN ANALYZE first — never guess, and say that. Then read it for four things:

1. A sequential scan on a large table where you expected an index scan.
2. A row estimate wildly different from actual — the planner is working from stale statistics, so ANALYZE the table.
3. A nested loop over many rows where a hash join would be right.
4. A sort or hash that spilled to disk — work_mem too small for the query.

Then look at the query itself for the usual causes:

• No index on the filter or join column, or the wrong column order in a composite index.
• A function wrapping an indexed column — WHERE LOWER(email) = $1 cannot use a plain index on email; it needs a functional index on LOWER(email).
• An implicit type cast doing the same thing — comparing a varchar column to an integer parameter.
• SELECT * pulling columns nobody uses, defeating a covering index.
• An N+1 from the ORM.
• OFFSET 100000, which makes the database count through 100,000 rows to discard them.

EXPLAIN (ANALYZE, BUFFERS)
SELECT b.* FROM bookings b
 WHERE b.user_id = $1 AND b.created_at > now() - interval '30 days'
 ORDER BY b.created_at DESC LIMIT 20;

-- the index this wants:
CREATE INDEX ON bookings (user_id, created_at DESC);
-- equality column first, then the range/sort column

A balanced tree kept sorted by the indexed columns. Logarithmic lookup, and — the part people forget — ordered range scans, which is why the same index that serves WHERE created_at > x also serves ORDER BY created_at without a sort.

A composite index on (a, b) is sorted by a first, then by b within equal values of a. So it serves:

CREATE INDEX ON t (a, b);

WHERE a = 1              -- yes
WHERE a = 1 AND b = 2    -- yes
WHERE a = 1 ORDER BY b   -- yes, no sort needed
WHERE b = 2              -- NO. leftmost-prefix rule.

-- rule of thumb for ordering columns:
-- equality predicates first, then the range/sort column, then included columns

Then the cost side, which is what makes it a senior answer: every index slows every write and consumes storage, because each insert, update and delete must maintain it. An index nobody uses is pure overhead — pg_stat_user_indexes shows you which ones have zero scans.

Also worth naming: a covering index (INCLUDE in Postgres) contains every column the query needs, so it never touches the table at all — an index-only scan. And a partial index (WHERE status = 'pending') is far smaller when you only ever query a small subset.

One query fetches N parents, then the ORM issues one query per parent to load a relation — 101 round trips instead of two. It never appears locally with ten rows and destroys you at ten thousand.

// N+1
const camps = await repo.find()                 // 1 query
for (const c of camps) c.slots = await slots.findBy({ campId: c.id })  // N

// fix 1 — join
await repo.find({ relations: { slots: true } })

// fix 2 — batch, when a join would multiply rows badly
const all = await slots.findBy({ campId: In(camps.map(c => c.id)) })
const byCamp = Object.groupBy(all, s => s.campId)

How you catch it: query logging in development with a per-request count and a threshold that fails a test; or an APM trace that shows the fan-out visually. In GraphQL the standard answer is DataLoader, which batches the per-item resolver calls within a tick.

The counter-point worth raising: a join is not always right. Joining a parent to a large child collection multiplies the parent columns across every child row — for wide parents with many children, two queries move less data than one join.

Atomicity all or nothing. Consistency constraints hold before and after. Isolation concurrent transactions do not see each other's partial work. Durability a committed write survives a crash.

Isolation is the one with levels, because full isolation is expensive:

Define the three anomalies in one line each, because that is what they will actually ask:

• Dirty read — you see data another transaction wrote but has not committed.
• Non-repeatable read — you read the same row twice in one transaction and get different values.
• Phantom read — you run the same query twice and the second time a new row matches.

Read: check Redis; on a miss read the database and write back with a TTL. Write: update the database, then delete the key — do not update it.

Why delete rather than update: two concurrent writers can interleave so that the slower one writes its older value into the cache last, leaving the cache permanently stale. Deleting means the next read repopulates from the source of truth.

async function getCamp(id) {
  const hit = await redis.get(`camp:${id}`)
  if (hit) return JSON.parse(hit)
  const row = await db.camp.findUnique({ where: { id } })
  if (!row) { await redis.set(`camp:${id}`, 'null', { EX: 60 }); return null }  // negative cache
  await redis.set(`camp:${id}`, JSON.stringify(row), { EX: 300 })
  return row
}

async function updateCamp(id, data) {
  const row = await db.camp.update({ where: { id }, data })
  await redis.del(`camp:${id}`)      // delete AFTER commit, not before
  return row
}

The three failure modes — this is where the marks are:

• Stale reads. Between the database commit and the cache delete, readers get old data. Bounded by short TTLs and by deleting after commit. Perfect consistency is not achievable here; the honest position is that you choose an acceptable staleness window per key.
• Thundering herd (cache stampede). A popular key expires and a thousand concurrent requests all miss and all hit the database at once. Fix with single-flight — one request takes a short lock and refills while the others wait — or probabilistic early expiry, where each reader has a small chance of refreshing before the TTL ends.
• Cache penetration. Repeated requests for a key that does not exist bypass the cache every time, which is also an easy attack. Cache the negative result with a short TTL, or use a Bloom filter for high volume.

And the eviction policy question: allkeys-lru for a pure cache; volatile-lru or volatile-ttl when the same instance also holds sessions or locks that must not be evicted. Getting that wrong means Redis silently drops your session data under memory pressure.

// acquire — NX so only one wins, EX so a crashed holder cannot deadlock
const token = crypto.randomUUID()
const ok = await redis.set('lock:job', token, { NX: true, EX: 30 })

// release — must check ownership, or you delete someone else's lock
// after your TTL expired. Lua makes it atomic.
// if redis.call('GET',KEYS[1]) == ARGV[1] then return redis.call('DEL',KEYS[1]) end

The honest caveat on locks: this is safe enough for "do not run this cron twice", not safe enough for money. Redis locks can be lost during failover, which is what Redlock tries to address and why it is contested. For correctness-critical mutual exclusion, use the database.

Five queries. Know each with a window function and, where relevant, without one — some interviewers ban window functions specifically to see if you can do it the hard way.

-- window function
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM emp
) t WHERE r = 2;

-- without: correlated subquery
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);

-- duplicate rows
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- top 3 earners per department
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
  FROM emp
) t WHERE rn <= 3;

-- employees earning more than their manager
SELECT e.name FROM emp e JOIN emp m ON e.manager_id = m.id
 WHERE e.salary > m.salary;

-- running total
SELECT d, amount, SUM(amount) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING) AS running
FROM payments;

Also be able to state cleanly: what each join returns; WHERE filters rows before grouping while HAVING filters groups after; UNION de-duplicates and UNION ALL does not (and is therefore faster); and that COUNT(col) skips nulls while COUNT(*) does not.

Expand, migrate, contract. Never do a breaking change in one step, because old and new code run simultaneously during a rolling deploy.

Renaming full_name to name, done safely:

1. Expand. Add name as nullable. Deploy code that writes to both columns and reads from full_name.
2. Backfill. Copy in batches, not one UPDATE — a single statement over ten million rows locks the table and blocks everything.
3. Switch reads. Deploy code that reads name, still writing both.
4. Contract. Stop writing full_name, deploy, then drop the column in a later release.

Postgres specifics worth naming: CREATE INDEX CONCURRENTLY so building an index does not block writes; adding a NOT NULL column with a default is safe on modern Postgres but was a full table rewrite on older versions; and adding a foreign key takes a lock unless you add it NOT VALID and validate it separately.

• SQL vs NoSQL — when genuinely MongoDB? When the shape varies per document and you never need multi-entity transactions or ad-hoc joins — event payloads, CMS content, catalogues with wildly different attributes. Not "because it scales"; Postgres scales further than most teams ever need, and it has JSONB when you want document flexibility inside a relational model.
• Normalisation to 3NF, and when you would denormalise. Normalise by default. Denormalise a specific read path that is measurably too slow, and accept the write-time cost of keeping the copy correct.
• Connection pooling. Opening a Postgres connection is expensive and each one costs server memory, so you keep a pool. When it is exhausted, requests queue and then time out — which surfaces as "the API is slow" and is actually "we leaked connections" or "one slow query is holding them all".
• Read replicas and replication lag. The replica is behind by milliseconds to seconds. The thing that breaks is read-your-own-writes: a user saves, is redirected, reads from a replica, and their change is not there. Fix by routing reads to the primary for a short window after a write.
• Sharding vs partitioning. Partitioning splits one table across storage on one server; sharding splits data across servers. A bad shard key is one that concentrates traffic — created_at puts all new writes on one shard.
• CAP theorem. Under a network partition you choose consistency or availability. A single-node Postgres is not a distributed system, so CAP does not really apply; a Postgres cluster with synchronous replication chooses CP.
• Soft delete — what does it break? Every unique constraint (two "deleted" rows with the same email now collide), and every query that forgets the flag. Use a partial unique index, and put the filter in a view or repository so it cannot be forgotten.
• UUID vs auto-increment as a primary key. Sequential integers index tightly but leak volume and are enumerable. Random UUIDv4 fragments the B-tree and hurts insert performance at scale. UUIDv7 is time-ordered and gets you both — that is the current best answer.
• Storing money. Never a float. Integer minor units, or NUMERIC/DECIMAL. 0.1 + 0.2 !== 0.3 is not an acceptable property for a payment system.
• Storing timestamps. timestamptz in UTC, always. Store the user's timezone separately if you need to render local time correctly for future events.
• Where do you run a transaction in Nest? In the service, not the controller, and pass the transaction handle down to the repository calls — otherwise each repository opens its own connection and you have no transaction at all.