Skip to content
sysgeeks

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

Filtering by who did it

Customers filter charts by person properties too: "signups from people on the Pro plan", where plan belongs to the person and changes when they upgrade. People live in Postgres: about 2 billion of them across all teams, keyed by ID, with their current properties.

System so far· 8 parts
12345678CLIENTCustomers' appsEDGECapture APILOG / STREAMEvent streamWORKERIngestionworkersDATABASEEvents(column store)DATABASEPostgresSERVICEQuery 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
  4. 4Query service → Events (column store): Aggregate by column
  5. 5Capture API → Event stream: Append, then acknowledge
  6. 6Ingestion workers → Event stream: Read a partition
  7. 7Ingestion workers → Postgres: Who is this ID?
  8. 8Ingestion workers → Events (column store): Batch insert

What you need to know

0 of 2 checks done

  1. Denormalisation copies data to where it's read, so queries don't have to join. It costs storage and write-time work, and the copy is a snapshot: it records what was true when it was copied.

    In analytics, reads are the expensive part and writes are append-only, so the trade usually pays.

  2. Check

    Why not fetch Pro users' IDs from Postgres and filter events with WHERE person_id IN (…)?