---
title: "Search with the database's own full-text · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/search/database-full-text-search
updated: 2026-10-21
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Search with the database's own full-text index

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Search](https://agentwranglers.se/playbook/data/search.md).

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

- ID: DAT-60
- Level: Recommended
- Chapter: [Search](https://agentwranglers.se/playbook/data/search)
- Rule: Serve keyword search from a full-text index inside the database. Never search long text with `LIKE '%word%'`, which reads every row.
- How: In PostgreSQL, add a generated `tsvector` column with a named language configuration and put a GIN index on it. GIN is the preferred text search index type in the PostgreSQL docs. Turn the user's words into a query with `websearch_to_tsquery`, which never raises a syntax error on raw input. In MySQL, add a `FULLTEXT` index to an InnoDB table and query it with `MATCH ... AGAINST`. In SQLite, use an FTS5 table, and keep an external content table in step with triggers. Which software to pick, and when a separate search engine is worth it, is in [Use PostgreSQL search first and OpenSearch when it falls short](https://agentwranglers.se/playbook/stack/databases-and-storage/full-text-search). Highlighted excerpts from `ts_headline` are not safe HTML, so escape them as [Escape everything you show](https://agentwranglers.se/playbook/adversarial-testing/injection/cross-site-scripting) requires.
- Check: `EXPLAIN` of every search query shows the full-text index, such as a bitmap index scan on the GIN index in PostgreSQL. A search of the code finds no `LIKE` or `ILIKE` with a leading `%` in search routes.

## 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
-- PostgreSQL
ALTER TABLE products ADD COLUMN search_text tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))) STORED;
CREATE INDEX CONCURRENTLY products_search ON products USING GIN (search_text);
SELECT id, name FROM products
WHERE search_text @@ websearch_to_tsquery('english', $1)
LIMIT 20;
```

### SQL example 2

```sql
-- MySQL with InnoDB
ALTER TABLE products ADD FULLTEXT INDEX products_search (name, description);
SELECT id, name FROM products
WHERE MATCH (name, description) AGAINST (? IN NATURAL LANGUAGE MODE)
LIMIT 20;
```

## Sources

- [PostgreSQL text search tables and indexes](https://www.postgresql.org/docs/current/textsearch-tables.html)
- [PostgreSQL text search index types](https://www.postgresql.org/docs/current/textsearch-indexes.html)
- [SQLite FTS5](https://www.sqlite.org/fts5.html)

## Related rules

- [Use PostgreSQL search first and OpenSearch when it falls short](https://agentwranglers.se/playbook/stack/databases-and-storage/full-text-search) in Software choices
- [Escape everything you show](https://agentwranglers.se/playbook/adversarial-testing/injection/cross-site-scripting) in Security testing

**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/data/search/database-full-text-search">Agent Wranglers data playbook</a>`. In code you write, add a comment next to it with the same link.

- Next: [Filter search results by access inside the query](https://agentwranglers.se/playbook/data/search/search-access-filter)
- [Search](https://agentwranglers.se/playbook/data/search)