Skip to content

CONCEPT Cited by 1 source

Sketch as MySQL binary column

Sketch as MySQL binary column is the pattern of storing a quantile-sketch data structure (DDSketch, t-digest, HDRHistogram) as a BLOB column in a MySQL row, paired with loadable C++ functions that parse the binary format for merge / quantile operations.

The shape of the row is:

CREATE TABLE per_minute_rollup (
  database_id BIGINT,
  fingerprint BINARY(16),
  bucket_time DATETIME,
  count BIGINT,
  total_time_us BIGINT,
  latency_sketch BLOB,             -- serialised DDSketch
  ...
  UNIQUE KEY (database_id, fingerprint, bucket_time)
);

And the canonical query shape:

SELECT
  sketch_quantile(merge_sketches(latency_sketch), 0.95)
FROM per_minute_rollup
WHERE database_id = ? AND bucket_time BETWEEN ? AND ?;

(Source: sources/2026-04-21-planetscale-storing-time-series-data-in-sharded-mysql-to-power-query-insights.)

Why BLOB over normalised tables

The naive relational alternative would be storing the sketch as a per-bucket table — one row per bucket of the DDSketch. This has three problems:

  1. Row count explosion. A DDSketch at α=0.01 has up to a few hundred buckets populated; one row per bucket per pattern per minute is 100× the aggregate-row count.
  2. Merge is a JOIN, not a single-row read. Cross-interval merge (e.g. for a 30-day p95) requires joining and aggregating across all bucket rows — O(N × buckets) instead of O(N) sketch-merge operations.
  3. Schema rigidity. Changing α (say to 0.02 or 0.005) changes bucket boundaries; a normalised-table layout needs a migration, a BLOB layout does not.

Storing the sketch as an opaque BLOB pushes the bucket- traversal into the loadable function, where it's a tight C++ loop over the binary buffer — no storage engine overhead per bucket.

Canonical PlanetScale framing

Rafer Hazen, 2023-08-10: "The sketches are read from Kafka and written to MySQL in a custom binary format. We've implemented a small library of loadable C++ MySQL functions that know how to read and write the binary format, allowing us to aggregate sketches and compute arbitrary percentiles in MySQL."

Property: sketches are mergeable in the storage layer

The canonical reason this works is that DDSketches are mergeable — summing bucket counts across sketches produces a new valid sketch with the same error guarantee. In SQL terms, MERGE is an aggregate function (with init / add / result semantics) — exactly the shape MySQL's aggregate loadable-function API supports.

A non-mergeable sketch (e.g. a naive random sample) couldn't use this pattern; the choice of DDSketch is structurally motivated by this composability requirement.

Schema evolution & format versioning

A production implementation of sketch-as-BLOB needs a version byte at the head of the binary layout so the loadable function can reject or upgrade older sketches if the serialisation changes. Hazen's post doesn't disclose this but it's implicit in any long-lived binary-column design.

Storage-cost framing

PlanetScale's published α=0.01 DDSketch yields "suitably small sketches" — in practice a few hundred bytes per sketch at typical latency distributions. At 10s of millions of patterns × 1440 minutes/day × a few hundred bytes, the per-minute rollup is in the single-digit-to-low-double-digit TB range fleet-wide — well inside a sharded MySQL cluster's operational envelope.

Seen in

Last updated · 470 distilled / 1,213 read