Designing ClickHouse schemas for logs and traces: when the design decides cost and speed
The schema decides cost and speed for logs and traces before the first query runs. A team storing high-volume telemetry in self-managed ClickHouse needs a table design that keeps searches by service and time fast and keeps storage affordable as data grows. The decisions that matter are made up front: the ordering key of the MergeTree table, how attributes are stored (Map or JSON columns), which derived values are computed as materialized columns, how long rows are kept under TTL, and which codecs compress the log text. Changing most of these later means rewriting data, because the ordering key and codecs apply to parts already on disk.
The ordering key is the decision most teams get wrong. It determines which granules ClickHouse reads for a filter, so a key that leads with a high-cardinality trace ID or with a column rarely used in filters forces scans across most of the table. Attribute storage is the second trade-off. A Map column is flexible but every key lookup reads more data than a typed column, while a JSON column suits varied payloads only if the frequently queried paths are promoted to typed sub-columns or materialized columns. Confirm the exact behavior of the JSON type and text indexes against the version you plan to run, since these features change between releases. Retention and codecs also interact with the schema: a TTL expression is only as useful as the time column it references, and log text compresses differently depending on the codec chosen per column.
Ask for a written schema review before ingestion starts. The review should cover the ordering key against your real filter patterns, a sample of attribute cardinality, a TTL policy that matches your retention obligations, and a measured comparison of storage size and query time on representative data. The ClickHouse observability guides cover schema design, OpenTelemetry integration, data management and Grafana, and are a useful reference for the team. ClickHouse server is licensed under Apache-2.0, so the schema work is portable across your own infrastructure, but the cost of getting the design wrong is paid in storage, rewrites and downtime.
Choosing how to store log and trace attributes: Map, JSON or explicit columns
This table compares three ways to store log and trace attributes in ClickHouse: a Map column, the JSON type, and promoted typed columns. Read each row against your own attribute keys and query patterns, and treat the JSON row as something to confirm on your target version before you commit.
| Option | Best fit | Main trade-off | Watch for |
|---|---|---|---|
| Map(String, String) | Unpredictable or high-cardinality keys that are read mostly as a whole or filtered by a few known keys | Every key lookup reads the map column, and all values are strings unless you convert them in queries | Key explosion from user-supplied attribute names, which inflates storage and slows lookups |
| JSON type | Semi-structured attributes from many services where keys vary and the schema evolves often | Flexible typing with less upfront modeling, but query speed and storage depend on which paths are frequently read and on the version in use | Confirm JSON type behavior, path handling and limits for your ClickHouse version before relying on it in production |
| Promoted typed columns | Keys that are filtered or grouped on constantly, such as service name, status code or HTTP route | Fastest filtering and best compression for known keys, but every new key needs a schema change and backfill decision | Keys that are promoted but then stop arriving, leaving columns mostly empty |
| Materialized columns from a Map or JSON source | Hot keys you want typed and indexed while keeping the raw attributes for ad hoc search | Extra storage and insert work for each materialized expression, and data is duplicated | Expressions that change silently when the source key is renamed upstream |
| Ingest pipeline schema drift | Teams running OpenTelemetry Collector or custom pipelines that add attributes without central review | Drift shows up as new keys, type mismatches or casing differences that break queries and promoted columns | Agree attribute naming rules and monitor for unexpected keys on a schedule |
| Query performance versus flexibility | Dashboards and alerts on fixed keys favor typed columns; investigation workflows favor Map or JSON | A mixed design usually keeps a small typed core and a flexible residual store | Measure real queries on representative data in a pre-production cluster before choosing |
Ordering keys for service and time: how the sort order shapes query cost
Most investigations start with a question about one service: what it logged in the last hour, which errors it raised, or how a deployment changed its behavior. In a MergeTree table, the ordering key defines the sort order of rows inside each data part and an index over the sorted rows. ClickHouse stores that index as sparse marks, one per granule of rows, so a query only reads granules whose key range can match its filter. When the leading key column is the service name and the next column is the timestamp, a filter on one service and a time window maps onto a contiguous run of granules. Everything else is skipped without being read.
- Service first in the key: Rows for one service sit together, so a filter on
ServiceNameprunes most of the table before the time filter is applied. - Timestamp after the service: Within each service, rows are ordered by time, so a bounded window reads a narrow range of granules.
- Check the key against filters: A filter that does not reference a leading key column (for example, a bare time filter) cannot use the sparse index to skip ranges.
- Watch cardinality: Very high cardinality values in the leading position create many small key ranges and more index work, so review the service naming scheme before fixing the key.
- Confirm with query plans: Compare rows read in the query log before and after a change to see whether the key is doing the work.
Before finalizing, run your slowest per-service queries with EXPLAIN indexes = 1 and confirm that the key condition is applied to the granules you expect.
Some questions span every service: an incident dashboard showing error volume for the last fifteen minutes, or a capacity review over a week. A key that starts with the service name gives these queries no help, because matching rows are spread across every service's block of the sort order. The engine then has to check many ranges, and the cost grows with the number of services rather than with the time window. A key that starts with time keeps all services' recent rows close together, which suits these scans but weakens per-service pruning.
- Timestamp-led keys: Put the time column first when cross-service time scans dominate, so a window reads one continuous range of granules.
- Per-service pruning cost: The same key makes a single-service filter scan more granules, so this trade-off needs measurement.
- Partitioning by time: Partitions by day or month, together with TTL, help retention and let old partitions be dropped without touching the sort order. Confirm the partition grain against your retention policy.
- Materialized views for rollups: When dashboards need the same aggregation repeatedly, a materialized view that pre-aggregates by service and minute can serve them without rescanning raw logs.
- Keep the decision written down: Record which pattern the key favors so later schema changes are made deliberately.
Ask your team which dashboards run most often and whether they filter by service, by time only, or both. The answer usually determines which column leads.
Trace lookup asks for every span that shares one trace ID, often across several services and a short time span. A trace ID is high cardinality and random, so it has no useful locality in a key that starts with service or time. Putting the trace ID in the leading position would make trace lookups fast but would scatter per-service and time scans. Many teams therefore keep the primary key focused on service and time and add a secondary index or a projection for trace ID, so each pattern gets an access path suited to it. The exact choice depends on the table layout your ingestion produces and should be tested.
- Random IDs and sparse indexes: A sparse index cannot narrow a random value to a few granules unless the data is ordered by it, so trace ID lookups need a separate mechanism.
- Skip indexes: A bloom filter or similar skip index on the trace ID column can rule out granules that do not contain it, at the cost of index build and storage.
- Projections: A projection stores a copy of the table sorted by trace ID, which speeds lookups at the cost of extra write work and storage.
- Time bounds on lookups: Adding a time window to trace queries narrows the scan whatever index is used.
- Verify the feature set: Confirm the behavior and availability of skip indexes and projections for your ClickHouse version in the official documentation before relying on them.
Test a sample of real trace IDs from your incident history and compare rows read with and without each access path.
Ordering keys are hard to judge from first principles because the result depends on data distribution, ingestion order and the actual query mix. A sound approach is to build two or three candidate tables from a representative sample, load them with the same data, and run the same set of queries against each. Measure rows and granules read, memory and elapsed time, and keep the query text fixed so differences come from the schema. The sample should reflect real service count, attribute width and burst behavior, not a synthetic evenly spread dataset. Because ClickHouse is designed for analytical scans, the results should be read in terms of data read, not only wall time.
- Representative data: Use a window of production-like logs or spans that includes the largest services and their peaks.
- Fixed query set: Include per-service, cross-service and trace ID queries, and weight them by how often operators run them.
- Read metrics: Compare rows read, bytes read and granules selected using the query log and
EXPLAINoutput. - Ingestion cost: Measure insert throughput and part counts for each candidate, since a different sort order can change merge behavior.
- Ask for a written test plan: If a partner runs this work, request the candidate keys, data sample, query set and acceptance criteria before the test starts.
Record the chosen key, the reasons, and the queries it was tested against so future schema changes start from evidence.
How to build the schema in sequence and check each step
The sequence turns raw telemetry into a table design that answers the target queries cheaply, with a check at each step so problems surface before production data arrives.
Profile incoming telemetry fields and cardinality
Your team collects a sample of logs and traces from the real sources and lists every field, its type, how often it appears and how many distinct values it takes. Service name, severity, status code and route are usually low cardinality, while trace IDs, user IDs and full URLs are high cardinality. Write down the target queries your engineers and on-call staff run, because those queries decide which fields need fast access. The step is done when you have a field inventory with cardinality estimates and a short list of target queries, reviewed by the people who will investigate incidents with the data.
Choose the ordering key for service and time
Select the ORDER BY key for the MergeTree table based on the target queries, typically a leading time bucket followed by the fields most often used as filters, such as service and severity. Keep the key short and put low-cardinality columns first, because ClickHouse uses the key to skip data while reading. Confirm the choice by running each target query under EXPLAIN with indexes enabled and checking that the primary key condition filters granules. If a query still scans most of the table, revisit the key before adding other structures.
Define how attributes are stored
Decide how the variable attributes of each event are kept, using a Map column for flexible string attributes, a JSON column where nested structure matters, or dedicated columns for fields that every query needs. The choice affects both storage and query cost, so test each option with the sample data and compare compressed size on disk from the system tables. The step is complete when the team agrees on one storage pattern per attribute group and the test results are recorded.
Add materialized columns for hot fields
For attributes that dashboards and alerts read on every query, add materialized columns that extract them from the Map or JSON value at insert time, so queries read a plain column instead of parsing the attribute. Only extract fields that appear in the target queries, because each materialized column adds storage and insert work. Verify the extraction with sample inserts and a SELECT that compares the materialized value with the original attribute for a set of rows. The step is done when the extracted values match the source and the target queries reference the new columns.
Set codecs for log body text
Assign compression codecs to the large text columns, usually the log body, and keep the default codec for numeric and low-cardinality columns unless tests show a gain. Compare compressed size and query time on representative text for each candidate codec, and measure insert throughput at the volume you expect. Record the chosen codec per column in the table definition so the setting survives schema changes. The check is a size and timing comparison from system tables for the sample data, not a generic recommendation.
Configure TTL retention on the table
Add a TTL expression that deletes or moves rows after the retention period your compliance and operations teams have agreed, using the event timestamp column. Confirm the period with the owners of the data, since it drives storage sizing and any legal retention duties. Test the TTL on the sample data by setting a short test window in a non-production table, then check in the system tables that expired parts are removed on merge. The step is done when the production retention value is documented and the test confirmed the expiry behaves as expected.
Load representative data and run target queries
Load a copy of realistic production volume into a pre-production cluster, using representative data and the same queries your teams run, as the ClickHouse production FAQ recommends for choosing a version and validating changes. Run each target query, record read rows and bytes from the query log, and compare against the targets set in the profiling step. Review the results with the people who will use the tables, and re-run the cycle after each change to the ordering key or columns. The sequence is complete when every target query meets the latency and read-volume goals your team set in advance.
TTL retention and codecs for log text: keeping storage predictable
Retention in ClickHouse is set by a TTL clause on the table, and the first decision is the retention window itself. That window should come from your organization's data retention policy and any regulatory obligations for logs and traces, not from storage capacity alone. Once the window is written down, a TTL expression on the timestamp column tells ClickHouse to delete rows once they age past it, and the deletion runs as part of background merges rather than as a scheduled job you have to build. Confirm with your compliance owner whether logs that contain personal data need a shorter window than traces, because a single table with one TTL cannot express both without a separate design.
Align the TTL with partitioning before you rely on it. ClickHouse removes expired data by merging parts, so a partition key that matches the retention unit (for example, a monthly partition on the timestamp) lets whole partitions age out cleanly, with DROP PARTITION available for emergencies. A partition key that is too fine creates many small parts and slows merges, while one that is too coarse keeps expired rows on disk longer than the policy allows. Test the expiry on a copy of production-shaped data and check that row counts and disk usage fall as expected, since a TTL that never fires is a common cause of storage growth that looks like a sizing problem.
Column codecs decide how much CPU you spend to save disk. Log message text is usually the largest column and compresses well with the default LZ4 path, while a stronger codec such as ZSTD with a level set in CODEC(ZSTD(n)) trades more ingest and merge CPU for smaller parts. Attribute columns, whether stored as Map or JSON, benefit from the same reasoning, but high-cardinality keys can inflate metadata regardless of codec. Benchmark both ingest throughput and query latency with your own log samples before choosing levels, because the right balance depends on your hardware and query mix. Storage tiers and moves to object storage are covered in the storage, tiering and retention content group.
Schema mistakes that cost the most later
Most ClickHouse schema problems for logs and traces do not show up on day one. They appear as slow queries, growing disks and inserts that back up once real traffic arrives. The cards below name the failures we see most often, the symptom to watch for and the check to run before the schema reaches production.
High-cardinality ordering keys
An ordering key that starts with a unique value, such as a trace ID or request ID, stops ClickHouse from skipping granules when queries filter by service and time. The usual symptom is that every query on a service scans most of the table even though the filter looks selective. Check the ORDER BY clause against the filters your dashboards actually use, and confirm that service and time lead the key with the high-cardinality column placed after them.
Unbounded attribute keys in Map
Storing every attribute as a key in a Map or JSON column feels flexible until services start emitting unique keys, such as user IDs or generated field names. The number of distinct keys then grows without limit, and the column becomes expensive to scan and hard to query. Watch for queries that slow down after a new release, and count distinct attribute keys per service each week. Promote only the keys you filter on often, and agree on a naming convention before instrumentation spreads.
Missing TTL on raw logs
Raw log and trace tables without a TTL keep every row until someone deletes it by hand, which rarely happens. Disk use then rises with ingest volume, and retention drifts away from what the privacy or security policy requires. Check that each raw table has a TTL expression tied to the retention period you have approved, and that the same period is documented for any rollup or summary table that outlives it.
Over-promoted materialized columns
Materialized columns that extract many fields from every log line are computed on each insert, so a long list of them adds CPU cost to the ingest path. Teams often add a column for each new dashboard filter and never remove the old ones. Watch for rising insert latency or part merge backlog after a schema change. Review each materialized column against the queries that use it, and remove columns that no query has read recently.
Exporter-created schemas nobody reviewed
The OpenTelemetry Collector exporter and ClickStack can create tables automatically, with column types, codecs and ordering keys chosen for a generic workload. Teams that deploy them without reading the generated DDL often inherit defaults that do not fit their volume or query patterns. Compare the live CREATE TABLE output with the design your team approved, and record any differences. Treat a change to the exporter configuration as a schema change that needs the same review.
Protecting telemetry tables: replication, backups and access
Replication keeps a telemetry table available when a node fails, but it is not a backup. ReplicatedMergeTree tables coordinate their replicas through ClickHouse Keeper, which replaces ZooKeeper, and a recovered replica automatically syncs missing data from a healthy replica. That behavior is useful for log and trace tables, but it also copies a bad DROP, a faulty ALTER, or a corrupted part to every replica. The replication guide is explicit that replication alone does not protect against human error or corruption. Plan for Keeper as its own dependency: the guide recommends at least three Keeper nodes on dedicated hosts in production, with about 4 GB of RAM each. Confirm that sizing against your ingest rate and retention before you build the cluster.
Protection against those failures comes from BACKUP and RESTORE, which support full and incremental backups of tables, partitions or databases to local disk, S3-compatible storage or Azure Blob Storage. Choose the destination and retention period with your security and compliance owners before ingestion starts, because backups of telemetry can contain sensitive attributes and identifiers. The backup documentation also warns that a backup never test-restored will probably not restore properly when needed. Schedule restore tests on a spare cluster, time the restore of your largest telemetry table, and record the result. Those tests are the evidence that the backup works, and they should recur on a fixed cadence.
Access is the third control. The default user starts with all permissions, so create named users and roles through SQL, restrict or disable the default user, and confirm that the change persists across restarts. For team-scoped log access, use row policies so that a team's users can query only the rows for their services, rather than building separate tables per team. Identity can come from external authenticators: the documentation covers LDAP directories, Kerberos, SSL X.509 certificates, an HTTP authenticator and JWT authentication. Decide which of these fits your directory and token issuer, and test each login path with a role that has the narrowest access it needs. The ClickHouse access control documentation lists the objects involved.
How long schema design takes and what it needs from your team
Who should own the ClickHouse schema for logs and traces?
Ownership should sit with a named engineer on the platform or observability team, with the application teams consulted on attributes they emit. The schema decides ordering keys, retention and which attributes become columns, so changes affect every service that writes telemetry. Assign one owner who approves schema changes, keeps a written record of table definitions, and can answer for query performance. DeployOpen can design and document the schema with your team, but the decisions about retention periods, access boundaries and who may add new attributes remain yours to make and sign off.
What does the schema work need from our internal team?
The work needs time from people who know your telemetry in practice. Plan for these inputs before the build starts:
- A sample of real log, trace and metric records from each main source, including the attribute keys they carry.
- The queries your engineers and on-call staff run most often, since these set the ordering keys.
- Retention and access rules from security, compliance and legal teams.
- A pre-production ClickHouse environment with representative data, as the ClickHouse production guidance recommends.
Confirm who will review the design and how quickly those reviewers can respond, because slow review is one of the most common causes of delay.
Can our own team design the observability schema in-house?
Yes, a capable team can design it in-house, especially if it already runs ClickHouse and understands the ClickHouse observability guide. The main risks are less about syntax and more about experience: choosing ordering keys that match real query patterns, deciding which attributes to promote to materialized columns, and setting TTL and codec choices that hold up as volume grows. Ask whether your team has tested these choices against representative data. If not, an outside review of the design before go-live is a common middle path, and a fixed-scope engagement can cover the design alone.
What drives the effort of a ClickHouse schema design project?
Effort rises with the variety of the data more than with its raw size. The main drivers are:
- Telemetry volume and the number of distinct sources, which affect how many table shapes are needed.
- Number of services and teams emitting data, which affects how consistent attribute naming must be.
- Attribute variety, including how many keys appear in Map or JSON columns and how many need promotion to typed columns.
- Retention rules, since different tiers or deletion requirements add TTL logic and testing.
- Query patterns your users depend on, which determine ordering keys and any skip indexes.
Ask for each of these to be counted in the written scope rather than assumed.
What should we ask for in a fixed-scope quote for schema design?
Ask the quote to list each deliverable and the environment it applies to. A useful scope names the telemetry sources covered, the tables and materialized columns to be designed, the TTL and codec choices, the test data and queries used for validation, and the documentation handed over. Also ask what is excluded, such as ingestion pipeline changes, dashboard work or cluster sizing, and what happens if the source inventory differs from what was agreed. Confirm the review and acceptance steps, and whether any follow-up support is included or sold as a separate retainer. DeployOpen does not publish a price list, so the quote follows from this written scope.
How are schema changes handled after go-live?
Schema changes after go-live need a documented change process, because altering ordering keys or column types on a large table is costly and can require data to be rewritten. Agree on who approves changes, how they are tested on a pre-production copy with representative data, and how older and newer ingestion paths coexist during the switch. Confirm how changes will be recorded and whether the backup and restore process is tested before a risky change. Ask for these rules to be written into the handover, since they are what keeps the schema stable after the project team leaves.
Does ClickStack or the OpenTelemetry exporter create the schema for us?
Both can create tables, but the defaults are a starting point rather than a finished design. The OpenTelemetry Collector ClickHouse exporter and ClickStack each create tables with a standard layout, which suits many teams for an initial rollout. Confirm which tables they create, what ordering keys and TTL they set, and how they store attributes, since these may not match your query patterns or retention rules. If you need different keys, promoted columns or codecs, plan those as explicit changes to the defaults and test them before production. Related sibling topics cover the exporter and ClickStack setup in more detail.
Limits to confirm in your ClickHouse version and environment
The schema choices on this page depend on features that change between ClickHouse releases, and on support windows that decide which version you can run. Each item below is a question to settle with the vendor documentation or a pre-production test before the table definitions go into production.
JSON and text index behavior by version
Attribute columns built on the JSON type and any text index you plan for log bodies depend on the server version in use. Confirm which JSON type behavior, subcolumn handling and index functions your release supports, and whether they are generally available or still experimental. Run your exporter's real attribute payloads through a pre-production table and check query plans before you commit the design.
Supported versions per the security policy
Only some ClickHouse versions receive security updates, and the list changes over time. Check the security policy against the version you plan to deploy, and set a rule for when a schema-dependent cluster must move to a supported line. A schema that depends on a feature from an unsupported release becomes an upgrade decision, not a routine patch.
Exporter-created schema defaults
If an OpenTelemetry exporter creates the log and trace tables for you, its default ordering keys, partitioning, TTL and column types may not match your query patterns. Ask which tables the exporter creates, which keys it uses, and whether you can override them before first ingest. Changing ordering keys on a populated table usually means rebuilding it, so review the generated DDL first.
Codec availability for log text
Compression codecs for message bodies and high-cardinality strings vary by release and by how columns are declared. Confirm which codecs your version supports for String and JSON-related columns, and measure compressed size and decompression cost on a sample of your own logs. Codec choice affects storage cost and query latency together, so test both rather than assuming the default is best.
TTL behavior with your partitioning
TTL rules move or delete data at the part level, so how your partitions are defined determines when expired rows actually disappear. Confirm how TTL expressions interact with your partition key, when merges apply deletions, and whether a delete-based TTL can leave data in older parts longer than expected. Verify retention on a test table with compressed timestamps before you rely on it for a compliance window.
Keeper sizing for replicated tables
If your log and trace tables use ReplicatedMergeTree, ClickHouse Keeper coordinates them, and its sizing affects how quickly replicas recover after failures. The replication guide recommends at least three Keeper nodes on dedicated hosts in production, with about 4 GB of RAM each. Confirm these figures against the version you run, and load-test Keeper with your expected insert rate of parts and metadata before go-live.
How DeployOpen works on this
DeployOpen plans, builds and documents ClickHouse observability schemas inside your own infrastructure, starting from the queries your teams run and the retention your compliance owners require. Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.
How we scope
- Inventory the telemetry sources, services and query patterns the schema must serve before choosing any ordering key.
- Agree retention periods per signal type and which attributes need fast filtering versus occasional lookup.
- Record the written scope, deliverables and acceptance tests for the table design, codecs and TTL rules.
How we build
- Build candidate tables for logs and traces, then compare ordering key options against representative queries on representative data.
- Decide Map or JSON handling for attributes and define materialized columns for the fields dashboards filter on most.
- Test TTL rules and codec choices on a pre-production cluster before any rule touches production data.
How we hand over
- Deliver DDL, migration scripts and a schema decision record explaining each ordering key, codec and TTL choice.
- Document how the OpenTelemetry Collector or ClickStack writes into the tables, and which settings must not change without review.
- Walk your platform and operations teams through the design and the restore test that proves retention and backups work.
How we support
- Ongoing support, upgrades and continuous testing are available as retainers after handover.
- Review schema changes when ClickHouse versions change or when new services and attributes are added to the telemetry pipeline.
- Confirm with your team which upgrade and support terms apply to your environment before the engagement is signed.
