Question 1

A nightly job ran UPDATE orders SET synced = true over all 10 million rows. The table nearly doubled on disk and seq scans got slower, although the row count did not change. What did the UPDATE physically do, and why did readers not wait during it?

Accepted Answer

PostgreSQL implements MVCC: UPDATE does not change a row in place, it writes a new version and sets xmax on the old one to the transaction that replaced it; xmin of the new version says which transaction created it, and ctid is the version's physical address. So after updating every row the table holds 10 million live and 10 million dead versions, and a seq scan reads both until VACUUM marks the space free. Readers did not wait because each sees only the versions visible to its snapshot, that is the old ones until the UPDATE committed. VACUUM makes space reusable inside the file but does not return it to the OS, so mass updates are done in batches with pauses for autovacuum, and shrinking the file takes VACUUM FULL with an exclusive lock or pg_repack.

Question 2

To get rid of races the service was switched to SERIALIZABLE, and under load could not serialize access (40001) errors started. What do they mean, what should the application do, and why were there none on READ COMMITTED?

Accepted Answer

By default PostgreSQL runs at READ COMMITTED: each statement sees a snapshot taken when it starts, so transactions do not fail on conflicts, but anomalies such as lost update and write skew are possible. REPEATABLE READ gives one snapshot for the whole transaction, and SERIALIZABLE adds SSI, which tracks read and write dependencies and aborts one of the transactions if their outcome matches no serial order. 40001 is not a bug but part of the contract: the application must retry the whole transaction from the start, with a bounded number of attempts and backoff. To get fewer errors keep transactions short, and for hot rows an explicit SELECT ... FOR UPDATE at READ COMMITTED is sometimes simpler. READ UNCOMMITTED in PostgreSQL behaves like READ COMMITTED; dirty reads are impossible.

Question 3

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 4

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 5

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 6

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 7

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 8

A MySQL service switched its primary key from BIGINT AUTO_INCREMENT to random UUIDv4, and inserts became several times slower while secondary indexes grew noticeably. Why does this hurt more in InnoDB than in PostgreSQL, and what do you do?

Accepted Answer

InnoDB has a clustered index: rows live directly in the primary key B-tree, so a random key inserts every row at a random spot in the tree, splitting pages and churning the buffer pool, while an increasing key appends at the end. Every InnoDB secondary index contains the primary key value, so 16 bytes of UUID (or 36 as CHAR(36)) instead of the 8-byte BIGINT bloat every secondary index, and reads through them walk two trees. PostgreSQL keeps rows in a separate heap and indexes point to the version's physical address, so only the primary key index itself suffers there. The fix: time-ordered identifiers (UUIDv7 or ULID) stored as BINARY(16), or a BIGINT primary key with the UUID as a separate unique column for the outside world.

Question 9

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 10

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 11

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 12

The primary under Patroni dropped off the network, and only after 30-odd seconds did one of the replicas become the new primary. What happened during those seconds, how was that particular replica chosen, and why does the 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 13

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 14

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 15

A single Redis holding sessions and cache has become a single point of failure. It has 8 GB of data, growing by a gigabyte a month. The team lead proposes Redis Cluster straight away, you are thinking of Sentinel. What does each give when the master fails, how many nodes does each need at minimum, and which do you choose?

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 16

While a node was being added to a Redis Cluster, a client without cluster support started getting MOVED 3999 10.0.0.5:6379 and ASK 3999 10.0.0.6:6379 errors. What do they mean, how do they differ, and what should the client do with them?

Accepted Answer

A key's slot is CRC16(key) mod 16384, and a hash tag in curly braces hashes only its content, which allows colocating keys. MOVED means the slot permanently belongs to another node: the client updates its slot map and repeats the request. ASK means the slot is currently being migrated and the needed key is already on the target node: the client sends ASKING and the command once, without updating the map. During migration the slot is in MIGRATING and IMPORTING states, and multi key commands can get TRYAGAIN. Clients such as ioredis handle redirects themselves.

