Projections - Clickhouse

Topic

In ClickHouse, there is a concept called a projection in a table. The new schema proposal for graduate insights uses a lot of these projections to aggregate and make queries more performant, but I don’t fully understand what they do.

Conjecture

Based on the name and the use cases that I have seen for projections, they allow the user to take a table of data and reorder it by a new set of sort key to make queries by those parameters more efficient. I believe that instead of creating a wholly new table they link to the existing rows and just create a different “projection” of the table where the rows are ordered by they new sort key instead of the old sort key. In my head though, it is difficult to understand how this could be done efficiently without creating a lot of duplicate data.

Research

Ok so my basic hypothesis is correct. According to the documentation, projections “help optimize queries by creating a reordering of data by attributes of interest.” This reorder can take three different forms:

1. A complete reordering - This is closest to what I mention in the conjecture phase

1. A subset of the original table with a different order - So this could potentially just be over a specific grain?

1. A precomputed aggregation (similar to a materialized view) but with an orderign aligned to aggregation

The mental model for projections is that they can be thought of as a “hidden table” to the original table. They are similar to Materialized Views in the fact that they allow you to pre-compute aggregations and have multiple row orders. However, the big difference is that materialized views are only computed at insert time, whereas projections are automatically updated and kept in sync with the original table. Additionally, instead of having to be targeted directly, when a query targets the original table, ClickHouse automatically samples the primary keys of the projections and chooses the table that can generate the correct result with the least amount of data.

Despite many benefits, there are a few Caveats:

• Projections don’t allow using different TTL from the source table - essentially your source table and your projection are inherently linked. They can’t operate independently like a MV can. This is important if you are automatically deleting data from the source table.

• You can’t do lightweight updates and deletes on a table with projections - Projections are for mostly stable tables that may be fully reloaded but otherwise don’t change much.

• You can’t chain projections like you can MV. Projections are always on the source table as a starting poitn

• You can’t do a join on a projection. You can do it on the source table but not on the projection.

• You can’t have a WHERE clause on a projections. You can queries a table with projection and filter freely but the projection itself can’t have a join.

Final Summary

Essentially, projections allow you to have different sorts or “views” (That is a loaded word; this is probably not the best use for it) of the same data. So for the steppingblocks use case, they are perfect for some instances where we have a data set that we want to filter by major, or by job title. We could have the same table with a major sort key and then have a projection with a job title. Then when we query it, ClickHouse will automatically select the projection or the source table depending on which will deliver the same result with the least amount of rows scanned. I still don’t fully understand the precomputed aggregations, but I will look more at that after this.