Aggregation
How the roll-up turns an hour of raw events into numbers without a SQL engine, with its group-bys, histograms, session assembly, the ledger that makes counters exactly-once, its memory bounds and the daily Parquet file.
The roll-up is the part of the engine that counts. Every 5 minutes it reads the raw events of the open hour and writes page speed, API speed, clicks, the session index, the traces index and usage. It does not touch problems: those belong to the fast path, described in Problem grouping.
The system this engine comes from did this job with an embedded SQL engine, once an hour. Its own audit counted what the SQL was: about 20 statements and 270 lines, all of them single-pass group-bys over one hour of rows, none joining across hours. That is a loop and some hash maps. The Sypnia engine writes it as exactly that.
One run, for one project and one hour
The steps, with the callouts of the figure:
- (1) Ask the ledger. The
segmentstable lists the hour’s segments and says which haverolled_atempty. If none is new and the hour’s numbers are current, the run ends here. - (2) Stream. Each segment is fetched, decompressed and parsed line by line. A line that does not parse is counted and skipped. A line whose batch is in the segment’s
skippedlist, a duplicate, is skipped by every lane. - (A) (B) (C) Fold. Each event goes to up to three places, described below.
- (3) Write per-session files to the Bucket. They are overwrites, so they can happen before the transaction and be repeated freely.
- (4) Commit. One transaction replaces the hour’s rows, adds to the counters and marks the segments. If anything fails, nothing is written and the next pass does the whole run again.
Which hours are rolled
On every pass that is due, for every project and environment:
| Hour | Rolled when |
|---|---|
| The current hour | It has a segment with rolled_at empty |
| The previous hour | Until 10 minutes after it ended, then once more to close it |
| Any hour of the last 48 hours | Its row in hours is failed or running for more than 20 minutes, or the ledger has a segment of it not yet rolled; oldest first |
| Older than that | Only by hand: sypnia aggregate --from … --to … |
The 48-hour window and the 20-minute rule for a stuck run are kept from the system this comes from, where they recovered every hour that failed.
Reading objects as a stream
A segment is at most 4 MiB before compression, so it is read whole, then walked line by line. Events are never collected in a list: each one is folded and forgotten. What stays in memory grows with the number of different things in the hour (routes, sessions, elements), not with the number of events.
Notice the three outcomes of a line: a typed event, an event of an unknown type that is kept for the files but skipped by the folds, and garbage that is counted.
async fn fold_segment(
engine: &Engine,
segment: &SegmentRow,
hour: &mut HourFold,
delta: Option<&mut DeltaFold>, // None when this segment was already added to the counters
spill: &mut Spill,
) -> Result<SegmentStats, RollupError> {
let mut stats = SegmentStats::default();
let mut delta = delta;
let mut lines = engine.store.read_lines(&segment.key).await?;
while let Some(line) = lines.try_next().await? {
let record: Record = match serde_json::from_slice(&line) {
Ok(record) => record,
Err(_) => { stats.unreadable += 1; continue; }
};
if segment.skipped.contains(&record.bid) {
stats.duplicates += 1; // the same batch arrived before, in another segment
continue;
}
hour.push(&record.event); // (A) numbers of the hour
if let Some(delta) = delta.as_deref_mut() {
delta.push(&record.event); // (B) counters that only ever add
}
if let Some(sid) = record.event.session_id() {
spill.append(sid, record.event.ts(), &line)?; // (C) the raw line, for the session file
}
stats.events += 1;
}
Ok(stats)
}The group-bys
Each table of numbers is one hash map: the key is the table’s primary key without the project and the hour, the value is a small struct of counters.
| Fold | Map key | Value | Table | Write |
|---|---|---|---|---|
| Hour | (route, device) | views, set of sessions, errors, rage and dead clicks, four histograms | perf_pages_hourly | Replace |
| Hour | (method, route) | browser half: requests, ok, rejected, failed, duration histogram; server half: requests, failed, timeouts, database calls and time, duration histogram | perf_api_hourly | Replace |
| Hour | trace id | the request’s summary and where its spans are | traces | Replace by key |
| Delta | sid | first and last ts, person, account, device, counters, routes in order | sessions | Add |
| Delta | (day, route, device, cell x, cell y) | clicks | clicks_daily | Add |
| Delta | (day, route, device, selector) | clicks, rage, dead, the most frequent role and label | click_elements_daily | Add |
| Delta | (day, route, device, metric, selector) | samples, poor, needs improvement, histogram | vital_elements_daily | Add |
| Delta | (day, name) | count, people | custom_events_daily | Add |
| Delta | (day, route, field) | count, sessions | form_failures_daily | Add |
| Delta | day | counted events, all events, sessions with a recording | usage_daily | Add |
Replace tables are computed from every segment of the hour, every run. That is why a count of distinct sessions in an hour can be exact: the set is rebuilt whole each time. Add tables are computed from new segments only, and added to what is stored.
This is the page fold. Notice that a session is counted with a set, so ten page views by one person are one session, and that a vital goes into a histogram instead of being kept.
#[derive(Default)]
pub struct PageCell {
pub views: u32,
pub sessions: HashSet<u64>, // 64-bit hash of the sid: 8 bytes a session, not 32
pub errors: u32,
pub rage_clicks: u32,
pub dead_clicks: u32,
pub lcp: Histogram<15>,
pub inp: Histogram<15>,
pub ttfb: Histogram<15>,
pub cls: Histogram<10>,
}
#[derive(Default)]
pub struct PageFold {
cells: HashMap<(Route, Device), PageCell>,
}
impl PageFold {
pub fn push(&mut self, event: &Event) {
match event {
Event::PageView(e) => {
let cell = self.cell(e);
cell.views += 1;
cell.sessions.insert(e.sid.hash64());
}
Event::Vital(e) => {
let cell = self.cell(e);
match e.body.name {
VitalName::Lcp => cell.lcp.record(e.body.value),
VitalName::Inp => cell.inp.record(e.body.value),
VitalName::Ttfb => cell.ttfb.record(e.body.value),
VitalName::Cls => cell.cls.record(e.body.value),
VitalName::Fcp => {} // collected, shown in the session, not aggregated
}
}
Event::Error(e) => self.cell(e).errors += 1,
Event::RageClick(e) => self.cell(e).rage_clicks += 1,
Event::DeadClick(e) => self.cell(e).dead_clicks += 1,
_ => {}
}
}
fn cell<T>(&mut self, e: &Browser<T>) -> &mut PageCell {
let route = e.route.clone().unwrap_or_else(Route::unknown);
let device = e.dev.unwrap_or(Device::Desktop);
self.cells.entry((route, device)).or_default()
}
}Two things the SQL engine did by accident are now rules. arg_max and mode broke ties arbitrarily there; here a tie goes to the earliest ts, then to the smaller string. And a label for a clicked element is kept only when one text shape covers more than half of its clicks and the element is not a table row or a link with an id in it, the rule that keeps people’s names out of click maps.
Histograms and percentiles
The engine never stores a duration to compute a percentile later. It stores how many values fell in each of a fixed set of cells.
| Bounds (1) | Cells | Used for |
|---|---|---|
50, 100, 200, 300, 500, 750, 1000, 1500, 2000, 3000, 5000, 8000, 12000, 20000 ms | 15 | LCP, INP, TTFB, request and server durations |
0.01, 0.025, 0.05, 0.1, 0.15, 0.25, 0.4, 0.6, 1 | 10 | CLS |
These bounds are a contract. They are the same in the system this comes from, and changing them would make stored arrays unreadable. A value goes to the cell whose index is the number of bounds strictly below it, so a value equal to a bound falls in that bound’s cell. The last cell is the overflow.
Notice in the excerpt that percentile returns None for an empty histogram, never 0, and that it interpolates inside the cell (2) instead of answering with a bound.
pub const LATENCY_MS: &[f64] =
&[50.0, 100.0, 200.0, 300.0, 500.0, 750.0, 1000.0, 1500.0, 2000.0, 3000.0, 5000.0, 8000.0, 12000.0, 20000.0];
#[derive(Debug, Clone, PartialEq)]
pub struct Histogram<const N: usize> {
bounds: &'static [f64], // N - 1 of them
cells: [u32; N],
}
impl<const N: usize> Histogram<N> {
pub fn record(&mut self, value: f64) {
// The number of bounds strictly below the value. NaN and negatives are dropped at ingest.
let cell = self.bounds.partition_point(|bound| *bound < value);
self.cells[cell] = self.cells[cell].saturating_add(1);
}
/// Merging two hours, or a stored row and a new one: position by position.
pub fn merge(&mut self, other: &Self) {
for (mine, theirs) in self.cells.iter_mut().zip(other.cells) {
*mine = mine.saturating_add(theirs);
}
}
/// `p` in 0..=1. Linear inside the cell that holds the target; the overflow cell answers its lower bound.
pub fn percentile(&self, p: f64) -> Option<f64> {
let total: u64 = self.cells.iter().map(|c| u64::from(*c)).sum();
if total == 0 {
return None;
}
let target = ((total as f64 * p).ceil() as u64).max(1);
let mut before = 0u64;
for (i, count) in self.cells.iter().map(|c| u64::from(*c)).enumerate() {
if before + count >= target {
let lower = if i == 0 { 0.0 } else { self.bounds[i - 1] };
let Some(upper) = self.bounds.get(i) else { return Some(lower) };
let inside = ((target - before) as f64 - 0.5) / count as f64;
return Some(lower + (upper - lower) * inside.clamp(0.0, 1.0));
}
before += count;
}
None
}
}In the figure, 75 of 100 values are needed. The cells up to 1,500 ms hold 67. The next cell holds 12, so the target is its 8th value: 1,500 + 500 × (8 − 0.5) ÷ 12 = 1,813 ms. The same function reads a p75 for one hour or for 30 days: the arrays are added first (3), then read once.
Session assembly
A session is put together from events that arrive in many batches, from the browser and from the server, over minutes or hours. The roll-up does three things with them.
1. It fills in who the session was. The first page view of a visit is often sent before the app knows the person. Within one session, an event with no person takes the person of the next identified event, or else of the previous one. In SQL this was two window functions. Here it is the events of one sid sorted by ts, one pass forward and one backward.
2. It writes the session file. Lane (C) of the first figure. Every line with a sid is appended to a spill file on local disk, and an index remembers where each session’s lines are. At the end, for each session touched by a new segment, its lines are read back, ordered by ts, and written as sessions/<sid>/hr=<day>-<hour>.ndjson.gz, eight uploads at a time.
The spill file exists because of an incident in the system this comes from: writing per-session files straight to the bucket kept one upload buffer of about 76 MiB open per session, and an hour with 92 sessions ran the job out of memory. The rule learned was «write to local disk, upload after». The index costs 32 bytes an event; the lines themselves are on disk.
3. It updates the index row. One row per session in sessions, widened by each run. This is the «add» half: counters add, the first and last times widen, identity is filled in when it appears, and a replay, once seen, stays.
INSERT INTO sessions AS s
(project_id, sid, environment, started_at, ended_at, person_id, account_id, device_id,
release, device, browser, os, locale, entry_route, routes,
page_views, clicks, rage_clicks, dead_clicks, errors, requests, failed_requests, has_replay)
SELECT $1, * FROM UNNEST($2::text[], $3::text[], $4::timestamptz[], $5::timestamptz[], $6::text[], $7::text[],
$8::text[], $9::text[], $10::text[], $11::text[], $12::text[], $13::text[], $14::text[], $15::jsonb[],
$16::int[], $17::int[], $18::int[], $19::int[], $20::int[], $21::int[], $22::int[], $23::bool[])
AS t(sid, environment, started_at, ended_at, person_id, account_id, device_id, release, device, browser,
os, locale, entry_route, routes, page_views, clicks, rage_clicks, dead_clicks, errors, requests,
failed_requests, has_replay)
ON CONFLICT (project_id, sid) DO UPDATE SET
started_at = LEAST(s.started_at, EXCLUDED.started_at),
ended_at = GREATEST(s.ended_at, EXCLUDED.ended_at),
person_id = COALESCE(EXCLUDED.person_id, s.person_id), -- a new non-null value wins
account_id = COALESCE(EXCLUDED.account_id, s.account_id),
device = COALESCE(s.device, EXCLUDED.device), -- the first one seen stays
browser = COALESCE(s.browser, EXCLUDED.browser),
release = COALESCE(EXCLUDED.release, s.release), -- the newest
routes = merge_routes(s.routes, EXCLUDED.routes, 50), -- first-seen order, at most 50
page_views = s.page_views + EXCLUDED.page_views,
clicks = s.clicks + EXCLUDED.clicks,
rage_clicks = s.rage_clicks + EXCLUDED.rage_clicks,
dead_clicks = s.dead_clicks + EXCLUDED.dead_clicks,
errors = s.errors + EXCLUDED.errors,
requests = s.requests + EXCLUDED.requests,
failed_requests = s.failed_requests + EXCLUDED.failed_requests,
has_replay = s.has_replay OR EXCLUDED.has_replay; -- stickyRows travel as arrays through UNNEST, 2,000 rows a statement, as in the system this comes from. A session that only the server saw updates an existing row and never creates one: a project that sends only server data has traces and no sessions.
Two more rules of pairing are applied while folding, both kept:
- A partial server record, written while a request was still running, is dropped when the full record of the same trace is in the hour. If the full record never comes, the partial one counts as a timeout.
- A browser
requestand theserver_requestwith the same trace id are one row intraces, with both durations.
Exactly once: the ledger
Counters that add are dangerous. Add the same segment twice and a session has 40 clicks instead of 20. Functions are retried, runs overlap, processes die halfway. The system this comes from had the incident: a run failed after writing some tables, the retry doubled them.
The answer has two parts, and both are in one transaction.
The ledger says what was added. Each segment has one row in segments. rolled_at (1) is set in the same transaction that adds the segment’s counters. If the transaction commits, both happened. If it does not, neither did.
The claim detects a race. The first statement of the transaction (3) marks the segments it is about to add and checks how many rows it marked. If another run marked one of them first, the count is short, and this run rolls back without writing anything. Notice that there is no lock to forget and no timeout to tune: the row itself is the lock.
pub async fn commit_hour(
pool: &PgPool,
scope: &Scope,
hour: DateTime<Utc>,
hourly: &HourlyRows, // from every segment of the hour: replaces
delta: &DeltaRows, // from the new segments only: adds
new_segments: &[String],
) -> Result<CommitOutcome, DbError> {
let mut tx = pool.begin().await?;
// The claim. Marking and adding share this transaction, so they cannot come apart.
let claimed: Vec<String> = sqlx::query_scalar(
"UPDATE segments SET rolled_at = now()
WHERE project_id = $1 AND key = ANY($2) AND rolled_at IS NULL
RETURNING key",
)
.bind(scope.project.as_str())
.bind(new_segments)
.fetch_all(&mut *tx)
.await?;
if claimed.len() != new_segments.len() {
return Ok(CommitOutcome::LostRace); // dropping `tx` rolls back; the other run's work stands
}
replace_pages(&mut tx, scope, hour, &hourly.pages).await?; // DELETE the hour, INSERT … UNNEST
replace_api(&mut tx, scope, hour, &hourly.api).await?;
upsert_traces(&mut tx, scope, &hourly.traces).await?;
upsert_sessions(&mut tx, scope, &delta.sessions).await?; // counters add, dates widen
add_daily(&mut tx, scope, &delta.daily).await?;
add_usage(&mut tx, scope, &delta.usage).await?;
mark_hour(&mut tx, scope, hour, HourStatus::Open, hourly.events).await?;
tx.commit().await?;
Ok(CommitOutcome::Committed)
}Duplicated batches are the third case, and the ledger covers it too (2). When the fast path scans a segment it inserts the segment’s batch ids into ingest_batches with ON CONFLICT DO NOTHING. The ids that were not inserted already belong to an earlier segment; they are written to this segment’s skipped column. From then on every reader, the fast path, the roll-up and the Parquet writer, skips those lines. The fast path always scans a segment before the roll-up adds it: a run that finds a segment not yet scanned scans it first.
| What can go wrong | Why nothing is counted twice |
|---|---|
| The run dies before the commit | Nothing was written. The next pass does it again. |
| The run dies after the commit | rolled_at is set. The next pass sees nothing new. |
| Two workers roll the same hour at once | Their hourly rows are the same, so replacing twice is harmless; for the counters, one claim wins and the other rolls back. |
| The SDK sent a batch twice | Its second copy is in a skipped list. |
| The queue delivered a message twice (AWS) | Two segments carry the same batch id; the second is skipped. |
Someone runs sypnia aggregate --from … --to … over a day already processed | The hourly tables are replaced with the same values. The counters are not touched, because every segment has rolled_at. |
Late and out-of-order events
Three clocks are involved: the event’s own ts, from a device that may be wrong; rcv, when ingest got it; and the hour of the segment, when it was written.
| Question | Answer |
|---|---|
| Which hour’s numbers does an event count in? | The hour its segment was written. A page view sent at 09:59:58 and written at 10:00:01 counts in hour 10. This is the rule of the system this comes from, and it is what makes an hour closeable: nothing can arrive in the past. |
| Which day’s counters? | The UTC day of that same hour. |
| Where does it appear in a session’s timeline? | At its ts. A session file is ordered by ts, whatever order the batches arrived in. |
| An event that arrives a day late, from a phone that was offline? | Accepted up to 7 days old. It counts in the hour it arrives, it widens its session’s times, and it is written into that session’s file for the arrival hour. The session’s timeline is still in order, because readers merge a session’s files by ts. |
| A device whose clock is wrong? | The batch carries sent_at. When it differs from rcv by more than 2 minutes, the roll-up shifts the batch’s ts values by the difference before using them for ordering and for the session’s times. The stored line keeps the original ts. |
| Events of one batch in the wrong order? | Order inside a batch does not matter. Nothing in the roll-up depends on it. |
The cost of counting by arrival is honest to state: an hour’s chart can be a few seconds off at its edges, and a burst that arrives late shows when it arrived. For numbers by hour and day, that is the right trade against never being able to close an hour.
Memory bounds
One project is rolled inside a budget of 256 MB. processing.concurrency (default 2) projects are rolled at the same time, so a worker needs about 512 MB for the roll-up, whatever the number of projects.
| What holds memory | Grows with | Arithmetic for a busy hour: 60,000 events, 1,000 sessions, 200 routes |
|---|---|---|
| One segment being parsed | Segment size, capped | 4 MiB compressed input at most; about 32 MB while parsed |
| Page and API cells | Routes × devices | 600 cells × about 400 bytes = 0.24 MB |
| Session sets inside the cells | Sessions × routes each visited | 1,000 × 10 × 8 bytes = 0.08 MB |
| Session rows of the delta | Sessions in new segments | 1,000 × about 1 KB = 1 MB |
| Click and element cells | Distinct elements clicked | 5,000 × about 300 bytes = 1.5 MB |
| The spill index | Events with a session | 60,000 × 32 bytes = 1.9 MB |
| Rows waiting for the transaction | The sum of the above | about 3 MB |
| Total | about 40 MB, most of it the one segment in flight |
That busy hour is ten times the peak hour written down for the system this comes from, where the SQL engine was given a ceiling of 1,200 MB for the same job. At a hundred times, 600,000 events and 10,000 sessions, the same arithmetic gives about 110 MB. The spill file on disk is the hour’s lines uncompressed: about 60 MB for the busy hour.
Nothing here grows with the number of events except the spill index and the disk file. If a project does pass its budget, the run stops, the hour is marked failed with the reason, and after 30 minutes the engine.failing alert says which project. The fold never takes memory from another project’s run.
Parallelism
- Between projects: up to
processing.concurrencyruns at once, each on its own connection. Projects are independent: no run reads another project’s rows or objects. - Inside a run: segments are fetched four at a time and folded in key order, so results do not depend on timing. Per-session files are uploaded eight at a time.
- Between workers: safe by construction. Two workers that pick the same hour produce the same hourly rows, and only one claim wins. To avoid the wasted work, a run first sets the
hoursrow torunning; another worker skips an hour that has beenrunningfor less than 20 minutes. - The database: a worker holds one connection per run and one for the fast path. Rule kept from the system this comes from, which once exhausted its pooler: long jobs use few session connections; request-serving code uses a small bounded pool and answers
503withRetry-Afterinstead of queueing.
Compaction to Parquet
Once a day, at 02:30 UTC, each project’s previous day becomes one Parquet file per environment: parquet/<environment>/dt=<day>/events.parquet, compressed with ZSTD.
| Step | Rule |
|---|---|
| Read | The day’s segments, in key order, streamed like the roll-up does. Duplicated batches are skipped, so the Parquet file is the deduplicated day. |
| Schema | Fixed by the engine’s version: one typed column per field of the event contract, nested lists for spans, and extra, a JSON string with every unknown field. The system this comes from let the schema be guessed from each day’s data, so it changed from day to day; here it does not. |
| Write | To a local file in row groups of 50,000 rows, then one upload. Never a streaming upload: same lesson as the session files. |
| Idempotent | The object is overwritten. Running the day again gives the same file. |
| After | The segments stay until their own retention ends. Parquet is an addition, not a replacement: the engine itself only ever reads segments. |
The file is yours. Any tool that reads Parquet from a bucket can query it, and the engine never needs it to answer a request.
What changed from the system this comes from
| There (embedded SQL engine, hourly) | Here (plain Rust, every 5 minutes) |
|---|---|
Schema guessed from the hour’s JSON, union_by_name | Typed Event with extra for unknown fields; a corrupt line is skipped |
| Identity backfill with two window functions | Sort by (sid, ts), one pass forward, one backward |
count(DISTINCT sid) | A set of 64-bit hashes per cell |
arg_max, mode with arbitrary ties | The same folds with a defined tie-break |
Partitioned COPY to local disk, then upload | A spill file and an index, then upload |
| Replace per hour; add guarded by one run row per hour | Replace per hour; add guarded by one ledger row per segment |
| 1,200 MB ceiling, a 68 MB native binding, x86_64 only | 256 MB budget, no native dependency, any architecture |
| Ad hoc SQL over raw events came for free | It does not. Use the Parquet files with your own tool |
The last row is the honest cost of the change. An embedded SQL engine let a person query raw events on the spot. The engine gives that up to stay one small binary, and gives you Parquet instead.
Next
- The other reader of the same segments: the fast path.
- The tables these folds fill.
- How a recorded hour becomes a test of every fold.