Forem: Yasser B.

PostgreSQL Connection Pooling with PgBouncer: A Complete Guide

Yasser B. — Tue, 31 Mar 2026 15:22:09 +0000

You launch your app. Traffic is light, everything works. A few weeks later you start seeing FATAL: remaining connection slots are reserved for non-replication superuser connections. Your PostgreSQL server is out of connections and your app is falling over.

This is one of the most common PostgreSQL scaling problems, and connection pooling is the fix. But the fix has its own complexity: PgBouncer has three modes with different tradeoffs, the configuration is full of footguns, and if you get it wrong you get subtle bugs that are much harder to debug than the original connection error.

This guide covers how PostgreSQL connections actually work, how to set up and configure PgBouncer correctly, and how to choose the right pool mode for your application.

Why PostgreSQL Connections Are Expensive

PostgreSQL handles each connection with a dedicated server process. When a client connects, Postgres forks a new OS process. That process:

Allocates its own memory (typically 5-10 MB per connection including shared memory overhead)
Maintains its own backend state, transaction state, and lock tables
Requires the kernel to schedule it like any other process

At 50 connections, this is fine. At 500 connections, you have 500 OS processes and the scheduler starts showing up in your performance profiles. At 1,000 connections, you are likely hitting the max_connections limit (default 100 in stock PostgreSQL) and your app is returning errors.

The naive fix is to increase max_connections. Don't do that without thinking it through. Each connection costs memory. Set max_connections = 1000 on a server with 8 GB of RAM and you've allocated the entire heap to idle connections before a single query runs. The shared_buffers and work_mem math goes sideways fast.

The right fix is to reduce the number of actual connections to PostgreSQL. That's what connection poolers do.

What PgBouncer Does

PgBouncer sits between your application and PostgreSQL. Your app thinks it's talking to Postgres, but it's actually talking to PgBouncer. PgBouncer maintains a pool of real connections to Postgres and hands them out to client requests.

The numbers look like this in practice:

Before PgBouncer: 300 app threads, 300 Postgres connections
After PgBouncer (transaction mode): 300 app threads, 20 actual Postgres connections

Those 20 connections serve 300 clients because most clients are not actually executing SQL at any given moment. They're waiting for network I/O, processing results, or sitting idle. Transaction mode takes advantage of this by returning a connection to the pool the moment a transaction commits.

Installing PgBouncer

On Ubuntu/Debian:

sudo apt-get install pgbouncer

On macOS with Homebrew:

brew install pgbouncer

Configuring PgBouncer

A minimal working config:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600

For production, use hashed passwords. Generate them with:

SELECT concat('"', rolname, '" "', rolpassword, '"')
FROM pg_authid
WHERE rolname = 'myuser';

Your app connects to port 6432 instead of 5432. Nothing else changes in the app code.

The Three Pool Modes

Session Mode

A server connection is assigned when the client connects and held until the client disconnects. This is the safest mode, behaves identically to a direct PostgreSQL connection. Prepared statements, advisory locks, LISTEN/NOTIFY all work correctly.

Session mode does not help much with connection counts at steady state. Use it when you need full compatibility and your problem is peak load, not constant high concurrency.

Transaction Mode

A server connection is assigned for the duration of a transaction and returned to the pool immediately after. This gives you the dramatic reduction in server connections.

The tradeoff: session-level state does not persist across transactions. This breaks:

PREPARE and server-side prepared statement caching
SET commands that are not wrapped in a transaction
Advisory locks (session-scoped)
LISTEN and NOTIFY subscriptions
Temp tables that are supposed to persist across transactions

Transaction mode works well for stateless web applications using pg (Node.js), psycopg2/psycopg3 (Python), or JDBC (Java), as long as those frameworks don't use session-level features.

Statement Mode

A connection is held only for a single SQL statement, then returned. Breaks multi-statement transactions entirely. Rarely the right choice for web applications.

Choosing Your Mode

Your app	Recommended mode
Stateless API using ORM (Django, Rails, Prisma)	Transaction
Long-lived connections with prepared statements	Session
Connection count issues at peak only	Session
Connection count issues at steady state	Transaction
Serverless functions	Transaction
Application using LISTEN/NOTIFY	Session

Pool Sizing

A reasonable starting formula:

default_pool_size = (number of PostgreSQL CPU cores) * 2 + number of disks

In practice, 20-30 works well for most web applications. Check pool utilization while running:

SHOW POOLS;

cl_waiting > 0 sustained means the pool is undersized. sv_idle consistently high means it's oversized.

Monitoring PgBouncer

Connect to the admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Key commands: SHOW POOLS, SHOW STATS, SHOW CLIENTS, SHOW SERVERS, RELOAD.

SHOW STATS tells you avg_query_time and avg_wait_time. If either is climbing, something is backing up.

Common Pitfalls

Prepared statements in transaction mode: If your app uses server-side prepared statements, transaction mode will break it. Disable them in your driver: prepare_threshold=None in psycopg2, { prepare: false } in pg (Node.js).

Connection storms at startup: Set reserve_pool_size = 5 and max_client_conn to at least 2-3x your expected peak.

SSL: Always use SSL for both client-to-PgBouncer and PgBouncer-to-PostgreSQL in production.

When to Skip PgBouncer

You probably don't need it if:

Fewer than 50 concurrent connections at peak
Using a modern ORM with client-side pooling already (Prisma, Django, Rails)
Serverless workloads with 1-2 connections per function instance

You need it if multiple processes or pods each maintain their own pool and the total exceeds max_connections.

Bottom Line

PostgreSQL connection pooling is not optional at scale. PgBouncer in transaction mode is the right default for most web applications.

The main things to get right: pool size (start at 20-30 for OLTP), mode (transaction for stateless apps, session for anything using prepared statements or advisory locks), monitoring (cl_waiting and avg_wait_time), and SSL in production.

