Question 1

What is MVCC in PostgreSQL? What does UPDATE physically do? What are xmin, xmax and ctid and why don't readers block writers?

Accepted Answer

MVCC keeps several versions of a row, so each transaction works with its own snapshot of the data. UPDATE does not change a row in place: it creates a new version and the old one stays in the table until cleanup. xmin is the transaction that created the version, xmax is the transaction that deleted or locked it, and ctid is the physical address of the version. A reader sees only the versions visible to its snapshot, so it does not wait for writers. The cost is dead versions that VACUUM has to clean and table bloat.

Question 2

How does row storage in InnoDB differ from PostgreSQL? Why does a random UUID primary key hurt more in MySQL and what is stored in a secondary index?

Accepted Answer

InnoDB has a clustered index: rows are stored directly in the B-tree of the primary key. PostgreSQL keeps rows in a separate heap, and all indexes point to the physical address of a version. A secondary index in InnoDB holds the primary key value, so reading through it walks two trees, and a large or random key inflates all secondary indexes. Random inserts also split pages of the clustered index. InnoDB keeps old row versions in undo logs, while PostgreSQL keeps them in the table itself.

Question 3

How does streaming replication work in PostgreSQL and how does sync differ from async? What happens to the primary when the only sync replica goes down?

Accepted Answer

Every change first goes into the WAL. The walsender process on the primary streams WAL records to the walreceiver on the replica, which replays them; this is a physical copy of the whole cluster. Replication is async by default: COMMIT returns after the WAL fsync on the primary, so losing the primary loses transactions within the lag. Sync is enabled through synchronous_standby_names, and synchronous_commit (remote_write, on, remote_apply) sets what exactly COMMIT waits for. If the only sync replica goes down, COMMITs on the primary hang, so you use two replicas with ANY 1 or Patroni.

Question 4

A report moved to a replica fails after about 30 seconds with canceling statement due to conflict with recovery, while on the primary the same query runs for 5 minutes. Why, and what settings are there?

Accepted Answer

The replica replays WAL, and a VACUUM from the primary may remove row versions still seen by the snapshot of a query on the replica, or an ACCESS EXCLUSIVE lock arrives. The query then conflicts with recovery. max_standby_streaming_delay (30 seconds by default) sets how long recovery waits before cancelling the query. hot_standby_feedback = on protects the query at the price of bloat on the primary, because VACUUM does not remove the needed versions. A longer delay means lag for all other readers of the replica, so long reports get a dedicated replica. The conflict type is shown by pg_stat_database_conflicts.

Question 5

How does Patroni decide which node is the primary? What happens in those 30 odd seconds when the leader vanishes, and why must an etcd cluster have three nodes rather than two?

Accepted Answer

The leader holds the leader key in the DCS (etcd, Consul) with a TTL and renews it every loop_wait. When the leader disappears, the key expires and healthy replicas with lag within maximum_lag_on_failover compete for the key through compare and set. The winner promotes itself and the rest switch primary_conninfo; the total time is about ttl plus loop_wait. etcd is Raft with a quorum: three nodes survive the loss of one, two nodes survive none. Without access to the DCS the leader demotes itself unless failsafe_mode is on.

Question 6

B2B SaaS: 5000 tenants, tables orders, order_items, invoices, 2 TB and growing fast. You decided to shard PostgreSQL across 8 nodes. Compare tenant_id, order_id and created_at as the shard key. What do you choose and what changes in the schema?

Accepted Answer

created_at by ranges: all new writes go to the last shard, producing a hot node. order_id by hash is even, but tenant queries fan out to all shards, and the JOIN with order_items has to be colocated. tenant_id keeps almost all queries on one shard with local JOINs and transactions, and the only risk is skew from large tenants. I choose tenant_id. The schema needs tenant_id in every table, composite primary keys, foreign and unique keys that include the shard key, global IDs and replication of shared reference tables.

Question 7

Pick a Redis structure and commands for these tasks: a user session with a TTL, a player leaderboard with the top 100 and a player's rank, the number of unique visitors per day, a rate limit of 100 requests per sliding minute, the last 50 feed events.

