Outline updated November 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
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_engineandAsyncSession- Typed ORM models with
Mappedandmapped_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()andbindparam - 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, andbittypes- 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