Knowledge check rationales

Theme 1: which pipe does which leg, and what it carries

Covering both legs. The answer is the option with two pipes: one whose destination is your Managed Postgres instance, one whose destination is your ClickHouse service. Both legs of this workshop are ClickPipes; what differs is the destination type.

If you picked the option saying ClickPipes only lands data in ClickHouse and the Postgres leg must be hand-rolled, you are holding a claim that used to be true. ClickPipes began as a Postgres source connector with a ClickHouse destination only, and a Managed Postgres destination was added later — it is the "Data sources" import flow on a Managed Postgres service, still in public beta at the time of writing. If a ticket, a blog post or an earlier version of this very workshop told you otherwise, that is where the belief came from. Check the destination list rather than trusting the shape you remember.

Note the corollary, because it is not "logical replication is obsolete": CREATE PUBLICATION and CREATE SUBSCRIPTION remain a documented migration path into Managed Postgres, and there are good reasons to reach for them — no beta dependency, no console, and every step expressible in SQL you can put in a script. The pipe is the shorter road, not the only one. Module 03 takes the pipe and then inspects the replication slot it creates, so you can see that the primitive is still there underneath.

If you picked the cascading option, the thing to see is that nothing in this workshop chains one leg into the other. Cascade is a real topology — RDS -> Managed Postgres -> ClickHouse, built before cutover so ClickHouse is warm when the application flips — and it is a reasonable design for a real customer. It is not one pipe, and it is not what you built: one leg completes and cuts over before the second is created, so that every failure has exactly one candidate cause.

If you picked pg_clickhouse, you have the right extension attached to the wrong job. It is a foreign data wrapper, so it makes ClickHouse tables readable from Postgres at query time, which is precisely what module 06 uses it for. It moves no data and keeps no copy. A pipe replicates; a wrapper reads through.

Re-read: module 01's table of the two legs.

Scoping the replication. The answer is the option about an explicit list making replication scope a decision, so a new table cannot join silently.

The distractors here are all plausible technical claims, which is the point. Replicating every table is supported; both scopes perform the initial load; primary keys matter for replica identity but not for whether a scope is legal. So none of the capability-flavoured reasons are the reason. What distinguishes them is governance: an enumerated list is a decision recorded where someone can find it, and a table added next week is out of scope until a human changes the pipe.

That the pipe manages the publication for you does not move the decision — it relocates it. The publication still exists on the source and you can still read it out of pg_publication_tables; what changed is that the table list is a setting on the pipe rather than a statement you typed. The question is about whether the scope was chosen, not about which surface you chose it on.

If you got this right but by elimination, sit with the corollary: replicating everything is not wrong. It is a defensible choice if you write down that you accept future tables being enrolled without review. The unexamined default is what the question is against.

Re-read: module 03's pipe configuration, and module 02's first decision.

Theme 2: the prerequisite that fails loudly, and the two that do not

Why the parameter group needs a reboot. The answer is the option identifying rds.logical_replication as a static parameter read only at server start, fixed by rebooting.

The reasoning most people miss is the asymmetry: this prerequisite is the nice one. It fails visibly, SHOW wal_level returns replica, and nothing works until you fix it. Its two companions — backup_retention_period >= 1 and the rds_replication grant — fail silently. A publication is created, a subscription is created, everything reports success, and rows never arrive. So the useful takeaway is not "remember the reboot"; it is that two of the three prerequisites have no symptom except absence of data, which is why module 01 checks all three before it starts the seed rather than discovering them in module 03.

If you picked the option about asynchronous application within the hour, the tell you missed is pending-reboot in the parameter's apply status. RDS tells you what it is waiting for.

Re-read: module 01 Step 2, and the wal_level recovery.

Theme 3: inside the cutover window

What replication leaves behind. The answer is the option about sequence values not being replicated, so setval on each target sequence was skipped.