Accepted Answer

A session is a Hash with EXPIRE. A leaderboard is a Sorted Set: ZINCRBY for points, ZREVRANGE for the top 100 and ZREVRANK for a rank. Unique visitors: HyperLogLog (PFADD, PFCOUNT) with an error of about 0.81% and 12 KB of memory, while the exact variant is a Set or a bitmap. A sliding window: a Sorted Set with ZREMRANGEBYSCORE, ZADD and ZCARD, atomically via Lua or MULTI. A feed is a List with LPUSH and LTRIM. Redis should not be the source of truth for data that cannot be lost or needs complex queries.

Question 8

Redis was killed with kill -9. How much data do you lose with default RDB, with AOF everysec and with AOF always? Why can BGSAVE on an instance with 20 GB of data on a 32 GB server bring it down?

Accepted Answer

RDB is a snapshot via fork according to the save rules, so everything after the last snapshot is lost, that is, minutes of data. AOF everysec loses about a second, always almost nothing at the cost of throughput. Recommendation: AOF together with an RDB preamble, which in Redis 7 is multi part AOF. fork blocks the main thread while page tables are copied, and copy on write during writes can double memory use. So you need RAM headroom, Transparent Huge Pages disabled and vm.overcommit_memory = 1.

Question 9

How does Redis Sentinel differ from Redis Cluster? What does each do when a master fails, how many nodes are needed at minimum and when would you choose one or the other?

Accepted Answer

Sentinel gives HA for a single master with replicas and no sharding: you need at least three sentinels, the failover decision is made by quorum, and the client asks a sentinel for the master address. Cluster splits data across 16384 hash slots between masters, nodes exchange gossip, there are at least three masters, and failover is decided by a vote of the masters. Cluster limits: multi key commands only within a single slot, only DB 0, and the client must support clustering. The choice depends on the data volume and the throughput of one node, and in both cases replication is async.

Question 10

Code takes a lock with SET key 1 NX PX 10000 and runs DEL key after the work. The task sometimes takes 15 seconds, and the logs show two workers running it at once. Find the two mistakes and fix them.

Accepted Answer

The first mistake: the 10 second TTL is shorter than the 15 second work, so the lock expires during execution and another worker takes it. The second: DEL without an owner check can delete someone else's lock, because the value is the same for everyone. The fix is to store a unique token and release the lock with a Lua compare and delete. You also need to extend the TTL while the work continues and stop the work if the extension fails. Even so a process pause can outlive the TTL, so correctness needs a fencing token or idempotency.

Question 11

Design MongoDB collections for a blog: posts, authors, comments (up to 200 thousand on a popular post), tags. What do you embed in a document, what do you reference and what antipatterns stop you?

Accepted Answer

The principle: what is read together is stored together, and the schema follows the queries. I embed tags and brief author data in the post, accepting duplication, and keep authors in a separate collection. I move comments to their own collection with an index on postId and time, because an unbounded array would hit the 16 MB document limit and be rewritten constantly. Subset pattern: the post holds the latest comments and a counter. It is worth embedding a bounded one to few relation and referencing the unbounded, shared or independently changing.

Question 12

Query: db.orders.find({ status: 'paid', total: { $gt: 100 } }).sort({ createdAt: -1 }).limit(20). Which compound index do you create? Explain the ESR rule and how to check with explain.

Accepted Answer

ESR: equality fields first (Equality), then sort fields (Sort), then range fields (Range). Here that is the index { status: 1, createdAt: -1, total: 1 }: the sort comes from the index, and with limit(20) the scan stops early. The order status, total, createdAt would force an in memory sort over all matches. You check with explain('executionStats'): the plan should show IXSCAN with no SORT stage, and totalKeysExamined and totalDocsExamined should be close to nReturned. ESR is a guideline, and for a very selective range another order may be better.

Question 13

What are strong and eventual consistency? Name the intermediate guarantees: read your writes, monotonic reads, causal. For each, give a product bug that appears without it.

Accepted Answer

