What MariaDB’s plugin API can carry: the HyperLogLog case

Can a complete analytic feature live outside the MariaDB server core? By “complete” I mean a column type, aggregates that build its values, and scalar functions that read them. I built the type_hll plugin, which adds HyperLogLog sketches to MariaDB, and I use it here to answer that question.

The answer is mostly yes. One shared library, type_hll.so, registers the HLL data type and eight functions. You enable all of it with one statement:

INSTALL SONAME 'type_hll';

The “mostly” is important. The HLL commits change only files under plugin/type_hll/. But the aggregate mechanism they use needed server and parser changes on the same branch first. Pluggable aggregate functions will officially come to the server with the MariaDB 13.3 release. Until then, the plugin needs a server built from this branch.

The code is in the pull request MariaDB/server#6011. It contains the pluggable aggregate commits and the HLL commits on top of them.

Three extension mechanisms and their history

The plugin uses three mechanisms:

• Pluggable Data Type (PDT) for the HLL column type;
• Pluggable Aggregate Function (PAF), based on Item_sum_plugin, for building and merging sketches;
• function plugins (MariaDB_FUNCTION_PLUGIN) for scalar operations.

Two of them are not new. Alexander Barkov added data type plugins (MDEV-20016) and function plugins (MDEV-20837, first part) in October 2019. In the same month, the server got a data type for IPv6/IPv4 addresses (MDEV-274) and the UUID type (MDEV-4958). HLL treats UUID, INET4 and INET6 as pluggable data types. So these two APIs have existed for years, and other server types already use the PDT model.

The third mechanism is new. On this branch, pluggable aggregate functions were added first, in MDEV-40672. That commit changed the parser, the aggregate and window code, and the plugin header include/mysql/plugin_function.h. Three follow-up commits came next:

• a bug fix for DISTINCT with a literal argument, in the server and the parser, with no change to the plugin header;
• a change that lets every PAF decide if it supports DISTINCT;
• support for the FILTER syntax.

The first commit and two of the three follow-ups changed include/mysql/plugin_function.h and sql/item_sum.h. The HLL commits came only after that. HLL uses these changes: the build aggregate accepts DISTINCT, the union aggregate refuses it, and both accept FILTER (WHERE ...). So HLL uses two mature mechanisms and one young one.

The test case: distinct counts you can store and merge

Here is the user problem. You store the number of distinct users per day. You cannot add two daily numbers, because one user can appear on both days. So each new question (“per week”) usually means a new scan of raw events.

A HyperLogLog sketch is a small value that you store instead of the number, and sketches can be merged. Merge is commutative, associative and idempotent at the level of serialized bytes. In plain words: the order of merging does not matter, and merging the same sketch twice changes nothing. HLL_BUILD_AGG over a full set gives the same bytes as HLL_UNION_AGG over its parts.

This needs all three kinds of extension, so HLL is a good test. A daily rollup (names are only an example; user_id is an integer):

CREATE TABLE daily_users (
  day  DATE NOT NULL,
  h    HLL  NOT NULL
);

INSERT INTO daily_users (day, h)
SELECT event_date, HLL_BUILD_AGG(user_id)
FROM events
GROUP BY event_date;

-- distinct users for one week, from stored sketches only
SELECT HLL_CARDINALITY_ROUND(HLL_UNION_AGG(h)) AS weekly_users
FROM daily_users
WHERE day BETWEEN '2024-01-01' AND '2024-01-07';

Besides the two aggregates, there are six scalar functions: HLL_UNION(a, b), HLL_CARDINALITY() (DOUBLE), HLL_CARDINALITY_ROUND() (BIGINT UNSIGNED), HLL_EMPTY(), HLL_FORMAT_VERSION() and HLL_INFO().

Up to 256 distinct values, the count is exact. Above that, the sketch uses classic HyperLogLog with 16384 registers and an expected standard error of about 0.8125%. Both aggregates skip SQL NULL and never return NULL, but HLL_UNION(NULL, x) returns NULL.

Two more use cases

Daily users are the classic example, but the same pattern appears in other areas. The two cases below use the same type and the same two aggregates. They need no new plugin code.

