PostgreSQL Search Options Compared: LIKE, Trigrams, Full-Text, and External Search

Most applications need search long before they need a dedicated search platform. A customer table needs lookup by email or partial company name. A help desk needs issue titles and comments. A marketplace needs product filters and text relevance. PostgreSQL can handle many of these jobs well, but the right approach depends on what users expect search to mean.

The common mistake is treating every search box as the same database problem. Prefix lookup, fuzzy name matching, natural-language document search, JSON attribute filtering, and faceted catalog search put different pressure on indexes, writes, ranking, and operational ownership. This comparison gives you a practical way to choose between ordinary B-tree patterns, pg_trgm, PostgreSQL full-text search, JSONB indexes, and an external search service.

The decision starts with search behavior, not the tool

Before adding an index or a new service, write down the exact behavior the product needs. If users type the beginning of a customer email, a normalized prefix query may be enough. If they misspell a company name or paste part of a domain, trigram similarity is often a better fit. If they search long documents and expect ranking by terms, language rules, and phrase behavior, PostgreSQL full-text search is the feature designed for that job.

PostgreSQL's search tools overlap, but they are not interchangeable. B-tree indexes are excellent for equality, ordering, ranges, and some left-anchored prefix patterns. The pg_trgm extension supports similarity matching and can accelerate certain LIKE, ILIKE, regular expression, and similarity operators with GiST or GIN indexes. Full-text search turns documents into tsvector values and queries into tsquery values, then ranks matches with functions such as ts_rank or ts_rank_cd. JSONB GIN indexes help when the question is attribute containment rather than text relevance.

B-tree lookup is still the baseline

A surprising amount of product search is really lookup. Users search for an email address, an account slug, an invoice number, or the start of a company name. For those cases, a normalized column and a B-tree index are usually simpler than trigrams or a search engine. Store the canonical value you query, such as a lowercased email, and shape the query so PostgreSQL can use the index.

The failure mode is hidden transformation. If the table stores mixed-case names and the query wraps the column in lower(name), a plain index on name will not help that expression. Use a matching expression index, a generated normalized column, or a case-insensitive data model. For prefix search, left-anchored patterns are much easier to index than arbitrary contains searches. If the product only needs email starts with, do not pay the write and storage cost of a trigram index just because it feels more search-like.

Use pg_trgm for fuzzy strings and contains search

pg_trgm is a good fit for user-facing lookup where the searched value is a short or medium string: names, company names, domains, titles, tags, and issue summaries. It breaks text into trigrams and exposes similarity operators and functions. PostgreSQL documents that trigram indexes can support similarity search and accelerate LIKE, ILIKE, regular expression, and equality queries, depending on the operator and index class.

The practical value is forgiving search without leaving PostgreSQL. A SaaS admin page can let support staff find Acme Incorporated by typing acm incorp. A project dashboard can find issues by fragments of a title. A migration tool can support partial object-name lookup. This keeps the data path simple and avoids introducing asynchronous indexing before the product has enough search complexity to justify it.

The tradeoff is quality and write overhead. Very short strings have few trigrams, so results may be noisy. Common words and repeated prefixes can produce matches that are technically similar but not useful. GIN trigram indexes are often fast for lookup but add storage and maintenance cost on writes; GiST trigram indexes have different performance tradeoffs and support distance-style operations. Test with production-shaped strings, not a synthetic list of unique names.

Full-text search is for documents, not every text column

PostgreSQL full-text search is designed for natural-language documents. It parses text into lexemes, applies dictionaries, removes stop words, and lets you query with full-text operators. A common implementation stores a generated or maintained tsvector column and indexes it with GIN. Queries use plainto_tsquery, websearch_to_tsquery, phraseto_tsquery, or explicit tsquery syntax depending on how much control the application exposes.

