Databases

Cards

1. Postgres starts a process for each connection, so thousands of app instances should share a small number of connections through a pooler such as _____. easy Fill in the blank
2. An index makes reads on a column fast. What does it cost? easy Flashcard
3. What is the N+1 query problem, and how do you fix it? easy Flashcard
4. Which workload most clearly calls for a relational database? easy Multiple choice
5. Before acknowledging a commit, a database appends the change to its _____ and flushes it to disk, so a crash can be recovered by replaying it. easy Fill in the blank
6. A table has an index on (country, city). Which query cannot use it efficiently? medium Multiple choice
7. In a flash sale, how do you decrement stock so it never goes below zero? medium Multiple choice
8. A service runs 2,000 queries/s, and each query holds a database connection for 5 ms. On average, how many connections are busy? medium Estimate
9. What makes an index covering for a query, and why is it faster? medium Flashcard
10. When would you denormalize a schema, and what do you take on? medium Flashcard
11. You need to split full_name into first_name and last_name on a busy table, while old app versions keep reading full_name. Which order does it with no downtime and no broken readers? medium Multiple choice
12. Which column gains the least from a B-tree index of its own? medium Multiple choice
13. An LSM tree first puts writes in an in-memory _____, flushes it to disk as immutable sorted files called _____, and merges those files in the background, which is called _____. medium Fill in the blank
14. Why not run the analytics team's big reports on the production database? medium Flashcard
15. Why does a backfill of millions of rows run in small batches from a background job, after dual writes have started, rather than as one UPDATE before them? medium Flashcard
16. When does pessimistic locking (SELECT … FOR UPDATE) beat optimistic concurrency (a version check at write time)? medium Multiple choice
17. A service ingests millions of small writes a second and reads them rarely. Which storage engine design suits it best? hard Multiple choice
18. A key in an LSM tree may be in any of dozens of SSTables. What keeps reads from checking all of them? hard Flashcard
19. How does MVCC let readers and writers avoid blocking each other, and what does it cost? hard Flashcard
20. Why can random (v4) UUID primary keys make inserts slow on a large B-tree table? hard Flashcard
21. Under read committed isolation, which of these can still happen inside a single transaction? hard Multiple choice
22. Two doctors are on call. Each, in their own transaction, checks "at least two are on call" and takes themselves off. Both commit, and no one is on call. What anomaly is this, and how do you prevent it? hard Flashcard