Backend engineer backend interview questions

As asked

Implement cursor-based pagination for a feed API in TypeScript. Explain why cursor beats offset for this case.

Sample answer outline

Offset pagination is O(offset) per page on the database (the query still scans the skipped rows) and breaks when items are inserted or deleted mid-paging. Cursor pagination uses an opaque token encoding the last-seen sort key (e.g. created_at + id for uniqueness). The next query is WHERE (created_at, id) < (last_created_at, last_id) ORDER BY created_at DESC, id DESC LIMIT N. Constant time per page, stable under inserts. Encode the cursor as base64 of a small JSON object so clients treat it as opaque. Discuss edge cases: ties on the sort key, bidirectional paging.

Reference implementation (typescript)

type Cursor = { createdAt: string; id: string };

export function decodeCursor(token: string | null): Cursor | null {
  if (!token) return null;
  return JSON.parse(Buffer.from(token, "base64url").toString());
}

export function encodeCursor(c: Cursor): string {
  return Buffer.from(JSON.stringify(c)).toString("base64url");
}

export async function fetchFeed(after: string | null, limit = 20) {
  const cursor = decodeCursor(after);
  const rows = await db.query(
    `SELECT id, created_at, body FROM posts
     WHERE ($1::timestamptz IS NULL OR (created_at, id) < ($1, $2))
     ORDER BY created_at DESC, id DESC LIMIT $3`,
    [cursor?.createdAt ?? null, cursor?.id ?? null, limit],
  );
  const last = rows[rows.length - 1];
  return {
    items: rows,
    nextCursor: last ? encodeCursor({ createdAt: last.created_at, id: last.id }) : null,
  };
}

Expect these follow-ups

• How do you implement bidirectional pagination (back and forth)?
• What if the sort field is a non-unique field like score?
• When is offset still the right choice?

As asked

Explain the difference between offset-based pagination and cursor-based pagination for a list API. When would you reach for each, and what is the simplest cursor you could use?

Sample answer outline

Offset pagination uses page and size, translating to LIMIT and OFFSET in SQL. It is simple and great for a fixed dataset where users jump to a specific page. Its two weaknesses are that the database still scans the skipped rows, which gets slow deep into a large list, and that items shifting position while paging can cause skipped or repeated rows. Cursor pagination instead remembers the last item seen and asks for rows after it, ordered by a stable sort key. The simplest cursor is the sort value of the last row, such as its id or created_at. The early-career answer should land the core tradeoff clearly: offset for small or jump-to-page cases, cursor for large or live feeds.

Reference implementation (sql)

-- Offset: simple, but scans skipped rows
SELECT id, body FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100;

-- Cursor: ask for rows after the last id seen
SELECT id, body FROM posts WHERE id < $1 ORDER BY id DESC LIMIT 20;

Expect these follow-ups

• Why does offset get slower as you page deeper?
• What problem do inserts cause for offset pagination?
• What is the simplest value you can use as a cursor?

As asked

Implement cursor pagination over a feed sorted by a non-unique field such as a relevance score, supporting both forward and backward paging, stable results under concurrent inserts, and a deterministic order. Show the query and explain how the cursor is encoded.

Sample answer outline

The hard part is that a non-unique sort key alone cannot produce a stable, gap-free cursor, so you must form a composite key of the sort field plus a unique tiebreak such as the primary key, and order by both consistently. Forward paging uses a strict comparison on the tuple, WHERE (score, id) < (lastScore, lastId) for descending order; backward paging reverses the comparison and the ORDER BY, then reverses the returned rows in application code so the page reads naturally. Encode the cursor as a base64 token wrapping both the score and the id, kept opaque so clients cannot tamper with the ordering. Stability under inserts holds because the comparison is anchored to concrete values rather than a positional offset. Call out the traps: mismatched comparison and order-by direction silently returns wrong pages, and forgetting the tiebreak causes rows on a shared score to be skipped or duplicated.

Reference implementation (sql)

-- Composite cursor: (score, id) with a strict tuple comparison
-- Forward page (descending by score, then id):
SELECT id, score, body
FROM feed
WHERE (score, id) < ($1, $2)   -- ($1,$2) = last (score, id) from prior page
ORDER BY score DESC, id DESC
LIMIT 20;

-- Backward page: flip comparison and order, then reverse rows in code
SELECT id, score, body
FROM feed
WHERE (score, id) > ($1, $2)
ORDER BY score ASC, id ASC
LIMIT 20;

Expect these follow-ups

• Why is a tiebreak column mandatory when the sort key is non-unique?
• How does the query differ for backward paging, and why reverse in app code?
• What breaks if the comparison direction and the ORDER BY disagree?

As asked

You need to read every row of a 500 million row table to export it to the warehouse, without holding one giant transaction or hammering the source database. As a data engineer, how do you chunk through it reliably and resumably?

Sample answer outline

Reframe cursor pagination as keyset-based batch extraction. Do not use OFFSET, which rescans from the start each batch and degrades badly over hundreds of millions of rows. Instead, order by the primary key and read fixed-size batches with WHERE id > lastId ORDER BY id LIMIT N, carrying lastId forward. This keeps each query cheap on the index, avoids one long-lived transaction that would bloat the source database, and is naturally resumable: persist the last id processed so a failed job restarts from there rather than from zero. Add throttling between batches to protect the source, and decide how to handle rows that change mid-export, often by snapshotting or by tolerating eventual consistency in the warehouse. The data-engineering signal is reliability and resumability at scale, not just correct pagination semantics.

