Full-text search in ClickHouse with text indexes

Full-text search in ClickHouse with text indexes: what it does and when teams need it

Teams that hold large volumes of log lines or document text in ClickHouse often need keyword search in the same place as their SQL analytics. ClickHouse text indexes, generally available from 26.2, add tokenized full-text search to MergeTree tables, so a query can filter rows by the words they contain and then aggregate those rows with ordinary SQL. This fits best when the search terms are known words or phrases, the data already lives in ClickHouse, and the same team runs the retention, access controls and backups for both the search data and the analytics. Keeping both workloads in one cluster means one query language, one set of operational procedures and one place to enforce access rules.

The cost sits at write time and in storage. A text index is built as data is inserted and merged, so the reader should measure ingest throughput, part merge behavior and disk growth on a copy of production-shaped data before committing to it. The tokenizer decides what counts as a match, so test it against real log formats, mixed casing, punctuation, identifiers and non-English text, and confirm the tokenizer options and query functions that your ClickHouse version supports. Ask the team who owns that schema decision, because changing a tokenizer later means rebuilding the index on existing data.

A text index does not replace a dedicated search engine. Relevance ranking, typo tolerance, synonym handling, faceted search interfaces and search across many separate indexes are outside what this feature is meant to provide, and teams that need them should evaluate a tool such as OpenSearch alongside ClickHouse rather than stretch the index. Before rollout, check the version line as well. The ClickHouse security policy lists 26.9, 26.8, 26.7 and 26.3 as supported with security updates, and 26.2 is not on that list, so a team that adopts text indexes on 26.2 should plan a move to a supported line, reviewing the changelog first, as part of the same change.

Choosing a tokenizer for logs, traces and documents

Log lines mix prose messages with key-value pairs, JSON fragments, IP addresses, class names and request IDs. The tokenizer decides where one token ends and the next begins, which determines whether a search for one fragment finds the line that contains it.

Splitting on separators: Characters such as =, :, / and . may split user=alice or 10.0.4.12 into smaller tokens, so a search for alice can match while a phrase search for user=alice depends on how the query is written.
Case handling: ERROR, Error and error may or may not be normalized. This decides whether one search term covers every log level spelling your services emit.
Punctuation inside values: Hyphenated request IDs, dotted class names and stack frame paths are kept whole or broken apart depending on the tokenizer, which changes both recall and the size of the index.
Very common tokens: Terms such as INFO, GET or 200 appear in almost every line. Indexing them adds entries that rarely narrow a search.

Before you commit, collect a week of representative lines from each service and run the exact searches your on-call engineers use during an incident. Confirm the tokenizer options documented for your ClickHouse version, because the behavior described here depends on which one you pick.

Documents such as tickets, runbooks, chat exports and case notes are written for people. Their search needs are closer to information retrieval: users type words in their own forms, expect plurals and tenses to match, and sometimes search for a phrase. The tokenizer and any normalization step settle how much of that behavior the index provides.

Word boundaries: A tokenizer that splits on non-alphanumeric characters treats re-index as two words, which can help a search for index and hurt a search for the exact hyphenated term.
Word forms: Plurals, tenses and inflections only match each other if stemming or a similar normalization step is part of the design. Check whether your chosen setup provides it or whether queries must handle it.
Stop words: Removing frequent words such as the or and shrinks the index, but it also means searches for those words, or phrases built from them, may return nothing.
Partial words: Substring or n-gram tokenization finds fragments inside words, at the cost of a larger index and more false positives. Decide whether users really need partial matches.
Phrases and proximity: Phrase queries depend on token positions being retained. Confirm that the index and functions you plan to use support the phrase behavior your users expect.

Test with real documents that contain the vocabulary of your organization, including product names, abbreviations and non-English text, and compare results against the search behavior your users already accept.

Identifiers such as UUIDs, file paths, email addresses, version strings and hashes carry meaning only as complete values. Splitting them into pieces makes many unrelated records match, while keeping them whole makes partial lookups fail. This is the category where a default tokenizer most often gives surprising results.

