The store that was never listening

The previous chapter asked Postgres a question the dashboard will need every day — messages and unique active users per environment per day, over ninety days — and it cost 585.9 ms over a million rows. Then it added the index that should have fixed it and measured again. The index removed the join. It did not touch the sort, and the sort was 656 ms of the 698 ms plan.

That is the sentence this chapter starts from. You cannot index your way out of an aggregation. An index finds rows; it does not stop a million of them having to be put in day order before they can be counted by day. The only thing that removes that sort is a table already stored in that order — and a table can only be stored in one order, which the operational one is already spending on something else.

So there is a second store. It has been running since chapter 1.2.

flowchart TB
    subgraph pg["Postgres — ordered by nothing this question asks for"]
      pgt["messages<br/>PRIMARY KEY (id)<br/>no environment_id on the row"]
      pgq["the join finds the tenant,<br/>then 1,052,655 rows are SORTED<br/>to be grouped by day"]
      pgn["585.9 ms<br/>join 140 ms · sort 656 ms"]
      pgt --> pgq --> pgn
    end
    subgraph ch["ClickHouse — ordered by exactly this question"]
      cht["message_events<br/>ORDER BY (environment_id, ts)<br/>environment_id IS the row"]
      chq["the range is contiguous,<br/>so there is no join and<br/>nothing to sort"]
      chn["13.22 ms<br/>granules 131/154"]
      cht --> chq --> chn
    end
    note["Same question, same ninety days, same 91 rows of answer.<br/>The row store is not slow at being a row store —<br/>it is being asked a question its ordering says nothing about."]
    ch ~~~ note

The check that could not fail

Before anything is built, run the store you already have:

curl -s "http://localhost:${RELAY_CLICKHOUSE_HTTP_PORT:-8123}/ping"
curl -s "http://localhost:${RELAY_CLICKHOUSE_HTTP_PORT:-8123}/?query=SELECT+1"

The first returns Ok. The second returns this:

Code: 194. DB::Exception: default: Authentication failed: password is incorrect,
or there is no user with such name. ... (REQUIRED_PASSWORD)

ClickHouse has been in compose.yaml since the first chapter of Part 1. Its health check has been green every day since. And nothing outside that container has ever been able to run a query against it. The reason is in the image, not in this repository:

docker compose exec clickhouse cat /etc/clickhouse-server/users.d/default-user.xml

<clickhouse>
  <users>
    <default>
      <!-- User default is available only locally -->
      <networks>
        <ip>::1</ip>
        <ip>127.0.0.1</ip>
      </networks>
    </default>
  </users>
</clickhouse>

The health check ran /ping, and /ping neither authenticates nor is network-restricted. It answers Ok. to anyone who can reach the port, which is exactly what makes it useless here: a check that cannot fail for the reason you care about is not a check. It was not lying about the container being up. It was answering a question nobody needed the answer to, for sixteen chapters, in a green tick.

The fix adds a user rather than editing the image's file, and replaces the check with one that runs a query:

@@ -61,16 +61,35 @@
     image: clickhouse/clickhouse-server:25.3
     # The analytical store rides its own path (CON-01): single node in v1,
     # schema already cluster-shaped (ADR-08).
     ports:
       - "${RELAY_CLICKHOUSE_HTTP_PORT:-8123}:8123"
       - "${RELAY_CLICKHOUSE_NATIVE_PORT:-9000}:9000"
+    environment:
+      # A NEW USER. This does not lift `default`'s restriction and is not meant to:
+      # the image ships `users.d/default-user.xml` limiting `default` to ::1 and
+      # 127.0.0.1, that file belongs to the image, and anyone "fixing" it is editing
+      # somebody else's file. `relay` answers from the host; `default` still does not.
+      CLICKHOUSE_USER: relay
+      CLICKHOUSE_PASSWORD: relay
+      # CREATES THE DATABASE. It does NOT make it the session's: `SELECT
+      # currentDatabase()` over HTTP as `relay` still answers `default`, so an
+      # unqualified CREATE TABLE builds the schema in `default` while this one sits
+      # empty. Every statement in `analytics/` names `relay_analytics` for that reason.
+      CLICKHOUSE_DB: relay_analytics
     volumes:
       - clickhouse-data:/var/lib/clickhouse
     healthcheck:
