PostgreSQL Backend Development
Basic Course
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo by: Oleg Bartunov (Phu monastery at Bhrikuti summit, Nepal)
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
In no event shall Postgres Professional company be liable for any damages or
loss, including loss of profits, that arise from direct or indirect, special or
incidental use of course materials. Postgres Professional company specifically
disclaims any warranties on course materials. Course materials are provided
“as is,” and Postgres Professional company has no obligations to provide
maintenance, support, updates, enhancements, or modifications.
2
Developers
DEV1, 4 days
Basic Course
on Backend Development
DEV2, 4 days
Advanced Course
QPT, 3 days
Query Performance Tuning
SQL Basics
Any textbook of your choice
PGPRO, 3 days
Features of
Postgres Pro Enterprise
For backend developers, we offer a special range of courses.
Prior knowledge of the basics of the SQL language is required to complete
these courses. There is no course on the SQL language in our line of
courses, but there are many books and other educational resources with
which you can master SQL.
DEV1 is a basic course for developers.
The DEV2 course examines the features of the server's internal structure
that affect the development of application code, and comprehensively
discusses extensibility: the ability to extend server mechanisms with your
own code, which allows you to use PostgreSQL to solve a wide variety of
tasks.
The QPT course examines in detail the mechanisms for planning and
executing queries, discusses the configuration parameters related to
performance, and examines the options for finding problematic queries and
optimizing them.
The PGPRO course discusses additional features provided by the Postgres
Pro Enterprise DBMS.
3
Administrators
DBA1, 3 days
Basic Course
on PostgreSQL Administration
DBA2, 4 days
Configuration and Monitoring
QPT, 3 days
Query Performance Tuning
DBA3, 2 days
Backup and Replication
SQL Basics
Any textbook of your choice
PGPRO, 3 days
Features of
Postgres Pro Enterprise
For administrators, we offer the following courses.
The DBA1 basic course provides general information about the PostgreSQL
architecture, installation process, basic server configuration and
management. It also considers basic administration tasks and access
control issues. Lastly, it provides an overview of backup and replication
mechanisms.
The DBA2 course focuses on determining appropriate values of
configuration parameters based on an understanding of the internals of the
server. It also talks about monitoring the server and using feedback for
iterative parameter tweaking. Besides, it touches upon localization settings,
extension management, and server upgrades.
The DBA3 covers backups, as well as configuration and usage scenarios of
physical and logical replication. It also provides a general overview of how to
build scalable high-availability clusters and discusses the related challenges.
The QPT and PGPRO courses are intended for both developers and
administrators.
DBA courses can be interesting to developers if they would like to deepen
their knowledge of PostgreSQL internals, or if there is no separate
administrator role in their project.
4
About the DEV1 Course
Duration: 4 days
Background knowledge required
SQL fundamentals
experience with any procedural programming language
basic knowledge of Unix OS
Knowledge and skills gained
general information about the PostgreSQL architecture
working with the main database objects: tables, indexes, views
programming in SQL and PL/pgSQL on the server side
using the main data types, including records and arrays
setting up client-server interface
This basic course introduces backend application developers to PostgreSQL
fundamentals and writing stored procedures and functions in SQL and
PL/pgSQL.
5
Provided Resources
Pre-configured virtual machine
Xubuntu 24.04 OS
PostgreSQL 16 with documentation in English
a sample web application Bookstore
DBeaver
Course handouts
Student’s Guide
presentations, demonstrations, practical assignments and assignment keys
(in html and pdf)
reference materials: PostgreSQL functions and data types,
a chart of the main system catalog tables with psql commands,
some Unix commands
If you are taking the course on your own, start with the Student's Guide.
Among other things, it shows where to download and how to use the virtual
machine and other materials. All course materials are available at:
Completing practical assignments is very important to gain the basic skills in
working with PostgreSQL. Be sure to try to complete the assignments
yourself first, and then review the provided keys, even if the assignment did
not raise questions. Assignment keys may contain additional information that
is not mentioned in presentations and demonstrations.
Course handouts (presentations, demonstrations, practical assignments and
keys) are available in two formats. HTML is good for browsing and copying
code snippets. PDF is divided into pages and is easy to print.
Additional reference materials will help you quickly find the information you
need.
The virtual machine comes with DBeaver pre-installed. Although we use psql
throughout the course, you can use this GUI tool if you like.
6
Outline and Schedule
Each training day is approximately 6 hours + lunch (1 hour)
Each topic usually consists of
a presentation with demos: ~25-60 min
practical assignments: ~20-30 min, including a break
7
Day 1
Basic Tools
01. Installation and Management, psql
Architecture
02. PostgreSQL Fundamentals
03. Isolation and MVCC
04. Buffer Cache and WAL
Data Organization
05. Logical Structure
06. Physical Structure
In the first day, we mainly cover theoretical questions. We present
PostgreSQL architecture fundamentals, which is indispensable for efficient
use of database features. The gained knowledge will be applied to practice
in the upcoming days.
8
Day 2
The Bookstore App
07. Data Schema and API
SQL
08. Functions
09. Procedures
10. Composite Types
PL/pgSQL
11. Overview and Programming Structures
Starting from the Bookstore app module on the second day, each topic
contains two sets of practical assignments: the main ones related to the
application itself (marked with a book icon) as well as supplemental
assignments. It is impossible to complete all the practice within the allotted
time (about 30 minutes). The supplemental assignments should be used for
self-study.
9
Day 3
PL/pgSQL (continued)
12. Query Execution
13. Cursors
14. Dynamic Commands
15. Arrays
16. Error Handling
10
Day 4
PL/pgSQL (continued)
17. Triggers
18. Debugging
Access Control
19. Overview
Backup
20. Logical Backup
