I'm building a driver-monitoring system for roughly 150–200 buses. It generates alert events, and a Svelte supervision dashboard may need to analyze around 360,000 records in a typical case, with a possible upper limit near one million.
The current design loads events from Firestore into Redis and then calculates dashboard metrics in Python. Each cached record is about 798 bytes, which means roughly 287 MB for 360,000 events or 798 MB for one million.
I'm concerned that storing all raw events in Redis and repeatedly recalculating metrics may not scale well. Firestore provides server-side count, sum, and average aggregations, but the dashboard also needs grouped results, rankings, time windows, multiple alert types, driver statistics, and other custom calculations.
How would you normally structure this system? Should I use precomputed rollups, a relational or time-series database, Firestore queries, Redis caching, or some combination?
4 Answers
A million rows is not especially large for a normal SQL database, so I’d avoid overengineering this. A relational database with good indexes and a few materialized or summary views would likely handle the workload well. Firestore is convenient for document storage, but analytical queries involving grouping, ranking, and multiple dimensions are generally a better fit for SQL.
TimescaleDB is worth considering if the events are strongly time-based. It builds on PostgreSQL and supports time-series partitioning, retention policies, and incremental rollups. You can retain the raw events for audits while having the database maintain hourly or daily aggregates for the dashboard.
Redis is a lot of overhead for nearly a gigabyte of raw data that you recalculate on every request. Keep the raw events in Firestore or another durable store for auditing, but precompute the dashboard aggregates you actually need and have the dashboard query those much smaller results. If the time windows and grouping become more complex, a time-series database could make slicing by driver, bus, alert type, and time period much easier than rebuilding everything in Python.
Make the rollups incremental instead of scanning the entire event history for every dashboard request. For example, upsert hourly buckets keyed by time range, bus, driver, and alert type. Keep the aggregation process idempotent so late or duplicated events can be replayed safely, and use Redis only as a short-lived cache for expensive dashboard responses—not as the primary analytics store.
That also gives you a clean path for drill-downs: load the summary buckets for normal views, then query the raw events only when someone opens a detailed time range or investigates a specific alert.