Building Japanese forum search with pg_trgm partial matching

You can now search posts in the Metronome.fm forum. Type words separated by spaces in the search box, and it finds posts that contain all of them. It looks in post bodies and topic titles.

Japanese text doesn't work well with PostgreSQL's built-in full-text search as is. This post covers why forum search uses partial matching with the PostgreSQL extension pg_trgm instead, how fast it was in a local benchmark, and what to watch for when creating the index with Supabase and Prisma.

Full-text search can't split Japanese into words

PostgreSQL full-text search (tsvector) splits text into words at spaces and punctuation before indexing it. Japanese doesn't put spaces between words, so a whole sentence becomes a single word.

SELECT to_tsvector('simple', '裏拍だけ鳴らす設定で練習しています');
-- '裏拍だけ鳴らす設定で練習しています':1

SELECT to_tsvector('simple', '裏拍だけ鳴らす設定で練習しています')
    @@ to_tsquery('simple', '練習');
-- f

Searching for 練習 ("practice") doesn't find this post. Splitting Japanese into words requires an extension with a dictionary for morphological analysis (the process of breaking text into words).

Supabase also offers pgroonga, a full-text search extension that handles Japanese. But our end-to-end (E2E) tests, which drive the app in a browser, run against Postgres started in local Docker and in CI (the postgres:15 image), so using pgroonga would mean switching both to an image with pgroonga installed. To avoid changing the test setup, we went with pg_trgm, which can be built with only the extensions that ship with standard Postgres.

What pg_trgm is

pg_trgm is one of the extensions that ship with PostgreSQL (contrib). After you enable it with CREATE EXTENSION pg_trgm, you get functions and operators that work with strings split into trigrams (groups of three characters). It also adds operator classes (such as gin_trgm_ops) that let indexes use them.

You can see how trigrams are made with show_trgm. It adds two spaces before each word and one after, then takes three characters at a time, shifting by one character. Letters are lowercased, and trigrams are made per word, with words separated by spaces.

SELECT show_trgm('cat');
-- {"  c"," ca","at ",cat}

SELECT show_trgm('Click Track');
-- {"  c","  t"," cl"," tr",ack,"ck ",cli,ick,lic,rac,tra}

Japanese is split the same way, but the output shows hex values. pg_trgm handles any trigram containing multibyte characters, as Japanese ones do, as a 3-byte hash.

SELECT show_trgm('三連符');
-- {0x0d64d3,0x4f2cac,0x66efe0,0xa9ebeb}
-- The four trigrams "  三", " 三連", "三連符", and "連符 ", shown as hashes

pg_trgm does two main things. The first is measuring how similar two strings are. similarity returns the share of trigrams they have in common, from 0 to 1, and the % operator returns whether that share is at or above a threshold (0.3 by default). This is useful for typo-tolerant search, but forum search doesn't use it.

SELECT similarity('metronome', 'metoronome'); -- 0.61538464
SELECT similarity('metronome', 'drum');       -- 0

The second is speeding up LIKE, ILIKE, and regular expression searches with an index. With a GIN (gin_trgm_ops) or GiST (gist_trgm_ops) index, even a partial match with a leading %, like ILIKE '%三連符%', can use the index. A regular B-tree index can't be used for a partial match with a leading %. This is why forum search uses pg_trgm, with a GIN index.

The index returns the rows that contain every trigram of the search word as candidates. Having all the trigrams doesn't guarantee they appear together as the word, so PostgreSQL checks the original condition again on the candidate rows. That is the Recheck Cond in the query plan that EXPLAIN shows (how PostgreSQL plans to run the search). ~~* means ILIKE.

Bitmap Heap Scan on t (actual rows=1 loops=1)
  Recheck Cond: (body ~~* '%三連符%'::text)
  ->  Bitmap Index Scan on t_body_idx (actual rows=1 loops=1)
        Index Cond: (body ~~* '%三連符%'::text)

The index is there for speed and doesn't change the results. Without it, ILIKE reads every row in the table and returns the same rows.

When a new post is saved, the index is updated along with it. New posts show up in search without rebuilding the index.

Because it searches by three-character groups instead of words, Japanese and English are searched the same way. Two-character words are a weak spot, though, which the "Speed in a local benchmark" section covers.

Whether Japanese becomes trigrams depends on the locale

Whether Japanese characters become trigrams depends on the database locale (the setting that decides how characters are treated for a language and region). pg_trgm only makes trigrams from letters and digits, and whether a character counts as a letter follows the locale. In a test Postgres, we created two databases with different locales and compared the trigrams for the same text. The extensions. before the function is the name of the schema the extension lives in (covered in the "Creating the index with Supabase and Prisma" section).

