Full-Text Search in PostgreSQL for Multi-Tenant SaaS Using Go

Most SaaS teams reach for Elasticsearch the moment search becomes a requirement. It is the industrial-strength answer, but it comes with real operational costs: another cluster to provision, another set of connections to manage, data synchronization between your primary database and the search index, and a memory footprint that makes ECS task sizing more expensive. For most SaaS products under 10 million records per tenant, PostgreSQL full-text search handles product lookups, customer search, document retrieval, and order history well enough that you never need a dedicated search cluster.

How PostgreSQL full-text search works

PostgreSQL full-text search centers on two data types: tsvector, a preprocessed normalized list of lexemes (the searchable form of words), and tsquery, the parsed search query that is matched against vectors. The database stores tsvector columns, builds GIN indexes on them, and the query planner uses those indexes for fast lookups.

A tsvector for an English string is produced by to_tsvector():

SELECT to_tsvector('english', 'Organic Lebanese olive oil from Bekaa Valley');
-- Returns: 'bekaa':5 'lebanes':2 'oil':4 'oliv':3 'organ':1 'valley':6

Stopwords like "from" are stripped, and stemming reduces "Lebanese" to "lebanes" so searches for "lebanon" match "Lebanese" and "lebaon" as well.

Indexing strategy for multi-tenant SaaS

In a multi-tenant PostgreSQL schema, tables that need search get a tsvector column populated by a trigger. The GIN index must support the per-tenant filter that always accompanies a search query, or PostgreSQL will scan the full index and post-filter, which is expensive at scale.

CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    description TEXT,
    sku         TEXT,
    search_vec  TSVECTOR
);

CREATE INDEX products_search_gin ON products USING GIN(search_vec);
CREATE INDEX products_tenant_idx ON products (tenant_id);

The tsvector is maintained by a trigger on INSERT and UPDATE:

CREATE OR REPLACE FUNCTION update_product_search_vec() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vec :=
        setweight(to_tsvector('english', coalesce(NEW.name, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.sku, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(NEW.description, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_search_update
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_product_search_vec();

The setweight() call assigns priority: name matches outrank SKU matches, which outrank description matches. This ranking feeds into ts_rank() in the query to sort results by relevance.

Querying from Go with sqlc

With sqlc, the search query is type-safe and straightforward:

-- name: SearchProducts :many
SELECT id, tenant_id, name, sku, description,
       ts_rank(search_vec, query) AS rank
FROM products,
     websearch_to_tsquery('english', @search_term) query
WHERE tenant_id = @tenant_id
  AND search_vec @@ query
ORDER BY rank DESC
LIMIT @limit_count OFFSET @offset_count;

websearch_to_tsquery() parses natural language input and handles quoted phrases, AND, OR, and negation automatically. A user typing "bekaa olive oil" gets parsed to bekaa & olive & oil, and a user typing '"organic olive" -refined' gets the quoted phrase with negation.

The generated Go function:

type SearchProductsParams struct {
    TenantID    uuid.UUID
    SearchTerm  string
    LimitCount  int32
    OffsetCount int32
}

rows, err := queries.SearchProducts(ctx, SearchProductsParams{
    TenantID:    tenantID,
    SearchTerm:  userQuery,
    LimitCount:  20,
    OffsetCount: int32(page * 20),
})

Handling Arabic content

PostgreSQL supports Arabic with careful configuration. For SaaS products serving Arabic-speaking markets in Lebanon and the Gulf, the simplest reliable approach is to use the simple configuration for Arabic text, which tokenizes correctly without the stemming that English uses:

CREATE TEXT SEARCH CONFIGURATION arabic_simple (COPY = simple);

A practical pattern for bilingual SaaS products is to store two tsvector columns: one for English content with English stemming and one for Arabic content with simple tokenization:

ALTER TABLE products ADD COLUMN search_vec_ar TSVECTOR;

-- In the trigger body:
NEW.search_vec_ar := to_tsvector('arabic_simple', coalesce(NEW.name_ar, ''));

The search query uses both:

WHERE tenant_id = @tenant_id
  AND (search_vec @@ websearch_to_tsquery('english', @search_term)
    OR search_vec_ar @@ to_tsquery('arabic_simple', @search_term))

This lets a user searching in Arabic find Arabic product names, while a user searching in English finds English names, both running against the same table and the same query structure.

Performance characteristics at scale

PostgreSQL GIN indexes on tsvector are fast for the pattern of searching within one tenant's rows. At 1 million products per tenant, a search query with a GIN index lookup followed by a tenant filter runs in under 20ms on a db.t3.medium RDS instance. The bottleneck shifts to ts_rank computation when result sets are large (10,000+ matches), not to the index scan.

For tenants with very high row counts, a partial GIN index scoped to frequently searched tenants helps:

CREATE INDEX products_search_gin_hot ON products
    USING GIN(search_vec)
    WHERE tenant_id = 'high-volume-tenant-uuid';

In practice, most SaaS products with under 5 million searchable records per tenant never hit a performance ceiling with this approach. The operational simplicity of keeping search inside PostgreSQL avoids a whole class of data consistency problems that Elasticsearch introduces: delayed indexing, reindex operations, and the risk of search results diverging from the database of record.

Highlighting matched terms in results

For a better search UX, ts_headline() generates a snippet from the matched text with the search terms highlighted:

SELECT name,
       ts_headline('english', description, query,
           'MaxFragments=2, MaxWords=10, MinWords=5') AS snippet,
       ts_rank(search_vec, query) AS rank
FROM products,
     websearch_to_tsquery('english', @search_term) query
WHERE tenant_id = @tenant_id
  AND search_vec @@ query
ORDER BY rank DESC
LIMIT 20;

ts_headline() adds some compute cost per result row, so it should only be used when rendering search results to a user, not in batch operations.

When to move to a dedicated search system

PostgreSQL full-text search is not the right tool for every situation. Move to Typesense or Meilisearch when:

• You need fuzzy matching ("did you mean?") at scale
• Your content is unstructured: PDFs, emails, free-form notes without clear field boundaries
• You need faceted search with dynamic facet counts across millions of records
• Search queries need to span all tenants simultaneously
• Query latency requirements are under 5ms at very high QPS

Typesense and Meilisearch are operationally simpler than Elasticsearch and integrate cleanly with Go. They are the right next step when PostgreSQL full-text search genuinely reaches its ceiling, not as a default first choice.

Key lessons from production

Full-text search in PostgreSQL is a practical, production-ready solution for most SaaS search needs in the 0 to 5 million record range per tenant. The setup is straightforward: tsvector columns, GIN indexes, weighted ranking with setweight(), triggers for updates, and websearch_to_tsquery for natural language input. Arabic content works with the simple configuration. The operational advantage of staying inside PostgreSQL outweighs the feature gap versus Elasticsearch until the product's search requirements genuinely exceed what tsvector handles.

For SaaS teams in Lebanon and the Gulf building their first search feature, PostgreSQL full-text search is where to start.