Basic Server-Side Application Development

This course keeps the official topic order and exposes the English handout as readable, native Markdown for review and future editorial work.

Source status: 官方 PDF 已提取为原生 Markdown

Translation baseline: English baseline. The current English archive is the working baseline; the Russian edition is retained for chapter-level cross-checking.

Course scope

SQL and PL/pgSQL functions, procedures, triggers, dynamic commands and server-side logic.

Source status

Contents

Supplementary materials

• catalogs.pdf
• datatypes.pdf
• dev1_new_features.pdf
• dev1_student_guide.pdf
• unix_commands.pdf

Editorial status

The available English handout is extracted from the official PDF. Where no current English archive was found, the route remains ready for a later source edition.

• Version matrix
• Work checklist
• Sources and permissions

00 Lesson 00: PostgreSQL Backend Development / Basic Course

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

• 1. Installation and Management, psql
• Architecture
  • 2. PostgreSQL Fundamentals
  • 3. Isolation and MVCC
  • 4. Buffer Cache and WAL
• Data Organization
  • 5. Logical Structure
  • 6. 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

• 7. Data Schema and API
• SQL
  • 8. Functions
  • 9. Procedures
  • 10. Composite Types
• PL/pgSQL
  • 11. 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)

• 12. Query Execution
• 13. Cursors
• 14. Dynamic Commands
• 15. Arrays
• 16. Error Handling

Page 10: Day 4

PL/pgSQL (continued)

• 17. Triggers
• 18. Debugging
• Access Control
  • 19. Overview
• Backup
  • 20. Logical Backup

01 Lesson 01: Basic Tools / Installation and Management, psql

Page 1: Basic Tools / Installation and Management, psql

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, 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

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental 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: Topics

• PostgreSQL Installation Types
• Server Management
• Server Message Log
• Configuration Parameters
• Using psql

Page 3: Database Cluster

