Here’s the problem: sometimes users search with exact keywords like “PostgreSQL error 23505”. Other times they search with the meaning: “why is my database slow”. Most of the time, it’s somewhere in between.

Documents are the same way. Some are full of specific terms and jargon. Others are conversational and conceptual. Most are a mix of both.

So you have queries that could be keywords or meaning, hitting documents that could be keywords or meaning. That’s four combinations:

No single approach covers all four quadrants. You need both keyword search AND vector search. And you need a way to combine them intelligently.

That’s exactly what Elasticsearch does. It uses BM25 for keyword ranking, vector embeddings for semantic search, and RRF (Reciprocal Rank Fusion) to merge the results into a single ranked list. This combination is called hybrid search, and it’s why Elasticsearch actually works.

But here’s the trade-off: to use Elasticsearch, you need to build a pipeline. Your data lives in Postgres, but search lives in Elasticsearch. So you’re stuck with:

Postgres → Kafka/Debezium → Elasticsearch

That’s three systems to manage. Three things that can break. Sync jobs to maintain. Stale data to debug. And with AI agents now needing to search through docs and codebases on the fly, the pipeline problem is getting worse. You can’t easily spin up a test environment when your search lives in a completely different system.

Still, teams pay for it. Over a billion dollars a year, collectively. Because search that works is worth the pain.

Here’s the good news: all three pieces of Elasticsearch’s hybrid search are now available in Postgres: 

• BM25 via pg_textsearch (open source, PostgreSQL license) 
• Vector search via pgvectorscale (high-performance DiskANN) 
• RRF? That’s just SQL. No extension needed.

And pgai eliminates the embedding pipeline entirely (no more Postgres → Kafka → Elasticsearch sync jobs). It automatically syncs changes to the data and updates the corresponding embeddings appropriately.

We’ve already covered how BM25 works. This blog focuses on Hybrid Search, RRF, pgai, how they all work together, why it’s elegant, and how to implement hybrid search entirely in Postgres.

How RRF (Reciprocal Rank Fusion) Works

RRF (Reciprocal Rank Fusion) is elegantly simple. It’s the industry standard for combining ranked lists, and it’s what Elasticsearch uses for hybrid search.

The Problem

You run two searches and get two ranked lists:

BM25 Results (keyword): 1. Doc A (score: 15.2) 2. Doc B (score: 12.1) 3. Doc C (score: 8.4)

Vector Results (semantic): 1. Doc C (distance: 0.12) 2. Doc D (distance: 0.18) 3. Doc A (distance: 0.25)

How do you combine them? You can’t just add the scores. They’re on completely different scales. BM25 scores might be 0-50. Vector distances are 0-2.

The RRF Solution

RRF ignores the actual scores. It only cares about rank position:

RRF_score = Σ (1 / (k + rank))

Where:

• k is a constant (typically 60)
• rank is the position (1st, 2nd, 3rd…)

That’s it. Dead simple.

Worked Example

Let’s calculate RRF scores for our documents:

Result: Doc A and Doc C tie for first place. Why? Because they appeared in both lists. RRF naturally boosts documents that multiple systems agree on.

Why RRF Works So Well

1. Scale-independent. Doesn’t matter if one score is 0-50 and another is 0-2. RRF only looks at order.
2. Rewards consensus. If both keyword AND semantic search agree a doc is relevant, it gets boosted.
3. Preserves outliers. A doc that only appears in one list still gets scored. Nothing is thrown away.
4. The k=60 trick. This constant prevents the #1 result from dominating everything. It smooths the curve.

Hybrid Search in Postgres

Here’s how to implement hybrid search with RRF using pg_textsearch and pgvectorscale.

Setup

-- Enable extensions
CREATE EXTENSION pg_textsearch;       -- Adds BM25 ranking for keyword search
CREATE EXTENSION vectorscale CASCADE; -- Adds DiskANN for fast vector search (includes pgvector)
CREATE EXTENSION ai;                  -- Adds auto-embedding generation (optional but recommended)

-- Create your table
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,                       -- This column gets BM25 indexed
  embedding vector(1536)              -- This column stores OpenAI embeddings (1536 dimensions)
);

-- Create indexes
CREATE INDEX idx_bm25 ON documents 
  USING bm25(content)                 -- BM25 index on content column
  WITH (text_config = 'english');     -- Use English stemming/stopwords

CREATE INDEX idx_vector ON documents 
  USING diskann(embedding);           -- DiskANN index for fast approximate nearest neighbor

The Hybrid Search Query

WITH 
-- STEP 1: Get top 20 keyword matches using BM25
bm25_results AS (
  SELECT 
    id, 
    ROW_NUMBER() OVER (
      ORDER BY content <@> to_bm25query('database optimization', 'idx_bm25')
    ) as rank                         -- Assign rank 1, 2, 3... based on BM25 score
  FROM documents
  ORDER BY content <@> to_bm25query('database optimization', 'idx_bm25')
  LIMIT 20                            -- Only keep top 20 keyword matches
),

-- STEP 2: Get top 20 semantic matches using vector similarity
vector_results AS (
  SELECT 
    id, 
    ROW_NUMBER() OVER (
      ORDER BY embedding <=> $1       -- $1 is the query embedding (passed from app)
    ) as rank                         -- Assign rank 1, 2, 3... based on vector distance
  FROM documents
  ORDER BY embedding <=> $1           -- <=> is cosine distance operator
  LIMIT 20                            -- Only keep top 20 semantic matches
)

