# How Buzz Implements Full‑Text Search with Postgres FTS and GIN Indexes

> Buzz implements full-text search with Postgres FTS and GIN indexes. Learn how Buzz stores tsvector values, uses GIN indexing, and queries with websearch_to_tsquery for efficient search.

- Repository: [Block Open Source/buzz](https://github.com/block/buzz)
- Tags: deep-dive
- Published: 2026-08-27

---

**Buzz implements full‑text search by storing generated PostgreSQL `tsvector` values in a `search_tsv` column, indexing them with a GIN index, and querying via SQLx using `websearch_to_tsquery` or prefix matching, then ranking results with `ts_rank_cd` before the relay re‑validates each hit.**

The `block/buzz` repository contains a Rust‑based Nostr relay that leverages native PostgreSQL Full‑Text Search (FTS) capabilities to deliver community‑scoped search. Rather than relying on external search engines, the `buzz‑search` crate constructs dynamic SQL queries that utilize generated columns and GIN indexes to perform fast, relevance‑ranked text retrieval.

## Database Schema: The GIN Index and Generated Column

At the storage layer, Buzz defines a generated column that aggregates searchable text from each event and indexes it for sub‑millisecond containment tests.

### The search_tsv tsvector column

The `events` table includes a generated column named `search_tsv` of type `tsvector`. This column automatically compiles the searchable content of each event into a normalized document vector that PostgreSQL can index and query. By using a generated column, the database maintains the vector automatically whenever an event is inserted or updated, ensuring the FTS index stays synchronized without application‑level logic.

### GIN index creation in migrations

To accelerate the `@@` containment operator used in queries, a GIN (Generalized Inverted Index) is created on the `search_tsv` column. According to the migration files in `migrations/`, the DDL typically resembles:

```sql
CREATE TABLE events (
    id bytea PRIMARY KEY,
    created_at bigint NOT NULL,
    kind integer NOT NULL,
    -- ... additional columns ...
    search_tsv tsvector GENERATED ALWAYS AS (to_tsvector('simple', coalesce(content, ''))) STORED
);

CREATE INDEX idx_events_search_tsv ON events USING GIN (search_tsv);

```

This GIN index allows PostgreSQL to rapidly locate rows where `search_tsv` matches a `tsquery`, even across large community datasets.

## Query Architecture: Building TSQueries in Rust

The Rust implementation resides in [`crates/buzz-search/src/query.rs`](https://github.com/block/buzz/blob/main/crates/buzz-search/src/query.rs), where the `SearchQuery` struct and associated helpers translate high‑level search parameters into PostgreSQL‑native FTS syntax.

### SearchQuery struct and filters

The `SearchQuery` type defines a community‑scoped search request that includes channel constraints, event kinds, author filters, time windows, and pagination. Every query is bound to a specific `community_id`, ensuring that the fts results are automatically scoped to the requesting user’s community.

Key fields include:
- `community` – The mandatory community UUID that partitions the search space.
- `q` – The raw user search string.
- `channel_scope` – Restricts results to specific channels or channel‑less events.
- `kinds` and `authors` – Optional vectors for event kind and pubkey filtering.
- `mode` – Determines whether to use full websearch syntax or prefix matching.

### push_tsquery and search modes

Located around line 442 in [`query.rs`](https://github.com/block/buzz/blob/main/query.rs), the `push_tsquery` helper constructs the actual `tsquery` expression based on the `SearchMode` enum:

- **`SearchMode::FullText`** – Wraps the user input in `websearch_to_tsquery('simple', …)`, supporting boolean operators and quoted phrases.
- **Prefix mode** – For typeahead scenarios, the function tokenizes the input and appends `:*` to the final term, enabling prefix matching (e.g., `project:*` matches "projects", "projector").

The generated SQL fragment is then injected into a lateral subquery that feeds the main `SELECT` statement.

## Execution Flow: From SQLx to Results

The async `search` function in [`crates/buzz-search/src/query.rs`](https://github.com/block/buzz/blob/main/crates/buzz-search/src/query.rs) orchestrates query execution using `sqlx::QueryBuilder` to safely parameterize dynamic SQL.

### Dynamic SQL construction

The function composes a query that:
1. Builds a lateral subquery producing the `tsquery` as `query`.
2. Selects from the `events` table where `community_id` matches the context and `search_tsv @@ query` evaluates to true.
3. Joins against the lateral subquery to expose the query object for ranking.

The query template documented in the source comments around line 200 resembles:

```sql
SELECT e.id, e.created_at, e.kind, ts_rank_cd(search_tsv, q) as rank
FROM events e
CROSS JOIN LATERAL (SELECT websearch_to_tsquery('simple', $1) AS q) AS t
WHERE e.community_id = $2
  AND e.search_tsv @@ t.q
  AND (e.channel_id = ANY($3) OR e.channel_id IS NULL)
ORDER BY ts_rank_cd(e.search_tsv, t.q) DESC, e.created_at DESC
LIMIT $4 OFFSET $5;

```

### Ranking with ts_rank_cd

Results are ordered by `ts_rank_cd(search_tsv, query)` descending, which provides a relevance score based on cover density (higher scores indicate denser term matches). For short prefix queries targeting profile events, the implementation applies additional ordering logic to boost exact matches before falling back to the rank score.

## Security and Validation: Relay‑Side Verification

Although the database layer returns a list of matching event IDs, Buzz does not trust the raw FTS results implicitly. After the search function returns `SearchHit` objects containing event IDs and ranks, the relay (located in [`crates/buzz-relay/src/bridge.rs`](https://github.com/block/buzz/blob/main/crates/buzz-relay/src/bridge.rs)) refetches each `StoredEvent` and re‑authorizes it against the user’s permissions via the `search_hit_accepted` check. This ensures that row‑level security scoped by `community_id` in the query is double‑checked at the application layer before returning data to the client.

## Summary

- **Buzz stores searchable text as a generated `tsvector` in the `search_tsv` column of the `events` table**, ensuring the search document stays synchronized with event content.
- **A GIN index on `search_tsv`** enables PostgreSQL to execute the `@@` containment operator efficiently, even for large communities.
- **The `push_tsquery` function in [`crates/buzz-search/src/query.rs`](https://github.com/block/buzz/blob/main/crates/buzz-search/src/query.rs)** translates user input into `tsquery` expressions, supporting both websearch syntax and prefix typeahead modes.
- **Queries use `ts_rank_cd` for relevance ordering**, combining lexical ranking with temporal sorting for optimal result quality.
- **The relay performs secondary authorization** on all database hits to enforce strict access controls beyond the SQL `WHERE` clause.

## Frequently Asked Questions

### How does Buzz handle prefix search for typeahead functionality?

When `SearchMode` is configured for prefix matching, the `push_tsquery` function (around line 442 in [`crates/buzz-search/src/query.rs`](https://github.com/block/buzz/blob/main/crates/buzz-search/src/query.rs)) splits the input string and appends the `:*` operator to the final token. This generates a PostgreSQL prefix query (e.g., `to_tsquery('simple', 'road:*')`) that matches any word starting with the provided characters, enabling real‑time autocomplete without additional indexing.

### What is the role of the GIN index in Buzz's search performance?

The GIN index on the `search_tsv` column, defined in the database migrations, accelerates the `tsvector @@ tsquery` containment test from a sequential scan to an inverted index lookup. According to the Buzz source code, this index allows the relay to perform sub‑millisecond full‑text searches across communities containing millions of events, as the index directly maps lexeme entries to matching rows.

### Why does Buzz re‑validate search results in the relay after querying the database?

The `search` function in `buzz-search` returns event IDs based solely on the SQL `WHERE` clause filters (including `community_id` and `search_tsv` matches). However, the relay in [`crates/buzz-relay/src/bridge.rs`](https://github.com/block/buzz/blob/main/crates/buzz-relay/src/bridge.rs) implements a `search_hit_accepted` validation step to verify that each returned event still passes the requesting user’s authorization rules and channel permissions. This defense‑in‑depth pattern ensures that even if a query races against a permission change, the relay never exposes unauthorized data.

### How is the search_tsv column populated without application logic?

The `search_tsv` column is defined as a `GENERATED ALWAYS` column in the PostgreSQL schema migration (e.g., [`migrations/2023-01-01_create_events.sql`](https://github.com/block/buzz/blob/main/migrations/2023-01-01_create_events.sql)). It uses `to_tsvector('simple', coalesce(content, ''))` to automatically generate the search vector from the event’s `content` field whenever a row is inserted or updated. This database‑level generation removes the need for Rust code to maintain search documents and guarantees consistency between the stored text and the searchable index.