Self-Assessment Quiz: Relational Databases for Data Engineering

Twenty questions. Aim for 16 or more.

Question 1

The defining feature of a data engineer's relationship to a source database is that:

• A. It contains more data than the warehouse
• B. You are a guest — the database exists for someone else's purpose
• C. It is always relational
• D. It cannot be queried during business hours

Question 2

PostgreSQL stores table data in pages of:

• A. 4 KB
• B. 8 KB
• C. 64 KB
• D. 1 MB

Question 3

Summing one column across six million rows is slow on a row store because:

• A. The column is not indexed
• B. Rows are stored whole, so reading one column reads every column on every page
• C. Aggregation cannot be parallelized
• D. The planner refuses to use an index for aggregates

Question 4

A bitmap heap scan is chosen when:

• A. No index exists
• B. The query matches a very small number of rows
• C. Selectivity is moderate — too many rows for an index scan, too few for a sequential scan
• D. The table has more than one index

Question 5

Under MVCC, a long-running read-only query harms a production database by:

• A. Holding locks that block writers
• B. Preventing vacuum from reclaiming superseded row versions
• C. Consuming all available connections
• D. Forcing the planner to use sequential scans

Question 6

Which is NOT one of the five defenses against long-transaction bloat?

• A. statement_timeout
• B. idle_in_transaction_session_timeout
• C. Chunking into short transactions
• D. Increasing shared_buffers

Question 7

A connection that opened a transaction and then went idle is:

• A. Harmless, since nothing is executing
• B. Worse than a long-running query, because it holds a snapshot with nothing running
• C. Automatically closed by PostgreSQL after 60 seconds
• D. Only a problem on replicas

Question 8

Why can currency never be stored in a floating-point type in this book?

• A. Floats are slower than integers
• B. Errors accumulate asymmetrically, so a reconcile-to-the-cent criterion cannot be met
• C. PostgreSQL does not support floats
• D. Floats cannot be indexed

Question 9

TIMESTAMP WITH TIME ZONE stores:

• A. The wall-clock time and the zone name
• B. An absolute point in time, normalized to UTC
• C. Local time only
• D. A UTC offset in minutes

Question 10

The seasonal bug produced by storing local wall-clock timestamps is:

• A. Leap seconds
• B. 23-hour and 25-hour days at daylight saving transitions
• C. Year 2038 overflow
• D. Timezone database updates

Question 11

UUID v4 hurts insert performance because:

• A. It is 16 bytes rather than 8
• B. Its randomness sends each insert to a random point in the B-tree, destroying index locality
• C. It requires a function call per row
• D. It cannot be a primary key

Question 12

The chapter's practical rule for JSONB is:

• A. Never use it
• B. Use it for all semi-structured data
• C. If you extract the same key in more than about three places, it should be a column
• D. Index every key

Question 13

An index on (customer_id, placed_at) can serve which query?

• A. WHERE placed_at >= '2025-11-01'
• B. WHERE customer_id = 8841 AND placed_at >= '2025-11-01'
• C. WHERE lower(status) = 'paid'
• D. All of the above

Question 14

Which index type is the easiest ask to get approved on a large append-only table?

• A. A composite B-tree
• B. A covering index with INCLUDE
• C. A BRIN index on the timestamp
• D. A partial index

Question 15

Which EXPLAIN option reveals how many pages came from disk?

• A. ANALYZE
• B. VERBOSE
• C. BUFFERS
• D. COSTS

Question 16

Estimated rows differ from actual rows by 40×. Your first action is:

• A. Add an index
• B. Rewrite the query
• C. Run ANALYZE tablename — you are debugging statistics, not a query
• D. Increase work_mem

Question 17

Why should an extract's watermark be the range's upper bound rather than max(updated_at) of rows read?

• A. It is faster to compute
• B. An empty chunk must still advance it, and the bound came from a source that vouched for it
• C. max() is not indexable
• D. It avoids a full table scan

Question 18

CDC requires:

• A. Physical streaming replication
• B. Logical replication / logical decoding, with wal_level = logical
• C. A read replica with hot_standby_feedback on
• D. A materialized view

Question 19

A logical replication slot whose consumer has stopped will:

• A. Be dropped automatically after 24 hours
• B. Cause the primary to retain WAL without bound until the disk fills and writes stop
• C. Switch to physical replication
• D. Have no effect on the primary

Question 20

A practical difference between MySQL/InnoDB and PostgreSQL that affects extraction is:

• A. MySQL cannot do range scans
• B. InnoDB clusters the table on the primary key, so PK range scans are sequential
• C. PostgreSQL has no secondary indexes
• D. MySQL does not support transactions

Answer Key

1. B — §7.1. It is sized, tuned, monitored, and owned for transactional load, and your extract runs on capacity that is there for someone else.

2. B — §7.2. The page is the unit of I/O, and that fact explains most of the section.

3. B — §7.2. And this asymmetry is the entire reason columnar storage exists (Chapter 8 §8.2).

4. C — §7.2. Collect row locations from the index, sort by page, read pages in order. Seeing it tells you the selectivity is in the middle.

5. B — §7.3. Readers do not block writers — and old row versions cannot be cleaned up while any transaction might still need to see them.

6. D — §7.3. More buffer cache does not help; the problem is unreclaimable dead tuples. The fifth defense is alerting on oldest transaction age.

7. B — §7.3. Nothing is running and the snapshot is still held. A crashed client or a paused debugger does this.

8. B — §7.4. Binary floating point cannot represent 0.1 exactly, and the errors do not cancel. The platform's acceptance criterion is reconciliation to the cent.

9. B — §7.4. Despite the name it does not store a zone; it normalizes to UTC on write and converts on read.

10. B — §7.4. Wrong twice a year, and small enough to be dismissed as noise.

11. B — §7.4. Each insert lands at a random point rather than the right edge, so the working set is the whole index. UUID v7 is time-ordered and does not have this problem.

12. C — §7.4. JSONB is right for genuinely schemaless data and wrong as a way to avoid deciding.

13. B — §7.5. Composite indexes are used left to right. (a) skips the leading column; (c) applies a function to an unindexed expression.

14. C — §7.5. Tiny, works because an append-only timestamp correlates with physical row order, and it barely touches the write path — which is why the owning team will say yes.

15. C — §7.6. Always EXPLAIN (ANALYZE, BUFFERS), never bare EXPLAIN ANALYZE.

16. C — §7.6. A plan built on stale statistics is evidence about your statistics, not about your query.

17. B — §7.7, and Chapter 4's Case Study 2. This is exactly the else now() bug, designed out.

18. B — §7.8. Which is why Chapter 5's compose file set wal_level=logical nine chapters early.

19. B — §7.8. And a PostgreSQL primary with a full WAL disk stops accepting writes, which means checkout stops. Set max_slot_wal_keep_size.

20. B — §7.9. So range your extract on the primary key where you can. A secondary-index range scan requires a lookup back into the clustered index per row.

Topic map