-- STEP 3: Combine both lists using RRF formula
SELECT 
  d.id,
  d.title,
  d.content,
  -- RRF: 1/(k+rank) for each list, summed together
  -- k=60 prevents top results from dominating
  COALESCE(1.0 / (60 + b.rank), 0) +  -- Score from BM25 (0 if not in BM25 results)
  COALESCE(1.0 / (60 + v.rank), 0)    -- Score from vectors (0 if not in vector results)
  as rrf_score
FROM documents d
LEFT JOIN bm25_results b ON d.id = b.id   -- Join BM25 ranks
LEFT JOIN vector_results v ON d.id = v.id -- Join vector ranks
WHERE b.id IS NOT NULL OR v.id IS NOT NULL -- Must appear in at least one list
ORDER BY rrf_score DESC               -- Highest RRF score = most relevant
LIMIT 10;                             -- Return top 10 results

Just one query, two search types, but RRF smooths everything over. This makes your life super simple. What's better? Wrap it into a function called hybrid_search and then you can now call that function.

Wrap It in a Function

-- Reusable function: call hybrid_search('your query', $embedding, 10)
CREATE OR REPLACE FUNCTION hybrid_search(
  query_text TEXT,                    -- The search query (for BM25)
  query_embedding vector(1536),       -- The query embedding (for vectors)
  match_count INT DEFAULT 10          -- How many results to return
)
RETURNS TABLE (id INT, title TEXT, content TEXT, rrf_score FLOAT)
AS $$
  WITH 
  -- BM25 keyword search
  bm25_results AS (
    SELECT id, ROW_NUMBER() OVER (
      ORDER BY content <@> to_bm25query(query_text, 'idx_bm25')
    ) as rank
    FROM documents
    ORDER BY content <@> to_bm25query(query_text, 'idx_bm25')
    LIMIT 20
  ),
  -- Vector semantic search  
  vector_results AS (
    SELECT id, ROW_NUMBER() OVER (
      ORDER BY embedding <=> query_embedding
    ) as rank
    FROM documents
    ORDER BY embedding <=> query_embedding
    LIMIT 20
  )
  -- Combine with RRF
  SELECT 
    d.id, d.title, d.content,
    COALESCE(1.0 / (60 + b.rank), 0) + 
    COALESCE(1.0 / (60 + v.rank), 0) as rrf_score
  FROM documents d
  LEFT JOIN bm25_results b ON d.id = b.id
  LEFT JOIN vector_results v ON d.id = v.id
  WHERE b.id IS NOT NULL OR v.id IS NOT NULL
  ORDER BY rrf_score DESC
  LIMIT match_count;
$$ LANGUAGE SQL;

Now your app code is just:

SELECT * FROM hybrid_search('database optimization', $embedding, 10);

Weighted Hybrid Search

Sometimes you want to favor one search type over the other. Technical docs might benefit from stronger keyword matching. Conversational queries might need more semantic weight. So you can expose all the weights in your function, and then you can call this weighted function.

-- Weighted version: control how much keyword vs semantic matters
CREATE OR REPLACE FUNCTION weighted_hybrid_search(
  query_text TEXT,                    -- The search query (for BM25)
  query_embedding vector(1536),       -- The query embedding (for vectors)
  bm25_weight FLOAT DEFAULT 0.5,      -- Weight for keyword search (0.0 to 1.0)
  vector_weight FLOAT DEFAULT 0.5,    -- Weight for semantic search (0.0 to 1.0)
  match_count INT DEFAULT 10          -- How many results to return
)
RETURNS TABLE (id INT, title TEXT, content TEXT, rrf_score FLOAT)
AS $$
  WITH 
  -- BM25 keyword search
  bm25_results AS (
    SELECT id, ROW_NUMBER() OVER (
      ORDER BY content <@> to_bm25query(query_text, 'idx_bm25')
    ) as rank
    FROM documents
    ORDER BY content <@> to_bm25query(query_text, 'idx_bm25')
    LIMIT 20
  ),
  -- Vector semantic search
  vector_results AS (
    SELECT id, ROW_NUMBER() OVER (
      ORDER BY embedding <=> query_embedding
    ) as rank
    FROM documents
    ORDER BY embedding <=> query_embedding
    LIMIT 20
  )
  SELECT 
    d.id, d.title, d.content,
    -- Weighted RRF: multiply each score by its weight
    (bm25_weight * COALESCE(1.0 / (60 + b.rank), 0)) +   -- Weighted BM25 score
    (vector_weight * COALESCE(1.0 / (60 + v.rank), 0))   -- Weighted vector score
    as rrf_score
  FROM documents d
  LEFT JOIN bm25_results b ON d.id = b.id
  LEFT JOIN vector_results v ON d.id = v.id
  WHERE b.id IS NOT NULL OR v.id IS NOT NULL
  ORDER BY rrf_score DESC
  LIMIT match_count;
$$ LANGUAGE SQL;

Usage:

-- Equal weight (default): 50% keywords, 50% semantic
SELECT * FROM weighted_hybrid_search('database optimization', $embedding);

-- Favor keywords (70% BM25, 30% vectors)
-- Good for: error codes, specific terms, exact phrases
SELECT * FROM weighted_hybrid_search('error 23505', $embedding, 0.7, 0.3);

-- Favor semantic (30% BM25, 70% vectors)
-- Good for: natural language questions, conceptual queries
SELECT * FROM weighted_hybrid_search('how do I make queries faster', $embedding, 0.3, 0.7);

Auto-Sync Embeddings (No Pipelines)

Remember the pipeline problem from the intro? Postgres → Kafka → Elasticsearch, with separate jobs to generate embeddings?

pgai eliminates pretty much all of that.

When you create a vectorizer, pgai sets up background workers that monitor your table for changes. When a row is inserted or updated, pgai automatically: 

1. Detects the change 

2. Calls the embedding API (OpenAI, Cohere, or local models) 

