TIN: PlanetScale Postgres Search

​

A search engine inside Postgres

​

Compared with other Postgres search

​

Key features

• The ==> operator. WHERE body ==> '…' searches a TIN-indexed column with a TINQL string. ==> is boolean: the row matches if the text satisfies the query.
• TIN index. Create a TIN index with CREATE INDEX ... USING tin on a text column.
• BM25 ranking with top-k retrieval. tin.score(ctid) returns the BM25 relevance of each matching row, and ORDER BY tin.score(ctid) DESC LIMIT 10 returns the 10 best documents in score order. The same row gets the same score however the query is executed. tin.max_score(ctid) gives a denominator for normalized relevance, and k1 and b can be tuned per index or per query.
• The TINQL query language. One query string expresses phrases with word gaps, ordered and unordered proximity (THEN/5, NEAR/5), span relations (ENCLOSES, OVERLAPPING, BEFORE, AFTER), positional filters (IN FIRST 100 WORDS), wildcards, fuzzy terms, regular expressions, term ranges, AT LEAST 2 OF, and boosts. Every expression produces spans, so the operators compose freely.
• Exact counts. count(*) with WHERE body ==> '…' is answered from the index and stays exact under concurrent writes, VACUUM, and on read replicas.
• Highlighting. tin.highlight adds configurable markers around the text that produced the match. When highlighted, a match on pineapple returns '<b>pineapple</b>'. tin.highlight_ansi does the same for terminals.
• Cross-column scoring. Search several TIN-indexed columns in one query, and tin.score(ctid) combines relevance across them.
• Configurable tokenization. The default tokenizer runs on indexed columns and queries, splitting text on Unicode word boundaries, folding case and accents, and indexes emoji as terms.
• Parallel build and parallel query. Index builds use parallel workers, and based on cost, queries and count(*) can run in parallel too.

​

TIN example

CREATE EXTENSION IF NOT EXISTS tin;

CREATE TABLE posts (
  id   bigint PRIMARY KEY,
  body text NOT NULL
);

INSERT INTO posts (id, body) VALUES
  (1, 'Fuji apple slices with citrus and melon make a bright fruit salad'),
  (2, 'Fuji apple, citrus, and melon for lunch'),
  (3, 'Peel the fuji apple and toss it with citrus and melon'),
  (4, 'Citrus and melon salad with fresh mint'),
  (5, 'Grape tasting notes from the orchard');

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;

​

Hybrid search with pgvector

CREATE TABLE posts (
  id bigint PRIMARY KEY,
  body text NOT NULL,
  embedding vector(1536) NOT NULL
);

CREATE INDEX posts_body_tin ON posts USING tin (body);
CREATE INDEX posts_embedding ON posts USING hnsw (embedding vector_cosine_ops);

-- Lexical: words and phrases in the query
SELECT id
FROM posts
WHERE body ==> '"fuji apple"'
ORDER BY tin.score(ctid) DESC
LIMIT 10;

-- Semantic: embedding of that same query, from the same model that embedded `body`
SELECT id
FROM posts
ORDER BY embedding <=> $query_embedding
LIMIT 10;

​

Next steps

• Get started — install the extension, index a column, run your first query
• Scoring — BM25 relevance and top-k retrieval
• Highlighting — mark the text that matched
• TINQL — the query language
• Operational guidance — vacuum settings for tables with churn
• Reference — index options and the SQL API

​

Need help?