Skip to content

CONCEPT Cited by 1 source

Loadable MySQL function

A loadable MySQL function (often called a user-defined function / UDF in MySQL terminology, or "loadable function" in 8.0+ vocabulary) is a native C or C++ function compiled into a shared library and registered with the MySQL server so it can be called from SQL. Once registered, the function behaves syntactically like a built-in — it can appear in SELECT expressions, WHERE predicates, aggregate contexts, and returns values convertible to MySQL types.

The mechanism has existed in MySQL since the 4.x era; the canonical uses are domain-specific numeric functions, cryptographic helpers, and bridges to custom binary formats stored as BLOB columns.

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

Why loadable functions

Three load-bearing properties:

  • Server-side computation on binary columns. A BLOB-backed custom data structure (e.g. a DDSketch) is opaque to stock SQL. A loadable function that parses the BLOB binary format lets the server compute sketch_merge() or sketch_quantile() in-query, avoiding a round trip of the raw bytes to the application.
  • Full SQL expressive power. Once the function is a first-class SQL expression, it composes with joins, GROUP BY, WHERE predicates, subqueries. "Performing these functions in MySQL allows us to calculate percentiles without needing to pull the underlying sketches into our application. It also lets us use the full expressive power of SQL to get the data we need" (Hazen, 2023-08-10).
  • Aggregate variants. MySQL supports aggregate loadable functions (with init → add → result callbacks) that accumulate across rows in a GROUP BY — the natural shape for SELECT merge_sketches(latency_sketch) FROM per_minute_rollup GROUP BY minute_bucket.

PlanetScale's canonical usage

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." Planned open-source: "We plan to open source the MySQL function library in the near future."

The conjectural API surface (not disclosed in the post):

  • sketch_merge(b1 BLOB, b2 BLOB) → BLOB — merge two serialised DDSketches, return a new serialised DDSketch. Probably exposed as both a scalar function and an aggregate.
  • sketch_quantile(b BLOB, q DOUBLE) → DOUBLE — compute the q-th quantile of a serialised DDSketch.
  • sketch_count(b BLOB) → BIGINT — sample count of the sketch.

Compilation and deployment

A loadable function library is a shared object (.so on Linux) installed into the MySQL plugin directory and registered via CREATE FUNCTION name RETURNS <type> SONAME 'lib.so'. In managed-database environments this is typically a vendor-only capability because untrusted shared libraries running inside mysqld are a blast-radius risk. PlanetScale's fleet can ship custom .so files as part of their managed-database platform; a DBaaS customer on stock RDS could not.

Security model

Loadable functions run inside the mysqld process with server privileges. A buggy function can crash the server; a malicious one is a root-equivalent escape. For this reason MySQL 8.0 restricts the default plugin directory to root-owned paths and the CREATE FUNCTION … SONAME privilege to specific roles. In a managed-database product only the vendor is expected to author and install these.

Relationship to server-side procedural SQL

Stored procedures in MySQL (SQL-procedural) can carry business logic, but they cannot implement binary-format parsing efficiently. Loadable functions fill the performance-critical niche: anything that needs to traverse a custom byte layout, call C / C++ library code, or amortise per-row overhead across a batch.

Seen in

Last updated · 470 distilled / 1,213 read