Connection pooling is unglamorous infrastructure, but it's the difference between an app that falls over at traffic spikes and one that just handles them.

Originally published at rivestack.io

PostgreSQL Full Text Search: A Complete Guide

Yasser B. — Mon, 30 Mar 2026 14:49:54 +0000

There's a database already running in your stack. It has your users, your content, your transactions. And buried in that same PostgreSQL instance is a full text search engine you've probably never turned on.

PostgreSQL full text search has been production ready for over a decade. It handles stemming, stop words, multiple languages, weighted ranking, and trigram fuzzy matching. You don't need Elasticsearch for a search feature. You don't need Algolia if your data is already in Postgres. For most applications, especially those with under a few million documents, built-in full text search is the right call.

This guide covers everything you need to ship full text search in PostgreSQL: how the underlying model works, how to index correctly, how to rank results, and how it compares to vector search with pgvector.

How PostgreSQL Full Text Search Works

PostgreSQL doesn't search raw text. It converts text into a normalized representation called a tsvector, then matches queries expressed as tsquery objects. This two-step process is what makes it fast.

A tsvector is a sorted list of lexemes: normalized word forms that strip suffixes and reduce words to their base form. The word "running" becomes "run". "Postgres" becomes "postgr". Stop words like "the", "a", "an" are dropped entirely.

SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

A tsquery is what you match against:

SELECT to_tsquery('english', 'jumping');
-- Returns: 'jump'

-- Boolean operators
SELECT to_tsquery('english', 'postgres & search');  -- AND
SELECT to_tsquery('english', 'postgres | mysql');   -- OR
SELECT to_tsquery('english', 'database & !oracle'); -- NOT

The match operator is @@:

SELECT to_tsvector('english', 'PostgreSQL is a powerful database')
  @@ to_tsquery('english', 'powerful');
-- Returns: true

Setting Up Full Text Search

Option 1: Generated column (recommended for PostgreSQL 12+)

ALTER TABLE articles
  ADD COLUMN search_vector TSVECTOR
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

setweight assigns priority to fields: 'A' (highest) to title, 'B' to body. Documents where the search term appears in the title rank higher.

Query with ranking:

SELECT id, title,
  ts_rank(search_vector, query) AS rank
FROM articles,
  to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Option 2: Trigger-maintained column

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

CREATE FUNCTION articles_search_vector_trigger() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_vector_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION articles_search_vector_trigger();

Indexing for Performance

Without an index, full text search does a full table scan. GIN (Generalized Inverted Index) is purpose built for tsvectors.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

With this index, full text search queries return in milliseconds even on tables with millions of rows.

Ranking Results

SELECT id, title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

ts_rank_cd uses cover density (how close matching terms are to each other) and often gives better results for multi-word queries.

Snippet Generation

SELECT id, title,
  ts_headline(
    'english', body,
    to_tsquery('english', 'postgresql'),
    'MaxWords=50, MinWords=15, StartSel=<mark>, StopSel=</mark>'
  ) AS snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql')
LIMIT 5;

Note: ts_headline does not use the GIN index. Call it only on the final page of results, not before pagination.

Handling User Input Safely

-- websearch_to_tsquery: safe, handles Google-style syntax
SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', '"full text search" postgres -oracle');

websearch_to_tsquery (PostgreSQL 11+) is the best default for user input. It's injection-safe, handles partial syntax, and supports quoted phrases and exclusions.

Full Text Search vs pgvector

They solve different problems:

Full text search finds documents containing specific words or phrases. Fast, precise, no ML model required.

pgvector finds semantically similar documents, even without shared keywords. Needs an embedding model.

Use case	Best approach
Blog/docs search	Full text search
Semantic Q&A, RAG	pgvector
E-commerce	Often both

Many systems use both, merging scores with Reciprocal Rank Fusion. For RAG pipelines, see our guides on getting started with pgvector and pgvector with Python.

Complete Production Setup

CREATE TABLE articles (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  search_vector TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED
);

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

CREATE FUNCTION search_articles(query_text TEXT, page_size INT DEFAULT 20, page_offset INT DEFAULT 0)
RETURNS TABLE (id BIGINT, title TEXT, snippet TEXT, rank FLOAT4) AS $$
DECLARE q TSQUERY := websearch_to_tsquery('english', query_text);
BEGIN
  RETURN QUERY
  SELECT a.id, a.title,
    ts_headline('english', a.body, q, 'MaxWords=40, MinWords=10, StartSel=<mark>, StopSel=</mark>'),
    ts_rank(a.search_vector, q)
  FROM articles a
  WHERE a.search_vector @@ q
  ORDER BY ts_rank(a.search_vector, q) DESC
  LIMIT page_size OFFSET page_offset;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM search_articles('postgresql replication', 20, 0);

Keyword based search, relevance ranking, HTML ready snippets, and pagination, all within PostgreSQL.

Try It on Rivestack

PostgreSQL full text search and pgvector work side by side on the same database. No separate infrastructure for keyword vs semantic search.

Rivestack gives you a fully managed PostgreSQL instance with pgvector, pg_trgm, and all standard extensions enabled by default. Try it free, no credit card required.

Originally published at rivestack.io

PostgreSQL High Availability: A Practical Guide for Production

Yasser B. — Sun, 29 Mar 2026 16:08:53 +0000

Your application is live. Customers are using it. The database goes down.

How long before traffic routes around the failure? Ten seconds? Five minutes? Never, because you're paged at 2 AM and have to manually promote a replica while the on-call engineer Slacks you asking if the database is "doing a thing"?

PostgreSQL high availability is one of those topics that looks straightforward in blog posts and turns out to be deeply humbling when you actually implement it in production. This guide covers how PostgreSQL HA actually works, the main tools people use, what typically goes wrong, and when the complexity of DIY HA stops being worth it.

