select * from depesz;

Waiting for PostgreSQL 19 – Add non-text output formats to pg_dumpall

On 26th of February 2026, Andrew Dunstan committed patch:

Add non-text output formats to pg_dumpall
 
pg_dumpall can now produce output in custom, directory, or tar formats
in addition to plain text SQL scripts. When using non-text formats,
pg_dumpall creates a directory containing:
- toc.glo: global data (roles and tablespaces) in custom format
- map.dat: mapping between database OIDs and names
- databases/: subdirectory with per-database archives named by OID
 
pg_restore is extended to handle these pg_dumpall archives, restoring
globals and then each database. The --globals-only option can be used
to restore only the global objects.
 
This enables parallel restore of pg_dumpall output and selective
restoration of individual databases from a cluster-wide backup.
 
Author: Mahendra Singh Thalor <mahi6run@gmail.com>
Co-Author: Andrew Dunstan <andrew@dunslane.net>
Reviewed-By: Tushar Ahuja <tushar.ahuja@enterprisedb.com>
Reviewed-By: Jian He <jian.universality@gmail.com>
Reviewed-By: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewed-By: Srinath Reddy <srinath2133@gmail.com>
Discussion: https://postgr.es/m/cb103623-8ee6-4ba5-a2c9-f32e3a4933fa@dunslane.net

Continue reading Waiting for PostgreSQL 19 – Add non-text output formats to pg_dumpall

Waiting for PostgreSQL 19 – Allow log_min_messages to be set per process type

On 9th of February 2026, Álvaro Herrera committed patch:

Allow log_min_messages to be set per process type
 
Change log_min_messages from being a single element to a comma-separated
list of type:level elements, with 'type' representing a process type,
and 'level' being a log level to use for that type of process.  The list
must also have a freestanding level specification which is used for
process types not listed, which convenientely makes the whole thing
backwards-compatible.
 
Some choices made here could be contested; for instance, we use the
process type `backend` to affect regular backends as well as dead-end
backends and the standalone backend, and `autovacuum` means both the
launcher and the workers.  I think it's largely sensible though, and it
can easily be tweaked if desired.
 
Author: Euler Taveira <euler@eulerto.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Tan Yang <332696245@qq.com>
Discussion: https://postgr.es/m/e85c6671-1600-4112-8887-f97a8a5d07b2@app.fastmail.com

Continue reading Waiting for PostgreSQL 19 – Allow log_min_messages to be set per process type

Waiting for PostgreSQL 19 – psql: Add %i prompt escape to indicate hot standby status.

On 3rd of February 2026, Fujii Masao committed patch:

psql: Add %i prompt escape to indicate hot standby status.
 
This commit introduces a new prompt escape %i for psql, which shows
whether the connected server is operating in hot standby mode. It
expands to standby if the server reports in_hot_standby = on, and
primary otherwise.
 
This is useful for distinguishing standby servers from primary ones
at a glance, especially when working with multiple connections in
replicated environments where libpq's multi-host connection strings
are used.
 
Author: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Fujii Masao <masao.fujii@gmail.com>
Reviewed-by: Greg Sabino Mullane <htamfids@gmail.com>
Reviewed-by: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Reviewed-by: Nathan Bossart <nathandbossart@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Andreas Karlsson <andreas@proxel.se>
Discussion: https://www.postgresql.org/message-id/flat/016f6738-f9a9-4e98-bb5a-e1e4b9591d46@uni-muenster.de

Continue reading Waiting for PostgreSQL 19 – psql: Add %i prompt escape to indicate hot standby status.

Per-worker, and global, IO bandwidth in explain plans

Jeremy Schneider suggested a change to how plans are displayed – adding another bit of information in case we have timing information for IO for explain node. Took me a while to research, but it finally made it's way…

Continue reading Per-worker, and global, IO bandwidth in explain plans

How to render timestamp with a timezone that is different from current?

This question appeared on IRC, and while I wasn't there while it happened, it caught my eye:

» Can I not render this with timezone offset: select ‘2026-01-09 04:35:46.9824-08'::timestamp with time zone at time zone ‘UTC';
» Returns ‘2026-01-09 12:35:46.9824' which is without the offset.

Continue reading How to render timestamp with a timezone that is different from current?

Quick and dirty loading of CSV files

Back in September 2025, David Fetter asked on IRC, about a tool to quickly load CSV to database. One that would require minimal configuration, will try to figure out as much as possible on its own.

I thought that it would be great idea. Plus, I'm trying to learn more JavaScript / Node, so figured I can try.

Work and life intervened, and I couldn't get to it.

In the mean time I also read a lot of praise for some AI tools for quickly starting projects, or adding functionality, or learning new stuff. So, I decided to try.

Used Claude to make the basics, and then worked to add more tests and functionality.

Long story short, the tool is here.

It does what David wanted – works as simply as possible. Assuming you have your environment configured, you can just:

=$ pg_csv_loader some_file.csv

And it will figure out delimiter, quote character, column names, datatypes, and will load it to default database – which works just like normal psql

Tested it on couple different csv files, and it seems to work. It also handled PostgreSQL csv logs, which are often problematic as they use literal new line characters, which is causing problems for some parsers.

I don't claim that I wrote it, but I did extend tests, and datatype detection, so I guess I have some partial credit 🙂

Hope you'll find it useful.

Small improvement for pretty-printing in paste.depesz.com

As you maybe know, some time ago I made paste service, mostly to use for queries, or related text to share on IRC.

One part of it is that it also has pretty printer of provided queries.

Recently I realized that in case of complex join conditions, the output is, well, sub-optimal. For example:

SELECT
    t1.a,
    t2.b,
    t3.c
FROM
    table_1 AS t1
    JOIN table_2 AS t2 ON t1.x = t2.x AND
    t1.y = t2.y AND
    t1.z = t2.z
    LEFT JOIN table_3 AS t3 ON t1.f = t3.f AND
    t2.g = t3.g
WHERE
    t1.v = 1 AND
    t2.t = 'x' AND
    t3.m < 123

Specifically the problem (for me) is bad indentation of join conditions (aside from first).

Anyway – as of now, the same query will be pretty-printed as:

SELECT
    t1.a,
    t2.b,
    t3.c
FROM
    table_1 AS t1
    JOIN table_2 AS t2 ON t1.x = t2.x
        AND t1.y = t2.y
        AND t1.z = t2.z
    LEFT JOIN table_3 AS t3 ON t1.f = t3.f
        AND t2.g = t3.g
WHERE
    t1.v = 1
    AND t2.t = 'x'
    AND t3.m < 123

Also, as a reminder – you can use this pretty printer from command line or some tools, without storing anything on paste.depesz.com site – simply use script that I described earlier.

Hope you'll find it useful.

What is index overhead on writes?

One of things people learn is that adding indexes isn't free. All write operations (insert, update, delete) will be slower – well, they have to update index.

But realistically – how much slower?

Full tests should involve lots of operations, on realistic data, but I just wanted to see some basic info. So I figured I'll just look at speed of inserting data (well, COPYing data), and will try to extract some knowledge from it…

Continue reading What is index overhead on writes?

Waiting for PostgreSQL 19 – Implement ALTER TABLE … MERGE/SPLIT PARTITIONS … command

On 14th of December 2025, Alexander Korotkov committed patch:

Implement ALTER TABLE ... MERGE PARTITIONS ... command
 
This new DDL command merges several partitions into a single partition of the
target table.  The target partition is created using the new
createPartitionTable() function with the parent partition as the template.
 
This commit comprises a quite naive implementation which works in a single
process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all
the operations, including the tuple routing.  This is why this new DDL
command can't be recommended for large partitioned tables under a high load.
However, this implementation comes in handy in certain cases, even as it is.
Also, it could serve as a foundation for future implementations with less
locking and possibly parallelism.
 
Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru
Author: Dmitry Koval <d.koval@postgrespro.ru>
Co-authored-by: Alexander Korotkov <aekorotkov@gmail.com>
Co-authored-by: Tender Wang <tndrwang@gmail.com>
Co-authored-by: Richard Guo <guofenglinux@gmail.com>
Co-authored-by: Dagfinn Ilmari Mannsaker <ilmari@ilmari.org>
Co-authored-by: Fujii Masao <masao.fujii@gmail.com>
Co-authored-by: Jian He <jian.universality@gmail.com>
Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com>
Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Robert Haas <rhaas@postgresql.org>
Reviewed-by: Stephane Tachoires <stephane.tachoires@gmail.com>
Reviewed-by: Jian He <jian.universality@gmail.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-by: Alexander Lakhin <exclusion@gmail.com>
Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Reviewed-by: Daniel Gustafsson <dgustafsson@postgresql.org>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Noah Misch <noah@leadboat.com>

and then, mere seconds later, he also committed second patch:

Implement ALTER TABLE ... SPLIT PARTITION ... command
 
This new DDL command splits a single partition into several partitions.  Just
like the ALTER TABLE ... MERGE PARTITIONS ... command, new partitions are
created using the createPartitionTable() function with the parent partition
as the template.
 
This commit comprises a quite naive implementation which works in a single
process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all
the operations, including the tuple routing.  This is why the new DDL command
can't be recommended for large, partitioned tables under high load.  However,
this implementation comes in handy in certain cases, even as it is.  Also, it
could serve as a foundation for future implementations with less locking and
possibly parallelism.
 
Discussion: https://postgr.es/m/c73a1746-0cd0-6bdd-6b23-3ae0b7c0c582%40postgrespro.ru
Author: Dmitry Koval <d.koval@postgrespro.ru>
Co-authored-by: Alexander Korotkov <aekorotkov@gmail.com>
Co-authored-by: Tender Wang <tndrwang@gmail.com>
Co-authored-by: Richard Guo <guofenglinux@gmail.com>
Co-authored-by: Dagfinn Ilmari Mannsaker <ilmari@ilmari.org>
Co-authored-by: Fujii Masao <masao.fujii@gmail.com>
Co-authored-by: Jian He <jian.universality@gmail.com>
Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com>
Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Alvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Robert Haas <rhaas@postgresql.org>
Reviewed-by: Stephane Tachoires <stephane.tachoires@gmail.com>
Reviewed-by: Jian He <jian.universality@gmail.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-by: Alexander Lakhin <exclusion@gmail.com>
Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Reviewed-by: Daniel Gustafsson <dgustafsson@postgresql.org>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Noah Misch <noah@leadboat.com>

Continue reading Waiting for PostgreSQL 19 – Implement ALTER TABLE … MERGE/SPLIT PARTITIONS … command

Using JSON: json vs. jsonb, pglz vs. lz4, key optimization, parsing speed?

Recently(ish) I had a conversation on one of PostgreSQL support chats (IRC, Slack, or Discord) about efficient storage of JSON data, which compression to use, which datatype.

Unrelated to this, some people (at least two over the last year or so) said that they aren't sure if PostgreSQL doesn't optimize storage between columns, for example, storing attribute names once per column, and not once per value.

Decided to investigate…

Continue reading Using JSON: json vs. jsonb, pglz vs. lz4, key optimization, parsing speed?