UUIDs and hashes: Hyphens and hexadecimal fragments can be split into short tokens that appear in many records, so a lookup by full identifier may return more matches than expected. Test with full-length values from your own systems.
File paths: Slashes and dots separate directories, file names and extensions. Decide whether a search for config.yaml should match every path that contains it, or only the exact file.
Email addresses: The @ and domain separators determine whether a search for a domain finds all addresses under it, and whether a search for a local part finds unrelated domains.
Version strings: Values such as 26.3.1.2 may split into numbers that match across many unrelated versions, which affects upgrade and vulnerability investigations.
Mixed needs: A single field often needs both exact lookups and partial lookups. Consider whether one index can serve both or whether exact-match filtering should stay on a regular column.

For each identifier field, write down whether analysts need whole-value lookup, fragment lookup or both, then confirm the result with full-length samples from production-like data.

Text index options and when each fits

This table compares the main configuration choices for a ClickHouse text index: tokenizer, indexed columns, index granularity and the cardinality of the text. Read each row as a decision with its cost in build time, storage and query speed, then confirm the choice against a representative sample of your logs or documents. The main takeaway is that a text index pays off on high-volume text that users search by term, and adds little on identifiers or fields you already filter by key.

Option	When it fits	Trade-off in build time, storage and query speed
Word-based tokenizer	Log messages and documents searched by whole words or terms	Lower build cost and smaller index. Queries that need partial-word or substring matches may not benefit, so confirm behavior for your version.
N-gram or substring-oriented tokenizer	Partial matches such as fragments of identifiers, hashes or paths	Supports partial matching, but the index is larger and slower to build. Confirm which tokenizer options and sizes your version offers and measure both.
Single primary text column	Log body or main document text that users search most	Smallest added insert and merge cost. Choose this first and add columns only when searches show a need.
Several text columns	Secondary fields such as error text, URL paths or user agents searched together	Each indexed column adds build time and storage on every insert and merge. Measure ingest throughput before adding columns.
Coarser index granularity	Large tables where text searches are occasional and storage is tight	Less index storage and build work, but fewer granules are skipped, so more data is read per query.
Finer index granularity	Frequent, selective searches on large tables where latency matters	More index storage and build work, with more granules pruned per query. Test the trade-off on your query patterns.
High-cardinality text (unique IDs, long stack traces, free-form payloads)	Rarely justified as a text index. Exact-ID lookups are often better served by a sorting key or a simpler skip index	The index grows with the number of unique tokens, and query gains may be small. Test on representative data before committing.
Low-cardinality text (levels, service names, enums)	Fields with few distinct values that you already filter on	Usually adds little. Ordinary columns, sorting keys or the LowCardinality type typically serve these better.

What text index build cost depends on

Text index cost is set by what you index and how the data arrives, not by a single setting. The drivers below interact, so measure each one against a representative sample of your own logs or documents before sizing a cluster.

Volume of text per row

The indexed text is the main input to tokenization and index writes. Long stack traces, request bodies and free-form messages cost far more per row than short structured fields with the same row count. Measure the average and the tail length of the indexed column in your data, since a small share of very long rows often dominates ingest time and index size. Compare these figures against a sample loaded into a pre-production cluster.

Number of indexed columns

Each text index is built and stored separately, so every additional column adds write work and disk use on each part. Index only the columns that users actually search, such as message bodies, and leave identifiers and enumerations on ordinary ordering keys or skip indexes. Ask which columns your investigators query by free text in practice, and treat the rest as candidates for removal from the index definition.

Tokenizer complexity

The tokenizer decides how text splits into terms, and more involved rules produce more terms per row and a larger index. Simple word splitting on whitespace and punctuation is cheaper than rules that keep separators, case folding or n-gram style splitting. Check the tokenizer options available in your ClickHouse version, then test how many distinct tokens a sample produces and whether your search terms still match them.

Merge activity

ClickHouse writes small parts on insert and merges them in the background. Index data is rebuilt and combined along with those parts, so frequent small inserts and heavy merge backlogs increase the CPU and disk I/O spent on the index. Batch inserts to a size your ingest pipeline can sustain, and watch merge queues and part counts during a load test before you accept a sizing estimate.

Replication overhead

