# 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.

Applies to: the Sypnia engine (open source, self-hosted). Status: at launch.  
Source: https://docs.sypnia.com/architecture/aggregation/

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](https://docs.sypnia.com/architecture/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.

> **Note: About the code and the numbers**
>
> The Rust and SQL here are design excerpts; the repository opens at launch. Memory figures are budgets with their arithmetic. Histogram bounds and upsert rules are the ones that run in production in the system this comes from, unchanged.

## One run, for one project and one hour

**Figure (diagram not drawn yet)**: One run of the roll-up. Every segment of the hour is read; what is done with each line depends on whether its segment was already added.

It will show: FLOW DIAGRAM, top to bottom, of one roll-up run, with a header tag «project prj_5k2m9x4q7w1z · production · hour 09». Step nodes in order with exact labels: 1 «Ledger: which segments does hour 09 have, which are new?» (reads from a small «Postgres» cylinder on the left); 2 «Stream each segment from the Bucket» (reads from a «Bucket» node on the left; sub-label «gunzip → lines → Event; one segment in memory at a time»); then the flow SPLITS into three parallel lanes side by side: lane A node «HourFold» with sub-label «every segment of the hour» and under it three small tiles «pages × device», «API routes», «traces index»; lane B node «DeltaFold» with sub-label «only segments not yet added» and under it three tiles «sessions», «daily clicks», «usage»; lane C node «Spill file on local disk» with sub-label «every line, plus an index sid → offsets». The lanes JOIN into step 3 «Per-session files» (arrow from lane C; writes to «Bucket», label «8 uploads at a time, same key = overwrite»); then step 4 «One transaction» drawn as a bracketed box containing three lines: «REPLACE the hour's rows (from HourFold)», «ADD to counters (from DeltaFold)», «mark the new segments as rolled»; arrow from step 4 to «Postgres». The lit element is the bracketed transaction box, with the note «all three, or none». Numbered callouts (1) to (4) on the steps as numbered; lanes labelled (A), (B), (C).

The steps, with the callouts of the figure:

1. **(1) Ask the ledger.** The `segments` table lists the hour's segments and says which have `rolled_at` empty. If none is new and the hour's numbers are current, the run ends here.
2. **(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 `skipped` list, a duplicate, is skipped by every lane.
3. **(A) (B) (C) Fold.** Each event goes to up to three places, described below.
4. **(3) Write per-session files** to the Bucket. They are overwrites, so they can happen before the transaction and be repeated freely.
5. **(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.

`crates/sypnia-pipeline/src/rollup.rs`

```rust
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.

`crates/sypnia-pipeline/src/fold/pages.rs`

```rust
#[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.

**Figure (diagram not drawn yet)**: A histogram is 15 integers. Two of them are merged by adding, and a percentile is read from the array when you ask.

It will show: HISTOGRAM DIAGRAM for LCP. A horizontal row of 15 adjoining cells of equal drawn width. Under the 14 inner boundaries write the bounds in milliseconds, exactly: 50, 100, 200, 300, 500, 750, 1000, 1500, 2000, 3000, 5000, 8000, 12000, 20000. Label the last cell «overflow». Inside the cells draw bars with these exact counts written above them, left to right: 0, 0, 1, 3, 9, 14, 18, 22, 12, 8, 6, 4, 2, 1, 0 (total 100). Above the diagram the title «lcp_hist for /checkout · desktop · one hour = 15 integers». Mark the cell between 1500 and 2000 (count 12) as the lit element, with a vertical marker inside it at about 62 percent of its width and the call-out «p75 = 1,813 ms: the 75th value of 100 falls in this cell». Under the diagram, a second smaller row showing two arrays being added position by position: «hour 09: \[0,0,1,3,9,…]» plus «hour 10: \[0,1,0,4,7,…]» equals «day: \[0,1,1,7,16,…]», with the label «merging is addition». Numbered callouts: (1) on the bounds row, (2) on the lit cell, (3) on the merging row.

| 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.

`crates/sypnia-core/src/histogram.rs`

```rust
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.

`crates/sypnia-db/sql/session_upsert.sql`

```sql
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;         -- sticky
```

Rows 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 `request` and the `server_request` with the same trace id are one row in `traces`, 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.

**Figure (diagram not drawn yet)**: The ledger at 09:20. Rows 3 and 4 are new for the roll-up; marking them and adding their counters happen in the same transaction.

It will show: DIAGRAM IN TWO PANELS side by side. LEFT PANEL titled «The ledger: one row per segment» shows a small table with four columns «key», «scanned_at», «rolled_at», «skipped» and four rows with exact contents: row 1 «…091207Z-2b81e4» · «09:12:31» · «09:15:02» · «—»; row 2 «…091216Z-7f3a9c» · «09:12:31» · «09:15:02» · «—»; row 3 «…091744Z-7f3a9c» · «09:18:01» · «(empty)» · «0b9f6c3e…»; row 4 «…091902Z-2b81e4» · «(empty)» · «(empty)» · «—». Row 3's empty rolled_at cell is highlighted, with the note «new for the roll-up»; row 3's skipped cell has the note «this batch already arrived in row 2: not counted»; row 4's empty scanned_at has the note «new for the fast path». RIGHT PANEL titled «The 09:20 run, one transaction» shows a vertical stack of four steps inside one bracket: «1. UPDATE segments SET rolled_at = now() WHERE key IN (row 3, row 4) AND rolled_at IS NULL → must return 2 rows», «2. DELETE + INSERT the rows of hour 09 (from all four segments)», «3. INSERT … ON CONFLICT DO UPDATE: add the counters of rows 3 and 4 only», «4. COMMIT». To the right of step 1 a small branch arrow labelled «returned fewer? another run got there first → ROLLBACK, nothing written». The lit element is step 1 and its branch. Numbered callouts (1) on the highlighted rolled_at cell, (2) on the skipped cell, (3) on step 1.

**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.

`crates/sypnia-db/src/rollup_commit.rs`

```rust
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.concurrency` runs 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 `hours` row to `running`; another worker skips an hour that has been `running` for 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 `503` with `Retry-After` instead 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
