Partition pruning
Partition pruning skips the data that a query cannot need. It works only when the table is split on the column that the query filters on. A time key cuts a scan of 1 billion rows to one day of data.
See it on the board
The same traffic runs on two boards. The simulator measured both when this page was built.
| Measure | Goal | Partitioned by a hash | Partitioned by time |
|---|---|---|---|
| p99 latency (Dash) | under 2 s | 60.4 s Fail | 4.1 ms Pass |
| p99 latency (Views) | under 100 ms | 10.1 s Fail | 3.4 ms Pass |
| Error rate (*) | under 0.1% | 92.5% Fail | 0% Pass |
| Monthly cost | 16 USD/mo | 16 USD/mo | |
| Error rate | 88.4% | 0% |
Key numbers
| Number | Value |
|---|---|
| Reads per second, one database vCPU | 30,000 reads/s |
| Writes per second, one database vCPU | 10,000 writes/s |
| Disk throughput of a wide-column store | 500 MB/s |
Hash ring · 3 to 4 nodes · 8 of 36 keys move
How it works
A big table is split into partitions. When a query comes, the database can read only the partitions that may hold the answer, and skip the others. This is pruning. It can turn a scan of the whole table into a scan of a small part.
The split column must match the filter. In the mini board a page-view table holds 1 billion rows from two years. A dashboard asks for yesterday's totals twice each second. The table is split by a hash of the row. A hash spreads rows evenly, but every partition holds some rows of yesterday. The database cannot skip any partition, so each query scans all 1 billion rows and takes about 30 s.
If the table is split by time, each partition holds one slice of time. A query for yesterday touches one partition. The scan shrinks from 1 billion rows to the rows of one day, and the dashboard p99 falls below 2 s. Writes still go to the newest partition, and they stay under 100 ms.
A time split also helps to drop old data: delete a whole partition instead of millions of rows. The risk is a hot newest partition, because all writes go there.
Choose the partition column from the query filters, not from the write path alone.
In the levels you change the partition key and check the query time and the write time.