Databases — Interview Questions

So a database is a program whose whole job is to store data safely and let me find it again fast. The files question is a good one, because files work right up until the app gets real traffic.

Let me give you an example. Say I store users in a users.json file and two signup requests come in at the same moment:

Request A: read users.json  (100 users)
Request B: read users.json  (100 users)
Request A: append Ada, write file   (101 users)
Request B: append Bo,  write file   (101 users, Ada is gone)

What happens here is a lost update. B overwrote A's write because nothing coordinated them. And there are two more problems waiting. If the process crashes halfway through writing, I have a half-written, corrupt file. And to find one user by email I have to read and parse the entire file every time.

A database solves all three for me: it handles concurrent writers, it guarantees that once it says "saved" the data survives a crash, and it keeps indexes so a lookup doesn't scan everything. On top of that it enforces rules and lets me ask questions I didn't plan for.

So in practice, files are fine for config or a one-off export, but the moment there are concurrent writes or lookups, I want a database.

Interviewers look for: concrete failure modes (concurrent writes, crashes, lookups) rather than "databases are more organised".

So I think of it as five guarantees I'm effectively paying the database for.

• Durability: once a write is acknowledged, it survives a power cut.
• Concurrency: hundreds of connections reading and writing without corrupting each other.
• Fast lookup: finding one row among fifty million through an index, not a full scan.
• Integrity: refusing data that breaks my rules, like an order pointing at a customer who doesn't exist.
• Ad-hoc querying: answering a new question with a query instead of new application code.

Let me give you an example of the last one, because people underrate it. In my last project, product asked on a Friday "how many users signed up last month and never placed an order?" Nobody had planned for that question. With Postgres it was one query:

SELECT COUNT(*)
FROM users u
WHERE u.created_at >= date_trunc('month', now()) - interval '1 month'
  AND u.created_at <  date_trunc('month', now())
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

If our data had lived in a key-value store keyed by user id, answering that would have meant writing a script that loaded every record. So when I pick a store, I ask which of these five I actually need, because different databases give up different ones.

So durable means that once the database tells me a commit succeeded, that change survives a crash or power loss. The main mechanism behind it is the write-ahead log.

Let me walk through it. When I commit a transaction in Postgres, it doesn't rewrite the actual table pages on disk straight away, because that's slow random I/O. Instead:

1. Append a record of the change to the WAL (sequential write)
2. fsync the WAL to disk
3. Return "COMMIT" to the client
4. Later, in the background, write the changed pages to the table files

Crash after step 3?  On restart, replay the WAL from the last checkpoint.
Crash before step 2? The commit was never acknowledged, so nothing is lost
                     that the client was told about.

The reason this works is that the log is the source of truth. The table files can be temporarily behind, and recovery just replays whatever's in the log. As a bonus, that same WAL is what replication streams to replicas.

One gotcha I've seen: people turn off fsync or set synchronous_commit = off for speed. The second one is a legitimate trade-off, since you can lose the last few hundred milliseconds of commits but not corrupt anything. Turning off fsync entirely can corrupt the database, so I'd only do that for a throwaway test instance.

Interviewers look for: the words "write-ahead log" and the idea that the commit is acknowledged only after the log is flushed.

So my answer is both, but for different reasons. The app validates for a good user experience. The database validates because it's the last line of defence and it's the only layer that sees every write.

Let me give you an example. We had email uniqueness checked only in the API:

// app code
const existing = await db.user.findUnique({ where: { email } });
if (existing) throw new Error('Email taken');
await db.user.create({ data: { email, name } });

What happens under load is two signup requests with the same email both run the findUnique, both see nothing, and both insert. We ended up with duplicate accounts. The app-level check is a race. The fix was a constraint:

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

Now the database rejects the second insert atomically, and I catch the unique-violation error (23505 in Postgres) and return a friendly message. The same logic applies to other writers: a backfill script, an admin tool, a second service. None of them go through my API validation, but all of them hit the constraint.

So in practice I keep friendly, detailed validation in the app, and hard invariants like uniqueness, not-null, foreign keys and simple checks in the schema.

So at a high level, the query goes through parse, plan, execute, and the interesting part is the planner, because SQL says what I want and the database decides how.

Let me take a concrete one:

SELECT u.name, o.total
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.email = 'ada@example.com';

• Parse and analyse: check the syntax, resolve that users and email exist, check permissions.
• Rewrite: expand views and apply rules.
• Plan: the optimizer generates candidate plans and estimates their cost using table statistics. Here it would likely use the unique index on email to find one user, then use an index on orders.user_id in a nested loop, because it estimates just one outer row.
• Execute: pull rows through the plan tree, reading pages from the buffer cache or from disk.

The reason this matters day to day is that the plan depends on statistics. One gotcha I've hit: after a bulk import of millions of rows, the stats were stale, the planner thought a table was tiny, and it picked a nested loop over millions of rows. Running ANALYZE fixed it instantly. So when a query is slow, the first thing I do is EXPLAIN ANALYZE and compare estimated rows to actual rows.

Interviewers look for: knowing the planner is cost-based and statistics-driven, and that EXPLAIN ANALYZE is the tool.

So the core difference is how the data is shaped. Relational databases store data in tables with fixed columns and link tables through keys. Non-relational is an umbrella for everything else, and the most common kind is the document store, where each record is a flexible, nested object.

Let me give you an example with a user and their addresses.

-- Relational: split across two tables, linked by user_id
users:     id | name | email
           1  | Ada  | a@b.com
addresses: id | user_id | city
           9  | 1       | London
           10 | 1       | Pune

// Document: one nested object
{ _id: 1, name: "Ada", email: "a@b.com",
  addresses: [{ city: "London" }, { city: "Pune" }] }

What that shows is the trade. The relational version stores each fact once, enforces the schema, and joins when I need data together. The document version gets everything in one read and lets each record have a different shape, but the schema lives in my application code.

Other differences follow from that: SQL is a standard language across Postgres, MySQL and SQLite, whereas every NoSQL store has its own API. And document stores were designed to spread across machines from day one, while relational databases usually scale up first.

Interviewers look for: framing it as a data-shape and access-pattern trade-off, not "SQL is old, NoSQL is fast".

So NoSQL originally meant "not only SQL", and honestly it's a bad name, because it describes what a database isn't rather than what it is. Some NoSQL stores even have SQL-like query languages, like Cassandra's CQL.

The useful way to think about it is by data model. There are really four families:

The reason the distinction matters is that each one is optimised for a specific access pattern. Redis is incredibly fast for "get this key", but it can't answer "all users in London over 30" without me building that index myself. So when someone says "let's use NoSQL", my follow-up is always "which kind, and for which queries?"

So no, there's always a schema. The question is only where it's enforced. In a relational database it's enforced by the database on write. In a document database it's usually implicit, living in the code that reads the data, which people call schema-on-read.

Let me give you an example of how that bites. In a MongoDB project, early users were saved like this, and a later release changed the shape:

// v1 documents
{ _id: 1, name: "Ada Lovelace" }

// v2 documents
{ _id: 2, firstName: "Grace", lastName: "Hopper" }

// Every reader now has to handle both
const display = user.name ?? `${user.firstName} ${user.lastName}`;

What happens over a couple of years is that every consumer accumulates these branches, and nobody is sure which shapes still exist in production. The schema didn't go away. It spread into every reader.

So in practice, when I use a document store I still define a schema, either with MongoDB's $jsonSchema validator on the collection or with Mongoose or Zod in the app, and I write migrations for shape changes just like I would in SQL. The flexibility is genuinely useful for fields that vary per record, like product attributes, but "no schema" isn't really a thing.

Interviewers look for: "schema-on-write vs schema-on-read" and awareness that the schema moves into the application.

So for a lot of apps, honestly, yes. JSONB lets me keep the relational core and add flexible fields exactly where I need them, and it can be indexed. But it doesn't replace a document database in every case.

Let me give you an example from an e-commerce catalogue, where every category has different attributes:

