Docs

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 Engine At launch

View as Markdown
On this page
  1. One run, for one project and one hour
  2. Which hours are rolled
  3. Reading objects as a stream
  4. The group-bys
  5. Histograms and percentiles
  6. Session assembly
  7. Exactly once: the ledger
  8. Late and out-of-order events
  9. Memory bounds
  10. Parallelism
  11. Compaction to Parquet
  12. What changed from the system this comes from
  13. Next

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

Diagram to drawFLOW 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).
Figure. 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.

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:

HourRolled when
The current hourIt has a segment with rolled_at empty
The previous hourUntil 10 minutes after it ended, then once more to close it
Any hour of the last 48 hoursIts 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 thatOnly 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
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.

FoldMap keyValueTableWrite
Hour(route, device)views, set of sessions, errors, rage and dead clicks, four histogramsperf_pages_hourlyReplace
Hour(method, route)browser half: requests, ok, rejected, failed, duration histogram; server half: requests, failed, timeouts, database calls and time, duration histogramperf_api_hourlyReplace
Hourtrace idthe request’s summary and where its spans aretracesReplace by key
Deltasidfirst and last ts, person, account, device, counters, routes in ordersessionsAdd
Delta(day, route, device, cell x, cell y)clicksclicks_dailyAdd
Delta(day, route, device, selector)clicks, rage, dead, the most frequent role and labelclick_elements_dailyAdd
Delta(day, route, device, metric, selector)samples, poor, needs improvement, histogramvital_elements_dailyAdd
Delta(day, name)count, peoplecustom_events_dailyAdd
Delta(day, route, field)count, sessionsform_failures_dailyAdd
Deltadaycounted events, all events, sessions with a recordingusage_dailyAdd

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

Diagram to drawHISTOGRAM 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.
Figure. A histogram is 15 integers. Two of them are merged by adding, and a percentile is read from the array when you ask.
Bounds (1)CellsUsed for
50, 100, 200, 300, 500, 750, 1000, 1500, 2000, 3000, 5000, 8000, 12000, 20000 ms15LCP, INP, TTFB, request and server durations
0.01, 0.025, 0.05, 0.1, 0.15, 0.25, 0.4, 0.6, 110CLS

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

Diagram to drawDIAGRAM 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.
Figure. 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.

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
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 wrongWhy nothing is counted twice
The run dies before the commitNothing was written. The next pass does it again.
The run dies after the commitrolled_at is set. The next pass sees nothing new.
Two workers roll the same hour at onceTheir 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 twiceIts 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 processedThe 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.

QuestionAnswer
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 memoryGrows withArithmetic for a busy hour: 60,000 events, 1,000 sessions, 200 routes
One segment being parsedSegment size, capped4 MiB compressed input at most; about 32 MB while parsed
Page and API cellsRoutes × devices600 cells × about 400 bytes = 0.24 MB
Session sets inside the cellsSessions × routes each visited1,000 × 10 × 8 bytes = 0.08 MB
Session rows of the deltaSessions in new segments1,000 × about 1 KB = 1 MB
Click and element cellsDistinct elements clicked5,000 × about 300 bytes = 1.5 MB
The spill indexEvents with a session60,000 × 32 bytes = 1.9 MB
Rows waiting for the transactionThe sum of the aboveabout 3 MB
Totalabout 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.

StepRule
ReadThe day’s segments, in key order, streamed like the roll-up does. Duplicated batches are skipped, so the Parquet file is the deduplicated day.
SchemaFixed 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.
WriteTo a local file in row groups of 50,000 rows, then one upload. Never a streaming upload: same lesson as the session files.
IdempotentThe object is overwritten. Running the day again gives the same file.
AfterThe 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_nameTyped Event with extra for unknown fields; a corrupt line is skipped
Identity backfill with two window functionsSort by (sid, ts), one pass forward, one backward
count(DISTINCT sid)A set of 64-bit hashes per cell
arg_max, mode with arbitrary tiesThe same folds with a defined tie-break
Partitioned COPY to local disk, then uploadA spill file and an index, then upload
Replace per hour; add guarded by one run row per hourReplace per hour; add guarded by one ledger row per segment
1,200 MB ceiling, a 68 MB native binding, x86_64 only256 MB budget, no native dependency, any architecture
Ad hoc SQL over raw events came for freeIt 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

Sections

Start hereWhat Sypnia is, how it works, what is open source and what is the paid cloud, and the two ways to get your first problem in plain words.ConceptsThe ten ideas Sypnia is made of, each defined precisely, from a project and its keys to problems, traces and alerts.SDKsHow your app sends data to Sypnia. One SDK for the browser, one for Node, one for Next.js, and the OpenTelemetry intake for every other backend.The agentHow Sypnia analyses a problem, what it writes, what it sends to a model and what it never does.MCP and skillsConnect your coding agent to Sypnia so it can ask what is failing in production, read what it needs to fix it, and set Sypnia up for you.API referenceThe Sypnia HTTP API: one API for the open-source engine and for Sypnia Cloud, with every endpoint, parameter, response and error.CLI referenceThe sypnia command: how to install it, the variables it reads, what its exit codes mean, and where each command is documented.Self-hostingWhat you run when you host the Sypnia engine yourself, what you need, what it costs, and what you do not get compared with Sypnia Cloud.How the engine is builtThe whole Sypnia engine on one page, from a click in your app to the sentence your coding agent reads, with the design rules and the code that holds them.Sypnia CloudHow the paid service works, from organizations and roles to plans, limits, billing, where your data lives, the GitHub App and support.Privacy and securityWhat Sypnia collects from your app and its users, what it never collects, how masking works, how to delete data, and how the service is protected.LicensingWhich licence covers each part of Sypnia, what you may do with it, how the name may be used, and the terms of a contribution.GuidesStep-by-step recipes for the common ways to use Sypnia, from apps made with AI builders and Next.js on Vercel to Supabase, source maps in CI, alert e-mails from your own engine, and moving from Sentry.ReferenceThe pages you look things up in, from the event schema and the glossary to what is planned, the changelog and the common questions.
↑↓ to move↵ to openEsc to close