Ingesting AggregateFunction States: Patterns and Best Practices

An AggregateFunction column stores the intermediate state of an aggregate function - a hash table for uniq, a digest for quantile, a running sum for sum - rather than a finalized number. Reading and storing these states is well documented, but actually getting raw data into an AggregateFunction column trips up many teams, because you cannot simply INSERT a plain value into one.

This guide covers the full ingest side: how to turn raw rows into state objects, how to push those states into an AggregatingMergeTree table, the differences between per-row and per-group state creation, and how to reload previously serialized states. For the storage and read side (-Merge combinators, querying rollups), see the AggregatingMergeTree page.

Why You Can't Just INSERT a Value

An AggregateFunction(uniq, UInt64) column does not hold a UInt64. It holds the serialized internal state of uniq. A naive INSERT INTO t VALUES (42) fails with a type error, because 42 is not a state object. Every ingestion technique below is a way to construct a valid state from raw input.

There are three broad sources of states:

1. Aggregating at insert time - a GROUP BY with -State combinators produces one state per group. This is the canonical materialized-view pattern.
2. One state per row - arrayReduce or initializeAggregation wrap a single value into a state without grouping, leaving the actual aggregation to background merges.
3. Pre-serialized states - binary states already produced by ClickHouse, reloaded with the right input format.

Pattern 1: Materialized View with GROUP BY (Canonical)

The most common pattern feeds an AggregatingMergeTree from a raw table through a materialized view that runs -State combinator functions over a GROUP BY.

-- Raw landing table
CREATE TABLE events_raw
(
    tenant_id  UInt32,
    event_time DateTime,
    user_id    UInt64
)
ENGINE = MergeTree
ORDER BY (tenant_id, event_time);

