Vietnamese search without diacritics in Postgres
Typing tuan doesn't find Tuấn with ILIKE, and NFD can't strip đ. Accent-insensitive search in Postgres with unaccent, pg_trgm and a folded column.
Searching Sosifie's posts for "ha noi" doesn't find the location "Hà Nội". Its search uses Express, Prisma 5 and Postgres, but doesn't fold diacritics yet. Typing "tuan" does find people named Tuấn, but that's luck.
Search today matches character for character
Users are matched with Prisma's contains plus mode: 'insensitive' on full_name, user_name and email, which becomes ILIKE on Postgres. Posts go through a raw query that unnests the content and tags arrays and runs ILIKE on each element, plus the location column. The pattern escapes % and _ but misses the backslash.
"tuan" finds people because usernames are built from first name, last name and the last two phone digits, then passed through unidecode, so they're ASCII. A full_name typed without marks misses. Posts and chat messages have no unidecoded column at all.
ILIKE is case-insensitive but NFD doesn't strip đ
ILIKE only ignores case. To Postgres, ấ and a are as different as a and b. Typing the marks doesn't guarantee a match either. "Tuấn" stored as NFC and as NFD looks identical but compares unequal, and "hoà" and "hòa" are both valid spellings of one word. The Prisma docs also say case-insensitive filtering doesn't work with the C collation, so run \l in psql first.
The usual JavaScript approach is to normalize to NFD and delete the combining marks. Vietnamese vowels come out clean, but đ (U+0111) has no decomposition, so "Đức" becomes "Đuc", and a \p{Diacritic} regex doesn't help. You have to map it yourself:
export function foldVi(input: string): string {
return input
.normalize('NFD')
.replace(/[̀-ͯ]/g, '') // combining marks: tones, ^, horn, breve
.replace(/đ/g, 'd')
.replace(/Đ/g, 'D')
.toLowerCase()
}
foldVi('Đặng Văn Đức') // "dang van duc"
foldVi('hoà') === foldVi('hòa') // true, both are "hoa"
unidecode gets đ right but transliterates everything, Chinese and Cyrillic included, so for search I'd rather have a small function. Once everything folds to ASCII, the NFC/NFD and "hoà"/"hòa" problems go away too.
Stored text and keyword need the same fold
Fold on write into a separate column or fold both sides in the query, as long as both use the same function. Fold the column with unidecode and the keyword with unaccent and they'll eventually disagree on some odd character, in a bug that only a few users ever hit.
unaccent(full_name) ILIKE unaccent($1) is the least work, but every keystroke scans the whole table. That's fine for a small users table, not for posts.
unaccent is a trusted contrib module, so anyone with CREATE on the database can install it, and its default rules cover đ and combining marks.
Wrap unaccent as IMMUTABLE to get a trigram index
unaccent() is STABLE because it depends on a dictionary, and expression indexes only accept IMMUTABLE functions. The usual workaround is a wrapper that pins the dictionary, with a trigram index on top:
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- unaccent() is STABLE; this wrapper promises Postgres it won't change
CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
AS $$ SELECT public.unaccent('public.unaccent'::regdictionary, $1) $$;
CREATE INDEX user_full_name_search_idx ON "User"
USING gin (lower(f_unaccent(full_name)) gin_trgm_ops);
-- the query has to use the exact same expression to hit the index
SELECT id FROM "User"
WHERE lower(f_unaccent(full_name)) LIKE lower(f_unaccent('%' || $1 || '%'));
Marking it IMMUTABLE is a promise to Postgres, so if someone edits the rules file, rebuild the index. On a managed Postgres you normally can't touch that file. Prisma can't describe an expression index, so this goes in a hand-written migration (prisma migrate dev --create-only), and later migrations might try to drop it.
A B-tree can't help with a pattern that starts with %. pg_trgm splits text into three-character chunks, and its GIN and GiST operator classes support LIKE, ILIKE and regex. A pattern with no extractable trigrams falls back to a full index scan. The API should ignore keywords shorter than two or three characters.
I'd use similarity() (default threshold 0.3) only for ordering. Without marks, "tuan", "tuyen" and "tran" share plenty of chunks, and filtering by similarity turns the results into noise.
Posts get a pre-folded search_text column
Post content, tags and location are spread over text arrays and a nullable column, too awkward for one immutable expression. The plan is a search_text column, written with foldVi() in the post service on every create and update, backfilled once, with a trigram index on it:
const q = foldVi(raw.trim())
if (q.length < 2) return []
const pattern = `%${q.replace(/[\\%_]/g, '\\$&')}%`
const rows = await prisma.$queryRaw<{ id: string }[]>`
SELECT id FROM "Post"
WHERE search_text LIKE ${pattern}
ORDER BY similarity(search_text, ${q}) DESC, "createAt" DESC
LIMIT 20
`
The column is already lowercase, so plain LIKE works, and this time the backslash is escaped. The risk is an update path that changes content and forgets search_text, so only one function in the service should write it.
Full-text search isn't a good fit. Postgres has no Vietnamese configuration. Copying simple with unaccent in front (like the French example in the unaccent docs) works, but the parser doesn't know "Hà Nội" is one word, and full-text only matches whole tokens or prefixes, while people type fragments of names. Postgres 18 lets LIKE work with nondeterministic collations such as ICU und-u-ks-level1, which ignores accents and case, but I haven't tried it with Vietnamese, don't know how it treats đ, and the docs warn it's slower.
The highlight has to map back to the original string
The frontend chat search highlights with a regex built from exactly what the user typed, so "tuan" highlights nothing even when the backend finds the message. The plan is to fold the original text one character at a time, keep an array mapping folded positions back to original ones, search the folded string, then slice the original:
function foldWithMap(text: string) {
let folded = ''
const map: number[] = [] // folded index -> index in the original string
let i = 0
for (const ch of text) {
const f = foldVi(ch)
for (let k = 0; k < f.length; k++) { folded += f[k]; map.push(i) }
i += ch.length
}
return { folded, map }
}
export function Highlight({ text, query }: { text: string; query: string }) {
const q = foldVi(query.trim())
if (!q) return <>{text}</>
const { folded, map } = foldWithMap(text)
const parts: React.ReactNode[] = []
let cursor = 0
for (let at = folded.indexOf(q); at !== -1; at = folded.indexOf(q, at + q.length)) {
const start = map[at]
const end = map[at + q.length] ?? text.length
if (start > cursor) parts.push(text.slice(cursor, start))
parts.push(<mark key={start}>{text.slice(start, end)}</mark>)
cursor = end
}
parts.push(text.slice(cursor))
return <>{parts}</>
}
Combining marks fold to an empty string, so a decomposed "Nội" still gets highlighted whole. This uses foldVi while the database uses unaccent, which breaks the rule above. I'd accept that, because a mismatch here only costs a missing yellow box. The result still shows up.
Where search still needs exact marks
Right now only usernames work without diacritics. The conversation filter in the chat sidebar, the location picker and the friend-tagging box all filter client-side with toLowerCase().includes(), so they need exact marks too. There's no index for contains-search.
Contains-search without a full table scan needs a trigram index on lower(f_unaccent(full_name)) for users and one on search_text for posts. I haven't built either against real data, so I don't know how big the posts index gets.
More posts
- 01
Deploying Next.js and NestJS to AWS EC2 with Docker Compose
How I deploy a pnpm monorepo to one EC2 box. Images get built in GitHub Actions and Docker Compose pulls them from GHCR. The server has no code.
- 02
How I store OAuth tokens for social media APIs in NestJS
OAuth for Facebook, TikTok and YouTube in a NestJS API. State and PKCE, encrypted tokens, refreshing before each publish, and when to ask for a reconnect.
- 03
llms.txt on my Next.js site, and now Lighthouse grades it
vennee.dev has a hand-written llms.txt and an llms-full.txt built from the blog. Notes on the spec, .txt routes in Next.js, and Lighthouse 13's new audit.