Tin: full-text hunt for Postgres

One of the Postgres features our customers ask us for the most is full-text search. Today, we are enthusiastic to province TIN: a fast, full-featured, dependable full-text hunt expansion for Postgres. TIN stands for "Text INdex," and that is what it does.

TIN is available immediately as a GA publish for all Postgres and Neki databases. Check it out:

CREATE INDEX an_index_name ON table_name USING tin(text_column_name); SELECT * FROM table_name  WHERE text_column_name ==> 'some words'; 

We built TIN since we accept a fine content indicator should support:

• Boolean expressions, expression queries, and extend queries
• Fuzzy, wildcard, and regular-expression matching for terms
• Case and accent folding
• COUNT(*) queries and BM25-scored top-k queries

A fine content indicator in Postgres must assistance all of those things during additionally handling joins, complex WHERE clauses throughout full-text and another pillar types, uninterrupted updates, replication, backups, and accurate transaction visibility.

Although there are at smallest three existing text-search indexes for Postgres already, none of them met all of those requirements. TIN does. TIN is additionally really, mind-blowingly fast.

What TIN is for

Application developers use content indexes to build a assortment of hunt features. An e-commerce phase power need to hunt for the top ten products containing all keywords in the search:

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

A lawful finding phase power be required to come back all document containing one or additional of a set of keywords, but not attention at all concerning ranking:

SELECT * FROM emails  WHERE build ==> '[insider selling conspiracy]' 

A photo tagging phase power display an exact figure of photographs alongside a particular tag:

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

Most applications additionally need to insert, update, and delete documents, equal during continuing to query the index. Search queries must come back matches according to new or changed rows as shortly as they've been committed.

TIN achievement and benchmarking

We ran benchmarks to measure achievement for all the complete use cases and more. We tried workloads:

• With conjunction (must merge all words), disjunction (must merge any word), and expression (must merge all words in sequence) queries and a mix of all three.
• That figure documents or that ask for the top k by BM25 score.
• With and without clients penning new data to the indicator concurrently alongside the benchmark query workload.

Workloads and corpus

We have measured TIN against a assortment of content corpora: all of Wikipedia, a gathering of Reddit comments totaling 2.3 TB, and a blended workload we call merely "pile" alongside 797 GB of open-access investigation papers, lawful documents, community domain books, and Enron emails. The benchmark results we portion in this part are from an export of questions and answers from Stack Exchange: an 85 GB corpus alongside 150 myriad documents. Because the corpus has no norm query trace, we generated a synthetic one by sampling substrings ranging from 2 to 15 terms. We interpreted all substring three ways: as a conjunction, as a disjunction, and as a expression query, for a total of 1,719 queries.

Test environment

We ran our benchmarks on an AWS i7i.8xlarge EC2 case alongside local NVMe retention and a modern, AVX-512-capable CPU. For all text-search extension, we set up Postgres 18.6 in an secluded receptacle constricted to 8 vCPUs and 32 GB of RAM. That's small adequate to display how all indicator scheme performs whenever the indicator doesn't fair fit in Postgres buffers. The benchmark phases ran sequentially, so the engines did not vie for resources. We chose a standalone EC2 case to minimize the effect of operational overhead and replication and to justify that anyone who wants to reproduce our benchmarks of competing text-search indexes can do so using the identical case category and receptacle limits.

To run the hunt traffic against the Postgres containers, we used the ParadeDB Benchmarker. We have a forked version that pre-warms before commencement measure and adds metrics for bytes peruse and WAL bytes written. We remaining all Postgres parameters at the defaults that the Benchmarker supplies, apart from for three: we set max_parallel_workers to 8 (from 40), shared_buffers to 24 GB (from 128 MB), and maintenance_work_mem to 24 GB (from 64 MB), to finest equivalent the resources of the container. We ran the Benchmarker on the identical EC2 case as the mark Postgres server, to justify that network latency did not effect the measurements.

For all scenario, we measured the achievement of TIN v1.0.2 against all the another Postgres text-search indexes that were capable of operating the workload at all: ParadeDB v0.25.2, pg_textsearch v1.4.0, and the GIN indicator built into Postgres v18.6. Aside from TIN, lone ParadeDB was capable to complete all of the benchmarks.