3. Stores the embedding in a linked table 4. Keeps everything in sync

-- Set up automatic embedding generation
SELECT ai.create_vectorizer(
  'documents'::regclass,              -- Which table to watch for changes
  loading => ai.loading_column(
    column_name => 'content'          -- Which column to generate embeddings from
  ),  
  embedding => ai.embedding_openai(
    model => 'text-embedding-3-small', -- OpenAI model to use
    dimensions => '1536'               -- Output embedding dimensions
  )
);
-- That's it! pgai now watches 'documents' and auto-generates embeddings
-- whenever content changes. No cron jobs. No sync scripts.

That’s it. Now any change to the documents table triggers automatic embedding updates: 

- INSERT into documents → embedding generated for content column 

- UPDATE the content column → embedding regenerated

- DELETE from documents → embedding removed

Embeddings are stored in a linked table (documents_embedding) that pgai creates and manages for you.

Note: On Tiger Data, the vectorizer worker runs automatically as a managed service. If you’re self-hosting, you’ll need to run the pgai vectorizer worker (a Python CLI) to process the embedding queue.

No Kafka. No Debezium. No sync jobs. No “why is my search stale?” debugging sessions at 3 AM.

Your embeddings live next to your data, updated in near real-time, managed by Postgres itself. This is what makes hybrid search in Postgres practical for production.

Get Started

Everything you need is available on Tiger Data:

-- 1. Enable extensions (one-time setup)
CREATE EXTENSION pg_textsearch;        -- BM25 keyword search
CREATE EXTENSION vectorscale CASCADE;  -- Vector search (includes pgvector)

-- 2. Create indexes on your table
CREATE INDEX idx_bm25 ON documents USING bm25(content)
  WITH (text_config = 'english');      -- BM25 index with English stemming
CREATE INDEX idx_vector ON documents 
  USING diskann(embedding);            -- Fast vector similarity index

-- 3. Search! (using the hybrid_search function from this post)
SELECT * FROM hybrid_search('your query', $embedding, 10);

The Bottom Line

Elasticsearch built a billion-dollar business on BM25 and RRF. Those algorithms aren’t proprietary. They’re not even complicated. And now they run natively in Postgres.

No pipelines. No sync jobs. No extra infrastructure. Just your database doing what databases should do: storing your data and making it searchable.

The question isn’t whether Postgres can do hybrid search. It can. The question is: why are you still running two systems when one will do?

Learn More

Our Posts: 

• You Don’t Need Elasticsearch: BM25 is Now in Postgres: Why BM25 beats native Postgres search
• From ts_rank to BM25. Introducing pg_textsearch

Extensions:

• pg_textsearch: BM25 for Postgres, open source (PostgreSQL license) 
• pgvectorscale: High-performance vector search using DiskANN 
• pgvector: The foundation for vector similarity in Postgres 
• pgai: Auto-sync embeddings, RAG workflows

Background: 

• BM25 on Wikipedia: The original algorithm (1994) 
• Reciprocal Rank Fusion paper: The academic paper behind RRF 
• Elasticsearch Hybrid Search docs: How Elasticsearch implements RRF (for comparison)

Get Started:

• Tiger Data Console: Managed Postgres with all extensions pre-installed 
• pg_textsearch Documentation

That's what Postgres is. One home with many rooms. Search, vectors, time-series, queues—all under one roof.

But this is exactly what specialized database vendors don't want you to hear. Their marketing teams have spent years convincing you to "use the right tool for the right job." It sounds reasonable. It sounds wise. And it sells a lot of databases.

Let me show you why it's a trap and why Postgres is the better choice in 99% of cases.

The “Use the Right Tool” Trap

You’ve heard the advice: “Use the right tool for the right job.”

Sounds wise. So you end up with: 

1. Elasticsearch for search
2. Pinecone for vectors
3. Redis for caching
4. MongoDB for documents
5. Kafka for queues
6. InfluxDB for time-series
7. PostgreSQL for… the stuff that’s left

Congratulations. You now have seven databases to manage. Seven query languages to learn. Seven backup strategies to maintain. Seven security models to audit. Six sets of credentials to rotate. Seven monitoring dashboards to watch. And seven things that can break at 3 AM.

And when something does break? Good luck spinning up a test environment to debug it.

Here’s a different idea: Just use Postgres.

Why This Matters Now: The AI Era

This isn’t just about simplicity. AI agents have made database sprawl a nightmare.

Think about what agents need to do: 

• Quickly spin up a test database with production data 
• Try a fix or experiment 
• Verify it works 
• Tear it down

With one database? That’s a single command. Fork it, test it, done.

With seven databases? Now you need to: 

• Coordinate snapshots across Postgres, Elasticsearch, Pinecone, Redis, MongoDB, and Kafka
• Make sure they’re all at the same point in time 
• Spin up seven different services 
• Configure seven different connection strings 
• Hope nothing drifts while you’re testing 
• Tear down seven services when you’re done

This is virtually impossible without a ton of R&D.

And it’s not just agents. Every time something breaks at 3 AM, you need to spin up a test environment to debug. With six databases, that’s a coordination nightmare. With one database, it’s a single command.

In the AI era, simplicity isn’t just elegant. It’s essential.

“But Specialized Databases Are Better!”

Let’s address this head-on.

The myth: Specialized databases are far superior at their specific tasks.

The reality: Sometimes they’re marginally better at a narrow task. But they also bring unnecessary complexity. It’s like hiring a private chef for every meal. Sounds luxurious, but it adds expense, coordination overhead, and creates problems you didn’t have before.

Here's the thing: 99% of companies don't need them. The top 1% have tens of millions of users and a large engineering team to match. You've read their blog posts about how amazing Specialized Database X works for them. But that's their scale, their team, their problems. For everyone else, Postgres is more than enough.

