How to Install PostgreSQL with pgvector Step by Step

Updated: 2026-07-12

Setting up PostgreSQL^[1] with pgvector^[2] is the most sensible way to start a retrieval-augmented generation project without introducing a new database into the team’s stack. This guide describes a reproducible install on Debian 12 or Ubuntu 22.04/24.04 with PostgreSQL 16 and pgvector 0.6, justifying each decision and operating with the same tools already known: pg_dump, pg_basebackup, pg_stat_statements, and the familiar replica machinery.

Why pgvector Instead of a Dedicated Vector Database

The temptation to reach for Pinecone, Weaviate, Milvus, or Qdrant is natural. All are competent, but each introduces a new system that must be deployed, backed up, monitored, and learned. For volumes below fifty million vectors with workloads mixing semantic search and relational filters, pgvector is usually the economically correct decision.

The honest trade-off: pgvector doesn’t compete on raw performance with engines written specifically for vectors, nor on advanced features like product quantisation or native hybrid filtering. If the application lives and dies by latency over hundreds of millions of documents, it deserves a fresh evaluation. Below that threshold, running a single system the team already knows pays dividends every time a backup has to be restored at three in the morning.

For guidance on when to scale to a dedicated vector database and how pgvector behaves with HNSW in production, see pgvector in 2024: HNSW and real-world scaling.

Step 1: Official PGDG Repository

PostgreSQL versions shipped by distributions lag behind the stable branch. The PGDG repository offers PostgreSQL 16 alongside an up-to-date postgresql-16-pgvector package:

# Install transport dependencies
sudo apt install -y curl ca-certificates gnupg

# Download and install the PGDG key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc 
  | sudo gpg --dearmor -o /etc/apt/keyrings/pgdg.gpg

# Add the repository
echo "deb [signed-by=/etc/apt/keyrings/pgdg.gpg] 
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" 
  | sudo tee /etc/apt/sources.list.d/pgdg.list

# Install PostgreSQL 16
sudo apt update && sudo apt install -y postgresql-16 postgresql-client-16

The service starts automatically as postgresql@16-main.service.

Step 2: Install pgvector

The postgresql-16-pgvector package from PGDG ships the latest released version:

sudo apt install -y postgresql-16-pgvector

If you need a specific build or want to apply a patch, you can also compile from source:

git clone https://github.com/pgvector/pgvector.git
cd pgvector
make && sudo make install

Unlike many extensions, pgvector doesn’t require shared_preload_libraries. It’s per-database, not cluster-wide.

Step 3: User, Database, and Extension

Never use the postgres role for the application. Create a dedicated role:

-- Connect as postgres
CREATE ROLE ragapp WITH LOGIN PASSWORD 'strong_password_here';
CREATE DATABASE ragdb OWNER ragapp;

-- Connect to ragdb
c ragdb
CREATE EXTENSION IF NOT EXISTS vector;
GRANT ALL ON SCHEMA public TO ragapp;

-- Verify installation
SELECT * FROM pg_extension WHERE extname = 'vector';
-- Should return extversion = '0.6.0'

The query against pg_extension is the first check that the binary and the catalog are aligned.

Step 4: Minimal Production Configuration

The defaults PostgreSQL ships with are conservative. Adjust postgresql.conf for a machine with 16 GB dedicated to the database:

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 512MB
wal_level = replica
max_wal_size = 4GB
max_connections = 100

The practical rule sets shared_buffers at around 25% of memory and effective_cache_size around 75%. Keep work_mem moderate because it multiplies by connections and by operations within each query: 64 MB × 100 connections × 2 operations is 12 GB in theory, so measure before raising it.

maintenance_work_mem matters especially because CREATE INDEX on HNSW consumes it generously. With 512 MB the index builds in reasonable time; with 64 MB it can take hours.

Step 5: IVFFlat or HNSW

IVFFlat: older, partitions space into lists via k-means. Requires ANALYZE after loading representative data. Low memory, reasonable for a few million vectors.

HNSW: hierarchical graph, better recall-latency trade-off, incremental inserts without quality loss. Higher build time and memory. Stable since 0.6.

The recommendation is to start with HNSW with defaults (m = 16, ef_construction = 64). In most cases quality beats IVFFlat without tuning. If the dataset exceeds tens of millions of rows or the maintenance window is tight, IVFFlat with lists set to the square root of row count is appropriate.

Step 6: Typical Schema and Distance Operators

CREATE TABLE docs (
    id          bigserial PRIMARY KEY,
    tenant_id   integer NOT NULL,
    content     text,
    metadata    jsonb,
    embedding   vector(1536)  -- dimension depends on the model; OpenAI ada-002 / text-embedding-3-small
);

