Indexing Strategy and Design Questions

Direct answer

The Seq Scan on orders step is the most telling clue: its ESTIMATED row count (100,000) matches its ACTUAL row count (100,000) almost exactly, which normally means the optimizer's statistics were accurate for this predicate, ruling out the single most common cause (stale statistics) for the orders side specifically. What's genuinely alarming is the gap between the abstract cost=0.00..1000.00 and the huge real actual time=0.05..1200.00 (1,200 milliseconds): a cost of 1000 in Postgres's default cost units, calibrated against seq_page_cost = 1, is not a promise of any particular millisecond figure, it is an abstract estimate of relative page-read effort assuming typical, largely cached I/O. When actual time blows far past what that estimate would suggest for a supposedly cheap scan, the leading suspects are disk-level: the pages being read are not in cache and are coming from slow or contended storage, or the table has bloat (dead, not-yet-vacuumed rows inflating how many physical pages a logically-100,000-row result has to scan through).

Structured elaboration

Why the planner might still choose a Seq Scan on orders even with an index on customer_id existing. The fragment shows customer_id = 42 matching 100,000 rows with the estimate landing right on the actual count. If this represents a large share of the table (say the table itself isn't much bigger than a few hundred thousand rows, or this table only holds data for a handful of large accounts), the optimizer is very likely correct to prefer a sequential scan: at that selectivity, following an index to fetch 100,000 individually-scattered rows is usually MORE expensive than reading the table straight through, not a planner mistake at all. The presence of an index does not obligate the optimizer to use it; the second half of the fragment shows an index being used in the same overall plan (Nested Loop -> Index Scan using idx_customers), which confirms the planner is capable of choosing index scans for this query, it simply didn't find one worthwhile for the orders side.

Why the actual runtime is so much larger than the cost estimate suggests. Postgres's cost units are calibrated by a handful of tunable settings, seq_page_cost (default 1) and random_page_cost (default 4) among them, which represent the RELATIVE expense of reading a page sequentially versus randomly, assuming a fairly standard, warm-cache-friendly storage setup. They are not literal milliseconds. A "cheap-looking" cost of 1000 reading from a cold cache, a heavily loaded disk, or network-attached storage (common on cloud-hosted databases) can take far longer in wall-clock time than the cost number would suggest to someone reading it as if it meant something absolute.

Diagnostic steps, in the order to actually run them:

1. Re-run with EXPLAIN (ANALYZE, BUFFERS) if it wasn't already, and check the Buffers: shared hit=X read=Y line: a large read count relative to hit means most pages came from disk (or the OS page cache had to fetch them fresh), directly explaining slow wall-clock time despite a modest abstract cost.
2. Check pg_stat_user_tables for last_analyze / last_autoanalyze and n_dead_tup: stale statistics or heavy unvacuumed bloat both inflate the number of physical pages a scan has to churn through, even when the row-count estimate itself still happens to be accurate.
3. Check whether this ran on a cold cache (first query after a restart, or a table that's rarely touched) versus a warmed one; a second, immediate re-run of the identical query separates "genuinely slow I/O" from "one-time cold cache" as an explanation.
4. Compare random_page_cost and effective_cache_size against the real storage this database sits on; defaults assume commodity spinning-disk-era ratios that are frequently wrong for SSD or cloud storage, which can cause the optimizer's cost MODEL to be internally consistent but still miscalibrated against wall-clock reality.

Fixes, matched to cause. If it's genuinely a cold-cache/slow-storage issue: nothing about the query or schema is wrong, this is an infrastructure/caching question, consider whether effective_cache_size reflects real available memory, and whether this table's working set fits in cache at all. If it's bloat: run VACUUM (or address why autovacuum isn't keeping up). If it's genuinely bad statistics (which this particular fragment's row estimates argue against, but a real investigation should still verify rather than assume): run ANALYZE and re-check the plan.

Worked example

The "stale statistics" cause specifically (even though it doesn't appear to be the culprit for orders in this fragment, it's the most common real-world version of "index exists but the plan or its actual/estimate gap looks wrong," and is directly reproducible): starting from orders, customer_id = 4 matching a single row, with fresh statistics:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4;

