Originally published at: The Airtable Engineering Blog
About Airtable
Airtable customers keep their data in bases. Each base is assigned to one of many sharded MySQL instances that Airtable operates on Amazon RDS, and all of the data scoped to that base lives on that shard.
Proving that an archive matches its source, row by row, turns into a join between two datasets of nearly a trillion rows each. Airtable chose StarRocks because it could run those joins at a compute cost the team could afford.
Key takeaway · Validation at this scale is a join problem
The original post was written by Riley for The Airtable Engineering Blog. It credits Daniel Kozlowski, Kun Zhou, Matthew Jin and Xiaobing Xia for their contributions.
For every base, Airtable keeps append-only history tables. They back features such as undo and revision history, and enterprise customers can see record revisions going back up to three years. Together these tables make up close to half of everything Airtable stores. They are read rarely, yet they sat on the same costly RDS storage as the data customers use every day.
The challenge
Airtable's Storage team started the Live Shard Data Archive (LSDA) project to move this history data to Amazon S3, delete it from RDS and rebuild the instances to reclaim the space. The team expected this to take a substantial amount off the RDS bill. The project had three steps:
- Archive the RDS data to S3 and transform it.
- Validate the archive against the source.
- Change application code so the history data is served from S3.
For step 1, the team used the built-in RDS snapshot export to write the tables to S3 as Parquet. The export was then reorganized by customer: more than 1 PB in more than 10 million files. Apache Flink read the exported files incrementally into per-customer partitions, and a highly concurrent rewriter job on Kubernetes sorted each customer's archive and added an index and bloom filters to the Parquet files.
Comparing nearly 2 trillion rows
Before any data could be removed from RDS, every archived row had to be checked against the original. That meant almost 1 PB and close to 2 trillion rows. The production RDS instances serve customer traffic, so the team did not query them for validation. Instead, the untouched RDS export in S3 served as the source of truth.
Comparing rows one at a time from S3 would have been far too slow, so the team decided to load both the export and the archive into a relational database and join them to surface any differences. The hard part was the join itself: each side held nearly a trillion rows.
The solution
Why StarRocks
Airtable's Data Infrastructure team helped the Storage team pick the engine. They chose StarRocks for its join performance at an affordable compute cost; according to the team, other query engines had serious trouble with the same workload. The plan was to read the raw Parquet files from S3 into StarRocks local tables and run the validation as colocate joins.
To avoid copying the full petabyte, the team hashed every non-key column into one value with XX_HASH3_64 over CONCAT_WS, so each loaded table holds only the id, the application and a hash_value. Two tables were ingested this way; the examples in the post use _actionLog.
Settings for a one-time bulk load
The source data was spread across hundreds of millions of small Parquet files, and the team estimated that an unoptimized load would take several months. Three general changes helped:
- One replica instead of three. The load was a one-time job, so high availability was not needed.
- More ingestion parallelism, through
pipeline_dopandpipeline_sink_dop. - More buckets per partition, sized so that no bucket exceeds about 5 GB. The compaction lag this causes was acceptable here.
Loading the export
The first version used a Primary Key table with DISTRIBUTED BY HASH(id, application) and no bucket count. Loading two shards took about a day, and loads got slower as the table grew. With more than five loads in parallel, jobs stalled at LOAD 99% while the data was being made visible.
On the StarRocks team's advice, the second version used about 7,200 buckets for the smaller table. Loads were faster, but they failed with "primary key memory usage exceeds the limit".
The third version partitioned the tables by shard ID, parsed from the S3 path. The hashed actionLog data was about 10 TB across 148 shards, or about 69 GB per shard. At 5 GB per bucket that is 34 buckets; with headroom, the team used 64 buckets per partition, for 9,472 buckets in total. Partitioning by shard also let them validate one shard at a time, which kept memory use in check.
Loading the archive
The archive tables use the same bucket count, the same partitioning by shard ID and the same colocation group as the export tables, so the two could be joined with a colocate join.
The archive is laid out differently. It is organized by application for serving, in more than 6 million small directories that do not line up with shards, and the shard is not part of the S3 path. Airtable already kept the archive's file metadata in Amazon DynamoDB, so the team added a Global Secondary Index with the shard ID as the sort key to list the files for each shard.
Because each insert reads from one path, the archive needed more than 6 million insert statements, compared with about 160 folders for the export. Running ten parallel Node.js/TypeScript processes per shard produced "too many versions, current/limit 1006/1000", and that version limit cannot be set above 1000. The team switched to generating a single INSERT INTO … SELECT … FROM file1 UNION ALL SELECT … FROM file2 … statement per batch, and ran 100 applications at once.
The results
Both datasets were loaded into StarRocks for the validation joins:
- Export: under 10 hours to load from S3, averaging about 2 billion rows per minute.
- Archive: about 3 days for roughly 1 trillion rows across 6 million applications.
Since both sides share partitioning, bucket counts and a colocation group, the comparison is set up to run as colocate joins, one shard at a time. The post covers this ingestion phase; it does not report the results of the comparison or the final RDS savings.
What's next
The last LSDA step is to change Airtable's application code so that history data is served from the archive in S3.