CREATE TABLE products (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX products_attrs_gin ON products USING GIN (attributes);

-- laptops with 16GB RAM
SELECT id, name FROM products WHERE attributes @> '{"ram_gb": 16}';

What I get here is the best of both: price and name are real typed columns with constraints, orders can have foreign keys to products, and the variable stuff sits in JSONB with a GIN index for containment queries.

Where a dedicated document store still wins is when the whole data model is document-shaped and I need built-in horizontal sharding, or when the team wants the document API end to end. Also, updating one field inside a big JSONB value rewrites the whole row, so heavily mutated nested data is awkward.

So in practice my default is Postgres with JSONB for the flexible bits, and I reach for MongoDB when the documents are the model.

So it's a half-truth. It's accurate that most NoSQL stores were designed for sharding from the start, and that sharding a relational database is more work. But "SQL can't scale" is wrong in practice.

Let me break down why. The reason relational databases are harder to shard is that their features assume all the data is in one place:

-- Easy on one node, hard across shards:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- shard A
UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- shard B
COMMIT;   -- now needs a distributed transaction

SELECT ... FROM orders JOIN users ...   -- cross-shard join
FOREIGN KEY across shards               -- not enforceable locally

NoSQL stores "scale easily" partly because they gave up those features: no joins, limited transactions, and the data model forces you to pick a partition key upfront. The scaling isn't free, it was paid for by the application.

On the other side, a single well-tuned Postgres box with read replicas handles far more than most products ever need, and there's a mature ecosystem when you do outgrow it: Citus for sharded Postgres, Vitess for MySQL, and distributed SQL databases like CockroachDB, YugabyteDB, and Spanner that give you SQL and transactions across nodes.

So my honest answer is: the constraint isn't SQL, it's cross-node joins and transactions, and every system at scale has to pay for those somehow.

Interviewers look for: pushing back on the myth with the actual reason (joins and transactions across nodes) and naming real options like Citus, Vitess, or distributed SQL.

So my honest default is Postgres unless I have a specific reason not to. Then I check that default against a few questions about the data and how it'll be read.

There are really four things I ask:

• How connected is the data? Users, orders, payments, products all referencing each other points strongly to relational.
• Do records share a shape? If every product category has totally different fields, that leans document, or at least JSONB.
• Do I need multi-step operations to be safe? Money, bookings, inventory: I want real transactions.
• What are the access patterns? If it's always "get by key" at huge volume, a key-value or wide-column store fits. If people will ask new questions for reporting, I want SQL.

Let me give you an example. For a food-delivery app I'd put users, restaurants, orders and payments in Postgres, because it's connected, transactional data. Driver location pings, arriving every few seconds per driver, I'd put in Redis for the "current position" and push the history to something built for time series.

The reason I default to relational is that it's the easiest one to change your mind about. It handles JSON, it scales further than most apps need, and it gives me constraints and transactions for free.

Interviewers look for: a default with reasons, then questions about access patterns, not a list of database brand names.

So the clearest split is: structured, connected, correctness-critical data goes relational; self-contained records with varying shape go to documents.

Let me give you a concrete pair from a learning platform I worked on.

-- Relational: payments and enrolments
payments:   id | user_id | course_id | amount | status | created_at
enrolments: user_id | course_id | enrolled_at

// Document: course content, where every lesson type differs
{ _id: "lesson_42", type: "quiz", title: "Joins",
  questions: [{ prompt: "...", choices: [...], answer: 2 }] }
{ _id: "lesson_43", type: "video", title: "Indexes",
  url: "...", durationSec: 612, chapters: [...] }

What I like about this split is that payments need foreign keys, transactions and reporting ("revenue by course last month"), so SQL is a natural fit. Lesson content is always read as one whole piece by its id, it's deeply nested, and a quiz looks nothing like a video. Forcing that into tables would mean a table per lesson type or a pile of nullable columns.

That said, in a smaller project I'd have kept lessons in a Postgres JSONB column rather than run a second database. Two databases means two things to back up, monitor, and keep consistent.

So polyglot persistence just means using different storage technologies for different jobs within one system, instead of forcing everything into a single database.

Let me give you a typical setup:

Postgres       → users, orders, payments      (correctness, joins)
Redis          → sessions, cache, rate limits (speed, short-lived)
Elasticsearch  → product search               (full-text, relevance, facets)
S3             → images, exports              (large blobs)

The reason each is there is that it does one thing much better than Postgres would. Postgres can do basic full-text search, but relevance tuning, typo tolerance and faceting are what a search engine is built for.

The cost is real, though. Every extra store is another thing to deploy, monitor, back up and secure, and now I have to keep data in sync between them. In my last project the search index drifted from Postgres whenever a sync job failed, and customers saw products that were out of stock. We ended up moving to change-data-capture from the Postgres WAL with Debezium, so the index is fed from the source of truth.

So in practice I add a store only when the primary database genuinely stops fitting a specific need, and I always decide upfront which one is the source of truth.

Interviewers look for: naming the sync and operational cost, and a clear "source of truth" for each piece of data.

So I'd start by asking "faster at what?", because a database migration is one of the most expensive changes a team can make, and "faster" usually hides a specific slow query.

Let me give you how that conversation went in a real case. The complaint was that the order history page took two seconds. When I looked:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 20;

-- Seq Scan on orders  (rows=4,000,000)  ... 1900 ms

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

-- Index Scan using orders_customer_created_idx ... 0.4 ms

What happened was a missing index. MongoDB would have been just as slow without the equivalent index. The problem was the query, not the engine.

So my response has three parts. First, measure: find the actual slow operations and look at their plans. Second, fix within Postgres: indexes, query rewrites, denormalizing a hot read, caching, or a read replica. Third, if after that there's a genuine data-model mismatch, like highly variable nested documents that we always read whole, then we can talk about moving that part, not everything.

The reason I'm cautious is that we'd be giving up transactions across collections by default, foreign keys, and SQL reporting, in exchange for a speedup we haven't shown exists.

So for high-volume, append-mostly data, the key facts are: writes vastly outnumber updates, reads are almost always by time range for a device or stream, and old data gets less valuable. I'd pick storage that's optimised for exactly that pattern.

Let me give you an example. Say 50,000 devices each send a reading every 5 seconds, which is about 10,000 writes a second, forever.

Option A: Postgres + TimescaleDB (or native partitioning)
  readings partitioned by day; compression on old chunks;
  queries: WHERE device_id = $1 AND ts BETWEEN $2 AND $3

Option B: Cassandra / ScyllaDB
  PRIMARY KEY ((device_id, day), ts)
  partition = one device's day, rows sorted by ts

Option C: ClickHouse
  columnar, great for "average temperature per region per hour"

The way I choose between them: if the team already runs Postgres and the volume is in the tens of thousands of writes per second, TimescaleDB or plain time partitioning is the least new infrastructure, and dropping an old partition is instant compared to DELETE. If we need multi-region writes and near-linear scale, Cassandra or ScyllaDB, where I design the table around the query. If the main use is analytics over billions of rows, a columnar store like ClickHouse.

One gotcha I'd call out: in Cassandra, partitioning by device_id alone creates unbounded partitions that grow forever, which is why I bucket by day.

Interviewers look for: deriving the choice from write rate, query shape, and retention, plus partition design.

So a table represents one kind of thing, like users. A column is a property every user has, with a declared type. A row is one actual user. The key rule is that every row in a table has exactly the same columns.

Let me give you an example.

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  age        INTEGER CHECK (age >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The difference from a spreadsheet is that the database enforces this. In a spreadsheet I can type "twenty" into the age column, leave the email blank, or paste the same person twice. Here, each of those gets rejected: age must be an integer and non-negative, email is required and unique, and every row gets a unique id.

The other difference is that rows have no inherent order. A spreadsheet has row 1, row 2. A table is a set, so if I want a specific order I have to say ORDER BY. Without it, the database can return rows in any order it likes, and that order can change between runs.

Interviewers look for: enforced types and constraints, and knowing that rows are unordered without ORDER BY.

So in Postgres, which is what I use most, all three are stored the same way under the hood, so the real difference is the rules they enforce, not performance.

Let me lay it out:

What that means in practice is I use TEXT by default in Postgres, and when a real business limit exists I add it as a check, because changing it later is easier:

username TEXT NOT NULL CHECK (char_length(username) BETWEEN 3 AND 30)

One gotcha: this answer is database-specific. In MySQL, VARCHAR vs TEXT does matter. TEXT columns can't have a default value in older versions, have indexing restrictions (you need a prefix length), and can be handled differently in temporary tables. So in MySQL I'd use VARCHAR(255) for things like names and emails and reserve TEXT for long bodies.

And I avoid CHAR(n). The padding causes confusing comparison behaviour, and fixed-length codes are better modelled as TEXT with a check.

So the risk isn't the column itself, it's the lock. Most ALTER TABLE statements take an exclusive lock, and if the change needs to rewrite or scan the whole table, everything waits behind it.

Let me give you what's safe and what isn't in modern Postgres:

-- Fast: metadata-only since Postgres 11, even with a constant default
ALTER TABLE orders ADD COLUMN notes TEXT;
ALTER TABLE orders ADD COLUMN is_gift BOOLEAN NOT NULL DEFAULT false;

-- Slow: rewrites the whole table
ALTER TABLE orders ALTER COLUMN total TYPE NUMERIC(12,2);

-- Slow: scans every row while holding the lock
ALTER TABLE orders ALTER COLUMN notes SET NOT NULL;

Even the fast ones have a trap I've hit in production: the ALTER needs an exclusive lock briefly, and if a long-running query is holding a lock on the table, my ALTER queues behind it, and then every new query queues behind my ALTER. The site effectively stops. So I always set a lock timeout and retry:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN notes TEXT;

For the slow cases I use the expand-and-contract approach: add a new nullable column, backfill in small batches, add a CHECK (col IS NOT NULL) NOT VALID constraint, then VALIDATE it separately, which doesn't block writes. Then switch the code over and drop the old column.

Interviewers look for: the lock queue problem, lock_timeout, and batching backfills.

So this is the classic weak spot of fixed columns, and there are a few patterns. I pick based on how often the attributes are queried and whether they need constraints.

Let me use products as the example: a laptop has RAM and CPU, a shirt has size and colour.

1. One wide table with lots of nullable columns
   products: id | name | ram_gb | cpu | size | colour | ...
   (simple, but sparse and grows forever)

2. Table per type
   products (shared columns) + laptops (product_id, ram_gb, cpu)
                             + shirts  (product_id, size, colour)
   (strong typing, more joins)

3. EAV: entity-attribute-value
   product_attrs: product_id | key | value
   (infinitely flexible, painful to query and to type-check)

4. JSONB column
   products: id | name | price | attributes JSONB

In practice I usually go with shared columns as real typed columns plus a JSONB column for the variable part, with a GIN index. That keeps price and stock enforceable and searchable, while letting each category have its own fields.

If a few types are stable and heavily queried, like an insurance system with "car policy" and "home policy", I'd do table per type. The one I avoid is EAV. I inherited one once and a simple "laptops with 16GB and an M3" filter was a four-way self-join where every value was stored as text.

So in a traditional row-store like Postgres or MySQL InnoDB, a row's columns are stored together in fixed-size pages, 8KB in Postgres and 16KB in InnoDB. Reading a row means reading its page, and the database caches pages in memory.

Let me give you why SELECT * matters then. If I only need two columns:

-- Can be answered from the index alone (index-only scan)
CREATE INDEX users_email_name_idx ON users (email) INCLUDE (name);
SELECT name FROM users WHERE email = 'a@b.com';

-- Has to visit the table, and drags large columns along
SELECT * FROM users WHERE email = 'a@b.com';

What happens with the first query is Postgres can answer from the index without touching the table, if the page is marked all-visible. With SELECT * it must visit the table. And if the row has big values, like a long bio or a JSON blob, those are stored out-of-line in TOAST storage, so pulling them in means extra reads, decompression, and more bytes over the network.

The other reason is fragility: SELECT * in app code means a new column silently changes what my code gets.

The contrast is a column-store like ClickHouse or BigQuery, where each column is stored separately. There, SELECT * on a wide table is dramatically worse, because the whole point is only reading the columns you need. That's why column-stores win for analytics and row-stores win for fetching whole records by key.

Interviewers look for: pages, index-only scans, and the row-store vs column-store trade-off.

So a primary key uniquely identifies each row in its own table: it must be unique and not null, and there's one per table. A foreign key is a column that points at a primary key (or unique key) in another table, and the database guarantees it points at something real.

Let me give you an example.

CREATE TABLE users (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total   NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (user_id, total) VALUES (999, 50);
-- ERROR: insert or update on table "orders" violates foreign key
--        constraint "orders_user_id_fkey"

What happens there is the database refuses the insert because user 999 doesn't exist. That's referential integrity: I can never end up with an orphaned order that points at nobody. It also works the other direction, so by default I can't delete a user who still has orders.

A table can have many foreign keys but only one primary key, although that primary key can span several columns, which is common in join tables.

Interviewers look for: "referential integrity" and that the FK is enforced on both insert and delete.

So many-to-many always needs a third table, usually called a join table or junction table. Neither side can hold the relationship, because a student would need a list of courses and a course a list of students, and a column can't hold a list in a normalized design.

Let me give you the students and courses example.

CREATE TABLE enrolments (
  student_id  BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id   BIGINT NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  grade       TEXT,
  PRIMARY KEY (student_id, course_id)
);

-- All courses for student 7
SELECT c.title
FROM enrolments e JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 7;

A couple of things I do deliberately here. The composite primary key on (student_id, course_id) stops someone enrolling twice in the same course. And the join table often turns out to be a real entity with its own data, like enrolled_at and grade. So I treat it as a first-class table, not just plumbing.

One gotcha: the composite key gives me an index that starts with student_id, which serves "courses for a student". For "students in a course" I add a second index on (course_id), otherwise that direction scans.

So a natural key is a value that already exists in the domain, like an email or ISBN. A surrogate key is an id the database makes up, with no business meaning. I almost always use a surrogate primary key, and put a unique constraint on the natural key.

The reason is that natural keys change. We had a system keyed on email, a user changed their email, and every foreign key pointing at it had to be updated. With a surrogate key, the email is just a column.

For the surrogate itself, here's how I think about it:

-- Sequential integer: small, fast, index-friendly, but guessable
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- UUID v4: random, can be generated anywhere, but fragments the index
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- UUID v7: time-ordered, so inserts stay near the end of the index
id UUID PRIMARY KEY DEFAULT uuidv7()   -- built in from Postgres 18

Integers are the best for performance and I use BIGINT, never INT, because running out at 2.1 billion is a painful migration. The downside is they're guessable in URLs, and they leak volume: order 10452 tells a competitor how many orders you have.

UUIDs let clients or multiple services generate ids without a round trip. Random v4 UUIDs scatter inserts across the B-tree and hurt write performance on big tables. UUID v7 fixes most of that because it's time-ordered. So in practice: BIGINT identity internally, or UUID v7 when ids are public or generated outside the database.

Interviewers look for: natural keys change; random UUIDs hurt index locality; UUID v7 as the modern middle ground.

So these decide what happens to child rows when the parent they point at gets deleted.

Let me give you an example of why this choice matters. On one project, orders.user_id had ON DELETE CASCADE. Support deleted a test account that turned out to be a real customer, and it silently wiped two years of their orders, which also broke our revenue reports.

-- What we changed it to
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey,
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;

After that, deleting a user with orders fails loudly, and we handle account deletion as an explicit process: anonymise the user's personal data but keep the order rows.

One more gotcha: Postgres doesn't automatically index foreign key columns. A cascade or restrict check on a big child table without an index on user_id does a full scan for every parent delete.

So one-to-one is a foreign key with a unique constraint on it, or, even more simply, the child uses the parent's primary key as its own primary key.

Let me give you an example with users and profiles.

CREATE TABLE profiles (
  user_id  BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio      TEXT,
  avatar   TEXT,
  settings JSONB NOT NULL DEFAULT '{}'
);

What makes it one-to-one is that user_id is the primary key, so each user can have at most one profile row.

The reason to split instead of just adding columns to users is usually one of three things. First, performance: the users table is hit on every request for auth, and keeping it narrow means more rows per page and better caching, while the big bio text only loads on the profile page. Second, optionality: if most users never fill in a profile, I avoid a lot of NULL columns. Third, security or ownership: sensitive data like KYC documents can live in a separate table with tighter permissions.

If none of those apply, I just keep them in one table. One-to-one splits add a join for no gain.

So the simplest model is an adjacency list: each row has a parent_id pointing at another row in the same table. It's a self-referencing foreign key, and with recursive CTEs it's usually all I need.

Let me give you an example with categories.

CREATE TABLE categories (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      TEXT NOT NULL,
  parent_id BIGINT REFERENCES categories(id)
);
CREATE INDEX ON categories (parent_id);

-- Whole subtree under "Electronics" (id 1)
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

What happens is the first part picks the root, and the recursive part keeps joining children onto what's already been found until nothing new comes back.

If reads of whole subtrees dominate and the tree is deep, there are alternatives. A materialized path stores '1/4/17' on each row, so "everything under 4" is a prefix match, and Postgres has the ltree extension for exactly this. A closure table stores every ancestor-descendant pair, which makes subtree queries trivial but makes moves expensive.

So in practice I start with adjacency list plus recursive CTE, and only move to ltree or a closure table if profiling says so.

Interviewers look for: adjacency list with a recursive CTE, and knowing at least one alternative with its trade-off.

So normalization means structuring tables so that each fact is stored in exactly one place. The goal is to avoid update anomalies, where the same fact exists in several rows and they drift apart.

Let me give you an example.

-- Not normalized
orders: id | customer_name | customer_email | customer_city | product | price
        1  | Ada           | a@b.com        | London        | Laptop  | 999
        2  | Ada           | a@b.com        | London        | Mouse   | 25

-- Normalized
customers: id | name | email   | city
           1  | Ada  | a@b.com | London
orders:    id | customer_id | product | price
           1  | 1           | Laptop  | 999
           2  | 1           | Mouse   | 25

In the first version there are three problems. If Ada moves to Pune, I have to update every order row, and if I miss one, the data now disagrees with itself. That's an update anomaly. I also can't store a customer who hasn't ordered yet, which is an insert anomaly. And if I delete her only order, I lose her details entirely, which is a delete anomaly.

In the normalized version her city lives in one row. Changing it is one update, and every order sees the new value through the join.

Interviewers look for: the three anomalies (update, insert, delete), not just "less duplication".

So the way I remember them is each form fixes one kind of misplaced data.

1NF: every cell holds a single value, no lists or repeating groups.

-- Breaks 1NF
posts: id | title | tags
       1  | Joins | "sql,postgres,beginner"

-- Fixed
post_tags: post_id | tag
           1       | sql
           1       | postgres

2NF: every non-key column depends on the whole primary key. This only matters with composite keys. If order_items has key (order_id, product_id) and I also store product_name there, that depends only on product_id, so it belongs in products.

3NF: non-key columns don't depend on other non-key columns. If users has zip_code and city, and city is determined by the zip code, then city depends on zip, not on the user. The classic phrase is "the key, the whole key, and nothing but the key".

In practice I aim for 3NF and stop. BCNF and the higher forms exist, but they rarely change a real design. And honestly, the zip-to-city one I often leave, because the "fix" adds a table nobody wants to maintain.

So denormalization is copying data on purpose to avoid a join or an expensive computation on read. It trades write complexity and consistency risk for faster reads.

Let me give you an example I've actually shipped. A feed page showed posts with their comment count, and counting on every request was slow:

-- Before: count on every page load
SELECT p.*, (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id)
FROM posts p ORDER BY p.created_at DESC LIMIT 20;

-- After: a stored counter
ALTER TABLE posts ADD COLUMN comment_count INTEGER NOT NULL DEFAULT 0;

-- Updated in the same transaction as the insert
BEGIN;
INSERT INTO comments (post_id, body) VALUES (42, 'Nice');
UPDATE posts SET comment_count = comment_count + 1 WHERE id = 42;
COMMIT;

The reason I put both statements in one transaction is that if they're separate, a failure between them leaves the count silently wrong. And every path that inserts or deletes comments, including admin tools and bulk deletes, has to maintain it. For that reason I sometimes use a trigger, or I accept eventual correctness and have a nightly job that recomputes counts.

Other common cases: storing unit_price on an order item even though it's on the product, which is actually correct because I want the price at the time of purchase, and materialized views for reporting.

So my rule: normalize first, denormalize when I've measured a problem, because wrong data is invisible while a slow query is obvious.

Interviewers look for: keeping the copy in sync (transactions, triggers, reconciliation), and "normalize first, measure, then denormalize".

So no, and this is a good trick question. It looks like duplication, but it's actually a different fact. products.price is "what this costs now", and order_items.unit_price is "what this customer paid on that day".

Let me show why it has to be stored.

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL,   -- snapshot at purchase time
  PRIMARY KEY (order_id, product_id)
);

-- Price goes up next month
UPDATE products SET price = 1099 WHERE id = 5;

What happens if I don't snapshot it is that every past invoice for product 5 would suddenly show the new price. Refunds would be calculated wrong, and last year's revenue report would change. That's clearly a bug.

The same thinking applies to shipping addresses on orders, tax rates, and currency exchange rates. Anything that can change but must be remembered as-it-was belongs on the record of the event.

So the way I phrase it: normalization is about not storing the same fact twice. A historical value is a separate fact that happens to look similar.

So I wouldn't jump straight to denormalizing. Nine joins by itself isn't the problem, and Postgres handles that fine with the right indexes. I'd find out which part is slow first.

Let me walk through the order I'd go in.

1. EXPLAIN (ANALYZE, BUFFERS) the query
   - Seq scans on big tables?  → missing FK indexes
   - estimated rows way off?   → ANALYZE, extended statistics
   - huge row counts mid-plan? → join order / filter applied too late

2. Check the query itself
   - fetching columns nobody uses?
   - an OR or function on an indexed column?
   - N+1 from the ORM instead of one query?

3. Only then, reshape the data
   - materialized view refreshed on a schedule
   - a denormalized read table kept in sync
   - cache the rendered result

In my last project a dashboard query with a lot of joins took eight seconds. The EXPLAIN showed a sequential scan on order_items because the foreign key column wasn't indexed. Adding one index took it to 40 milliseconds. No schema change needed.

When it genuinely is the shape, for example an aggregated dashboard across millions of rows, I'd reach for a materialized view with REFRESH MATERIALIZED VIEW CONCURRENTLY on a schedule, or a separate read model updated from events. That keeps the normalized tables as the source of truth, and the denormalized copy can always be rebuilt from them.

Interviewers look for: measure before restructuring, and keeping denormalized data rebuildable from a normalized source of truth.

So it's a SELECT with a filter, an order and a limit. I'd write it like this.

SELECT name, email
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 10;

A few things I'm doing on purpose. I name the columns instead of SELECT *, so I only pull what I need and the query doesn't change shape when someone adds a column. And ORDER BY is required here: without it, "the 10 most recent" is meaningless, because tables have no guaranteed order.

One gotcha I'd mention: if two users have the exact same created_at, the order between them isn't stable, so paginating with OFFSET can skip or repeat rows. I add a tiebreaker:

ORDER BY created_at DESC, id DESC

And for performance on a big table, an index on (created_at DESC), or a partial one WHERE active = true if most users are inactive, lets the database read just the first 10 entries instead of sorting the whole table.

So NULL means "unknown", not zero or empty. Any comparison with an unknown value gives unknown, which is also NULL, and WHERE only keeps rows where the condition is actually true.

Let me give you examples.

SELECT NULL = NULL;   -- NULL, not true
SELECT NULL <> 1;     -- NULL
SELECT NULL + 5;      -- NULL

WHERE email = NULL    -- matches nothing, ever
WHERE email IS NULL   -- correct

The one that bites people in real code is the negative filter:

-- "everyone who isn't an admin"
SELECT * FROM users WHERE role <> 'admin';
-- silently skips users whose role is NULL

SELECT * FROM users WHERE role IS DISTINCT FROM 'admin';
-- includes them, because IS DISTINCT FROM treats NULL as a comparable value

In my last project a "non-admin users" report was missing a few hundred people for exactly this reason. They had never been assigned a role.

For defaults I use COALESCE(nickname, name), which returns the first non-null argument. And where a column should never be unknown, I just make it NOT NULL with a default, which removes the whole class of bugs.

Interviewers look for: three-valued logic (true, false, unknown), IS NULL, and the <> trap.

So it fails because SQL isn't executed in the order it's written. WHERE runs before SELECT, so at the point the filter runs, the alias total doesn't exist yet. Postgres says column "total" does not exist.

The logical order is:

1 FROM  2 JOIN  3 WHERE  4 GROUP BY  5 HAVING
6 SELECT  (aliases created here)
7 ORDER BY  8 LIMIT

So there are a few fixes:

-- Repeat the expression
SELECT price * qty AS total FROM items WHERE price * qty > 100;

-- Or wrap it, so the alias exists in the outer query
SELECT * FROM (
  SELECT price * qty AS total FROM items
) t WHERE total > 100;

-- ORDER BY runs after SELECT, so this works as is
SELECT price * qty AS total FROM items ORDER BY total DESC;

The same order explains a bunch of related errors. You can't use an aggregate like SUM(total) in WHERE, because grouping hasn't happened yet, which is exactly why HAVING exists.

One nuance I'd add: this is the logical order that defines what's legal. The planner is free to physically execute things differently, like pushing filters down or using an index to avoid sorting, as long as the result is the same.

So DELETE removes rows, TRUNCATE empties a whole table fast, and DROP removes the table itself, structure and all.

Let me give you an example of when this matters. We had to clear a 200-million-row staging table nightly. DELETE FROM staging took ages, generated enormous amounts of WAL, and left the table bloated until vacuum caught up. TRUNCATE staging just swaps in new empty files.

TRUNCATE staging RESTART IDENTITY;   -- also resets the id sequence

One gotcha: in Postgres, TRUNCATE is transactional, so I can roll it back inside a transaction. In MySQL it causes an implicit commit, so it can't be rolled back. Also, TRUNCATE refuses if other tables have foreign keys pointing at it, unless I add CASCADE, which would empty those too. I'd be very careful with that.

So SQL injection is when user input gets concatenated into a SQL string and ends up being interpreted as SQL instead of as data. The fix is parameterized queries, every time.

Let me give you an example in Node.

// Vulnerable
const q = `SELECT * FROM users WHERE email = '${req.body.email}'`;
// email = "' OR '1'='1"  → returns every user

// Safe: the value is sent separately from the SQL text
const { rows } = await pool.query(
  'SELECT id, name FROM users WHERE email = $1',
  [req.body.email]
);

What happens with the parameterized version is the database parses the SQL first, with $1 as a placeholder, and the value is sent separately. No matter what characters are in it, it can only ever be a value. Escaping by hand isn't a real fix, because there are always edge cases.

The gotcha I've seen in real code is that parameters only work for values, not identifiers. So a "sort by column" feature tempts people to concatenate:

// Allow-list identifiers instead
const SORTABLE = { name: 'name', created: 'created_at' };
const col = SORTABLE[req.query.sort] ?? 'created_at';
await pool.query(`SELECT id, name FROM users ORDER BY ${col} LIMIT 20`);

ORMs like Prisma or Drizzle parameterize for me, but their raw-query escape hatches, like $queryRawUnsafe, bring the risk right back. On top of that I give the app's database user only the permissions it needs.

Interviewers look for: parameterized queries, and the identifier allow-list case that parameters can't cover.

So in Postgres I use INSERT ... ON CONFLICT, which does the check and the write atomically against a unique constraint. Doing "select, then insert or update" in the app is a race.

Let me give you an example: tracking daily page views per page.

CREATE TABLE page_views (
  page_id BIGINT NOT NULL,
  day     DATE   NOT NULL,
  views   BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (page_id, day)
);

INSERT INTO page_views (page_id, day, views)
VALUES ($1, current_date, 1)
ON CONFLICT (page_id, day)
DO UPDATE SET views = page_views.views + EXCLUDED.views;

What happens here is if the row doesn't exist, it's inserted with 1. If it does, the conflict branch runs and EXCLUDED refers to the row I tried to insert. Two concurrent requests can't both insert, because the unique index serialises them.

The app-level version fails like this:

A: SELECT ... → not found
B: SELECT ... → not found
A: INSERT     → ok
B: INSERT     → duplicate key error (or a duplicate row if there's no constraint)

A couple of gotchas. ON CONFLICT needs a unique constraint or index to target. ON CONFLICT DO NOTHING doesn't return the existing row with RETURNING, which surprises people. And Postgres 15 added MERGE for more complex cases, though for simple upserts I still prefer ON CONFLICT because its concurrency behaviour is clearer. In MySQL the equivalent is INSERT ... ON DUPLICATE KEY UPDATE.

So the only real difference is what happens to rows that don't have a match. An inner join drops them. A left join keeps every row from the left table and fills the right side with NULLs when there's no match.

Let me give you an example: three users, and only Ada and Bo have orders.

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- Ada | 100
-- Ada | 50
-- Bo  | 30

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Ada | 100
-- Ada | 50
-- Bo  | 30
-- Cy  | NULL

What happens with the inner join is Cy just vanishes, because she has no orders. That's a real bug risk: if someone asks "show every customer and what they've spent", an inner join silently drops new customers from the report.

Notice also that Ada appears twice. A join produces one row per matching pair, so joining to a one-to-many table multiplies the left side. That's expected, but it matters as soon as I start summing things.

So in practice I ask: "should rows without a match appear?" If yes, left join. If they're meaningless without the match, inner join.

Interviewers look for: framing it around unmatched rows, and noticing that one-to-many joins multiply rows.

So there are two standard ways: a left join and check for NULL on the right side, or NOT EXISTS. They both work, and I usually reach for NOT EXISTS because it reads like the question.

-- Anti-join with LEFT JOIN
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Same thing with NOT EXISTS
SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

What happens with the first one is the left join keeps every user, and users with no orders get NULLs in all the order columns. Then WHERE o.id IS NULL keeps only those. I check the primary key, o.id, because it can't be NULL in a real row, so a NULL there definitely means "no match".

The one I avoid is NOT IN:

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

If orders.user_id contains even a single NULL, this returns zero rows, because x NOT IN (..., NULL) evaluates to unknown for every row. I verified that one the hard way. The planner also handles NOT EXISTS as a proper anti-join.

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

So the WHERE clause is undoing the left join. For users with no paid orders, the left join produces a row with NULLs on the order side, and then o.status = 'paid' evaluates to NULL for those rows, so WHERE drops them. The result is exactly an inner join.

The fix is to move the condition into the ON clause:

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid';
-- Ada | 100
-- Bo  | NULL   (has orders, none paid)
-- Cy  | NULL   (no orders)

What happens now is the status condition decides which orders match, but every user still appears. I ran this against a small test dataset and the difference is exactly that: the WHERE version returned only Ada, the ON version returned all three.

The rule I follow: conditions on the right-hand table of a left join go in ON; conditions on the left table go in WHERE. For inner joins it makes no difference to the result, which is exactly why people build the habit and then get bitten on left joins.

Interviewers look for: explaining why (NULL fails the WHERE test), not just the fix.

So that's almost always join fan-out. Joining a one-to-many table repeats the parent row once per child, and if I sum a column from the parent, it gets counted multiple times.

Let me give you an example: orders have many items and many payments.

-- Order 1: total 100, two items, one payment
SELECT SUM(o.total)
FROM orders o
JOIN order_items i ON i.order_id = o.id;
-- 200, because order 1 appears once per item

-- Joining two child tables is even worse:
-- 2 items x 2 payments = 4 rows for the same order

What happens is the join itself is correct, but the grain of the result changed from "one row per order" to "one row per order item". Summing an order-level column at item grain double counts.

The fix is to aggregate each child table before joining, so every join is one-to-one:

SELECT o.id, o.total, i.item_count, p.paid
FROM orders o
LEFT JOIN (SELECT order_id, COUNT(*) AS item_count
           FROM order_items GROUP BY order_id) i ON i.order_id = o.id
LEFT JOIN (SELECT order_id, SUM(amount) AS paid
           FROM payments GROUP BY order_id) p ON p.order_id = o.id;

In my last project a finance dashboard overstated revenue for weeks because of this. Now whenever I write a report query, I ask "what is one row of this result?" before I aggregate anything. And SUM(DISTINCT ...) is not a fix, because two different orders can have the same total.

So a self join is joining a table to itself, using two different aliases so I can treat it as two tables. It's how I answer questions about relationships between rows of the same kind.

Let me give you the classic one: employees and their managers, where manager_id points at another employee.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

What happens is e is "the employee" and m is "the manager", even though they're the same table. I use a left join so the CEO, who has no manager, still shows up with a NULL.

A more interesting real use case: finding duplicate signups.

SELECT a.id, b.id, a.email
FROM users a
JOIN users b
  ON lower(a.email) = lower(b.email)
 AND a.id < b.id;

The a.id < b.id is important. Without it, every user matches itself, and every duplicate pair appears twice, once as (1, 2) and once as (2, 1).

Other places I've used it: comparing each day's metric to the previous day, though these days I'd reach for the LAG() window function for that, which is cleaner.

So the planner has three main algorithms, and it picks one based on how many rows it expects from each side and which indexes exist.

Let me give you what it looks like in a plan.

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.email = 'ada@example.com';

Nested Loop  (rows=12)
  -> Index Scan using users_email_key on users u  (rows=1)
  -> Index Scan using orders_user_id_idx on orders o  (rows=12)

That's ideal: one user, then an index lookup for her orders. If I dropped the WHERE and joined all users to all orders, I'd expect a hash join instead, because doing millions of index lookups would be slower than one pass building a hash table.

The debugging lesson I've learned is that bad join choices usually come from bad row estimates. If the planner thinks the outer side has 1 row but it has 500,000, it picks a nested loop and the query takes minutes. So I compare estimated vs actual rows in EXPLAIN ANALYZE, and fix statistics before I touch anything else.

Interviewers look for: matching each algorithm to its sweet spot, and linking bad plans to bad estimates.

So WHERE filters individual rows before they're grouped, and HAVING filters the groups after aggregation. That's why aggregates like SUM can appear in HAVING but not in WHERE.

Let me give you an example: customers who spent over 1,000 this year.

SELECT user_id, SUM(total) AS spent
FROM orders
WHERE created_at >= '2026-01-01'   -- row filter, runs first
GROUP BY user_id
HAVING SUM(total) > 1000;          -- group filter, runs after grouping

What happens here is the database first throws away orders from before this year, then groups what's left by user, computes each sum, and finally keeps only the groups whose sum is over 1,000.

One practical point: anything that can go in WHERE should go there. If I wrote HAVING user_id = 5, it would still work, but the database would aggregate every user first and throw most of the work away. Filtering in WHERE reduces the rows before the expensive grouping step, and it can use an index.

Interviewers look for: linking it to execution order, and putting non-aggregate filters in WHERE for performance.

So COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values.

Let me show where it matters: counting orders per user with a left join.

SELECT u.name, COUNT(*) AS wrong, COUNT(o.id) AS right_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

-- Ada | 2 | 2
-- Bo  | 1 | 1
-- Cy  | 1 | 0    <- Cy has no orders

What happens is Cy still gets one row from the left join, with NULLs on the order side. COUNT(*) counts that row, so it says she has one order. COUNT(o.id) skips NULLs, so it correctly says zero. I ran this exact case to be sure, and it's a bug I've seen in production dashboards.

The same NULL-skipping applies to other aggregates. AVG(rating) ignores NULL ratings, which is usually what I want, but SUM over only NULLs returns NULL, not zero. So in reports I wrap it: COALESCE(SUM(total), 0).

And COUNT(DISTINCT user_id) is how I'd answer "how many unique customers ordered this month", which is different from how many orders there were.

So it fails because after grouping by city, each output row represents a whole city, which can contain many users with different names. The database has no way to know which name I mean, so Postgres raises "column users.name must appear in the GROUP BY clause or be used in an aggregate function".

The rule is every selected column must either be in GROUP BY or wrapped in an aggregate. So depending on what I actually want:

-- One row per city
SELECT city, COUNT(*) FROM users GROUP BY city;

-- All names per city
SELECT city, string_agg(name, ', ' ORDER BY name) AS names
FROM users GROUP BY city;

-- One row per city+name
SELECT city, name, COUNT(*) FROM users GROUP BY city, name;

A couple of nuances I'd mention. Postgres allows other columns if I group by the table's primary key, because they're functionally dependent on it: GROUP BY u.id lets me select u.name.

And older MySQL versions used to let the original query run and silently return an arbitrary name from each group. That's been off by default since MySQL 5.7 with ONLY_FULL_GROUP_BY, but I've inherited legacy code relying on it, and it returned a different "random" name after a migration.

So GROUP BY collapses many rows into one per group. A window function computes across a set of related rows but keeps every row in the output. It's for when I want the detail and the aggregate side by side.

Let me give you an example: each order with the user's total spend and a running total.

SELECT
  user_id,
  created_at,
  total,
  SUM(total) OVER (PARTITION BY user_id) AS user_total,
  SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  total * 100.0 / SUM(total) OVER (PARTITION BY user_id) AS pct_of_user
FROM orders;

What happens is PARTITION BY works like GROUP BY in that it defines the set of rows, but instead of collapsing, each row just gets the calculated value attached. Adding ORDER BY inside the window makes it cumulative, so the sum only includes rows up to the current one.

Without window functions, "each order as a percentage of that user's total" needs a subquery or a join back to an aggregated copy of the table. With them it's one pass.

I use them all the time for rankings, running totals, "compare to previous row" with LAG(), and moving averages. One gotcha: window functions run after WHERE, so I can't filter on them directly. I wrap the query in a subquery or CTE and filter outside.

Interviewers look for: "keeps the rows", PARTITION BY, and knowing you filter on a window result in an outer query.

So all three number rows in order. The difference is only how they handle ties.

Let me give you an example with scores where two people tie.

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM players;

-- name | score | row_num | rnk | dense
-- Ada  | 95    | 1       | 1   | 1
-- Bo   | 90    | 2       | 2   | 2
-- Cy   | 90    | 3       | 2   | 2
-- Dee  | 80    | 4       | 4   | 3

What happens is ROW_NUMBER always gives unique numbers, so it breaks the tie arbitrarily between Bo and Cy. RANK gives them both 2 and then skips to 4, like a sports ranking. DENSE_RANK gives them both 2 and continues at 3, with no gaps.

How I choose: ROW_NUMBER when I need exactly one row per group, like deduplicating and keeping the latest record. RANK for leaderboards where "joint second" should push the next person down. DENSE_RANK for "the top 3 distinct scores".

One gotcha with ROW_NUMBER: if the ORDER BY has ties, which row gets 1 isn't deterministic and can change between runs. So I always add a tiebreaker like id when I'm using it to pick a winner.

So this is the classic "top N per group" question, and the cleanest general answer is a window function to rank within each category, then filter in an outer query.

WITH ranked AS (
  SELECT
    p.category_id,
    p.id,
    p.name,
    SUM(oi.quantity * oi.unit_price) AS sales,
    ROW_NUMBER() OVER (
      PARTITION BY p.category_id
      ORDER BY SUM(oi.quantity * oi.unit_price) DESC, p.id
    ) AS rn
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.category_id, p.id, p.name
)
SELECT category_id, name, sales
FROM ranked
WHERE rn <= 3
ORDER BY category_id, rn;

What happens here is the GROUP BY first computes total sales per product. Then the window function runs after grouping, so it can rank those sums within each category. The outer query keeps ranks 1 to 3. I added p.id as a tiebreaker so the result is deterministic.

If the interviewer wants ties included, say two products tied for third, I'd switch to RANK() or DENSE_RANK(), and I'd say that out loud, because it's a product decision.

For performance at scale, when there are many categories and a good index, a LATERAL join can be faster, because it does an index-ordered "top 3" per category and stops early instead of ranking every product:

SELECT c.id, t.*
FROM categories c
CROSS JOIN LATERAL (
  SELECT name, sales FROM product_sales s
  WHERE s.category_id = c.id
  ORDER BY sales DESC LIMIT 3
) t;

Interviewers look for: the window-plus-outer-filter pattern, handling ties explicitly, and bonus points for LATERAL.

So a CTE, the WITH clause, is a named temporary result that exists only for that one query. It does the same job as a subquery, but it reads top to bottom instead of inside out. That's why I reach for it the moment a query has more than one level.

Let me give you an example. Say I want the top spenders and their names:

WITH big_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, b.spent
FROM users u
JOIN big_spenders b ON b.user_id = u.id
ORDER BY b.spent DESC;

What I like here is that big_spenders has a name that says what it is, and I can run that inner SELECT on its own while I'm debugging. With a nested subquery I'd be reading from the middle outwards.

A CTE can also be referenced more than once in the same query, which a subquery can't without copying it. And only a CTE can be recursive.

So in practice: a one-line IN (…) filter stays a subquery, and anything with two or more logical steps becomes a chain of CTEs.

So IN checks whether a value is in a list the subquery produces, and EXISTS just checks whether the subquery returns at least one row. For the positive case they usually give the same answer, and a modern planner like Postgres often turns both into the same semi-join.

-- users who have placed at least one order
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);

SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

The EXISTS version is correlated: it refers to u.id from the outer query. Conceptually it can stop at the first matching order, so it doesn't care how many orders there are. The SELECT 1 is just a convention, since the columns are never read.

Where the difference really matters is the negative case, NOT IN versus NOT EXISTS, because of NULLs. That's the one that bites people.

So my default is EXISTS for "is there a related row" checks, and IN for a short literal list like status IN ('paid', 'shipped').

SELECT name FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

So my first guess is that orders.user_id has at least one NULL in it, maybe from guest checkouts. If the subquery returns even one NULL, NOT IN returns nothing at all.

The reason is three-valued logic. id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. Anything compared with NULL is unknown, not true, so the whole condition is never true and every row is filtered out.

I actually checked this with three users and orders (1), (NULL):

NOT IN      -- 0 rows
NOT EXISTS  -- 2 rows (users 2 and 3)

The fix is to use NOT EXISTS, which only asks "is there a matching row" and doesn't get poisoned by NULLs:

SELECT name FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

A LEFT JOIN … WHERE o.id IS NULL also works. So as a rule I never write NOT IN against a subquery, only against a literal list I control.

Interviewers look for: naming NULL and three-valued logic as the cause, not "the planner is wrong," and knowing NOT EXISTS as the safe replacement.

So a correlated subquery references a column from the outer query, so logically it runs once per outer row. An uncorrelated one runs once in total and its result is reused.

Let me give you an example that I've seen cause trouble:

-- last order date per user, correlated in the SELECT list
SELECT u.name,
  (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

What happens here is that for every user the database runs that MAX lookup. With an index on orders(user_id, created_at), each lookup is a quick index probe and this is honestly fine. Without the index, it's a scan of orders per user. With 100k users, that's where the query goes from milliseconds to minutes.

The alternative is to aggregate once and join:

SELECT u.name, o.last_order
FROM users u
LEFT JOIN (
  SELECT user_id, MAX(created_at) AS last_order
  FROM orders GROUP BY user_id
) o ON o.user_id = u.id;

So I don't treat correlated subqueries as bad by default. Postgres can often decorrelate EXISTS into a join anyway. I just check EXPLAIN ANALYZE, and if I see a subplan running thousands of times without an index behind it, I rewrite it as a join or add the index.

So this is the classic "top N per group" problem, and I'd solve it with a window function. GROUP BY can't do it, because it collapses each group down to one row.

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY created_at DESC
         ) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn <= 3;

What happens here is that PARTITION BY user_id restarts the numbering for each user, and ORDER BY created_at DESC gives the newest order number 1. The CTE is needed because you can't filter on a window function in the same query's WHERE. Window functions are computed after WHERE.

If I only need this for a small page of users, Postgres has LATERAL, which is basically a per-row subquery that can use an index and a LIMIT:

SELECT u.id, o.*
FROM users u
CROSS JOIN LATERAL (
  SELECT * FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) o
WHERE u.id = ANY($1);

So for the whole table I use ROW_NUMBER. For "these 20 users on screen" I use LATERAL with an index on (user_id, created_at DESC). I'd also pick RANK instead of ROW_NUMBER only if ties should share a position.

Interviewers look for: window functions, knowing why the filter needs an outer query, and bonus points for LATERAL.

So a recursive CTE has two parts joined by UNION ALL: an anchor query that produces the starting rows, and a recursive part that joins back to the CTE itself. The database runs the recursive part over and over, each time feeding it only the rows produced by the last round, until a round produces nothing.

WITH RECURSIVE chain AS (
  -- anchor: start at the CEO
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE id = 1
  UNION ALL
  -- recursive step: everyone whose manager is already in the chain
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY depth;

I ran this with a CEO, a VP under them and two engineers under the VP. Round 0 gives the CEO. Round 1 finds the VP. Round 2 finds both engineers. Round 3 finds nobody, so it stops, and I get four rows with depth 0, 1, 2, 2.

One gotcha I've hit is cycles. If bad data makes A manage B and B manage A, this loops forever. I guard against it with a depth limit like WHERE c.depth < 20, or I carry a path array and skip ids already in it. Postgres 14+ also has a built-in CYCLE clause for this.

I used this in a project for threaded comments, where we stored just parent_id. One query fetched a whole thread at any depth, which is something document databases genuinely struggle with.

So it depends on the database and the version, and this is a good example of old advice going stale. Before Postgres 12, every CTE was materialized: computed in full, stored, and then read. The planner could not push filters from the outer query into it. That made CTEs an "optimization fence."

Let me give you an example of where that hurt:

WITH all_orders AS (
  SELECT * FROM orders          -- millions of rows
)
SELECT * FROM all_orders WHERE id = 42;

On old Postgres this built the whole CTE first and then filtered it. It could not use the primary key index. Since Postgres 12, a CTE that is non-recursive, side-effect free and referenced only once gets inlined like a subquery, so the id = 42 is pushed down and it's an index lookup.

I can also control it explicitly:

WITH x AS MATERIALIZED (...)       -- force: compute once, reuse
WITH x AS NOT MATERIALIZED (...)   -- force inlining

MATERIALIZED is actually useful when an expensive CTE is referenced several times, or when I want to stop the planner from choosing a bad plan.

So in practice I write CTEs for readability, and if EXPLAIN shows a "CTE Scan" over a huge result where I expected an index scan, I check whether it's being materialized and why.

Interviewers look for: knowing that the Postgres 12 change exists. It shows you know how the planner treats your SQL, not just the syntax.

So the main ones are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT. A constraint is a rule the database enforces on every write. If a write breaks it, the write is rejected.

CREATE TABLE products (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku    TEXT UNIQUE NOT NULL,
  price  NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock  INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  status TEXT NOT NULL DEFAULT 'draft'
         CHECK (status IN ('draft', 'live', 'archived'))
);

The reason I don't rely on app validation alone is that my app isn't the only thing writing to the database. There's the admin script someone runs at midnight, a second service, a backfill job, and my own code with a bug in it. Validation in the app is for nice error messages. The constraint is the guarantee.

In my last project a race let stock go to minus three during a flash sale. Two requests both read "stock is 1" and both decremented. A CHECK (stock >= 0) would have made the second update fail instead of overselling.

So I do both: validate early in the app for UX, and let the database be the last line of defence.

Interviewers look for: "the app is not the only writer." That is the core argument.

So for money I use NUMERIC, or integer cents, never FLOAT. For time I use TIMESTAMPTZ. For IDs I use BIGINT identity or a UUID, not a 32-bit SERIAL.

Let me show why floats are wrong for money. I ran this in Postgres:

-- a is FLOAT8, n is NUMERIC(10,2), both hold 0.1 and 0.2
SELECT SUM(a) = 0.3, SUM(n) = 0.3, SUM(a) FROM t;
-- f | t | 0.30000000000000004

Floats are binary, and 0.1 has no exact binary representation. Across millions of transactions those tiny errors turn into reconciliation bugs. NUMERIC is exact decimal.

For time, TIMESTAMPTZ stores an absolute instant, and converts to the session's time zone on the way out. Plain TIMESTAMP just stores "3pm" with no zone, and once you have servers or users in different zones nobody knows which 3pm it was.

For IDs, a 32-bit SERIAL tops out around 2.1 billion. That sounds like a lot until it's an events table. Migrating a primary key type on a huge live table is painful, so I start with BIGINT.

So in practice: NUMERIC or cents, TIMESTAMPTZ, BIGINT or UUID, TEXT with a CHECK for length if needed, and BOOLEAN for flags.

So the bug is almost always check-then-insert. The code does a SELECT to see if the email exists and then an INSERT. Between those two statements there's a gap, and two requests can both pass the check.

// Both requests can see "no user" and both insert
const exists = await db.query(
  "SELECT 1 FROM users WHERE email = $1", [email]);
if (exists.rowCount === 0) {
  await db.query("INSERT INTO users (email) VALUES ($1)", [email]);
}

The fix is to put a UNIQUE constraint on the column and let the database decide. Then I just try the insert and handle the error:

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

try {
  await db.query("INSERT INTO users (email) VALUES ($1)", [email]);
} catch (e) {
  if (e.code === "23505") {            // unique_violation
    return res.status(409).json({ error: "Email already registered" });
  }
  throw e;
}

A unique index has no gap. The database serializes the two inserts on that key, and the second one fails. If I don't want an exception, INSERT … ON CONFLICT (email) DO NOTHING works too.

One gotcha: Ada@x.com and ada@x.com are different strings. So I either lowercase before storing, or add a unique index on lower(email).

Interviewers look for: spotting the race, trusting the constraint over the SELECT, and knowing to handle the specific error code rather than swallowing all errors.

So a foreign key says what happens to child rows when the parent is deleted. The main options are RESTRICT or NO ACTION, which block the delete, CASCADE, which deletes the children too, and SET NULL, which keeps the children but detaches them.

CREATE TABLE comments (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id BIGINT REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE orders (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT
);

How I choose is by asking whether the child means anything without the parent. Comments on a deleted post are meaningless, so CASCADE. A comment by a deleted user can stay as "deleted user," so SET NULL. Orders are financial records, so RESTRICT: you shouldn't be able to delete a customer who has orders at all.

One gotcha I've hit: Postgres indexes the referenced side automatically, because it's a primary key, but not the foreign key column. So deleting a post with CASCADE scans the whole comments table to find children unless I add an index on comments(post_id).

So in practice I'm careful with CASCADE on anything important, since one delete can silently wipe thousands of rows, and many teams use soft deletes for exactly that reason.

So a plain UNIQUE (email) breaks here, because a deleted user still holds the email and a new user can't reuse it. What I want is a partial unique index: uniqueness enforced only on rows that match a condition.

CREATE UNIQUE INDEX users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

INSERT INTO users VALUES ('a@x.com', now());  -- deleted user: ok
INSERT INTO users VALUES ('a@x.com', NULL);   -- active user: ok
INSERT INTO users VALUES ('a@x.com', NULL);   -- ERROR: duplicate key

What happens is that deleted rows are simply not in the index, so they don't count. Any number of deleted a@x.com rows can exist, but only one active one.

A related gotcha: you might think UNIQUE (email, deleted_at) works. It doesn't, because by default NULLs are treated as distinct in a unique constraint, so two active rows with deleted_at = NULL are both allowed. Postgres 15 added UNIQUE NULLS NOT DISTINCT if you ever actually want NULLs to collide.

So partial indexes are my go-to for "unique among active," "only one default address per user," and similar rules.

Interviewers look for: partial indexes, and knowing that NULLs don't collide in a normal unique constraint.

So the naive ALTER TABLE … ADD CONSTRAINT takes a strong lock and then scans every row to validate it. On 200 million rows, that blocks reads and writes for minutes, which is an outage. The trick is to split "add the rule" from "check the old data."

-- 1. Add it without checking existing rows. Brief lock only.
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

-- 2. New writes are already checked. Now fix bad old rows in batches.
UPDATE orders SET amount = 0.01
WHERE id BETWEEN 1 AND 100000 AND amount <= 0;   -- repeat per batch

-- 3. Validate. This scans, but only takes a lock that allows writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

What happens is that NOT VALID starts enforcing the rule on new inserts and updates immediately, but skips the full scan. VALIDATE CONSTRAINT then does the scan under a SHARE UPDATE EXCLUSIVE lock, so the app keeps working.

For NOT NULL, the older trick is to add a CHECK (col IS NOT NULL) NOT VALID, validate it, and then SET NOT NULL, which Postgres can do without a rescan because the check proves it. Postgres 18 lets you add NOT NULL … NOT VALID directly.

I also always set lock_timeout before migrations like this, so if the ALTER can't get its lock quickly it fails instead of queueing behind a long query and blocking everyone behind it.

So I think about three things: whether IDs are exposed publicly, whether IDs need to be generated outside the database, and index performance.

Integers are small and fast, but /invoices/1043 tells a competitor how many invoices you have, and invites people to try 1044. They also need a round trip to the database to get the ID.

Random UUID v4 fixes that, but because each new key lands at a random spot in the B-tree, inserts touch pages all over the index. On big tables that means more cache misses and write amplification.

UUID v7 is what I'd pick today. It starts with a timestamp, so new keys land near the end of the index like integers, and it's still globally unique and generated anywhere. Postgres 18 added a built-in uuidv7(), and most languages have a library for it.

So in practice: a BIGINT for internal tables that never leave the backend, and UUID v7 for anything that shows up in a URL or is created across services.

Interviewers look for: the index locality argument against random UUIDs. Mentioning v7 shows you're current.

So "NoSQL" really means "not only relational." It's not one technology, it's a label for four quite different families that solve different problems. I find it much easier to talk about them separately.

What they have in common is that they don't force a fixed schema, they were designed to spread across machines from day one, and many of them relax consistency by default in exchange for availability and speed.

What they don't share is a query language. Knowing MongoDB tells you nothing about writing CQL for Cassandra.

So when someone says "we should use NoSQL," my first question is "which kind, for which access pattern?"

Interviewers look for: treating NoSQL as four families with different strengths, not as one alternative to SQL.

So no, not really. The schema still exists, it just moves from the database into your application code. The honest term is "schema-on-read" instead of "schema-on-write."

Let me give you an example. In MongoDB nothing stops this:

db.users.insertOne({ name: "Ada", email: "ada@x.com" });
db.users.insertOne({ name: "Bob", emial: "bob@x.com" });  // typo

The second insert succeeds. Now every piece of code that reads user.email gets undefined for Bob. In Postgres, that typo would have been an error the moment someone wrote it.

So every reader has to know which shapes of document can exist and handle old versions of them. In one project we had three generations of the user document from different eras of the app, and every read path had little if (doc.address) checks for the old shape.

So in practice I treat the schema as real either way. In a document store I define it in code with something like Mongoose or Zod, and I turn on the database's own schema validation too.

So it's mostly because of the features they gave up. The hard parts of spreading a relational database across machines are joins across machines, transactions across machines and constraints that span machines. Most NoSQL systems simply don't offer those, or offer them in a limited way, so splitting the data is much simpler.

Let me give you an example with Cassandra. Every query must include the partition key:

SELECT * FROM messages_by_room WHERE room_id = ?;

Because the partition key is required, the database hashes it and knows exactly which node owns the data. There's no query that needs to gather rows from every node and join them. Adding a node just means moving some partitions to it.

In Postgres, a JOIN between users and orders, a UNIQUE on email and a transaction touching both tables all assume the data sits together. Split it across servers and each of those becomes a distributed coordination problem.

The flip side is that the modelling burden moves to you. You have to design around the partition key from the start. And to be fair, it's no longer SQL versus NoSQL: distributed SQL systems like CockroachDB, YugabyteDB and Spanner scale horizontally with joins and transactions, they just pay for it in latency.

Interviewers look for: "they scale because they restrict what you can ask," not "NoSQL is faster."

So ACID is the guarantee relational databases give by default: Atomic, Consistent, Isolated, Durable. A committed transaction is all-or-nothing and every reader sees a correct state. BASE is the looser model many distributed NoSQL systems default to: Basically Available, Soft state, Eventually consistent.

Let me give you a scenario. A user updates their profile photo in a system replicated across three regions.

ACID (single primary):
  write commits  ->  every read after that sees the new photo

BASE (eventual):
  write accepted in one region  ->  other regions catch up in ms to seconds
  a friend in another region may see the old photo briefly

For a profile photo, BASE is completely fine, and it means the write never waits on a far-away region. For an account balance, that same delay could let someone withdraw money twice.

It's also not a strict split anymore. MongoDB has multi-document ACID transactions. DynamoDB has transactions and strongly consistent reads. Cassandra lets me choose consistency per query, for example QUORUM.

So I think of it as a dial per operation, not a property of a product. The question I ask is: what's the cost if a reader sees stale data for a second?

So let me go through them, but I'd start by saying that for a normal product company, Postgres plus Redis handles most of this, and I'd only add a specialised store when a specific access pattern demands it.

The reasoning is always the access pattern. Chat is "give me the latest 50 messages in room X," which maps perfectly to a partition per room sorted by time. Fraud is about paths through relationships, which is expensive as repeated SQL joins.

What I'd avoid is using several databases just because each is "best" at something. Every extra store is another thing to back up, monitor and keep in sync. In my last project we started search on Postgres full-text search and only moved to OpenSearch when we needed fuzzy matching and facets at scale.

Interviewers look for: justifying each choice with the query shape, and restraint about adding databases.

So a vector database stores embeddings, which are arrays of numbers that represent the meaning of text or images, and it answers "find me the items closest to this vector." It's the retrieval part of most AI search and RAG features. It isn't really a fifth family replacing the others. It's a specialised index for similarity search.

Let me give you an example with Postgres and the pgvector extension:

CREATE EXTENSION vector;

CREATE TABLE docs (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content   TEXT,
  embedding VECTOR(1536)
);

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- nearest 5 by cosine distance
SELECT id, content
FROM docs
ORDER BY embedding <=> $1
LIMIT 5;

What happens here is the HNSW index does approximate nearest-neighbour search. It trades a little accuracy for being fast on millions of vectors.

The design choice is whether to use a dedicated store like Pinecone, Qdrant or Weaviate, or keep vectors next to the data. In a recent project we kept them in Postgres, because we needed to filter by tenant_id and permissions in the same query, and it meant one less system to keep in sync. I'd move to a dedicated store when the vector count goes into hundreds of millions or the search load starts competing with normal traffic.

So a document database stores each record as a JSON-like object, and related data can be nested inside it instead of living in another table. A collection is roughly a table, a document is roughly a row, and a field is roughly a column. The big difference is that documents in one collection don't have to have the same fields.

Let me give you an example. In SQL a user with two addresses is two tables and a join. In MongoDB it's one document:

{
  _id: ObjectId("66f1c2..."),
  name: "Ada",
  email: "ada@x.com",
  addresses: [
    { type: "home", city: "London" },
    { type: "work", city: "Pune" }
  ],
  preferences: { theme: "dark" }
}

What's nice is that this maps directly to the object my API returns. One read, no join, no ORM mapping rows back into nested objects.

Instead of a join, you either embed, like I did with addresses, or you reference by id and use $lookup or a second query.

So the mental shift is that in SQL I model the data and join on the way out, and in a document store I model around how the data will be read.

So it's a good fit when records are naturally self-contained and vary in shape, and it's a bad fit when the data is heavily interconnected and you need to ask new questions of it all the time.

Let me give you the best example I know, a product catalogue:

{ name: "T-shirt", size: "M", colour: "blue" }
{ name: "Laptop",  ram: "16GB", cpu: "M4", screen: 14 }
{ name: "Book",    author: "...", pages: 320, isbn: "..." }

In a relational table I'd need dozens of mostly empty columns, or a table per category, or a messy entity-attribute-value table. Here it's just natural. Other good fits are CMS content, event payloads, user settings and IoT readings.

Where it hurts is something like an accounting system or a marketplace with orders, users, sellers, payouts and refunds all pointing at each other. There I need joins, foreign keys and transactions constantly, and I'd be rebuilding a relational database in application code.

One thing I'd add: Postgres has JSONB, so I can keep the core fields as real columns and the variable attributes in a JSON column in the same table. That covers a lot of the "we need flexibility" arguments without leaving relational.

Interviewers look for: a specific example on each side, and awareness that JSONB narrows the gap.

So there are really four things I've seen teams regret:

First, nothing stops bad data unless you turn on validation. A typo in a field name silently creates a new field.

Second, duplicated data drifts. If I copy the product name into every order for fast reads, renaming the product means updating thousands of documents, and if that job fails halfway, some orders show the old name and some the new.

// the "rename" that actually means updating every copy
db.orders.updateMany(
  { "items.productId": pid },
  { $set: { "items.$[i].name": "New name" } },
  { arrayFilters: [{ "i.productId": pid }] }
);

Third, unplanned queries are hard. Because the shape is designed around one read pattern, a question like "revenue per seller per month" from the business team may need a heavy aggregation pipeline or a data export.

Fourth, joins are clumsy. $lookup exists, but it's slower and more awkward than a SQL join, especially on large collections.

So the honest summary I'd give is that a document database moves work from the database into the application. The flexibility is real, but you take on the job of enforcing the rules yourself.

So there are two broad approaches: migrate everything up front with a script, or migrate lazily as documents are read. For big collections I usually do the lazy one, with a schema version field so the code knows what shape it's looking at.

Let me give you an example. We split name into firstName and lastName:

function upgradeUser(doc) {
  if ((doc.schemaVersion ?? 1) < 2) {
    const [firstName, ...rest] = (doc.name ?? "").split(" ");
    doc = { ...doc, firstName, lastName: rest.join(" "), schemaVersion: 2 };
    delete doc.name;
  }
  return doc;
}

// on read: upgrade in memory; on the next save, the new shape is written
const user = upgradeUser(await users.findOne({ _id }));

What happens here is that old documents keep working, and they get rewritten to v2 whenever they're next saved. Meanwhile a background job can walk the collection in batches and upgrade the rest, so eventually I can delete the v1 code path.

One gotcha: queries. If I search by lastName, un-migrated documents won't match. So for fields I filter on, I finish the backfill before switching reads over.

In practice we kept migration scripts in the repo and ran them in CI and deploys, using a tool like migrate-mongo, so schema changes were still reviewed like code.

Interviewers look for: the schema versioning pattern, and noticing that lazy migration breaks queries on the new field.

So my default is Postgres with JSONB for the flexible parts, unless the whole data model is document-shaped and the team really benefits from MongoDB's tooling or scaling model.

Let me give you what the hybrid looks like:

CREATE TABLE products (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        TEXT UNIQUE NOT NULL,
  price      NUMERIC(10,2) NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);

CREATE INDEX ON products USING gin (attributes);

SELECT * FROM products
WHERE attributes @> '{"colour": "blue", "size": "M"}';

What I get is real constraints and types on the fields that matter, like price and SKU, joins to orders, transactions, and flexible attributes that are still indexed and queryable.

Where MongoDB wins is when almost everything is nested documents, when I want built-in sharding and replica sets without extra tooling, or when the team is already very fluent in it. Its aggregation pipeline and document-level updates like $push into arrays are also nicer than editing big JSONB values.

One gotcha with JSONB: updating one key rewrites the whole value, so a huge, frequently updated JSONB blob gets expensive.

So the question I ask is: is the flexible part a detail of a relational model, or is it the model itself?

So I can index nested fields with dot notation, and if the field is an array MongoDB automatically creates a multikey index, with one index entry per array element. That's what makes { tags: "urgent" } fast.

db.users.createIndex({ "address.city": 1 });
db.posts.createIndex({ tags: 1 });            // multikey

db.posts.find({ tags: "urgent" });            // uses the index

// compound index for a common query + sort
db.orders.createIndex({ userId: 1, createdAt: -1 });
db.orders.find({ userId: 7 }).sort({ createdAt: -1 }).limit(20);

A few gotchas I've run into. A compound index can include at most one array field. If a document has an array of 5,000 tags, it has 5,000 index entries, so writes get slower and the index grows. And if I query on two fields of array elements, I need $elemMatch, otherwise the conditions can match different elements:

// matches a doc with {sku:"A", qty:1} and {sku:"B", qty:10}
db.orders.find({ "items.sku": "A", "items.qty": { $gt: 5 } });

// only matches when ONE item satisfies both
db.orders.find({ items: { $elemMatch: { sku: "A", qty: { $gt: 5 } } } });

For compound index order I follow the ESR rule: equality fields first, then the sort field, then range fields. And I always confirm with .explain("executionStats") that I'm getting an IXSCAN and not a COLLSCAN.

Interviewers look for: multikey indexes, the $elemMatch trap and the ESR rule.

So embedding means putting related data inside the parent document, and referencing means storing just an id and looking it up separately. It's the central modelling decision in MongoDB, the equivalent of designing tables in SQL.

// Embed: addresses live inside the user
{ _id: 7, name: "Ada", addresses: [{ city: "London" }] }

// Reference: the order points at the user
{ _id: 101, userId: 7, total: 42 }
// then: db.users.findOne({ _id: 7 })

With embedding, one read gets everything and a single-document write is atomic, but if the same data is used elsewhere, I've duplicated it. With referencing, the data lives once and updates in one place, but reading it needs a second query or a $lookup, and nothing enforces that user 7 actually exists.

So in practice I embed data that's owned by the parent and read with it, and reference data that's shared or grows without limit.

So I ask four questions, and the answers usually agree:

Let me apply it. Order line items: always read with the order, belong only to it, bounded, and shouldn't change after checkout. So embed. The customer on that order: shared by many orders and changes independently, so reference. Comments on a viral post: can grow to tens of thousands, so reference, one document per comment with a postId.

The deeper rule is that I model around how the data is read, not how it's naturally shaped. So before modelling I literally list the top five queries the app will run.

Interviewers look for: "bounded vs unbounded" and "owned vs shared." Starting from access patterns is the senior signal.

So there's a hard limit and a soft one. The hard limit is that a MongoDB document can't exceed 16 MB, so an array that grows forever eventually makes every write to that document fail. The soft limit hits long before that: performance.

Let me give you a scenario I've seen. Someone modelled a chat room like this:

{
  _id: "room-1",
  name: "general",
  messages: [ /* every message ever sent, 80,000 of them */ ]
}

What happens is that every time you load the room to show its name, you pull megabytes of messages over the network unless you remember to project them away. Every new message rewrites a big document. If messages is indexed, each element is an index entry, so the index balloons. And you can't page through messages efficiently.

The fix is to reference, one document per message:

// rooms
{ _id: "room-1", name: "general" }

// messages, indexed on { roomId: 1, sentAt: -1 }
{ roomId: "room-1", sentAt: ISODate("..."), text: "hi" }

If I want to keep the "latest messages in one read" benefit, I also embed just the last 20 on the room with $push and $slice. That's called the subset pattern.

So the hybrid is: store the reference id, but also embed a small copy of the fields I display most. That way the common read is a single query, and the full record is still one lookup away.

// comment
{
  postId: 1,
  author: { id: 7, name: "Ada", avatarUrl: "/a/7.png" },  // copy for display
  text: "Great post",
  createdAt: ISODate("...")
}

What this gives me is that rendering a list of 50 comments needs zero extra lookups for author names. If someone clicks the author, I use author.id to fetch the full profile.

The trade-off is staleness. If Ada changes her name, old comments keep the old one. So the question I ask is whether the copy should be a snapshot or should stay in sync. For an order, the shipping address and product price at purchase time should be a snapshot, that's the legally correct record. For a comment's display name, I might run a background job on a "user renamed" event to updateMany the copies, and accept that it's eventually consistent.

So I only copy fields that are small, read constantly and change rarely.

Interviewers look for: the snapshot vs sync question. Recognising that duplication is sometimes correct, like prices on an order, is a strong answer.

So it depends on how big each side gets and which direction I query more. There are three options.

// Option 1: array of ids on the smaller side
// student
{ _id: "s1", name: "Ada", courseIds: ["c1", "c2"] }

// Option 2: arrays on both sides (fast both ways, must keep in sync)
// course
{ _id: "c1", title: "Databases", studentIds: ["s1", "s9"] }

// Option 3: a separate enrollments collection, like a join table
{ studentId: "s1", courseId: "c1", enrolledAt: ISODate("..."), grade: "A" }

A student takes maybe ten courses, which is bounded, so courseIds on the student is fine. But a MOOC course might have 200,000 students, so studentIds on the course is an unbounded array and I'd avoid it.

The moment the relationship itself has data, like an enrolment date or a grade, I use option 3. That's really a join table, and I'd index it both ways, { studentId: 1 } and { courseId: 1 }.

One gotcha with option 2 is keeping both sides in sync. Enrolling means two writes, so I'd need a transaction or accept a brief inconsistency.

And honestly, if a model has lots of many-to-many relationships like this, it's a hint that the data is relational and Postgres might be the better choice.

So one document per reading would work, but it's wasteful: 8.6 billion tiny documents a day, each with its own _id and index entries. The classic answer is the bucket pattern, and today MongoDB has time series collections that do the bucketing for me.

Here's the manual bucket pattern, one document per sensor per hour:

{
  sensorId: "s-481",
  hour: ISODate("2026-09-26T10:00:00Z"),
  count: 3600,
  sum: 81234.5,
  readings: [ { t: 0, v: 22.4 }, { t: 1, v: 22.5 } /* ... */ ]
}

db.readings.updateOne(
  { sensorId: "s-481", hour: hourStart, count: { $lt: 3600 } },
  { $push: { readings: { t, v } }, $inc: { count: 1, sum: v } },
  { upsert: true }
);

What that gets me is 3,600 times fewer documents and index entries. The array is bounded by design, so no 16 MB problem. And the pre-computed sum and count mean an hourly average needs no scan of readings.

In practice, though, on MongoDB 5.0 and later I'd use a native time series collection:

db.createCollection("readings", {
  timeseries: { timeField: "ts", metaField: "sensorId", granularity: "seconds" },
  expireAfterSeconds: 60 * 60 * 24 * 90      // keep 90 days
});

I insert one document per reading and MongoDB buckets and compresses them internally. I'd still pre-aggregate daily rollups into a separate collection for dashboards, and I'd set an expiry so raw data doesn't grow forever.

Interviewers look for: the bucket pattern and bounded arrays; knowing native time series collections exist shows you're current.

So MongoDB queries are objects instead of sentences. The filter object is the WHERE, operators start with a dollar sign, and updates use operators like $set and $inc.

// SELECT * FROM users WHERE age > 30 AND city IN ('London','Pune')
db.users.find({ age: { $gt: 30 }, city: { $in: ["London", "Pune"] } });

// nested field and array membership
db.users.find({ "address.city": "London" });
db.posts.find({ tags: "urgent" });          // array contains "urgent"

// UPDATE users SET name = 'Ada L' WHERE _id = ...
db.users.updateOne({ _id: id }, { $set: { name: "Ada L" } });

// atomic counter and array edits
db.posts.updateOne({ _id: id }, { $inc: { views: 1 } });
db.posts.updateOne({ _id: id }, { $push: { tags: "new" } });

Two things that surprise SQL people: listing two fields in the filter is an implicit AND, and { tags: "urgent" } matches if the array contains that value, not equals it.

And $inc is atomic on the server, so a view counter doesn't need read, add one, write back, which would race.

So $set changes only the fields I name and leaves everything else alone. replaceOne swaps the entire document for the one I pass, keeping only the _id. Every field I didn't include is gone.

// { _id: 1, name: "Ada", email: "ada@x.com", role: "admin" }

db.users.updateOne({ _id: 1 }, { $set: { name: "Ada L" } });
// { _id: 1, name: "Ada L", email: "ada@x.com", role: "admin" }

db.users.replaceOne({ _id: 1 }, { name: "Ada L" });
// { _id: 1, name: "Ada L" }       email and role are gone

The classic mistake is passing a plain object as the update. With the old update() method, that silently replaced the whole document. The current Node driver and mongosh throw an error if updateOne gets no operators, which is safer. But Mongoose quietly wraps a plain object in $set for you, so the behaviour depends on which layer you're using, and that confuses people.

So my habit is always an explicit operator in updates, and replaceOne only when I really mean "this is the whole new document," like saving a full edited config.

Interviewers look for: knowing replace wipes unlisted fields. Knowing that driver and Mongoose behaviour differ is a nice bonus.

So the aggregation pipeline is MongoDB's version of GROUP BY, joins and reshaping. Documents flow through a list of stages, and each stage transforms the output of the one before, like piping Unix commands.

db.orders.aggregate([
  { $match: { status: "paid", createdAt: { $gte: ISODate("2026-01-01") } } },
  { $group: { _id: "$userId", revenue: { $sum: "$amount" }, orders: { $sum: 1 } } },
  { $sort: { revenue: -1 } },
  { $limit: 10 },
  { $lookup: { from: "users", localField: "_id", foreignField: "_id", as: "user" } },
  { $unwind: "$user" },
  { $project: { _id: 0, name: "$user.name", revenue: 1, orders: 1 } }
]);

What happens here is: $match filters like WHERE, $group sums per user, $sort and $limit keep the top ten, $lookup is a left join to users, $unwind turns the one-element array into an object, and $project shapes the output.

The order matters a lot for performance. $match goes first so it can use an index and shrink the data before grouping. And I do the $lookup after $limit, so it runs ten times instead of once per customer.

One gotcha: $group and $sort have a per-stage memory limit of 100 MB. Since MongoDB 6.0 they can spill to disk by default, but on big data I'd still rather narrow it down early than rely on that.

So the easy version, skip plus limit, gets slower the deeper you go, because the server still has to walk past every skipped document. For large collections I use range-based, or cursor, pagination on an indexed field.

// slow at page 5,000: walks 100,000 docs to throw them away
db.posts.find().sort({ _id: -1 }).skip(100000).limit(20);

// fast: jump straight to where the last page ended
db.posts.find({ _id: { $lt: lastSeenId } })
  .sort({ _id: -1 })
  .limit(20);

What happens in the second query is that the _id index lets MongoDB seek directly to lastSeenId and read 20 entries. Page 5,000 costs the same as page 1. I return lastSeenId to the client as the "next cursor."

If I'm sorting by something that isn't unique, like createdAt, I add _id as a tiebreaker, otherwise documents with the same timestamp can be skipped or repeated:

// index: { createdAt: -1, _id: -1 }
db.posts.find({ $or: [
  { createdAt: { $lt: lastTs } },
  { createdAt: lastTs, _id: { $lt: lastId } }
]}).sort({ createdAt: -1, _id: -1 }).limit(20);

The trade-off is you can't jump to "page 37." For infinite scroll and APIs that's fine. For admin tables that need page numbers, I'd keep skip but cap how deep you can go.

Interviewers look for: why skip is O(n), and the tiebreaker on non-unique sort keys.

So yes, multi-document ACID transactions have been there since 4.0 on replica sets and 4.2 on sharded clusters. The old "NoSQL has no transactions" line is out of date. But single-document writes were always atomic, and a good document model means I rarely need more than that.

const session = client.startSession();
try {
  await session.withTransaction(async () => {
    await accounts.updateOne(
      { _id: from, balance: { $gte: 500 } },
      { $inc: { balance: -500 } }, { session });
    await accounts.updateOne(
      { _id: to }, { $inc: { balance: 500 } }, { session });
    await ledger.insertOne({ from, to, amount: 500 }, { session });
  });
} finally {
  await session.endSession();
}

What happens here is all three writes commit together or none do. withTransaction also retries automatically on transient errors like write conflicts, which is why I use it instead of managing commit and abort by hand.

One gotcha: the balance: { $gte: 500 } filter doesn't throw if the balance is too low, it just matches nothing. So in real code I check modifiedCount and throw to abort.

They need a replica set, even locally, and they're slower than single-document writes. So when I find myself needing lots of transactions, I take it as a sign the data that changes together should be embedded together, or that the domain is relational.

So I start by finding which query, then look at its plan, then fix the index or the query shape. It's pretty much the same loop as in Postgres.

First I find it, using the Atlas Performance Advisor or the profiler:

db.setProfilingLevel(1, { slowms: 100 });
db.system.profile.find().sort({ millis: -1 }).limit(5);

Then I run the query with explain:

db.orders.find({ userId: 7, status: "paid" })
  .sort({ createdAt: -1 })
  .explain("executionStats");

The three things I read are the stage, COLLSCAN versus IXSCAN, then totalDocsExamined compared with nReturned, and whether there's a SORT stage, which means an in-memory sort. If it examined 400,000 documents to return 20, the index is missing or wrong.

The fix here would be a compound index following the ESR rule, equality then sort then range:

db.orders.createIndex({ userId: 1, status: 1, createdAt: -1 });

Beyond indexes, other culprits I've found: a $regex without a leading ^ that can't use the index, a $lookup on an unindexed foreignField, and fetching huge documents when a projection of three fields would do. The goal I aim for is examined roughly equal to returned.

Interviewers look for: a method, not guesses: profiler, explain("executionStats"), the examined vs returned ratio, then an ESR index.

So I layer it: validation in the app for good error messages, JSON Schema validation in the database as the guarantee, and unique indexes for anything that must not duplicate.

db.createCollection("users", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["email", "createdAt"],
    properties: {
      email:     { bsonType: "string", pattern: "^.+@.+$" },
      role:      { enum: ["user", "admin"] },
      createdAt: { bsonType: "date" }
    }
  }},
  validationLevel: "strict",
  validationAction: "error"
});

db.users.createIndex({ email: 1 }, { unique: true });

What happens is that any insert or update that breaks the schema is rejected by the server, no matter whether it came from the app, a script or someone in the shell.

For an existing collection full of messy data, I don't turn on error straight away. I use collMod with validationAction: "warn" first, which logs violations without blocking, and validationLevel: "moderate", which skips documents that were already invalid. Once the logs are clean and old documents are fixed, I switch to strict.

A unique index is the only way to prevent duplicates, same as in SQL. Checking with findOne first has the same race. In my experience, most teams who regret MongoDB regret not doing this early.

So Redis is an in-memory key-value store. You give it a key and get the value back, and the values can be richer structures like lists, hashes, sets and sorted sets. It's fast mainly because everything lives in RAM, so there's no disk seek on a read, and answers come back in well under a millisecond.

SET user:1:name "Ada"
GET user:1:name                  # "Ada"
SET session:abc "7" EX 3600      # expires in one hour
INCR page:views                  # atomic counter
HSET user:1 name Ada plan pro    # a hash, like a small object
ZADD leaderboard 420 ada         # sorted set, ranked by score

The other reason is its design: commands are executed one at a time by a single main thread, so there are no locks, and each command is atomic. Newer versions use extra threads for network I/O, but command execution is still serial.

The flip side is that memory is limited and expensive, and one slow command blocks everyone. So I use Redis alongside a main database, not instead of it.

One current detail: after Redis changed its license in 2024, the Linux Foundation forked it as Valkey, which a lot of cloud providers now offer. Redis 8 added an AGPL option. For application code they're compatible.

So in most projects I've used it for five things, and they all rely on it being fast, shared across servers and having atomic operations.

Let me give you the leaderboard, because it shows why the data structures matter:

ZINCRBY game:scores 50 "ada"
ZINCRBY game:scores 30 "bob"
ZREVRANGE game:scores 0 9 WITHSCORES   # top 10
ZREVRANK game:scores "bob"             # bob's position, 0-based

Doing "what's my rank out of 5 million players" in SQL means counting everyone above you. A sorted set answers it in logarithmic time.

What I don't use it for is anything that must never be lost, like orders or payments. Redis can persist to disk, but it's designed for data you can rebuild.

Interviewers look for: concrete uses tied to a specific data structure, and clarity that it's not the source of truth.

So the simplest version is a fixed window: one counter per user per minute, incremented on each request, with an expiry.

const key = `rate:${userId}`;
const count = await redis.incr(key);
if (count === 1) await redis.expire(key, 60);
if (count > 100) return res.status(429).end();

The bug is that INCR and EXPIRE are two separate commands. If the app crashes or the connection drops between them, the key exists with no expiry. That user's counter never resets, and they're rate-limited forever. I've seen exactly this in production after a deploy restart.

The fix is to make it atomic, either with a pipeline in a MULTI transaction or a small Lua script, which Redis runs as one command:

const count = await redis.eval(`
  local c = redis.call("INCR", KEYS[1])
  if c == 1 then redis.call("EXPIRE", KEYS[1], ARGV[1]) end
  return c
`, 1, key, 60);

The other weakness of a fixed window is bursts at the boundary: 100 requests at 0:59 and 100 more at 1:00 is 200 in two seconds. If that matters, I use a sliding window with a sorted set of timestamps, or a token bucket.

So in practice I'd usually reach for a tested library, but I want to know what it does underneath.

Interviewers look for: spotting the non-atomic INCR and EXPIRE, and knowing the fixed-window boundary burst.

So it depends on the maxmemory-policy setting. By default it's noeviction, which means once memory is full, writes start failing with an out-of-memory error while reads still work. The other policies evict keys to make room.

maxmemory 2gb
maxmemory-policy allkeys-lru

The choice depends on what's in there. For a pure cache I want allkeys-lru, since losing a cached value just means a slower read. But in a project where BullMQ jobs and cache shared one Redis, eviction would have silently deleted queued jobs. So we split them: one instance for cache with LRU, and one for the queue with noeviction, which is also what BullMQ recommends.

So my rule is: set maxmemory explicitly, set TTLs on cache keys, and keep cache and must-not-lose data on separate instances.

So yes, you can lose data, and how much depends on the persistence mode. There are two mechanisms, and you can use both.

save 900 1              # RDB: snapshot if at least 1 change in 15 min
appendonly yes          # AOF on
appendfsync everysec    # fsync once per second

With appendfsync always you lose nothing, but you've given up most of Redis's speed. And replication to a replica is asynchronous, so if the primary dies right after acknowledging a write, failover can lose it anyway.

So in practice, even with AOF on, I design as if Redis is a cache: sessions, where the worst case is a user logs in again, and cache, where the worst case is a slower request. Anything that needs real durability goes in Postgres. If I use Redis for jobs, I make sure a lost job can be detected and retried.

Interviewers look for: RDB vs AOF, and the conclusion that Redis is not your system of record even with persistence on.

So the basic lock is SET with NX, which only succeeds if the key doesn't exist, plus an expiry so a crashed holder can't keep it forever. The part people get wrong is releasing it.

const token = crypto.randomUUID();
const ok = await redis.set("lock:invoice:42", token, "NX", "PX", 30000);
if (!ok) return;                        // someone else holds it

try {
  await generateInvoice(42);
} finally {
  // delete ONLY if the lock is still mine, atomically
  await redis.eval(`
    if redis.call("GET", KEYS[1]) == ARGV[1] then
      return redis.call("DEL", KEYS[1])
    end
    return 0
  `, 1, "lock:invoice:42", token);
}

Why the token? Say my job takes 35 seconds. My lock expires at 30, worker B takes it, and then my finally runs a plain DEL. I've just deleted B's lock, and now C can get in too. Checking the token first, in one atomic script, prevents that.

But even that doesn't fully solve it. If my process pauses for a long GC or the network stalls past the expiry, I still think I hold the lock while B does too. Redis can't prevent that alone. The robust fix is a fencing token: an increasing number handed out with each lock, which the database checks and rejects if it's older than the last one it saw.

So in practice I use Redis locks to avoid duplicate work, like two cron workers sending the same email batch. For correctness, like never charging twice, I rely on a database constraint or row lock instead.

Interviewers look for: NX with expiry, token-checked release, and the honest point that a Redis lock is not a correctness guarantee.

So since commands run one at a time, the most common cause is one slow command blocking everything behind it. My checklist:

SLOWLOG GET 10          # slowest recent commands
INFO memory             # used_memory vs maxmemory, fragmentation
INFO stats              # evicted_keys, expired_keys
--bigkeys               # via redis-cli, finds huge keys

The usual culprits I've seen:

KEYS * in production. It scans every key in one blocking call. On millions of keys, that's a pause of seconds. The fix is SCAN, which iterates in small batches.

Big keys. A hash with 2 million fields, or reading a whole huge list with LRANGE 0 -1. Deleting one with DEL also blocks, so I use UNLINK, which frees memory in the background.

Mass expiry. If I cached a million keys with exactly the same TTL, they all expire together. I add random jitter to TTLs.

Memory pressure. Near maxmemory, Redis is evicting on every write. Or a snapshot fork on a big dataset causes a brief stall.

In my last project it was a debug endpoint someone had added that called KEYS session:*. P99 latency went from 1 ms to 800 ms whenever it was hit. So I also rename or disable KEYS and FLUSHALL in production config.

So Cassandra is built for enormous write volume spread across many machines with no single point of failure: things like message history, activity feeds, sensor data and logs. The catch is you have to know your queries before you design the tables, because there are no joins and you can only query efficiently by the key.

Let me give you an example for a chat app:

CREATE TABLE messages_by_room (
  room_id  UUID,
  sent_at  TIMEUUID,
  sender   TEXT,
  body     TEXT,
  PRIMARY KEY ((room_id), sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);

SELECT * FROM messages_by_room WHERE room_id = ? LIMIT 50;

What makes this fast is that all messages for a room live together on the same nodes, already sorted newest first. "Latest 50 in this room" is a single sequential read.

But if the product team asks for "all messages by user X across every room," this table can't answer it. I'd create a second table, messages_by_user, and write each message to both. In SQL that duplication is a design smell. In Cassandra it's the intended approach.

So I'd only reach for it at real scale, like billions of rows and heavy writes, where Postgres is actually struggling.

So the partition key decides which nodes a row lives on, and the clustering key decides the order of rows inside that partition.

PRIMARY KEY ((room_id), sent_at)
--           ^^^^^^^^^  ^^^^^^^
--           partition  clustering

-- efficient: one partition, a sorted range within it
SELECT * FROM messages_by_room
WHERE room_id = ? AND sent_at > ?;

-- rejected: no partition key, would hit every node
SELECT * FROM messages_by_room WHERE sender = 'ada';

What happens is Cassandra hashes room_id to pick the nodes. Within that partition, rows are stored sorted by sent_at, so range queries and "latest N" are cheap.

The second query gets rejected unless I add ALLOW FILTERING, which basically means "scan the whole cluster." That's a warning sign, not a fix.

So the rule is: every query must provide the full partition key with equality, and can then filter or sort on clustering columns in order.

Interviewers look for: distribution vs ordering, and knowing ALLOW FILTERING is a red flag.

So a hot partition is when one partition key gets a disproportionate share of the traffic or data, so the few nodes holding it are overloaded while the rest of the cluster sits idle. Adding nodes doesn't help, because that one partition still lives on the same replicas.

Let me give you a scenario. We partitioned events by date:

PRIMARY KEY ((event_date), event_time, event_id)

Every write today goes to the same partition, so one set of replicas takes 100% of the writes. Same problem with partitioning a chat by room_id when one room is a 2-million-member announcement channel.

The fix is to spread it with a better key or a bucket:

-- spread each day across 16 buckets
PRIMARY KEY ((event_date, bucket), event_time, event_id)
-- bucket = hash(event_id) % 16

-- or for chat, bound partition size by time
PRIMARY KEY ((room_id, day), sent_at)

The trade-off is on reads: to get all of today's events, I now query 16 partitions in parallel and merge. For the chat case, loading older history means stepping back one day at a time.

I also watch partition size, not just traffic. A rough guideline is to keep partitions well under about 100 MB, because huge partitions make compaction and repairs painful.

So Cassandra lets me choose the consistency level per query. Each piece of data is copied to several replicas, usually three, and the consistency level says how many of them must respond before the read or write counts as successful.

The key idea is this: if writes and reads both use QUORUM, then 2 + 2 is greater than 3, so every read overlaps with at least one replica that saw the latest write. That gives me read-your-writes behaviour while still tolerating one node down.

R + W > N   ->  reads see the latest write
2 + 2 > 3   ->  QUORUM + QUORUM is consistent

In a multi-region setup I'd use LOCAL_QUORUM, so a request only waits for replicas in its own data centre, not across an ocean.

So in practice: LOCAL_QUORUM for most things, and ONE for high-volume data where a slightly stale read is harmless, like a view counter.

Interviewers look for: the R + W > N rule, which also comes up in general distributed systems questions.

So a graph database stores connections as first-class data. There are nodes, like people and accounts, and edges between them, like FRIEND or PAID. Following an edge is a direct pointer hop, not an index lookup and join. It shines when the question is about paths through relationships, several hops deep.

Let me give you "friends of friends" in Cypher, Neo4j's query language:

MATCH (me:Person {name: "Ada"})-[:FRIEND]->()-[:FRIEND]->(fof)
WHERE NOT (me)-[:FRIEND]->(fof) AND fof <> me
RETURN DISTINCT fof.name;

In SQL, that's a self-join on a friendships table per hop. Two hops is fine. But questions like "is this new account within four hops of a known fraud account through shared devices, cards or addresses" mean a variable-depth search, and the joins explode.

So where I'd pick a graph database is fraud detection, recommendations, access control with nested groups and network or dependency analysis.

Where I wouldn't: an app with users, posts and follows. A foreign key and an indexed join do that fine, and a recursive CTE covers occasional hierarchy walks. I'd need a real multi-hop question at the core of the product before adding a whole new database.

So Cassandra uses a log-structured merge tree, an LSM tree. A write never updates anything in place. It's appended to a commit log on disk and put into an in-memory table, and that's it, the write is done. Later the memtable is flushed to an immutable file on disk called an SSTable. Sequential appends are about the cheapest thing a disk can do, which is why writes are fast.

write  ->  commit log (append)  +  memtable (memory)
memtable full  ->  flush to SSTable (immutable)
background     ->  compaction merges SSTables

The cost moves to reads: a row might be spread across several SSTables, so a read may check multiple files and merge them. Bloom filters and compaction keep that manageable.

Deletes are the interesting part. Since files are immutable, a delete writes a marker called a tombstone. The data is only removed during compaction after gc_grace_seconds, which is 10 days by default, so replicas that were down have time to learn about the delete.

One gotcha I've read about in real incidents: using Cassandra as a queue, inserting and deleting rows in the same partition constantly. Reads then scan over thousands of tombstones to find a few live rows. Cassandra warns, and past a threshold it fails the query outright.

So I avoid delete-heavy patterns. For data that ages out, I use TTLs and time-bucketed partitions, so whole old partitions expire together.

Interviewers look for: LSM trees and append-only writes, and tombstones as the consequence. The queue anti-pattern is a strong concrete example.

So an index is a separate, sorted data structure the database keeps next to the table, usually a B-tree, that maps a column's values to where the rows live. Without one, a lookup means checking every row. With one, the database walks down a few levels of the tree and jumps straight to the match.

Let me give you an example.

-- 10 million users, no index on email
SELECT * FROM users WHERE email = 'ada@example.com';
-- Seq Scan: reads all 10M rows

CREATE INDEX idx_users_email ON users(email);
-- Index Scan: about 3-4 page hops down the B-tree

What happens here is the cost goes from O(n) to O(log n). And because a B-tree is wide, with hundreds of keys per page, ten times more data usually adds just one more level. The leaves are also linked in order, so the same index makes range queries like created_at > … and ORDER BY cheap. The database can read the rows in index order and skip the sort.

So in practice, indexes are the first thing I check when a query is slow. It's the biggest performance lever you have, in SQL and in MongoDB alike.

Interviewers look for: a real mental model (sorted structure, O(log n), linked leaves), not just "it makes things faster".

So because indexes aren't free. Every index has to be updated on every insert, every delete, and every update that touches its columns, and each one takes up disk and memory. It's a straight trade: faster reads, slower writes.

Let me give you an example. Say an orders table has eight indexes. One insert is now nine writes: the row plus eight index entries. Each of those can cause page splits, and all of it goes through the write-ahead log. On a write-heavy table, like an events or logging table, that really shows up in write latency.

There's a memory cost too. The indexes you actually use want to live in RAM. If they're bloated with ones nobody queries, they push useful pages out of the cache.

So in practice I index what my queries actually need: the columns in WHERE, JOIN and ORDER BY, every foreign key, and lookup fields like email or slug. Every so often I also check for dead weight. In Postgres, that's this:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;       -- never used since stats were reset

One gotcha: Postgres doesn't index the referencing side of a foreign key automatically, while MySQL's InnoDB does. That's a common source of slow joins and slow cascading deletes in Postgres.

So the rule is the leftmost prefix. A composite index is sorted by the first column, then by the second within that, and so on. A query can use it efficiently as long as it filters on a prefix of those columns, from the left.

WHERE user_id = 1                                   -- yes
WHERE user_id = 1 AND status = 'paid'               -- yes
WHERE user_id = 1 AND status = 'paid'
  AND created_at > now() - interval '7 days'       -- yes, fully
WHERE user_id = 1 ORDER BY status, created_at       -- yes, no sort needed
WHERE status = 'paid'                               -- no: skipped user_id
WHERE user_id = 1 AND created_at > '2026-01-01'     -- partly: seeks on user_id only

I think of it like a phone book sorted by surname, then first name. You can find "Smith" or "Smith, John", but not everyone called John.

The reason I order the columns this way is: equality filters first, then the range or sort column last. Once you hit a range condition, the columns after it can't be used for seeking any more.

One nuance: newer engines can sometimes "skip scan" a missing leading column when it has very few distinct values. MySQL 8 has had that for a while, and Postgres 18 added it for B-trees. But I wouldn't design around it. If WHERE status = … on its own is a hot query, it gets its own index.

Interviewers look for: equality columns before range columns, and knowing why a range condition stops the index from narrowing further.

So first I'd run EXPLAIN ANALYZE to confirm it. Then I go through a short list, because it's almost always one of these:

-- 1. A function on the column: the index stores email, not LOWER(email)
WHERE LOWER(email) = 'a@b.com'
-- fix: CREATE INDEX ON users (LOWER(email));

-- 2. Leading wildcard: sort order can't help
WHERE name LIKE '%ada%'          -- use a trigram (pg_trgm) index or full-text search

-- 3. Type mismatch forces a cast on the column
WHERE phone = 5551234            -- phone is text, so compare to '5551234'

-- 4. Wrong column order in a composite index (leftmost rule)

-- 5. Low selectivity
WHERE is_active = true           -- 95% of rows match; a scan is cheaper

Number five is the one people argue with. The planner is cost-based. If the query returns a big chunk of the table, jumping around the index and then randomly into the table is slower than just reading it sequentially. So the planner is right to ignore the index.

The last one is stale statistics. If the table grew fast and ANALYZE hasn't run, the planner's row estimates are off. In EXPLAIN ANALYZE I compare estimated rows with actual rows. If they're off by orders of magnitude, I run ANALYZE users; and check again.

So in practice, I don't reach for index hints first. I figure out why the planner thinks the scan is cheaper.

So a covering index contains every column the query needs, so the database can answer from the index alone and never visit the table. A clustered index is different: it means the table's rows are physically stored in that index's order.

Let me give you an example of covering.

-- hot query on an order list page
SELECT status, total FROM orders WHERE user_id = 42;

-- Postgres 11+: key on user_id, carry the other columns in the leaf
CREATE INDEX idx_orders_user_cover ON orders (user_id) INCLUDE (status, total);
-- plan: Index Only Scan

What happens there is the leaf entries already hold status and total, so there's no extra hop into the table for each row. In Postgres, one gotcha is that index-only scans rely on the visibility map. On a table that's heavily updated and not vacuumed enough, it still ends up checking the heap.

For clustering, it depends on the engine. In MySQL's InnoDB, the table is the primary key's B-tree. Secondary indexes store the primary key, so a secondary lookup is two tree walks. That's also why a random UUIDv4 primary key hurts InnoDB inserts, and why I'd pick a time-ordered ID like UUIDv7. Postgres stores rows in an unordered heap. CLUSTER reorders it once, but doesn't maintain that order.

Interviewers look for: the extra lookup a covering index avoids, and knowing that InnoDB clusters on the primary key while Postgres uses a heap.

So B-tree is my default, and it covers equality, ranges and sorting. But Postgres has a few specialised options, and each one solves a specific problem I've actually run into.

-- Partial: index only the rows you query
CREATE INDEX ON jobs (created_at) WHERE status = 'pending';
-- Unique email, but only among non-deleted users (soft deletes)
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;

-- Expression: match what the query actually computes
CREATE INDEX ON users (LOWER(email));

-- GIN: "contains" queries on JSONB, arrays, full-text
CREATE INDEX ON products USING GIN (attributes jsonb_path_ops);
SELECT * FROM products WHERE attributes @> '{"color":"red"}';

-- BRIN: huge append-only tables where values follow insert order
CREATE INDEX ON events USING BRIN (created_at);

In my last project, the jobs table had tens of millions of finished rows and a few hundred pending ones. A partial index on status = 'pending' was tiny and stayed in memory, and the worker poll query went from hundreds of milliseconds to under one.

BRIN is the other one people don't know about. It only stores min and max per block range, so it can be a thousand times smaller than a B-tree. It works well for time-series data, but it's useless if the column isn't correlated with physical order.

For GiST, I'd mention geospatial data with PostGIS and exclusion constraints, like "no overlapping bookings". And in MongoDB, the same ideas show up as partial, compound, multikey and text indexes.

So a transaction groups several operations into one unit that either fully happens or doesn't happen at all. ACID is the four guarantees that make that trustworthy.

Let me give you an example: the classic money transfer.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- or ROLLBACK, and neither update happened

• Atomicity: if the second update fails, or the server crashes between the two, the first one is undone. Money is never created or destroyed.
• Consistency: the database's rules hold before and after. A CHECK (balance >= 0) or a foreign key can't end up violated by a committed transaction.
• Isolation: another transaction reading these balances never sees the halfway state, where 500 has left one account and not arrived in the other.
• Durability: once COMMIT returns, the change survives a power cut.

One thing I'd add: isolation is the one with a dial. Databases offer several isolation levels, and the default usually isn't the strictest. So "ACID" doesn't automatically mean "no race conditions".

Interviewers look for: a concrete example for each letter, and awareness that isolation isn't all-or-nothing.

So nothing half-done survives. That's because of the write-ahead log. Before the database changes the actual data pages, it appends a record of the change to the WAL and flushes that to disk. A transaction only counts as committed once its commit record is safely in the log.

Let me walk through both cases.

BEGIN;
UPDATE accounts ... id = 1;   -- logged
-- server crashes here, before COMMIT

-- on restart: recovery replays the WAL
-- no commit record for this transaction, so its changes are treated as never happened

BEGIN; UPDATE ...; UPDATE ...; COMMIT;   -- commit record flushed
-- crash a millisecond later, data pages not yet written

-- on restart: recovery replays the log and reapplies both updates

What happens here is the data files can lag behind. That's fine, because the log is the source of truth. It's also much faster than writing every changed page straight away, since the log is sequential appends.

The nice side effect is that the same log drives replication. Postgres streams WAL to its replicas, and MySQL's binlog plays a similar role.

One gotcha: durability depends on config. In Postgres, if someone set synchronous_commit = off for speed, a crash can lose the last few hundred milliseconds of commits the app thought had succeeded. The data stays consistent, but those recent commits are gone.

So the key thing is that a transaction belongs to one connection. I have to check out a single client from the pool, run BEGIN, my queries and COMMIT on that same client, and always release it.

// Wrong: each pool.query may use a DIFFERENT connection
await pool.query("BEGIN");
await pool.query("UPDATE accounts ...");   // not in the transaction
await pool.query("COMMIT");

// Right
const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [500, 1]);
  await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [500, 2]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}

