PostgreSQL 17 changes the work around recovery and replication

Reducing a backup's size changes what must be retained to restore it. PostgreSQL 17, released on September 26, adds incremental physical backups through pg_basebackup and a companion utility, pg_combinebackup, for reconstructing a full backup. The useful change is smaller backup inputs when much of a large database remains unchanged. The corresponding obligation is to preserve the earlier backups that supply the omitted blocks.

That is part of a broader operational theme in the release. Logical replication gains support that reduces resynchronization during future upgrades and improves failover control. Vacuum changes its internal memory representation. These additions concern work around the database's ordinary queries, where an implementation choice can determine whether recovery or maintenance remains manageable as the database grows.

I default to PostgreSQL for relational systems because its operational behavior is usually explicit enough to reason about. The incremental backup implementation fits that preference. The documentation states what the new files omit, what reconstruction needs and which responsibilities remain with the operator.

Incremental backups describe changed relation blocks

The backup documentation in the PostgreSQL 17.0 source describes pg_basebackup --incremental as taking the manifest of an earlier backup from the same server. Non-relation files are included in full. Some relation files can instead become incremental files containing changed blocks and enough metadata to reconstruct the current file.

The server identifies changed blocks through WAL summaries stored under pg_wal/summaries. Those summaries must cover the interval from the start position of the earlier backup to the start position of the new backup. This is a server-maintained account of changed blocks, rather than a comparison performed by reading every byte of an older backup at the destination.

Summary availability determines whether an incremental backup can proceed. The server can wait for summarization to catch up after it establishes the new backup's start position. If required summaries have already been removed, or the summarizer does not catch up in time, the operation fails. Backup retention and WAL-summary availability therefore describe different dependencies in the process.

The documentation identifies a workload where the feature is useful. Large databases with substantial unchanged or slowly changing data can avoid copying those relation blocks repeatedly. Small databases may be simpler to manage with full backups. A large database that changes heavily may produce incremental backups that are not much smaller than full ones. The feature follows the volume of changed blocks, not the database's nominal size alone.

An incremental taken on a standby has another condition. Replay must begin from a later checkpoint than the backup on which it depends. A primary backup triggers a new checkpoint, while a standby relies on restartpoints. With very little activity between standby backups, there may be no newer restartpoint, and an incremental attempt can fail. That behavior follows from the documented backup starting positions.

Reconstruction needs an ordered chain

The 17.0 pg_combinebackup documentation requires input backup directories in order, oldest first. The first is a full backup and the final one is the incremental backup to reconstruct. Its output directory receives a synthetic full backup containing the reconstructed files.

A simple example shows the relationship among a full backup and two dependent incrementals. These names stand for existing backup directories in the correct chain.

pg_combinebackup -o /restore/full \
  /backups/base /backups/incremental-one /backups/incremental-two

The utility attempts to verify that the inputs form a valid chain. It does not maintain an inventory of which retained backups depend on which earlier backups. Removing a necessary predecessor makes the dependent incremental unrestorable, even if its own files remain present. Retention consequently needs to preserve dependencies rather than simply keep the newest directory.

The utility also offers a dry-run mode. It determines the planned work without creating the output directory or files, which separates inspecting the proposed reconstruction from writing the synthetic full backup.

Chain validation also differs from checking each backup's integrity. The documentation directs users to pg_verifybackup for the latter. A set of backups can have the right relationship while one of its inputs is damaged. The two checks answer different questions, so successful reconstruction planning alone is not evidence that every input file is intact.

The synthetic full output can become the starting input for another reconstruction. This allows a reconstructed backup to replace its original chain as an input to the utility. The operator still needs to keep track of the dependency relationship used for subsequent incrementals. Producing a synthetic full does not turn the utility into a retention manager.

Recovery requirements continue beyond combining files. The backup documentation says the ordinary requirements for a full physical backup also apply, including required WAL segment and history files and the recovery procedure. Combining incrementals supplies the reconstructed backup directory. It does not eliminate WAL replay or replace the process that brings the restored database into its intended state.

Replication continuity starts with a PostgreSQL 17 source

The release announcement says logical replication slots can survive major-version upgrades starting with upgrades from PostgreSQL 17. This direction matters. The feature does not promise that an upgrade into 17 from every older version preserves existing slots. It establishes support for a source already on the new major version.

Previously, the announced upgrade limitation required dropping logical replication slots and resynchronizing subscribers afterward. Retaining that state removes a specific source of repeated work in the supported upgrade path. It is relevant to a database whose subscribers contain substantial data, since resynchronization is part of the overall upgrade cost even when the primary server's own transition is acceptable.

PostgreSQL 17 also adds failover control for logical replication. The release presents it as an improvement for high-availability deployments. This is a distinct concern from preserving slots through an upgrade. A planned major-version transition and a primary failover change the environment in different ways, even when both require maintaining useful replication state.

The new pg_createsubscriber utility converts a physical replica into a logical replica. That provides another explicit transition between replication arrangements. It should not be conflated with the backup reconstruction utility, which combines physical backup files. The release adds tools for different state transitions, and their inputs identify which transition each one performs.

I find the upgrade direction particularly important when evaluating these additions. A statement that PostgreSQL 17 preserves logical slots during upgrades can sound broader than the documented claim. Recording that the source must already be on 17 preserves the condition that determines whether the new behavior applies.

JSON_TABLE exposes documents as relational rows

The release also adds JSON_TABLE, which addresses a different boundary. The function documentation in the release source defines it as querying JSON and presenting the result as a relational view. A JSON path selects the row pattern, and a COLUMNS clause specifies typed values extracted from each match.

The following example extracts two values from an array of objects. The JSON document is an input expression, and the output columns have ordinary SQL types.

SELECT j.item_id, j.label
FROM JSON_TABLE(
    '[{"id":1,"label":"alpha"},{"id":2,"label":"beta"}]'::jsonb,
    '$[*]' COLUMNS (
        item_id integer PATH '$.id',
        label text PATH '$.label'
    )
) AS j;

The function's row pattern determines how many input matches produce rows. The column paths then evaluate within each match. That separates choosing records from choosing fields, and makes the output available to surrounding relational SQL. It is a query-time representation, not a command that creates and populates a persistent table.

Nested paths can produce child rows, with nested column definitions for deeper document structures. The documentation says sibling nested paths combine their rows using a union after joining with their parent. That behavior deserves attention when predicting row counts. Nested document extraction does not imply a Cartesian multiplication of every sibling array.

Maintenance memory changes alongside the new interfaces

PostgreSQL 17's vacuum implementation uses a new internal memory structure. The release announcement reports up to twenty times less memory use, with improvements to vacuum speed and shared resource use. That is an upper-end reported improvement, not a universal ratio for every database. The mechanism is a representation change within maintenance, so applications can benefit without adopting new SQL syntax.

The release also adds monitoring for vacuum's index work and a pg_wait_events view that can be combined with session activity. These help expose operations that occur around application queries. Incremental backups make the same operational requirements more explicit in another area, where each retained backup must remain connected to the files needed to reconstruct it and the WAL needed for recovery.