06 Route the OLAP workload — instructor notes

Budget 45 to 60 minutes. This is the module the workshop exists for, and it has one facilitation risk that is not about time: the mechanism is so short — one ALTER ROLE — that a room can watch it work and not believe it. The three proofs are the antidote and none of them is optional.

Its second risk is that the pushdown hunt has no fixed answer. pg_clickhouse coverage moves, so plan to facilitate a search rather than to reveal a result.

What to demo, what to let them do

• Read sql/06_pg_clickhouse.sql with the room before anyone runs it. Ten minutes, on screen, and structure it as the file does: steps 1 to 5 change nothing an application can observe, step 6 is the entire migration. Ask what would happen if you ran the first half in production during business hours. The answer is nothing, and that is the design.
• Demo the two EXPLAINs yourself, side by side, once. Same query, same database, same host and port, different role: a Foreign Scan on ch.order_items for shop_analytics, a local plan for shop_writer. Then have every participant reproduce both. This pair is the artifact they will show a skeptical colleague, so they need to have produced it themselves.
• Let them run the pushdown sweep over all eight queries. The for loop in the learner page writes /tmp/explain-before.txt; the sorting into two piles is the exercise and it does not compress.
• Demo the system.query_log corroboration on your screen. read_rows in the millions for a panel that did not push down is the least arguable evidence in the workshop, and it teaches people to look at the far end of a connection rather than at the plan alone.
• Let them apply the repair to both files themselves, then run ./scripts/preflight.sh. A participant who edits only the dashboard, or only the query file, gets a failing check — which is the check doing its job and worth letting happen.

The three proofs, and why you insist on all three

Each answers a different objection, and a participant who skips one has an argument they cannot finish:

1. The analytics role gets a Foreign Scan. Answers "is it really executing in ClickHouse?" Under VERBOSE, read the remote query and ask whether it carries the sum(...) and the GROUP BY or only a column list. That distinction is the entire content of Step 5, previewed here.
2. The writer role gets a local plan. Answers "did you break the application to speed up the dashboard?" Nothing left to argue about once the two plans are on one screen.
3. git status and preflight.sh are clean. Answers "what did you edit?" The full inventory of application-side change across the whole workshop, up to this point, is one environment variable edited once at cutover.

Make participants say the third one as a sentence. "The migration required no application change" is a claim people will repeat at work, and it should come out of their mouths already narrowed.

Facilitating a pushdown hunt with no fixed answer

q7_revenue_run_rate.sql is the designed case: an inner daily aggregate that reduces 90 days to 90 rows, with a window function over the result. When window-function handling prevents the subquery from being planned as its own remote aggregate, the plan becomes a WindowAgg over a Sort over a Foreign Scan shipping raw columns, and the panel becomes the slowest on the dashboard — slower than it was on RDS, which at least had an index on placed_at and no network between the scan and the aggregate.

It may push down cleanly for your room. Coverage grows, and the workshop says so. Handle that like this:

• Record the extension version on the board at the start of the module: SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_clickhouse';. One version for the room means one shared reality, and it is the number that makes any of these observations reproducible later.
• State the exercise as the general form, not as q7. "Find whichever of your eight queries has an aggregate above its Foreign Scan, and repair that one." There is very likely one. If a participant genuinely finds all eight pushed down, give them the harder task: construct a query shape that does not push down, and explain why from the plan.
• Do not let the room conclude the extension is magic, and do not let it conclude the extension is broken. It is a planner, and planners have coverage. The transferable skill is reading the plan and the remote query, which is why EXPLAIN here is mandatory rather than advisory.
• The repair is AS MATERIALIZED, and the reasoning is worth spelling out. An explicit optimization fence is normally something to avoid; here it is the point, because the fence is what lets the aggregate be planned as a pushed-down foreign scan with nothing above it. If the CTE form still does not push, the self-join fallback in the learner page contains no window function at all. Whichever form they keep, they verify both return the same numbers first — a repair that changes the answer is not a repair.
• Then be honest about the amendment. The workshop's claim is not "no query is ever touched". It is that the migration itself required no application change, and that the eighth panel was edited to tune a query against a new engine — the same edit anyone makes after any platform change, made visible by an EXPLAIN somebody was required to read. Say that in the room. A workshop that hides it teaches that the extension is magic.

The model reading, with the plan shapes and the repair's reasoning, is in the pushdown walkthrough. It is a snapshot, not a specification: the participant's own EXPLAIN is the authority, and the page says so.

Keeping the final benchmark comparable

