When to add a search engine alongside PostgreSQL

A catalogue search can respond quickly and still return the wrong product. Before adding a search engine alongside PostgreSQL, describe the failure: a wanted item is missing, an irrelevant accessory appears, a typo produces no results, or an update takes too long to arrive.

I would start with those cases and the alternatives the team can actually operate. A separate engine deserves a trial when it offers a sustainable way to meet a requirement the current solution cannot satisfy. The row count alone does not identify that requirement.

A correct search can find too much

Consider an Italian catalogue containing products from several organisations. Each search must stay within the authorised organisation’s catalogue and show only public products. In this example, someone searching for “lampada tavolo” wants a table lamp; a table whose description mentions a lamp that is not included must be excluded. This is an illustrative requirement, fixed before the comparison.

PostgreSQL full-text search converts text into normalised terms and looks for matches. A GIN index helps find documents containing those terms. Ranking then assigns a score to order them; that number does not certify commercial relevance.

This PostgreSQL 18 baseline uses the Italian configuration and gives the title more weight than the description. Those weights are choices for the example, to be tuned for the catalogue. The documentation explains configuration and ranking; GIN is the preferred index for text search.

The following block creates a demonstration table in an empty test database. It requires a PostgreSQL 18 connection with permission to create tables and indexes and can run in a psql session. All data is synthetic; the Italian names and search terms are kept to match the language configuration.

CREATE TABLE products (
  tenant_id integer NOT NULL,
  id integer PRIMARY KEY,
  title text NOT NULL,
  description text NOT NULL DEFAULT '',
  is_public boolean NOT NULL,
  search_doc tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('italian', title), 'A') ||
    setweight(to_tsvector('italian', description), 'D')
  ) STORED
);
CREATE INDEX products_search_gin ON products USING gin(search_doc);

INSERT INTO products(tenant_id,id,title,description,is_public) VALUES
(1,101,'Lampada da tavolo','Luce orientabile',true),
(1,102,'Lampada da terra','Luce per il soggiorno',true),
(1,103,'Lampada da tavolo riservata','Prototipo',false),
(2,104,'Lampada da tavolo','Altro catalogo',true),
(1,105,'Tavolo in legno','Lampada da tavolo non inclusa',true);

PREPARE catalog_search(integer,text) AS
SELECT id, title, ts_rank_cd(search_doc, q) AS rank
FROM products, websearch_to_tsquery('italian', $2) AS q
WHERE tenant_id = $1 AND is_public AND search_doc @@ q
ORDER BY rank DESC, id
LIMIT 20;

EXECUTE catalog_search(1, 'lampada tavolo');

search_doc is a database-generated column. $1 and $2 are parameters for the organisation and search text. In an application, the organisation must come from the server’s authorised context; freely accepting a number sent by the client does not provide isolation. The filters logically precede the limit of twenty results, and the ID gives a stable order when scores tie.

On PostgreSQL 18.6, this query returns 101, followed by 105. It excludes the private product and the other organisation’s product, but includes the table: its description contains the terms even though the lamp is not for sale. Five rows make this error visible; they do not measure system performance.

Fix the catalogue before changing engines

The baseline fails the stated relevance requirement. One correction is to obtain an explicit category from the user and apply it before ranking. This requires an interface that offers that choice and reliable product classification.

In the same test session, after the previous block, we can add the category. Assigning it by ID is only for these five synthetic records. In a real catalogue, category is product data, not something inferred from these identifiers.

ALTER TABLE products ADD COLUMN category text;
UPDATE products
SET category = CASE WHEN id = 105 THEN 'tavoli' ELSE 'lampade' END;
ALTER TABLE products ALTER COLUMN category SET NOT NULL;

PREPARE category_search(integer,text,text) AS
SELECT id, title, ts_rank_cd(search_doc,q) AS rank
FROM products, websearch_to_tsquery('italian',$2) AS q
WHERE tenant_id=$1 AND is_public
  AND category=$3 AND search_doc@@q
ORDER BY rank DESC,id
LIMIT 20;

EXECUTE category_search(1,'lampada tavolo','lampade');
EXECUTE category_search(1,'tavolo legno','tavoli');

The first query returns only 101: table 105 no longer enters the candidate set. The second returns 105, as expected for a table search. These outcomes were checked on PostgreSQL 18.6. Increasing the title’s weight could have changed the order; the filter changes which products may appear.

The cost becomes visible when 101 is deliberately misclassified as tavoli: the lamp search returns no rows. Restoring its category brings 101 back in the check. The filter removes a false positive but can introduce a false negative. Using it requires ownership of category quality and a way to report misclassified products.

I would choose this solution when customers can supply the category and the catalogue maintains it reliably. It cannot be the sole solution for a search that must span categories without asking for a filter. That is a different requirement, calling for other retrieval and ranking criteria. A candidate engine must demonstrate that it meets it, rather than merely finding the same words faster.

A typo does not decide the architecture

In the same session, EXECUTE catalog_search(1, 'lampda tavolo'); returns no rows.

