PlanetScale unveils TIN — full-text search for PostgreSQL

PlanetScale has announced the release of TIN, a full-text search extension for PostgreSQL. The name stands for “Text INdex,” and, according to its authors, that is precisely what it does. The extension is available in general availability from day one for all Postgres and Neki databases, and it hooks in through the familiar CREATE INDEX ... USING tin(...), with searches performed via the ==> operator.

The developers argue that a good text index should support boolean expressions, phrase and span queries, fuzzy search, wildcards and regular expressions, case and diacritic folding, COUNT(*) tallies and top-k ranking by BM25 — all while working correctly with joins, replication, backups and transaction visibility. Existing extensions, PlanetScale says, each fail to cover these requirements on their own.

Why it's fast

TIN's central idea is to abandon its own sequential document numbering. Conventional search engines split the index into segments and, within each one, assign documents numbers from 1 to n, so that the same number in different segments points to different documents — and when segments are merged, everything has to be renumbered and rewritten. ParadeDB and pg_textsearch are forced to maintain a separate structure to translate their identifiers back into ctid, the physical address of a row in the Postgres heap.

TIN uses ctid directly. It's a 48-bit value of the form “(page number, offset)” that gives instant access to a row. Since no more than 291 tuples fit on an 8-kilobyte page, TIN encodes posting lists as two-level bitmaps: one for pages, another for offsets within a page. Such bitmaps map perfectly onto AVX2/AVX-512 vector registers, so query intersections and unions reduce to AND/OR instructions, and counting matches reduces to POPCNT. When segments are merged, nothing needs to be renumbered: the bitmaps simply move to the new segment without re-encoding, which sharply reduces write amplification.

The numbers

The authors are candid that the benchmarks are their own. The tests were run on an AWS i7i.8xlarge instance with Postgres 18.6 in a container with 8 vCPUs and 32 GB of memory, against ParadeDB, pg_textsearch and the built-in GIN. Of the competitors, only ParadeDB managed to pass every scenario; GIN stumbled on disjunctive queries for lack of memory, and pg_textsearch can only handle disjunction.

Across the various scenarios TIN delivered at least 8 times the throughput, and on individual tasks the gap reached tens and hundreds of times over, with p99 latencies several times lower. The match count over Wikipedia — which fits entirely in the buffers — is especially striking: more than 10,000 queries per second versus hundreds for ParadeDB. At the same time, TIN reads substantially less data from disk per query, which spares the cache and leaves resources for other queries on the same server.

Source: Lobsters