Full-text and trigram search in PostgreSQL

lexemes_session.py

"""One sentence through to_tsvector, the tsquery parsers (to_tsquery's two syntax errors are the errors the session
expects), and @@ matching on PostgreSQL 18.6."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off", "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("lexemes.sql", errors=2)

$ psql -X -a -f lexemes.sql
-- One short sentence through to_tsvector, three ways to turn user text into a tsquery, and @@ matching.
SELECT to_tsvector('english',
  'Running shoes for the monsoon rains in Mumbai: waterproof and quick drying');
                                     to_tsvector                                     
-------------------------------------------------------------------------------------
 'dri':12 'monsoon':5 'mumbai':8 'quick':11 'rain':6 'run':1 'shoe':2 'waterproof':9
(1 row)

-- The three parsers. plainto_tsquery joins the lexemes with &, phraseto_tsquery with <N> for word distance,
-- and websearch_to_tsquery handles "quoted phrases", OR between words and a leading - that drops a word; it also
-- forgives text that would be a syntax error for to_tsquery (an unclosed quote, a stray punctuation).
SELECT plainto_tsquery('english', 'running shoes') AS plain,
       phraseto_tsquery('english', 'running shoes') AS phrase,
       websearch_to_tsquery('english', '"monsoon rains" OR waterproof -mumbai') AS web;
     plain      |      phrase      |                       web                       
----------------+------------------+-------------------------------------------------
 'run' & 'shoe' | 'run' <-> 'shoe' | 'monsoon' <-> 'rain' | 'waterproof' & !'mumbai'
(1 row)

-- to_tsquery refuses words with no operator between them, with or without an unclosed quote; websearch_to_tsquery
-- builds a query from the same text.
SELECT to_tsquery('english', 'monsoon rains or snow');
psql:lexemes.sql:12: ERROR:  syntax error in tsquery: "monsoon rains or snow"
SELECT to_tsquery('english', '"monsoon rains or snow');
psql:lexemes.sql:13: ERROR:  syntax error in tsquery: ""monsoon rains or snow"
SELECT websearch_to_tsquery('english', '"monsoon rains or snow') AS forgiving;
            forgiving            
---------------------------------
 'monsoon' <-> 'rain' <2> 'snow'
(1 row)

-- Matching with @@: the phrase in order matches; the swapped phrase and the query without shoes do not.
WITH sentence AS (
  SELECT to_tsvector('english',
    'Running shoes for the monsoon rains in Mumbai: waterproof and quick drying') AS v
)
SELECT v @@ phraseto_tsquery('english', 'monsoon rains')  AS exact_phrase,
       v @@ phraseto_tsquery('english', 'rains monsoon')  AS swapped,
       v @@ websearch_to_tsquery('english', 'quick -shoes') AS has_quick_without_shoes
FROM sentence;
 exact_phrase | swapped | has_quick_without_shoes 
--------------+---------+-------------------------
 t            | f       | f
(1 row)

lexemes.sql

-- One short sentence through to_tsvector, three ways to turn user text into a tsquery, and @@ matching.
SELECT to_tsvector('english',
  'Running shoes for the monsoon rains in Mumbai: waterproof and quick drying');
-- The three parsers. plainto_tsquery joins the lexemes with &, phraseto_tsquery with <N> for word distance,
-- and websearch_to_tsquery handles "quoted phrases", OR between words and a leading - that drops a word; it also
-- forgives text that would be a syntax error for to_tsquery (an unclosed quote, a stray punctuation).
SELECT plainto_tsquery('english', 'running shoes') AS plain,
       phraseto_tsquery('english', 'running shoes') AS phrase,
       websearch_to_tsquery('english', '"monsoon rains" OR waterproof -mumbai') AS web;
-- to_tsquery refuses words with no operator between them, with or without an unclosed quote; websearch_to_tsquery
-- builds a query from the same text.
SELECT to_tsquery('english', 'monsoon rains or snow');
SELECT to_tsquery('english', '"monsoon rains or snow');
SELECT websearch_to_tsquery('english', '"monsoon rains or snow') AS forgiving;
-- Matching with @@: the phrase in order matches; the swapped phrase and the query without shoes do not.
WITH sentence AS (
  SELECT to_tsvector('english',
    'Running shoes for the monsoon rains in Mumbai: waterproof and quick drying') AS v
)
SELECT v @@ phraseto_tsquery('english', 'monsoon rains')  AS exact_phrase,
       v @@ phraseto_tsquery('english', 'rains monsoon')  AS swapped,
       v @@ websearch_to_tsquery('english', 'quick -shoes') AS has_quick_without_shoes
FROM sentence;

pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.

Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
Besides PostgreSQL's own programs, the sessions read only this folder (and an extension's own folder where a lesson
needs one), and they connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. A session may name more patterns (mask=) for numbers that change on
every run, such as the rates and times ANALYZE VERBOSE prints or the random key of pg_dump's \\restrict line; the
session's own file says which, and its record shows a fixed word in their place. The sessions never print the port.
A session stops with status 1 when a command fails that should have worked, or reports a different number of errors
than the session expects, so a run that went wrong is never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""

    def __init__(self, settings=None, initdb=(), name="data"):
        self.settings = dict(settings or {})
        self.initdb_args = list(initdb)
        self.name = name
        self.running = []

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        (self.base / "pwfile").write_text(self.password + "\n")
        self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
                    "PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "60"}
        for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
            self.env.pop(key, None)
        for script in Path(".").glob("*.sql"):
            shutil.copy(script, self.base / script.name)
        for data in Path(".").glob("*.csv"):
            shutil.copy(data, self.base / data.name)
        return self

    def __exit__(self, *exc):
        for folder, _port in reversed(self.running):
            self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
        self.running = []
        shutil.rmtree(self.base, ignore_errors=True)

    # ---- the cluster ---------------------------------------------------------------------------------------------
    def initdb(self, show=False):
        args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
                *self.initdb_args]
        if not show:
            args.append("--no-instructions")
        self._expect(args, show=show)
        with open(self.base / self.name / "postgresql.conf", "a") as conf:
            conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
            conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
            for key, value in self.settings.items():
                conf.write(f"{key} = {value}\n")

    def start(self, show=False, folder=None, port=None):
        folder = folder or self.name
        self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
        self.running.append((folder, port or self.port))

    def up(self):
        """initdb and start, quietly."""
        self.initdb()
        self.start()
        return self

    def standby(self, folder="standby"):
        """A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
        self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
        port = free_port()
        with open(self.base / folder / "postgresql.conf", "a") as conf:
            conf.write(f"port = {port}\n")
        self.start(folder=folder, port=port)
        for _ in range(300):
            if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
                return port
            time.sleep(0.1)
        sys.exit("the standby did not start streaming")

    # ---- running things ------------------------------------------------------------------------------------------
    def _call(self, args, env=None):
        args = [str(a) for a in args]
        done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
                              text=True, cwd=self.base, env=env or self.env)
        return done.returncode, self.clean(done.stdout)

    def _expect(self, args, show=False, status=0, mask=()):
        code, out = self._call(args)
        out = _masked(out, mask)
        if show:
            _show(args, out)
        if code != status:
            if not show:
                print(out)
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
        text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
        text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
        return text.replace(f'"{OS_USER}"', '"learner"')

    def wait_for(self, sql, value):
        """Waits (unprinted) until a query returns the value, for example after a reload."""
        for _ in range(300):
            if self.query(sql) == value:
                return
            time.sleep(0.1)
        sys.exit(f"{sql} never returned {value}")

    def show_file(self, path):
        """Prints a text file of the cluster, as `cat` would."""
        print(f"$ cat {path}")
        print((self.base / path).read_text().rstrip("\n"))
        print()

    def query(self, sql, port=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only to give a role the run's password)."""
        self.query(sql)

    def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None, mask=()):
        """Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
        `errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        if user:
            env["PGUSER"] = user
        if db:
            env["PGDATABASE"] = db
        args = ["psql", "-X", *flags, "-f", script]
        code, out = self._call(args, env=env)
        out = _masked(out, mask)
        shown = ["psql", "-X", *flags, "-f", script]
        if user:
            shown[1:1] = ["-U", user]
        if db:
            shown[1:1] = ["-d", db]
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
        expected_status = 0 if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")

    def sessions(self, count):
        """Opens `count` idle psql connections (each one a client backend) until the with block ends."""
        return _Idle(self, count)

    def shell(self, *args, status=0, mask=()):
        """Runs one of PostgreSQL's programs and prints the command and its output."""
        return self._expect(list(args), show=True, status=status, mask=mask)

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
        print(textwrap.fill("  ".join(names), width=78, initial_indent="   ", subsequent_indent="   ",
                            break_on_hyphens=False))
        print()
        return names


