Tag: postgresql

Old tech is sometimes better – a case study based on blogpost from Now-Next…

Some time ago I read this blogpost.

They wrote about logging changes to table using a trigger, but one thing that caught my attention was:

Storing only the changed columns made the log five times smaller and the trigger slower.

Figured I can try it myself…

Continue reading Old tech is sometimes better – a case study based on blogpost from Now-Next…

New things for regular expressions in PostgreSQL (pg_tre and pg_re2)

Well, truth be told these are not all that new (couple of months), but I finally have gotten around to research it.

So, let's see what's what. For starters I need some test data. Luckily, I have explain.depesz.com DB…

Continue reading New things for regular expressions in PostgreSQL (pg_tre and pg_re2)

Waiting for PostgreSQL 19 – SQL Property Graph Queries (SQL/PGQ)

Important update

This change has been rolled back.

Discussion can be found here.

On 16th of March 2026, Peter Eisentraut committed patch:

SQL Property Graph Queries (SQL/PGQ)
 
Implementation of SQL property graph queries, according to SQL/PGQ
standard (ISO/IEC 9075-16:2023).
 
This adds:
 
- GRAPH_TABLE table function for graph pattern matching
- DDL commands CREATE/ALTER/DROP PROPERTY GRAPH
- several new system catalogs and information schema views
- psql \dG command
- pg_get_propgraphdef() function for pg_dump and psql
 
A property graph is a relation with a new relkind RELKIND_PROPGRAPH.
It acts like a view in many ways.  It is rewritten to a standard
relational query in the rewriter.  Access privileges act similar to a
security invoker view.  (The security definer variant is not currently
implemented.)
 
Starting documentation can be found in doc/src/sgml/ddl.sgml and
doc/src/sgml/queries.sgml.
 
Author: Peter Eisentraut <peter@eisentraut.org>
Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Junwang Zhao <zhjwpku@gmail.com>
Reviewed-by: Ajay Pal <ajay.pal.k@gmail.com>
Reviewed-by: Henson Choi <assam258@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/a855795d-e697-4fa5-8698-d20122126567@eisentraut.org

Continue reading Waiting for PostgreSQL 19 – SQL Property Graph Queries (SQL/PGQ)

Waiting for PostgreSQL 20 – Add min() and max() aggregate support for uuid.

On 1st of July 2026, Masahiko Sawada committed patch:

Add min() and max() aggregate support for uuid.
 
The uuid type already has a full set of comparison operators and a
btree operator class, so it is totally ordered.  min() and max() were
the only common aggregates missing for it. Add the uuid_larger() and
uuid_smaller() support functions and register the min(uuid) and
max(uuid) aggregates that use them.
 
uuid values are compared lexicographically over their 128 bits.  For
UUIDv7, whose most significant bits encode a Unix timestamp, this
coincides with chronological order, so min() and max() return the
oldest and newest values.
 
Bump catalog version.
 
Author: Tristan Partin <tristan@partin.io>
Reviewed-by: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Reviewed-by: Zsolt Parragi <zsolt.parragi@percona.com>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://postgr.es/m/DJGML0T9FCDV.3VA29JLODXEHZ@partin.io

Continue reading Waiting for PostgreSQL 20 – Add min() and max() aggregate support for uuid.

Waiting for PostgreSQL 20 – Add backend-level lock statistics

On 30th of June 2026, Michael Paquier committed patch:

Add backend-level lock statistics
 
This commit adds per-backend lock statistics, providing the same
information as pg_stat_lock.  It is now possible to retrieve those stats
(lock wait counts, wait times, and fast-path exceeded count) on a
per-backend basis.
 
This data can be retrieved with a new system function called
pg_stat_get_backend_lock(), that returns one tuple per lock type based
on the PID provided in input.  Like pg_stat_get_backend_io(), this is
useful if joined with pg_stat_activity to get a live picture of the
locks behavior for each running backend.
 
pgstat_flush_backend() gains a new flag value, able to control the flush
of the lock stats.
 
This commit is straight-forward, relying on the infrastructure provided
by 9aea73fc61d4 (backend-level pgstats).
 
