Full-text search: PostgreSQL tsvector and pg_trgm versus Elasticsearch/OpenSearch

-- ======================================================================
-- Q: You need text search over assets and alerts. PostgreSQL or
-- Elasticsearch/OpenSearch?
--
-- I start with PostgreSQL full-text search (tsvector + GIN) and pg_trgm for
-- typos/LIKE '%x%'. It covers many cases without a new system to maintain. I
-- move to OpenSearch/Elasticsearch when I need facets/aggregations over millions
-- of documents, relevance tuning, many languages or log-style search. Then
-- PostgreSQL stays the source of truth and the index is updated from events
-- (outbox → indexer), with the ability to do a full reindex.
-- ======================================================================

-- Generated column + GIN index
ALTER TABLE alerts ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(description, '')), 'B')
  ) STORED;
CREATE INDEX CONCURRENTLY ix_alerts_search ON alerts USING GIN (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM alerts, websearch_to_tsquery('simple', 'ssh brute force') q
WHERE tenant_id = 42 AND search @@ q
ORDER BY rank DESC LIMIT 20;

-- Typos / fuzzy
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_assets_hostname_trgm ON assets USING GIN (hostname gin_trgm_ops);
SELECT hostname FROM assets WHERE hostname % 'webserv01' ORDER BY similarity(hostname, 'webserv01') DESC;