Vector Database Integration Guide — From PostgreSQL to Pinecone

Vector databases are the backbone of modern AI applications. They store and query vector embeddings at scale, enabling semantic search, RAG systems, and recommendation engines. But choosing the right solution and implementing it correctly can be challenging.

This post covers integrating vector databases into your application: using pgvector when you already have PostgreSQL, migrating to Pinecone for scale, and implementation patterns that work in production.

Why a Vector Database?

Traditional databases are great for exact matches. Vector databases excel at similarity:

-- Traditional: exact match
SELECT * FROM products WHERE name = 'iPhone 15';

-- Vector: semantic similarity
SELECT * FROM products
ORDER BY embedding <-> '[0.1, -0.2, 0.3, ...]'
LIMIT 10;

The difference is crucial for AI applications. When a user searches for “smartphone with great camera,” you want results about phones with good cameras — not just documents containing those exact words.

Option 1: pgvector for PostgreSQL

If you already use PostgreSQL, pgvector is the fastest path to vector search. No new infrastructure, just an extension.

Installation and Setup

# Install pgvector (PostgreSQL 14+)
git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # may need sudo

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a table with vector column
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB,
  embedding vector(1536), -- OpenAI embeddings are 1536 dimensions
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create an index for faster searches
CREATE INDEX documents_embedding_idx
ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

The IVFFlat index is crucial for performance. Without it, every query does a full table scan. The lists parameter should be roughly sqrt(number_of_rows).

Basic Operations

-- Insert with embedding (assuming you have the embedding array)
INSERT INTO documents (content, embedding, metadata)
VALUES ('Hello world', '[0.1, -0.2, 0.3, ...]', '{"category": "greeting"}');

-- Cosine similarity search (most common for text)
SELECT id, content, 1 - (embedding <=> '[0.1, -0.2, 0.3, ...]') as similarity
FROM documents
ORDER BY embedding <=> '[0.1, -0.2, 0.3, ...]'
LIMIT 10;