What happens with the wrong version is really nasty. BEGIN runs on connection A, and the updates autocommit on B and C. Connection A goes back to the pool with an open transaction, so the next request that borrows it silently runs inside it.

The finally matters too. If I forget release() on an error path, the pool leaks one connection per failure, and eventually every request hangs waiting for a client.

In practice, with an ORM I use its helper, like Prisma's $transaction or Drizzle's db.transaction. It handles all of this for me.

Interviewers look for: the fact that a transaction lives on one connection, and the finally { release() }.

So no, and it's a really common mix-up. They share a word but mean completely different things.

Let me give you an example. A single Postgres primary with async read replicas is fully ACID. Every transaction on the primary keeps the constraints intact. But it is not CAP-consistent from the app's point of view. If I write to the primary and read from a replica 10 ms later, I can get the old value.

The other subtle point is that ACID consistency is partly on the application. The database enforces the constraints you declared. If the rule is "an order total equals the sum of its line items" and you never wrote a constraint for it, the database won't protect it for you.

So two reasons. First, a transaction holds locks and a connection for as long as it's open, so a slow third-party call turns into blocked rows and an exhausted pool. Second, the external call can't be rolled back. The database and Stripe can end up disagreeing.

Let me give you an example of what goes wrong.

BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = 7;   -- row now locked
  -- call Stripe: takes 4 seconds, charge succeeds
  -- then our INSERT fails, or the process gets killed
