06 Route the OLAP workload

The module the workshop exists for. One ALTER ROLE moves the analytical workload to a column store, and the dashboard does not know.

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration"

Outcome

Eight Grafana panels executing in ClickHouse, with panel SQL you can prove is unchanged, the writer still hitting local heap tables in the same database on the same host and port, and an after benchmark to set beside module 01's before.

Step 1 — Read the file before you run it

sql/06_pg_clickhouse.sql is heavily commented and it is the source of truth for this module. Read it now. It is two halves, and the split is the design:

Steps 1 to 5 change nothing an application can observe. CREATE EXTENSION, CREATE SERVER, CREATE USER MAPPING, CREATE SCHEMA ch, IMPORT FOREIGN SCHEMA. No existing object is dropped, renamed or altered. public.orders is still public.orders. Every query in flight resolves exactly where it did. You could run this half in production during business hours.

Step 6 is the entire migration. One statement puts ch in front of public on the search_path of one role, so an unqualified order_items in a dashboard panel resolves to ch.order_items — a ClickHouse table reached over the foreign data wrapper — instead of the local heap table. The SQL text did not change. Name resolution did.

Three details in that file worth pulling out before you execute it:

• port and secure are deliberately absent from the CREATE SERVER options. pg_clickhouse defaults secure to auto, meaning TLS when the host is a ClickHouse Cloud host or the port is a secure port, and it defaults the port from the driver and host type — binary to Cloud is 9440. Pinning port '9004' or secure 'off' out of habit is how you get a connection refused, or a plaintext attempt against a TLS-only port.
• The GRANT SELECT ... IN SCHEMA ch comes after the import, not before. The foreign tables do not exist until IMPORT FOREIGN SCHEMA runs, and the import leaves them owned by the master user. Skip the grant and step 6 turns every panel into permission denied for foreign table order_items, which looks exactly like a search_path bug and is not one.
• shop_writer is absent from step 6, and must stay absent. Setting this database-wide with ALTER DATABASE shop SET search_path would point the application's INSERTs at read-only foreign tables and break it — which is precisely the change this workshop claims not to make.

Step 2 — Run it

Run as the Managed Postgres master user, with the ClickHouse connection details from module 05.

psql "$TARGET_DSN" \
  -v ch_host="$CH_HOST" \
  -v ch_database="$CH_DATABASE" \
  -v ch_user="$CH_USER" \
  -v ch_password="$CH_PASSWORD" \
  -f sql/06_pg_clickhouse.sql

Confirm the shape of what it built:

psql "$TARGET_DSN" -c "SELECT srvname, srvoptions FROM pg_foreign_server;"
psql "$TARGET_DSN" -c "SELECT foreign_table_schema, foreign_table_name FROM information_schema.foreign_tables ORDER BY 2;"
psql "$TARGET_DSN" -c "SELECT rolname, rolconfig FROM pg_roles WHERE rolname LIKE 'shop%' ORDER BY rolname;"

rolconfig must show {search_path=ch, public} for shop_analytics and null for shop_writer.

Step 3 — Recycle the readers, or nothing will appear to happen

A role-level SET search_path applies to new sessions. Every connection already open keeps the search_path it had when it connected, so a pooled reader shows no change at all until its pool recycles. This is the single most common reason people conclude the reroute did not work.

Grafana holds a pool. Recreate it, re-exporting its two variables — docker compose reads them from the environment, and a recreate without them silently rebuilds the datasource against whatever SHOP_DB_HOST currently holds, which in a fresh shell or one left over from module 01 is your RDS instance:

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration/grafana"
export SHOP_DB_HOST="${TARGET_HOST}:5432"
export SHOP_ANALYTICS_PASSWORD="$ANALYTICS_PASSWORD"
docker compose up -d --force-recreate
docker inspect grafana-grafana-1 --format '{{range .Config.Env}}{{println .}}{{end}}' | grep SHOP_DB_HOST

That last line must show your Managed Postgres host. If it shows the RDS endpoint, the panels are querying the instance you migrated away from and no amount of search_path will change it.

psql "$TARGET_DSN" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'shop_analytics';"

Then verify on a fresh session that the setting is actually in force:

psql "postgresql://shop_analytics:${ANALYTICS_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require" \
  -c "SELECT current_setting('search_path');"

 current_setting
-----------------
 ch, public

If it still reads "$user", public, one of two things is true: you are on a session that predates the ALTER ROLE, or the client is setting search_path itself on connect and overriding the role default. Check the connection options before touching anything in the SQL.