def _masked(text, mask):
    """Replaces what differs on every run (a rate, a time, a random key) with fixed text (see the docstring)."""
    for pattern, replacement in mask:
        text = re.sub(pattern, replacement, text, flags=re.M)
    return text


def _arg(a):
    """A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


class _Idle:
    """Idle psql connections, closed again when the with block ends."""

    def __init__(self, server, count):
        self.server, self.count, self.procs = server, count, []

    def __enter__(self):
        before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
        for _ in range(self.count):
            self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
                                               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
                                               env=self.server.env, cwd=self.server.base))
        sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
        for _ in range(300):
            # the query's own connection is one more client backend
            if int(self.server.query(sql)) == before + self.count:
                return self
            time.sleep(0.1)
        sys.exit("the idle connections did not open")

    def __exit__(self, *exc):
        for p in self.procs:
            p.stdin.close()
            p.wait(timeout=30)

search_session.py

"""20,000 generated articles with a STORED weighted tsvector and a GIN index, on PostgreSQL 18.6. The one error the
session expects is CREATE INDEX on a virtual generated column, which PostgreSQL 18 refuses."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off", "timezone": "'UTC'",
            "max_parallel_maintenance_workers": 0}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("search.sql", errors=1)

$ psql -X -a -f search.sql
-- 20,000 articles, built from generate_series and fixed word lists.
CREATE TABLE articles (
  id    int PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL,
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A')
    || setweight(to_tsvector('english', body), 'B')
  ) STORED
);
CREATE TABLE
-- Without STORED, PostgreSQL 18 makes the column VIRTUAL (computed on read), which cannot be indexed.
CREATE TABLE drafts (t text, v tsvector GENERATED ALWAYS AS (to_tsvector('english', t)));
CREATE TABLE
CREATE INDEX ON drafts USING GIN (v);
psql:search.sql:13: ERROR:  indexes on virtual generated columns are not supported
INSERT INTO articles(id, title, body)
SELECT g, format('%s shoes for the %s', kind, season),
       format('A guide to picking %s shoes in Mumbai during the %s season. Reviews, prices and fit tips.', kind, season)
