# Severe Performance Degradation After Adding High-Cardinality Text Field to Large Index **URL:** \ **Category:** Elasticsearch\ **Created:** [May 27, 2025, 7:52am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571 "2025-05-27T07:52:22Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [May 27, 2025, 7:52am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/1 "2025-05-27T07:52:22Z")
I'm experiencing a significant performance issue after adding a new multi-valued text field to a large Elasticsearch index and would appreciate insights from anyone who has faced similar challenges. Setup: - Elasticsearch 8.13.0, 4-node cluster - Index: 400M documents, 18 shards (~100GB to 150GB per shard) - Routing by a specific field let's say routing\_id (there are thousands of routing\_id) - New field: names (text with edge n-gram analyzer, 2-20 chars) - Multi-valued: 3-4 entries per document on average Problem: Initially tested the field on 150K documents with excellent performance (milliseconds). After backfilling all 400M documents, query performance degraded catastrophically from milliseconds to 10+ seconds. Mapping: ```auto { "mappings": { "properties": { "routing_id": {"type": "keyword"}, "unique_id": {"type": "keyword"}, "names": { "type": "text", "analyzer": "edge_ngram_analyzer", "search_analyzer": "standard", "fields": { "keyword": {"type": "keyword"} } } } }, "settings": { "analysis": { "analyzer": { "edge_ngram_analyzer": { "type": "custom", "tokenizer": "edge_ngram_tokenizer", "filter": ["lowercase"] } }, "tokenizer": { "edge_ngram_tokenizer": { "type": "edge_ngram", "min_gram": 2, "max_gram": 20, "token_chars": ["letter","digit"] } } } } } ``` Query Pattern: ```auto GET index/_search?routing=123 { "size": 0, "query": { "bool": { "filter": [ {"term": {“routing_id”: 123}}, {"match": {“names”: {"query": "searchterm", "fuzziness": "1"}}} ] } }, "aggs": { "distribution": { "terms": {"field": "names.keyword", "size": 100}, "aggs": { "unique_count": {"cardinality": {"field": “unique_id"}} } } } } ``` Goal: I'm implementing an autocomplete feature for names with the following requirements: 1. **Interactive Autocomplete** : As users type names (e.g., “Joh”), show matching names (e.g., “Johannes, “John Borris”, “Some other John”) 2. **Document Counts** : For each suggested name, display how many documents exist (need to count only the unique ones based on a field called unique\_id) 3. **Substring Matching** : Users should be able to search for any part of a name, not just prefixes (e.g., “tris” should match “Dimitris”) or the second word of a name (e.g. “Makr” should match “Dimitris Makris”) 4. **Routing Isolation** : Results must be filtered by routing\_id 5. **Performance Requirements** : Sub-second response time for interactive user experience Analysis: Despite routing and filtering to ~5,000 documents or so, performance is poor (like 10 seconds). I believe this is due to term dictionary size explosion - the field now contains terms from 400M × 3-4 = ~1.2B entries, and ES must traverse the entire term dictionary before applying filters. Also the nature of the field is high cardinality which I assume doesn't help on this situation. Attempted Optimizations: - Replacing match+fuzziness with prefix queries - Adjusting refresh intervals - execution\_hint: "map" for aggregations Planned Optimizations: - Reindexing in order to reduce the size of each shard (but I'm not sure if the performance will become acceptable) Questions: What's the best architectural approach for this type of "autocomplete with aggs" functionality at scale? Should I: - Optimize the current approach? - Move to a separate autocomplete-specific index? - Use completion suggester with some additions maybe? - Consider a different approach entirely? Any insights on handling autocomplete functionality at this scale with the above-mentioned functionality would be greatly appreciated! ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 27, 2025, 7:57am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/2 "2025-05-27T07:57:34Z")
Welcome! > [@Dimitris\_Makris](#): > > Reindexing in order to reduce the size of each shard (but I'm not sure if the performance will become acceptable) Yes. That's the first thing I'd do. Have a look at [Size your shards | Elastic Docs](https://www.elastic.co/docs/deploy-manage/production-guidance/optimize-performance/size-shards) > - Very large shards can slow down search operations and prolong recovery times after failures. > - Aim for shard sizes between 10GB and 50GB What would be interesting in your case would be to find "when" it starts to degrade in terms of performances. After how many docs, for what exact size... As you are doing full text search here (it's not a time series dataset), I'd keep the shard size low, like 10gb. As you are using specific routing keys, do you have shards much bigger than others? What is the `GET _cat/shards?h` command returns for your index? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 27, 2025, 8:01am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/3 "2025-05-27T08:01:38Z")
> [@Dimitris\_Makris](#): > > Elasticsearch 8.13.0, 4-node cluster What is the hardware specification of this cluster? Are you using local SSDs? I would also recommend upgrading to the latest 8.18 version as there are have been some performance improvements made that I am not sure are in your version. > [@Dimitris\_Makris](#): > > Index: 400M documents, 18 shards (~100GB per shard) This is a quite large shard size and can affect performance. I would recommend trying to reindex the data into a new index with 80-100 primary shards. > [@Dimitris\_Makris](#): > > Routing by a specific field let's say routing\_id (there are thousands of routing\_id) Using routing is the right way to go as long as each document only has a single routing value. This will force all documents that contain a specific routing value to be located in the same shard and when you specify routing at query time only a single shard will need to be searched, which together with smaller shards should help improve latency and increase supported query throughput. ---
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [May 27, 2025, 8:16am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/4 "2025-05-27T08:16:52Z")
Hi David, thank you very much for your reply. > [@dadoonet](#): > > What would be interesting in your case would be to find "when" it starts to degrade in terms of performances. After how many docs, for what exact size... Actually the suggestion on "when" it starts to degrade is super interesting. I will definitely look into this. > [@dadoonet](#): > > As you are using specific routing keys, do you have shards much bigger than others? What is the `GET _cat/shards?h` command returns for your index? Regarding the shard size.. The shard sizes range from 100 to 150 GB ---
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [May 27, 2025, 8:22am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/5 "2025-05-27T08:22:48Z")
Hi Christian, thank you very much for your reply. > [@Christian\_Dahlqvist](#): > > This is a quite large shard size and can affect performance. I would recommend trying to reindex the data into a new index with 80-100 primary shards. I have a similar view regarding the number of shards, but I'm unsure how to distribute them across nodes. Is there a hard limit on the number of shards per node? > [@Christian\_Dahlqvist](#): > > Using routing is the right way to go as long as each document only has a single routing value. This will force all documents that contain a specific routing value to be located in the same shard and when you specify routing at query time only a single shard will need to be searched, which together with smaller shards should help improve latency and increase supported query throughput. I actually use routing (I updated the query to reflect it), but in that case I also use it for filtering. Imagine that each shard may contain ~ 50k routing keys and approximately 22 million documents. > [@Christian\_Dahlqvist](#): > > I would also recommend upgrading to the latest 8.18 version as there are have been some performance improvements made that I am not sure are in your version. I will also look into the newer version of ES. 🙏 ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 27, 2025, 9:14am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/6 "2025-05-27T09:14:15Z")
> [@Dimitris\_Makris](#): > > I have a similar view regarding the number of shards, but I'm unsure how to distribute them across nodes. Is there a hard limit on the number of shards per node? The limit per node is 1000, so you should be fine. Make sure you set the number of replica shards to 1 to reduce overall shard count. > [@Dimitris\_Makris](#): > > I actually use routing (I updated the query to reflect it), but in that case I also use it for filtering. Imagine that each shard may contain ~ 50k routing keys and approximately 22 million documents. Routing can result in uneven shard sizes depending on the distribution of routing keys, but efficiency generally increases with the primary shard count. As David suggested, try to keep the shard size small as it is a search use case. 10GB average size would mean around 180 primary shards. That is 360 total shards, or 90 per node, which is no problem. ---
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [May 27, 2025, 9:40am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/7 "2025-05-27T09:40:27Z")
> [@Christian\_Dahlqvist](#): > > Routing can result in uneven shard sizes depending on the distribution of routing keys, but efficiency generally increases with the primary shard count. I considered routing on specific routing\_key as the best possible approach, since all the functionalities are targeted to one routing\_key and there is no possibility that I might need to mix documents from more routing keys. So I kind of accepted the drawback of uneven shard sizes. But if there is a better approach, that I'm not aware of, I would definitely consider it. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 27, 2025, 9:48am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/8 "2025-05-27T09:48:25Z")
No, that is the approach I would recommend. I suspect you will see improved performance if you upgrade Elasticsearch and reduce the shard size. As the data set is large it is possible that you may also suffer from resource constraints in the cluster, e.g. disk I/O, but as you have not provided any details about the cluster that is hard to tell. It is worth monitoring though. ---
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [May 27, 2025, 11:25am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/9 "2025-05-27T11:25:54Z")
> [@Christian\_Dahlqvist](#): > > What is the hardware specification of this cluster? Here are my hardware specifications: - Total Memory: 201 GiB (1 + 50×4 = 201 GiB) - Total CPU: 32.5 cores (0.5 + 8×4 = 32.5 cores) - Total Storage: 10,001 GiB (1 + 2,500×4 = 10,001 GiB) - Total Nodes: 5 (1 master + 4 data nodes) ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 27, 2025, 11:35am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/10 "2025-05-27T11:35:14Z")
> [@Dimitris\_Makris](#): > > Total Nodes: 5 (1 master + 4 data nodes) Running a cluster with a single master eligible node is very bad as it does not offer any resiliency. If you in total have 5 nodes I would have 1 dedicated master node as you do now and 4 master/data nodes. > [@Dimitris\_Makris](#): > > Total Storage: 10,001 GiB (1 + 2,500×4 = 10,001 GiB) What type of storage is this? Local SSD? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 27, 2025, 11:40am UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/11 "2025-05-27T11:40:01Z")
> [@Christian\_Dahlqvist](#): > > Running a cluster with a single master eligible node is very bad as it does not offer any resiliency. If you on total have 5 nodes I would have 1 dedicated master node as you do now and 4 master/data nodes. Yes! More on this at: [Node roles | Elastic Docs](https://www.elastic.co/docs/deploy-manage/distributed-architecture/clusters-nodes-shards/node-roles#master-node-role) ---
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [May 27, 2025, 12:02pm UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/12 "2025-05-27T12:02:22Z")
> [@Christian\_Dahlqvist](#): > > What type of storage is this? Local SSD? The master node is **HDD** (standard-rwo) and the other nodes are local **SSD** (premium-rwo). ---
**Author:** ![Dimitris\_Makris](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dimitris_makris/32/143346_2.png) [@Dimitris\_Makris](https://discuss.elastic.co/u/Dimitris_Makris)\ **Post date:** [December 15, 2025, 9:23pm UTC](https://discuss.elastic.co/t/severe-performance-degradation-after-adding-high-cardinality-text-field-to-large-index/378571/13 "2025-12-15T21:23:59Z")
## **Resolution Update - Term Dictionary Explosion** **SOLUTION** : Oversized shards (100-150GB, ~20M docs) caused term dictionary explosion in the high-cardinality `names` field after full 400M doc backfill​ ## Root Cause Analysis Elasticsearch maintains one **Term Dictionary per field per shard**. The edge n-gram analyzer (2-20 chars) generated massive term growth: ```auto 150K docs (initial test) → ~450K-600K name entries → Minimal dictionary growth 400M docs (full backfill) → ~1.2B-1.6B name entries → 2500x+ explosion per shard ``` **Query execution order killed performance** : 1. **Term lookup** scans entire bloated dictionary for n-grams of a search term 2. **Only then** applies `routing_id` filter With 100-150GB shards (vs recommended 10-50GB), dictionary traversal took 10s+ despite routing to single shard.​ ## What Fixed It ```auto BEFORE: 18 shards (100-150GB/shard) AFTER: 200 shards (~10GB/shard) → Sub-second latencies restored + ES 8.18 upgrade for perf gains ``` | Metric | Before | After | | --- | --- | --- | | Query time | 10s+ | \<800ms | | Shard size | 100-150GB | ~10GB |