# Configuring Full-Text Indexes This topic provides information on how to tune ingest and search on tables with full-text version 2 indexes. ## Tune Ingest on Tables with Full-Text Indexes This section focuses on tuning tables with full-text version 2 indexes for high‑throughput ingest. In particular, the use of the full-text index threadpool to improve ingest performance is discussed. ## Key Recommendations * Set the number of [partitions](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/cluster-components.md) in a database based on query-parallelism needs, not by ingest throughput concerns. * Use the full-text index threadpool to maintain ingest throughput at lower partition counts. ## Partition Impact * Fewer partitions per leaf (more cores per partition) is generally recommended for better query performance with [flexible parallelism](https://docs.singlestore.com/#section-idm4114195857326106.md). * More partitions create more background flusher threads, which may help ingest throughput. However, SingleStore recommends choosing the number of partitions based on query needs, unless ingest performance is of primary importance. > **📝 Note**: SingleStore recommends addressing ingest issues by using the [full-text index threadpool](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-full-text-search/#section-id235489669550416.md) and periodic calls to [OPTIMIZE TABLE ... FLUSH](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/optimize-table.md) to help the flusher keep up. Keep the partition count aligned with query needs. Refer to [Tune Ingest](https://docs.singlestore.com/#section-id2354895955696.md) for more information. ## Full-Text Index Ingestion and Creation Process Full-text indexes are built using the following process. When rows are inserted into a table with a version 2 full-text index: 1. The rows are inserted into the table's rowstore segments. 2. The rowstore segments are flushed to disk to create columnstore segments. 3. Full-text indexes are created when the segments are flushed. The flushes may occur due to: * [Background flushing](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/managing-columnstore-segments/#section-idm234755125892086.md). * Explicit calls to [OPTIMIZE TABLE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/optimize-table.md). * [Large inserts that write straight to disk](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/writing-columnstore-data.md). * [Inserts helping with flushing (on small inserts)](https://docs.singlestore.com/db/v9.1/create-a-database/configuring-the-columnstore-to-work-effectively/#UUID-dc9d1ea3-4e9e-c4d5-7149-8c7e5f3628d1.md). Version 2 full-text indexes are built for each column with a full-text index defined on it when the associated row segment is flushed to disk and the [columnstore segment](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/how-the-columnstore-works.md) is created. ## Configure Index Build Full-text index build is affected by several mechanisms including the full-text index threadpool, the full-text index merger, the flush threads, and the background merger. * **Full-Text Index Threadpool**: When the [full-text index threadpool](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-full-text-search/#section-id235489669550416.md) is enabled with `fts2_use_index_threadpool`, each indexing request is decomposed into smaller tasks that are run by a threadpool; otherwise a single thread handles an entire segment’s indexing. * **Full-text index merger**: The [full-text index merger](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-full-text-search/#section-id235368504458.md) combines per-segment full-text indexes to create cross-segment full-text indexes. The merged indexes can improve the performance of full-text search queries by reducing the number of indexes that must be examined for a query. The full-text index merger is enabled with `enable_fts2_merger`. * **Concurrent Flush Threads**: Multiple flush threads can send concurrent indexing requests to JLucene (SingleStore's Lucene‑based full-text service). The concurrency of these requests is bounded by `fts2_max_connections`. From the engine’s perspective, each indexing request (sent by a flush thread) is synchronous. * **Background Merger**: The background merger re‑indexes full-text data when it merges columnstore segments. This background merging uses compute resources for full-text indexing. * If flushing is too frequent, which may be caused by overly frequent calls to `OPTIMIZE TABLE … FLUSH`, or low settings of `columnstore_flush_bytes` or `columnstore_segment_rows`, the engine creates many small segments, which increases background re‑indexing work. Refer to [Tune Ingest](https://docs.singlestore.com/#section-id2354895955696.md) for information on setting these variables. ## Key Engine Variables This table describes the function of key engine variables that impact full-text index creation. | Engine Variable / Command | Description of Function | | ---------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fts2_use_index_threadpool` | Enables parallelization of indexing tasks for a given flush request. Reduces time spent per index creation. | | `fts2_index_threadpool_size` | Controls the size of the full-text indexing thread pool. Is effective only when the thread pool is enabled. | | `enable_fts2_merger` | Enables the full-text index merger. | | `fts2_merge_soft_limit_mb` | Specifies the upper limit on the size of an index that can be merged by the full-text index merger. This limit is not strictly enforced and may be exceeded in some cases. | | `fts2_max_connections` | Upper bound on concurrent JLucene indexing requests from flush threads; increase carefully to match CPU budget and avoid oversubscription. | | `columnstore_flush_bytes` | Threshold for flushing rowstore‑backed data to columnstore. | | `columnstore_segment_rows` | Alternative threshold for flushing rowstore-backed data to columnstore that triggers flushing by row count; coordinate with flush bytes to control segment sizes and flush cadence. | | `OPTIMIZE TABLE … FLUSH` | Manual flush to help background flusher keep up with ingest. Use judiciously; running too often creates many small segments (worse query performance and more background merging - and hence background full-text re-indexing). | ## Tune Ingest 1. Start with fewer partitions aligned to query performance (e.g., >1 core/partition), then turn on the full-text index threadpool. Verify ingest uniformity and determine whether throttling delays ingest as described in the [Monitor Ingest](https://docs.singlestore.com/#section-id235489602213641.md) section. 2. If ingest still falls behind, schedule periodic calls to [OPTIMIZE TABLE FLUSH](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/optimize-table.md) during ingest to allocate more resources to flushing. Tune the frequency to avoid producing many small segments. 3. Increase `columnstore_flush_bytes` to form larger segments and [reduce flush overhead](https://docs.singlestore.com/db/v9.1/create-a-database/configuring-the-columnstore-to-work-effectively/#UUID-dc9d1ea3-4e9e-c4d5-7149-8c7e5f3628d1.md). 4. Calibrate `fts2_max_connections` so multiple flush threads can index concurrently without CPU oversubscription. Watch the Java (JLucene) CPU to ensure indexing is active and healthy. 5. Keep the `fts2_index_threadpool_size` fixed while testing different partition counts to determine whether the bottleneck is in flushing or indexing. If higher partitions help while the threadpool is turned off, the system is flushing‑bound, and indexing is not throttling ingest. SingleStore recommends increasing flush resources (by increasing the `OPTIMIZE TABLE ... FLUSH` cadence) instead of increasing the partition to node ratio, which permanently fractures data. 1. SingleStore recommends starting with `fts2_index_threadpool_size` set to the number of cores on a leaf node. 6. Once ingest is stable, iterate towards fewer partitions by relying on the indexing threadpool and a measured `OPTIMIZE TABLE ... FLUSH` schedule to preserve query performance while sustaining ingest throughput. ## Monitor Ingest and Obtain Diagnostics * Track pending flush pressure by observing columnstore ingest management metrics such as `columnstore_ingest_management_estimated_segments_to_flush`. A sharp rise in the value of this variable towards a threshold (e.g., around cores × 10) typically precedes throttling. * Compare full-text index sizes and counts per segment using the following query. ```sql SELECT database_name, table_name, node_id, size FROM information_schema.MV_COLUMNSTORE_FILES WHERE file_type = 'FULLTEXT'; ``` * Watch the CPU usage of the Java process (JLucene) during flush; high utilization indicates indexing is active and can explain differences across runs. ## Full-Text Index Management In SingleStore, a full-text index is built for each columnstore segment. A full-text index for a segment may be created either when a segment in a table with a full-text index is flushed to disk, or when a full-text index is created with an `ALTER` command. ## Monitor Index Build Status For large tables, there are often multiple segments per partition. The progress of index building can be monitored with the [SHOW FULLTEXT INDEX STATUS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-fulltext-index-status.md) command or by querying the [MV\_FULLTEXT\_INDEX\_STATUS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/mv-fulltext-index-status.md) information schema view. ```sql SHOW FULLTEXT INDEX STATUS FOR ; ``` ```sql SELECT * FROM information_schema.MV_FULLTEXT_INDEX_STATUS WHERE DATABASE_NAME = "" AND TABLE_NAME = ""; ``` **Note**: While full-text indexes are initially created per-segment, those indexes may later be [merged](https://docs.singlestore.com/#section-id235368504458.md). ## Broken Indexes In rare situations, an index creation may fail due to issues such as insufficient disk space or memory. If an index creation failure occurs, the index will be flagged as "broken" and an error message is returned when a user searches against the broken index. To identify broken indexes, use the [SHOW FULLTEXT INDEX STATUS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-fulltext-index-status.md) command or query the [MV\_FULLTEXT\_INDEX\_STATUS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/mv-fulltext-index-status.md) information schema view. ```sql SHOW FULLTEXT INDEX STATUS FOR ; ``` ```sql SELECT * FROM information_schema.MV_FULLTEXT_INDEX_STATUS WHERE DATABASE_NAME = "" AND TABLE_NAME = "" AND BROKEN_PERCENT > 0; ``` To attempt to fix a broken index, run the `OPTIMIZE TABLE … FIX_FULLTEXT` command. ```sql OPTIMIZE TABLE FIX_FULLTEXT; ``` However, full-text indexes cannot be repaired by the system in all cases, including: * There is an invalid path for the full-text JLucene service. * The Java runtime used by the JLucene service runs out of memory. Consider increasing the value of the `fts2_max_memory_mb` global variable if this occurs. * The size of the data being exchanged between the engine and the JLucene process is too large. In this case, consider increasing the value of the `fts2_max_service_buffer_mb` global variable. * A term to be indexed is longer than the maximum length of `32766`. This is a limitation of JLucene and may be addressed by truncating or breaking up tokens in the data itself. ## Debug Full-Text Index Tokens To debug full-text search query behavior, it can be helpful to understand how the input documents are broken into tokens. The tokens that will be generated for a full-text index can be viewed using the [ANALYZE FULLTEXT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/full-text-search-functions/analyze-fulltext.md) command. Use the specification from the analyzer key for `INDEX_OPTIONS` from the relevant index definition as the `OPTIONS` for the `ANALYZE FULLTEXT` command. ## Memory Usage The `Alloc_fts2_svc` status variable is the number of allocated bytes (out of the total `max_memory`) that is attributable to the next-generation (`VERSION 2`) full-text subprocess. View the value of `Alloc_fts2_svc` on the leaf nodes with the `SHOW LEAF STATUS EXTENDED` command. ```sql SHOW LEAF STATUS EXTENDED LIKE '%fts%'; ``` The value of `Alloc_fts2_svc` on the aggregator nodes can be viewed with the `SHOW STATUS EXTENDED` command. The `Alloc_fts2_svc` variable will have a value on the aggregator nodes if a search has been run using the `BM25_GLOBAL` search function. Thus, the following command will return results only if a `BM25_GLOBAL` search has been used. ```sql SHOW STATUS EXTENDED LIKE '%fts%'; ``` ## Full-Text Index Merger When a full-text index is built, a table-level index is created for the text column. This table-level index is composed of smaller pieces; one per-segment index per [columnstore segment](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/managing-columnstore-segments.md). The cost of full-text search is driven by the number of indexes searched. To improve performance, the full-text index merger combines per-segment full-text indexes to create cross-segment full-text indexes. These cross-segment indexes improve the performance of full-text search queries by reducing the number of indexes that must be examined. The full-text index merger: * Is enabled with the `enable_fts2_merger` engine variable. ```sql SET GLOBAL enable_fts2_merger = ON; ``` * Can merge any number of smaller indexes. * Has an upper limit on the size of an index that can be merged, controlled by `fts2_merge_soft_limit_mb`. This limit is not strictly enforced and may be exceeded in some cases. * Is used when the query is able to take advantage of the `ORDER BY LIMIT` optimization. SingleStore recommends using the largest possible `fts2_merge_soft_limit_mb` and nodes big enough to handle that size of index. The full-text index merger removes the need to specify larger segments. With the full-text index merger, you can use smaller segments, which improve segment elimination, while maintaining the benefits of larger segments for full-text search. The full-text index merger automatically runs in the background to continuously improve search performance. In addition, the full-text index merger is also run when the `OPTIMIZE TABLE ... FULL` command is run. ```sql OPTIMIZE TABLE FULL; ``` ## Full-Text Index Threadpool The full-text index threadpool uses multiple threads to build full-text indexes. The threadpool parallelizes index building within a single segment flush request. When using the threadpool, a full-text index (for one segment) is built using multiple threads. Thus using the threadpool can reduce index build time and reduce the impact of index builds on data ingest. Two engine variables are available to configure the threadpool. The goal is to configure the threadpool so that the rate of flushing segments to disk matches the rate of ingest. That is, set the thread pool parallelism high enough so that the index builds match the rate of ingest. | Engine Variable | Description of Function | | ---------------------------- | ----------------------------------------------------------------------------------------------------------- | | `fts2_use_index_threadpool` | Enables parallelization of indexing tasks for a given flush request. Reduces time spent per index creation. | | `fts2_index_threadpool_size` | Controls the size of the full-text indexing thread pool. Is effective only when the thread pool is enabled. | SingleStore recommends setting `fts2_index_threadpool_size` to the number of cores on a leaf node. ## Full-Text Search Performance Following are a series of considerations and optimizations to consider for full-text search performance. ## Query Optimization ## Indexing Limited Full-Text Columns For full-text indexes, the number of columns that are indexed can affect search performance. Only index the columns that will be searched. ## Full-Text ORDER BY … LIMIT Optimization There is a significant optimization that pushes an `ORDER BY…LIMIT` clause down into the Java Lucene process if a query is structured in a certain way.  Depending on the size of the data, this optimization can reduce a query's time from minutes to seconds. For this optimization to work, the query must follow these rules: * The `ORDER BY` clause must use `DESC` sort order. * There must be only one full-text search function (`MATCH`, `BM25`, or `BM25_GLOBAL`) in the query. The full-text portion of a query can often be refactored out of a query to meet these rules and employ the optimization as shown in the following example. **Original query:** ```sql SELECT ucid, company_name, fc.company_description, MATCH (TABLE fc) AGAINST ('company_technologies:("font awesome" OR "open graph")') AS _match FROM Company c JOIN FTS_Company_new fc USING (ucid) JOIN Person p USING(ucid) ANTI_JOIN GlobalExclude_global ge ON ge.PersonId = p.upid WHERE _match GROUP BY ucid ORDER BY _match DESC LIMIT 25 OFFSET 0; ``` Rewritten query that benefits from the optimization: ```sql WITH _fts AS ( SELECT ucid, company_description, MATCH (TABLE fc) AGAINST ('company_technologies:("font awesome" OR "open graph")') AS _match FROM FTS_Company_new fc WHERE _match ORDER BY _match desc limit 25 ) SELECT ucid, company_name, fc.company_description, fc._match FROM Company c JOIN _fts fc USING (ucid) JOIN Person p USING(ucid) ANTI_JOIN GlobalExclude_global ge ON ge.PersonId = p.upid WHERE fc._match GROUP BY ucid ORDER BY fc._match DESC LIMIT 25 OFFSET 0; ``` Factoring the full-text search away from the joins into a CTE allows the optimization to work. ## Add Attributes Used in Filters to Full-Text Index In full-text search (FTS) v2, the Lucene engine is decoupled from the main database engine and runs in a co-process.  While this architecture allows SingleStore to surface the latest and greatest FTS features available in Lucene, it limits the ability to optimize filtering when both FTS matches and other non-FTS criteria are present. This behavior is because the Lucene co-process isn't deeply integrated into the SingleStore engine. Lucene must first return all matches, to which other filters can be applied and limits that may be present in the query.  This causes overhead, particularly if there are a lot of FTS matches. In queries with filters on multiple attributes, consider adding attributes that are used in filters to the full-text index so that the full `WHERE` clause can be moved into the Lucene expression. This enables optimization (within Lucene) and eliminates the overhead of passing all matches from the Lucene process back to the SingleStore process. ## Caching ## Full-Text Index Cache The full-text index cache stores full-text index data in memory to improve search performance. Use the full-text index cache when the working set is small enough to fit in the [blob cache](https://docs.singlestore.com/db/v9.1/manage-data/setting-the-blob-cache.md). Enable the full-text index cache with the `fts2_cache_enable` engine variable. ```sql SET GLOBAL fts2_cache_enable = ON; ``` The `fts2_cache_blob_memory_mb` engine variable controls the size of the cached blobs. Increasing the value of this variable can improve search performance at the expense of more memory and disk consumption. Stop the full-text service using the `FULLTEXT SERVICE STOP` command after changing the value of `fts2_cache_blob_memory_mb` to ensure the change takes effect. The full-text service restarts automatically when needed again. ## Statistics Cache in Full-Text Search Service The statistics cache in the full-text V2 service is designed to store frequently accessed term and collection statistics used during full-text scoring computations, particularly for BM25 and BM25\_GLOBAL queries. These scoring models depend heavily on term-level and document-level statistics that are typically computed on-the-fly. When workloads involve repetitive or similar query patterns, recomputing these statistics repeatedly can become a performance bottleneck. By enabling and tuning the full-text statistics cache, the system can reuse previously computed statistics, leading to improved query throughput and lower response latency. However, as with any cache mechanism, the full-text statistics cache requires careful configuration to balance performance benefits against memory usage. Increasing the cache size may result in lower latencies at the cost of higher memory usage. Enable the full-text statistics cache with the following command: ```sql SET GLOBAL fts2_stats_cache_enable = TRUE; ``` The following engine variables are used to configure the full-text statistics cache: * `fts2_stats_cache_max_size`: Specifies the maximum number of index blobs (segments) that can be stored in the statistics cache at any given time. Helps manage memory usage by limiting the number of cached statistics, ensuring the system does not overuse memory. * `fts2_stats_cache_max_terms`: Specifies the maximum number of term statistics that can be cached for each index blob (segment). Controls the granularity of cached data, balancing memory consumption against query performance for term-based scoring models like BM25. ## Warm the Cache for Full-Text Search The disk cache can be warmed for a full-text search by running a query such as the following which will cause the full-text indexes to be downloaded into the local disk cache. ```sql SELECT COUNT(*) FROM table_name WHERE MATCH(TABLE table_name) AGAINST ('valid_column:random_term'); ``` ## Resource Management ## Throttle Full-Text Search If full-text search is taking too much CPU time, full-text search can be throttled by reducing the value of the `fts2_max_connections` engine variable. The value of this variable, that is the number of connections, is roughly equal to the number of CPUs on each leaf to use. That is, if `fts2_max_connections` is set to `6`, that means that approximately 6 CPUs on each leaf will be used. ## Other Strategies To get the best performance, get creative with application and even data changes. For example, can the database size be reduced? You may be able to eliminate older or less important data. Other ways to improve performance may be to add more hardware, or to right-size the hardware to the application and data needs. Another is to change the queries, for example, simplify the FTS portion of the queries, or break the queries into pieces. ## Segment Size Impacts Segment size impacts search and ingest. Tradeoffs include: * Large segments typically improve search speed because the cost of searching a large segment is only slightly more than the cost of searching a small segment (search time increases logarithmically with segment size). * Large segment sizes are typically good for Queries Per Second (QPS) and for search performance. * Smaller segments may be appropriate for workloads with updates or workloads with highly selective queries. * Queries with highly selective `WHERE` filters may benefit from smaller segments because segment elimination is applied before full-text index searches. * Large segments improve index search performance; small segments improve segment elimination. When indexes are updated regularly, when latency is important and high parallelism is useful, or when there are highly selective queries, appropriate segment size needs to be considered. There is a tradeoff between search performance and index update rate. Large segments improve index search performance while small segments improve index update rate. Thus there is a tradeoff between search speed and update speed. Segment size in SingleStore is managed through the variables `internal_columnstore_max_uncompressed_blob_size` and `columnstore_segment_rows`. > **📝 Note**: Note: Before reducing segment size to increase the ingest rate, turn on the full-text index threadpool. ## Segment Elimination Segment elimination is the process whereby the system avoids scanning segments that do not meet the filter in the query. With highly selective filters, segment elimination will result in much less data being scanned and can significantly improve performance. In this case, smaller segments mean less data is scanned (small segments are more likely to be able to be 'eliminated' and a smaller number of rows are read from the segments that pass segment elimination). Thus, smaller segments are good for performance for queries with highly selective filters that are not for full-text search. An example of such a filter is a date range filter in the `WHERE` clause of a query. That is, queries with highly selective predicates may benefit from an increased number of partitions and segments. The increased number of smaller partitions will enable more segment elimination. Refer to [Choosing a Columnstore Key](https://docs.singlestore.com/db/v9.1/create-a-database/optimizing-table-data-structures/choosing-a-columnstore-key.md) for information on segment elimination. ## Parallel Scan and Flexible Parallelism SingleStore is a powerful parallel database which supports a spectrum of parallelism; different levels of parallelization are effective for different types of workloads. In general, to optimize for throughput or high queries per second and for workloads with many relatively small queries, flexible parallelism should be turned down. To optimize for response time, flexible parallelism should be turned up. ## Flexible Parallelism Flexible parallelism allows multiple threads to scan a single partition, increasing the parallelism (number of cores) used for a query. Flexible parallelism allows all cores to work on a particular query even if there are more cores than partitions. This decreases query latency. More specifically, flexible parallelism allows each core to scan a sub-partition, so multiple threads per query per leaf. This is great for certain workloads, but not for all workloads. If you have multiple queries running concurrently, parallelism may not help. Multiple queries can use multiple cores, so there is less need for parallelism. However, if you have fewer queries running, parallelism can help improve performance. At a high level, if you have multiple concurrent queries, turn down the parallelism, but if you have few large queries running, turn up the parallelism. Flexible parallelism is managed with the engine variable `query_parallelism_per_leaf_core`. Turning down parallelism by decreasing `query_parallelism_per_leaf_core` helps throughput by reducing overhead for starting and synchronizing threads. More importantly, turning down parallelism can also reduce total work by avoiding having multiple threads process the same segment, potentially each doing an index search independently, or doing `ORDER BY ... LIMIT` queries on more threads which could increase the total number of rows decoded and transmitted. This extra decoding and transmission can drive up the total CPU cycles needed to run a query. ## Flexible Parallelism Configuration When doing a parallel (non-indexed) scan: * If (flexible) parallelism is set to one (`query_parallelism_per_leaf_core = 1`), then there will be one thread per CPU (vCPU) core and each thread will scan different sub-partitions in parallel. * If (flexible) parallelism is set lower (`query_parallelism_per_leaf_core < 1`), then fewer threads will be used and each thread will scan multiple sub-partitions. Setting `query_parallelism_per_leaf_core = .01` will give one thread per leaf node, which is ideal for high-throughput search workloads. ## Configure for Large Data For huge data, you may want more than one thread per leaf node to balance response time versus throughput. If throughput is still your top concern, you can still limit the threads per leaf node by setting `query_parallelism_per_leaf_core = 0.01`. ## Configure for Response Time To obtain the best response times for individual queries, use multiple threads per query. Set `query_parallelism_per_leaf_core` at its default value of `1.0` or some relatively high value such as `0.5` or `0.25`. Increasing query parallelism increases parallelism at the cost of thread overhead. Thus, there is a tradeoff between increased parallelism and thread overhead which can be managed by setting query parallelism. *** Modified at: April 1, 2026 Source: [/db/v9.1/developer-resources/functional-extensions/configuring-full-text-indexes/](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/configuring-full-text-indexes/) (An index of the documentation is available at /llms.txt)