Get started with TIN

Install the extension

On PlanetScale, enable TIN with the SQL below. For a local or CI database, first set up Lead. The database encoding must be UTF8 or SQL_ASCII. CREATE EXTENSION tin refuses other encodings (for example, LATIN1).

CREATE EXTENSION IF NOT EXISTS tin;

If CREATE EXTENSION fails with permission denied to create extension "tin" and Must be superuser to create this extension., your cluster needs an update before it can install TIN. Go to the Clusters page for your branch, find the “Cluster update available” indicator, and update your cluster. After the update completes, run CREATE EXTENSION again.

Upgrade the extension

When a new TIN version is available, update your cluster so it restarts onto the new library. Then, in every database that already has the extension, run:

ALTER EXTENSION tin UPDATE;

The cluster update installs the new binary. ALTER EXTENSION updates the SQL catalog so new options and function signatures are visible. Changing analysis options such as stemming on a populated index also needs REINDEX.

Create a table and index

The example below creates a table, inserts rows, and creates a TIN index on the text column:

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

INSERT INTO posts (id, category, body) VALUES
  (1, 'fruit', 'I love fuji apples and juicy mangoes'),
  (2, 'tasting', 'Grape tasting notes from the orchard'),
  (3, 'fruit', 'The best juicy fuji apple in town');

CREATE INDEX posts_body_tin ON posts USING tin (body);

Each TIN index covers one text column (or a text-producing expression). The default tokenizer folds case and accents and indexes emoji as terms, so Jalapeño and jalapeno match, and ”😀” is searchable. The same tokenizer applies to indexed columns and queries. Stemming is off by default. Add WITH (stemmer = 'en') so runs, running, and run match as the same term. See Stemming. You can preview how a string is tokenized with tin.tokenize:

SELECT * FROM tin.tokenize('Jalapeño 😀');
SELECT * FROM tin.tokenize('Jalapeño 😀', accent_folding => 'preserve');
SELECT * FROM tin.tokenize('runs running runner', stemmer => 'en');

You can also create partial indexes, expression indexes, and set per-index BM25 and tokenization options with WITH (k1, b, tokenizer, stemmer, …). After changing analysis options on a populated index, REINDEX so existing rows are re-tokenized.

Your first queries

TINQL keywords are UPPERCASE. Lowercase tokens are terms. Quote a multi-word phrase (“fuji apple”).

Filter

SELECT id, body
FROM posts
WHERE body ==> 'apple AND "fuji apple"';

Ranked results (BM25)

Order responses in a ranked list with tin.score. Add more sort keys after the score when equal scores must stay in a stable order:

SELECT id, tin.score(ctid) AS score, body
FROM posts
WHERE body ==> 'apple OR grape'
ORDER BY score DESC, id
LIMIT 10;

To normalize scores against the query’s best match, divide by tin.max_score(ctid), which is constant for the scan and identical on every row:

SELECT id,
       tin.score(ctid) AS score,
       tin.score(ctid) / tin.max_score(ctid) AS relative
FROM posts
WHERE body ==> 'apple OR grape'
ORDER BY score DESC
LIMIT 10;

tin.score and tin.max_score require a TIN index scan in the same query. Outside that context, they raise an error rather than returning NULL.

Count

count(*) over a TIN predicate is answered from the index, not a heap scan.

SELECT count(*)
FROM posts
WHERE body ==> 'juicy';

Highlight

tin.highlight adds markers around the text that produced the match. A match on apple returns '<b>apple</b>'.

SELECT id, tin.highlight(body)
FROM posts
WHERE body ==> 'apple'
LIMIT 20;

tin.highlight is configurable, pass in additional arguments to customize the markers and perform the search.

SELECT tin.highlight(body, '<mark>', '</mark>', 'apple')
FROM posts
WHERE id = 1;

Search across columns

Each TIN index covers one text column. Index every column you want to search, then combine ==> in SQL. tin.score(ctid) combines BM25 relevance across those fields for the row. To weight one column higher than another, use TINQL boost (^N) on that field’s query. For example, name ==> 'fuji^1.5' makes a name match count 1.5 times an unboosted notes match.

CREATE TABLE fruits (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  notes text NOT NULL
);

CREATE INDEX fruits_name_tin ON fruits USING tin (name);
CREATE INDEX fruits_notes_tin ON fruits USING tin (notes);

SELECT id, tin.score(ctid) AS score, name, notes
FROM fruits
WHERE name ==> 'fuji^1.5'
  AND notes ==> 'citrus'
ORDER BY score DESC
LIMIT 10;

Local development and CI

Lead is a Postgres extension for exercising TIN-compatible application SQL in local development and CI. It uses the tin extension name so you can test your application’s search queries without connecting to PlanetScale. Lead supports Postgres 17 and 18 and is built from source. Follow the Lead README for build prerequisites, local setup, and packaging instructions for your test database.

Lead is intended for small development and test datasets. It scans table rows instead of maintaining TIN’s search index, so it is not suitable for production workloads or benchmarking TIN’s performance. Use TIN on PlanetScale for production search.

Common pitfalls

Symptom	Fix
Parse error on `==>`	Fix TINQL syntax. Keywords must be UPPERCASE (`AND`, not `and` as an operator). Explicit empty syntax (`""`, `[]`) is a parse error.
Zero rows	Broaden the query (`OR`, fewer required terms, wildcards). Confirm you indexed the column you are searching. Check tokenization with `tin.tokenize`. Inputs that analyze to no tokens (`''`, whitespace, bare punctuation) match nothing.
Score / max_score error	Call `tin.score(ctid)` / `tin.max_score(ctid)` on a query that also has `col ==> …`. They require a TIN index scan and error in other contexts (including DML `RETURNING` without a scan). On a partitioned parent, every planned leaf needs a usable TIN index — see limitations.
Exclusion not working	Use `AND NOT`, not `-term`. `-` is not negation.
`MATCHES` finds nothing	`MATCHES` is not folded. Match the dictionary form (usually lowercase, accents folded): `MATCHES apple.`, not `MATCHES Apple.`.
Accent / case mismatch	Defaults fold both. Search `jalapeno` to match `Jalapeño`. Override with `WITH (case_folding = preserve, accent_folding = preserve)` if you need exact surface forms.
Inflected forms miss	Stemming is off by default. Create the index `WITH (stemmer = 'en')` (or another language code), then `REINDEX` if the index already has rows.
Fuzzy error on hyphenated term	Fuzzy needs one token after tokenization. Prefer `wi-fi` as a phrase (`"wi fi"`) or drop `~N`.
Out-of-range `k1` / `b` / boost	`k1` and query boosts must be in `[0.0..10000.0]`; `b` must be in `[0.0..1.0]`.
Index keeps growing	The index relation grows to a high-water mark and does not shrink on its own. `REINDEX` shrinks it. See limitations.

Need help?

Get help from the PlanetScale Support team, or join our Discord community to see how others are using PlanetScale.

Onboarding

Platform

Guides

Install the extension

Upgrade the extension

Create a table and index

Your first queries

Filter

Ranked results (BM25)

Count

Highlight

Search across columns

Local development and CI

Common pitfalls

Need help?

​Install the extension

​Upgrade the extension

​Create a table and index

​Your first queries

​Filter

​Ranked results (BM25)

​Count

​Highlight

​Search across columns

​Local development and CI

​Common pitfalls

​Need help?

Install the extension

Upgrade the extension

Create a table and index

Your first queries

Filter

Ranked results (BM25)

Count

Highlight

Search across columns

Local development and CI

Common pitfalls

Need help?