Strong means every read sees the latest committed write, eventual means replicas converge in the end but with no time bound. Read your writes: your own write is visible at once; without it a user sees the old name after saving a profile. Monotonic reads: data does not "go back" on the next read; without it a comment disappears when the page is refreshed on another replica. Causal: an answer is not visible before its question. All of this is implemented by routing to the primary, tracking the LSN or pinning a session to a replica. A stronger guarantee costs latency and load on the primary, so it is chosen per screen.

Question 14

How is data modeled in Cassandra and why do you design queries first and tables after? What are the partition key and the clustering key?

Accepted Answer

Cassandra supports no JOIN and no efficient arbitrary filters, so a table is designed for a specific query, and data for different queries is duplicated in different tables. The partition key determines which nodes hold the rows: a hash of the key points to a node, and all rows of one partition are stored together. The clustering key sets the order of rows inside a partition, so range queries and sorting by it are cheap. A good partition key spreads data evenly and does not make a partition too large. A query without the partition key requires ALLOW FILTERING and is usually a modeling mistake.

Question 15

Explain the isolation levels in PostgreSQL. Which is the default? Can READ UNCOMMITTED show uncommitted data? What must the application do on levels above READ COMMITTED?

Accepted Answer

The default is READ COMMITTED: each statement sees its own snapshot taken when the statement starts. In PostgreSQL READ UNCOMMITTED behaves like READ COMMITTED, so a dirty read is impossible. REPEATABLE READ gives one snapshot for the whole transaction, and phantom reads are absent here too. SERIALIZABLE adds anomaly detection through SSI. On REPEATABLE READ and SERIALIZABLE a transaction can fail with error 40001, so the application must retry it in full.

Question 16

Two parallel purchases each take 10 from a balance of 100, but afterward the balance is 90, not 80. The code reads the balance, computes in the application and writes UPDATE ... SET bal = $1. Why does this happen under READ COMMITTED and how do you fix it?

Accepted Answer

Both transactions read 100 and both wrote 90, so one update was lost: this is a lost update. The fixes: an atomic UPDATE acc SET bal = bal-10, so the database computes the value. Or SELECT ... FOR UPDATE, which locks the row until the end of the transaction, or optimistic locking with a version column. A conditional UPDATE ... WHERE bal >= 10 is also safe: the second statement waits, rereads the row and rechecks the condition. If it changed 0 rows, the application must treat it as a refusal.

Question 17

Rule: at least one doctor must remain on shift. Two doctors ask for leave at the same time: each transaction on REPEATABLE READ checks that two are on duty and updates its own row. What happens and how do you fix it?

Accepted Answer

Both transactions commit, and zero doctors remain on duty. This is write skew: the transactions change different rows, so there is no write conflict, and REPEATABLE READ does not notice the dependency on what was read. SERIALIZABLE detects the dangerous dependency and aborts one transaction with code 40001, which must be retried. The alternative is explicit locking, for example FOR UPDATE on the rows being checked, or a shared lock row. A plain UNIQUE does not help here, because the rule is about a count, not uniqueness.

Question 18

The logs show ERROR: deadlock detected (40P01). It is a money transfer: each transaction updates the sender account, then the recipient. How does this arise, what does PostgreSQL do and how do you fix it?

Accepted Answer

A transfer from A to B and simultaneously from B to A: each transaction holds a lock on one row and waits for the other. PostgreSQL detects the wait cycle after deadlock_timeout (1 second by default) and aborts one transaction while the other continues. The main fix is to always lock rows in the same order, for example the account with the smaller id first. The application must also retry a transaction that got 40P01. A deadlock is possible even within a single UPDATE ... WHERE id IN (...) if the order of visiting rows differs.

Question 19

Build a simple task queue on a PostgreSQL table for several workers. Why SKIP LOCKED, why not keep a transaction open for the duration of the task, and how do you handle a worker crash?

Accepted Answer

A worker takes a task with SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1, so different workers take different rows and do not wait for each other. Without SKIP LOCKED they would all queue up behind the same first row. The claim must be short: in a transaction you set the status to running and commit, then do the work separately. A long transaction holds a connection and a row and hinders VACUUM. You handle a worker crash through locked_at with a heartbeat or a timeout that returns the task to the queue, and the task itself must be idempotent.

