Course 720:
PostgreSQL for DevOps

(2 days)

 

Course Description

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

 

Chapter 1: PostgreSQL Architecture and System Tuning

  • Memory Architecture for In-House Deployment
    • Local Memory Area
    • Shared Memory Area
    • Tuning PostgreSQL Memory Parameters
  • Postmaster Daemon
  • OID in PostgreSQL

Chapter 2: PostgreSQL Database Fundamentals and Tuning

  • 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

Chapter 3: Query Performance and Tuning

  • 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

Chapter 4: Understanding and Tuning DML Operations

  • DML Operations Refresher
  • Performance Impact of Indexes for DML Operations
  • Partitioning
  • Checkpoints
  • Vacuum and Analyze
  • Fillfactor
  • Disk Sorting vs. Indexing
  • Bulk Data Load

Chapter 5: Accessing PostgreSQL Databases with Object-Oriented Languages

  • Python, Java, and .NET Examples
  • Tuning Limits when Using Hibernate for Java

Chapter 6: Migration and Deployment

  • Deploying PostgreSQL Database on AWS or Azure
  • Migration and Optimization Issues
  • Overcoming CI/CD Integration Limitation with Azure DevOps Pipelines

Please Contact Your ROI Representative to Discuss Course Tailoring!