Text Search Analytics
End-to-end guide for combining full-text search with OLAP aggregations in Apache Pinot.
This playbook covers workloads that combine full-text search with OLAP-style aggregations — searching logs for error patterns and then aggregating by service, filtering a product catalog by description text and then ranking by sales, or triaging support tickets by keyword and then grouping by severity.
When to use this pattern
Use this playbook when:
Your queries include both free-text predicates (keyword search, phrase match, fuzzy match) and structured filters/aggregations (GROUP BY, SUM, COUNT, time-range filters).
You want a single system for text search and analytics instead of maintaining both Elasticsearch and a separate OLAP store.
Text columns contain natural language (log messages, product descriptions, ticket bodies, user reviews) rather than short categorical values.
You need real-time ingestion of text data with immediate searchability.
If your text columns are short, low-cardinality labels (e.g., status codes, country names), standard inverted indexes are sufficient — you do not need a text index.
Architecture sketch
Log / event stream ──▶ Kafka ──▶ Pinot REALTIME table
│
┌───────┴────────┐
│ Servers with │
│ text index on │
│ message column │
└────────────────┘
│
TEXT_MATCH + OLAP
queries from appPinot supports two text index implementations:
Text index (Lucene-based)
Apache Lucene
Full Lucene query syntax: phrase queries, fuzzy, regex, wildcard, proximity
Native text index
Pinot built-in
Simple keyword/phrase search with lower memory and faster ingestion
This playbook covers both. Start with the native text index for simpler use cases, and switch to Lucene-based if you need advanced query syntax.
Schema
The subject and body columns will carry the text index. They must be declared as STRING type and should not have dictionary encoding (they go in the noDictionaryColumns list).
Table configuration with Lucene-based text index
Configuration highlights
fieldConfigList with indexTypes: ["TEXT"]
Creates a Lucene-based text index on subject and body
encodingType: RAW
Text-indexed columns must use raw encoding, not dictionary
noDictionaryColumns includes text columns
Disables dictionary encoding, which is inefficient for long text
invertedIndexColumns on structured columns
Standard inverted indexes for the non-text filters (severity, service)
Alternative: native text index
For simpler search needs (keyword match, phrase match) with lower resource overhead:
The native text index does not use Lucene and has lower memory overhead, but does not support fuzzy matching, regex, proximity queries, or boosting. See Native Text Index for a comparison.
Query patterns
TEXT_MATCH: keyword search with aggregation
Find tickets mentioning "timeout" and aggregate by service:
Phrase search
Find tickets with the exact phrase "connection refused":
Boolean text queries
Combine text predicates with AND, OR, NOT:
Wildcard and fuzzy search (Lucene index only)
Combining text search with OLAP aggregations
The power of this pattern is running text filters as part of a larger analytical query:
Log analytics variant
For log analytics (e.g., Apache access logs, application logs), the schema typically has a logMessage text column and structured columns extracted at ingestion time:
Use ingestion transformations to extract structured fields from log lines during ingestion, and apply a text index on the raw logMessage for ad-hoc search.
For high-cardinality log data, consider Stream Ingestion with CLP which provides compressed log storage with efficient search.
Operational checklist
Before go-live
Monitoring
Lucene index size on disk: Text indexes can be 1-3x the size of the raw text data. Monitor disk usage per server.
Query latency for TEXT_MATCH queries: Lucene query execution time is included in the server-side query metrics. If P99 spikes, check for expensive wildcard or regex patterns.
Segment flush time: Building text indexes during segment flush takes longer than standard indexes. If flush times grow, reduce
flush.threshold.rows.
Common pitfalls
TEXT_MATCH returns no results
Verify the column has a text index configured (not just inverted index). Check fieldConfigList
High memory usage on servers
Lucene indexes are memory-intensive. Use the native text index if you only need keyword/phrase search
Slow wildcard queries with leading wildcards
Leading wildcards (*error) require scanning the entire term dictionary. Avoid them, or use an FST index for prefix queries
Text index on a low-cardinality column
Use a standard inverted index instead — text indexes are overkill for columns with few unique values
Search relevance ranking needed
Pinot's TEXT_MATCH is a filter, not a scorer. Use Elasticsearch or similar for relevance-ranked search and Pinot for the aggregation layer
Further reading
Last updated
Was this helpful?