Network security: scan detection. A host that scans a network contacts many distinct ports or addresses in a short time. Research on traffic monitoring uses HyperLogLog to count distinct destination ports and detect port scans (Chabchoub et al., 2014). Another paper describes both directions: count distinct destination addresses or ports per source to find a scanner, and count distinct source addresses per destination to detect a DDoS attack (Xiao et al., 2020). In MariaDB you can keep one sketch per source and minute, and then ask for any time window:

CREATE TABLE scan_stats (
  minute DATETIME NOT NULL,
  src    INET6    NOT NULL,
  ports  HLL      NOT NULL
);

INSERT INTO scan_stats
SELECT DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00'), src_ip, HLL_BUILD_AGG(dst_port)
FROM flows
GROUP BY 1, 2;

-- sources that touched more than 1000 distinct ports in the last hour
SELECT src, HLL_CARDINALITY_ROUND(HLL_UNION_AGG(ports)) AS ports
FROM scan_stats
WHERE minute >= NOW() - INTERVAL 1 HOUR
GROUP BY src
HAVING ports > 1000;

Here the plugin’s INET6 support is useful: the address is a normal column, and HLL accepts INET4 and INET6 values as input.

Advertising: campaign reach. Reach is the number of distinct households or users that saw a campaign. Analysts filter it by campaign, region, device and date range, so an exact COUNT(DISTINCT) has to run again for every filter. Ad platforms store HyperLogLog sketches per combination of dimensions instead, and merge the rows that match the filter (Jamloop, Adobe Experience Platform). With the plugin, this is a small cube:

CREATE TABLE reach_cube (
  day        DATE    NOT NULL,
  campaign   INT     NOT NULL,
  region     CHAR(2) NOT NULL,
  households HLL     NOT NULL
);

INSERT INTO reach_cube
SELECT day, campaign, region, HLL_BUILD_AGG(household_id)
FROM impressions
GROUP BY day, campaign, region;

-- reach of one campaign in two regions for a month
SELECT HLL_CARDINALITY_ROUND(HLL_UNION_AGG(households)) AS reach
FROM reach_cube
WHERE campaign = 42
  AND region IN ('DE', 'AT')
  AND day BETWEEN '2024-03-01' AND '2024-03-31';

One limit matters here. A union of segments works well, but v1 has no intersection function. Questions like “households that saw both campaigns” need the formula |A| + |B| − |A ∪ B|, and its error is large when the overlap is small.

What the server provides, and what the plugin writes itself

The HLL type: a Pluggable Data Type

From the server. The type reuses the server’s long BLOB support: variable-length binary data with a 4-byte length, like LONGBLOB. Tables, row-based and statement-based replication, temporary tables, views, derived tables and prepared statements work with HLL, and tests cover them. The client sees a binary column with the extended type name hll, and a dump can use mariadb-dump --hex-blob. Neither path is tested yet.

Written by the plugin. The plugin decides what a valid value is and checks it on every input path, including INSERT, UPDATE, LOAD DATA, ALTER TABLE ... MODIFY blob_col HLL, row-based replication and CAST(expr AS HLL). A malformed value is always an error, even in non-strict sql_mode or with INSERT IGNORE. The plugin rejects comparison, arithmetic, MIN/MAX, numeric and temporal casts, and keys; the server itself rejects FULLTEXT. It also makes COALESCE(h, h), UNION ALL and CTAS keep the HLL type.

For plugin developers. Type_handler_hll extends Type_handler_long_blob, and Field_hll extends Field_blob; FixedBinTypeStorage is not used because the value has variable length. Validation runs in Field_hll::store(), Field_hll::unpack() and Item_hll_typecast. Type_collection_hll returns NULL from aggregate_for_comparison(), aggregate_for_min_max() and aggregate_for_num_op(), and illegal functions go to Item_func_or_sum_illegal_param.

The aggregates: Pluggable Aggregate Functions

From the server. The server parses the call (the parser changes on this branch are part of this), groups rows, and runs the aggregate in GROUP BY, HAVING, ROLLUP, CTEs and derived tables. It applies FILTER (WHERE ...) before the plugin sees a row. For HLL_BUILD_AGG(DISTINCT expr), it removes duplicates and replays the unique values to the plugin; the result has the same bytes as without DISTINCT. For moving window frames, it computes the frame again, because the plugin does not support removal.