FROM generate_series(1, 20000) AS g,
     LATERAL (SELECT (ARRAY['running','walking','trail','trekking','rain'])[1 + (g % 5)] AS kind,
                     (ARRAY['monsoon','winter','summer','marathon','city'])[1 + ((g/5) % 5)] AS season) AS w;
INSERT 0 20000
-- Row 7 puts the two words side by side in its title and in its body.
UPDATE articles SET title = 'Monsoon running shoes buyer guide',
                   body  = 'The best monsoon running shoes for Mumbai, with grip, waterproofing and quick drying.'
 WHERE id = 7;
UPDATE 1
CREATE INDEX articles_search_gin ON articles USING GIN (search);
CREATE INDEX
ANALYZE articles;
ANALYZE
-- The plan of one search.
EXPLAIN (COSTS OFF)
SELECT id FROM articles WHERE search @@ websearch_to_tsquery('english', 'monsoon running');
                            QUERY PLAN                            
------------------------------------------------------------------
 Bitmap Heap Scan on articles
   Recheck Cond: (search @@ '''monsoon'' & ''run'''::tsquery)
   ->  Bitmap Index Scan on articles_search_gin
         Index Cond: (search @@ '''monsoon'' & ''run'''::tsquery)
(4 rows)

-- Positions and weights of the two lexemes in rows 7 and 25.
SELECT id, lexeme, positions, weights FROM articles, unnest(search)
WHERE id IN (7, 25) AND lexeme IN ('monsoon', 'run') ORDER BY id, lexeme;
 id | lexeme  | positions | weights 
----+---------+-----------+---------
  7 | monsoon | {1,8}     | {A,B}
  7 | run     | {2,9}     | {A,B}
 25 | monsoon | {5,16}    | {A,B}
 25 | run     | {1,10}    | {A,B}
(4 rows)

-- Matches per score, under ts_rank_cd and under ts_rank, which ignores proximity.
SELECT round(ts_rank_cd(search, q)::numeric, 4) AS rank_cd, round(ts_rank(search, q)::numeric, 4) AS rank,
       count(*) AS matches
