The situation
A ride-hailing company runs a single self-managed PostgreSQL instance on EC2. It has grown to hold four very different things.
Bookings. Around 900,000 rows a day. Genuinely relational: a booking joins to a rider, a driver, a vehicle, a fare calculation and a payment, and the company needs those writes to be transactional. This is the part PostgreSQL is good at.
Driver location updates. Every active driver sends a position every three seconds, which at peak is about 40,000 writes a second. The rows are tiny, always written and read by driver ID, and never joined to anything. They are currently a table with 400 million rows that dominates the write load.
Session tokens. Every request from the mobile app looks up a token to find out who is calling. Several hundred thousand lookups a minute, every one a single-key read, and every one currently a query against the same database that is trying to take 40,000 location writes a second.
Analytics. At 09:00 each day the operations team runs queries across a year of bookings to produce utilisation and demand reports. Those queries take twenty minutes, scan hundreds of millions of rows, and make the booking system slow for everyone while they run.
The database is on one EC2 instance with a nightly pg_dump. Two people maintain it, and both spend a day a month on patching and backups.
What actually matters
The first thing to name is that these are four access patterns, not four tables. A relational engine is built around joins, transactions and ad-hoc queries over normalised data, and the booking workload is exactly that. The location stream is a single-key write at very high rate with no joins. The session lookup is a single-key read where the only property that matters is latency. The analytics workload scans a year of history column by column. One engine can be made to serve all four, which is what is happening now, and the result is that each workload’s load degrades the others.
The second is the difference between a managed service and a self-managed one, because it changes what the two administrators do next month. Running the engine on EC2 means owning the operating system, the engine patching, the backups, the failover rehearsal and the capacity planning. On a managed service AWS handles the operating system and engine patching, the automated backups with point-in-time recovery, and the failover. The schema, the queries, the users, the data and the query tuning stay with the company. A day a month of patching is the obvious problem. The larger one is what happens when the instance fails at 3am and the recovery point is last night’s dump.
Third, availability and read performance are separate problems with separate answers, and conflating them is the most common error on this ground. A synchronous standby in another Availability Zone with automatic failover addresses “the primary died”. An asynchronous read copy addresses “reads are slowing the primary down”. They are different features, they can be used together, and a requirement that names one does not name the other.
Finally, throughput at a scale that has no ceiling is a different requirement from throughput at a scale you can size an instance for. Forty thousand writes a second against a single relational primary means sizing an instance for the peak and living with the vertical limit. A key-value store designed to spread by partition key removes the sizing question, and it removes the joins along with it, which is acceptable here precisely because the location data is never joined.
What we’ll filter on
- Fits the access pattern: joins and transactions, single-key writes, single-key reads, or analytical scans.
- Scales to the workload’s peak without sizing an instance for it.
- Removes engine patching, backups and failover from the two administrators.
- Improves availability, or read performance, and is clear about which.
- Migratable with the source database staying online.
The landscape
Amazon RDS runs MySQL, PostgreSQL, MariaDB, Oracle, SQL Server and Db2 as a managed service. AWS handles the operating system, the engine patching, automated backups with point-in-time recovery, and optional Multi-AZ failover. The company keeps the schema, the queries, the users and the data. It is the smallest change from where they are: the same engine, the same SQL, less to operate.
Amazon Aurora is AWS’s own MySQL- and PostgreSQL-compatible engine. Each write is replicated synchronously to six storage nodes across three Availability Zones, and storage is separate from compute. A cluster supports up to fifteen Aurora Replicas alongside the writer, and its automated backups are continuous and incremental, stored in Amazon S3, with a retention period you set between one and thirty-five days. Aurora Serverless adjusts capacity automatically for variable workloads.
Amazon DynamoDB is a serverless NoSQL database supporting key-value and document models, with single-digit millisecond performance at any scale. There is no instance to size, no patching, and no engine version. It scales by partition key, which is why the data model has to be designed around known access patterns, and why there is no join operator. Throughput still has a default ceiling: 40,000 read request units and 40,000 write request units per table per second, adjustable through Service Quotas. DynamoDB Accelerator (DAX) adds an in-memory cache in front, taking reads from milliseconds to microseconds.
Amazon ElastiCache is managed in-memory caching, with Valkey, Memcached and Redis OSS. It sits in front of a database to absorb repeated reads, or holds session state directly. Used as a cache it is volatile by design, which matters when deciding whether something is the only copy.
Amazon MemoryDB is a durable in-memory database compatible with Valkey and Redis OSS, with microsecond reads and single-digit millisecond writes. It writes to a Multi-AZ transactional log, so the data survives node failure.
Amazon Redshift is a columnar data warehouse for analytical queries over large volumes. It is built for the scan-a-year-of-history workload and is not a transactional database.
Amazon Athena queries data in S3 directly with SQL, with no cluster to run, charged per data scanned. It suits analytics over data that already sits in object storage.
AWS Database Migration Service moves data with the source database online, and AWS Schema Conversion Tool converts the schema and stored code when the target engine differs from the source.
Evaluation
Side by side
| Service | Access pattern it fits | Scales without sizing | No engine patching | Migrate with source online |
|---|---|---|---|---|
| PostgreSQL on EC2 (today) | Joins and transactions | ✗ | ✗ | n/a |
| Amazon RDS for PostgreSQL | Joins and transactions | ✗ | ✓ | ✓ |
| Amazon Aurora PostgreSQL | Joins and transactions, higher throughput | Partly | ✓ | ✓ |
| Amazon DynamoDB | Single-key reads and writes at scale | ✓ | ✓ | ✓ |
| Amazon ElastiCache | Repeated reads, session state | ✗ | ✓ | n/a |
| Amazon Redshift | Analytical scans over history | Partly | ✓ | ✓ |
| Amazon Athena | Analytical queries over S3 | ✓ | ✓ | n/a |
The four workloads occupy four different rows, and the current architecture is what happens when all four are pushed into the first one.
Availability and read performance are different features
| Requirement | Feature | What it does |
|---|---|---|
| Survive the loss of the primary | Multi-AZ DB instance deployment | Synchronous standby in another AZ, automatic failover, same endpoint; the standby does not serve reads |
| Take read load off the primary | Read replica | Asynchronous copy that serves reads; can be promoted, with some lag |
| Do both in one deployment | Multi-AZ DB cluster deployment | Writer plus two reader instances across three AZs, semisynchronous replication, and the readers serve reads; RDS for MySQL and PostgreSQL only |
| Survive an AZ loss and serve reads | A Multi-AZ deployment plus read replicas | Common and correct; they are not alternatives |
| Absorb repeated identical reads | ElastiCache | Serves from memory so the query never reaches the database |
| Recover to a point in time | Automated backups | Managed services retain them; a nightly dump cannot do this |
The solution
Move the bookings to Amazon RDS for PostgreSQL, or to Aurora PostgreSQL if the workload needs the extra throughput headroom. Bookings are relational, transactional and joined, so the engine is already the right one and the change is about who operates it. Turn on a Multi-AZ deployment so the failure of an Availability Zone is a failover rather than an outage, and automated backups so recovery is to a point in time rather than to last night. Use DMS to migrate with the source online, so the cut-over is a short window rather than a weekend. The engine is unchanged, so the Schema Conversion Tool is not needed.
Move the location stream to DynamoDB, partitioned by driver ID with a timestamp sort key. Forty thousand writes a second of tiny, never-joined, single-key rows is the pattern DynamoDB is built for, and it removes the vertical sizing problem entirely: there is no instance to make bigger. That figure sits exactly on the default per-table quota of 40,000 write request units a second, so raise it through Service Quotas before the cut-over rather than during it. Set a time-to-live attribute so positions older than the operational window expire without consuming write throughput, rather than accumulating into another 400-million-row table. TTL deletion is not prompt: DynamoDB removes expired items within a few days of the timestamp, so filter them out of queries in the meantime. Taking that write load off the relational database is also what makes the booking workload comfortable on a modest instance.
Put session tokens in ElastiCache. Several hundred thousand single-key lookups a minute are memory-speed reads of a small value, and running them through a relational engine adds a connection, a query parse and a disk-backed page to every one. A token has a natural expiry, so the volatility of a cache matches the data. If the tokens cannot be lost on a failure, MemoryDB is the durable in-memory option, and that is a decision about whether a signed-out user is an inconvenience or a fault.
Move the analytics to Redshift, loaded from the bookings database or from S3. Scanning a year of history is a columnar workload, and running it against the transactional primary is what makes the booking system slow every morning at nine. Alternatively, if the historical bookings are already being exported to S3, Athena queries them in place with no cluster to run and a charge based on the data scanned, which suits a report run once a day. Either way, the analytics stop competing with live bookings, which was the original complaint.
The two administrators’ work changes rather than disappearing. Their day a month on patching and backups mostly goes, because AWS patches the engines and manages the automated backups across all four services. What replaces it is a smaller, different job: managing four data stores instead of one, and owning a data model in DynamoDB that has to be designed around the access patterns rather than normalised. Query tuning stays with them on every managed service too. That is a real trade and it should be made deliberately.
For the migration itself, DMS handles the relational move with the source online, and it also supports DynamoDB as a target for the location data. The Schema Conversion Tool is needed where the engine changes, for example PostgreSQL to Aurora MySQL, which is not the case here.
What’s worth remembering
- Pick the database by access pattern: relational with joins and transactions is RDS or Aurora, single-key reads and writes at scale is DynamoDB, in-memory caching and session state is ElastiCache, analytical scans are Redshift or Athena over S3.
- Managed means AWS handles the operating system, engine patching, automated backups and failover; the schema, the queries, the users, the data and the query tuning stay with the customer.
- A Multi-AZ DB instance deployment is a synchronous standby with automatic failover that does not serve reads; a read replica is an asynchronous copy for read performance; a Multi-AZ DB cluster, on RDS for MySQL or PostgreSQL, adds two readable standbys.
- Aurora is AWS’s MySQL- and PostgreSQL-compatible engine, writing to six storage nodes across three Availability Zones, with up to fifteen Aurora Replicas and continuous incremental backups in Amazon S3.
- DynamoDB has no instance to size and no join operator, so the data model is designed around known access patterns; a table still starts with default quotas of 40,000 read and 40,000 write request units a second, and DAX takes reads from milliseconds to microseconds.
- DMS migrates with the source database online; the Schema Conversion Tool is only needed when the target engine differs from the source.