-      test: ["CMD", "wget", "-q", "-O", "-", "http://localhost:8123/ping"]
+      # RUNS A QUERY, because the old check ran `/ping` — which neither authenticates
+      # nor is network-restricted. It was green from chapter 1.2 to this one while every
+      # query from outside the container was refused. A check that cannot fail for the
+      # reason you care about is not a check.
+      #
+      # `clickhouse-client` is in the image; `curl` is not.
+      test: ["CMD", "clickhouse-client", "--user", "relay", "--password", "relay",
+             "--query", "SELECT 1"]
       interval: 5s
       timeout: 3s
       retries: 5
       start_period: 15s
 
   mailpit:

Then prove the new check can fail, because otherwise it is the old check with a longer command. Point it at a wrong password and bring the stack up: the container goes unhealthy and the probe reports Code: 516. DB::Exception: relay: Authentication failed. Put the password back, and it is healthy again.

The published DDL does not apply

The architecture document has carried the analytical table since its first draft. Copy it out of docs/05-sad.md and paste it in unchanged:

Received exception from server (version 25.3.14):
Code: 450. DB::Exception: TTL expression result column should have DateTime or
Date type, but has DateTime64(3, 'UTC'). (BAD_TTL_EXPRESSION)

TTL ts + INTERVAL 90 DAY on a DateTime64 column is refused. It needs toDateTime(ts). The document is amended rather than quietly diverged from — the governance clause requires that, and the alternative is a specification that describes a schema nobody can create.

That is the first of six divergences from the published DDL, and the other five are all about what a column means when nobody filled it.

Six divergences, and five of them are the same argument

-- SAD 6.2's raw event table, with every divergence from the published DDL commented.
-- The document is amended to match (docs/05-sad.md, and the chapter says so).
CREATE TABLE IF NOT EXISTS relay_analytics.message_events (
    environment_id  UUID,
    channel_id      UUID,
    -- DIVERGENCE 1: SAD publishes UUID. `messages.user_id` is nullable -- a message whose
    -- author was deleted has none -- and a NULL inserted into a non-nullable UUID becomes
    -- the ZERO UUID without failing, inventing one active user per environment.
    -- Nullable makes uniqExact ignore it, exactly as count(DISTINCT user_id) does.
    user_id         Nullable(UUID),
    ts              DateTime64(3, 'UTC'),
    event           LowCardinality(String),   -- created|edited|deleted
    -- DIVERGENCE 2 and 3: both are Nullable because the value is sometimes NOT KNOWN, and
    -- 0 is a different claim. lengthUTF8(NULL) inserts 0 into a non-nullable column, and a
    -- text_length of 0 says a zero-length message was sent. A tombstone preserves no text
    -- and an unedited attachment-only message has none either.
    text_length     Nullable(UInt32),
    attachment_count Nullable(UInt8),
    -- DIVERGENCE 4: SAD publishes UInt32, and NOTHING PRODUCES THIS COLUMN until
    -- FR-ANL-10's chapter (FR-006a). Non-nullable, every row would read 0 ms -- a
    -- measured claim about a delivery nobody timed. An absent producer writes NULL.
    delivery_latency_ms Nullable(UInt32)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)                     -- DR-07
ORDER BY (environment_id, ts)                 -- tenant-scoped range scans
-- DIVERGENCE 5: SAD publishes `TTL ts + INTERVAL 90 DAY`, which this server refuses:
--   Code: 450. TTL expression result column should have DateTime or Date type,
--   but has DateTime64(3, 'UTC'). (BAD_TTL_EXPRESSION)
TTL toDateTime(ts) + INTERVAL 90 DAY          -- DR-09

