Do I really need a separate vector database for RAG, or can Postgres handle vector + keyword search with filters?

Most teams building RAG hit the same fork in the road: bolt on a dedicated vector database, or keep everything in Postgres and extend it. The short answer is that for most production RAG workloads, Postgres with pgvector (and optionally pgai) can handle vector search, keyword search, and rich filters in one place—if you design your schema and indexes correctly. A separate vector database is only necessary for very specific at-extreme-scale or vendor-lock-in reasons.

Quick Answer: You usually don’t need a separate vector database for RAG. Postgres—with pgvector for embeddings and native text search and filtering—can power hybrid search (vector + keyword + metadata) using plain SQL, while avoiding fragile, multi-system plumbing.

---

The Quick Overview

• What It Is: A Postgres-native approach to RAG using pgvector for embeddings, built-in full-text search, and standard SQL filters so you can run hybrid search without a separate vector DB.
• Who It Is For: Teams that already rely on Postgres, want to build RAG/search/AI agents, and care about maintainability, observability, and compliance as much as raw latency benchmarks.
• Core Problem Solved: Avoiding a fragile, high-maintenance stack of “LLM app + vector DB + sync jobs + analytics DB” by keeping live data, search, and AI retrieval in one Postgres platform.

---

How It Works

At a high level, Postgres handles RAG by storing documents (or chunks), their embeddings, and their metadata in regular tables. You then query those tables with SQL that combines:

• Vector similarity (pgvector distance operators)
• Keyword / full-text search (to_tsvector, tsvector, trigram search)
• Structured filters (time ranges, tenants, access control, numeric conditions)
• Ranking logic (order by vector distance, keyword rank, or a hybrid score)

Under the hood, extensions like pgvector and pgai stay close to Postgres’ core model: they introduce new types, operators, and functions, but not a new query language or separate store. On TigerData’s Postgres platform, you keep that same interface and add TimescaleDB primitives (hypertables, compression, tiered storage) once your RAG workload is also dealing with live telemetry or event streams.

A typical lifecycle looks like this:

1. Ingest & Embed Documents:

   • Store raw documents and metadata in a table.
   • Generate embeddings (outside or via pgai) and insert them into a vector column.
   • Add indexes for both vectors and text search.

2. Hybrid Search Queries (Vector + Keyword + Filters):

   • Use SELECT ... ORDER BY embedding <-> $query_embedding LIMIT k; for pure vector search.
   • Add full-text search via WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'engineer postgres');.
   • Add structured filters: time windows, tenants, permissions, numeric ranges.

3. Evolve to Production Scale:

   • Tune vector indexes and quantization parameters.
   • Partition large datasets with hypertables (time, tenant, or both).
   • Move cold vectors and documents into compressed, tiered storage without changing queries.

---

Features & Benefits Breakdown

Core Feature	What It Does	Primary Benefit
Vector search with pgvector	Stores embeddings in a vector column and supports similarity search with indexes.	Lets you implement RAG and semantic search inside Postgres without adding a new database.
Native text & keyword search	Uses tsvector, GIN/GiST indexes, and optional pg_trgm for full-text and fuzzy search.	Enables hybrid “vector + keyword” ranking in a single SQL query.
Rich SQL filters & joins	Leverages standard SQL predicates, joins, and aggregations over the same tables.	Gives fine-grained filtering (time, tenant, ACLs) and analytics without data duplication.
TimescaleDB hypertables	Automatically partitions large, time-based tables by time and key.	Keeps ingest and retrieval fast as your RAG index grows alongside telemetry or event data.
Hypercore row-columnar storage	Stores recent rows in row format and older ones in columnar format with compression.	Up to 98% compression for older documents and vectors, reducing storage and speeding scans.
Tiered + lakehouse integration	Moves cold data to object storage and integrates with Iceberg/S3 without custom glue.	Lets you keep RAG indexes in Postgres while syncing data to your lake for offline workloads.

---

How Postgres Handles Vector Search

1. Store embeddings with pgvector

Install pgvector (on TigerData this is already supported), then create a table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id           bigserial PRIMARY KEY,
    tenant_id    text NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now(),
    title        text,
    content      text,
    content_tsv  tsvector,
    embedding    vector(1536)  -- e.g., OpenAI / Cohere dimension
);

Populate embeddings:

INSERT INTO documents (tenant_id, title, content, content_tsv, embedding)
VALUES (
    'acme',
    'Postgres vectors',
    'Using PostgreSQL with pgvector for RAG workloads...',
    to_tsvector('english', 'Using PostgreSQL with pgvector for RAG workloads...'),
    $1  -- embedding as vector
);

For large-scale pipelines, you can generate embeddings directly in-database with pgai so there’s no external sync step.

2. Index for fast similarity search

Create an index on the embedding column. For example, IVF with L2 distance:

CREATE INDEX ON documents
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

-- Important: analyze after creating ivfflat
ANALYZE documents;

For small collections or where exact recall matters more than latency, you can start with a plain btree-like scan or HNSW (if available in your environment).

3. Run vector search queries

Basic vector similarity:

SELECT id, title, content
FROM documents
ORDER BY embedding <-> $query_embedding
LIMIT 10;

• <-> is the distance operator (nearest neighbors return first).
• With an index, this supports low-latency search even with millions of rows.

---

How Postgres Handles Keyword Search and Filters

Full-text search

Use tsvector and tsquery:

CREATE INDEX documents_tsv_idx
ON documents
USING GIN (content_tsv);

Query:

SELECT id, title, content
FROM documents
WHERE content_tsv @@ plainto_tsquery('english', 'postgres vector')
ORDER BY ts_rank(content_tsv, plainto_tsquery('english', 'postgres vector')) DESC
LIMIT 10;

Structured filters (time, tenant, ACL)

These are just standard SQL predicates—no new API or embedding-specific syntax:

SELECT id, title, content
FROM documents
WHERE tenant_id = 'acme'
  AND created_at >= now() - interval '30 days'
  AND content_tsv @@ plainto_tsquery('english', 'pricing')
ORDER BY created_at DESC
LIMIT 20;

Because Postgres keeps everything in one engine, you can also:

• Join to user/role tables for access control.
• Filter by numeric metrics (e.g., reading time, popularity).
• Aggregate for analytics (e.g., top topics by tenant).

---

Hybrid Search: Vector + Keyword + Filters in One Query

A dedicated vector database usually asks you to approximate hybrid search by:

• Running a vector search in the vector DB.
• Running keyword search in a separate system (e.g., Elasticsearch).
• Stitching results together in application code.

That’s exactly the “fragile and high-maintenance” pattern TigerData customers describe.

In Postgres, hybrid search is one query:

WITH params AS (
    SELECT
        $1::vector        AS q_embedding,
        plainto_tsquery('english', $2::text) AS q_ts,
        $3::text          AS q_tenant
)
SELECT
    d.id,
    d.title,
    d.content,
    -- simple hybrid scoring: 0–1 normalized similarity + text rank
    (1 - (d.embedding <-> p.q_embedding)) * 0.7
    + ts_rank(d.content_tsv, p.q_ts) * 0.3 AS score
FROM documents d
CROSS JOIN params p
WHERE d.tenant_id = p.q_tenant
  AND d.content_tsv @@ p.q_ts
  AND d.created_at >= now() - interval '90 days'
ORDER BY score DESC
LIMIT 10;

You can tune this to your workload:

• Change weighting between vector vs keyword.
• Add recency boosts (+ log(1 + EXTRACT(epoch FROM now() - d.created_at))).
• Override ranking for “must include” filters (e.g., specific tags).

No external service, no dual writes, no consistency lag between search indexes.

---

Ideal Use Cases

• Best for teams standardizing on Postgres: Because it keeps RAG, OLTP, telemetry, and analytics in one SQL-native system, with no new query language to learn.
• Best for regulated or audited environments: Because Postgres (and Tiger Cloud) brings encryption, backups, PITR, and SOC 2 / GDPR / HIPAA commitments to your RAG workload without separate security reviews for a new database.
• Best for RAG on live telemetry or event data: Because TigerData’s TimescaleDB primitives (hypertables, compression, tiered storage) keep ingest fast while vectors and metadata grow to billions of rows.

---

When a Separate Vector Database Might Still Make Sense

There are some scenarios where a dedicated vector DB can be reasonable:

• You’re already locked into a vendor ecosystem that bundles LLM APIs, vector storage, and hosting—and your team is comfortable with that trade-off.
• You need ultra-specialized ANN algorithms or distributed topologies that your current Postgres setup doesn’t yet support and you’re at extreme scale (hundreds of billions of vectors with sub-10ms latency across regions).
• You’re comfortable with dual writes and drift and prefer to optimize each component separately (vector DB for retrieval, a different warehouse for analytics, another system for OLTP).