What "High Availability" Means for PostgreSQL

High availability means your database keeps serving requests even when individual components fail. For PostgreSQL, that typically requires three things working together:

Data replication — at least one copy of your data exists on a server other than the primary
Failure detection — something notices when the primary is unreachable
Automatic failover — the replica promotes itself to primary without a human in the loop

PostgreSQL ships with excellent replication primitives but no built-in automatic failover. The replication part is solid and well-understood. The failover part is where teams get into trouble.

Streaming Replication: The Foundation

PostgreSQL replication is based on Write-Ahead Log (WAL) shipping. Every write to the primary is first written to the WAL. Replicas connect to the primary and stream that WAL in near-real-time, replaying it to stay current.

Setting up a basic standby looks like this in postgresql.conf on the primary:

-- postgresql.conf on the primary
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

And in pg_hba.conf, you allow the replica to connect for replication:

# pg_hba.conf on the primary
host replication replicator 10.0.0.2/32 scram-sha-256

The replica connects with a primary_conninfo in its configuration and starts streaming WAL from the primary. Once streaming, the replica is typically only milliseconds behind.

Synchronous vs Asynchronous Replication

By default, PostgreSQL replication is asynchronous: the primary commits a transaction and returns success to the client before confirming the replica received the data. If the primary dies at exactly the wrong moment, you can lose the last few transactions.

Synchronous replication waits for at least one replica to confirm it has received and written the WAL before reporting the commit as successful:

-- postgresql.conf on the primary
synchronous_standby_names = 'replica1'
synchronous_commit = on

This gives you zero-RPO (recovery point objective) — no committed data is ever lost. The tradeoff is latency: every write waits for a round trip to the replica. On a local network this is typically 1–5ms. Across availability zones it can be 10–30ms depending on the cloud provider.

Most production setups use synchronous replication for the hot standby and asynchronous replication for additional read replicas or DR standbys that are geographically distant.

The Problem: PostgreSQL Doesn't Fail Over Itself

With streaming replication running, you have your data in two places. But if the primary goes down, PostgreSQL doesn't automatically promote the replica. You have two choices:

Manual failover — someone runs pg_ctl promote or SELECT pg_promote() on the replica. Fast if someone is awake, catastrophic if not.
Automated failover via an HA tool — a separate process watches the primary and promotes the replica when it detects a failure.

Almost every production PostgreSQL HA setup uses one of three tools for automated failover: Patroni, pg_auto_failover, or repmgr. They all solve the same problem; they have meaningfully different complexity and tradeoff profiles.

