Indexes

CREATE INDEX <index_name> ON <table_name> USING tin (<text_column>);

Indexes a text or citext column (or a text-producing expression) for TINQL search, including phrases, proximity, and spans. Documents and queries are analyzed with the same tokenization pipeline. Each TIN index covers one text source. Multi-column USING tin (…) indexes are not supported. To search several columns, create one TIN index per column and combine ==> predicates. tin.score(ctid) combines relevance across those columns.

-- Create index
CREATE INDEX posts_body_tin ON posts USING tin (body);

-- Build or rebuild without blocking writes
CREATE INDEX CONCURRENTLY posts_body_tin ON posts USING tin (body);
REINDEX INDEX CONCURRENTLY posts_body_tin;

Partial and expression indexes

Partial and expression TIN indexes work like other Postgres index types:

-- Partial: only index active rows
CREATE INDEX posts_active_body_tin ON posts USING tin (body)
WHERE active;

-- Expression: index a JSON field or transformed text
CREATE INDEX docs_title_tin ON docs USING tin ((data->>'title'));
CREATE INDEX posts_lower_body_tin ON posts USING tin ((lower(body)));

The query must match the indexed expression exactly. (data->>'title') ==> … uses the expression index, and a plain title ==> … does not. A partial index is used only when Postgres can prove the query implies its WHERE predicate (for example WHERE active AND body ==> …).

Index options (`WITH`)

CREATE INDEX posts_body_tin ON posts USING tin (body)
WITH (k1 = 1.2, b = 0.75);

ALTER INDEX posts_body_tin SET (k1 = 1.5);
ALTER INDEX posts_body_tin RESET (k1);

-- Tokenization policy (same pipeline at index time and query time)
CREATE INDEX logs_msg_tin ON logs USING tin (message)
WITH (
  tokenizer = whitespace,
  case_folding = preserve,
  accent_folding = preserve,
  long_tokens = truncate,
  max_token_bytes = 64,
  graphemes = discard,
  position_gaps = collapse
);

-- Optional Snowball stemming (requires case_folding = fold)
CREATE INDEX posts_body_en_tin ON posts USING tin (body)
WITH (stemmer = 'en');

Scoring

Option	Type	Default	Notes
`k1`	real	`1.2`	BM25 term-frequency saturation. Domain `[0.0..10000.0]`, enforced at DDL.
`b`	real	`0.75`	BM25 length normalization. Domain `[0.0..1.0]`.
`score_stop_words`	text	unset	Comma-separated terms that `tin.score` leaves out of the BM25 sum. Each entry must match the stored term exactly and is never tokenized, so write entries in analyzed form (lowercase on a case-folding index, and stemmed when a stemmer is set). Matching, counting, and phrases are unaffected.

Scoring options are read at query time, so ALTER INDEX ... SET takes effect on the next scored query without a rebuild. k1 and b can also be overridden for a single query with tin.score(ctid, k1 => …, b => …). tin.score_inspect shows which terms a query would score after stop words and dense-term elision are applied.

Tokenization

Option	Type	Default	Notes
`tokenizer`	text	`unicode`	Base token boundary policy: `unicode` or `whitespace`.
`case_folding`	text	`fold`	Unicode case folding: `fold` or `preserve`.
`stemmer`	text	unset	Snowball language code. Unset means no stemming. Requires `case_folding = fold`. See Stemming.
`accent_folding`	text	`fold`	Accent/diacritic folding: `fold` or `preserve`.
`long_tokens`	text	`split`	Tokens longer than `max_token_bytes`: `split`, `truncate`, or `discard`.
`max_token_bytes`	int	`256`	Maximum token length in UTF-8 bytes (`[4..2692]`).
`graphemes`	text	`emoji`	Standalone grapheme clusters (emoji, symbols): `emoji`, `retain`, or `discard`.
`position_gaps`	text	`preserve`	Position numbering when analysis removes tokens: `preserve` or `collapse`.

