PostgreSQL for Python Programmers

Class Duration

14 hours of live training delivered over 2-4 days to accommodate your scheduling needs

Student Prerequisites

Experience with Python programming is required
No prior experience with PostgreSQL is necessary
Students should have a basic understanding of SQL and relational databases

Target Audience

Designed for software and data engineers, Python developers, analytics engineers, and DB-adjacent SREs working on production systems. Technical leads and engineering managers evaluating database choices will also benefit. Attendees should be comfortable with programming fundamentals and seek to improve query performance, data modeling, security posture, and Python–PostgreSQL data pipelines to accelerate delivery and impact business KPIs.

Description

This live, hands-on course equips engineers to deliver fast, reliable, and secure applications on PostgreSQL. Participants progress from setup and architecture to SQL mastery and sound schema design, then dive into query optimization with EXPLAIN/ANALYZE and indexing to cut latency and cost. You’ll harden deployments with roles, authentication, and encryption, and master concurrency with ACID, isolation levels, locking, and MVCC. The course finishes by operationalizing PostgreSQL from Python—using psycopg2, SQLAlchemy, and pandas to move data efficiently, manage transactions, work with JSON and vectors, and keep integrity and performance aligned. The result: teams build features faster, ship safer, and turn data into business outcomes.

Learning Objectives

Install PostgreSQL on major operating systems, perform initial configuration, and manage core server settings and tablespaces.
Write correct, idiomatic SQL in PostgreSQL: data types, DDL (create/alter/drop), and CRUD operations.
Compose advanced queries using joins, subqueries, CTEs, aggregates, and both built-in and user-defined functions and operators.
Design robust schemas with ER modeling, applying normalization and strategic denormalization for real-world use cases.
Diagnose and tune performance with EXPLAIN/ANALYZE, select effective indexing strategies, and apply configuration tuning.
Manage transactions and concurrency with ACID principles, isolation levels, locks, and MVCC; troubleshoot contention safely.
Secure databases with roles/RBAC, pg_hba.conf authentication, and SSL/TLS encryption to meet enterprise standards.
Build production data workflows in Python: psycopg2 and SQLAlchemy CRUD and transactions, pandas read_sql/to_sql at scale, handle JSON and vector data, and align data types and indexes for speed and integrity.

Training Materials

All students receive comprehensive courseware covering all topics in the course. Courseware is distributed via GitHub in the form of documentation and extensive code samples. Students practice the topics covered through challenging hands-on lab exercises. Students will need a free, personal GitHub account to access the courseware. Student will need permission to install Docker Desktop, Visual Studio Code, and Visual Studio Code Extensions on their computers. If students are unable to configure a local environment, a cloud-based environment can be provided.

Software Requirements

Students will need a free, personal GitHub account to access the courseware. Student will need permission to install Docker Desktop, Visual Studio Code, and Visual Studio Code Extensions on their computers. If students are unable to configure a local environment, a cloud-based environment can be provided.

Training Topics

PostgreSQL

Overview of PostgreSQL
Key Features and Benefits
Use Cases in the Industry

Setting Up PostgreSQL

Installing PostgreSQL on Different Operating Systems
Basic Configuration and Post-Installation Setup

PostgreSQL Architecture

System Architecture and Process Model
Data Storage and Tablespaces

Basic SQL with PostgreSQL

Basic SQL Syntax and Data Types
Creating, Altering, and Dropping Tables
CRUD Operations (Create, Read, Update, Delete)

Advanced SQL Techniques

Complex Queries: Joins, Subqueries, and Common Table Expressions (CTEs)
Functions and Operators: Built-in and User-Defined
Aggregate Functions and Grouping

Database Design and Modeling

Normalization and Denormalization
Entity-Relationship Modeling and Schema Design

Performance Tuning and Optimization

Using EXPLAIN and ANALYZE for Query Optimization
Indexing Strategies and Index Maintenance
Performance Tips and Configuration Tuning

Concurrency and Transactions

Transactions, ACID Properties, and Isolation Levels
Locking Mechanisms and Concurrency Control
Multi-Version Concurrency Control (MVCC)

Security and Authentication

User Management and Role-Based Access Control
Authentication Methods and Configuring pg_hba.conf
Data Encryption Techniques and SSL/TLS Configuration

Python Programming with PostgreSQL

Setting Up Psycopg2
Connecting to PostgreSQL from Python
Performing CRUD Operations with Python
Using SQLAlchemy with PostgreSQL
Handling Transactions in Python
Working with JSON Data
Working with Vectors

Loading Data from PostgreSQL into Pandas

Writing SQL Queries to Retrieve Data
Using pandas.read_sql() to Load Data
Handling Large Datasets Efficiently

Writing Data from Pandas to PostgreSQL

Creating New Tables from DataFrames
Inserting and Updating Data in Existing Tables
Using pandas.to_sql() for Efficient Data Transfers

Handling Data Types and Indexes

Mapping Data Types Between Pandas and PostgreSQL
Ensuring Data Integrity and Consistency
Managing Indexes for Optimized Performance

Extending PostgreSQL

Using Procedural Languages: PL/pgSQL, PL/Python, PL/Perl
Working with Extensions and Modules
Popular Extensions (PostGIS, hstore, etc.)