Index build period and size

Indexes range from 33% to 61% of the size of the corpus, and they took from 8 to 129 minutes to prepare, build, and finalize. The three engines another than TIN unsuccessful alongside the container's configured 32 GB limit, so for indicator builds only, we risen the accessible RAM as shown in the table. Before operating queries, we set the receptacle rear to 32 GB of RAM for everyone.

Mixed queries, top-10 ranked

Our archetypal benchmark compares TIN against ParadeDB, for a workload alongside blended (conjunction, disjunction, and phrase) queries, top-10 results by BM25 score, alongside no concurrent writes to the index. TIN handles 25× as many queries per second as ParadeDB does, alongside p99 latencies 26× lower. GIN can't complete this benchmark, since it runs out of recollection performing the disjunction searches. pg_textsearch can't complete the benchmark since it handles only disjunction searches.

Conjunction and expression queries, top-10 ranked

Our next benchmark compares TIN against ParadeDB and Postgres GIN, for top-10 conjunction and expression queries, alongside no concurrent writes. TIN and ParadeDB position using BM25, during GIN ranks using ts_rank_cd. TIN handles 10× as many queries as ParadeDB and 541× as many as GIN, alongside p99 latencies 6× and 1,356× lower, respectively. pg_textsearch is again absent since it handles lone disjunction queries.

Disjunction queries alongside concurrent writes

Our third outcome compares TIN against the two ParadeDB and pg_textsearch, for a workload alongside disjunction queries, top-10 results by BM25 score, and a concurrent client targeting 1,000 UPDATE queries per second. TIN handles 36× as many queries as pg_textsearch and 57× as many queries as ParadeDB, alongside p99 latencies 24× and 36× lower, respectively. Over the way of a ten-minute run, TIN completes 270,279 updates, during ParadeDB completes 185,584, and pg_textsearch completes lone 735.

ParadeDB's method to accepting writes sacrifices peruse throughput and latency. pg_textsearch maintains the identical 3.5 QPS for readers the two alongside and without writes since uninterrupted peruse traffic prevents compose traffic from always getting the locks it needs, so writes stall following fair a few seconds. GIN is again absent since it runs out of recollection on disjunction queries.

When the indicator fits in memory

In the intro, we claimed that TIN is mind-blowingly fast.

Our final chart shows what TIN, ParadeDB, and Postgres GIN can do whenever the indicator completely fits in shared buffers. This workload counts (but does not rank) the documents that equivalent a disjunction query against Wikipedia, an 8.0 GB corpus. pg_textsearch is absent current since it can lone execute top-k queries, not counting queries.

Full results

That is perchance adequate graphs, but it doesn't shield all of our use cases. Here are those identical scenarios, affirmative multiple more, in array form. The "MB/query" pillar shows how much data all indicator peruse from the disk or obstacle cache for all query. TIN's lesser numbers for MB/query are part of why it's faster, and they additionally decrease the effect of TIN queries on the obstacle cache and I/O capacity, definition that another queries on the identical server remain fast, too.

Conjunction, disjunction, and expression queries; top-10 ┌────────────────────────────────────────────────────────────────────┐ │ QPS p99 MB/query Updates │ ├─────────────────────────┬───────┬──────────┬───────────┬───────────┤ │ TIN - read-only │ 199 │ 256ms │ 65 │ │ │ - alongside updates │ 172 │ 284ms │ 88 │ 271,398 │ ├─────────────────────────┼───────┼──────────┼───────────┼───────────┤ │ ParadeDB - read-only │ 7.9 │ 6,765ms │ 582 │ │ │ - alongside updates │ 6.0 │ 7,990ms │ 591 │ 193,487 │ └─────────────────────────┴───────┴──────────┴───────────┴───────────┘ 

