Projections as secondary indices

Mark Needham

Looking to speed up your ClickHouse queries without doubling your storage costs? In this tutorial, we dive into lightweight projections, a feature introduced in ClickHouse 25.6 that gives you the performance benefits of secondary indices without the hefty storage overhead.

Instead of duplicating entire tables as traditional projections do, lightweight projections only store the sort key columns plus an offset column that points back to the original data.

We'll walk through a practical comparison using 30 million rows of UK property sales data, testing three different approaches: no projections, traditional projections, and the new lightweight projections. The results are pretty interesting—lightweight projections deliver query speeds that are roughly 2x slower than full projections but use half the storage space, making them a solid middle ground when you need better query performance but can't afford to double your disk usage.

• Understanding how lightweight projections work with the offset column mechanism
• Hands-on setup and data insertion comparison across different projection types
• Real performance benchmarks: 131ms (no projection) vs 6ms (full projection) vs 13ms (lightweight projection)
• Storage trade-offs: balancing query speed against disk space requirements
• Practical guidance on when to use lightweight projections versus traditional ones