Answers

Where the data lives

Storing things so they can be found again: what an index really is, what a planner is deciding, and what a transaction actually promises.

1. Why is the date off by one day after I save it and read it back?You saved a calendar date as if it were a precise moment, so showing it in another time zone can move it to the day before or after.
2. Is UTF-8 the same thing as Unicode, or what's the difference?No: Unicode assigns a number to each character, while UTF-8 is a way of writing those numbers as bytes, using one to four bytes per character.
3. Why does my CSV file fall apart when one of the fields has a comma in it?Because the same comma both separates fields and sits inside the value, so a reader splitting on commas finds one field too many and every later column shifts.
4. Why do programmers write numbers in hexadecimal instead of just normal numbers?Programmers use hexadecimal because each digit stands for exactly four bits, so one byte is always written as two digits and the columns line up.
5. How do I make a step safe to run again when I'm not sure whether it already ran?Change the operation so that sending it twice leaves the same stored data as sending it once, instead of relying on remembering not to send it again.
6. Why does my count come out doubled after the job restarts?Your count doubled because the restart replayed events the saved counter already held: the state and input position were not saved as one snapshot.
7. How does the database estimate how many rows a WHERE clause will return?It does not scan the table: it uses stored summary statistics to turn each filter into a fraction of rows and multiplies the fractions.
8. Why doesn't 0.1 plus 0.2 come out as 0.3?A tenth cannot be stored exactly in binary, so the two stored values are already slightly wrong before they are added.
9. Why can't a hash index handle a range query like created_at > '2024-01-01'?A hash index finds only exact keys: it scrambles key order to pick a bucket, so there is no way to seek to the start of a range and walk forward.
10. Why can't I just average the old average with the new number to get a running average?Averaging the old average with the new number gives that single new value half the weight, when it deserves one share out of however many values came before.
11. Why is it actually bad to store the same value in two different tables?Because once two copies disagree, nothing in the database says which one is correct, and fixing it needs information you no longer have.
12. How does a B-tree find one row among millions in just a few reads?A B-tree finds a row by reading only a few pages: each page holds hundreds of pointers, so the tree stays three or four levels deep.
13. Should I use a sliding window or a fixed window?Use a fixed window when the question names a period that must add up, and a sliding one when it asks about the recent past as of now. Questions about episodes want neither.
14. How can I prove nothing was lost without comparing every single row?Sample a few thousand rows drawn from the source, look each one up in the destination, and add one total row count. That proves loss and duplication without reading every row.
15. If SQL is declarative, who decides how the join actually gets run?The database's query planner chooses the join method and order; your SQL only says which rows the answer must contain.
16. Why does the planner underestimate rows when I filter on two columns that are related?It multiplies the two filter shares as if the columns were unrelated, so when one column already implies the other the estimate comes out far too low.
17. How do you count unique visitors without remembering every visitor?Turn each visitor identifier into a random-looking code, keep only a small summary of the rarest pattern seen, and scale it up; repeats change nothing and memory stays fixed.
18. How does a streaming system know when a window is finished?It cannot know. It guesses: it takes the latest event time it has seen, subtracts an allowance you choose, and treats every window ending before that point as finished.
19. Why does the order I join tables in matter if joins are commutative?The final result is the same, but the rows carried between joins are not, and that is what the work is paid on.
20. Will refreshing statistics fix a bad query plan?Only if the summary was taken before the data changed; otherwise refreshing re-measures the same assumptions and the plan stays wrong.
21. Why is a log-structured store faster for writes than a B-tree?Because a B-tree finds a page and then rewrites it in place for every insert, while a log-structured store only appends, and appending in order is far cheaper than jumping about the disk.
22. What does it mean for an index to cover a query?A covering index holds every column a query needs, so the database can answer from the index without reading the table row.
23. Why does inserting a single row sometimes cost many page writes?A row insert usually writes one page, but when the target page is full it splits in half and the split can climb to the root, so rare inserts write several pages for each index.
24. Why do new rows show up when I run the same query twice inside one transaction?The transaction is not holding one fixed set of rows: a later run of the same range query can see rows another transaction inserted and committed in between.

The other subjects