pgvector Free code examples in C#, Python, Java and JavaScript

The extension goes in the database; the package teaches your driver the type. Both are needed — without the adapter a vector comes back as a string.

# The extension, in Postgres:
#   docker run -p 5432:5432 -e POSTGRES_PASSWORD=dev pgvector/pgvector:pg17

pip install pgvector psycopg[binary]

export DATABASE_URL="postgresql://postgres:dev@localhost:5432/postgres"

The vector column sits beside the ordinary ones — that is the whole point. The width is fixed at 1536 for text-embedding-3-small and cannot be changed without rewriting the table.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id          bigserial PRIMARY KEY,
    tenant_id   bigint      NOT NULL REFERENCES tenants(id),
    source      text        NOT NULL,
    page        int         NOT NULL,
    body        text        NOT NULL,
    deleted_at  timestamptz,
    -- 1536 = text-embedding-3-small. Fixed for the life of the column.
    embedding   vector(1536)
);

HNSW builds slower and queries faster; IVFFlat is the opposite and MUST be built after the data is loaded, because it clusters what it can see. The ops class has to match the operator you will query with.

-- Cosine distance, so vector_cosine_ops. Using vector_l2_ops here and
-- <=> in the query means the index is never used, with no warning.
CREATE INDEX ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- A partial index is often the right answer: skip soft-deleted rows
-- entirely rather than filtering them out after the search.
CREATE INDEX ON documents
    USING hnsw (embedding vector_cosine_ops)
    WHERE deleted_at IS NULL;

-- IVFFlat instead, if build time matters more than query latency.
-- Build it AFTER loading: it clusters the rows that exist.
-- CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
--     WITH (lists = 1000);

register_vector teaches psycopg the type, so a numpy array or a list goes in and comes back as one. Without it you are formatting strings by hand.

import os
import psycopg
from pgvector.psycopg import register_vector

with psycopg.connect(os.environ["DATABASE_URL"]) as connection:
    register_vector(connection)

    connection.execute(
        "INSERT INTO documents (tenant_id, source, page, body, embedding)"
        " VALUES (%s, %s, %s, %s, %s)",
        (1, "handbook.pdf", 4, "The pump stalled.", embedding),
    )

    rows = connection.execute(
        "SELECT body, embedding <=> %s AS distance"
        "  FROM documents"
        " WHERE tenant_id = %s AND deleted_at IS NULL"
        " ORDER BY distance"
        " LIMIT 5",
        (query_embedding, 1),
    ).fetchall()

    for body, distance in rows:
        print(round(distance, 4), body)

THIS IS THE REASON TO BE HERE. Neighbours, filtered by a permission table, enriched with the author's name, in one query and one transaction. There is no second store to keep in step.

rows = connection.execute(
    """
    SELECT d.body,
           u.display_name AS author,
           d.embedding <=> %(query)s AS distance
      FROM documents d
      JOIN users u        ON u.id = d.author_id
      JOIN permissions p  ON p.document_id = d.id
     WHERE p.user_id = %(viewer)s        -- authorisation, not a filter
       AND d.deleted_at IS NULL
     ORDER BY distance
     LIMIT 5
    """,
    {"query": query_embedding, "viewer": viewer_id},
).fetchall()

# A dedicated vector database would need the permission check to
# happen somewhere else, against data it does not hold.

pgvector-java adds the PGvector type on top of the ordinary JDBC driver. Nothing else changes about how you talk to Postgres.

# Gradle
implementation("org.postgresql:postgresql:42.7.4")
implementation("com.pgvector:pgvector:0.1.6")

export DATABASE_URL="jdbc:postgresql://localhost:5432/postgres?user=postgres&password=dev"

PGvector.addVectorType(connection) must run on each connection before a vector is bound. In a pool, that belongs in the connection-init hook.

import com.pgvector.PGvector;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Search {
    public static void main(String[] args) throws Exception {
        String url = System.getenv("DATABASE_URL");
        if (url == null) throw new IllegalStateException("DATABASE_URL is not set.");

        try (Connection connection = DriverManager.getConnection(url)) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("CREATE EXTENSION IF NOT EXISTS vector");
            }

            // Per connection, before binding any vector parameter.
            // The column is vector(1536): text-embedding-3-small.
            PGvector.addVectorType(connection);
        }
    }
}

PGvector wraps a float[] — 1536 wide here, matching text-embedding-3-small. setObject binds it once the type is registered.

import java.sql.PreparedStatement;
import java.sql.ResultSet;