Index Scan using idx_orders_customer_id on orders (cost=0.43..8.48 rows=3 width=29) (actual time=0.018..0.018 rows=1 loops=1)
Execution Time: 0.073 ms

Now insert 60,000 new rows for that SAME customer_id, without running ANALYZE afterward, simulating a burst of new activity the optimizer doesn't know about yet:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4;

Index Scan using idx_orders_customer_id on orders (cost=0.43..8.48 rows=3 width=29) (actual time=0.020..2.840 rows=60001 loops=1)
  Buffers: shared hit=492
Execution Time: 3.793 ms

The cost estimate did not move at all (still rows=3 expected), but actual rows returned jumped to 60,001, a roughly 20,000x estimate error. In this dataset the absolute time stayed low (3.8 ms) purely because the working set stayed cache-resident (Buffers: shared hit=492, all cache hits, no disk reads); at larger scale, or on cold cache, this exact mechanism (planner confidently executing a plan built for 3 rows against a reality of 60,001) is what produces a runaway actual-versus-estimate gap like the one in the fragment. Running ANALYZE orders; and re-checking confirms the fix:

Bitmap Heap Scan on orders (cost=948.47..17366.94 rows=59877 width=29) (actual time=0.517..2.319 rows=60001 loops=1)

Post-ANALYZE, the estimate (59,877) now closely tracks the truth (60,001), and the plan itself changed shape (from a plain Index Scan to a Bitmap Heap Scan) because the optimizer now has an accurate picture to plan against.

Trade-offs & pitfalls

Resist jumping straight to "the statistics must be stale" as the universal answer just because it's the most common cause; this fragment's own numbers (estimate equals actual on the orders scan) argue against it for this specific case, and treating every actual-versus-cost mismatch as a stats problem, without checking Buffers and the storage/caching angle first, wastes time. Also resist treating the raw cost number as if it were milliseconds; that single misreading is behind a large share of "why is the estimate so wrong" confusion when the estimate, read correctly as a relative unit, was not actually wrong at all.

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

When a fact table's queries are dominated by many small, low-selectivity index range scans, the fix usually isn't "add more indexes," it's changing what kind of access pattern the query performs at all: consider a covering (INCLUDE) or composite index that lets the database satisfy the query from the index alone without random-access table fetches, restructure the query to raise combined selectivity (adding a second, more selective predicate to a composite index), or, if the workload is inherently a low-selectivity scan over a large slice of the table, accept that indexing this particular query is fighting its actual shape and look at a different strategy for the table's physical layout instead of another index. Hardware and configuration tuning (faster storage, more memory for caching) can mask the symptom but does not change the underlying selectivity math.

Structured elaboration

Why low selectivity defeats indexing at scale. A B-tree index earns its cost by eliminating rows before the database has to read them. Once a predicate matches a large fraction of the table (a low-selectivity, meaning weakly-narrowing, predicate), "many small index range scans" is exactly what that looks like in practice: the index correctly identifies a huge number of small, scattered matches, and the database ends up doing that many scattered, random-access reads against the table, one per match, which is often SLOWER than reading the table sequentially and filtering as it goes, because random reads cost more per row than sequential ones.

Composite/covering indexes, the indexing-specific fix. Two changes stay within indexing and can genuinely help: first, if the query only needs a few columns back, make the index covering (include those columns) so each match is satisfied from the index itself with no table round-trip at all, which turns "many small random table reads" into "many small, but much cheaper, index-only reads." Second, if there's a second predicate in the query beyond the low-selectivity one, put both in a composite index, leading with whichever narrows the result the most; the combined selectivity of two predicates together is usually far better than either alone, even if the second predicate looked minor.

When indexing genuinely is not the answer. If the query's actual job is "summarize a large fraction of a fact table" (a BI report scanning a whole quarter's activity, say), no index rearrangement fixes that, because the query was never going to touch a small slice of the table to begin with. At that point, the levers that matter are workload-shape changes: a separate aggregate/summary table maintained incrementally, materialized views, or reading from an OLAP (online analytical processing, meaning built for large summarizing scans) replica, rather than another index on the same OLTP (online transaction processing, meaning the live, write-heavy application table) table.

