Chapter 14: Real-World Search Architectures

Pattern 1: E-Commerce Product Search

The use case. A product catalog with categories, brands, prices, and ratings. Users search by keyword, filter by facets, expect autocomplete in the search box, and want “similar products” on product detail pages. This is the most common search architecture in production, and the one most frequently cited as requiring Elasticsearch.

It does not.

Pillars used:

• tsvector (Ch4) — keyword search across product name and description
• pg_trgm (Ch5 + Ch9) — autocomplete in the search box, fuzzy fallback for typos
• pgvector (Ch8) — “similar products” recommendations on product detail pages
• Aggregations (Ch10) — faceted navigation: category counts, brand counts, price ranges
• Hybrid search (Ch13) — RRF combining keyword and semantic for the main search bar

The materialized view:

CREATE MATERIALIZED VIEW search_products AS
SELECT p.id, p.name, p.description, p.price, p.rating,
    c.name AS category, b.name AS brand,
    setweight(to_tsvector('english', coalesce(p.name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(p.description, '')), 'B') AS search_vector,
    p.embedding
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
WHERE p.active = true;

-- Indexes
CREATE INDEX idx_sp_fts ON search_products USING GIN(search_vector);
CREATE INDEX idx_sp_embedding ON search_products USING hnsw(embedding vector_cosine_ops);
CREATE INDEX idx_sp_trgm ON search_products USING GIN(name gin_trgm_ops);
CREATE INDEX idx_sp_category ON search_products (category);
CREATE INDEX idx_sp_brand ON search_products (brand);
CREATE INDEX idx_sp_price ON search_products (price);

I would draw your attention to the JOINs. Category and brand names are pulled from their own tables — normalized, clean, maintained in one place. Elasticsearch would require denormalizing this data into every product document. PostgreSQL aggregates across relational data because PostgreSQL is a relational database. The architecture reflects this, and I consider that an advantage.

Key queries:

• Main search: Two-signal RRF (tsvector + pgvector) from Chapter 13. The user searches “ergonomic desk chair” and gets results ranked by both keyword precision and semantic understanding.
• Autocomplete: WHERE name ILIKE 'ergo%' ORDER BY similarity(name, 'ergo') DESC LIMIT 7 from Chapter 9.
• Faceted counts: GROUP BY category + COUNT(*) from Chapter 10. Category, brand, and price range facets computed from the same search results using the multi-facet CTE pattern.
• Similar products: ORDER BY embedding <=> product_embedding LIMIT 8 from Chapter 8. Uses only pgvector — the user is browsing, not searching with keywords. A different mode of interaction, served by a different pillar.

Specific considerations:

• Refresh frequency: on product catalog changes — new products, price updates, stock changes. Most e-commerce catalogs refresh every few minutes to every few hours depending on change velocity.
• Price-range facets use a CASE expression (Chapter 10, Approach 3), not a separate table.
• Autocomplete source: product names, category names, brand names — structured, finite lists. This is Chapter 9’s ideal case.

Pattern 2: SaaS Knowledge Base

The use case. A SaaS application where multiple tenants each have their own documents — help articles, internal wikis, knowledge base entries. Each tenant’s users should only see their own organization’s content. Search needs to handle long-form documents, support semantic queries (“how do I reset my password” matching “Account Recovery Procedures”), and provide highlighted snippets showing why a result matched.

Pillars used:

• tsvector (Ch4) — keyword search with ts_headline() for snippets
• pgvector (Ch8) — semantic search for meaning-based queries
• Hybrid search (Ch13) — RRF combining keyword and semantic
• Row-level security — and this is the pillar I would most like you to consider

The materialized view:

CREATE MATERIALIZED VIEW search_documents AS
SELECT d.id, d.tenant_id, d.title, d.body, d.updated_at,
    setweight(to_tsvector('english', coalesce(d.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(d.body, '')), 'B') AS search_vector,
    d.embedding
FROM documents d
WHERE d.published = true;

CREATE INDEX idx_sd_fts ON search_documents USING GIN(search_vector);
CREATE INDEX idx_sd_embedding ON search_documents USING hnsw(embedding vector_cosine_ops);
CREATE INDEX idx_sd_tenant ON search_documents (tenant_id);

Tenant isolation:

CREATE VIEW search_documents_secure WITH (security_barrier) AS
SELECT * FROM search_documents
WHERE tenant_id = current_setting('app.tenant_id')::int;

PostgreSQL’s Row Level Security cannot be applied directly to materialized views — RLS is defined for tables. But tenant isolation on search results is straightforward with a security barrier view layered on top.

The application queries search_documents_secure instead of search_documents. The security barrier view filters rows before any user-defined functions in the query can see them — preventing information leakage through carefully crafted function calls. The materialized view holds all tenants’ data (the refresh needs full access). The security barrier view enforces isolation at query time.

I would like to be direct about why this matters, because it is the single strongest argument for PostgreSQL in multi-tenant SaaS — stronger than performance, stronger than simplicity, stronger than cost.

Every search query — lexical, semantic, hybrid — automatically respects tenant isolation. The WHERE clause is invisible to the application. No filtering logic in application code. No risk of cross-tenant data leakage from a forgotten WHERE clause. A developer writes a search query against the secure view. The database ensures they only see their tenant’s results. If the developer forgets to filter by tenant — and developers do forget, because developers are human — the database does not forget. The view enforces it.

Elasticsearch has no equivalent to either approach. No external search service does. If you are building multi-tenant SaaS and evaluating search architecture, this is the consideration I would place above all others. Data leakage between tenants is not a performance problem. It is a business-ending event. PostgreSQL prevents it at the database level. I find that worth the emphasis.

Specific considerations:

• Long documents: consider chunking into sections for embedding generation — one embedding per section, not per document. The materialized view can include a section_id column for chunk-level search with document-level result grouping.
• ts_headline() for snippets is essential. Users need to see why a document matched, not just that it matched.
• The application sets app.tenant_id at connection time. Every subsequent query against the secure view is automatically filtered. The developer writes search queries without thinking about tenancy. The database enforces it regardless.

Pattern 3: Job Board / Marketplace with Alerts

The use case. A job board — or any marketplace where new listings arrive continuously and users want to be notified when something matches their interests. Users search with keywords and filters. Users save searches and receive notifications when new listings match. New listings are classified into categories automatically.

This is the pattern with the most pillars, and the one where the percolator from Chapter 11 finds its natural home.

Pillars used:

• tsvector (Ch4) — keyword search across title and description
• pgvector (Ch8) — semantic matching (“looking for backend roles” finds “server-side engineering positions”)
• pg_trgm (Ch5) — fuzzy matching for company names and skill names
• Percolator (Ch11) — saved search alerts, automatic classification
• Hybrid search (Ch13) — RRF for the main search
• Aggregations (Ch10) — facets by location, salary range, job type

The materialized view:

CREATE MATERIALIZED VIEW search_listings AS
SELECT l.id, l.title, l.description, l.company, l.location,
    l.salary_min, l.salary_max, l.job_type, l.posted_at,
    setweight(to_tsvector('english', coalesce(l.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(l.description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(l.company, '')), 'C') AS search_vector,
    l.embedding
FROM listings l
WHERE l.active = true AND l.expires_at > now();

CREATE INDEX idx_sl_fts ON search_listings USING GIN(search_vector);
CREATE INDEX idx_sl_embedding ON search_listings USING hnsw(embedding vector_cosine_ops);
CREATE INDEX idx_sl_trgm ON search_listings USING GIN(company gin_trgm_ops);

The percolator — saved search alerts:

CREATE TABLE saved_searches (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    label TEXT,
    query tsquery NOT NULL,
    filters JSONB,  -- location, salary range, job type
    created_at TIMESTAMP DEFAULT now()
);
CREATE INDEX idx_percolate ON saved_searches USING GIN(query);

New listing inserted → trigger fires → percolates against all saved searches → checks both tsquery text match AND JSONB filter conditions → matching users notified via pg_notify. Real-time alerts without polling. The Chapter 11 pattern extended with structured filtering in the JSONB column.

I find this pattern particularly satisfying because it demonstrates the composability of the architecture. The percolator is not a separate service or a bolted-on feature. It is a table with a GIN index. The notification is a trigger. The structured filters are a JSONB column. Every piece is standard PostgreSQL, and they compose into something that most teams assume requires dedicated infrastructure.

Automatic classification: Store one tsquery per job category. New listings are classified at insert time using the same trigger — the listing arrives, and the database tells you which categories it belongs to. No external classification service. No ML pipeline for simple keyword-based categorization.

Specific considerations:

• The JSONB filters column stores non-text criteria. The trigger function checks both text match and JSONB conditions. This extends Chapter 11’s basic pattern with structured filtering.
• Expiration: the materialized view WHERE clause filters expired listings automatically on refresh.
• Three-column weights: title (A), description (B), company name (C). A match in the title is a stronger signal than a company name mention.
• Six pillars combined — the most comprehensive pattern. Not every marketplace needs all six. Start with search and add alerts when users request them.

Pattern 4: Support Ticket System

The use case. A customer support system. Agents search existing tickets for similar past issues. Incoming tickets are routed to the right team. A knowledge base is searched to suggest solutions. Agents search by customer name — and customers spell their names in more ways than you might expect.

Pillars used:

• tsvector (Ch4) — keyword search across ticket subject and body
• fuzzystrmatch (Ch6) — phonetic matching for customer names (Smith/Smyth/Smit)
• pgvector (Ch8) — semantic search for similar past tickets
• Percolator (Ch11) — ticket routing via stored queries per team
• Hybrid search (Ch13) — RRF for the main ticket search

The materialized view:

CREATE MATERIALIZED VIEW search_tickets AS
SELECT t.id, t.subject, t.body, t.status, t.priority,
    c.name AS customer_name, c.email,
    a.name AS assigned_to, t.created_at,
    setweight(to_tsvector('english', coalesce(t.subject, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(t.body, '')), 'B') AS search_vector,
    t.embedding
FROM tickets t
JOIN customers c ON t.customer_id = c.id
LEFT JOIN agents a ON t.agent_id = a.id;

CREATE INDEX idx_st_fts ON search_tickets USING GIN(search_vector);
CREATE INDEX idx_st_embedding ON search_tickets USING hnsw(embedding vector_cosine_ops);
CREATE INDEX idx_st_soundex ON customers USING btree(soundex(name));

Routing with percolator:

CREATE TABLE team_routing (
    id SERIAL PRIMARY KEY,
    team_name TEXT NOT NULL,
    query tsquery NOT NULL
);
INSERT INTO team_routing (team_name, query) VALUES
('Billing', to_tsquery('english', 'invoice | payment | charge | refund | billing')),
('Technical', to_tsquery('english', 'error | crash | bug | login | password')),
('Shipping', to_tsquery('english', 'delivery | shipping | tracking | package | return'));
CREATE INDEX idx_routing ON team_routing USING GIN(query);

New ticket arrives → trigger percolates against team_routing → highest-scoring match determines which team receives the ticket. If no match exceeds a confidence threshold, route to general triage. The routing is automatic, instant, and based on the content of the ticket — not on the customer’s selection from a dropdown they may not understand.

Specific considerations:

• Phonetic search on customer names: Soundex B-tree expression index on customers.name (Chapter 6). Agent types “Smyth,” finds tickets for “Smith.” This is a common support workflow, and phonetic matching handles it with the quiet competence Chapter 6 demonstrated.
• Similar ticket search: when an agent opens a ticket, show semantically similar past tickets with their resolutions. pgvector similarity on ticket embeddings. An agent facing a problem someone else has already solved should not have to solve it again.
• Routing confidence: if the highest percolator score is below a threshold, the system acknowledges uncertainty and routes to general triage rather than guessing. I find that honest — a system that knows when it does not know.

Choosing Your Architecture

The common core. Every pattern uses tsvector + pgvector + hybrid RRF. This is the baseline — keyword precision and semantic understanding fused via Reciprocal Rank Fusion. The pillars you add beyond that depend on your domain. The table above is a recommendation, not a prescription.

Start with the baseline. Add pillars as you need them. You do not need to implement everything on day one — and I would gently suggest that you should not try. Start with keyword search. Add semantic search when you have embeddings. Add autocomplete when users request it. Add the percolator when you need alerts. Each pillar is independent. Each can be added without rebuilding what you already have. The architecture is additive, not all-or-nothing. This is, I believe, one of its most important properties — it grows with you, at your pace, without requiring that you commit to everything before you have committed to anything.

Honest Boundary

These patterns are starting points, not finished products. Every application has domain-specific requirements the pattern does not cover — custom ranking signals, business logic in filtering, user personalization, A/B testing of search results. The blueprint gives you the foundation. The building is yours.

The materialized view refresh strategy matters and varies by use case. E-commerce catalogs change frequently; knowledge bases change rarely; job boards have constant inserts with periodic expirations. Chapter 3’s refresh strategies apply directly. Match the refresh cadence to your data’s change rate — and test the refresh duration under realistic data volumes before committing to a schedule.

Not every application needs every pillar. A simple internal tool might need only tsvector and nothing else. A consumer marketplace might need all nine capabilities. Match the architecture to the problem, not the other way around. The most sophisticated architecture is the one that uses exactly what it needs and nothing more.