ROLLBACK;
-- the customer was charged, but the order says unpaid

What I do instead is split it into steps. I write a pending record in a short transaction. Then I call the provider with an idempotency key, so a retry can't double-charge. Then I confirm in a second short transaction, and I also listen to the provider's webhook as the source of truth.

When the database change needs to trigger something external, like sending an email or publishing an event, I use the outbox pattern. I insert an outbox row in the same transaction as the business change, and a worker picks it up and delivers it after the commit. That way the event exists if, and only if, the change committed.

Interviewers look for: the fact that you can't roll back a side effect, plus idempotency keys and the outbox pattern.

So in MongoDB, a single-document write has always been atomic. That's a big reason to embed data that changes together. Multi-document ACID transactions arrived in 4.0 for replica sets and 4.2 for sharded clusters, and they work much like SQL transactions, through a session.

const session = client.startSession();
try {
  await session.withTransaction(async () => {
    await accounts.updateOne({ _id: 1 }, { $inc: { balance: -500 } }, { session });
    await accounts.updateOne({ _id: 2 }, { $inc: { balance: 500 } },  { session });
  });
} finally {
  await session.endSession();
}

withTransaction also retries on transient errors, which is the main reason I use it over manual start and commit. They do cost more than single-document writes, and there's a default 60-second limit. So if I'm reaching for them constantly, I take that as a hint my schema should embed more.

