New PostgreSQL Features I Developed in 2025

Source: Dev.to

Overview

I started contributing to PostgreSQL around 2020. In 2025 I wanted to work harder, so I’ll explain the PostgreSQL features I developed and committed this year.

I also committed some other patches, but they were bug fixes or small documentation changes. Below are the ones that seem most useful.

Note: These are mainly features in PostgreSQL 19, now in development. They may be reverted before the final release.

1. Recommend default psql settings when restoring pg_dump backups

• Title: Doc: recommend “psql ‑X” for restoring pg_dump scripts
• Committer: Tom Lane
• Date: Sat, 25 Jan 2025

When you restore a dump file made by pg_dump with psql, you may get errors if psql is using non‑default settings (e.g., AUTOCOMMIT=off). The change is only in the docs: it recommends using the psql option -X (--no-psqlrc) to avoid reading the psqlrc configuration file.

For my past blog about the psqlrc file, see:

Example

# create a test database
createdb test1

# dump all databases to an SQL script file
# -c issues DROP for databases, roles, and tablespaces before recreating them
pg_dumpall -c -f test1.sql

# restore with psql (without -X)
psql -f test1.sql
# → errors:
# psql:test1.sql:14: ERROR:  DROP DATABASE cannot run inside a transaction block
# psql:test1.sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
# …

DROP DATABASE (generated by -c) cannot run inside a transaction block, so you see those errors. By default, when a statement fails inside a transaction block, the whole transaction aborts, causing later statements to fail as well. Using psql -X avoids this problem.

2. Add backup_type column to pg_stat_progress_basebackup view

• Title: Add backup_type column to pg_stat_progress_basebackup
• Committer: Masahiko Sawada
• Date: Tue, 5 Aug 2025

PostgreSQL 17 added incremental backup support to pg_basebackup, but the pg_stat_progress_basebackup view had no column to indicate whether a backup was full or incremental. The new backup_type column shows full or incremental.

Demonstration

# Full backup
pg_basebackup -D full

SELECT * FROM pg_stat_progress_basebackup;

-[ RECORD 1 ]--------+-------------------------
pid                  | 853626
phase                | streaming database files
backup_total         | 1592460800
backup_streamed      | 622124544
tablespaces_total    | 1
tablespaces_streamed | 0
backup_type          | full                     -- new!

# Incremental backup
pg_basebackup -i full/backup_manifest -D incl

SELECT * FROM pg_stat_progress_basebackup;

-[ RECORD 1 ]--------+-------------------------
pid                  | 854435
phase                | streaming database files
backup_total         | 1613615104
backup_streamed      | 726617088
tablespaces_total    | 1
tablespaces_streamed | 0
backup_type          | incremental              -- new!

3. COPY FROM now supports files with multi‑line headers

• Title: Support multi‑line headers in COPY FROM command
• Committer: Fujii Masao
• Date: Thu, 3 Jul 2025

The COPY command’s HEADER option controls header handling. Previously, COPY FROM could only skip a single header line (boolean). It now accepts an integer, allowing you to skip any number of header lines.

Example

\! cat /tmp/copy.csv
first header
second header
1,one
2,two
3,three

COPY t FROM '/tmp/copy.csv' WITH (HEADER 2, FORMAT csv);
-- COPY 3

TABLE t;
 id | data
----+------
  1 | one
  2 | two
  3 | three
(3 rows)

Similar functionality in other RDBMS

4. Split the autovacuum log settings for VACUUM and ANALYZE

• Title: Add log_autoanalyze_min_duration
• Committer: Peter Eisentraut
• Date: Wed, 15 Oct 2025

We already had log_autovacuum_min_duration, which logged both VACUUM and ANALYZE performed by autovacuum when they exceeded the configured duration (default unit: ms). This change introduces a separate setting, log_autoanalyze_min_duration, so you can control logging for ANALYZE independently of VACUUM.

(The rest of the description continues in the original commit message.)

ANALYZE vs. VACUUM Logging

ANALYZE mostly reads sampled rows, while VACUUM reads and writes table and index pages, so VACUUM often takes longer (it depends on table design, data size, stats target, workload, extended stats, etc.). We could not set the log‑duration parameters separately.

Now log_autovacuum_min_duration is VACUUM‑only, and the new log_autoanalyze_min_duration is ANALYZE‑only.
At first we proposed log_autovacuum_vacuum_min_duration and log_autovacuum_analyze_min_duration for consistency, but changing the existing name would break backward compatibility and affect pg_dump/pg_upgrade, so we stopped.

=# CREATE TABLE t (i int, d text) WITH (
  -- autoanalyze settings
  autovacuum_analyze_threshold = 1,
  autovacuum_analyze_scale_factor = 0,
  log_autoanalyze_min_duration = 0,
  -- autovacuum settings
  autovacuum_vacuum_threshold = 1,
  autovacuum_vacuum_scale_factor = 0,
  log_autovacuum_min_duration = 100_000_000
);
=# INSERT INTO t VALUES (1, 'a');
=# DELETE FROM t WHERE i = 1;

2025-12-03 15:15:39.608 JST [401368] LOG:  automatic analyze of table "postgres.public.t"
avg read rate: 18.229 MB/s, avg write rate: 0.000 MB/s
buffer usage: 155 hits, 7 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 530 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

Show compressed full‑page image size in pg_stat_wal

• Title: Add wal_fpi_bytes to pg_stat_wal and pg_stat_get_backend_wal()
• Committer: Michael Paquier
• Date: Tue, 28 Oct 2025

When wal_compression is on, full‑page images (FPI) in WAL are compressed.
Previously, measuring the effect required:

1. Running the same benchmark with compression on/off and comparing wal_bytes in pg_stat_wal. This measures all WAL, not just FPI, so the compression ratio is approximate.
2. Running pg_waldump --fullpage on the server to see compressed WAL sizes and calculate the ratio. This needs WAL files and server access, which is not feasible in many cloud environments.

The patch adds wal_fpi_bytes to pg_stat_wal:

=# SELECT * FROM pg_stat_wal;
-[ RECORD 1 ]----+-----------------------------
wal_records      | 2031667
wal_fpi          | 288581
wal_bytes        | 6346674376
wal_fpi_bytes    | 1932610356   -- new!
wal_buffers_full | 424447
stats_reset      | 2025-12-02 19:31:44.16184+09

There are also patches to expose this information in EXPLAIN (WAL) and VACUUM logs. [4][5]

Added mode and started_by columns to pg_stat_progress_vacuum view

• Title: Add mode and started_by columns to pg_stat_progress_vacuum view.
• Committer: Masahiko Sawada
• Date: Tue, 9 Dec 2025

VACUUM can be started for several reasons (auto, manual, wraparound) and can run in different modes (normal, aggressive, failsafe). The progress view previously did not expose this information.

The new columns are:

Example output

=# SELECT * FROM pg_stat_progress_vacuum;
-[ RECORD 1 ]--------+--------------
pid                  | 362895
datid                | 5
datname              | postgres
relid                | 24602
phase                | scanning heap
heap_blks_total      | 8850
heap_blks_scanned    | 5327
heap_blks_vacuumed   | 0
index_vacuum_count   | 0
max_dead_tuple_bytes| 67108864
dead_tuple_bytes    | 0
num_dead_item_ids   | 0
indexes_total       | 0
indexes_processed   | 0
delay_time          | 0
mode                | normal       -- new!
started_by          | autovacuum   -- new!

A similar patch adds a started_by column (manual, autovacuum) to pg_stat_progress_analyze. [6]

Conclusion

I introduced the PostgreSQL features I developed in 2025 and proposed several additional patches that are still under discussion. I hope to share more of them next year.

References