When Your Database Needs to Think: Adding AI Search with pgvector

Your Database is Smarter Than You Think

For years, if you wanted to search your data, you had two main options: full-text search (think LIKE %query% or specialized indexing) or exact matches. They work, but they often miss the mark on intent. Searching for "red car" might not show results for "crimson automobile," even though they're semantically similar. This gap is where semantic search shines, letting users find what they mean, not just what they type.

Traditionally, adding semantic search meant bolting on a vector database, an external service, or a whole new search stack. It's a significant architectural decision, adding operational overhead and data synchronization challenges. But what if your existing relational database, the one already holding all your critical data, could just… do it? This is exactly what extensions like pgvector enable for Postgres.

Why pgvector Changes the Game

Pgvector lets you store embeddings directly within your Postgres database. Embeddings are numerical representations of text, images, or other data, capturing their semantic meaning. When you have these embeddings, you can perform vector similarity search, finding items whose embeddings are 'close' to a query's embedding. It's like giving your database a brain for understanding context.

This is a big deal because it simplifies your stack. No need for a separate vector database cluster to manage, no more syncing data between your transactional database and your search index. Your application logic stays cohesive, and your data remains in one place, under one ACID-compliant roof. It democratizes AI-powered search, making it accessible for applications that might not justify a full-blown specialized vector store.

Getting Started with pgvector

First, you need Postgres and the pgvector extension. Most cloud providers offer pgvector as an option for their Postgres services. If you're running locally with Docker, it's straightforward to set up:

FROM postgres:16-alpine

# Install pgvector extension
RUN apk add --no-cache postgresql-dev build-base git
RUN git clone https://github.com/pgvector/pgvector.git /tmp/pgvector
WORKDIR /tmp/pgvector
RUN make && make install

# You might need to configure shared_preload_libraries in postgresql.conf
# For simplicity, we'll enable it inside the database later

Once your Postgres instance is running, you'll enable the extension:

CREATE EXTENSION vector;

Now, let's say we have a products table and we want to add semantic search capabilities to its descriptions. We'll add a description_embedding column of type vector.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2),
    description_embedding vector(1536) -- OpenAI's ada-002 model uses 1536 dimensions
);

The vector(1536) specifies that this column will store vectors with 1536 dimensions. This number depends on the embedding model you choose. OpenAI's text-embedding-ada-002 is a common choice, producing 1536-dimensional vectors.

Generating and Storing Embeddings

The core of semantic search is generating these embeddings. You'll typically use an external API or a local model for this. Let's use OpenAI's API as an example, integrating it with a simple Node.js application.

First, install the necessary packages:

npm install openai pg

Then, a basic script to generate and insert data:

import OpenAI from 'openai';
import pg from 'pg';

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

const client = new pg.Client({
  user: 'postgres',
  host: 'localhost',
  database: 'mydatabase',
  password: 'mysecretpassword',
  port: 5432,
});

async function generateEmbedding(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: 'text-embedding-ada-002',
    input: text,
  });
  return response.data[0].embedding;
}

async function addProduct(name: string, description: string, price: number) {
  const embedding = await generateEmbedding(description);
  const query = `
    INSERT INTO products (name, description, price, description_embedding)
    VALUES ($1, $2, $3, $4::vector)
  `;
  await client.query(query, [name, description, price, embedding]);
  console.log(`Added product: ${name}`);
}

async function main() {
  await client.connect();

  // Example products
  await addProduct('Vintage Leather Jacket', 'A classic brown leather jacket, perfect for cool evenings. Made from genuine aged leather.', 199.99);
  await addProduct('Summer Linen Shirt', 'Lightweight and breathable linen shirt, ideal for hot weather. Available in various vibrant colors.', 45.00);
  await addProduct('Ergonomic Office Chair', 'High-back office chair with lumbar support and adjustable armrests for maximum comfort during long work hours.', 349.50);
  await addProduct('Smart Home Security Camera', 'Wireless 1080p camera with night vision and motion detection. Integrates with smart home ecosystems.', 89.99);

  await client.end();
}

main().catch(console.error);

Notice the $4::vector cast in the SQL query. This explicitly tells Postgres to treat the array of numbers as a vector type. It's a small detail, but crucial for pgvector to correctly interpret the data.

Performing Semantic Search

With embeddings in place, semantic search becomes a query against your description_embedding column. The trick is to generate an embedding for the user's search query and then find the closest vectors in your table.

Pgvector provides several operators for calculating vector similarity:

