Skip to main content
We recommend users always create their own schema for logs and traces for the following reasons:
  • Choosing a primary key - The default schemas use an ORDER BY which is optimized for specific access patterns. It is unlikely your access patterns will align with this.
  • Extracting structure - You may wish to extract new columns from the existing columns e.g. the Body column. This can be done using materialized columns (and materialized views in more complex cases). This requires schema changes.
  • Optimizing Maps - The default schemas use the Map type for the storage of attributes. These columns allow the storage of arbitrary metadata. While an essential capability, as metadata from events is often not defined up front and therefore can’t otherwise be stored in a strongly typed database like ClickHouse, access to the map keys and their values isn’t as efficient as access to a normal column. We address this by modifying the schema and ensuring the most commonly accessed map keys are top-level columns - see “Extracting structure with SQL”. This requires a schema change.
  • Simplify map key access - Accessing keys in maps requires a more verbose syntax. You can mitigate this with aliases. See “Using Aliases” to simplify queries.
  • Secondary indices - The default schema uses secondary indices for speeding up access to Maps and accelerating text queries. These are typically not required and incur additional disk space. They can be used but should be tested to ensure they’re required. See “Secondary / Data Skipping indices”.
  • Using Codecs - You may wish to customize codecs for columns if they understand the anticipated data and have evidence this improves compression.
We describe each of the above use cases in detail below. Important: While users are encouraged to extend and modify their schema to achieve optimal compression and query performance, they should adhere to the OTel schema naming for core columns where possible. The ClickHouse Grafana plugin assumes the existence of some basic OTel columns to assist with query building e.g. Timestamp and SeverityText. The required columns for logs and traces are documented here [1][2] and here, respectively. You can choose to change these column names, overriding the defaults in the plugin configuration.
ClickStack ships an optimized default schemaClickStack provides out-of-the-box schemas for logs, traces, and metrics that incorporate the latest ClickHouse features (text indexes for full-text and map-key search, materialized columns and ALIAS arrays for direct-read filtering, block-number row lookups) and have been benchmarked to deliver strong out-of-the-box performance for logging and trace workloads. Use them as a reference point for your own design.

​
Extracting structure with SQL

Whether ingesting structured or unstructured logs, users often need the ability to:
  • Extract columns from string blobs. Querying these will be faster than using string operations at query time.
  • Extract keys from maps. The default schema places arbitrary attributes into columns of the Map type. This type provides a schema-less capability that has the advantage of users not needing to pre-define the columns for attributes when defining logs and traces - often, this is impossible when collecting logs from Kubernetes and wanting to ensure pod labels are retained for later search. Accessing map keys and their values is slower than querying on normal ClickHouse columns. Extracting keys from maps to root table columns is, therefore, often desirable.
Consider the following queries: Suppose we wish to count which URL paths receive the most POST requests using the structured logs. The JSON blob is stored within the Body column as a String. Additionally, it may also be stored in the LogAttributes column as a Map(String, String) if the user has enabled the json_parser in the collector.
Assuming the LogAttributes is available, the query to count which URL paths of the site receive the most POST requests:
Note the use of the map syntax here e.g. LogAttributes['request_path'], and the path function for stripping query parameters from the URL. If the user hasn’t enabled JSON parsing in the collector, then LogAttributes will be empty, forcing us to use JSON functions to extract the columns from the String Body.
Prefer ClickHouse for parsingWe generally recommend users perform JSON parsing in ClickHouse of structured logs. We’re confident ClickHouse is the fastest JSON parsing implementation. However, we recognize you may wish to send logs to other sources and not have this logic reside in SQL.
Now consider the same for unstructured logs:
A similar query for the unstructured logs requires the use of regular expressions via the extractAllGroupsVertical function.
The increased complexity and cost of queries for parsing unstructured logs (notice performance difference) is why we recommend users always use structured logs where possible.
Consider dictionariesThe above query could be optimized to exploit regular expression dictionaries. See Using Dictionaries for more detail.
Both of these use cases can be satisfied using ClickHouse by moving the above query logic to insert time. We explore several approaches below, highlighting when each is appropriate.
OTel or ClickHouse for processing?You may also perform processing using OTel Collector processors and operators as described here. In most cases, you will find ClickHouse is significantly more resource-efficient and faster than the collector’s processors. The principal downside of performing all event processing in SQL is the coupling of your solution to ClickHouse. For example, you may wish to send processed logs to alternative destinations from the OTel collector e.g. S3.