Two conditions, both of which produce a clean exit 0 when violated:

• Run the harness after the ALTER ROLE, never across it. The pool is created once at startup and held for the whole run, so it keeps whatever search_path was in force when it connected. The check is the fresh-session current_setting('search_path') from Step 3, and it must read ch, public.
• Stop the standing writer first. run.py spawns its own app/writer.py, exactly as it did for the before run. A participant who leaves theirs running doubles the write load without changing the config block printed beside the number.

Then have them check Comparable query set | yes before quoting anything. A query that errored on one target and not the other changes the set being compared, and it is the one way two tables from this harness can look comparable and not be.

Nobody passes --dashboard-concurrency or --duration-seconds. If somebody did on the before run, their two tables are not a pair and the honest move is to re-take one of them, not to caveat it.

Reading the result with the room

Take the rows in order and make the argument in this sequence:

1. Writer TPS during the dashboard load is the headline, and it is not about dashboards. The writer was asked for 200 orders a second; on RDS with eight dashboard sessions it delivered roughly half. That is the measurable form of the thesis: one database serving both workloads does not fail by making dashboards slow, it fails by letting dashboards steal throughput from checkout.
2. Now answer the read-replica question you parked in module 01. A replica gives the dashboard its own compute and buffers and is a genuinely useful thing to do. It is still a row store scanning tens of millions of rows per panel, it costs another instance, and it adds replica lag to every panel. Compare against the dashboard QPS row: not the same magnitude, not the same cost.
3. Panel latency and dashboard QPS are supporting evidence. They are what the person watching the dashboard notices, and they are not why the migration was worth doing.
4. The q7 rows are the pushdown lesson in numbers. Before the repair it was worse than on RDS. A pushdown failure does not present as an error or as "no improvement" — it presents as a regression on one panel inside a change that improved seven others, which is exactly the shape of result that ships unnoticed.
5. Two variables moved between the tables and the content does not pretend otherwise. The dashboard moved to ClickHouse and the writer moved off RDS onto Managed Postgres. The contention half is what this harness demonstrates; the engine-throughput half is the PostgresBench citation from module 03. Anyone quoting the first row as purely one or the other is quoting it wrong, and that distinction is question 13 and question 14 in module 07.

Common failures

• The reroute appears not to have worked. The commonest failure in the module, and it is a pool. A role-level SET search_path applies to new sessions only, so Grafana shows no change until its pool recycles. Recreate the container or terminate the shop_analytics backends, then verify on a fresh session — recovery.
• permission denied for foreign table order_items. The GRANT ran before IMPORT FOREIGN SCHEMA, so it granted on tables that did not exist yet. It looks exactly like a search_path bug and is not one — recovery.
• A connection failure from the foreign server. Almost always port or secure pinned by habit, or a CH_HOST carrying a scheme or a port. The file omits both options on purpose: secure defaults to auto and the port defaults from the driver and host type.
• search_path still reads "$user", public on a fresh session. Either the session predates the ALTER ROLE, or the client sets search_path itself on connect and overrides the role default. Check the connection options before touching the SQL.
• The pg_stat_statements corroboration query errors. Nothing in the lab creates that extension, so on an instance where it is not already installed the query fails with a missing relation. Either CREATE EXTENSION pg_stat_statements; on the target first, or skip that corroboration and use ClickHouse's system.query_log, which needs nothing installed. Do not let it derail the module — it is corroboration, not the measurement.
• preflight.sh fails after the repair. Only one of the two files was edited. That is the check working; have them fix the other side rather than skipping the check.
• A participant reroutes the writer too. Setting search_path database-wide, or on shop_writer, points the application's INSERTs at read-only foreign tables. ALTER ROLE shop_writer RESET search_path and re-verify rolconfig is null. Then ask them what claim that change would have broken.

End checkpoint

• A fresh shop_analytics session reports search_path as ch, public, and shop_writer's rolconfig is null.
• The two EXPLAINs exist and the participant can narrate the difference.
• git diff over the dashboard JSON and bench/queries/ was empty before the repair and names exactly those two files after it, with preflight.sh passing in both states.
• At least one panel was identified with an aggregate above its Foreign Scan, with the EXPLAIN and the system.query_log read_rows before and after the repair.
• results/after.json exists from a run that exited 0 with Comparable query set | yes.
• The participant can state the before and after writer TPS with its config block, and say what the comparison does and does not prove.

That last bullet is the one to hold the line on. A participant who leaves with the number and without the narrowing will misquote it within a week.