Tin: full-text search for Postgres
Tin: full-text search for Postgres
Tin：Postgres 全文搜索引擎
Introducing TIN: full-text search for Postgres Eric Ridge, Patrick Reynolds | September 16, 2026
介绍 TIN：Postgres 全文搜索引擎 Eric Ridge, Patrick Reynolds | 2026年9月16日
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.
Postgres 用户向我们咨询最多的功能之一就是全文搜索。今天，我们很高兴宣布推出 TIN：一个为 Postgres 设计的快速、功能齐全且可靠的全文搜索扩展。TIN 代表“Text INdex”（文本索引），这正是它的核心功能。
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';
TIN 现已作为正式版本（GA）发布，适用于所有 Postgres 和 Neki 数据库。使用示例如下：
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 queries
- Fuzzy, wildcard, and regular-expression matching for terms
- Case and accent folding
- COUNT(*) queries and BM25-scored top-k queries
我们构建 TIN 是因为我们认为优秀的文本索引应该支持：
- 布尔表达式、短语查询和跨度查询
- 术语的模糊匹配、通配符匹配和正则表达式匹配
- 大小写和重音折叠
- COUNT(*) 查询和基于 BM25 评分的 Top-k 查询
A 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.
Postgres 中的优秀文本索引不仅必须支持上述所有功能，还必须能够处理连接（joins）、跨全文及其他列类型的复杂 WHERE 子句、持续更新、复制、备份以及正确的事务可见性。
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.
尽管目前已经至少有三种现有的 Postgres 文本搜索索引，但没有一个能满足所有这些要求。而 TIN 做到了。此外，TIN 的速度快得令人难以置信。
What TIN is for
TIN 的用途
Application 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
应用程序开发人员使用文本索引来构建各种搜索功能。例如，电子商务平台可能需要搜索包含所有搜索关键词的前十名产品：
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]'
法律取证平台可能需要返回包含一组关键词中一个或多个关键词的所有文档，且完全不需要考虑排名：
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"';
照片标记平台可能需要显示带有特定标签的照片的精确数量：
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 benchmarking
TIN 性能与基准测试
We 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.
我们进行了基准测试，以评估上述所有用例及更多场景的性能。我们测试的工作负载包括：
- 包含合取（必须包含所有词）、析取（必须包含任意词）和短语（必须按顺序包含所有词）查询，以及这三者的混合查询。
- 统计文档数量或按 BM25 评分请求 Top-k 结果的查询。
- 在基准查询工作负载的同时，有或没有客户端向索引写入新数据的场景。
Workloads and corpus
工作负载与语料库
We 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.
我们针对各种文本语料库对 TIN 进行了测量：包括整个维基百科、总计 2.3 TB 的 Reddit 评论集，以及我们称为“pile”的混合工作负载（包含 797 GB 的开放获取研究论文、法律文档、公有领域书籍和安然公司邮件）。本文分享的基准测试结果来自 Stack Exchange 的问答导出数据：一个包含 1.5 亿份文档、大小为 85 GB 的语料库。由于该语料库没有标准的查询跟踪，我们通过采样 2 到 15 个词的子字符串生成了一个合成查询集。我们将每个子字符串以三种方式进行解释：合取、析取和短语查询，总计 1,719 个查询。
Test environment
测试环境
We 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.
我们在配备本地 NVMe 存储和现代 AVX-512 CPU 的 AWS i7i.8xlarge EC2 实例上运行了基准测试。对于每个文本搜索扩展，我们在一个隔离的容器中设置了 Postgres 18.6，限制为 8 个 vCPU 和 32 GB RAM。这个配置足够小，可以展示当索引无法完全放入 Postgres 缓冲区时，每个索引系统的性能表现。基准测试阶段按顺序运行，因此各引擎之间不会争夺资源。我们选择独立的 EC2 实例是为了最大限度地减少操作开销和复制的影响，并确保任何想要复现我们对竞争文本搜索索引基准测试的人，都可以使用相同的实例类型和容器限制来完成。
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.
为了向 Postgres 容器发送搜索流量，我们使用了 ParadeDB Benchmarker。我们使用了一个分支版本，它在开始测量前进行预热，并增加了读取字节数和写入 WAL 字节数的指标。除了三个参数外，我们保留了 Benchmarker 提供的所有 Postgres 默认参数：我们将 max_parallel_workers 设置为 8（原为 40），shared_buffers 设置为 24 GB（原为 128 MB），maintenance_work_mem 设置为 24 GB（原为 64 MB），以最好地匹配容器资源。我们在与目标 Postgres 服务器相同的 EC2 实例上运行 Benchmarker，以确保网络延迟不会影响测量结果。
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.
对于每个场景，我们测量了 TIN v1.0.2 的性能，并将其与所有其他能够运行该工作负载的 Postgres 文本搜索索引进行了对比：ParadeDB v0.25.2、pg_textsearch v1.4.0 以及 Postgres v18.6 内置的 GIN 索引。除了 TIN 之外，只有 ParadeDB 能够完成所有基准测试。
Index build time 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 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.
索引大小占语料库的 33% 到 61%，准备、构建和完成索引的时间从 8 分钟到 129 分钟不等。除 TIN 外的三个引擎在容器配置的 32 GB 限制下均失败，因此仅在索引构建阶段，我们增加了可用 RAM（如下表所示）。在运行查询之前，我们将所有容器的 RAM 恢复为 32 GB。
| Engine | Total time | Index size | Required RAM |
|---|---|---|---|
| TIN | 8m10s | 50.7 GB | 32 GB |
| ParadeDB | 19m20s | 52.1 GB | 64 GB |
| pg_textsearch | 26m49s | 41.5 GB | 128 GB |
| Postgres GIN | 2h09m04s | 28.0 GB | 64 GB |
| 引擎 | 总时间 | 索引大小 | 所需 RAM |
|---|---|---|---|
| TIN | 8分10秒 | 50.7 GB | 32 GB |
| ParadeDB | 19分20秒 | 52.1 GB | 64 GB |
| pg_textsearch | 26分49秒 | 41.5 GB | 128 GB |
| Postgres GIN | 2小时09分04秒 | 28.0 GB | 64 GB |
Mixed queries, top-10 ranked
混合查询，Top-10 排名
Our 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.
我们的第一个基准测试将 TIN 与 ParadeDB 进行了比较，工作负载包含混合（合取、析取和短语）查询，按 BM25 评分返回 Top-10 结果，且索引没有并发写入。TIN 每秒处理的查询量是 ParadeDB 的 25 倍，p99 延迟降低了 26 倍。GIN 无法完成此基准测试，因为它在执行析取搜索时内存耗尽。pg_textsearch 无法完成此基准测试，因为它仅能处理析取搜索。