Neon Postgres and pgvector: RAG Database Design for Production

Connection pooling, cold-start, index type, hybrid search — four decisions cheapest to make at design time, costliest to fix after launch

pgvector brings native vector storage and similarity search to Postgres; Neon provides serverless managed Postgres. Together they represent one of the most cost-effective choices for small-to-medium scale RAG systems. This article focuses on the configuration details most often overlooked in production.

Index Selection in pgvector

pgvector supports two index types; choosing incorrectly significantly impacts query performance:

IVFFlat (Inverted File Index): Generally faster to build and uses less memory than HNSW. It performs approximate nearest-neighbour search by probing selected vector clusters. Recall and query latency depend on index configuration and workload, so suitability should be validated through benchmarks rather than a fixed dataset-size threshold.

HNSW (Hierarchical Navigable Small World): Often provides a better speed-recall trade-off than IVFFlat, but generally requires more memory and longer index build times. Compare both index types using representative queries, recall targets, latency, and resource requirements rather than a fixed vector-count threshold.

Connection Pool Management in Serverless

Neon's serverless nature introduces a critical production issue: each Lambda function or Vercel Edge Function instance may attempt to establish its own Postgres connection. Under high traffic, database connection counts are rapidly exhausted (Postgres default: 100–200 connections).

Solution: use Neon's connection pooling endpoint (pool mode), or configure PgBouncer. The Neon Serverless Driver (@neondatabase/serverless) uses WebSocket rather than TCP, enabling correct operation in environments that don't support persistent connections (such as Cloudflare Workers) while avoiding the connection pool problem.

Cold-Start Latency Strategy

Neon suspends compute during inactivity (Autosuspend); first-query cold-start latency can reach 1–5 seconds — unacceptable for real-time vector search paths.

Strategy: set a minimum active period for latency-sensitive vector search endpoints (Neon Scale plan supports this), or maintain a persistent connection for vector search while allowing relational query endpoints to auto-suspend. Monitor cold-start frequency and log it to determine whether a plan upgrade is warranted.

Hybrid Search Design

Pure vector search sometimes misses exact keyword match results, especially for product codes, proper names, and regulatory clause numbers. Production systems commonly use hybrid search: vector similarity results combined with Postgres full-text search results, merged with weighted ranking.

Postgres full-text search has limited native Chinese support; pg_trgm covers some scenarios. For proper Chinese full-text search, use the zhparser extension or pre-process tokenisation at the application layer. Tune the hybrid search weight ratio using business dataset recall rate — not intuition.

Time Zone Standardisation

Vector record timestamp fields should consistently use TIMESTAMPTZ, which stores timestamps internally in UTC. Use the appropriate timezone, such as Asia/Hong_Kong, when displaying or interpreting local dates. Mixing timezone-aware and timezone-naive timestamps can cause errors in time-based filters (such as "memories from the last 7 days"). For the HKSoka memory system architecture, see AI Long-Term Memory System: HKSoka Architecture and Engineering.

Summary

Neon + pgvector is well-suited for rapid production launch of small-to-medium RAG systems. Four key production decisions: connection pool must be correctly configured; cold-start strategy selected based on usage patterns; index type selected through workload benchmarks; timestamps stored using TIMESTAMPTZ with consistent timezone handling. Determining these four at design time costs far less than fixing them after launch.