liveBook · Manning

Database index

As explained in Designing AI Systems

A database index is a persisted auxiliary structure that accelerates retrieval of rows matching query conditions.

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);    #C

The 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);    #F

The chunks schema shows indexes for document and index lookups, JSONB metadata, and IVFFlat vector search. section 5.4.3

How Designing AI Systems covers it

How deep it goes
Explains lookup benefits, index choices, and production creation tradeoffs, and contrasts IVFFlat with brute-force vector search.
Examples
Uses session and message lookup indexes, plus document, metadata, and embedding indexes in a pgvector schema.
What you can do after
Operate/configure PostgreSQL indexes for lookup columns and vector search while accounting for table size and write availability.
How it connects
Connects primary keys, table scans, and vector search to index selection.

Also explained in

Compare how 3 books explain database index →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Designing AI Systems. The section links point to the parts of the book it draws on.