This is the workshop's central lesson, so if you missed it, the thing to fix is not the fact but the method that would have found it. The method is: enumerate every object in the schema that holds state and is not table data, then ask what its value is on the target after every row has arrived. In sql/01_schema.sql that enumeration produces four bigserial sequences, none of which has ever issued a number — last_value reads NULL — while their tables hold millions of rows. Nothing else in that schema holds state — no triggers, no materialized views, no identity columns with their own quirks.

The distractors are all repair-flavoured, and each one describes a real Postgres operation that would be the right answer to a different symptom. That is the trap: a duplicate-key error on a primary key looks like an index problem, and the instinct to REINDEX is strong. What makes sequences the answer is the specific value in the error — Key (order_id)=(1) — the very first id, on a table with 12 million rows. An index problem does not start at 1.

Re-read: sql/04_sequences.sql, which is written as an explanation as much as a script.

Reading the slot during the cutover. The answer is the option saying the target has confirmed every WAL record the source wrote, so it is safe to proceed.

Every option here is a true sentence about logical replication; only one is what this LSN comparison establishes. Two clarifications that decide it:

• The comparison is only meaningful because writers are quiesced. While the writer runs, the source is always slightly ahead and the difference never reaches zero — which is why the window's order is quiesce, then wait, and not the other way round.
• confirmed_flush_lsn is about durability, not receipt. received_lsn on the subscriber says data arrived; confirmed_flush_lsn says the subscriber told the source it flushed. The gate wants the stronger one.

The tempting wrong option is the one that adds sequences to the guarantee. It is tempting because it is the more complete-sounding claim, and completeness is exactly what an LSN cannot give you: LSNs are about WAL, sequences are not replicated through WAL to the subscriber, so no LSN comparison will ever tell you anything about them.

Re-read: module 03 Step 6, and section 2 of the model runbook.

Theme 4: when replication becomes the incident

The cost of a slot nobody reads. The answer is the option about the source retaining WAL for the inactive slot, so pg_wal grows and can fill the volume.

Three properties make this the workshop's best incident, and they are what to carry out of it:

• The machine with the symptom is not the machine with the cause. The disk fills on the source; the thing you changed was a subscription on the target.
• The rate is the application's write rate, not the replication rate. You cannot slow it down by throttling replication, because nothing is replicating. Only the application's traffic or the slot itself can change the slope.
• No conventional Postgres maintenance helps. VACUUM, checkpoint tuning, log rotation: none of them can free a WAL segment a slot has not confirmed, because the slot is a promise the engine is keeping.

The distractor about the source discarding pending changes is the one worth understanding as the opposite of the truth: the source keeps everything, which is precisely why re-enabling loses no transactions and why the disk grows. The mechanism that makes recovery clean is the same mechanism that makes neglect dangerous.

The generalization that matters more than the fact: this applies to every slot, including the one the ClickPipe creates on Managed Postgres in module 05. A paused pipe is the same timer on a different machine, which is why module 07's teardown deletes the pipe before the instance.

Re-read: module 04 Step 1, and the orphaned slot recovery.

Theme 5: what verification actually proves

What reconciliation proves. The answer is the narrowest option: the replicated rows agree on the columns you hashed, at the moment both queries ran.

If you picked one of the broader options, the instinct is understandable — "every line matches" feels like a total statement. Look at what sql/05_reconcile.sql actually computes: one count(*) and one sum(hashtext(...)) per table, over email, sku, order_id || status, and order_item_id || line_total. So a corruption confined to customers.region, orders.placed_at, order_items.quantity or order_items.product_id passes cleanly, and nothing in the output mentions sequences, indexes or statistics.

That is a deliberate trade, not an oversight: the query has to be cheap enough to run inside a cutover window, so it costs one sequential scan per table. The professional habit is to know which columns you did not check, and to be able to say so when somebody asks whether the migration is verified.

The time qualifier matters as much as the column qualifier. Both sides were read at slightly different instants on a live system, and nothing about the result constrains what happens next. A clean reconciliation is evidence, not a guarantee.

