How to Install PostgreSQL with pgvector Step by Step

Tested with PostgreSQL 18.6 · pgvector 0.8.6 · PGDG APT · Docker · verified 2026-09-16

Updated: 2026-09-16

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 of PostgreSQL 18.6 and pgvector 0.8.6 on Debian 12 and 13 or Ubuntu 22.04, 24.04 and 26.04, with a Docker alternative. It justifies each decision and works with the tools you already know: pg_dump, pg_basebackup, pg_stat_statements, and the familiar replica machinery.

What has changed since pgvector 0.6 and PostgreSQL 16

This guide was written in February 2024 against PostgreSQL 16 and pgvector 0.6. I revised it on 16 September 2026: I re-ran every step on Debian 13 and the install steps on Ubuntu 24.04, both in arm64 containers. The current pgvector release is 0.8.6, from 29 July 2026 (CHANGELOG^[3]), and the current PostgreSQL release is 18.6, published on 13 August 2026 (announcement^[4]). Version 16 stays supported until 9 November 2028 per the versioning policy^[5], and 19 is at beta 3.

The steps now use PostgreSQL 18. PGDG ships postgresql-18 18.6 and postgresql-18-pgvector 0.8.6 for Debian 12 and 13 and for Ubuntu 22.04, 24.04 and 26.04, on amd64 and arm64; I checked the repository indexes. If you stay on 16, swap the number in the package names. This is what has changed:

• New types (0.7.0, April 2024): halfvec (half precision, up to 4,000 indexable dimensions), sparsevec (sparse vectors) and indexing of the bit type, along with binary_quantize, subvector, l2_normalize, and the L1, Hamming and Jaccard distances. An HNSW index over (embedding::halfvec(1536)) halfvec_l2_ops takes half the space.
• Iterative index scans (0.8.0, October 2024): this directly affects the WHERE tenant_id query in this guide. With approximate indexes the filter is applied after the index is scanned, so a selective condition can return fewer rows than requested. SET hnsw.iterative_scan = relaxed_order (or strict_order, and ivfflat.iterative_scan for IVFFlat) makes the index keep scanning until enough results are found. 0.8.0 also improved cost estimation so the planner chooses better between index and filter, and dropped PostgreSQL 12.
• Security fix for CVE-2026-3172 (0.8.2, 25 February 2026): a buffer overflow in parallel HNSW index builds lets a user who can create or reindex an index read data from other relations or crash the server. It affects 0.6.0 through 0.8.1, according to the project advisory^[6].
• PostgreSQL 18: supported since 0.8.1 (September 2025); 0.8.2 fixed the EXPLAIN output and 0.8.3 a performance regression of Hamming and Jaccard distances on 18.
• 2026 fixes that matter in production: 0.8.3 (17 June) fixed a possible HNSW index corruption during VACUUM, and 0.8.4 (30 June) the “hnsw graph not repaired” error and a possible error with inserts during that same vacuum. 0.8.4 also stopped IVFFlat builds from exceeding maintenance_work_mem, and 0.8.5 reduced their memory on small tables. 0.8.6 fixes a buffer overflow in IVFFlat builds on 32-bit systems and the memory usage of IVFFlat scans in nested loops. Coming from 0.6, upgrade the package and run ALTER EXTENSION vector UPDATE; in each database.
• PostgreSQL 18 changes that touch this guide: initdb now enables data checksums by default, and pg_upgrade requires matching checksum settings between clusters, as the FAQ shows. The Docker image also stores data in /var/lib/postgresql/18/docker and mounts the volume at /var/lib/postgresql.

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. If your queries mix semantic search with relational filters and the index fits in the server’s memory, pgvector is the economically correct default.

The honest trade-off: pgvector doesn’t compete on raw performance with engines written specifically for vectors, nor on features like product quantisation. It also does not filter inside the index graph, only after scanning it. 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, and with pgvector the gap matters. Ubuntu 24.04 offers postgresql-16-pgvector 0.6.0 in universe and Debian 13 ships postgresql-17-pgvector 0.8.0, both inside the CVE-2026-3172 range. The Debian security tracker^[7] lists the trixie package as vulnerable, and the Ubuntu tracker^[8] lists the noble one as needing evaluation.

