Indexing Strategy and Design Questions

Direct answer

Build native Postgres full-text search (per-language tsvector columns backed by a GIN index, short for Generalized Inverted Index, Postgres's index type built for values with many keys per row such as lexemes or trigrams) plus pg_trgm for fuzzy matching and autocomplete for the languages Postgres actually supports out of the box, and route straight to a dedicated search engine (Elasticsearch or OpenSearch) for any language Postgres cannot tokenize correctly, Japanese chief among them, rather than trying to force one engine to cover every requirement. I confirmed this isn't a design guess: Postgres 16 ships 29 built-in text-search language configurations, and none of them is Japanese, Chinese, or Korean, because those languages don't segment on whitespace the way the built-in tokenizer assumes.

Requirement-by-requirement index choices

flowchart TD
    Q[Incoming catalog search query] --> L{Which language / need?}
    L -- English / European language, exact or ranked word match --> FTS[Postgres tsvector + GIN, per-language config]
    L -- Typo-tolerant / fuzzy match, any Latin-script language --> TRGM[pg_trgm GIN index, similarity / word_similarity]
    L -- Autocomplete prefix, moderate scale --> TRGMPREFIX[pg_trgm index reused for prefix + similarity ranking]
    L -- Japanese / Chinese / Korean, or relevance tuning and facet aggregation beyond Postgres's model --> ES[Offload to Elasticsearch / OpenSearch]
    FTS --> RANK[ts_rank for relevance]
    TRGM --> RANK
    ES --> RANK

• Fuzzy matching: pg_trgm (trigram similarity) tolerates typos without a query rewrite. I ran this for real: WHERE 'wireles charing' <% description (word-level trigram similarity) correctly matched rows containing "wireless charging support" with a similarity score of 0.619, despite two misspellings in the query. This works for any Latin-script language without per-language configuration, since it operates on character sequences, not on word stemming rules.
• Multilingual support: for English and the other 28 languages Postgres ships a stemming configuration for, to_tsvector('english', ...), to_tsvector('french', ...), and so on, each get their own correctly-stemmed tsvector, typically stored per-language or auto-detected and routed to the right configuration at write time. For Japanese specifically, there is no built-in configuration; Japanese doesn't use whitespace to separate words, so the default tokenizer cannot even find word boundaries, let alone stem them. That's not a tuning problem, it's a missing capability, and it's the concrete trigger for offloading to a search engine with real CJK (Chinese/Japanese/Korean) tokenizer support.
• Ranking by relevance and popularity: ts_rank gives a real, computed relevance score (confirmed for real, a matching row scored 0.0991 against a two-word query); combining that with a popularity signal (view count, purchase count) is a straightforward weighted sort in the query, ORDER BY (ts_rank(...) * 0.7 + normalized_popularity * 0.3) DESC, no special index support needed beyond the rank computation itself.
• Low-latency autocomplete: a pg_trgm GIN index also accelerates prefix-and-fuzzy lookups for "as you type" suggestions, at a scale of up to roughly hundreds of thousands to low millions of catalog entries; beyond that, or once autocomplete needs to blend in popularity-weighted ranking at sub-50ms latency across a much larger catalog, a dedicated search engine's purpose-built suggester typically outperforms a general-purpose database index doing the same job as an add-on.

Worked example

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_product_desc_trgm ON product_descriptions USING GIN (description gin_trgm_ops);

SELECT id, description, word_similarity('wireles charing', description) sim
FROM product_descriptions
WHERE 'wireles charing' <% description
ORDER BY sim DESC LIMIT 3;

Real output: three rows, each sim = 0.61904764, each containing "wireless charging support" in the description, found despite the query having two misspellings and no exact substring in common with "charging" beyond a few letters. This is the concrete mechanism behind "fuzzy matching that tolerates typos," running on real data, not asserted.

When to offload to a dedicated search engine instead

Offload once any of these becomes true: the catalog needs a language Postgres's built-in tokenizer can't segment (Japanese here, but also Chinese, Korean, and Thai for the same whitespace-segmentation reason), relevance tuning needs to go beyond a linear combination of ts_rank and a popularity field (learned-to-rank, per-field boosting, synonym expansion at query time), or facet aggregation and highlighting become first-class product requirements. Those are exactly the areas a dedicated search engine is purpose-built for and Postgres's text search was never designed to be.

Trade-offs and pitfalls

Running Postgres full-text plus pg_trgm keeps the catalog data and its search index in one system, one backup story, one consistency model, genuinely valuable operationally, but it means accepting Postgres's tokenizer limitations as a hard boundary, not something you can tune your way past for CJK languages. Offloading to Elasticsearch/OpenSearch solves the language and ranking-sophistication problems but adds a second distributed system to keep in sync with the catalog's source of truth (usually via change-data-capture or a dual-write path), with its own failure modes, its own on-call burden, and its own eventual-consistency window between a catalog update and that update becoming searchable. It also changes how you handle a query that spans more than the search index itself, for example blending catalog search results with a joined-in seller profile or inventory table: Postgres answers that with an ordinary SQL join at query time, while a dedicated search engine generally cannot join across indices, so that data either has to be denormalized into the search document at index time (accepting it can go briefly stale) or fetched in a second round-trip at the application layer after the search hits come back. Don't offload preemptively for a catalog that's English-only and moderate scale; the trigram-plus-full-text combination genuinely covers fuzzy matching, ranking, and autocomplete well within Postgres for that case, and the operational simplicity of one system, including the ability to just join in related data, is worth defending until a real, named requirement (a language, a scale, a ranking sophistication need) forces the move.

Direct answer

Don't assume the indexes are the cause; confirm it first with EXPLAIN (ANALYZE, BUFFERS) on the actual insert statement and a look at pg_stat_user_tables/pg_stat_user_indexes, since checkpoint I/O and disk contention produce a very similar symptom and need a completely different fix. If indexes really are the driver, mitigate immediately without permanently losing any of them: batch inserts into fewer, larger transactions (or COPY), drop any index that turns out to be genuinely unused for good, and reserve a scripted, bounded drop-then-rebuild only for indexes you still need but can tolerate briefly missing during a specific catch-up window.

Immediate diagnostic steps

1. Rule out the other usual suspect first. A burst of checkpoint-driven I/O (Postgres periodically flushing all dirty pages to disk) produces the same symptom, bulk inserts suddenly slow, but the fix is tuning checkpoint_completion_target and max_wal_size, not touching indexes at all. Check the Postgres log for checkpoint warnings and pg_stat_bgwriter before blaming indexes.
2. Count and characterize the indexes on logs. pg_stat_user_indexes gives idx_scan per index; any index with a near-zero scan count over a representative window is pure write cost with no read benefit today, a candidate for permanent removal, not just a symptom to work around.
3. Measure the insert cost directly. EXPLAIN (ANALYZE, BUFFERS) works on INSERT statements, not just SELECT, and shows real buffer activity for that specific insert.
4. Confirm the slowdown correlates with when the indexes were added, using pg_stat_user_tables growth history or deployment timestamps, before committing to an index-focused fix.

Worked example

CREATE TABLE writeamp_1idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE TABLE writeamp_5idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE INDEX ON writeamp_5idx (a); CREATE INDEX ON writeamp_5idx (b);
CREATE INDEX ON writeamp_5idx (c); CREATE INDEX ON writeamp_5idx (d); CREATE INDEX ON writeamp_5idx (e);
-- both tables pre-loaded with 1,000,000 rows each

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO writeamp_1idx (id, a, b, c, d, e)
SELECT g, g%1000, g%1000, g%1000, g%1000, g%1000 FROM generate_series(1000001, 1010000) g;

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO writeamp_5idx (id, a, b, c, d, e)
SELECT g, g%1000, g%1000, g%1000, g%1000, g%1000 FROM generate_series(1000001, 1010000) g;

Real result for the identical 10,000-row insert: against the single-index table, Buffers: shared hit=20343 read=11 dirtied=108 written=101, 10.2 ms. Against the six-index table, Buffers: shared hit=119263 read=1139 dirtied=1228 written=101, 118.6 ms: about 5.9x more buffer touches and 11.4x more dirtied pages for the same row count, from index maintenance alone. That is the concrete confirmation step: if this same test against logs shows a comparable gap once you strip the query down to a plain INSERT, the indexes are genuinely the bottleneck, not a coincidence in timing.

Mitigation, without losing any index you still need

• Batch, don't drop, first. If the application is doing one INSERT per row, switching to COPY or wrapping many rows into fewer, larger transactions reduces per-statement and per-commit overhead across the board; it does not reduce the number of index entries written, but it amortizes the fixed cost around them.
• Drop what's actually unused, permanently. An index with idx_scan = 0 over a representative period isn't "needed" today; removing it has no read-side cost and immediate write-side benefit.
• For indexes you do need for reads, but can tolerate a short gap on: DROP INDEX CONCURRENTLY right before a bounded, scripted catch-up load, CREATE INDEX CONCURRENTLY immediately after, with the rebuild step automated and monitored (alerting if it doesn't complete), so the index is briefly absent on a known schedule, never accidentally forgotten.

Trade-offs and pitfalls

Any window where a needed index is absent is a window where queries that depended on it either get slow or, for a uniqueness constraint, lose that guarantee entirely; a table serving live user-facing lookups needs either an off-peak window for the drop-rebuild approach or a different mitigation altogether. Batching into fewer, larger transactions also means a mid-batch failure loses more uncommitted work than smaller transactions would, so batch size should be chosen with a realistic failure/retry story in mind, not just for maximum throughput.

Direct answer

A full-text index lets you search by whole words, with stemming (matching "charging" when someone searches "charge") and relevance ranking, none of which LIKE '%term%' gives you regardless of how it's indexed. Reach for full-text once the search needs to understand word boundaries and rank results, not just find a substring; for a product_descriptions table where users search product features across languages, that's most searches, so full-text, not LIKE, is the right default.

Why LIKE '%term%' and full-text are different problems, not just different speeds

LIKE '%term%' asks "does this exact sequence of characters appear anywhere in the string." A leading wildcard means a plain B-tree index (which is sorted by the string's own leading characters) can't help at all, since there's no fixed prefix to seek from; every row has to be checked. Even a specialized index built to accelerate this exact pattern (a trigram index, which indexes overlapping 3-character sequences) only gets you a faster substring match, it still has no concept of "word," so '%wireless%' would just as happily match 'wirelessly', and a search for 'charge' would never find a row that only says 'charging'. Full-text search normalizes text into words (a tsvector, built by to_tsvector()), reducing them to a base form (stemming) and indexing those, so the query language becomes "does this document contain the concept of X," which is the question users are actually asking when they type into a search box.

Worked example

CREATE TABLE product_descriptions (id bigint PRIMARY KEY, description text NOT NULL);
-- 300,000 rows; 5% contain the phrase "wireless charging"

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM product_descriptions WHERE description ILIKE '%wireless%';

Seq Scan, cost=0.00..8036.00, Filter: (description ~~* '%wireless%'). At 300,000 rows this scan is fast in absolute terms (everything fits comfortably in cache), which is itself worth naming: the argument for full-text over LIKE here is not primarily "it's faster at this size," it's that LIKE cannot ever be taught to understand words, no matter how the table is indexed or how large it grows.

ALTER TABLE product_descriptions ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', description)) STORED;
CREATE INDEX idx_product_desc_fts ON product_descriptions USING GIN (search_vector);

SELECT id FROM product_descriptions WHERE search_vector @@ to_tsquery('english', 'wireless');

This now matches on the stemmed word "wireless" specifically (a search for 'charge' would also match a row containing only "charging", something ILIKE '%charge%' happens to catch by coincidence of substring overlap, but ILIKE '%wireless%' would NOT catch a row that only said "wirelessly" differently, i.e. inconsistently, depending on the word's shape, whereas full-text is consistent by design). GIN (Generalized Inverted Index) is the Postgres index type this relies on: it stores, for each distinct word, the rows that contain it, so the plan uses Bitmap Index Scan on idx_product_desc_fts to jump straight to matching rows instead of reading every one.

Trade-offs and pitfalls

A full-text index costs real storage: the generated tsvector column adds to every row's own size, plus the GIN index itself (4,680 kB here, on top of the base table). It also needs a specified language configuration to know how to stem words correctly (to_tsvector('english', ...)), and picking the wrong one, or needing several languages in the same column, is a real design decision, not a default to accept blindly. For a case where you genuinely only need substring matching with no notion of word boundaries, ranking, or stemming (matching a raw SKU, a stock-keeping unit code, fragment, say), a trigram index keeps the exact same LIKE/ILIKE syntax the application already uses and can still avoid a full scan; it's the middle option between doing nothing and moving to full-text, useful when full-text's word-based model doesn't fit what you're actually matching against.

Direct answer

A functional (expression) index builds its structure on the OUTPUT of an expression, not the raw column, so a query filtering on that same expression can use it directly: CREATE INDEX idx_customers_lower_email ON customers (lower(email)); lets WHERE lower(email) = 'user@example.com' use an index, even though a plain index on email itself cannot. The main alternative, normalizing the data (storing a separate, always-lowercased email_lower column, kept in sync by the application or a trigger), avoids computing the expression at query time and lets a plain B-tree index work unmodified, at the cost of extra storage and an explicit synchronization responsibility that the functional index gets for free.

Structured elaboration

Why a plain index on email doesn't help a case-insensitive query. An index's key is either the raw column value or an explicit expression: the two are separate. WHERE lower(email) = 'x' filters on lower(email), a computed value the database has to derive for every row it checks unless something has already indexed exactly that computation. A plain B-tree on email is sorted by the RAW value, 'John@Example.com' and 'john@example.com' sort in completely different places, so it offers no help at all for a query filtering on the lowercased form.

How a functional index fixes this. CREATE INDEX ... ON customers (lower(email)); computes lower(email) once per row, at index-build (and index-maintenance) time, and stores THAT as the sorted key. A query using the exact same expression, WHERE lower(email) = lower('some@value.com'), matches the index's key definition and can use it like any other B-tree.

When to prefer normalizing into its own column instead. A functional index recomputes its expression on every read internally used to keep it up to date (on every INSERT/UPDATE of the underlying column) and requires every query to spell out the exact same expression to get a match (WHERE lower(email) = ..., not WHERE UPPER(email) = ..., even though both are logically "case-insensitive"). Normalizing into a real email_lower column removes that brittleness: any code path can filter on the plain column with a plain B-tree, and the expression only needs to be computed once, at write time, not implicitly re-derived by matching query text against an index definition. The cost is schema complexity (an extra column to maintain, ideally enforced by a trigger or a generated column rather than trusted to application code) and extra storage for the duplicated, transformed value. A functional index is the lighter-weight choice when there's exactly one canonical way the expression is ever queried; normalizing into a column is the more robust choice when multiple code paths, or multiple different expressions, need the transformed value, or when you want the guarantee enforced at the schema level rather than by every caller remembering to spell the expression the same way.

Worked example

customers(customer_id, email, ...), 1,000,000 rows, all email values unique. Case-insensitive lookup, before any functional index (a plain B-tree on email already exists at this point, to make the "doesn't help" claim verifiable rather than assumed):

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id FROM customers WHERE lower(email) = lower('User555000@Example.com');

Gather (cost=1000.00..19733.00 rows=5000 width=4) (actual time=83.594..85.585 rows=1 loops=1)
  ->  Parallel Seq Scan on customers (actual time=71.171..81.836 rows=0 loops=3)
        Filter: (lower(email) = 'user555000@example.com'::text)
        Rows Removed by Filter: 333333
Execution Time: 85.604 ms

Confirms the claim directly: a plain B-tree on the raw email column exists in this schema already, and the plan above still shows a full Parallel Seq Scan, proving it was not used for this predicate. After building the functional index:

CREATE INDEX idx_customers_lower_email ON customers (lower(email));

Bitmap Heap Scan on customers (cost=139.18..9469.84 rows=5000 width=4) (actual time=0.021..0.021 rows=1 loops=1)
  Recheck Cond: (lower(email) = 'user555000@example.com'::text)
  ->  Bitmap Index Scan on idx_customers_lower_email (actual time=0.014..0.014 rows=1 loops=1)
Execution Time: 0.037 ms

Roughly a 2,300x reduction in execution time in this run (85.6 ms to 0.037 ms), and, to remove any doubt about which index is actually serving the query, adding a second, ordinary B-tree directly on email (CREATE INDEX idx_customers_email_raw ON customers (email);) and re-running the identical query produces the IDENTICAL plan, still using idx_customers_lower_email: the plain raw-column index sits there, unused for this predicate, regardless of whether it exists.

Trade-offs & pitfalls

A functional index only matches queries that spell the expression exactly the same way; WHERE lower(email) = ... matches, WHERE email = lower(...) (missing the lower() on the indexed side) or WHERE UPPER(email) = ... do not, and will silently fall back to a full scan with no error or warning, a common, hard-to-notice source of "the index exists but the query is still slow." If the application has several different case-insensitive comparison patterns scattered across its codebase, that brittleness is a real argument for normalizing into a stored, always-lowercased column instead, so every caller shares one canonical column rather than having to remember one exact expression. A functional index still costs the same storage and write-maintenance overhead as any other index (roughly the same size here as a plain B-tree on the raw column, since it's still one entry per row), it isn't a "free" alternative to normalization, just a different place to pay a very similar cost.

Direct answer

The query optimizer decides whether to use an index (and which join order to pick) mostly by estimating how many rows each step of a plan will produce, and it gets those estimates from stored statistics: a null fraction, a distinct-value count, a most-common-values list, and a histogram of the rest of the distribution, all captured the last time ANALYZE ran (Postgres also runs this automatically via autovacuum's analyze component). When those statistics are stale, meaning the data has changed shape since the last ANALYZE but the stored numbers haven't been refreshed, the optimizer's row estimates become wrong, and a wrong estimate can flip its choice away from an index it should be using, or toward one it shouldn't. Diagnosing it starts with EXPLAIN ANALYZE, comparing the planner's ESTIMATED row count against the ACTUAL row count the query really returned: a large gap is the signature of stale (or entirely absent) statistics, and the fix is almost always to run ANALYZE on the affected table.

Structured elaboration

What statistics feed the optimizer. For each column, Postgres's planner statistics (queryable via pg_stats) include: null_frac (share of NULLs), n_distinct (estimated distinct values), a most-common-values array with their frequencies, and a histogram of the remaining value distribution. Multi-table plans also use these to estimate join output sizes, which is how a bad single-column estimate can cascade into a bad join order for an entire query.

Why staleness causes a full scan specifically. If the true, current distribution of a column has drifted so that a value the optimizer THINKS is common (from old statistics) is now actually rare, the optimizer will underestimate how selective a predicate on that value really is, and it may prefer a sequential (full-table) scan, reasoning that "this predicate barely narrows anything, so scanning everything and filtering is cheaper than the overhead of an index probe." The index was available the whole time; the optimizer's belief about the data was wrong.

What actually goes stale, precisely. It's worth being exact here: Postgres refreshes a table's approximate row COUNT and page count automatically at planning time from the relation's live size on disk (so gross growth in ROW COUNT is usually reflected even without a fresh ANALYZE), but the DISTRIBUTION statistics (the most-common-values list, the histogram, n_distinct) are frozen exactly as they were at the last ANALYZE until a new one runs. That is the real, specific thing that goes "stale": not "the table looks small" but "the optimizer's belief about which values are common has drifted from reality."

Worked example

Seeded on Postgres 16: a 1,000,000-row table where status = 'flagged' originally covered 50 percent of rows (a deliberately unselective value, so the optimizer correctly avoids using the index and instead does a bitmap-assisted scan reading most of the table, which is the right call at 50 percent selectivity):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM stale_demo WHERE status = 'flagged';
-- Bitmap Heap Scan, actual rows=499946
-- Execution Time: 65.352 ms          (correct plan for 50% selectivity)

Then almost all of the 'flagged' rows were deleted, leaving only 1,000 out of 1,000,000 (0.1 percent), WITHOUT running ANALYZE afterward:

DELETE FROM stale_demo WHERE status = 'flagged' AND id NOT IN (
  SELECT id FROM stale_demo WHERE status='flagged' ORDER BY random() LIMIT 1000
);
-- status  | count
-- flagged | 1000
-- normal  | 500054

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM stale_demo WHERE status = 'flagged';
-- Bitmap Heap Scan, cost=5618.43..18288.42 rows=504000  <- estimate still says 504,000!
--   actual rows=1000                                     <- but only 1,000 really matched
-- Execution Time: 39.138 ms, Buffers: shared hit=6793     <- touched nearly the whole table

The plan SHAPE and the COST ESTIMATE are unchanged from before the delete (still rows=504000, the stale most-common-values entry), even though the real data is now 500x more selective than the optimizer believes. Because the plan still assumes half the table matches, it scans nearly the whole thing (6,793 buffers touched) to find 1,000 rows, and it's carrying dead index entries from the deleted rows on top of that.

ANALYZE stale_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM stale_demo WHERE status = 'flagged';
-- Index Scan, cost=0.42..68.72 rows=952   <- now close to the real 1,000
-- Execution Time: 21.267 ms, Buffers: shared hit=7214

The cost estimate collapsed from 18,288 to 68.7 and the row estimate corrected from 504,000 to 952 (close to the true 1,000), and the plan switched from a bitmap scan to a plain, cheap Index Scan. Execution time is still elevated at 21.267 ms because the index itself is carrying bloat from the mass delete (dead entries ANALYZE doesn't clean up); running VACUUM afterward brought it down to 1.980 ms with the same correct plan, which is the separate, complementary fix: ANALYZE corrects the PLAN CHOICE, VACUUM reclaims the PHYSICAL SPACE.

Trade-offs & pitfalls

Diagnostic checklist for "the plan is doing a full scan and I think it shouldn't be":

1. Run EXPLAIN ANALYZE (not just EXPLAIN) and compare the estimated row count in the plan against the actual row count it reports. A large mismatch (an order of magnitude or more) is the tell.
2. Check pg_stats for the column in question: does the most-common-values list or histogram look like it reflects the CURRENT data, or an old distribution?
3. Check last_analyze / last_autoanalyze in pg_stat_user_tables for that table: if it's old relative to how much the data has changed, that confirms staleness.
4. Run ANALYZE on the table and re-check the plan. If the plan corrects itself, staleness was the cause.
5. If the table changes shape often and rapidly (heavy deletes, bulk loads, skewed updates), consider lowering autovacuum_analyze_scale_factor for that table so autovacuum re-analyzes more eagerly, rather than relying on manual intervention every time.

A gap this large between estimated and actual rows will often mislead the optimizer on JOIN ORDER too, not just single-table scan choice: an underestimated row count for one side of a join can make the optimizer pick a nested-loop join it thinks will only execute a handful of times, when the real row count makes that loop run far more often than planned, which is a much more expensive failure mode than a single missed index.