Deep Dive into Amazon Aurora and Its Innovations (DAT441)

Session Notes

Aurora Overview

• Amazon Aurora is a cloud-native relational database
• Combines speed and availability of high-end commercial databases with simplicity and cost-effectiveness of open source
• Fully managed, compatible with MySQL and PostgreSQL
• Includes tools for integration with serverless and machine learning applications
• No application changes needed to use Aurora
• Supports extensions for PostgreSQL
• Recent addition: Aurora DSQL (Distributed SQL)
• Aurora Architecture:
• Utilizes 3 availability zones
• Features Aurora storage spanning all 3 availability zones
• Storage nodes handle log records which are converted into database pages

Key Features of Aurora Storage

• No traditional database issues like checkpoints, full page writes, double write buffers, or log archival
• Data is redundantly stored with 6 copies across 6 storage nodes in 3 availability zones
• Automatic peer-to-peer repair for broken or missed log records or failed storage nodes
• Replica Management:
• Ability to add up to 15 read-only replicas without consistency lag
• Asynchronous invalidation and update between head nodes
• Replicas can be of different sizes and instance types (e.g., Graviton, Intel)
• Storage automatically expands up to 256 terabytes
• Automatic Failover:
• Promotion of a replica to read-write node upon failure
• DNS-based endpoint change for application rerouting
• Advanced rapid drivers available for faster (up to 66% quicker) failover
• Multi-Region Capabilities:
• Asynchronous replication of storage across regions (Global Database)
• Replication handled by Aurora, not requiring application changes
• Low latency region-local reads possible by running applications in secondary regions
• Up to 10 regions can be utilized for global database setup
• Technical Note:
• Head nodes are not involved in the replication process
• Read-only replicas in secondary regions maintain near-instantaneous data visibility with typical RPO lag of less than a second

Global Endpoint and Failover

• Global endpoint is a DNS name pointing to the current writer region
• In case of a problem in the primary region, a failover operation is issued
• Failover promotes a replica in another region to the writer node
• Potential data loss may occur due to asynchronous replication
• DNS name for the global endpoint is updated via Route 53 data plan API
• Global Database Switchover:
• Switchover occurs when both regions are healthy, and writer is moved for operational reasons
• Recent improvements reduced switchover time from 5 minutes to 30 seconds
• Utilizes log-based architecture to achieve zero data loss (RPO of 0) and 30 seconds recovery time (RTO of 30 seconds)
• Local Write Forwarding:
• Allows writes from a secondary availability zone to be forwarded to the writer node
• Does not convert the system into an active-active configuration
• Writes from availability zone 2 are forwarded to the writer node in availability zone 1, executed, and results are returned
• Enhances operational flexibility without compromising data integrity or system design
• Consistency Modes in Aurora:
• Three different consistency modes/visibility modes available
• Default mode is session visibility, which waits for updates to be acknowledged before proceeding with reads
• Eventual consistency mode does not wait for updates, potentially leading to reads not reflecting the latest writes
• Global consistency mode waits for all updates across the entire cluster before proceeding, ensuring reads reflect all writes
• Local Write Forwarding:
• Allows writes from a secondary availability zone to be forwarded to the writer node
• Maintains the same consistency modes and considerations as local operations
• Cross-region latency differences are a factor in global write forwarding
• Aurora Storage Node Operations:
• Each storage node runs a "storage demon" that facilitates communication between the engine and storage
• Log records are written into an incoming queue, drained to a hot log on disk, and then acknowledged
• Missing log records (e.g., due to transit delays) can be fetched peer-to-peer from other storage nodes
• Log records are coalesced into database pages, ensuring read requests are served with minimal latency impact
• Continuous backup to S3 allows for point-in-time restores within the last 35 days

IO Optimization Configuration

