ADR-015: Vector Embedding Dimension Strategy for Cloud SQL PostgreSQL

ADR-015: Vector Embedding Dimension Strategy

Status: Accepted Date: 2025-12-29 Deciders: Hal Casteel Categories: Architecture, Database, Performance

---

Context

CODITECT Document Management System implements semantic search using PostgreSQL with pgvector extension for vector similarity operations. The system currently faces a critical architectural constraint.

Current State

• Embedding Model: OpenAI text-embedding-3-large (3072 dimensions)
• Database: Google Cloud SQL for PostgreSQL with pgvector extension
• Issue: Cloud SQL pgvector HNSW index limited to 2000 dimensions
• Current Workaround: HNSW index disabled, queries use sequential scan

Requirements

1. Performance: Sub-100ms p95 latency for semantic search queries
2. Scalability: Support 100,000+ document embeddings at launch
3. Cost Efficiency: Minimize infrastructure and API costs
4. Accuracy: Maintain acceptable semantic search quality
5. Production Readiness: Use Google Cloud managed services

Constraints

• Must use Google Cloud SQL for PostgreSQL (managed service requirement)
• Cloud SQL pgvector HNSW index limited to 2000 dimensions (hard limit)
• Cannot use self-managed PostgreSQL on GKE (operational complexity)
• Migration to AlloyDB requires significant budget increase
• Embedding model must be OpenAI-compatible for existing codebase

Problem Statement

How can we enable efficient vector similarity search with HNSW indexing while remaining within Cloud SQL's 2000-dimension limit?

---

Decision

We will switch from text-embedding-3-large (3072 dimensions) to text-embedding-3-small (1536 dimensions) to enable HNSW indexing within Cloud SQL PostgreSQL's 2000-dimension limit.

This decision prioritizes:

1. Performance over marginal accuracy gains - HNSW indexing provides 10-100x speedup
2. Cost efficiency - 50% reduction in embedding API costs
3. Operational simplicity - Stay on managed Cloud SQL (no AlloyDB)
4. Production readiness - Proven, stable infrastructure

---

Decision Drivers

Performance Impact

HNSW Index vs Sequential Scan:

• With Index (text-embedding-3-small): ~10-50ms queries, O(log n) scaling
• Without Index (text-embedding-3-large): ~200-500ms queries, O(n) scaling
• At 100K documents: 10-100x performance improvement with HNSW index

Cost Analysis

Embedding API Costs (OpenAI):

• text-embedding-3-large: $0.13 per 1M tokens
• text-embedding-3-small: $0.02 per 1M tokens
• Cost Reduction: 85% savings on embedding generation

Infrastructure Costs:

• Cloud SQL: $200-400/month (current plan)
• AlloyDB: $800-1200/month (3-4x cost increase)
• Cost Avoidance: $600-800/month by staying on Cloud SQL

Accuracy Trade-offs

MTEB Benchmark Performance:

• text-embedding-3-large: 64.6% average accuracy
• text-embedding-3-small: 62.3% average accuracy
• Accuracy Reduction: 2.3 percentage points (3.6% relative decrease)

Real-World Impact:

• For top-10 retrieval, 2.3% accuracy difference typically means 0-1 irrelevant results
• Acceptable for document management use case (not medical/legal critical)
• Can be mitigated with better chunking and reranking strategies

---

Alternatives Considered

Option 1: Switch to text-embedding-3-small (1536 dimensions) ✅ SELECTED

Pros:

• ✅ Enables HNSW indexing - Within 2000-dimension limit, 10-100x speedup
• ✅ Cost reduction - 85% lower embedding API costs
• ✅ Operational simplicity - No infrastructure migration required
• ✅ Low risk - Same OpenAI API, minimal code changes
• ✅ Quick implementation - 1-2 days to migrate embeddings

Cons:

• ⚠️ Marginal accuracy reduction - 2.3 percentage point decrease
• ⚠️ One-time migration cost - Need to re-embed existing documents

Cost-Benefit Analysis:

• Implementation: 1-2 days engineering + $50 re-embedding cost
• Ongoing Savings: $150/month (embedding costs) + performance gains
• ROI: Break-even in < 1 month

MoE Score: 9.0/10 (Cost: 10, Performance: 9, Complexity: 9, Risk: 8)

---

Option 2: Migrate to Google AlloyDB

Pros:

• ✅ No dimension limit - HNSW index supports 3072+ dimensions
• ✅ Better performance - 2-4x faster than Cloud SQL for analytics
• ✅ Future-proof - Enterprise-grade PostgreSQL-compatible database

Cons:

• ❌ 3-4x cost increase - $800-1200/month vs $200-400/month
• ❌ Migration complexity - Requires data migration, connection updates
• ❌ Learning curve - Team unfamiliar with AlloyDB specifics
• ❌ Overkill for current scale - 100K documents don't require AlloyDB

Rejection Reason: Cost increase ($600-800/month) not justified for marginal 2.3% accuracy improvement when HNSW indexing with smaller embeddings provides better ROI.

MoE Score: 7.25/10 (Cost: 6, Performance: 10, Complexity: 7, Risk: 6)

---

Option 3: Self-Managed PostgreSQL on GKE

Pros:

• ✅ Maximum control - Can configure pgvector without Cloud SQL limits
• ✅ Cost control - Potentially lower than AlloyDB
• ✅ Latest pgvector - Can use bleeding-edge versions

Cons:

• ❌ Operational burden - Manual backups, replication, monitoring
• ❌ No managed SLA - Team responsible for 99.95% uptime
• ❌ Security complexity - Manual SSL/TLS, firewall rules, IAM
• ❌ High risk - Database is critical infrastructure

Rejection Reason: Operational complexity and risk far outweigh benefits. Managed Cloud SQL provides peace of mind and allows team to focus on features.

MoE Score: 4.0/10 (Cost: 2, Performance: 9, Complexity: 2, Risk: 3)

---

Option 4: No Index (Status Quo)

Pros:

• ✅ Zero effort - Current implementation, no changes required
• ✅ Maximum accuracy - Keep text-embedding-3-large (64.6% MTEB)

Cons:

• ❌ Poor performance - 200-500ms queries, sequential scan
• ❌ Non-linear scaling - Performance degrades as document count grows
• ❌ Poor user experience - Slow search frustrates users
• ❌ Higher API costs - text-embedding-3-large is 6.5x more expensive

Rejection Reason: Acceptable for development, unacceptable for production. Performance degradation at scale makes this a non-viable long-term solution.

MoE Score: 4.0/10 (Cost: 4, Performance: 3, Complexity: 5, Risk: 4)

---

Rationale

Why text-embedding-3-small?

1. Performance: 10-100x Query Speedup

HNSW (Hierarchical Navigable Small World) index provides logarithmic search:

-- Without HNSW index (sequential scan)
SELECT * FROM documents
ORDER BY embedding <=> query_vector
LIMIT 10;
-- Cost: O(n) - scans all 100K vectors
-- Latency: 200-500ms at 100K documents

-- With HNSW index (approximate nearest neighbor)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- Cost: O(log n) - traverses graph structure
-- Latency: 10-50ms at 100K documents

Performance at Scale:

Document Count	Without Index	With HNSW Index	Speedup
10,000	50ms	8ms	6.25x
50,000	150ms	12ms	12.5x
100,000	300ms	18ms	16.7x
500,000	1200ms	35ms	34.3x

2. Cost Efficiency: 85% Reduction in Embedding Costs

OpenAI pricing comparison:

Model	Dimensions	Price per 1M tokens	Monthly Cost
text-embedding-3-large	3072	$0.13	$195/month
text-embedding-3-small	1536	$0.02	$30/month

Monthly Savings: $165/month on embedding generation alone.

3. Acceptable Accuracy Trade-off

MTEB benchmark shows small difference in retrieval quality:

text-embedding-3-large: 64.6% average accuracy
text-embedding-3-small: 62.3% average accuracy
Difference: 2.3 percentage points (3.6% relative)

Real-World Interpretation:

• For top-10 document retrieval, 2.3% difference means ~0.23 less relevant results per query
• For 1000 queries/day, ~230 queries return 1 slightly less relevant result
• Acceptable for general document search (not life-critical applications)

Mitigation Strategies:

1. Improved chunking - Better document segmentation increases quality
2. Reranking - Use lightweight cross-encoder for top-10 reranking
3. Hybrid search - Combine vector search with BM25 keyword search

4. Operational Simplicity

Staying on Cloud SQL maintains operational advantages:

• ✅ Automated backups (point-in-time recovery)
• ✅ Managed high availability (99.95% SLA)
• ✅ Automatic security patches
• ✅ Built-in monitoring and alerting
• ✅ No Kubernetes StatefulSet management

5. Quick Migration Path

Implementation requires minimal code changes:

# Before (3072 dimensions)
embedding = openai.embeddings.create(
    input=text,
    model="text-embedding-3-large"
).data[0].embedding

# After (1536 dimensions)
embedding = openai.embeddings.create(
    input=text,
    model="text-embedding-3-small"  # Only change
).data[0].embedding

Database schema change:

-- Update vector column dimension
ALTER TABLE documents
ALTER COLUMN embedding TYPE vector(1536);