FROM articles, websearch_to_tsquery('english', 'monsoon running') AS q
WHERE search @@ q
GROUP BY 1, 2 ORDER BY 1 DESC;
 rank_cd |  rank  | matches 
---------+--------+---------
  1.4952 | 0.9985 |       1
  0.4310 | 0.9875 |     800
(2 rows)

-- The top three, ties broken by id, with snippets.
SELECT id, round(ts_rank_cd(search, q)::numeric, 4) AS rank_cd,
       ts_headline('english', title || ' - ' || body, q,
                   'StartSel=[,StopSel=],MaxFragments=1,MaxWords=10,MinWords=5') AS snippet
FROM articles, websearch_to_tsquery('english', 'monsoon running') AS q
WHERE search @@ q
ORDER BY ts_rank_cd(search, q) DESC, id
LIMIT 3;
 id | rank_cd |                                  snippet                                   
----+---------+----------------------------------------------------------------------------
  7 |  1.4952 | [Monsoon] [running] shoes buyer guide - The best [monsoon] [running] shoes
 25 |  0.4310 | [running] shoes for the [monsoon] - A guide to picking [running]
 50 |  0.4310 | [running] shoes for the [monsoon] - A guide to picking [running]
(3 rows)

search.sql

-- 20,000 articles, built from generate_series and fixed word lists.
CREATE TABLE articles (
  id    int PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL,
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A')
    || setweight(to_tsvector('english', body), 'B')
  ) STORED
);
-- Without STORED, PostgreSQL 18 makes the column VIRTUAL (computed on read), which cannot be indexed.
CREATE TABLE drafts (t text, v tsvector GENERATED ALWAYS AS (to_tsvector('english', t)));
CREATE INDEX ON drafts USING GIN (v);
INSERT INTO articles(id, title, body)
SELECT g, format('%s shoes for the %s', kind, season),
       format('A guide to picking %s shoes in Mumbai during the %s season. Reviews, prices and fit tips.', kind, season)
FROM generate_series(1, 20000) AS g,
     LATERAL (SELECT (ARRAY['running','walking','trail','trekking','rain'])[1 + (g % 5)] AS kind,
                     (ARRAY['monsoon','winter','summer','marathon','city'])[1 + ((g/5) % 5)] AS season) AS w;
-- Row 7 puts the two words side by side in its title and in its body.
UPDATE articles SET title = 'Monsoon running shoes buyer guide',
                   body  = 'The best monsoon running shoes for Mumbai, with grip, waterproofing and quick drying.'
 WHERE id = 7;
CREATE INDEX articles_search_gin ON articles USING GIN (search);
ANALYZE articles;
-- The plan of one search.
EXPLAIN (COSTS OFF)
SELECT id FROM articles WHERE search @@ websearch_to_tsquery('english', 'monsoon running');
-- Positions and weights of the two lexemes in rows 7 and 25.
SELECT id, lexeme, positions, weights FROM articles, unnest(search)
WHERE id IN (7, 25) AND lexeme IN ('monsoon', 'run') ORDER BY id, lexeme;
-- Matches per score, under ts_rank_cd and under ts_rank, which ignores proximity.
SELECT round(ts_rank_cd(search, q)::numeric, 4) AS rank_cd, round(ts_rank(search, q)::numeric, 4) AS rank,
       count(*) AS matches
FROM articles, websearch_to_tsquery('english', 'monsoon running') AS q
WHERE search @@ q
GROUP BY 1, 2 ORDER BY 1 DESC;
-- The top three, ties broken by id, with snippets.
SELECT id, round(ts_rank_cd(search, q)::numeric, 4) AS rank_cd,
       ts_headline('english', title || ' - ' || body, q,
                   'StartSel=[,StopSel=],MaxFragments=1,MaxWords=10,MinWords=5') AS snippet
FROM articles, websearch_to_tsquery('english', 'monsoon running') AS q
WHERE search @@ q
ORDER BY ts_rank_cd(search, q) DESC, id
LIMIT 3;

config_session.py