-- GIN index for metadata filters
CREATE INDEX ON docs USING gin (metadata);

-- HNSW index for vector search
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

For searches:

SET hnsw.ef_search = 100;

-- Semantic search with a tenant filter
SELECT id, content, embedding <=> $1 AS distance
FROM docs
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

Three operators available: <=> for cosine, <-> for Euclidean, <#> for negative inner product. With OpenAI models (normalised vectors), use cosine. Mixing operators with non-normalised vectors is the most common source of strange results.

PostgreSQL table schema with a vector-type column and an HNSW index; semantic-search flow for RAG on pgvector

Step 7: Access, Backup, and Observability

Remote access: by default the server listens on localhost. If the application lives on another host, open listen_addresses to the strictly necessary private interface and enforce scram-sha-256 in pg_hba.conf. Never expose PostgreSQL directly to the internet.

Backup: pg_dump -Fc -Z9 is enough on day one. When the database grows, adopt pgbackrest or restic with incremental rotations and rehearse restores regularly. A backup without a tested restore is a hope, not a guarantee.

Minimum observability:

-- Enable query statistics
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- Restart, then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Export metrics with postgres_exporter^[3] to Prometheus. Watch two key metrics:

• Buffer cache hit ratio: must stay above 99%. If HNSW falls out of RAM, performance drops off a cliff.

• pg_stat_user_indexes.idx_scan: confirms the HNSW index is actually being used.

HNSW indexes degrade if autovacuum doesn’t keep pace with deletes and updates.

For a broader observability perspective across modern stacks, see Grafana and the observability stack or OpenTelemetry for unification.

Final Verification

INSERT INTO docs (tenant_id, content, embedding)
VALUES (1, 'Test document',
        (SELECT array_agg(random())::vector(1536) FROM generate_series(1,1536)));

SELECT id, content FROM docs
ORDER BY embedding <=> (SELECT embedding FROM docs LIMIT 1)
LIMIT 5;

If the query returns results without error and EXPLAIN ANALYZE shows the HNSW index, the installation is correct.

Frequently asked questions

Does pgvector work with read-only replicas and pg_basebackup?

Yes. pgvector is just another extension in the PostgreSQL catalogue: it replicates exactly like any other table or index. A physical replica built with pg_basebackup includes the HNSW or IVFFlat index with no extra steps, but make sure the replica has the postgresql-16-pgvector package installed, because the extension’s binary lives outside the WAL stream.

Do you have to rebuild the HNSW index when upgrading PostgreSQL?

It depends on how you migrate. pg_upgrade with --link preserves the data files and does not rebuild indexes, as long as the pgvector version installed on the target cluster matches the source. A pg_dump/pg_restore, on the other hand, rebuilds HNSW from scratch, which can take a while depending on vector volume and the available maintenance_work_mem.

How many vectors can pgvector handle before a dedicated database pays off?

There’s no magic number: it depends on the query pattern and the latency budget. As a rough guide, below fifty million vectors with mixed relational filters pgvector is usually enough; above that, with aggressive latency requirements, it’s worth evaluating alternatives.

Can a table have several vector columns with different dimensions?

Yes. vector(n) fixes the dimension per column, not per table or per database. It’s common to have one column for one model’s embeddings and another for a different model, each with its own HNSW or IVFFlat index.

Does pgvector carry a license cost on top of PostgreSQL?

No. pgvector ships under the PostgreSQL License, a permissive grant equivalent to BSD/MIT, same as the engine itself. There is no license cost; the real cost is operational: CPU, memory, and storage to build and maintain the indexes.

Conclusion

Installing PostgreSQL with pgvector is not complicated, but each decision matters more than it looks. Picking the PGDG repository, creating a dedicated role, running the extension in the right database, building the HNSW index at the right moment, and sizing maintenance_work_mem properly mark the difference between a deployment that ages gracefully and one that demands manual intervention every few weeks.

The argument for pgvector over dedicated alternatives is operational more than technical. Few organisations genuinely benefit from introducing a vector-specific database when they already run PostgreSQL comfortably. Most discover that apparent performance savings are spent, and then some, on duplicated backup, replication, and monitoring complexity. When the time to migrate to a specialised engine arrives, the starting point will be a production database, which usually makes postponing the migration the right call.

This article is also available in Spanish: Cómo instalar PostgreSQL con pgvector paso a paso.

Sources

1. PostgreSQL
2. pgvector
3. postgres_exporter
4. PostgreSQL — official documentation
5. PGDG — official PostgreSQL APT repository

Route: Local LLMs: run models on your own hardware