Vincent Tran

Posted on Sep 23 Originally published at 0xgosu.dev on Sep 20

TIN Makes Postgres Search Fast by Speaking Postgres Natively

#ai #productivity #software

Fast full-text search is usually introduced as an information-retrieval problem: tokenize documents, build an inverted index, rank matches with BM25, and return the best results. That description is correct, but it skips the part that makes a search engine difficult to place inside a transactional database.

Postgres does not see a document as a permanent integer. It sees versions of rows stored on heap pages. An update creates a new tuple version. Transactions disagree, correctly, about which versions are visible. VACUUM eventually removes dead versions. Indexes must return physical tuple identifiers to the executor, and the executor still has to preserve snapshot semantics while other sessions write.

TIN, PlanetScale’s new full-text index, starts with that integration problem instead of hiding it behind a second identifier system. Its key decision is to use Postgres ctid values directly as document postings. The rest of the design follows from that choice: two-level bitmaps, vectorized Boolean operations, heap-ordered reads, visibility-map intersections, and segment merges that do not renumber documents.

The result is not simply “BM25 inside Postgres.” It is a search index whose internal coordinates are the same coordinates Postgres already uses.

What a useful Postgres search index must do

An inverted index maps each term to the documents containing it. For a collection containing “red apple” and “green apple,” the posting list for apple points to both documents while red points only to the first.

That basic structure supports much more than keyword lookup. A production search feature may need:

Boolean AND, OR, and NOT expressions;
phrases, proximity, spans, wildcards, fuzzy terms, and regular expressions;
exact counts as well as top-k ranked results;
highlighting and cross-column scoring;
inserts, updates, deletes, backups, replication, and transactional visibility.

Postgres already provides full-text search, and its documentation recommends GIN as the normal text-search index. A GIN index stores one entry per lexeme and a compressed posting list of matching row identifiers. It is mature, general, and often the right answer. TIN targets a broader search language and workload: direct text indexing, BM25 ranking, exact counts, richer positional expressions, and concurrent updates.

The application-facing surface stays recognizably SQL:

CREATE EXTENSION IF NOT EXISTS tin;

CREATE INDEX posts_body_tin
ON posts USING tin (body);

SELECT id, tin.score(ctid) AS score, tin.highlight(body)
FROM posts
WHERE body ==> '(citrus NEAR/5 melon) AND "fuji apple" AND NOT peel'
ORDER BY score DESC
LIMIT 10;

The ==> operator filters rows using TINQL. tin.score(ctid) supplies BM25 relevance, and ordinary SQL can combine the search predicate with joins and other filters. This matters operationally: the search result is still a Postgres relation rather than a response copied back from a separate search cluster.

The document identifier is the architectural decision

Most segmented search engines assign compact document IDs inside each segment. Sequential integers compress beautifully. If documents 100, 101, 102, and 103 contain a term, the index can store small deltas or tightly packed bits instead of four full identifiers.

The convenience ends at the database boundary. Segment 4’s document 42 is unrelated to segment 7’s document 42. Postgres does not fetch heap rows by either number; it expects tuple identifiers. A search extension using private document IDs therefore needs a mapping back to Postgres ctid values. A large match set can turn that mapping into millions of extra lookups.

A ctid is a physical address for one tuple version. Its upper 32 bits identify an 8 KiB heap block, and its lower 16 bits identify a slot within that block. Textually, (190,17) means the tuple at slot 17 on page 190. Updating a row produces a new tuple version with a new ctid, which is why ctid must never be used as an application-level primary key. Inside an index that already tracks row versions, however, it is exactly the identifier Postgres wants back.

TIN stores that address directly. There is no document-ID translation step between search and execution.

“Dark — TIN keeps the search index in Postgres’s physical coordinate system, so a set bit can become a heap address without a separate document-ID lookup.

This is a classic systems trade: give up the globally tidy identifier in exchange for removing a boundary conversion. The surprising part is that physical identifiers can still be compressed efficiently.

Turning a sparse 48-bit address into dense local bitmaps

Naively storing 48 bits for every posting would be expensive. Ordinary delta encoding also behaves poorly when tuple addresses jump from one page to another. TIN exploits the structure already present in a ctid by splitting the problem into two levels.

First, a page-level bitmap records which heap pages contain a term. Second, each selected page has a small offset bitmap recording which tuple slots on that page contain it. Postgres pages have a hard upper bound on tuple slots, and pages containing realistic text rows usually have far fewer. The global address space is sparse, but each local page is compact.

PlanetScale reports that this representation approaches roughly one bit per posting for very common terms, around seven bits for medium-frequency terms, and up to about 25 bits for rare terms. Terms occurring only once avoid bitmap storage entirely.

The layout also matches modern CPUs. TIN groups page-level bitmaps into 256-bit blocks, which fit in an AVX2 register. Offset bitmaps fit in one AVX-512 register or two AVX2 registers. That turns important query operations into hardware-friendly bit operations:

term_a AND term_b intersects page bitmaps before decoding offsets;
term_a OR term_b unions them;
POPCNT counts matching bits;
a zero page intersection lets TIN skip all offset work for that page group.

Consider the AND rareword. The common term may appear nearly everywhere, but the rare term touches few pages. Intersecting the page bitmaps first eliminates most pages without reading or decoding their tuple offsets. The index avoids work rather than merely executing the same work faster.

