1 min lesson
Cost optimization at TB/PB scale
Use "The posting asks you to improve the 'performance, reliability, and cost profile' of TB-scale ingestion" to say what you would do next.
Step 1 of 3
The posting asks you to improve the "performance, reliability, and cost profile" of TB-scale ingestion. Cost is the third leg, and at that scale a sloppy storage layout or an always-on cluster is a line item someone in finance will ask you about.
Cost optimization splits cleanly into two bills: what you pay to store the data and what you pay to compute over it. Treat them separately, because the levers are different and a good answer touches both.
Learn more
Advanced table
Storage: the small-file problem and friends
Storage: the small-file problem and friendsobject-store cost and read amplification
- Lever
- Compaction
- Problem it fixes
- Millions of tiny files from streaming writes: slow reads, metadata bloat
- What you actually do
- Periodic OPTIMIZE or compaction to target files of about 128MB to 1GB
- Lever
- Partitioning
- Problem it fixes
- Every query scans the whole table
- What you actually do
- Partition by a high-selectivity column, usually date or hour, so the engine prunes
- Lever
- Z-ordering / liquid clustering
- Problem it fixes
- Even within a partition, related rows are scattered
- What you actually do
- Cluster on common filter columns, using Z-order or liquid clustering depending on the table setup
- Lever
- Lifecycle / retention
- Problem it fixes
- Raw data accumulating forever in expensive hot storage
- What you actually do
- Age raw data out past the replay window, covered below
| Lever | Problem it fixes | What you actually do |
|---|---|---|
| Compaction | Millions of tiny files from streaming writes: slow reads, metadata bloat | Periodic OPTIMIZE or compaction to target files of about 128MB to 1GB |
| Partitioning | Every query scans the whole table | Partition by a high-selectivity column, usually date or hour, so the engine prunes |
| Z-ordering / liquid clustering | Even within a partition, related rows are scattered | Cluster on common filter columns, using Z-order or liquid clustering depending on the table setup |
| Lifecycle / retention | Raw data accumulating forever in expensive hot storage | Age raw data out past the replay window, covered below |
Each lever targets a different cost: bytes stored, files opened or bytes scanned.
Interactive diagram. Step through it with the Next and Previous controls below, or Tab to a region to read its detail.
The storage levers work in sequence on the same table.
A good partition scheme means a query for "yesterday" reads one day of files, not the whole table. You pay less for the bytes scanned, which is compute, and the engine touches less of the object store, which is I/O. Over-partition by something like user_id, though, and you recreate the small-file problem with one tiny file per partition. The art is picking a column selective enough to prune but coarse enough to keep files fat.