"We ran our PoC with Pinecone, but want to integrate into existing PostgreSQL for production"—such inquiries are surging from companies whose RAG initiatives are moving past PoC and into production operations. Dedicated vector DBs certainly offer high performance, but in production, subtle yet impactful challenges emerge: operational DBs multiply, transaction boundaries diverge, and SLAs duplicate.
With pgvector, you can keep vector search entirely within your existing PostgreSQL database. This article is an operations guide structured from the implementation practitioner's perspective on "what decisions to make when using pgvector in production."
Four reasons to choose pgvector over dedicated vector DBs
1. Transaction boundaries align
Because document metadata and embedding vectors reside in the same DB, INSERTs and UPDATEs are completed in a single transaction. If you maintain Pinecone separately, you will have to manage consistency across two systems.
2. Existing permissions and audit infrastructure can be reused
The RLS detailed in SaaS Multi-Tenant Design Implemented with PostgreSQL RLS applies directly to vector search as well. Being able to unify tenant isolation, audit logging, and backups is a major advantage in enterprise custom development.
3. Inherited operational knowledge
PostgreSQL DBAs are plentiful both in-house and in the contracting market. The fact that you do not need to hire new personnel specifically to operate a vector DB is a crucial factor for mid-sized enterprises.
4. Cost
At a scale of 1 million vectors, costs often end up at 1/3 to 1/5 of the monthly price of dedicated vector DBs.
Index selection — HNSW vs. IVFFlat
While pgvector supports two types of indexes, HNSW is generally recommended for production.
| Dimension | HNSW | IVFFlat |
|---|---|---|
| Search speed | Fast | Medium |
| Build time | Slow (several times longer) | Fast |
| Memory consumption | High | Low |
| Dynamic additions | Strong (no rebuild required) | Accuracy degrades upon additions |
| Recommended use case | Production operations | Large batch ingestion / initial builds |
Practical parameters for HNSW
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- クエリ時: 精度と速度のトレードオフを ef_search で調整
SET hnsw.ef_search = 80;The defaults for m (connection count) and ef_construction (search width during construction) are sufficient for most use cases. By varying ef_search at query time between 40 and 200, you can identify the optimal balance between your target QPS and recall rate.
Hybrid search — "Vectors alone" are not production-ready
The limitation of vector search is that exact matches for proper nouns or product numbers are weak. In production, a hybrid approach with BM25 (full-text search) is essential.
-- 全文検索 + ベクトル検索を Reciprocal Rank Fusion で統合
WITH bm25 AS (
SELECT id, ts_rank(tsv, plainto_tsquery('japanese', $1)) AS score
FROM documents
WHERE tsv @@ plainto_tsquery('japanese', $1)
ORDER BY score DESC LIMIT 50
), vec AS (
SELECT id, 1 - (embedding <=> $2) AS score
FROM documents
ORDER BY embedding <=> $2 LIMIT 50
)
SELECT id, SUM(1.0 / (60 + rank)) AS rrf_score
FROM (
SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM bm25
UNION ALL
SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM vec
) merged
GROUP BY id
ORDER BY rrf_score DESC LIMIT 10;This pattern achieves even higher accuracy when combined with the rerankers discussed in our Enterprise RAG Guide: Multimodal Embeddings + Rerankers.
Scaling — What breaks at which stage
From an implementer's perspective, here is a breakdown of the next bottlenecks encountered at each scale.
| Scale | Bottleneck | Supported |
|---|---|---|
| Up to 100k records | Nothing in particular | A single instance is sufficient |
| 100k to 1M records | HNSW memory | Set shared_buffers to 25% of physical memory; increase maintenance_work_mem |
| 1M to 10M records | HNSW update cost during INSERTs | Batch INSERTs + index rebuilding with CONCURRENTLY |
| Over 10M records | Single-node limits | Consider partitioning or Citus / pgvecto.rs |
In particular, HNSW running out of memory is easy to overlook; you need to adjust work_mem via SET statements and monitor index residency in the page cache.
Monitoring — Five metrics you must track
- Average query latency (P50 / P95) — Captured via pg_stat_statements
- Index hit rate — pg_statio_user_indexes
- Vector search Recall@K — Run offline evaluation sets in CI
- Connection count / wait count — Mandatory when routing through PgBouncer or Hyperdrive
- Embedding generation API cost — Monthly expense for OpenAI, Voyage, or Gemini Embedding
For integrating monitoring with SaaS platforms, the patterns from our OpenTelemetry Migration Guide can be directly reused.
Cost estimates — 1 million vector comparison with Pinecone
| Cost component | Pinecone（Standard） | pgvector（RDS db.r6g.xlarge） |
|---|---|---|
| Monthly DB cost | Approx. $70+ | Approx. $230 |
| If co-located on existing DB? | Separate contract mandatory | $0 additional cost |
| Operational labor | Specialized knowledge required | Can be managed by existing DBAs |
| Estimated TCO for 1M records | $80+ / month | $0 to tens of dollars with existing DB co-location |
The advantage of dedicated vector DBs is increasingly concentrated in ultra-large-scale workloads of over 100 million records with global distribution.
Conclusion — "Do not add more DBs" is the answer beyond operational PoCs
The appeal of pgvector lies in how it demotes vector search from a "specialized technology" to "just another PostgreSQL feature." When RAG moves beyond PoCs into production, operational simplicity almost always outweighs marginal gains in performance.
At GleamHub, we provide end-to-end support ranging from pgvector migration PoCs from Pinecone and Qdrant to implementing hybrid search and setting up monitoring infrastructure. If you want to deploy RAG to production or reduce vector DB operational overhead, please feel free to reach out via our contact form.