Reference implementation (sql)

-- Keyset batch extraction, resumable via persisted lastId
SELECT id, payload
FROM events
WHERE id > $1            -- $1 = last id from the previous batch (0 to start)
ORDER BY id ASC
LIMIT 10000;

Expect these follow-ups

• Why is OFFSET a poor choice for a full-table export?
• How do you make the export resumable after a mid-run failure?
• How do you handle rows that change while the export is running?

As asked

A client calls your charge endpoint, the network drops, and it retries. Now you risk charging the customer twice. Design the API so a retry never results in a double charge, and tell me where the idempotency state lives.

Sample answer outline

The core idea is an idempotency key chosen by the client and sent on every attempt of the same logical charge. On the first request you record the key with the request fingerprint and the result; on a retry you recognise the key and return the stored result instead of charging again. The state belongs in durable storage, not memory, because retries can land on a different instance, and the write that records the key must be in the same transaction as the charge so a crash between them cannot leave you charged with no record. Use a unique constraint on the key so two concurrent retries race into the database rather than both succeeding. Cover the awkward cases: a reused key with a different payload should be rejected, not silently served, and stored results need a sensible retention window. The honest point is that idempotency is a database concurrency problem wearing an API hat.

Expect these follow-ups

• What happens if two retries with the same key arrive at the same instant?
• Should a client reuse a key for a genuinely new charge, and how do you stop it?
• How long do you keep idempotency records, and why not forever?

As asked

Your API runs on a fleet of servers behind a load balancer and you need a per-user limit of 100 requests per minute that holds across the whole fleet, not per server. How do you build it, and what is the sliding-window subtlety people get wrong?

Sample answer outline

A purely local counter on each server fails because a user spread across ten servers gets ten times the limit, so the counter must be shared, typically in Redis keyed by user and window. The naive fixed window resets on a clock boundary, which lets a user fire 100 requests at the end of one minute and 100 at the start of the next, 200 in a couple of seconds. The fix is a sliding window: either a log of timestamps trimmed to the last minute, which is exact but heavier, or a sliding-window counter that weights the previous window by how much of it still overlaps, which is cheap and close enough. The shared increment-and-check must be atomic, so use a Lua script or an atomic counter rather than read-then-write, otherwise concurrent requests undercount. Mention what happens when Redis itself is unavailable: you choose fail-open or fail-closed deliberately, because each has a real cost.

Reference implementation (lua)

-- Atomic fixed-window check in Redis (Lua): increment, set TTL on first hit
local current = redis.call("INCR", KEYS[1])
if current == 1 then
  redis.call("EXPIRE", KEYS[1], ARGV[1])  -- ARGV[1] = window seconds
end
if current > tonumber(ARGV[2]) then       -- ARGV[2] = limit
  return 0                                 -- reject
end
return 1                                    -- allow

Expect these follow-ups

• Why does a fixed window allow a burst of double the limit at the boundary?
• Why must the increment and check be atomic?
• Do you fail open or fail closed if the shared store is down?

As asked

A Node service is fine on deploy but its memory climbs steadily over a few days until it gets killed and restarts. You cannot reproduce it locally. How do you find the leak in production without taking the service down?

Sample answer outline

Start by confirming it is actually a leak and not just a large but stable working set: watch heap used over time and check whether it plateaus or grows without bound after garbage collection. Once confirmed, capture heap snapshots from a live instance at intervals and diff them; the objects that keep growing between snapshots point at the retained set. Common culprits in Node are closures held by long-lived timers or event listeners that are never removed, an ever-growing module-level cache or Map with no eviction, and listeners added per request to a shared emitter. Do this on one instance pulled from rotation if snapshots are heavy, so users are not affected. The disciplined answer is to follow the evidence from the snapshot diff to a specific retainer rather than guessing, then fix the root cause, for example bounding the cache or removing the listener, and confirm the curve flattens after deploy.

Expect these follow-ups

• How do you tell a real leak apart from a large steady-state heap?
• Which Node patterns most often retain memory by accident?
• How do you profile without affecting live traffic?

As asked

Design the OTP supervision tree for a Phoenix service that handles chat rooms, user presence, and outbound webhooks. What processes exist and what restarts when something fails?

Sample answer outline

A strong answer starts with process boundaries, not modules. Put room processes under a DynamicSupervisor so rooms can start and stop independently, and isolate webhook delivery workers so a bad third-party endpoint cannot take down chat state. Choose restart strategies deliberately: one_for_one for independent room workers, rest_for_one only where later children depend on earlier ones. Presence should be treated as derived state that can be rebuilt, while durable chat messages belong in the database. Candidates often trip up by making one GenServer own too much state or by restarting a whole subtree for a local failure.

Expect these follow-ups

• When would you use Registry versus pg for finding room processes?
• How do you avoid a thundering restart loop after a deploy?
• What state is safe to keep only in a process?