Search Functionality in an AI-Built App: When Postgres Is Enough
Quick answer
Most apps don't need a dedicated search engine — Postgres full-text search with a GIN index handles filtering, ranking, and typo-tolerant-ish matching for datasets up to a few hundred thousand rows just fine. You need Meilisearch, Typesense, or Algolia once you want instant-as-you-type results, real fuzzy matching, or search across millions of rows with sub-50ms latency. Start with the database you already have.
The mistake that costs the most time
Someone adds a search bar, wires it to WHERE name ILIKE '%query%', ships it, and moves on. It works in the demo because the demo has forty rows. Six weeks later there are sixty thousand rows, that query is doing a sequential scan on every keystroke, and the "search" feature is why the dashboard feels slow.
ILIKE isn't search. It's string matching that happens to work when nobody's looking closely. It can't rank results, it can't handle "invocie" when someone means "invoice," and it gets linearly slower as the table grows because there's no index it can actually use.
I've seen this exact bug report land in a support inbox three separate times: "search feels broken," when the real issue was never the query logic — it was that nobody had indexed anything, so the database was reading every row on every keystroke.
What real search needs to do
Three things, roughly in order of how often people skip them: rank results by relevance instead of just matching or not matching, handle multiple words in any order, and stay fast as the table grows past what fits comfortably in a sequential scan. Typo tolerance is nice. It's also the thing people reach for first when the first two are the actual problem.
Postgres full-text search, and why it's underrated
Postgres has had a real search engine built in since version 8.3, and most teams never touch it. tsvector and tsquery give you stemming (search "running" and match "run"), ranking via ts_rank, and a GIN index that makes the whole thing fast at scale most apps never reach.
The setup is genuinely small. Add a generated tsvector column, index it, query with @@:
ALTER TABLE posts ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX posts_search_idx ON posts USING GIN (search_vector);
From there a query like SELECT * FROM posts WHERE search_vector @@ plainto_tsquery('english', 'ai app builder') ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'ai app builder')) DESC returns ranked, stemmed results, and it'll stay fast into the hundreds of thousands of rows because the GIN index is doing real work, not a table scan pretending to be search.
What it won't do: forgive typos gracefully, feel instant on every keystroke without extra work, or search across multiple unrelated tables in one query without you gluing the results together yourself.
Where Postgres search actually breaks down
Two spots, and they show up predictably. First, typo tolerance — pg_trgm gets you partway there with trigram similarity, but it's a workaround, not the real thing a dedicated engine ships out of the box. Second, instant-as-you-type UX at real scale. A GIN-indexed query is fast, but wiring debounced keystrokes straight into your primary database on every request adds load you don't need there, especially once search is popular enough that it's a meaningful share of your total query volume.
Got an idea? Build it now!
Just start with a simple Prompt. No coding required — Greta turns your idea into a working app in minutes.
When to bring in a dedicated search engine
The honest trigger isn't row count on its own — it's whether search is a core product feature or a nice-to-have. A support ticket search that gets used twice a day doesn't need Typesense. A marketplace where search is the product, or a docs site where users bounce if the first result isn't right, earns the extra infrastructure.
Meilisearch and Typesense are the two worth knowing. Both are open-source, self-hostable or managed, and built specifically for typo-tolerant, sub-50ms, instant-as-you-type search — the thing Postgres can approximate but wasn't designed for. Algolia does the same job as a fully managed SaaS with a more mature UI toolkit and a per-request pricing model that gets expensive fast at real volume.
The tradeoff you're accepting either way: a second system to keep in sync with your primary database, usually via a webhook or a queued job on every create/update/delete. That's not free. It's worth it exactly when search quality is something users would notice and complain about if it were worse.
A comparison: Postgres search vs. a dedicated engine
| Concern | Postgres full-text search | Meilisearch / Typesense / Algolia |
|---|---|---|
| Setup cost | One migration, one index | New service, sync pipeline |
| Typo tolerance | Partial, via pg_trgm | Native, out of the box |
| Latency at scale | Good to ~500k rows | Sub-50ms at millions of rows |
| Data freshness | Always live (same DB) | Eventually consistent, needs sync |
| Extra infra to run | None | Yes — hosted or self-managed |
| Right for | Internal tools, most SaaS search | Marketplaces, docs, e-commerce |
Where this lives in a Greta-built app
A Greta-scaffolded app runs on Postgres by default, so full-text search is usually the right starting point — you're not adding anything, just using what's already there. The tsvector column and GIN index live in the same schema as everything else, and the query sits in a normal API route next to your other data-fetching logic, not as some bolted-on service.
If a project does need to graduate to a dedicated engine later, the sync job that keeps it current is the same background-job pattern covered in running background jobs and scheduled tasks — a queued job on write, not an inline call that slows down every save. And because search endpoints tend to be some of the most-hit routes in an app, it's worth pairing them with the tiered limits from rate limiting and abuse prevention, especially if search results ever feed into something that calls an LLM, like AI-generated summaries of results.
Got an idea? Build it now!
Just start with a simple Prompt. No coding required — Greta turns your idea into a working app in minutes.
FAQ
Can I just use ILIKE for a small app? For a handful of rows and an internal tool nobody depends on, sure. The moment search is a feature real users touch daily, switch to tsvector — it costs one migration and removes an entire category of "why is search slow" bug reports later.
Does Postgres full-text search work across multiple tables? Not in one query without extra work. You either combine results from separate queries in application code, or build a materialized view that unions the searchable fields you care about. It's doable, just not automatic the way it is in a purpose-built search engine.
Is Algolia worth the price for a small startup? Usually not at first. Algolia's strength is a polished UI toolkit and instant setup, but the per-search pricing adds up once you have real traffic. Meilisearch or Typesense get you 90% of the experience self-hosted for a fraction of the cost, and Postgres gets you further than most people expect before you need either.
How do I add typo tolerance to Postgres search without switching engines? Enable the pg_trgm extension and add a trigram index for similarity matching alongside your tsvector column. It's not as forgiving as a dedicated fuzzy-search engine, but it catches the common case — a couple of transposed or missing letters — without adding a new service to operate.
Closing
Search is one of those features where the obvious first move — ILIKE in a WHERE clause — is also the one that quietly turns into a performance problem nobody notices until the table's grown ten times over. Postgres full-text search fixes that for the vast majority of apps with one migration and no new infrastructure. Save the dedicated search engine for the day search itself is the product.
