Question 1

Postgres 16 has a B-tree index on (customer_id, created_at), with millions of distinct customers. A query filters only on created_at = $1. How does the index help?

Accepted Answer

It cannot seek on created_at, so at best the planner scans the whole index Entries are sorted by customer_id first, so created_at values are scattered across the whole index. Without the leading column there is no range to seek to. Being in the index is not enough, column order matters. Postgres never reorders index columns. Skip scan arrived only in Postgres 18, and even there it pays off only when the leading column has few distinct values.

Question 2

Query: SELECT * FROM orders
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC LIMIT 20; Which single index lets Postgres read just 20 entries with no sort step?

Accepted Answer

(tenant_id, status, created_at) Equality columns first, then the sort column. The index lands on the tenant and status block, which is already ordered by created_at, and reads the last 20 backwards. Leading with created_at walks the whole time order filtering rows. With created_at second the order is right but status is checked entry by entry, reading extra rows for rare statuses. Separate indexes need a bitmap combine and then a sort.

Question 3

Query: WHERE status = 'failed' AND created_at > now() - interval '1 day'. Which composite index is better?

Accepted Answer

(status, created_at) With the equality column first, matching rows are one contiguous slice: status = failed, then created_at in range. With the range first, the scan covers every status in the last day and filters status per entry. Order matters for B-trees, AND or not. created_at alone reads every status in the window.

Question 4

Hot query: SELECT email FROM users WHERE org_id = $1. There is an index on org_id, but EXPLAIN shows many heap fetches. Which change can let Postgres answer from the index alone?

Accepted Answer

CREATE INDEX ON users (org_id) INCLUDE (email) INCLUDE stores email in the index leaf, so an index-only scan can return it. Postgres still checks the visibility map, so recently changed pages may need heap visits until VACUUM runs. A separate email index does not help a lookup by org_id. Hash indexes do not support index-only scans. work_mem affects sorts and hashes, not heap fetches.

Question 5

Login runs WHERE lower(email) = lower($1). There is a B-tree index on email, yet Postgres does a sequential scan. What fixes it?

Accepted Answer

An index on lower(email) The index stores raw email values, so it cannot match an expression on the column. An expression index stores lower(email) and the planner can use it for that exact expression. ANALYZE refreshes statistics but cannot make an index on email match lower(email). A hash index on email has the same mismatch. ILIKE cannot use a plain B-tree either.

Question 6

MySQL InnoDB, primary key is a random UUIDv4. Insert throughput drops as the table grows past memory. What is the main cause?

Accepted Answer

Random keys land all over the clustered index, causing page splits and reads of cold pages InnoDB stores rows in primary key order. Random keys insert into random leaf pages, so once the working set exceeds the buffer pool most inserts read a page from disk and split pages. Time-ordered keys (UUIDv7, bigint) append to the right edge. Comparing 16 bytes costs little next to disk reads. InnoDB does not hash the primary key. The primary key is already the clustered index, and another index adds write cost.

Question 7

Postgres. You need fast lookups for WHERE created_at BETWEEN $1 AND $2 ORDER BY created_at. Which index type?

Accepted Answer

B-tree B-trees keep keys sorted, so they serve ranges and ORDER BY. Hash indexes only support equality. GIN is for composite values such as arrays, jsonb and full-text search, not scalar ranges.

Question 8

Postgres. A 200M row tasks table has about 0.5% rows with status = 'pending', and the worker queries only those. Which index gives the smallest, cheapest structure for that query?

Accepted Answer

CREATE INDEX ON tasks (created_at) WHERE status = 'pending' A partial index holds only the 1M pending rows, so it is small, stays in cache, and done rows never touch it on update. An index on status covers all 200M rows to serve 0.5%. The composite works but is equally huge. Low cardinality overall does not matter when the queried value is rare.

Question 9

Postgres. EXPLAIN ANALYZE shows a nested loop where the planner estimated rows=10 but actual rows=2000000, and the query takes minutes. What do you check first?

Accepted Answer