Question 17

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 18

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 19

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 20

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?

Accepted Answer

An operation on a single document is always atomic, and transactions appeared in 4.0 for replica sets and in 4.2 for sharded clusters. The cost: a snapshot and locking in the WiredTiger cache, write conflicts, a 60 second time limit and 2PC across shards. The errors TransientTransactionError and UnknownTransactionCommitResult are handled by retrying, session.withTransaction does that itself, so the callback must tolerate being executed again. Without a transaction you keep the invariant in one document, use a conditional updateOne with a matchedCount check, or use an outbox. If many scenarios need transactions, PostgreSQL may suit you better.

Question 21

Reads go to async replicas, and users complain: they post a comment, refresh, and do not see it; on later refreshes the comment comes and goes; and someone sees a reply before the comment it answers. Which consistency guarantee is broken in each case, and what does it cost to provide 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 22

Device events live in a Cassandra table with PRIMARY KEY (device_id, ts). A year later reads for some devices started timing out, and the logs show large partition warnings. What happened, and how do you change the model?

Accepted Answer

The partition key device_id decides which nodes hold the data, and all of a device's events live in one partition, ordered by the clustering key ts. An unbounded partition grows forever: for chatty devices it reaches hundreds of megabytes, reads and compaction of such partitions are slow, and load concentrates on a few nodes. The fix: add a time bucket to the partition key, PRIMARY KEY ((device_id, day), ts), so a partition is bounded by a day and a query over a period reads several partitions. You model from the queries: a table per query and data duplicated across tables, since there is no JOIN, and a query without the partition key using ALLOW FILTERING usually means a modelling mistake. Old data is removed with TTL and time window compaction (TWCS).

Question 23

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 24

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 25

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 26

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 27

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 28

pgbouncer in transaction mode was put between the service's 40 pods and PostgreSQL. Since then a SET search_path from one request affects other requests, and LISTEN stopped receiving notifications. Why, and why use pgbouncer at all when every pod already has a pool?

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 29

Requirement: a payment confirmed to the customer must not vanish even if the primary burns down. Replication is async today. What do you enable in PostgreSQL, what does it cost in latency, and what happens to writes when the only sync replica goes down?

Accepted Answer

Today COMMIT returns after the WAL fsync on the primary, and walsender streams WAL to the replica asynchronously, so losing the primary loses the transactions within the lag. Sync is enabled with synchronous_standby_names, and synchronous_commit (remote_write, on, remote_apply) sets what COMMIT waits for: receipt, a write to the replica's disk, or apply. The cost: every COMMIT waits for a network round trip and an fsync on the replica, which across datacenters means milliseconds per transaction, so sync is often enabled only for payments with SET LOCAL synchronous_commit. If the only sync replica dies, COMMITs on the primary do not fail, they hang until it returns, so you run two replicas with ANY 1 (s1, s2) or let Patroni manage the switch.

Question 30

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.

Databases interview questions and answers

Middle questions

1. A nightly job ran UPDATE orders SET synced = true over all 10 million rows. The table nearly doubled on disk and seq scans got slower, although the row count did not change. What did the UPDATE physically do, and why did readers not wait during it?

2. To get rid of races the service was switched to SERIALIZABLE, and under load could not serialize access (40001) errors started. What do they mean, what should the application do, and why were there none on READ COMMITTED?

3. 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?

4. 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?

5. 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?

6. 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?

7. 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?

8. A MySQL service switched its primary key from BIGINT AUTO_INCREMENT to random UUIDv4, and inserts became several times slower while secondary indexes grew noticeably. Why does this hurt more in InnoDB than in PostgreSQL, and what do you do?

9. 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?

10. 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?

11. 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?

12. The primary under Patroni dropped off the network, and only after 30-odd seconds did one of the replicas become the new primary. What happened during those seconds, how was that particular replica chosen, and why does the etcd cluster have three nodes rather than two?

13. 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.

14. 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?