Here’s what most people don’t realize: Postgres extensions use the same or better algorithms as specialized databases (in many cases). 

The “specialized database” premium? Mostly marketing.

These aren’t watered-down versions. They’re the same/better algorithms, battle-tested, open source, and often developed by the same researchers.

The benchmarks back this up: 

• pgvectorscale: 28x lower latency than Pinecone at 75% less cost
• TimescaleDB: Matches or beats InfluxDB while offering full SQL 
• pg_textsearch: The exact same BM25 ranking that powers Elasticsearch

The Hidden Costs Add Up

Beyond the AI/agent problem, database sprawl has compounding costs:

Cognitive load: Your team needs SQL, Redis commands, Elasticsearch Query DSL, MongoDB aggregation, Kafka patterns, and InfluxDB’s non-native SQL workaround. That’s not specialization. That’s fragmentation.

Data consistency: Keeping Elasticsearch in sync with Postgres? You build sync jobs. They fail. Data drifts. You add reconciliation. That fails too. Now you’re maintaining infrastructure instead of building features.

SLA math: Three systems at 99.9% uptime each = 99.7% combined. That’s 26 hours of downtime per year instead of 8.7. Every system multiplies your failure modes.

The Modern Postgres Stack

These extensions aren’t new. They’ve been production-ready for years:

• PostGIS: Since 2001 (24 years). Powers OpenStreetMap and Uber.
• Full-text search: Since 2008 (17 years). Built into core Postgres.
• JSONB: Since 2014 (11 years). As fast as MongoDB, with ACID.
• TimescaleDB: Since 2017 (8 years). 21K+ GitHub stars.
• pgvector: Since 2021 (4 years). 19K+ GitHub stars.

Over 48,000 companies use PostgreSQL, including Netflix, Spotify, Uber, Reddit, Instagram, and Discord.

The AI-Era Extensions

The AI era brought a new generation:

What this means: Building a RAG app used to require Postgres + Pinecone + Elasticsearch + glue code.

Now? Just Postgres. One database. One query language. One backup. One fork command for your AI agent to spin up a test environment.

Quick Start: Add These Extensions

Here’s all you need:

-- Full-text search with BM25
CREATE EXTENSION pg_textsearch;

-- Vector search for AI
CREATE EXTENSION vector;
CREATE EXTENSION vectorscale;

-- AI embeddings & RAG workflows
CREATE EXTENSION ai;

-- Time-series
CREATE EXTENSION timescaledb;

-- Message queues
CREATE EXTENSION pgmq;

-- Scheduled jobs
CREATE EXTENSION pg_cron;

-- Geospatial
CREATE EXTENSION postgis;

That’s it.

Show Me the Code

Below are working examples for each use case. Skip to what you need.

Full-Text Search (Replace Elasticsearch)

The extension: pg_textsearch (true BM25 ranking)

What you’re replacing: 

• Elasticsearch: Separate JVM cluster, complex mappings, sync pipelines, Java heap tuning 
• Solr: Same story, different wrapper 
• Algolia: $1/1000 searches, external API dependency

What you get: The exact same BM25 algorithm that powers Elasticsearch, directly in Postgres.

-- Create table
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);

-- Create BM25 index
CREATE INDEX idx_articles_bm25 ON articles USING bm25(content)
  WITH (text_config = 'english');

-- Search with BM25 scoring
SELECT title, -(content <@> 'database optimization') as score
FROM articles
ORDER BY content <@> 'database optimization'
LIMIT 10;

Hybrid Search: BM25 + Vectors in one query:

SELECT 
  title,
  -(content <@> 'database optimization') as bm25_score,
  embedding <=> query_embedding as vector_distance,
  0.7 * (-(content <@> 'database optimization')) + 
  0.3 * (1 - (embedding <=> query_embedding)) as hybrid_score
FROM articles
ORDER BY hybrid_score DESC
LIMIT 10;

This is what Elasticsearch requires a separate plugin for. In Postgres, it’s just SQL.

Vector Search (Replace Pinecone)

The extensions: pgvector + pgvectorscale

What you’re replacing: 

• Pinecone: $70/month minimum, separate infrastructure, data sync headaches 
• Qdrant, Milvus, Weaviate: More infrastructure to manage

What you get: pgvectorscale uses the DiskANN algorithm (from Microsoft Research), achieving 28x lower p95 latency and 16x higher throughput than Pinecone at 99% recall.

-- Enable extensions
CREATE EXTENSION vector;
CREATE EXTENSION vectorscale CASCADE;

-- Table with embeddings
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

-- High-performance index (DiskANN)
CREATE INDEX idx_docs_embedding ON documents USING diskann(embedding);

-- Find similar documents
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector as distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Auto-sync embeddings with pgai:

SELECT ai.create_vectorizer(
  'documents'::regclass,
  loading => ai.loading_column(column_name=>'content'),
  embedding => ai.embedding_openai(model=>'text-embedding-3-small', dimensions=>'1536')
);

Now every INSERT/UPDATE automatically regenerates embeddings. No sync jobs. No drift. No 3 AM pages.

Time-Series (Replace InfluxDB)

The extension: TimescaleDB (21K+ GitHub stars)

What you’re replacing: 

• InfluxDB: Separate database, Flux query language or non-native SQL, limited SQL support 
• Prometheus: Great for metrics, not your application data

What you get: Automatic time partitioning, compression up to 90%, continuous aggregates. Full SQL.

-- Enable TimescaleDB
CREATE EXTENSION timescaledb;

-- Create table
CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  temperature DOUBLE PRECISION
);

