Full-text search

Full-text search indexes text columns so queries can find records by words, phrases, and approximate text matches without scanning every value as plain strings.

Use it for log messages, trace messages, support tickets, documents, event properties, or any table where users search by text rather than exact IDs. The OpenTelemetry catalog uses full-text indexes on message fields so logs and spans can be searched quickly.

Create an index

Create a full-text index on the column you search most often:

Copy
1
2
3
CREATE INDEX logs_message_fts
ON opentelemetry.main.logs
USING TNVYX(message);

Index names must be unique in the table. Build indexes on columns with meaningful free-form text; low-cardinality fields such as status codes are usually better handled with normal filters.

Query with filters

Combine text search with time and service filters so the engine can narrow the scan before ranking or returning matches:

Copy
1
2
3
4
5
6
7
8
9
10
11
SELECT
  timestamp,
  service_name,
  severity_text,
  message
FROM opentelemetry.main.logs
WHERE timestamp >= now() - INTERVAL '1 day'
  AND service_name = 'api'
  AND message @@ 'payment timeout'
ORDER BY timestamp DESC
LIMIT 100;

When to use it

Use full-text search when exact equality, LIKE, or JSON field filters are too narrow or too slow for the question. Keep structured predicates in the query whenever possible; they make text search more selective and easier to reason about.

Learn more

• OpenTelemetry: logs and traces stored in the lakehouse.
• SQL Explorer: test search queries interactively.
• SQL engine: DuckDB SQL support in Altertable.