Question 20

An ADD COLUMN migration on a small table takes milliseconds, but in production it caused 30 seconds of downtime: all SELECTs hung. At that time only one long report was running. What happened and how do you protect against it?

Accepted Answer

ALTER TABLE takes an ACCESS EXCLUSIVE lock, which conflicts even with SELECT. It waits until the long report holding ACCESS SHARE finishes. All new queries line up behind the ALTER, even those compatible with the report, so the table effectively stands still. Protection: SET lock_timeout for the migration with retries, so it gives up quickly instead of blocking the queue. Additionally you limit long transactions and run migrations in quiet hours.

Question 21

A migration with CREATE UNIQUE INDEX CONCURRENTLY first failed because of a transaction block, then because of duplicates, and a rerun says the relation already exists. Explain each problem and the right procedure.

Accepted Answer

CONCURRENTLY cannot run inside a transaction, while the migration tool opens one by default, so it has to be disabled for this migration. A plain CREATE INDEX blocks writes and CONCURRENTLY does not, but it is slower and makes several passes. If duplicates were found during the build, the index stays INVALID: reads do not use it but writes still maintain it. That is why a rerun sees an existing object. The right way: DROP INDEX CONCURRENTLY, remove the duplicates and repeat. Check with pg_index.indisvalid.

Question 22

A frequently updated table grows and queries slow down even though autovacuum is running. VACUUM VERBOSE says: 0 removed, 50000 dead but not yet removable. What is in the way, how do you find the cause and will VACUUM FULL help?

Accepted Answer

Dead versions cannot be removed while the oldest snapshot may still see them. Typical causes: a long transaction or a session in idle in transaction state, an inactive replication slot, a prepared transaction. You look in pg_stat_activity by xact_start, and also in pg_replication_slots and pg_prepared_xacts. A plain VACUUM frees space inside the table but does not return it to the operating system. VACUUM FULL rewrites the table under ACCESS EXCLUSIVE, which means downtime, and without removing the blocker it will not help either.

Question 23

The PostgreSQL logs say: WARNING: database "app" must be vacuumed within 10000000 transactions. What does it mean, what happens next and what do you do now?

Accepted Answer

The transaction identifier is 32 bits, so old rows have to be "frozen", otherwise they become "in the future" and vanish from sight. A forced anti-wraparound VACUUM starts at autovacuum_freeze_max_age (200 million by default). When about 3 million transactions remain to the limit, the database stops issuing new xids and refuses writes. You look for blockers: long transactions, abandoned replication slots, prepared transactions, hot_standby_feedback. Then you remove the blocker and run VACUUM on the oldest tables, without panicking about a restart.

Question 24

The events table grows by 100 million rows a month and must keep 6 months. Propose partitioning. How does it simplify deleting old data, what are the limitations and when does it not speed up a query?

Accepted Answer

Range partitioning by created_at, one partition per month. Old data is removed with DROP or DETACH of a partition instead of DELETE: it is instant, with no bloat and no work for VACUUM. Limitations: unique and primary keys must include the partition key. Partition pruning works only when WHERE restricts the key directly. A query without the partition key scans all partitions and may even be slower than without partitioning.

Question 25

The users table is being moved to partitioning by created_at, and CREATE UNIQUE INDEX ON users (email) fails: unique constraint on partitioned table must include all partitioning columns. Why, and how do you keep email unique?

Accepted Answer

PostgreSQL has no global indexes: each partition has its own index, and uniqueness is guaranteed only within a partition. So the partition key must be part of UNIQUE, PRIMARY KEY and foreign keys referencing such a table. To keep email unique you move it into a separate unpartitioned table with a unique index or choose a different partition key. It is also worth questioning the idea itself: a users table is rarely large enough to need partitioning.

Question 26

Why pgbouncer if the application already has a pool? How do session, transaction and statement modes differ and what breaks in transaction mode?

Accepted Answer

