Columnar Compression Ratio Calculator

This usually starts out well: you have a big pile of unprocessed raw JSON log data and a data warehouse that promises to take it and make something beautiful of it. So far so good; throw all your stuff into some cloud object storage. Unlimited! Cheap! It’s got to be OK right? It is usually not.

When the bill gets way too high for the month, or when query latency become unbearable, that’s when confidence melts away. And it’s usually not just because there’s a whole lot of data; it’s how that data exist on disk prior to being processed by an engine. Columnar storage changes the geometry of your data, but only if you let it.

How to Save Money on Data Storage

Input your data characteristics for each row in the table, and the calculator does the math for you. This saves you from guessing whether your mix of text and timestamps will compress, or if it will just cause higher CPU use when reading. It makes you realize that there’s no magic switch called “compression.” Rather, it’s a series of choices, beginning with how data appears on disk and ending with which codec to run across that representation.

The encoding layer gets skipped. People assume that gzip or zstd is where all the action happens. No. First the data are encoded. Dictionary encoding reduces repeating strings down to little integer pointers if you have low cardinality column such as status flags. It’s a huge reduction as it converts the text to numbers prior to being seen by compressor. An encoder can do nothing for you if your data look like random noise. Patterns makes a difference.

More than most engineer realize, sort order matters. Long strings of the same value, or adjacent values; is best for run-length encoding. User IDs in random order across files? Nothing. But if you sort by tenant ID (or some other value like a partition date) first, then write, those runs extend themselves and they’re good at compression. It is a little thing you do operationally that save you a lot on storage. The repeat percentage input and sort quality account for that. You get a feel for just how clustered your own workload really are.

Next, consider the codec choice. For interactively analyzing data, you can safely choose Snappy as your codec since it’s fast to decompress. This makes scanning cheaper by using less CPU. For big fact tables or other cold data, Zstd will give you slightly better compression at the expense of some CPU cycles. For these, storage cost trumps compute cycle. In other words: there’s no free lunch. You’re paying for either disk space or processor time.

The chart on the page makes this very clear. It suggests (as you might expect) gzip isn’t a good analytic codec, but is familiar from file archival purposes.

Another variable is nulls. Does your format handle them well? Is it compressed with high nulls? Good! Is it wasting space where you store a complete payload for a null value? Bad! If you have wide tables and lots of optional columns, then don’t query these columns when querying because it doesn’t compress very well. You should of drop the ones that aren’t used in the query. This shows up as part of the scan reduction metric which tell you how much of your database has been transferred across the wire vs. How much is sitting on disk. It shouldn’t cost you anything to move the 32 out of 40 columns you didn’t pick in your BI tool.

The point of the demo (with preset examples such as customer profiles or IoT time series) is that there are not one-size-fits-all solution to different types of data. For example, a clickstream log full of chaotic event attributes differ from a table of financial records sorted first by account number and then by date. The former benefits from delta encoding on counters, while the latter rely on dictionary compression for categorical strings. The latter leverages counter-based delta encoding. Warehouses cost money when they treat everything as if it belong in a single mold.

The last part is validation. The models are good for planning, but you’ll be surprised in the real world by distributions. Don’t test on synthetics; test with a representative sample. Apply those settings, then see what happens in the real world; how big the files turn out, how fast queries perform. There’s no magic to compression. It simply shows how well your data structure match the tools that know best how to shrink it. Match your access patterns with codec, encoding, and sort order, and storage stops behaving as if it were a bottomless pit. It begins acting like an engineered system.