Bump catalog version.  No need to touch PGSTAT_FILE_FORMAT_ID as backend
statistics are never written to disk.
 
Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Reviewed-by: Tatsuya Kawata <kawatatatsuya0913@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Tristan Partin <tristan@partin.io>
Reviewed-by: Rui Zhao <zhaorui126@gmail.com>
Discussion: https://postgr.es/m/aiAzEY+cMQb/W8yu@bdtpg

Continue reading Waiting for PostgreSQL 20 – Add backend-level lock statistics

Waiting for PostgreSQL 19 – Add CONCURRENTLY option to REPACK

On 6th of April 2026, Álvaro Herrera committed patch:

Add CONCURRENTLY option to REPACK
 
When this flag is specified, REPACK no longer acquires access-exclusive
lock while the new copy of the table is being created; instead, it
creates the initial copy under share-update-exclusive lock only (same as
vacuum, etc), and it follows an MVCC snapshot; it sets up a replication
slot starting at that snapshot, and uses a concurrent background worker
to do logical decoding starting at the snapshot to populate a stash of
concurrent data changes.  Those changes can then be re-applied to the
new copy of the table just before swapping the relfilenodes.
Applications can continue to access the original copy of the table
normally until just before the swap, which is the only point at which
the access-exclusive lock is needed.
 
There are some loose ends in this commit:
1. concurrent repack needs its own replication slot in order to apply
   logical decoding, which are a scarce resource and easy to run out of.
2. due to the way the historic snapshot is initially set up, only one
   REPACK process can be running at any one time on the whole system.
3. there's a danger of deadlocking (and thus abort) due to the lock
   upgrade required at the final phase.
 
These issues will be addressed in upcoming commits.
 
The design and most of the code are by Antonin Houska, heavily based on
his own pg_squeeze third-party implementation.
 
Author: Antonin Houska <ah@cybertec.at>
Co-authored-by: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Co-authored-by: Álvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com>
Reviewed-by: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Robert Treat <rob@xzilla.net>
Reviewed-by: Noriyoshi Shinoda <noriyoshi.shinoda@hpe.com>
Reviewed-by: vignesh C <vignesh21@gmail.com>
Discussion: https://postgr.es/m/5186.1706694913@antos
Discussion: https://postgr.es/m/202507262156.sb455angijk6@alvherre.pgsql

Continue reading Waiting for PostgreSQL 19 – Add CONCURRENTLY option to REPACK

Waiting for PostgreSQL 19 – Online enabling and disabling of data checksums

On 3rd of April 2026, Daniel Gustafsson committed patch:

Online enabling and disabling of data checksums
 
This allows data checksums to be enabled, or disabled, in a running
cluster without restricting access to the cluster during processing.
 
Data checksums could prior to this only be enabled during initdb or
when the cluster is offline using the pg_checksums app. This commit
introduce functionality to enable, or disable, data checksums while
the cluster is running regardless of how it was initialized.
 
A background worker launcher process is responsible for launching a
dynamic per-database background worker which will mark all buffers
dirty for all relation with storage in order for them to have data
checksums calculated on write.  Once all relations in all databases
have been processed, the data_checksums state will be set to on and
the cluster will at that point be identical to one which had data
checksums enabled during initialization or via offline processing.
 
When data checksums are being enabled, concurrent I/O operations
from backends other than the data checksums worker will write the
checksums but not verify them on reading.  Only when all backends
have absorbed the procsignalbarrier for setting data_checksums to
on will they also start verifying checksums on reading.  The same
process is repeated during disabling; all backends write checksums
but do not verify them until the barrier for setting the state to
off has been absorbed by all.  This in-progress state is used to
ensure there are no false negatives (or positives) due to reading
a checksum which is not in sync with the page.
 
A new testmodule, test_checksums, is introduced with an extensive
set of tests covering both online and offline data checksum mode
changes.  The tests which run concurrent pgbdench during online
processing are gated behind the PG_TEST_EXTRA flag due to being
very expensive to run.  Two levels of PG_TEST_EXTRA flags exist
to turn on a subset of the expensive tests, or the full suite of
multiple runs.
 
