Course Code: psqldevad
Duration: 35 hours
Course Outline:

1 day PostgreSQL Overview 

  • Connecting to the Database
  • Overview of PgAdmin III/IV
  • Configuring the Server
  • Setting up and Managing Users
  • Overview of Database Object Types
  • Setting Permissions & Security
  • Querying the Database
  • Monitoring the Database
  • Ensuring Atomicity and Isolation
  • Migrating Data to Another System
  • Working with PostreSQL Logs

2 days PostgreSQL for Administrators

Performance Optimization

  • Reducing I/O with HOT
  • Locking & Concurrency
  • Evaluating the Design of the Database
  • Optimizing SQL Queries with EXPLAIN
  • Inspecting the Workload
  • Benchmarking and Profiling
  • Optimzing Joins
  • Using Indexes Efficiently
  • PostgreSQL Extensions

PostgreSQL Replication& Backup

  • Overview of PostgreSQL Replication
  • High Availability in PostgreSQL
  • Backing up and Restoring PostreSQL
  • Streaming Replication
  • Managing Clusters Using repmgr
  • Running Queries on Database in Hot Standby
  • Synchronous Replication
  • File based Log Shipping
  • Connection Pooling
  • Upgrading PostgreSQL

2 days PostgreSQL for Developers

The SQL Language

  • SQL Syntax
  • Data Definition
  • Data Manipulation
  • Queries
  • Data Types
  • JSON
  • Functions and Operators
  • Type Conversion
  • Indexes

Transactions and Concurrency

  • Transactions and Isolation
  • Multi-Version Concurrency Control

Client Interfaces

  • Command Line Interface - psql
  • Graphical Interface - pgadmin4

Server Programming

  • Extending SQL
  • Triggers
  • The Rule System
  • Procedural Languages
  • PL/pgSQL - SQL Procedural Language
  • Error Handling
  • Cursors

Foreign Data Wrappers

  • Extension in PostgreSQL
  • Adding FDW in a Database
  • postgres_fdw
  • file_fdw
  • Other FDWs

SQL Tuning

  • Logging in PostgreSQL
  • Query Plans
  • Optimizing Queries
  • Statistics
  • Planner Parameters
  • Parallel Query Scans
  • SQL Best Practices
  • Indexes
  • Table Partitioning