-- Convert to hypertable
SELECT create_hypertable('metrics', 'time');

-- Query with time buckets
SELECT time_bucket('1 hour', time) as hour,
       AVG(temperature)
FROM metrics
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- Auto-delete old data
SELECT add_retention_policy('metrics', INTERVAL '30 days');

-- Compression (90% storage reduction)
ALTER TABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

Caching (Replace Redis)

The feature: UNLOGGED tables + JSONB

-- UNLOGGED = no WAL overhead, faster writes
CREATE UNLOGGED TABLE cache (
  key TEXT PRIMARY KEY,
  value JSONB,
  expires_at TIMESTAMPTZ
);

-- Set with expiration
INSERT INTO cache (key, value, expires_at)
VALUES ('user:123', '{"name": "Alice"}', NOW() + INTERVAL '1 hour')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

-- Get
SELECT value FROM cache WHERE key = 'user:123' AND expires_at > NOW();

-- Cleanup (schedule with pg_cron)
DELETE FROM cache WHERE expires_at < NOW();

Message Queues (Replace Kafka)

The extension: pgmq

CREATE EXTENSION pgmq;
SELECT pgmq.create('my_queue');

-- Send
SELECT pgmq.send('my_queue', '{"event": "signup", "user_id": 123}');

-- Receive (with visibility timeout)
SELECT * FROM pgmq.read('my_queue', 30, 5);

-- Delete after processing
SELECT pgmq.delete('my_queue', msg_id);

Or native SKIP LOCKED pattern:

CREATE TABLE jobs (
  id SERIAL PRIMARY KEY,
  payload JSONB,
  status TEXT DEFAULT 'pending'
);

-- Worker claims job atomically
UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  FOR UPDATE SKIP LOCKED LIMIT 1
) RETURNING *;

Documents (Replace MongoDB)

The feature: Native JSONB

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- Insert nested document
INSERT INTO users (data) VALUES ('{
  "name": "Alice",
  "profile": {"bio": "Developer", "links": ["github.com/alice"]}
}');

-- Query nested fields
SELECT data->>'name', data->'profile'->>'bio'
FROM users
WHERE data->'profile'->>'bio' LIKE '%Developer%';

-- Index JSON fields
CREATE INDEX idx_users_email ON users ((data->>'email'));

Geospatial (Replace Specialized GIS)

The extension: PostGIS

CREATE EXTENSION postgis;

CREATE TABLE stores (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location GEOGRAPHY(POINT, 4326)
);

-- Find stores within 5km
SELECT name, ST_Distance(location, ST_MakePoint(-122.4, 37.78)::geography) as meters
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-122.4, 37.78)::geography, 5000);

Scheduled Jobs (Replace Cron)

The extension: pg_cron

CREATE EXTENSION pg_cron;

-- Run every hour
SELECT cron.schedule('cleanup', '0 * * * *', 
  $$DELETE FROM cache WHERE expires_at < NOW()$$);

-- Nightly rollup
SELECT cron.schedule('rollup', '0 2 * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats$$);

Hybrid Search (BM25 + Vectors)

For AI applications, you often need both keyword search and semantic search:

-- Reciprocal Rank Fusion: combine keyword + semantic search
WITH bm25 AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY content <@> $1) as rank
  FROM documents LIMIT 20
),
vectors AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) as rank  
  FROM documents LIMIT 20
)
SELECT d.*, 
  1.0/(60 + COALESCE(b.rank, 1000)) + 
  1.0/(60 + COALESCE(v.rank, 1000)) as score
FROM documents d
LEFT JOIN bm25 b ON d.id = b.id
LEFT JOIN vectors v ON d.id = v.id
WHERE b.id IS NOT NULL OR v.id IS NOT NULL
ORDER BY score DESC LIMIT 10;

Try that with Elasticsearch + Pinecone. You’d need two API calls, result merging, failure handling, and double latency.

In Postgres: one query, one transaction, one result.

Fuzzy Search (Typo Tolerance)

The extension: pg_trgm (built into Postgres)

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Finds "PostgreSQL" even with typo
SELECT name FROM products
WHERE name % 'posgresql'
ORDER BY similarity(name, 'posgresql') DESC;

Graph Traversal (Replace Graph DBs)

The feature: Recursive CTEs

-- Find all reports under a manager (org chart)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 as depth
  FROM employees WHERE id = 42
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM org_tree;

The Bottom Line

Remember the home analogy? You don't build a separate restaurant just to cook dinner. You don't construct a commercial garage across town just to park your car. You use the rooms in your home.

That's what we've shown you here. Search, vectors, time-series, documents, queues, caching—they're all rooms in the Postgres home. Same algorithms as the specialized databases. Battle-tested for years. Used by Netflix, Uber, Discord, and 48,000 other companies.

So what about that 99%?

For 99% of companies, Postgres handles everything you need. The 1%? That's when you're processing petabytes of logs across hundreds of nodes, or you need Kibana's specific dashboards, or you have exotic requirements that genuinely exceed what Postgres can do.

But here's the thing: you'll know when you're in the 1%. You won't need a vendor's marketing team to tell you. You'll have benchmarked it yourself and hit a real wall.

Until then, don't scatter your data across seven buildings because someone told you to "use the right tool for the right job." That advice sells databases. It doesn't serve you.

Start with Postgres. Stay with Postgres. Add complexity only when you've earned the need for it.

In 2026, just use Postgres.

Get Started

All these extensions are available on Tiger Data. Create a free database in minutes:

psql "postgresql://user:pass@your-instance.tsdb.cloud.timescale.com:5432/tsdb"

CREATE EXTENSION pg_textsearch;  -- BM25 search
CREATE EXTENSION vector;         -- Vector search 