In a replicated table, each replica performs its own index work on the data it receives, and replicas coordinate through ClickHouse Keeper. Doubling replicas roughly doubles index maintenance across the cluster, and a replica that falls behind adds catch-up work when it recovers. Measure ingest on a replicated test table with the replica count you plan to run, and confirm the Keeper nodes have enough headroom for the extra coordination traffic.

How to set up and verify a text index

This sequence takes a team from confirming that its ClickHouse version supports text indexes to deciding whether to promote one to production. Each step ends with a check, so the output of one step is the input for the next. The result is evidence that search results match a baseline scan and that storage and insert costs are acceptable.

Confirm the version supports text indexes
Start by checking the ClickHouse release line you plan to run against the release notes and the security policy. Text indexes are generally available from 26.2, so any older build is outside this design. For production, a stable release suits most teams, and the current LTS lines are v26.8.29.10-lts and v26.3.53.4-lts. The security policy lists 26.9, 26.8, 26.7 and 26.3 as supported, so a planned line outside that list needs an upgrade decision first. Check: the test server reports the version you intend to deploy, and the chosen release line is recorded in your change ticket.
Create a test table with representative data
Build a test environment on the same ClickHouse version as production, then recreate the table schema, ordering key, partitioning and TTL settings. Load a slice of data that reflects production: the same log message shapes, field lengths, vocabulary and attribute spread. A sample that only contains clean, uniform text will hide tokenizer problems. Check: column types and row counts match the source table, and a few known queries return the rows you expect before any index exists.
Define the tokenizer and add the index
Choose the tokenizer that matches how your team searches: word-based splitting for most log messages, or a different option if identifiers, paths or partial terms need to match. Confirm the tokenizer options and index syntax in the documentation for your release line, because options can differ between versions. Add the text index to the text column in the test table and confirm whether it applies to existing data parts or only to new inserts. Check: the table definition shows the index with the tokenizer you chose, and the statement completes without errors.
Load a sample and build the index
Insert data in batches sized like your production ingest path, so the index is built under conditions close to the real workload. If the index does not cover existing parts, trigger its materialization and wait for background work to finish. Watch the system tables for parts, merges and mutations to see when the index is complete on every part. Check: all parts show the index as built, no mutations remain pending, and the row count matches the source sample.
Compare search results with a baseline scan
Run the same set of search queries twice: once with the text index in use, and once through an equivalent filter that does not depend on the index, such as a plain scan over the column. Use EXPLAIN to confirm the index is used in the indexed run and is absent in the baseline. Sort both result sets and compare them row for row. Check: the results are identical, and the plan shows the index is applied. Any mismatch points to tokenizer choice or query form, so resolve it before continuing.
Measure storage and insert latency
Compare the on-disk size of the table with and without the index using the system tables for parts and columns, and record the difference per million rows of your sample. Then measure insert latency for batches at the rate you expect in production, with and without the index. Ask your team which latency and storage limits apply to your environment, since those thresholds come from your own service expectations. Check: the storage overhead and insert timings are written down, and the team agrees they fit the ingest and capacity plan.
Restore a backup and roll out to production
Before changing production, take a backup of the target table using BACKUP, then restore it to a spare cluster and confirm the index exists and the query results still match. A backup that has never been restored may not restore when you need it, so the restore test is part of the rollout, not an optional extra. Apply the change to replicated tables and confirm every replica reports the index. Watch insert latency and merges after the change. Check: the restored copy passes the same queries, and production inserts stay within the limits you recorded.

Which query functions use the text index

Only the search functions and predicates on ClickHouse's supported list can read the text index. A query that calls a function outside that list still returns correct results, but ClickHouse evaluates it by scanning the column, so the index helps nothing and the query gets slower as data grows. The failure is silent: no error appears, and the query only looks slow on a dashboard or in a support ticket. Before you standardize a search pattern, confirm the exact function list in the text index reference for the ClickHouse version you run, because the list can change between releases. Pin the version in your pre-production environment and read the changelog before any upgrade, as the ClickHouse production guidance recommends.

Do not rely on the documentation alone. Run EXPLAIN indexes = 1 in front of a representative query, using the same filters your users type, and confirm that the text index appears in the plan and that the number of granules or rows read drops compared with a query without the filter. Test the variants your teams are likely to write, such as wrapping the column in a string function, changing case handling, or using a leading wildcard. Illustrative example: a query that filters message with a token function may use the index, while the same condition written with a pattern match on a wrapped expression may not, and only the plan shows the difference. Record the verified forms in your runbook so that analysts know which patterns are safe.