Patroni: The Industry Standard (and Why It's Hard)

Patroni is what most teams with serious PostgreSQL HA requirements end up using. It's battle-tested, highly configurable, and runs at scale. It's also genuinely complex to operate.

Patroni uses a distributed consensus store — either etcd, Consul, or ZooKeeper — to maintain cluster state and elect a primary. A minimal production Patroni setup is 3 etcd nodes + 2–3 PostgreSQL nodes + HAProxy. That's 5–6 servers before you've even started.

A minimal patroni.yml:

scope: prod-cluster
namespace: /db/
name: pg-node-1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.0.1:8008

etcd3:
  hosts: 10.0.1.1:2379,10.0.1.2:2379,10.0.1.3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 30
    maximum_lag_on_failover: 1048576
    synchronous_mode: true

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.0.1:5432
  data_dir: /var/lib/postgresql/data

HAProxy needs health checks against the Patroni REST API (port 8008), not just the PostgreSQL port — that's how it knows which node is the current primary.

Patroni is genuinely good software. But "run Patroni in production" is a weeks-long project, not an afternoon task.

pg_auto_failover: Simpler, More Opinionated

pg_auto_failover uses a dedicated monitor node instead of etcd:

# On the monitor node
pg_autoctl create monitor \
  --pgdata /var/lib/postgresql/monitor \
  --pgport 5000 \
  --hostname monitor.internal

# On the primary node
pg_autoctl create postgres \
  --pgdata /var/lib/postgresql/data \
  --monitor postgres://autoctl_node@monitor.internal:5000/pg_auto_failover \
  --hostname primary.internal \
  --pgport 5432

Easier to set up than Patroni, but the monitor is a single point of failure and it's less flexible for complex topologies. Good choice for teams that want something working quickly without running etcd.

What Goes Wrong in Production

Split-Brain

The most dangerous failure mode: a network partition causes both nodes to think they're primary and accept writes. Patroni prevents this with etcd distributed locks — a node can only be primary if it holds the lock. If it can't reach etcd, it demotes itself. pg_auto_failover prevents it by centralizing all promotion decisions in the monitor.

Replication Lag at Failover Time

With async replication, a lagged replica that promotes will be missing the last N transactions. Patroni's maximum_lag_on_failover controls this — but set it too conservatively and failover blocks entirely if all replicas are lagged after a network partition.

Application Not Reconnecting

After failover, point your connection string at a VIP or load balancer, not a node's IP:

postgresql://ha-proxy.internal:5432/mydb?target_session_attrs=read-write

The target_session_attrs=read-write parameter tells libpq to reject connections to read-only servers, which helps clients find the current primary automatically.

Testing Your Failover

# Kill the primary while watching application logs
sudo systemctl stop postgresql@17-main

# Check the cluster state
patronictl -c /etc/patroni/config.yml list

If you haven't tested your failover, you don't have HA. You have a plan that might work.

The Failover Time Question

Typical Patroni failover: 10–30 seconds. The timeline:

Primary unreachable (0s)
Patroni TTL expires, primary declared dead (default: 30s)
Replica acquires DCS lock and promotes (1–2s)
HAProxy detects new primary (2–5s)
Application reconnects (depends on pool settings)

Reducing TTL speeds detection but increases false positives from transient network blips. Most teams settle on 15–30 seconds.

When Managed PostgreSQL High Availability Makes Sense

DIY HA works. Large companies run Patroni at massive scale. But you're running 5+ nodes, maintaining etcd, and debugging edge cases at the worst possible time.

As we've broken down before, the real cost of self-hosted PostgreSQL HA includes infrastructure, tooling, and engineering time — and engineering time dominates.

Rivestack's HA clusters handle streaming replication, automatic failover, and connection routing automatically. Failover happens in seconds, your connection string doesn't change, and there's no etcd cluster to maintain. HA clusters start at $99/month with NVMe storage, automated backups, point-in-time recovery, and monitoring included.

What to Do If You're Starting From Scratch

For a managed setup: Try Rivestack — spin up an HA cluster, verify failover works from the dashboard, and move on to building your application.

For a self-managed setup:

Start with pg_auto_failover for 2–3 nodes and fast setup
Move to Patroni if you need multi-datacenter support or fine-grained control
Use HAProxy with Patroni REST API health checks (port 8008) for connection routing
Enable synchronous replication if your application can't tolerate data loss
Test failover in staging before relying on it in production

The Bottom Line

PostgreSQL has excellent HA building blocks. Streaming replication is fast, reliable, and well-understood. The gap is automated failover, and Patroni or pg_auto_failover fills it — but adds real operational complexity.

Test your failover before you need it. The worst time to discover your replica is 10 minutes behind is during an actual outage.

If you want to skip the infrastructure work, Rivestack handles the HA layer for you — including pgvector if you're building AI applications. See the getting started guide if that's your stack.

Originally published at rivestack.io

How to Use pgvector with Python: A Complete Guide

Yasser B. — Sat, 28 Mar 2026 13:58:17 +0000

You've decided to use PostgreSQL for your vector embeddings. Smart move. Now you need to wire it up from Python — and if you've landed here, you've probably already noticed that there are a few different libraries involved, the syntax isn't immediately obvious, and the official pgvector docs give you the C extension but leave the Python story somewhat scattered.

This guide covers the whole picture: installing the Python client, connecting with both psycopg3 and SQLAlchemy, storing and querying embeddings, building indexes, and wiring it up into a real RAG pipeline. By the end you'll have a working setup you can actually ship.

What You Need Before You Start

You'll need:

A PostgreSQL database with the vector extension enabled
Python 3.8+
The pgvector Python package

If you're running PostgreSQL locally, install the pgvector extension from the pgvector GitHub repo and run CREATE EXTENSION vector;. If you're using a managed PostgreSQL service, the extension is typically pre-installed — on Rivestack, it's enabled by default on every database.

Install the Python package:

pip install pgvector

You'll also want a database driver. The two most common choices for Python are psycopg3 (direct, fast, recommended) and SQLAlchemy (for ORM-based projects). We'll cover both.

# For psycopg3
pip install "psycopg[binary]"

# For SQLAlchemy
pip install sqlalchemy psycopg2-binary

Setting Up the Vector Extension

Connect to your database and enable the extension if it isn't already:

CREATE EXTENSION IF NOT EXISTS vector;

Run this once per database. You can verify it's installed with:

SELECT * FROM pg_extension WHERE extname = 'vector';

Using pgvector with psycopg3

psycopg3 is the modern PostgreSQL driver for Python. It's faster than psycopg2, has proper async support, and the pgvector Python package integrates with it natively.

Connecting and Registering the Vector Type

import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("postgresql://user:password@localhost:5432/mydb")
register_vector(conn)

The register_vector call is important — it tells psycopg how to serialize and deserialize Python lists/numpy arrays into the vector type PostgreSQL expects. Without it, you'll get type errors when inserting.

Creating a Table with a Vector Column

with conn.cursor() as cur:
    cur.execute("""
        CREATE TABLE IF NOT EXISTS documents (
            id BIGSERIAL PRIMARY KEY,
            content TEXT NOT NULL,
            embedding VECTOR(1536)
        )
    """)
    conn.commit()

The 1536 dimension matches OpenAI's text-embedding-3-small model. If you're using a different model, adjust accordingly:

Model	Dimensions
OpenAI text-embedding-3-small	1536
OpenAI text-embedding-3-large	3072
Cohere embed-v4	1024
Google text-embedding-005	768
all-MiniLM-L6-v2 (local)	384

Inserting Embeddings

In practice, you generate embeddings with an API call or a local model, then insert them alongside your content:

from openai import OpenAI

client = OpenAI()

def get_embedding(text: str) -> list[float]:
    response = client.embeddings.create(
        model="text-embedding-3-small",
        input=text
    )
    return response.data[0].embedding

documents = [
    "PostgreSQL is a powerful open-source relational database.",
    "pgvector adds vector similarity search to PostgreSQL.",
    "Python is a high-level programming language.",
]

with conn.cursor() as cur:
    for doc in documents:
        embedding = get_embedding(doc)
        cur.execute(
            "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
            (doc, embedding)
        )
    conn.commit()

pgvector's psycopg integration accepts plain Python lists directly — no special wrapping needed.

Querying by Similarity

To find the documents most similar to a query, compute the query embedding and use one of pgvector's distance operators:

<=> — cosine distance (most common for text embeddings)
<-> — L2 (Euclidean) distance
<#> — negative inner product

query = "How does vector search work in PostgreSQL?"
query_embedding = get_embedding(query)

with conn.cursor() as cur:
    cur.execute("""
        SELECT content, 1 - (embedding <=> %s) AS similarity
        FROM documents
        ORDER BY embedding <=> %s
        LIMIT 5
    """, (query_embedding, query_embedding))

    results = cur.fetchall()
    for content, similarity in results:
        print(f"[{similarity:.3f}] {content}")

The <=> operator returns cosine distance (0 = identical, 2 = opposite), so 1 - distance gives you cosine similarity if you want a score between 0 and 1.

Using pgvector with SQLAlchemy

If your project uses SQLAlchemy for ORM models, pgvector has first-class support there too.

from sqlalchemy import create_engine, Column, BigInteger, Text
from sqlalchemy.orm import declarative_base, Session
from pgvector.sqlalchemy import Vector

engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/mydb")
Base = declarative_base()

class Document(Base):
    __tablename__ = "documents"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    content = Column(Text, nullable=False)
    embedding = Column(Vector(1536))

Base.metadata.create_all(engine)

Inserting with SQLAlchemy:

doc = Document(
    content="pgvector makes PostgreSQL a capable vector store.",
    embedding=get_embedding("pgvector makes PostgreSQL a capable vector store.")
)

with Session(engine) as session:
    session.add(doc)
    session.commit()

Querying with SQLAlchemy — use the l2_distance, cosine_distance, or max_inner_product functions:

from pgvector.sqlalchemy import cosine_distance

query_embedding = get_embedding("vector search with PostgreSQL")

with Session(engine) as session:
    results = (
        session.query(Document)
        .order_by(cosine_distance(Document.embedding, query_embedding))
        .limit(5)
        .all()
    )
    for doc in results:
        print(doc.content)

Adding an Index for Production

The queries above work fine for development and small datasets — PostgreSQL does an exact nearest-neighbor scan. But with more than ~50,000 vectors, you'll want an approximate nearest-neighbor (ANN) index to keep queries fast.

pgvector supports two index types:

HNSW — faster queries, higher memory usage during build, better recall
IVFFlat — faster build, less memory, slightly lower recall

For most production use cases, HNSW is the right choice:

with conn.cursor() as cur:
    cur.execute("""
        CREATE INDEX IF NOT EXISTS documents_embedding_idx
        ON documents
        USING hnsw (embedding vector_cosine_ops)
        WITH (m = 16, ef_construction = 64)
    """)
    conn.commit()

The vector_cosine_ops tells pgvector to optimize the index for cosine distance — make sure this matches the operator you use in queries (<=>). If you're using L2 distance, use vector_l2_ops instead.

Important: Build the index after loading your initial data. Building on an empty table and then loading data works too, but index quality is better when built on the full dataset.

HNSW parameters

m — number of connections per layer (default 16, range 2–100). Higher = better recall, more memory.
ef_construction — search depth during build (default 64, range 4–1000). Higher = better recall, slower build.

For query-time speed/recall tradeoff, set hnsw.ef_search:

with conn.cursor() as cur:
    cur.execute("SET hnsw.ef_search = 100")
    # Now run your similarity query

Building a RAG Pipeline with pgvector and Python

Here's a minimal but complete Retrieval-Augmented Generation pipeline using everything above:

import psycopg
from pgvector.psycopg import register_vector
from openai import OpenAI

openai_client = OpenAI()
db = psycopg.connect("postgresql://user:password@localhost:5432/mydb")
register_vector(db)

def embed(text: str) -> list[float]:
    return openai_client.embeddings.create(
        model="text-embedding-3-small",
        input=text
    ).data[0].embedding

def index_document(content: str):
    """Store a document and its embedding."""
    embedding = embed(content)
    with db.cursor() as cur:
        cur.execute(
            "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
            (content, embedding)
        )
    db.commit()

def retrieve(query: str, k: int = 5) -> list[str]:
    """Find the k most relevant documents for a query."""
    query_embedding = embed(query)
    with db.cursor() as cur:
        cur.execute("""
            SELECT content
            FROM documents
            ORDER BY embedding <=> %s
            LIMIT %s
        """, (query_embedding, k))
        return [row[0] for row in cur.fetchall()]

def answer(question: str) -> str:
    """Retrieve context and generate an answer."""
    context_docs = retrieve(question)
    context = "\n\n".join(context_docs)

    response = openai_client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {
                "role": "system",
                "content": "Answer questions using only the provided context. Be concise."
            },
            {
                "role": "user",
                "content": f"Context:\n{context}\n\nQuestion: {question}"
            }
        ]
    )
    return response.choices[0].message.content

