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): Fast to build, low memory footprint, suitable for datasets up to ~100K records initially. Queries approximate search by probing a set of cluster centres — slightly lower accuracy than exact search but significantly faster.

HNSW (Hierarchical Navigable Small World): Higher recall at large data volumes, but longer index build times and significantly higher memory use. Evaluate switching only at 1M+ vectors; IVFFlat is sufficient initially.

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 stored at UTC+8 (AT TIME ZONE 'Asia/Hong_Kong'). Mixing UTC and local time in time-based vector filter queries (such as "memories from the last 7 days") causes hard-to-detect errors at boundary periods. 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; initial index type IVFFlat; all timestamps standardised to UTC+8. Determining these four at design time costs far less than fixing them after launch.