Get Started with pgvector for Similarity Search in PostgreSQL

1. 1
   Enable the pgvector Extension

   First, connect to your PostgreSQL database and run the `CREATE EXTENSION` command. This one-time setup loads the pgvector functionality, adding the `vector` data type and associated search operators and functions to your database.
2. 2
   Create a Table with a Vector Column

   Define a table to store your items. Add a column of type `vector(n)`, where `n` is the number of dimensions in your embeddings. For example, OpenAI's `text-embedding-ada-002` uses 1536 dimensions.
3. 3
   Insert Embeddings

   Insert your data and its corresponding vector embeddings into the table. Embeddings are passed as a string representation of a float array. Ensure the dimension matches the one defined in your table schema.
4. 4
   Query for Nearest Neighbors

   Use vector operators to find the most similar items. The cosine distance operator (`<=>`) is excellent for text embeddings. This query finds the 5 items closest to the given query vector.
5. 5
   Create an Index for Performance

   For large datasets, create an index to enable fast Approximate Nearest Neighbor (ANN) search. This dramatically speeds up queries. For IVFFlat, a good starting point for `lists` is `number_of_rows / 1000`.

import psycopg
import numpy as np
from psycopg.types.json import Jsonb
from pgvector.psycopg import register_vector

# This script demonstrates a full cycle: setup, insert, and query.
#
# Prerequisites:
# 1. Install libraries: pip install psycopg "psycopg[binary]" pgvector numpy
# 2. Start a PostgreSQL instance with pgvector. For Docker:
#    docker run --name pgvector-demo -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d ankane/pgvector

# --- 1. Connect to PostgreSQL ---
conn_string = "postgresql://postgres:mysecretpassword@localhost:5432/postgres"

try:
    with psycopg.connect(conn_string) as conn:
        print("Connection successful!")
        # --- 2. Setup Database ---
        conn.execute('CREATE EXTENSION IF NOT EXISTS vector')
        register_vector(conn)
        conn.execute('DROP TABLE IF EXISTS items')
        # Use a 3-dimensional vector for simplicity
        conn.execute('CREATE TABLE items (id serial PRIMARY KEY, embedding vector(3))')
        print("Database setup complete.")

        # --- 3. Insert Data ---
        embeddings = [
            [1, 1, 1],
            [2, 2, 2],
            [1, 1, 2]
        ]
        for embedding in embeddings:
            conn.execute('INSERT INTO items (embedding) VALUES (%s)', (np.array(embedding),))
        print(f"Inserted {len(embeddings)} items.")

        # --- 4. Query for Nearest Neighbors ---
        query_embedding = np.array([1, 1, 1])
        
        # Use the cursor as a context manager
        with conn.cursor() as cur:
            # The `<->` operator calculates the L2 distance
            cur.execute('SELECT * FROM items ORDER BY embedding <-> %s LIMIT 5', (query_embedding,))
            results = cur.fetchall()
            
            print("\n--- Query Results ---")
            print(f"Finding items closest to {query_embedding.tolist()}")
            for row in results:
                item_id, embedding_val = row
                # The registered type handler automatically converts the string from postgres to a numpy array
                print(f"Item ID: {item_id}, Embedding: {embedding_val.tolist()}")

except psycopg.OperationalError as e:
    print(f"Connection failed: {e}")
    print("Please ensure PostgreSQL is running and accessible.")