# Index some documents
index_document("pgvector supports HNSW and IVFFlat indexes.")
index_document("Cosine similarity is best for text embedding comparisons.")
index_document("Rivestack provides managed PostgreSQL with pgvector pre-installed.")

# Ask a question
print(answer("What index types does pgvector support?"))

Metadata Filtering

One of the big advantages of pgvector over standalone vector databases is that you can filter by regular columns in the same query. If you have multi-tenant data or want to search within a category, just add a WHERE clause:

# Add a source column to your table
cur.execute("""
    ALTER TABLE documents ADD COLUMN IF NOT EXISTS source TEXT
""")

# Query with metadata filter
cur.execute("""
    SELECT content, embedding <=> %s AS distance
    FROM documents
    WHERE source = 'internal-wiki'
    ORDER BY embedding <=> %s
    LIMIT 5
""", (query_embedding, query_embedding))

No syncing, no secondary filtering step, no extra infrastructure. It's just SQL.

Connection Pooling

pgvector queries are fast, but embedding vectors are large (1536 floats ≈ 6KB per vector). If you're running a web application with concurrent requests, use a connection pool to avoid exhausting PostgreSQL's connection limit:

from psycopg_pool import ConnectionPool

pool = ConnectionPool("postgresql://user:password@localhost:5432/mydb",
                      min_size=2, max_size=10,
                      configure=register_vector)

with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT content FROM documents ORDER BY embedding <=> %s LIMIT 5",
                    (query_embedding,))
        results = cur.fetchall()

On Rivestack, connection pooling is built into the platform so you don't need to manage PgBouncer yourself — you connect to the pooler endpoint and the rest is handled for you.

Async Support

If you're building with FastAPI, asyncio, or any other async Python framework, psycopg3 has full async support:

import asyncio
import psycopg
from pgvector.psycopg import register_vector_async