​
Materialized columns

Materialized columns offer the simplest solution to extract structure from other columns. Values of such columns are always calculated at insert time and can’t be specified in INSERT queries.
OverheadMaterialized columns incur additional storage overhead as the values are extracted to new columns on disk at insert time.
Materialized columns support any ClickHouse expression and can exploit any of the analytical functions for processing strings (including regex and searching) and urls, performing type conversions, extracting values from JSON or mathematical operations. We recommend materialized columns for basic processing. They’re especially useful for extracting values from maps, promoting them to root columns, and performing type conversions. They’re often most useful when used in very basic schemas or in conjunction with materialized views. Consider the following schema for logs from which the JSON has been extracted to the LogAttributes column by the collector:
The equivalent schema for extracting using JSON functions from a String Body can be found here. Our three materialized columns extract the request page, request type, and referrer’s domain. These access the map keys and apply functions to their values. Our subsequent query is significantly faster:
Materialized columns will, by default, not be returned in a SELECT *. This is to preserve the invariant that the result of a SELECT * can always be inserted back into the table using INSERT. This behavior can be disabled by setting asterisk_include_materialized_columns=1 and can be enabled in Grafana (see Additional Settings -> Custom Settings in data source configuration).

​
Materialized views

Materialized views provide a more powerful means of applying SQL filtering and transformations to logs and traces. Materialized Views allow you to shift the cost of computation from query time to insert time. A ClickHouse materialized view is just a trigger that runs a query on blocks of data as they’re inserted into a table. The results of this query are inserted into a second “target” table.
Real-time updatesMaterialized views in ClickHouse are updated in real time as data flows into the table they’re based on, functioning more like continually updating indexes. In contrast, in other databases materialized views are typically static snapshots of a query that must be refreshed (similar to ClickHouse Refreshable Materialized Views).
The query associated with the materialized view can theoretically be any query, including an aggregation although limitations exist with Joins. For the transformations and filtering workloads required for logs and traces, you can consider any SELECT statement to be possible. You should remember the query is just a trigger executing over the rows being inserted into a table (the source table), with the results sent to a new table (the target table). In order to ensure we don’t persist the data twice (in the source and target tables) we can change the table of the source table to be a Null table engine, preserving the original schema. Our OTel collectors will continue to send data to this table. For example, for logs, the otel_logs table becomes:
The Null table engine is a powerful optimization - think of it as /dev/null. This table won’t store any data, but any attached materialized views will still be executed over inserted rows before they’re discarded. Consider the following query. This transforms our rows into a format we wish to preserve, extracting all columns from LogAttributes (we assume this has been set by the collector using the json_parser operator), setting the SeverityText and SeverityNumber (based on some simple conditions and definition of these columns). In this case we also only select the columns we know will be populated - ignoring columns such as the TraceId, SpanId and TraceFlags.
We also extract the Body column above - in case additional attributes are added later that aren’t extracted by our SQL. This column should compress well in ClickHouse and will be rarely accessed, thus not impacting query performance. Finally, we reduce the Timestamp to a DateTime (to save space - see “Optimizing Types”) with a cast.
ConditionalsNote the use of conditionals above for extracting the SeverityText and SeverityNumber. These are extremely useful for formulating complex conditions and checking if values are set in maps - we naively assume all keys exist in LogAttributes. We recommend users become familiar with them - they’re your friend in log parsing in addition to functions for handling null values!
We require a table to receive these results. The below target table matches the above query:
The types selected here are based on optimizations discussed in “Optimizing types”.
Notice how we have dramatically changed our schema. In reality you will likely also have Trace columns they will want to preserve as well as the column ResourceAttributes (this usually contains Kubernetes metadata). Grafana can exploit trace columns to provide linking functionality between logs and traces - see “Using Grafana”.
Below, we create a materialized view otel_logs_mv, which executes the above select for the otel_logs table and sends the results to otel_logs_v2.
This above is visualized below: If we now restart the collector config used in “Exporting to ClickHouse” data will appear in otel_logs_v2 in our desired format. Note the use of typed JSON extract functions.
An equivalent Materialized view, which relies on extracting columns from the Body column using JSON functions is shown below:

​
Beware types

The above materialized views rely on implicit casting - especially in the case of using the LogAttributes map. ClickHouse will often transparently cast the extracted value to the target table type, reducing the syntax required. However, we recommend users always test their views by using the views SELECT statement with an INSERT INTO statement with a target table using the same schema. This should confirm that types are correctly handled. Special attention should be given to the following cases:
  • If a key doesn’t exist in a map, an empty string will be returned. In the case of numerics, you will need to map these to an appropriate value. This can be achieved with conditionals e.g. if(LogAttributes['status'] = ", 200, LogAttributes['status']) or cast functions if default values are acceptable e.g. toUInt8OrDefault(LogAttributes['status'] )
  • Some types won’t always be cast e.g. string representations of numerics won’t be cast to enum values.
  • JSON extract functions return default values for their type if a value isn’t found. Ensure these values make sense!
Avoid NullableAvoid using Nullable in Clickhouse for Observability data. It is rarely required in logs and traces to be able to distinguish between empty and null. This feature incurs an additional storage overhead and will negatively impact query performance. See here for further details.

​
Choosing a primary (ordering) key

Once you have extracted your desired columns, you can begin optimizing your ordering/primary key. Some simple rules can be applied to help choose an ordering key. The following can sometimes be in conflict, so consider these in order. You can identify a number of keys from this process, with 4-5 typically sufficient:
  1. Select columns that align with your common filters and access patterns. If you typically start Observability investigations by filtering by a specific column e.g. pod name, this column will be used frequently in WHERE clauses. Prioritize including these in your key over those which are used less frequently.
  2. Prefer columns which help exclude a large percentage of the total rows when filtered, thus reducing the amount of data which needs to be read. Service names and status codes are often good candidates - in the latter case only if you filter by values which exclude most rows e.g. filtering by 200s will in most systems match most rows, in comparison to 500 errors which will correspond to a small subset.
  3. Prefer columns that are likely to be highly correlated with other columns in the table. This will help ensure these values are also stored contiguously, improving compression.
  4. GROUP BY and ORDER BY operations for columns in the ordering key can be made more memory efficient.

On identifying the subset of columns for the ordering key, they must be declared in a specific order. This order can significantly influence both the efficiency of the filtering on secondary key columns in queries and the compression ratio for the table’s data files. In general, it is best to order the keys in ascending order of cardinality. This should be balanced against the fact that filtering on columns that appear later in the ordering key will be less efficient than filtering on those that appear earlier in the tuple. Balance these behaviors and consider your access patterns. Most importantly, test variants. For further understanding of ordering keys and how to optimize them, we recommend this article.
Structure firstWe recommend deciding on your ordering keys once you have structured your logs. Don’t use keys in attribute maps for the ordering key or JSON extraction expressions. Ensure you have your ordering keys as root columns in your table.

​
Using maps

Earlier examples show the use of map syntax map['key'] to access values in the Map(String, String) columns. As well as using map notation to access the nested keys, specialized ClickHouse map functions are available for filtering or selecting these columns. For example, the following query identifies all of the unique keys available in the LogAttributes column using the mapKeys function followed by the groupArrayDistinctArray function (a combinator).
Avoid dotsWe don’t recommend using dots in Map column names and may deprecate its use. Use an _.

​
Using aliases

Querying map types is slower than querying normal columns - see “Accelerating queries”. In addition, it’s more syntactically complicated and can be cumbersome for you to write. To address this latter issue we recommend using Alias columns. ALIAS columns are calculated at query time and aren’t stored in the table. Therefore, it is impossible to INSERT a value into a column of this type. Using aliases we can reference map keys and simplify syntax, transparently expose map entries as a normal column. Consider the following example:
We have several materialized columns and an ALIAS column, RemoteAddr, that accesses the map LogAttributes. We can now query the LogAttributes['remote_addr'] values via this column, thus simplifying our query, i.e.
Furthermore, adding ALIAS is trivial via the ALTER TABLE command. These columns are immediately available e.g.
Alias excluded by defaultBy default, SELECT * excludes ALIAS columns. This behavior can be disabled by setting asterisk_include_alias_columns=1.

​
Optimizing types

The general Clickhouse best practices for optimizing types apply to the ClickHouse use case.

​
Using codecs

In addition to type optimizations, you can follow the general best practices for codecs when attempting to optimize compression for ClickHouse Observability schemas. In general, you will find the ZSTD codec highly applicable to logging and trace datasets. Increasing the compression value from its default value of 1 may improve compression. This should, however, be tested, as higher values incur a greater CPU overhead at insert time. Typically, we see little gain from increasing this value. Furthermore, timestamps, while benefiting from delta encoding with respect to compression, have been shown to cause slow query performance if this column is used in the primary/ordering key. We recommend users assess the respective compression vs. query performance tradeoffs.

​
Using dictionaries

Dictionaries are a key feature of ClickHouse providing in-memory key-value representation of data from various internal and external sources, optimized for super-low latency lookup queries. This is handy in various scenarios, from enriching ingested data on the fly without slowing down the ingestion process and improving the performance of queries in general, with JOINs particularly benefiting. While joins are rarely required in Observability use cases, dictionaries can still be handy for enrichment purposes - at both insert and query time. We provide examples of both below.
Accelerating joinsUsers interested in accelerating joins with dictionaries can find further details here.

​
Insert time vs query time

Dictionaries can be used for enriching datasets at query time or insert time. Each of these approaches have their respective pros and cons. In summary:
  • Insert time - This is typically appropriate if the enrichment value doesn’t change and exists in an external source which can be used to populate the dictionary. In this case, enriching the row at insert time avoids the query time lookup to the dictionary. This comes at the cost of insert performance as well as an additional storage overhead, as enriched values will be stored as columns.
  • Query time - If values in a dictionary change frequently, query time lookups are often more applicable. This avoids needing to update columns (and rewrite data) if mapped values change. This flexibility comes at the expense of a query time lookup cost. This query time cost is typically appreciable if a lookup is required for many rows, e.g. using a dictionary lookup in a filter clause. For result enrichment, i.e. in the SELECT, this overhead is typically not appreciable.
We recommend that users familiarize themselves with the basics of dictionaries. Dictionaries provide an in-memory lookup table from which values can be retrieved using dedicated specialist functions. For simple enrichment examples see the guide on Dictionaries here. Below, we focus on common observability enrichment tasks.

​
Using IP dictionaries

Geo-enriching logs and traces with latitude and longitude values using IP addresses is a common Observability requirement. We can achieve this using ip_trie structured dictionary. We use the publicly available DB-IP city-level dataset provided by DB-IP.com under the terms of the CC BY 4.0 license. From the readme, we can see that the data is structured as follows:
Given this structure, let’s start by taking a peek at the data using the url() table function:
To make our lives easier, let’s use the URL() table engine to create a ClickHouse table object with our field names and confirm the total number of rows:
Because our ip_trie dictionary requires IP address ranges to be expressed in CIDR notation, we’ll need to transform ip_range_start and ip_range_end. This CIDR for each range can be succinctly computed with the following query:
There is a lot going on in the above query. For those interested, read this excellent explanation. Otherwise accept the above computes a CIDR for an IP range.
For our purposes, we’ll only need the IP range, country code, and coordinates, so let’s create a new table and insert our Geo IP data:
In order to perform low-latency IP lookups in ClickHouse, we’ll leverage dictionaries to store key -> attributes mapping for our Geo IP data in-memory. ClickHouse provides an ip_trie dictionary structure to map our network prefixes (CIDR blocks) to coordinates and country codes. The following query specifies a dictionary using this layout and the above table as the source.
We can select rows from the dictionary and confirm this dataset is available for lookups:
Periodic refreshDictionaries in ClickHouse are periodically refreshed based on the underlying table data and the lifetime clause used above. To update our Geo IP dictionary to reflect the latest changes in the DB-IP dataset, we’ll just need to reinsert data from the geoip_url remote table to our geoip table with transformations applied.
Now that we have Geo IP data loaded into our ip_trie dictionary (conveniently also named ip_trie), we can use it for IP geo location. This can be accomplished using the dictGet() function as follows:
Notice the retrieval speed here. This allows us to enrich logs. In this case, we choose to perform query time enrichment. Returning to our original logs dataset, we can use the above to aggregate our logs by country. The following assumes we use the schema resulting from our earlier materialized view, which has an extracted RemoteAddress column.
Since an IP to geographical location mapping may change, users are likely to want to know from where the request originated at the time it was made - not what the current geographic location for the same address is. For this reason, index time enrichment is likely preferred here. This can be done using materialized columns as shown below or in the select of a materialized view:
Update periodicallyUsers are likely to want the ip enrichment dictionary to be periodically updated based on new data. This can be achieved using the LIFETIME clause of the dictionary which will cause the dictionary to be periodically reloaded from the underlying table. To update the underlying table, see “Refreshable Materialized views”.
The above countries and coordinates offer visualization capabilities beyond grouping and filtering by country. For inspiration see “Visualizing geo data”.

​
Using regex dictionaries (user agent parsing)

The parsing of user agent strings is a classical regular expression problem and a common requirement in log and trace based datasets. ClickHouse provides efficient parsing of user agents using Regular Expression Tree Dictionaries. Regular expression tree dictionaries are defined in ClickHouse open-source using the YAMLRegExpTree dictionary source type which provides the path to a YAML file containing the regular expression tree. Should you wish to provide your own regular expression dictionary, the details on the required structure can be found here. Below we focus on user-agent parsing using uap-core and load our dictionary for the supported CSV format. This approach is compatible with OSS and ClickHouse Cloud.
In the examples below, we use snapshots of the latest uap-core regular expressions for user-agent parsing from June 2024. The latest file, which is occasionally updated, can be found here. You can follow the steps here to load into the CSV file used below.
Create the following Memory tables. These hold our regular expressions for parsing devices, browsers and operating systems.
These tables can be populated from the following publicly hosted CSV files, using the url table function:
With our memory tables populated, we can load our Regular Expression dictionaries. Note that we need to specify the key values as columns - these will be the attributes we can extract from the user agent.
With these dictionaries loaded we can provide a sample user-agent and test our new dictionary extraction capabilities:
Given the rules surrounding user agents will rarely change, with the dictionary only needing updating in response to new browsers, operating systems, and devices, it makes sense to perform this extraction at insert time. We can either perform this work using a materialized column or using a materialized view. Below we modify the materialized view used earlier:
This requires us modify the schema for the target table otel_logs_v2:
After restarting the collector and ingesting structured logs, based on earlier documented steps, we can query our newly extracted Device, Browser and Os columns.
Tuples for complex structuresNote the use of Tuples for these user agent columns. Tuples are recommended for complex structures where the hierarchy is known in advance. Sub-columns offer the same performance as regular columns (unlike Map keys) while allowing heterogeneous types.

​
Further reading

For more examples and details on dictionaries, we recommend the following articles:

​
Accelerating queries

ClickHouse supports a number of techniques for accelerating query performance. The following should be considered only after choosing an appropriate primary/ordering key to optimize for the most popular access patterns and to maximize compression. This will usually have the largest impact on performance for the least effort.

​
Using Materialized views (incremental) for aggregations

In earlier sections, we explored the use of Materialized views for data transformation and filtering. Materialized views can, however, also be used to precompute aggregations at insert time and store the result. This result can be updated with the results from subsequent inserts, thus effectively allowing an aggregation to be precomputed at insert time. The principal idea here is that the results will often be a smaller representation of the original data (a partial sketch in the case of aggregations). When combined with a simpler query for reading the results from the target table, query times will be faster than if the same computation was performed on the original data. Consider the following query, where we compute the total traffic per hour using our structured logs:
We can imagine this might be a common line chart users plot with Grafana. This query is admittedly very fast - the dataset is only 10m rows, and ClickHouse is fast! However, if we scale this to billions and trillions of rows, we would ideally like to sustain this query performance.
This query would be 10x faster if we used the otel_logs_v2 table, which results from our earlier materialized view, which extracts the size key from the LogAttributes map. We use the raw data here for illustrative purposes only and would recommend using the earlier view if this is a common query.
We need a table to receive the results if we want to compute this at insert time using a Materialized view. This table should only keep 1 row per hour. If an update is received for an existing hour, the other columns should be merged into the existing hour’s row. For this merge of incremental states to happen, partial states must be stored for the other columns. This requires a special engine type in ClickHouse: The SummingMergeTree. This replaces all the rows with the same ordering key with one row which contains summed values for the numeric columns. The following table will merge any rows with the same date, summing any numerical columns.
To demonstrate our materialized view, assume our bytes_per_hour table is empty and yet to receive any data. Our materialized view performs the above SELECT on data inserted into otel_logs (this will be performed over blocks of a configured size), with the results sent to bytes_per_hour. The syntax is shown below:
The TO clause here is key, denoting where results will be sent to i.e. bytes_per_hour. If we restart our OTel Collector and resend the logs, the bytes_per_hour table will be incrementally populated with the above query result. On completion, we can confirm the size of our bytes_per_hour - we should have 1 row per hour:
We’ve effectively reduced the number of rows here from 10m (in otel_logs) to 113 by storing the result of our query. The key here is that if new logs are inserted into the otel_logs table, new values will be sent to bytes_per_hour for their respective hour, where they will be automatically merged asynchronously in the background - by keeping only one row per hour bytes_per_hour will thus always be both small and up-to-date. Since the merging of rows is asynchronous, there may be more than one row per hour when a user queries. To ensure any outstanding rows are merged at query time, we have two options:
  • Use the FINAL modifier on the table name (which we did for the count query above).
  • Aggregate by the ordering key used in our final table i.e. Timestamp and sum the metrics.
Typically, the second option is more efficient and flexible (the table can be used for other things), but the first can be simpler for some queries. We show both below:
This has sped up our query from 0.6s to 0.008s - over 75 times!
These savings can be even greater on larger datasets with more complex queries. See here for examples.

​
A more complex example

The above example aggregates a simple count per hour using the SummingMergeTree. Statistics beyond simple sums require a different target table engine: the AggregatingMergeTree. Suppose we wish to compute the number of unique IP addresses (or unique users) per day. The query for this:
In order to persist a cardinality count for incremental update the AggregatingMergeTree is required.
To ensure ClickHouse knows that aggregate states will be stored, we define the UniqueUsers column as the type AggregateFunction, specifying the function source of the partial states (uniq) and the type of the source column (IPv4). Like the SummingMergeTree, rows with the same ORDER BY key value will be merged (Hour in the above example). The associated materialized view uses the earlier query:
Note how we append the suffix State to the end of our aggregate functions. This ensures the aggregate state of the function is returned instead of the final result. This will contain additional information to allow this partial state to merge with other states. Once the data has been reloaded, through a Collector restart, we can confirm 113 rows are available in the unique_visitors_per_hour table.
Our final query needs to utilize the Merge suffix for our functions (as the columns store partial aggregation states):
Note we use a GROUP BY here instead of using FINAL.

​
Using Materialized views (incremental) for fast lookups

You should consider their access patterns when choosing the ClickHouse ordering key with the columns that are frequently used in filter and aggregation clauses. This can be restrictive in Observability use cases, where users have more diverse access patterns that can’t be encapsulated in a single set of columns. This is best illustrated in an example built into the default OTel schemas. Consider the default schema for the traces:
This schema is optimized for filtering by ServiceName, SpanName, and Timestamp. In tracing, users also need the ability to perform lookups by a specific TraceId and retrieving the associated trace’s spans. While this is present in the ordering key, its position at the end means filtering won’t be as efficient and likely means significant amounts of data will need to be scanned when retrieving a single trace. The OTel collector also installs a materialized view and associated table to address this challenge. The table and view are shown below:
The view effectively ensures the table otel_traces_trace_id_ts has the minimum and maximum timestamp for the trace. This table, ordered by TraceId, allows these timestamps to be retrieved efficiently. These timestamp ranges can, in turn, be used when querying the main otel_traces table. More specifically, when retrieving a trace by its id, Grafana uses the following query:
The CTE here identifies the minimum and maximum timestamp for the trace id ae9226c78d1d360601e6383928e4d22d, before using this to filter the main otel_traces for its associated spans. This same approach can be applied for similar access patterns. We explore a similar example in Data Modeling here.

​
Using projections

ClickHouse projections allow you to specify multiple ORDER BY clauses for a table. In previous sections, we explore how materialized views can be used in ClickHouse to pre-compute aggregations, transform rows and optimize Observability queries for different access patterns. We provided an example where the materialized view sends rows to a target table with a different ordering key than the original table receiving inserts in order to optimize for lookups by trace id. Projections can be used to address the same problem, allowing the user to optimize for queries on a column that aren’t part of the primary key. In theory, this capability can be used to provide multiple ordering keys for a table, with one distinct disadvantage: data duplication. Specifically, data will need to be written in the order of the main primary key in addition to the order specified for each projection. This will slow inserts and consume more disk space.
Projections vs Materialized ViewsProjections offer many of the same capabilities as materialized views, but should be used sparingly with the latter often preferred. You should understand the drawbacks and when they’re appropriate. For example, while projections can be used for pre-computing aggregations we recommend users use Materialized views for this.
Consider the following query, which filters our otel_logs_v2 table by 500 error codes. This is likely a common access pattern for logging with users wanting to filter by error codes:
Use Null to measure performanceWe don’t print results here using FORMAT Null. This forces all results to be read but not returned, thus preventing an early termination of the query due to a LIMIT. This is just to show the time taken to scan all 10m rows.
The above query requires a linear scan with our chosen ordering key (ServiceName, Timestamp). While we could add Status to the end of the ordering key, improving performance for the above query, we can also add a projection.
Note we have to first create the projection and then materialize it. This latter command causes the data to be stored twice on disk in two different orders. The projection can also be defined when the data is created, as shown below, and will be automatically maintained as data is inserted.
Importantly, if the projection is created via an ALTER, its creation is asynchronous when the MATERIALIZE PROJECTION command is issued. You can confirm the progress of this operation with the following query, waiting for is_done=1.
If we repeat the above query, we can see performance has improved significantly at the expense of additional storage (see “Measuring table size & compression” for how to measure this).
In the above example, we specify the columns used in the earlier query in the projection. This will mean only these specified columns will be stored on disk as part of the projection, ordered by Status. If alternatively, we used SELECT * here, all columns would be stored. While this would allow more queries (using any subset of columns) to benefit from the projection, additional storage will be incurred. For measuring disk space and compression, see “Measuring table size & compression”.

​
Secondary/data skipping indices

No matter how well the primary key is tuned in ClickHouse, some queries will inevitably require full table scans. While this can be mitigated using Materialized views (and projections for some queries), these require additional maintenance and users to be aware of their availability in order to ensure they’re exploited. While traditional relational databases solve this with secondary indexes, these are ineffective in column-oriented databases like ClickHouse. Instead, ClickHouse uses “Skip” indexes, which can significantly improve query performance by allowing the database to skip over large data chunks with no matching values. The default OTel schemas use secondary indices in an attempt to accelerate access to map access. While we find these to be generally ineffective and don’t recommend copying them into your custom schema, skipping indices can still be useful. You should read and understand the guide to secondary indices before attempting to apply them. In general, they’re effective when a strong correlation exists between the primary key and the targeted, non-primary column/expression and users are looking up rare values i.e. those which don’t occur in many granules.

​
Text index for full text search

ClickHouse provides a specialized text index for full-text search. This index builds an inverted index over tokenized text data, enabling fast token-based search queries. Text indexes are available starting from ClickHouse version 26.2. They can be defined on the following column types in MergeTree tables: String, FixedString, Array(String), Array(FixedString), and Map (via mapKeys and mapValues map functions) columns in MergeTree tables. A text index requires a tokenizer argument in its definition. Optionally, a preprocessor function can be specified to transform the input string before tokenization. The recommended functions to search in the index are: hasAnyTokens and hasAllTokens. Some traditional string search functions are also automatically optimized when a text index is present. See the documentation for details and supported functions here and here. In the examples below, we use a structured logs dataset.
We can use hasAnyTokens also without text index but the query will perform a slow full scan of the Body column:

​
Adding a text index

A text index on the Body column can be added during table creation:
or added later using ALTER TABLE:
If we run the same SELECT query again, it will do a text index lookup. The amount of accessed data reduces from gigabytes to megabytes and performance improves by approximately 45x.

​
Using a preprocessor

In this dataset, the Body column contains a JSON-formatted string with multiple key-value pairs (e.g., msg, id, ctx, attr, etc.). Assume we are only interested in searching within the msg field. Instead of indexing the entire JSON string, we can define a preprocessor to extract only the msg value before tokenization. For example:
In this example the preprocessor:
  • reduces the amount of text that is tokenized and indexed,
  • decreases index size,
  • reduces the probability of false positives, and
  • improves query performance.
Compared to the non-preprocessed index, performance improves by approximately 2x. Using a preprocessor also reduces index size from gigabytes to a few hundred kilobytes, approximately 0.01% of the original size
**Other indexes for text search Further details on secondary skip indices can be found here.

​
Extracting from maps

The Map type is prevalent in the OTel schemas. This type requires the values and keys to have the same type - sufficient for metadata such as Kubernetes labels. Be aware that when querying a subkey of a Map type, the entire parent column is loaded. If the map has many keys, this can incur a significant query penalty as more data needs to be read from disk than if the key existed as a column. If you frequently query a specific key, consider moving it into its own dedicated column at the root. This is typically a task that happens in response to common access patterns and after deployment and may be difficult to predict before production. See “Managing schema changes” for how to modify your schema post-deployment.

​
Measuring table size & compression

One of the principal reasons ClickHouse is used for Observability is compression. As well as dramatically reducing storage costs, less data on disk means less I/O and faster queries and inserts. The reduction in IO will out weight the overhead of any compression algorithm with respect to CPU. Improving the compression of the data should therefore be the first focus when working on ensuring ClickHouse queries are fast. Details on measuring compression can be found here.
Last modified on July 3, 2026