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:

Postgres
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;
Result
ACCOUNT_ID BIGINTEVENT_ID BIGINTAGE_DAYS BIGINTAMOUNT DOUBLEKIND VARCHAR
11110.0purchase
12325.0refund
134020.0purchase
24230.0purchase

The features

FeatureQL
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
    );
Result
feature_name VARCHARstatus VARCHARmessage VARCHAR
FM.TUTORIALS.HISTORY.ACCOUNTSCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.ACCOUNT_IDCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.EVENTSCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.EVENT_IDCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.EVTCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.IN_LAST_30DCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.LAST_30DCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.POSTGRES_CONNCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.REQ_TSCREATEDFeature created as not exists

Read it from the bottom up:

  • LAST_30D is a row of three aggregates. Each is RELATED(aggregate ON condition): the aggregate over the events for which the condition holds, computed for each account.
  • IN_LAST_30D is that condition, written once and shared: the event belongs to the account, and it happened in the 30 days before REQ_TS.
  • EVT maps the table's columns. The table is keyed by event_id (BIND TO EVENT_ID); account_id is just a column, annotated #ACCOUNTS so that FeatureQL knows it holds an account key.
  • REQ_TS is an input like any other: the time the window ends at. Making it an input, rather than calling NOW() inside the feature, keeps the feature deterministic: the same REQ_TS gives 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:

FeatureQL
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);
Result
ACCOUNT_ID BIGINTLAST_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_ID is the accounts to compute.
  • REQ_TS is 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 that RELATED() aggregates over: all the rows of the table. NESTED makes 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:

FeatureQL
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())
);
Result
feature_name VARCHARstatus VARCHARmessage VARCHAR
FM.TUTORIALS.HISTORY.LAST_30D_PSCREATEDFeature 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:

Prepared
FM.TUTORIALS.HISTORY.LAST_30D_PS
Call 1
{"input_1": [[1], [2], [3]]}
ACCOUNT_ID BIGINTREQ_TS VARCHARLAST_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
sql

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:

FeatureQL
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()));
Result
feature_name VARCHARstatus VARCHARmessage VARCHAR
FM.TUTORIALS.HISTORY.EVT_ROWSCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.IN_LAST_30D_ROWSCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.LAST_30D_ENGINECREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.LAST_30D_ENGINE_PSCREATEDFeature created as not exists
FM.TUTORIALS.HISTORY.POSTGRES_ROWSCREATEDFeature created as not exists

The answer is the same:

Prepared
FM.TUTORIALS.HISTORY.LAST_30D_ENGINE_PS
Call 1
{"input_1": [[1], [2], [3]]}
ACCOUNT_ID BIGINTREQ_TS VARCHARLAST_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:

FeatureQL
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()));
Result
ERROR UE/SERVING-NOT-A-KEY-LOOKUP

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.

FeatureQL
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)
);
Result
ERROR UE/PREPARED-STATEMENT-NESTED-IMPLICIT

Choosing a storage for history

StorageHow the window is readFits when
PostgreSQLOne aggregation query per call, by key and time, in the databaseThe events are already there, and the per-key row counts are moderate
PostgreSQL with aggregate_pushdown = FALSERows by key and time, aggregated in the engineThe database is the bottleneck
MySQL, MariaDBOne aggregation query per call by key, without a time window; with a window, rows by key aggregated in the engineThe events are already there
DynamoDBRows by key, filtered and aggregated in the engineThe events are already there, and each key has few rows
Redis sorted setThe key's members as an array of rows (INDEXED ON), filtered and aggregated in the engineLatency matters most, and a writer can maintain one sorted set per key, trimmed to the window