Introducing TIN: full-text search for Postgres — PlanetScale

One of the Postgres features our customers ask us for the most is full-text search. Today, we are excited to announce TIN: a fast, full-featured, reliable full-text search extension for Postgres. TIN stands for "Text INdex," and that is what it does.TIN is available immediately as a GA release 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 because we believe a good text index should support:Boolean expressions, phrase queries, and span queriesFuzzy, wildcard, and regular-expression matching for termsCase and accent foldingCOUNT(*) queries and BM25-scored top-k queriesA good text index in Postgres must support all of those things while also handling joins, complicated WHERE clauses across full-text and other column types, continuous updates, replication, backups, and correct transaction visibility.Although there are at least three existing text-search indexes for Postgres already, none of them met all of those requirements. TIN does. TIN is also really, mind-blowingly fast.What TIN is forApplication developers use text indexes to build a variety of search features. An e-commerce platform might need to search 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 legal discovery platform might be required to return every document containing one or more of a set of keywords, but not care at all about ranking:SELECT * FROM emails WHERE body ==> '[insider trading conspiracy]' A photo tagging platform might show an exact count of photographs with a particular tag:SELECT COUNT(*) FROM photos WHERE tags ==> '"san francisco"'; Most applications also need to insert, update, and delete documents, even while continuing to query the index. Search queries must return matches based on new or changed rows as soon as they've been committed.TIN performance and benchmarkingWe ran benchmarks to assess performance for all the above use cases and more. We tried workloads:With conjunction (must contain all words), disjunction (must contain any word), and phrase (must contain all words in sequence) queries and a mix of all three.That count documents or that ask for the top k by BM25 score.With and without clients writing new data to the index concurrently with the benchmark query workload.Workloads and corpusWe have measured TIN against a variety of text corpora: all of Wikipedia, a collection of Reddit comments totaling 2.3 TB, and a mixed workload we call simply "pile" with 797 GB of open-access research papers, legal documents, public domain books, and Enron emails. The benchmark results we share in this article are from an export of questions and answers from Stack Exchange: an 85 GB corpus with 150 million documents. Because the corpus has no standard query trace, we generated a synthetic one by sampling substrings ranging from 2 to 15 terms. We interpreted each substring three ways: as a conjunction, as a disjunction, and as a phrase query, for a total of 1,719 queries.Test environmentWe ran our benchmarks on an AWS i7i.8xlarge EC2 instance with local NVMe storage and a modern, AVX-512-capable CPU. For each text-search extension, we set up Postgres 18.6 in an isolated container limited to 8 vCPUs and 32 GB of RAM. That's small enough to show how each index system performs when the index doesn't just fit in Postgres buffers. The benchmark phases ran sequentially, so the engines did not compete for resources. We chose a standalone EC2 instance to minimize the impact of operational overhead and replication and to ensure that anyone who wants to reproduce our benchmarks of competing text-search indexes can do so using the same instance type and container limits.To drive the search traffic against the Postgres containers, we used the ParadeDB Benchmarker. We have a forked version that pre-warms before beginning measurement and adds metrics for bytes read and WAL bytes written. We left all Postgres parameters at the defaults that the Benchmarker supplies, except 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 best match the resources of the container. We ran the Benchmarker on the same EC2 instance as the target Postgres server, to ensure that network latency did not impact the measurements.For each scenario, we measured the performance of TIN v1.0.2 against all the other Postgres text-search indexes that were capable of running the workload at all: ParadeDB v0.25.2, pg_textsearch v1.4.0, and the GIN index built into Postgres v18.6. Aside from TIN, only ParadeDB was able to complete all of the benchmarks.Index build time and sizeIndexes 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 other than TIN failed with the container's configured 32 GB limit, so for index builds only, we increased the available RAM as shown in the table. Before running queries, we set the container back to 32 GB of RAM for everyone.Total timeIndex sizeRequired RAMTIN8m10s50.7 GB32 GBParadeDB19m20s52.1 GB64 GBpg_textsearch26m49s41.5 GB128 GBPostgres GIN2h09m04s28.0 GB64 GBMixed queries, top-10 rankedOur first benchmark compares TIN against ParadeDB, for a workload with mixed (conjunction, disjunction, and phrase) queries, top-10 results by BM25 score, with no concurrent writes to the index. TIN handles 25× as many queries per second as ParadeDB does, with p99 latencies 26× lower. GIN can't complete this benchmark, because it runs out of memory performing the disjunction searches. pg_textsearch can't complete the benchmark because it handles only disjunction searches.Conjunction and phrase queries, top-10 rankedOur next benchmark compares TIN against ParadeDB and Postgres GIN, for top-10 conjunction and phrase queries, with no concurrent writes. TIN and ParadeDB rank using BM25, while GIN ranks using ts_rank_cd. TIN handles 10× as many queries as ParadeDB and 541× as many as GIN, with p99 latencies 6× and 1,356× lower, respectively. pg_textsearch is again absent because it handles only disjunction queries.Disjunction queries with concurrent writesOur third result compares TIN against both ParadeDB and pg_textsearch, for a workload with 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, with p99 latencies 24× and 36× lower, respectively. Over the course of a ten-minute run, TIN completes 270,279 updates, while ParadeDB completes 185,584, and pg_textsearch completes only 735.ParadeDB's approach to accepting writes sacrifices read throughput and latency. pg_textsearch maintains the same 3.5 QPS for readers both with and without writes because continuous read traffic prevents write traffic from ever getting the locks it needs, so writes stall after just a few seconds. GIN is again absent because it runs out of memory on disjunction queries.When the index fits in memoryIn the intro, we claimed that TIN is mind-blowingly fast.Our final graph shows what TIN, ParadeDB, and Postgres GIN can do when the index fully fits in shared buffers. This workload counts (but does not rank) the documents that match a disjunction query against Wikipedia, an 8.0 GB corpus. pg_textsearch is absent here because it can only perform top-k queries, not counting queries.Full resultsThat is perhaps enough graphs, but it doesn't cover all of our use cases. Here are those same scenarios, plus several more, in table form. The "MB/query" column shows how much data each index read from the disk or block cache for each query. TIN's lower numbers for MB/query are part of why it's faster, and they also reduce the impact of TIN queries on the block cache and I/O capacity, meaning that other queries on the same server stay fast, too.Conjunction, disjunction, and phrase queries; top-10 ┌────────────────────────────────────────────────────────────────────┐ │ QPS p99 MB/query Updates │ ├─────────────────────────┬───────┬──────────┬───────────┬───────────┤ │ TIN - read-only │ 199 │ 256ms │ 65 │ │ │ - with updates │ 172 │ 284ms │ 88 │ 271,398 │ ├─────────────────────────┼───────┼──────────┼───────────┼───────────┤ │ ParadeDB - read-only │ 7.9 │ 6,765ms │ 582 │ │ │ - with updates │ 6.0 │ 7,990ms │ 591 │ 193,487 │ └─────────────────────────┴───────┴──────────┴───────────┴───────────┘ Conjunction and phrase 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 │ │ │ - with updates │ 125 │ 354ms │ 77 │ 270,279 │ ├──────────────────────────────┼───────┼───────────┼─────────┼───────────┤ │ ParadeDB - read-only │ 17 │ 2,385ms │ 303 │ │ │ - with updates │ 2.2 │ 12,634ms │ 394 │ 185,584 │ ├──────────────────────────────┼───────┼───────────┼─────────┼───────────┤