Pingu
Computer MySQL PostgreSQL Books Publications
Spielereien Kanu Geopolitik Business TopoDB POI Klettersteigen History TransPool Thermal Baden Brokenstuben Goldwaschen
Blog Contact
Shinguz
Google
/ch/open

PostgreSQL Architecture

/ home / computer / postgresql / postgresql-for-mysql-admins / .

PostgreSQL process architecture

PostgreSQL is a multi-process architecture:

$ ps aux -L | grep -e postgres: -e VSZ -e main | grep -v grep
USER         PID     LWP %CPU NLWP %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres    1976    1976  0.0    1  0.1 225816 28744 ?        Ss   Jun20   0:08 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
postgres    2133    2133  0.0    1  0.0 225952  8924 ?        Ss   Jun20   0:00 postgres: 16/main: checkpointer
postgres    2135    2135  0.0    1  0.0 225968  7144 ?        Ss   Jun20   0:00 postgres: 16/main: background writer
postgres    2173    2173  0.0    1  0.0 225816 10132 ?        Ss   Jun20   0:00 postgres: 16/main: walwriter
postgres    2174    2174  0.0    1  0.0 227420  8436 ?        Ss   Jun20   0:01 postgres: 16/main: autovacuum launcher
postgres    2175    2175  0.0    1  0.0 227396  8028 ?        Ss   Jun20   0:00 postgres: 16/main: logical replication launcher
postgres  369240  369240  0.0    1  0.0 228200 14888 ?        Ss   15:10   0:00 postgres: 16/main: postgres postgres [local] idle
postgres  369250  369250  0.0    1  0.0 228200 14888 ?        Ss   15:10   0:00 postgres: 16/main: postgres postgres [local] idle
postgres  369252  369252  0.0    1  0.0 228200 14888 ?        Ss   15:10   0:00 postgres: 16/main: postgres postgres [local] idle

vs. MySQL multi-thread architecture:

$ ps aux -L | grep -e mysql-57 -e VSZ | grep -v grep
USER         PID     LWP %CPU NLWP %MEM     VSZ    RSS TTY    STAT START   TIME COMMAND
mysql       2573    2573  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2574  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2575  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2576  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2577  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2578  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2579  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2580  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2581  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2582  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2583  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2584  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2585  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:05 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2588  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:04 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2589  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:08 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2590  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2591  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:06 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2592  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2593  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2594  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2595  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2596  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2597  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2598  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2599  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2623  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2624  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql
mysql       2573    2665  0.0   28  1.0 1364832 172468 ?      Sl   Jun20   0:00 bin/mysqld --defaults-file=/home/mysql/database/mysql-57/etc/my.cnf --basedir=/home/mysql/product/mysql-5.7 --datadir=/home/mysql/database/mysql-57/data --user=mysql

Every connection to PostgreSQL spawns its own process:

  • Which is slow(er) than spawning a thread.
  • Which takes more memory than a thread.

Thus a connection pooler like PgBouncer makes sense or is even needed when having many connections.

Terminology: Cluster - Database - Schema - Relation - Tuple

  • “A database cluster is a collection of databases that is managed by a single instance of a running database server."
    1 Cluster (/var/lib/postgresql/16 vs. /var/lib/mysql) : 1 Instance (postgres vs. mysqld) : n Databases (similar to MariaDB Catalog).
    Has NOTHING to do with a high-availability (HA) Cluster (M/S, failover Cluster, Galera, InnoDB Cluster, etc.)
  • “A schema is essentially a namespace” (similar to MySQL databases/schemas (which are synonyms)).
    1 Database : n Schemas
  • As a result we get:
    PostgreSQL: 1 Cluster : 1 Instance : n Databases : m Schemas
    MySQL: 1 Instance : n Databases/Schemas
    MariaDB: 1 Instance : n Catalogs : m Databases/Schemas
  • Relation - Table, Sequence?
  • Tuple - Row
  • Tablespace
Cluster/Instance

Databases:  postgres   template0   template1   customer1   customer2   customer3
Schemas:                                       foodmart    world       crm
                                               erp         foodmart    dwh
                                                           admin

Cluster

Sources: Cluster, https://stackoverflow.com/questions/12908205/whats-a-postgresql-cluster-and-how-do-i-create-one

Database

Connecting to a database

Note: While the MySQL CLI allows you to connect without specifying a database, PostgreSQL does not. You will not be able to connect to a database that does not exist. If you do not specify a database name (ie. just use the psql command), you will be connected to the database that has the same name as the user you are connecting to. If you do not specify a user, it will be assumed that your PostgreSQL user has the same name as the user you are logged into the server with.

$ psql -U username --> database username
$ psql --user=username --> database username
$ psql database_name --> database database_name

List databases

postgres=# \l
postgres=# \list
postgres=# \l+
postgres=# \list+
                                                                                         List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------+---------+------------+--------------------------------------------
 world     | postgres | UTF8     | libc            | en_GB.UTF-8 | en_GB.UTF-8 |            |           |                       | 7425 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_GB.UTF-8 | en_GB.UTF-8 |            |           |                       | 7095 MB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_GB.UTF-8 | en_GB.UTF-8 |            |           | =c/postgres          +| 7361 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |            |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_GB.UTF-8 | en_GB.UTF-8 |            |           | =c/postgres          +| 7580 kB | pg_default | default template for new databases
           |          |          |                 |             |             |            |           | postgres=CTc/postgres |         |            |
(4 rows)

postgres=# SELECT datname FROM pg_database;
  datname
-----------
 postgres
 world
 template1
 template0
  • postgres is …???
  • template1 is template for cloning a new database.
  • template0 pristine database (one where no user-defined objects exist and where the system objects have not been altered) containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.

List current database

postgres=# SELECT current_database();
 current_database
------------------
 postgres

Switch database connection

See also prompt:

oli=# \connect postgres
oli=# \c postgres
You are now connected to database "postgres" as user "oli".
postgres=#

Create and drop a database

By default, the new database will be created by cloning the standard system database template1.

To create a database you must be a superuser or have the special CREATEDB privilege.

postgres=# CREATE DATABASE world;
postgres=# CREATE DATABASE dwh OWNER marketing TEMPLATE template0;
postgres=# DROP DATABASE world;

Source: CREATE DATABASE, DROP DATABASE

Schema

PostgreSQL offers schemas as a way to separate database objects. Every database gets a public schema by default that is owned by the superuser that created the database. This makes it difficult for regular users to drop, so avoid testing in the public schema unless you are planning to drop and recreate the database.

The maximum portability with other databases would be achieved by not using schemas at all.

List schemas

oli=# -- display namespaces?
oli=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

oli=# \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)

oli=# \dnS+
                                                  List of schemas
        Name        |       Owner       |           Access privileges            |           Description
--------------------+-------------------+----------------------------------------+----------------------------------
 information_schema | postgres          | postgres=UC/postgres                  +|
                    |                   | =U/postgres                            |
 pg_catalog         | postgres          | postgres=UC/postgres                  +| system catalog schema
                    |                   | =U/postgres                            |
 pg_toast           | postgres          |                                        | reserved schema for TOAST tables
 public             | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
                    |                   | =U/pg_database_owner                   |
(4 rows)

oli=# \dnS
            List of schemas
        Name        |       Owner
--------------------+-------------------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
(4 rows)
  • pg_catalog contains the system tables and all the built-in data types, functions, and operators.
  • pg_toast is for overflow tables (too long columns)???
  • public there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should NOT use the public schema.
  • information_schema

Create and drop schemas

postgres=# CREATE SCHEMA world;
postgres=# DROP SCHEMA foodmart;
postgres=# DROP SCHEMA foodmart CASCADE;

List current schema

oli=# SELECT current_schema();
 current_schema
----------------
 public
(1 row)

Querying

Full qualified name or via search_path:

oli=# SELECT * FROM test.test;
 id | data |             ts
----+------+----------------------------
  1 | Bla  | 2025-08-28 10:13:28.428007
  2 | Bla  | 2025-08-28 10:13:32.404196
  3 | Bla  | 2025-08-28 10:13:35.099629
(3 rows)

oli=# SELECT * FROM test;
ERROR:  relation "test" does not exist
LINE 1: SELECT * FROM test;
                      ^

By default, search_path is set to $user,public, meaning it will search in the schema named after the user you are logged in as first, then it will search in public. To query tables in schemas that are not in your current search_path or tables that have been shadowed by a schema with higher precedence, you must specify which schema you mean.

oli=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

oli=# SELECT * FROM pg_settings WHERE name='search_path';
    name     |     setting     | unit |                    category                     |                              short_desc                               | extra_desc | context | vartype | source  | min_val | max_val | enumvals |    boot_val     |    reset_val    | sourcefile | sourceline | pending_restart
-------------+-----------------+------+-------------------------------------------------+-----------------------------------------------------------------------+------------+---------+---------+---------+---------+---------+----------+-----------------+-----------------+------------+------------+-----------------
 search_path | "$user", public |      | Client Connection Defaults / Statement Behavior | Sets the schema search order for names that are not schema-qualified. |            | user    | string  | default |         |         |          | "$user", public | "$user", public |            |            | f
(1 row)

Changing your current search_path

oli=# SET search_path TO test,"$user",public;

oli=# SHOW search_path;
      search_path
-----------------------
 test, "$user", public
(1 row)

Note that this is not a permanent command, it only lasts for the duration of the current session. Making a permanent change for a given user requires updating their account settings.

Querying via search_path:

oli=# SELECT * FROM test;
 id | data |             ts
----+------+----------------------------
  1 | Bla  | 2025-08-28 10:13:28.428007
  2 | Bla  | 2025-08-28 10:13:32.404196
  3 | Bla  | 2025-08-28 10:13:35.099629
(3 rows)

Sources: Schemas

Relation - Table, Sequence?

Tuple - Row

Tablespace

Sources: Hussein Nasser: Postgres Internal Architecture Explained