-- Inner product search (faster, if vectors are normalized)
SELECT id, content, (embedding <#> '[0.1, -0.2, 0.3, ...]') * -1 as similarity
FROM documents
ORDER BY embedding <#> '[0.1, -0.2, 0.3, ...]'
LIMIT 10;

-- Euclidean distance (L2)
SELECT id, content, - (embedding <-> '[0.1, -0.2, 0.3, ...]') as similarity
FROM documents
ORDER BY embedding <-> '[0.1, -0.2, 0.3, ...]'
LIMIT 10;

Node.js Integration

import { Pool } from 'pg';

class PgVectorStore {
  private pool: Pool;

  constructor(connectionString: string) {
    this.pool = new Pool({ connectionString });
  }

  async initialize(): Promise<void> {
    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS embeddings (
        id TEXT PRIMARY KEY,
        text TEXT NOT NULL,
        embedding vector(1536),
        metadata JSONB DEFAULT '{}',
        created_at TIMESTAMPTZ DEFAULT NOW()
      );

      CREATE INDEX IF NOT EXISTS embeddings_vector_idx
      ON embeddings
      USING ivfflat (embedding vector_cosine_ops)
      WITH (lists = 100);
    `);
  }

  async insert(
    id: string,
    text: string,
    embedding: number[],
    metadata: Record<string, any> = {}
  ): Promise<void> {
    await this.pool.query(
      `INSERT INTO embeddings (id, text, embedding, metadata)
       VALUES ($1, $2, $3, $4)
       ON CONFLICT (id) DO UPDATE
       SET text = EXCLUDED.text,
           embedding = EXCLUDED.embedding,
           metadata = EXCLUDED.metadata`,
      [id, text, `[${embedding.join(',')}]`, JSON.stringify(metadata)]
    );
  }

  async batchInsert(items: Array<{
    id: string;
    text: string;
    embedding: number[];
    metadata?: Record<string, any>;
  }>): Promise<void> {
    const client = await this.pool.connect();

    try {
      await client.query('BEGIN');

      for (const item of items) {
        await client.query(
          `INSERT INTO embeddings (id, text, embedding, metadata)
           VALUES ($1, $2, $3, $4)
           ON CONFLICT (id) DO UPDATE
           SET text = EXCLUDED.text,
               embedding = EXCLUDED.embedding,
               metadata = EXCLUDED.metadata`,
          [
            item.id,
            item.text,
            `[${item.embedding.join(',')}]`,
            JSON.stringify(item.metadata || {}),
          ]
        );
      }

      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async search(
    queryEmbedding: number[],
    limit: number = 10,
    filters?: Record<string, any>
  ): Promise<Array<{ id: string; text: string; similarity: number; metadata: any }>> {
    let whereClause = '';
    const params: any[] = [`[${queryEmbedding.join(',')}]`, limit];
    let paramIndex = 3;

    if (filters) {
      const conditions: string[] = [];
      for (const [key, value] of Object.entries(filters)) {
        if (typeof value === 'string') {
          conditions.push(`metadata->>$${paramIndex} = $${paramIndex + 1}`);
          params.push(key, value);
          paramIndex += 2;
        } else if (typeof value === 'number') {
          conditions.push(`(metadata->>$${paramIndex})::numeric = $${paramIndex + 1}`);
          params.push(key, value);
          paramIndex += 2;
        }
      }
      if (conditions.length > 0) {
        whereClause = 'AND ' + conditions.join(' AND ');
      }
    }

    const result = await this.pool.query(
      `SELECT id, text, metadata, 1 - (embedding <=> $1) as similarity
       FROM embeddings
       WHERE 1=1
       ${whereClause}
       ORDER BY embedding <=> $1
       LIMIT $2`,
      params
    );

    return result.rows;
  }

  async delete(id: string): Promise<void> {
    await this.pool.query('DELETE FROM embeddings WHERE id = $1', [id]);
  }

  async update(
    id: string,
    updates: Partial<{ text: string; embedding: number[]; metadata: Record<string, any> }>
  ): Promise<void> {
    const clauses: string[] = [];
    const params: any[] = [];
    let paramIndex = 1;

    if (updates.text !== undefined) {
      clauses.push(`text = $${paramIndex++}`);
      params.push(updates.text);
    }

    if (updates.embedding !== undefined) {
      clauses.push(`embedding = $${paramIndex++}`);
      params.push(`[${updates.embedding.join(',')}]`);
    }

    if (updates.metadata !== undefined) {
      clauses.push(`metadata = $${paramIndex++}`);
      params.push(JSON.stringify(updates.metadata));
    }

    params.push(id);

    await this.pool.query(
      `UPDATE embeddings SET ${clauses.join(', ')} WHERE id = $${paramIndex}`,
      params
    );
  }
}

pgvector Pros and Cons

Pros:

• No additional infrastructure
• SQL joins with vector search
• ACID transactions
• Familiar tooling

Cons:

• Limited scale (~1M vectors before performance degrades)
• Single machine (no horizontal scaling)
• Index rebuilds are expensive

Option 2: Pinecone for Scale

When you outgrow pgvector, Pinecone provides a managed vector database that scales horizontally.

Setup

import { Pinecone, PineconeRecord } from '@pinecone-database/pinecone';

class PineconeVectorStore {
  private client: Pinecone;
  private indexName: string;

  constructor(apiKey: string, indexName: string) {
    this.client = new Pinecone({ apiKey });
    this.indexName = indexName;
  }

  private async getIndex() {
    return this.client.index(this.indexName);
  }

  async upsert(
    items: Array<{
      id: string;
      vector: number[];
      metadata?: Record<string, any>;
    }>
  ): Promise<void> {
    const index = await this.getIndex();

    const records: PineconeRecord[] = items.map(item => ({
      id: item.id,
      values: item.vector,
      metadata: item.metadata || {},
    }));

    await index.upsert(records);
  }

  async search(
    queryVector: number[],
    limit: number = 10,
    filter?: Record<string, any>
  ): Promise<Array<{
    id: string;
    score: number;
    metadata: Record<string, any>;
  }>> {
    const index = await this.getIndex();

    const result = await index.query({
      vector: queryVector,
      topK: limit,
      includeMetadata: true,
      filter,
    });

    return (result.matches || []).map(match => ({
      id: match.id,
      score: match.score || 0,
      metadata: match.metadata || {},
    }));
  }

  async delete(ids: string[]): Promise<void> {
    const index = await this.getIndex();
    await index.deleteMany(ids);
  }

  async deleteAll(): Promise<void> {
    const index = await this.getIndex();
    await index.deleteAll();
  }
}

Pinecone Filtering

Pinecone supports metadata filtering, which is crucial for relevance:

// Filter by category
const results = await store.search(queryVector, 10, {
  category: { $eq: 'laptop' },
});

// Filter by numeric range
const results = await store.search(queryVector, 10, {
  price: { $lte: 1000, $gte: 500 },
});

// Combine filters
const results = await store.search(queryVector, 10, {
  category: { $eq: 'laptop' },
  price: { $lte: 1500 },
  inStock: { $eq: true },
});

Pinecone Pros and Cons

Pros:

• Scales to billions of vectors
• Horizontal scaling
• Managed service (no maintenance)
• Fast queries at scale

Cons:

• Additional cost
• Vendor lock-in
• No SQL joins
• Eventual consistency

Hybrid Approach: Best of Both

For many applications, the best approach is using both: PostgreSQL for transactional data and metadata, Pinecone for vector search:

class HybridVectorStore {
  private pg: PgVectorStore;
  private pinecone: PineconeVectorStore;

  constructor(pgConnectionString: string, pineconeApiKey: string, pineconeIndex: string) {
    this.pg = new PgVectorStore(pgConnectionString);
    this.pinecone = new PineconeVectorStore(pineconeApiKey, pineconeIndex);
  }

  async insert(
    id: string,
    text: string,
    embedding: number[],
    metadata: Record<string, any>
  ): Promise<void> {
    // Store everything in PostgreSQL
    await this.pg.insert(id, text, embedding, metadata);

    // Store only vector + ID in Pinecone
    await this.pinecone.upsert([{
      id,
      vector: embedding,
      metadata: { id }, // Just reference
    }]);
  }

  async search(
    queryEmbedding: number[],
    limit: number = 10
  ): Promise<Array<{
    id: string;
    text: string;
    similarity: number;
    metadata: any;
  }>> {
    // Vector search in Pinecone (fast)
    const vectorResults = await this.pinecone.search(queryEmbedding, limit * 2);

    // Fetch full documents from PostgreSQL
    const ids = vectorResults.map(r => r.id);
    const documents = await this.pg.fetchByIds(ids);

    // Combine and return
    return vectorResults
      .map(result => ({
        ...result,
        text: documents.get(result.id)?.text || '',
        metadata: documents.get(result.id)?.metadata || {},
      }))
      .slice(0, limit);
  }
}

This pattern gives you the scale of Pinecone with the flexibility of SQL joins in PostgreSQL.

Performance Tuning

Connection Pooling

import { Pool } from 'pg';

// Configure pool for vector workloads
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Increase for concurrent vector queries
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Batch Operations

Always batch your operations. Single inserts are slow:

// Bad: individual inserts
for (const item of items) {
  await store.insert(item.id, item.text, item.embedding);
}

// Good: batch insert
await store.batchInsert(items);

Embedding Caching

Don’t re-encode text that hasn’t changed:

class CachedEmbeddingService {
  private cache = new Map<string, number[]>();
  private underlying: EmbeddingService;

  constructor(underlying: EmbeddingService) {
    this.underlying = underlying;
  }

  async encode(text: string): Promise<number[]> {
    const cacheKey = this.hash(text);

    if (this.cache.has(cacheKey)) {
      return this.cache.get(cacheKey)!;
    }

    const embedding = await this.underlying.encode(text);
    this.cache.set(cacheKey, embedding);

    return embedding;
  }

  private hash(text: string): string {
    // Simple hash for caching
    return require('crypto')
      .createHash('sha256')
      .update(text)
      .digest('hex');
  }
}

When to Migrate

Here’s a practical guide for when to move from pgvector to Pinecone:

Metric	pgvector	Pinecone
Vectors	< 1M	> 1M
QPS	< 100	> 100
Latency	< 50ms	< 20ms
Team	Small	Any size
Budget	Low	Medium-High

Start with pgvector. It’s free and fast enough for most applications. Migrate when you hit scale limits.

Conclusion

Vector databases are essential infrastructure for AI applications. Start with pgvector if you already use PostgreSQL — it’s the fastest path to production. When you outgrow it, Pinecone provides a managed solution that scales horizontally.

The hybrid approach gives you the best of both worlds: SQL flexibility with vector scale. Choose based on your actual needs, not hypothetical future requirements. You can always migrate later.