Async & data line
Databases
Keya checks the card catalog before she walks every shelf.
Stops explain each step. The film plays the whole ride.
- Asking
- Still looking
- Handed over
- Sent back
She asks for one book
Keya names one book, and the librarian chooses how to look for it.
Next stop: She walks every shelf
All stops
The everyday version
A library. The shelves are the table, the card catalog is the index, and one checkout is a transaction.
- Keyais the Keya
The person asking for a book.
- Librarianis the Librarian
The person who picks a path to the book.
- Card catalogis the Card catalog
Sorted cards that point at one shelf.
- The shelvesis the The shelves
The books, sitting in the order they arrived.
- Checkoutis the Checkout
A stack that leaves together, or not at all.
Interview questions
Say the short answer first. Go deeper only if they ask.
How does a B-tree index help, and what does it cost?
Sorted cards give a fast lookup and a range. Every write has to update those cards too.
PostgreSQL creates a B-tree unless you write USING another type. It serves equality and range, including BETWEEN and IN. A multicolumn B-tree on (a, b) is most efficient when the query constrains the leading column. A predicate on only b does not by itself limit the scan, though skip scan can help when a has few distinct values. lower(email) does not use a plain index on email. Every insert, update, and delete maintains each index.
“Index every column.”
What does ACID mean?
All or nothing, rules kept, readers separated, and a finished checkout survives a crash.
Atomicity is the group. Consistency here means constraints still hold, not that replicas agree. Isolation is how much concurrent checkouts can see, at a chosen level. Durability is the flushed log: by default synchronous_commit is on, so commit waits for the local WAL flush. The table pages can be written later.
“Consistency means every replica agrees.”
What do the isolation levels actually do in PostgreSQL?
Dirty reads never happen. Higher levels hold a view longer and may cancel a conflicting write.
Read Committed is the default: each statement sees rows committed before it started. Requesting Read Uncommitted still behaves as Read Committed. Repeatable Read is snapshot isolation for the whole transaction, and it also refuses phantom reads. Both Repeatable Read and Serializable can abort a conflicting transaction, which the app must retry. Serializable adds checks for serialization anomalies. A read-only Repeatable Read transaction does not hit those update conflicts.
“Read Uncommitted shows rows that are not committed yet.”
What is the many-trips problem, and how do you stop it?
One query for the list, then one more query for each item. Bring the related rows with the list.
Lazy load is the default relationship style: access emits a query per parent. selectinload emits one more query with an IN list and is the usual fix for a collection. joinedload adds a join, and in the 2.0 style a collection result must call unique(). An async session is not allowed to do that implicit IO, so load the relationship before you touch it. Django’s select_related is the join and prefetch_related is the second query. An index does not remove the extra trips.
“Add an index and the extra trips go away.”
How do you read EXPLAIN?
It shows the chosen plan and its estimated cost. ANALYZE runs the query and adds real time.
Plain EXPLAIN does not run the statement. EXPLAIN ANALYZE does, so a writing statement still writes. Compare estimated rows with actual rows. A large gap often means stale statistics, and ANALYZE on the table refreshes them. A high loops count on a nested index scan is the many-trips shape inside one statement. BUFFERS adds the cache and read counts.
“EXPLAIN only prints the query text.”
Cheat-sheet
Commands you will actually type.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'a@b.c';Run the plan and show real time plus buffer use.
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);Build the index without locking out writes. It cannot run inside a transaction, and a failure leaves an invalid index.
CREATE UNIQUE INDEX ON users (lower(email));A unique catalog on the lowered form of the address.
BEGIN; UPDATE books SET out = true WHERE id = 1; COMMIT;Group the writes. ROLLBACK throws the group away.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;One snapshot for the whole transaction. A conflicting write can be aborted.
SELECT * FROM books WHERE id = 1 FOR UPDATE;Lock the row so the next writer waits.
select(User).options(selectinload(User.orders))One extra IN query for the related rows, instead of one query per parent.
ANALYZE users;Refresh the statistics the planner uses.