PostgreSQL Advanced

Why enforce a business invariant in the database rather than only in application code?

• AEvery client and code path is covered — no service, script, or migration can write around the rule
• BThe rule runs faster than in the application — the planner rewrites the check into an index scan
• CThe rule can be changed without a migration — constraints are metadata and skip the DDL path
• DThe rule removes the need for transactions — constraint checks take the locks that writers need

What does WITHOUT OVERLAPS do in a PostgreSQL 18 PRIMARY KEY declaration?

• AThe last column is compared for overlap rather than equality, so a key may repeat over disjoint periods
• BThe last column is dropped from the key itself and carried in the index only as a payload for scans
• CThe key stops enforcing uniqueness altogether and rejects a row only when two periods are identical
• DThe key forces an ordering on that column so the index accepts periods in ascending sequence only

What is write skew?

• ATwo transactions read overlapping data and then write different rows, leaving a state no serial order allows
• BTwo transactions write the very same row, and the commit that lands second silently discards the first value
• COne transaction reads a row twice and sees two different values because another commit landed in between
• DOne transaction reads rows another has written but not committed, and keeps that result after the rollback

Which requirement genuinely calls for Kafka rather than a queue table in PostgreSQL?

• AIndependent consumer groups replaying a retained, ordered log at sustained high throughput
• BA single worker pool draining tasks reliably, where each task must be handled exactly once
• CA handful of background jobs that have to run in order and survive an application restart
• DA flow where the producer needs to learn whether a consumer finished handling its message