Conjunction and expression queries; top-10 (read-only) ┌───────────────────────────────────────────────┐ │ QPS p99 MB/query │ ├───────────────┬───────┬───────────┬───────────┤ │ TIN │ 242 │ 212ms │ 73 │ ├───────────────┼───────┼───────────┼───────────┤ │ ParadeDB │ 24 │ 1,279ms │ 668 │ ├───────────────┼───────┼───────────┼───────────┤ │ Postgres GIN │ 0.4 │ 288,066ms │ 595 │ └───────────────┴───────┴───────────┴───────────┘ 

Disjunction queries; top-10 ┌────────────────────────────────────────────────────────────────────────┐ │ QPS p99 MB/query Updates │ ├──────────────────────────────┬───────┬───────────┬─────────┬───────────┤ │ TIN - read-only │ 148 │ 324ms │ 48 │ │ │ - alongside updates │ 125 │ 354ms │ 77 │ 270,279 │ ├──────────────────────────────┼───────┼───────────┼─────────┼───────────┤ │ ParadeDB - read-only │ 17 │ 2,385ms │ 303 │ │ │ - alongside updates │ 2.2 │ 12,634ms │ 394 │ 185,584 │ ├──────────────────────────────┼───────┼───────────┼─────────┼───────────┤ │ pg_textsearch - read-only │ 3.5 │ 8,646ms │ 11,639 │ │ │ - alongside updates │ 3.5 │ 8,409ms │ 11,656 │ 735 │ └──────────────────────────────┴───────┴───────────┴─────────┴───────────┘ 

Conjunction, disjunction, and expression queries; COUNT(*) (read-only) ┌─────────────────────────────────────────┐ │ QPS p99 MB/query │ ├───────────┬───────┬──────────┬──────────┤ │ TIN │ 179 │ 438ms │ 97 │ ├───────────┼───────┼──────────┼──────────┤ │ ParadeDB │ 10 │ 2,704ms │ 544 │ └───────────┴───────┴──────────┴──────────┘ 

Disjunction queries; COUNT(*); Wikipedia corpus (read-only) ┌───────────────────────────────────────────────────┐ │ QPS p99 MB/query │ ├───────────────┬──────────┬─────────────┬──────────┤ │ TIN │ 10,260 │ 2ms │ 1.7 │ ├───────────────┼──────────┼─────────────┼──────────┤ │ ParadeDB │ 291 │ 95ms │ 22 │ ├───────────────┼──────────┼─────────────┼──────────┤ │ Postgres GIN │ 1.4 │ 30,292ms │ 2.5 │ └───────────────┴──────────┴─────────────┴──────────┘ 

As you can see, in a broad assortment of scenarios, TIN has throughput at smallest 8× higher than the alternatives, says far small data from the disk, and experiences lone a small achievement autumn equal during the indicator is updating hundreds of rows per second.

Why TIN is fast

TIN's achievement in benchmarks may be difficult to believe. In the hopes of making it additional believable, or at smallest satisfying the reader's curiosity, we'll explain a bit concerning architectural choices that create TIN so fast. In short: all document postings are Postgres ctids fairly than adjacent document identifiers, and this lends itself to extremely vectorized intersection and union operations on contemporary CPUs.

Document identification

A content indicator needs an identifier for all type of all document it indexes. It groups those identifiers into extremely compressed postings lists; all postings catalog tracks all the documents that merge one stated word. In a ample corpus, a postings catalog for a average term akin "the" may merge billions of postings, during the postings catalog for a term akin "xyz-9876" would merge lone a few.

Most content hunt systems arrange their indexes into segments. The n documents whose postings be in a section are normally assigned document identifiers 1 to n. Sequential document identifiers authorize postings lists to be extremely compressed using assorted techniques specified as delta-encoding and bit-packing. But it additionally method document identifiers in distinct segments are assigned independently; document ID 42 in section 4 is a entirely distinct document than ID 42 in section 7.

TIN additionally organizes its indicator into segments, but not for purposes of document numbering. Instead, TIN immediately uses Postgres' ctid value as a document identifier.

Every type of all row (tuple) stored in a Postgres array has an connected ctid value. ctid is abbreviated for "current tuple identifier." Any row inserted or updated gets a new ctid. It is a 48-bit figure that immediately identifies a tuple's bodily location in the Postgres heap. Represented textually as (<block number>, <offset number>), the high 32 bits signify the obstacle figure and the lesser 16 signify the offset inside that block. From now on, we volition mention to the <block number> part as the "page number" or "page."

