ClickHouse for events, MariaDB for issues

An error tracker receives two very different kinds of writes. Events arrive by the thousand, are never updated, and are mostly read in bulk: “show me everything from this route in the last hour”. Issues are few, change constantly (resolved, assigned, reopened, seen again), and are read one at a time. loggerxo keeps them in different databases.

The ingest path

The Laravel SDK and the browser script both send a JSON envelope to /api/ingest. That endpoint does as little as possible: validates the project key, checks a rate limit, pushes the envelope onto a Redis list, and returns 202. It does not touch either database. Under load this is what keeps the SDK from slowing down the application it is monitoring.

A worker pops envelopes from the list in batches of up to five hundred and does the real work:

1. Mask anything that looks like a secret (the SDK already did this once; the server does it again, because SDK versions in the wild lag).
2. Compute a fingerprint for each exception.
3. Upsert the issue in MariaDB: insert if the fingerprint is new, otherwise bump last_seen and the event count, and reopen it if it was resolved.
4. Bulk insert the events into ClickHouse, each carrying its issue id.

The upsert is one INSERT ... ON DUPLICATE KEY UPDATE per batch, grouped by fingerprint, so a burst of a thousand copies of the same error is one MariaDB write. The ClickHouse insert is one statement per batch.

Fingerprinting

Two exceptions are the same issue if they have the same type, the same normalised message and the same top frames. Normalising the message is the part that took iteration. A message like User 4f2a...c9 not found in tenant 1882 has to become User <uuid> not found in tenant <int>, or every user produces a new issue. The fingerprinter replaces UUIDs, hex hashes, IP addresses, integers and quoted strings with placeholders before hashing. It also drops frames inside vendor directories from the hash, so an upgrade of a library does not split an existing issue in two.

Why not one database

We tried MariaDB alone first. It handled ingest fine. Queries did not: “p95 latency per route for the last seven days” over a few hundred million rows is a table scan with a sort, and it took long enough that the performance page was unusable. ClickHouse answers the same query in under a second on the same hardware because it stores columns, not rows, and the query only reads three of them.

We also tried ClickHouse alone. Updates are the problem there. Resolving an issue is a mutation, and ClickHouse mutations are asynchronous and expensive. Keeping a small, frequently updated table in a row store and pointing at it by id from the column store is the obvious split once you have seen both fail.

Retention

Each project has a retention period. A nightly job drops ClickHouse partitions older than that, which is instant because partitions are by day, and deletes issues whose last_seen is older than the period and that have no remaining events. Logs and performance spans follow the same rule. Self-hosters with small disks set seven days; the hosted version defaults to thirty.