Skip to content
sysgeeks

Design a Product Analytics System, stage 2 of 9: decide

Where the events live

Charts are built by customers, so the questions are not known in advance. They nearly always filter by team and time range, then by event name and some properties.

System so far· 5 parts
123CLIENTCustomers' appsEDGECapture APIDATABASEPostgresSERVICEQuery serviceCLIENTCustomers'dashboards

Select a component to see what it is responsible for and which state it owns.

  1. 1Customers' apps → Capture API: Batches of events
  2. 2Customers' dashboards → Query service: Chart request
  3. 3Query service → Postgres: Teams and saved charts

What you need to know

0 of 2 checks done

  1. A column store keeps each column's values together on disk: all timestamps in one place, all event names in another. A query reads only the columns it uses. See Columnar storage.

    Values in one column are similar (the same few event names, increasing timestamps), so they compress very well, often 5 to 10×.

  2. Column stores also keep data sorted by a chosen key, and record the min and max of each block. A query whose filter matches the sort key can skip whole blocks without reading them.

    With the sort key (team, event, time), "team 42's signups last quarter" reads a thin slice and skips every block from other teams, events and dates.

  3. Check

    Why not precompute daily counts for every event and property instead?