pgvector is often praised as an easy solution for integrating vector search into Postgres, but the author points out significant gaps between theory and reality. While pgvector allows for vector similarity searches, the practical challenges become clear when trying to implement it in a production environment. Many blogs touting pgvector seem to come from users who have only tested it on small datasets, giving an overly optimistic view without addressing the complexities of scaling. Indexing is a major concern. pgvector offers two indexing options: IVFFlat and HNSW. IVFFlat is easier on memory during creation and provides reasonable performance, but requires upfront knowledge of how many clusters to create, which can lead to suboptimal results as data grows. HNSW generally performs better with larger datasets but has high memory demands, making it risky for production use. Building an HNSW index can consume over 10 GB of RAM, jeopardizing the database’s stability during the process. Real-time search capabilities are also problematic. When new data is added, the system struggles to keep it searchable immediately. IVFFlat can lead to degraded search quality over time due to cluster overloads, necessitating periodic index rebuilds that require downtime. HNSW allows for incremental updates, but the locking and resource demands can create bottlenecks, especially under heavy write loads. The author emphasizes that managing metadata alongside vector data complicates consistency further, as traditional transaction management doesn't seamlessly apply during lengthy index builds. The workarounds for these issues, such as maintaining separate indexes or staging tables, highlight the limitations of pgvector for high-velocity data ingestion.