-- A database with the en_US.utf8 locale
SELECT extensions.show_trgm('三連符の練習');
-- {0x0d64d3,0x4a13a8,0x4f2cac,0x6975c5,0xa9ebeb,0xdc990a,0xdd00eb}

-- A database with the C locale
SELECT extensions.show_trgm('三連符の練習');
-- {}

With the C locale, Japanese text produces no trigrams at all, so the index can't narrow anything down (ASCII letters and digits still become trigrams under C). Our Supabase databases, both production and development, use en_US.UTF-8, so this worked as is.

Speed in a local benchmark

We loaded 100,020 posts with an average body length of 102 characters into Postgres in Docker on a Mac (PostgreSQL 15.14) and measured ILIKE searches with EXPLAIN ANALYZE. We measured each query six times, and the table shows the median of the five runs after the first. The index was 18 MB, about 60% of the table's 31 MB.

For a word that matches few rows, the index made the search about 2,000 times faster. For a word that matches 60% of the rows, the planner (the part of PostgreSQL that decides how to run a search) skipped the index and read every row, estimating that a full scan beats the index when most rows match anyway.

The two-character word also read every row. In a partial match with % on both sides, like %拍子%, the groups padded with spaces aren't used. Two characters don't make a three-character group, so the index can't narrow anything down. Japanese searches often use two-character words like 拍子, 裏拍 (offbeat), and 練習, so this is a weak spot of pg_trgm. The forum has few posts for now, and reading them all is fine. As posts grow, we'll check how long searches for two-character words take.

Creating the index with Supabase and Prisma

We wrote the migration (the SQL that changes the database's tables and indexes) by hand. The extension goes in the extensions schema (a namespace that groups tables and functions inside the database), because putting it in public triggers a warning from Supabase's Security Advisor. That means the index's operator class is also written with the schema, as extensions.gin_trgm_ops.

CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA extensions;

CREATE INDEX "forum_topics_title_trgm_idx" ON "public"."forum_topics"
  USING GIN ("title" extensions.gin_trgm_ops);
CREATE INDEX "forum_posts_body_trgm_idx" ON "public"."forum_posts"
  USING GIN ("body" extensions.gin_trgm_ops);

CREATE SCHEMA IF NOT EXISTS is there for the standard Postgres used in automated tests. Supabase has an extensions schema out of the box, but standard Postgres doesn't.

Indexes created in SQL also need to go in schema.prisma, Prisma's definition file. If you leave them out, Prisma tries to drop them as indexes missing from schema.prisma the next time it generates a migration. Running prisma migrate diff against a test Postgres produced SQL that drops both indexes.

-- When the indexes aren't in schema.prisma
DROP INDEX "public"."forum_posts_body_trgm_idx";
DROP INDEX "public"."forum_topics_title_trgm_idx";

Prisma's @@index accepts an operator class through raw().

model ForumPost {
  // …
  @@index([body(ops: raw("gin_trgm_ops"))], type: Gin, map: "forum_posts_body_trgm_idx")
}

Unlike in the migration, the operator class in schema.prisma has no schema. Even without it, comparing the migrations with schema.prisma using prisma migrate diff showed no difference (No difference detected.).

The search query

Search is written with Prisma's $queryRaw. It builds an ILIKE condition for each word and joins them with AND, passing every value as a parameter instead of embedding it in the SQL text.

const termConditions = terms.map((term) => {
  const pattern = toForumLikePattern(term); // "%word%"
  return Prisma.sql`(p."body" ILIKE ${pattern} OR (t."title" ILIKE ${pattern} AND ${IS_FIRST_POST}))`;
});

A topic's title is searched as part of its first post. If the title contains the word, the topic's first post shows up in the results. IS_FIRST_POST is the condition that there's no earlier post in the same topic.

In ILIKE, % and _ in a search word are wildcards. So we put \ in front of them to search for the characters themselves. The escape character for LIKE (the mark that makes a symbol count as a plain character) is \ by default, so this works without writing ESCAPE.

export const toForumLikePattern = (term: string): string =>
  `%${term.replace(/[\\%_]/g, (char) => `\\${char}`)}%`;

Deleted posts and topics, and posts and topics hidden through reports or by an admin, are excluded from the results by conditions in the SQL.

Each result shows an excerpt from around the first match in the body, with the search words in bold. Posts can be written in Markdown, so Markdown syntax is removed before the excerpt is taken.

Limiting the number of searches and pages

Search works without signing in. Partial matching sometimes can't narrow things down with the index, as with two-character words. So we set two limits to keep the database from being overloaded.

• Each user (or each IP address when not signed in) can search up to 20 times a minute
• Results go up to 50 pages (1,000 posts)

The page limit is there because later pages are heavier. To link each result to the page of the topic where the post appears, we count which post in the topic it is. That count also runs for the rows skipped by OFFSET. Results past 1,000 aren't shown, so the idea is that you add more words to narrow the search.