Tin: full-text search for Postgres
summary
TIN is a new full-text search extension for Postgres designed to outperform existing options by using Postgres' own physical tuple identifiers (ctids) as the native index format. By avoiding the need to map internal document IDs to ctids, TIN significantly reduces computational overhead and enables the use of vectorized CPU instructions for faster queries. This architectural choice results in superior throughput, lower latency, and reduced write amplification during index maintenance compared to competitors like ParadeDB and GIN.
key points
- TIN utilizes a two-level bitmap encoding system based on Postgres page and offset numbers to store ctids efficiently, allowing for highly compressed postings lists.
- The index leverages AVX-512 and AVX2 vectorization to perform conjunction and disjunction operations as simple CPU register instructions, drastically increasing query speed.
- By using ctids directly, TIN eliminates the need for document renumbering during segment merges, which reduces I/O costs and write amplification while maintaining MVCC correctness.
who should read it
Postgres database administrators and application developers who need high-performance full-text search capabilities without sacrificing transaction visibility or system stability.
discussion positive
consensus
Commenters generally agree that while implementing the BM25 algorithm is relatively straightforward, the actual engineering challenge lies in the complex storage engine and index design required for high performance. There is a strong sentiment that domain expertise in database internals is essential to achieve these results, contradicting the idea that AI alone could drive this trend. A secondary debate emerges regarding the ethics of building proprietary extensions on top of open-source software, with views split between strict license adherence and a perceived moral obligation to contribute back.
top insight
The critical distinction between PostgreSQL's built-in full-text search and these new extensions is the implementation of ranking based on overall corpus statistics. While built-in search scores rows individually, BM25 considers the rarity of terms across the entire document collection to provide more accurate relevance ranking, which necessitates the sophisticated indexing structures discussed.
Generated by an LLM from the article and thread. Check the sources before relying on it.