Aggregations over a key's history
Many real-time features summarize what happened to a key recently: the purchases of a customer in the last 30 days, the failed logins of an account in the last hour, the refunds of a merchant this week. The rows live in an operational database, one per event, and the feature is an aggregate over the rows of one key within a window that ends at the time of the request.
This page builds such a feature end to end over PostgreSQL: the events table, the features, a free-form query, a prepared statement that takes the time of the call, and where the aggregation runs.
The events
One row per event, indexed by account and time. The fixture dates them relative to now, so account 1 has two events in the last 30 days (a purchase and a refund) and one older; account 2 has one; account 3 has none:
SELECT account_id, event_id, EXTRACT(DAY FROM (now() AT TIME ZONE 'UTC') - event_ts)::BIGINT AS age_days, amount, kind
FROM serving_slt_history_events;| ACCOUNT_ID BIGINT | EVENT_ID BIGINT | AGE_DAYS BIGINT | AMOUNT DOUBLE | KIND VARCHAR |
|---|---|---|---|---|
| 1 | 1 | 1 | 10.0 | purchase |
| 1 | 2 | 3 | 25.0 | refund |
| 1 | 3 | 40 | 20.0 | purchase |
| 2 | 4 | 2 | 30.0 | purchase |
The features
CREATE OR REPLACE FEATURES IN FM.TUTORIALS.HISTORY AS
PERSIST
POSTGRES_CONN := SOURCE_DB(
'postgresql://featuremesh@host.docker.internal:25432/featuremesh?sslmode=disable'
WITH (
password = SECRET('env', 'FEATUREMESH_DEMOS_PG_PASSWORD'),
tables = ARRAY['serving_slt_history_events'],
timeout = '2s'
)
),
ACCOUNTS := ENTITY(),
EVENTS := ENTITY(),
ACCOUNT_ID := INPUT(BIGINT#ACCOUNTS),
EVENT_ID := INPUT(BIGINT#EVENTS),
REQ_TS := INPUT(TIMESTAMP),
EVT := EXTERNAL_COLUMNS(
event_id BIGINT#EVENTS BIND TO EVENT_ID,
account_id BIGINT#ACCOUNTS,
event_ts TIMESTAMP,
amount DOUBLE,
kind VARCHAR
FROM TABLE(FM.TUTORIALS.HISTORY.POSTGRES_CONN[serving_slt_history_events])
),
IN_LAST_30D := EVT[account_id] = ACCOUNT_ID AND EVT[event_ts] > REQ_TS - INTERVAL '30 days',
LAST_30D := ROW(
RELATED(COUNT() ON IN_LAST_30D) AS n_events,
RELATED(SUM(EVT[amount]) ON IN_LAST_30D) AS total,
RELATED(COUNT_IF(EVT[kind] = 'refund') ON IN_LAST_30D) AS n_refunds
);| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.TUTORIALS.HISTORY.ACCOUNTS | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.ACCOUNT_ID | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.EVENTS | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.EVENT_ID | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.EVT | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.IN_LAST_30D | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.LAST_30D | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.POSTGRES_CONN | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.REQ_TS | CREATED | Feature created as not exists |
Read it from the bottom up:
LAST_30Dis a row of three aggregates. Each isRELATED(aggregate ON condition): the aggregate over the events for which the condition holds, computed for each account.IN_LAST_30Dis that condition, written once and shared: the event belongs to the account, and it happened in the 30 days beforeREQ_TS.EVTmaps the table's columns. The table is keyed byevent_id(BIND TO EVENT_ID);account_idis just a column, annotated#ACCOUNTSso that FeatureQL knows it holds an account key.REQ_TSis an input like any other: the time the window ends at. Making it an input, rather than callingNOW()inside the feature, keeps the feature deterministic: the sameREQ_TSgives the same answer, so a test pins it and a past call can be replayed. The prepared statement below binds it to the time of the call.
A free-form query
A query binds every input itself:
RETURN
FM.TUTORIALS.HISTORY.ACCOUNT_ID,
FM.TUTORIALS.HISTORY.LAST_30D
FOR CROSS
FM.TUTORIALS.HISTORY.ACCOUNT_ID := BIND_VALUES(ARRAY[1, 2, 3]),
FM.TUTORIALS.HISTORY.REQ_TS := BIND_VALUES(ARRAY[@eval_as_literal(NOW())]),
NESTED FM.TUTORIALS.HISTORY.EVENT_ID := @BIND_KEYSET(FM.TUTORIALS.HISTORY.EVT);| ACCOUNT_ID BIGINT | LAST_30D ROW(n_events BIGINT, total DOUBLE, n_refunds BIGINT) |
|---|---|
| 1 | {n_events: 2, total: 35.0, n_refunds: 1} |
| 2 | {n_events: 1, total: 30.0, n_refunds: 0} |
| 3 | {n_events: 0, total: NULL, n_refunds: 0} |
ACCOUNT_IDis the accounts to compute.REQ_TSis the end of the window.@eval_as_literal(NOW())is the time the query is compiled.NESTED EVENT_ID := @BIND_KEYSET(EVT)names the events thatRELATED()aggregates over: all the rows of the table.NESTEDmakes them a nested domain of each account rather than one more axis of the cross product, so the result has one row per account, not one per account and event.
Account 3 has no event: COUNT() is 0 and SUM() is NULL, as in SQL.
A prepared statement
In production, a caller sends account IDs and nothing else. The prepared statement binds the rest:
CREATE OR REPLACE FEATURE FM.TUTORIALS.HISTORY.LAST_30D_PS AS
PREPARED_STATEMENT(
FM.TUTORIALS.HISTORY.LAST_30D
USING (FM.TUTORIALS.HISTORY.ACCOUNT_ID, FM.TUTORIALS.HISTORY.REQ_TS := NOW())
);| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.TUTORIALS.HISTORY.LAST_30D_PS | CREATED | Feature created as not exists |
REQ_TS := NOW()binds the end of the window to the time of each call (see call binders ). The caller cannot send it.- The events need no binding. In a prepared statement, the rows a
RELATED()aggregates over are always the whole table. The key in the condition,EVT[account_id] = ACCOUNT_ID, is what limits the read to the caller's accounts.
After REFRESH SERVING, a call carries only input_1, the accounts:
FM.TUTORIALS.HISTORY.LAST_30D_PS{"input_1": [[1], [2], [3]]} | ACCOUNT_ID BIGINT | REQ_TS VARCHAR | LAST_30D_PS ROW |
|---|---|---|
| 1 | * | {n_events: 2, total: 35.0, n_refunds: 1} |
| 2 | * | {n_events: 1, total: 30.0, n_refunds: 0} |
| 3 | * | {n_events: 0, total: NULL, n_refunds: 0} |
REQ_TS comes back with each row: it is the time the server received the call.
Where the aggregation runs
With PostgreSQL, a prepared statement sends the aggregation to the database (a free-form query computes it in the serving engine). For each call, PostgreSQL receives one query with the accounts as a parameter and the 30-day bound already computed from the call's time. In spirit, it looks like this:
SELECT account_id, COUNT(*), SUM(amount), COUNT(*) FILTER (WHERE kind = 'refund')
FROM serving_slt_history_events
WHERE account_id = ANY($1) AND event_ts > $2
GROUP BY account_id Only one row per account crosses the network, and the index on (account_id, event_ts) serves the read. The three aggregates of LAST_30D share their condition, IN_LAST_30D, so they are computed by this one query rather than three.
The source property aggregate_pushdown = FALSE turns this off. The serving engine then reads the account's rows (still by key, with the window filter) and aggregates them itself:
CREATE OR REPLACE FEATURES IN FM.TUTORIALS.HISTORY AS
PERSIST
POSTGRES_ROWS := SOURCE_DB(
'postgresql://featuremesh@host.docker.internal:25432/featuremesh?sslmode=disable'
WITH (
password = SECRET('env', 'FEATUREMESH_DEMOS_PG_PASSWORD'),
tables = ARRAY['serving_slt_history_events'],
timeout = '2s',
aggregate_pushdown = FALSE
)
),
EVT_ROWS := EXTERNAL_COLUMNS(
event_id BIGINT#EVENTS BIND TO EVENT_ID,
account_id BIGINT#ACCOUNTS,
event_ts TIMESTAMP,
amount DOUBLE,
kind VARCHAR
FROM TABLE(FM.TUTORIALS.HISTORY.POSTGRES_ROWS[serving_slt_history_events])
),
IN_LAST_30D_ROWS := EVT_ROWS[account_id] = ACCOUNT_ID AND EVT_ROWS[event_ts] > REQ_TS - INTERVAL '30 days',
LAST_30D_ENGINE := ROW(
RELATED(COUNT() ON IN_LAST_30D_ROWS) AS n_events,
RELATED(SUM(EVT_ROWS[amount]) ON IN_LAST_30D_ROWS) AS total,
RELATED(COUNT_IF(EVT_ROWS[kind] = 'refund') ON IN_LAST_30D_ROWS) AS n_refunds
),
LAST_30D_ENGINE_PS := PREPARED_STATEMENT(LAST_30D_ENGINE USING (ACCOUNT_ID, REQ_TS := NOW()));| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.TUTORIALS.HISTORY.EVT_ROWS | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.IN_LAST_30D_ROWS | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.LAST_30D_ENGINE | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.LAST_30D_ENGINE_PS | CREATED | Feature created as not exists |
| FM.TUTORIALS.HISTORY.POSTGRES_ROWS | CREATED | Feature created as not exists |
The answer is the same:
FM.TUTORIALS.HISTORY.LAST_30D_ENGINE_PS{"input_1": [[1], [2], [3]]} | ACCOUNT_ID BIGINT | REQ_TS VARCHAR | LAST_30D_ENGINE_PS ROW |
|---|---|---|
| 1 | * | {n_events: 2, total: 35.0, n_refunds: 1} |
| 2 | * | {n_events: 1, total: 30.0, n_refunds: 0} |
| 3 | * | {n_events: 0, total: NULL, n_refunds: 0} |
Keep the default unless you measure otherwise. Turn it off when the database is the bottleneck and the engine has CPU to spare, or when a key has few rows and the aggregate is costly for the database to plan.
MySQL and DynamoDB sources are read by key only: their aggregations always run in the serving engine. Redis holds no rows to aggregate, but a sorted set read as a history gives the same feature from a precomputed list.
What a prepared statement may read
A prepared statement serves one call in milliseconds, so every source it reads must be bounded by the call's keys. The prepared statement is refused when it is created if one is not. Here the condition counts the large events of every account, and nothing in it ties an event to the caller's account:
CREATE OR REPLACE FEATURES IN FM.TUTORIALS.HISTORY AS
PERSIST
N_LARGE := RELATED(COUNT() ON EVT[amount] >.2 20.0 AND ACCOUNT_ID IS NOT NULL AND REQ_TS IS NOT NULL),
N_LARGE_PS := PREPARED_STATEMENT(N_LARGE USING (ACCOUNT_ID, REQ_TS := NOW()));The same feature is fine in a free-form query, which may scan. To serve it, add the key to the condition (EVT[account_id] = ACCOUNT_ID AND ...), or precompute the value in batch and serve it from Redis.
Writing the events' binding in the prepared statement is refused too: there is only one possible binding, the whole table, so the statement makes it for you.
CREATE OR REPLACE FEATURE FM.TUTORIALS.HISTORY.LAST_30D_NESTED_PS AS
PREPARED_STATEMENT(
FM.TUTORIALS.HISTORY.LAST_30D
USING (FM.TUTORIALS.HISTORY.ACCOUNT_ID, FM.TUTORIALS.HISTORY.REQ_TS := NOW())
FOR NESTED FM.TUTORIALS.HISTORY.EVENT_ID := @BIND_KEYSET(FM.TUTORIALS.HISTORY.EVT)
);Choosing a storage for history
| Storage | How the window is read | Fits when |
|---|---|---|
| PostgreSQL | One aggregation query per call, by key and time, in the database | The events are already there, and the per-key row counts are moderate |
PostgreSQL with aggregate_pushdown = FALSE | Rows by key and time, aggregated in the engine | The database is the bottleneck |
| MySQL, MariaDB | One aggregation query per call by key, without a time window; with a window, rows by key aggregated in the engine | The events are already there |
| DynamoDB | Rows by key, filtered and aggregated in the engine | The events are already there, and each key has few rows |
| Redis sorted set | The key's members as an array of rows (INDEXED ON), filtered and aggregated in the engine | Latency matters most, and a writer can maintain one sorted set per key, trimmed to the window |