Hardware and configuration are supporting context, not the fix. Faster storage (raising effective random I/O throughput) and more cache (effective_cache_size, or just more available memory) reduce the pain of scattered small reads, but they don't change the fact that the query is fundamentally reading a large fraction of the table; they buy headroom, not a structural fix, and they stop helping once data volume outgrows the extra headroom.

Worked example

order_items(order_item_id, order_id, product_id, quantity, order_date), 800,000 rows, quantity uniformly distributed across only 5 distinct values (1 through 5), so WHERE quantity = 3 matches roughly 20%, a textbook low-selectivity predicate:

CREATE INDEX idx_order_items_quantity ON order_items (quantity);
EXPLAIN (ANALYZE, BUFFERS) SELECT order_item_id FROM order_items WHERE quantity = 3;

Bitmap Heap Scan on order_items (cost=1742.46..9577.12 rows=156133 width=8) (actual time=4.587..88.327 rows=159691 loops=1)
  Recheck Cond: (quantity = 3)
  Heap Blocks: exact=5883
  ->  Bitmap Index Scan on idx_order_items_quantity (actual time=4.151..4.152 rows=159691 loops=1)
Execution Time: 90.916 ms

Forcing a plain sequential scan on the SAME query, for comparison:

Seq Scan on order_items (cost=0.00..15883.00 rows=156133 width=8) (actual time=0.011..25.301 rows=159691 loops=1)
  Filter: (quantity = 3)
  Rows Removed by Filter: 640309
Execution Time: 28.008 ms

In this run, the sequential scan actually finished faster (about 28 ms) than the index-assisted plan the optimizer's own cost model preferred (about 91 ms), even though the index-assisted plan had the lower ESTIMATED cost (9,577 versus 15,883). This is a genuine, honest result, not a benchmark artifact to explain away: it demonstrates precisely why "an index exists" is not the same claim as "the index helps," and why the optimizer's cost estimate and a single run's observed timing can disagree, since the cost model reasons about expected page reads in the abstract, not the specific caching state of one run. The fix for THIS specific query is not a better index on quantity alone, since no single-column index on a 5-value column will ever get much more selective; it's either combining quantity with a more selective predicate in a composite index, or accepting that this filter, on its own, is a poor fit for indexing and letting it scan.

Trade-offs & pitfalls

Resist the urge to respond to "the index isn't helping enough" by adding yet another index on the same low-cardinality column with a different column order, or by indexing a second low-cardinality column separately; neither changes the fundamental selectivity math. Confirm the diagnosis before applying a fix: check the predicate's actual selectivity (distinct value count and distribution) rather than assuming an index will help just because a WHERE clause exists. If a covering or composite redesign still leaves a query touching a large fraction of a genuinely huge fact table, that is a signal to reconsider the table's physical layout (partitioning by the dimension most queries filter on, or a separate aggregate path) rather than to keep iterating on indexes.

Direct answer

A B-tree index on orders.customer_id lets the database jump straight to the rows for a given customer instead of scanning the whole table, which is a large win for point lookups (WHERE customer_id = ?). The same index makes every INSERT, and every UPDATE that touches customer_id, more expensive, because the database must also insert a new entry into the B-tree (a sorted, self-balancing tree structure) in the correct sorted position, not just append to the table. It also costs storage: in a 2,000,000-row orders table, a single-column B-tree index on an integer column measured 24 MB against a 100 MB table, roughly a quarter of the table's own size again.

Structured elaboration

Read side. A B-tree keeps its keys sorted and organized into a shallow tree, so an equality lookup takes on the order of log(row count) comparisons to find the first matching entry, then follows pointers to the matching rows. Without the index, the database has no choice but to read every row (a sequential scan) and evaluate the filter against each one, a cost that grows linearly with table size.

Write side. An INSERT into an indexed table is not just "append a row." The database also has to locate the correct position in the B-tree for the new key and insert an entry there, which occasionally causes a page split (the tree rebalancing itself to make room). Each additional index on the table means one more structure to update, synchronously, inside the same transaction as the row write. This is real, measurable I/O (input/output, meaning disk or cache page reads and writes) and CPU work, not just a theoretical cost.