PostgreSQL also provides pg_trgm, an extension that compares three-character sequences to find similar strings. It can help handle misspellings alongside full-text search. In a separate check, similarity('lampda','lampada') returns 0.5. This shows similarity between those two strings, not an automatic correction already integrated into the catalogue.

For this catalogue, I would start with explicit suggestions: after “lampda tavolo”, the interface could offer “Search instead for lampada tavolo” while keeping the original request visible. Confirmation starts a separate search. This product choice accepts an extra click to avoid silently changing the request’s meaning.

Candidates can come from a vocabulary of catalogue terms, as in the full-text integration described for pg_trgm. I would restrict suggestions to the same authorised scope as the search: even a term drawn from a private product can reveal information. The vocabulary needs updating and thresholds need testing against actual terms.

I would keep product-code lookup separate, with a dedicated field and exact matching. An unknown code resembling an existing one is not permission to substitute it. For words, I would test the correctly spelled request, the intended typo and a rare but valid word close to a more common one. I would record original results, the suggestion and results after confirmation separately.

An empty result can also come from filters or a product simply being absent. I would therefore avoid presenting every unsuccessful search as a spelling mistake. The proposed behaviour needs testing in the interface; the two-string similarity measured above is insufficient to judge its usefulness.

The internal alternative also extends beyond built-in functions. ParadeDB documents a PostgreSQL extension with BM25 ranking, a family of methods for ordering documents against search terms. It is a candidate if it fits the hosting environment, upgrade process and database operations. A constraint that prevents installation is a reason to exclude it without requiring a pointless trial.

A service such as Elasticsearch provides queries across multiple fields, weights and typo-tolerance options. These are tools for building the desired search. Their benefit for the catalogue needs to be checked against the same cases, without directly comparing scores produced by different algorithms.

The new component brings a second copy

With an external index, PostgreSQL can remain the authoritative product store. Every insertion, update, deletion and visibility change must also reach the copy used for search.

A transactional outbox is one option: the same transaction updates the product and records an event in a table. A separate process reads the events and updates the index. A commit then cannot save the product while forgetting its event, provided both writes belong to that transaction.

The process may send an event more than once. The consumer must tolerate duplicates and prevent an old version from overwriting a newer one. I would include a version for each product and an explicit deletion rule: a delayed event must not bring back a removed product.

Receiving a document also does not mean it is searchable yet. Elasticsearch distinguishes indexing from the refresh that makes changes visible to searches. The delay from commit to visibility includes propagation, processing and waiting for a refresh. The refresh interval alone is not a promise about overall freshness.

An outdated title and revoked access need separate treatment. If revocation must take effect immediately, a delayed copy cannot be the only check. The API path must verify current authorisation before exposing protected data, including snippets and counts. Filtering after taking the first twenty candidates can also leave pages incomplete; the requirement needs to be designed together with result retrieval.

The test that justifies the move

I would define the comparison before configuring the candidate engine, using anonymised real queries and expected results agreed with people who know the catalogue.

After making 101 private, rerunning EXECUTE catalog_search(1, 'lampada tavolo') returns only 105. The visibility filter excludes the revoked product; the relevance problem in the query without a category remains. The load, propagation and recovery tests in the table are proposed next steps, not results from a comparison with Elasticsearch or ParadeDB.

Trial the candidate before changing responses

If a requirement remained unmet, I would write down the decision before running the trial. For example, using the initial catalogue with 101 public, the product requires free-text search without choosing a category: “lampada tavolo” must retrieve 101 and exclude 105. The initial query fails; the category filter works but requires an interaction that the product rejects in this scenario. Whether the candidate can solve this case remains unmeasured.

That is still insufficient to choose Elasticsearch. I would include a PostgreSQL variant that treats title and description differently, alongside queries for which the description contains essential information. A solution that excludes 105 by always ignoring descriptions could miss other relevant products. Expected outcomes must precede the configuration of both alternatives.

In the proposed trial, read-only requests go to both paths while users continue to receive the current response. For an authorised sample, compare returned IDs with the agreed required and excluded products. Differences become cases to explain: a missing product, a false positive, a different order or an update that has not arrived. Running a second search adds load, so comparison traffic needs a limit that protects the existing service.

Mandatory conditions should remain separate from preferences. A candidate that exposes data after revocation cannot compensate by returning better results on other queries. Only alternatives that meet access and operational constraints proceed to comparison on relevance, latency and cost. Revocation and replay cases require controlled API tests as well as comparing index IDs.

Before switching, the path back to the previous implementation also needs testing: it must handle the expected load and preserve the same checks. It may offer a less capable search during a failure, provided that degradation is accepted. If returning is not sustainable, part of the operational decision is still missing.

The cost comparison should include service and index resources, the implementation of synchronisation and the team’s recurring time for monitoring, incidents and upgrades.

I would keep the simplest solution that meets the requirements and the team’s constraints. I would add an engine when a trial shows the necessary benefit and makes the cost of the copy acceptable. The decision worth recording is concrete: which search used to fail, how it now succeeds and who keeps that result correct as the data changes.