Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Aurora Serverless V2: pgvector HSNW index not used

0

Dear all,

The setup:

  • I am running an aurora serverless V2 cluster with one writer instance (allowed it to run up to 256ACUs).
  • I am storing embeddings in a table, using pgvector's vector extension (1024 dimensions).
  • An HSNW index was created; it consumes around 90GB.
CREATE INDEX IF NOT EXISTS titan_dims_1024_idx_cosine 
ON public.langchain_pg_embedding 
USING hnsw (((embedding)::public.vector(1024)) public.vector_cosine_ops) 
WHERE (collection_id = 'my-uuid'::uuid);

The challenge: I cannot get even a basic query to use the HSNW index. It always falls back to seq scan (EXPLAIN)

SELECT *
FROM langchain_pg_embedding
WHERE collection_id = 'my-uuid'
ORDER BY embedding <=> '[my-vector]'
LIMIT 4;

"Limit  (cost=5579931.98..5579932.45 rows=4 width=1528)"
"  ->  Gather Merge  (cost=5579931.98..6905972.03 rows=11365264 width=1528)"
"        Workers Planned: 2"
"        ->  Sort  (cost=5578931.96..5593138.54 rows=5682632 width=1528)"
"              Sort Key: ((embedding <=> '[my-vector]'::vector))"
"              ->  Parallel Seq Scan on langchain_pg_embedding  (cost=0.00..5493692.48 rows=5682632 width=1528)"
"                    Filter: (collection_id = 'my-uuid'::uuid)"

What I tried:

  • Enforcing settings with a new parameter group:
SET enable_seqscan = off;
SET maintenance_work_mem = '4GB';
SET effective_cache_size = '32GB';
  • Setting minimum ACUs to 64 and 128

Any input is welcome, M.

Topics
ServerlessDatabase
Tags
Aurora PostgreSQLServerlessDatabasePerformance InsightsAmazon Aurora
Language
English

asked a year ago458 views

3 Answers
0
Accepted Answer

Problem resolved (thx to the guys over at pgvector!):

The issue was that the query is not using the right expression as defined in the index itself:

ORDER BY embedding::vector(1024) <=> '[0.0028470755,...]

answered a year ago

EXPERT

reviewed a year ago

0

The issue you're experiencing with Aurora Serverless v2 not using the HNSW index for your pgvector query is likely related to the limitations and requirements of Aurora Serverless v2, rather than a problem with your index or query setup.

Aurora Serverless v2 has some specific requirements and limitations that can affect query performance and index usage. One important factor to consider is that some Aurora features might not perform optimally if your capacity range is lower than needed for the memory requirements of those features with your specific workload.

In your case, the HNSW index you've created is quite large at 90GB. This size suggests that it might require more memory than is currently available in your Aurora Serverless v2 configuration, even though you've allowed it to scale up to 256 ACUs.

To address this issue, you could try the following:

  1. Increase the minimum capacity setting for your Aurora Serverless v2 cluster. You mentioned setting it to 64 and 128 ACUs, but given the size of your index, you might need to set it even higher to ensure enough memory is consistently available.

  2. Monitor your cluster's performance and resource utilization closely. Look for signs of memory pressure or out-of-memory errors, which could indicate that your current capacity range is insufficient for your workload.

  3. Consider splitting your data into smaller partitions if possible. This could help reduce the size of individual indexes and make it easier for Aurora Serverless v2 to manage the memory requirements.

  4. If these steps don't resolve the issue, you might want to consider using a provisioned Aurora instance instead of Serverless v2 for this particular workload. A provisioned instance would give you more control over the available resources and might be better suited for managing large indexes like the one you've created.

Remember that Aurora Serverless v2's automatic scaling is designed to handle varying workloads, but it may not always be the best fit for all types of database operations, especially those involving very large indexes or memory-intensive operations.
Sources
Requirements and limitations for Aurora Serverless v2 - Amazon Aurora

answered a year ago

EXPERT

reviewed a year ago

AWS
SUPPORT ENGINEER

reviewed a year ago

  • Mahdi Manesh
    a year ago

    Thx AI :-) This is exactly what I will aim to find out -- probably, I need to provision an Aurora "db.r6g.4xlarge" or higher. If anyone has experience with using pgvector embedding data with 15M+ vector records, I would highly appreciate any insights.

0

Hello Mahdi,

Thank you for sharing the solution to your problem. Your contribution helps the community, as others searching for similar issues on re:Post can benefit from this clarity. This serves as a valuable reminder of the importance of aligning query syntax with index definitions for optimal performance.

Even after implementing the suggestions from the pgvector community, if you feel that persistent issues are specifically related to your Aurora Serverless setup, I would recommend creating a support case with AWS. Include details of the resources involved and the specific queries causing problems to allow for a more in-depth investigation.

AWS
SUPPORT ENGINEER

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content