Defaults fold case and accents, split over-long tokens on grapheme boundaries, and emit emoji as searchable terms. Stemming is off until you set stemmer. Indexing and query analysis use the same pipeline, so ==> searches agree with what the index stored.

CREATE INDEX posts_body_tin ON posts USING tin (body);

SELECT id FROM posts WHERE body ==> 'jalapeno';  -- matches 'Jalapeño'
SELECT id FROM posts WHERE body ==> '😀';         -- emoji are terms

Use tin.tokenize to inspect the pipeline. Named arguments match the index options and use the same defaults:

SELECT * FROM tin.tokenize('Jalapeño 😀');
-- default policy: case + accent folding, emoji as a term

SELECT * FROM tin.tokenize('Jalapeño 😀', accent_folding => 'preserve');

Hyphenated surface forms may still split into multiple tokens (for example wi-fi becomes the phrase "wi fi"). Fuzzy (term~N) requires a single token after tokenization. Changing tokenization options on a populated index does not re-tokenize stored rows. Run REINDEX to apply the new policy to them.

Stemming

stemmer applies Snowball stemming after lowercasing and before accent folding. It is unset by default. Set an ISO language code to match inflected words as the same term:

CREATE INDEX posts_body_en_tin ON posts USING tin (body)
WITH (stemmer = 'en');

SELECT * FROM tin.tokenize('runs running runner', stemmer => 'en');
-- run
-- run
-- runner

English run, runs, and running share the stored term run. runner stays a separate term. Stemming requires case_folding = fold; unknown language codes and case_folding = preserve with a stemmer fail at CREATE / ALTER. Ordinary query terms and phrases use the same stemmer as stored text. Wildcard, fuzzy, and range literals skip stemming and search the stored stems. Regular expressions are unchanged. score_stop_words still match stored terms exactly, so write them in stemmed form on a stemmed index.

Code	Language	Code	Language
`ar`	Arabic	`da`	Danish
`nl`	Dutch	`en`	English
`fi`	Finnish	`fr`	French
`de`	German	`el`	Greek
`hu`	Hungarian	`it`	Italian
`no`	Norwegian	`pt`	Portuguese
`ro`	Romanian	`ru`	Russian
`es`	Spanish	`sv`	Swedish
`ta`	Tamil	`tr`	Turkish

Enable, change, or disable stemming and rebuild together so existing rows and later queries agree:

BEGIN;
ALTER INDEX posts_body_tin SET (stemmer = 'en');
REINDEX INDEX posts_body_tin;
COMMIT;

ALTER INDEX ... RESET (stemmer) turns stemming off. That also needs REINDEX on a populated index.

Segments

A TIN index is stored as segments. The segment count sets how many parallel workers a build and a query can use, and background maintenance folds new writes into segments over time.

Option	Type	Default	Notes
`initial_segment_count`	int	available parallelism	Segments created at build (`[1..4096]`). Read at `CREATE INDEX` and `REINDEX`, and used as the default for `target_segment_count`.
`target_segment_count`	int	= `initial_segment_count`	Segment count that background maintenance converges to as writes add segments (`[1..4096]`).
`max_mutable_segment_size`	int	`4194304` (4 MiB)	Bytes of new writes that accumulate before background maintenance folds them into the index (`≥ 131072`). Folding also happens every 16,384 documents.
`max_merged_segment_size`	int	`2000`	Size in megabytes above which a segment is no longer merged with others (`≥ 100`).
`dead_percent_threshold`	real	`0.5`	Fraction of dead rows at which a segment is rewritten to drop them. Domain `[0.0..1.0]`.

Changes to these options take effect on the next maintenance pass, except initial_segment_count, which is read only at build.

Need help?

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

Onboarding

Platform

Guides

Partial and expression indexes

Index options (`WITH`)

Scoring

Tokenization

Stemming

Segments

Need help?

​Partial and expression indexes

​Index options (WITH)

​Scoring

​Tokenization

​Stemming

​Segments

​Need help?

Partial and expression indexes

Index options (`WITH`)

Scoring

Tokenization

Stemming

Segments

Need help?