Treat index use as a test, not a one-time check. Add the verified queries from dashboards, alerts and saved searches to a regression suite, and re-run the plan checks after every version upgrade and after any change to dashboard queries. Ask your team or DeployOpen to scope this as part of the search design, since the cost of these checks is effort from engineers who understand both the data and the query patterns. Where a required search pattern is not on the supported list, decide early whether to change the query, add a materialized column that narrows the scan, or accept the scan cost for low-volume use, because that decision affects server sizing and the retention window you can afford.

Security and operational considerations for search data

Text indexes make searchable copies of log and document content, so they inherit every sensitivity question the raw data raises. The cards below cover the controls that decide who can search what, what gets indexed at all, how long it stays, and how you know the search layer is healthy and recoverable.

Access control on indexed tables

A text index is only as protected as the table it sits on. Query access to the table is query access to the indexed text, so row policies and roles should be designed with search in mind, not added after the schema is set. Check that the default user, which starts with all permissions, is restricted, and that each team's role limits which tables and rows its searches can reach. Confirm this with the access control documentation for your version before rollout.

Masking text before indexing

Once a log line is tokenized into a text index, removing the original value from the source no longer removes it from search results. Decide which fields, such as tokens, email addresses, card numbers or patient identifiers, must be masked or dropped before the insert reaches the indexed column. Test the masking rules against representative samples, since a pattern that misses one format leaves that data searchable by anyone with table access.

Retention through TTL

Retention rules should cover the index as well as the data. Define a TTL on the table so that expired rows are removed, and the text index shrinks as the underlying parts are deleted. Compare the retention period in your policy with the period your search and investigation teams actually need, and document who approves exceptions. Test that expired data is gone from search results, not just from the base table view.

Index included in table backups

Back up the index as part of the table, not as a separate task. The backup documentation covers tables, partitions and databases, and replication alone does not protect against human error or corruption. Confirm that a restored table answers the same text queries as the original, and schedule restore tests on a spare cluster, because a backup that has never been restored may not restore properly when needed.

Monitoring search query performance

Text searches can degrade quietly as data grows or as queries become broader. Track query duration and resource use for search workloads separately from ingestion, and compare results before and after index changes. Watch the built-in system tables and metrics from your monitoring stack, and set alerts on slow search queries and on index build or merge backlogs, so teams see a drop in search speed before users report it.

When a dedicated search engine is still needed

ClickHouse text indexes suit analytics-first search: finding rare tokens across large log or event tables, then aggregating, joining and charting the results in the same SQL. A dedicated search engine such as OpenSearch or Graylog remains the better fit when search is the product itself. The clearest signals are relevance scoring that ranks documents by how well they match, complex analyzers for many languages with stemming and synonyms, search-as-you-type and fuzzy matching in user-facing boxes, and a team whose daily workflow lives in a search UI rather than in SQL. Before deciding, test your real queries. If most of them return ranked lists of documents for people to read, a search engine is likely the right primary store for that data.

Some teams run both, and that is a valid design rather than a failure. A common split keeps long-term event storage, aggregation and dashboards in ClickHouse while a search engine serves investigation views or customer-facing search over a smaller, curated subset. The decision points to check are which languages and analyzers your queries need, whether ranking matters to users, how much duplicate ingestion and storage the second system adds, and who will operate it. Confirm the tokenizer and function behavior against the release you plan to run, since text index coverage changes across ClickHouse versions. Ask whether the team would miss the search UI or the SQL access more if it were removed, because that answer usually settles the question.

Questions teams ask before adding text search to ClickHouse

Does ClickHouse text search replace OpenSearch for our logs?

Not in every case, so decide per workload. ClickHouse text indexes fit searches that combine keyword matching with SQL aggregations, filters and joins over structured telemetry you already keep in ClickHouse. OpenSearch remains the better fit when you need relevance ranking, rich analyzers, its plugin ecosystem or existing dashboards and alerting built on it. Write down your ten most important search and analytics queries, run them against both engines in a test environment, and compare results, latency and operating effort. Confirm the exact function and tokenizer support in your target ClickHouse version before you commit to a replacement.

