Databases: Part 3 — PostgreSQL

PostgreSQL — a free object-relational DBMS developed by an independent international community. The project started in 1986 at the University of California, Berkeley under the name POSTGRES; in 1996 it received its current name and SQL support. PostgreSQL is known for strict adherence to the SQL standard, reliability and extensibility: modules for geodata, time series, full-text and vector search are available.

License and versions

PostgreSQL is distributed under its own PostgreSQL License — a short permissive license similar to BSD and MIT. It can be used, modified and embedded into commercial products without royalties and without the obligation to open your code. The project is not owned by a single company, so the risk of sudden changes in terms, as with some other DBMSs, is minimal.

A new major version is released once a year, usually in autumn, and is supported for five years. The current version is PostgreSQL 18 (released in September 2025). The release of PostgreSQL 19 in 2026 is delayed: the release team is planning it for the end of October. Support for PostgreSQL 14 ends on November 12, 2026 — servers on that version should be upgraded.

Within a major version, patch releases (18.1, 18.2, and so on) are issued at least once a quarter. They should always be installed: they contain only bug fixes and security fixes and do not change the data format.

Where PostgreSQL fits

• Accounting and financial systems, ERP, CRM — where strict data integrity and complex queries are important.
• 1C:Enterprise. The 1C platform supports PostgreSQL; builds with modifications for 1C are released, including the Russian DBMS Postgres Pro by Postgres Professional. Postgres Pro is included in the Russian software registry and is released in the Standard, Enterprise and Certified editions (with an FSTEC certificate) — this is important for organizations that need import substitution.
• Web applications on Django, Ruby on Rails, Laravel, Node.js, where arrays, JSONB and full-text search are useful.
• Geographic information systems — with the PostGIS extension.
• Medium-volume analytics — window functions, materialized views and parallel query execution allow building reports without a separate analytical warehouse.

How PostgreSQL works

MVCC: concurrent work without mutual interference

PostgreSQL uses multiversion concurrency control (MVCC). When a row is modified, a new version of it is created and the old one remains available to transactions that started earlier. Thanks to this, reads do not block writes, and writes do not block reads. There are still locks: two transactions modifying the same row are executed in sequence.

The downside of MVCC is the obsolete row versions that need to be removed. This is handled by the autovacuum process. It must not be disabled, and long-running unfinished transactions (state idle in transaction) prevent it from working, causing tables to bloat.

JSONB

The JSONB type stores JSON in a parsed binary form and allows building indexes on it (GIN). You can have strict columns and a flexible field with arbitrary attributes in the same table — for example, product characteristics for different categories.

Extensions

Extensions add new data types, functions and indexes without changing the core:

• PostGIS — geometry and geography: distances, intersections, search for objects within a radius;

• pg_stat_statements — statistics for all executed queries: which are the most frequent and which take the most time. The first thing to enable on a production server. The module needs to be loaded at server start:

  Copy

  # postgresql.conf
  shared_preload_libraries = 'pg_stat_statements'

  then run CREATE EXTENSION pg_stat_statements; in the database;

• TimescaleDB — time series storage. Basic features are distributed under Apache 2.0, while advanced features (continuous aggregates, compression, retention policies) are under the developer’s own license: you can use them for free on your own servers, but you cannot sell them as a cloud service;

• pgvector — storage of vectors and similarity search, used in applications with language models.

Basic configuration

User and database for the application

CREATE ROLE shop LOGIN PASSWORD 'надёжный_пароль';
CREATE DATABASE shop OWNER shop;

The database owner gets full rights on it, and other users do not. Since version 15, ordinary users by default cannot create objects in the public schema of someone else’s database; this protects against a whole class of mistakes and attacks.

Who and from where can connect is controlled by the pg_hba.conf file. By default the server listens only on localhost; if the application is on another machine, specify the desired address in listen_addresses in postgresql.conf, and the allowed subnet and the scram-sha-256 method in pg_hba.conf.

Connection pool

Each connection to PostgreSQL is a separate operating system process. Several hundred concurrent connections consume a noticeable amount of memory and slow down the server. Therefore a connection pool is placed in front of PostgreSQL — most often PgBouncer. It keeps a small number of real connections to the database and hands them out to clients. Modes of operation:

• session — the connection is bound to the client for the whole session; safe for any applications, but the savings are small;
• transaction — the connection is given only for the duration of a transaction; gives the main benefit, but the application must not rely on session state (SET, temporary tables, advisory locks between transactions).

Backups

Logical backup: pg_dump

pg_dump -Fc -f shop.dump shop
pg_restore -d shop_restored shop.dump

The -Fc format is compressed and allows restoring individual tables. pg_dump copies a single database; server-level roles and permissions are saved separately with pg_dumpall --globals-only. Logical backups are convenient for small databases and for migration between major versions.

Physical backup and point-in-time recovery (PITR)

For large databases and the ability to roll back to a specific minute, physical copying is used:

1. Enable write-ahead log (WAL) archiving in postgresql.conf:

   Copy

   wal_level = replica
   archive_mode = on
   archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

2. Periodically take a base backup: pg_basebackup -D /backup/base -Ft -z -P.

3. When restoring, unpack the base backup, set restore_command and, if necessary, recovery_target_time, create a recovery.signal file in the data directory and start the server. PostgreSQL will apply WAL files up to the specified point.

In practice, this scheme is automated by ready-made tools — pgBackRest, Barman, WAL-G: they manage scheduling, storage, verification of backups and sending them to S3-compatible storage.

Common mistakes

• No connection pool — the application opens hundreds of connections, the server runs out of memory.
• Default settings. They are tuned for minimal hardware. At a minimum you should configure shared_buffers (usually around 25% of RAM), effective_cache_size and work_mem.
• Hanging transactions. Sessions in idle in transaction state interfere with autovacuum; the idle_in_transaction_session_timeout parameter terminates them automatically.
• In-place major version upgrade. Data files of different major versions are incompatible. The transition is performed via pg_upgrade, a logical dump, or logical replication — and is first tested on a copy.
• Backups without recovery testing. A backup should be regularly restored on a test server.

Conclusion

PostgreSQL is a strong default choice for a new project: a permissive license without the risk of changing terms, strict transactions, rich data types and extensions. Its entry barrier is higher than MySQL — you’ll need to figure out memory settings, a connection pool and backups — but these efforts pay off in reliability and capabilities you won’t need to look for in other DBMSs.