The project’s own PGDG repository ships PostgreSQL 18.6 and pgvector 0.8.6. This is the manual setup documented on the PGDG wiki^[9], with the sources file in deb822 format:

sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

. /etc/os-release
sudo tee /etc/apt/sources.list.d/pgdg.sources <<EOF
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: $VERSION_CODENAME-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

sudo apt update
sudo apt install -y postgresql-18

The block stores the repository key, writes the sources file with your distribution’s codename (trixie, bookworm, noble…) and architecture, and installs the server. The previous version of this guide relied on lsb_release, which the debian:trixie image does not include, and on an echo split across two lines that makes APT answer E: Malformed entry 1. If you prefer a script, the wiki offers sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh once postgresql-common is installed; without -y it stops to ask you to press Enter.

On a server with systemd, the cluster starts on its own as postgresql@18-main.service. In my containers, without systemd, pg_lsclusters showed 18 main stopped in /var/lib/postgresql/18/main, and I started it with sudo pg_ctlcluster 18 main start.

Step 2: Install pgvector

The postgresql-18-pgvector package from PGDG ships the latest released version. Check the candidate and install it:

apt-cache policy postgresql-18-pgvector
sudo apt install -y postgresql-18-pgvector

On Debian 13, apt-cache policy returned this; on Ubuntu 24.04 the candidate was 0.8.6-1.pgdg24.04+1:

postgresql-18-pgvector:
  Installed: (none)
  Candidate: 0.8.6-1.pgdg13+1

PGDG has kept the last three versions of each package since 23 April 2026, so you can pin one with sudo apt install postgresql-18-pgvector=0.8.6-1.pgdg13+1.

If you need to compile from source, for example to apply a patch, install the build tools and the server headers first:

sudo apt install -y build-essential git postgresql-server-dev-18
git clone --branch v0.8.6 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

Without those dependencies, the first make fails with make: command not found. postgresql-server-dev-18 also pulls in clang-19, which make uses to generate the bitcode for PostgreSQL’s JIT compiler.

Unlike extensions that hook into the server at startup, pgvector doesn’t require shared_preload_libraries. It’s per-database, not cluster-wide.

Alternative: pgvector in Docker with PostgreSQL 18

If you prefer containers, the pgvector/pgvector image adds the extension to the official PostgreSQL image. The 0.8.6-pg18-trixie tag points to the same digest as pg18-trixie (both updated on 13 August 2026 on Docker Hub). This is the compose.yaml I used:

services:
  db:
    image: pgvector/pgvector:0.8.6-pg18-trixie
    restart: unless-stopped
    shm_size: 1g
    environment:
      POSTGRES_USER: ragapp
      POSTGRES_PASSWORD: strong_password_here
      POSTGRES_DB: ragdb
    volumes:
      - pgdata:/var/lib/postgresql
    ports:
      - "127.0.0.1:5432:5432"

volumes:
  pgdata:

Look at the volume. Since PostgreSQL 18, the image stores data in /var/lib/postgresql/18/docker and declares the volume at /var/lib/postgresql, not /var/lib/postgresql/data (official image change^[10]). If you reuse an old compose file with the old path, the container does not start: in my test it exited with code 1 and this message:

Error: in 18+, these Docker images are configured to store database data in a
       format which is compatible with "pg_ctlcluster" (specifically, using
       major-version-specific directory names).

shm_size matters too. With Docker’s default 64 MB /dev/shm and maintenance_work_mem at 2 GB, a parallel HNSW build failed with could not resize shared memory segment and No space left on device. With 4 GB it finished without error, and the pgvector documentation asks for at least as much as maintenance_work_mem.

With this configuration, SHOW data_directory returned /var/lib/postgresql/18/docker, and a test table survived docker compose down and up -d. In the image, POSTGRES_USER is a superuser, so ragapp can create the extension itself; if you want an unprivileged application role, create it separately as in step 3. For the remaining details, see how to install PostgreSQL with Docker.

Step 3: User, Database, and Extension

Never use the postgres role for the application. Create a dedicated role from sudo -u postgres psql:

-- 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;

