DEVOURED
How Airtable Saved Millions by Cutting Archive Storage Costs by 100x
Airtable cut archive storage costs by 100x and saved millions annually by migrating petabytes of cold MySQL data to S3 Parquet files queried with Apache DataFusion.
What: Airtable built a two-tier storage system that keeps recent archive data in MySQL while moving older, rarely-accessed rows (cell history and action logs) to S3 as base-partitioned Parquet files. They query this cold data using embedded Apache DataFusion, achieving 10x compression from Parquet and 10x lower per-byte costs on S3 compared to MySQL RDS.
Why it matters: This demonstrates a practical pattern for the common problem of cold data accumulation in expensive OLTP databases. The article provides a detailed implementation blueprint including migration strategy, validation approaches, and latency optimizations that preserve interactive performance while dramatically cutting costs.
Takeaway: If you're managing growing databases with historical data that's rarely queried, evaluate a tiered storage approach: keep hot data in your transactional database and archive cold data to columnar format on object storage with a query engine like DataFusion.
Deep dive
- Airtable's MySQL footprint had grown to petabytes with some databases approaching the 64TB RDS limit, driven primarily by archive tables storing cell history and action logs
- The archive data was mostly cold, read-only, and rarely accessed but required retention for up to 10 years for some enterprise customers with strict durability and availability requirements
- They built a two-tier system where recent rows stay in MySQL while older archive data moves to S3 stored as Parquet files partitioned by base
- Parquet compression reduced the dataset size by 10x and S3 was about 10x cheaper per byte than MySQL storage, combining for roughly 100x cost savings
- Apache DataFusion was chosen as the query engine over Athena (too slow for interactive queries), DuckDB (inefficient projection pushdowns), and StarRocks (operational overhead of running a cluster)
- DataFusion runs as an embedded Rust library inside existing workers, providing process-level isolation per base and high request affinity for excellent cache hit rates
- Migration used RDS snapshots for consistent exports, Flink jobs for parallel repartitioning by base, and Step Functions with SQS for compaction into 1GB Parquet files
- Bulk validation with StarRocks confirmed zero data corruption by comparing serving Parquet files against original RDS snapshots across petabytes
- Shadow validation on live traffic caught cross-runtime bugs including float precision mismatches, sorting issues (lexicographic vs numeric), and async crashes in the napi-rs Node.js bridge
- Tiered caching architecture achieved 99%+ hit rates by caching Parquet metadata and ListObjects results in memory, plus custom page header metadata caching and optional full-file disk caching for outlier bases
- Custom secondary indexes built on top of Parquet files handle sparse filter queries, which is practical here because the archived data is effectively read-only
- Parquet bloom filters enable efficient point lookups on randomly-distributed unique identifiers by pruning page groups that definitely don't contain target values
- Files are sorted by autoincr_id and partitioned by base, allowing the query engine to use Parquet metadata for selective byte-range downloads instead of full-file scans
- Next steps include building CDC-style incremental archiving with Flink to replace the bulk migration approach and expanding the platform to other log-like tables
Decoder
- Parquet: Columnar storage file format that stores data by column rather than row, with built-in compression and metadata that query engines can use to skip irrelevant data
- Apache DataFusion: Embedded query engine written in Rust that efficiently queries Parquet files by exploiting their metadata for pruning
- Apache Flink: Stream processing framework used here for parallelized data migration and repartitioning workloads
- CDC: Change Data Capture, a pattern for tracking and streaming database changes in real-time rather than bulk exports
- Bloom filter: Probabilistic data structure that can definitively say a value is not present or possibly present, used to skip scanning data that won't match
- Row group: Unit of data organization in Parquet containing a subset of rows, with metadata like min/max values for pruning
- OLTP: Online Transaction Processing, databases optimized for transactional workloads with frequent writes rather than analytical queries
- Metadata pruning: Query optimization that uses file statistics to skip reading data blocks that can't contain matching results
Original article
Airtable cut archive storage costs by about 100x by moving cold, mostly immutable MySQL data into S3 as partitioned Parquet files and querying it with embedded Apache DataFusion. The dataset became 10x smaller, while S3 was about 10x cheaper per byte. A Flink-based migration, bulk and shadow validation, tiered caching, custom secondary indexes, and Parquet bloom filters preserved interactive latency and enterprise guarantees.
Explore all 66 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi