Database Server

Amazon Aurora

Overview

Amazon Aurora is a cloud-native, fully managed relational database engine provided by AWS. Maintaining complete compatibility with MySQL and PostgreSQL, it delivers 5x the performance of standard MySQL and 3x the performance of PostgreSQL. With cloud-first design, Aurora provides 99.99% high availability, automatic scaling, and automatic failure recovery capabilities, supporting everything from enterprise-level workloads to rapidly growing applications. In 2025, the new Aurora DSQL became generally available, establishing Aurora's position as a leader in the distributed SQL market.

Details

Amazon Aurora 2025 edition has significantly enhanced its innovative feature set that pioneering the new era of distributed databases. Particularly noteworthy is the general availability of Aurora DSQL, which serves as a PostgreSQL-compatible serverless distributed database, implementing active-active high availability and multi-region strong consistency. PostgreSQL enhancements include up to 9x faster vector search using pgvector, and automated horizontal scaling through Aurora PostgreSQL Limitless Database. Storage automatically expands up to 128 TiB, Aurora Parallel Query improves analytical query performance by up to 100x, and Aurora Serverless provides fully automated on-demand auto scaling.

Key Features

• Cloud-Native Design: 5x/3x performance of MySQL/PostgreSQL respectively
• Fully Managed: No server management, patching, or high availability configuration required
• Auto Scaling: Automatically adjusts capacity based on workload
• High Availability: Designed for 99.999% multi-region availability
• Distributed Capabilities: Distributed SQL database through Aurora DSQL
• Compatibility: Existing MySQL/PostgreSQL applications and tools work without modification

Pros and Cons

Pros

• Overwhelming integration and complete automation of operational management in AWS environments
• Performance and scalability far exceeding standard MySQL/PostgreSQL
• 99.99% high availability with Multi-AZ and global replication support
• Complete pay-as-you-use pricing with Aurora Serverless
• Enterprise-grade security and backup/recovery capabilities
• Migration possible without changes to existing applications

Cons

• Strong lock-in to AWS environment and vendor dependency
• Higher operational costs compared to MySQL or PostgreSQL
• Not available in on-premises environments
• Limitations on some advanced PostgreSQL features or MySQL extensions
• Aurora DSQL is relatively new service with limited track record
• Costs may increase more than expected for large-scale databases

Reference Pages

• Amazon Aurora Official Page
• Amazon Aurora Documentation
• Amazon Aurora Pricing

Code Examples

Aurora MySQL Basic Setup

# Create Aurora MySQL cluster using AWS CLI
aws rds create-db-cluster \
    --db-cluster-identifier myapp-aurora-cluster \
    --engine aurora-mysql \
    --engine-version 8.0.mysql_aurora.3.02.0 \
    --master-username admin \
    --master-user-password MySecurePassword123 \
    --vpc-security-group-ids sg-12345678 \
    --db-subnet-group-name myapp-subnet-group \
    --backup-retention-period 7 \
    --preferred-backup-window 03:00-04:00 \
    --preferred-maintenance-window sun:04:00-sun:05:00 \
    --enable-cloudwatch-logs-exports error,general,slow-query \
    --deletion-protection

# Create Aurora MySQL instance
aws rds create-db-instance \
    --db-instance-identifier myapp-aurora-instance-1 \
    --db-cluster-identifier myapp-aurora-cluster \
    --db-instance-class db.r6g.xlarge \
    --engine aurora-mysql \
    --auto-minor-version-upgrade \
    --monitoring-interval 60 \
    --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role

# Create Aurora PostgreSQL cluster
aws rds create-db-cluster \
    --db-cluster-identifier myapp-aurora-pg-cluster \
    --engine aurora-postgresql \
    --engine-version 14.9 \
    --master-username postgres \
    --master-user-password MySecurePassword123 \
    --vpc-security-group-ids sg-12345678 \
    --db-subnet-group-name myapp-subnet-group \
    --backup-retention-period 7 \
    --enable-cloudwatch-logs-exports postgresql

# Check cluster information
aws rds describe-db-clusters --db-cluster-identifier myapp-aurora-cluster

# Check endpoints
aws rds describe-db-cluster-endpoints --db-cluster-identifier myapp-aurora-cluster

Aurora MySQL Connection and Database Operations

-- Aurora MySQL connection (mysql commandline client)
mysql -h myapp-aurora-cluster.cluster-123456789012.us-east-1.rds.amazonaws.com \
      -u admin -p myapp_database

-- Create database
CREATE DATABASE myapp_production 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE myapp_production;

-- Create table (InnoDB Engine, Aurora optimized)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_users_email (email),
    INDEX idx_users_created_at (created_at)
) ENGINE=InnoDB;

-- Query utilizing read replicas
-- Writer endpoint (for writes)
INSERT INTO users (username, email, first_name, last_name) VALUES
('john_doe', '[email protected]', 'John', 'Doe'),
('jane_smith', '[email protected]', 'Jane', 'Smith'),
('bob_wilson', '[email protected]', 'Bob', 'Wilson');

-- Reader endpoint (for reads)
SELECT id, username, email, CONCAT(first_name, ' ', last_name) as full_name
FROM users 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY);

-- Aurora MySQL specific feature: Query execution plan check
EXPLAIN FORMAT=JSON 
SELECT u.username, u.email 
FROM users u 
WHERE u.created_at >= '2024-01-01' 
ORDER BY u.created_at DESC 
LIMIT 100;

-- Aurora Parallel Query (for large tables)
-- (Note: Applied automatically, no special syntax required)
SELECT DATE(created_at) as signup_date, COUNT(*) as signups
FROM users 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY DATE(created_at)
ORDER BY signup_date;

Aurora PostgreSQL Advanced Features

-- Aurora PostgreSQL connection
psql -h myapp-aurora-pg-cluster.cluster-123456789012.us-east-1.rds.amazonaws.com \
     -U postgres -d myapp_database

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For similarity search
CREATE EXTENSION IF NOT EXISTS "btree_gist"; -- For advanced indexing

-- Table utilizing JSON/JSONB features
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    specifications JSONB,
    tags TEXT[],
    price DECIMAL(10, 2),
    inventory_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- JSONB indexes
    GIN INDEX ON specifications,
    GIN INDEX ON tags
);

-- JSONB data insertion
INSERT INTO products (name, description, specifications, tags, price) VALUES
(
    'Gaming Laptop',
    'High-performance gaming laptop',
    '{
        "cpu": "Intel Core i7-12700H",
        "gpu": "NVIDIA RTX 3070",
        "memory": "32GB DDR4",
        "storage": "1TB NVMe SSD",
        "display": {
            "size": 15.6,
            "resolution": "1920x1080",
            "refresh_rate": 144
        }
    }',
    ARRAY['gaming', 'laptop', 'nvidia', 'intel'],
    1299.99
),
(
    'Workstation Desktop',
    'Professional workstation for content creation',
    '{
        "cpu": "AMD Ryzen 9 5950X",
        "gpu": "NVIDIA RTX 4080",
        "memory": "64GB DDR4",
        "storage": "2TB NVMe SSD",
        "cooling": "AIO Liquid Cooling"
    }',
    ARRAY['workstation', 'desktop', 'amd', 'content-creation'],
    2499.99
);

-- JSONB query examples
-- Search products with specific GPU
SELECT name, specifications->>'gpu' as gpu, price
FROM products 
WHERE specifications->>'gpu' LIKE '%RTX%';

-- Array element search
SELECT name, tags, price
FROM products 
WHERE 'gaming' = ANY(tags);

-- Nested JSONB value search
SELECT name, specifications->'display'->>'resolution' as resolution
FROM products 
WHERE (specifications->'display'->>'refresh_rate')::INTEGER >= 120;

-- Full-text search (using pg_trgm)
SELECT name, description, 
       similarity(name, 'laptop') as name_similarity
FROM products 
WHERE name % 'laptop'
ORDER BY name_similarity DESC;

-- Window functions and CTE
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', created_at) as month,
        COUNT(*) as product_count,
        AVG(price) as avg_price
    FROM products 
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT 
    month,
    product_count,
    avg_price,
    LAG(product_count) OVER (ORDER BY month) as prev_month_count,
    product_count - LAG(product_count) OVER (ORDER BY month) as growth
FROM monthly_sales 
ORDER BY month;

Aurora Serverless Configuration and Auto Scaling

# Create Aurora Serverless v2 cluster
aws rds create-db-cluster \
    --db-cluster-identifier myapp-serverless-cluster \
    --engine aurora-mysql \
    --engine-mode provisioned \
    --scaling-configuration \
        MinCapacity=0.5,MaxCapacity=16,AutoPause=true,SecondsUntilAutoPause=300 \
    --master-username admin \
    --master-user-password MySecurePassword123 \
    --vpc-security-group-ids sg-12345678 \
    --db-subnet-group-name myapp-subnet-group

# Create Aurora Serverless v2 instance
aws rds create-db-instance \
    --db-instance-identifier myapp-serverless-instance \
    --db-cluster-identifier myapp-serverless-cluster \
    --db-instance-class db.serverless \
    --engine aurora-mysql

# Create Aurora DSQL (distributed SQL) cluster
aws aurora-dsql create-cluster \
    --identifier myapp-dsql-cluster \
    --engine aurora-dsql \
    --engine-version 1.0 \
    --vpc-configuration \
        VpcId=vpc-12345678,SubnetIds=subnet-12345678,subnet-87654321 \
    --tags Key=Environment,Value=production Key=Application,Value=myapp

# Modify cluster scaling configuration
aws rds modify-current-db-cluster-capacity \
    --db-cluster-identifier myapp-serverless-cluster \
    --capacity 4 \
    --seconds-before-timeout 300

# Auto scaling configuration
aws application-autoscaling register-scalable-target \
    --service-namespace rds \
    --scalable-dimension rds:cluster:ReadReplicaCount \
    --resource-id cluster:myapp-aurora-cluster \
    --min-capacity 1 \
    --max-capacity 15

aws application-autoscaling put-scaling-policy \
    --service-namespace rds \
    --scalable-dimension rds:cluster:ReadReplicaCount \
    --resource-id cluster:myapp-aurora-cluster \
    --policy-name cpu-scaling-policy \
    --policy-type TargetTrackingScaling \
    --target-tracking-scaling-policy-configuration \
    '{
        "TargetValue": 70.0,
        "PredefinedMetricSpecification": {
            "PredefinedMetricType": "RDSReaderAverageCPUUtilization"
        },
        "DisableScaleIn": false
    }'

Backup, Recovery, and Multi-Region Configuration

# Create manual snapshot
aws rds create-db-cluster-snapshot \
    --db-cluster-identifier myapp-aurora-cluster \
    --db-cluster-snapshot-identifier myapp-aurora-snapshot-$(date +%Y%m%d-%H%M%S) \
    --tags Key=CreatedBy,Value=backup-script Key=Retention,Value=30days

# Point-in-time recovery
aws rds restore-db-cluster-to-point-in-time \
    --source-db-cluster-identifier myapp-aurora-cluster \
    --db-cluster-identifier myapp-aurora-recovered \
    --restore-to-time 2024-01-15T10:30:00.000Z \
    --vpc-security-group-ids sg-12345678 \
    --db-subnet-group-name myapp-subnet-group

# Create Global Database (multi-region)
aws rds create-global-cluster \
    --global-cluster-identifier myapp-global-cluster \
    --source-db-cluster-identifier myapp-aurora-cluster \
    --engine aurora-mysql \
    --engine-version 8.0.mysql_aurora.3.02.0

# Add cluster to secondary region
aws rds create-db-cluster \
    --region us-west-2 \
    --db-cluster-identifier myapp-aurora-cluster-west \
    --engine aurora-mysql \
    --global-cluster-identifier myapp-global-cluster \
    --vpc-security-group-ids sg-87654321 \
    --db-subnet-group-name myapp-subnet-group-west

# Encryption configuration
aws rds create-db-cluster \
    --db-cluster-identifier myapp-encrypted-cluster \
    --engine aurora-postgresql \
    --master-username postgres \
    --master-user-password MySecurePassword123 \
    --storage-encrypted \
    --kms-key-id arn:aws:kms:us-east-1:123456789012:key/12345678-1234-1234-1234-123456789012 \
    --vpc-security-group-ids sg-12345678

# Cross-region backup copy
aws rds copy-db-cluster-snapshot \
    --source-db-cluster-snapshot-identifier arn:aws:rds:us-east-1:123456789012:cluster-snapshot:myapp-aurora-snapshot-20240115 \
    --target-db-cluster-snapshot-identifier myapp-aurora-snapshot-west-20240115 \
    --region us-west-2 \
    --kms-key-id arn:aws:kms:us-west-2:123456789012:key/87654321-4321-4321-4321-210987654321

Monitoring and Performance Analysis

# Check CloudWatch metrics
aws cloudwatch get-metric-statistics \
    --namespace AWS/RDS \
    --metric-name DatabaseConnections \
    --dimensions Name=DBClusterIdentifier,Value=myapp-aurora-cluster \
    --start-time 2024-01-15T00:00:00Z \
    --end-time 2024-01-15T23:59:59Z \
    --period 3600 \
    --statistics Average,Maximum

# Enable Performance Insights
aws rds modify-db-instance \
    --db-instance-identifier myapp-aurora-instance-1 \
    --enable-performance-insights \
    --performance-insights-retention-period 7

# Enable enhanced monitoring
aws rds modify-db-instance \
    --db-instance-identifier myapp-aurora-instance-1 \
    --monitoring-interval 60 \
    --monitoring-role-arn arn:aws:iam::123456789012:role/rds-monitoring-role

# Check log files
aws rds describe-db-log-files \
    --db-instance-identifier myapp-aurora-instance-1

aws rds download-db-log-file-portion \
    --db-instance-identifier myapp-aurora-instance-1 \
    --log-file-name error/mysql-error.log \
    --starting-token 0

# Set up CloudWatch alarm
aws cloudwatch put-metric-alarm \
    --alarm-name "Aurora-High-CPU" \
    --alarm-description "Aurora cluster CPU utilization" \
    --metric-name CPUUtilization \
    --namespace AWS/RDS \
    --statistic Average \
    --period 300 \
    --threshold 80 \
    --comparison-operator GreaterThanThreshold \
    --evaluation-periods 2 \
    --alarm-actions arn:aws:sns:us-east-1:123456789012:aurora-alerts \
    --dimensions Name=DBClusterIdentifier,Value=myapp-aurora-cluster

Application Integration Examples

# Python (pymysql + Aurora MySQL)
import pymysql
import boto3
import os
from contextlib import contextmanager

class AuroraConnection:
    def __init__(self):
        self.writer_endpoint = os.environ['AURORA_WRITER_ENDPOINT']
        self.reader_endpoint = os.environ['AURORA_READER_ENDPOINT']
        self.username = os.environ['DB_USERNAME']
        self.password = os.environ['DB_PASSWORD']
        self.database = os.environ['DB_NAME']
    
    @contextmanager
    def get_writer_connection(self):
        """Writer connection (writer endpoint)"""
        connection = None
        try:
            connection = pymysql.connect(
                host=self.writer_endpoint,
                user=self.username,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                autocommit=False,
                connect_timeout=10,
                read_timeout=30,
                write_timeout=30
            )
            yield connection
        finally:
            if connection:
                connection.close()
    
    @contextmanager
    def get_reader_connection(self):
        """Reader connection (reader endpoint)"""
        connection = None
        try:
            connection = pymysql.connect(
                host=self.reader_endpoint,
                user=self.username,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                autocommit=True,  # autocommit for read-only
                connect_timeout=10,
                read_timeout=30
            )
            yield connection
        finally:
            if connection:
                connection.close()

# Usage example
aurora = AuroraConnection()

# Write operations
def create_user(username, email, first_name, last_name):
    with aurora.get_writer_connection() as conn:
        try:
            with conn.cursor() as cursor:
                sql = """
                INSERT INTO users (username, email, first_name, last_name) 
                VALUES (%s, %s, %s, %s)
                """
                cursor.execute(sql, (username, email, first_name, last_name))
                user_id = cursor.lastrowid
            conn.commit()
            return user_id
        except Exception as e:
            conn.rollback()
            raise

# Read operations
def get_users_by_date_range(start_date, end_date):
    with aurora.get_reader_connection() as conn:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = """
            SELECT id, username, email, first_name, last_name, created_at
            FROM users 
            WHERE created_at BETWEEN %s AND %s
            ORDER BY created_at DESC
            LIMIT 1000
            """
            cursor.execute(sql, (start_date, end_date))
            return cursor.fetchall()

# PostgreSQL with psycopg2 example
import psycopg2
from psycopg2.extras import RealDictCursor
import json

class AuroraPostgreSQLConnection:
    def __init__(self):
        self.writer_endpoint = os.environ['AURORA_PG_WRITER_ENDPOINT']
        self.reader_endpoint = os.environ['AURORA_PG_READER_ENDPOINT']
        self.username = os.environ['DB_USERNAME']
        self.password = os.environ['DB_PASSWORD']
        self.database = os.environ['DB_NAME']
    
    @contextmanager
    def get_connection(self, read_only=False):
        endpoint = self.reader_endpoint if read_only else self.writer_endpoint
        connection = None
        try:
            connection = psycopg2.connect(
                host=endpoint,
                user=self.username,
                password=self.password,
                database=self.database,
                connect_timeout=10,
                cursor_factory=RealDictCursor
            )
            if read_only:
                connection.set_session(readonly=True)
            yield connection
        finally:
            if connection:
                connection.close()

# JSONB query example
def search_products_by_specs(gpu_brand=None, min_memory_gb=None):
    aurora_pg = AuroraPostgreSQLConnection()
    
    with aurora_pg.get_connection(read_only=True) as conn:
        with conn.cursor() as cursor:
            conditions = []
            params = []
            
            if gpu_brand:
                conditions.append("specifications->>'gpu' ILIKE %s")
                params.append(f'%{gpu_brand}%')
            
            if min_memory_gb:
                conditions.append("(specifications->>'memory')::INTEGER >= %s")
                params.append(min_memory_gb)
            
            where_clause = " AND ".join(conditions) if conditions else "1=1"
            
            sql = f"""
            SELECT 
                name,
                specifications,
                tags,
                price,
                specifications->>'gpu' as gpu,
                specifications->>'memory' as memory
            FROM products 
            WHERE {where_clause}
            ORDER BY price DESC
            """
            
            cursor.execute(sql, params)
            return cursor.fetchall()

# Usage example
products = search_products_by_specs(gpu_brand='RTX', min_memory_gb=16)
for product in products:
    print(f"Name: {product['name']}")
    print(f"GPU: {product['gpu']}")
    print(f"Memory: {product['memory']}")
    print(f"Price: ${product['price']}")
    print("---")

Aurora Infrastructure with Terraform

# terraform/aurora.tf
resource "aws_rds_cluster" "aurora_mysql" {
  cluster_identifier      = "myapp-aurora-cluster"
  engine                 = "aurora-mysql"
  engine_version         = "8.0.mysql_aurora.3.02.0"
  database_name          = "myapp_production"
  master_username        = "admin"
  master_password        = var.db_password
  
  vpc_security_group_ids = [aws_security_group.aurora.id]
  db_subnet_group_name   = aws_db_subnet_group.aurora.name
  
  backup_retention_period = 7
  preferred_backup_window = "03:00-04:00"
  preferred_maintenance_window = "sun:04:00-sun:05:00"
  
  enabled_cloudwatch_logs_exports = ["error", "general", "slow-query"]
  
  storage_encrypted = true
  kms_key_id       = aws_kms_key.aurora.arn
  
  deletion_protection = true
  skip_final_snapshot = false
  final_snapshot_identifier = "myapp-aurora-final-snapshot"
  
  tags = {
    Environment = "production"
    Application = "myapp"
  }
}

resource "aws_rds_cluster_instance" "aurora_instances" {
  count              = 2
  identifier         = "myapp-aurora-instance-${count.index + 1}"
  cluster_identifier = aws_rds_cluster.aurora_mysql.id
  instance_class     = "db.r6g.xlarge"
  engine             = aws_rds_cluster.aurora_mysql.engine
  engine_version     = aws_rds_cluster.aurora_mysql.engine_version
  
  auto_minor_version_upgrade = true
  monitoring_interval = 60
  monitoring_role_arn = aws_iam_role.rds_monitoring.arn
  
  performance_insights_enabled = true
  performance_insights_retention_period = 7
  
  tags = {
    Environment = "production"
    Application = "myapp"
  }
}

# Global Database
resource "aws_rds_global_cluster" "global" {
  global_cluster_identifier = "myapp-global-cluster"
  source_db_cluster_identifier = aws_rds_cluster.aurora_mysql.arn
  engine                    = "aurora-mysql"
  engine_version           = "8.0.mysql_aurora.3.02.0"
  
  deletion_protection = true
}

# Secondary region cluster
resource "aws_rds_cluster" "aurora_secondary" {
  provider = aws.us_west_2
  
  cluster_identifier     = "myapp-aurora-cluster-west"
  engine                = "aurora-mysql"
  global_cluster_identifier = aws_rds_global_cluster.global.id
  
  vpc_security_group_ids = [aws_security_group.aurora_west.id]
  db_subnet_group_name  = aws_db_subnet_group.aurora_west.name
  
  tags = {
    Environment = "production"
    Application = "myapp"
    Region     = "us-west-2"
  }
  
  depends_on = [aws_rds_global_cluster.global]
}

# Security group
resource "aws_security_group" "aurora" {
  name_prefix = "aurora-sg"
  vpc_id      = var.vpc_id
  
  ingress {
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = [var.vpc_cidr]
  }
  
  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
  
  tags = {
    Name = "aurora-security-group"
  }
}

# Outputs
output "aurora_writer_endpoint" {
  value = aws_rds_cluster.aurora_mysql.endpoint
}

output "aurora_reader_endpoint" {
  value = aws_rds_cluster.aurora_mysql.reader_endpoint
}

output "aurora_cluster_identifier" {
  value = aws_rds_cluster.aurora_mysql.cluster_identifier
}