Fine‑Tuning Full‑Text Search in SourceEngine: BM25, Indexing, and Common Pitfalls

Why Full‑Text Search Matters in SourceEngine

When your application needs to surface relevant text quickly—think product catalogs, support tickets, or log analytics—SourceEngine’s built‑in Full‑Text Search (FTS) can replace a custom search service. The key advantage is that it runs inside the database, so you avoid duplicate data stores and keep the ranking logic close to the data.

Getting Started: Enable the FTS Index

FTS is not enabled by default. The first step is to create a dedicated index on the column you want to search. The syntax is straightforward:

-- Run as a database superuser or a role with CREATE privilege
CREATE INDEX idx_articles_content
  ON articles
  USING fts5(content);

Replace articles with your table name and content with the text column. This creates a full‑text index that tokenizes the column, removes stop‑words, and applies stemming automatically.

Configuring BM25 Ranking

SourceEngine uses the BM25 algorithm as the default ranking function. BM25 scores a document based on term frequency, inverse document frequency, and document length. Two tunable parameters control the behavior:

• b (0–1) – controls how much document length normalisation is applied.
• k1 (≥0) – controls term frequency saturation.

You can set these parameters per‑session or globally. For a session‑wide setting, run:

SET search_params = 'b=0.75,k1=1.2';

Typical values are b=0.75 and k1=1.2, but you may tweak them if you notice that short documents are undervalued (b too high) or that term repetition is over‑rewarded (k1 too low).

Example Query Using BM25

Once the index and parameters are in place, you can query with the MATCH operator. The result set includes a rank column that reflects the BM25 score:

SELECT id, title, rank
FROM articles
WHERE content MATCH 'database engine'
ORDER BY rank DESC
LIMIT 10;

Notice that the query is executed on the index, so it is far faster than a LIKE scan on the same column.

Pagination: Avoid Deep OFFSETs

Using LIMIT/OFFSET is convenient but can become a performance bottleneck on large result sets because the database still has to scan and sort all preceding rows. For most applications, OFFSET values up to a few hundred rows are acceptable. Beyond that, consider cursor‑based pagination:

-- First page
SELECT id, title, rank
FROM articles
WHERE content MATCH 'database engine'
ORDER BY rank DESC, id
LIMIT 10;

-- Subsequent pages use the last seen (rank, id) tuple as a cursor
SELECT id, title, rank
FROM articles
WHERE (rank, id) < (last_rank, last_id)
  AND content MATCH 'database engine'
ORDER BY rank DESC, id
LIMIT 10;

Cursor pagination skips the expensive offset calculation and scales linearly with the page size.

Common Mistakes & How to Avoid Them

• Not Rebuilding After Schema Changes: Adding a new column or altering the content type does not automatically update the FTS index. Run REINDEX INDEX idx_articles_content; after such changes.
• Over‑Tuning BM25: Setting b near 0 or k1 near 0 can produce unintuitive rankings. Start with the defaults and adjust only if you see a systematic bias.
• Excessive Stop‑Word Lists: Removing too many common words reduces recall. Use the built‑in stop‑word list unless you have a domain‑specific need.
• Large Disk Footprint: FTS indexes can be several times larger than the base table. Monitor pg_indexes_size('articles'); and consider VACUUM or REINDEX to reclaim space.
• Binary Data in Text Columns: FTS only works on TEXT, VARCHAR, or CHAR types. Storing blobs in these columns will produce errors.

Verification Checklist

1. Create a test table with known content.

   CREATE TABLE test_articles (id SERIAL PRIMARY KEY, content TEXT);
   INSERT INTO test_articles(content) VALUES
     ('Database engines are complex'),
     ('Engineers build database engines'),
     ('The engine runs smoothly');
   

2. Build the index and set BM25.

   CREATE INDEX idx_test ON test_articles USING fts5(content);
   SET search_params = 'b=0.75,k1=1.2';
   

3. Run a MATCH query and verify rank ordering.

   SELECT id, rank
   FROM test_articles
   WHERE content MATCH 'database engine'
   ORDER BY rank DESC;
   

4. Test pagination: fetch first 2 rows, then next 2 using OFFSET, and compare with cursor pagination.

   -- OFFSET
   SELECT id FROM test_articles WHERE content MATCH 'engine' ORDER BY rank DESC LIMIT 2 OFFSET 0;
   SELECT id FROM test_articles WHERE content MATCH 'engine' ORDER BY rank DESC LIMIT 2 OFFSET 2;
   
   -- Cursor
   SELECT id FROM test_articles WHERE content MATCH 'engine' ORDER BY rank DESC LIMIT 2;
   -- Suppose last id returned is 2
   SELECT id FROM test_articles WHERE (rank, id) < (last_rank, 2) AND content MATCH 'engine' ORDER BY rank DESC LIMIT 2;
   

5. Alter the table (e.g., add a column) and confirm that SELECT * FROM test_articles WHERE content MATCH 'engine'; still works after REINDEX INDEX idx_test;.

Limitations to Keep in Mind

• FTS does not support ranking on binary or non‑text columns.
• Complex query conditions (e.g., OR across multiple columns) require separate indexes or concatenated columns.
• The built‑in tokenizer may not handle language‑specific nuances; for advanced tokenization you must implement a custom analyzer and ensure consistent tokenization between indexing and querying.
• Very large text columns can cause the index to grow quickly; consider truncating or summarizing before indexing if storage is a concern.

Conclusion

SourceEngine’s FTS, powered by BM25, offers a powerful, low‑latency search capability when you need to surface relevant text from large tables. By creating the index, tuning b and k1, and following best practices for pagination and maintenance, you can deliver precise search results without external services. Use the verification checklist to confirm that your configuration behaves as expected before deploying to production.