[Feature Request] Support for AWS Aurora PostgreSQL with PgVector (serverless) as an alternative to AWS OpenSearch Serverless in the version 2

## Describe the solution you'd like

Support for AWS Aurora PostgreSQL with PgVector (serverless) as an alternative to AWS OpenSearch Serverless in the version 2 architecture. This solution would leverage the PgVector extension to enable vector search directly within the Aurora PostgreSQL serverless database. It offers a cost-effective

## Why the solution needed

Cost Efficiency: AWS Aurora PostgreSQL serverless with PgVector significantly reduces costs compared to maintaining a separate AWS OpenSearch Serverless infrastructure. Aurora allows on-demand scaling, reducing costs during low usage periods while meeting scalability requirements during high-demand times.
Simplified Architecture & Cloud Native database.

## Additional context

PgVector: PgVector is an open-source extension for PostgreSQL designed for efficient similarity searches on high-dimensional vectors, commonly used in AI/ML-driven applications.

[PgVector GitHub Repository](https://github.com/pgvector/pgvector)
[AWS Aurora PostgreSQL PgVector support announcement (if available)](https://aws.amazon.com/blogs/...)

Cost Comparison:

Aurora PostgreSQL Serverless + PgVector offers reduced costs compared to OpenSearch Serverless, particularly for smaller-scale applications or those with intermittent usage patterns.
Operational overhead is reduced, as Aurora PostgreSQL serverless requires less tuning and management compared to maintaining a dedicated OpenSearch cluster.

## Implementation feasibility

Are you willing to collaborate with us to discuss the solution, decide on the approach, and assist with the implementation?
No, I am unable to implement the feature, but I am open to discussing the solution.

The issue names no files, tests, or entry points. Start by locating the version 2 AWS OpenSearch Serverless integration and determine where an Aurora PostgreSQL with PgVector alternative would connect; done means the alternative architecture, configuration, and validation criteria are defined and implemented.

Written by the indexing model from the issue text.