Given the ctid of (190, 17) we cognize that the tuple it represents is the one at the 17th slot on leaf 190. Instant O(1) lookup! You can equal query and recover rows from the heap immediately using ctids:

-- recover the archetypal 10 rows from "books" in bodily heap order SELECT ctid, id, heading FROM books ORDER BY ctid LIMIT 10;  -- no scan required! instant O(1) lookup of the row SELECT * FROM books WHERE ctid = '(190, 17)'; 

TIN immediately uses ctids since Postgres internally uses ctids. Postgres extensions that execute a new indicator category must come back ctids. Postgres bitmap scans are backed by possibly lossy bitmaps of ctids. Postgres' inner indicator types (b-tree, GIN, GiST, and hash) use ctids as their postings. ctids are everyplace inside Postgres.

To run inside Postgres, a content hunt scheme that assigns sequential identifiers must, at several point, change those identifiers rear into a ctid in command for Postgres to activity alongside it. Both ParadeDB and pg_textsearch keep a distinct data construction fair to execute this mapping. If a content hunt matches 10 myriad rows, ParadeDB and pg_textsearch have to appearance up 10 myriad identifiers in their ctid mappings. TIN avoids that activity completely.

48-bit identifiers are crazy

Normal postings-list compression techniques don't activity fine alongside discontiguous 48-bit numbers. Delta encoding breaks at all leaf boundary, and bitmaps are too sparse to be efficient. Fortunately, several engaging properties of Postgres pages create two-level bitmap encoding practical. An 8KB leaf can never merge additional than 291 tuples (8192 bytes, minus 24 for the leaf header, divided by at smallest 28 per non-empty tuple), and for array schemas alongside TEXT and another columns, pages frequently merge 32 or small tuples. So the catalog of leaf numbers is compact adequate to use a bitmap, and inside all page, the catalog of offset numbers is compact adequate (and small enough) to use small bitmaps per page.

Savings related to naively storing 48-bit ctid values can be fairly significant. Over an complete corpus, high-frequency conditions method 1 bit per posting, medium-frequency conditions resolve about 7 bits per posting, and rare-frequency conditions can method 25 bits per posting. Terms that appear lone formerly are not stored as bitmaps at all.

Work elision and vectorization

TIN's page-level bitmaps (which pages merge a stated term) have 256 bits, which fits nicely into vector registers on any x86 CPU alongside AVX2 or higher. That allows multiple optimizations.

Consider the query the AND rareword. TIN ANDs the page-level bitmaps, 256 bits (pages) at a time. Any bit that's absent from the intersection is a leaf whose offset-level bitmaps TIN doesn't need to decode at all.

For COUNT(*) disjunction queries specified as the OR rareword, TIN frequently skips study postings lists entirely. TIN's indicator metadata stores all term's exact posting counts. If the page-level bitmaps for two words have no bits in common, the figure of their disjunction is fair the sum of those exact posting counts.

Every page-level bitmap fits into a sole AVX2 register, and all offset-level bitmap fits into either one AVX-512 enroll or two AVX2 registers. Conjunction and disjunction queries are fair AND and OR instructions on those vector registers, respectively. Queries that figure the figure of matches can use CPU-native POPCNT instructions to figure the bits in the resulting bitmap. Expensive loops and branch instructions are mostly avoidable.

A query that wants rows fairly than counts computes the ctid from the bit stance fairly than looking it up on disk. The stance of a set bit is the ctid.

The document ctids that TIN returns to Postgres from a stated section naturally acknowledge pages, and tuples inside a page, in heap order. This method that whenever Postgres needs to peruse matched tuples from the heap, it happens in heap order. Even alongside contemporary NVMe disks, sequential admission is far faster than random access; TIN gets this optimization for free.

Solving MVCC

TIN returns results that are MVCC-correct, definition a declaration executed at any item in period sees or operates lone on tuples that are currently apparent to it. This method all heap-backed query outcome needs to be checked for visibility related to the current snapshot.