try (PreparedStatement insert = connection.prepareStatement(
        "INSERT INTO documents (tenant_id, source, page, body, embedding)"
        + " VALUES (?, ?, ?, ?, ?)")) {
    insert.setLong(1, 1L);
    insert.setString(2, "handbook.pdf");
    insert.setInt(3, 4);
    insert.setString(4, "The pump stalled.");
    insert.setObject(5, new PGvector(embedding));
    insert.executeUpdate();
}

try (PreparedStatement query = connection.prepareStatement(
        "SELECT body, embedding <=> ? AS distance FROM documents"
        + " WHERE tenant_id = ? AND deleted_at IS NULL"
        + " ORDER BY distance LIMIT 5")) {
    query.setObject(1, new PGvector(queryEmbedding));
    query.setLong(2, 1L);

    try (ResultSet rows = query.executeQuery()) {
        while (rows.next()) {
            System.out.printf("%.4f  %s%n",
                rows.getDouble("distance"), rows.getString("body"));
        }
    }
}

One statement: nearest neighbours, restricted by a permission table, with the author's name attached. That is the trade pgvector makes — a little less raw speed for everything living in one transactional database.

String sql = """
    SELECT d.body,
           u.display_name AS author,
           d.embedding <=> ? AS distance
      FROM documents d
      JOIN users u       ON u.id = d.author_id
      JOIN permissions p ON p.document_id = d.id
     WHERE p.user_id = ?
       AND d.deleted_at IS NULL
     ORDER BY distance
     LIMIT 5
    """;

try (PreparedStatement query = connection.prepareStatement(sql)) {
    query.setObject(1, new PGvector(queryEmbedding));
    query.setLong(2, viewerId);

    try (ResultSet rows = query.executeQuery()) {
        while (rows.next()) {
            System.out.println(rows.getString("author") + ": " + rows.getString("body"));
        }
    }
}

pgvector/pg registers the type with node-postgres so vectors round-trip as arrays rather than strings.

npm install pg pgvector

export DATABASE_URL="postgresql://postgres:dev@localhost:5432/postgres"

Do this on the POOL, not on a connection — otherwise only the first checked-out client knows what a vector is and the rest return strings.

import pg from "pg";
import pgvector from "pgvector/pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

// Once per pool, on every new connection.
pool.on("connect", async (client) => {
  await pgvector.registerTypes(client);
});

// The documents.embedding column is vector(1536) — the width of
// text-embedding-3-small, and fixed for the life of the column.
await pool.query("CREATE EXTENSION IF NOT EXISTS vector");

toSql() formats the array the way Postgres wants it. Passing a bare JavaScript array is a type error that mentions neither vectors nor the column.

await pool.query(
  `INSERT INTO documents (tenant_id, source, page, body, embedding)
   VALUES ($1, $2, $3, $4, $5)`,
  [1, "handbook.pdf", 4, "The pump stalled.", pgvector.toSql(embedding)],
);

const { rows } = await pool.query(
  `SELECT body, embedding <=> $1 AS distance
     FROM documents
    WHERE tenant_id = $2 AND deleted_at IS NULL
    ORDER BY distance
    LIMIT 5`,
  [pgvector.toSql(queryEmbedding), 1],
);

for (const row of rows) {
  console.log(Number(row.distance).toFixed(4), row.body);
}

A mismatched operator class is a silent sequential scan. EXPLAIN is the only way to find out, and it is worth doing once per query shape rather than trusting the CREATE INDEX succeeded.

const { rows: plan } = await pool.query(
  `EXPLAIN ANALYZE
   SELECT body FROM documents
    WHERE deleted_at IS NULL
    ORDER BY embedding <=> $1
    LIMIT 5`,
  [pgvector.toSql(queryEmbedding)],
);

const text = plan.map((row) => row["QUERY PLAN"]).join("\n");

// "Index Scan using documents_embedding_idx" — good.
// "Seq Scan on documents"                    — the ops class does not
//                                              match your operator.
if (text.includes("Seq Scan")) {
  console.warn("pgvector index not used:\n" + text);
}

Neighbours, filtered by a permission table, in one query. That is what having the vectors inside the transactional database buys — and it is the only argument that matters.

const { rows } = await pool.query(
  `SELECT d.body,
          u.display_name AS author,
          d.embedding <=> $1 AS distance
     FROM documents d
     JOIN users u       ON u.id = d.author_id
     JOIN permissions p ON p.document_id = d.id
    WHERE p.user_id = $2
      AND d.deleted_at IS NULL
    ORDER BY distance
    LIMIT 5`,
  [pgvector.toSql(queryEmbedding), viewerId],
);