ClickHouse projections now behave like true secondary indexes

ClickHouse, a columnar database engine, initially supported only a single primary index per table, limiting query optimization based on the table's sort order. However, recent updates have introduced lightweight projections that act as secondary indexes without duplicating full data, enhancing query performance by allowing multiple projections with different sort orders. These projections store only their sorting key and a pointer back to the base table, reducing storage overhead. They enable finer filtering through granule-level pruning, significantly speeding up query response times. A benchmark test demonstrated a roughly 90% speedup when using these projections compared to a full table scan, showcasing their efficiency in processing complex queries with multiple filters. The article also explains the introduction of new settings to control the optimization process, allowing ClickHouse to leverage all applicable projections for improved query performance.