05 Stream the analytical copy — instructor notes

Budget 30 to 45 minutes. It is the easiest module to facilitate and the easiest one to mis-frame: no downtime, no cutover, no runbook, and — deliberately — no measured improvement. The whole module is one console flow plus one genuine design decision, and the design decision is where the time should go.

Open by making the module's argument yourself

Have every participant load the dashboard before they build anything, and then say the uncomfortable half out loud: every panel renders, the data is current, and nothing got better. Managed Postgres bought transactional headroom, which module 03 cited and did not measure. It did not turn a row store into a column store. The eight panels are still scanning tens of millions of rows through a heap, still competing with the checkout path for the same buffers and the same disk.

Somebody will want to re-run the benchmark here. That instinct is correct and one module early; say so explicitly rather than deflecting, because the reason matters — nothing about how the panels execute has changed yet, so there is nothing to measure. A number taken here would be noise with a label on it.

The one thing to forbid: quoting the hand-run ClickHouse query from Step 5 as a comparison against module 01's baseline. It is a single interactive query with no concurrent dashboard sessions and no writer contending, against a baseline that was eight concurrent readers under 200 orders a second. It will look spectacular and it is a different experiment. Module 06 measures it properly, and that is the only form of the number worth carrying out of the room.

What to demo, what to let them do

• Demo the ClickPipes creation flow on your screen, slowly, and stop on the table-definition step. That screen is the module's actual content and it is the one everybody clicks Next past. Show the proposed engine and the proposed ordering key per table, and do not advance until the room has argued about order_items.
• Demo the source selection deliberately. Point at Managed Postgres in the source list and say why it is not RDS: pointing it at RDS works, it is the shorter workshop from module 01's road not taken, and it leaves the write path on the slower engine. A participant who picks RDS here ends the day with a coherent-looking system and an incoherent story.
• Let them create their own service, user and pipe. Three things must match what module 06 expects and they are worth saying as a list: the ClickHouse database is named shop, the user is workshop with SELECT on shop.*, and CH_HOST is captured as a bare hostname with no scheme and no port.
• Let them verify from ClickHouse rather than from the console. system.parts for the per-table row counts, then the UPDATE-and-find-it-with-FINAL check for CDC liveness. A console page reporting green is not evidence that a row arrived.

The engine and ordering key discussion

Run this while the initial load progresses — PROVISIONAL: 12 to 20 minutes for 60,520,000 seeded rows — so the wait costs nothing. Four beats:

1. Why ReplacingMergeTree at all. ClickHouse has no in-place UPDATE, so a CDC stream of Postgres UPDATE and DELETE statements arrives as inserts of new versions and something has to decide which version wins at read time. That something is the engine, keyed on the ORDER BY tuple, resolved during background merges.
2. Why order_items gets it too, despite being append-only. This is the row that surprises people and the one worth arguing. The engine is a property of the destination table; the append-only-ness is a property of today's writer. The first UPDATE or DELETE anyone issues upstream — a correction, an erasure request, a backfill — has no representation in a plain MergeTree, which then keeps both versions forever and silently double-counts.
3. The correctness constraint on the ordering key. ReplacingMergeTree deduplicates within the ORDER BY tuple, so the tuple must uniquely identify a source row. If it does not, two versions of the same row land in different tuples, never collapse, and every aggregate over the table double-counts. That is not a tuning consideration; it is the contract.
4. The tension, which is the real lesson. Every panel filters on placed_at, and a table ordered by order_item_id has a sparse index that is useless for that predicate. Leading with placed_at is safe only if placed_at never changes for a given row — true for order_items.placed_at in this schema, false for orders.updated_at, and emphatically false for orders.status.

Then have each participant state, per table, what they chose and why, including the ClickPipes defaults they deliberately kept. "I took the default" is a fine answer recorded as a decision and a poor one recorded as an absence — the difference matters when the table is a customer's and the ordering key cannot be changed later without a rebuild.

Questions worth asking

• "Why is this pipe pointed at Managed Postgres and not at RDS? What would still work if it were pointed at RDS, and what would you no longer have?"
• "order_items is append-only. Defend ReplacingMergeTree for it anyway." Then: "who upstream could issue the UPDATE that breaks the other choice?"
• "Is FINAL a keyword you would put in a dashboard panel?" No — it resolves duplicates on every read. It is the right tool for verifying one row by hand and the wrong one for a panel, and the panels get away without it because they aggregate over windows large enough that a handful of unmerged duplicates cannot move the answer. A workload that needs exactness on mutable rows is a design conversation, not a keyword.
• "This pipe created a second replication slot. On which machine, and what are you now responsible for?" On Managed Postgres — and everything module 03 taught about an abandoned slot applies to it, which is why module 07's teardown deletes the pipe before the instance.
• "The ClickHouse counts trail the Postgres counts. When is that fine and when is it a problem?" Fine when the gap is small and stable; a problem when it grows.

Common failures

• The pipe was pointed at RDS. It works, the data arrives, and nothing errors. Catch it by asking to see the source on the pipe's overview page, not by waiting for a symptom. The fix is to delete the pipe and recreate it against Managed Postgres; the slot it created on RDS goes with it, and that slot is exactly the one module 07 checks for.
• The ClickHouse database is default, not shop. Module 06's sql/06_pg_clickhouse.sql passes the same value as dbname and as the schema for IMPORT FOREIGN SCHEMA, so a mismatch surfaces there as an import that creates nothing. Fix it here, not there.
• CH_HOST captured with a scheme or a port. https://… or …:8443 in the foreign server's host option produces a connection failure in module 06 that reads like a credentials problem. The learner page is explicit: bare hostname.
• The workshop user was never granted SELECT. The user mapping is created successfully and every foreign-table query then fails on the ClickHouse side. Have them run one SELECT as workshop before leaving this module.
• The service is in a different region from the pipe's source. It works and adds latency to every CDC batch and every module 06 panel. Region discipline was module 00's job; this is where it surfaces.
• A participant pauses the pipe to "have a look at something". Same disk-fill timer as module 03, on Managed Postgres this time. Resume it, and use the moment: the lesson generalizes across both replication tools, which is the point.

End checkpoint

• A ClickHouse service exists in the same region, with a shop database and a workshop user holding SELECT.
• A Postgres CDC ClickPipe runs from Managed Postgres — verified on the pipe's own page, not assumed — over the four tables.
• The initial load has completed and system.parts shows all four tables populated.
• An UPDATE made on Managed Postgres appears in ClickHouse within seconds via FINAL.
• The pipe's slot on Managed Postgres reads active = t with small, stable lag.
• Every participant can state, per table, the engine and ordering key they chose and why.

Nobody should leave this module with a benchmark number. If somebody has one, make sure they know what it is not.