Across microservices, each with its own database, there's no shared transaction. Two-phase commit exists, but it's fragile and ties services together, so in practice I use a saga. It's a sequence of local transactions, each with a compensating action. For example: reserve stock, charge payment, and if shipping fails, refund and release the stock. The steps are wired together with events through an outbox so nothing gets lost. The trade-off is that you accept eventual consistency and have to design for the in-between states.

So a race condition is when two operations run at the same time and the result depends on their timing. The classic database version is read-then-write: two requests both read a value, both decide based on it, and both write.

Let me give you an example: one item left in stock.

// Both requests run this at the same moment
const { stock } = await db.one("SELECT stock FROM products WHERE id = $1", [id]);
if (stock > 0) {
  await db.query("UPDATE products SET stock = $1 WHERE id = $2", [stock - 1, id]);
  await createOrder();   // both get here: one item sold twice
}

What happens here is that both requests read 1, both pass the check, and both write 0. Two orders, one item, and stock still says 0, so nothing even looks wrong.

The simplest fix is to move the check into the write, so the database does it atomically:

UPDATE products SET stock = stock - 1
WHERE id = $1 AND stock > 0;
-- rowCount 0 means sold out

The row lock on the update serialises the two requests. The second one re-checks stock > 0 against the committed value and matches zero rows. In practice, any time I see "SELECT, then an if, then UPDATE" in a code review, I flag it.

Interviewers look for: spotting the gap between the read and the write, and closing it with a conditional atomic update.

So pessimistic locking assumes conflicts will happen and prevents them. You lock the row up front and others wait. Optimistic locking assumes conflicts are rare and detects them. You check at write time that nobody changed the row since you read it.

-- Pessimistic
BEGIN;
SELECT * FROM seats WHERE id = 12 FOR UPDATE;   -- others block here
UPDATE seats SET held_by = 99 WHERE id = 12;
COMMIT;

-- Optimistic: a version column
UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;    -- $3 = the version I read
-- 0 rows updated means someone else saved first

The way I choose is by contention and how long the lock would last. For concert ticket seats, where lots of people hit the same rows in a short burst, I'd go pessimistic. It's better to queue them than have most of them fail and retry.

For user-facing edits, like two people editing the same document or an admin form, I'd go optimistic. The time between reading and saving can be minutes, and you can't hold a database lock across a human thinking. So I send the version to the client, and on a conflict I show "someone else changed this, please review".

ORMs have optimistic locking built in. Mongoose uses __v with optimisticConcurrency, JPA has @Version, and in Prisma I just add the version condition to the where.

So isolation levels are a dial between correctness and concurrency. The SQL standard defines four, by which read anomalies they allow.

Let me give you an example of each anomaly:

• Dirty read: I see another transaction's uncommitted balance change, and then it rolls back.
• Non-repeatable read: inside one transaction, I read a row's price twice and get two values, because someone committed in between.
• Phantom: I run COUNT(*) WHERE status = 'pending' twice and get new rows the second time.

Real engines don't map exactly onto the standard. Postgres treats Read Uncommitted as Read Committed. Its Repeatable Read is snapshot isolation, so it prevents phantoms too, but it still allows write skew. MySQL's InnoDB uses next-key locks to prevent most phantoms at Repeatable Read.

So in practice, I leave the default on and handle specific races with atomic updates or row locks. I only switch to Serializable for flows where correctness really depends on it.

Interviewers look for: a concrete example of each anomaly, and knowing your database's default.

So that's a lost update. Read Committed only guarantees you never read uncommitted data. It doesn't stop two transactions reading the same committed value and both writing back something based on it.

// Both requests
const { views } = await db.one("SELECT views FROM pages WHERE id = 1");  // both read 41
await db.query("UPDATE pages SET views = $1 WHERE id = 1", [views + 1]);  // both write 42

What happens here is that the computation happens in my app, so the database just sees two unrelated "set to 42" statements. They run one after another without complaint.

The fix is to let the database do the arithmetic:

UPDATE pages SET views = views + 1 WHERE id = 1;

In Postgres, the second update waits on the first one's row lock. Once the first commits, it re-reads the latest row version before applying + 1, so I get 43. MongoDB is the same with $inc instead of read, modify, save().

At very high write rates, say a viral page, even that one hot row becomes a lock bottleneck. There I'd batch increments in Redis with INCR and flush them to the database periodically, or spread the count over a few shard rows and sum them.

So a deadlock is two transactions each holding a lock the other one needs, so neither can move. The database detects the cycle and kills one of them.

-- T1: transfer from 1 to 2         -- T2: transfer from 2 to 1
UPDATE accounts ... WHERE id = 1;   UPDATE accounts ... WHERE id = 2;
UPDATE accounts ... WHERE id = 2;   UPDATE accounts ... WHERE id = 1;
-- waits for T2                     -- waits for T1: cycle
-- Postgres: ERROR: deadlock detected (SQLSTATE 40P01), one is rolled back

Postgres checks for deadlocks after deadlock_timeout, one second by default, and aborts one victim. The other one then continues normally.

For prevention, the main rule is to always take locks in a consistent order. For transfers, I update the lower id first, whichever direction the money goes:

const [first, second] = [fromId, toId].sort((a, b) => a - b);

Beyond that, I keep transactions short and lock fewer rows, with good indexes so updates don't lock more than they need.

And because deadlocks can never be fully ruled out under load, the app should treat that error as retryable. I wrap critical transactions in a small retry helper that retries on 40P01 and 40001 a few times with backoff. In my last project, a bulk-import job that updated rows in random order deadlocked with the API. Sorting the batch by id made it go away.

Interviewers look for: consistent lock ordering, and treating the deadlock error as retryable.

So write skew is when two transactions each read the same data, each make a decision that's valid on its own, and write to different rows. Together they break a rule. No single row conflicts, so row locks and snapshot isolation don't catch it.

Let me give you an example: the textbook one is doctors on call. The rule is that at least one doctor must stay on call.

-- Alice and Bob both click "go off call" at the same time
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call AND shift = 5;   -- both see 2
UPDATE doctors SET on_call = false WHERE name = 'alice';     -- Bob's updates 'bob'
COMMIT;
-- result: nobody on call

In Postgres, Serializable uses serializable snapshot isolation. It tracks read and write dependencies, and if the transactions couldn't have happened in some serial order, it aborts one with SQLSTATE 40001, "could not serialize access". So the second commit fails, and my code retries it. On the retry it sees only one doctor and refuses.

The trade-off is that I must build retries in, and under heavy contention I'll see more aborts. The alternatives are to lock explicitly, with SELECT … FOR UPDATE on the rows the decision depends on, or to turn the rule into a constraint. For "no double-booking a room", a Postgres exclusion constraint on a time range is the cleanest fix.

So I'd use FOR UPDATE SKIP LOCKED. Each worker locks the next available row, and instead of waiting on rows other workers already hold, it just skips past them.

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending' AND run_at <= now()
ORDER BY run_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- process it, then
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

What happens here is that ten workers can run this at the same moment. Each gets a different row, with no double-processing and no queue of workers blocked on the same row. If a worker crashes mid-job, its transaction rolls back, the lock is released, and the job is picked up again.

The catch is that holding a transaction open for a long job is bad for the reasons I mentioned earlier. So for longer work, I claim the job quickly: update it to running with a locked_until timestamp, commit, do the work outside the transaction, then mark it done. A reaper resets jobs whose lease expired. I'd pair that with a partial index on pending jobs so polling stays cheap.

Honestly, libraries like pg-boss or Graphile Worker already do exactly this. In a real project I'd use one before writing my own, unless the need is tiny.

So I don't guess, I ask the database. EXPLAIN ANALYZE runs the query and shows the plan it actually used, with real row counts and timings for each step.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Limit  (actual time=812.4..812.4 rows=20)
  ->  Sort  (actual time=812.4..812.4 rows=20)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (rows=1840) (actual time=0.1..805.3 rows=1840)
              Filter: (customer_id = 42)
              Rows Removed by Filter: 4998160

What this tells me is that it read five million rows to keep 1,840, then sorted them. The fix is obvious:

