Advanced SQL — Series 3

Preview — 3 of 10 questions

What does SEARCH DEPTH FIRST BY id SET ordercol add to a recursive CTE traversing a tree?

javascript

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name FROM categories c JOIN tree t ON c.parent_id = t.id
)
SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM tree ORDER BY ordercol;

AIt changes the recursion itself to explore depth-first instead of breadth-first

BIt's purely cosmetic and has no effect on the query

CIt's required syntax for any recursive CTE, whether or not the output order matters

DIt adds a hidden ordercol column that, when used in the final ORDER BY, produces a natural depth-first tree listing (parent, then its children before its siblings) — the recursion itself is unordered; SEARCH only computes an ordering key to sort by afterward

A parameterized query executed via PREPARE/EXECUTE (or a drivers server-side prepared statement) sometimes gets faster after several executions, then occasionally gets slower again for a specific parameter value. Whats happening?

APostgreSQL builds a fresh "custom" plan (using the actual parameter values) for the first several executions, then — by default, after the 5th — considers switching to a single reusable "generic" plan built without knowing the parameter values; if that generic plan turns out much worse for some parameter values than a custom plan would be, performance regresses for those calls

BPostgreSQL caches the query result itself after repeated executions, then invalidates the cache

CThis only happens if the table has no indexes at all

DPrepared statements always use the same plan from the very first execution; the described behavior can't happen

A primary has one synchronous streaming replica. Setting synchronous_commit = remote_write instead of the default on changes what guarantee a committed transaction has. What's the trade-off?

Aremote_write makes replication asynchronous, so synchronous_commit no longer applies

BThere's no practical difference between the levels; they only affect log verbosity

Cremote_write waits only for the replica to receive the WAL and hand it to its OS (not necessarily flush it to the replica's own disk) before confirming the commit locally — faster than waiting for a durable flush on the replica, but a replica OS crash right after receiving (before its own flush) could still lose that transaction on failover

Dremote_write is strictly stronger than the default and should always be preferred for durability

Answer all 10 questions (7 more), see explanations for every answer, and track your score.