The Postgres extension pgvector supports vector similarity search, but there is a large gap between demo-level use and real production environments
Both IVFFlat and HNSW indexes have clear pros and cons; HNSW in particular suffers from multi-GB memory usage and long build times during index creation
Real-time search and index updates are structurally difficult, and continuous inserts/updates cause lock contention and performance degradation
Due to filtering strategies (Pre/Post) and the limits of the query planner, an unstable balance between search accuracy and speed emerges
Features provided by dedicated vector databases (Pinecone, Weaviate, etc.) must be implemented manually, ultimately leading to increased operational complexity and cost