PostgreSQL for DevOps
Contact us to book this course
Oracle and SQL Databases
On-Site, Virtual
2 days
DevOps personnel help to shape an organization’s deployment strategies for applications and data repositories. An understanding of what deployment and tuning strategies can be applied for PostgreSQL-related applications is essential for good customer/client experience. This course shows how to tune locally deployed PostgreSQL databases including recommended hardware configurations, tune and maintain databases while in production or development to optimize query performance, and understand and tune DML operations for large datasets.
Learning objectives
- Understand PostgreSQL memory architecture
- Understand challenges and limits when moving in-house PostgreSQL databases to the cloud
- Understand cost and performance implications of Single-AZ/Multi-AZ database deployments on Amazon Web Services (AWS) cloud or Azure equivalent
- Tune PostgreSQL memory parameters
- Utilize OID types (Object Identifier)
- Utilize PostgreSQL stored procedures to expose optimized complex SQL statements to application developers and authorized clients
- Identify proper setup and normalization limits
- Utilize native and third-party tools to analyze SQL statements for optimization purposes
- Perform vacuuming and maintenance in general on your database to preserve performance
- Optimize DML operations through partitioning, checkpoints, indexing, and other factors
Who should attend
Managers, DevOps staff, developers, and analysts having solid understanding of SQL statements and exposure to large datasets stored and managed in PostgreSQL databases. Some exposure to database management and cloud operations would be beneficial.
Prerequisites
ROI will provide cloud-based virtual machines, called RVC, already preinstalled with PostgreSQL and other management and development tools for the course. The client is responsible for possible firewall issues and/or proxy setting requirements.
Course outline
- Memory Architecture for In-House Deployment
- Local Memory Area
- Shared Memory Area
- Tuning PostgreSQL Memory Parameters
- Postmaster Daemon
- OID in PostgreSQL
- Comparison to DB2 and Sybase
- Data Types in PostgreSQL
- Databases, Tables, and Partitions
- Indexing in PostgreSQL (including partial indexing)
- Proper Setup and Normalization Limits
- Configuration Optimizations
- Vacuuming
- SQL Refresher
- Analyzing SQL Statements Using ANALYZE
- Stored Procedure Fundamentals
- Impact of Indexing on SQL Performance, Do’s and Don’ts
- Third-Party Tool to Monitor SQL Statements
- DML Operations Refresher
- Performance Impact of Indexes for DML Operations
- Partitioning
- Checkpoints
- Vacuum and Analyze
- Fillfactor
- Disk Sorting vs. Indexing
- Bulk Data Load
- Python, Java, and .NET Examples
- Tuning Limits when Using Hibernate for Java
- Deploying PostgreSQL Database on AWS or Azure
- Migration and Optimization Issues
- Overcoming CI/CD Integration Limitation with Azure DevOps Pipelines
