Why LIKE '%term%' is slow, full-text search, and when to reach for Elasticsearch

• LIKE '%term%' – Slow because a leading wildcard means the match can start anywhere in the string, so a B-tree index (ordered by prefix) cannot be used and the database must scan every row.
• tsvector – A normalized list of stemmed lexemes (tokens) extracted from text, stored in a column for full-text search.
• GIN index – A Generalized Inverted Index that maps each token to the rows containing it, turning substring scanning into an index lookup.
• Stemming – Reducing words to their root so that "payment," "payments," and "paying" all match the same lexeme.
• ts_rank – A PostgreSQL function that scores how well a document matches a query, enabling relevance-ordered results.
• Elasticsearch – A dedicated, horizontally scalable search engine for distributed full-text search with fuzzy matching, faceting, and custom analyzers.

Why is LIKE '%term%' slow?

A B-tree index is ordered alphabetically by the start of the value. A trailing wildcard like LIKE 'rent%' can use the index because the database seeks to entries beginning with "rent." But a leading wildcard like LIKE '%rent%' means the match could begin at any position in the string. There is no ordered starting point to seek to, so the database must read every row and test it – a full sequential scan that gets slower as the table grows.

-- SLOW: leading wildcard forces full table scan
EXPLAIN ANALYZE
SELECT txn_id, description, amount
FROM transactions
WHERE description LIKE '%rent%';

Expected EXPLAIN output:
Seq Scan on transactions  (cost=0.00..25000.00 rows=5000 width=100)
  Filter: (description ~~ '%rent%')
Planning Time: 0.1 ms
Execution Time: 1200 ms   ← full scan of millions of rows

-- FAST: trailing wildcard can use B-tree index
CREATE INDEX idx_txn_desc ON transactions (description);

EXPLAIN ANALYZE
SELECT txn_id, description, amount
FROM transactions
WHERE description LIKE 'rent%';

Expected EXPLAIN output:
Index Scan using idx_txn_desc on transactions  (cost=0.42..8.45 rows=10 width=100)
  Index Cond: (description >= 'rent' AND description < 'renu')
Planning Time: 0.1 ms
Execution Time: 0.5 ms   ← index seek, very fast

How does full-text search fix it?

Full-text search solves a better-shaped problem: matching words, not arbitrary substrings. In PostgreSQL, you convert text into a tsvector, which is a normalized list of lexemes – words lowercased, stripped of punctuation, and reduced to their stem. A GIN index on the tsvector maps each lexeme to the rows containing it, like the index at the back of a book.

-- Set up full-text search on transaction descriptions
ALTER TABLE transactions
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', description)) STORED;

CREATE INDEX idx_txn_search ON transactions USING gin(search_vector);

-- FAST: full-text search with stemming and index lookup
SELECT txn_id, description, amount,
       ts_rank(search_vector, to_tsquery('english', 'rent')) AS relevance
FROM transactions
WHERE search_vector @@ to_tsquery('english', 'rent')
ORDER BY relevance DESC;

Expected output:
 txn_id  | description                    | amount    | relevance
---------+--------------------------------+-----------+----------
 TXN-042 | Monthly rent payment for office | 85000.00  | 0.0607
 TXN-089 | Rental deposit for branch      | 200000.00 | 0.0607
-- "rent" matches "rental" thanks to stemming

How does stemming help?

Stemming reduces words to their root form so that related words match. A search for "payment" also finds "payments," "paying," and "paid," which plain LIKE cannot do.

-- Stemming demonstration
SELECT to_tsvector('english', 'The customer made monthly payments for the rental');
-- Result: 'custom':2 'made':3 'month':4 'payment':5 'rental':8
-- 'customer' → 'custom', 'payments' → 'payment', 'monthly' → 'month'

SELECT to_tsquery('english', 'paying');
-- Result: 'pay'

-- Both 'payment' and 'paying' stem to related lexemes

How do you use full-text search in a Java application?

package com.bank.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TransactionSearchService {

    private static final String SEARCH_SQL = """
        SELECT txn_id, description, amount,
               ts_rank(search_vector, to_tsquery('english', ?)) AS relevance
        FROM transactions
        WHERE search_vector @@ to_tsquery('english', ?)
        ORDER BY relevance DESC
        LIMIT 20
        """;

    public List<String> searchTransactions(Connection conn,
                                            String query) throws SQLException {
        List<String> results = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(SEARCH_SQL)) {
            ps.setString(1, query);
            ps.setString(2, query);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    results.add(rs.getString("txn_id") + ": "
                        + rs.getString("description")
                        + " (Rs." + rs.getBigDecimal("amount") + ")");
                }
            }
        }
        return results;
    }
}

How is LIKE different from full-text search?

| Aspect | LIKE '%term%' | Full-Text Search |
|---|---|---|
| Index usage | Cannot use B-tree (full scan) | Uses GIN inverted index |
| Match type | Exact substring anywhere | Word-level with stemming |
| Stemming | None: "rent" does not match "rental" | Yes: "rent" matches "rental" |
| Ranking | None: no relevance score | ts_rank for relevance ordering |
| Boolean logic | Cannot combine terms | & (AND), | (OR), ! (NOT) |
| Performance on large table | Degrades linearly with row count | Constant: index lookup |
| Setup required | None | tsvector column + GIN index |

When do you reach for Elasticsearch?

Elasticsearch is the choice when you outgrow what PostgreSQL's full-text feature offers:

• Search across tens of millions of documents that must be sharded across a cluster.
• Rich relevance scoring with custom analyzers, synonyms, and boosting.
• Typo tolerance through fuzzy matching.
• Faceted navigation and aggregations for search-driven dashboards.
• Autocomplete and suggesters.
• Very high search query throughput isolated from the transactional database.

Elasticsearch is a separate system you must keep in sync with the source of truth, usually through change-data-capture, and it is eventually consistent.

Decision rule:
- Data fits in PostgreSQL, simple word search → PostgreSQL full-text search
- Scale, fuzzy matching, faceting, synonyms → Elasticsearch

What are common mistakes?

The most common mistake is using LIKE '%term%' on a large table in production and wondering why it is slow. Another mistake is forgetting to keep the tsvector column in sync with the source text.

-- WRONG: slow LIKE on a million-row table
SELECT * FROM transactions WHERE description LIKE '%rent%';
-- Full scan: 1200 ms

-- CORRECT: full-text search with GIN index
SELECT * FROM transactions WHERE search_vector @@ to_tsquery('english', 'rent');
-- Index lookup: 2 ms

What should a senior engineer know?

A senior engineer knows that a leading wildcard prevents index usage, that full-text search with a GIN index solves the performance problem while adding stemming and ranking, that the tsvector should be a stored generated column for automatic sync, and that Elasticsearch is justified only when scale, fuzzy matching, or search-specific features exceed what the relational database can provide.