<div id="readability-page-1" class="page"><div><img src="https://devstarsj.github.io/assets/https://images.unsplash.com/photo-1544383835-bda2bc66a55d?w=1200&amp;auto=format&amp;fit=crop"><h2 id="postgres-as-a-vector-database-pgvector-in-production-2026">Postgres as a Vector Database: pgvector in Production 2026</h2>The vector database gold rush of 2023-2024 left many teams with a fragmented data stack: Pinecone for vectors, Redis for caching, PostgreSQL for relational data. In 2026, there’s a quiet consolidation happening — teams are asking whether they actually need a dedicated vector database, or whether pgvector running inside their existing PostgreSQL instance is good enough. For most workloads, the answer is increasingly: pgvector is enough.<img src="https://images.unsplash.com/photo-1489875347316-0d6a7b6aa6dd?w=1000&amp;auto=format&amp;fit=crop" alt="Database architecture visualization"> Photo by Markus Spiske on Unsplash<h2 id="what-pgvector-provides-today">What pgvector Provides Today</h2>pgvector has matured significantly since its early releases. Version 0.8.x (current as of mid-2026) supports:<ul><li>Vector storage up to 16,000 dimensions</li><li>Exact nearest-neighbor search (brute-force)</li><li>Approximate nearest-neighbor via HNSW and IVFFlat indexes</li><li>Filtering: combine vector search with SQL WHERE clauses</li><li>Distance metrics: L2 (<code>&lt;-&gt;</code>), inner product (<code>&lt;#&gt;</code>), cosine (<code>&lt;=&gt;</code>), L1 (<code>&lt;+&gt;</code>)</li><li>Half-precision vectors (<code>halfvec</code>) for 2x storage reduction</li><li>Binary vectors (<code>bit</code>) for ultra-compressed embeddings</li></ul><h2 id="setup-and-basic-operations">Setup and Basic Operations</h2><div><pre><code>-- Enable extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with vector column
CREATE TABLE documents (
 id BIGSERIAL PRIMARY KEY,
 content TEXT NOT NULL,
 embedding vector(1536), -- OpenAI text-embedding-3-small
 metadata JSONB,
 created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert with embedding
INSERT INTO documents (content, embedding, metadata)
VALUES (
 'pgvector enables vector similarity search in PostgreSQL',
 '[0.023, -0.041, 0.178, ...]'::vector,
 '{"source": "blog", "topic": "database"}'
);

-- Cosine similarity search
SELECT
 id,
 content,
 1 - (embedding &lt;=&gt; '[0.021, -0.038, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding &lt;=&gt; '[0.021, -0.038, ...]'::vector
LIMIT 10;
</code></pre></div><h2 id="the-critical-part-hnsw-vs-ivfflat">The Critical Part: HNSW vs IVFFlat</h2>Index choice is the most consequential performance decision.<h3 id="hnsw-hierarchical-navigable-small-world">HNSW (Hierarchical Navigable Small World)</h3><div><pre><code>CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
</code></pre></div>Parameters:<ul><li><code>m</code>: Number of connections per node (8–64, default 16). Higher = better recall, more memory</li><li><code>ef_construction</code>: Quality of graph construction (32–400, default 64). Higher = better quality, slower build</li></ul>Characteristics:<ul><li>✅ Excellent query performance (fast ANN search)</li><li>✅ No training data required</li><li>✅ Handles insertions without full rebuild</li><li>❌ High memory usage (~2x the data size at m=16)</li><li>❌ Slow to build for large datasets</li></ul><h3 id="ivfflat-inverted-file-index">IVFFlat (Inverted File Index)</h3><div><pre><code>-- Requires data to exist before building index
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
</code></pre></div>Parameters:<ul><li><code>lists</code>: Number of clusters (~sqrt(num_rows) is a good starting point)</li></ul>Characteristics:<ul><li>✅ Much lower memory footprint</li><li>✅ Faster to build</li><li>❌ Lower recall at equivalent speed vs HNSW</li><li>❌ Requires data present at index creation</li><li>❌ Bulk inserts require periodic VACUUM/reindex</li></ul>Recommendation for 2026: Default to HNSW for most production workloads. Its query performance and operational simplicity outweigh the memory cost unless you’re memory-constrained.<h2 id="filtered-vector-search-where-pgvector-shines">Filtered Vector Search: Where pgvector Shines</h2>This is where pgvector beats most dedicated vector DBs — seamless hybrid queries:<div><pre><code>-- Find similar documents, only for a specific user, recent ones
SELECT
 id,
 content,
 metadata-&gt;&gt;'source' AS source,
 1 - (embedding &lt;=&gt; $1::vector) AS similarity
FROM documents
WHERE
 user_id = $2
 AND created_at &gt; NOW() - INTERVAL '30 days'
 AND metadata-&gt;&gt;'status' = 'published'
ORDER BY embedding &lt;=&gt; $1::vector
LIMIT 5;
</code></pre></div>Try doing this in Pinecone. You’ll be pulling IDs, then querying your RDBMS separately, then merging — a double round-trip with significant complexity.<h3 id="pre-filter-vs-post-filter">Pre-filter vs Post-filter</h3>pgvector (like most ANN indexes) does approximate search within the full index, then applies SQL filters. For high-selectivity filters (&lt; 5% of rows match), this can hurt recall:<div><pre><code>-- For high-selectivity filters, set probes/ef_search higher
SET hnsw.ef_search = 200; -- default is 40

-- Or use a partial index if you query a subset frequently
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE status = 'published';
</code></pre></div><h2 id="production-configuration">Production Configuration</h2><h3 id="postgresql-settings-for-vector-workloads">PostgreSQL Settings for Vector Workloads</h3><div><pre><code># postgresql.conf adjustments

# More memory for larger working sets
shared_buffers = 4GB # 25% of RAM
effective_cache_size = 12GB # 75% of RAM

# HNSW index build parallelism
max_parallel_maintenance_workers = 4
maintenance_work_mem = 2GB # Per worker for index builds

# Larger work_mem helps ANN searches
work_mem = 256MB

# Vector-specific
vector.hnsw_ef_search = 40 # Default, tune up for recall
</code></pre></div><h3 id="connection-pooling-is-essential">Connection Pooling is Essential</h3>Vector search queries can be CPU-intensive. Use PgBouncer in transaction mode:<div><pre><code># pgbouncer.ini
[databases]
vector_db = host=localhost dbname=production

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
server_idle_timeout = 600
</code></pre></div><h2 id="scaling-strategies">Scaling Strategies</h2><h3 id="partitioning-for-large-datasets">Partitioning for Large Datasets</h3>For tens of millions of vectors, partition by a natural dimension:<div><pre><code>CREATE TABLE documents (
 id BIGSERIAL,
 user_id BIGINT NOT NULL,
 embedding vector(1536),
 content TEXT
) PARTITION BY HASH (user_id);

CREATE TABLE documents_0 PARTITION OF documents
 FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE documents_1 PARTITION OF documents
 FOR VALUES WITH (modulus 4, remainder 1);
-- ... etc

-- Partition-aware index on each partition
CREATE INDEX ON documents_0 USING hnsw (embedding vector_cosine_ops);
</code></pre></div><h3 id="pgvector-with-citus">pgvector with Citus</h3>For massive scale, Citus (PostgreSQL distributed extension, now open-source) allows sharding vector tables across nodes while preserving SQL semantics.<h2 id="real-cost-comparison">Real Cost Comparison</h2>For a concrete example: 10 million vectors at 1536 dimensions.<table><thead><tr><th>Option</th><th>Monthly Cost</th><th>Notes</th></tr></thead><tbody><tr><td>Pinecone (p1.x1)</td><td>~$700</td><td>Managed, s1 pod type</td></tr><tr><td>Qdrant Cloud</td><td>~$300</td><td>16GB plan</td></tr><tr><td>Weaviate Cloud</td><td>~$450</td><td>Standard plan</td></tr><tr><td>RDS PostgreSQL + pgvector</td><td>~$250</td><td>db.r7g.2xlarge</td></tr><tr><td>Self-hosted Postgres</td><td>~$100</td><td>EC2 r7g.2xlarge</td></tr></tbody></table>Cost advantage is real — but the bigger win is operational simplicity: one backup strategy, one monitoring stack, one connection pool, one auth system.<h2 id="when-to-use-a-dedicated-vector-db">When to Use a Dedicated Vector DB</h2>pgvector isn’t always the answer:<ul><li>&gt;100M vectors with complex filtering — dedicated DBs optimize for this</li><li>Multi-tenancy at massive scale — some vector DBs have better isolation primitives</li><li>Real-time indexing at high write throughput — pgvector’s HNSW can lag under heavy concurrent writes</li><li>Sophisticated metadata filtering — some dedicated DBs have better ANN-with-filter algorithms</li><li>Managed service without PostgreSQL expertise — fully managed vector DBs reduce ops burden</li></ul><h2 id="rag-pattern-with-pgvector">RAG Pattern with pgvector</h2>A complete Python example:<div><pre><code>import asyncpg
from openai import AsyncOpenAI

openai = AsyncOpenAI()

async def semantic_search(query: str, user_id: int, top_k: int = 5):
 # Get query embedding
 response = await openai.embeddings.create(
 input=query,
 model="text-embedding-3-small"
 )
 query_vector = response.data[0].embedding
 
 # Search with filter
 conn = await asyncpg.connect(DATABASE_URL)
 try:
 results = await conn.fetch("""
 SELECT
 id,
 content,
 metadata,
 1 - (embedding &lt;=&gt; $1::vector) AS similarity
 FROM documents
 WHERE user_id = $2
 ORDER BY embedding &lt;=&gt; $1::vector
 LIMIT $3
 """, query_vector, user_id, top_k)
 
 return [
 {
 "id": row["id"],
 "content": row["content"],
 "similarity": float(row["similarity"]),
 "metadata": dict(row["metadata"])
 }
 for row in results
 ]
 finally:
 await conn.close()
</code></pre></div><h2 id="the-verdict">The Verdict</h2>pgvector in 2026 is production-grade for most RAG and similarity search use cases. If you’re already running PostgreSQL, consolidating your vector workload there is probably the right default. The tooling is mature, the performance is competitive up to tens of millions of vectors, and the operational cost of running one database instead of two is real.Start with pgvector. Migrate to a specialized solution when your data size, query complexity, or SLA requirements genuinely demand it — not because of theoretical future scale.<h2 id="references">References</h2><ul><li><a href="https://github.com/pgvector/pgvector">pgvector GitHub</a></li><li><a href="https://github.com/pgvector/pgvector#hnsw">pgvector HNSW Documentation</a></li><li><a href="https://neon.tech/blog/pgvector-vs-pinecone">Choosing Between pgvector and Dedicated Vector DBs — Neon Blog</a></li><li><a href="https://www.postgresql.org/docs/current/datatype.html">PostgreSQL Documentation: Vector Type</a></li></ul> <div>이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :) </div></div></div>

Dev Note

Postgres as a Vector Database: pgvector in Production 2026
The vector database gold rush of 2023-2024 left many teams with a fragmented data stack: Pinecone for vectors, Redis for caching, PostgreSQL for relational data. In 2026, there’s a quiet consolidation happening — teams are asking whether they actually need a dedicated vector database, or whether pgvector running inside their existing PostgreSQL instance is good enough. For most workloads, the answer is increasingly: pgvector is enough.
Photo by Markus Spiske on Unsplash
What pgvector Provides Today
pgvector has matured significantly since its early releases. Version 0.8.x (current as of mid-2026) supports:
Vector storage up to 16,000 dimensions
Exact nearest-neighbor search (brute-force)
Approximate nearest-neighbor via HNSW and IVFFlat indexes
Filtering: combine vector search with SQL WHERE clauses
Distance metrics: L2 (<->), inner product (<#>), cosine (<=>), L1 (<+>)
Half-precision vectors (halfvec) for 2x storage reduction
Binary vectors (bit) for ultra-compressed embeddings
Setup and Basic Operations
-- Enable extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Create table with vector column
CREATE TABLE documents (
 id BIGSERIAL PRIMARY KEY,
 content TEXT NOT NULL,
 embedding vector(1536), -- OpenAI text-embedding-3-small
 metadata JSONB,
 created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert with embedding
INSERT INTO documents (content, embedding, metadata)
VALUES (
 'pgvector enables vector similarity search in PostgreSQL',
 '[0.023, -0.041, 0.178, ...]'::vector,
 '{"source": "blog", "topic": "database"}'
);
-- Cosine similarity search
SELECT
 id,
 content,
 1 - (embedding <=> '[0.021, -0.038, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.021, -0.038, ...]'::vector
LIMIT 10;
The Critical Part: HNSW vs IVFFlat
Index choice is the most consequential performance decision.
HNSW (Hierarchical Navigable Small World)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
Parameters:
m: Number of connections per node (8–64, default 16). Higher = better recall, more memory
ef_construction: Quality of graph construction (32–400, default 64). Higher = better quality, slower build
Characteristics:
✅ Excellent query performance (fast ANN search)
✅ No training data required
✅ Handles insertions without full rebuild
❌ High memory usage (~2x the data size at m=16)
❌ Slow to build for large datasets
IVFFlat (Inverted File Index)
-- Requires data to exist before building index
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
Parameters:
lists: Number of clusters (~sqrt(num_rows) is a good starting point)
Characteristics:
✅ Much lower memory footprint
✅ Faster to build
❌ Lower recall at equivalent speed vs HNSW
❌ Requires data present at index creation
❌ Bulk inserts require periodic VACUUM/reindex
Recommendation for 2026: Default to HNSW for most production workloads. Its query performance and operational simplicity outweigh the memory cost unless you’re memory-constrained.
Filtered Vector Search: Where pgvector Shines
This is where pgvector beats most dedicated vector DBs — seamless hybrid queries:
-- Find similar documents, only for a specific user, recent ones
SELECT
 id,
 content,
 metadata->>'source' AS source,
 1 - (embedding <=> $1::vector) AS similarity
FROM documents
WHERE
 user_id = $2
 AND created_at > NOW() - INTERVAL '30 days'
 AND metadata->>'status' = 'published'
ORDER BY embedding <=> $1::vector
LIMIT 5;
Try doing this in Pinecone. You’ll be pulling IDs, then querying your RDBMS separately, then merging — a double round-trip with significant complexity.
Pre-filter vs Post-filter
pgvector (like most ANN indexes) does approximate search within the full index, then applies SQL filters. For high-selectivity filters (< 5% of rows match), this can hurt recall:
-- For high-selectivity filters, set probes/ef_search higher
SET hnsw.ef_search = 200; -- default is 40
-- Or use a partial index if you query a subset frequently
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE status = 'published';
Production Configuration
PostgreSQL Settings for Vector Workloads
# postgresql.conf adjustments
# More memory for larger working sets
shared_buffers = 4GB # 25% of RAM
effective_cache_size = 12GB # 75% of RAM
# HNSW index build parallelism
max_parallel_maintenance_workers = 4
maintenance_work_mem = 2GB # Per worker for index builds
# Larger work_mem helps ANN searches
work_mem = 256MB
# Vector-specific
vector.hnsw_ef_search = 40 # Default, tune up for recall
Connection Pooling is Essential
Vector search queries can be CPU-intensive. Use PgBouncer in transaction mode:
# pgbouncer.ini
[databases]
vector_db = host=localhost dbname=production
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
server_idle_timeout = 600
Scaling Strategies
Partitioning for Large Datasets
For tens of millions of vectors, partition by a natural dimension:
CREATE TABLE documents (
 id BIGSERIAL,
 user_id BIGINT NOT NULL,
 embedding vector(1536),
 content TEXT
) PARTITION BY HASH (user_id);
CREATE TABLE documents_0 PARTITION OF documents
 FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE documents_1 PARTITION OF documents
 FOR VALUES WITH (modulus 4, remainder 1);
-- ... etc
-- Partition-aware index on each partition
CREATE INDEX ON documents_0 USING hnsw (embedding vector_cosine_ops);
pgvector with Citus
For massive scale, Citus (PostgreSQL distributed extension, now open-source) allows sharding vector tables across nodes while preserving SQL semantics.
Real Cost Comparison
For a concrete example: 10 million vectors at 1536 dimensions.
Option
 Monthly Cost
 Notes
 
Pinecone (p1.x1)
 ~$700
 Managed, s1 pod type
 
Qdrant Cloud
 ~$300
 16GB plan
 
Weaviate Cloud
 ~$450
 Standard plan
 
RDS PostgreSQL + pgvector
 ~$250
 db.r7g.2xlarge
 
Self-hosted Postgres
 ~$100
 EC2 r7g.2xlarge
 
Cost advantage is real — but the bigger win is operational simplicity: one backup strategy, one monitoring stack, one connection pool, one auth system.
When to Use a Dedicated Vector DB
pgvector isn’t always the answer:
>100M vectors with complex filtering — dedicated DBs optimize for this
Multi-tenancy at massive scale — some vector DBs have better isolation primitives
Real-time indexing at high write throughput — pgvector’s HNSW can lag under heavy concurrent writes
Sophisticated metadata filtering — some dedicated DBs have better ANN-with-filter algorithms
Managed service without PostgreSQL expertise — fully managed vector DBs reduce ops burden
RAG Pattern with pgvector
A complete Python example:
import asyncpg
from openai import AsyncOpenAI
openai = AsyncOpenAI()
async def semantic_search(query: str, user_id: int, top_k: int = 5):
 # Get query embedding
 response = await openai.embeddings.create(
 input=query,
 model="text-embedding-3-small"
 )
 query_vector = response.data[0].embedding
 
 # Search with filter
 conn = await asyncpg.connect(DATABASE_URL)
 try:
 results = await conn.fetch("""
 SELECT
 id,
 content,
 metadata,
 1 - (embedding <=> $1::vector) AS similarity
 FROM documents
 WHERE user_id = $2
 ORDER BY embedding <=> $1::vector
 LIMIT $3
 """, query_vector, user_id, top_k)
 
 return [
 {
 "id": row["id"],
 "content": row["content"],
 "similarity": float(row["similarity"]),
 "metadata": dict(row["metadata"])
 }
 for row in results
 ]
 finally:
 await conn.close()
The Verdict
pgvector in 2026 is production-grade for most RAG and similarity search use cases. If you’re already running PostgreSQL, consolidating your vector workload there is probably the right default. The tooling is mature, the performance is competitive up to tens of millions of vectors, and the operational cost of running one database instead of two is real.
Start with pgvector. Migrate to a specialized solution when your data size, query complexity, or SLA requirements genuinely demand it — not because of theoretical future scale.
References
pgvector GitHub
pgvector HNSW Documentation
Choosing Between pgvector and Dedicated Vector DBs — Neon Blog
PostgreSQL Documentation: Vector Type

Postgres as a Vector Database: pgvector in Production 2026

댓글