Aurora PostgreSQL Archives

Tech Optimizer

June 1, 2025

Supercharging vector search performance and relevance with pgvector 0.8.0 on Amazon Aurora PostgreSQL

The Amazon Aurora PostgreSQL-Compatible Edition has integrated support for pgvector 0.8.0, enhancing vector search capabilities significantly. This version achieves up to 9x faster query processing and delivers 100x more relevant search results compared to previous iterations. Key improvements in pgvector 0.8.0 include: - Up to 5.7x improvement in query performance for specific patterns. - Introduction of the iterative_scan feature, enhancing recall for filter queries requiring approximate nearest neighbor (ANN) index searches. - Improved cost estimation for more efficient query planning, allowing the selection of traditional indexes for complex filtered searches. - Flexible performance tuning with two modes for iterative_scan: relaxed_order and strict_order. Previous versions faced challenges with overfiltering, where combining vector similarity search with traditional SQL filters resulted in fewer results than expected. The HNSW indexing algorithm accelerates vector similarity searches by creating a multi-layered graph structure for efficient navigation. Iterative index scans in pgvector 0.8.0 improve query reliability and performance by applying filters during the scanning process, reducing false negatives and enhancing performance. The configuration options for iterative scanning include off, strict_order, and relaxed_order, with relaxed_order providing the best balance between performance and accuracy. In performance tests, pgvector 0.8.0 demonstrated substantial improvements across various query patterns, with significant enhancements in result completeness and recall. The database can now efficiently handle enterprise-scale applications, such as e-commerce and recommendation systems, with high performance and result quality even as product catalogs expand. Best practices for deploying pgvector 0.8.0 include using HNSW with recommended parameters, creating additional indexes on commonly filtered metadata columns, and tuning query parameters based on specific use cases. Operational best practices involve using Graviton4-based instances for optimal performance, monitoring and tuning slow vector queries, and considering partitioning for very large tables.

Tech Optimizer

May 27, 2025

AWS adds observability support to Aurora PostgreSQL Limitless

Amazon Web Services (AWS) has enhanced its managed Aurora PostgreSQL Limitless service by integrating observability support through CloudWatch Database Insights, allowing enterprises to monitor the health of their database ecosystems. This feature assists in monitoring and troubleshooting various AWS databases, including Amazon Aurora PostgreSQL, Amazon Aurora MySQL, Amazon RDS for SQL Server, RDS for Oracle, and RDS for MariaDB.

Tech Optimizer

May 27, 2025

Immuta Deepens AWS Integration with Native Support for PostgreSQL on Amazon RDS and Aurora

Immuta has introduced native support for PostgreSQL on Amazon RDS and Amazon Aurora PostgreSQL-Compatible Edition, now available in public preview. This integration aims to streamline access to PostgreSQL data on AWS through automated policy enforcement and a centralized data marketplace. Key features include automated access provisioning, dynamic policy enforcement, and marketplace integration for PostgreSQL datasets. This support facilitates faster data access, enhances compliance with privacy regulations, and empowers organizations to manage PostgreSQL data effectively in cloud-native environments. The capability is available globally starting today, and the Immuta Platform can be accessed on the AWS Marketplace.

Tech Optimizer

May 24, 2025

Connect Amazon Bedrock Agents with Amazon Aurora PostgreSQL using Amazon RDS Data API

Generative AI applications are being integrated with relational databases, allowing organizations to utilize structured data for training AI models. This integration involves using the RDS Data API with Amazon Aurora PostgreSQL-Compatible Edition and Amazon Bedrock for AI model access and automation. The solution enables natural language queries to be converted into SQL statements, executed against the database, and returns results in a user-friendly format. The architecture includes several steps: invoking the Amazon Bedrock agent with natural language input, generating SQL queries using large language models (LLMs), executing those queries via the Data API, and returning formatted results. Security measures are in place to restrict operations to read-only, preventing modifications that could compromise data integrity. To implement this solution, prerequisites include deploying an Aurora PostgreSQL cluster using AWS CDK and setting up the necessary Lambda functions and IAM roles. The agent is designed to convert natural language prompts into SQL queries and execute them securely. Testing can be conducted through the Amazon Bedrock console or the InvokeAgent API, with options for tracing the agent's steps. Key considerations for this integration include limiting it to read-only workloads, implementing parameter validation to prevent SQL injection, and ensuring comprehensive logging and auditing. For multi-tenant applications, appropriate isolation controls should be established. To avoid future charges, all resources created through CDK should be deleted after use.

Tech Optimizer

May 21, 2025

Achieve up to 1.7 times higher write throughput and 1.38 times better price performance with Amazon Aurora PostgreSQL on AWS Graviton4-based R8g instances

