search performance

Running pgvector on Amazon Aurora PostgreSQL-Compatible Edition offers a vector store with operational capabilities, high availability, and scalability. It is favored for Retrieval Augmented Generation (RAG) workloads transitioning to production, but increased traffic introduces challenges like query latency and memory management. Key operational practices for pgvector workloads include selecting the appropriate index type (HNSW or IVFFlat), establishing a baseline schema, choosing a suitable distance operator, scaling the index through quantization and partitioning, and preparing for churn and observability. The prerequisites for using pgvector include an Aurora PostgreSQL-Compatible cluster with specific PostgreSQL versions and the vector extension enabled. The embedding model used in examples is Amazon Titan Text Embeddings V2, which produces 1024-dimensional embeddings. pgvector supports two Approximate Nearest Neighbor (ANN) index types: HNSW, which is efficient for querying and allows for incremental insertions, and IVFFlat, which is less resource-intensive but requires rebuilding if data changes. There are scenarios where forgoing an index is beneficial, such as small datasets or partitioned datasets requiring 100% recall. A baseline schema for a multi-tenant document store includes creating a table for documents with an embedding vector and establishing indexes for tenant IDs and embeddings using HNSW. The recommended parameters for HNSW include m = 16 and ef_construction = 128. Scaling to millions of vectors involves quantization, tuning HNSW parameters, and partitioning. Aurora Optimized Reads can extend effective cache capacity, and managing index churn is crucial for maintaining performance. Observability metrics include query-level statistics, instance-level metrics, and custom application-defined metrics. To clean up after testing, it is advisable to drop the created indexes and tables, and delete the Aurora PostgreSQL-Compatible cluster and any manual snapshots taken during testing.