Useful Cheatsheets
Build / AI /

Vector Databases

Index and query high-dimensional embeddings using Pinecone, PostgreSQL pgvector, HNSW indexes, and metadata filtering.

TL;DR

  1. Initialize managed vector indexes using @pinecone-database/pinecone client SDKs.
  2. Store high-dimensional vector embeddings alongside relational records using pgvector.
  3. Accelerate approximate nearest neighbor queries utilizing HNSW index configurations.
Jump to section

Managed Indexing with Pinecone

    Pinecone Client Initialization

    Connect to managed serverless index with Pinecone TypeScript SDK.

    import { Pinecone } from '@pinecone-database/pinecone';
const pc = new Pinecone({
  apiKey: process.env.PINECONE_API_KEY!,
});
const index = pc.index('kb-docs');
    Upserting Vectors with Metadata

    Insert or update vector records with attached metadata payloads.

    await index.upsert([
  {
    id: 'doc-101',
    values: [0.012, 0.431, -0.219],
    metadata: {
      tenantId: 'team-4',
      category: 'legal',
      text: 'Clause 5 terms...',
    },
  },
]);
    Filtered Vector Similarity Query

    Query top-K nearest neighbors scoped by metadata filter conditions.

    const results = await index.query({
  vector: queryVector,
  topK: 5,
  includeMetadata: true,
  filter: {
    tenantId: { $eq: 'team-4' },
    category: { $in: ['legal', 'compliance'] },
  },
});

PostgreSQL with pgvector

    SQL Table & HNSW Index Schema

    Enable pgvector extension and create indexed vector table.

    -- Enable extension in PostgreSQL database
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  embedding vector(1536)
);

-- Build HNSW index using cosine distance
CREATE INDEX doc_hnsw_idx ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
    Drizzle ORM Vector Query

    Query nearest neighbors using cosine distance operator in SQL.

    import { sql } from 'drizzle-orm';

async function searchDocs(
  db: any, queryVec: number[]
) {
  const vecStr = `[${queryVec.join(',')}]`;
  return await db.execute(sql`
    SELECT id, content,
      1 - (embedding <=> ${vecStr}::vector) AS score
    FROM documents
    WHERE metadata->>'tenant' = 'org-12'
    ORDER BY embedding <=> ${vecStr}::vector
    LIMIT 5;
  `);
}
    Vector Dimension Match Check

    Enforce exact vector dimension parity before database inserts.

    function validateVector(
  v: number[], targetDim = 1536
) {
  if (v.length !== targetDim) {
    const err = 'Vector dimension mismatch';
    throw new Error(err);
  }
}

Index Performance & Scaling

    HNSW vs IVFFlat Selection

    Trade-offs between indexing build time, memory, and search speed.

    // HNSW: Fast queries, high memory, no training step.
// Best for production real-time search (< 5ms).

// IVFFlat: Low memory, faster build, requires train.
// Best for batch systems with tight RAM budgets.
    Dynamic ef_search Tuning

    Increase recall accuracy at query time for critical searches.

    async function queryWithHighRecall(client: any) {
  // Default ef_search is 40. Bump to 100 for accuracy
  await client.query('SET hnsw.ef_search = 100;');
  return await runVectorQuery(client);
}
    Namespaces for Multi-Tenancy

    Isolate tenant vector records completely using index namespaces.

    const ns = 'tenant_enterprise_99';
const tenantIndex = index.namespace(ns);
await tenantIndex.upsert(records);
const matches = await tenantIndex.query({
  vector: q, topK: 3,
});

Metadata Filtering & Tenancy

    Pre-Filtering vs Post-Filtering

    Filter candidates before distance calculation to prevent recall collapse.

    // Pre-filtering restricts the search graph to matches.
// Post-filtering searches K items, then filters,
// which can yield zero results if K has few matches.
    Composite Metadata Query

    Filter by customer tenant ID and document update timestamp.

    const filter = {
  tenant: { $eq: 'customer_7' },
  updatedAt: { $gte: 1704067200 },
};
const res = await index.query({
  vector: q, topK: 5, filter,
});
    Soft Deletion Tombstones

    Filter out deleted vectors without paying index compaction costs.

    const activeFilter = { isDeleted: { $ne: true } };
const active = await index.query({
  vector: q, topK: 5, filter: activeFilter
});

Tips

  1. Create HNSW indexes over IVFFlat in pgvector for dramatically higher queries-per-second and low latency under scale.
  2. Attach tenant and role identifiers as vector metadata to enforce data isolation at retrieval time.

Warnings

  1. Never perform slow, unindexed vector table scans on production tables exceeding ten thousand records in PostgreSQL.
  2. Avoid deeply nested metadata objects in Pinecone that exceed provider payload indexing limits and inflate retrieval latencies.

In Practice

FAQ