No need for specialized databases, just use Postgres.

Learn More

• pg_textsearch Documentation
• pgvector GitHub
• pgvectorscale Documentation
• TimescaleDB Documentation
• pgmq for Message Queues
• PostGIS for Geospatial
• Learn why vanilla Postgres hits performance ceilings at scale
• Learn how MarketReader Processes 3M Trades/Min to Deliver US Market Trading Insights with TimescaleDB
• Learn how Plexigrid moved from 4 databases to 1 with Tiger Data

After years of teams running Elasticsearch in production, certain patterns keep emerging. The same issues show up in blog posts, Stack Overflow questions, and incident reports. We've compiled ten of the most common ones below, with references to the engineers who've documented them. We’ve also added images to make it easy to quickly skim through it and compare the challenges against Postgres. 

TLDR: With great power comes great operational complexity.

1. JVM Garbage Collection Pauses

Elasticsearch runs on the Java Virtual Machine (JVM). This means garbage collection (GC) is part of your life.

The problem: Java periodically pauses everything to clean up unused memory. These "stop-the-world" pauses can freeze your Elasticsearch node for seconds at a time. If the pause lasts longer than 30 seconds, the cluster assumes the node is dead and starts moving data around to compensate. Now you have a cascading failure.

Say for example, your e-commerce site is running on Black Friday. Traffic spikes, memory fills up, and Java decides it's time to clean house. Search goes unresponsive for 45 seconds. The cluster panics, starts redistributing shards, and suddenly you're dealing with a full outage instead of a brief slowdown.

References:

• Sirius Open Source: Problems and Operational Weaknesses of Elasticsearch — "GC pause time correlates with heap size. Larger heaps mean longer pauses."
• Elastic Docs: Fix Common Cluster Issues — Official troubleshooting guide for JVM memory pressure

Why Postgres avoids this: Postgres is written in C and manages memory directly. There's no JVM, no garbage collection, and no "stop-the-world" pauses. Memory usage is predictable and grows linearly with your workload. You still need to tune shared_buffers and work_mem, but you're not fighting a runtime that can freeze your entire process.

2. Mapping Explosion

Elasticsearch tries to be helpful by automatically detecting the type of data you're storing. Send it a field called user_id with a number, and it remembers "user_id is a number." Convenient, right?

The problem: This "helpful" feature becomes a nightmare with semi-structured data. If your application logs include arbitrary metadata keys, or your users can create custom fields, Elasticsearch creates a new mapping for each one. Thousands of fields later, your cluster state is massive, your master node is struggling, and everything slows to a crawl.

Say you're logging API requests and including the request body as JSON. One customer sends a payload with 500 unique keys. Elasticsearch dutifully creates 500 new field mappings. Multiply that by thousands of requests, and suddenly you have tens of thousands of fields. Your cluster becomes unresponsive, and you're scrambling to figure out why.

References:

• Sirius Open Source — "Mapping explosion is a common anti-pattern... the cluster state can grow to hundreds of megabytes."
• MindfulChase: Advanced Troubleshooting for Elasticsearch — Mapping conflicts and silent failures

Why Postgres avoids this: Postgres requires you to define your schema upfront. You decide what columns exist. If you need flexible data, you use JSONB—it stores arbitrary JSON without creating new columns or bloating system catalogs. You can still index specific paths inside the JSON when needed. The schema is explicit, which means surprises are rare.

3. Oversharding (or Undersharding)

Elasticsearch splits your data into "shards" to distribute it across machines. You have to decide how many shards to use when you create an index. Choose wisely, because you can't change it later without rebuilding everything.

The problem: Too many shards? Each one consumes memory, CPU, and file handles. You're wasting resources on overhead. Too few shards? You can't parallelize queries effectively, and recovery takes forever when a node dies. And here's the kicker: there's no formula. It depends on your data size, query patterns, hardware, and growth rate. It's guesswork.

Say you create an index with 5 shards because that's the default. Six months later, your data has grown 10x, and those 5 shards are now bottlenecks. Your only option? Create a new index with more shards and reindex all your data. Hope you have the disk space and time for that migration.

References:

• Pure Insights: Top 7 Elasticsearch Pitfalls — "Improper shard allocation is one of the most common causes of performance degradation."
• Sirius Open Source — "Each shard is a Lucene index with its own file handles, memory, and CPU overhead."

Why Postgres avoids this: Postgres doesn't shard by default—your data lives in tables on a single node. When you need to scale reads, you add replicas. When tables get large, you use declarative partitioning (by time, by tenant, etc.). These decisions can be made later, as your data grows, rather than upfront when you're guessing. And if you do need horizontal sharding someday, tools like Citus exist. But most applications never get there.

4. Deep Pagination Performance Cliff

Users click "next page" a lot. They expect page 500 to load as fast as page 1. Elasticsearch has other plans.

The problem: When you ask for page 500 (results 5000-5010), Elasticsearch doesn't skip to result 5000. It fetches and ranks all 5010 results, then throws away the first 5000. Every node in your cluster does this work, then sends results to a coordinator that combines them. The deeper you paginate, the more work everyone does.

Say a user searches your product catalog and starts browsing. Page 1 loads in 50ms. Page 10 takes 200ms. By page 100, it's taking 2 seconds. Page 500? Timeout. Elasticsearch actually has a hard limit (index.max_result_window, default 10,000) specifically because this gets so bad.

References:

• Sirius Open Source — "Deep pagination is a well-known limitation... the coordinating node must aggregate results from all shards."
• Elastic Docs — Official guidance recommending search_after for deep pagination

