Troubleshooting Playbook and SQL Cookbook

Troubleshoot pgvector by preserving evidence and narrowing one layer at a time: extension and schema, data and model compatibility, SQL and authorization, plan and index, recall and labels, resources and concurrency, then application stages. Avoid changing several parameters during an incident. Save versions, queries, plans, metrics, and timelines before applying a reversible fix.

What will you be able to do?

Triage common pgvector symptoms.
Run safe diagnostic SQL.
Separate data, quality, planner, and resource failures.
Apply reversible mitigations.
Capture evidence for a durable correction.

Which checks answer common questions?

sql

-- Versions
SELECT version();
SELECT extversion FROM pg_extension WHERE extname = 'vector';

-- Table and index size
SELECT pg_size_pretty(pg_total_relation_size('app.chunk_embeddings'));
SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid)), idx_scan
FROM pg_stat_user_indexes
WHERE relid = 'app.chunk_embeddings'::regclass;

-- Index validity
SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'app.chunk_embeddings'::regclass;

-- Table health
SELECT n_live_tup, n_dead_tup, last_autovacuum, last_analyze
FROM pg_stat_user_tables
WHERE relid = 'app.chunk_embeddings'::regclass;

-- Active work (privilege-dependent visibility)
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS age, left(query, 120) AS query_excerpt
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start;

Do not publish query excerpts when they may contain sensitive data.

What symptoms suggest what causes?

Symptom	First checks
type vector does not exist	extension enabled in this database, search path
dimension error	model, revision, preprocessing, vector length, column type
ANN index unused	operator/order/limit, operator class, casts, plan cost, statistics
fewer filtered results	selectivity, ef_search/probes, iterative scans, partial design
low recall	exact baseline, metric, model mismatch, ANN settings, data drift
slow build	rows, dimensions, memory notice, workers, I/O, concurrent load
slow API, fast SQL	pool wait, embedding, rerank, generation, network, serialization
stale results	source version, job lag, visibility cutover, replica/cache generation
RLS leak	runtime role, owner/BYPASSRLS, session state, functions, caches

How do you run an incident?

State impact and start time, freeze risky changes, preserve plans and metrics, mitigate with a reversible flag or capacity step, test the hypothesis, communicate, then correct the underlying design. Afterward, write a blameless timeline and add the missing test, alert, or runbook.

How do you verify a fix?

Reproduce the original symptom on a fixture or snapshot, apply one change, show expected plan/quality/resource difference, run security and regression suites, observe under canary, and retain rollback. “The graph went down” is not causal evidence.

What breaks in production?

Global planner settings change during a guess.
An index is dropped before its replacement is valid.
Recall is “fixed” by returning far more irrelevant results.
Sensitive queries are pasted into tickets.
Emergency access remains permanent.

AI pair-work prompt

Help triage this pgvector incident. I will provide sanitized versions, symptom, timeline, query, plan, metrics, and recent changes. Build a hypothesis table with supporting/refuting evidence and the smallest reversible test. Do not recommend destructive or global changes without target validation and rollback.

Verification contract: Close the incident only after the original failure is reproduced or explained, the fix passes regression/security checks, and a prevention artifact is assigned.

Check your understanding

Which fields show an invalid index?
Why can fast SQL coexist with a slow API?
Write a hypothesis table for low filtered result count.

Official references