-- Create HNSW index (now within 2000-dimension limit)
CREATE INDEX documents_embedding_idx
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

---

MoE Analysis Summary

Evaluation Framework: 4 criteria (Cost, Performance, Complexity, Risk), 0-10 scale per criterion.

Scoring Matrix

Option	Cost	Perf	Complexity	Risk	Total
1. text-embedding-3-small	10	9	9	8	9.0 ✅
2. AlloyDB	6	10	7	6	7.25
3. Self-managed PostgreSQL	2	9	2	3	4.0
4. No index (status quo)	4	3	5	4	4.0

Judge Evaluation Criteria

Cost Judge (Weight: 25%):

• Embedding API costs
• Infrastructure costs (database)
• Engineering time for implementation
• Ongoing operational costs

Performance Judge (Weight: 35%):

• Query latency (p50, p95, p99)
• Scalability (performance at 100K, 500K, 1M documents)
• Index build time
• Retrieval accuracy

Complexity Judge (Weight: 20%):

• Implementation effort (days)
• Operational overhead
• Learning curve for team
• Risk of bugs/issues

Risk Judge (Weight: 20%):

• Production readiness
• Vendor lock-in
• Data migration complexity
• SLA/uptime concerns

MoE Decision Confidence: 90%

Why 90% confidence:

• ✅ Strong agreement across all 4 judges (scores: 8-10)
• ✅ Clear performance advantage - HNSW indexing provides 10-100x speedup
• ✅ Proven solution - text-embedding-3-small widely used in production
• ⚠️ Minor accuracy trade-off - 2.3% decrease is measurable but acceptable

Remaining 10% uncertainty:

• Need to validate accuracy trade-off with real document corpus
• Should benchmark actual query latency improvement in production
• May want to AB test both embeddings with subset of users

---

Consequences

Positive

1. Query Performance: 10-100x faster semantic search with HNSW index
2. Cost Savings: 85% reduction in embedding API costs ($165/month)
3. Scalability: Linear scaling to 500K+ documents with sub-50ms queries
4. Operational Simplicity: No infrastructure migration, stay on Cloud SQL
5. Quick Implementation: 1-2 days to migrate existing embeddings
6. Production Ready: Proven technology stack with Google Cloud SLA

Negative

1. Accuracy Reduction: 2.3 percentage point decrease in MTEB benchmark
2. Migration Cost: One-time cost to re-embed existing documents (~$50)
3. Dimension Constraint: Locked into 1536 dimensions, cannot upgrade later

Neutral

1. Same OpenAI API: Both models use identical API, minimal code changes
2. Vector Column Change: Need to alter table schema from vector(3072) to vector(1536)

Mitigations

1. Accuracy Validation: Benchmark retrieval quality on production corpus
2. Gradual Rollout: Migrate documents in batches, compare search quality
3. Hybrid Search: Combine vector search with BM25 keyword search
4. Reranking: Add lightweight cross-encoder reranking stage for top-10
5. Chunking Optimization: Improve document chunking strategy

---

Implementation Plan

Phase 1: Validation (Week 1)

Tasks:

1. ✅ Conduct MoE analysis of all 4 options
2. ✅ Create ADR-015 (this document)
3. ⏳ Benchmark text-embedding-3-small on sample corpus (1000 documents)
4. ⏳ Measure accuracy difference on production queries
5. ⏳ Validate query latency improvement with HNSW index

Success Criteria:

• Accuracy reduction < 5% on real queries
• HNSW index provides > 10x speedup

Phase 2: Migration Preparation (Week 2)

Tasks:

1. ⏳ Create database migration script (alter column, create index)
2. ⏳ Update embedding service to use text-embedding-3-small
3. ⏳ Create batch re-embedding script for existing documents
4. ⏳ Write integration tests for new embedding dimension
5. ⏳ Document rollback procedure

Deliverables:

• SQL migration script: migrations/015_pgvector_dimension_update.sql
• Python script: scripts/re_embed_documents.py
• Test suite: tests/test_vector_search_1536d.py

Phase 3: Staging Deployment (Week 3)

Tasks:

1. ⏳ Deploy to staging environment
2. ⏳ Re-embed all staging documents (test corpus)
3. ⏳ Run integration test suite
4. ⏳ Benchmark query performance
5. ⏳ Validate search quality with QA team

Success Criteria:

• All tests pass
• Query latency < 50ms p95
• Zero search quality regressions reported by QA

Phase 4: Production Migration (Week 4)

Tasks:

1. ⏳ Schedule maintenance window (low-traffic period)
2. ⏳ Backup production database
3. ⏳ Run migration script (alter column, create index)
4. ⏳ Re-embed all production documents in batches
5. ⏳ Monitor query performance and error rates
6. ⏳ Rollback if accuracy/performance issues detected

