database-design-database-architect

“I'm building a fleet-tracking SaaS. About 50k vehicles each send GPS and engine telemetry every 5 seconds. Customers need live maps, 90-day historical playback, and monthly trip and fuel reports. We also need relational data for accounts, drivers, vehicles, and billing. Which database technologies would you pick for each part, and how would you model the main tables or collections, partition the telemetry, and handle retention? Our team is small and already knows Postgres.”

“Our Rails monolith has a single Postgres 12 database with an orders table of about 400M rows. It's slow, and the table mixes order headers, line items stored as JSONB, and status history in an array column. We want to normalize it without downtime. Can you propose a target schema, indexing and partitioning strategy, and a phased migration plan (dual writes, backfill, cutover, rollback) that keeps the app online?”