Introducing TIN: High-Performance Full-Text Search for Postgres

PlanetScale is proud to unveil TIN, a robust, high-speed, and fully-featured extension designed to bring professional-grade full-text search (FTS) directly into Postgres. Alongside this, we are announcing the availability of Neki, our sharded Postgres offering.

TIN is now generally available (GA) for all Postgres and Neki database instances.

Quick Start Guide

Implementing TIN is straightforward. You can initialize an index and query it using the following syntax:

-- Creating the search index
CREATE INDEX an_index_name ON table_name USING tin(text_column_name);

-- Performing a search query
SELECT * FROM table_name WHERE text_column_name ==> 'some words';

---

The Philosophy Behind TIN

We recognized that existing Postgres text-search options were adequate insufficient for modern enterprise needs. We believe a premier text index must satisfy a rigorous set of criteria:

"A truly capable text index shouldn't just find words; it must integrate seamlessly with the relational power of Postgres—handling complex joins, strict transaction visibility, and continuous data streams without sacrificing performance."

Feature Checklist

TIN was engineered to check every box:

• Complex Queries: Support for Boolean expressions, phrase matching, and span queries.
• Flexibility: Wildcard, regular-expression, and fuzzy matching.
• Normalization: Built-in case and accent folding.
• Analytics & Ranking: Support for COUNT(*) and top-k results using BM25 scoring.
• Database Integration: Full compatibility with WHERE clauses, replication, backups, and MVCC.

---

Target Use Cases

Application developers require diverse search capabilities depending on their domain. TIN handles these varied workloads with ease:

1. E-commerce (Ranked Search): Finding the most relevant products using conjunctions.

   SELECT * FROM products 
   WHERE description ==> 'stretch denim jeans' 
   ORDER BY tin.score(ctid) DESC 
   LIMIT 10;
   

2. Legal Tech (Broad Discovery): Retrieving all documents containing any of several keywords (disjunction).

   SELECT * FROM emails 
   WHERE body ==> '[insider trading conspiracy]';
   

3. Media Tagging (Exact Counts): Counting specific phrase matches.

   SELECT COUNT(*) FROM photos 
   WHERE tags ==> '"san francisco"';
   

Crucially, TIN ensures that committed writes are immediately visible to search queries, maintaining strict consistency.

---

Performance Benchmarking

To validate TIN, we conducted an exhaustive series of tests focusing on three primary query types:

• Conjunctions: Must contain all terms.
• Disjunctions: Must contain any term.
• Phrases: Must contain terms in a specific sequence.

The Data Corpus

We tested TIN against massive datasets to ensure scalability:

• Wikipedia: The complete archive.
• Reddit: 2.3 TB of user comments.
• The Pile: 797 GB of mixed research papers, legal texts, and books.
• Stack Exchange: Our primary benchmark set consisting of 150 million documents ($\approx 85 \text{ GB}$).

For the Stack Exchange set, we generated a synthetic trace of $1,719$ queries, sampling substrings of $2$ to $15$ terms.

Testing Infrastructure

The environment was standardized to ensure reproducibility:

Custom Postgres Configurations:

• max_parallel_workers $\rightarrow$ 8
• shared_buffers $\rightarrow$ 24 GB
• maintenance_work_mem $\rightarrow$ 24 GB

Benchmarking Workflow

---

Initial Findings

We compared TIN v1.0.2 against ParadeDB v0.25.2, pg_textsearch v1.4.0, and the native Postgres GIN index.

Key Observation: Only TIN and ParadeDB were capable of completing the entire suite of benchmarks.

Index Efficiency

The physical footprint and creation time of the indexes varied based on the corpus:

• Size: The index size typically ranged from $33\%$ to $61\%$ of the original corpus size.
• Build Time: Preparation and finalization took between $8$ and $129$ minutes.

Notably, the competing engines failed under the $32 \text{ GB}$ RAM limit during the build phase, requiring us to temporarily increase memory just to complete the index creation.

The results demonstrate that TIN is not only faster but significantly more memory-efficient during both the build and query phases.