This work is based on an earlier version of this patch which was
reviewed by among others Heikki Linnakangas, Robert Haas, Andres
Freund, Tomas Vondra, Michael Banck and Andrey Borodin.  During
the work on this new version, Tomas Vondra has given invaluable
assistance with not only coding and reviewing but very in-depth
testing.
 
Author: Daniel Gustafsson <daniel@yesql.se>
Author: Magnus Hagander <magnus@hagander.net>
Co-authored-by: Tomas Vondra <tomas@vondra.me>
Reviewed-by: Tomas Vondra <tomas@vondra.me>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi>
Discussion: https://postgr.es/m/CABUevExz9hUUOLnJVr2kpw9Cx=o4MCr1SVKwbupzuxP7ckNutA@mail.gmail.com
Discussion: https://postgr.es/m/20181030051643.elbxjww5jjgnjaxg@alap3.anarazel.de
Discussion: https://postgr.es/m/CABUevEwE3urLtwxxqdgd5O2oQz9J717ZzMbh+ziCSa5YLLU_BA@mail.gmail.com

Continue reading Waiting for PostgreSQL 19 – Online enabling and disabling of data checksums

Waiting for PostgreSQL 19 – new pg_get_*_ddl() functions

update

This has been reverted, for now.

On 5th of April 2026, Andrew Dunstan committed patch:

Add pg_get_database_ddl() function
 
Add a new SQL-callable function that returns the DDL statements needed
to recreate a database. It takes a regdatabase argument and an optional
VARIADIC text argument for options that are specified as alternating
name/value pairs. The following options are supported: pretty (boolean)
for formatted output, owner (boolean) to include OWNER and tablespace
(boolean) to include TABLESPACE. The return is one or multiple rows
where the first row is a CREATE DATABASE statement and subsequent rows are
ALTER DATABASE statements to set some database properties.
 
The caller must have CONNECT privilege on the target database.
 
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Co-authored-by: Andrew Dunstan <andrew@dunslane.net>
Co-authored-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Quan Zongliang <quanzongliang@yeah.net>
Discussion: https://postgr.es/m/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com
Discussion: https://postgr.es/m/e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net

and 2nd patch:

Add pg_get_role_ddl() function
 
Add a new SQL-callable function that returns the DDL statements needed
to recreate a role. It takes a regrole argument and an optional VARIADIC
text argument for options that are specified as alternating name/value
pairs. The following options are supported: pretty (boolean) for
formatted output and memberships (boolean) to include GRANT statements
for role memberships and membership options. The return is one or
multiple rows where the first row is a CREATE ROLE statement and
subsequent rows are ALTER ROLE statements to set some role properties.
Password information is never included in the output.
 
The caller must have SELECT privilege on pg_authid.
 
Author: Mario Gonzalez <gonzalemario@gmail.com>
Author: Bryan Green <dbryan.green@gmail.com>
Co-authored-by: Andrew Dunstan <andrew@dunslane.net>
Co-authored-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Quan Zongliang <quanzongliang@yeah.net>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://postgr.es/m/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com
Discussion: https://postgr.es/m/e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net

and then 3rd patch:

Add pg_get_tablespace_ddl() function
 
Add a new SQL-callable function that returns the DDL statements needed
to recreate a tablespace. It takes a tablespace name or OID and an
optional VARIADIC text argument for options that are specified as
alternating name/value pairs. The following options are supported: pretty
(boolean) for formatted output and owner (boolean) to include OWNER.
(It includes two variants because there is no regtablespace pseudotype.)
The return is one or multiple rows where the first row is a CREATE
TABLESPACE statement and subsequent rows are ALTER TABLESPACE statements
to set some tablespace properties.
 
The caller must have SELECT privilege on pg_tablespace.
 
get_reloptions() in ruleutils.c is made non-static so it can be called
from the new ddlutils.c file.
 
Author: Nishant Sharma <nishant.sharma@enterprisedb.com>
Author: Manni Wood <manni.wood@enterprisedb.com>
Co-authored-by: Andrew Dunstan <andrew@dunslane.net>
Co-authored-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
Discussion: https://postgr.es/m/e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net

Continue reading Waiting for PostgreSQL 19 – new pg_get_*_ddl() functions