Updated June 2026

PostgreSQL for Python Programmers

Class Duration

14 hours of live training delivered over 2 days

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 beginner-level course equips Python engineers to build fast, reliable, and secure applications on PostgreSQL 18 (with notes on PostgreSQL 17 for teams not yet upgraded). Participants progress from running PostgreSQL in Docker and the psql command line to SQL fundamentals and sound schema design, then explore query optimization with EXPLAIN/ANALYZE, indexing basics, and JSONB with GIN indexes. The course introduces transactions and concurrency through ACID principles and isolation levels, and covers user management, roles, and authentication concepts to secure the database. Students learn to create and call stored procedures and functions in PL/pgSQL. The course finishes by operationalizing PostgreSQL from Python using async SQLAlchemy 2.0 with asyncpg for connections, CRUD, transactions, parameterized queries, and stored procedure calls; using pandas with the ADBC PostgreSQL driver for fast typed data movement; and using pgvector (with halfvec and binary quantization) for similarity search on vector data.

Learning Objectives

Run PostgreSQL 18 with Docker, perform initial configuration via environment variables and volumes, and connect with psql.
Write correct, idiomatic SQL in PostgreSQL: data types, DDL (create/alter/drop), and CRUD operations.
Compose advanced queries using joins, subqueries, CTEs, aggregates, JSONB, JSON_TABLE, and MERGE.
Design schemas with ER modeling, applying normalization and strategic denormalization.
Create and call stored procedures and user-defined functions written in PL/pgSQL, understanding the procedure vs function distinction.
Diagnose and tune performance with EXPLAIN/ANALYZE, B-tree and GIN indexes, and configuration tuning.
Manage transactions and concurrency with ACID principles, isolation levels, locks, and MVCC.
Understand database security concepts: roles, RBAC, authentication methods, and encryption fundamentals.
Build production data workflows with async SQLAlchemy 2.0 and asyncpg: CRUD, transactions, parameterized queries, stored procedure calls, and connection pooling.
Move data efficiently between PostgreSQL and pandas DataFrames using both the ADBC PostgreSQL driver (recommended) and SQLAlchemy fallback.
Manage schema changes safely with Alembic migrations.
Use the pgvector extension (with halfvec, binary quantization, and HNSW/IVFFlat indexes) for similarity search on vector data.

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.

Software Requirements

Students will need a free, personal GitHub account to access the courseware. Students 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

Overview of PostgreSQL
Key Features and Benefits
What's New in PostgreSQL 17 (JSON_TABLE, MERGE RETURNING)
What's New in PostgreSQL 18 (Async I/O, uuidv7, OAuth Authentication, Virtual Generated Columns, Multicolumn B-tree Skip Scans, OLD/NEW in RETURNING)
Use Cases in the Industry

Setting Up PostgreSQL with Docker

Selecting an Official PostgreSQL Docker Image
Running PostgreSQL in a Container
Persisting Data with Docker Volumes
Configuring PostgreSQL via Environment Variables
Using docker-compose for Development
Connecting with psql CLI: Common Commands and Workflow
Connecting from Python Applications

PostgreSQL Architecture

System Architecture and Process Model
Shared Buffers, WAL, and the Async I/O Subsystem (PG 18)
Data Storage and Tablespaces
MVCC, Bloat, and Autovacuum

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
JSONB: Querying and Updating JSON Data
JSON_TABLE for Tabular JSON Output (PG 17+)
MERGE and MERGE RETURNING for Upserts (PG 17+)
OLD and NEW References in RETURNING (PG 18)

Database Design and Modeling

Normalization and Denormalization
Entity-Relationship Modeling and Schema Design
Choosing Between Relational Columns and JSONB

Stored Procedures and Functions

Introduction to PL/pgSQL
Procedures vs Functions: CALL vs SELECT, Transaction Control
Creating User-Defined Functions
Creating Stored Procedures
Parameters, Return Types, and Control Flow Basics
Calling Functions and Procedures from SQL

Performance Tuning and Optimization

Using EXPLAIN and ANALYZE for Query Optimization
Indexing Strategies: B-tree (with Multicolumn Skip Scans in PG 18), GIN (for JSONB), 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 Overview (Including OAuth Authentication in PG 18)
Encryption and SSL/TLS Concepts

Python Programming with PostgreSQL (Async SQLAlchemy)

Setting Up Async SQLAlchemy 2.0 with asyncpg
create_async_engine and AsyncSession
Defining ORM Models with the 2.0 Style
Async CRUD Operations
Lazy-Loading Caveats Under asyncio and Eager Loading Patterns
Handling Transactions in async Code (with expire_on_commit=False)
Connection Pooling: SQLAlchemy Engine Pool Settings and PgBouncer (with the asyncpg statement_cache_size=0 Caveat for Transaction-Pooling Mode)
Parameterized Queries with text() and bindparam (vs Server-Side PREPARE)
Calling Stored Procedures and Functions from SQLAlchemy
Schema Migrations with Alembic
Working with JSONB from SQLAlchemy

Pandas with PostgreSQL

The ADBC PostgreSQL Driver: The Modern Recommended Path
Loading Data with pandas.read_sql() and dtype_backend="pyarrow"
SQLAlchemy as a Fallback Path for Loading
Handling Large Datasets Efficiently
Creating New Tables from DataFrames
Inserting and Updating Data with pandas.to_sql()
Mapping Data Types Between Pandas and PostgreSQL
Ensuring Data Integrity and Consistency

Working with pgvector

Installing and Enabling the pgvector Extension
Vector Column Types: vector, halfvec, sparsevec, and bit
Inserting Vectors with SQLAlchemy
Similarity Search: Cosine, L2, and Inner Product Distance
Binary Quantization with binary_quantize() for Cost and Performance
Creating Vector Indexes (HNSW and IVFFlat) with Iterative Index Scans
Use Cases for Vector Search