Why Postgres avoids this: LIMIT 10 OFFSET 5000 in Postgres doesn't have a hard ceiling like ES. The query planner handles it without needing to score every document. That said, large offsets still have overhead—keyset pagination (WHERE id > last_seen_id) is better for deep pages. But you won't hit a wall at 10,000 results, and you won't see the dramatic slowdown ES has.

5. Split-Brain and Data Loss

Elasticsearch is a distributed system. Distributed systems fail in distributed ways.

The problem: Imagine your cluster has 5 nodes, and a network issue splits them into two groups: 3 nodes on one side, 2 on the other. Both groups might elect their own master and start accepting writes independently. When the network heals, you have two conflicting versions of your data. This is "split-brain," and it can cause permanent data loss.

Say your Elasticsearch cluster spans two data centers for redundancy. A network blip between them lasts 60 seconds. Both sides elect a master. Users on both sides keep searching and indexing. When connectivity returns, some documents exist in one half, some in the other, and some have conflicting updates. Reconciling this manually is a nightmare.

References:

• Sirius Open Source — "Split-brain scenarios can occur when network partitions isolate nodes."
• Wikipedia: Elasticsearch — History of split-brain issues and improvements

Why Postgres avoids this: Postgres uses a simpler primary/replica model. There's one primary that accepts writes; replicas are read-only. This doesn't make split-brain impossible—any distributed system can have it—but the architecture makes it much harder to create accidentally. Tools like Patroni, pg_auto_failover, and managed services (like Tiger Data) handle leader election and fencing automatically. You're not managing quorum math yourself.

6. Eventual Consistency Surprises

When you save something to Elasticsearch, it's not immediately searchable. This catches a lot of teams off guard.

The problem: Elasticsearch buffers writes and periodically "refreshes" to make them searchable (default: every 1 second). But under load, or with specific configurations, that delay can grow. Users create a record, immediately search for it, and get nothing. "But I just saved it!"

Say a user posts a comment on your platform. Your app saves it to Postgres (source of truth) and indexes it to Elasticsearch. The user refreshes the page to see their comment. The Postgres query shows it, but the search results don't include it yet because Elasticsearch hasn't been refreshed. The user thinks something is broken and posts again. Now you have duplicate comments.

References:

• AceTheCloud: Why You Rarely Need Elastic — "Elasticsearch does not support ACID transactions... data may not be immediately visible."
• StackShare: Elasticsearch vs PostgreSQL — Comparison of consistency models

Why Postgres avoids this: Postgres is ACID-compliant. When your transaction commits, the data is immediately visible to all subsequent queries—on the same connection or any other. If you're using BM25 search via pg_textsearch, the index updates synchronously—no refresh interval. No eventual consistency, no "I just saved it but can't find it" bugs.

7. Security Misconfigurations

Elasticsearch has been responsible for some of the largest data breaches in history. Not because it's insecure by design, but because it's easy to misconfigure.

The problem: For years, Elasticsearch shipped with no authentication by default. Spin up a cluster, expose port 9200, and anyone on the internet could read your data. While newer versions have improved, the damage is done: countless clusters were exposed, and security misconfigurations remain common.

Here's a real example: in 2019, researchers found an exposed Elasticsearch server containing 1.2 billion records of personal data—social media profiles, email addresses, phone numbers. The cluster had no authentication. This wasn't a sophisticated hack; someone just found an open port. Similar breaches happen regularly.

References:

• Pure Insights — "Clusters without TLS, authentication, or access controls are vulnerable."
• HevoData: Elasticsearch PostgreSQL — "By default, Elasticsearch lacks built-in authentication."

Why Postgres avoids this: Postgres has required authentication from day one. You literally cannot connect without credentials—there's no "open by default" mode. SSL/TLS encryption, role-based access control, and row-level security are all built in and battle-tested over decades. Misconfiguration is still possible (any system can be misconfigured), but the secure path is the default path.

8. Monitoring Complexity

Elasticsearch gives you hundreds of metrics. The problem is knowing which ones matter.

The problem: JVM heap usage, young GC time, old GC time, thread pool queue sizes, circuit breaker trips, pending tasks, unassigned shards, indexing rate, search latency, segment count... Elasticsearch exposes all of this, but understanding what's normal vs. concerning requires deep expertise. Most teams set up basic monitoring, miss the early warning signs, and only find out something's wrong when it's already broken.

Say your cluster has been running fine for months. One day, search latency spikes. You check CPU and memory—they look okay. Disk? Fine. What you didn't notice: the "pending tasks" queue has been growing for days, and the "circuit breaker" tripped twice last week. By the time you figure this out, users have been complaining for hours.

References:

• Pure Insights — "Without proper observability, issues like JVM heap spikes or unassigned shards can escalate into outages."
• Elastic Docs — Guide to understanding circuit breaker exceptions and task queue backlogs

Why Postgres avoids this: Postgres monitoring is well-understood after 30+ years. The key metrics are simpler: connections, query latency, cache hit ratio, replication lag. pg_stat_statements shows your slowest queries. EXPLAIN ANALYZE tells you exactly why. Tools like pgBadger, pg_stat_activity, and any standard APM will get you 90% of the way there. You don't need to become a Postgres internals expert to keep it healthy.

9. Data Pipeline Sync Issues

Your real data lives in Postgres. Elasticsearch is just a copy for search. Keeping them in sync is harder than it sounds.

The problem: The typical architecture is Postgres → Kafka/Debezium → Elasticsearch. That's three systems. When data updates in Postgres, it flows through Kafka, gets transformed, and lands in Elasticsearch. What could go wrong? Everything. Kafka lag causes stale search results. Schema changes break the connector. A bug in your transformer corrupts data. And when something breaks, you're debugging across three different systems with three different log formats.

