pgvector-advanced

You are an expert in advanced pgvector v0.8.0 features and optimizations for PostgreSQL 17.

pgvector v0.8.0 Advanced Features

Binary Vectors (bit)

// src/db/binaryVectors.ts
import { sql } from "drizzle-orm";
import { db } from "./client";

// Binary vectors for compact storage and Hamming distance
export async function setupBinaryVectors() {
  // Create table with binary vectors
  await db.execute(sql`
    CREATE TABLE IF NOT EXISTS binary_features (
      id SERIAL PRIMARY KEY,
      companion_id TEXT NOT NULL,
      user_id TEXT NOT NULL,
      feature_name TEXT NOT NULL,
      binary_vector bit(1024),  -- 1024-bit binary vector
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  // Create index for Hamming distance search
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS binary_features_hamming_idx
    ON binary_features
    USING ivfflat (binary_vector bit_hamming_ops)
    WITH (lists = 50);
  `);
}

// Convert float embeddings to binary for space efficiency
export function floatToBinary(embedding: number[]): string {
  // Convert to binary by thresholding at 0
  const bits = embedding.map(v => v > 0 ? '1' : '0');
  return bits.join('');
}

// Hamming distance search for binary vectors
export async function searchBinaryVectors(queryVector: string, limit = 10) {
  return await db.execute(sql`
    SELECT 
      *,
      binary_vector <~> B'${queryVector}' as hamming_distance
    FROM binary_features
    ORDER BY binary_vector <~> B'${queryVector}'
    LIMIT ${limit}
  `);
}

Sparse Vectors (sparsevec)

// src/db/sparseVectors.ts
import { sql } from "drizzle-orm";

// Sparse vectors for high-dimensional but mostly zero data
export async function setupSparseVectors() {
  // Enable sparsevec type
  await db.execute(sql`CREATE EXTENSION IF NOT EXISTS vector`);
  
  // Create table with sparse vectors
  await db.execute(sql`
    CREATE TABLE IF NOT EXISTS sparse_memories (
      id SERIAL PRIMARY KEY,
      companion_id TEXT NOT NULL,
      user_id TEXT NOT NULL,
      content TEXT,
      sparse_embedding sparsevec(100000),  -- Up to 100k dimensions
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);

  // Create index for sparse vector search
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS sparse_memories_idx
    ON sparse_memories
    USING ivfflat (sparse_embedding sparsevec_l2_ops)
    WITH (lists = 100);
  `);
}

// Convert dense to sparse representation
export function denseToSparse(embedding: number[], threshold = 0.01): Record<number, number> {
  const sparse: Record<number, number> = {};
  embedding.forEach((value, index) => {
    if (Math.abs(value) > threshold) {
      sparse[index] = value;
    }
  });
  return sparse;
}

// Format sparse vector for PostgreSQL
export function formatSparseVector(sparse: Record<number, number>, dimensions: number): string {
  const entries = Object.entries(sparse)
    .map(([idx, val]) => `${idx}:${val}`)
    .join(',');
  return `{${entries}}/${dimensions}`;
}

// Search with sparse vectors
export async function searchSparseVectors(
  sparseQuery: Record<number, number>,
  dimensions: number,
  limit = 10
) {
  const sparseStr = formatSparseVector(sparseQuery, dimensions);
  
  return await db.execute(sql`
    SELECT 
      *,
      sparse_embedding <-> '${sparseStr}'::sparsevec as distance
    FROM sparse_memories
    WHERE sparse_embedding IS NOT NULL
    ORDER BY sparse_embedding <-> '${sparseStr}'::sparsevec
    LIMIT ${limit}
  `);
}