PEEQ

The problem

The original system wrote every ping straight to the primary database. At scale, dashboards timed out and history queries locked the write path.

Business challenge

Ingest tens of thousands of events per minute while keeping live maps under a second and history queries interactive.

Research

Replayed two months of production traffic to find the real burst shape - it was not uniform, it was shift-change spikes.

Planning

Split ingest from read entirely: a thin write path, a stream, and purpose-built read stores for live and historical views.

Architecture

• Stateless ingest service behind a load balancer, writing to a stream
• Redis for last-known-position and geofence evaluation
• Time-partitioned PostgreSQL for history with rollup tables
• WebSocket fan-out layer decoupled from ingest

Database design

PostgreSQL with monthly partitions on the event table, plus 1-minute and 1-hour rollups that power every chart.

API flow

1. 1 Devices post batched pings over a compact payload
2. 2 Ingest validates, deduplicates, and appends to the stream
3. 3 Workers update Redis live state and evaluate geofences
4. 4 Rollup workers write aggregates; dashboards read only aggregates

Implementation

• Dual-wrote to old and new pipelines for three weeks before cutover
• Introduced backpressure and dead-letter handling before launch
• Load-tested at 5x projected peak as a release gate

Key features

• Live map with sub-second position updates
• Geofence rules with instant alerting
• Historical playback and trip reconstruction
• Fleet-level utilisation reporting

Performance

• Zero dropped events across a 5x peak load test
• Database write load reduced 70% by batching and partitioning

Lessons learned

• Never let the read path share a table with a hot write path.
• Rollups decided up front are cheaper than indexes added later.