Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.
Vector search observability needs recall, result count, filter selectivity, freshness lag, tenant skew, index health, reranker cost, and answer grounding - not just latency.
HNSW defaults can look fast while missing useful results. Production tuning needs recall@k, p99, index build time, memory, and filtered result count together.
The best vector database depends on ownership boundaries, filter semantics, recall targets, migration paths, cost, and operational maturity - not benchmark screenshots.
Vector search looks easy until tenant filters, permissions, freshness, and deleted content arrive. The hard part is not nearest neighbors; it is filtered recall under production rules.
Rerankers can improve answer quality, but they cannot recover evidence that retrieval never found. Use them after recall, cost, and latency are understood.
The hard vector database decision is not pgvector versus Pinecone on a checklist. It is whether your filters, recall target, update rate, and incident budget still fit inside Postgres.
EXPLAIN ANALYZE is readable once you know the order: find the slow node, compare estimates to actuals, check loops, then confirm buffers and waits.
A nested loop join is fast or slow depending on the row counts. Here is when it is the right plan, when it is not, and how to read the EXPLAIN output to tell.
Hash joins are the workhorse for large equi-joins. They are also the join type that quietly spills to disk when work_mem is too small, turning a fast query into a slow one.
A sort that fits in `work_mem` is fast. A sort that does not is 10x slower. Here is how to tell which case you are in and what to do about it.
Planner problems usually start with one wrong estimate. Learn how to spot the first lie in the plan, then fix the statistics, predicate, or index that caused it.
CTEs read beautifully and sometimes cost beautifully too. The behavior changed in Postgres 12, and most teams I work with have not updated their mental model.