Planner statistics: run ANALYZE, or add extended statistics for correlated columns A nested loop is a good plan for 10 rows and a terrible one for 2M. The estimate is off by 200,000x, so fix what the planner believes: stale stats or correlated predicates the planner treats as independent. An index does not fix a wrong row count. More cache helps a little but the plan is still wrong. Disabling seq scans globally hides the cause and harms other queries.

Question 10

Postgres. A feed pages with ORDER BY created_at DESC, id DESC OFFSET 1000000 LIMIT 20 and deep pages are slow, even with an index on (created_at, id). What fixes it?

Accepted Answer

Keyset pagination: WHERE (created_at, id) < ($last_ts, $last_id) with the same ORDER BY and LIMIT 20 OFFSET still reads and throws away a million index entries. A keyset condition seeks straight to the last row seen and reads 20. The existing index already covers the order, the cost is the skipped rows. Counting does not change the scan. Bigger pages only reduce how often you pay the same cost.

Question 11

Keyset pagination uses WHERE created_at < $last ORDER BY created_at DESC LIMIT 20. Many rows share the same timestamp. What goes wrong?

Accepted Answer

Rows with the boundary timestamp get skipped, so add a unique tiebreaker such as id to the key If page 1 ends in the middle of rows sharing one timestamp, the strict < skips the rest of them. Compare (created_at, id) and order by both so every row has a unique position. Bulk inserts and coarse clocks often produce equal timestamps. The plan does not change. Rows are lost, not only reordered.

Question 12

Product asks to move a table view from OFFSET to keyset pagination. What do you give up?

Accepted Answer

Jumping straight to an arbitrary page number such as page 500 Keyset needs the last row of the previous page, so it supports next and previous, not random page jumps. Keyset is more stable under inserts than OFFSET, which shifts rows between pages. It is the variant that uses the index well. Descending order works with a matching index or a backward scan.

Question 13

DynamoDB. Access pattern: get a customer's orders, newest first, paged. Which key design fits?

Accepted Answer

Partition key customer_id, sort key order_date#order_id A Query on the customer's partition returns its items in sort key order, and ScanIndexForward = false gives newest first with paging. A Scan reads the whole table. order_date as partition key spreads one customer over many dates and makes today hot. Without a sort key each customer can have only one item.

Question 14

A DynamoDB Query on a partition reads 1 MB of items and a FilterExpression keeps 5 of them. What do you pay for?

Accepted Answer

The full 1 MB read, because filters apply after items are read Read capacity is charged for items read before filtering. If you filter often on an attribute, make it part of a key or a GSI. Paying only for returned items is the common assumption and it is wrong. FilterExpression does not use an index. There is no discount for filtered items.

Question 15

A payments ledger needs multi-row atomic transfers, strong constraints, and finance's ad hoc reporting queries. Scale is 500 writes per second. Which store fits?

Accepted Answer

A relational database such as PostgreSQL Transactions across rows, constraints and ad hoc SQL are what relational databases do, and 500 writes per second is light load. Cassandra is tuned for huge write volume on known queries, with weak multi-row transactions and no ad hoc joins. One document per account makes transfers cross-document. Redis as the primary ledger has weaker durability and no reporting.

Question 16

A feed is read 100 times per write, so you copy author_name into every post row to skip a join. What cost do you accept?

Accepted Answer

A name change must update every post, and copies can be stale in the meantime Denormalising trades write work and consistency for cheaper reads. Renames fan out to many rows, usually async, so some posts show the old name for a while. Reads get faster, that is the point. Indexes still work. A new post copies the name at insert in a single write.

Question 17

Model a chat app in DynamoDB that must list a user's conversations by latest activity and page the messages in a conversation.

Accepted Answer

Messages: partition key conversation_id, sort key a time-ordered message id, then Query newest first with a limit. Inbox: an item per user and conversation with partition key user_id and sort key last_activity#conversation_id, updated on each message (fan out per participant). Start from access patterns, not entities.

Big group chats make the inbox fan-out expensive, so cap it or read group activity lazily. A very hot conversation may need write sharding.

Database indexing and data modelling interview questions

Indexing and pagination

Choosing and modelling a datastore

More database topics

Practise these on your commute