---
title: "Use PostgreSQL search first and OpenSearch · Agent Wranglers"
url: https://agentwranglers.se/playbook/stack/databases-and-storage/full-text-search
updated: 2026-10-21
licence: "All rights reserved"
---

# Use PostgreSQL search first and OpenSearch when it falls short

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers). Updated 2026-10-21.

- Level: Recommended
- Chapter: [Databases and storage](https://agentwranglers.se/playbook/stack/databases-and-storage)
- Rule: Build search on PostgreSQL full-text search first. Add OpenSearch only when a measured need fails in PostgreSQL.
- Cost: Open source. PostgreSQL search is built in, and OpenSearch is Apache-2.0. Managed OpenSearch hosting is paid.
- How: 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](https://agentwranglers.se/playbook/data/caches-and-copies/search-index-sync). 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.
- 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.

## 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

```sql
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

```js
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

```python
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()
```

## Sources

- [PostgreSQL text search](https://www.postgresql.org/docs/current/textsearch.html)
- [OpenSearch releases](https://opensearch.org/releases/)

## Related rules

- [Keep search indexes in step](https://agentwranglers.se/playbook/data/caches-and-copies/search-index-sync) in Data

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/stack/databases-and-storage/full-text-search">Agent Wranglers software choices playbook</a>`. In code you write, add a comment next to it with the same link.

- Previous: [Use the migration tool that comes with your ORM](https://agentwranglers.se/playbook/stack/databases-and-storage/migration-tool)
- Next: [Use the S3 API for files and SeaweedFS to host them](https://agentwranglers.se/playbook/stack/databases-and-storage/s3-api)
- [Databases and storage](https://agentwranglers.se/playbook/stack/databases-and-storage)