Storage side. Every index is a second on-disk copy of the indexed columns (plus row pointers), sized independently of the table. A narrow, high-cardinality (many distinct values) integer column indexes relatively cheaply; wider or lower-cardinality columns, or indexes covering multiple columns, cost more.

OLTP vs. BI/reporting trade-off. Online transaction processing (OLTP) workloads (the app's live traffic: creating orders, updating order status) are write-heavy and latency-sensitive on individual rows, so they are the ones that pay the write tax on every index. BI/reporting workloads are read-heavy over large ranges and usually tolerate more indexes, or even a separate, denormalized copy of the data purpose-built for scanning, because they are not the ones absorbing the insert cost. The practical implication: an index that makes a dashboard query fast is not free just because the dashboard is happy. Someone is paying for it on the write path, and for a high-throughput OLTP table that cost is real.

Worked example

Point lookup on orders.customer_id, 2,000,000-row orders table, before any index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 424242;

Gather  (cost=1000.00..24156.07 rows=4 width=18) (actual time=10.296..22.292 rows=4 loops=1)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders  (cost=0.00..23155.67 rows=2 width=18) (actual time=10.020..17.318 rows=1 loops=3)
        Filter: (customer_id = 424242)
        Rows Removed by Filter: 666665
Execution Time: 22.303 ms

After CREATE INDEX idx_orders_customer_id ON orders(customer_id); (measured: 24 MB, versus 100 MB for the table at that point):

Index Scan using idx_orders_customer_id on orders  (cost=0.43..19.73 rows=4 width=18) (actual time=0.016..0.021 rows=4 loops=1)
  Index Cond: (customer_id = 424242)
Execution Time: 0.025 ms

That is the read win: roughly 890x less execution time in this single run, driven by reading a handful of pages instead of scanning the whole table.

Now the write cost. Two identical staging tables were built, one with the same B-tree index on customer_id, one without, and 200,000 new rows were inserted into each with EXPLAIN (ANALYZE, BUFFERS) wrapped around the INSERT:

-- no index on customer_id:
Insert on orders_noidx  (actual time=187.357..187.358 rows=0 loops=1)
  Buffers: shared hit=405843 read=3 dirtied=1830 written=1822
Execution Time: 187.595 ms

-- same 200,000 rows, WITH the index on customer_id:
Insert on orders_idx  (actual time=359.504..359.505 rows=0 loops=1)
  Buffers: shared hit=1007046 read=1205 dirtied=4237 written=3024
Execution Time: 359.746 ms

The buffer count (pages touched) more than doubled (405,843 to 1,007,046, roughly 2.5x), the pages actually dirtied (modified, meaning they must eventually be written back to disk) also increased sharply (1,830 to 4,237, roughly 2.3x), and execution time rose by a comparable margin (187.6 ms to 359.7 ms, roughly 1.9x), because every one of the 200,000 inserts now had to write a table row AND maintain the B-tree. These three multiples are close but not identical (2.5x, 2.3x, 1.9x): "touched" counts every buffer access, including repeat visits to the same hot B-tree pages during the index insert, while "dirtied" only counts each page the first time it is modified in this statement, which is why it grows a little slower than the raw touch count. The buffer counts are the reliable signal here (they reflect real page-level work the engine did), and the timing, from a single, non-benchmarked run in one container, moved in the same direction, which is consistent, not a coincidence.

Trade-offs & pitfalls

The read win and the write cost are not symmetric with volume: the read win applies once per query, however many times it runs; the write cost applies to every single row written, forever, whether or not anyone ever runs the query the index was built for. For a high-throughput OLTP orders table, adding an index because "reporting wants it" is a decision that should weigh insert/update volume just as heavily as the reporting query's benefit; a common resolution is to serve reporting from a read replica or an OLAP (online analytical processing, the read-heavy, large-scan-oriented counterpart to OLTP) store instead of adding write-path cost to the primary. A related pitfall: indexing a column that changes frequently (as opposed to one that is only ever inserted and read) multiplies the write cost, since every UPDATE to that column means removing the old B-tree entry and inserting a new one.

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.