CREATE INDEX ON orders (customer_id, created_at DESC);
-- now: Index Scan, stops after 20 rows, under 1 ms

My checklist goes like this. First, is there a Seq Scan with a huge "Rows Removed by Filter"? That means a missing index. Second, do estimated rows and actual rows differ wildly? That means stale stats, so I run ANALYZE. Third, is there an expensive Sort or a Nested Loop over lots of rows? Then I check the node with the biggest actual time.

One gotcha: EXPLAIN ANALYZE really executes the statement. For an UPDATE or DELETE, I wrap it in BEGIN and ROLLBACK.

Interviewers look for: measuring before changing anything, and being able to read a Seq Scan versus an Index Scan.

So N+1 is when you run one query to get a list, and then one more query for each item in it. It's invisible with ten rows in development, and it falls over with real data.

// 1 query for posts + 1 per post = 101 round trips
const posts = await Post.find().limit(100);
for (const p of posts) {
  p.author = await User.findById(p.userId);
}

What happens here is that each query is fast on its own, maybe 1 ms. But 101 network round trips add up to hundreds of milliseconds, and it grows linearly with the page size.

There are three fixes, depending on the stack:

-- 1. Let the database join
SELECT p.*, u.name FROM posts p JOIN users u ON u.id = p.user_id;

// 2. Eager load in the ORM
await prisma.post.findMany({ include: { author: true } });
await Post.find().populate("author");

// 3. Batch it: 2 queries, however many posts
const ids   = [...new Set(posts.map(p => p.userId))];
const users = await User.find({ _id: { $in: ids } });
const byId  = new Map(users.map(u => [String(u._id), u]));
posts.forEach(p => { p.author = byId.get(String(p.userId)); });

In GraphQL it's even easier to trigger, because each resolver fetches on its own. That's what DataLoader exists for: it batches all the userId lookups in one tick into one query.

In practice, I turn on query logging in development. When a page fires 200 queries, it's obvious straight away.

So OFFSET doesn't jump anywhere. The database still has to produce and throw away every skipped row. Page 5,000 with 20 per page means walking 100,000 rows to return 20.

-- gets slower the deeper you go
SELECT * FROM posts ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- keyset (cursor) pagination: same speed at any depth
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)       -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- index: (created_at DESC, id DESC)

What happens with keyset is that the database seeks straight to the cursor position in the index and reads 20 entries. The cost doesn't depend on how deep you are.

A gotcha I've hit: if you only sort by created_at, rows with the same timestamp get skipped or duplicated across pages. The id tiebreaker makes the order unique. The row comparison (created_at, id) < (…) handles that neatly in Postgres.

There's a second win too. With OFFSET, if a new post is inserted while someone is scrolling, everything shifts and they see a duplicate. Cursors don't have that problem.

The trade-off is that you lose "jump to page 37". So I use keyset for feeds, infinite scroll and APIs, which is what most public APIs return as a next_cursor. I use offset for small admin tables where page numbers matter.

Interviewers look for: why OFFSET costs grow with depth, and the tiebreaker column.

So it moves data you don't need, it can block index-only scans, and it makes your code fragile when the schema changes.

Let me give you an example from a real project. We had an articles list endpoint doing SELECT *. The table had a body column averaging 40 KB of HTML. The list only showed title, author and date. Twenty rows was almost a megabyte over the wire from the database and through JSON serialisation, for about 2 KB of useful data.

-- before
SELECT * FROM articles ORDER BY published_at DESC LIMIT 20;

-- after: roughly 400x less data
SELECT id, title, slug, author_id, published_at
FROM articles ORDER BY published_at DESC LIMIT 20;

The other reasons: if I only select indexed columns, Postgres can do an index-only scan, but * forces it to visit the table. Large values in Postgres are stored out of line (TOAST), and fetching them has a real cost. And when someone adds a column, SELECT * quietly starts returning it. I've seen that leak a password_hash into an API response.

ORMs have the same problem. By default Prisma and Mongoose fetch every field, so on hot paths I use Prisma's select or a Mongoose projection.

So "fast locally" usually means small data, a warm cache, and no concurrency. I start by finding out whether the time is actually spent in the database, and then which query.

There are really four steps I follow:

1. Trace the request. With APM or OpenTelemetry spans, I can see whether it's one 2-second query, 300 small ones (N+1), or not the database at all.
2. Find the heavy queries. In Postgres, pg_stat_statements ranks queries by total time.
3. Get the real plan. I run EXPLAIN (ANALYZE, BUFFERS) against production-sized data, like a replica or a restored snapshot. A plan on a 50-row dev table tells me nothing.
4. Check the environment. I look for lock waits in pg_stat_activity, pool exhaustion where requests queue for a connection, and bloat or stale stats.

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

In my last project, the culprit was a query that was fine for most customers. One enterprise customer had 2 million rows, and the planner chose a nested loop based on an estimate for a typical customer. Fixing the stats target on that column and adding a composite index sorted it out.

So in practice, I sort by total time, not the slowest single run. A 5 ms query called a million times a day matters more than one 3-second report.

Interviewers look for: a systematic process with real tools (tracing, pg_stat_statements, production-sized plans) rather than guessing.

So no. A seq scan is only bad when it's reading a big table to return a small fraction of it. If the query needs a large share of the rows, reading the table start to finish is genuinely cheaper than using an index.

Let me give you an example.

-- small table: fits in a couple of pages, a scan is instant
SELECT * FROM countries WHERE code = 'GB';

-- low selectivity: most rows match
SELECT * FROM users WHERE is_active = true;   -- 90% of 5M rows

-- whole-table work
SELECT status, count(*) FROM orders GROUP BY status;

What happens with an index here is that for every match, the database jumps to a random page in the table. Random I/O over most of the table costs more than one sequential pass, which disks and the OS read-ahead are good at. Postgres models this with random_page_cost versus seq_page_cost. For in-between cases it uses a Bitmap Index Scan, which collects matching pages first and then reads them in physical order.

One tuning gotcha: random_page_cost defaults to 4, which assumes spinning disks. On SSDs or cloud storage, many teams lower it to around 1.1, so the planner stops over-favouring scans.

So in practice, before "fixing" a seq scan I check the numbers. How many rows came back, compared with how many are in the table? If it's most of the table, the scan is the right plan.

So opening a database connection is expensive: a TCP handshake, TLS, authentication, and on Postgres a whole new backend process on the server. A pool opens a set of connections once, then lends them to requests and takes them back.

import pg from "pg";
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,   // fail fast instead of hanging forever
});

// borrows a connection, runs the query, returns it automatically
const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", [id]);

What happens without a pool is that every request pays tens of milliseconds for setup, often more than the query itself. Under load you can also open thousands of connections and hit the database's limit.

A pool also caps concurrency. With max: 20, request number 21 waits in line in the app rather than piling onto the database, and that's usually what you want. Postgres doesn't get faster with more active connections than it has cores. It just thrashes.

So in practice, I create exactly one pool per process, at module level, and reuse it everywhere. Creating a pool per request is a bug I've seen more than once.

Interviewers look for: what connection setup costs, and the fact that the pool also protects the database by capping concurrency.

So the key thing is that the number that matters is the total across all instances, not the per-process setting. And bigger is usually not better.

Let me give you the math from a real setup:

Postgres max_connections       = 100 (default), minus a few reserved for superuser
API: 8 pods x pool max 20      = 160   -- over the limit
+ 2 worker pods x 10           =  20
+ migrations, cron, admin tools

Fix: 8 x 8 + 2 x 5 = 74, leaving headroom

What happens when you blow past the limit is that new connections fail with "sorry, too many clients already". It usually happens during a deploy or an autoscale event, when old and new pods overlap and it's hardest to debug.

For the per-instance size, I start small, around 10 to 20, and measure. A common rule of thumb for the database's total active connections is roughly twice the CPU cores. Beyond that, the extra connections just queue inside the database and fight over CPU and locks. The metrics I watch are pool wait time in the app and active connections in pg_stat_activity.

If I really need more instances than the database can hold connections for, the answer isn't raising max_connections a lot, since each one costs server memory. I put PgBouncer or a managed pooler in front.

So with serverless, every concurrent invocation is its own instance with its own pool. A traffic spike to 500 concurrent Lambda or Vercel functions can mean 500 or more connections at once, and a normal Postgres runs out long before that. Instances also freeze and die without closing their connections cleanly.

The fixes fall into three buckets:

• A pooler in front of the database. PgBouncer, RDS Proxy, Supabase's Supavisor, or the pooled connection strings from Neon. Thousands of client connections get multiplexed onto a small number of real ones.
• HTTP or WebSocket drivers. The Neon serverless driver or PlanetScale's driver send queries over HTTP, so there's no long-lived TCP connection to leak. Prisma Accelerate is the same idea for Prisma.
• Tiny pools per instance. Each function handles one request at a time anyway.

// created outside the handler: reused while the instance stays warm
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 1 });

export async function handler(event) {
  const { rows } = await pool.query("SELECT ...");
  return rows;
}

One gotcha: poolers usually run in transaction mode, so session features break. That means named prepared statements on older PgBouncer versions, SET commands, advisory locks and LISTEN. With Prisma, I point the runtime at the pooled URL and give migrations the direct URL.

Interviewers look for: the connection-multiplication math, and a named solution: a pooler, an HTTP driver, or a managed proxy.

So that pattern, fine and then hung and then fixed by a restart, is almost always a connection leak. Some code path borrows a client from the pool and never gives it back. After enough of those, the pool is empty and every new request waits for a connection that will never come.

Let me give you an example of the bug.

const client = await pool.connect();
const user = await client.query("SELECT ...");
if (!user.rows.length) {
  return res.status(404).end();   // leaked: never released
}
client.release();

The fix is always try/finally around a checked-out client, or just pool.query() for one-off queries, since it releases for you.

To confirm it, I'd look at two things. On the app side, pool.totalCount, idleCount and waitingCount, and a leak shows as idle at 0 and waiting climbing. On the database side:

SELECT state, count(*), max(now() - state_change) AS oldest
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;
-- lots of "idle in transaction" for minutes = someone opened BEGIN and never finished

As guard rails, I set connectionTimeoutMillis so requests fail loudly instead of hanging. On the database, idle_in_transaction_session_timeout kills sessions that were abandoned mid-transaction.

So it's about when PgBouncer hands the real server connection back. In session mode, a client keeps one server connection for its whole session. In transaction mode, it only holds a server connection for the length of one transaction. Between transactions, that connection goes to someone else.

Transaction mode is what gives you the big win, and it's what serverless setups use. The price is that anything that assumes "my connection" breaks.

A gotcha I hit: we used SET search_path per tenant at the start of a request. In transaction mode, the next query could land on a different server connection with a different tenant's search_path. The fix was SET LOCAL inside an explicit transaction, which resets at commit. The other option was to stop relying on session state altogether.

So in practice: transaction mode for app traffic, and a direct connection or session mode for migrations, LISTEN consumers and anything using advisory locks.

So replication means keeping live copies of the database on other machines, updated automatically from the primary. The usual setup is single-leader: all writes go to one primary, and it streams its changes to one or more replicas.

There are really three reasons I'd set it up:

• High availability. If the primary dies, a replica gets promoted and we're back in seconds instead of restoring from backup.
• Read scaling. Most apps read far more than they write, so I can send reports, search pages and analytics to replicas and take load off the primary.
• Isolation of heavy work. A long analytics query or a backup job runs on a replica and doesn't slow down user traffic.

Application
  | writes              | reads
  v                     v
PRIMARY --WAL stream--> Replica 1, Replica 2

In Postgres, that stream is the write-ahead log, which is the same log used for crash recovery, shipped over the network. MySQL uses its binlog, and MongoDB replica sets use the oplog.

What replication doesn't do is scale writes. Every replica still applies every write. When writes are the bottleneck, that's where sharding comes in.

Interviewers look for: both reasons, availability and read scaling, and knowing that replication doesn't help write throughput.

So no, and it's an important distinction. A replica protects you from a machine failing. A backup protects you from data being wrong. Replication copies every change faithfully, including the mistakes.

Let me give you an example.

-- someone runs this against production at 14:02
DELETE FROM orders;          -- forgot the WHERE

-- 14:02 + a few milliseconds: every replica has also deleted every order

What happens here is that the replicas did exactly their job. The same goes for a bad migration, a bug that corrupts rows, or ransomware. It all replicates.

So what you need alongside replicas is point-in-time recovery: regular base backups plus archived WAL, so you can restore to 14:01:59. Managed services like RDS, Cloud SQL, Neon and Supabase give you this with a retention window. Some teams also run a delayed replica, with Postgres's recovery_min_apply_delay set to an hour, as a quick way back after human error.

And the part people skip: a backup you've never restored is just a hope. In practice, I want a scheduled restore test into a scratch environment.

So that's replication lag. The write went to the primary, the next read was routed to a replica, and the replica hadn't applied the change yet. Async replicas are usually milliseconds behind, but under load or during a big migration it can be seconds.

await primary.query("UPDATE users SET name = 'Ada L' WHERE id = 1");
res.redirect("/profile");

// next request
const user = await replica.query("SELECT * FROM users WHERE id = 1");
// may still say 'Ada' -- the user thinks the save failed

The guarantee you want here is called read-your-writes consistency. A few ways I've implemented it:

• Sticky to primary after a write. After a user writes, I set a short-lived flag, like a cookie or session value lasting a few seconds, and route that user's reads to the primary until it expires. It's simple and covers most cases.
• Route by data ownership. A user's reads of their own profile, cart or settings always go to the primary. Other people's data can come from replicas.
• Wait for the replica to catch up. Record the primary's WAL position after the write and only read from a replica that has replayed past it. It's more precise, and more work.

So in practice, I only send reads to replicas when slightly stale data is acceptable, like feeds, search and dashboards. That's the whole design decision.

Interviewers look for: naming it (replication lag, read-your-writes) and giving at least one concrete routing fix.

So it's about when the primary tells the client "committed". With async, the primary commits locally and ships the change afterwards. With sync, it waits until at least one replica confirms it has the change.

Let me give you the scenario that matters. With async, a user places an order and gets "success", and 50 ms later the primary's disk dies before the change reached the replica. We fail over, and that order simply doesn't exist.

What most production setups do is a middle ground. In Postgres, you can set synchronous_standby_names = 'ANY 1 (r1, r2)', so a commit waits for any one of two replicas. One slow replica doesn't block writes, but every acknowledged commit exists on two machines. Managed "multi-AZ" setups generally do something similar with a standby in another zone. Postgres even lets you choose per transaction with SET LOCAL synchronous_commit, so I can make payments sync and page-view logging async.

So failover is promoting a replica to become the new primary when the old one dies, then pointing the application at it. Managed databases do it automatically, usually within a minute. But several things can go wrong.

Here's the sequence and the risks:

1. Detect the failure. Health checks miss for a few seconds. Set that too aggressive and a network blip triggers a needless failover. Too lax and you're down longer.
2. Pick and promote a replica. Ideally the most up-to-date one. With async replication, any commits it never received are lost.
3. Fence the old primary. This is the dangerous one: split brain. If the old primary was only cut off, not dead, and comes back still accepting writes, you now have two primaries and diverging data. Tools like Patroni use a consensus store (etcd) and fencing so only one node can hold the leader lock.
4. Redirect clients. Through a DNS or endpoint switch, or a proxy.

On the app side, what I make sure of:

// the pool must drop dead connections and reconnect
pool.on("error", (err) => logger.warn({ err }, "idle client error"));
// short DNS TTL / connect timeout so we find the new primary
// retry idempotent operations on connection errors

One gotcha I've seen: JVM or Node processes that cached the old DNS result keep hammering the dead host until they're restarted. The other lesson is to actually test failover. Most managed services let you trigger one, and it's the only way to know the app recovers without anyone having to step in.

Interviewers look for: split brain and fencing, lost async writes, and app-side reconnect behaviour.

So the difference is who's allowed to accept writes, and that decides how you deal with conflicts.

Let me give you a concrete case for each. Single-leader is my default for almost everything. It's simple, and the only real downsides are write latency for users far from the primary, plus the failover window.

Multi-leader makes sense when users in Europe and Asia both need fast local writes. But then two regions can edit the same record at once, and you need a resolution strategy. Last-write-wins silently drops data, so for something like collaborative editing you'd use CRDTs.

Leaderless with quorums, where W + R > N (say N=3, W=2, R=2), means any read overlaps with the latest write. The system stays writable even when nodes are down. It's great for huge write-heavy workloads like event logs, but you give up multi-row transactions and joins.

So in practice I'd stay single-leader until a concrete latency or availability requirement forces something else, because conflict handling is real engineering work.

So replication copies the same data to several machines. Partitioning splits one table into chunks on the same machine. Sharding splits the data across different machines, so each one holds a different slice.

The key thing I'd point out is that replication doesn't help writes at all. Every replica still has to apply every write. If the primary is write-bound, adding replicas does nothing. Sharding is the only one of the three that actually spreads the write load.

In practice they're combined. A sharded setup usually has each shard replicated too, because otherwise losing one machine loses a slice of your data.

Interviewers look for: "replication scales reads, sharding scales writes," and knowing that partitioning is a single-node feature.

So honestly, as late as I possibly can. Sharding is one of those decisions that's very hard to undo and makes every query, migration and on-call incident more complicated.

Before sharding I'd work through this list:

1. Fix slow queries and missing indexes. That's usually the real problem.
2. Scale up vertically. A big Postgres box handles tens of thousands of transactions per second.
3. Add read replicas for read-heavy load.
4. Add caching for hot reads.
5. Partition or archive old data so the working set stays small.
6. Split by service, like moving analytics or event logs into their own database.

I'd only shard when the write volume or the total data size genuinely exceeds what one primary can handle, and I can show that with numbers. Even then I'd look at managed options first, like Citus for Postgres, Vitess for MySQL, or a distributed SQL database like CockroachDB or Spanner, before hand-rolling shard routing in the application.

Interviewers look for: restraint. Candidates who jump straight to sharding for a startup-scale problem lose points.

So a good shard key does two things: it spreads data and traffic evenly, and it keeps the data that's queried together on the same shard. If I get either wrong, sharding hurts more than it helps.

Let me give you an example. Say we're sharding a multi-tenant SaaS app.

What I check is: high cardinality, even distribution, and does it appear in the WHERE clause of my most common queries? If the main queries don't include the shard key, every one becomes a scatter-gather across all shards.

One gotcha with tenant_id is the one giant customer. If one tenant is 30% of the data, that shard is overloaded. The fix is to isolate big tenants onto dedicated shards, or use a compound key like (tenant_id, user_id) for them.

Interviewers look for: tying the key to access patterns, and spotting the monotonic-key hot spot.

So range sharding keeps nearby keys together, which is great for range scans but creates hot spots. Hash sharding spreads keys evenly but makes range queries hit every shard.

Range:  ids 1–1M -> shard A, 1M–2M -> shard B   (new ids all go to the last shard)
Hash:   shard = hash(user_id) % 4               (even, but "ids 100–200" hits all 4)

The problem with plain hash % N is what happens when I add a fifth shard. Going from % 4 to % 5 changes the target for roughly 80% of keys, so almost all the data has to move.

Consistent hashing fixes that. Both keys and shards are placed on a ring, and a key belongs to the next shard clockwise. Adding a shard only takes over keys from its neighbour, so roughly 1/N of the data moves. In practice each physical node gets many virtual nodes on the ring so the load evens out. That's how DynamoDB and Cassandra do it.

Another common approach is a fixed number of logical shards, say 1024, mapped to physical servers through a lookup table. Rebalancing is just moving whole logical shards and updating the map. That's simpler to reason about and it's what I'd do if I were building it myself.

So I reach for partitioning when a single table gets huge and most queries only touch a recent slice of it. Logs, events, metrics, audit trails. It's all still one database, so I keep joins, transactions and foreign keys to other tables.

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  user_id    bigint NOT NULL,
  created_at timestamptz NOT NULL,
  payload    jsonb,
  PRIMARY KEY (id, created_at)          -- must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Retention: instant, no giant DELETE, no bloat
DROP TABLE events_2025_09;

Partition pruning is the planner skipping partitions that can't match. If I query WHERE created_at >= now() - interval '7 days', it only scans this month's partition. I'd confirm that in EXPLAIN.

Two gotchas I've hit. First, primary keys and unique constraints must include the partition key, so I can't have a globally unique id on its own. Second, a query that doesn't filter on created_at scans every partition, so it can be slower than before. I'd also automate creating future partitions, with pg_partman or a cron job, because inserts fail if no partition matches.

Interviewers look for: the retention win (drop vs delete) and the unique-constraint limitation.

So you lose most of the things that made a single relational database pleasant: cross-shard joins, cross-shard transactions, global unique constraints, and auto-increment IDs.

Here's how I'd handle each:

• IDs. A per-shard SERIAL gives duplicates. I'd use UUIDv7 (time-ordered, so indexes stay friendly) or a Snowflake-style 64-bit ID with a timestamp, a machine ID and a sequence.
• Joins. Choose the shard key so related data co-locates, like everything for one tenant. For small reference tables such as countries or plans, replicate them to every shard.
• Transactions. Design so a business operation stays inside one shard. When it can't, use a saga with compensating steps, or the outbox pattern, instead of two-phase commit.
• Uniqueness. "Email is unique" across shards needs a separate lookup table keyed by email, which is effectively its own small shard.
• Queries without the key. Build a secondary index table or push data into a search system like Elasticsearch.

-- Global uniqueness via a lookup table, sharded by email
INSERT INTO email_index (email, user_id, shard_id)
VALUES ($1, $2, $3);          -- fails on conflict, before creating the user

So in practice, the shard key choice decides how many of these workarounds I need. Get it right and 95% of queries never leave one shard.

So that's a hot shard, and it's either a hot key or a skewed distribution. The first thing I do is figure out which.

I'd look at the top queries on that shard, with pg_stat_statements or the database's slow log, and group them by shard key value. In my last project this turned out to be one enterprise customer running a bulk import. One tenant was generating 40% of all writes.

The fixes depend on the cause:

• One huge tenant. Move it to a dedicated shard. With a directory-based mapping that's just updating the lookup after copying its data.
• One hot key, like a celebrity's post. Sharding can't split one key. I'd cache reads for it, and for writes like counters, split the key into sub-keys (likes:post9:0 to likes:post9:15) and sum them on read.
• Monotonic key. If everything new lands on one shard, the key is wrong. Hash it or add a prefix.
• Uneven ranges. Split the shard, or rebalance logical shards across servers.

Short term I'd rate-limit the noisy client so everyone else stays up, then do the structural fix.

Interviewers look for: measuring before acting, and knowing that sharding can't fix a single hot key.

So CAP says that when a distributed database has a network partition, meaning some nodes can't talk to each other, it has to choose between consistency and availability. It can't give you both during that failure.

Let me give you an example. I have two replicas, one in Mumbai and one in Frankfurt, and the link between them drops. A user in Frankfurt reads their balance.

• Choose consistency (CP): Frankfurt says "I can't confirm this is current," and returns an error or waits.
• Choose availability (AP): Frankfurt answers with its copy, which might be missing a withdrawal made in Mumbai a second ago.

The common misquote is "pick two of three." You can't really opt out of partition tolerance, because networks fail whether you like it or not. So the real choice is C or A, and only during a partition. When the network is healthy, a good system gives you both.

So for a bank balance or seat booking I want CP. For a like count or a news feed, AP is fine. Showing a slightly stale count is better than an error page.

Interviewers look for: correcting "pick two," and that CAP only applies during a partition.

So eventual consistency means if writes stop, all replicas will converge on the same value, just not instantly. For a short window, different readers can see different values.

The question I actually ask is: what goes wrong if this piece of data is a few seconds stale?