Say a customer updates their email address. The change saves to Postgres immediately. But the Kafka consumer is backed up, and Elasticsearch doesn't get the update for 10 minutes. During that window, any email sent via a search lookup goes to the old address. Customer support gets an angry call. You spend two hours figuring out where the delay happened.

References:

• InfoQ: Instacart Elasticsearch Postgres — Case study on data synchronization challenges
• Medium: Real-time Integration of PostgreSQL with Elasticsearch — Discussion of sync tooling and challenges

Why Postgres avoids this: If search lives in Postgres (using BM25 via pg_textsearch), there's no pipeline to manage. When you update a row, the search index updates in the same transaction. No Kafka, no Debezium, no sync jobs, no lag, no "which system has the latest data?" debugging. Your source of truth and your search index are the same thing.

10. Infrastructure Cost

Elasticsearch clusters are expensive to run. This isn't a bug—it's the architecture.

The problem: ES runs on the JVM, which means each node needs significant RAM (typically 16-64GB, with half allocated to heap). Production clusters require at least 3 nodes for high availability and quorum. Larger deployments need dedicated master nodes, coordinating nodes, and data nodes—that's a lot of instances. And because ES stores everything in Lucene segments, you need fast SSDs to avoid I/O bottlenecks. Add replicas for redundancy, and your storage costs multiply.

Say you spin up a "small" production cluster: 3 nodes, 32GB RAM each, SSDs. That's your baseline before you've even scaled. Now add a separate cluster for staging. And one for testing. Your cloud bill starts climbing, and you're not even at high traffic yet.

References:

• Elastic Cloud Pricing — Managed ES starts at ~$95/month for minimal configs, scales quickly
• AWS OpenSearch Pricing — Instance costs add up fast with multi-node clusters
• Sirius Open Source — "Each shard is a Lucene index with its own file handles, memory, and CPU overhead"

Why Postgres avoids this: Postgres runs on a single node for most workloads. No JVM overhead means lower memory requirements. You add read replicas when you actually need them, not because the architecture demands it. A single Postgres instance with pg_textsearch can handle substantial search workloads on hardware that would barely run one ES node. And if you're on a managed service like Tiger Data, you're paying for one database, not a distributed cluster.

The Alternative: Keep Search in Postgres

If you've made it this far, you've probably nodded along to at least a few of these issues. Maybe all ten. The question is: what do you do about it?

Can you overcome all these issues? Yes. Hire an Elasticsearch specialist (or a team of them). Invest in training. Build expertise over years. Companies do it every day.

But here's the question: do you want to?

It's like the shift from gas cars to electric cars. When you go electric, you don't just swap out the engine. You eliminate an entire category of problems: no oil changes, no transmission repairs, no spark plugs, no fuel injectors, no exhaust systems. The complexity just disappears.

That's what's happening with search. Extensions like pg_textsearch (BM25 ranking), pgvectorscale (vector search), and pgai (automatic embeddings) mean search can now live inside Postgres. No separate cluster. No JVM. No data pipelines. No sync jobs. A whole category of problems just goes away.

Every issue above has the same root cause: Elasticsearch is a separate system. Separate systems mean separate infrastructure, separate expertise, separate failure modes, and separate pipelines.

What if you didn't need a separate system?

PostgreSQL now has:

• BM25 ranking via pg_textsearch (same algorithm Elasticsearch uses)
• Vector search via pgvector and pgvectorscale
• Hybrid search combining both, with RRF (Reciprocal Rank Fusion) in pure SQL
• Automatic embedding sync via pgai

And for AI workflows, there's a bonus: when everything lives in Postgres, you can fork your database and get an instant copy of your search index, vectors, and embeddings. Try doing that with Elasticsearch.

For many workloads, "just use Postgres" isn't a compromise. It's a simplification.

One caveat: pg_textsearch doesn't currently support faceted search, so e-commerce with "filter by brand/price/color" is still a gap. For RAG, document search, and knowledge bases, you're covered.

Get Started in 5 Minutes

Ready to try hybrid search in Postgres? Here's all you need:

-- 1. Enable extensions
CREATE EXTENSION pg_textsearch;        -- BM25 keyword search
CREATE EXTENSION vectorscale CASCADE;  -- Vector search (includes pgvector)
CREATE EXTENSION ai;                   -- Auto-embedding generation (optional)

-- 2. Create your table
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding vector(1536)
);

-- 3. Create indexes
CREATE INDEX idx_bm25 ON documents USING bm25(content)
  WITH (text_config = 'english');
CREATE INDEX idx_vector ON documents USING diskann(embedding);

-- 4. Hybrid search with RRF (one query, two search types)
WITH 
bm25_results AS (
  SELECT id, ROW_NUMBER() OVER (
    ORDER BY content <@> to_bm25query('your search query', 'idx_bm25')
  ) as rank
  FROM documents
  LIMIT 20
),
vector_results AS (
  SELECT id, ROW_NUMBER() OVER (
    ORDER BY embedding <=> $1  -- $1 is your query embedding
  ) as rank
  FROM documents
  LIMIT 20
)
SELECT d.*, 
  COALESCE(1.0/(60 + b.rank), 0) + COALESCE(1.0/(60 + v.rank), 0) as rrf_score
FROM documents d
LEFT JOIN bm25_results b ON d.id = b.id
LEFT JOIN vector_results v ON d.id = v.id
WHERE b.id IS NOT NULL OR v.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 10;

That's it. BM25 + vectors + RRF, all in Postgres. No Elasticsearch, no Kafka, no sync jobs.

Try it on Tiger Data—all extensions come pre-installed.

Learn More

• pg_textsearch on GitHub — BM25 for Postgres (open source)
• pgvectorscale on GitHub — High-performance vector search