SLIDE 96: Section Intro, Database Design
Section 8: Database Design
A ride-sharing platform does not have one database. It has several, each chosen for a specific data type and access pattern. User profiles live in PostgreSQL. Real-time driver positions live in Redis. Location history lives in a time-series database. Payment transactions live in their own PostgreSQL instance with ACID guarantees. The architectural question is not “which database should we use?” It is “which database should we use for each data type, and why?”
This section makes that decision for every data type in the system, defines schemas with concrete column names and indexes, and explains the sharding strategy that makes geographic partitioning work.
What We’ll Cover:
- Database-per-service pattern and why shared databases fail
- Technology selection per data type: PostgreSQL, Redis, time-series DB, Elasticsearch
- Schemas, indexes, and query patterns for users, trips, payments, and locations
- Geographic sharding: why ride-sharing shards by city, not by user
- Denormalization trade-offs and the full entity-relationship model
This section connects backward to Section 5 (Data Flow), where we defined which services own which data, and forward to Section 11 (Scaling), where we scale these databases across regions.
SLIDE 97: Database-Per-Service Pattern
Each Service Owns Its Database
No service directly queries another service’s database. Trip Service owns trip records. Location Service owns positions. Payment Service owns transactions. If Matching Service needs trip data, it calls Trip Service’s API or consumes its Kafka events. It never runs SQL against Trip Service’s database.
Why Not a Shared Database?
A monolithic database shared by all services creates three problems.
First, coupling. A schema change to the trips table requires coordinating every service that reads from it. In a shared database, that might be five or six services. In database-per-service, only Trip Service is affected.
Second, contention. All services compete for database connections, CPU, and I/O. A slow analytics query from the reporting dashboard degrades ride matching performance because they share the same database. With separate databases, a slow analytics query has zero impact on matching.
Third, scaling limitations. You cannot scale the database for one service’s workload without over-provisioning for all others. Location Service needs extreme write throughput (1.25M writes/sec). Payment Service needs strong consistency with moderate throughput. These are fundamentally different hardware profiles.
How Services Communicate Without Shared Databases
Synchronous: service-to-service API calls when an immediate response is needed. Trip Service calls Pricing Service for fare calculation.
Asynchronous: Kafka events when no immediate response is needed. Trip Service emits “trip_completed,” Payment Service consumes it and processes the charge.
Cached copies: a service maintains a local read-only copy of another service’s data, refreshed via events. Matching Service caches driver ratings locally rather than calling User Service on every match.
SLIDE 98: Choosing the Right Database Per Data Type
Technology Selection Is Driven by Access Patterns
The choice of database for each data type is not a preference. It is a consequence of the data’s volume, access pattern, and consistency requirements.
The Key Insight
Most of the data in a ride-sharing system is relational. Users have profiles. Trips have riders, drivers, and payments. Drivers have vehicles. These relationships are well-served by PostgreSQL. The exceptions are targeted and specific: real-time positions need in-memory speed (Redis), and location history needs extreme write throughput (time-series DB). A common interview mistake is reaching for NoSQL for everything. The stronger answer is PostgreSQL as the default, with surgical use of Redis and time-series storage where the access pattern demands it.
SLIDE 99: Users Database, PostgreSQL Schema
Riders, Drivers, and Vehicles
CREATE TABLE riders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT UNIQUE NOT NULL,
payment_methods JSONB DEFAULT '[]',
home_location GEOGRAPHY(Point, 4326),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE drivers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT UNIQUE NOT NULL,
license_number TEXT NOT NULL,
status TEXT DEFAULT 'offline',
rating NUMERIC(3,2) DEFAULT 5.00,
acceptance_rate NUMERIC(5,2) DEFAULT 100.00,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE vehicles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
driver_id UUID REFERENCES drivers(id),
type TEXT NOT NULL,
make TEXT,
model TEXT,
plate TEXT NOT NULL,
capacity INT DEFAULT 4
);
Indexes Driven by Query Patterns
The Matching Service needs to look up online drivers quickly. The login flow needs to find users by email. These query patterns drive index decisions.
CREATE INDEX idx_drivers_status ON drivers(status);
CREATE INDEX idx_riders_email ON riders(email);
CREATE INDEX idx_vehicles_driver ON vehicles(driver_id);
The status index on drivers is critical for operational queries: “how many drivers are currently online in this city?” Without this index, the query scans every driver row.
SLIDE 99B: Users Database, Why PostgreSQL
Why PostgreSQL for User Data
The user data in a ride-sharing platform is a textbook case for a relational database. Drivers have vehicles (one-to-many). Riders have payment methods (embedded as JSONB for flexibility). Registration flows require ACID transactions: creating a driver record, a vehicle record, and uploading documents must either all succeed or all roll back.
Volume Analysis
Uber has roughly 5 million active drivers and 130 million monthly active riders. Even at full scale, the users table is tens of millions of rows. PostgreSQL handles this comfortably on a single instance with read replicas. There is no volume argument for NoSQL here.
Write frequency is low. Users update their profiles rarely. Drivers update their status (online/offline) a few times per day. The dominant access pattern is reads: look up a rider by ID when they request a ride, look up a driver by ID when displaying trip details, join driver and vehicle tables when showing driver info to a rider.
What About the Driver Status Field?
The status field on the drivers table tracks whether a driver is online, offline, or in a trip. This field updates a few times per day per driver, not per second. It is a separate concern from the real-time position data (which lives in Redis and updates every 4 seconds). A common mistake is conflating “driver status” with “driver position.” The status is relational metadata. The position is a real-time geospatial data point. They live in different databases for good reason.
SLIDE 100: Trips Database, Core Trip Record
The Trip Record Schema
CREATE TABLE trips (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
rider_id UUID NOT NULL,
driver_id UUID,
status TEXT NOT NULL DEFAULT 'requested',
pickup_location GEOGRAPHY(Point, 4326) NOT NULL,
dropoff_location GEOGRAPHY(Point, 4326) NOT NULL,
pickup_time TIMESTAMPTZ,
dropoff_time TIMESTAMPTZ,
distance_km NUMERIC(8,2),
duration_minutes NUMERIC(8,2),
fare_amount NUMERIC(10,2),
surge_multiplier NUMERIC(4,2) DEFAULT 1.00,
payment_id UUID,
city_id TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
The status field is the state machine from Section 5. Valid values: requested, matched, driver_en_route, driver_arrived, in_progress, completed, cancelled, failed. The Trip Service enforces valid transitions in application logic.
The city_id field is the geographic shard key. Every trip belongs to a city, and all data for that city lives on the same shard. This is covered in depth in SLIDE 107.
Indexes Driven by Query Patterns
CREATE INDEX idx_trips_rider_history ON trips(rider_id, created_at DESC);
CREATE INDEX idx_trips_driver_active ON trips(driver_id, status);
CREATE INDEX idx_trips_status ON trips(status);
CREATE INDEX idx_trips_city_created ON trips(city_id, created_at DESC);
Each index exists because a specific query needs it. The rider history index supports paginated trip history in the rider app. The driver active index supports “does this driver currently have an active trip?” lookups, which happen on every dispatch. The city index supports operational queries for a specific market.
SLIDE 100B: Trips Database, Trip State History
Why Store Every State Transition
The trips table stores the current state. But for auditability, debugging, and dispute resolution, you need the full history of how a trip moved through its states.
CREATE TABLE trip_state_history (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trip_id UUID NOT NULL REFERENCES trips(id),
from_state TEXT NOT NULL,
to_state TEXT NOT NULL,
triggered_by TEXT NOT NULL,
metadata JSONB DEFAULT '{}',
transitioned_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_state_history_trip ON trip_state_history(trip_id, transitioned_at);
Each state transition is an immutable append. When a trip moves from “requested” to “matched,” a row is inserted recording: the trip ID, the previous state, the new state, what triggered the transition (e.g., “driver_47_accepted”), and a timestamp.
Use Cases for State History
Customer support: a rider disputes a cancellation fee. Support pulls the state history and sees the trip was in “driver_en_route” when cancelled, confirming the fee is valid.
Debugging: a trip ended in “failed” but the rider claims they saw a match. State history shows the trip reached “matched” but then transitioned to “failed” 90 seconds later because the driver never arrived and the system timed out.
Analytics: calculate average time-in-state for each stage. How long do drivers typically take from “en_route” to “arrived”? This feeds into ETA model improvements.
The metadata JSONB column stores context-specific details: for a cancellation, it might include the cancellation reason and fee amount. For a match, it might include the driver ID and acceptance latency.
SLIDE 101: Payments Database, Transactions and Settlement
Payment Records
CREATE TABLE payments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trip_id UUID NOT NULL,
rider_id UUID NOT NULL,
amount NUMERIC(10,2) NOT NULL,
currency TEXT NOT NULL DEFAULT 'USD',
status TEXT NOT NULL DEFAULT 'authorized',
payment_method_id UUID NOT NULL,
gateway_ref TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
captured_at TIMESTAMPTZ,
refunded_at TIMESTAMPTZ
);
The payment lifecycle has three stages. At trip start, the system authorizes a hold on the rider’s payment method for the estimated fare. At trip end, the system captures the actual fare amount. If the actual is less than the authorized hold, the difference is released. If the rider disputes, a refund is processed.
The status field tracks this lifecycle: authorized, captured, refunded, failed. Each transition is critical. A payment must be captured exactly once. Capturing twice means double-charging the rider. Failing to capture means the rider rides for free.
Driver Settlement
CREATE TABLE driver_settlements (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
driver_id UUID NOT NULL,
period_start DATE NOT NULL,
period_end DATE NOT NULL,
total_trips INT NOT NULL,
gross_amount NUMERIC(12,2) NOT NULL,
commission NUMERIC(12,2) NOT NULL,
net_amount NUMERIC(12,2) NOT NULL,
status TEXT DEFAULT 'pending',
paid_at TIMESTAMPTZ
);
Settlements are batch-processed daily or weekly. The system aggregates all completed trips for a driver within the period, calculates the platform commission (typically 20-25%), and initiates a payout. This is an asynchronous process with no real-time latency requirement.
Why a Separate Payments Database
Payment data has the strictest consistency requirements in the entire system. It must be isolated from other workloads so that a spike in trip record writes never contends with payment processing. It also has distinct compliance requirements (PCI DSS) that benefit from physical isolation.
SLIDE 102: Location History, Time-Series Storage
Why Not PostgreSQL for Location History
The Location Service writes 1.25 million GPS updates per second. Each update is roughly 100 bytes: driver_id, latitude, longitude, timestamp, heading, speed. That is 10 TB of new data per day, or approximately 3.6 PB per year.
PostgreSQL is not designed for this workload. At 1.25M inserts/sec, a relational database would choke on write-ahead log (WAL) overhead, index maintenance, and MVCC bloat. The data is append-only (never updated after write), rarely queried in real time, and naturally partitioned by time. This is exactly the workload that time-series databases are optimized for.
What Is a Time-Series Database?
Time-series databases optimize for three properties that describe location history perfectly:
High write throughput with append-only semantics. Every write is a new row, never an update to an existing row. The database can batch and compress writes aggressively.
Automatic time-based partitioning. Data is split into chunks by time period (hourly, daily). Old chunks can be compressed or archived independently. Querying “all positions for driver_47 in the last hour” only touches one partition.
Time-range queries. The primary access pattern is “give me all positions between timestamp A and timestamp B.” The database indexes by time natively, making these queries efficient.
Redis holds the current position (overwritten every 4 seconds, TTL-based expiry). The time-series database holds the full history (append-only, retained for weeks or months). These serve completely different purposes and should never be conflated.
SLIDE 102B: Time-Series Options Compared
Three Viable Options for Location History
Decision Framework
If the team already runs PostgreSQL and wants a familiar operational model, TimescaleDB is the simplest choice. It installs as a PostgreSQL extension, supports full SQL, and integrates with existing tooling. Write throughput is sufficient for city-scale deployments.
If the system operates at the largest global scale (all cities, all countries, multi-million writes per second across the cluster), Cassandra provides the highest write throughput with tunable consistency and linear horizontal scaling. The trade-off is operational complexity: Cassandra requires expertise in compaction strategies, repair processes, and data modeling that is very different from relational thinking.
InfluxDB is purpose-built for time-series but is most commonly used for infrastructure metrics and monitoring. It works for location history but is a less common choice in ride-sharing architectures.
Interview Tip:
Do not name a technology without justifying it. “I would use Cassandra for location history” is weak. “I would use Cassandra because location history is 10 TB/day of append-only writes with time-range queries and no update or join requirements, which is exactly the workload Cassandra optimizes for” is strong.
SLIDE 103: Real-Time Driver Positions, Redis
Redis Is Both Cache and Primary Store for Positions
For real-time driver positions, Redis is not a caching layer in front of a database. It is the primary store. There is no “source of truth” database behind it for current positions. When a driver sends a GPS update, it goes into Redis via GEOADD. When the spatial index is queried, the answer comes from Redis via GEOSEARCH. The location history database holds the archival copy, but it is never queried for real-time matching.
Core Commands
GEOADD drivers -73.9857 40.7484 "driver_123"
This stores driver_123 at longitude -73.9857, latitude 40.7484. If driver_123 already exists, the position is overwritten. No explicit deletion of the old position is needed.
GEOSEARCH drivers FROMLONLAT -73.9851 40.7489 BYRADIUS 5 km ASC COUNT 20
This returns up to 20 drivers within 5 km of the specified point, sorted by distance ascending. Performance: O(N+log(M)) where N is the number of results returned and M is the total number of elements in the sorted set.
GEODIST drivers "driver_123" "driver_456" km
This returns the distance between two drivers in kilometers.
TTL for Stale Position Expiry
Each driver’s position key has a TTL of 15-20 seconds. If a driver stops sending GPS updates (app crash, phone died, went into a tunnel), the position auto-expires. This prevents the spatial index from returning ghost drivers who are no longer reachable.
The TTL is set on a wrapper key (not on the sorted set member directly, since Redis sorted sets do not support per-member TTL). The Location Service checks the wrapper key’s existence before including a driver in query results.
Scaling Redis for Positions
A single Redis instance can handle one city comfortably. For a city with 50,000 drivers: 50K sorted set members consume roughly 2.5 MB of memory. Write throughput of 12,500 GEOADD operations per second is well within Redis’s capacity (100K+ ops/sec on modern hardware). For multi-city or global deployment, run one Redis instance per city or use Redis Cluster for very large cities.
SLIDE 104: Geospatial Column Types, PostGIS
PostgreSQL + PostGIS for Spatial Queries on Trip Data
Redis handles real-time driver positions. But trip records also have spatial data: pickup and dropoff locations. These locations are stored in PostgreSQL using the PostGIS extension, which adds native geospatial column types and spatial indexes.
ALTER TABLE trips ADD COLUMN pickup_location GEOGRAPHY(Point, 4326);
ALTER TABLE trips ADD COLUMN dropoff_location GEOGRAPHY(Point, 4326);
CREATE INDEX idx_trips_pickup_geo ON trips USING GIST(pickup_location);
The GEOGRAPHY column type stores latitude/longitude as a spherical coordinate. The GiST (Generalized Search Tree) index enables efficient spatial queries against trip data.
Use Cases for PostGIS on Trip Data
These are not real-time queries. They are analytical and operational queries that run against historical trip records.
“How many trips started within 1 km of Times Square this month?”
SELECT COUNT(*)
FROM trips
WHERE ST_DWithin(
pickup_location,
ST_MakePoint(-73.9855, 40.7580)::geography,
1000
)
AND created_at >= '2026-08-01';
“What is the average trip distance for rides originating from the airport zone?”
SELECT AVG(ST_Distance(pickup_location, dropoff_location)) / 1000 AS avg_km
FROM trips
WHERE ST_Within(
pickup_location::geometry,
(SELECT boundary FROM geofences WHERE name = 'JFK_Airport')
);
When to Use PostGIS vs Redis Geospatial
PostGIS: historical and analytical spatial queries against trip records. Low query frequency, high data volume, SQL joins with other columns. Redis: real-time position lookups for active drivers. Extreme query frequency, low data volume per query, sub-200ms latency requirement. They solve different problems and should not be substituted for each other.
SLIDE 105: Denormalization Decisions
When to Duplicate Data for Read Performance
In a normalized schema, displaying a trip in the rider’s history requires joining the trips table with the riders table and the drivers table to get names, and with the vehicles table to get vehicle info. That is three joins for every trip history page load.
Denormalization embeds frequently-read fields directly into the trip record to avoid these joins at read time.
ALTER TABLE trips ADD COLUMN rider_name TEXT;
ALTER TABLE trips ADD COLUMN driver_name TEXT;
ALTER TABLE trips ADD COLUMN vehicle_type TEXT;
ALTER TABLE trips ADD COLUMN vehicle_plate TEXT;
Now the trip history query is a single table scan with no joins. The rider app loads faster, and the database does less work per request.
The Trade-Off
Faster reads, but risk of stale embedded data. If a driver changes their name, every trip record that embedded the old name still shows the old name. This is acceptable when the source of truth exists separately (the drivers table) and the embedded copy is for display convenience. No business logic depends on the embedded name being current.
Where Denormalization Makes Sense in This System
Trip record with embedded rider and driver names: avoids a join on every trip history query. The trip history is read far more often than a user changes their name.
Driver record with pre-calculated average rating: avoids aggregating all ratings on every Matching Service read. The average is recalculated asynchronously when a new rating arrives.
Where Denormalization Does Not Make Sense
Payment amounts should never be duplicated. The payment record is the source of truth for how much was charged. Embedding the fare in both the trip record and the payment record creates a reconciliation problem.
SLIDE 106: SQL vs NoSQL, Decision Table for This System
Most of the System Is Relational
A common misconception is that a high-scale system needs NoSQL everywhere. In a ride-sharing platform, most data is relational and most access patterns benefit from SQL. The NoSQL choices are surgical, targeted at specific data types where the relational model breaks down.
Five out of seven data types are PostgreSQL. Two are purpose-built NoSQL for extreme volume or specialized access patterns.
The Decision Criteria
Use SQL (PostgreSQL) when the data has relationships you need to query across, requires ACID transactions, or has moderate volume where relational overhead is not a bottleneck.
Use NoSQL when the data volume exceeds what a relational database can ingest (millions of writes per second), the access pattern is specialized (geospatial, time-series, full-text), or the data is naturally key-value or append-only with no relational joins.
SLIDE 107: Geographic Sharding, By City, Not By User
Why Ride-Sharing Shards Differently from Social Networks
Instagram shards by user_id. A user’s posts, followers, and feed live on a shard determined by hashing their user ID. This works because the dominant access pattern is “show me everything for this user,” and that data can live anywhere in the world.
Ride-sharing shards by geography. A trip involves a rider, a driver, and a location, all in the same city. The dominant access pattern is “show me everything happening in this city right now.” If you shard by user_id, a single trip’s data could be spread across three different shards (one for the rider, one for the driver, one for the trip). Every ride request would require cross-shard coordination.
Geographic sharding keeps all data for a city on the same shard: riders in Mumbai, drivers in Mumbai, trips in Mumbai, and positions in Mumbai all live together. Every operation in the core ride flow is shard-local with zero cross-shard queries.
The Shard Key
The shard key is city_id (or region_id for larger geographic areas). Every table that participates in the ride flow includes a city_id column. The shard routing logic lives in the API Gateway or a routing layer: determine the city from the request’s location, route to the correct shard.
Cross-Shard Queries Are Rare and Acceptable
A rider who travels from Mumbai to New York has trip history across both shards. Displaying their full trip history requires querying both shards and merging results. This is a rare, read-only operation (viewing history is infrequent compared to active rides). The slight latency increase from querying multiple shards is acceptable for this use case.
SLIDE 107B: Geographic Sharding, Implementation
Shard Routing
The API Gateway extracts the pickup location from the request, determines which city it belongs to (via geofence lookup or a city boundary table), and routes the entire request to the appropriate shard. All downstream services within that shard handle the request without any cross-shard communication.
Adding a New City
Launching in a new city is operationally clean with geographic sharding. Provision a new database shard (PostgreSQL instance, Redis instance, time-series partition). Configure the routing layer to recognize the new city boundary. Onboard drivers. Enable ride requests. The new city’s infrastructure is completely independent of existing cities. A bug or outage in the new city has zero impact on established markets.
Scaling Within a City
If a single city grows beyond what one shard can handle (e.g., a megacity with millions of concurrent drivers), the city shard can be further partitioned by zone or district. However, most cities fit comfortably on a single well-provisioned shard. Even a city with 100,000 active drivers generates roughly 25,000 GPS writes/sec, well within a single Redis instance’s capacity.
Contrast with User-Based Sharding
If you shard by user_id, adding a new city does not simplify anything. The new city’s riders and drivers are distributed across all existing shards based on their user IDs. You cannot scale one city independently. You cannot isolate one city’s failure. Geographic sharding is not just a preference. It is a consequence of the data model.
SLIDE 108: Entity-Relationship Diagram, Full Data Model
How the Data Fits Together
Cardinalities
A rider has many trips. A driver has many trips. Each trip has exactly one rider and one driver (after matching). Each trip has exactly one payment record. Each trip has many state history records (one per transition). Each driver has one active vehicle (though historically may have had others).
The Trip Record Is the Central Entity
Trips are the nucleus of the data model. Every other entity connects through trips. The rider connects to their history via trips. The driver connects to their earnings via trips. Payments exist because of trips. State history tracks trips. Analytics aggregates trips.
This centrality is why the trips table is the most queried, most indexed, and most carefully sharded table in the system. Its schema, indexes, and partitioning strategy have the largest impact on overall system performance.
Entities Not in the Core Ride Flow
Ratings (many-to-one with trips), geofences (zone boundaries for pricing and matching rules), promotions (discount rules applied at fare calculation time), and driver documents (license, insurance, uploaded during onboarding). These exist in the database but are not on the critical path for ride matching, so they do not drive the core schema design.
SLIDE 109: Elasticsearch for Search and Logs
Not a Primary Data Store
Elasticsearch is not the source of truth for any data in the system. It is a secondary index, populated asynchronously from the primary databases via Kafka events. If Elasticsearch goes down, rides continue. The primary databases are unaffected. When Elasticsearch recovers, it catches up by consuming events from Kafka.
Use Case 1: Support and Operations Search
Customer support needs to search trips by various criteria: rider name, driver name, date range, city, trip status, fare range. This is a flexible, multi-field search that would require multiple PostgreSQL indexes or slow full-table scans. Elasticsearch’s inverted index handles this naturally.
GET /trips/_search
{
"query": {
"bool": {
"must": [
{ "match": { "rider_name": "Alex" } },
{ "range": { "created_at": { "gte": "2026-08-01" } } },
{ "term": { "city_id": "mumbai" } }
]
}
}
}
Use Case 2: Log Aggregation
Application logs from all services are shipped to Elasticsearch (typically via the ELK stack: Elasticsearch, Logstash, Kibana). Engineers search logs by trace_id, trip_id, service name, error type, and time window. This is the primary debugging tool for production incidents.
Use Case 3: Driver Search
Operations teams search for drivers by name, license plate, vehicle type, status, or city. This is another multi-field search problem that Elasticsearch handles better than PostgreSQL with its specialized full-text indexing.
Feeding Elasticsearch
Trip Service emits events to Kafka on every state change. An Elasticsearch consumer reads these events and indexes the trip data. The index is eventually consistent: there may be a delay of seconds between a trip update and its appearance in search results. This is acceptable for support and operational use cases.
SLIDE 110: Worked Example, Design the Complete Trip Database
Step 1: Define the Tables
The trip database has two tables: trips (the current state of each trip) and trip_state_history (every state transition). Both live on the same PostgreSQL instance, sharded by city_id.
Step 2: Choose Indexes Based on Query Patterns
The most frequent queries determine which indexes to create. Creating unnecessary indexes slows down writes (every INSERT and UPDATE must update every index). Creating too few indexes causes slow reads. The balance is driven by which queries run at high frequency.
Step 3: Estimate Row Counts and Storage
At 12.3 TB/year, the trip database is large but manageable with PostgreSQL. Geographic sharding distributes this across city-level shards. A city processing 500K trips/day stores roughly 90 GB/year of trip data, which is comfortable for a single PostgreSQL instance.
Step 4: Decide on Sharding
Shard key: city_id. All trips for Mumbai live on the Mumbai shard. Rider trip history across cities requires querying multiple shards, but this is infrequent and acceptable.
SLIDE 110B: Worked Example, Query Patterns That Drive Index Decisions
Every Index Must Justify Its Existence
An index that no high-frequency query uses is a liability. It slows writes without speeding reads. The table below maps each query pattern to its frequency and the index that supports it.
The Most Critical Index
The (driver_id, status) composite index is the single most important index in the trip database. Every time the Dispatch Service wants to send an offer to a driver, it must verify the driver does not already have an active trip. This query runs on every dispatch attempt, hundreds of times per second per city. Without this index, the query scans all trips for that driver. With this index, it is a direct lookup.
SELECT id FROM trips
WHERE driver_id = 'driver_47' AND status IN ('matched', 'driver_en_route', 'in_progress')
LIMIT 1;
This query must return in under 5 milliseconds. The composite index makes it a single index seek.
SLIDE 111: Wrong vs Right, Database Design Mistakes
Common Mistake 1: One Database for Everything
Wrong: “I would use a PostgreSQL database for all the services.”
PostgreSQL cannot handle 1.25 million geospatial writes per second for driver positions. It is not optimized for 10 TB/day of append-only time-series data. Using one database for everything means the highest-volume workload degrades the lowest-latency workload.
Better: “Most of the data is relational, so PostgreSQL is the default. But real-time driver positions need Redis for in-memory geospatial operations at 1.25M writes/sec, and location history needs a time-series database for 10 TB/day of append-only writes. I am matching the database to the access pattern.”
Common Mistake 2: Sharding by User ID
Wrong: “I would shard the database by user_id using consistent hashing.”
In a ride-sharing system, a single trip involves a rider, a driver, and a location, all in the same city. User-based sharding splits this data across multiple shards, requiring cross-shard joins for every ride request.
Better: “I would shard by geography because every operation in the ride flow is city-local. All data for a trip, its rider, and its driver in Mumbai lives on the Mumbai shard. Zero cross-shard queries for the core flow.”
Common Mistake 3: Storing Location History in PostgreSQL
Wrong: “I would store all driver location history in a PostgreSQL table with a timestamp column.”
At 1.25M inserts/sec and 10 TB/day, PostgreSQL’s write-ahead log, index maintenance, and MVCC overhead would make this unsustainable within hours.
Better: “Location history is append-only with time-range queries and no joins. This is exactly what TimescaleDB or Cassandra is built for. I would use a time-series database that auto-partitions by time and handles bulk append writes efficiently.”
SLIDE 112: How to Present Database Design in an Interview
3-4 Minutes, Four Moves
Database design in an interview should follow a tight structure. You are not giving a lecture on PostgreSQL. You are making decisions and justifying them.
Move 1: Start with data types and volumes (30 seconds)
“This system has six data types with different volumes and access patterns. User profiles and trip records are relational at moderate volume. Driver positions are 1.25 million writes per second with geospatial queries. Location history is 10 TB per day of append-only writes. Payments need strong consistency.”
Move 2: Match each data type to a technology (30 seconds)
“PostgreSQL for users, trips, and payments because they are relational with ACID requirements. Redis for real-time positions because it handles geospatial operations in-memory at the required throughput. TimescaleDB for location history because it is optimized for time-series append writes. Elasticsearch for search and log aggregation.”
Move 3: Show one schema with indexes (1-2 minutes)
Pick the most important table (trips) and show the schema with column names, types, and the key indexes. Explain why each index exists by naming the query it supports. This demonstrates that you think about access patterns, not just data modeling.
Move 4: Explain sharding strategy (30 seconds)
“The system shards by geography. All data for a city lives on one shard. This keeps every ride operation shard-local with no cross-shard queries. Adding a new city is as simple as provisioning a new shard.”
Interview Tip:
Do not draw ER diagrams in an interview unless asked. They take too long and add little value in a time-constrained setting. Instead, show one table schema with indexes and explain your sharding strategy. The interviewer can infer the rest of the data model from your architecture discussion.
SLIDE 113: Interview Practice, Database Design
Practice Exercise: Present the Database Design in 3 Minutes
Set a timer. Cover the following from memory:
- Name the data types and their volumes (30 seconds)
- State the technology choice for each with one-line reasoning (30 seconds)
- Write the trips table schema with at least 3 indexes and explain why each exists (1-2 minutes)
- State the sharding strategy and why geographic sharding beats user-based sharding (30 seconds)
Evaluation Criteria
Strong answer: names PostgreSQL, Redis, and a time-series option with volume-based justification. Shows a schema with indexes tied to specific query patterns. Explains geographic sharding with a concrete reason (ride flow is city-local).
Adequate answer: names the right technologies but without volume justification. Shows a schema but without explaining index choices. Mentions sharding but does not contrast geographic vs user-based.
Weak answer: “I would use a NoSQL database because it scales better.” No schema, no access pattern analysis, no sharding discussion.
Transition to Next Section
“Now that we have the data model and storage technologies selected, let me define the API layer that clients use to interact with these services.” That is Section 9: API Design.
SLIDE 114: Section Cheat Sheet
Technology Selection Summary
PostgreSQL: Users, trips, payments, trip state history
Redis: Real-time driver positions (GEOADD/GEOSEARCH), session storage
TimescaleDB: Location history (10 TB/day append-only)
Elasticsearch: Trip search, log aggregation, driver search
PostGIS: Spatial queries on historical trip data (pickup/dropoff analytics)
Key Numbers
Sharding Strategy
Shard key: city_id (geographic)
Why not user_id: ride flow is city-local, user sharding splits trip data across shards
Cross-shard: only rider trip history across cities (rare, read-only)
New city launch: provision shard, configure routing, independent of other cities
Critical Index
How This Connects to Other Sections
Section 5 (Data Flow) defined which services own which data. This section chose the technology and schema for each. Section 6 (Geospatial) designed the spatial index algorithms that run on top of Redis. Section 7 (Matching) depends on the (driver_id, status) index for dispatch eligibility checks. Section 9 (API Design) defines the endpoints that read from and write to these databases. Section 11 (Scaling) covers how these databases scale across regions with read replicas and geographic sharding.