Use PostgreSQL search first and OpenSearch when it falls short

Cost

Open source. PostgreSQL search is built in, and OpenSearch is Apache-2.0. Managed OpenSearch hosting is paid.

How to do it

PostgreSQL full-text search ships with the database, so search needs no second system to run, back up and keep in step. It handles word stems, ranking and phrase search for many languages. OpenSearch 1.0 came out on 12 July 2021, and 3.9.0 on 29 September 2026. It is under Apache-2.0 and trademarked by LF Projects. Its Python client has about 8.9 million weekly PyPI downloads. It adds typo tolerance, facets and search across very large collections. A second system needs its own sync job, so follow Keep search indexes in step. Choose something else when search is the product itself, such as a catalogue of millions of items with typo-tolerant search, and start with OpenSearch there.

Examples

Using these examples: they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

SQL example

ALTER TABLE products ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))) STORED;
CREATE INDEX products_search_idx ON products USING GIN (search);

JavaScript example

const SEARCH_LIMIT = 20;

export const searchProducts = (pool, text) => pool.query(
  `SELECT id, name, ts_rank(search, query) AS rank
     FROM products, websearch_to_tsquery('english', $1) AS query
    WHERE search @@ query ORDER BY rank DESC LIMIT $2`,
  [text, SEARCH_LIMIT],
);

Python example

SEARCH_LIMIT = 20

def search_products(conn, text: str) -> list[tuple]:
    return conn.execute(
        """SELECT id, name, ts_rank(search, query) AS rank
             FROM products, websearch_to_tsquery('english', %s) AS query
            WHERE search @@ query ORDER BY rank DESC LIMIT %s""",
        (text, SEARCH_LIMIT),
    ).fetchall()

How to check

Search queries in PostgreSQL use a GIN index and answer within 200 ms at the 95th percentile. A decision record names the failed measure for every second search engine in use.

Sources

• PostgreSQL text search
• OpenSearch releases

Related rules

• Keep search indexes in step in Data