LLM Best Practices

How to configure pgvector on Neon

Last updated 4 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Neon is serverless Postgres with pgvector available on every plan. This guide enables the extension, creates a table whose vector dimension matches your embedding model, adds an HNSW index, and runs a cosine-similarity query. For when pgvector is enough versus a dedicated vector database, see pinecone-vs-pgvector.

Prerequisites

  • A Neon project and its connection string (DATABASE_URL) from the dashboard.
  • Python 3.10 or newer with pip install "psycopg[binary]" pgvector.
  • An embedding model and its output dimension; see embeddings. This guide assumes 1024 (the default for Voyage’s voyage-4 family).
  • Free-plan limits change; at the time of writing the free plan allows 1 GB of storage per project (20 GB per account) and 100 CU-hours of compute per project per month, and scales to zero after 5 minutes of inactivity. Check Neon’s pricing page.

Steps

1. Enable the extension

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Extensions are enabled per database, not per project.

2. Create the table

CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  content   text NOT NULL,
  metadata  jsonb,
  embedding vector(1024) NOT NULL
);

The dimension must equal the model’s output. A mismatch raises different vector dimensions on insert. Indexes on the vector type support up to 2,000 dimensions; use halfvec for larger ones.

3. Insert vectors

import os, psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector
 
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    register_vector(conn)
    conn.execute(
        "INSERT INTO documents (content, metadata, embedding) VALUES (%s, %s::jsonb, %s)",
        ("The quick brown fox", '{"source": "test"}', Vector(embedding_vector)),  # list[float], length 1024
    )

For bulk loads, use cursor.copy() or executemany rather than one round trip per row.

4. Add an HNSW index

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

The defaults are m = 16 and ef_construction = 64; raise them only if recall is too low, at the cost of build time and index size. An HNSW index can be built on an empty table, because it has no training step. Match the operator class to the distance you query with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#> (negative inner product).

5. Query by similarity

-- $1 is the query embedding
SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

The ORDER BY ... LIMIT form is what lets the planner use the index.

6. Tune recall

SET hnsw.ef_search = 100;   -- default 40; higher improves recall and costs latency

Set it per session or transaction. With a WHERE filter, HNSW can return fewer rows than LIMIT; pgvector’s hnsw.iterative_scan setting widens the scan to compensate.

Verify it worked

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'documents';
EXPLAIN (ANALYZE) SELECT id FROM documents ORDER BY embedding <=> (SELECT embedding FROM documents LIMIT 1) LIMIT 5;

The plan should show an Index Scan using ..._embedding_idx. A small table may still plan a sequential scan, which is correct.

Common errors

  • type "vector" does not exist. The extension was enabled in a different database.
  • different vector dimensions. Column and model dimensions disagree; recreate the column.
  • The index is not used. The query lacks ORDER BY ... LIMIT, uses a different operator than the index’s operator class, or the table is tiny.
  • The first query after idle is slow. The free-plan compute scales to zero after five minutes and must wake up.

FAQ

Is pgvector available on Neon’s free plan?

Yes. Neon offers pgvector on every plan; at the time of writing the free plan allows 1 GB of storage per project and 100 CU-hours of compute per project per month.

Why do I get “type vector does not exist” on Neon?

The extension was enabled in a different database. Extensions are enabled per database, not per project.

Should I use HNSW or a different pgvector index?

Use HNSW with the defaults m = 16 and ef_construction = 64, and raise them only if recall is too low. It has no training step, so it can be built on an empty table.

Why is my pgvector index not being used?

The query lacks ORDER BY ... LIMIT, uses an operator that does not match the index’s operator class, or the table is small enough that a sequential scan is correct.

Why is the first query on Neon slow after idle time?

Free-plan compute scales to zero after 5 minutes of inactivity and must wake up on the next query.

Backlinks