{#source-figure-003 caption=“Source slide page 3: Database Cluster”}

• client

Speaker notes

Let’s start with the general concepts.

PostgreSQL is a program that belongs to the class of database management systems.

When this program is running, we call it a PostgreSQL server or a server instance. So far, the server seems to be a “black box” for us, but gradually we will get acquainted with how it works.

The data managed by PostgreSQL is stored in databases. One instance of PostgreSQL simultaneously manages several databases. This set of databases is called a database cluster. We will talk more about databases in the “Data organization. Logical structure” lesson.

The server interacts with clients. Clients are external applications that can connect to server databases and send queries for execution.

To summarize: a database cluster is the data in files; a server or a server instance is a program that manages the database cluster; a client is an application that “talks” to the server.

Page 4: Installation

Installation types

• pre-build packages (preferred)
• installation from source code
• without installation (cloud services)
• Extensions
  • provide additional features
  • installed separately
  • shipped with the server as modules and programs (~50 extensions)

Speaker notes

The preferred option for installing PostgreSQL is via a package manager (such as apt or rpm) using pre-built packages. This gives you a comprehensive installation that is easy to support and upgrade. There are packages available for most operating systems.

Another option is to build PostgreSQL from source code. This may be necessary if you want to set up a non-standard configuration or deploy on an exotic platform.

Pre-built packages and source codes: http://www.postgresql.org/download/

Besides, you can work with cloud-based managed databases that do not require any installation at all. Such ready-to-use services are provided by all major cloud platforms (Amazon RDS, Google Cloud SQL, Microsoft Azure).

In this course, we are going to use a virtual machine with Xubuntu 24 OS; PostgreSQL 16 is installed from the package for this OS. In addition, the installation is set up to start and stop PostgreSQL when the OS starts and stops.

There are a lot of PostgreSQL extensions that add new database functionality “on the fly”, without modifying the system core. About 50 extensions are included into the PostgreSQL distribution itself.

https://postgrespro.com/docs/postgresql/16/contrib

https://postgrespro.com/docs/postgresql/16/contrib-prog

The list of available extensions and their installation status can be accessed using the pg_available_extensions view.

Page 5: Server Management

Management tools

• pg_ctlcluster
• pg_ctl
• Main operations
  • start the server
  • stop the server
  • reload configuration parameters

Speaker notes

The main server management tasks are: initializing the database cluster, starting and stopping the server, reloading configuration parameters, and a few others. To perform these actions, use the pg_ctl utility, which comes together with PostgreSQL.

In the Ubuntu PostgreSQL package, pg_ctl is not run directly, but through a special wrapper pg_ctlcluster. The help information for pg_ctlcluster can be viewed using man:

$ man pg_ctlcluster

Information about existing clusters and their status can be obtained using the following commands:

$ pg_lsclusters

$ pg_ctlcluster status

For more details about server management for database administrators, see:

https://postgrespro.com/docs/postgresql/16/app-pg-ctl

https://postgrespro.com/docs/postgresql/16/runtime

Page 6: Installation and Management

PostgreSQL in the course VM is installed from packages. PostgreSQL installation directory:

student$ ls -l /usr/lib/postgresql/16

total 8
drwxr-xr-x 2 root root 4096 Nov 27 12:16 bin
drwxr-xr-x 4 root root 4096 Nov 27 13:14 lib

The owner of the server software is the root user. The database cluster is automatically initialized when installed from a package and is located in the /var/lib/postgresql/16/main directory. This directory will be referred to as PGDATA in subsequent topics. The owner of the directory is postgres. Here is what the directory contains:

student$ sudo ls -l /var/lib/postgresql/16/main

total 96
drwx------ 1 postgres postgres 4096 Nov 27 14:17 base
drwx------ 1 postgres postgres 4096 Nov 27 14:17 global
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_commit_ts
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_dynshmem
drwx------ 4 postgres postgres 4096 Nov 27 13:16 pg_logical
drwx------ 4 postgres postgres 4096 Nov 27 13:14 pg_multixact
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_notify
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_replslot
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_serial
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_snapshots
drwx------ 1 postgres postgres 4096 Nov 27 14:17 pg_stat
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_subtrans
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_tblspc
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_twophase
-rw------- 1 postgres postgres    3 Nov 27 13:14 PG_VERSION
drwx------ 1 postgres postgres 4096 Nov 27 13:14 pg_wal
drwx------ 2 postgres postgres 4096 Nov 27 13:14 pg_xact
-rw------- 1 postgres postgres   88 Nov 27 13:14 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Nov 27 14:17 postmaster.opts
-rw------- 1 postgres postgres  108 Nov 27 14:17 postmaster.pid

With the package installation, PostgreSQL is automatically added to the OS startup list, so there is no need to start it manually. Use the following commands to control the server as a privileged OS user with sudo: Stop the server:

student$ sudo pg_ctlcluster 16 main stop

Start the server:

student$ sudo pg_ctlcluster 16 main start

Restart:

student$ sudo pg_ctlcluster 16 main restart

Reload configuration:

student$ sudo pg_ctlcluster 16 main reload

Get server information:

student$ sudo pg_ctlcluster 16 main status

pg_ctl: server is running (PID: 2836)
/usr/lib/postgresql/16/bin/postgres "-D" "/var/lib/postgresql/16/main" "-c"
"config_file=/etc/postgresql/16/main/postgresql.conf"

List of installed instances (works without sudo):

student$ pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main

Page 7: Page 7

/var/log/postgresql/postgresql-16-main.log

Page 8: Server Message Log

Server message log contains

• server messages
• user session messages
• application messages
• Log configuration
  • log file location
  • message format
  • events to log

Speaker notes

Database operations are tracked in the server message log. The log keeps records about starting and stopping the server, as well as various signal messages, including messages about possible issues.

It can also store records of executed commands, their running time, locks that occur, etc. It can be used to trace user sessions.

Application developers can generate and log their own messages.

PostgreSQL allows you to flexibly configure which messages and in which format should be recorded into the log.

For example, the CSV and JSON output formats are convenient for automating log analysis.

https://postgrespro.com/docs/postgresql/16/runtime-config-logging

Page 9: Server Message Log

The server message log is located here:

student$ ls -l /var/log/postgresql/postgresql-16-main.log

-rw-r----- 1 postgres adm 4532 Nov 27 14:17 /var/log/postgresql/postgresql-16-main.log

Let’s look at the last messages:

student$ tail -n 10 /var/log/postgresql/postgresql-16-main.log

2025-11-27 14:17:36.340 MSK [812] LOG:  background worker "logical replication launcher"
(PID 837) exited with exit code 1
2025-11-27 14:17:36.344 MSK [829] LOG:  shutting down
2025-11-27 14:17:36.350 MSK [829] LOG:  checkpoint starting: shutdown immediate
2025-11-27 14:17:36.382 MSK [829] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.009 s, sync=0.005 s, total=0.038 s;
sync files=2, longest=0.003 s, average=0.003 s; distance=0 kB, estimate=0 kB;
lsn=0/1963610, redo lsn=0/1963610
2025-11-27 14:17:36.391 MSK [812] LOG:  database system is shut down
2025-11-27 14:17:37.181 MSK [2836] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-11-27 14:17:37.182 MSK [2836] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-11-27 14:17:37.186 MSK [2836] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2025-11-27 14:17:37.207 MSK [2839] LOG:  database system was shut down at 2025-11-27
13:16:17 MSK
2025-11-27 14:17:37.243 MSK [2836] LOG:  database system is ready to accept connections

Page 10: Configuration Parameters

For the entire instance

• the main configuration file is postgresql.conf

ALTER SYSTEM modifies postgresql.auto.conf

• For the current session

SET/RESET
set_config()

• View the current setting

SHOW
current_setting()
pg_settings
\dconfig

Speaker notes

The PostgreSQL server configuration is governed by a variety of parameters that are used to manage resource consumption, tune system processes and user sessions, manage the server log, and handle many other tasks. It’s important to know how to check and update the parameter values.

Server configuration is usually stored in configuration files. The main configuration file is postgresql.conf, it has to be edited manually. The second configuration file is postgresql.auto.conf. Its contents are set using the ALTER SYSTEM command. The parameters set via ALTER SYSTEM take precedence over the parameters in postgresql.conf.

File and directory inclusion directives include and include_dir allow splitting large postgresql.conf into manageable parts. This may come in handy when managing multiple servers with similar configurations.

Most configuration parameters can be adjusted within a session with no server restart. You can define custom parameters and manage them in the same manner as with the system parameters.

Different ways of setting and updating parameters are described here:

https://postgrespro.com/docs/postgresql/16/config-setting

And the current parameter values are shown in the pg_settings view.

https://postgrespro.com/docs/postgresql/16/view-pg-settings

Page 11: Configuration Parameters

The main postgresql.conf configuration file is located in this directory:

student$ ls -l /etc/postgresql/16/main

total 60
drwxr-xr-x 2 postgres postgres  4096 Nov 27 13:14 conf.d
-rw-r--r-- 1 postgres postgres   315 Nov 27 13:14 environment
-rw-r--r-- 1 postgres postgres   143 Nov 27 13:14 pg_ctl.conf
-rw-r----- 1 postgres postgres  5743 Nov 27 13:14 pg_hba.conf
-rw-r----- 1 postgres postgres  2640 Nov 27 13:14 pg_ident.conf
-rw-r--r-- 1 postgres postgres 29960 Nov 27 13:14 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 Nov 27 13:14 start.conf

Other configuration files are also stored here. Let’s check the value of the work_mem parameter:

=> SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

The work_mem parameter specifies the amount of memory that will be used for internal sorting and hash table allocation operations before using temporary files on disk. 4MB is the default value and it is too small. Let’s say we want to increase it to 16MB for the entire instance. There are various ways to do this. First, you can uncomment and change the line in postgresql.conf where the parameter is defined:

student$ grep '#work_mem' /etc/postgresql/16/main/postgresql.conf

#work_mem = 4MB
# min 64kB

Secondly, you can place the parameter definition in a file with the .conf suffix in the /etc/postgresql/16/main/conf.d directory or in a custom configuration file, the location of which should be set in the include directive of the main configuration file postgresql.conf. Thirdly, you can change the parameter value using an SQL command, and this is what we will do:

=> ALTER SYSTEM SET work_mem TO '16MB';

ALTER SYSTEM

This change does not end up in postgresql.conf, but in postgresql.auto.conf, which is located in the PGDATA directory:

student$ sudo cat /var/lib/postgresql/16/main/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'

Reload the configuration files for the change to take effect. To do this, you can use pg_ctlcluster, or use the SQL function:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Verify that the new value is applied. Here is another way to check it:

=> SELECT current_setting('work_mem');

 current_setting
-----------------
 16MB
(1 row)

To restore the default value of a parameter, you can use the RESET command instead of SET (and then reload the configuration, of course):

=> ALTER SYSTEM RESET work_mem;

Page 12: Page 12

ALTER SYSTEM

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Most parameter values can be set for the current session live. For example, you can increase work_mem just for a single query that you expect to sort a large volume of data:

=> SET work_mem = '64MB';

SET

The new value will remain for the current session or even for the current transaction (with SET LOCAL). Yet another way to check a current value is to query the pg_settings view:

=> SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';

   name   | setting | unit
----------+---------+------
 work_mem | 65536   | kB
(1 row)

Lastly, there is the \dconfig command:

=> \dconfig work_mem

List of configuration parameters
 Parameter | Value
-----------+-------
 work_mem  | 64MB
(1 row)

Page 13: The psql Client

Terminal client for working with PostgreSQL

• Comes with the DBMS
• Used by administrators and developers for interactive work and script execution

Speaker notes

There are other third-party tools available, but they are not considered in the scope of the course.

In the course, we will use the psql terminal client:

1. psql is the only client supplied with the DBMS.

2. The knowledge of psql will be useful to both developers and DB administrators, regardless of which tool they choose to work with at the end of the day.

For interactivity, psql provides built-in support for readline and pager programs (such as less and pspg), as well as the option to use external editing tools. Using psql, you can interact with the operating system, browse through the system catalog, and write scripts to automate routine tasks.

https://postgrespro.com/docs/postgresql/16/app-psql

Page 14: Connection

When starting psql, you need to specify the connection parameters. Required parameters include: database name, same as the user name by default user name (role), same as the OS user name by default host, local connection by default port, usually 5432 by default This is how you put them in:

student$ psql -d database -U user -h host -p port

The VM is set up to connect to PostgreSQL without specifying the parameters:

student$ psql

Check the connection:

=> \conninfo

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

The \connect command creates a new connection without leaving psql. You can use its short form: \c. From here on, the optional parts of commands will be shown in square brackets: \c[onnect].

Help and Documentation

You don’t need to open the documentation every time you need to check something. Instead, you can get the information from within the system directly. The command

student$ psql --help

displays the help information for psql. If PostgreSQL was installed with documentation, then you can get the reference manual with the command

student$ man psql

psql can execute SQL commands and its own commands which start with a backslash, such as \conninfo. psql commands are always single-line, unlike SQL commands. Inside psql, you can get a list and a brief description of all psql commands: \? displays the list of psql commands \h[elp] provides a list of SQL commands that the server supports, as well as the syntax of an SQL command (if specified).

Formatting the Output

The psql client can return output in different formats: aligned format unaligned format extended format The aligned format is the default:

=> SELECT name, setting, unit FROM pg_settings LIMIT 7;

            name            |  setting   | unit
----------------------------+------------+------
 allow_in_place_tablespaces | off        |
 allow_system_table_mods    | off        |
 application_name           | psql       |
 archive_cleanup_command    |            |
 archive_command            | (disabled) |
 archive_library            |            |
 archive_mode               | off        |
(7 rows)

It sets each column’s width based on its contents. There’s also the header and the footer. psql commands to switch display modes: \a — mode switch: aligned/unaligned. \t — toggle the header and footer display. Let’s disable alignment, heading, and summary line:

=> \a \t

Output format is unaligned.
Tuples only is on.

=> SELECT name, setting, unit FROM pg_settings LIMIT 7;

Page 15: Page 15

allow_in_place_tablespaces|off|
allow_system_table_mods|off|
application_name|psql|
archive_cleanup_command||
archive_command|(disabled)|
archive_library||
archive_mode|off|

=> \a \t

Output format is aligned.
Tuples only is off.

This format is not the best for readability, but can be more suitable for automatic processing. The extended format is best for displaying multiple columns for one or several records. It is switched on for the current command with the \gx flag at the end in place of a semicolon:

=> SELECT name, setting, unit, category, context, vartype,
    min_val, max_val, boot_val, reset_val
FROM pg_settings
WHERE name = 'work_mem' \gx

-[ RECORD 1 ]----------------------
name      | work_mem
setting   | 4096
unit      | kB
category  | Resource Usage / Memory
context   | user
vartype   | integer
min_val   | 64
max_val   | 2147483647
boot_val  | 4096
reset_val | 4096

Or you can toggle it on and off with the \x flag. The \pset command displays all formatting options.

Interacting with the OS and Executing Scripts

psql can run shell commands:

=> \! pwd

/home/student

An SQL query can generate several other SQL queries and write them to a file using the \o[ut] command:

=> \a \t \pset fieldsep ''

Output format is unaligned.
Tuples only is on.
Field separator is "".

=> \o dev1_tools.sql

=> SELECT format('SELECT %L AS tbl, count(*) FROM %I;', tablename, tablename)
FROM pg_tables LIMIT 3;

Nothing is printed to the screen (to the standard output). Check the file:

=> \! cat dev1_tools.sql

SELECT 'pg_statistic' AS tbl, count(*) FROM pg_statistic;
SELECT 'pg_type' AS tbl, count(*) FROM pg_type;
SELECT 'pg_foreign_table' AS tbl, count(*) FROM pg_foreign_table;

Revert to default output and formatting.

=> \o \t \a

Tuples only is off.
Output format is aligned.

We can run the commands from the file using \i[nclude]:

=> \i dev1_tools.sql

     tbl      | count
--------------+-------
 pg_statistic |   409
(1 row)

   tbl   | count
---------+-------
 pg_type |   613
(1 row)

       tbl        | count
------------------+-------
 pg_foreign_table |     0
(1 row)

There are other ways to execute commands, including from files. After executing the commands, the psql session will be terminated: psql < filename psql -f < filename psql -c ‘command’ (one command per call)

Page 16: psql Variables

Like shell, psql has its own variables. Set a variable:

=> \set TEST Hi!

To replace a variable with its value, use the colon in front of its name:

=> \echo :TEST

Hi!

Unset a value:

=> \unset TEST

=> \echo :TEST

:TEST

Variables can be used, for example, to store the text of frequently used queries. Here is a query that returns a list of the five largest tables:

=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Now we can execute the query by just typing:

=> :top5

   tablename    |  bytes
----------------+---------
 pg_proc        | 1245184
 pg_rewrite     |  745472
 pg_attribute   |  720896
 pg_description |  630784
 pg_statistic   |  294912
(5 rows)

You can add the command to set the value of top5 to the .psqlrc starter file in the user’s home directory. Commands from .psqlrc are executed automatically every time psql starts. If used with no parameters, \set displays all currently set variables and their values, including the built-in ones. You can check the help for the built-in variables:

\? variables

To exit psql, either use \q[uit], quit, or exit, or press Ctrl+D:

=> \q

Page 17: Takeaways

Installing PostgreSQL from pre-built packages is the preferred installation type

• Pre-packaged distributions bring some OS specifics that you should be aware of:
  • how to start and stop the server
  • location of configuration files
  • location of the server message log
• psql is a client application for working with PostgreSQL

Page 18: Practice

1. Set the parameter work_mem to 8MB for all sessions. Reload the configuration and verify that the changes have taken effect. Restore the default value. 2. Put the command to create a table to the ddl.sql file. Put the commands to insert rows into this table to the populate.sql file. Run both scripts and verify that the table has been created and populated. 3. Find today’s records in the server message log.

Speaker notes

To launch psql in the terminal window, type psql without parameters.

student:~$ psql

It is convenient to have separate databases for tasks that are related to different topics:

student/student=# CREATE DATABASE tools_overview;

CREATE DATABASE

student/student=# \c tools_overview
You are now connected to database "tools_overview" as user
"student".
student/tools_overview=#

1. Use the ALTER SYSTEM command.

2. If the output does not fit into one terminal screen, psql forwards it to the tool called less for page-by-page output. To return to the prompt, press q.

Page 19: 1. Configuration Parameters

Connect to the database:

student$ psql

Set the parameters:

=> ALTER SYSTEM SET work_mem = '8MB';

ALTER SYSTEM

Reload the configuration:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Check the parameter value:

=> \dconfig work_mem

List of configuration parameters
 Parameter | Value
-----------+-------
 work_mem  | 8MB
(1 row)

Reset to the default value:

=> ALTER SYSTEM RESET work_mem;

ALTER SYSTEM

Apply changes:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

2. Executing Scripts in psql

Write a command into ’ddl.sql’ to create a table in PostgreSQL (you can use any text editor):

student$ cat > ~/ddl.sql <<EOF
CREATE TABLE keywords (
    word text,
    category text,
    description text
);
EOF

Write commands into ’populate.sql’ to populate the table:

student$ cat > ~/populate.sql <<EOF
INSERT INTO keywords
  SELECT word, catcode, catdesc FROM pg_get_keywords();
EOF

Create a new database and connect to it:

=> CREATE DATABASE tools_overview;

CREATE DATABASE

=> \c tools_overview

You are now connected to database "tools_overview" as user "student".

Run the scripts and check the records in the table:

Page 20: Page 20

=> \i ddl.sql

CREATE TABLE

=> \i populate.sql

INSERT 0 471

=> SELECT * FROM keywords LIMIT 10;

   word    | category | description
-----------+----------+-------------
 abort     | U        | unreserved
 absent    | U        | unreserved
 absolute  | U        | unreserved
 access    | U        | unreserved
 action    | U        | unreserved
 add       | U        | unreserved
 admin     | U        | unreserved
 after     | U        | unreserved
 aggregate | U        | unreserved
 all       | R        | reserved
(10 rows)

3. Viewing the Log

The log can be viewed in any text editor. Each log record starts with a date, contains the server process ID (with the packaged installation configuration), and may span several lines. The last records will be at the end of the file.

student$ tail /var/log/postgresql/postgresql-16-main.log

2025-11-27 14:26:39.316 MSK [44254] LOG:  database system is shut down
2025-11-27 14:26:40.321 MSK [46618] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-11-27 14:26:40.321 MSK [46618] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-11-27 14:26:40.325 MSK [46618] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2025-11-27 14:26:40.341 MSK [46621] LOG:  database system was shut down at 2025-11-27
13:16:17 MSK
2025-11-27 14:26:40.367 MSK [46618] LOG:  database system is ready to accept connections
2025-11-27 14:26:43.184 MSK [46618] LOG:  received SIGHUP, reloading configuration files
2025-11-27 14:26:43.185 MSK [46618] LOG:  parameter "work_mem" changed to "8MB"
2025-11-27 14:26:43.572 MSK [46618] LOG:  received SIGHUP, reloading configuration files
2025-11-27 14:26:43.572 MSK [46618] LOG:  parameter "work_mem" removed from configuration
file, reset to default

The database can now be disconnected and deleted (see the Data Organization module for more)

=> \c postgres

You are now connected to database "postgres" as user "student".

=> DROP DATABASE tools_overview;

DROP DATABASE

02 Lesson 02: Architecture / PostgreSQL Fundamentals

Page 1: Architecture / PostgreSQL Fundamentals

Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, 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

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental 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: Topics

• Client-Server Protocol
• Transactionality and its Implementation
• Query Processing and Execution
• Processes and Memory Structures
• Storing Data on Disk and Data Processing
• System Extensibility

Page 3: Client and Server

{#source-figure-003 caption=“Source slide page 3: Client and Server”}

• connection
• authentication
• query generation
• query execution
• transaction management
• transactionality support

Speaker notes

A client application, such as psql or any other program written in any programming language, connects to the server and “communicates” with it. In order for the client and the server to understand each other, they must use the same communication protocol. Usually, the client uses a driver that implements the protocol and provides a set of functions to use in the program. Internally, the driver can use the standard protocol implementation (the libpq library), or can implement the protocol itself.

The language the client is written in is unimportant, as the functionality behind the syntax is defined by the protocol. As an example, we will use the SQL language and the psql client. Of course, no one really would program a client in SQL, but we will use it here purely for educational purposes. It should not be that difficult to substitute any of the SQL commands provided below with corresponding statements in your programming language of choice.

Generally speaking, a connection protocol allows the client to connect to a database in a cluster. The server performs authentication: decides if the client should be allowed to connect, i.e. by demanding a password.

Then, the client sends the server queries in the SQL language, the server executes the queries and sends back the results. A powerful and convenient query language is one of the fundamentals of relational databases.

Another one is the ability to maintain transaction support.

https://postgrespro.com/docs/postgresql/16/protocol

Page 4: Transactions

{#source-figure-004 caption=“Source slide page 4: Transactions”}

• atomicity — everything or nothing consistency — integrity constraints and user restrictions isolation — parallel processes impact
• durability — no data loss after a failure

Speaker notes

A transaction is a sequence of operations that preserves the consistency of data, provided that the operations are performed completely and without interference from other transactions.

Transactions must satisfy four properties collectively known as ACID:

● Atomicity. A transaction is either executed in full or not executed at all. To that end, the beginning of a transaction is marked with the BEGIN command, and the end with either COMMIT (commit changes) or ROLLBACK (undo changes).

● Consistency. Transactions move the database from one consistent state to another consistent one (consistency here means that certain restrictions are fulfilled).

● Isolation. Transactions running simultaneously should not affect the specific one.

● Durability. Once data is committed, it should not be lost even after a server failure.

In PostgreSQL, the client application is the side usually responsible for transaction management (that is, for determining what commands make up a transaction, and for committing or canceling the transaction). Transactions can be managed on the server side by stored procedures.

https://postgrespro.com/docs/postgresql/16/sql-begin

https://postgrespro.com/docs/postgresql/16/sql-savepoint

https://postgrespro.com/docs/postgresql/16/transactions

Page 5: Transaction Management

By default, psql runs in autocommit mode:

=> \echo :AUTOCOMMIT

on

This means that any single command sent without explicitly opening the transaction is committed immediately. Check if this mode is enabled by default in the PostgreSQL driver for your favourite programming language. Create a table with one row:

=> CREATE TABLE t(
    id integer,
    s text
);

CREATE TABLE

=> INSERT INTO t(id, s) VALUES (1, 'foo');

INSERT 0 1

Will another transaction see this table and row?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
(1 row)

Yes. Compare the result:

=> BEGIN; -- explicit start of transaction

BEGIN

=> INSERT INTO t(id, s) VALUES (2, 'bar');

INSERT 0 1

What will the other transaction see now?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
(1 row)

The changes are not yet committed so the other transaction does not see them.

=> COMMIT;

COMMIT

What about now?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
(2 rows)

When autocommit is off, a transaction is implicitly opened upon command input. The command then must be committed manually.

=> \set AUTOCOMMIT off

=> INSERT INTO t(id, s) VALUES (3, 'baz');

INSERT 0 1

What do we see now?

Page 6: Page 6

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
(2 rows)

The changes are not there, as the transaction was opened implicitly.

=> COMMIT;

COMMIT

Now, finally:

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

Turn the default autocommit mode back on.

=> \set AUTOCOMMIT on

Individual changes can be rolled back without interrupting the entire transaction (although this is rarely used).

=> BEGIN;

BEGIN

=> SAVEPOINT sp;

SAVEPOINT

=> INSERT INTO t(id, s) VALUES (4, 'qux');

INSERT 0 1

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | qux
(4 rows)

=> ROLLBACK TO sp;

ROLLBACK

Check the table:

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

The changes have been rolled back, but the transaction is still open:

=> INSERT INTO t(id, s) VALUES (4, 'xyz');

INSERT 0 1

Page 7: Page 7

=> COMMIT;

COMMIT

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | xyz
(4 rows)

Page 8: Query Processing

{#source-figure-008 caption=“Source slide page 8: Query Processing”}

parsing ← system catalog rewriting ← rules planning ← statistics execution ← data

Speaker notes

Query execution is complicated. First, a query is sent from a client to the server as text. The server parses the text, analyzing its syntax (whether letters are formed into words, and words into commands) and semantics (whether there are tables and other objects in the database that the query refers to by name). To do that, the server needs data on what is actually stored in the database. This meta-data is called the system catalog and is stored in special tables in the same database.

A query can be rewritten (transformed). For example, a view name can be substituted with the query text. Users can implement their own transformations using the rule system.

SQL is a declarative language: a query defines what data to get, but not how to get it. It is at this point when the query (already parsed and presented in the form of a tree) is passed on to the planner, which develops an execution plan. For example, the planner can decide whether or not to use indexes to find the data for the query. To plan the execution efficiently, the planner needs certain information about the tables it is going to work with, such as the size of the tables and the distribution of data within them. Together, this information is called statistics.

When a plan is selected, the query is executed in accordance with it, and the result is returned to the client in its entirety.

https://postgrespro.com/docs/postgresql/16/query-path

This is convenient and simple when we are talking about just a row or two, provided by the simple mode of the protocol.

Page 9: Prepared Statements

{#source-figure-009 caption=“Source slide page 9: Prepared Statements”}

parsing rewriting

• binding ← parameter values planning execution

Speaker notes

Each query goes through the steps listed above: parsing, rewriting, planning and execution. But if the same query (possibly with different parameters) is executed over and over again, there is no point in parsing it anew every time.

Therefore, in addition to the usual query execution process, the PostgreSQL protocol provides an extended mode that can control statement execution more precisely.

One of its features is the ability to prepare a statement. When a statement is prepared, it is parsed and rewritten as usual and its parse tree is saved.

When the statement is executed, specific parameter values are bound to it. If necessary, planning is redone (in some cases, PostgreSQL remembers the query plan and does not repeat this step). Then, the statement is executed.

Another advantage of prepared statements is that they are protected from possible SQL injections.

https://postgrespro.com/docs/postgresql/16/sql-prepare

https://postgrespro.com/docs/postgresql/16/sql-execute

Page 10: Prepared Statements

In SQL, you can prepare a statement by using the PREPARE command (it is a PostgreSQL extension not present in the SQL standard):

=> PREPARE q(integer) AS
  SELECT * FROM t WHERE id = $1;

PREPARE

The statement is parsed and rewritten, and the parse tree is saved. A prepared statement can be called by its name using arbitrary parameters:

=> EXECUTE q(1);

 id |  s
----+-----
  1 | foo
(1 row)

For non-parametric statements, an execution plan is saved as well. For queries with parameters, as in the example, the parameter values are taken into account at the planning stage. The planner may decide that the plan built without considering the parameters is good enough. In this case, it will not try to plan the query again. How do you prepare and execute a statement using your favourite programming language? Can you execute a statement WITHOUT preparing it? All prepared statements in the current session can be displayed with:

=> SELECT * FROM pg_prepared_statements \gx

-[ RECORD 1 ]---+---------------------------------
name            | q
statement       | PREPARE q(integer) AS           +
                |   SELECT * FROM t WHERE id = $1;
prepare_time    | 2025-11-27 14:18:05.968349+03
parameter_types | {integer}
result_types    | {integer,text}
from_sql        | t
generic_plans   | 0
custom_plans    | 1

Page 11: Cursors

{#source-figure-011 caption=“Source slide page 11: Cursors”}

parsing rewriting

• binding ← parameter values planning execution
• output

Speaker notes

The client may not want to get all the output at once. There can be too much data, and not all of it may be needed.

This issue is solved by cursors, another feature of the extended mode. The protocol can open a cursor for any operator, and then receive the output row by row.

A cursor can be imagined as a sliding window that shows only a part of the output at a time. When an output row is received, the window shifts down. In other words, cursors allow you to work with relational data (that comes in sets) iteratively, row by row.

An open cursor is represented on the server by a so-called portal. This term is mentioned in the documentation, but in general, the words “cursor” and “portal” can be considered synonyms.

A statement used within a cursor is implicitly prepared (that is, its parsing tree and possibly execution plan are saved).

https://postgrespro.com/docs/postgresql/16/sql-declare

https://postgrespro.com/docs/postgresql/16/sql-fetch

Page 12: Cursors

When the SELECT command is executed, the server sends and the client receives all the rows at once:

=> SELECT * FROM t ORDER BY id;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | xyz
(4 rows)

Cursors let the client to retrieve data in batches of rows.

=> BEGIN;

BEGIN

=> DECLARE c CURSOR FOR
  SELECT * FROM t ORDER BY id;

DECLARE CURSOR

=> FETCH c;

 id |  s
----+-----
  1 | foo
(1 row)

You can set the size of the batch:

=> FETCH 2 c;

 id |  s
----+-----
  2 | bar
  3 | baz
(2 rows)

The batch size is paramount for huge outputs, when processing them one row at a time is inefficient. What if we reach the end of the table?

=> FETCH 2 c;

 id |  s
----+-----
  4 | xyz
(1 row)

=> FETCH 2 c;

 id | s
----+---
(0 rows)

FETCH will just stop returning rows. All regular programming languages have a way to check for this condition. How do you get data row by row using the cursor in your favorite programming language? Is it possible NOT to use the cursor and get all the rows at once? How is the cursor batch size set? You can close your cursor when done, freeing up some resources:

=> CLOSE c;

CLOSE CURSOR

However, cursors are automatically closed when the transaction ends, so explicit closing is optional (except WITH HOLD cursors.)

=> COMMIT;

Page 13: Page 13

COMMIT

Page 14: Processes and Memory

{#source-figure-014 caption=“Source slide page 14: Processes and Memory”}

Speaker notes

Between processing queries from clients, the server must store technical information, such as parsed queries and their plans and the status of open cursors (portals). Where is it stored and how?

Under the hood, a PostgreSQL server consists of several interacting processes. First of all, when the server starts, a process traditionally called postmaster is started. It starts all other processes (using the fork system call in Unix). It also “babysits” them: if any of the processes crashes, postmaster will restart it (or restart the entire server if it considers that the failed process could have damaged any of the shared data).

Operations of the server are maintained by a number of background processes. The main ones will be discussed in the following lessons.

In order for the processes to exchange information between them, postmaster allocates shared memory that all the processes can access. In addition to shared memory, each process has its own local memory, accessible only to itself.

Postmaster also listens for incoming connections. For each connecting client, postmaster generates a designated backend process for the client to communicate with on the server side, and each client gets its own process. The backend process performs authentication, among other things.

The space required to execute a client’s query (parsed queries and their plans, cursor states, system catalog cache, a place to sort data, etc.) is allocated in the local memory of the backend process of this client.

Page 15: Multiple Clients

{#source-figure-015 caption=“Source slide page 15: Multiple Clients”}

Speaker notes

When multiple clients connect to a server, each one gets a backend process created for it. As long as there are not too many clients, RAM is sufficient, and connections do not occur too often, sustaining many connections at once is not a problem in itself.

However, when multiple processes try to access the same database object, things must be done to ensure that one process will not change the data while another is in the process of reading it.

For objects in shared memory, this is ensured by short-term locks. PostgreSQL does this carefully enough so that the system scales well with an increase in the number of processors (cores).

Tables are more complicated. Locks will have to be held until the end of transactions (that is, potentially for a long time), so scalability may suffer. To avoid that, PostgreSQL uses a multiversion concurrency control mechanism (MVCC) and snapshot isolation: multiple versions of the same data can exist simultaneously, and each process sees only its own (but always consistent) data snapshot. Now, only those processes that are trying to change data that has already been changed, but not yet committed by other processes, will be locked.

MVCC is the main mechanism that enables the first three properties of transactions (atomicity, consistency, and isolation). We will talk about it more in its dedicated lesson.

Page 16: Connection Pool

{#source-figure-016 caption=“Source slide page 16: Connection Pool”}

Speaker notes

If there are too many clients, or connections are established and broken too often, using a connection pool may help. This functionality is usually provided by the application server or third-party pool managers (the most popular of which is pgBouncer).

With a connection pool in place, clients connect not to the PostgreSQL server directly, but to the pool manager. The manager keeps several connections to the database server open and uses free ones to fulfill client queries. From the server’s point of view, the number of clients remains constant regardless of how many clients access the pool manager.

The drawback is that multiple clients end up sharing the same backend process, which, as we remember, stores client-specific state in its local memory (such as parsed queries for prepared statements). Therefore, care should be taken when developing applications for such deployments.

One of pgBouncer’s features is the ability to temporarily pause client connections without disconnecting them. This pause can be used for server software updates or other operations that require a server restart.

The use of connection pools is discussed in greater detail in the DEV2 course.

Page 17: Data Storage

{#source-figure-017 caption=“Source slide page 17: Data Storage”}

Speaker notes

Data is stored as regular OS files on disks. How exactly the data is distributed among the files is discussed in one of following lessons.

Logically, the files are divided into pages (sometimes the term block is used). A page is usually 8KB in size. It can be changed within some limits (16 KB or 32 KB), but only during server compilation. A cluster that has been compiled and started up this way can work with pages of only one size.

Each page has a certain internal structure. It contains a header and actual data. There may be free space between them if the page is not fully occupied.

Since disks work much slower than RAM (especially HDD, but SSD too), data heading to disk is cached first. A buffer cache, a certain amount of space in RAM, is allocated for recently read pages. The idea is that the system may want to read the same pages multiple times, and keeping them on hand may save time compared to repeated disk scans. Any recently changed data is also cached for some time before being written on disk.

Page 18: Data Storage

{#source-figure-018 caption=“Source slide page 18: Data Storage”}

Speaker notes

The PostgreSQL buffer cache is located in shared memory so that all processes have access to it.

PostgreSQL does not directly access disks storing its data. Instead, it relies on the operating system. The operating system also has its own data cache. Therefore, if a page is not found in the buffer cache, there is a chance that it is in the OS cache and access to the disk will be avoided.

In case of a failure (for example, power supply dies), the contents of the RAM are lost, and the data changed but not yet written to disk will be lost. This is unacceptable and breaks the durability property of transactions. To avoid that, PostgreSQL keeps a log that allows it to redo lost operations and restore data to a consistent state. We will talk about the buffer cache and the write-ahead log in the dedicated lesson later on.

Page 19: Extensibility

{#source-figure-019 caption=“Source slide page 19: Extensibility”}

Speaker notes

PostgreSQL is designed with extensibility in mind. It allows to create new data types based on existing ones, write stored procedures for data processing, and offers a convenient toolkit for administration, monitoring, and performance tuning.

You can always write an extension that adds the necessary functionality that you need. Most extensions can be installed “hot”, without stopping the server. Thanks to this architecture, there are plenty of existing extensions doing things such as:

● adding support for programming languages (in addition to standard SQL, PL/pgSQL, PL/Perl, PL/Python and PL/Tcl);

● introducing new data types and operators to work with them;

● creating new index types that work more efficiently with specific data types (in addition to standard B-trees, hash indexes, GiST, SP-GiST, GIN, BRIN);

● starting background workers to perform additional tasks;

● connecting to external data sources;

● collecting system load information, performing monitoring, and generating reports;

● exploring system data structures.

Extensibility is discussed in more detail in the DBA2 and DEV2 courses.

Page 20: Takeaways

A server manages a database cluster

• The protocol allows clients to connect to the server, transmit queries and manage transactions
• Each client is served by a dedicated backend process
• Data is stored in files and accessed via the operating system
• Data is cached both in local memory (system catalog, parsed queries) and in shared memory (buffer cache)

03 Lesson 03: Architecture / Isolation and MVCC

Page 1: Architecture / Isolation and MVCC

Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, 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

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental 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: Topics

• Multiversion Concurrency Control
• Data Snapshot
• Isolation Levels
• Vacuuming and its Horizon
• Locking
• Transaction Status

Page 3: MVCC

{#source-figure-003 caption=“Source slide page 3: MVCC”}

Storing multiple versions of the same row

• versions have different validity time
• timestamp = transaction ID (IDs are given in ascending order)

Speaker notes

When multiple sessions are running at the same time, two transactions may access the same row at the same time. If both transactions are just reading the row, there is no problem. If both transactions try to write, no problem either (in this case, they line up and make the changes one after the other). The tricky part is when one transaction wants to read a row and another one wants to change it at the same time.

There are two simple ways about it. You can let such transactions block each other, but then performance suffers. Otherwise, you can let the reading transaction immediately see the changes made by the writing transaction, even if they are not committed (this is called a “dirty read”). This is dangerous, because the changes can be rolled back.

PostgreSQL goes the hard way and utilizes what is known as Multiversion concurrency control. In essence, the system stores multiple versions of each row. So, a writing transaction operates on its own version, while a reading transaction sees its own version.

To distinguish between the versions, PostgreSQL marks each one with two timestamps, which together specify a version’s “validity time”.

The timestamps are essentially just transaction IDs, which always come in ascending order. (In reality, the whole thing is a bit more complicated, but not worth getting into right now.) Upon creation, a row version is marked with the ID of the transaction that executed the INSERT command. When deleted, the version is marked with the ID of the transaction that did the DELETE command (but is not physically deleted). An UPDATE command is a DELETE and an INSERT executed back to back.

https://postgrespro.com/docs/postgresql/16/mvcc-intro

Page 4: Data Snapshot

{#source-figure-004 caption=“Source slide page 4: Data Snapshot”}

Representation of database data in a consistent state at a specific point in time

• transaction ID defines the point in time
• list of active transactions helps the system
• to exclude changes that have not been committed

Speaker notes

PostgreSQL uses snapshot-based transaction isolation.

A transaction accessing a table should see only one of the versions of each row (or none at all). To achieve this, PostgreSQL presents the transaction with a data snapshot created at a certain point in time. The snapshot includes the most recent versions of all committed rows but does not include any non-committed changes from active transactions. In other words, the snapshot takes the version of each row that corresponds to the moment when the snapshot was created.

A snapshot is not a physical copy of the data, but just a few numbers:

● the ID of the last committed transaction at the time of snapshot,

● list of active transactions at that point in time.

The list is needed in order to exclude from the snapshot any changes those transactions may have made but not yet committed.

With just these numbers, we can always tell which row version will be visible in the snapshot. Sometimes it will be the current (most recently committed) version, as with row 1 in the diagram. Sometimes not: row 2 has been deleted after the snapshot has been created (and the change has already been committed), but the transaction still continues to see it while working with the snapshot. This is the correct behavior, the snapshot gives a consistent representation of data at the selected point in time.

Some rows will not get into the snapshot at all: row 3 was deleted before the snapshot was made, so it is not included.

Page 5: Isolation Levels

Read Uncommitted

• not supported by PostgreSQL: works as Read Committed
• Read Committed (default)
  • the snapshot is created as of the time a statement starts
  • repeated query may return different data
• Repeatable Read
  • the snapshot is created as of the time the first transaction statement starts
  • a transaction may fail with a serialization error
• Serializable
  • total isolation, but additional overhead
  • a transaction may fail with a serialization error

Speaker notes

The SQL standard defines four isolation levels: the stricter the level, the less concurrent transactions affect each other. At the time when the standard was adopted, it was believed that the stricter the level, the more difficult it is to implement and the stronger its impact on performance (since then, these views have changed somewhat).

The most lax level of Read Uncommitted allows dirty reads. It is not supported by PostgreSQL, because it is of no practical value and does not give a performance gain.

The Read Committed level is the default isolation level in PostgreSQL. At this level, data snapshots are created at the beginning of each SQL statement execution. Thus, the statement works with an unchanged and consistent data snapshot, but two identical queries following one after the other may show different data.

At the Repeatable Read level, the snapshot is built at the beginning of a transaction (when executing the first statement). This makes all queries inside the same transaction see the same data. This level is convenient, for example, for generating reports from several queries.

The Serializable level guarantees total isolation. At this level, you can use any statements as if the transaction is running alone. The drawback is that some transactions will fail, and your application must be able to repeat such transactions.

https://postgrespro.com/docs/postgresql/16/transaction-iso

Page 6: Row Version Visibility

How do you verify that the same row can exist in multiple versions? Create a table:

=> CREATE TABLE t(s text);

CREATE TABLE

And insert one row. Remember that if you do not explicitly start a transaction with BEGIN, psql executes the command and immediately commits the result:

=> INSERT INTO t VALUES ('Version one');

INSERT 0 1

Start the transaction and get its ID:

=> BEGIN;

BEGIN

=> SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                736
(1 row)

The transaction sees the first (and so far the only) version of the row:

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version one |  735 |    0
(1 row)

Here, the hidden columns show the transaction IDs that limit the visibility of the row version: xmin is the ID of the previous transaction which created the version, and xmax=0 means that this version is current. Now start another transaction in another session:

=> BEGIN;

BEGIN

=> SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                737
(1 row)

The transaction sees the same and only version:

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version one |  735 |    0
(1 row)

Now change the row from within the second transaction.

=> UPDATE t SET s = 'Version two';

UPDATE 1

This is what we get:

=> SELECT *, xmin, xmax FROM t;

Page 7: Page 7

      s      | xmin | xmax
-------------+------+------
 Version two |  737 |    0
(1 row)

What will the first transaction see?

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version one |  735 |  737
(1 row)

Since the change is not yet committed, the first transaction continues to see the first version of the row.

=> COMMIT;

COMMIT

What will the first transaction see now?

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version two |  737 |    0
(1 row)

The first transaction also sees the second version of the string. After commit, the first version of the row is no longer visible in any transaction.

=> COMMIT;

COMMIT

Page 8: Vacuuming and its Horizon

{#source-figure-008 caption=“Source slide page 8: Vacuuming and its Horizon”}

There is a single horizon for each database

• Long-running transactions can hold back the horizon
  • thereby blocking the vacuuming of outdated row versions

Speaker notes

MVCC makes it possible to effectively implement snapshot-based isolation, but as a result, old (“dead”) row versions (“dead tuples”) accumulate in table pages.

Historical versions are needed for some time so that transactions can work with their data snapshots and, in case of rollback, get back to old values. For each database, there is a transaction ID (xid), so that all historical versions deleted by transactions with lower xids are no longer visible in any snapshot. This xid is called vacuum horizon.

Vacuuming is performed by special background processes; but can also be executed manually using the VACUUM command.

Long-running transactions and queries can hold back the horizon, which prevents the removal of accumulated row versions. If you do not vacuum historical data in a timely manner, tables and indexes will bloat, consuming excessive disk space, and the search for current row versions will slow down.

https://postgrespro.com/docs/postgresql/16/routine-vacuuming

Page 9: Locking

Row locks

• reading never locks rows
• changing rows locks them for changes, but not for reads
• Table locks
  • prohibit changing or deleting a table while it is being worked on
  • prohibit reading the table when rebuilding or moving
  • etc.
• Lock lifetime
  • set as needed or manually
  • released automatically upon transaction completion

Speaker notes

So what does MVCC provide? It allows the system to have only the most necessary minimum of locks, thereby increasing performance.

The main locks are set at the row level. Reading never blocks either reading or writing transactions. Changing a row does not lock it for reading. The only case when a transaction will wait for a lock to be released is if it tries to change a row that has already been changed by another transaction that has not been committed yet.

Locks can also be set at a higher level, particularly on tables. They are needed so that no one can delete the table while other transactions are reading data from it, or to prohibit access to the table being rebuilt. Such locks generally do not cause problems, since deleting or rebuilding tables is only done once in a while. However, some changes of table structure trigger implicit table rebuilding, completely blocking access to the table and its indexes. Locks are explained in detail in the Locking module of the DBA2 course.

All necessary locks are set automatically and automatically removed at the end of the transaction. However, if a lock was acquired after the savepoint was set, it will be released immediately when rolling back to that savepoint.

You can also set additional custom locks, but this is rarely necessary.

https://postgrespro.com/docs/postgresql/16/explicit-locking

Page 10: Locking

Repeat the experiment, but now let both transactions try to change the same row.

=> BEGIN;

BEGIN

=> UPDATE t SET s = 'Version three' RETURNING *;

       s
---------------
 Version three
(1 row)

UPDATE 1

And in the second transaction:

=> BEGIN;

BEGIN

=> UPDATE t SET s = 'Version four' RETURNING *;

The second transaction “hung up”: it cannot change the row until the first transaction releases the lock.

=> COMMIT;

COMMIT

Now the second transaction can continue:

      s
--------------
 Version four
(1 row)

UPDATE 1

=> COMMIT;

COMMIT

Both transactions have committed their changes. The first session reads the table again and sees the current row: this is the result committed by the second transaction:

=> SELECT * FROM t;

      s
--------------
 Version four
(1 row)

Page 11: Transaction Status

Transaction status (clog)

• service information; two bits per transaction
• stored in files on disk
• cached in shared memory
• Commit
  • the “transaction committed” bit is set
• Termination
  • the “transaction aborted” bit is set
  • performed as fast as commit (no data rollback needed)

Speaker notes

For multiversion concurrency control to work, the server needs to understand the status of transactions. A transaction can be active or finished. A transaction can end either in a commit or in an abort. Therefore, two bits are required to represent the state of each transaction.

Transaction statuses (commit log, clog) are stored in special service files in the PGDATA/pg_xact directory and worked upon in the server’s shared memory, as to avoid constantly accessing the disk.

At any transaction completion (either successful or not), it is enough to set the appropriate status bits. Both transaction commit and abort occur equally quickly.

If an aborted transaction managed to create new row versions, these versions are not destroyed (there is no “physical” rollback of data). Thanks to the status information, other transactions will see that the transaction that created or deleted the row versions was actually aborted, and will not take changes made by it into account.

Page 12: Takeaways

Multiple versions of each row can be stored in data files

• Transactions work with data snapshots, representations of database data in a consistent state at a specific point in time
• Isolation levels have different snapshot creation times
• Dead row versions beyond the vacuum horizon must be periodically vacuumed
• Writers do not block readers, readers do not block anyone

Page 13: Practice

1. Create a table with one row. Begin a transaction at the Read Committed isolation level and query the table. In another session, delete the row and commit the changes. How many rows will the first transaction see after executing the same query again? Try and see. Complete the first transaction. 2. Repeat the same thing, but now let the transaction work at the isolation level Repeatable Read:

• BEGIN ISOLATION LEVEL REPEATABLE READ;
• Explain the differences.

Page 14: 1. Read Committed Isolation Level

Create a table:

=> CREATE TABLE t(n integer);

CREATE TABLE

=> INSERT INTO t VALUES (42);

INSERT 0 1

Query from the first transaction (default isolation level is Read Committed):

=> BEGIN;

BEGIN

=> SELECT * FROM t;

 n
----
 42
(1 row)

Delete the row in the second transaction and commit the changes:

=> DELETE FROM t;

DELETE 1

Repeat the query:

=> SELECT * FROM t;

 n
---
(0 rows)

The first transaction sees the change: the row has been deleted.

=> COMMIT;

COMMIT

2. Repeatable Read Isolation Level

Return the row:

=> INSERT INTO t VALUES (42);

INSERT 0 1

Query from the first transaction:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT * FROM t;

 n
----
 42
(1 row)

Delete the row in the second transaction and commit the changes:

=> DELETE FROM t;

DELETE 1

Repeat the query:

=> SELECT * FROM t;

Page 15: Page 15

 n
----
 42
(1 row)

At this isolation level, the first transaction does not see any changes: the row still exists for it.

=> COMMIT;

COMMIT

Page 16: Practice+

1. Begin a transaction and create a new table with one row. Without completing the transaction, open a second session and query the table in it. Check what the transaction will return in the second session.

• Commit the transaction in the first session and repeat the query
• to the table in the second session.
• 2. Repeat task 1, but roll back rather than commit the transaction in the first session. What has changed?
• 3. In the first session, start a transaction and make a query to the previously created table. Will it be possible to delete this table in the second session before the transaction is completed? Try and see.

Page 17: 1. Transactions and DDL Commands: Commit

Start a transaction and create a new table:

=> BEGIN;

BEGIN

=> CREATE TABLE t1(n integer);

CREATE TABLE

=> INSERT INTO t1 VALUES (42);

INSERT 0 1

In the second session, query the table:

=> SELECT * FROM t1;

ERROR:  relation "t1" does not exist
LINE 1: SELECT * FROM t1;
                      ^

Until the transaction that created the table is completed, all other transactions do not see the table. The table will be visible only after the completion of the transaction that created it:

=> COMMIT;

COMMIT

=> SELECT * FROM t1;

 n
----
 42
(1 row)

2. Transactions and DDL Commands: Rollback

Start a transaction and create a new table:

=> BEGIN;

BEGIN

=> CREATE TABLE t2(n integer);

CREATE TABLE

=> INSERT INTO t2 VALUES (42);

INSERT 0 1

The query in the second session sees no changes, as expected:

=> SELECT * FROM t2;

ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;
                      ^

When the first transaction is rolled back, the table creation command is also rolled back:

=> ROLLBACK;

ROLLBACK

=> SELECT * FROM t2;

ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;
                      ^

In PostgreSQL, DDL commands are transactional.

3. Table Locks

Page 18: Page 18

Start a transaction, query the table:

=> BEGIN;

BEGIN

=> SELECT * FROM t1;

 n
----
 42
(1 row)

The second transaction, which is trying to remove the table is locked: you cannot remove a table that is being used.

=> DROP TABLE t1;

The table will be removed only after the first transaction is completed:

=> COMMIT;

COMMIT

DROP TABLE

04 Lesson 04: Architecture / Buffer Cache and WAL

Page 1: Architecture / Buffer Cache and WAL

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, Bhrikuti Peak, 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

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental 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: Topics

• Buffer Cache
• Replacement Algorithm
• Write-Ahead Log
• Checkpoint
• Processes Related to the Buffer Cache and WAL

Page 3: Buffer Cache

{#source-figure-003 caption=“Source slide page 3: Buffer Cache”}

Buffer array

• data page (8 KB)
• additional information
• “Dirty” buffers
  • asynchronous write
• Locks in memory
  • for shared access

Speaker notes

The buffer cache is used to smooth out the difference between the RAM and disk speed. It consists of an array of buffers which contain data pages and some additional information (for example, the file name and the position of the page inside this file).

The page size is usually 8 KB; the size can only be changed when building PostgreSQL.

Any work with data pages goes through the buffer cache. If any process is going to work with the page, it first tries to find it in the cache. If the page does not exist, the process requests the operating system to read this page and places it in the buffer cache. (Note that the OS can read the page either from disk or from its own cache.)

After the page is written to the buffer cache, it can be accessed repeatedly without the overhead of operating system calls.

If a process has changed the data in the page, the corresponding buffer becomes “dirty”. The modified page must be written on disk, but for performance reasons, the recording occurs asynchronously and may be delayed.

The buffer cache, like other shared memory structures, is protected by locks to control concurrent access. Although locks are implemented effectively, access to the buffer cache is not nearly as fast as simply accessing RAM. Therefore, in general, the less data a query reads and modifies, the faster it will work.

Page 4: Replacement

{#source-figure-004 caption=“Source slide page 4: Replacement”}

Least Recently Used replacement

• dirty buffer is
• written on disk
• another page is read
• into the vacant space

Speaker notes

The buffer cache size is usually not so large as to fit the entire database. It is limited by the available RAM. Also, the larger the buffer cache, the greater the overhead. Therefore, when reading the next page, sooner or later the buffer cache has to run out of space. In this case, page replacement happens.

The replacement algorithm selects a page in the cache that has been used less often than others. If the selected buffer is dirty, the page is written on disk first to store the changes made to it. Then, a new page is written into the buffer.

This replacement is called LRU (Least Recently Used). It keeps the most frequently accessed data in the cache. Such “hot” blocks of data are not very common, and this approach helps to significantly reduce the number of requests to OS (and disk operations), provided enough cache memory.

Page 5: The Impact of Buffer Cache on Query Execution

Creating a new database in the cluster and connecting to it (for more details about databases, see the Data Organization module):

=> CREATE DATABASE arch_wal_overview;

CREATE DATABASE

=> \c arch_wal_overview

You are now connected to database "arch_wal_overview" as user "student".

Create a table:

=> CREATE TABLE t(n integer);

CREATE TABLE

Populate it with rows:

=> INSERT INTO t SELECT id FROM generate_series(1,100_000) AS id;

INSERT 0 100000

The shared_buffers parameter indicates the buffer cache size:

=> SHOW shared_buffers;

 shared_buffers
----------------
 128MB
(1 row)

The default value is too low. In the real world, you should increase it immediately after server installation (it will be applied after restart). Restart the server to wipe the cache clean.

=> \q

student$ sudo pg_ctlcluster 16 main restart

student$ psql arch_wal_overview

Now, let’s compare the behaviour of the system as we run a query once, and then the same query again. Query plans is not the topic of this course, but we will peek into them every now and again. The EXPLAIN ANALYZE command used below will execute the query as well as display the execution plan and some extra details:

=> EXPLAIN (analyze, buffers, costs off, timing off)
SELECT * FROM t;

                 QUERY PLAN
--------------------------------------------
 Seq Scan on t (actual rows=100000 loops=1)
   Buffers: shared read=443
 Planning:
   Buffers: shared hit=12 read=8 dirtied=1
 Planning Time: 0.308 ms
 Execution Time: 23.718 ms
(6 rows)

The “Buffers: shared” line shows the buffer utilization. read — the number of buffers where pages had to be read from disk.

=> EXPLAIN (analyze, buffers, costs off, timing off)
SELECT * FROM t;

                 QUERY PLAN
--------------------------------------------
 Seq Scan on t (actual rows=100000 loops=1)
   Buffers: shared hit=443
 Planning Time: 0.315 ms
 Execution Time: 14.299 ms
(4 rows)

hit — the number of buffers where requested pages were found.

Page 6: Page 6

Page 7: Write-ahead Log (WAL)

Problem: when a crash occurs, data from RAM that is not written on disk is lost

• WAL
  • stream of records of the actions being performed;
  • can be used to redo the steps lost during the crash
  • records are written to disk earlier than the changed data
• WAL tracks changes to
  • pages in tables, indexes and other objects
  • transaction status (clog)
• WAL does not track changes to
  • temporary and unlogged tables

Speaker notes

Having a buffer cache (and other buffers in RAM) increases performance at the cost of reliability. When a crash happens, all buffer cache content is lost. If the crash occurs on the OS or hardware level, the content of OS buffers will also be lost (the OS may have its own failsafes for this).

To increase reliability, PostgreSQL uses the Write-ahead log. When performing any operation, the WAL records minimum necessary information about the operation to be able to perform it again. The record must be written into the disk (or another persistent storage) before the data modified by the operation is (that is why it is called Write-ahead log).

WAL files are located in the PGDATA/pg_wal directory.

All objects that are being worked on in RAM have their operations logged. These include tables, indexes and other objects, as well as transaction statuses. Operations with temporary tables (tables which exist only during the scope of a session or a transaction and are only available to the user who has created them) are not logged. You can also set a regular table to be explicitly unlogged. The table will be quicker to work with, but will be wiped on crash.

https://postgrespro.com/docs/postgresql/16/wal-intro

Page 8: Write-Ahead Log

The WAL can be considered as a continuous stream of records. Each record has a unique ID called an LSN (Log Sequence Number). This 64-bit number represents the record’s byte offset from the start of the WAL. The current WAL position can be seen with pg_current_wal_lsn function:

=> SELECT pg_current_wal_lsn();

 pg_current_wal_lsn
--------------------
 0/2378568
(1 row)

The position is displayed as two 32-bit numbers separated by a slash. Let’s save it for future reference. Now let’s perform some operations and see what’s changed.

=> UPDATE t SET n = 100_001 WHERE n = 1;

UPDATE 1

=> SELECT pg_current_wal_lsn();

 pg_current_wal_lsn
--------------------
 0/237B580
(1 row)

It’s not the absolute values we’re interested in, but the distance between them, as it shows the size of generated WAL records in bytes:

=> SELECT '0/237B580'::pg_lsn - '0/2378568'::pg_lsn AS bytes;

 bytes
-------
 12312
(1 row)

The WAL is stored in files in a separate catalog (PGDATA/pg_wal). By default, the files are 16 MB each, but you can change that during cluster initialization. In addition to browsing the files by means of the OS, you can also display them by the following command:

=> SELECT * FROM pg_ls_waldir() ORDER BY name LIMIT 10;

           name           |   size   |      modification
--------------------------+----------+------------------------
 000000010000000000000002 | 16777216 | 2025-11-27 14:18:39+03
 000000010000000000000003 | 16777216 | 2025-11-27 14:18:33+03
(2 rows)

Page 9: Checkpoint

{#source-figure-009 caption=“Source slide page 9: Checkpoint”}

Regular flushing of all dirty buffers to disk

• ensures that all data changes before the checkpoint get to the disk
• limits the size of the WAL required for recovery
• Crash recovery
  • starts from the last checkpoint
  • WAL records are replayed one-by-one to restore data

Speaker notes

When PostgreSQL crashes, it enters the recovery mode on the next start. The data on disk at this point is inconsistent. Changes to hot pages were in the buffer cache, and are now lost, while some of the later changes have been flushed to disk already.

To restore consistency, PostgreSQL sequentially reads the WAL records, replaying the changes that did not make it to the disk. This way, the state of all transactions at the time of the crash is restored. Then, any transactions that have not been logged as committed are considered aborted.

However, logging all changes throughout a server’s lifetime and replaying everything from day one after each crash is impractical, if not impossible. Instead, PostgreSQL uses checkpoints. Every now and then, it forces all dirty buffers to disk (including clog buffers, which store transaction statuses).

A checkpoint is the moment in time when the flushing of all data to disk is started. However, you only have a valid checkpoint when the flushing of all such buffers is complete. It ensures that all data changes up to this point are safe in persistent memory.

In production environments with a large buffer cache, a checkpoint can flush many dirty buffers, so the server spreads this flushing over time to smooth out the I/O load.

When a crash occurs, recovery is started from the last completed checkpoint. Consequently, it is sufficient to store WAL files only as far back as the last completed checkpoint goes.

Page 10: Crash Recovery Using WAL

Modified table pages exist in the buffer cache but haven’t been written to disk yet. During normal shutdown, the server performs a checkpoint to flush all dirty pages to disk. However, we’ll simulate a system crash by sending a signal to the postmaster process.

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/main/postmaster.pid)

When the server comes back up, it should begin the recovery. Let’s try:

student$ sudo pg_ctlcluster 16 main start

student$ psql arch_wal_overview

=> SELECT min(n), max(n) FROM t;

 min |  max
-----+--------
   2 | 100001
(1 row)

All the changes have been recovered. After performing a checkpoint, PostgreSQL automatically deletes WAL files that are no longer necessary for recovery.

Page 11: Performance

{#source-figure-011 caption=“Source slide page 11: Performance”}

Synchronous mode

• write on commit
• backend
• Asynchronous mode
  • background write
  • walwriter

Speaker notes

The WAL approach is faster than working directly with disk without a buffer cache. Firstly, a WAL record is smaller than an entire page of data. Secondly, the WAL is written sequentially (and usually not read until a crash occurs), which is better for basic hard disk drives.

Performance can also be managed via configuration settings. If the records are stored to disk immediately (synchronous mode), this guarantees that the committed transaction will not be lost. But recording to disk is expensive and forces the committing backend process to wait in line. To prevent WAL records from getting “stuck” in the OS cache, PostgreSQL relies on call of the fsync function, which forces the data into persistent storage.

There is also asynchronous mode, which has a background process walwriter constantly flushing WAL records to disk, with a certain delay. It is more efficient at the cost of some reliability, but still ensures consistency after crash recovery.

In fact, both modes work together. WAL records of a long transaction are written asynchronously (to free up WAL buffers). And if a page is getting flushed to disk and the corresponding WAL record is not there yet, it will immediately be flushed in synchronous mode.

Page 12: Main Processes

{#source-figure-012 caption=“Source slide page 12: Main Processes”}

WAL writer

• Checkpointer
  • flush all
  • dirty buffers
• Background writer
  • flush some
  • dirty buffers
• Backends
  • flush replaced
  • dirty buffer

Speaker notes

Let’s take a step back and look at the processes that maintain the buffer cache and the WAL.

First, there is walwriter. This process writes WAL records to disk in asynchronous mode. In synchronous mode, this job is handled by the backend that commits the transaction.

Second, checkpointer, the checkpoint process. It periodically flushes all dirty buffers to disk.

Third, bgwriter (or background writer). It operates similarly to checkpointer, but it only flushes some of the dirty buffers, specifically, those that are most likely to be replaced soon. It frees up buffer space so that when backend selects a buffer to put a new page in, it does not have to flush the old contents of the buffer to disk itself.

Fourth, there are backends that put data into the buffer cache. Whenever a buffer being replaced is still dirty (despite the efforts of checkpointer and bgwriter), the backend will flush it to disk.

Page 13: WAL Levels

Minimal

• guarantees crash recovery
• Replica (default)
  • backup
  • replication: transmit the WAL on another server and replay it there
• Logical
  • logical replication: information about adding, changing,
  • and deleting table rows

Speaker notes

WAL was developed as a data protection tool to mitigate the risk of data loss due to crashes.

However, the WAL mechanism turned out to have other applications, if it is supplemented with additional information.

The data stored in the WAL is controlled by the wal_level parameter.

● The minimal level is sufficient to recover after a crash, and nothing else.

● The replica level stores additional information that allows WAL to be used for backup and replication. During replication, WAL records are transmitted to another server and applied there, creating an exact copy (replica) of the original server.

● At the logical level, information is added to the WAL that allows decoding “physical” WAL records and forming “logical” records of adding, changing and deleting table rows. This enables logical replication (see corresponding lessons of DEV2 and DBA3 courses for details).

Page 14: Takeaways

Buffer cache increases performance by reducing the number of disk operations

• WAL ensures reliability
• WAL size is kept in check by checkpoints
• WAL has multiple uses:
  • crash recovery
  • backup
  • replication

Page 15: Practice

1. Using the OS tools, find the processes responsible for the buffer cache and the WAL. 2. Stop PostgreSQL in fast mode; start it again. Check the server message log. 3. Now stop PostgreSQL in immediate mode; start it again. Check the server message log and compare with the previous one.

Speaker notes

2. To stop in fast mode, use the command

pg_ctlcluster 16 main stop

This makes the server abort all open connections and perform a checkpoint before shutting down, so that all data is flushed to disk and consistent. In this mode, the shutdown may take some time, but on startup the server will be good to go right away.

3. To stop in immediate mode, use the command

pg_ctlcluster 16 main stop -m immediate --skip-systemctl-redirect

The server will also abort open connections, but will not perform a checkpoint. Data on disk will be inconsistent, like after a crash. In this mode, the server shuts down quickly, but will have to restore data consistency using WAL on startup.

If your PostgreSQL is compiled from source code, the fast stop command will be

pg_ctl stop

and the immediate stop command will be

pg_ctl stop -m immediate

Page 16: 1. Operating System Processes

First, get the postmaster process ID. It is stored in the first line of postmaster.pid. This file is located in the data directory and is created every time the server starts.

student$ sudo cat /var/lib/postgresql/16/main/postmaster.pid

49039
/var/lib/postgresql/16/main
1764242838
5432
/var/run/postgresql
localhost
  1336636        57
ready

Now, find all the processes spawned by postmaster:

student$ sudo ps -o pid,command --ppid 49039

    PID COMMAND
  49040 postgres: 16/main: checkpointer
  49041 postgres: 16/main: background writer
  49043 postgres: 16/main: walwriter
  49044 postgres: 16/main: autovacuum launcher
  49045 postgres: 16/main: logical replication launcher
  49088 postgres: 16/main: student student [local] idle

The processes that serve the buffer cache and the WAL include: checkpointer background writer walwriter

2. Stop in Fast Mode

To easily separate old messages from new ones, clear the message log before restarting the server. Do not do this on a real production server.

student$ sudo -u postgres truncate -cs0 /var/log/postgresql/postgresql-16-main.log

student$ sudo pg_ctlcluster 16 main stop

student$ sudo pg_ctlcluster 16 main start

Server message log:

student$ cat /var/log/postgresql/postgresql-16-main.log

2025-11-27 14:27:22.121 MSK [49039] LOG:  received fast shutdown request
2025-11-27 14:27:22.125 MSK [49039] LOG:  aborting any active transactions
2025-11-27 14:27:22.128 MSK [49088] student@student FATAL:  terminating connection due to
administrator command
2025-11-27 14:27:22.143 MSK [49039] LOG:  background worker "logical replication
launcher" (PID 49045) exited with exit code 1
2025-11-27 14:27:22.143 MSK [49040] LOG:  shutting down
2025-11-27 14:27:22.147 MSK [49040] LOG:  checkpoint starting: shutdown immediate
2025-11-27 14:27:22.485 MSK [49040] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.034 s, sync=0.009 s, total=0.342 s;
sync files=3, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB;
lsn=0/1921AB0, redo lsn=0/1921AB0
2025-11-27 14:27:22.491 MSK [49039] LOG:  database system is shut down
2025-11-27 14:27:22.877 MSK [49341] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-11-27 14:27:22.878 MSK [49341] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-11-27 14:27:22.881 MSK [49341] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2025-11-27 14:27:22.893 MSK [49344] LOG:  database system was shut down at 2025-11-27
14:27:22 MSK
2025-11-27 14:27:22.908 MSK [49341] LOG:  database system is ready to accept connections

3. Stop in Immediate Mode

student$ sudo -u postgres truncate -cs0 /var/log/postgresql/postgresql-16-main.log

Page 17: Page 17

student$ sudo pg_ctlcluster 16 main stop -m immediate --skip-systemctl-redirect

student$ sudo pg_ctlcluster 16 main start

Server message log:

student$ cat /var/log/postgresql/postgresql-16-main.log

2025-11-27 14:27:25.524 MSK [49341] LOG:  received immediate shutdown request
2025-11-27 14:27:25.541 MSK [49341] LOG:  database system is shut down
2025-11-27 14:27:25.975 MSK [49529] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-11-27 14:27:25.976 MSK [49529] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-11-27 14:27:25.979 MSK [49529] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2025-11-27 14:27:25.991 MSK [49532] LOG:  database system was interrupted; last known up
at 2025-11-27 14:27:22 MSK
2025-11-27 14:27:35.796 MSK [49532] LOG:  database system was not properly shut down;
automatic recovery in progress
2025-11-27 14:27:35.805 MSK [49532] LOG:  invalid record length at 0/1921B28: expected at
least 24, got 0
2025-11-27 14:27:35.806 MSK [49532] LOG:  redo is not required
2025-11-27 14:27:35.823 MSK [49530] LOG:  checkpoint starting: end-of-recovery immediate
wait
2025-11-27 14:27:35.852 MSK [49530] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.009 s, sync=0.005 s, total=0.035 s;
sync files=2, longest=0.003 s, average=0.003 s; distance=0 kB, estimate=0 kB;
lsn=0/1921B28, redo lsn=0/1921B28
2025-11-27 14:27:35.862 MSK [49529] LOG:  database system is ready to accept connections

Before starting to accept connections, the DBMS performed an automatic recovery.

05 Lesson 05: Data Organization / Logical Structure

Page 1: Data Organization / Logical Structure

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, 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

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental 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: Topics

• Databases and Templates
• Schemas and Search Path
• Special Schemas
• System Catalog

Page 3: Database Cluster

{#source-figure-003 caption=“Source slide page 3: Database Cluster”}

Cluster initialization creates three databases

• A new database is always cloned from an existing one

CREATE DATABASE

Speaker notes

A PostgreSQL instance manages multiple databases. This is known as a database cluster. During cluster initialization (which is performed either automatically during installation or manually using the initdb command), three identical databases are created. All other databases created by the user are cloned from an existing one.

By default, the template1 DB is used as the source for creating new databases. Any objects and extensions added to the template will be copied into any database created from it.

The template0 database must never be modified. It is required in at least two scenarios. First, to restore the database from a backup made by the pg_dump utility (this is discussed in the Logical Backup lesson). Secondly, it is used when creating a new database with an encoding different from the one specified during cluster initialization (this is discussed in the DBA2 course).

The postgres database is used to connect to by default by the postgres user. It is not a hard requirement to have it, but some utilities expect the postgres database to be there, so removing it is not a good idea, even if you never use the database directly.

https://postgrespro.com/docs/postgresql/16/manage-ag-templatedbs

Page 4: Databases

To obtain the list of databases, open psql and run:

=> \l

                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+----------
--+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |
 student   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres
(4 rows)

The student database is there for the student user to connect to more easily. There are some columns in the output that are of no interest for us for now. When a database is created, the template1 template is used by default.

=> CREATE DATABASE data_logical;

CREATE DATABASE

=> \c data_logical

You are now connected to database "data_logical" as user "student".

=> \l

                                                         List of databases
     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges
--------------+----------+----------+-----------------+-------------+-------------+-------
-----+-----------+-----------------------
 data_logical | student  | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 student      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
(5 rows)

Page 5: Schemas

Namespaces for objects

• divide objects into logical groups
• prevent name conflicts between applications
• Schemas and users are different entities
• Special schemas
  • public — all objects are created here by default
  • pg_catalog — system catalog
  • information_schema — an alternative variant of the system catalog
  • pg_temp — a storage for temporary tables
  • …

Speaker notes

Schemas are namespaces for database objects. They separate objects into groups for easier management and serve to prevent name conflicts when multiple users access the same database, or when multiple applications or extensions are installed.

In PostgreSQL, schema and user are different entities (although the default settings allow users to seamlessly operate schemas of the same name).

There are several special schemas usually present in any database.

The public schema is used by default for storing objects, unless intentionally configured otherwise.

The pg_catalog schema stores system catalog objects. The system catalog is a collection of tables containing metadata about objects belonging to the cluster. It is itself stored in the cluster. The information_schema is another one with an alternative representation of the system catalog (as defined in the SQL standard).

The pg_temp schema stores temporary tables. (In fact, temporary tables are created in schemas called pg_temp_1, pg_temp_2, and so on: each user has its own schema, but they are all referred to as pg_temp.)

There are other special schemas, more technical in nature.

https://postgrespro.com/docs/postgresql/16/ddl-schemas

Page 6: Cluster Databases and Schemas

{#source-figure-006 caption=“Source slide page 6: Cluster Databases and Schemas”}

Speaker notes

Schemas belong to databases, and all database objects are distributed between schemas.

However, several system catalog tables store information that is shared by the entire cluster: the list of databases, the list of users, and some other data. These tables are stored outside of any single database, but at the same time they are accessible from any database within the cluster.

This way, a client connected to a database can see descriptions of not only objects that belong to the database but also of cluster-wide objects. Descriptions of objects in other databases, however, cannot be accessed without connecting to the databases first.

Page 7: Schemas

To display schemas in psql, use \dn command (= describe namespace):

=> \dn

      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

It does not show any system schemas, though. To list them, add the ’S’ flag (it works for many other commands too):

=> \dnS

            List of schemas
        Name        |       Owner
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
(4 rows)

We have already talked about some of these schemas (public, pg_catalog, information_schema); we will discuss the rest in other lectures. Another useful flag is the plus sign, which displays additional information:

=> \dn+

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+--------------------
----
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public
schema
        |                   | =U/pg_database_owner                   |
(1 row)

Create a new schema:

=> CREATE SCHEMA special;

CREATE SCHEMA

=> \dn

       List of schemas
  Name   |       Owner
---------+-------------------
 public  | pg_database_owner
 special | student
(2 rows)

Create a table:

=> CREATE TABLE t(n integer);

CREATE TABLE

By default, the table will be created in the public schema. To list all the tables in this schema, use the \dt command, specifying a template for schema and table names:

=> \dt public.*

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | t    | table | student
(1 row)

Page 8: Page 8

Tables (as well as other objects) can be moved between schemas. Since this is the logical level, the moving takes place only in the system catalog; physically, the data remains in place.

=> ALTER TABLE t SET SCHEMA special;

ALTER TABLE

What will be left in the public schema?

=> \dt public.*

Did not find any relation named "public.*".

Nothing. What about special?

=> \dt special.*

        List of relations
 Schema  | Name | Type  |  Owner
---------+------+-------+---------
 special | t    | table | student
(1 row)

The table has been moved. Now you can access it, as long as you specify the schema.

=> SELECT * FROM special.t;

 n
---
(0 rows)

Without the schema name, no table is found.

=> SELECT * FROM t;

ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^

Page 9: Search Path

Determining the object’s schema

• schema is explicitly defined by a qualified name (schema.name)
• name without a qualifier is looked up in the schemas specified in the
• search path
• Search path
  • defined by the search_path parameter,
  • the real value is shown by the current_schemas function
  • non-existent and inaccessible schemas are excluded
  • objects are created in the first schema explicitly specified in the path
  • pg_temp and pg_catalog schemas are implicitly included first
  • unless they are already specified in the search_path parameter

Speaker notes

When specifying an object, the schema it belongs to must be specified as well, since different schemas may contain objects with the same name.

If the object name is qualified by a schema, the explicitly specified schema is used. Otherwise, the schema is determined by the search_path configuration parameter. A search path is a list of schemas that is scanned sequentially from left to right, while excluding non-existent schemas and any schemas inaccessible to the current user.

When a new object is created with an unqualified name, the first viable schema from the search path is selected as the target schema for the object to be stored in. During object search, two schemas are implicitly added to the front of the search path:

● the pg_catalog schema for access to the system catalog,

● the pg_temp schema if the user has created any temporary objects.

The actual search path, including implicit schemas, can be obtained using the current_schemas(true) function.

The concept of search_path is similar to the PATH variable in operating systems.

https://postgrespro.com/docs/postgresql/16/runtime-config-client#GUC-SEA RCH-PATH

Page 10: Search Path

This is the default search path value:

=> SHOW search_path;

   search_path
-----------------
 "$user", public
(1 row)

“$user” stands for the schema with the same name as the current user (in our case, student). Since there is no student schema, it is ignored. Instead of guessing which schemas actually exist, which do not, and which are currently unavailable, you can use the function:

=> SELECT current_schemas(false);

 current_schemas
-----------------
 {public}
(1 row)

The logical parameter defines if the system schemas implicitly added to the path are displayed or not. Here, in addition to excluding the non-existent schema, PostgreSQL has implicitly included the system catalog schema at the top of the list:

=> SELECT current_schemas(true);

   current_schemas
---------------------
 {pg_catalog,public}
(1 row)

We can define the search path, for example:

=> SET search_path = public, special;

SET

The table will now be found.

=> SELECT * FROM t;

 n
---
(0 rows)

We have just set a configuration parameter at the session level. If we restart the session, it will have reverted to default. Setting the parameter at the cluster level is also ill-advised. Perhaps this path does not always work for everyone, and besides, different databases may have different sets of schemas. Thankfully, you can set the parameter for a specific database:

=> ALTER DATABASE data_logical SET search_path = public, special;

ALTER DATABASE

Now, it should apply for all connections to data_logical. Let’s try:

=> \c data_logical

You are now connected to database "data_logical" as user "student".

=> SHOW search_path;

   search_path
-----------------
 public, special
(1 row)

Page 11: System Catalog

Description of all cluster objects

• the list of tables in each database (the pg_catalog schema) and several
• cluster-wide objects
• several quality-of-life views
• Access
  • SQL queries, special psql commands
• Conventions
  • table names start with pg_
  • column names contain a three-letter prefix
  • the column oid of the oid type is used as the primary key
  • object names are always lowercase

Speaker notes

The system catalog stores metadata about cluster objects. In each database, you can find a separate set of tables that describe the objects of this particular database, as well as several tables common to the whole cluster. There are several convenient views defined for these tables.

https://postgrespro.com/docs/postgresql/16/catalogs

You can access the system catalog using regular SQL queries. DDL commands can be used to modify data in the system catalog. In addition, psql offers a number of commands to browse the system catalog content.

https://postgrespro.com/docs/postgresql/16/app-psql

All system catalog table names start with pg_, for example, pg_database. Table columns start with a prefix, usually corresponding to the table name, for example, datname. Object names are always in lowercase, for example, postgres.

System catalog tables have primary keys. As a rule, these are columns with the name oid and of the oid type (object identifier, a 32-bit integer). These identifiers are also found in other columns as individual values or arrays, linking tables together logically. Foreign keys in the system catalog are not explicitly defined.

https://postgrespro.com/docs/postgresql/16/datatype-oid

Page 12: System Catalog

To display information about an object, psql (like other interactive user tools) accesses system catalog tables. For example, the \l command (list cluster databases) reads from pg_database table:

=> SELECT datname FROM pg_database;

   datname
--------------
 postgres
 student
 template1
 template0
 data_logical
(5 rows)

You can always check what queries a command performs:

=> \set ECHO_HIDDEN on

=> \l

********* QUERY **********
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  d.daticulocale as "ICU Locale",
  d.daticurules as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                                                         List of databases
     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges
--------------+----------+----------+-----------------+-------------+-------------+-------
-----+-----------+-----------------------
 data_logical | student  | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 student      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           |
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
     |           | =c/postgres          +
              |          |          |                 |             |             |
     |           | postgres=CTc/postgres
(5 rows)

This is one way to explore the system catalog. Disable extended command output.

=> \set ECHO_HIDDEN off

The schema list is stored in the table:

=> SELECT nspname FROM pg_namespace;

Page 13: Page 13

      nspname
--------------------
 pg_toast
 pg_catalog
 public
 information_schema
 special
(5 rows)

You can get lists of objects such as tables or indexes like this:

=> SELECT relname, relkind, relnamespace FROM pg_class WHERE relname = 't';

 relname | relkind | relnamespace
---------+---------+--------------
 t       | r       |        16387
(1 row)

All columns here start with rel (relation). relkind — object type (r - table, i - index, etc.) relnamespace — schema The relnamespace field is of the oid type; here is the corresponding row in the pg_namespace table:

=> SELECT oid, nspname FROM pg_namespace WHERE oid = 16387;

  oid  | nspname
-------+---------
 16387 | special
(1 row)

To easily translate between text and oid formats, a special regnamespace type is used:

=> SELECT relname, relkind, relnamespace::regnamespace::text
FROM pg_class WHERE relname = 't';

 relname | relkind | relnamespace
---------+---------+--------------
 t       | r       | special
(1 row)

For example, let’s retrieve the list of objects from pg_catalog:

=> SELECT relname, relkind FROM pg_class
WHERE relnamespace = 'pg_catalog'::regnamespace LIMIT 5;

            relname             | relkind
--------------------------------+---------
 pg_statistic                   | r
 pg_type                        | r
 pg_foreign_table               | r
 pg_proc_oid_index              | i
 pg_proc_proname_args_nsp_index | i
(5 rows)

Similar reg types are defined for some other tables in the system catalog. They simplify queries and avoid unnecessary table joins.

Deleting Objects

Can you delete the special schema?

=> DROP SCHEMA special;

ERROR:  cannot drop schema special because other objects depend on it
DETAIL:  table t depends on schema special
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

A schema cannot be deleted while there are any objects in it. You need to delete or move them elsewhere first. Or you can delete a schema along with all its objects:

=> DROP SCHEMA special CASCADE;

NOTICE:  drop cascades to table t
DROP SCHEMA

Page 14: Page 14

What about deleting the entire database? Firstly, you cannot delete the database that you are currently connected to, so let’s disconnect.

=> \conninfo

You are connected to database "data_logical" as user "student" via socket in
"/var/run/postgresql" at port "5432".

=> \c postgres

You are now connected to database "postgres" as user "student".

Secondly, the database also cannot be deleted while there are any active connections to it. Let’s create a connection in a separate session and try to delete the database:

=> \c data_logical

You are now connected to database "data_logical" as user "student".

=> DROP DATABASE data_logical;