So it's not a per-database decision, it's a per-feature one. In the same app I'll read the feed from a replica or cache, and do the checkout against the primary inside a transaction.

So this is almost always replication lag. The write went to the primary, the next read went to a replica that hadn't caught up yet. The user is missing read-your-writes consistency.

POST /profile   -> primary   (name = "Laeba")
GET  /profile   -> replica   (still name = "L. F.", 300ms behind)

There are a few fixes, and I'd pick depending on scale:

• Sticky primary after a write. After a user writes, route their reads to the primary for a few seconds. I usually set a short-lived cookie or flag in the session.
• Read own data from the primary. Anything that's "my profile" or "my settings" always hits the primary. Other people's data can come from replicas.
• Wait for the replica. Return the write's position (the LSN in Postgres) and have the read wait until the replica has replayed past it.
• Update the UI optimistically. The client shows what it just saved, so the stale read doesn't matter.

It can also be a cache that wasn't invalidated, so I'd check that too. In my experience the "sticky primary" approach solves 90% of these with very little code.

Interviewers look for: naming "read-your-writes" and giving a routing fix, not "just make replication synchronous."

So the textbook labels are: Postgres with a single primary, MongoDB with majority write concern, etcd and ZooKeeper are CP. Cassandra, DynamoDB by default, and Riak are AP. But it's not really that simple, because most modern databases let you tune it per operation.

Let me give you examples.

// MongoDB: I choose per operation
await orders.insertOne(doc, { writeConcern: { w: "majority" } });
await orders.find(q).readConcern("majority");     // leans CP
await feed.find(q).readPref("secondaryPreferred"); // leans AP, may be stale

// DynamoDB
GetItem({ ..., ConsistentRead: true })   // strongly consistent read
GetItem({ ..., ConsistentRead: false })  // default, eventually consistent

Cassandra does the same with consistency levels like ONE vs QUORUM. And even plain Postgres becomes "AP-ish" the moment I read from an async replica.

So in interviews I'd say CAP is a property of how a specific operation is configured, not a label stamped on the product. I'd also mention PACELC as a better model: even when there's no partition, you trade latency against consistency. Waiting for a majority ack is safer but slower.

So PACELC extends CAP: if there's a Partition, choose A or C; Else, when things are normal, choose Latency or Consistency.

The reason it's more useful is that partitions are rare, but the "else" branch is every single request. Every time I write, I'm choosing whether to wait for other replicas to confirm.

So when I design a system I'm really asking: how much latency will I pay on every write to avoid ever reading stale data? For payments I pay it. For activity feeds I don't.

Interviewers look for: knowing that the latency cost exists even without failures.

So in a leaderless system like Cassandra or Dynamo-style stores, each value lives on N replicas. A write waits for W of them to acknowledge, a read asks R of them. If R + W > N, the set of nodes I read from must overlap with the set that took the latest write, so at least one response has the newest version.

N = 3 replicas: A, B, C
W = 2  -> write v2 lands on A and B (C still has v1)
R = 2  -> read from any two: {A,B}, {A,C} or {B,C}
         every pair includes A or B, so v2 is always seen
         the client takes the newest version and repairs C

The common setting is N=3, W=2, R=2. I can shift it: W=3, R=1 gives fast reads but writes fail if any node is down. W=1, R=1 is fastest but gives up the guarantee.

One gotcha: quorum alone isn't full linearizability. With concurrent writes, sloppy quorums during failures, or last-write-wins with clock skew, you can still see anomalies. That's why systems that need strict correctness, like a booking or a bank ledger, use a consensus protocol such as Raft or Paxos with a single leader per range, which is what etcd, CockroachDB and Spanner do.

So cache-aside means the application owns the cache. On a read, I check the cache first. On a miss, I load from the database and put it in the cache with a TTL. On a write, I update the database and delete the cache key.

async function getProduct(id) {
  const key = `product:${id}`;
  const hit = await redis.get(key);
  if (hit) return JSON.parse(hit);

  const product = await db.query("SELECT * FROM products WHERE id = $1", [id]);
  await redis.set(key, JSON.stringify(product), "EX", 300);
  return product;
}

async function updateProduct(id, data) {
  await db.query("UPDATE products SET name = $1 WHERE id = $2", [data.name, id]);
  await redis.del(`product:${id}`);   // delete, don't overwrite
}

What I like about it is that the cache only holds what's actually requested, and if Redis goes down the app still works, just slower.

The reason I delete on write instead of setting the new value is concurrency. Two writers setting the cache can finish in the wrong order and leave the old value there. Deleting means the next read reloads the truth. And I always keep a TTL as a safety net, so even if an invalidation is missed, the data can only be wrong for so long.

Interviewers look for: delete-on-write rather than update-on-write, and a TTL as a backstop.

So I cache data that's read far more often than it changes, is expensive to produce, and where a bit of staleness is acceptable. If it fails any of those three, caching usually just adds bugs.

Let me give you examples from an e-commerce app:

• Good: product details, category pages, the homepage's "top sellers" aggregate, a user's permissions, results of a slow report.
• Bad: the cart during checkout, stock counts at the moment of purchase, anything that's already a 1ms primary-key lookup.

I also check the hit rate after adding one. If a cache is only hitting 20% of the time, the keys are too specific, like including a timestamp or a full query string, and it's mostly overhead.

And before any of that, I make sure the query itself is good. If a query is slow because of a missing index, caching hides it until the cache is cold, and then everything falls over at once. So in practice: index first, measure, then cache what's still slow.

So they differ in who loads the cache and when writes reach the database.

By default I use cache-aside. I'd consider write-behind only for data where losing a few seconds is acceptable and write volume is massive, like view counters. In my last project we incremented view counts in Redis with INCR and a job flushed them to Postgres every 30 seconds, which took thousands of tiny updates per second off the database.

Interviewers look for: naming the durability risk of write-behind.

So a stampede, or thundering herd, is when a hot key expires and hundreds of requests miss at the same time. They all run the same expensive query together, and the database falls over exactly when it's busiest.

There are three fixes I use:

// 1. Lock so only one request rebuilds
async function getHomepage() {
  const hit = await redis.get("homepage");
  if (hit) return JSON.parse(hit);

  const gotLock = await redis.set("lock:homepage", "1", "NX", "EX", 10);
  if (!gotLock) {
    await sleep(50);
    return getHomepage();          // someone else is rebuilding, retry
  }
  try {
    const data = await buildHomepage();          // expensive
    await redis.set("homepage", JSON.stringify(data), "EX", 300);
    return data;
  } finally {
    await redis.del("lock:homepage");
  }
}

// 2. Jitter so keys don't all expire together
const ttl = 300 + Math.floor(Math.random() * 60);

The third is refreshing early. Serve the cached value but, if it's close to expiring, kick off a background refresh. That's the "stale-while-revalidate" idea, and for something like a homepage it means users never see a miss at all.

In practice I retry with a cap in the lock version rather than recursing forever, and in production I'd put a small limit on retries and fall back to the database.

So it depends on maxmemory-policy. The default is noeviction, which means once Redis hits maxmemory, writes start failing with an OOM error. For a pure cache that's the wrong setting.

# redis.conf for a cache
maxmemory 2gb
maxmemory-policy allkeys-lru

For a cache I'd use allkeys-lru, or allkeys-lfu if I have a stable set of popular keys and occasional scans that would otherwise push them out.

One gotcha I've hit: using the same Redis for cache and for things that must not disappear, like sessions or job queues. With an allkeys policy those get evicted too. I'd separate them into different instances, or use volatile-lru and make sure only cache keys have TTLs.

So there's a race between a reader filling the cache and a writer invalidating it. The reader loads the old value from the database, the writer updates and deletes the key, and then the reader writes the old value back into the cache.

Reader: cache miss
Reader: SELECT -> gets price = 100
Writer: UPDATE price = 120
Writer: DEL product:7
Reader: SET product:7 = 100      (stale, stays until TTL)

It's rare, but on hot keys with frequent writes it happens. A few fixes, from simplest:

• Short TTL on data that changes often, so the window is small.
• Delayed double delete. Delete, then delete again a second later. Crude but effective.
• Versioning. Store a version or updated_at with the value, and only set the cache if the version is newer, using a small Lua script so the check and set are atomic.
• Invalidate from the change stream. Consume the database's change data capture, like Debezium reading the Postgres WAL, and delete keys from there. Then invalidation can't be skipped by a code path that forgot.

Another cause is the delete happening before the transaction commits. If I delete inside the transaction, a reader can reload the old row before commit. So I always invalidate after commit.

Interviewers look for: being able to draw the interleaving, and "invalidate after commit."

So penetration is requests for keys that don't exist in the database. Every request misses the cache and hits the DB, because there's nothing to cache. It's often an attacker scanning random IDs.

const user = await db.getUser(id);
if (!user) {
  await redis.set(`user:${id}`, "null", "EX", 60);   // cache the miss, briefly
  return null;
}

Caching the "not found" with a short TTL fixes most of it. For large ID spaces, a Bloom filter in front tells me "definitely doesn't exist" without touching anything.

A hot key is the opposite: one key getting so much traffic that a single Redis node, which is single-threaded for commands, becomes the bottleneck. Think a flash-sale product or a viral post.

For that I'd add a tiny in-process cache in each app server, even a 1-second TTL, so most reads never leave the process. Or I'd replicate the key as product:7:0 to product:7:9 and read a random copy. Redis 6+ also has client-side caching with invalidation messages, which is a cleaner version of the local cache.

So a migration is a versioned file that changes the schema, checked into the repo alongside the code. A migration tool keeps a table of which ones have run, and applies the rest in order on every environment.

migrations/
  20260901_create_users.sql
  20260915_add_phone_to_users.sql

-- The tool tracks what's applied
SELECT * FROM schema_migrations;
--  version
--  20260901
--  20260915

The reason I'd never change production by hand is reproducibility. With migrations, a new developer runs one command and gets the exact same schema as prod. CI can spin up a fresh database for tests. And code review sees every schema change next to the code that depends on it.

Tools I've used: Prisma Migrate and Drizzle Kit in TypeScript projects, Knex, Flyway on the JVM side, and plain SQL files with something like dbmate. They all work the same way.

One rule I stick to: never edit a migration that has already run anywhere shared. If it's wrong, I write a new one that fixes it.

Interviewers look for: reproducibility, and "migrations are immutable once applied."

So an ORM gives me type safety, less boilerplate and built-in migrations, at the cost of hiding the SQL, which makes it easy to write slow queries without noticing.

// Prisma: nice, typed, readable
const posts = await prisma.post.findMany({
  where: { authorId: userId, published: true },
  include: { comments: true },
  orderBy: { createdAt: "desc" },
  take: 20,
});

For CRUD like that I'm happy with an ORM. Where it falls down is reporting queries, window functions, CTEs, bulk upserts, anything Postgres-specific. For those I drop to raw SQL, still parameterized, through the ORM's escape hatch like prisma.$queryRaw, or I use a query builder like Drizzle or Kysely that stays close to SQL but is still typed.

The biggest risk is not knowing what SQL is being generated. So in development I turn on query logging, and if a page makes 50 queries I know I have an N+1.

So my honest take is: I use an ORM for productivity, but I read the SQL it generates, and I'm comfortable writing the query by hand when it's the better tool.

So the risky part isn't adding the column, it's anything that rewrites or scans the whole table while holding an exclusive lock. I'd do it in steps.

-- 1. Add nullable column. Metadata-only, instant.
ALTER TABLE orders ADD COLUMN currency text;

-- 2. Deploy code that writes currency on every new row.

-- 3. Backfill in batches, not one giant UPDATE
UPDATE orders SET currency = 'INR'
WHERE id IN (
  SELECT id FROM orders WHERE currency IS NULL LIMIT 10000
);   -- repeat until 0 rows

-- 4. Add the constraint without scanning under a lock
ALTER TABLE orders ADD CONSTRAINT orders_currency_nn
  CHECK (currency IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;  -- no write lock

-- 5. Now SET NOT NULL uses the validated check, no full scan (PG 12+)
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_currency_nn;

A nuance worth knowing: since Postgres 11, ADD COLUMN ... DEFAULT 'INR' NOT NULL with a constant default is actually instant too, because the default is stored in the catalog. But a volatile default like gen_random_uuid() still rewrites the table, and backfilling from other columns needs the batched approach anyway.

I'd also set lock_timeout on migration sessions, like SET lock_timeout = '5s'. Even a quick ALTER has to wait for the lock, and while it waits, every query behind it queues up. Failing fast and retrying is much safer.

Interviewers look for: batched backfill, NOT VALID then VALIDATE, and lock_timeout.

So I use the expand-and-contract pattern. During a deploy, old and new code run side by side, so every step has to work with both versions. A straight RENAME COLUMN breaks the old code instantly.

Let me walk through renaming name to full_name:

1. Expand. Add full_name. Deploy code that writes to both columns and reads from name.
2. Backfill. Copy name into full_name in batches.
3. Switch reads. Deploy code that reads from full_name, still writing both.
4. Stop writing the old one. Deploy code that only uses full_name.
5. Contract. A later migration drops name.

Dropping a column is just steps 4 and 5: first ship code that no longer references it, and only then drop it. One gotcha with ORMs is that some of them select every column by default, so removing it from the code isn't enough. The model has to stop mapping it, or old instances will throw on "column does not exist."

It's more deploys than a quick rename, but it's the only way to change schema on a system that can't have a maintenance window.

So a normal CREATE INDEX takes a lock that blocks all writes to the table until it finishes. On a big table that can be minutes of failed inserts. CONCURRENTLY builds the index while writes continue.

CREATE INDEX CONCURRENTLY idx_orders_customer
  ON orders (customer_id);

-- If it fails, it leaves an INVALID index behind
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_orders_customer;   -- then retry

The catches I've run into:

• It can't run inside a transaction block. Many migration tools wrap every migration in a transaction by default, so I have to disable that for this file.
• It's slower, because it scans the table twice and waits for existing transactions to finish.
• If it fails, for example on a duplicate while building a unique index, it leaves an invalid index that still slows down writes. I have to drop it and retry.
• A long-running transaction elsewhere can make it wait indefinitely.

So in practice every index migration on a production table uses CONCURRENTLY, and that migration has no other statements in it.

So N+1 is when I load a list with one query, then trigger one more query per item to load a relation. With 100 posts, that's 101 round trips instead of one or two.

// N+1: one query for posts, then one per post for the author
const posts = await prisma.post.findMany({ take: 100 });
for (const p of posts) {
  p.author = await prisma.user.findUnique({ where: { id: p.authorId } });
}

// Fixed: 2 queries total, or 1 with a join strategy
const posts = await prisma.post.findMany({
  take: 100,
  include: { author: true },
});

How I spot it: query logging in dev, where I see the same query repeated with different IDs. In production, APM tools like Datadog or Sentry show a request with dozens of near-identical spans. And pg_stat_statements shows a simple query with an enormous call count.

The fixes are eager loading (include in Prisma, with in Drizzle, a JOIN in raw SQL), batching with WHERE id = ANY($1), or in GraphQL, a DataLoader that collects all the IDs requested in one tick and fetches them together.

The subtle version is N+1 hidden in a serializer or a lazy-loaded getter, where the loop isn't in my code at all. That's why I check query counts in tests for key endpoints.

Interviewers look for: how you'd detect it in production, not only how to fix it.

So the rule I follow is: migrations run as a separate step before the new code is rolled out, and every migration must be compatible with the code that's currently running. That's what makes rollbacks of the app safe.

CI:
  1. spin up Postgres, run all migrations from zero
  2. run tests
  3. lint migrations (squawk / strong_migrations style checks)
Deploy:
  4. run pending migrations as a one-off job, with lock_timeout set
  5. roll out new app version
  6. (weeks later) contract migrations that drop old columns

A few things I'd insist on. Migrations should never run on app startup across multiple instances, because five pods racing to migrate is a mess. Use a single job, or a tool with an advisory lock. I'd lint for dangerous operations, like a non-concurrent index or adding a volatile default, so they're caught in review. And for big data backfills I'd run them as a separate background job, not inside the migration, so the deploy doesn't hang for an hour.

On rollback: I rarely run "down" migrations in production. It's usually safer to roll forward with a fix, because a down migration that drops a column also drops data written since.

So SQL injection is when user input gets concatenated into the query text, so the input can change the structure of the query instead of just being a value.

// Vulnerable
db.query(`SELECT * FROM users WHERE email = '${email}' AND password_hash = '${hash}'`);
// email = admin@x.com' --
// -> WHERE email = 'admin@x.com' --' AND password_hash = ...
//    everything after -- is a comment, password check gone

// Safe
db.query("SELECT * FROM users WHERE email = $1", [email]);

Parameterized queries work not because they escape quotes cleverly, but because the SQL and the values travel separately. The database parses and plans the query with $1 as a placeholder first, and binds the value afterwards. By then the structure is fixed, so the value can never become SQL, whatever characters it contains.

One gotcha: you can only parameterize values. Table names, column names and ORDER BY direction can't be placeholders. For those I use an allow-list:

const SORTABLE = { name: "name", date: "created_at" };
const col = SORTABLE[req.query.sort] ?? "created_at";
const dir = req.query.dir === "asc" ? "ASC" : "DESC";
db.query(`SELECT * FROM posts ORDER BY ${col} ${dir} LIMIT $1`, [20]);

Interviewers look for: "parse first, bind later," and the identifier allow-list.

So never in plain text and never with a fast hash like MD5 or SHA-256. I use a slow, salted password hashing function: Argon2id is the current recommendation, bcrypt is still fine and very common.

import argon2 from "argon2";

// Sign up
const hash = await argon2.hash(password);          // salt included in output
await db.query("INSERT INTO users (email, password_hash) VALUES ($1, $2)", [email, hash]);

// Login
const ok = await argon2.verify(user.password_hash, password);

The reason it has to be slow is that if the database leaks, the attacker brute-forces offline. SHA-256 does billions of guesses per second on a GPU. Argon2id is deliberately expensive in time and memory, so that drops to a tiny fraction.

The salt is a random value per user, stored inside the hash string. It means two users with the same password get different hashes, so precomputed rainbow tables are useless.

Also, I never write my own crypto, I never log the password or the request body on login routes, and I never encrypt passwords, because encryption is reversible. Hashing is the point.

So in MongoDB the query itself is an object, so if a request body sends an object where I expected a string, it can inject a query operator.

// Attacker's JSON body
{ "email": "admin@x.com", "password": { "$ne": null } }

// Vulnerable login
const user = await User.findOne({ email: req.body.email, password: req.body.password });
// becomes password: { $ne: null } -> matches any password

Parameterization doesn't help here because the input is the query structure. The fix is validating types before they reach the database:

const Login = z.object({ email: z.string().email(), password: z.string().min(1) });
const { email, password } = Login.parse(req.body);   // throws on objects

const user = await User.findOne({ email });
if (!user || !(await argon2.verify(user.passwordHash, password))) { /* 401 */ }

Notice I also stopped comparing the password in the query at all. I find by email and verify the hash in code, which is how it should be anyway.

Other layers: Mongoose's sanitizeFilter option, or express-mongo-sanitize, strip $-prefixed keys. And $where or $function with user input is basically eval, so I never use them.

Interviewers look for: knowing this exists at all, then schema validation as the fix.

So it means each thing connecting to the database gets exactly the permissions it needs and nothing more. If the app is compromised, the blast radius is limited.

-- Migrations run as the owner
CREATE ROLE app_migrator LOGIN;
-- The app can read/write data but not change schema
CREATE ROLE app_rw LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_rw;
-- Analytics gets read-only, ideally on a replica
CREATE ROLE analytics_ro LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_ro;

So the app user can't DROP TABLE or ALTER, even if there's an injection bug. The BI tool can't write. And no human uses the superuser day to day.

For multi-tenant apps I'd add Postgres row-level security, so even a forgotten WHERE tenant_id = ... can't leak another tenant's rows:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Plus the network side: the database isn't publicly reachable, only from the app's private network, with TLS on connections and credentials in a secrets manager, not in the repo.

So RPO, recovery point objective, is how much data I can afford to lose, measured in time. RTO, recovery time objective, is how long I can afford to be down. Those two numbers decide the backup strategy, not the other way round.

In practice on a managed database like RDS or Cloud SQL, I'd enable automated backups with PITR and a retention of say 14 days, and also run a nightly logical dump copied to a separate account or region. That second copy protects against someone deleting the whole cloud account or a ransomware scenario.

And the thing most teams skip: restore tests. I'd schedule an automated restore into a scratch instance, run a sanity query, and record how long it took. That time is my real RTO.

Interviewers look for: "a replica is not a backup," and tested restores.

So first, stay calm and stop the bleeding, then restore the data without throwing away everything written since.

Let me walk through it:

1. Confirm the scope and time. Check the logs or pg_stat_statements for the exact statement and timestamp. Say it ran at 14:32:10.
2. Contain. Put the affected feature into maintenance mode if orders being missing causes further damage, like re-sending emails.
3. Restore to the side, not in place. Use point-in-time recovery to spin up a new instance at 14:32:09, one second before.
4. Copy back only what was lost. Export the orders table from the restored instance and insert rows that are missing in production.

-- On prod, after loading restored rows into orders_restored
INSERT INTO orders
SELECT r.* FROM orders_restored r
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = r.id);

The reason I don't just roll the whole database back to 14:32 is that I'd lose every other write since then, like new signups and payments.

Afterwards, the blameless postmortem: why could a human run that against prod? I'd add read-only access by default, require BEGIN first in the prod console so mistakes can be rolled back, maybe a safe-updates style guard, and soft deletes on critical tables.

Interviewers look for: restoring to a side instance and merging back, not overwriting prod.

So they protect against different attackers, which is why I want more than one.

• In transit (TLS) stops someone on the network reading queries and results. I'd enforce it with sslmode=verify-full on the client, not just require, so the server certificate is actually checked.
• At rest (disk or storage encryption) protects against a stolen disk or a leaked snapshot. On managed clouds it's usually one checkbox. But it does nothing against someone who can query the database, because the database decrypts transparently.
• Column or application-level encryption protects specific fields even from people with database access, like a DBA or a SQL injection. The app encrypts before writing, with keys from a KMS.

// App-level encryption for a sensitive field
const { ciphertext, iv, tag } = encrypt(pan, dataKey);   // AES-256-GCM
await db.query("INSERT INTO cards (user_id, pan_enc) VALUES ($1, $2)", [uid, ciphertext]);

The trade-off with column encryption is that I can no longer search, sort or index that column. If I need to look up by email, I store a keyed hash (HMAC) of the email alongside for exact-match lookups.

So in practice: TLS and at-rest everywhere by default, column-level only for truly sensitive data like government IDs, health records, or tokens. And for card numbers, the best option is not storing them at all and using the payment provider's tokens.

So I'll assume employees(id, name, salary, manager_id, dept_id). The first thing I'd clarify is: if two people tie for the top, is the "second highest" the same value or the next distinct one? Usually it's the next distinct value.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

What happens here is the subquery finds the top salary, and the outer query takes the max of everything below it. The nice property is that if there's no second salary, it returns a single row with NULL rather than no rows, which is often what the question expects.

An alternative that generalises better:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;

The DISTINCT matters. Without it, if two people earn the top salary, OFFSET 1 just returns the top salary again. This version returns zero rows when there's no answer, so I'd mention that difference.

Interviewers look for: asking about ties, and handling the no-second-salary case.

So for any N I'd use a window function, specifically DENSE_RANK, so ties share a rank and don't skip the next value.

SELECT DISTINCT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 3;          -- N = 3, or a $1 parameter

With salaries 9500, 9000, 9000, 8000, this returns 8000. Let me compare the three ranking functions on that data, because that's usually the follow-up:

So RANK would say there's no 3rd highest, and ROW_NUMBER would give 9000. DENSE_RANK matches what people mean by "third highest salary."

The alternative is SELECT DISTINCT salary ... ORDER BY salary DESC OFFSET N-1 LIMIT 1, which is simpler and fine with an index on salary.

So this is GROUP BY plus HAVING. I'll assume users(id, email, created_at). I'd also ask whether Bob@x.com and bob@x.com count as duplicates. For emails they usually should.

SELECT lower(email) AS email, COUNT(*) AS copies
FROM users
GROUP BY lower(email)
HAVING COUNT(*) > 1
ORDER BY copies DESC;

The reason it's HAVING and not WHERE is that WHERE filters rows before grouping, and the count doesn't exist yet at that point. HAVING filters the groups afterwards.

If they want to see the actual duplicate rows, not just the emails, I'd use a window function:

SELECT *
FROM (
  SELECT u.*, COUNT(*) OVER (PARTITION BY lower(email)) AS copies
  FROM users u
) t
WHERE copies > 1
ORDER BY lower(email), id;

And once they're cleaned up, I'd prevent it happening again with CREATE UNIQUE INDEX ON users (lower(email)).

So I'll keep the row with the lowest id for each email and delete the rest. The cleanest way in Postgres is ROW_NUMBER.

DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY id) AS rn
    FROM users
  ) t
  WHERE rn > 1
)
RETURNING id;

What happens is each group of same-email rows gets numbered 1, 2, 3 by id, and everything except number 1 is deleted. If "keep" meant "keep the most recent," I'd just change the ORDER BY to created_at DESC.

An alternative is a self-join with DELETE ... USING:

DELETE FROM users a
USING users b
WHERE lower(a.email) = lower(b.email)
  AND a.id > b.id;

That reads nicely but on a big table with many copies it does more work. In production I'd run the SELECT version first to see what would be deleted, do it inside a transaction, and check whether other tables reference those IDs. If orders point to a duplicate user, I need to re-point them to the kept row first.

Interviewers look for: the "check foreign keys and preview first" instinct, not just the query.

So this is a self-join: the employees table joined to itself, once as the employee and once as the manager.

SELECT e.name   AS employee,
       e.salary AS employee_salary,
       m.name   AS manager,
       m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

What happens here is each row e is matched to the row m whose id is e's manager_id. Then I just compare the two salaries. I'm using an inner join on purpose: the CEO has no manager, so they drop out, which is correct because they can't earn more than a manager who doesn't exist.

An alternative with a correlated subquery:

SELECT e.name
FROM employees e
WHERE e.salary > (SELECT m.salary FROM employees m WHERE m.id = e.manager_id);

The planner often turns that into the same join, but I find the join easier to read and it lets me show both salaries in the output. An index on manager_id helps both.

So "top N per group" is the classic window function question. I rank within each department and filter.

SELECT dept_id, name, salary
FROM (
  SELECT e.*,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees e
) ranked
WHERE rnk <= 2
ORDER BY dept_id, salary DESC;

PARTITION BY restarts the ranking for each department, so every department gets its own 1 and 2. I have to filter in an outer query because window functions are computed after WHERE, so I can't write WHERE rnk <= 2 in the same level.

The choice of function matters: DENSE_RANK returns everyone tied at the top two salaries, which might be more than two people. If they want exactly two rows per department, I'd switch to ROW_NUMBER and add a tiebreaker like id.

An alternative that's very fast when there are few departments and an index on (dept_id, salary DESC):

SELECT d.id, t.name, t.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 2
) t;

Interviewers look for: knowing why the filter needs an outer query, and choosing between ROW_NUMBER and DENSE_RANK deliberately.

So a running total is SUM as a window function with an ORDER BY. I'll assume orders(id, customer_id, amount, created_at).

SELECT id,
       created_at::date AS day,
       amount,
       SUM(amount) OVER (ORDER BY created_at, id) AS running_total
FROM orders
ORDER BY created_at, id;

 id |    day     | amount | running_total
----+------------+--------+---------------
  1 | 2026-01-05 |    100 |           100
  2 | 2026-01-20 |     50 |           150
  3 | 2026-02-03 |    200 |           350

Once a window has an ORDER BY, the default frame is "from the start up to the current row," which is exactly a running total. I add id as a tiebreaker on purpose: the default frame is RANGE, so if two orders share the same timestamp, they'd both get the combined total. The tiebreaker makes each row distinct.

For a running total per customer I'd add PARTITION BY customer_id. And for a 7-day rolling sum I'd change the frame: OVER (ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW).

The old alternative is a correlated subquery summing everything before each row, but that's quadratic, so I'd only mention it to explain why windows are better.

So this is an anti-join. My go-to is NOT EXISTS.

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

The alternative most people write is a LEFT JOIN and check for null:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Both give the same result and Postgres plans them the same way, as an anti-join.

The one I'd avoid is NOT IN:

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- trap

If any orders.customer_id is NULL, NOT IN returns no rows at all, because id <> NULL is unknown, not true. That's a nasty bug that only shows up once some bad data gets in. So in practice I always use NOT EXISTS.

Interviewers look for: the NOT IN with NULL trap. It's the whole reason this question is asked.

So I'll aggregate to one row per month first, then use LAG to pull in the previous month's value.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
), with_prev AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT to_char(month, 'YYYY-MM') AS month,
       revenue,
       prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS growth_pct
FROM with_prev
ORDER BY with_prev.month;

  month  | revenue | prev_revenue | growth_pct
---------+---------+--------------+------------
 2026-01 |     150 |              |
 2026-02 |     200 |          150 |       33.3
 2026-03 |     100 |          200 |      -50.0

A few details: NULLIF(prev_revenue, 0) avoids a division-by-zero error, and 100.0 forces decimal math. The first month has no previous, so it's null, which is honest.

One gotcha: if a month has zero orders, it's missing from monthly, so LAG compares March with January. To fix that I'd generate the full month list with generate_series and LEFT JOIN the revenue onto it, using COALESCE(revenue, 0).

So this is the "gaps and islands" trick. I'll assume logins(user_id, login_at) with multiple logins per day possible.

WITH days AS (
  SELECT DISTINCT user_id, login_at::date AS day
  FROM logins
), grouped AS (
  SELECT user_id, day,
         day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day)::int AS grp
  FROM days
)
SELECT user_id,
       MIN(day) AS streak_start,
       MAX(day) AS streak_end,
       COUNT(*) AS days
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Let me explain the trick, because that's what they want to hear. For consecutive dates, both the date and the row number go up by one each step, so date - row_number stays constant. When there's a gap, the date jumps but the row number doesn't, so the difference changes.

day         rn   day - rn
2026-03-01   1   2026-02-28
2026-03-02   2   2026-02-28    same group
2026-03-03   3   2026-02-28
2026-03-05   4   2026-03-01    new group

The DISTINCT step matters: without it, two logins on the same day would break the pattern. And I'd confirm the timezone, because login_at::date uses the session timezone. For a user-facing streak I'd convert with AT TIME ZONE the user's zone.

An alternative is LAG(day) to flag where a new streak starts, then a running SUM of those flags as the group id. Same result, sometimes easier to extend.

Interviewers look for: explaining why date minus row number identifies an island.

So in Postgres I'd use conditional aggregation with FILTER. Each column is a SUM restricted to one month.

SELECT customer_id,
  SUM(amount) FILTER (WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01') AS jan,
  SUM(amount) FILTER (WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01') AS feb,
  SUM(amount) FILTER (WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01') AS mar
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01'
GROUP BY customer_id
ORDER BY customer_id;

 customer_id | jan | feb | mar
-------------+-----+-----+-----
           1 | 150 |     |  70
           2 |     | 200 |  30

A few choices here. I use half-open date ranges, >= start and < next start, so I never miss orders at 23:59:59.5 on the last day. The outer WHERE lets an index on created_at cut the scan down. And if they want zeros instead of blanks, I'd wrap each in COALESCE(..., 0).

The portable alternative, which works in MySQL too, is SUM(CASE WHEN ... THEN amount ELSE 0 END). Postgres also has crosstab in the tablefunc extension, but the column list still has to be fixed, so for truly dynamic columns I'd pivot in the application.

So Postgres has ordered-set aggregates for this, which is much cleaner than the old tricks.

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) AS median_actual_value
FROM employees;

-- Per department
SELECT dept_id, percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees
GROUP BY dept_id;

The difference between the two: with salaries 1, 2, 3 and 10, percentile_cont interpolates and returns 2.5, which is the textbook median. percentile_disc returns an actual value from the data, 2. I'd ask which one they want, but percentile_cont is usually right.

If they ask me to do it without those functions, here's the manual version:

SELECT AVG(salary) AS median
FROM (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS total
  FROM employees
) t
WHERE rn IN ((total + 1) / 2, (total + 2) / 2);

With integer division, for an odd count both expressions point at the same middle row, and for an even count they point at the two middle rows, which get averaged. It's also a good way to show I understand what a median is, not just the function name.

So I'd use LEAD to look at the next invoice number and report wherever it jumps by more than one.

SELECT id + 1       AS gap_start,
       next_id - 1  AS gap_end
FROM (
  SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
  FROM invoices
) t
WHERE next_id > id + 1;

With invoices 1, 2, 3, 6, 7, 10, that returns two ranges: 4 to 5 and 8 to 9. Returning ranges instead of every missing number matters if a gap could be a million wide.

If they want each missing number listed individually, generate_series is the alternative:

SELECT s AS missing
FROM generate_series(
  (SELECT MIN(id) FROM invoices),
  (SELECT MAX(id) FROM invoices)
) AS s
WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE id = s);

That's simple but builds the full range, so it's only sensible for small spans.

And I'd mention the real-world point: if they're using a Postgres sequence or identity column, gaps are normal. Rolled-back transactions and cached sequence values burn numbers. If the business legally needs gapless invoice numbers, a sequence is the wrong tool. I'd use a counter row locked with SELECT ... FOR UPDATE inside the same transaction as the invoice insert.

Interviewers look for: knowing sequences aren't gapless by design.

So I'll assume an employees collection with documents like { name, salary, deptId }. On MongoDB 5.2 and later, the cleanest way is the $topN accumulator inside $group.

db.employees.aggregate([
  {
    $group: {
      _id: "$deptId",
      top: {
        $topN: {
          n: 2,
          sortBy: { salary: -1 },
          output: { name: "$name", salary: "$salary" }
        }
      }
    }
  },
  { $unwind: "$top" },
  { $project: { _id: 0, deptId: "$_id", name: "$top.name", salary: "$top.salary" } },
  { $sort: { deptId: 1, salary: -1 } }
]);

What happens is $group buckets by department, and $topN keeps only the two highest-paid in each bucket as it goes, so it never builds a full array. Then $unwind flattens back to one document per employee, like the SQL result.

The alternative, which is closer to the SQL version and handles ties the same way as DENSE_RANK, is $setWindowFields:

db.employees.aggregate([
  { $setWindowFields: {
      partitionBy: "$deptId",
      sortBy: { salary: -1 },
      output: { rnk: { $denseRank: {} } }
  } },
  { $match: { rnk: { $lte: 2 } } }
]);

The older approach you'll still see is $sort, then $group with $push, then $slice. It works, but it pushes every employee into memory before slicing, and group stages have a 100MB memory limit unless they can spill to disk. For performance I'd have an index on { deptId: 1, salary: -1 }.

OK, so first I'd clarify a couple of things: do products have variants like size and colour, and do we need to handle price changes after an order is placed? I'll assume yes to both, because that's what real stores need.

Let me write this out:

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (lower(email));

CREATE TABLE products (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

CREATE TABLE product_variants (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id   bigint NOT NULL REFERENCES products(id),
  sku          text NOT NULL UNIQUE,
  price_cents  integer NOT NULL CHECK (price_cents >= 0),
  stock        integer NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id      bigint NOT NULL REFERENCES users(id),
  status       text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_cents  integer NOT NULL,
  shipping_address jsonb NOT NULL,          -- snapshot, not a FK
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id, created_at DESC);

CREATE TABLE order_items (
  order_id          bigint NOT NULL REFERENCES orders(id),
  variant_id        bigint NOT NULL REFERENCES product_variants(id),
  quantity          integer NOT NULL CHECK (quantity > 0),
  unit_price_cents  integer NOT NULL,       -- price at time of purchase
  PRIMARY KEY (order_id, variant_id)
);

The key design decisions: money is stored as integer cents, never floats. order_items copies the price, because if the product price changes next week, last month's invoice must not change. Same with the shipping address, it's a snapshot. And the CHECK (stock >= 0) means an oversell fails at the database, even if two checkouts race.

The checkout itself is one transaction: decrement stock with UPDATE ... SET stock = stock - $1 WHERE id = $2 AND stock >= $1, insert the order and items, commit.

I'd pick SQL here without hesitating. Orders, payments and inventory are relational and need transactions. The one place I might use a document store is the product catalog, if attributes vary wildly by category, but Postgres jsonb covers that too.

Interviewers look for: price snapshot on order items, integer money, and stock safety under concurrency.

OK, so the follow graph is easy. The feed is the real question: do I build it when the user opens the app (fan-out on read) or when someone posts (fan-out on write)? I'd ask about scale and whether there are celebrity accounts with millions of followers.

CREATE TABLE follows (
  follower_id  bigint NOT NULL REFERENCES users(id),
  followee_id  bigint NOT NULL REFERENCES users(id),
  created_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (follower_id, followee_id),
  CHECK (follower_id <> followee_id)
);
CREATE INDEX ON follows (followee_id);           -- "who follows me"

CREATE TABLE posts (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES users(id),
  body        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON posts (author_id, created_at DESC);

-- Fan-out on read: fine up to moderate scale
SELECT p.*
FROM posts p
JOIN follows f ON f.followee_id = p.author_id
WHERE f.follower_id = $1
  AND p.created_at < $2                          -- cursor for pagination
ORDER BY p.created_at DESC
LIMIT 20;

The composite primary key on follows prevents duplicate follows and serves "who do I follow." The second index serves "who follows me."

At larger scale, reading 500 followees' posts on every refresh gets expensive. Then I'd switch to fan-out on write: when someone posts, a background worker pushes the post ID into each follower's feed, typically a Redis sorted set capped at a few hundred entries. Reads become a single ZREVRANGE.

The catch is celebrities. Writing to 10 million feeds per post is too slow. So the hybrid: fan out on write for normal users, and at read time merge in recent posts from the few celebrities you follow. That's roughly how Twitter described it.

The source of truth stays in Postgres. Redis holds a disposable, rebuildable feed cache.

OK, so first I'd clarify: roughly how many messages per day, do we need read receipts, and how far back do users scroll? The dominant query is almost always "latest 50 messages in this conversation," then "older than this one."

CREATE TABLE conversations (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  is_group    boolean NOT NULL,
  title       text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE conversation_members (
  conversation_id  bigint NOT NULL REFERENCES conversations(id),
  user_id          bigint NOT NULL REFERENCES users(id),
  last_read_message_id bigint,              -- read receipts / unread count
  PRIMARY KEY (conversation_id, user_id)
);
CREATE INDEX ON conversation_members (user_id);   -- "my conversations"

CREATE TABLE messages (
  id               bigint GENERATED ALWAYS AS IDENTITY,
  conversation_id  bigint NOT NULL,
  sender_id        bigint NOT NULL,
  body             text,
  created_at       timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (conversation_id, id)
);

-- Page backwards with a cursor, never OFFSET
SELECT * FROM messages
WHERE conversation_id = $1 AND id < $2
ORDER BY id DESC
LIMIT 50;

The primary key (conversation_id, id) keeps each conversation's messages together in the index, so that query is a single index range scan. Unread count is just messages with id > last_read_message_id. I treat 1:1 chats as conversations with two members, so there's one code path.

For the SQL vs NoSQL call: Postgres handles a lot, and I'd start there. But messages are append-only, huge in volume, and always queried by one key. That's the ideal shape for a wide-column store. Discord famously moved messages to Cassandra and later ScyllaDB, partitioned by channel and time bucket. So I'd keep users and memberships in Postgres and move messages to Cassandra or ScyllaDB once volume demands it.

Interviewers look for: designing the key around the access pattern, and cursor pagination.

OK, so there are three models, and I'd first ask how many tenants, how big the largest one is, and whether any customer contractually needs their own database.

For a typical B2B SaaS I'd go with shared tables, and make isolation hard to get wrong:

CREATE TABLE projects (
  tenant_id   bigint NOT NULL REFERENCES tenants(id),
  id          bigint GENERATED ALWAYS AS IDENTITY,
  name        text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE tasks (
  tenant_id   bigint NOT NULL,
  id          bigint GENERATED ALWAYS AS IDENTITY,
  project_id  bigint NOT NULL,
  title       text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- per request, inside the transaction
SELECT set_config('app.tenant_id', $1, true);

Putting tenant_id first in every key and foreign key does three things. Indexes are naturally scoped by tenant. A task can't point to another tenant's project, because the composite foreign key won't allow it. And if I ever need to shard, tenant_id is already the shard key, which is exactly how Citus works.

Row-level security is the safety net for the forgotten WHERE tenant_id. One gotcha: the app must not connect as the table owner or a superuser, because they bypass RLS by default.

And I'd keep the option open to move one enormous or regulated tenant to its own database later. The schema is identical, only the connection routing changes.

OK, so the core problem is a race: two people check availability at the same time, both see the room is free, both insert. Checking in application code isn't enough. I want the database itself to reject overlapping bookings.

Postgres can do that directly with an exclusion constraint on a time range:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id   bigint NOT NULL REFERENCES rooms(id),
  guest_id  bigint NOT NULL REFERENCES users(id),
  during    tstzrange NOT NULL,
  status    text NOT NULL DEFAULT 'confirmed',
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
    WHERE (status <> 'cancelled')
);

INSERT INTO bookings (room_id, guest_id, during)
VALUES (1, 7, '[2026-05-01 14:00, 2026-05-03 11:00)');   -- ok

INSERT INTO bookings (room_id, guest_id, during)
VALUES (1, 9, '[2026-05-02 12:00, 2026-05-03 10:00)');
-- ERROR: conflicting key value violates exclusion constraint

What this says is: no two non-cancelled rows may have the same room and overlapping ranges. The [ ) bounds mean checkout at 11:00 and a new check-in at 11:00 don't conflict. The constraint holds under any concurrency, because the database enforces it on insert.

For the user experience I'd add a held status with an expiry, so a room is reserved for ten minutes during payment, and a job cancels stale holds.

Alternatives if the database can't do range exclusion: model each night as a row in room_nights(room_id, night) with a unique constraint, which works in any SQL database and MongoDB too. Or lock the room row with SELECT ... FOR UPDATE before checking overlaps. For seat-style inventory like concert tickets, the per-seat row with a unique constraint is the simplest.

So this is a clear SQL case. I need a hard guarantee, and a relational constraint gives me one.

Interviewers look for: pushing the guarantee into the database, not a "check then insert" in code.

OK, so I'd first ask how deep nesting can go and what the main read is. Usually it's "load the whole thread for a post, in display order," and it's read far more than it's written.

The simplest model is an adjacency list, where each comment points to its parent:

CREATE TABLE comments (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id     bigint NOT NULL REFERENCES posts(id),
  parent_id   bigint REFERENCES comments(id),     -- NULL = top level
  author_id   bigint NOT NULL,
  body        text NOT NULL,
  path        text NOT NULL,                      -- materialized path
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON comments (post_id, path);

-- Load a thread with a recursive CTE (no path needed)
WITH RECURSIVE thread AS (
  SELECT c.*, 1 AS depth FROM comments c
  WHERE post_id = $1 AND parent_id IS NULL
  UNION ALL
  SELECT c.*, t.depth + 1 FROM comments c
  JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread;

The recursive CTE works well, but the output isn't in display order. That's why I also added a materialized path. Each comment stores its ancestors' IDs, zero-padded, like 0000042/0000107/0000311. Then the whole thread in the correct nested order is just:

SELECT * FROM comments WHERE post_id = $1 ORDER BY path;

Subtree is WHERE path LIKE '0000042/%'. The cost is that paths are set at insert, which is fine because comments don't move. Postgres also has an ltree extension built for exactly this.

In MongoDB, embedding replies inside the parent document is tempting but breaks down fast: the 16MB document limit, and every reply rewrites a growing document. I'd store one document per comment with postId, parentId and the same path trick. For paging in practice, I load top-level comments by score and fetch deep branches lazily, like "load more replies."

OK, so I'd clarify the requirements first: is this for compliance, meaning it must be tamper-evident and kept for years, or for support, like "who changed this setting"? And do we need the full before and after values?

CREATE TABLE audit_log (
  id           bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at  timestamptz NOT NULL DEFAULT now(),
  actor_id     bigint,                     -- user, or NULL for system jobs
  tenant_id    bigint,
  action       text NOT NULL,              -- 'invoice.updated'
  entity_type  text NOT NULL,
  entity_id    text NOT NULL,
  before       jsonb,
  after        jsonb,
  request_id   text,                       -- tie to app logs / traces
  ip           inet,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX ON audit_log (entity_type, entity_id, occurred_at DESC);
CREATE INDEX ON audit_log (actor_id, occurred_at DESC);

-- The app role can only append
REVOKE UPDATE, DELETE ON audit_log FROM app_rw;
GRANT INSERT, SELECT ON audit_log TO app_rw;

The design choices: it's append-only, enforced by permissions, not by trust. It's partitioned by month so retention is a DROP of old partitions, and queries for recent history only touch recent partitions. before and after in jsonb mean one table covers every entity type.

How it gets written is the interesting part. I prefer writing it in the application, in the same transaction as the change, because only the app knows who the user is and why. Database triggers catch every change including manual SQL, but they only see the database user unless I pass the actor through set_config. For large systems, change data capture from the WAL into Kafka and then cold storage like S3 decouples it fully.

For tamper-evidence I'd hash-chain rows, each row storing a hash of the previous one, or ship them to write-once storage.

And I'm careful with PII: the audit log is often the one place passwords or card data leak into, so I redact sensitive fields before writing before and after.

OK, so there are two separate needs here: "has this user liked this post" must be exact, but "how many likes" can be approximate for a few seconds. I'd design them differently.

CREATE TABLE likes (
  post_id     bigint NOT NULL,
  user_id     bigint NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (post_id, user_id)            -- one like per user, enforced
);
CREATE INDEX ON likes (user_id, created_at DESC);   -- "posts I liked"

-- Idempotent like: double-tap doesn't double count
INSERT INTO likes (post_id, user_id) VALUES ($1, $2)
ON CONFLICT DO NOTHING;

For the count, the naive options both fail at scale. COUNT(*) on every page view is too slow for viral posts. And UPDATE posts SET like_count = like_count + 1 on every like makes one row the hottest lock in the database: thousands of transactions queue on the same row for a viral post.

So what I'd do:

// On like, only if the insert actually added a row
if (result.rowCount === 1) await redis.incr(`likes:${postId}`);

// Every 10–30s a worker flushes deltas to Postgres
UPDATE posts SET like_count = like_count + $1 WHERE id = $2;

Redis INCR is atomic and very fast, reads come from Redis, and Postgres gets one batched update per post every few seconds instead of thousands. For truly extreme posts I can split the counter into sharded sub-keys and sum them. If Redis loses the counter, I can rebuild it from the likes table, which stays the source of truth.

On SQL vs NoSQL: the likes table is a simple key-value shape with huge volume, so at very large scale it fits Cassandra or DynamoDB well, partitioned by post_id. But I'd start in Postgres with the Redis counter, which comfortably handles far more than most products ever reach.

Interviewers look for: separating the exact relationship from the approximate counter, and spotting the hot-row problem.