• L2 distance (<->): Measures Euclidean distance, useful for general similarity. Smaller is better.
• cosine distance (<=>): Measures the angle between vectors, also for general similarity. Smaller is better.
• inner product (<#>): Measures the projection of one vector onto another. Larger is better.

For most semantic search scenarios, cosine distance (<=>) or L2 distance (<->) are good starting points. Let's use cosine distance for finding similar products.

// ... (imports and client setup from above) ...

async function searchProducts(queryText: string, limit: number = 5) {
  const queryEmbedding = await generateEmbedding(queryText);

  const query = `
    SELECT
      id, name, description, price,
      description_embedding <=> $1::vector AS cosine_distance
    FROM
      products
    ORDER BY
      cosine_distance ASC
    LIMIT $2;
  `;
  const res = await client.query(query, [queryEmbedding, limit]);
  return res.rows;
}

async function searchMain() {
  await client.connect();

  console.log('\nSearching for "comfortable office chair":');
  const results1 = await searchProducts('comfortable office chair');
  console.log(results1.map(r => ({ name: r.name, description: r.description, distance: r.cosine_distance })));

  console.log('\nSearching for "beachwear":');
  const results2 = await searchProducts('beachwear');
  console.log(results2.map(r => ({ name: r.name, description: r.description, distance: r.cosine_distance })));

  console.log('\nSearching for "surveillance device":');
  const results3 = await searchProducts('surveillance device');
  console.log(results3.map(r => ({ name: r.name, description: r.description, distance: r.cosine_distance })));

  await client.end();
}

searchMain().catch(console.error);

When you run this, you'll see how queries like "comfortable office chair" correctly pick up the "Ergonomic Office Chair", and "beachwear" retrieves the "Summer Linen Shirt". Even "surveillance device" correctly points to the "Smart Home Security Camera." The database now understands the meaning behind the words.

Indexing for Performance

For small datasets, a sequential scan might be fine. But for production applications with millions of entries, you'll need indexes. pgvector supports Hierarchical Navigable Small World (HNSW) and IVFFlat indexes for efficient approximate nearest neighbor (ANN) search.

HNSW is generally preferred for its better recall and performance, especially on larger datasets. IVFFlat can be faster for very high dimensions but might have lower recall.

-- For HNSW index (recommended for most cases)
CREATE INDEX ON products USING hnsw (description_embedding vector_cosine_ops);

-- For IVFFlat index (alternative, sometimes faster for very high dimensions)
-- You need to specify a 'lists' parameter, typically `num_rows / 1000` up to `num_rows / 10`
-- Let's assume 1000 products for this example, so 10 lists.
-- CREATE INDEX ON products USING ivfflat (description_embedding vector_cosine_ops) WITH (lists = 10);

The vector_cosine_ops is crucial; it tells the index to use the cosine distance operator for comparisons. You'd use vector_l2_ops for L2 distance. Without an index, pgvector performs an exact nearest neighbor search, which is accurate but slow on large datasets. With an index, it's an approximate nearest neighbor search, offering a trade-off between speed and perfect recall.

Trade-offs and Considerations

While pgvector is incredibly powerful, it's not a silver bullet. There are trade-offs to acknowledge:

1. Resource Usage: Storing high-dimensional vectors increases your database size. Indexing also consumes memory and disk space. Each 1536-dimension vector for text-embedding-ada-002 is about 6KB of raw data. Multiply that by millions of records, and it adds up quickly.
2. Indexing Speed and Accuracy: ANN indexes are approximate. This means they might not always return the absolute closest vectors, especially with very high lists settings for IVFFlat or certain HNSW parameters. You'll need to benchmark and tune index parameters for your specific use case to balance speed and recall.
3. Embedding Generation Cost: Generating embeddings for all your data (and for every search query) involves API calls to a model provider (like OpenAI) or running a local model. These incur costs and latency. You'll need a strategy for batching, caching, and updating embeddings as your data changes.
4. Model Dependency: The quality of your semantic search is directly tied to the quality of your embedding model. If the model has biases or doesn't understand your domain well, your search results will reflect that.
5. Schema Migrations: Adding a vector column and populating it for existing data requires a migration strategy. For large tables, this can be a long-running process.

For many applications, the convenience and simplicity of pgvector within an existing Postgres setup far outweigh these considerations. It's a fantastic solution for adding powerful AI capabilities without dramatically increasing architectural complexity.

Wrapping up

Pgvector brings the power of AI-driven semantic search directly to your trusted relational database. It's a pragmatic approach for many applications that need to go beyond keyword matching without the operational burden of a separate vector database. You can start integrating it today without overhauling your entire data stack. Try creating a products table, generate a few embeddings using OpenAI's API, and play around with semantic queries. See how much smarter your application becomes when your database truly understands what your users are looking for.