EXPANSION30 min61 sections

Database Patterns for AI Products

THIS WEEK'S JOURNEY

Database Patterns for AI Products

The database layer is where AI products either scale gracefully or collapse under their own weight. Unlike traditional applications where you're storing and retrieving structured data, AI workloads demand a fundamentally different approach—you're dealing with high-dimensional vectors, streaming conversation histories, real-time user preferences that inform model behavior, and caching layers that can make or break your cost structure.

Key Insight

AI Products Have Three Distinct Data Planes

Traditional applications typically have one primary data plane—your relational database. AI products operate across three distinct planes simultaneously: the semantic plane (vector embeddings for similarity search), the temporal plane (conversation history and context windows), and the preference plane (user settings, feedback, and behavioral data).

The Three Data Planes of AI Products

User Query

Preference Plane (Po...

Semantic Plane (Vect...

Temporal Plane (Redi...

Traditional Database Architecture vs. AI-Native Architecture

Traditional Architecture

Single relational database handles all workloads

Queries return exact matches based on indexed columns

Caching primarily for read-heavy pages and sessions

Schema changes require careful migrations

AI-Native Architecture

Polyglot persistence with specialized stores per workload

Queries return similarity rankings across vector space

Caching critical for expensive LLM inference results

Embedding dimensions may change with model updates

Notion

Building AI Search Across 10 Billion Blocks

Notion AI search now handles 50 million queries daily with p99 latency under 180...

Framework

The CAVE Framework for AI Data Architecture

Context Assembly

Design your data layer around how context gets assembled for LLM calls. Every query should be optimi...

Access Pattern Separation

Separate your hot path (real-time inference) from your warm path (batch embedding generation) from y...

Vector-First Indexing

Index by semantic meaning first, metadata second. Your primary query pattern is 'find similar conten...

Embedding Versioning

Plan for embedding model changes from day one. When you switch from text-embedding-ada-002 to text-e...

73%

of AI inference costs come from redundant computations

Most AI products repeatedly compute the same or highly similar responses.

Key Insight

Vector Databases Are Not Just 'Databases for Vectors'

A common misconception is that vector databases are simply databases optimized for storing arrays of floats. In reality, they're fundamentally different systems built around Approximate Nearest Neighbor (ANN) algorithms.

Don't Embed Everything—Start with What Users Actually Search

A common mistake is embedding your entire database on day one. This is expensive, slow, and often unnecessary.

Basic Vector Store Setup with Pinecone and TypeScripttypescript

123456789101112
import { Pinecone } from '@pinecone-database/pinecone';
import { OpenAI } from 'openai';

const pinecone = new Pinecone({ apiKey: process.env.PINECONE_API_KEY });
const openai = new OpenAI();

interface Document {
  id: string;
  content: string;
  metadata: {
    userId: string;
    createdAt: string;

Anti-Pattern: The 'One Giant Index' Mistake

❌ Problem

Query latency degrades linearly with total index size, not with the user's data ...

✓ Solution

Use namespaces or separate indexes per tenant for B2B products. For B2C with man...

Vector Database Selection Checklist

Linear

Hybrid Search for Technical Issue Tracking

Search relevance (measured by click-through rate on first result) improved from ...

Key Insight

Chunking Strategy Is Your Most Important Embedding Decision

How you split documents into chunks before embedding determines the quality of your retrieval more than any other factor. Too large, and you'll retrieve irrelevant content alongside relevant content, wasting precious context window space.

Implementing a Production-Ready Chunking Pipeline

Analyze Your Content Types

Choose Chunk Boundaries Intelligently

Implement Overlap for Context Preservation

Add Contextual Headers to Each Chunk

Store Both Chunk and Source References

Use Late Chunking for Better Embeddings

A newer technique called 'late chunking' embeds the entire document first using a long-context model, then extracts chunk embeddings from the full-document embedding. This preserves document-level context in each chunk embedding.

Semantic Chunking with Context Preservationtypescript

123456789101112
import { RecursiveCharacterTextSplitter } from 'langchain/text_splitter';

interface ChunkWithContext {
  content: string;
  metadata: {
    documentId: string;
    documentTitle: string;
    sectionPath: string[];
    chunkIndex: number;
    totalChunks: number;
    previousChunkId: string | null;
    nextChunkId: string | null;

Key Insight

Your Embedding Model Choice Affects Everything Downstream

The embedding model you choose determines vector dimensions, semantic quality, cost, and latency. OpenAI's text-embedding-3-small (1536 dimensions) costs $0.02 per million tokens and works well for general text.

Practice Exercise

Build a Chunking Quality Analyzer

45 min

Framework

The AI Data Layer Architecture

Hot Path Storage

Real-time data that needs sub-100ms access. This includes active conversation context, user session ...

Warm Path Storage

Recent data accessed within minutes to hours. Conversation history from the past 24 hours, recently ...

Cold Path Storage

Historical data for analytics and model training. Archived conversations, audit logs, and bulk embed...

Vector Index Layer

Specialized storage for semantic search and retrieval. This crosses hot/warm boundaries depending on...

Vector Database Approaches

Purpose-Built (Pinecone/Weaviate)

Sub-20ms queries at scale with optimized HNSW indexes

Advanced filtering, hybrid search, and metadata queries buil...

Managed infrastructure with automatic scaling and replicatio...

Higher cost: $70-500+/month for production workloads

PostgreSQL + pgvector

50-200ms queries, degrading past 1M vectors

Basic vector similarity search with standard SQL integration

Use existing Postgres infrastructure and expertise

Minimal additional cost if already running Postgres

Conversation History Schema Designtypescript

123456789101112
// Optimized schema for AI conversation storage
interface ConversationSchema {
  // Core tables
  conversations: {
    id: string;           // UUID v7 for time-ordering
    user_id: string;
    created_at: Date;
    updated_at: Date;
    metadata: {
      model: string;      // 'gpt-4', 'claude-3'
      system_prompt_version: string;
      total_tokens: number;

Notion

Building Semantic Search at Scale

Notion AI search launched with 99.5% availability, handling over 10 million quer...

Implementing Efficient Conversation History

Design Your Access Patterns First

Choose Your Primary Key Strategy

Implement Message Pagination

Add Soft Deletes with Retention

Separate Hot and Cold Storage

Anti-Pattern: Storing Raw Embeddings in Your Primary Database

❌ Problem

Your primary database becomes bloated with embedding data that's 10-100x larger ...

✓ Solution

Store embeddings in a separate table with a foreign key to your content. Better ...

Key Insight

User Preferences Are Your Secret Weapon for Personalization

Most AI products treat every user identically, missing a massive opportunity. User preferences—communication style, expertise level, preferred response length, domain context—can dramatically improve response quality without fine-tuning.

User Preferences Storage Patterntypescript

123456789101112
interface UserPreferences {
  // Core preferences
  communicationStyle: 'concise' | 'detailed' | 'conversational';
  expertiseLevel: 'beginner' | 'intermediate' | 'expert';
  preferredLanguage: string;
  timezone: string;
  
  // AI-specific preferences
  responseFormat: {
    includeExamples: boolean;
    includeCodeSnippets: boolean;
    maxResponseLength: 'short' | 'medium' | 'long';

Framework

The Caching Hierarchy for AI Products

L1: Exact Match Cache

Cache identical queries with identical context. Use a hash of (query + system_prompt + relevant_cont...

L2: Semantic Similarity Cache

For queries that are semantically similar but not identical, return cached responses. Embed the quer...

L3: Partial Response Cache

Cache components that can be reused across responses. For RAG systems, cache retrieved documents and...

L4: Computation Cache

Cache expensive intermediate computations: embeddings, entity extraction, sentiment analysis, summar...

Anthropic

Prompt Caching for Cost Reduction

Enterprise customers reported 50-90% cost reductions on RAG workloads. One legal...

Cache Invalidation Is Where AI Caching Gets Hard

Unlike traditional web caching, AI cache invalidation has unique challenges. When you update your system prompt, all cached responses become potentially stale.

73%

Average cost reduction with intelligent caching

This study analyzed token costs before and after implementing multi-tier caching strategies.

Production Database Readiness Checklist

Multi-Tier AI Data Architecture

User Request

Redis Cache (L1: <1m...

PostgreSQL Primary (...

Vector Store (L3: 10...

Anti-Pattern: Synchronous Embedding Generation in Request Path

❌ Problem

User-facing latency increases by 200-500ms per document. If the embedding API is...

✓ Solution

Make embedding generation asynchronous. Accept the upload immediately, store the...

Practice Exercise

Design Your Conversation Storage Schema

45 min

Essential Database Resources for AI Engineers

pgvector Documentation and Benchmarks

article

Pinecone Learning Center

article

The Art of PostgreSQL

book

Redis University - Caching Patterns

video

Start with PostgreSQL, Graduate When Necessary

For most AI products, PostgreSQL with pgvector handles everything you need until you hit millions of vectors or need sub-10ms queries. Starting with Postgres means one less system to manage, familiar tooling, and ACID guarantees for your conversation data.

Practice Exercise

Build a Complete Vector Search Pipeline

45 min

Production Vector Search with Caching and Fallbacktypescript

123456789101112
import { Pinecone } from '@pinecone-database/pinecone';
import { Redis } from 'ioredis';
import { createHash } from 'crypto';

class VectorSearchService {
  private pinecone: Pinecone;
  private redis: Redis;
  private index: any;
  
  constructor() {
    this.pinecone = new Pinecone({ apiKey: process.env.PINECONE_KEY! });
    this.redis = new Redis(process.env.REDIS_URL!);

Practice Exercise

Implement Conversation History with Summarization

30 min

Production Database Deployment Checklist

Anti-Pattern: The Monolithic Vector Index

❌ Problem

Query latency grows from 50ms to 500ms+ as the index scales. Metadata filter acc...

✓ Solution

Implement namespace isolation or separate indexes per tenant from the start. Pin...

Anti-Pattern: Storing Raw LLM Responses Without Structure

❌ Problem

Analytics queries take hours instead of seconds. You cannot build indexes on res...

✓ Solution

Define a structured schema that extracts key fields from LLM responses into inde...

Anti-Pattern: Synchronous Database Writes in Request Path

❌ Problem

Response latency increases by 100-300ms due to database operations. Database fai...

✓ Solution

Implement asynchronous write patterns using message queues for non-critical pers...

Multi-Layer Caching Strategy Implementationtypescript

123456789101112
import { Redis } from 'ioredis';
import { LRUCache } from 'lru-cache';

class AIResponseCache {
  private l1Cache: LRUCache<string, CachedResponse>;
  private l2Cache: Redis;
  private metrics: CacheMetrics;
  
  constructor() {
    // L1: In-memory, 1000 items, 5 min TTL
    this.l1Cache = new LRUCache({
      max: 1000,

Practice Exercise

Design a User Preferences System with Real-Time Sync

35 min

Essential Database Tools for AI Products

pgvector Extension

tool

Prisma ORM with Vector Support

tool

Redis Stack with Vector Search

tool

Designing Data-Intensive Applications

book

Start with PostgreSQL, Add Specialized Stores Later

For most AI products, PostgreSQL with pgvector handles vectors, JSONB handles flexible schemas, and proper indexing handles performance up to significant scale. Only add specialized databases like Pinecone or dedicated Redis clusters when you have clear evidence of PostgreSQL limitations.

Database Migration Strategy for Growing AI Productstypescript

123456789101112
// Migration service for seamless database transitions
class DatabaseMigrationService {
  private sourceDb: PostgresClient;
  private targetVectorStore: PineconeClient;
  private redis: Redis;
  
  async migrateVectorsIncrementally(options: MigrationOptions) {
    const batchSize = options.batchSize || 1000;
    let lastId = options.resumeFrom || 0;
    let migrated = 0;
    
    while (true) {

Practice Exercise

Build a Comprehensive Analytics Pipeline

40 min

Test Database Failures in Staging

Before launching, simulate every database failure mode in staging: primary failover, replica lag, connection pool exhaustion, and network partitions. Use tools like Toxiproxy to inject latency and failures.

Framework

Database Selection Decision Matrix

Query Pattern Analysis

Map your access patterns: read-heavy vs write-heavy, point lookups vs range scans, real-time vs batc...

Consistency Requirements

Determine what data requires strong consistency (user preferences, billing) vs eventual consistency ...

Scale Projections

Estimate data volume and query rates at 10x current scale. If PostgreSQL handles your 10x projection...

Operational Capacity

Assess your team's ability to operate each database type. A managed PostgreSQL you understand beats ...

67%

of AI startups over-provision database infrastructure

Most early-stage AI products could run on a single PostgreSQL instance with proper optimization.

Chapter Complete!

Vector databases are essential for semantic search but start...

Conversation history requires careful schema design with sum...

Multi-layer caching (in-memory L1 + Redis L2) can reduce AI ...

User preferences need real-time sync across devices with con...

Next: Begin by auditing your current database architecture against the patterns in this chapter

PreviousNext