PostgreSQL Full-Text Search: FTS vs pgvector vs Elasticsearch

Search is a core feature of almost every content application. The question is not whether to implement it, but how to balance setup complexity, operational overhead, search quality, and cost.

PostgreSQL Native FTS

PostgreSQL has built-in full-text search using tsvector and tsquery types. It supports stemming, ranking, and phrase search—sufficient for most applications without additional infrastructure.

-- Add FTS index to posts
ALTER TABLE posts ADD COLUMN fts_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'C')
  ) STORED;

CREATE INDEX idx_posts_fts ON posts USING GIN(fts_vector);

-- Search query
SELECT title, ts_rank(fts_vector, query) AS rank
FROM posts, to_tsquery('english', 'kubernetes & deployment') query
WHERE fts_vector @@ query
ORDER BY rank DESC LIMIT 10;

pgvector: Semantic Search

Unlike keyword search, semantic search finds conceptually related content even when exact keywords don't match. A search for "scaling horizontally" should find articles about "adding more servers," "load balancing," and "distributed systems." pgvector makes this possible using embeddings.

# Generate embedding for search query
query_embedding = openai.embeddings.create(
    model="text-embedding-3-small",
    input="scaling horizontally"
).data[0].embedding

# Find semantically similar articles
sql = (
    "SELECT title, 1 - (embedding <=> %s::vector) AS similarity "
    "FROM posts WHERE status = 'Published' "
    "ORDER BY embedding <=> %s::vector LIMIT 10"
)
results = db.execute(sql, (query_embedding, query_embedding)).fetchall()

Hybrid Search: Best of Both

Combine keyword search (precision for specific terms) with semantic search (recall for concepts) using Reciprocal Rank Fusion. Weight keyword matches higher for technical queries; weight semantic similarity higher for natural language questions.

When to Choose Elasticsearch

Elasticsearch is warranted when: you need complex faceted search (filter by multiple fields simultaneously), very large document volumes (>50M), or multi-language search. Its operational overhead (dedicated cluster, index management) isn't worth it for most applications that can serve search from PostgreSQL.

Production Database Connection Pool Manager

Here is an optimized database transaction manager in Python utilizing SQLAlchemy 2.0 with explicit connection pooling limits, connection recycling, and automated deadlock retries:

import logging
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.exc import DBAPIError

logger = logging.getLogger("MirahLabs.DatabasePool")
DATABASE_URL = "postgresql://user:pass@localhost:5432/db"

engine = create_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    pool_recycle=1800,
    pool_timeout=30,
    pool_pre_ping=True
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def run_transaction_with_retry(session_action_func, max_retries=3):
    for attempt in range(max_retries):
        db = SessionLocal()
        try:
            result = session_action_func(db)
            db.commit()
            return result
        except DBAPIError as e:
            db.rollback()
            if attempt == max_retries - 1: raise e
            logger.warning(f"Deadlock detected. Retrying attempt {attempt+2}...")
        finally:
            db.close()

Runtime & Concurrency Metrics Profile

Below is a runtime latency and throughput benchmark compiled in a containerized environment (2 vCPU, 4GB RAM) running under simulated concurrent request volumes:

US & UK Compliance and Data Governance

Modern applications operating across US and UK regions must establish comprehensive data governance frameworks. This includes meeting the security baselines of the US NIST Cybersecurity Framework and the UK Cyber Essentials certification. Enforcing encryption at rest and in transit, keeping audit logs, and maintaining a clear incident response plan are essential to comply with both CCPA and UK GDPR regulations.