-- Rollup table holding partial states
CREATE TABLE events_hourly_agg
(
    tenant_id    UInt32,
    event_hour   DateTime,
    events_count AggregateFunction(sum, UInt64),
    unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(event_hour)
ORDER BY (tenant_id, event_hour);

-- MV that builds states per group on every insert into events_raw
CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly_agg AS
SELECT
    tenant_id,
    toStartOfHour(event_time) AS event_hour,
    sumState(1::UInt64)       AS events_count,
    uniqState(user_id)        AS unique_users
FROM events_raw
GROUP BY tenant_id, event_hour;

Each INSERT into events_raw triggers the view, which aggregates that insert block by (tenant_id, event_hour) and writes one state row per group. Background merges combine same-key states across blocks. Note that the GROUP BY only aggregates within a single insert block, so multiple partial states per key are expected and normal - they collapse during merges and are reconciled at read time with -Merge.

The column order and types in the MV's SELECT must line up with the target table. sumState(1::UInt64) must feed an AggregateFunction(sum, UInt64); a mismatch in the inner type (for example sumState(1) producing UInt8) will fail or silently store an incompatible state.

Pattern 2: One State Per Row (No GROUP BY)

Sometimes you want exactly one output row per source row, deferring all aggregation to merge time. This is useful when the source stream is already keyed and you do not want insert-time grouping. Two functions build a single-row state.

initializeAggregation

initializeAggregation('funcState', arg1[, arg2, ...]) creates an aggregate state from a single value, one state per row:

CREATE MATERIALIZED VIEW events_hourly_mv2 TO events_hourly_agg AS
SELECT
    tenant_id,
    toStartOfHour(event_time)                AS event_hour,
    initializeAggregation('sumState', 1::UInt64)   AS events_count,
    initializeAggregation('uniqState', user_id)    AS unique_users
FROM events_raw;

Because there is no GROUP BY, each source row yields exactly one state row; the AggregatingMergeTree merges them later. If you go this route, set optimize_on_insert = 0 so ClickHouse does not pre-collapse rows during the insert.

arrayReduce

arrayReduce('funcState', [values][, [extra_args]]) applies an aggregate function over an array and returns the state. It is the building block for wrapping single values and is especially handy for argMax/argMin, which need a second argument array:

SELECT arrayReduce('argMaxState', ['Jane'], [toDateTime('2020-01-02 00:00:00')]);

You can sanity-check any state you build by finalizing it in place with finalizeAggregation:

SELECT finalizeAggregation(arrayReduce('maxState', [1, 2, 3]));
-- 3

Pattern 3: Inserting Directly via input()

If you do not want a permanent MV, the input() table function lets a single INSERT accept raw columns and transform them into states inline:

INSERT INTO events_hourly_agg
SELECT
    tenant_id,
    toStartOfHour(event_time) AS event_hour,
    arrayReduce('sumState', [1::UInt64]),
    arrayReduce('uniqState', [user_id])
FROM input('tenant_id UInt32, event_time DateTime, user_id UInt64')
FORMAT Values (10, '2024-01-01 10:05:00', 555);

input() describes the raw schema the client sends; the SELECT converts those raw columns into states before they land in the AggregateFunction columns.

Pattern 4: Ephemeral Column with a Default Expression

You can keep raw values out of the table while letting clients insert them by their natural type, using an Ephemeral column plus a default expression that builds the state. This is convenient when an upstream producer cannot run aggregate SQL but can send plain columns:

CREATE TABLE users
(
    uid        Int16,
    updated    SimpleAggregateFunction(max, DateTime),
    name_stub  String Ephemeral,
    name       AggregateFunction(argMax, String, DateTime)
        DEFAULT arrayReduce('argMaxState', [name_stub], [updated])
)
ENGINE = AggregatingMergeTree
ORDER BY uid;

INSERT INTO users (uid, updated, name_stub)
VALUES (1231, '2020-01-02 00:00:00', 'Jane');

The name_stub column is never stored; it only exists to feed the argMaxState default that materializes the real name state. Note the use of `SimpleAggregateFunction` for max - simple, associative functions (sum, max, min, any, anyLast) can use SimpleAggregateFunction, which stores the plain value and needs no -State/-Merge plumbing.

Pattern 5: Loading Pre-Serialized Binary States

An AggregateFunction value has an implementation-specific binary representation. If you SELECT a state column to a binary format (e.g. Native, RowBinary, or TabSeparated), you can load the dump straight back with INSERT - this is how backups, cross-table copies, and INSERT ... SELECT between AggregateFunction tables work without re-deriving states.

The session setting aggregate_function_input_format (introduced in ClickHouse 25.12) controls how an input column for an AggregateFunction is interpreted, with three modes:

With the default state, the bytes are taken as-is, so a round-trip dump-and-reload is lossless:

-- Copy states between AggregateFunction tables without re-aggregating
INSERT INTO events_hourly_agg_copy
SELECT * FROM events_hourly_agg;

Because state binary formats are version- and implementation-specific, only reload states produced by a compatible ClickHouse build, and prefer the Native format for state round-trips.

Choosing a Pattern

Best Practices

1. Match inner types exactly. sumState(1::UInt64) for AggregateFunction(sum, UInt64). A wrong inner type (UInt8 vs UInt64) is the most common ingest failure.
2. Keep raw and state columns separate. Do not mix a plain UInt64 and an AggregateFunction for the same metric in one column - the engine only merges AggregateFunction / SimpleAggregateFunction columns.
3. Prefer SimpleAggregateFunction where it applies. For sum, max, min, any, you avoid -State/-Merge entirely and can insert and read plain values.
4. Use initializeAggregation with optimize_on_insert = 0 when you want exactly one state per source row and merge-time aggregation.
5. Watch insert block size. A per-block GROUP BY that produces few groups per block but many blocks creates many small parts - the same Too Many Parts risk as any high-frequency insert. Batch inserts upstream.
6. Never finalize with the wrong combinator. Reading an AggregateFunction(sum, ...) with sum() instead of sumMerge()/finalizeAggregation() returns garbage.

Common Issues

• "Cannot convert ... to AggregateFunction" on INSERT - you tried to insert a raw value. Wrap it with a -State function, arrayReduce, or initializeAggregation, or route through an MV.
• Garbage numbers when reading - you read the state column directly or with a plain aggregate. Use the matching -Merge combinator, or finalizeAggregation.
• Type-mismatch on the MV SELECT - the state's argument type does not match the column definition; cast the raw value (e.g. 1::UInt64).
• Duplicate-looking rows - multiple partial states per key are normal before merges complete. Always read with GROUP BY ... -Merge rather than assuming a single row per key.
• Reloaded states read as nonsense - the dump came from an incompatible ClickHouse version or a non-binary format. Round-trip states via Native and matching versions.

How Pulse Helps

State ingestion problems are easy to introduce and hard to spot, because a wrong inner type or a missing GROUP BY often succeeds at insert time and only produces wrong numbers downstream. Pulse monitors AggregatingMergeTree tables and their feeding materialized views - tracking merge backlogs, part growth from per-block state inserts, and view chains that fan out too many small writes. When a rollup stops converging or a state column starts returning unexpected values, Pulse surfaces the table, the responsible view, and the merge or insert pattern behind it, so you can correct the ingest path rather than guessing.

Frequently Asked Questions

Q: Can I INSERT a plain number into an AggregateFunction column?

Not directly. You must construct a state first - with a -State combinator, arrayReduce, or initializeAggregation - or set aggregate_function_input_format = 'value' so a raw value is interpreted as a one-row state.

Q: What's the difference between sumState in a GROUP BY and initializeAggregation('sumState', x)?

sumState inside a GROUP BY produces one state per group (per insert block). initializeAggregation('sumState', x) produces one state per row with no grouping, leaving all aggregation to background merges. Use the latter with optimize_on_insert = 0.

Q: How do I create an argMaxState for ingestion?

Use arrayReduce('argMaxState', [value], [ordering_value]) - it needs both the value array and the ordering-argument array. It works in an MV, an INSERT SELECT, or an Ephemeral column default.

Q: Can I copy AggregateFunction data between tables without re-aggregating?

Yes. With the default aggregate_function_input_format = 'state', INSERT INTO copy SELECT * FROM source moves the serialized states as-is. Use the Native format for dump-and-reload across servers, and keep ClickHouse versions compatible.

Q: Why do I see multiple rows per key after ingesting states?

AggregatingMergeTree merges are asynchronous, so several partial states per key coexist until a merge collapses them. Always read with an explicit GROUP BY and -Merge combinators (or FINAL, which is slower).

Q: Should I aggregate at insert time or store one state per row?

Aggregate at insert time (GROUP BY + -State) when each block contains many rows per key - it shrinks parts immediately. Store one state per row (initializeAggregation) when the stream is already keyed or low-cardinality per block, so you avoid the cost of insert-time grouping and let merges do the work.