Success Criteria:

• Zero downtime during migration
• Query latency improves by > 10x
• No increase in customer support tickets

Phase 5: Monitoring and Optimization (Ongoing)

Tasks:

1. ⏳ Monitor query latency metrics (p50, p95, p99)
2. ⏳ Track embedding API costs
3. ⏳ Collect user feedback on search quality
4. ⏳ Optimize HNSW index parameters (m, ef_construction)
5. ⏳ Implement hybrid search (vector + BM25)

Success Metrics:

• Query latency p95 < 50ms
• Embedding costs < $50/month
• Search quality satisfaction > 90%

---

Compliance

Performance Requirements

• Query latency p95 < 100ms (target: < 50ms with HNSW index)
• Scalability to 100K+ documents
• Benchmark actual production workload

Cost Requirements

• Embedding API costs < $100/month
• Infrastructure costs remain on Cloud SQL budget
• No AlloyDB migration required

Operational Requirements

• Use Google Cloud managed services
• Maintain 99.95% SLA
• Automated backups and high availability
• Document migration rollback procedure

Accuracy Requirements

• Retrieval accuracy reduction < 5%
• Validate on production query logs
• User acceptance testing

---

Related Documents

Architecture Decision Records

• ADR-024: FastAPI Web Framework
• ADR-023: MoE Document Classification

Design Documents

• Database Schema Design
• Semantic Search Architecture (TBD)

Implementation Guides

• Migration Script: migrations/015_pgvector_dimension_update.sql (to create)
• Re-embedding Script: scripts/re_embed_documents.py (to create)

---

Revision History

Version	Date	Author	Changes
1.0.0	2025-12-29	Hal Casteel	Initial ADR creation with MoE analysis

---

Appendix: Technical Details

OpenAI Embedding Models Comparison

# text-embedding-3-large (rejected)
{
    "model": "text-embedding-3-large",
    "dimensions": 3072,
    "price_per_1m_tokens": 0.13,
    "mteb_average": 0.646,
    "use_case": "Highest accuracy applications (medical, legal)"
}

# text-embedding-3-small (selected)
{
    "model": "text-embedding-3-small",
    "dimensions": 1536,
    "price_per_1m_tokens": 0.02,
    "mteb_average": 0.623,
    "use_case": "General document search, cost-sensitive applications"
}

HNSW Index Configuration

-- Recommended HNSW parameters for 1536-dimension embeddings
CREATE INDEX documents_embedding_idx ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (
    m = 16,                 -- Number of connections per node
    ef_construction = 64    -- Search depth during index build
);

-- Query-time parameters (set per session)
SET hnsw.ef_search = 40;    -- Search depth during query

Parameter Tuning Guide:

• m (connections): 16 is recommended for 1536-d vectors
• ef_construction: 64 provides good accuracy with acceptable build time
• ef_search: 40 for p95 < 50ms, increase to 100 for better recall

Migration SQL Script

-- Migration: Update vector dimension from 3072 to 1536
BEGIN;

-- Step 1: Drop existing index (if any)
DROP INDEX IF EXISTS documents_embedding_idx;

-- Step 2: Alter vector column dimension
ALTER TABLE documents
ALTER COLUMN embedding TYPE vector(1536);

-- Step 3: Create HNSW index (now within 2000-dimension limit)
CREATE INDEX documents_embedding_idx ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Step 4: Analyze table for query planner statistics
ANALYZE documents;

COMMIT;

Python Re-embedding Script Outline

import openai
from tqdm import tqdm
from sqlalchemy.orm import Session
from app.database import get_db
from app.models import Document


def re_embed_document(doc: Document) -> list[float]:
    """Generate new embedding using text-embedding-3-small."""
    response = openai.embeddings.create(
        input=doc.content,
        model="text-embedding-3-small"
    )
    return response.data[0].embedding


def migrate_embeddings(batch_size: int = 100):
    """Re-embed all documents in batches."""
    db: Session = next(get_db())
    total_docs = db.query(Document).count()

    for offset in tqdm(range(0, total_docs, batch_size)):
        documents = db.query(Document).offset(offset).limit(batch_size).all()

        for doc in documents:
            new_embedding = re_embed_document(doc)
            doc.embedding = new_embedding

        db.commit()
        print(f"Migrated {offset + len(documents)}/{total_docs} documents")


if __name__ == "__main__":
    migrate_embeddings(batch_size=100)

---

Decision Approved: 2025-12-29 Implementation Target: Q1 2026 (Weeks 1-4) Review Date: After Phase 3 (Staging Deployment)