Each PostgreSQL connection is a separate process, so their number is expensive, and max_connections is 100 by default. An application pool does not limit the total across many instances or serverless functions, while pgbouncer does. In session mode a connection is held for the whole session, in transaction mode for one transaction, in statement mode for one query, and multi statement transactions are forbidden. In transaction mode SET, LISTEN, session advisory locks, WITH HOLD cursors and per session temporary tables do not work. Prepared statements work if max_prepared_statements is nonzero.

Question 27

MySQL 8.4, InnoDB. Session A runs SELECT ... WHERE v BETWEEN 10 AND 20 FOR UPDATE and does not commit, session B inserts a row with v = 15. What happens under REPEATABLE READ and under READ COMMITTED, and how does it differ from PostgreSQL?

Accepted Answer

On REPEATABLE READ (the default level in InnoDB) the INSERT blocks and may fail with a lock wait timeout, because the query takes next key and gap locks on the range. On READ COMMITTED gap locks are disabled for such queries, so the insert goes through. In PostgreSQL FOR UPDATE locks only existing rows, so the new row is inserted, and phantoms are caught by SERIALIZABLE. In MySQL on REPEATABLE READ a plain SELECT reads a snapshot, while FOR UPDATE reads the latest committed version.

Question 28

A nightly bonus accrual job is started by cron in each of 3 instances and credits the bonuses three times. There is no Redis, only PostgreSQL. How do you guarantee that only one instance runs the job, and is a lock alone enough?

Accepted Answer

pg_try_advisory_lock returns false immediately if the lock is taken, so the losing instances simply skip the run. A session lock lives with the connection, while a transaction lock (pg_try_advisory_xact_lock) is released at the end of the transaction. PgBouncer in transaction mode breaks session advisory locks, because the connection changes between statements. But a lock gives only mutual exclusion, not run once semantics: if the first instance finished, a second one can start after it. So you also need a run marker with UNIQUE on the date or job identifier.

Question 29

The API writes to the primary while reads are spread over two async replicas. A user changes the name in a profile, the page reloads and shows the old one, and a second later everything is fine. Explain the cause and design read your writes.

Accepted Answer

The cause is replication lag: the read landed on a replica that has not replayed the WAL yet. Options: after a write, pin that user's reads to the primary for a few seconds (sticky), or track the LSN. For the LSN, take pg_current_wal_lsn() after the write and read only from a replica whose pg_last_wal_replay_lsn() is not lower. For critical writes you can enable remote_apply at the price of latency. Transactions and sensitive screens always go to the primary, and lagging replicas are removed from the pool.

Question 30

At 3 AM an alert: the primary disk is 95% full, pg_wal takes 400 GB instead of the usual 2 GB. A week ago a replica was removed simply by stopping its container. What happened, how do you find the cause and fix it?

Accepted Answer

A replication slot retains WAL until the consumer confirms the read position. The slot of a stopped replica is inactive, so WAL accumulates without limit. The cause is visible in pg_replication_slots: the difference between pg_current_wal_lsn() and restart_lsn. The fix is pg_drop_replication_slot, after which WAL starts to be cleaned; you must not delete files from pg_wal by hand. To prevent a repeat, set max_slot_wal_keep_size and add alerts on inactive slots and on the size of retained WAL. The same happens with logical slots (for example Debezium) and with a failing archive_command.

Databases interview questions and answers

Middle questions

1. What is MVCC in PostgreSQL? What does UPDATE physically do? What are xmin, xmax and ctid and why don't readers block writers?

2. How does row storage in InnoDB differ from PostgreSQL? Why does a random UUID primary key hurt more in MySQL and what is stored in a secondary index?

3. How does streaming replication work in PostgreSQL and how does sync differ from async? What happens to the primary when the only sync replica goes down?

4. A report moved to a replica fails after about 30 seconds with canceling statement due to conflict with recovery, while on the primary the same query runs for 5 minutes. Why, and what settings are there?

5. How does Patroni decide which node is the primary? What happens in those 30 odd seconds when the leader vanishes, and why must an etcd cluster have three nodes rather than two?

