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

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:

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, 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, 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 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:

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) 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 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) 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 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). 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}