This is the better option when search relevance depends on words rather than substrings. Support tickets, documentation pages, release notes, comments, product descriptions, and knowledge-base articles usually benefit from tokenization and ranking. You can weight title terms more than body terms, rank results with PostgreSQL's ranking functions, and combine full-text predicates with ordinary relational filters such as tenant_id, status, or created time.

The cost is implementation discipline. Language configuration matters; English stemming is different from simple token matching, and multilingual products may need separate configurations or a fallback strategy. Ranking is not magic either. You need to decide whether recency, status, tenant boundaries, exact title hits, or business rules should modify text rank. Full-text search can be excellent inside PostgreSQL, but it still needs product-specific relevance work.

JSONB search solves filtering, not relevance

JSONB GIN indexes are useful when users filter records by structured attributes that vary across tenants or object types. A product catalog might filter on flexible attributes, or an event table might store metadata used for diagnostics. PostgreSQL's JSONB documentation describes containment and existence operators that work naturally with GIN indexes.

That does not mean JSONB should become the default search schema. If an attribute is central to joins, constraints, sorting, or high-cardinality filters, a relational column is usually easier to validate and index predictably. JSONB search is strongest when the product genuinely needs flexible attributes and the query is about containment or existence. It is weaker when the product really needs text relevance, typo tolerance, analytics, or complex faceting.

When an external search engine is justified

An external search system becomes attractive when search is a product surface, not just a convenience feature. Autocomplete quality, typo handling, synonyms, language analyzers, highlighting, large cross-entity result sets, personalization, and search analytics are all reasons to consider a dedicated engine. The database remains the source of truth, while the search index becomes a derived read model optimized for discovery.

The operational price is real. You now own data synchronization, delayed consistency, backfills, reindexing, schema evolution, failure handling, and another bill. If a user updates a record, the application must tolerate the search result lagging behind the transaction. If a backfill fails halfway through, you need a repeatable indexing job. If the search service is unavailable, you need to decide whether the product falls back to PostgreSQL, hides search temporarily, or serves stale results.

For many early SaaS teams, PostgreSQL search is the right first system because it keeps writes transactional and operations simple. ArmorDB's managed PostgreSQL plans are a good fit for that stage because the same database can handle ordinary lookup, trigram search, full-text search, pooling, and backups while the product learns what search quality users actually need. Move search out only when the product requirements clearly exceed what the database should own.

Practical implementation path

Start with the simplest searchable model that could plausibly satisfy the product. Normalize exact lookup fields, add B-tree indexes for equality and prefix access, and measure real queries with EXPLAIN (ANALYZE, BUFFERS) on production-shaped data. If support or admin users need forgiving partial string lookup, add pg_trgm to one or two targeted columns rather than indexing every text field. If users search long text, design a tsvector strategy and ranking rules before adding the index.

Keep tenant and permission filters close to the search predicate. A fast global search query that ranks rows before applying tenant_id is both risky and expensive. In SaaS applications, the useful index often combines a narrow relational filter with a search-specific index or uses query structure that allows PostgreSQL to reduce the candidate set early. Search is not only about matching text; it is also about returning the right rows for the right user.

Review write cost before adding broad GIN indexes. GIN indexes are powerful because one row can produce many index entries, but that also means inserts and updates have more work to do. On write-heavy tables, use targeted indexes, avoid indexing low-value columns, and watch table and index growth over time. The ArmorDB pricing page is useful when estimating whether search belongs in the primary database tier or should become a separate system later.

Takeaway

Choose PostgreSQL search by user behavior. Use B-tree indexes for exact and prefix lookup, pg_trgm for forgiving partial strings, full-text search for documents and ranked term search, JSONB GIN indexes for structured containment, and external search when search itself becomes a specialized product system. PostgreSQL gives teams a strong first search stack, but the best results come from matching the tool to the query shape instead of adding the most powerful option by default.

Sources and further reading

• PostgreSQL documentation: Full Text Search
• PostgreSQL documentation: pg_trgm
• PostgreSQL documentation: JSON Types
• PostgreSQL documentation: GIN Indexes