The sixth is that every statement names relay_analytics. Four of the other five are one argument repeated: zero is a measurement and NULL is an absence, and a column that cannot say "I don't know" will lie rather than fail. A delivery_latency_ms of 0 is a claim that a message was delivered instantaneously. A text_length of 0 is a claim that somebody sent an empty message. Neither is true; both are what a non-nullable column produces when the loader has nothing to give it.

A second store needs a ledger of its own

The operational database has a migration runner. This one cannot use it — constitution III keeps the two paths apart, and schema_migrations is the operational path's own table. So analytics/ is a directory of statements and apply.mjs is forty lines that apply them.

CREATE TABLE IF NOT EXISTS is idempotent and silent: it cannot tell you whether it created the table or found it already there. That silence is the whole reason this script exists. A run that applies nothing has to say so:

$ node analytics/apply.mjs
database relay_analytics ready
applied 3: 0000_message_events.sql, 0001_daily_usage.sql, 0002_schema_applied.sql
skipped nothing

$ node analytics/apply.mjs
database relay_analytics ready
applied nothing
skipped 3: 0000_message_events.sql, 0001_daily_usage.sql, 0002_schema_applied.sql

Three rules fall out of running it rather than designing it.

One statement per file, because the interface says so. The HTTP interface rejects a multi-statement body with Code: 62. DB::Exception: Syntax error (Multi-statements are not allowed). That is not a tidiness convention — and it happens to be the only rule under which a ledger keyed on filename means anything. One filename, one change, one row.

Every statement names the database. This is the CLICKHOUSE_DB trap from earlier, made impossible instead of handled. An unqualified CREATE TABLE applies to default successfully, with no error, and the schema then exists somewhere nothing else looks. The runner refuses a file that does not name relay_analytics, and it refuses it by reading the statement rather than the file — a probe with relay_analytics. sitting in a comment is still refused.

A file that changed after it was applied is refused by name. ClickHouse has no ALTER path for most of what these statements do, so re-applying is not available and skipping silently is worse:

apply failed: 0001_daily_usage.sql changed after it was applied
              (ledger 385b775b5df80d89, file e676989c630ad7c9). ClickHouse has no
              ALTER path for most of this; add a new statement file instead

Loading it, and what a store reconstructed from state cannot know

ClickHouse reads Postgres itself, through the postgresql() table function, so the load is one INSERT … SELECT and this repository gains no dependency — grep -c clickhouse pnpm-lock.yaml is 0 before and after.

The table holds three rows per message, not one. SAD's event column is created|edited|deleted and the rollup filters on it, so a load that labels everything created would inflate messages-sent by every deletion. The edit rows come from message_edits, one row per edit — not from messages.edited_at, which holds only the latest and would lose an event for every message edited more than once.

And text_length is a different expression on each of the three:

message_edits records what a message used to say. So the text an edit produced is only ever in the row after it. For a message edited three times with prior lengths 47, 40 and 33 and a current text of 9, the store holds a created of 47 and edits of 40, 33 and 9 — each edit's result is the next one's memory.

The question, asked again

                    store                  rows       best of 3   days
   4.1   Postgres, ordered by id        1,000,000      585.9 ms     91
   4.2   ClickHouse, (environment_id, ts)  1,241,071    13.22 ms     91

Forty-four times, and it is the same question: count per day, count distinct senders per day, one environment, ninety days. There is no join, because environment_id is on the row. There is no sort, because the rows are already in that order.

The plan says where the work actually happens, and two of the three stages do none:

MinMax        Parts 4/4   Granules 154/154     skips nothing
Partition     Parts 4/4   Granules 154/154     skips nothing
PrimaryKey    Parts 4/4   Granules 131/154     skips 23

The date predicate cannot exclude anything from this table, and the reason is the TTL. Raw events expire at ninety days; the question asks about ninety days. Every row that survives already satisfies the date half of the query, so MinMax and the monthly partition have nothing to reject. The tenant half of ORDER BY (environment_id, ts) does all the work — which is the half you would have been least sure about, and you only know because you asked for the plan rather than the clock.

The rollup, and the one day that can never agree