What does adding text search to ClickHouse cost?

Cost has three parts: the design and build work, the extra compute and storage the indexes consume, and the ongoing operations. Engagement fees depend on the written scope, including how many tables, query shapes and tokenizer choices are in play. Infrastructure cost depends on how much text you index and how long you retain it. Ask any partner to quote these separately, state which items are one-time and which recur, and name who pays for test clusters. DeployOpen does not publish a price list, and a quote follows from the agreed scope.

How long does a text search pilot take?

The duration depends on the data and the query list more than on the feature itself. The main drivers are the volume of text to index, how many distinct search patterns must work, whether you need a new tokenizer strategy, and how quickly your team can provide representative data and a test cluster. A pilot usually covers one table, a defined set of queries, index build measurement and a restore check. Ask for phases, entry and exit criteria and a fixed timeline in the written scope before work starts. Avoid planning around any duration that a vendor has not put in writing.

What must our team provide before a text search build starts?

Three inputs decide whether the pilot produces useful answers. First, representative data: a sample that matches production in volume, field shape, language mix and noise, not a tidy extract. Second, query samples: the searches analysts and on-call engineers really run, with expected results and acceptable response times. Third, a test cluster sized close to production, with the same ClickHouse version you plan to run. Also provide access to your schema, retention rules and an owner who can approve results. The pre-production guidance in the ClickHouse production FAQ recommends testing with representative data and queries, which is the same principle here.

Can our own engineers build text search in ClickHouse without a partner?

Yes, if your team already runs ClickHouse in production and has time to test properly. The work is manageable for a team comfortable with table design, the MergeTree family and SQL, but it still needs a test environment, query benchmarking and a restore test. Teams usually struggle with tokenizer choices, index build cost on large tables and deciding which queries the index actually serves. A partner is most useful when the same team must also handle replication, backups, security hardening and upgrades on a tight schedule. Ask yourself which of those skills you can staff for the full project.

How do text indexes affect ingest speed and storage?

Text indexes add work at write time, because each inserted part must build index structures for the text columns you choose. The impact grows with the amount of text per row, the number of indexed columns and the insert batch pattern. Storage grows with the index, so the size depends on your data and tokenizer settings, not on a general rule. Measure both on your sample data: insert throughput at expected peak, merge behavior, disk use per day of retention and query latency after a bulk load. Keep indexed columns narrow when the query list allows it, and check the changelog for index behavior changes before upgrading.

Which queries will actually use the text index?

Only the query functions and predicates that ClickHouse supports for the index will use it, so check that list against your real queries before designing the schema. Queries that filter on a column that is not indexed, or wrap the indexed column in an expression the optimizer cannot push down, will fall back to scanning. Run EXPLAIN on each query in the pilot and confirm the index is used, then measure latency on production-sized data. Keep the list of supported functions in your design document, and retest after each ClickHouse upgrade because support can change between versions.

How DeployOpen scopes and delivers ClickHouse text search

DeployOpen plans and builds text search in ClickHouse inside infrastructure you control, starting from the log and document data you need to search and the queries your teams run. Each engagement records which text index, tokenizer and query functions are in use, what the build costs in storage and time, and what remains the job of a dedicated search engine.

How we scope

Written scope lists the text sources, fields and retention periods to be indexed for search.
Scope records the queries teams run today and the tokenizer each one needs.
Quote shows whether a second search engine is in scope or ruled out for these workloads.

How we build

Text indexes are defined on a pre-production copy with representative data and real query patterns.
Index build cost is measured on ingest and merges before the design is accepted.
Replicated tables are configured so text indexes are present on every replica.

How we hand over

Documentation covers tokenizer choices, index definitions, query examples and known limits.
Runbooks describe how to rebuild or drop an index and how to check its health.
Backup and restore tests confirm the indexed tables recover, with results recorded for your team.

How we support

Ongoing support is available as a retainer for index tuning and version upgrades.
Upgrade reviews check the ClickHouse changelog for changes that affect text index behavior.
Your team receives a re-test plan whenever the data volume or query mix changes significantly.

Full-text search in ClickHouse with text indexes