-- Verify installation
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

On PostgreSQL 18.6 with pgvector 0.8.6, the check returned:

 extname | extversion
---------+------------
 vector  | 0.8.6
(1 row)

The query against pg_extension is the first check that the binary and the catalogue are aligned. The postgres role creates the extension because pgvector is not marked as trusted: when I tried as ragapp, PostgreSQL answered permission denied to create extension "vector", with the hint Must be superuser to create this extension.

The previous version of this guide added GRANT ALL ON SCHEMA public TO ragapp, which is no longer needed. Since PostgreSQL 15 the public schema belongs to pg_database_owner (version 15 release notes^[11]), so the owner of ragdb can create tables in it: I checked as ragapp without that GRANT.

Step 4: Minimal Production Configuration

The defaults PostgreSQL ships with are conservative: 18 starts with shared_buffers = 128MB. For a machine with 16 GB dedicated to the database, put your settings in a conf.d file, which the Debian and Ubuntu postgresql.conf includes through include_dir:

sudo tee /etc/postgresql/18/main/conf.d/ragdb.conf <<'EOF'
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 512MB
wal_level = replica
max_wal_size = 4GB
max_connections = 100
EOF
sudo pg_ctlcluster 18 main restart

Run as root, pg_ctlcluster hands the action to systemctl when systemd is present, according to its manual page^[12]. After the restart, SHOW shared_buffers; returned 4GB and SHOW maintenance_work_mem; returned 512MB.

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. If the graph does not fit, pgvector warns with hnsw graph no longer fits into maintenance_work_mem and, according to its documentation, the build slows down from that point. For a large load, raise it only in the session that builds the index with SET maintenance_work_mem = '2GB';.

The pgvector documentation also suggests raising max_parallel_maintenance_workers (2 by default) to build in parallel. In my test with 99,000 embeddings that was not enough, because the vectors live in TOAST and the main table took 7 MB. The build did not request shared memory for parallel workers until I set ALTER TABLE documents SET (parallel_workers = 4) on that table.

Step 5: IVFFlat or HNSW

pgvector offers two approximate index structures, and I measured both on 99,000 real embeddings with 1,536 dimensions:

IVFFlat: older, partitions space into lists via k-means at build time, so create it after loading representative data. It uses less memory and builds faster: with four parallel workers it took 4.3 s, against 23.0 s for HNSW. The machine had 18 cores and a load average between 3.5 and 4.3.

HNSW: added in 0.5.0 (August 2023), builds a hierarchical graph with a better recall-latency trade-off. It has no training step, so it can be created on an empty table. In the same test it reached 95.1% recall@10 at a 1.15 ms median, against 92.8% and 8.7 ms for IVFFlat with lists = 100 and probes = 10.

The recommendation, in 2024 and still in 2026, is to start with HNSW with defaults (m = 16, ef_construction = 64). If the dataset exceeds tens of millions of rows or the maintenance window is tight, IVFFlat can be the better fit. Set lists to rows / 1000 up to one million rows and to the square root of the row count above that. The full measurement table is in pgvector: Semantic Search Without Leaving Postgres.

Step 6: Typical Schema and Distance Operators

A canonical RAG schema, with a B-tree index for the tenant filter:

CREATE TABLE docs (
    id          bigserial PRIMARY KEY,
    tenant_id   integer NOT NULL,
    content     text,
    metadata    jsonb,
    embedding   vector(1536)  -- text-embedding-3-small
);

-- B-tree for the tenant filter, GIN for metadata
CREATE INDEX ON docs (tenant_id);
CREATE INDEX ON docs USING gin (metadata);

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

The dimension depends on the model: OpenAI’s text-embedding-3-small returns 1,536 by default. For searches:

SET hnsw.ef_search = 100;
SET hnsw.iterative_scan = relaxed_order;

-- 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;

relaxed_order matters here. I tested it with 20,000 rows across 10 tenants, without the B-tree index and with HNSW forced. Without iterative scans it returned 4 of 10 rows with ef_search = 40 and 9 with ef_search = 100; with relaxed_order, all 10. That mode can return rows slightly out of distance order, so use strict_order when exact ordering matters.

