Outline updated November 2026

Foundation14 hours delivered over 2 daysLive, online or on-site

Request this course

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

Python developers, data engineers, and analytics engineers who build applications and pipelines on PostgreSQL. Those new to SQL can start with SQL Fundamentals. Teams that want more server-side logic can continue with PostgreSQL Stored Procedures and Functions.

Description

This beginner-level course teaches Python developers to build applications on PostgreSQL. Students run PostgreSQL in Docker, use psql, and learn SQL and schema design. They tune queries with EXPLAIN and indexes, work with JSONB, and write PL/pgSQL functions and procedures. The course also covers transactions, isolation levels, and database security. The Python modules use async SQLAlchemy with asyncpg, Alembic migrations, pandas with the ADBC driver, and pgvector for similarity search.

Learning Outcomes

  • Run PostgreSQL in Docker and connect with psql.
  • Write PostgreSQL SQL with joins, CTEs, JSONB, JSON_TABLE, and MERGE.
  • Design normalized schemas with entity-relationship modeling.
  • Write and call PL/pgSQL functions and stored procedures.
  • Tune queries with EXPLAIN ANALYZE and B-tree and GIN indexes.
  • Explain transactions, isolation levels, locks, and MVCC.
  • Secure a database with roles and modern authentication methods.
  • Build async data access with SQLAlchemy, asyncpg, and Alembic.
  • Move data between PostgreSQL and pandas DataFrames.
  • Run vector similarity searches with pgvector.

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. The course is taught on PostgreSQL 18 with SQLAlchemy 2.1. If students are unable to configure a local environment, a cloud-based environment can be provided.

Training Topics

PostgreSQL Overview

  • PostgreSQL overview
  • Key features and benefits
  • Recent features for developers
  • Virtual generated columns
  • Common use cases

Setting Up PostgreSQL with Docker

  • Choosing an official PostgreSQL image
  • Running PostgreSQL in a container
  • Persisting data with Docker volumes
  • Configuring with environment variables
  • Docker Compose for development
  • Common psql commands
  • Connecting from Python applications

PostgreSQL Architecture

  • System architecture and process model
  • Shared buffers and WAL
  • The asynchronous I/O subsystem
  • Data storage and tablespaces
  • MVCC, bloat, and autovacuum

Basic SQL with PostgreSQL

  • SQL syntax and data types
  • Creating, altering, and dropping tables
  • CRUD operations

Advanced SQL Techniques

  • Joins and subqueries
  • Common table expressions
  • Built-in functions and operators
  • Aggregate functions and grouping
  • Querying and updating JSONB
  • JSON_TABLE for tabular JSON output
  • MERGE and MERGE RETURNING for upserts
  • OLD and NEW references in RETURNING
  • Time-ordered keys with uuidv7()

Database Design and Modeling

  • Normalization and denormalization
  • Entity-relationship modeling and schema design
  • Relational columns vs. JSONB

Stored Procedures and Functions

  • PL/pgSQL basics
  • Procedures vs. functions
  • Transaction control in procedures
  • Creating user-defined functions
  • Creating stored procedures
  • Parameters and return types
  • Control flow basics
  • Calling functions and procedures from SQL

Performance Tuning and Optimization

  • Query optimization with EXPLAIN ANALYZE
  • B-tree indexes and skip scans
  • GIN indexes for JSONB
  • Index maintenance
  • Performance tips and configuration tuning

Concurrency and Transactions

  • Transactions and ACID properties
  • Isolation levels
  • Locking and concurrency control
  • Multi-version concurrency control

Security and Authentication

  • Users and role-based access control
  • SCRAM-SHA-256 and OAuth authentication
  • Deprecation of MD5 passwords
  • Encryption and SSL/TLS concepts

Python Programming with PostgreSQL (Async SQLAlchemy)

  • Setting up async SQLAlchemy with asyncpg
  • create_async_engine and AsyncSession
  • Typed ORM models with Mapped and mapped_column
  • Async CRUD operations
  • Lazy loading under asyncio
  • Eager loading patterns
  • Transactions in async code
  • Using expire_on_commit=False
  • Engine pool settings
  • PgBouncer with asyncpg
  • Prepared statements and transaction pooling
  • Parameterized queries with text() and bindparam
  • Calling procedures and functions from SQLAlchemy
  • Schema migrations with Alembic
  • JSONB columns in SQLAlchemy

Pandas with PostgreSQL

  • The ADBC PostgreSQL driver
  • Loading data with pandas.read_sql()
  • PyArrow-backed DataFrames
  • Loading data through SQLAlchemy
  • Handling large datasets
  • Creating tables from DataFrames
  • Inserting and updating data with to_sql()
  • Mapping types between pandas and PostgreSQL
  • Data integrity and consistency

Working with pgvector

  • Installing and enabling pgvector
  • Vector column types
  • halfvec, sparsevec, and bit types
  • Inserting vectors with SQLAlchemy
  • Cosine, L2, and inner product distance
  • Binary quantization with binary_quantize()
  • HNSW and IVFFlat indexes
  • Iterative index scans
  • Vector search use cases

Private team training

Bring this course to your team

Tell us about your team and we will tailor this outline to your tools, stack, and schedule. Courses can also be combined into a longer program.

  1. Tell us what you needYour tools, stack, team size, and timeline.
  2. We send a proposalA course or program, an outline adjusted to your team, and possible dates.
  3. Your team trains liveOnline or on-site, with courseware for every student.

Prefer to talk? Call +1 434-509-6890 or email help@cloudcontraptions.com.

We reply within 1 business day.

Training partner?