"""Text search configurations and unaccent on PostgreSQL 18.6: what the server ships, lexemes under simple and
hindi, a configuration the server lacks (the one error the session expects), the volatility of unaccent, and the
IMMUTABLE wrapper an expression index needs."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off", "timezone": "'UTC'"}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("config.sql", errors=1)

$ psql -X -a -f config.sql
-- What text search configurations this server actually ships. Only the ones in this list can parse text.
SELECT cfgname FROM pg_ts_config ORDER BY 1;
  cfgname   
------------
 arabic
 armenian
 basque
 catalan
 danish
 dutch
 english
 estonian
 finnish
 french
 german
 greek
 hindi
 hungarian
 indonesian
 irish
 italian
 lithuanian
 nepali
 norwegian
 portuguese
 romanian
 russian
 serbian
 simple
 spanish
 swedish
 tamil
 turkish
 yiddish
(30 rows)

-- A short Hindi sentence through two configurations. simple lower-cases each word and keeps it whole, particles
-- included; hindi sends each Devanagari word through the Snowball Hindi stemmer.
SELECT to_tsvector('simple', 'मुंबई में अच्छे बरसाती जूते') AS simple_lexemes;
             simple_lexemes              
-----------------------------------------
 'अच्छे':3 'जूते':5 'बरसाती':4 'मुंबई':1 'में':2
(1 row)

SELECT to_tsvector('hindi',  'मुंबई में अच्छे बरसाती जूते') AS hindi_lexemes;
            hindi_lexemes            
-------------------------------------
 'अच्छ':3 'जूत':5 'बरस':4 'म':2 'मुंब':1
(1 row)

-- What the stemmer buys: a search for the singular 'जूता' (a shoe) against a row that says 'जूते' (shoes).
SELECT to_tsvector('simple', 'जूते') @@ plainto_tsquery('simple', 'जूता') AS simple_matches,
       to_tsvector('hindi',  'जूते') @@ plainto_tsquery('hindi',  'जूता') AS hindi_matches;
 simple_matches | hindi_matches 
----------------+---------------
 f              | t
(1 row)

-- A language the list does not have is an error, not a fallback.
SELECT to_tsvector('marathi', 'मुंबईत पावसाळी बूट');
psql:config.sql:11: ERROR:  text search configuration "marathi" does not exist
LINE 1: SELECT to_tsvector('marathi', 'मुंबईत पावसाळी बूट');
                           ^
-- unaccent removes accents and other diacritics, as its rules file lists them.
CREATE EXTENSION unaccent;
CREATE EXTENSION
SELECT unaccent('München Café résumé') AS unaccented;
     unaccented      
---------------------
 Munchen Cafe resume
(1 row)

-- Why unaccent cannot be used in an expression index as it is: pg_proc records the single-argument form as
-- STABLE (provolatile = 's'), not IMMUTABLE, because its dictionary can change. The fix is a small IMMUTABLE
-- wrapper that names the function and the dictionary with their schema; CREATE INDEX then accepts it.
SELECT proname, provolatile FROM pg_proc WHERE proname = 'unaccent' AND pronargs = 1;
 proname  | provolatile 
----------+-------------
 unaccent | s
(1 row)

CREATE FUNCTION f_unaccent(text) RETURNS text AS $$
  SELECT public.unaccent('public.unaccent'::regdictionary, $1)
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;
CREATE FUNCTION
CREATE TABLE cafes (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE
INSERT INTO cafes VALUES (1, 'Café'), (2, 'München'), (3, 'Résumé');
INSERT 0 3
CREATE INDEX cafes_name_lower_unaccent ON cafes (lower(f_unaccent(name)));
CREATE INDEX
-- A search for 'cafe' now reaches the row stored as 'Café'.
SELECT id, name FROM cafes WHERE lower(f_unaccent(name)) = lower(f_unaccent('cafe'));
 id | name 
----+------
  1 | Café
(1 row)

config.sql

-- What text search configurations this server actually ships. Only the ones in this list can parse text.
SELECT cfgname FROM pg_ts_config ORDER BY 1;
-- A short Hindi sentence through two configurations. simple lower-cases each word and keeps it whole, particles
-- included; hindi sends each Devanagari word through the Snowball Hindi stemmer.
SELECT to_tsvector('simple', 'मुंबई में अच्छे बरसाती जूते') AS simple_lexemes;
SELECT to_tsvector('hindi',  'मुंबई में अच्छे बरसाती जूते') AS hindi_lexemes;
-- What the stemmer buys: a search for the singular 'जूता' (a shoe) against a row that says 'जूते' (shoes).
SELECT to_tsvector('simple', 'जूते') @@ plainto_tsquery('simple', 'जूता') AS simple_matches,
       to_tsvector('hindi',  'जूते') @@ plainto_tsquery('hindi',  'जूता') AS hindi_matches;
-- A language the list does not have is an error, not a fallback.
SELECT to_tsvector('marathi', 'मुंबईत पावसाळी बूट');
-- unaccent removes accents and other diacritics, as its rules file lists them.
CREATE EXTENSION unaccent;
SELECT unaccent('München Café résumé') AS unaccented;
-- Why unaccent cannot be used in an expression index as it is: pg_proc records the single-argument form as
-- STABLE (provolatile = 's'), not IMMUTABLE, because its dictionary can change. The fix is a small IMMUTABLE
-- wrapper that names the function and the dictionary with their schema; CREATE INDEX then accepts it.
SELECT proname, provolatile FROM pg_proc WHERE proname = 'unaccent' AND pronargs = 1;
CREATE FUNCTION f_unaccent(text) RETURNS text AS $$
  SELECT public.unaccent('public.unaccent'::regdictionary, $1)
$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;
CREATE TABLE cafes (id int PRIMARY KEY, name text NOT NULL);
INSERT INTO cafes VALUES (1, 'Café'), (2, 'München'), (3, 'Résumé');
CREATE INDEX cafes_name_lower_unaccent ON cafes (lower(f_unaccent(name)));
-- A search for 'cafe' now reaches the row stored as 'Café'.
SELECT id, name FROM cafes WHERE lower(f_unaccent(name)) = lower(f_unaccent('cafe'));

trigrams_session.py

"""pg_trgm on PostgreSQL 18.6: show_trgm, similarity, the % operator at the default threshold, which characters make
trigrams, and a GIN trigram index that turns ILIKE '%...%' from a Seq Scan into a Bitmap Index Scan once the pattern
holds a trigram."""

from pg_session import Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off", "timezone": "'UTC'",
            "max_parallel_maintenance_workers": 0}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("trigrams.sql")

$ psql -X -a -f trigrams.sql
-- Trigrams: the extension records every three-character window of each word (with two spaces in front of the word
-- and one behind it), and compares strings by how many trigrams they share.
CREATE EXTENSION pg_trgm;
CREATE EXTENSION
SELECT show_trgm('postgresql');
                      show_trgm                      
-----------------------------------------------------
 {"  p"," po",esq,gre,ost,pos,"ql ",res,sql,stg,tgr}
(1 row)

-- A typo and the correct spelling share many trigrams.
SELECT similarity('postgresql', 'postgersql') AS score;
   score    
------------
 0.46666667
(1 row)

-- The default threshold the % operator uses.
SHOW pg_trgm.similarity_threshold;
 pg_trgm.similarity_threshold 
------------------------------
 0.3
(1 row)

SELECT 'postgresql' % 'postgersql' AS over_threshold;
 over_threshold 
----------------
 t
(1 row)

-- Only characters this server counts as letters or digits make trigrams: here the Devanagari vowel signs do not,
-- so 'जूते' (shoes) and 'जूता' (a shoe) compare as the same string.
SELECT similarity('जूते', 'जूता') AS shoes_vs_shoe;
 shoes_vs_shoe 
---------------
             1
(1 row)

-- A table of 20,000 names, built deterministically with setseed before any random().
SELECT setseed(0.42);
 setseed 
---------
 
(1 row)

CREATE TABLE names (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE
INSERT INTO names(id, name)
SELECT g,
       (ARRAY['Ananya','Arjun','Bhavna','Chetan','Divya','Esha','Farhan','Gaurav','Harini','Ishan',
              'Jaya','Kiran','Leela','Manish','Nisha','Omkar','Priya','Rahul','Sneha','Tanvi'])[1 + floor(random()*20)::int]
       || ' '
       || (ARRAY['Agarwal','Banerjee','Chatterjee','Deshpande','Iyer','Joshi','Kapoor','Mehta','Nair','Patel',
                 'Rao','Shah','Singh','Verma','Yadav','Pillai','Reddy','Khan','Das','Bose'])[1 + floor(random()*20)::int]
FROM generate_series(1, 20000) AS g;
INSERT 0 20000
ANALYZE names;
ANALYZE
-- Before an index: an ILIKE with leading and trailing wildcards reads every row.
EXPLAIN (COSTS OFF)
SELECT id, name FROM names WHERE name ILIKE '%meht%';
             QUERY PLAN              
-------------------------------------
 Seq Scan on names
   Filter: (name ~~* '%meht%'::text)
(2 rows)

-- With a GIN trigram index the same ILIKE becomes a Bitmap Index Scan.
CREATE INDEX names_trgm ON names USING GIN (name gin_trgm_ops);
CREATE INDEX
EXPLAIN (COSTS OFF)
SELECT id, name FROM names WHERE name ILIKE '%meht%';
                  QUERY PLAN                   
-----------------------------------------------
 Bitmap Heap Scan on names
   Recheck Cond: (name ~~* '%meht%'::text)
   ->  Bitmap Index Scan on names_trgm
         Index Cond: (name ~~* '%meht%'::text)
(4 rows)

-- Two letters between the wildcards hold no trigram to look up, so the index cannot narrow the search.
EXPLAIN (COSTS OFF)
SELECT id, name FROM names WHERE name ILIKE '%me%';
            QUERY PLAN             
-----------------------------------
 Seq Scan on names
   Filter: (name ~~* '%me%'::text)
(2 rows)

-- A misspelt name with the % operator, which the same trigram index can answer.
SELECT DISTINCT name, round(similarity(name, 'Nisha Mehtaa')::numeric, 4) AS sim
FROM names WHERE name % 'Nisha Mehtaa' ORDER BY sim DESC, name LIMIT 3;
    name     |  sim   
-------------+--------
 Nisha Mehta | 0.7857
 Esha Mehta  | 0.4118
 Ishan Mehta | 0.3889
(3 rows)

trigrams.sql

-- Trigrams: the extension records every three-character window of each word (with two spaces in front of the word
-- and one behind it), and compares strings by how many trigrams they share.
CREATE EXTENSION pg_trgm;
SELECT show_trgm('postgresql');
-- A typo and the correct spelling share many trigrams.
SELECT similarity('postgresql', 'postgersql') AS score;
-- The default threshold the % operator uses.
SHOW pg_trgm.similarity_threshold;
SELECT 'postgresql' % 'postgersql' AS over_threshold;
-- Only characters this server counts as letters or digits make trigrams: here the Devanagari vowel signs do not,
-- so 'जूते' (shoes) and 'जूता' (a shoe) compare as the same string.
SELECT similarity('जूते', 'जूता') AS shoes_vs_shoe;
-- A table of 20,000 names, built deterministically with setseed before any random().
SELECT setseed(0.42);
CREATE TABLE names (id int PRIMARY KEY, name text NOT NULL);
INSERT INTO names(id, name)
SELECT g,
       (ARRAY['Ananya','Arjun','Bhavna','Chetan','Divya','Esha','Farhan','Gaurav','Harini','Ishan',
              'Jaya','Kiran','Leela','Manish','Nisha','Omkar','Priya','Rahul','Sneha','Tanvi'])[1 + floor(random()*20)::int]
       || ' '
       || (ARRAY['Agarwal','Banerjee','Chatterjee','Deshpande','Iyer','Joshi','Kapoor','Mehta','Nair','Patel',
                 'Rao','Shah','Singh','Verma','Yadav','Pillai','Reddy','Khan','Das','Bose'])[1 + floor(random()*20)::int]
FROM generate_series(1, 20000) AS g;
ANALYZE names;
-- Before an index: an ILIKE with leading and trailing wildcards reads every row.
EXPLAIN (COSTS OFF)
SELECT id, name FROM names WHERE name ILIKE '%meht%';
-- With a GIN trigram index the same ILIKE becomes a Bitmap Index Scan.
CREATE INDEX names_trgm ON names USING GIN (name gin_trgm_ops);
EXPLAIN (COSTS OFF)
SELECT id, name FROM names WHERE name ILIKE '%meht%';
-- Two letters between the wildcards hold no trigram to look up, so the index cannot narrow the search.
EXPLAIN (COSTS OFF)
SELECT id, name FROM names WHERE name ILIKE '%me%';
-- A misspelt name with the % operator, which the same trigram index can answer.
SELECT DISTINCT name, round(similarity(name, 'Nisha Mehtaa')::numeric, 4) AS sim
FROM names WHERE name % 'Nisha Mehtaa' ORDER BY sim DESC, name LIMIT 3;