Important: even in these cases, many teams still keep Postgres in the center and treat the vector database as an optimization cache—not the source of truth.

---

Limitations & Considerations (Postgres for RAG)

• Index and memory tuning are not optional:
  ANN indexes (IVF/HNSW) require careful choice of lists, probes, and memory settings. You need to benchmark and adjust, just as you would any large Postgres workload.

• RAG is still constrained by schema design:
  Poorly thought-out schemas—giant unindexed tables, no partitioning, or wide, uncompressed columns—can drag down performance. On TigerData, hypertables and compression mitigate this, but you still need to model carefully.

• Analytics vs retrieval trade-offs:
  Deep analytics on the same tables can contend with your RAG queries for I/O and CPU. Hypercore’s row-columnar storage and read replicas help, but you should consider isolating analytics with replicas or separate services as usage grows.

---

Pricing & Plans (TigerData Context)

If you run Postgres yourself, cost is mostly cloud infra plus your own ops time.

On Tiger Cloud (TigerData’s managed Postgres for telemetry and AI workloads):

• You pay for provisioned compute and storage (with independent scaling), not per query.
• Backups, snapshots, PITR, and internal networking between components are included—no surprise per-query or per-ingest fees.
• Compression (up to 98%) and tiered storage reduce the cost of storing large RAG corpora alongside telemetry and events.

Example positioning:

• Performance Plan: Best for teams needing a managed Postgres with pgvector and TimescaleDB to support moderate-to-large RAG workloads, with HA, backups, and observability out of the box.
• Scale / Enterprise Plans: Best for teams running mission-critical RAG and telemetry together, needing multi-AZ HA, higher SLAs, SOC 2 reports, GDPR support, and HIPAA (on Enterprise), plus dedicated support and VPC peering.

(Exact plan names and details evolve; contact TigerData for the latest specifics.)

---

Frequently Asked Questions

Do I really need a separate vector database for RAG?

Short Answer: Usually not. Postgres with pgvector can handle vector + keyword search with filters for most RAG workloads.

Details:
Modern Postgres, especially on a platform like TigerData, can:

• Store billions of documents and embeddings with hypertables and compression.
• Run ANN vector search with ivfflat/HNSW indexes.
• Combine vector scores with full-text search and arbitrary SQL filters in the same query.
• Provide HA, backups, PITR, and security controls required in production.

A separate vector DB adds another system to operate and keep in sync. For most teams, the operational simplicity of a Postgres-native RAG stack outweighs any marginal latency gains from a specialized store.

---

How do I implement hybrid (vector + keyword) search in Postgres?

Short Answer: Put embeddings and text in the same table, index both, and compute a combined score in SQL.

Details:

1. Store and index both representations:

   ALTER TABLE documents
   ADD COLUMN embedding vector(1536),
   ADD COLUMN content_tsv tsvector;
   
   CREATE INDEX documents_embedding_idx
   ON documents USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
   
   CREATE INDEX documents_content_tsv_idx
   ON documents USING GIN (content_tsv);
   

2. Query with a hybrid score:

   WITH params AS (
       SELECT
           $1::vector AS q_embedding,
           plainto_tsquery('english', $2::text) AS q_ts
   )
   SELECT
       d.id,
       d.title,
       (1 - (d.embedding <-> p.q_embedding)) * 0.6
       + ts_rank(d.content_tsv, p.q_ts) * 0.4 AS score
   FROM documents d
   CROSS JOIN params p
   WHERE d.content_tsv @@ p.q_ts
   ORDER BY score DESC
   LIMIT 20;
   

You can further filter by tenant, user permissions, or time ranges with standard WHERE clauses. No special SDK is required; it’s just SQL.

---

Summary

You don’t automatically need a separate vector database for RAG. With pgvector (and optionally pgai), Postgres can:

• Store and query embeddings at scale.
• Combine vector similarity with full-text search and rich SQL filters.
• Keep RAG retrieval, OLTP, telemetry, and analytics in one system, avoiding fragile glue code.

On TigerData’s Postgres platform, you add TimescaleDB primitives—hypertables, compression, Hypercore row-columnar storage, and tiered storage—to keep performance and costs predictable as you scale to billions of documents and quadrillions of data points. The result is a durable, Postgres-native RAG stack that’s fast enough for production and maintainable enough for the long haul.

---

Next Step

Get Started