Running pgvector on Amazon Aurora PostgreSQL-Compatible Edition provides a robust vector store within a familiar database environment, enhanced by the operational capabilities, high availability, and scalability of Amazon Aurora. This synergy has led to pgvector becoming a preferred solution for Retrieval Augmented Generation (RAG) workloads transitioning from proof of concept to production, complete with Service Level Agreements (SLAs). However, as production traffic increases, it brings a set of operational challenges such as query latency, recall on filtered vector searches, memory management during index builds, and connection handling under load. This discussion focuses on the database operations essential for maintaining a healthy RAG retrieval layer, while model customization through fine-tuning or continued pre-training is not addressed here.
We will explore the operational practices necessary for sustaining a pgvector workload: selecting the appropriate index and distance function, scaling with quantization and partitioning, managing Hierarchical Navigable Small World (HNSW) churn, sizing for memory-resident operations, and identifying observability signals that can help detect issues early.
How this post is organized
The structure of this post is as follows:
- Select an index type (HNSW or Inverted File with Flat Compression) suited to your dataset and query patterns.
- Establish a baseline schema and query using AWS-recommended parameters.
- Choose a distance operator that aligns with your embedding model.
- Scale the index to accommodate your target dataset size through quantization, parameter tuning, and partitioning.
- Prepare for churn, capacity, and observability before traffic increases.
Each section builds upon the previous one, providing a comprehensive guide to managing your pgvector workload effectively.
The examples presented utilize a multi-tenant document-store schema on Aurora PostgreSQL-Compatible with Amazon Titan Text Embeddings V2, a foundation model from Amazon Bedrock, serving as the embedding model. All SQL examples can be executed against an Aurora PostgreSQL-Compatible cluster with the vector extension enabled.
This post emphasizes a self-managed approach, where you oversee the vector store and retrieval pipeline. For those seeking a fully managed RAG capability that automates ingestion, embedding, and retrieval, Amazon Bedrock Knowledge Bases is available, supporting Amazon Aurora PostgreSQL with pgvector as one of its vector store options.
Prerequisites
To follow the examples outlined in this post, you will need:
- An Aurora PostgreSQL-Compatible cluster running PostgreSQL version 17.4+, 16.8+, 15.12+, 14.17+, or 13.20+, which supports pgvector 0.8.0. For launch details, refer to the pgvector 0.8.0 Aurora announcement. For the current Region list, see the Aurora PostgreSQL extension versions page.
- The vector extension enabled on your database.
- An embedding model, with examples assuming the use of Amazon Titan Text Embeddings V2, which produces 1024-dimensional embeddings by default.
Choosing the right index strategy
pgvector offers two Approximate Nearest Neighbor (ANN) index types: HNSW and IVFFlat. Both prioritize speed over perfect accuracy, as conducting a brute-force exact search across millions of vectors is impractically slow for online queries. The AWS Prescriptive Guidance on vector database options outlines the positioning of pgvector among AWS vector stores. For a refresher on terminology, the Self-managed multi-tenant vector search with Amazon Aurora PostgreSQL post provides foundational insights.
For production RAG on Aurora PostgreSQL, HNSW is typically the default choice for most workloads. However, there are specific scenarios where opting for no index is more advantageous: small datasets and partitioned datasets requiring 100% recall. The following sections elucidate why HNSW is generally preferred, when IVFFlat is still relevant, and when forgoing the index altogether is the optimal decision.
How HNSW works, and why it is the default
HNSW, or Hierarchical Navigable Small World, constructs a multi-layer proximity graph. The first layer encompasses every vector, closely connected to its nearest neighbors, while each subsequent layer samples a smaller subset of vectors with longer-range connections. A single vector serves as the entry point, present in every layer. For an in-depth exploration of both HNSW and IVFFlat internals, refer to the deep dive into IVFFlat and HNSW techniques.
A search commences at the entry point in the top layer. Within each layer, pgvector executes a greedy best-first walk along the graph edges, progressing to the nearest connected neighbor relative to the query vector. Upon reaching a point where no closer connections exist, it descends to the corresponding node in the layer below and continues the search. At layer 0, the search expands into a beam search governed by the hnsw.ef_search parameter, returning the top candidates.
This structure renders HNSW efficient for querying and allows for incremental insertions. New vectors can be written directly into a live index without necessitating pauses or batching. Recall and latency can be managed at query time through hnsw.ef_search, enabling tuning per workload without the need to rebuild the index.
However, the trade-off lies in the build cost. HNSW indexes require more time and memory to construct compared to IVFFlat, as the graph must be stored alongside the vectors. For the production RAG pattern discussed here, this trade-off is justified: the index is built once, and queries remain fast as long as churn is effectively managed (as will be elaborated in the Managing index churn section).
When IVFFlat still makes sense
IVFFlat, or Inverted File with Flat compression, organizes vectors into clusters using k-means, with each cluster having a representative centroid. During a query, pgvector compares the query vector to these centroids, selects the nearest clusters, and searches only within those clusters.
While IVFFlat is less resource-intensive to build than HNSW, it has limitations. The centroids are fixed at build time, meaning that if the data changes post-index creation, new vectors may not align well with the existing clusters, leading to decreased recall. To remedy this, the index must be rebuilt, which involves re-running k-means across the entire dataset.
IVFFlat is suitable for a limited range of workloads, specifically large, mostly static corpora with an established batch rebuild schedule. For all other scenarios, HNSW is the preferred choice.
When no index is the right choice
There are two specific production scenarios where forgoing an index yields better performance than either ANN option.
The first scenario involves small datasets. For tables containing approximately 10,000 to 50,000 vectors, a parallel sequential scan is sufficiently fast, and bypassing the index eliminates build time, maintenance costs, and the recall loss associated with approximate searches. This is highlighted in the pgvector 0.8.0 best practices and often serves as the initial approach for new workloads before the corpus expands.
The second scenario pertains to partitioned datasets where 100% recall is essential. If your schema partitions data such that every query interacts with a limited subset (for instance, by tenant or user), and your application demands complete recall, a brute-force parallel scan within a partition can outperform an ANN index. The Ring engineering team exemplifies this pattern in production, managing 100 to 200 billion embeddings distributed across per-user partitions of approximately 1 GB each. Each partition is scanned using max_parallel_workers_per_gather = 16, without any vector index. This adjustment allowed PostgreSQL to favor parallel sequential scans over single-threaded index scans, significantly enhancing EBS throughput from around 50 MB/s to nearly 500 MB/s. Detailed insights can be found in the Ring billion-scale semantic video search post.
For this pattern to be effective, two conditions must be met: the per-partition scan must align with the latency budget, meaning each partition should remain small enough to be served primarily from the buffer cache or local NVMe, and the workload must genuinely necessitate 100% recall. If the recall achievable with HNSW is acceptable, it is simpler and faster. When both conditions are satisfied, opting out of the index alleviates an entire category of operational concerns, including build costs, churn management, and memory sizing for index pages.
Quick decision guide
The following flowchart summarizes the decision-making process, starting from dataset size and progressing through write patterns, HNSW build costs, and partitioning to arrive at a definitive index choice.
A runnable baseline
Before delving into similarity functions and scaling, here is a baseline schema, index, and query that will serve as the foundation for the remainder of this post. It reflects the AWS-recommended HNSW parameters from the pgvector 0.8.0 best practices section and the iterative scan default we recommend for production.
The baseline employs HNSW, which is suitable for the common production RAG case: per-tenant partitions are sufficiently large that a sequential scan is not competitive, and ANN recall at the default settings is acceptable. If your workload resembles the no-index pattern discussed earlier, involving small tables or partitioned schemas requiring 100% recall within a bounded per-partition scan, you may omit the HNSW index from the following example while retaining the rest of the schema as shown.
-- Enable the extension (one-time, per database)
CREATE EXTENSION IF NOT EXISTS vector;
-- A multi-tenant document store with Titan Text Embeddings V2 (1024 dimensions)
CREATE TABLE documents (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tenant_id text NOT NULL,
content text NOT NULL,
embedding vector(1024) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- B-tree on the filter column, so the planner has an alternative path
CREATE INDEX documents_tenant_idx ON documents (tenant_id);
-- HNSW with the AWS-recommended starting parameters, cosine distance
CREATE INDEX documents_embedding_hnsw_idx
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);
-- Production-ready filtered vector query (iterative scan values are identifiers, not strings)
SET hnsw.iterative_scan = relaxed_order;
SET hnsw.ef_search = 100;
SELECT id, content
FROM documents
WHERE tenant_id = ''
ORDER BY embedding ''
LIMIT 10;When executed against a populated documents table, this query returns up to 10 rows ordered by cosine distance:
| id | content |
| 42 | Q3 revenue grew 18% year over year, driven by enterprise… |
| 117 | The FY26 capital plan was approved at the November board… |
| 203 | Customer churn declined to 2.1% following the support model… |
| … | … |
| (10 rows) | |
In production code, encapsulate the two SET statements and the SELECT within a transaction and utilize SET LOCAL instead, ensuring that the values apply solely to that query rather than the entire session. Substitute vector_cosine_ops with vector_ip_ops (and the operator with ) if your embeddings are unit-normalized. The subsequent section will clarify when each choice is applicable.
Similarity functions at scale
pgvector supports various distance operators, with three particularly relevant for text and semantic embeddings:
| Operator | Meaning | HNSW operator class | When to use |
| Cosine distance | vector_cosine_ops |
Safe default for text or semantic embeddings. Ignores vector magnitude. | |
| Negative inner product | vector_ip_ops |
Faster than cosine when vectors are already unit-normalized. | |
| L2 (Euclidean) distance | vector_l2_ops |
Rarely appropriate for semantic search. |
Note that for the operator, pgvector returns the negative inner product because PostgreSQL can only utilize ASC-order index scans. Consequently, ORDER BY embedding query ASC yields the most similar vectors first. To obtain a positive similarity score in the results, multiply by -1: SELECT (embedding query) * -1 AS similarity.
Picking cosine or inner product
Amazon Titan Text Embeddings V2 normalizes by default via the normalize API parameter, which defaults to true. For outputs from Amazon Titan Text Embeddings V2 with default settings, the inner product () is the appropriate choice. On normalized vectors, cosine and inner product yield identical rankings since cosine divides the inner product by the product of the vector norms, and for unit vectors, that divisor is consistently 1. Although norms and division are computed per comparison, the inner product circumvents this additional computation. If you override normalization to false or use an embedding model that does not normalize (such as BGE or older Sentence-Transformers models), revert to cosine.
To ensure your stored vectors are unit-normalized before switching operators, verify the norm is 1.0 within floating-point tolerance:
SELECT id,
sqrt(greatest(0, (embedding embedding) * -1)) AS norm
FROM documents
LIMIT 10;If any rows return a norm significantly different from 1.0, your data is not normalized, and the shortcut will yield incorrect results. The greatest(0, …) guard prevents sqrt of a minuscule negative floating-point residual for vectors that are unit-normalized within tolerance.
Iterative scans for filtered queries
pgvector 0.8.0 introduced iterative index scans, addressing the overfiltering issue. Prior to 0.8.0, a query combining a WHERE clause with a vector search often returned fewer results than the specified LIMIT due to the index returning its top-k candidates before the filter was applied, resulting in many candidates being filtered out.
Iterative scans continue to retrieve candidates from the index until the query meets the filter criteria or reaches a configurable limit. Three modes are available:
- off: the pre-0.8.0 behavior. Fastest, but may under-return.
- strict_order: maintains exact distance ordering. Safer, but slower for selective filters.
- relaxed_order: provides the correct count with approximate ordering within the result set. This mode is recommended for most production use cases.
Two related parameters constrain the scan when iterative mode is enabled: hnsw.max_scan_tuples (default 20,000) limits how far the scan traverses the index, and hnsw.scan_mem_multiplier (default 1) caps memory usage during the scan as a multiple of work_mem. If increasing max_scan_tuples alone does not enhance recall for a filtered query, consider raising scan_mem_multiplier first.
Scaling to millions of vectors
As datasets expand beyond a few hundred thousand vectors, three key levers come into play: quantization, HNSW parameter tuning, and partitioning.
Quantization reduces memory usage at a slight cost to recall. pgvector supports halfvec (16-bit floats) and binary quantization. The AWS pgvector 0.7.0 benchmarks indicate that halfvec cuts memory usage by approximately half with minimal recall loss, while binary quantization accelerates build times at the expense of recall, which can be recovered with a re-ranking pass, as discussed in the subsequent subsection. For most workloads, starting with halfvec is advisable, transitioning to binary quantization only when a re-rank pipeline is already established.
The benchmarks utilized datasets from OpenAI (5M vectors, 1536 dimensions) and Cohere (10M vectors, 768 dimensions). Amazon Titan Text Embeddings V2, with its 1024 dimensions, falls between these two, suggesting that the memory and build-time trade-offs should apply proportionally. Validate against your own data before committing to a quantization strategy.
The following chart illustrates the memory and recall trade-off across float32, halfvec, and binary representations based on those benchmarks.
Two-stage retrieval with binary quantization
For performance, an HNSW index and its full-precision vectors must remain memory-resident. When the working set exceeds available RAM, options include shrinking the in-memory representation (quantization), extending effective memory with Aurora Optimized Reads, or both. Binary quantization combined with re-ranking achieves this reduction: a coarse candidate selection operates on compact binary vectors that fit easily, while re-ranking by cosine distance retrieves full-precision vectors only for the final top-N results.
The following SQL pattern implements this two-stage retrieval: Hamming distance for the initial pass, cosine distance for re-ranking. The re-rank is responsible for recovering most of the recall lost to quantization.
-- Coarse recall pass (fast, on binary-quantized vectors), then re-rank by cosine
SELECT id, content
FROM (
SELECT id, content, embedding
FROM documents
WHERE tenant_id = ''
ORDER BY binary_quantize(embedding)::bit(1024)
binary_quantize('')
LIMIT 100
) candidates
ORDER BY embedding ''
LIMIT 10;The outer query processes only the 100 coarse candidates, allowing the costly cosine comparison to run on a smaller set. The inline binary_quantize() cast is included for clarity. For production at scale, materialize the binary vectors in a stored column and index them with bit_hamming_ops to ensure the coarse pass utilizes an index rather than computing quantization at query time. For benchmarks of this pattern on pgvector 0.8.0 and Aurora, refer to Supercharging vector search performance and relevance with pgvector 0.8.0 on Amazon Aurora PostgreSQL.
Three parameters in HNSW are worth tuning:
m: maximum connections per layer. Higher values enhance recall but increase memory usage and build time. AWS recommends starting withm = 16.ef_construction: dynamic candidate list size during index construction. Higher values improve index quality at build time. AWS suggestsef_construction = 128, while the pgvector default is 64.ef_search: dynamic candidate list size during querying. Higher values improve recall but may impact query latency. The pgvector default is 40, which is often insufficient for production. Tune this parameter based on workload requirements, avoiding hardcoding.
Partitioning is essential for managing individual indexes. Consider partitioning by tenant for multi-tenant workloads, by time for append-heavy workloads such as event or log data, or by category when queries are limited to a known subset. Partitioning also facilitates parallel index builds and per-partition rebuilds.
For bulk loads, it is advisable to defer indexing. Load the data first, then build the index once at the end. Inserting into a live HNSW index is slower than performing a single post-load build, and the resulting index is typically better structured.
When the working set outgrows RAM
While maintaining RAM residency is crucial, as datasets grow, it may become uneconomical to keep the entire HNSW graph in shared_buffers. Aurora Optimized Reads can extend effective cache capacity by up to 5x the instance memory, utilizing local NVMe storage as a tiered cache, resulting in up to 8x lower read latency for queries that would otherwise require fetching from Aurora storage. The feature documentation identifies pgvector nearest-neighbor search across millions of vector embeddings as a target use case.
Optimized Reads tiered cache necessitates an Aurora I/O-Optimized cluster on an NVMe-backed instance family (r6gd, r8gd, or r6id) and is enabled automatically on these instance classes. In contrast, an Aurora Standard cluster provides only temporary object acceleration, not tiered cache. Monitor the AuroraOptimizedReadsCacheHitRatio Amazon CloudWatch metric alongside BufferCacheHitRatio to assess how much traffic misses RAM yet still accesses NVMe instead of storage.
It is essential to view tiered cache as an extension of the memory budget rather than a substitute: RAM remains faster than NVMe, and NVMe is quicker than Aurora storage. Size the instance to ensure the hot working set resides in RAM, allowing Optimized Reads to manage the long tail.
Managing index churn
HNSW indexes experience degradation due to deletes and updates, as each modification leaves an invalid entry in the graph. Over time, this accumulation negatively impacts recall and inflates index size, with no option for in-place compaction.
The following chart illustrates how recall declines over time between rebuilds and recovers at each scheduled REINDEX CONCURRENTLY.
Three effective patterns exist for production:
REINDEX CONCURRENTLYrebuilds the index without blocking writes, though it is resource-intensive. Schedule this during low-traffic periods and monitormaintenance_work_memheadroom. For large indexes, anticipate hours rather than minutes.
REINDEX INDEX CONCURRENTLY documents_embedding_hnsw_idx;Your write pattern will dictate the most suitable approach. Workloads primarily consisting of inserts with minimal updates can effectively utilize a scheduled REINDEX CONCURRENTLY. Conversely, workloads characterized by heavy updates or deletes are better served by partitioning (by time or batch) and independently rebuilding partitions. Workloads where relevance is time-sensitive, such as searches limited to the last 90 days, are ideal for the append-only pattern with periodic compaction of older partitions.
Capacity planning
Churn management influences the frequency of graph rebuilds, while capacity planning ensures that each rebuild and query has adequate memory resources. These two aspects are interconnected.
An HNSW index must remain memory-resident. When it spills to disk, search latency deteriorates due to the random traversal pattern, with each miss resulting in a page fault. Plan for the index to fit within RAM, allowing headroom for concurrent queries and maintenance tasks.
The HNSW graph consumes more memory than the raw vector data, primarily driven by the m parameter. Size Aurora instances to keep shared_buffers plus connection and work memory below the instance’s available RAM.
Opt for a memory-optimized instance class for Aurora PostgreSQL vector workloads, as the HNSW graph must fit in RAM. The Amazon Relational Database Service (Amazon RDS) r-series instance families are tailored for memory-bound workloads such as vector search. For the current list of supported instance classes, consult the Aurora DB instance classes documentation.
Adjust the following PostgreSQL parameters:
shared_buffers: Aurora sets a reasonable default, but verify that it accommodates your expected index working set.effective_cache_size: informs the query planner about available OS-level cache. Set this to approximately 75% of instance memory on Aurora.maintenance_work_mem: critical during index builds. If set too low,CREATE INDEXorREINDEXoperations may spill and slow down. pgvector emits aNOTICEwhen the HNSW graph exceedsmaintenance_work_mem. If this appears in your build logs, increase the parameter on the instance and re-run the build.work_mem: per-operation memory allocation. Lowwork_memcan lead to spills with numerous concurrent vector queries, while excessively high values may induce memory pressure under load.max_parallel_maintenance_workers: pgvector 0.7.0 introduced parallel HNSW builds. Set this parameter to leverage larger instances during index creation.
For pricing details, refer to the Amazon Aurora pricing page.
Observability
Effective tuning relies on visibility. Four layers of observability are crucial for pgvector: query-level statistics, instance-level metrics, wait-event analysis, and application-defined custom metrics, complemented by connection handling.
Query-level statistics are derived from pg_stat_statements and the Aurora-specific aurora_stat_statements function. The Aurora variant includes storage I/O and peak memory columns, which are significant for vector queries since a spilling search exhibits different characteristics compared to a cached one. To identify the top vector queries by total time and peak memory:
SELECT substring(query, 1, 80) AS query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
max_exec_peakmem
FROM aurora_stat_statements(true)
WHERE query ILIKE '%%' OR query ILIKE '%%'
OR query ILIKE '%%' OR query ILIKE '%%'
ORDER BY total_exec_time DESC
LIMIT 10;total_exec_time is measured in milliseconds, and max_exec_peakmem is in bytes. The peak memory columns necessitate Aurora PostgreSQL 16.3, 15.7, or 14.12 and higher. For earlier minor versions, remove max_exec_peakmem from the SELECT statement.
Instance-level metrics in CloudWatch provide insights into whether the index fits in memory. Monitor BufferCacheHitRatio (ideally above 99% for healthy vector workloads), SwapUsage (should remain at zero), and ReadIOPS (sustained high values in a read-heavy vector workload suggest the index is spilling).
Amazon CloudWatch Database Insights highlights slow queries along with wait event breakdowns, enabling identification of vector queries hindered by I/O or lock contention.
Custom metrics bridge the gap between database health and application performance. Two noteworthy metrics to implement include:
- Recall tracking: periodically execute a fixed set of evaluation queries with known correct top-k results, compare the output from pgvector against the expected set, and emit the recall percentage to CloudWatch. Declines in recall indicate index drift or regression in query parameters.
- p99 latency for vector searches, categorized by query type. Tail latency for vector searches may shift before CPU or memory metrics do, as a handful of queries that evict the HNSW graph from cache can degrade recall and latency without impacting averages.
Monitoring connection handling is also essential. Vector queries are memory-intensive, so an over-subscribed connection pool can quickly exhaust work_mem. Utilize Amazon RDS Proxy in front of Aurora and keep an eye on DatabaseConnections, ClientConnections, and MaxDatabaseConnectionsAllowed.
Clean up
If you created the baseline documents table and its indexes to follow along with the examples, it is advisable to remove them after completion to avoid incurring ongoing storage charges for unused data:
DROP INDEX IF EXISTS documents_embedding_hnsw_idx;
DROP INDEX IF EXISTS documents_tenant_idx;
DROP TABLE IF EXISTS documents;The vector extension itself does not incur charges, but if it was enabled solely for these examples, you can remove it with DROP EXTENSION IF EXISTS vector;. Avoid dropping the extension if other databases within the cluster are utilizing it.
If you provisioned a dedicated Aurora PostgreSQL-Compatible cluster for testing, delete the cluster and its automated backups using the AWS Command Line Interface (AWS CLI) once testing is complete. Aurora PostgreSQL-Compatible clusters incur charges for as long as they exist, including storage charges on stopped clusters, so it is prudent to delete the cluster after testing to halt these charges:
# Delete all DB instances in the cluster first
aws rds delete-db-instance
--db-instance-identifier
--skip-final-snapshot
# Then delete the cluster
aws rds delete-db-cluster
--db-cluster-identifier
--skip-final-snapshot
--delete-automated-backupsManual snapshots persist after a cluster is deleted and continue to incur storage charges until explicitly removed. If any were taken during testing, delete them:
# Remove any manual snapshots you took during testing
aws rds delete-db-cluster-snapshot
--db-cluster-snapshot-identifier Replace , , and with the values from your test environment. The --skip-final-snapshot flag is suitable for a disposable test cluster. Avoid using it on a cluster containing data you wish to retain.
Key takeaways
Before going live, ensure the following five points are addressed:
- Plan for HNSW rebuilds from the outset. Choose
REINDEX CONCURRENTLYon a schedule, partition-based rebuilds, or append-only with compaction based on your write pattern. - Explicitly set
hnsw.ef_searchat the session or query level. The default (40) is frequently too low for production recall, with a value of 100 serving as a reasonable starting point. - Size
maintenance_work_memto accommodate your largest index build, allowing for headroom. A build that spills to disk results in a lower-quality graph. - Consider concurrent vector searches when determining connection quotas. Each concurrent query consumes
work_mem. Multiply this out and utilize Amazon RDS Proxy in front of the cluster. - Monitor
BufferCacheHitRatioas the primary metric. It serves as the earliest indicator that an index has outgrown its instance.