Exact disjunction counts can sometimes skip postings too. If two terms occupy disjoint page groups, COUNT(*) for a OR b is the sum of their exact stored posting counts. No tuple needs to be materialized just to count it.

Finally, set bits are already ordered by page and offset. When Postgres does need heap tuples, TIN returns ctids in physical heap order. That improves locality and avoids converting search-engine order into database order after the fact.

MVCC is where “inside Postgres” becomes real

A search index can find every tuple version containing a word and still return the wrong answer. Under multi-version concurrency control, a query must see only the row versions visible to its snapshot. A concurrent transaction may see a different correct set.

Queries that return heap columns naturally perform visibility checks while fetching those tuples. Count-only queries are harder: touching every matching heap tuple would throw away much of the index’s advantage.

Postgres maintains a visibility map with bits identifying heap pages whose tuples are all visible. TIN’s page-level representation lines up with that map. For an exact count, it can accept postings from all-visible pages directly and reserve heap checks for pages that are not marked all-visible. Recently changed pages cost more; stable pages stay cheap.

TIN also keeps a per-segment liveness bitmap. When VACUUM confirms that an old tuple version is dead, TIN clears that tuple’s bit. Search operations touching affected page groups intersect term postings with this liveness mask so deleted versions are neither returned nor counted.

This is the strongest part of the design. The same page boundary supports compression, SIMD filtering, physical read order, visibility tests, and garbage-collection state. One representation pays rent in several subsystems.

Stable addresses make segment merging cheaper

Search indexes use immutable segments because read-only structures compress and search well. New writes first land in mutable structures; background work later promotes and merges them.

With segment-local sequential document IDs, merging two segments changes the numbering. Posting lists must be decoded, renumbered, repacked, recompressed, and rewritten into the merged segment. That can approach twice the storage footprint during a merge and creates substantial write amplification.

TIN’s (page, offset) address means the same thing in every segment. Merging segments does not change a tuple’s identifier. PlanetScale says many on-disk bitmaps can therefore be transferred into the new segment without recompression or even copying. The system still has background maintenance, but it avoids work created solely by an internal numbering scheme.

That advantage has a boundary: updates produce new tuple versions at new ctids, and old versions still need liveness tracking and vacuum cooperation. Stable means stable across segments for a particular tuple version, not permanent across row updates.

Reading the benchmark claims carefully

PlanetScale tested an 85 GB Stack Exchange-derived corpus containing 150 million documents. Each engine ran in an isolated Postgres 18.6 container limited to eight vCPUs and 32 GB of RAM for query workloads, on an AVX-512-capable AWS i7i.8xlarge host with local NVMe. The query set contained 1,719 sampled substrings interpreted as conjunction, disjunction, and phrase searches.

In PlanetScale’s published results, TIN built its 50.7 GB index in 8 minutes 10 seconds using 32 GB of RAM. The reported mixed top-10 workload reached 199 queries per second at 256 ms p99, compared with 7.9 QPS and 6,765 ms p99 for ParadeDB in that setup. For disjunction top-10 queries with a writer targeting 1,000 updates per second, TIN reported 125 QPS and 270,279 completed updates during the run.

Those are vendor-run benchmarks, not a universal ranking. The corpus, query distribution, memory settings, CPU instruction set, index options, warm-up, feature overlap, and product versions all shape the outcome. Some engines could not execute every workload, which is important product information but also makes a single “times faster” number incomplete. PlanetScale published its benchmarker fork and configuration details, which makes the claims more inspectable; users should still reproduce their own query mix.

The useful metrics are not only QPS and p99. The reported megabytes read per query expose why the design is fast. In the mixed read-only workload, TIN reported 65 MB read per query versus 582 MB for ParadeDB. Reducing data movement protects the database’s shared I/O and cache budget instead of winning one search benchmark by disturbing every other query.

The product boundary matters

TIN is currently a PlanetScale Postgres feature, not a production extension you can install on an arbitrary self-hosted server. PlanetScale provides Lead, an open-source compatibility extension for local development and CI. Lead accepts TIN-style SQL but scans table rows; its documentation explicitly says it is unsuitable for production or performance testing.

That distinction affects architecture decisions. Keeping search inside hosted Postgres removes a separate cluster and synchronization pipeline, but it also couples production behavior to a provider-specific extension. Teams should evaluate both sides:

whether transactional consistency and operational simplicity outweigh portability;
whether TINQL and ==> can be isolated behind a repository or query layer;
how data and queries migrate if the hosted feature no longer fits;
whether missing features matter, such as stemming, which TIN’s current comparison table marks unsupported;
how churn, VACUUM, index high-water growth, and REINDEX behave on the real dataset.

The SQL surface makes application migration easier than a proprietary external API would, but it does not make the production engine interchangeable.

The larger lesson is alignment

TIN’s most transferable idea is not a particular bitmap width or ranking formula. It is to look for a representation that aligns several layers of the system.

Using ctid directly removes ID translation. Splitting it by page and offset makes sparse physical addresses locally dense. Those bitmaps fit vector registers, preserve heap order, intersect naturally with the visibility map, carry liveness state, and keep their meaning across segment merges.

Many fast systems are built this way. The best optimization is often not a faster implementation of an isolated stage. It is choosing a boundary representation that lets several stages disappear or collapse into the same operation.

TIN makes full-text search interesting not because it puts a familiar ranking function in Postgres, but because it asks what a search index would look like if it stopped pretending Postgres were somewhere else.

DEV Community