postgres 17 - ghdrako/doc_snipets GitHub Wiki

Improvments:

logical replication pg_createsubscriber

allows users to create a new logical replica from a physical standby server. The main advantage of this tool over a common logical replication setup is the initial data copy, which can take longer on large databases and have side effects, like autovacuum issues, due to the long-running transaction to copy data from one server to another. This tool will also help reduce the catchup phase.

Support for MERGE PARTITIONS and SPLIT PARTITIONS

Two new commands: MERGE PARTITIONS and SPLIT PARTITIONS merge or split several partitions. Limitation: It works as a single process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all operations. This is why the new DDL commands are not advisable for large partitioned tables under a high load.

Add support for incremental file system backup

Backup capabilities are expanded, with pg_basebackup supporting incremental backups and the new pg_combinebackup utility enabling full backup reconstruction. A new background worker named summarize_wal needs to be enabled to collect WAL.

Enable the failover of logical slots

Enabling the failover of logical replication slots in PostgreSQL enhances the robustness and reliability of logical replication setups by allowing logical slots to be transferred and maintained across different database instances.

Vacuum improvements

Improvements is the new memory management system for vacuum processes, which reduces memory usage by up to 20 times, accelerating operations and freeing resources

  • Faster index cleanup and reduce its memory usage - This is achieved by changing the mechanism to remove dead tuples during vacuum to use TID store to collect dead tuples from the heap and use them to clean up the index

  • Reduced WAL sync and write time

  • The freezing and pruning steps in the vacuum process has been combined to produce a single WAL record

  • Reduced WAL volume

  • Vacuuming of relations with no indexes are optimized by reducing the steps by marking them LP_UNUSED directly

  • High-concurrency workloads also benefit from up to twice the write throughput, thanks to optimisations in write-ahead log (WAL) processing. *

  • PostgreSQL 17 introduces a streaming I/O interface, speeding up sequential scans and ANALYZE operations.

  • Expanded JSON capabilities, including the new JSON_TABLE function, which converts JSON data into relational tables.

  • Adds support for parallel BRIN index builds and SIMD (single instruction/multipledata) to boost computational performance.

  • The update enhances bulk data exports, with the COPY command offering up to double the speed for large exports. A new ON_ERROR option allows imports to continue despite insert errors, streamlining data migration

  • Introduces a new TLS option (sslnegotiation) for direct handshakes using ALPN, while the pg_maintain role grants users maintenance permissions without full superuser access.

  • Monitoring tools are bolstered with EXPLAIN now tracking local I/O times.

not null optimization

select version();
create table t ( a int not null );
insert into t select * from generate_series(1,1000000);
analyze t;

-- 16
explain select * from t where a is not null;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
   Filter: (a IS NOT NULL)

-- 17
postgres=# explain select * from t where a is not null;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
(1 row)


--16 -do do a parallel sequential scan over the whole table
postgres=# explain select * from t where a is null;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Gather  (cost=1000.00..9591.77 rows=1 width=4)
   Workers Planned: 2
   ->  Parallel Seq Scan on t  (cost=0.00..8591.67 rows=1 width=4)
         Filter: (a IS NULL)

--17 - do not
postgres=# explain select * from t where a is null;
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false