Designing a schema for observability

Ok.

0 rows in set. Elapsed: 0.177 sec. Processed 10.37 million rows, 685.32 MB (58.66 million rows/s., 3.88 GB/s.)
Peak memory usage: 56.54 MiB.
```

Use Null to measure performance

We 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.

```
ALTER TABLE otel_logs_v2 (
  ADD PROJECTION status
  (
     SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent ORDER BY Status
  )
)

ALTER TABLE otel_logs_v2 MATERIALIZE PROJECTION status
```

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.

```
CREATE TABLE otel_logs_v2
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8,
        PROJECTION status
        (
           SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
           ORDER BY Status
        )
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)
```

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`.

```
SELECT parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE (`table` = 'otel_logs_v2') AND (command LIKE '%MATERIALIZE%')

┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
│           0 │     1   │                    │
└─────────────┴─────────┴────────────────────┘

1 row in set. Elapsed: 0.008 sec.
```

If we repeat the above query, we can see performance has improved significantly at the expense of additional storage (see ["Measuring table size & compression"](#measuring-table-size--compression) for how to measure this).

```
SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`

0 rows in set. Elapsed: 0.031 sec. Processed 51.42 thousand rows, 22.85 MB (1.65 million rows/s., 734.63 MB/s.)
Peak memory usage: 27.85 MiB.
```

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"](#measuring-table-size--compression).

### Secondary/data skipping indices[​](#secondarydata-skipping-indices "Direct link to 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](/docs/optimize/skipping-indexes) 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[​](#text-index-for-full-text-search "Direct link to Text index for full text search")

ClickHouse provides a specialized [text index](/docs/engines/table-engines/mergetree-family/textindexes) 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](/docs/sql-reference/data-types/string), [FixedString](/docs/sql-reference/data-types/fixedstring), [Array(String)](/docs/sql-reference/data-types/array), [Array(FixedString)](/docs/sql-reference/data-types/array), and [Map](/docs/sql-reference/data-types/map) (via [mapKeys](/docs/sql-reference/functions/tuple-map-functions#mapKeys) and [mapValues](/docs/sql-reference/functions/tuple-map-functions#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](/docs/engines/table-engines/mergetree-family/textindexes#using-a-text-index) and [here](/docs/engines/table-engines/mergetree-family/textindexes#functions-example-hasanytokens-hasalltokens).

In the examples below, we use a structured logs dataset.

```
CREATE TABLE otel_logs
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8
)
ENGINE = MergeTree
ORDER BY Timestamp
SETTINGS index_granularity = 8192
```

We can use `hasAnyTokens` also without text index but the query will perform a slow full scan of the Body column:

```
SELECT count()
FROM otel_logs
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: ff0b866c-6df7-47be-9e36-795ef3888169

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.584 sec. Processed 19.95 million rows, 3.08 GB (34.15 million rows/s., 5.27 GB/s.)
```

#### Adding a text index[​](#adding-a-text-index "Direct link to Adding a text index")

A text index on the Body column can be added during table creation: