PostgreSQL Administration, Part 3 - Architecture and MVCC

Part 3 of the PostgreSQL course - we look at how PostgreSQL works internally: processes and memory, prepared statements, cursors, as well as the MVCC mechanism, data snapshots and transaction isolation levels 🐧.

Preamble

In the first and second parts we talked about tools - how to start a server and how to connect to it. But today we’re going to peek under the hood: why PostgreSQL can simultaneously serve hundreds of clients, what happens in memory when a query is executed, and why two transactions can simultaneously modify the database and not see each other’s “dirty” data.

The second focus of this lesson is MVCC (Multiversion Concurrency Control). We’ll look at how this mechanism works in practice: we’ll open two parallel psql sessions and see how the same row looks different for different transactions.

Prerequisites

Software used in the article:

The setup is the same postgres container from the first part, running and accessible at 127.0.0.1:5432.

Client-server architecture

PostgreSQL follows the classic client-server model. The server is a control process postmaster that listens on a port and forks a separate process - backend - for each new connection. This is why PostgreSQL has “expensive” connections: not a thread within a single process, but a full-fledged OS process for each client.

You can verify this without leaving your container:

cd ~/Postgres

docker compose exec postgres ps -eHf

The output will look something like this:

UID          PID    PPID  C STIME TTY          TIME CMD
postgres       1       0  0 Sep08 ?        00:00:11 postgres
postgres      27       1  0 Sep08 ?        00:00:00   postgres: io worker 0
postgres      28       1  0 Sep08 ?        00:00:00   postgres: io worker 1
postgres      29       1  0 Sep08 ?        00:00:00   postgres: io worker 2
postgres      30       1  0 Sep08 ?        00:00:00   postgres: checkpointer 
postgres      31       1  0 Sep08 ?        00:00:00   postgres: background writer 
postgres      33       1  0 Sep08 ?        00:00:00   postgres: walwriter 
postgres      34       1  0 Sep08 ?        00:00:01   postgres: autovacuum launcher 
postgres      35       1  0 Sep08 ?        00:00:00   postgres: logical replication launcher

Notice - there are no backend processes for a specific client here, only postmaster itself (PID 1) and background service processes. Let’s open a connection and try again:

docker compose exec postgres psql -U ivan -d raven

SELECT pg_backend_pid();

\! ps -eHf

A new line postgres: ivan raven [local] idle will appear in the list - this is our backend, the same PID that was returned by pg_backend_pid(). As soon as the session ends - the process disappears.

UID          PID    PPID  C STIME TTY          TIME CMD
root       52196       0  0 17:08 pts/1    00:00:00 /usr/lib/postgresql/18/bin/psql -U ivan -d raven
root       52341   52340  0 17:10 pts/1    00:00:00     ps -eHf
postgres       1       0  0 Sep08 ?        00:00:11 postgres
postgres      27       1  0 Sep08 ?        00:00:00   postgres: io worker 0
postgres      28       1  0 Sep08 ?        00:00:00   postgres: io worker 1
postgres      29       1  0 Sep08 ?        00:00:00   postgres: io worker 2
postgres      30       1  0 Sep08 ?        00:00:00   postgres: checkpointer 
postgres      31       1  0 Sep08 ?        00:00:00   postgres: background writer 
postgres      33       1  0 Sep08 ?        00:00:00   postgres: walwriter 
postgres      34       1  0 Sep08 ?        00:00:01   postgres: autovacuum launcher 
postgres      35       1  0 Sep08 ?        00:00:00   postgres: logical replication launcher 
postgres   52202       1  0 17:08 ?        00:00:00   postgres: ivan raven [local] idle

In DBeaver there’s no direct access to ps -eHf (and you don’t need it), but the list of active backends is visible in the Sessions section of the connection properties (right-click on the connection in Tools → Session Manager) - essentially the same information as in pg_stat_activity, but in a table with a button to “kill” a session.

Memory: shared and local

Each backend process has access to two types of memory:

• Shared memory - buffer cache (shared_buffers), lock table and other structures visible to all backends simultaneously;
• Local memory - what belongs to only one backend: prepared statements, cursors, sorting memory (work_mem) and so on.

SHOW shared_buffers;

SHOW work_mem;

In DBeaver all server parameters are immediately visible in the connection properties, Configuration tab - a convenient tabular view of the same pg_settings that we touched in the second part.

Prepared statements

A prepared statement (PREPARE) parses a query once and saves the execution plan in the session’s local memory. The main benefit of PREPARE is to avoid re-parsing/planning a query when executing it multiple times with different parameters.

PREPARE get_now AS SELECT now();

EXECUTE get_now;

SELECT * FROM pg_prepared_statements;

DEALLOCATE get_now;

• PREPARE get_now AS SELECT now(); - creates a named query get_now, which when executed will return the current date/time. The query is parsed and planned in advance, just once;
• EXECUTE get_now; - executes a previously prepared query. If the query had parameters ($1, $2, etc.), you would pass them here;
• SELECT * FROM pg_prepared_statements; - a system view that shows a list of all prepared statements in the current session (name, query text, creation time, parameter types, etc.);
• DEALLOCATE get_now; - removes the prepared statement, freeing memory.

Cursors

A cursor allows you to fetch query results in chunks, without loading everything into memory at once:

BEGIN;

DECLARE c CURSOR FOR SELECT * FROM pg_roles;

FETCH 2 FROM c;
FETCH 2 FROM c;

CLOSE c;
COMMIT;

DBeaver actually uses cursors internally - when a query result is large, it loads rows in batches, the size of which can be configured in Preferences → Editors → Data Editor (ResultSet fetch size). The only difference is that it happens automatically rather than by an explicit FETCH command.

MVCC: multiversion concurrency

PostgreSQL doesn’t overwrite rows on UPDATE - instead it creates a new version of the row, and marks the old one as stale. Each version stores two system columns:

• xmin - the transaction number that created this version;
• xmax - the transaction number that deleted this version (0 if the version is still current).

Let’s create a demo table - it has no relation to the future raven schema, it’s just a sandbox for examples:

CREATE TABLE demo_mvcc (id INT PRIMARY KEY, note TEXT);

INSERT INTO demo_mvcc VALUES (1, 'первая версия');

SELECT xmin, xmax, * FROM demo_mvcc;

Now to demonstrate MVCC, let’s open two psql sessions in the terminal:

docker compose exec postgres psql -U ivan -d raven

In the first session (let’s call it A) we start a transaction and change a row, but don’t commit:

BEGIN;

UPDATE demo_mvcc SET note = 'вторая версия' WHERE id = 1;

In the second session (B) we look at the same row:

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id |     note      
------+------+----+---------------
  777 |    0 |  1 | первая версия

Session B sees the old version - первая версия, even though A has already executed UPDATE.

Let’s go back to A and commit:

COMMIT;

Repeat the SELECT in session B - now it sees the new version.

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id |     note      
------+------+----+---------------
  778 |    0 |  1 | вторая версия

The key point: both the old and new versions of the row were physically present in the table at the same time, but each transaction had its own data snapshot that determined which version to show it.

Transaction isolation levels

The data snapshot that a transaction sees depends on the isolation level:

Let’s repeat the previous experiment, but in session B we explicitly set REPEATABLE READ:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id |     note      
------+------+----+---------------
  775 |    0 |  1 | вторая версия

BEGIN;

UPDATE demo_mvcc SET note = 'третья версия' WHERE id = 1;

COMMIT;

SELECT xmin, xmax, * FROM demo_mvcc; -- we see the same version as at the beginning

 xmin | xmax | id |     note      
------+------+----+---------------
  775 |  776 |  1 | вторая версия

COMMIT;

SELECT xmin, xmax, * FROM demo_mvcc; -- and now the new one

 xmin | xmax | id |     note      
------+------+----+---------------
  776 |    0 |  1 | третья версия

In Read Committed (the default) the second SELECT in session B would see the new version immediately after session A’s COMMIT, even without exiting its own transaction - this is why the isolation level is not set once “for the whole project”, but is consciously chosen for the task at hand.

You can change the isolation level in DBeaver directly on the panel, using a special toggle:

A brief note on locks in PostgreSQL

If two clients try to modify the same row simultaneously - MVCC doesn’t help here, you have to wait. Let’s repeat the experiment with sessions A and B, but now both will write:

BEGIN;

UPDATE demo_mvcc SET note = 'от A' WHERE id = 1;

UPDATE demo_mvcc SET note = 'от B' WHERE id = 1; -- will hang

Session B will wait:

You can see who blocks whom from a third session:

SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event IS NOT NULL;

SELECT pg_blocking_pids(pid), * FROM pg_stat_activity WHERE pid = <PID of session B>;

As soon as session A executes COMMIT or ROLLBACK - session B immediately resumes.

COMMIT;

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id | note 
------+------+----+------
  784 |  784 |  1 | от B

SELECT xmin, xmax, * FROM demo_mvcc;

 xmin | xmax | id | note 
------+------+----+------
  784 |  784 |  1 | от B

A full discussion of locks, pg_locks and deadlocks will be a separate lesson - here it’s important to note only one fact: reads never block and are never blocked, but concurrent writes to the same row - are always blocked, MVCC has nothing to do with it.

DDL - also transactions

A separate nice feature of PostgreSQL: CREATE, ALTER, DROP commands - are just as transactional as normal INSERT. Unlike, for example, MySQL, where DDL is committed immediately.

BEGIN;

CREATE TABLE ddl_demo (id INT);

\dt ddl_demo -- table is not visible, even though A has already created it

Did not find any tables named "ddl_demo".

ROLLBACK; -- table won't be created at all

If instead of ROLLBACK there was COMMIT - the table would appear in session B too. This also works for multiple DDL commands in a row in a single transaction - the entire block either applies in full or not at all.

Cleaning up after yourself

The demo table is no longer needed:

DROP TABLE demo_mvcc;

💡 Tip

When you execute DROP in DBeaver, the Beaver kindly asks you to confirm the table deletion.

Possible issues

• DBeaver “hangs” on a simple SELECT

Almost always the reason is that an open transaction without COMMIT/ROLLBACK remains in another SQL editor of the same connection (it’s especially easy to forget when Auto-commit is turned off).

• A test deadlock between sessions A and B

If in the example above you change the order and in session B first try to modify a row that A is already holding, and then let A try to modify what B is holding - you’ll get a classic deadlock, and PostgreSQL itself will interrupt one of the transactions with a deadlock detected error.

ERROR:  deadlock detected
DETAIL:  Process 49 waits for ShareLock on transaction 800; blocked by process 42.
Process 42 waits for ShareLock on transaction 797; blocked by process 49.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,8) in relation "demo_mvcc"

This is normal protective behavior, not a bug - but with frequent deadlocks it’s worth reviewing the lock order in your application code.

Afterword

Gradually diving deeper into PostgreSQL…

MVCC is a fairly abstract thing, especially in words. But in this lesson we got to see firsthand how two sessions look at one row. You may have also heard somewhere about VACUUM - the mechanism for cleaning up those old row versions that we looked at today through xmin/xmax.

The next topic is configuring PostgreSQL: postgresql.conf, ALTER SYSTEM and where PostgreSQL actually gets the current parameter values from. Don’t miss it.

Thank you for reading. Good luck learning PostgreSQL! 🐧

Reference materials

• Documentation: PostgreSQL architecture overview
• Documentation: Concurrency via MVCC
• Documentation: Transaction isolation levels

👨‍💻And…

Don’t forget about our Telegram channel 📱 and chat

Or maybe you want to become a co-author? Then click here🔗

💬 All the best ✌️

That should be it. If not, check the logs 🙂