Prefix Search and Autocomplete in PostgreSQL Without Pain

Autocomplete is a search with unusual rules: it runs on every keystroke, it sees half a word and it must answer in a few milliseconds. The temptation is WHERE name ILIKE 'abc%' and hope. That works on a small table and falls over on a large one, because the default index cannot serve it. PostgreSQL has three good tools, each for a different shape of data: a pattern-ops B-tree for column prefixes, a prefix tsquery for words inside text and a trigram index for anything else. This article shows each, then the ranking and request handling that make autocomplete usable. It belongs to the cluster introduced by full text search in PostgreSQL before you add Elasticsearch.

Prefix on a column: B-tree with text_pattern_ops

The query for names starting with "post":

SELECT name FROM product WHERE name LIKE 'post%' ORDER BY name LIMIT 10;

A plain B-tree index on name does not help when the database collation is not C, because locale-aware sorting does not put all strings with a given prefix next to each other in index order. The fix is an operator class that sorts by raw characters:

CREATE INDEX product_name_prefix ON product (name text_pattern_ops);

With text_pattern_ops, the planner can turn LIKE 'post%' into a range condition (name >= 'post' AND name < 'posu') and use the index. The index no longer serves ordinary < and ORDER BY on locale order, so keep a normal index too if you need it. Use varchar_pattern_ops for varchar columns. A column declared with the C collation can use the normal operator class.

For case-insensitive prefixes, index the lowered value and query the same expression:

CREATE INDEX product_lower_name_prefix ON product (lower(name) text_pattern_ops);

SELECT name FROM product WHERE lower(name) LIKE lower($1) || '%' ORDER BY lower(name) LIMIT 10;

Escape % and _ in the user's input before appending the wildcard, or a user typing 100% changes the pattern. Parameterize the value and escape the wildcards with a helper that replaces them with the escape character.

Word prefixes in documents: tsquery with :*

For prefix matching on any word of a document or a multi-word title, full text search supports prefixes inside a tsquery:

SELECT id, title
FROM article
WHERE search @@ to_tsquery('english', 'postg:*')
LIMIT 10;

postg:* matches any lexeme that begins with "postg", and the GIN index on search supports it. For more than one typed word, join with & and put the prefix marker on the last word only, since earlier words are complete:

to_tsquery('english', 'full & text & sea:*')

Building this from user input needs care: to_tsquery raises an error on syntax problems, so sanitize the input by keeping letters and digits, splitting on spaces and assembling the string yourself. A typical function:

CREATE FUNCTION prefix_tsquery(cfg regconfig, input text) RETURNS tsquery AS $$
    SELECT to_tsquery(cfg, string_agg(w || CASE WHEN i = n THEN ':*' ELSE '' END, ' & ' ORDER BY i))
    FROM (
        SELECT w, i, max(i) OVER () AS n
        FROM regexp_split_to_table(regexp_replace(input, '[^[:alnum:] ]', '', 'g'), '\s+')
             WITH ORDINALITY AS t(w, i)
        WHERE w <> ''
    ) s;
$$ LANGUAGE sql STABLE;

The function strips punctuation, splits on whitespace and appends :* to the last word. It returns null for empty input, and an application should treat that as no query. Stemming complicates prefixes: the configuration stems the typed fragment, and a half-typed word may stem to something that matches nothing. For example "runn" stays "runn", which matches stored "runner" and "running" only because those stems begin with "run". Test the behavior on your vocabulary. For autocomplete over names and short titles, a simple configuration (no stemming) gives predictable prefixes.

Anything else: trigram indexes

A trigram GIN index (see typo tolerant search with pg_trgm) speeds LIKE '%term%' and ILIKE, so autocomplete that matches anywhere in the string, not only at the start, can use it:

CREATE INDEX product_name_trgm ON product USING gin (name gin_trgm_ops);
SELECT name FROM product WHERE name ILIKE '%' || $1 || '%' LIMIT 10;

It handles inputs of three or more characters well and shorter ones poorly. The index is larger than a B-tree. Use it when "contains" semantics are the product requirement, and a prefix index when "starts with" is.

Ranking suggestions

Alphabetical order is rarely what users want. Rank by something that reflects likelihood: how often the item is chosen, how recent, how many sales. Keep a popularity number on the row, or a separate table of search counts, and order by it:

SELECT name
FROM product
WHERE lower(name) LIKE lower($1) || '%'
ORDER BY popularity DESC, name
LIMIT 8;

This has a cost trap: with ORDER BY popularity the index on the prefix finds all matching rows, which the database then sorts. For a short prefix that matches 100,000 rows, that is slow. Options: a composite index that serves both conditions only helps when the prefix is a constant range; precompute top suggestions for short prefixes in a table (prefix, rank, suggestion) refreshed periodically; or cap how short a prefix may be. Many systems precompute the top ten for every one- and two-character prefix, and compute on the fly from three characters on, where the match set is small.

Request handling

Most of the performance of autocomplete is not in SQL. Rules that save the database:

• Minimum length. Do not query before two or three characters.

• Debounce. Wait 150 to 250 ms after the last keystroke before sending, and cancel in-flight requests when a new one starts.

• Limit. Return eight to ten rows; the user will not read more.

• Cache. Cache prefix results for a minute or two in the application; many users type the same first letters.

• Connection use. A page of autocomplete requests can consume the pool. Keep queries short and rely on a pool configured as in connection pooling for PostgreSQL.

• Statement timeout. Set a short timeout (100 to 300 ms) for the autocomplete role so a bad query cannot pile up.

Accents and normalization

Users type "cafe" for "café". Index and query the same normalized form, for instance lower(unaccent(name)) wrapped in an immutable function, because the extension's function is not marked immutable and cannot be used in an index directly. Normalize the typed input in the application with the same rules, and confirm the index is used with EXPLAIN. For languages with different case rules (Turkish dotless i), lower() depends on the collation; test with your data.

When autocomplete needs a dedicated service

PostgreSQL handles autocomplete well for catalogs up to millions of rows when suggestions are prefixes of names or words. It strains when suggestions need typo tolerance combined with prefix matching, when ranking blends many signals including personalization, when you want suggestions from query logs ("people also searched") or when the volume of keystroke requests competes with transactional load. A separate service built for suggestions, or a read replica dedicated to search, isolates the load. The path from here is covered in when PostgreSQL search stops being enough.

What to do next

Pick the shape of your data: column prefix, word prefix or contains. Create the matching index and confirm with EXPLAIN that it is used for a three-letter input. The manual explains the operator classes in operator classes and operator families and prefix queries in text search parsing queries. Then look at using websearch_to_tsquery for user input for the full-search box.