AWS Aurora PostgreSQL DBA
Course Duration: 30 Hours
Required Prerequisites
- Basic Knowledge of Oracle SQL
- Basic Knowledge of Oracle DBA
Target Audience
- SQL Developers, PL/SQL Developers and Oracle DBAs who want to gain an in-depth understanding of Oracle SQL Tuning.
Students Enquiry Form
Course Highlights
- Introduction to SQL Tuning
- Setting Up Oracle DB 19c For Practice
- Query Optimizer & Execution Plans
- Indexes
- Oracle Hints
- Optimizer Statistics
- Adaptive Query Optimization
- Cursor Sharing
- Monitoring and Tracing SQL
- SQL Tuning Sets
- SQL Tuning Advisor
- SQL Plan Management
- SQL Access Advisor
- Server Result Cache
- More SQL Tuning Tools
- Partitioning in Oracle Database
Course Content
Section 1: Getting Started with Aurora PostgreSQL
- Introduction to Amazon Aurora PostgreSQL
- Course objectives and learning approach
- Understanding Aurora in the AWS database ecosystem
- Where Aurora PostgreSQL fits in modern application architectures
Section 2: PostgreSQL Core Concepts
- PostgreSQL architecture and database internals
- Storage structures and database processes
- Query lifecycle and query execution
- PostgreSQL background processes
- WAL and its role in database durability
- Checkpoints and archiving
- MVCC fundamentals
- PostgreSQL system catalogs and statistics
Section 3: PostgreSQL Availability & Replication Fundamentals
- High Availability concepts
- Primary and standby database architecture
- PostgreSQL replication fundamentals
- Replication lag and its impact
- Read and write workload separation
- Scaling database workloads
- Understanding common PostgreSQL HA patterns
Section 4: AWS PostgreSQL Services – RDS vs Aurora
- PostgreSQL deployment options on AWS
- Introduction to Amazon RDS for PostgreSQL
- Introduction to Amazon Aurora PostgreSQL
- Key architectural differences between RDS and Aurora
- Aurora-specific capabilities
- Aurora PostgreSQL versioning and release lifecycle
- Selecting the right platform based on workload requirements
Section 5: Building an Aurora PostgreSQL Environment
- Preparing the AWS development environment
- Networking requirements for Aurora
- Creating a test VPC
- Deploying an Aurora PostgreSQL cluster
- Configuring Bastion Hosts
- Linux and Windows connectivity options
- AWS CLI basics and utility scripts
- Environment validation and cleanup
Section 6: Understanding Aurora Cluster Architecture
- Aurora distributed storage design
- Separation of compute and storage
- Aurora storage growth and data durability
- Writer and Reader instances
- Aurora endpoints and connection routing
- Instance classes and I/O characteristics
- Database instance sizing
- Aurora Replicas
- Read and write operations
- Multi-AZ architecture and fault tolerance
- Aurora replication behavior
- Measuring and understanding replication lag
Section 7: Monitoring Aurora PostgreSQL
- Establishing performance baselines
- Identifying important database KPIs
- Introduction to workload generation using pgbench
- Amazon CloudWatch fundamentals
- Aurora-specific CloudWatch metrics
- Understanding metric dimensions
- Enhanced Monitoring
- PostgreSQL database logs
- RDS events and event categories
- Sending database events to CloudWatch
- Practical monitoring exercises
Section 8 : Scaling, Availability & Failover
- Aurora storage scalability
- Scaling database compute resources
- Vertical scaling of Aurora instances
- Aurora Auto Scaling
- Read scaling using Aurora Replicas
- Aurora failover architecture
- Performing and validating a failover
- Understanding failover behaviour from the application perspective
- Cluster Cache Management
Section 9 : Aurora Configuration & PostgreSQL Extensions
- Understanding Aurora configuration management
- Cluster Parameter Groups
- DB Parameter Groups
- Changing and validating database parameters
- RDS Option Groups
- PostgreSQL extension architecture
- Aurora-supported extensions
- Working with Address Standardizer
- Using the PostgreSQL S3 extension
Section 10 : PostgreSQL Performance Diagnostics
- PostgreSQL statistics architecture
- Important statistics views and functions
- Identifying database workload
- Understanding database wait events
- Lock monitoring and analysis
- Introduction to pg_stat_statements
- Finding expensive and frequently executed SQL
- Practical performance investigation
Section 11: SQL Execution & Database Maintenance
- How PostgreSQL generates execution plans
- Query optimizer fundamentals
- Understanding paths and query plans
- Using EXPLAIN
- Reading execution plans
- Role of ANALYZE
- Table statistics and optimizer decisions
- MVCC and dead tuples
- Understanding table bloat
- VACUUM fundamentals
- Transaction ID wraparound
- Autovacuum behavior and configuration
- Practical VACUUM and ANALYZE exercise
Section 12: Securing Aurora PostgreSQL
- Aurora security architecture
- Encryption at rest
- Encryption in transit
- TLS/SSL configuration
- Network-level database protection
- Authentication vs authorization
- PostgreSQL password authentication
- IAM database authentication
- AWS IAM integration with Aurora
- Kerberos authentication concepts
- AWS Managed Microsoft AD
- Configuring Aurora for Kerberos authentication
- Database activity auditing with pgAudit
Section 13: Business Continuity & Aurora Global Database
- Global Database architecture
- Cross-region database replication
- Creating an Aurora Global Database
- Managing global clusters
- Monitoring cross-region replication
- Planned regional failover
- Disaster Recovery architecture
- Recovery Point Objective (RPO)
- Managing Aurora endpoints during DR
- Global Database recovery considerations
- Testing and cleanup
Section 14: Aurora Backup, Restore & Recovery
- Aurora security architecture
- PostgreSQL logical backup concepts
- Using pg_dump
- Restoring databases with pg_restore
- Aurora automated backups
- Manual snapshots
- Snapshot restoration
- Point-in-Time Recovery
- Backup retention considerations
- Backup encryption
- Copying and sharing snapshots
- Exporting snapshots to Amazon S3
- Backup cost considerations
- Monitoring backup operations
- Introduction to AWS Backup
Section 15:Aurora Database Cloning
- Why database cloning is useful
- Aurora Copy-on-Write architecture
- Creating development and testing clones
- Working with cloned databases
- Clone storage considerations
- Cross-account cloning
- Practical cloning scenarios
Section 16: Aurora Serverless v2
- Introduction to Aurora Serverless v2
- Serverless database architecture
- Deploying a Serverless v2 cluster
- Aurora Capacity Units (ACUs)
- Automatic capacity adjustment
- Scaling behavior under changing workloads
- High Availability considerations
- Monitoring Serverless databases
- Choosing Serverless vs provisioned Aurora instances
Section 17: More SQL Tuning Tools
- SQL Performance Analyzer
- SQL Tuning Health – Check scripts (SQLHC)
Section 18: Partitioning in Oracle Database
- What is Partitioning and why it improves SQL performance
- Partition Pruning (Static vs Dynamic)
- Local vs Global Indexes
- Impact of partitioning on execution plans
- Types of Partitioning
- Range Partitioning
- List Partitioning
- Hash Partitioning
- Composite Partitioning
- Range-Hash
- Range-List
- List-Hash
- Interval Partitioning
- Partitioning Enhancements in 19c
- Automatic interval partition maintenance
- Improved partition pruning