Upgrading to Graviton4-based R8g instances with Aurora PostgreSQL-Compatible 17.4 in an Aurora I/O-Optimized cluster configuration results in significant performance improvements. The new instances provide up to 1.7 times higher write throughput, 1.38 times better price-performance, and reduce commit latency by up to 46% on r8g.16xlarge instances and 38% on r8g.2xlarge instances compared to Graviton2-based R6g instances. The Amazon Aurora PostgreSQL-Compatible Edition now supports AWS Graviton4-based R8g instances and PostgreSQL 17.4, which introduces performance enhancements for I/O-Optimized configurations, optimizing write operations and batch processing. R8g instances offer up to 192 vCPUs and 1.5 TB of memory, supporting larger configurations and providing up to 50 Gbps of network bandwidth. PostgreSQL 17 includes vacuum improvements, eliminates the need to drop logical replication slots during upgrades, and expands SQL/JSON standards. Aurora PostgreSQL-Compatible separates compute from storage, enabling independent scaling and maintaining six-way replication for durability, while processing changes as log records to reduce I/O operations. Performance benchmarks using HammerDB show improvements in throughput and commit latency across various workloads. For small workloads on 2xlarge instance size, throughput increased by 50.25% and commit latency improved by 33.87%. For medium workloads on 16xlarge instance size, throughput increased by 30% and commit latency improved by 17.44%. The most significant performance benefits arise from combining hardware upgrades from Graviton2 to Graviton4 with database engine upgrades from PostgreSQL 15.10 to 17.4. For small workloads, throughput increased by 70% and commit latency improved by 38.71%. For medium workloads, throughput increased by 70% and commit latency improved by 46.67%. Cost efficiency is also enhanced, with a 38% improvement in price performance and a 61.26% improvement in price-performance ratio when comparing Graviton2 and Graviton4 instances. Reserved Instances for Graviton4-based R8g instances offer additional cost-optimization opportunities.

Tech Optimizer

May 3, 2025

Understanding transaction visibility in PostgreSQL clusters with read replicas

On April 29, 2025, Jepsen released a report on transaction visibility behavior in Amazon RDS for PostgreSQL and its Multi-AZ clusters, which has been acknowledged since at least 2013. The report identifies a Long Fork anomaly affecting the visibility order of transactions between primary and replica nodes in cluster configurations, which does not lead to data loss or corruption and is absent in Single-AZ deployments. This anomaly allows two readers to see transactions in different sequences, breaching Snapshot Isolation. It affects all isolation levels in community PostgreSQL and can also occur in self-managed deployments. The issue has been discussed extensively in the PostgreSQL community, and potential solutions, including synchronizing visibility with commit order using Commit Sequence Numbers, have been proposed. AWS has established the PostgreSQL Contributors Team to address this anomaly and enhance PostgreSQL's capabilities.

Tech Optimizer

May 2, 2025

Amazon Aurora now supports PostgreSQL major version 17

Amazon Aurora has integrated support for PostgreSQL major version 17, specifically version 17.4. This release includes enhancements such as improved memory management, faster storage metadata initialization, and optimizations for write-heavy workloads on Graviton 4 instances. It also introduces new features for Babelfish, security updates, and revisions to extensions like pgvector 0.8.0 and postgis 3.5.1. PostgreSQL 17.4 is available across all commercial AWS Regions and AWS GovCloud (US) Regions. Users can create a new Aurora PostgreSQL-compatible database through the Amazon RDS Management Console and should review the Aurora documentation for upgrade strategies.

Tech Optimizer

April 16, 2025

Transition a pivot query that includes dynamic columns from SQL Server to PostgreSQL

The text discusses the process of migrating workloads from SQL Server to PostgreSQL, specifically focusing on the implementation of the PIVOT function. It highlights the limitations of using CASE WHEN statements in SQL Server for dynamic reports and introduces the crosstab function from PostgreSQL’s tablefunc extension as a more flexible alternative. The solution involves creating a PostgreSQL function called get_dynamic_pivot_data that utilizes crosstab to dynamically generate columns based on query results. To test the function, users need to provision either Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL and install a PostgreSQL client tool. For those using C#, .NET SDK 8 and Visual Studio Code must be installed. The text provides detailed steps for creating sample tables and stored procedures in SQL Server, along with the equivalent setup in PostgreSQL, including the use of the crosstab function and cursor management. It emphasizes the need for security measures, such as storing database credentials in AWS Secrets Manager. The author encourages sharing experiences related to implementing PIVOT functionality in PostgreSQL.

Tech Optimizer

April 11, 2025

Announcing pgvector 0.8.0 support in Aurora PostgreSQL

Amazon Aurora PostgreSQL-Compatible Edition has integrated support for pgvector 0.8.0, an open-source extension for storing vector embeddings in databases, enhancing its capabilities for generative AI applications, particularly in semantic search and retrieval-augmented generation. The new version improves the PostgreSQL query planner's index selection, elevating query performance and search result quality. It refines data filtering through WHERE clauses and joins, and features iterative index scans to prevent overfiltering, ensuring sufficient query results. Additionally, pgvector 0.8.0 enhances performance for searching and constructing HNSW indexes. This version is available in Amazon Aurora clusters on PostgreSQL versions 16.8, 15.12, 14.17, and 13.20 or higher, across all AWS Regions except China. Users can upgrade by modifying their DB cluster settings.

Tech Optimizer

April 10, 2025

Amazon Bedrock Knowledge Bases now supports hybrid search for Aurora PostgreSQL and MongoDB Atlas vector stores

Amazon Bedrock Knowledge Bases has expanded to support hybrid search for knowledge bases created with Amazon Aurora PostgreSQL and MongoDB Atlas vector stores. This enhancement allows for a dual-query approach that combines semantic and full-text search methodologies, improving the relevance of search results. Users can access hybrid search through the Knowledge Base APIs or the Bedrock console, where they can select hybrid search as their preferred method. Hybrid search functionality with Aurora PostgreSQL is available in all AWS Regions except Europe (Zurich) and GovCloud (US) Regions, while for MongoDB Atlas, it is available in the US West (Oregon) and US East (N. Virginia) AWS Regions.