16. While a node was being added to a Redis Cluster, a client without cluster support started getting MOVED 3999 10.0.0.5:6379 and ASK 3999 10.0.0.6:6379 errors. What do they mean, how do they differ, and what should the client do with them?

17. 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.

18. 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?

19. 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.

20. 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?

21. Reads go to async replicas, and users complain: they post a comment, refresh, and do not see it; on later refreshes the comment comes and goes; and someone sees a reply before the comment it answers. Which consistency guarantee is broken in each case, and what does it cost to provide it?

22. Device events live in a Cassandra table with PRIMARY KEY (device_id, ts). A year later reads for some devices started timing out, and the logs show large partition warnings. What happened, and how do you change the model?

Senior questions

1. 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?

2. 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.

3. 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?

4. 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?

5. 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?

6. pgbouncer in transaction mode was put between the service's 40 pods and PostgreSQL. Since then a SET search_path from one request affects other requests, and LISTEN stopped receiving notifications. Why, and why use pgbouncer at all when every pod already has a pool?

7. Requirement: a payment confirmed to the customer must not vanish even if the primary burns down. Replication is async today. What do you enable in PostgreSQL, what does it cost in latency, and what happens to writes when the only sync replica goes down?

8. 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.

9. 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?

10. 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?

11. 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?

12. 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?

13. 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?

14. After sharding by tenant_id, product asks for a "top 100 customers by revenue across all tenants" report, globally unique invoice numbers, and a single paginated list of all orders. What becomes hard here, and how is it done in Citus versus an application that shards on its own?

15. 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?

16. 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?

17. The team wants to replace SET NX on a single Redis with Redlock over five nodes so that the lock around charging a balance becomes "reliable". What do you tell them?

18. 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?

19. After a failover in a MongoDB replica set some orders vanished although the client had received confirmation. And a page that reads from a secondary does not show an order right after it is created. Which write concern and read concern settings explain this, and what do you change?

20. 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.

21. 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. A nightly job ran `UPDATE orders SET synced = true` over all 10 million rows. The table nearly doubled on disk and seq scans got slower, although the row count did not change. What did the `UPDATE` physically do, and why did readers not wait during it?

2. To get rid of races the service was switched to `SERIALIZABLE`, and under load `could not serialize access` (`40001`) errors started. What do they mean, what should the application do, and why were there none on `READ COMMITTED`?

3. 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?

4. 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?

5. 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?

6. 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?

7. 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?

8. A MySQL service switched its primary key from `BIGINT AUTO_INCREMENT` to random UUIDv4, and inserts became several times slower while secondary indexes grew noticeably. Why does this hurt more in InnoDB than in PostgreSQL, and what do you do?

9. 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?

11. 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?

14. 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?

16. While a node was being added to a Redis Cluster, a client without cluster support started getting `MOVED 3999 10.0.0.5:6379` and `ASK 3999 10.0.0.6:6379` errors. What do they mean, how do they differ, and what should the client do with them?

17. 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.

19. 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`.

22. Device events live in a Cassandra table with `PRIMARY KEY (device_id, ts)`. A year later reads for some devices started timing out, and the logs show large partition warnings. What happened, and how do you change the model?

1. 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?

2. 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.

3. 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?

4. 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?

5. 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?

6. pgbouncer in transaction mode was put between the service's 40 pods and PostgreSQL. Since then a `SET search_path` from one request affects other requests, and `LISTEN` stopped receiving notifications. Why, and why use pgbouncer at all when every pod already has a pool?

9. 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?

11. 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?

12. 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?

13. 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?

14. After sharding by `tenant_id`, product asks for a "top 100 customers by revenue across all tenants" report, globally unique invoice numbers, and a single paginated list of all orders. What becomes hard here, and how is it done in Citus versus an application that shards on its own?

15. 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?

16. 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?

17. The team wants to replace `SET NX` on a single Redis with Redlock over five nodes so that the lock around charging a balance becomes "reliable". What do you tell them?

21. 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.