While working with long-term enterprise projects, everyone’s focus is on implementing features, maintaining quality, and delivery execution. But gradually, as applications start growing, the problem shifts from features to handling large datasets. It is important to identify correct data storage strategy while application is growing instead of waiting for storage layer to become bottleneck for the application performance. The database that kept historical information for more than three years; it can lead to slow processing of indexes. The truth is that most of the historical data has not been referenced for months. We hit this on a multi-tenant transactional platform running on Aurora MySQL. What follows is the archival strategy we ended up with after a fair amount of trial and error: three tiers, which we call hot, warm, and cold.
The Core Problem: Not All Data Deserves the Same Treatment
When we first noticed the database slowing down, our instinct was the same as it usually is—add more resources. A larger instance, additional memory, or faster storage would certainly help for a while. But after looking into it, we realized compute wasn’t really the problem. The database was storing data with completely different access patterns in the same place, and every query had to work around that.
- Data the application reads and writes constantly
- Data that gets looked up now and then, but rarely
- Data kept purely for compliance or audit purposes that almost nobody ever queries
Maintaining all three on identical data tiers entails additional costs. More importantly, it adds delays to query response time as popular queries have to share performance resources with obsolete records dating back as far as 2022.
Defining the Tiers
Before we understand migration strategy, let’s understand what warm, cold, and hot storage means.
Hot Data Stays in the RDBMS
Hot data is what the application touches all day: current billing cycles, active device states, in-flight transactions. For most of our tables, this worked out to roughly the last 30 to 90 days, depending on the domain. This data needs millisecond reads and writes, full ACID guarantees, proper indexing and joins, and it has to hold up under high concurrency. That is exactly what a relational engine is for, so it stays in Aurora MySQL, kept as lean as we can manage so the query planner stays predictable.
Warm Data: Still Structured, Out of the Way
Warm data is no longer used on a daily basis, but it is still occasionally hit. A six-month-old invoice is contested by a client. Device events from the previous quarter are retrieved by a support agent. A year is covered by a compliance check. It still needs to be queryable in structured form, and a response time in seconds is acceptable, but none of this justifies competing with the hot path for resources. Additionally, it ought to be less expensive per gigabyte than primary storage.
Cold Data Goes to Object Storage
Cold data was different. These were records we almost never expected to access, but they still had to be retained for audit, compliance, or business reasons. Keeping years of transaction history in the primary database didn’t make sense when the data might not be touched for months. At that stage, storage cost became more important than retrieval latency. to retrieve historical records was an acceptable trade-off. We moved this data to Amazon S3 and relied on Athena whenever we needed to retrieve the data and share with the customers.
| Hot | Warm | Cold | |
|---|---|---|---|
| Access pattern | Constant | Occasional | Rare |
| Latency requirement | In ms | In seconds | In seconds to minutes |
| Storage | Aurora MySQL (Master) | Aurora MySQL (partitioned/secondary) | Amazon S3 |
| Query engine | MySQL engine | MySQL engine | Athena (Presto/Trino) |
| Cost profile | Most Expensive per GB | Still expensive as data lives in the Aurora but less frequent so improved performance on Hot partition. | Least expensive per GB |
Architecture for the Partitioning strategy
Once the tiers were defined, the following three steps were needed.
1. Partitioning as the First Line of Defense
Our largest transactional tables had grown as single unpartitioned tables, and that was the first thing to fix. We restructured them with native MySQL partitioning, keyed on date ranges. Monthly for the high-volume tables, quarterly for the rest.
The benefits of partitioning showed up almost immediately. Queries against recent data became much more efficient because the optimizer only scanned the relevant partitions instead of years of historical records. It also gave us a much cleaner way to separate hot and warm data. Once a partition moved beyond the active window, it automatically became part of the warm tier, with lighter indexing and its own retention policy. The data was still stored in Aurora and remained fully queryable with SQL, but it was no longer competing with the application’s primary workload.
2. Graduating Data to Cold Storage
Partitioning improved performance, but it wasn’t the complete solution. The data was still stored in Aurora and continued to consume database storage. As records became older, we evaluated how often they were being accessed. Instead of choosing an archival threshold based on assumptions, we used access logs from each data domain. Once the access frequency dropped beyond that point, we moved the data out of the transactional database.
There were two different approaches for moving the data from transactional database to S3 storage. First option is using export. If you go with the S3 native approach, it supports exporting a partition’s data directly to S3 without setting up any pipeline. The workflow with the first approach settled into a repeatable pattern: identify partitions past the warm retention window, export them to S3 in Parquet with a prefix layout organized by tenant and date, then validate the export with row counts, checksums, and a few spot queries. Validation is mandatory, and once the data in the exported files is validated, we drop the partition from the table. But there is a risk using S3 native approach. for a very large partition’s this can put CPU at risk of high utilization. For partitions with less than 100 million records, this approach works well. Second option that can be used for the very large data set is to create a data archival script that uses chunk mechanism to copy data from database to S3 bucket. Again, validation is necessary. After exported file is validated, we dropped the partition from the table. Dropping the partition immediately recovered the storage space.
Choosing Parquet mattered more than it might seem, because the columnar format is what made the next piece cheap to run.
3. Querying Cold Data Without a Database
The risk with cold storage is that it quietly becomes write-only. Data goes in, and nobody looks at it again until an audit forces the question, at which point retrieving it is a project. We wanted cold to mean slower and cheaper, not inaccessible.
Therefore, we directed Athena to use the exported parquet files, with the table structures being set up on the basis of some tenant/year/month prefix framework. The S3 partition layout mirrored how the data had been partitioned in Aurora, thus allowing partition pruning to reduce the scanning volume even with respect to a lot of historical data. The compliance query for the last two years no longer required the estimated capacities of Aurora infrastructure, as it became a targeted scanning process in Athena, where only relevant partitions were scanned and which took only a few seconds, thus saving us money on the process.
What It Got Us
Aurora stayed lean. Once aged partitions were routinely exported and dropped, active storage shrank considerably, and buffer pool efficiency and query latency stayed stable even though total historical volume kept growing underneath.
Costs landed where they belonged. Hot data pays for performance. Cold data pays almost nothing to sit in S3 and only incurs computing when someone asks it a question, which is rare.
And nothing was lost along the way. Every tier stayed queryable, just through an engine matched to how often the data gets touched. Once the export-and-drop workflow was automated against each table’s retention policy, archival stopped being a quarterly fire drill. It became a background job nobody thinks about, which is the best compliment an operational process can get.
The Broader Lesson
The biggest change wasn’t a single piece of architecture. It was accepting that a transactional record isn’t equally valuable to the business at every point in its life and designing storage around that instead of defaulting to keeping everything in the database forever. That is what let the relational engine keep doing the thing it’s genuinely good at, while S3 and Athena absorbed the rest at a fraction of the cost.