The usual operators are three: <=> for cosine, <-> for Euclidean, <#> for negative inner product; 0.7.0 added <+> (L1) and, for bit vectors, <~> and <%>. With OpenAI models (normalised vectors), use cosine. Mixing operators with non-normalised vectors is the most common source of strange results.

PostgreSQL logo (Image: Daniel Lundin, BSD, via Wikimedia Commons)

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^[13] to Prometheus. Watch three key signals:

• 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.

• Recall: compare the index results with exact search from time to time (SET LOCAL enable_indexscan = off inside a transaction), as the pgvector documentation suggests.

With HNSW, VACUUM has to repair the graph after deletes and updates, and the pgvector documentation warns that it can take a while. If it does, reindex first and run VACUUM afterwards:

REINDEX INDEX CONCURRENTLY docs_embedding_idx;
VACUUM docs;

0.8.3 fixed a possible index corruption in that VACUUM path, and 0.8.4 the “hnsw graph not repaired” error. Do not run anything older than 0.8.4.

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

Final Verification

Check that everything works with a test vector:

-- Insert a test vector (1536 dims with random values)
INSERT INTO docs (tenant_id, content, embedding)
VALUES (1, 'Test document',
        (SELECT array_agg(random())::vector(1536)
         FROM generate_series(1, 1536)));

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

If the query returns the document without error and the plan shows the HNSW index, the installation is correct. Prefix the search with EXPLAIN (COSTS OFF) to see the plan; this is the one I got on PostgreSQL 18.6 with a single row in the table:

 Limit
   InitPlan 1
     ->  Limit
           ->  Seq Scan on docs docs_1
   ->  Index Scan using docs_embedding_idx on docs
         Order By: (embedding <=> (InitPlan 1).col1)

Frequently asked questions

Does pgvector work with read-only replicas and pg_basebackup?

Yes. pgvector writes to the WAL, so it replicates exactly like any other table or index and supports point-in-time recovery. A physical replica built with pg_basebackup includes the HNSW or IVFFlat index with no extra steps. Make sure the replica has the postgresql-18-pgvector package (or the one for your version) installed, because the extension’s binary lives outside the WAL stream.

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

Not with pg_upgrade in --link mode: I tested 16.15 to 18.6 with pgvector 0.8.6 and pg_upgradecluster -m link, and the HNSW index file kept its inode and stayed in use. Install postgresql-18-pgvector first; if it ships a newer version, pg_upgrade reports it and writes a script to update the extension. Because 18 enables data checksums by default, a pg_upgrade --check against a 16 cluster without them failed with old cluster does not use data checksums but the new one does, while pg_upgradecluster created the new cluster without them and finished cleanly. 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, the latency budget and whether the index fits in memory. For scale, the HNSW index for 99,000 vectors with 1,536 dimensions took 773 MB in my test, about 7.6 GB per million vectors. If that figure does not fit in the server’s RAM, try halfvec or binary quantisation with re-ranking first, which the pgvector documentation proposes for scaling.

Can a table have several vector columns with different dimensions?

Yes. vector(n) fixes the dimension per column, not per table or per database. You can have one column for one model’s embeddings and another for a different model, each with its own HNSW or IVFFlat index. If you need different dimensions in the same column, declare it as vector without a size and index each dimension with a partial expression index, as the pgvector documentation explains.

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. Five choices mark the difference between a deployment that ages gracefully and one that demands manual intervention every few weeks. They are the PGDG repository with pgvector 0.8.4 or later, a dedicated role, the extension created in the right database and the HNSW index built at the right moment. The fifth is a maintenance_work_mem sized for that build.

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. The apparent performance savings get 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 is itself an argument for postponing the migration.

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

Sources

1. PostgreSQL
2. pgvector
3. CHANGELOG
4. announcement
5. versioning policy
6. project advisory
7. Debian security tracker
8. Ubuntu tracker
9. PGDG wiki
10. official image change
11. version 15 release notes
12. manual page
13. postgres_exporter
14. PostgreSQL 18 — official documentation
15. PostgreSQL — version 18 release notes
16. PostgreSQL — pg_upgrade documentation

Route: Local LLMs: run models on your own hardware