Written by the plugin. The per-row step, the reset step and the final result. The plugin also chooses which features it accepts: HLL_UNION_AGG refuses DISTINCT with ER_NOT_SUPPORTED_YET, because HLL has no equality contract in v1.

For plugin developers. Both aggregates extend Item_sum_hll, which extends Item_sum_plugin. Read the row through aggregation_arg(i), not args[i]; implement clear(), add_row(), result accessors and copy_or_same(); add() is final and applies FILTER; call aggr->endup() before returning the result; return the PDT through val_native(); do not share mutable state between Item copies. HLL_UNION_AGG overrides supports_distinct() to false; supports_removal() stays false.

The scalar functions: function plugins

From the server. One library declares all SQL functions, scalar and aggregate, next to the data type. There is no separate CREATE AGGREGATE FUNCTION ... SONAME step.

Written by the plugin. Argument checks. Arguments can be HLL, NULL or a binary string, because user variables and statement parameters do not keep the HLL type. Other types fail with ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION.

For plugin developers. One maria_declare_plugin(type_hll) declares the data type plugin hll and eight MariaDB_FUNCTION_PLUGIN entries. One template builder creates scalar and aggregate functions, and create_native() checks the argument count:

template<class T, uint ARG_COUNT, bool AGGREGATE= false>
class Create_func_hll: public Create_native_func
{
  bool is_aggregate() const override { return AGGREGATE; }
  Item *create_native(THD *, const LEX_CSTRING *, List<Item> *) override;
  static Plugin_function *descriptor();
};

Rules the plugin had to define itself

The server does not know what an HLL value means. Three things were the plugin’s own job, and they decide whether stored sketches stay valid for years.

A versioned binary format. The first byte holds the format version in the high four bits and the representation in the low four bits. There are three representations:

unique values:   0       1..256                  257+
representation:  EMPTY   EXPLICIT                FULL
bytes on disk:   1       1 + 8*n (max 2049)      12289 (fixed)
count:           0       exact                   estimate

There is no magic number, no length field and no checksum. The type identifies the value, the BLOB container gives the length, and the storage engine protects integrity. Because the server provides the container, the format can stay minimal. The value stores no derived caches and no C++ struct layout.

One codec. All paths that read or write HLL bytes use Hll_codec, which dispatches by version. Today only v1 exists; any other version, including 0, is rejected with the same ER_WRONG_VALUE as a broken value.

A hash contract. A sketch stores only hashes, so the hash input must never change silently. v1 uses XXH3-64 with seed 0, and every input starts with a one-byte type tag followed by canonical bytes. A change of algorithm, seed or encoding needs a new format version, because old registers cannot be re-hashed without the source data.

So the plugin is strict. Integers, FLOAT/DOUBLE, temporal types, binary-collation strings and UUID/INET4/INET6 are accepted. DECIMAL, BIT, JSON, ENUM, SET, unknown types, HLL itself and text with a normal collation are rejected when the query is prepared. For text, the user chooses the byte form:

SELECT HLL_BUILD_AGG(CAST(email AS BINARY)) FROM users;

The type is part of the element: HLL_BUILD_AGG(5) and HLL_BUILD_AGG(CAST(5 AS DOUBLE)) together give cardinality 2. All integer types share one domain, so TINYINT 5 and BIGINT UNSIGNED 5 are the same element.

For plugin developers. The algorithm (Hll_state in hll.h / hll.cc) has no dependency on THD, Item or Field. The persisted format lives only in decode_v1() / encode_v1(). The aggregate calls free functions hll_input_domain() and hll_hash_item() from item_hllfunc.h, so a future scalar API can reuse the contract. Byte-level vectors in MTR hll_hash.test fix the canonical encodings.

Limits: where the extension contract ends today

Some limits come from HLL v1. Others show where the plugin API is still incomplete, and they matter to anyone who plans their own plugin.

Limits at the plugin/server boundary