Step 4 — Prove the routing three ways

Do not trust a fast panel. Three independent proofs, each answering a different objection.

Proof 1: the analytics role gets a Foreign Scan

Run q1_revenue_by_hour.sql's text verbatim, as shop_analytics:

psql "postgresql://shop_analytics:${ANALYTICS_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require" \
  -c "EXPLAIN (VERBOSE) SELECT date_trunc('hour', placed_at) AS hour, sum(line_total) AS revenue
FROM order_items
WHERE placed_at >= now() - interval '7 days'
GROUP BY 1
ORDER BY 1;"

What to look for, in this order:

1. a Foreign Scan node, on relation ch.order_items;
2. under VERBOSE, the remote query the wrapper will send — and specifically whether it carries the sum(...) and the GROUP BY, or only a column list. The aggregate appearing in the remote query is pushdown; a bare column list means ClickHouse is being used as a very fast disk and Postgres is doing the maths;
3. the absence of any Seq Scan or Index Scan on public.order_items.

Proof 2: the writer role gets a local plan

Same query. Same database. Same host and port. Different role.

psql "postgresql://shop_writer:${WRITER_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require" \
  -c "EXPLAIN (VERBOSE) SELECT date_trunc('hour', placed_at) AS hour, sum(line_total) AS revenue
FROM order_items
WHERE placed_at >= now() - interval '7 days'
GROUP BY 1
ORDER BY 1;"

Expect an Index Scan using order_items_placed_at_idx, or a Seq Scan, on public.order_items, and no Foreign Scan node anywhere. That is the two-role split from sql/01_schema.sql paying off: OLTP resolves to local heap tables, OLAP resolves to ClickHouse, through one connection string and one database.

This is also the proof that answers "did you just break the application to speed up the dashboard". Show someone these two plans side by side and there is nothing left to argue about.

Proof 3: the application did not change

git status --short
git diff --stat -- grafana/dashboards/shop-ops.json bench/queries/
./scripts/preflight.sh && echo "panel SQL matches bench/queries"

git status --short shows nothing under grafana/ or bench/queries/. git diff --stat prints nothing at all. preflight.sh confirms, in both directions, that every panel's rawSql is byte-identical to a file in bench/queries/ and that no panel carries SQL that exists nowhere on disk.

The full inventory of application-side changes across this entire workshop is now, and remains, one line: the SHOP_DB_HOST environment variable, edited once, at cutover, in module 04. Not the database name. Not the role. Not one panel's SQL.

Reload the dashboard and confirm all eight panels render.

Step 5 — Find the panel that does not push down

pg_clickhouse analyzes queries for pushdown and gets a lot: filters, joins, semi-joins, aggregations, and a wide set of functions including ordered-set aggregates such as percentile_cont(). ClickHouse's published TPC-H SF1 result is 14 of 22 queries fully pushed down with 60x-plus speedup on that set.

Fourteen of twenty-two is not twenty-two of twenty-two. CTE and window-function pushdown is still expanding, which is why EXPLAIN is mandatory here rather than advisory — and why a workshop that only showed you the happy path would be teaching you that EXPLAIN is optional.

Check all eight. Run each panel query under EXPLAIN (VERBOSE) as shop_analytics:

export ANALYTICS_DSN="postgresql://shop_analytics:${ANALYTICS_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require"
for q in bench/queries/*.sql; do
  echo "=== $(basename "$q")"
  { echo 'EXPLAIN (VERBOSE)'; cat "$q"; echo ';'; } | psql "$ANALYTICS_DSN" -f -
done 2>&1 | tee /tmp/explain-before.txt

Read /tmp/explain-before.txt and sort the eight into two piles, using one question per query: is the aggregation in the remote query, or above the Foreign Scan?

• A plan whose Foreign Scan carries the sum/count/GROUP BY in its remote query, with a cheap Sort or Limit on top, is pushed down. ClickHouse does the work and returns tens of rows.
• A plan with HashAggregate, GroupAggregate or WindowAgg above a Foreign Scan that returns raw columns is not. ClickHouse is scanning and shipping millions of rows for Postgres to aggregate, over the network, one row at a time.

Corroborate from the other end rather than trusting the plan. On ClickHouse, look at what actually arrived:

SELECT event_time, read_rows, formatReadableSize(read_bytes) AS read, query
FROM system.query_log
WHERE type = 'QueryFinish' AND user = 'workshop' AND event_time > now() - INTERVAL 10 MINUTE
ORDER BY event_time DESC
LIMIT 20;

A pushed-down panel appears here as one query containing sum( and GROUP BY with a small result. A panel that did not push down appears as a query selecting bare columns with read_rows in the millions. That column is the tell, and it is not a matter of interpretation.

The one to expect

q7_revenue_run_rate.sql is the designed case:

SELECT day, revenue, avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
) daily
ORDER BY day

The inner aggregate reduces 90 days of order_items to 90 rows. The window function then runs over those 90 rows and is trivially cheap. If the planner pushes the subquery down, this panel should be one of the fastest on the dashboard.

Look at its actual plan. When window-function handling prevents the subquery from being pushed as its own remote aggregate, you get a WindowAgg over a Sort over a Foreign Scan that selects placed_at and line_total — tens of millions of rows crossing the wire to be aggregated in Postgres. The panel is then the slowest on the dashboard rather than the fastest, and it is slower than it was on RDS in module 01, because RDS at least had an index on placed_at and no network between the scan and the aggregate.

psql "$TARGET_DSN" -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_clickhouse';"

Step 6 — Repair it

The goal is to get the aggregation into the remote query while keeping the panel a single statement that returns the same result set. Force the daily aggregate to be evaluated as its own step, so the window function runs over its 90-row output rather than being fused into a plan that cannot be pushed:

WITH daily AS MATERIALIZED (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
)
SELECT day, revenue, avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily
ORDER BY day

AS MATERIALIZED is an explicit optimization fence: it tells the planner to evaluate the CTE once, on its own, rather than inlining it into the outer query. That is normally something you avoid; here it is the whole point, because the fence is what lets the aggregate be planned as a pushed-down foreign scan with nothing above it.

Check it:

psql "$ANALYTICS_DSN" -c "EXPLAIN (VERBOSE) WITH daily AS MATERIALIZED (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
)
SELECT day, revenue, avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily
ORDER BY day;"

You want a CTE Scan on daily with the WindowAgg above it, and inside the CTE a Foreign Scan whose remote query carries sum(line_total) and a GROUP BY. Confirm from ClickHouse's system.query_log that read_rows for that query dropped from millions of shipped rows to a scan that returns 90.

If the CTE form still does not push — CTE pushdown is on the same expanding frontier as window functions — the fallback is to express the rolling average as a self-join over the same materialized aggregate, which contains no window function at all:

WITH daily AS MATERIALIZED (
  SELECT date_trunc('day', placed_at) AS day, sum(line_total) AS revenue
  FROM order_items
  WHERE placed_at >= now() - interval '90 days'
  GROUP BY 1
)
SELECT d.day, d.revenue, round(avg(w.revenue), 2) AS rolling_7d
FROM daily d
JOIN daily w ON w.day BETWEEN d.day - interval '6 days' AND d.day
GROUP BY d.day, d.revenue
ORDER BY d.day

Keep whichever form your EXPLAIN and system.query_log show pushing the aggregate down. Verify the two forms return the same numbers before you keep one — a repair that changes the answer is not a repair.

Apply the repair to the application, and be honest about what that costs

Change it in both places, so the panel and the harness stay the same experiment:

${EDITOR:-nano} bench/queries/q7_revenue_run_rate.sql
${EDITOR:-nano} grafana/dashboards/shop-ops.json

In the JSON, the panel is Revenue run rate, rolling 7 day (90 days), and its targets[0].rawSql is the string to replace. Then re-run the identity check:

./scripts/preflight.sh && echo "panel SQL matches bench/queries"
git diff --stat -- grafana/dashboards/shop-ops.json bench/queries/
cd grafana && SHOP_DB_HOST="${TARGET_HOST}:5432" SHOP_ANALYTICS_PASSWORD="$ANALYTICS_PASSWORD" \
  docker compose up -d --force-recreate && cd ..

preflight.sh passes again, because you changed both sides identically. git diff --stat now shows exactly two files: the query file and the dashboard.

That is an amendment to the workshop's central claim, and it should be stated rather than smuggled. The claim is not "no query is ever touched". It is that the migration itself required no application change: seven of eight panels are byte-identical across a move from RDS Postgres to Managed Postgres to ClickHouse, and the eighth was edited not to make the migration work but to tune a query against the new engine — the same kind of edit anyone makes after any platform change, made visible by an EXPLAIN you were required to read. A workshop that hid this would have taught you that pg_clickhouse is magic. It is not magic; it is a planner, and planners have coverage.

Step 7 — Measure it

Two things must be true before this run means anything.

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. You verified a fresh session reads ch, public in Step 3; that is the check.

Stop the standing writer first. run.py spawns its own app/writer.py, exactly as it did for the before run. Leaving yours running doubles the write load without changing the config block that gets printed beside the number.

kill "$(cat /tmp/writer.pid)" && sleep 3 && tail -1 /tmp/writer.log
cd bench
python3 run.py \
  --dsn "postgresql://shop_analytics:${ANALYTICS_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require" \
  --writer-dsn "postgresql://shop_writer:${WRITER_PASSWORD}@${TARGET_HOST}:5432/shop?sslmode=require" \
  --label after \
  --out results/after.json

Same pinned defaults, same eight queries, same 30 seconds of discarded warm-up and 300 measured seconds. Check the exit status and the WARNINGS block before quoting anything, and confirm Comparable query set | yes — a query that errored on one target and not the other changes the set being compared, which is the one way two tables from this harness can look comparable and not be.

Restart the writer afterwards if you want the dashboard live for the rest of the session.

The result

Both runs, side by side. Every figure below is provisional and is replaced by a measured one after the end-to-end run; the config blocks that printed above each table are part of the result and are kept with results/before.json and results/after.json.

Read the rows in this order.

The first row is the headline, and it is not about dashboards. The writer was asked for 200 orders a second. On RDS, with eight dashboard sessions running, it delivered roughly half that. On Managed Postgres with the dashboard executing in ClickHouse, it delivers essentially all of it. The dashboard stopped competing for the checkout path's buffers and disk, because it is no longer on the same engine. That is the measurable form of the whole thesis: one database serving both workloads does not fail by making dashboards slow, it fails by letting dashboards steal throughput from checkout.

It is also the answer to "why not just add a read replica". A replica gives the dashboard its own compute and its own buffers, and it is a genuinely useful thing to do — but it is still a row store scanning tens of millions of rows per panel, it costs another instance, it adds replica lag to every panel, and it does nothing about the fact that the analytical workload is running on the wrong kind of engine. Compare the second row against what a replica would give you: not the same magnitude, not the same cost.

The second and third rows are supporting evidence. They are what the person watching the dashboard notices. They are not why the migration was worth doing.

The last two rows are the pushdown lesson, in numbers. Before the repair, q7 was worse in ClickHouse than on RDS: the aggregate ran in Postgres over rows shipped across a network, where RDS had at least run it over a local index. After the repair it is in line with the rest. A pushdown failure does not present as an error, and it does not present as "no improvement" — it presents as a regression, on one panel, in a change that improved seven others. That is exactly the shape of result that ships to production unnoticed, and reading EXPLAIN is the only thing that catches it.

Two variables moved between these tables, and the content will not pretend otherwise. The dashboard moved to ClickHouse and the writer moved off RDS onto Managed Postgres. The dashboard-contention half is what this harness demonstrates. The engine-throughput half is the PostgresBench citation from module 04, not something measured here. Anyone quoting the first row as purely a contention result, or purely an engine result, is quoting it wrong.

Corroborate both runs rather than trusting the harness's own clock. On Postgres:

SELECT calls, round(mean_exec_time, 1) AS mean_ms, round(max_exec_time, 1) AS max_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

On ClickHouse:

SELECT count(), round(quantile(0.5)(query_duration_ms), 1) AS p50, round(quantile(0.95)(query_duration_ms), 1) AS p95
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 10 MINUTE;

Done when

• a fresh shop_analytics session reports search_path as ch, public, and shop_writer's rolconfig is null;
• EXPLAIN (VERBOSE) as shop_analytics shows a Foreign Scan on ch.order_items, and the same query as shop_writer shows a local plan with no Foreign Scan;
• git diff over grafana/dashboards/shop-ops.json and bench/queries/ was empty before the q7 repair, and shows exactly those two files after it, with ./scripts/preflight.sh passing in both states;
• you identified at least one panel whose aggregate sat above its Foreign Scan, and can show the EXPLAIN and the system.query_log read_rows before and after your repair;
• results/after.json exists from a run that exited 0 with Comparable query set | yes; and
• you can state the before and after writer TPS, with the config block, and say what the comparison does and does not prove.

Next: module 07 grades what you have learned, then tears everything down. The teardown is not optional — an RDS instance and a ClickHouse service left running are this workshop's only ongoing cost.