DEVOURED
Introducing TIN: full-text search for Postgres
TIN is a new PostgreSQL full-text search extension that achieves 8x higher throughput than existing solutions by using physical storage locations as identifiers.
What: PlanetScale introduced TIN (Text INdex), a PostgreSQL extension designed for high-performance full-text search. It uses Postgres 'ctid' values as identifiers, allowing for vectorized CPU operations and avoiding the overhead of remapping document IDs.
Why it matters: This demonstrates a shift toward optimizing database extensions for modern CPU architecture—specifically AVX-512 vectorization—to handle high-volume text search workloads directly within the database heap.
Takeaway: If you are struggling with GIN index performance for full-text search on large Postgres datasets, evaluate TIN for your next indexing task.
Deep dive
- TIN provides support for boolean expressions, phrase/span queries, and fuzzy/regex matching.
- The extension is built specifically to work within Postgres' transaction visibility and MVCC constraints.
- It outperforms ParadeDB and GIN by using native 48-bit ctid (current tuple identifier) values instead of mapping documents to sequential integers.
- TIN uses bitmaps for postings lists, which are optimized for AVX-2 and AVX-512 CPU instructions.
- It minimizes write amplification during segment merging because it does not require document renumbering.
- Benchmarks show significantly lower p99 latencies compared to existing pg_textsearch solutions.
Decoder
- ctid: The physical identifier of a row (tuple) in a Postgres table, representing its block number and slot within that block.
- Postings list: A list of document identifiers (or in this case, ctids) that contain a specific term.
- MVCC: Multi-Version Concurrency Control, the method Postgres uses to allow simultaneous reads and writes without locking.
- BM25: A ranking function used to estimate the relevance of documents to a given search query.
- Write amplification: The phenomenon where the volume of physical data written to storage exceeds the amount of data the application actually intended to write, often due to index merging or re-indexing.
Original article
Full article content is not available for inline reading.
Explore all 60 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi