All posts

Slow or missing search in your AI-built app: how to fix it

Your app has a search box. It takes seconds to answer. Or it finds "bike" but not "bikes". Or a customer types "fietsen" and gets nothing, although you sell fietsen. Apps built with Lovable, Bolt, Cursor and Replit often ship a search that works on ten test rows and falls apart on real data. Most of these apps run on Supabase (which is Postgres), some on Neon (also Postgres), some on Firebase. Vendor facts below come from each vendor's own docs, checked in November 2026.

Short answer: Generated search is usually a plain "contains" match, or the browser downloads all rows and filters them. Both miss word forms and get slower as your data grows. In Postgres, fix it in two steps. First, use full-text search: a stored search column in the right language, a GIN index, and a ranked query. Second, if people make typos or type half a word, add the pg_trgm extension. Always search on the server, never by downloading all rows. Test with a list of awkward searches, and time the query before and after. Back up first, and try changes on a copy.

1. Why the generated search is slow and misses things

The usual "contains" search

Ask an AI tool for a search box and you often get a query like where name ilike '%bike%'. It means: "the text contains these letters, upper or lower case". It looks right in a demo. It has three problems.

  • No word forms. It matches letters, not words. "bike" will match "bikes" by luck, because the letters are inside. But "mice" will never match "mouse", and "running" will not match "run". The PostgreSQL docs on full text search say that LIKE and ILIKE have "no linguistic support, even for English."
  • Weak with several words. Someone types "red bike". The text has to contain exactly "red bike" in that order, with that space. A product called "bike, red" is missed.
  • No ranking. Every match is equal. The same docs say these operators "provide no ordering (ranking) of search results, which makes them ineffective when thousands of matching documents are found."

Why it gets slow

With a few hundred rows, nobody notices. With a hundred thousand rows, the database may have to look at every row for every search. This is called a sequential scan. A normal index (a B-tree) does not rescue you here. The PostgreSQL docs on index types say the optimizer can use a B-tree index for LIKE if the pattern is a constant "anchored to the beginning of the string", for example col LIKE 'foo%', "but not col LIKE '%bar'." A search that puts a wildcard at the start, as '%bike%' does, cannot use that kind of index.

There is a good fix for this, covered in section 4: a trigram index. It does support these searches.

The other common version: filtering in the browser

Some generated apps load every row into the page, then filter them with JavaScript. It feels fast with 50 rows. With 5,000 rows the page downloads a lot of data on every visit, and phones suffer most. It is also a privacy problem: the browser has received every row, including rows the visitor never looks at. Anyone can open the network tab and read them. See section 7.

2. Postgres full-text search in plain words

Postgres has a search system built in. The docs call it full text search. It works in three steps: turn the stored text into a list of normalised words, turn the search phrase into the same kind of words, and match them. In the docs' words, a document is "the unit of searching", such as a magazine article or an email message. For you, a document is usually one row: a product, a post, a job.

The pieces

  • to_tsvector parses and normalises a piece of text into a searchable list of words (a "tsvector").
  • to_tsquery builds a search query from words you already normalised. It is strict about its syntax.
  • websearch_to_tsquery is the one to use for a search box. The docs say it "will never raise syntax errors, which makes it possible to use raw user-supplied input for search." It understands quotes for phrases, the word or, and a dash to exclude a word.
  • @@ is the match operator: does this document match this query?
  • ts_rank gives each match a score. The docs say it "ranks vectors based on the frequency of their matching lexemes." Sort by it, highest first.

A first example on the docs' own pattern, where products, name and description are example names (use your own): select name from products where to_tsvector('english', name || ' ' || description) @@ websearch_to_tsquery('english', 'red bikes');

The docs show the same idea: to_tsvector('english', 'fat cats ate fat rats') @@ to_tsquery('english', 'fat & rat') is true. "rats" matches "rat", because both are reduced to a common base form.

Why the language setting matters

The word 'english' in those examples is a text search configuration. The docs say a configuration "specifies all options necessary to transform a document into a tsvector: the parser to use to break text into tokens, and the dictionaries to use to transform each token into a lexeme." That is where stemming happens: reducing word forms to a base form. The docs say that each Snowball stemming algorithm "understands how to reduce common variant forms of words to a base, or stem, spelling within its language."

So the language you pick decides what counts as the same word. The docs list stemmers for Dutch (dutch_stem), German (german_stem) and English (english_stem), plus a simple dictionary that, per the docs, just converts "the input token to lower case" and checks it against stop words. That means:

  • english, dutch, german: word forms are reduced, so "fiets" and "fietsen" can match. Use the language of your content.
  • simple: no word forms at all, just lower case. Useful for names, codes and mixed-language text. It will not match plurals.
  • Use the same configuration when you store the text and when you search. An English search over Dutch text misses Dutch word forms.

Not sure which configurations your database has? Run select cfgname from pg_ts_config; in the SQL editor. If your app has more than one language, see adding a second language (Dutch, German) to an AI-built app. A single column in one config will not serve two languages well. Plan one search column per language, or use simple for mixed content.

3. The fast version: a stored column and a GIN index

The docs on tables and indexes say a search without an index works, "although these queries will work without an index, most applications will find this approach too slow, except perhaps for occasional ad-hoc searches." They then show two ways to make it fast. The simpler to use is a stored generated column: "To keep this column automatically up to date with its source data, use a stored generated column." Then you put a GIN index on it. Example (names are examples, and Dutch text is assumed):

  • alter table products add column search_text tsvector generated always as (to_tsvector('dutch', coalesce(name, '') || ' ' || coalesce(description, ''))) stored;
  • create index products_search_idx on products using gin (search_text);
  • select name, ts_rank(search_text, q) as rank from products, websearch_to_tsquery('dutch', 'rode fietsen') as q where search_text @@ q order by rank desc limit 20;

The coalesce parts matter: if a column is empty (null), joining it with text would otherwise make the whole result empty. The docs give this same pattern. They list the benefit of the separate column too: "it is not necessary to explicitly specify the text search configuration in queries in order to make use of the index", and searches are faster because the words do not need to be recalculated. The cost is disk space. The docs note the expression-index approach "requires less disk space".

One trap the docs warn about for the other approach, an index on an expression: only a query that uses the two-argument to_tsvector with the same configuration name will use that index. With the stored column you avoid this.

Adding a column and an index to a big table takes time and locks, so take a backup first (see backups for Supabase, Neon and Firebase) and try it on a copy. The steps for making database changes safely are in changing your database without losing data.

Using it from Supabase

Supabase has its own full-text search guide. It uses the same pieces: a generated column, then create index books_fts on books using gin (fts);. In supabase-js you search with textSearch(), for example supabase.from('products').textSearch('search_text', 'rode fietsen', { config: 'dutch', type: 'websearch' }). The guide describes websearch_to_tsquery() as providing "an intuitive search syntax similar to popular web search engines." The textSearch reference shows examples with type: 'plain', type: 'phrase' and type: 'websearch', and a config of "english". The reference does not explain these options in words. They line up with the Postgres functions of similar names (plain text, phrase, web search). For a search box, websearch is the friendliest. Test it, and set config to the same language as your column.

4. Typos and half-typed words: pg_trgm

Full-text search fixes word forms. It does not fix typos. "fietz" will not become "fiets". For that, Postgres has the pg_trgm extension. The docs say it "provides functions and operators for determining the similarity of alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings." A trigram is a group of three letters; two texts that share many groups are similar.

  • similarity(a, b) returns a number "from zero (indicating that the two strings are completely dissimilar) to one (indicating that the two strings are identical)."
  • The % operator is true when the similarity is above the threshold set by pg_trgm.similarity_threshold. The docs give the default as 0.3.
  • The docs say that GIN and GiST trigram indexes support these operators and "additionally support trigram-based index searches for LIKE, ILIKE, ~, ~* and = queries."

The last point is useful: if your app already has ilike '%bike%' and you only need it faster, a trigram index is the direct fix. Set up (example names): create extension if not exists pg_trgm; then create index products_name_trgm_idx on products using gin (name gin_trgm_ops);. A typo-tolerant query: select name, similarity(name, 'fietz') as score from products where name % 'fietz' order by score desc limit 20;

In Supabase you can enable extensions in the Dashboard (Database, then Extensions) or with SQL. Supabase's extensions page shows this form: create extension pgtap with schema extensions;, and notes that this "is the equivalent of enabling the extension." That page did not list pg_trgm when I checked, so confirm it in your Dashboard. The Postgres docs say pg_trgm is "trusted", so a user with CREATE privilege on the database can install it. On Neon, which is Postgres, the SQL is the same; check Neon's own extension list for your plan.

Trigram matching works on letters, not on language. It will not know that "fietsen" is the plural of "fiets". The two tools do different jobs, so many apps use both: full-text search first for word forms and ranking, trigram as a fallback when the first returns nothing. Keep it simple. Add the second tool only if your test searches show you need it.

5. Firebase and Firestore

Search on Firestore is different from Postgres. Firestore's text search page says: "Use text search features in Cloud Firestore to search for specific strings within documents." It also says: "Text search requires a Firestore Enterprise edition database." and "The operation searches only in the fields indexed with a text index." So you must create a text index on the fields you want to search, and your database must be on that edition. The page describes a few search forms: a single term, an exact phrase in quotes, several terms (all must match), and excluding a term with a hyphen. Results are sorted by creation time by default, and you can sort by search score instead, which the page says needs more computation.

That page does not mention word forms (stemming) or typo tolerance. So do not assume them. Test the searches in section 8 on your own data. If your Firestore database is not on the edition the page asks for, I could not confirm from that page which route Firebase recommends. Check the current Firebase docs for your edition, or use an outside search service (next section). Also remember that Firestore security rules decide who can read what, so search results need the same rules as any other read.

6. When a search service makes sense

Algolia, Meilisearch and Typesense are services (or open-source programs you host) built only for search. They keep a copy of your data in their own index, and your app queries them. Reasons to look at one: you need typo tolerance out of the box, you need search-as-you-type on a lot of data, or you are on a database without good search. Meilisearch's docs say, for example, that "Typo tolerance helps users find relevant results even when their search queries contain spelling mistakes or typos" and that it "is enabled by default." Algolia's docs describe typo tolerance as matching "words that are close in spelling."

The cost is a second system. Your data now lives in two places. You must keep the copy in sync when rows change, protect it (so private rows do not leak into a public index), and pay or host it. For a small app with a few thousand rows, Postgres full-text search plus pg_trgm is often enough. I am not comparing prices or speed here. Check each vendor's own pricing page when you decide.

7. Search on the server, and keep your access rules

The safe design: the browser sends the words someone typed, the server (or database) runs the search, and only a short page of results comes back, for example 20 rows. Do not load every row to filter it in the page.

  • Speed. The database has indexes. The phone has not. Sending 20 rows is far less than sending all of them.
  • Privacy. Everything sent to the browser can be read by the visitor. If search needs the whole table in the page, the whole table is exposed.
  • Limit and page. Always add a limit, and cap the length of the search text.

Searching through Supabase does not skip your Row Level Security rules. Supabase's RLS guide says: "Think of a policy as adding a WHERE clause to every query." So a search runs only over rows the user may read. That is good, but it means a missing or wrong policy shows up in search too. The same guide warns that "A table in an exposed schema without RLS is readable and writable by any role with a grant on it." If you add a search column or a search function, check the rules again. A database function that runs with extra rights can bypass them. See database security checks for AI-built apps.

8. How to test your search

Ten test searches