Re-read: sql/05_reconcile.sql, and section 4 of the model runbook.

Theme 6: modelling the analytical copy

ReplacingMergeTree and the cost of FINAL. The answer is the option pairing collapse-on-merge of rows sharing a sorting key with FINAL doing that work per query.

Two halves, and people usually have one of them:

• Why the engine. ClickHouse has no in-place UPDATE in the Postgres sense, so a CDC stream of updates and deletes arrives as inserts of new versions. Something must decide which version wins at read time, and that something is the engine plus its sorting key, resolved during background merges.
• What FINAL costs. It resolves duplicates at query time, on every read, and modifies nothing on disk. The distractor that says FINAL rewrites parts is the most attractive wrong answer because it would be a reasonable design — an operation that both answers the query and cleans up. It is not what happens, and the distinction is practical: FINAL is right for verifying one row by hand and wrong for a dashboard panel.

Why module 06's panels can skip it: they aggregate over windows large enough that a handful of not-yet-merged duplicates cannot move the result meaningfully. If your workload needs exactness on mutable rows, that is a design conversation about deduplication strategy, not a keyword you add.

Re-read: module 05 Step 3 and Step 4.

Choosing the ordering key. The answer is the option about the columns the dashboard filters on, lowest cardinality first, so granules get pruned.

This is the question most likely to feel unfair, because module 05 also insists the ordering key must contain the source primary key. Both are true and they operate at different levels:

• The dedup contract is a constraint. ReplacingMergeTree collapses within the ORDER BY tuple, so the tuple must uniquely identify a source row. If it does not, two versions land in different tuples, never collapse, and every aggregate double-counts. Any candidate key that fails this is out, regardless of how much it helps queries.
• Read patterns are the driver. Among the keys that satisfy the constraint, the one to pick is the one whose leading columns match the predicates the panels carry. For order_items that argues for (placed_at, order_item_id) — time first, primary key last for uniqueness — and it is only safe because placed_at is copied from the order at insert and never updated. A row whose ordering-key value changes moves to a different tuple and stops deduplicating against its own earlier version, which is why the same trick is indefensible for orders.updated_at and absurd for orders.status.

So "copy the Postgres primary key" is not wrong about correctness — it satisfies the constraint. It is wrong about the workload: it optimizes for point lookups this dashboard never issues, and leaves the placed_at filter with a sparse index it cannot use. The hashed-key distractor fails the constraint and destroys range pruning, which is why it is the only option here that is wrong twice.

Re-read: module 05 Step 3, and your own notes on what you chose per table.

Theme 7: routing without touching the application

What IMPORT FOREIGN SCHEMA changes. The answer is that nothing changes, because the foreign tables sit in a schema no query resolves against.

"Nothing changes" is an uncomfortable answer to a question about a migration step, which is why the distractors work. The reasoning that makes it obvious: the import created tables in schema ch, and ch is on nobody's search_path yet. Unqualified order_items in a panel still resolves to public.order_items. Foreign tables do not shadow same-named local tables by existing — schema resolution order is the only thing that decides which one an unqualified name means.

The design consequence is the part worth keeping: steps 1 to 5 of sql/06_pg_clickhouse.sql are observably inert, so they can be run in production during business hours, and the routing change is one later statement that is reversible in one more. A migration built as "install, then flip" is a very different risk profile from one built as "install and it takes effect".

The grant distractor is close to a real failure and mis-times it. The GRANT must come after the import, and skipping it produces permission denied for foreign table — but only once something actually resolves to those tables, which is after the search_path change, not at import time.

Re-read: module 06 Step 1, and the file's own step 5 comments.

Shadowing one role, not the database. The answer is the option about changing name resolution for that one role, so writes still reach the local tables.

Note what the correct option does not say: nothing moves, and no SQL is rewritten. The same identifier resolves to a different table depending on who is connected. If you picked the option about moving tables into ch for reads, that is the mental model to correct — there are two distinct sets of tables, public.* heap tables and ch.* foreign tables, coexisting permanently in one database.