No comparison for HLL values. Comparison and keys are rejected. ORDER BY, GROUP BY, DISTINCT and PARTITION BY over an HLL value are not tested, and the plugin does not define special behavior for them. Do not sort, group, partition or use DISTINCT on a sketch column.

No removal in aggregates. A sketch cannot remove an element, so the plugin does not implement remove(). Moving window frames are still correct, byte for byte, because the server computes each frame again, but this can be much slower. A plugin aggregate with DISTINCT is not allowed as a window function.

Other pluggable types need an adapter. The plugin knows UUID, INET4 and INET6 only through adapters, selected by the type handler name. A new pluggable type is rejected until someone writes an adapter, because there is no generic stable-encoding callback yet. HLL also treats INET4 and INET6 as different types, although SQL sees INET4 '1.2.3.4' and INET6 '::ffff:1.2.3.4' as equal. After ALTER ... MODIFY inet4_col INET6, old and new sketches must not be merged, or common addresses are counted twice. Use CAST(addr AS INET6) if you need one address space.

Lifecycle and client paths are not tested. Plugin unload while tables use the type, protocol metadata, and dump with restore are not covered by tests. Results of builds, MTR runs and ASan/UBSan runs are not documented. Treat these areas as unknown.

A young API. After its first commit, PAF got three follow-up commits on this branch. Two of them changed the plugin header plugin_function.h. A plugin written against it today should expect more changes.

Limits of HLL v1

Fixed size and precision. Precision is fixed at p = 14. Above 256 distinct values a sketch always takes 12289 bytes; at 256 values it takes 2049 bytes. There is no SPARSE representation.

Memory. Each runtime state is about 18.4 KB, even for one value, and HLL_UNION_AGG keeps two. The design document gives no numbers for memory use in a GROUP BY with many groups.

Edge cases. On non-transactional engines such as MyISAM, rows written before a bad row stay in the table. A temporal value that the server cannot convert is skipped as NULL, with no warning; this path is not tested.

No binary compatibility with pg_hll, ClickHouse, Redis or DuckDB.

Trajectory: next steps, and why the plugin model allows them

The items below are directions, not commitments, and there are no dates.

New format versions inside the plugin. The version in the first byte and the version dispatch in the codec are there for future formats. SPARSE and configurable precision could come as a new format version, if measurements show a need. The upgrade plan is defined: the new plugin first reads v1 and v2 but writes v1; then all primary and replica nodes are upgraded; only then does output switch to v2. The v1 decoder stays, and a SELECT never rewrites stored values. Because format and codec live in the plugin, I expect a new HLL format to need only a new plugin version, but this is not proven.

A prehashed API. The design describes HLL_HASH(expr) -> HLL_HASHVALUE and HLL_ADD_AGG(HLL_HASHVALUE) -> HLL. HLL_BUILD_AGG(value) must give the same bytes as HLL_ADD_AGG(HLL_HASH(value)). The hash code is already separate from the aggregate code for this reason. This would add a new value type (HLL_HASHVALUE, possibly a second PDT), one scalar function and one aggregate to the same library.

A generic encoding callback for data types. The design notes that such a callback for PDTs could be designed separately. In my view, this is a change to the extension system, not to HLL. With it, a new pluggable type could work with HLL without new adapter code.

My own proposals, which need design work and benchmarks: a faster HLL_UNION_AGG that merges packed registers directly (today each FULL argument is unpacked to one byte per register); and a smaller runtime state for small sketches (today about 18.4 KB for every representation).

The PAF API should also grow. The DISTINCT and FILTER commits show the pattern: a missing feature is added once in the server, and each plugin aggregate can then use it. FILTER works for all of them, and DISTINCT is a choice of each aggregate. Removal support and a comparison contract for pluggable types would remove two of the limits above.

Conclusion

type_hll shows that MariaDB’s extension system can provide a full analytic feature in one library: a data type, two aggregates and six scalar functions. The server provides storage, replication, the client protocol, DISTINCT, FILTER and window execution. On this branch, it also got new server code for plugin aggregates, which comes officially with MariaDB 13.3. The plugin provides the meaning: format, codec, hash contract, validation and type rules. Some limits belong to a young aggregate API, not to HLL. If you plan your own plugin, HLL is a working example of how to divide the work.