Projections
Understand automatic and manual projections, their lifecycle, and their costs.
Projections
A projection stores an alternate layout for a table. It can arrange selected fields differently or store aggregate results. A matching query can use that layout instead of doing all the work from the base data.
The query still addresses the table. A projection is a physical optimization, not a separate source that the application must query by name. Its existence does not guarantee that every query will use it.
Automatic and manual creation
A manual projection describes a layout chosen for a known query pattern. Supported projection operations are available through the Query API, subject to its permissions and validation rules.
When automatic projections are enabled, an advisor examines eligible query patterns and can create layouts for repeated work. Eligibility depends on settings such as query execution time and aggregation characteristics. The advisor limits the number of automatic projections and can remove unused ones.
Manual design gives you an explicit layout. Automatic design responds to observed use. Both consume storage and maintenance resources. See Adaptive optimization for their settings and engine defaults.
Definition and stored data
A projection definition describes how to build the layout. Projection parts contain the resulting data. Creating the definition makes it available for new parts, but does not populate every existing part.
Materialization builds projection data for existing parts. It creates background mutations, so command acceptance and completed coverage are separate events. Automatic projection creation and automatic materialization have separate settings.
A table can therefore have a projection definition while only some parts contain its data. This explains why creating a projection need not immediately change a query's performance.
Lifecycle
| Operation | Effect |
|---|---|
| Add | Create a projection definition for the table. |
| Materialize | Build its data for existing parts in the background. |
| Clear | Remove its stored data while retaining the definition. |
| Drop | Remove the projection definition and its stored data. |
Clearing a projection is different from removing its definition. New writes can still produce projection data while the definition exists. Cancelling a materialization stops unfinished work, but does not undo completed part changes.
Benefits and costs
A useful projection reduces repeated work for matching queries. It can reduce scanning, sorting, or aggregation. A layout that does not match the workload still adds storage and work during inserts and merges.
Materialization also competes for resources with foreground queries and inserts. The benefit depends on query frequency, data volume, coverage, and the plan the engine chooses. A projection is not a guarantee of lower latency.
EXPLAIN can show whether a plan uses a projection. system.projection_parts
shows stored projection data, while system.mutations describes active builds.
Execution statistics show whether the chosen plan reduced actual work.