00 Lesson 00: PostgreSQL Backend Development / Basic Course
This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. HTML source: dev1_00_introduction.html Archive: DEV1-handouts-16-en.zip PDF pages: 10
Page 1: PostgreSQL Backend Development / Basic Course
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.
Page 2: Developers
{#source-figure-002 caption=“Source slide page 2: Developers”}
Speaker notes
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. https://postgrespro.com/community/courses
Page 3: Administrators
{#source-figure-003 caption=“Source slide page 3: Administrators”}
- DBA2, 4 days
Speaker notes
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.
Page 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
Speaker notes
This basic course introduces backend application developers to PostgreSQL fundamentals and writing stored procedures and functions in SQL and PL/pgSQL.
Page 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
Speaker notes
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: https://postgrespro.com/community/courses/DEV1 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.
Page 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
Page 7: Day 1
Basic Tools
- Installation and Management, psql
- Architecture
- PostgreSQL Fundamentals
- Isolation and MVCC
- Buffer Cache and WAL
- Data Organization
- Logical Structure
- Physical Structure
Speaker notes
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.
Page 8: Day 2
{#source-figure-008 caption=“Source slide page 8: Day 2”}
The Bookstore App
- Data Schema and API
- SQL
- Functions
- Procedures
- Composite Types
- PL/pgSQL
- Overview and Programming Structures
Speaker notes
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.
Page 9: Day 3
PL/pgSQL (continued)
- Query Execution
- Cursors
- Dynamic Commands
- Arrays
- Error Handling
Page 10: Day 4
PL/pgSQL (continued)
- Triggers
- Debugging
- Access Control
- Overview
- Backup
- Logical Backup