The Law Of One Density

What The Law Of One Density Actually Means In Practice

The Law Of One Density is a data organization principle that says: if your query needs a small slice of columns from a wide table, the data for those columns should be stored contiguously so the system can skip reading the rest entirely. It sounds obvious until you've spent three weeks debugging why a simple SELECT query is reading 400GB of disk when it only needs 12GB of actual data. In systems like ClickHouse, this maps directly to how primary keys, sparse indexes, and columnar storage interact. A well-ordered table means adjacent rows that share similar key values end up in the same data part on disk. When you filter on that key, the engine reads only the relevant parts instead of scanning the whole file. The density law is essentially saying: maximize the ratio of useful bytes read per byte touched on disk.

Applying The Law Of One Density To Your Tables

Start by understanding how your query patterns actually look. Not your ideal queries, not the dashboard you hope to build eventually. Look at the actual WHERE clauses, the GROUP BY columns, the JOIN keys, and the filters that run most frequently. If you're filtering heavily on event_date and event_type, those columns need to sit together in the key order. Here's the practical part. When you define a PRIMARY KEY (or more precisely, the sorting key in modern ClickHouse) make sure it matches your dominant query predicate pattern. A single-column key might seem simpler but leaves complex multi-predicate queries under-indexed. A composite key that mirrors your most common filter combination extracts far more value. The engine uses that key to compute Granules to skip, and each granule represents a contiguous chunk of rows. More granules that fall outside your filter range means less I/O. Column order matters too, though less dramatically than people assume. Data parts store columns separately, so a query that only reads columns A and B from a 50-column table still won't touch columns C through Z on disk. However, keeping frequently co-accessed columns near each other in memory during decompression does help CPU cache behavior. Don't over-optimize this. It's a secondary concern compared to your sort key.

The Edge Case That Cost Me A Weekend

I had a table with a sort key on (org_id, event_date, event_type). Performance was fine for most queries, but one specific dashboard started timing out. The problem was that a particular org_id had a massive outlier of events in a single hour, while the vast majority of other org_ids had sparse activity across months. The sort key placed all of that hour's data in one dense cluster, which was great for queries filtering on that org_id and date range. Terrible for the aggregate query that needed to sum across all org_ids for a given event_type, because it had to read every single granule across the entire partition. The fix wasn't to change the sort key. It was to add a materialized view that pre-aggregated at the (event_date, event_type) level and switched the dashboard query to hit that instead. The materialized view uses a different sort key suited to its access pattern. This is one of those things that doesn't appear in documentation until you've already burned through your query budget and wondered why a simple aggregation query is reading 80 percent of the table.

Common Mistakes And Where The Law Breaks

The biggest mistake is designing the sort key around a single query or a single dashboard. Tables serve multiple access patterns, and optimizing for one almost always degrades another. The solution isn't a perfect key that serves everything equally well. It's choosing the dominant pattern and using denormalization or materialized views for the exceptions. Another pitfall is assuming low cardinality columns should go first in the sort key. If you sort by status (active, inactive, deleted) first, you get large chunks of data that all look the same to the index. The granule skipping becomes almost useless because every granule passes the filter. Put high-cardinality columns earlier in the key, and use the low-cardinality ones later to refine the slice. The law also breaks down when your workload is fundamentally write-heavy with analytical queries running offline. In those scenarios, the overhead of maintaining fine-grained sorting and merging parts can dominate. You're spending more CPU and I/O on compaction than on query serving. If your typical query runs once an hour against aggregated data, just write to a flat log table and aggregate on read or on a schedule. Don't force the density law where there's nothing dense to exploit.

What The Law Of One Density Can't Fix

It won't help if your data distribution is random with respect to your filter columns. If you're querying with filters that correlate poorly with your sort key, the engine still has to scan most of the table regardless of how well-ordered the data is. Pre-filtering with a subquery or pushing predicates into a joined table often yields better returns than reorganizing a table that's structurally misaligned with its access pattern. There's also a hard limit around partitioning. If you partition by date at a daily granularity and then query across six months, the optimizer still has to open and examine 180 partitions even if each partition's internal sort key is optimal. Coarser partitioning reduces metadata overhead but means larger individual partitions with potentially less granular skipping. Find the middle ground. Weekly or biweekly partitions often work better than daily for tables with months of history and queries that span weeks. The practical takeaway is that The Law Of One Density is about making the data you need physically contiguous on disk so that the index can eliminate everything else. It's not a silver bullet, it requires real understanding of your actual queries, and it creates tradeoffs that will surface as annoying edge cases. Design for the common path, isolate the exceptions, and measure the actual bytes read versus bytes scanned before declaring victory.