• Adjusting configuration options to enhance throughput for IO-intensive applications.
• Modifying storage driver for more aggressive batching, beneficial for IO-heavy applications.
• Utilizing modern engine versions for further optimization.
• Changing incoming queue to a durable queue to reduce latency and jitter, improving performance.
• Aurora PostgreSQL Compatibility and Updates:
• Aurora PostgreSQL is fully compatible with PostgreSQL.
• Regular integration of PostgreSQL updates from upstream.
• Support for PostgreSQL up to version 17.6.
• Compatibility with RAG instance type with local disk.
• Numerous performance improvements from upstream including correlated subquery cache, adaptive joins, etc.
• Introduction of shared plan cache for memory efficiency.
• Enhanced read availability for large instances and replicas.
• Implementation of FIPS 140-3 security encryption.
• Updates to various extensions, notably PG vector.
• Support for volumes up to 256 terabytes.
• Aurora PostgreSQL Performance Analysis:
• Throughput evaluation across different instance generations.
• Example test:
• CPU-bound performance with R52.4Xlarge (older instance) vs. R7i.48Xlarge (newer, larger instance) showing a 2.1 times performance improvement.
• Introduction of R8G (Graviton version, next generation) showing a 2.7 times improvement over the baseline, indicating better-than-linear scaling.
• Better-than-linear scaling means a system or process improves its performance or efficiency disproportionately well as resources (like data size or nodes) increase, often seen in logarithmic growth or specialized architectures, contrasting with linear growth (output doubles if input doubles) or sub-linear scaling (diminishing returns, Amdahl's Law limit), with applications in data visualization, database performance, and computational efficiency.

Aurora PostgreSQL Dynamic Data Masking

• New feature for protecting sensitive data in Aurora.
• Sensitive data include account numbers, account holder names, and personally identifiable information (PII).
• Dynamic Data Masking in Aurora PostgreSQL:
• Designed for protecting sensitive data by providing rounded or masked versions of data.
• Implemented via the new PG column mask extension in Aurora.
• Allows definition of masking policies for specific tables and columns.
• Example:
• Masking customer ID as X's and rounding account balances.
• Policies apply to specified roles (e.g., 'analyst') with defined weights for conflict resolution.
• Implemented in the query rewrite layer for efficient performance without affecting indexes.
• Implementation Details of Dynamic Data Masking:
• The masking function is applied during the query rewrite phase, not post-fetch.
• Ensures good performance and maintains index functionality.
• Visible in the query description output where the mask function is applied to targeted columns.
• Aurora MySQL Updates:
• Recent release of version 3.11 with MySQL 8.0.43 compatibility.
• Introduction of a 3.10.0 long-term support version with enhanced features.
• In-memory relay log cache for up to 40% improved log replication throughput.
• Support for ODBC in advanced rapid drivers for MySQL. ODBC (Open Database Connectivity) is an API that enables applications to access data from various database management systems using a standard language, SQL. It acts as a bridge, allowing a single application to connect to different databases, such as SQL Server, MySQL, or Oracle, without needing to be rewritten for each one.
• Increased volume support up to 256 terabytes.
• Global database secondary readers for improved read availability during global issues.

Aurora Serverless

• Recommended instance type for streamlined fleet management.
• Automatically adjusts CPU and memory based on workload demands.
• Renamed from Aurora Serverless V2 to just Aurora Serverless following the end of life for V1.
• Offers an elastic instance type for flexible resource allocation.
• Aurora Serverless Features and Benefits:
• Automatically scales memory, CPU, and network without engine reboots.
• Scaling occurs every second, measured in Aurora Capacity Units (ACUs), each representing 2 GB of RAM and associated resources.
• Scales in response to Lambda functions or analytics jobs, adjusting ACUs based on demand.
• Priced per second, users only pay for the resources actually used.
• Scaling Mechanism in Aurora Serverless:
• Operates using a scaling bucket that periodically fills with credits, allowing scaling up to a set maximum ACU limit.
• Credits are consumed when scaling is needed, with the bucket refilling over time.
• Recent enhancements include faster trigger responses (under 1 second), a larger initial bucket size, and quicker refill rates.
• Performance Improvements with Platform Version 3:
• Offers up to 30% improved performance without requiring any selection or additional configuration.
• Available for all new clusters, accessible by restoring from a backup, cloning, or creating a new cluster.
• Performance Examples:
• Tests comparing platform version 2 with and without fast scaling showed a 3.6 times longer period of peak performance and 9% fewer ACU hours used with fast scaling.
• Tests with platform version 3 (combined with fast scaling) demonstrated a 20% reduction in runtime and ACU hours used compared to platform version 2, leading to 20% lower billing.
• Both faster scaling and platform version 3 are automatically applied to new clusters, offering enhanced performance and cost efficiency with no extra effort from the user.

Create with Express Configuration

• Rapid creation of database clusters in seconds.
• Targeted at users who frequently create clusters, such as those in CI/CD pipelines or agentic AI applications.
• Enables the creation of a new database for each interaction, enhancing agility and speed.
• Features of Create with Express Configuration:
• Offers a flexible and editable configuration, starting with 16 ACUs (Aurora Capacity Units).
• Allows modification of most settings post-creation, including the ability to change ACUs and other configurations.
• Secure by default with encryption and IAM (Identity and Access Management) enabled.
• Supports nearly all Aurora features, including global databases and zero ETL.
• No VPC Requirement:
• Introduces the Aurora Internet Access Gateway, a new component that facilitates access to the database cluster without the need for a VPC (Virtual Private Cloud).
• The Internet Access Gateway is a highly available endpoint compatible with the PostgreSQL wire protocol, minimizing latency.
• Allows access to the DB cluster from anywhere, reducing the need for VPNs and simplifying the process for developers.
• Integrates with Amazon IAM and AWS Shield for enhanced security features like fraud protection and data safeguarding.
• AWS Shield is a managed service for protecting applications running on Amazon Web Services (AWS) from Distributed Denial of Service (DDoS) attacks. It offers two tiers: the free AWS Shield Standard, which automatically mitigates common network layer attacks, and the paid AWS Shield Advanced, which provides protection against more sophisticated attacks, advanced features, and expert support.

Integration of Aurora with Agentic AI

• Aurora can serve as memory for agentic AI systems, essential for the functioning of agentic AI loops.
• Example using the "Letter" framework: creating a DB cluster with express configuration, enabling the PG vector extension for vector embeddings, and pointing the Letter framework to the PostgreSQL endpoint.
• Aurora PostgreSQL can be used for genetic AI memory across various frameworks like LangChain.
• MCP (Model Context Protocol) servers, open-sourced by AWS, allow natural language queries that understand the schema of an Aurora database, converting them into SQL queries. This is beneficial for both new users exploring the database and power users seeking efficiency.
• Patching and Upgrades in Aurora:
• Aurora offers a managed experience for patching and upgrading clusters, with pending maintenance actions visible in the console.
• OS patches are applied in a rolling fashion across multiple nodes in a cluster, enhancing availability.
• Users can set a maintenance window for updates and receive notifications about maintenance actions.
• Automation of maintenance actions is optional, allowing users to control upgrades through their own systems like Terraform.
• For fleets of clusters, sequencing upgrades across development, QA, and production environments is crucial.
• Sequential Upgrades for Aurora Clusters:
• Importance of sequencing upgrades across different environments (dev, QA, prod) to allow for testing and reaction time.
• Challenge with random maintenance announcements potentially upgrading QA environments before dev, contrary to desired order.

AWS Organization's Upgrade Roll-Out Policy

• Introduced to address the sequencing issue, allowing upgrades to be broken into waves (1st, 2nd, last).
• The process involves notifying users, waiting for the maintenance window of the first wave, upgrading those instances, baking (testing period), and then proceeding to the next wave.
• Provides ample time for users to react or opt-out if issues arise.
• Upgrades proceed in sequence regardless of the day of the week an update is released.
• Implementation of the Upgrade Roll-Out Policy:
• Requires enabling AWS Organizations and placing accounts within the organization.
• Normal setup involves enabling automatic minor version upgrades and setting a maintenance window.
• The policy is set by associating tags with resources; for example, tagging resources with "env=prod" for the last wave.
• Resources without recognized tags default to a specified wave (e.g., second wave).
• Users can choose to not use tags and accept defaults for a one-time upgrade across all resources.
• Applying the Upgrade Roll-Out Policy:
• After creating the policy, it can be attached to the entire organization (root) or individual accounts for selective application.
• During an upgrade, users will see notifications in pending maintenance actions and AWS Health, indicating the wave in which the upgrade will occur.

Blue-Green Deployments for Major Version Upgrades

• Blue-green deployments are essential for major version upgrades that are incompatible with the current version and require downtime if done in-place.
• The process involves creating a complete copy of the production environment (the "green" environment), syncing data using logical replication, and keeping it in sync as long as the blue-green deployment is running.
• Allows for thorough testing of the upgraded environment before switching over.
• The switchover, facilitated through CLI or console, renames AWS resources and switches the endpoint to the green environment, ensuring no data loss and minimal downtime (typically less than a minute).
• Provides a fallback option if the switchover times out, automatically reverting to the original (blue) environment.
• Useful not only for version upgrades but also for schema changes, static parameter changes, and maintenance updates deemed too risky for in-place changes.
• Blue-Green Deployments for Global Databases:
• Recent support for global databases allows for major version upgrades across regions using blue-green deployments.
• The process mirrors that of single-region deployments, with logical replication ensuring data synchronization and minimal downtime (about 1 minute for switchover).
• Enables a major version upgrade of the entire global cluster with zero data loss (RPO of 0) and a recovery time objective (RTO) of about 1 minute.

Aurora's Zero ETL Feature

• Facilitates low-latency data transfer from Aurora PostgreSQL to Redshift without managing an ETL pipeline.
• Achieved through a CLI command that creates a pipeline between Aurora and Redshift clusters.
• Offers a replication lag of 5 to 12 seconds, ensuring quick data availability in Redshift.
• Manages data seeding, maintenance, and change data capture (CDC) streaming, relieving users of these complexities.
• Supports integration of multiple Aurora clusters (both MySQL and PostgreSQL) into a single Redshift cluster or data lake.
• Also enables data flow from Aurora MySQL to SageMaker using the same technology.
• Underlying Technology of Zero ETL:
• Utilizes parallel direct export at the storage layer to quickly seed data into Redshift without impacting performance.
• Employs enhanced bin logs (for MySQL) or equivalent for PostgreSQL, embedded in the storage, to capture logical replication logs.
• CDC streaming servers read logs directly from storage, applying filters and modifications before pushing data to Redshift, ensuring no performance impact on the head node.
• Aurora Storage Types:
• Aurora Standard: Default storage type offering basic performance and cost characteristics.

Detailed Explanation of Aurora Storage Costs and Performance

• Read operations: If data is cached, there's minimal cost (a few milliseconds of instance running time). If the cache is missed, data is read from storage, costing a fraction of a cent per 8K or 16K database page, depending on the database engine.
• Write operations: Up to 4 kilobytes costs a fraction of a cent. Larger writes may be batched into up to 4 I/Os, with each batch costing a fraction of a cent, depending on its size.
• Aurora I/O Optimized Storage:
• Offer more predictable pricing and potentially better performance.
• A cluster-level configuration that can be chosen at cluster creation or switched online.
• Changes both system performance and architecture, including storage-side changes.
• Recommended for users whose Aurora bill shows an I/O proportion of more than 25%, as it likely offers cost savings and better performance.
• Available in all modern engine versions from the last 18 months and compatible with database savings plans.
• Cost Model of I/O Optimized Storage:
• Users pay a slight premium for compute and storage but have zero cost for I/Os, making billing simpler and more predictable.
• Ideal for workloads with significant I/O operations, as the premium cost for compute and storage is offset by the elimination of I/O costs.
• Performance Improvement with I/O Optimized:
• Demonstrated through a test on a 16X large instance, showing a 1.9x improvement in throughput with I/O optimized enabled on an R6I 16X large instance running version 14.
• Further improvement (an additional 10%) observed when upgrading to an R7I instance with version 16 PostgreSQL.

Latency and Throughput Improvements with I/O Optimized

• Latency Test: Demonstrates a significant improvement in latency, with a 3 times improvement at the low end and a 6.4 times improvement at the high end when using I/O optimized on an R8G 48X large instance running version 17 PostgreSQL.
• Throughput Test: Shows a 5 times improvement in throughput in the same conditions, highlighting the dual benefits of I/O optimized for both latency and throughput.
• Temporary Objects and Local NVMe Storage:
• Local NVMe storage refers to blazing-fast, physically attached solid-state drives (SSDs) within a server or cloud instance, offering extremely low latency and high throughput, perfect for demanding apps (AI/ML, databases) needing temporary, high-performance scratch space.
• Speed: Uses PCIe bus, bypassing network/controller bottlenecks for sub-millisecond latency and massive IOPS.
• Direct Attachment: Physically inside the server/instance chassis (like AWS EC2 or Azure VMs).
• Ephemeral: Data is temporary; it doesn't survive instance termination, failure, or sometimes even restarts/maintenance.
• Use of temporary objects in PostgreSQL for operations like index rebuilds or large sorts, which may exceed memory and require disk storage.
• In a normal system, this disk is an EBS disk, introducing performance considerations.
• With D instances like RAGD, local NVMe instance storage is used instead, allowing for up to 6 times the memory size to be allocated for spills, significantly reducing latency and improving performance for workloads using these temporary objects.
• Impact of I/O Optimized on Temporary Object Storage:
• I/O optimized reduces the allocation for temporary objects from 6 times the memory to 2 times the memory, still providing a performance boost but using less storage space.
• The savings in storage space are utilized for a feature called tiered cache, which is designed to further enhance performance by providing an additional layer of caching.

Tiered Cache

• Utilizes the saved space from reducing temporary object storage allocation under I/O optimized.
• Aimed at improving performance by adding an extra layer of caching, though specific details on how tiered cache operates are not provided in the excerpt.
• Regular Reads Process:
• Initial check in shared buffers; if data is found, it's read back with no further action needed.
• If data is not in shared buffers, it's read from storage into shared buffers and then provided to the engine.
• Optimized Reads with D Instances:
• Allocates 4 times the memory on disk for tiered cache, used similarly to shared buffers.
• Metadata is used to determine if requested data is in the disk cache.
• Data read from storage is placed in shared buffers; when buffers are full, data is evicted to a tiered cache with metadata tracking.
• Handling Updates with Tiered Cache:
• Updates invalidate data in tiered cache by flipping a metadata bit, avoiding frequent writes to tiered cache.
• Elastic and Dynamic Tiered Cache:
• Ability to adjust the size of tiered cache versus temporary objects based on workload needs.
• Increases temp space for disk spill-heavy workloads and decreases it for larger tiered cache post-workload.

R8GD Instance Performance

• New instance type with the same price as R6GD but offering a 165% performance improvement.
• Encouraged for users of tiered cache and those considering its use.
• Performance Improvement with Tiered Cache:
• Reduced latency in read operations, especially for data exceeding memory capacity.
• For a 340 GB test, tiered cache shows a 1.5% increase in latency compared to memory, and a 3 times deficit versus an 8 times increase without tiered cache.
• Pareto random distribution tests show even better performance, with only a 1.4 times latency reduction for large tests.
• Real-world Use Case:
• PG vector benchmark shows over a 3.5 times improvement in queries per second with optimized reads (R7GD vs. R8G).
• Comparing R6GD to 8GD (both at the same price) yields a 1.6x improvement in vector performance.
• R: Indicates a memory-optimized instance family.
• 7: Denotes the current generation (7th generation).
• g: Signifies that the instance uses AWS Graviton processors (Arm architecture).
• d: Indicates the inclusion of local NVMe-based SSD storage.
• Amazon EC2 R8g instances, powered by AWS Graviton 4 processors, representing AWS's latest generation of memory-optimized instances for demanding, in-memory workloads like large databases, real-time analytics.
• Overview of Aurora PostgreSQL vs. Aurora MySQL:
• Aurora PostgreSQL: Fully compatible with PostgreSQL, uses single writer architecture with implicit or explicit locking for concurrent writes.
• Aurora MySQL: Utilizes active-active query processors for multiple writes, employing optimistic concurrency control to manage conflicts.
• Scaling out in Aurora PostgreSQL involves adding replicas with asynchronous replication, raising consistency questions and leveraging caching for performance.
• Key Differences:
• Concurrency Handling: PostgreSQL uses locking; MySQL uses optimistic concurrency control.
• Write Architecture: PostgreSQL has a single writer; MySQL supports multiple active writers.
• Scaling Mechanisms: PostgreSQL scales through replicas; MySQL scales through additional query processors.

Detailed Comparison of Aurora PostgreSQL and Aurora MySQL

• Aurora PostgreSQL: Uses an active-passive architecture with a single writer and explicit locking for concurrency. Supports scaling through adding replicas and uses asynchronous replication.
• Aurora MySQL (DSQL): Employs an active-active architecture with multiple query processors for writes, using optimistic concurrency control. Scales independently for reads with a distributed block store.
• Read and Write Process in Aurora:
• Reads: Data is fetched from storage servers across availability zones (AZs) by a chosen query processor.
• Writes: Query processor spools up write operations until transaction commit. Upon commit, writes are sent to adjudicators (for conflict resolution) and then to journals (for log storage across AZs), which update impacted storage shards.
• Multi-region Operation:
• Aurora PostgreSQL: Uses asynchronous replication for cross-region data, resulting in in-region commit latency.
• Aurora MySQL: Commits are synchronously replicated across regions, leading to cross-region commit latency as a fundamental difference.