6. B2B SaaS: 5000 tenants, tables orders, order_items, invoices, 2 TB and growing fast. You decided to shard PostgreSQL across 8 nodes. Compare tenant_id, order_id and created_at as the shard key. What do you choose and what changes in the schema?

7. Pick a Redis structure and commands for these tasks: a user session with a TTL, a player leaderboard with the top 100 and a player's rank, the number of unique visitors per day, a rate limit of 100 requests per sliding minute, the last 50 feed events.

8. Redis was killed with kill -9. How much data do you lose with default RDB, with AOF everysec and with AOF always? Why can BGSAVE on an instance with 20 GB of data on a 32 GB server bring it down?

9. How does Redis Sentinel differ from Redis Cluster? What does each do when a master fails, how many nodes are needed at minimum and when would you choose one or the other?

10. Code takes a lock with SET key 1 NX PX 10000 and runs DEL key after the work. The task sometimes takes 15 seconds, and the logs show two workers running it at once. Find the two mistakes and fix them.

11. Design MongoDB collections for a blog: posts, authors, comments (up to 200 thousand on a popular post), tags. What do you embed in a document, what do you reference and what antipatterns stop you?

12. Query: db.orders.find({ status: 'paid', total: { $gt: 100 } }).sort({ createdAt: -1 }).limit(20). Which compound index do you create? Explain the ESR rule and how to check with explain.

13. What are strong and eventual consistency? Name the intermediate guarantees: read your writes, monotonic reads, causal. For each, give a product bug that appears without it.

14. How is data modeled in Cassandra and why do you design queries first and tables after? What are the partition key and the clustering key?

Senior questions

1. Explain the isolation levels in PostgreSQL. Which is the default? Can READ UNCOMMITTED show uncommitted data? What must the application do on levels above READ COMMITTED?

2. Two parallel purchases each take 10 from a balance of 100, but afterward the balance is 90, not 80. The code reads the balance, computes in the application and writes UPDATE ... SET bal = $1. Why does this happen under READ COMMITTED and how do you fix it?

3. Rule: at least one doctor must remain on shift. Two doctors ask for leave at the same time: each transaction on REPEATABLE READ checks that two are on duty and updates its own row. What happens and how do you fix it?

4. The logs show ERROR: deadlock detected (40P01). It is a money transfer: each transaction updates the sender account, then the recipient. How does this arise, what does PostgreSQL do and how do you fix it?

5. Build a simple task queue on a PostgreSQL table for several workers. Why SKIP LOCKED, why not keep a transaction open for the duration of the task, and how do you handle a worker crash?

6. An ADD COLUMN migration on a small table takes milliseconds, but in production it caused 30 seconds of downtime: all SELECTs hung. At that time only one long report was running. What happened and how do you protect against it?

7. A migration with CREATE UNIQUE INDEX CONCURRENTLY first failed because of a transaction block, then because of duplicates, and a rerun says the relation already exists. Explain each problem and the right procedure.

8. A frequently updated table grows and queries slow down even though autovacuum is running. VACUUM VERBOSE says: 0 removed, 50000 dead but not yet removable. What is in the way, how do you find the cause and will VACUUM FULL help?

9. The PostgreSQL logs say: WARNING: database "app" must be vacuumed within 10000000 transactions. What does it mean, what happens next and what do you do now?

10. The events table grows by 100 million rows a month and must keep 6 months. Propose partitioning. How does it simplify deleting old data, what are the limitations and when does it not speed up a query?

11. The users table is being moved to partitioning by created_at, and CREATE UNIQUE INDEX ON users (email) fails: unique constraint on partitioned table must include all partitioning columns. Why, and how do you keep email unique?

12. Why pgbouncer if the application already has a pool? How do session, transaction and statement modes differ and what breaks in transaction mode?

13. MySQL 8.4, InnoDB. Session A runs SELECT ... WHERE v BETWEEN 10 AND 20 FOR UPDATE and does not commit, session B inserts a row with v = 15. What happens under REPEATABLE READ and under READ COMMITTED, and how does it differ from PostgreSQL?

14. A nightly bonus accrual job is started by cron in each of 3 instances and credits the bonuses three times. There is no Redis, only PostgreSQL. How do you guarantee that only one instance runs the job, and is a lock alone enough?