Two operational consequences that the question does not test and the workshop does:

• A role-level SET applies to new sessions, so a pooled reader shows no change until its pool recycles. This is the most common reason people conclude the reroute failed.
• Setting it database-wide would point the application's INSERTs at read-only foreign tables and break it — which is exactly the change this workshop claims not to make. The two-role split in sql/01_schema.sql exists so that this lever has something narrow to act on.

Re-read: module 06 Steps 1 to 3, and sql/06_pg_clickhouse.sql step 6.

Theme 8: reading a plan

Reading EXPLAIN for pushdown. The answer is the option pairing "the remote SQL on the foreign scan carries the grouping" with "CTE and window pushdown is still growing".

Both halves matter and each rules out a different distractor. The presence of a Foreign Scan node proves routing, not pushdown — a plan can show a foreign scan that returns tens of millions of bare rows for Postgres to aggregate, which is strictly worse than the row store it replaced. And planner cost estimates are an output of the decision, not the mechanism: reading the cost tells you what the planner believed, and reading the remote SQL tells you what it will actually send.

The second half is why the check is mandatory rather than advisory. Coverage grows between releases, so a panel that pushed down in one version is not evidence about another, and a published EXPLAIN — including the walkthrough page's — is a snapshot. Your own plan, against your own extension version, is the authority.

The habit to take away: corroborate the plan from the far end. ClickHouse's system.query_log reports read_rows per query, and millions of read rows for a panel that returns 90 is not a matter of interpretation.

Re-read: module 06 Steps 5 and 6.

Theme 9: what the numbers prove

What PostgresBench does and does not show. The answer is the narrowest option: pgbench write throughput is several times higher at the same instance size, and no more.

Every other option extends a transactional benchmark into a claim it cannot support, and the extension feels natural because the numbers are large and the direction is favourable. The discipline is to read the benchmark's filter state — instance size, region, HA setting, Postgres version, workload — and then say only the sentence those filters license. Here that sentence is about pgbench TPS at a stated size, and PostgresBench is not an analytical benchmark, so it says nothing about panel latency.

The workshop is deliberately structured to make this hard to get wrong in practice: module 05 opens by showing you a dashboard that is exactly as slow as it was after the Postgres migration completed. If better Postgres had fixed analytics, that module would have nothing to say.

The transferable form: when somebody quotes a benchmark at you, ask what workload it ran. If the answer is not the workload you care about, the number is background, not evidence.

The number that carries the argument. The answer is writer TPS during the dashboard load, because it shows checkout getting headroom back.

The reasoning is a comparison against the alternative design rather than against the baseline. A read replica genuinely does make panels faster — it gives the dashboard its own compute and its own buffers. So panel p95 and dashboard QPS do not distinguish "we moved analytics to a column store" from "we added a replica", and quoting them as the justification invites exactly that objection. Writer TPS under dashboard load is the figure a replica does not move for the same money, because the analytical workload is still a row store scanning tens of millions of rows, now on another instance you are paying for, with replica lag added to every panel.

Two things to attach to that number whenever you quote it, both of which the workshop insists on:

• Its config block. Dashboard concurrency 8, 300 measured seconds, 30 discarded, writer rate 200, writer concurrency 16. A throughput figure without its load parameters is not a result.
• Its narrowing. Two variables moved between the before and after tables — the dashboard went to ClickHouse and the writer went from RDS to Managed Postgres. The contention half is what the harness demonstrates; the engine half is the PostgresBench citation. Anyone quoting the row as purely one or the other is quoting it wrong, including you.

Re-read: module 01 Step 5 for the baseline's framing, and module 06's result section.

If a theme did not land

Go to the module, not to this page again. Every question here was written from one decision the workshop asked you to make, and the module has the context that makes the decision obvious; this page only has the reasoning that makes it defensible.