async def main():
    conn = await psycopg.AsyncConnection.connect(
        "postgresql://user:password@localhost:5432/mydb"
    )
    await register_vector_async(conn)

    query_embedding = get_embedding("vector search")
    async with conn.cursor() as cur:
        await cur.execute("""
            SELECT content FROM documents
            ORDER BY embedding <=> %s LIMIT 5
        """, (query_embedding,))
        results = await cur.fetchall()
        for row in results:
            print(row[0])

asyncio.run(main())

Note the register_vector_async — you need the async version when working with AsyncConnection.

Common Mistakes

Using the wrong distance operator for your index. If you create an HNSW index with vector_cosine_ops but query with <-> (L2 distance), the index won't be used and you'll get a full sequential scan. Match the operator to the index ops class.

Forgetting register_vector. You'll get can't adapt type 'list' errors. Call it once per connection (or once on the pool).

Building indexes on empty tables. The index will exist but won't actually improve query quality. Load your data first.

Not setting ef_search for precision-sensitive use cases. The default is 40, which is fast but sacrifices some recall. For RAG where missing a relevant document matters, SET hnsw.ef_search = 100 is worth the small latency increase.

Where to Go From Here

You now have everything you need to build vector search into a Python application using pgvector:

psycopg3 for direct, fast database access
SQLAlchemy if you prefer an ORM
HNSW indexes for production performance
Metadata filtering using standard SQL WHERE clauses
A full RAG pipeline skeleton you can extend

The operational side — backups, connection pooling, high availability, keeping pgvector updated — is where the real work begins if you're self-hosting. If you'd rather not deal with that, try Rivestack: managed PostgreSQL with pgvector pre-installed, NVMe storage for fast index traversal, and automatic backups. You get the same SQL interface you've just built, without the 3 AM pages.

Originally published at rivestack.io

pgvector Cosine Distance: How <=> Actually Works

Yasser B. — Fri, 27 Mar 2026 21:17:05 +0000

Introduction

When we first wired up pgvector for semantic search, I assumed <=> (cosine distance) was just "the normal one." Then a colleague asked why our similarity scores were sometimes above 1.0, and I realized I'd been cargo-culting the operator without understanding what it actually returns.

The pgvector cosine distance operator <=> is probably the most used distance function in similarity search — and the most misunderstood. Here's what's actually happening under the hood, when to use it, and when to switch.

What `<=>` Returns

<=> returns cosine distance, not cosine similarity. The relationship is:

cosine_distance = 1 - cosine_similarity

So two identical vectors return 0 (not 1), and two orthogonal vectors return 1. Two vectors pointing in opposite directions return 2.

-- Identical vectors: distance = 0
SELECT '[1,0,0]'::vector <=> '[1,0,0]'::vector;
-- Returns: 0

-- Opposite vectors: distance = 2
SELECT '[1,0,0]'::vector <=> '[-1,0,0]'::vector;
-- Returns: 2

-- Find the 5 nearest neighbors by cosine distance
SELECT id, content, embedding <=> '[0.1, 0.3, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.3, ...]'::vector
LIMIT 5;

This trips people up constantly. If you're used to thinking "higher = more similar," you need to flip your mental model. With <=>, lower scores are better.

Three Operators, Three Use Cases

pgvector exposes three distance operators:

Operator	Metric	Best for
`<=>`	Cosine distance	NLP embeddings, semantic search
`<->`	L2 (Euclidean) distance	Image embeddings, dense numeric vectors
`<#>`	Negative inner product	When vectors are already unit-normalized

Use <=> when: your embeddings come from an NLP model (OpenAI, Cohere, Mistral, etc.) and you care about directional similarity rather than magnitude. Most language model embeddings encode meaning in the direction of the vector, not its length.

Use <-> when: magnitude matters. Image feature vectors or tabular embeddings where distance in absolute space is meaningful.

Use <#> when: your vectors are already L2-normalized (unit length). It returns the negative dot product, which is equivalent to cosine similarity for unit vectors — and it's faster because it skips the normalization step.

The Normalization Shortcut

If you pre-normalize your embeddings before inserting them, <#> is strictly faster than <=> and produces equivalent ranking:

-- Normalize on insert (Python)
import numpy as np

def normalize(vec):
    norm = np.linalg.norm(vec)
    return vec / norm if norm > 0 else vec

embedding = normalize(model.encode(text))