15. The API writes to the primary while reads are spread over two async replicas. A user changes the name in a profile, the page reloads and shows the old one, and a second later everything is fine. Explain the cause and design read your writes.

16. At 3 AM an alert: the primary disk is 95% full, pg_wal takes 400 GB instead of the usual 2 GB. A week ago a replica was removed simply by stopping its container. What happened, how do you find the cause and fix it?

17. Three PostgreSQL nodes under Patroni, async replication. The network splits: the primary ends up in the minority, while two replicas and the majority of etcd are on the other side. What happens on each side? Are two primaries possible and how much data can be lost?

18. After sharding by tenant_id one of 8 nodes sits at 85% CPU and the rest at 20%. How do you find the cause and what do you do this week and over a quarter?

19. Application level sharding: shard = hash(tenant_id) % 4. You need to add 4 more nodes without downtime. Why is changing to % 8 bad and how do you design the data move?

20. What becomes hard after sharding: queries without the shard key, uniqueness, cross shard transactions, pagination, reports, migrations? How does Citus cope with it, and how does an application that shards itself?

21. A single 4 GB Redis holds the API cache and BullMQ queues. At a peak some tasks vanished without a trace. After a settings change the next day the API started getting OOM command not allowed when used memory > 'maxmemory'. What happened and how do you separate the load?

22. How does Redis Cluster decide which node a key lives on? What do MOVED and ASK replies mean, how do they differ and what does the client do with them?

23. After a failover in Redis Sentinel the writes of the last few seconds vanished, even though the client got OK for each. How did that happen? What do min-replicas-to-write and WAIT give and why do they not make Redis a lossless store?

24. What is Redlock and what is the point of Martin Kleppmann's critique? When is SET NX on a single Redis enough, and when is neither it nor Redlock suitable?

25. Design fencing tokens for workers that update a document in PostgreSQL under a lock. Where does the token come from, who checks it and what happens to a worker that woke up after a pause with an already expired lock?

26. Explain write concern and read concern in a MongoDB replica set. What happens to a w:1 write after a failover? How do you get read your writes when reading from a secondary?

27. MongoDB supports multi document transactions. Why does the documentation advise against building the data model on them? What does a transaction cost, which errors must be handled and how do you do without one?

28. A candidate says: PostgreSQL is CA, MongoDB is CP, Cassandra is AP. What is wrong with that claim? Explain CAP and PACELC in a way that helps configure a real system.

29. Cache aside: a read takes a key from Redis, on a miss reads PostgreSQL and does SET with a 1 hour TTL. An update writes to PostgreSQL and then does DEL on the key. Once every few days a product shows an old price for a whole hour. Reproduce the sequence and propose a fix.

Train these questions

Answer out loud and get graded

Other topics

Explore the platform

1. What is MVCC in PostgreSQL? What does `UPDATE` physically do? What are `xmin`, `xmax` and `ctid` and why don't readers block writers?

4. A report moved to a replica fails after about 30 seconds with `canceling statement due to conflict with recovery`, while on the primary the same query runs for 5 minutes. Why, and what settings are there?

6. B2B SaaS: 5000 tenants, tables `orders`, `order_items`, `invoices`, 2 TB and growing fast. You decided to shard PostgreSQL across 8 nodes. Compare `tenant_id`, `order_id` and `created_at` as the shard key. What do you choose and what changes in the schema?

8. Redis was killed with `kill -9`. How much data do you lose with default RDB, with AOF `everysec` and with AOF `always`? Why can `BGSAVE` on an instance with 20 GB of data on a 32 GB server bring it down?

10. Code takes a lock with `SET key 1 NX PX 10000` and runs `DEL key` after the work. The task sometimes takes 15 seconds, and the logs show two workers running it at once. Find the two mistakes and fix them.

12. Query: `db.orders.find({ status: 'paid', total: { $gt: 100 } }).sort({ createdAt: -1 }).limit(20)`. Which compound index do you create? Explain the ESR rule and how to check with `explain`.