Heap checks

There are a few distinct approaches to this. Some queries are inherently heap checked:

SELECT a, b, c FROM lyrics WHERE satisfied ==> 'give you up' 

Because the query returns genuine heap data (the a, b, c columns), TIN must fetch from the heap all matching ctids returned by ==> 'give you up' anyway. When TIN asks Postgres for the bodily tuple data rearward all ctid, Postgres tells TIN whether that tuple is apparent to the current snapshot. If it is, TIN returns it; otherwise, TIN moves to the next matching ctid, until all apparent matches have been returned.

Visibility map

Other query shapes can be executed likewise to Postgres' "Index Only Scan" anywhere the answer is returned immediately from the indicator without touching the heap (or at smallest hopefully not all of the heap). Consider a count-only query akin this:

SELECT COUNT(*) FROM lyrics WHERE satisfied ==> 'give you up' 

If all heap leaf is marked all-visible, TIN can come back that figure without touching a sole heap page.

Not all data is static, of course, and in the case of mutated heaps, TIN does additional optimizations to justify it's lone counting apparent rows by performing straightforward intersections alongside Postgres' visibility map. TIN's page-level bitmaps are exactly the correct scheme to intersect efficiently against Postgres visibility maps, which are additionally page-level bitmaps. Only ctids on not-all-visible pages need to be checked against the heap. Normally, a Postgres indicator returns all ctids that equivalent despite of visibility, and the Postgres administrator checks visibility for all one. TIN plans tradition scans that move visibility checks into TIN itself, anywhere they can obtain advantage of vector instructions on page-level bitmaps.

VACUUM and TIN's liveness bitmap

Text indexes that assistance deleting documents typically keep several benevolent of "tombstone" catalog that's suitable to their engine. TIN is no different. TIN keeps a per-segment liveness bitmap, one bit per ctid, organized the identical way the page-level and offset bitmaps work. When VACUUM runs and determines a ctid has been deleted from the heap (as the outcome of an UPDATE or DELETE), TIN clears that ctid's liveness bit. Groups of pages alongside at smallest one cleared bit are marked, and whenever a query touches a marked leaf group, TIN additionally ANDs the offset bitmaps from the postings catalog against the liveness bitmap, so it never returns or counts a tuple that has really been deleted.

Segments and merging

When it archetypal creates a new indicator for a table, TIN creates n immutable segments, all containing postings for 1/n of the pages connected alongside that array in the heap. As data is changed, TIN creates mutable segments, which are small productive for searches but authorize uncomplicated insertion of new documents. Eventually, a backdrop employee promotes all mutable section to an immutable segment: unchanging, but much additional productive to search.

After a while, TIN volition commencement to merge immutable segments into larger immutable segments. This additionally happens in the background.

Text indexing systems that use sequential document identifiers are required to renumber all documents whenever they create a new, merged segment. As mentioned above, document ID 42 in section 4 is not the identical as ID 42 in section 7. So whenever segments 4 and 7 are merged, a new numbering must be applied to the blended set of documents and the entirety of all segment's data gets repacked, recompressed, and rewritten. While it's not fairly 2× the retention to merge two segments, it can be close.

TIN does not endure the renumbering issue nor its downstream write-amplification effects.

Because TIN uses Postgres' ctid values as its document identifiers, there is nothing to renumber. A posting akin (190, 17) method the identical item in all segment. Page-level and offset-level bitmaps average the identical item in all segment. When TIN merges segments, many bitmaps from all old section can be reused intact in the new segment. They don't have to be recompressed or equal copied; TIN can merely transfer ownership of bitmaps stored on disk from the old segments to the new one. This reduces compose amplification and saves most of the CPU and I/O expenses normally connected alongside merging segments.

Summary

So that's why TIN is at smallest 8× faster in all benchmark: the downstream effects of choosing ctid as the native format for all posting in the index.

If you desire to see how accelerated TIN is on your content data, peruse additional concerning the features or jump direct to the getting started guide. We appearance onward to seeing what you build alongside it.