-- Then query with inner product
SELECT id, content, (embedding <#> query_vec) * -1 AS similarity
FROM documents
ORDER BY embedding <#> query_vec
LIMIT 10;

We benchmarked this on a 1M-row table with 1536-dim vectors. Pre-normalizing and using <#> cut query time by ~18% vs <=> on an IVFFlat index. Not huge, but free.

Index Compatibility

One thing that bites teams: your index operator class must match your query operator.

-- For cosine distance queries (<=>), use vector_cosine_ops
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- For L2 queries (<->), use vector_l2_ops
CREATE INDEX ON documents USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

If you create an ivfflat index with vector_l2_ops and then query with <=>, Postgres will do a full sequential scan. No error, no warning — just slow queries that silently skip the index. Run EXPLAIN and look for Index Scan vs Seq Scan to confirm your index is being used.

Picking Lists and probes

lists is the number of clusters IVFFlat partitions your data into. A good starting point is sqrt(row_count). At query time, ivfflat.probes controls how many clusters to search:

-- Set probes at session level (or per query)
SET ivfflat.probes = 10;

SELECT id, embedding <=> '[...]'::vector AS dist
FROM documents
ORDER BY dist
LIMIT 5;

Higher probes = better recall, slower queries. For most semantic search workloads, probes = 10 gives 95%+ recall at reasonable speed. Test with your actual data.

Wrapping Up

The pgvector cosine distance operator <=> is the right default for language model embeddings — but know what it returns (distance, not similarity), verify your index operator class matches your query operator, and consider pre-normalizing if you're chasing extra performance.

If you want pgvector running on managed Postgres without tuning kernel params or fighting storage I/O, Rivestack handles that — provisioned in 30 seconds with the pgvector extension pre-enabled.

I built a managed pgvector service and here's what I learned about vector search performance

Yasser B. — Wed, 25 Mar 2026 09:01:38 +0000

I Ran pgvector on NVMe vs Cloud SSD. The Difference Shocked Me.
2,000 queries per second at under 4ms. That's what I'm getting on a $35/month server. Let me tell you how I got there and what I had to build to make it work.

The problem started with a side project

I was building a RAG pipeline. Standard stuff: OpenAI embeddings, PostgreSQL, pgvector extension, HNSW index. Everything worked fine in development. Then I moved it to a managed database on a regular cloud provider and watched my query latency go from 4ms to 47ms under any real load.

I spent two days thinking my index was wrong. Wrong ef_search value. Wrong m parameter. Wrong dimension count. None of that was it.
The problem was the disk.

HNSW does not behave like a normal database query
Most database queries are sequential reads. Your disk reads a chunk of data in order, hands it back, done. SSDs are fast at this. Even gp3 cloud SSDs are fast at this.

HNSW is different. An HNSW index traversal is essentially a graph walk. You start at an entry point, compare distances, jump to neighbors, compare again, jump again. Each jump is a random read to a different location on disk. The more vectors you have, the more jumps, the more random reads.

Cloud SSDs are slow at random reads. NVMe drives are very fast at random reads. That gap, which barely matters for most database workloads, is everything for pgvector at scale.
I didn't believe it until I benchmarked it myself.

The actual numbers

I tested 1 million vectors at 1536 dimensions (OpenAI text-embedding-3-small), HNSW index, cosine distance, ef_search=40, 16 concurrent clients.

On a cloud SSD backed instance: around 410 QPS, p95 latency at 18ms.
On NVMe: 2,150 QPS, p95 at 2.8ms.
Same PostgreSQL version. Same pgvector version. Same query. Same index parameters. Five times the throughput, six times lower latency. Just from the storage layer.

That's when I decided to build Rivestack.

Why PostgreSQL and not a dedicated vector database

Honest answer: because I didn't want to manage two databases.
The moment you move your vectors to Pinecone or Qdrant or Weaviate, you have split your data across two systems. Your relational data lives in Postgres. Your vectors live somewhere else. Every query that needs both involves a round trip between systems. That latency adds up fast.

With pgvector you write one query. You can filter by user_id, join against your documents table, and do a vector similarity search in a single SQL statement. That's not a minor convenience. It changes how you architect the whole application.
The only real argument against pgvector is scale. If you have a billion vectors, you need a dedicated system. For the other 99% of applications, pgvector on NVMe is genuinely competitive.

What I actually built

Rivestack is a managed PostgreSQL service where pgvector is the whole point, not an afterthought. NVMe storage on every plan. HNSW pre-configured with sensible defaults. Daily backups, point-in-time recovery, HA failover if you want it.

The thing I kept running into with other managed Postgres providers is that pgvector is just... there. An extension you can enable. Nobody has tuned the storage layer for it. Nobody has written documentation for RAG use cases. Nobody has thought about what happens to your index when you have 5 million vectors and 50 concurrent clients.

That's the gap I'm filling.

The honest trade-offs

Rivestack is not for everyone. If you need built-in auth, storage buckets, or a real-time WebSocket layer, use Supabase. They're great at that. If you have hundreds of millions of vectors and need automatic sharding, use Pinecone. They're built for that.
Rivestack is for teams who need pgvector to actually perform under load and don't want to spend three days tuning PostgreSQL configuration to get there.

What I'd do differently

I spent too long on the benchmark tooling before I had a single user. Classic founder mistake. The infrastructure is solid but I should have shipped earlier and iterated based on real workloads instead of synthetic ones.

Also I underestimated how much developers care about EU data residency. It's come up in every conversation I've had since launch. If you're building anything GDPR-adjacent, knowing your vectors never leave EU territory is not a minor detail.

Try it if you're building with embeddings
Free shared tier at rivestack.io, no credit card required. Paid plans start at $35/month for a dedicated node with 2 vCPU, 4GB RAM and 55GB NVMe.
If you're already running pgvector somewhere, migration is just pg_dump and pg_restore. Takes about five minutes for most databases.

What are you using for vector storage right now? And what's the biggest pain point you've hit with it? Genuinely curious whether the storage layer issue I ran into is common or whether I just had unusually bad luck with my cloud provider.

I indexed 30 million Hacker News posts into a single Postgres database. Here is what I learned about pgvector at scale.

Yasser B. — Sun, 22 Feb 2026 15:53:49 +0000

I have been running Kubernetes clusters and CI/CD pipelines for a living for years. Big org, 5000 developers, the usual enterprise stuff. But on the side I have always been building things. A few weeks ago I launched Rivestack, a managed Postgres service built around pgvector for AI workloads. The problem was obvious: nobody knew it existed and I had zero marketing budget.

So instead of writing blog posts nobody would read or tweeting into the void, I decided to build something useful and give it away. I built a semantic search engine over the entire Hacker News archive.
You can try it here: https://ask.rivestack.io

Type any question in plain English and it finds relevant HN threads by meaning, not by matching keywords. Search for "how to deal with burnout as a solo founder" and you will get threads where people talked about exactly that, even if they never used the word burnout.
The whole thing runs on a single Postgres instance with pgvector. No Pinecone. No Weaviate. No Qdrant. Just Postgres.
Here is what I learned building it.

pgvector is way faster than people think
There is this narrative that pgvector is fine for prototyping but you need a "real" vector database for production. That has not been my experience at all.

With HNSW indexes on NVMe storage, I am getting sub 4ms query latency at 99% recall. For context, that is fast enough that users cannot tell the difference between this and a keyword search. The bottleneck in most RAG and search applications is not the vector lookup. It is everything else around it.

I spent a lot of time tuning HNSW parameters and the biggest takeaway is that the defaults are actually pretty good for most workloads. The main thing that matters is having enough memory for the index and using NVMe storage. If your index fits in memory, pgvector flies.
HNSW vs IVFFlat is not even a close decision anymore

I started with IVFFlat because it uses less memory. Bad idea. The recall was inconsistent and it degrades as you add and delete data because the centroids get stale. I switched to HNSW and never looked back. The build time is slower but the query performance and recall stability are worth it.

If you are starting a new project in 2026, just use HNSW. The memory overhead is manageable and the results are dramatically better.
Keeping everything in one database is a superpower
This is the part that surprised me the most. When your embeddings live next to your relational data, things get simple in ways you do not expect.

Want to filter search results by date? Just add a WHERE clause. Want to boost recent posts? Order by a combination of vector distance and recency. Want to update an embedding when the source data changes? Do it in a single transaction and you never have stale data.

With a separate vector database you need to build syncing logic, handle failures where one system updated but the other did not, and manage two sets of credentials, backups, and monitoring. With pgvector it is just Postgres. The same backups, the same monitoring, the same failover you already know.

Embeddings are the easy part. Data cleaning is the hard part.
I expected the embedding generation to be the big challenge. It was not. The real work was cleaning and preparing the HN data. Dealing with deleted posts, HTML entities, encoding issues, extremely long comments that need to be chunked, short comments that are just "this" or "+1" and add noise to the index.

I probably spent 70% of my time on data preparation and 30% on everything else combined. If you are building something similar, budget your time accordingly.

The cost surprised me too
I keep hearing that vector search is expensive. Maybe it is if you are paying Pinecone prices at scale. Running this on a single Postgres instance with pgvector costs me almost nothing compared to what a dedicated vector database would charge for the same dataset. The entire infrastructure for ask.rivestack.io costs less than what some teams spend on their vector database alone.

That is not a knock on Pinecone or Qdrant. They solve real problems at very large scale. But for the vast majority of applications, the ones with under 50 million vectors, pgvector on properly configured Postgres is more than enough.

What I would do differently

If I were starting over, three things:
First, I would use HNSW from day one instead of wasting a week on IVFFlat.

Second, I would invest more in data quality upfront. Garbage embeddings give garbage results no matter how good your index is.
Third, I would set up monitoring for recall quality from the start. It is easy to ship something that seems to work and not notice that results are degrading as your dataset grows.

Why I built this as a product

After going through all of this, I realized that the hard part of pgvector is not pgvector itself. It is everything around it: provisioning, backups, HA, monitoring, index tuning, keeping Postgres updated. The vector search part is actually the easy part once the infrastructure is solid.

That is why I built Rivestack (https://rivestack.io). It is managed Postgres with pgvector already configured and optimized. You get a database in minutes with backups, metrics, SSL, and autoscaling. There is a free tier if you want to try it.

I am a one person team running this alongside my day job so I am not trying to compete with Supabase or Neon on features. I am just trying to be the simplest option for people who want pgvector to work without thinking about infrastructure.
Try the search

The semantic search over HN is free and will stay free. Go play with it: https://ask.rivestack.io
If you are building something with pgvector and have questions about tuning or architecture, drop them in the comments. I have made most of the mistakes already so I might be able to save you some time.

Forem: Yasser B.

PostgreSQL Connection Pooling with PgBouncer: A Complete Guide

Why PostgreSQL Connections Are Expensive

What PgBouncer Does

Installing PgBouncer

Configuring PgBouncer

The Three Pool Modes

Session Mode

Transaction Mode

Statement Mode

Choosing Your Mode

Pool Sizing

Monitoring PgBouncer

Common Pitfalls

When to Skip PgBouncer

Bottom Line

PostgreSQL Full Text Search: A Complete Guide

How PostgreSQL Full Text Search Works

Setting Up Full Text Search

Option 1: Generated column (recommended for PostgreSQL 12+)

Option 2: Trigger-maintained column

Indexing for Performance

Ranking Results

Snippet Generation

Handling User Input Safely

Full Text Search vs pgvector

Complete Production Setup

Try It on Rivestack

PostgreSQL High Availability: A Practical Guide for Production

What "High Availability" Means for PostgreSQL

Streaming Replication: The Foundation

Synchronous vs Asynchronous Replication

The Problem: PostgreSQL Doesn't Fail Over Itself

Patroni: The Industry Standard (and Why It's Hard)

pg_auto_failover: Simpler, More Opinionated

What Goes Wrong in Production

Split-Brain

Replication Lag at Failover Time

Application Not Reconnecting

Testing Your Failover

The Failover Time Question

When Managed PostgreSQL High Availability Makes Sense

What to Do If You're Starting From Scratch

The Bottom Line

How to Use pgvector with Python: A Complete Guide

What You Need Before You Start

Setting Up the Vector Extension

Using pgvector with psycopg3

Connecting and Registering the Vector Type

Creating a Table with a Vector Column

Inserting Embeddings

Querying by Similarity

Using pgvector with SQLAlchemy

Adding an Index for Production

HNSW parameters

Building a RAG Pipeline with pgvector and Python

Metadata Filtering

Connection Pooling

Async Support

Common Mistakes

Where to Go From Here

pgvector Cosine Distance: How <=> Actually Works

Introduction

What <=> Returns

Three Operators, Three Use Cases

The Normalization Shortcut

Index Compatibility

Picking Lists and probes

Wrapping Up

I built a managed pgvector service and here's what I learned about vector search performance

I indexed 30 million Hacker News posts into a single Postgres database. Here is what I learned about pgvector at scale.

What `<=>` Returns