1. Explain the isolation levels in PostgreSQL. Which is the default? Can `READ UNCOMMITTED` show uncommitted data? What must the application do on levels above `READ COMMITTED`?

2. Two parallel purchases each take 10 from a balance of 100, but afterward the balance is 90, not 80. The code reads the balance, computes in the application and writes `UPDATE ... SET bal = $1`. Why does this happen under `READ COMMITTED` and how do you fix it?

3. Rule: at least one doctor must remain on shift. Two doctors ask for leave at the same time: each transaction on `REPEATABLE READ` checks that two are on duty and updates its own row. What happens and how do you fix it?

4. The logs show `ERROR: deadlock detected` (`40P01`). It is a money transfer: each transaction updates the sender account, then the recipient. How does this arise, what does PostgreSQL do and how do you fix it?

5. Build a simple task queue on a PostgreSQL table for several workers. Why `SKIP LOCKED`, why not keep a transaction open for the duration of the task, and how do you handle a worker crash?

6. An `ADD COLUMN` migration on a small table takes milliseconds, but in production it caused 30 seconds of downtime: all `SELECT`s hung. At that time only one long report was running. What happened and how do you protect against it?

7. A migration with `CREATE UNIQUE INDEX CONCURRENTLY` first failed because of a transaction block, then because of duplicates, and a rerun says the relation already exists. Explain each problem and the right procedure.

8. A frequently updated table grows and queries slow down even though autovacuum is running. `VACUUM VERBOSE` says: 0 removed, 50000 dead but not yet removable. What is in the way, how do you find the cause and will `VACUUM FULL` help?

9. The PostgreSQL logs say: `WARNING: database "app" must be vacuumed within 10000000 transactions`. What does it mean, what happens next and what do you do now?

10. The `events` table grows by 100 million rows a month and must keep 6 months. Propose partitioning. How does it simplify deleting old data, what are the limitations and when does it not speed up a query?

11. The `users` table is being moved to partitioning by `created_at`, and `CREATE UNIQUE INDEX ON users (email)` fails: unique constraint on partitioned table must include all partitioning columns. Why, and how do you keep `email` unique?

13. MySQL 8.4, InnoDB. Session A runs `SELECT ... WHERE v BETWEEN 10 AND 20 FOR UPDATE` and does not commit, session B inserts a row with `v = 15`. What happens under `REPEATABLE READ` and under `READ COMMITTED`, and how does it differ from PostgreSQL?

16. At 3 AM an alert: the primary disk is 95% full, `pg_wal` takes 400 GB instead of the usual 2 GB. A week ago a replica was removed simply by stopping its container. What happened, how do you find the cause and fix it?

18. After sharding by `tenant_id` one of 8 nodes sits at 85% CPU and the rest at 20%. How do you find the cause and what do you do this week and over a quarter?

19. Application level sharding: `shard = hash(tenant_id) % 4`. You need to add 4 more nodes without downtime. Why is changing to `% 8` bad and how do you design the data move?

21. A single 4 GB Redis holds the API cache and BullMQ queues. At a peak some tasks vanished without a trace. After a settings change the next day the API started getting `OOM command not allowed when used memory > 'maxmemory'`. What happened and how do you separate the load?

22. How does Redis Cluster decide which node a key lives on? What do `MOVED` and `ASK` replies mean, how do they differ and what does the client do with them?

23. After a failover in Redis Sentinel the writes of the last few seconds vanished, even though the client got `OK` for each. How did that happen? What do `min-replicas-to-write` and `WAIT` give and why do they not make Redis a lossless store?

24. What is Redlock and what is the point of Martin Kleppmann's critique? When is `SET NX` on a single Redis enough, and when is neither it nor Redlock suitable?

26. Explain write concern and read concern in a MongoDB replica set. What happens to a `w:1` write after a failover? How do you get read your writes when reading from a secondary?

29. Cache aside: a read takes a key from Redis, on a miss reads PostgreSQL and does `SET` with a 1 hour TTL. An update writes to PostgreSQL and then does `DEL` on the key. Once every few days a product shows an old price for a whole hour. Reproduce the sequence and propose a fix.