flowchart LR
    raw["message_events<br/>1,241,029 rows"]
    mv["daily_usage<br/>363 rows"]
    q1["the raw query reads<br/>1,052,655 rows"]
    q2["the rollup reads<br/>315 rows"]
    raw -->|"materialised view,<br/>on the way in"| mv
    raw --> q1
    mv --> q2
    note["3,342x fewer rows read, and 19% less time.<br/>At a million rows the scan is not what costs.<br/>What changes is the SHAPE of the cost: 1,052,655 grows<br/>with the tenant, 315 grows with the calendar."]
    q2 ~~~ note

DR-10 says metering never scans raw events, so there is a materialised view maintaining a daily per-tenant rollup. It is not refreshed by anything: rows inserted after it exists are included on the way in, and 1,241,029 raw rows become 363 rollup rows.

Read it with sum() and GROUP BY, and not because it reads better:

SELECT day, sum(messages), uniqMerge(active_users_state)
  FROM relay_analytics.daily_usage
 WHERE environment_id = ? AND day >= toDate(now() - INTERVAL 90 DAY)
 GROUP BY day

SummingMergeTree holds one physical row per key per INSERT until a background merge collapses them. The whole corpus arrives in one statement, so it lands at one row per key and a bare SELECT messages looks perfectly correct. Three separate inserts of a thousand rows into one key then measured three physical rows, a bare read of 1000, and a sum of 3000. The row count per key is the number of inserts, not the number of events — so a dashboard written against a quiet afternoon is wrong on a busy one, and it is wrong by a plausible number.

The cost is worth stating honestly. The rollup reads 315 rows where the raw query reads 1,052,655, and it saves 19% of the time. At a million rows this query is already dominated by something other than scanning. What changes is not the clock but the shape: 1,052,655 grows with the tenant and 315 grows with the calendar.

flowchart TB
    ins["INSERT — the view counts the day WHOLE<br/>2026-06-15: 11,161 messages"]
    ttl["TTL cuts at a TIMESTAMP<br/>ts + 90 days &lt; now()"]
    raw["message_events keeps 6,220<br/>— the part of the day still inside ninety days"]
    roll["daily_usage keeps 11,161<br/>— it has no TTL, and a day is its finest grain"]
    gap["difference 4,941 — 0.49% of the ninety-day total,<br/>against FR-ANL-06's 0.1%"]
    ins --> ttl
    ttl --> raw
    ins --> roll
    raw --> gap
    roll --> gap
    note["90 of 91 days agree EXACTLY. The 91st cannot,<br/>and it is the oldest one, and the gap moves<br/>continuously as now() advances through it."]
    gap ~~~ note

Now compare the rollup against the raw table, day by day, with the same ninety days on both sides:

days compared   91
days agreeing   90
days differing   1     2026-06-15: raw 6,220 · rollup 11,161 · difference 4,941

Ninety of ninety-one agree exactly. The one that does not is the oldest day in the window, and it is structural. The TTL cuts at a timestamp and the rollup's finest grain is a day. The view counted that day whole when it was inserted; the merge then removed the rows whose ninety days had elapsed. As now() advances, the gap moves continuously through that day.

That is 4,941 of 1,004,877 — 0.49%, against FR-ANL-06's requirement that metered totals agree with operational counts to within 0.1%. DR-10 forbids reconciling against raw events. So the two clauses cannot both hold, and this reason does not depend on how big anyone's tenant is.

There is a second reason, and it is the one you would find first if you only tested the distinct-user column. On this corpus:

uniqMerge(active_users_state)   5,000
uniqExact(user_id)              5,000
difference                          0      0.0000%

A perfect agreement — over a corpus holding exactly 5,000 users. uniq is exact up to about 65,000 distinct values and approximate above it:

distinct     uniq          error
  60,000     60,000        0%
  65,000     65,000        0%
  70,000     70,359        0.5129%
 500,000    502,646        0.5292%

The threshold is a cardinality, not a row count, and this corpus is three orders of magnitude under it. The zero above is a fact about the corpus rather than about the reconciliation. Neither conflict is resolved here: there is no reconciliation job yet to test an amendment against, and amending a published clause without one is deciding before measuring. Both are filed.