Pick a record you know exists, for example a product called "Red racing bike", and run these. Write down what you expect first.

  1. Exact word: "bike". It should find the product.
  2. Plural: "bikes". Should find the same product.
  3. Singular of a plural: search "bike" when the record says "bikes". Same result expected.
  4. Two words, any order: "bike red". Should still find it.
  5. Different case: "RED BIKE". Same result.
  6. A typo: "bikr". Decide what you want: nothing, or a "did you mean" style result.
  7. A half-typed word: "rac". Decide if you need partial words.
  8. A Dutch word form: "fietsen" against a record that says "fiets". Only works if the language setting is Dutch.
  9. Empty search: just spaces, or nothing. It should show a normal list or a friendly message, not an error.
  10. A very long search, and odd characters: paste 2,000 letters, then try quotes, a single quote and a percent sign. It must not crash, and must not slow the whole app.

Also test as a logged-out visitor and as a second user: can search show rows that person should not see?

Timing a query with EXPLAIN ANALYZE

Open the SQL editor (in Supabase or Neon) and put explain analyze in front of your search query. The PostgreSQL docs on using EXPLAIN say that with this option "EXPLAIN actually executes the query, and then displays the true row counts and true run time." In plain words, look at three things:

  • The scan type. "Seq Scan" means the database read the whole table. After you add your index, you hope to see an index scan or a bitmap index scan instead. The docs list "sequential scans, index scans, and bitmap index scans" as scan node types. On a tiny table Postgres may still choose a sequential scan, because that can be cheaper. Test on a table with realistic size.
  • Execution time. The docs say it includes executor start-up and shut-down and any triggers, but not parsing, rewriting or planning. It is shown in milliseconds. Run it a few times, and compare before and after your change.
  • Planning time. The time to prepare the query, shown separately.

Because explain analyze really runs the query, never use it on a statement that changes data without wrapping it in begin; ... rollback;. A search is read-only, so it is safe.

For wider speed problems beyond search, see fixing a slow AI-built app.

9. Prompts for your AI tool

Adapt table and column names. Ask the tool to show its plan and the SQL before it runs anything on live data.

  • "Find every place this app does a search. For each, show me the query or code, and tell me if it uses ilike, like, or filters rows in the browser."
  • "Replace the browser-side filtering with a server-side search that returns at most 20 rows, takes a page number, and limits the search text to 100 characters."
  • "Write a SQL migration that adds a stored generated tsvector column on products (name and description, with coalesce, using the 'dutch' configuration) and a GIN index on it. Do not run it. Show me the SQL first."
  • "Change the search to use websearch_to_tsquery with the same configuration as the column, sorted by ts_rank, with a limit. In supabase-js use textSearch with type 'websearch'."
  • "Enable the pg_trgm extension and add a GIN trigram index on products.name. If the full-text search returns no rows, fall back to a similarity search with the % operator."
  • "Check that Row Level Security is enabled on every table involved in search, and show me the select policies. Test the search as two different users."
  • "Write 10 test searches for my products table: plural, typo, two words, a Dutch word, empty, very long. Show me the explain analyze output for the search query before and after the index."

10. Checklist

  • Search runs on the server or in the database, not by loading all rows into the browser.
  • Results are limited and paged. The search text has a length limit.
  • The search uses full-text search (or a trigram index) instead of a bare ilike '%word%' on a big table.
  • The language configuration matches the content (dutch, german, english, or simple for mixed text), for both the stored column and the query.
  • The search column is a stored generated column with a GIN index.
  • Results are sorted by rank, not by random order.
  • If typos matter, pg_trgm is enabled and has an index.
  • Row Level Security is on for every searched table, and tested with two users.
  • All ten test searches were run, and results written down.
  • Before and after timings with explain analyze on realistic data.
  • A backup was made, and the change was tried on a copy first.
  • On Firestore: the edition and text index requirements were checked, and word forms and typos were tested, not assumed.

Want this fixed for you?

I finish and fix apps built with Lovable, Bolt, Cursor or Replit, including slow search, missing results and typo handling on Supabase, Neon and Firebase. Send me the details and I'll reply within 24 hours.