Skip to content
sysgeeks

Design a Product Analytics System, stage 4 of 9: break it

Slow again, for a different reason

Properties are stored as one JSON string column, because every customer sends different keys. Select the lines that point at the cause.

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 1 checks done

  1. A column store only helps when the data is actually in columns. A single JSON column holding every property is, for those fields, a row store again: to read one property, the query reads and parses the whole blob for every row.

  2. Check

    A query profile shows 98% of blocks skipped by the sort key, but 37 of 38 GB read are the properties column. What's the problem?