Autocomplete: Typo Tolerance with Postgres Trigrams

Type “adaptr” into the product search and it still finds adapter. Type “charg” and it finds chargers and wall chargers. That forgiveness — partial words, typos, matches mid-name — comes from a Postgres feature called trigrams, no search engine attached.

The concept

flowchart TB
  A["adapter"] --> B["ada / dap / apt"]
  C["adaptr"] --> D["ada / dap / apt"]
  B --> E["Shared trigram: adp"]
  D --> E
  E --> F["Similarity score"]
  F --> G["Adapter suggestion still ranks"]
  class A,C source;
  class B,D,E data;
  class F process;
  class G output;
  classDef source fill:#ffffff,stroke:#111827,color:#111827,stroke-width:2px;
  classDef process fill:#fef3c7,stroke:#92400e,color:#111827,stroke-width:2px;
  classDef data fill:#dbeafe,stroke:#1d4ed8,color:#111827,stroke-width:2px;
  classDef decision fill:#dcfce7,stroke:#166534,color:#111827,stroke-width:2px;
  classDef output fill:#fee2e2,stroke:#991b1b,color:#111827,stroke-width:2px;

A trigram is every three-letter slice of a word. “adapter” breaks into:

"  a", " ad", "ada", "dap", "apt", "pte", "ter", "er "

The database indexes these slices ahead of time. When someone types “adp”, it looks up that slice and instantly knows which rows contain it — “Adapter Cable”, “Travel Adapter”, “USB Adapter” — without reading every item.

This is what makes “contains” and “close enough” searches fast. An ordinary index is great at “starts with” but useless mid-string. A trigram index handles “appears anywhere” and tolerates a typo, because a wrong letter only ruins a couple of slices — the rest still match.

Multi-word searches get a small trick: turn the spaces into wildcards so words match in any order with anything between them.

search_pattern := '%' || regexp_replace(query, '\s+', '%', 'g') || '%';
-- "red cable"  →  "%red%cable%"  →  matches "Red Braided Cable"

Where the win is

Users don’t know your exact spelling and shouldn’t have to. Without typo-tolerance, “adaptr” returns nothing and the user assumes you have no adapter. With it, the suggestion appears as they type.

And it stays fast because the slices are pre-indexed — a quick lookup, not a scan of every name on every keystroke. That’s what makes as-you-type suggestions feel instant.