A database index is a persisted auxiliary structure that lets PostgreSQL find matching rows without scanning an entire table. Primary keys create indexes automatically; other indexes target frequently queried columns. section 4.4.1
Index choices support different retrieval needs:
- Index session user IDs to retrieve a user's sessions efficiently; index message session IDs to find messages belonging to a session. section 4.4.1
- Use specialized vector indexes to avoid comparing a query against every stored embedding. IVFFlat searches selected clusters, trading some accuracy for speed, and needs representative data when created. section 5.4.3
- Plan index creation for production: standard CREATE INDEX blocks writes, while CREATE INDEX CONCURRENTLY avoids write locks but takes longer and increases system load. section 4.4.1
Listing 4.9 PostgreSQL schema for the sessions table
CREATE TABLE sessions (
session_id VARCHAR(255) PRIMARY KEY, #A
user_id VARCHAR(255) NOT NULL, #B
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_sessions_user_id
➥ON sessions(user_id); #CThe schema pairs the sessions primary-key index with a separate index on user_id. section 4.4.1
Listing 5.18 PostgreSQL schema for vector storage with pgvector
CREATE EXTENSION IF NOT EXISTS vector; #A
CREATE TABLE chunks (
chunk_id VARCHAR(255) PRIMARY KEY,
document_id VARCHAR(255) NOT NULL, #B
index_name VARCHAR(255) NOT NULL, #B
chunk_text TEXT NOT NULL,
embedding vector(1536), #C
metadata JSONB DEFAULT '{}', #D
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_chunks_document ON chunks(document_id);
CREATE INDEX idx_chunks_index ON chunks(index_name);
CREATE INDEX idx_chunks_metadata
➥ON chunks USING gin (metadata); #E
CREATE INDEX idx_chunks_embedding ON chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100); #FThe chunks schema shows indexes for document and index lookups, JSONB metadata, and IVFFlat vector search. section 5.4.3


