Choosing Between Couchbase Full Text Search and N1QL LIKE/REGEXP for Text Queries

Decision and Constraints

When you need to search text inside Couchbase documents you have two built‑in options: Full Text Search (FTS) and N1QL LIKE/REGEXP. Pick FTS if you need sub‑50 ms latency, relevance scoring, faceting, or language‑specific tokenization on datasets larger than about one million documents. Pick N1QL LIKE/REGEXP for ad‑hoc, low‑volume queries where higher latency is acceptable and you want zero‑maintenance indexing.

Comparison Table

Feature	Full Text Search (FTS)	N1QL LIKE/REGEXP
Text analysis	Built‑in analyzers (standard, whitespace, keyword, language)	None – raw string match
Faceting & scoring	Yes – relevance scores, drill‑down facets	No – only boolean match
Index size	Larger – inverted index stores term frequencies	Minimal – uses primary or covering index only
Update cost	Higher – document mutation triggers re‑indexing of affected terms	Lower – no extra index maintenance
Typical latency (indexed)	5‑30 ms for most queries	10‑100 ms+ depending on data scan size

Trade‑offs

FTS delivers superior relevance and features such as stemming, synonyms, and faceted navigation, but it consumes more disk space, adds indexing latency on writes, and introduces operational complexity (monitoring index size, planning rebuilds). N1QL LIKE/REGEXP is lightweight and requires no extra index, yet it lacks linguistic processing, cannot provide relevance scores, and may trigger full‑collection scans if no suitable covering index exists, degrading cluster performance under load.

Concrete Implementation – Creating and Validating an FTS Index

1. Create a test bucket (if you do not already have one):

   cbc bucket-create -c localhost -u Administrator -p password \
       -b test_bucket -r 1 -q 0 -memb 100

2. Insert sample documents (≈100 k) with a description field. Example for a single document:

   cbc doc-set -c localhost -u Administrator -p password \
       -b test_bucket -k doc::00001 -j '{"description":"quick brown fox jumps over lazy dog"}'

3. Create an FTS index via the CLI (equivalent to the Web UI path Indexes → Full Text → Add Index):

   cbc ft-index-create -c localhost -u Administrator -p password \
       -i desc_idx -b test_bucket -t description \
       -a '{"type":"standard"}' -m 1 -n 1

   • -i index name
   • -b source bucket
   • -t field to index
   • -a analyzer JSON (standard analyzer shown)
   • -m number of index partitions (adjust for cluster size)
   • -n number of replica index partitions
4. Run a sample FTS query using the Go SDK (or any SDK). The query asks for the phrase quick brown fox and requests facets:

   searchQuery := gocb.NewSearchQuery("desc_idx", "quick brown fox")
   searchQuery.Facets(map[string]interface{}{
       "terms": map[string]string{"field": "description"},
   })
   result, err := cluster.SearchQuery("desc_idx", searchQuery)
   if err != nil { log.Fatal(err) }
   for _, hit := range result.Hits() {
       fmt.Printf("ID: %s, Score: %.2f\n", hit.ID, hit.Score)
   }
   fmt.Printf("Facets: %v\n", result.Facets())
   

   Check that each hit includes a Score field and that the Facets map contains term counts for the description field.

5. Run the equivalent N1QL LIKE query (using cbq or the SDK):

   cbq -c localhost -u Administrator -p password \
       -q "SELECT * FROM test_bucket WHERE description LIKE '%quick brown fox%'"
   

   Observe the execution time (reported in the response) and note that no score or facet information is returned.

6. Validate index usage – prepend EXPLAIN to the N1QL statement to see whether the primary index or a covering index is used:

   cbq -c localhost -u Administrator -p password \
       -q "EXPLAIN SELECT * FROM test_bucket WHERE description LIKE '%quick brown fox%'"
   

   If the plan shows a PrimaryScan or a full collection scan, the query is not optimized for large data sets.

Limitations and Practical Checks

Monitor FTS index size regularly; in Couchbase Server versions prior to 7.0 the index compaction was less efficient, so rapid growth can fill disk. Use the admin UI Metrics → Index → FTS or the CLI command cbc ft-index-stat to track disk_size and document_count. If size approaches your storage threshold, consider:

• Increasing the index_purge_interval to reclaim space.
• Rebuilding the index during a maintenance window (cbc ft-index-delete followed by cbc ft-index-create).

For N1QL LIKE/REGEXP, ensure a covering index exists on the searched field if you expect low latency:

cbc index-create -c localhost -u Administrator -p password \
    -i idx_desc -b test_bucket -f '["description"]' -d

Then re‑run the EXPLAIN step; the plan should show an IndexScan using idx_desc.

Rollback (State‑Changing Operation)

Creating an FTS index modifies the cluster state. To revert, drop the index:

cbc ft-index-delete -c localhost -u Administrator -p password -i desc_idx -b test_bucket

Verify removal with cbc ft-index-list; the index should no longer appear.

Takeaway

Choose Full Text Search when you need linguistic analysis, relevance scoring, faceting, and predictable sub‑50 ms latency on large text fields. Choose N1QL LIKE/REGEXP for simple, low‑volume ad‑hoc queries where you can accept higher latency and want zero‑maintenance indexing. Validate your choice by measuring latency, checking for scores/facets, and confirming index usage with EXPLAIN.