PostgreSQL 17� What's new?

Aychin Gasimov

Senior Partner SA EMEA DATA & AI

AWS

PostgreSQL release cycle��

• A new major version containing new features about once a year
• Each major version supported for 5 years after its initial release.
• Bug fixes, security patches once every three month (minor release)
• For minor releases, the community considers not upgrading to be riskier than upgrading.

​

Performance

• Vacuum process now uses up to 20x less memory
• Removed 1GB limit for autovacuum_work_mem
• Efficient memory allocation with ART (Adaptive Radix Tree)

VACUUM performance and optimization

1. Heap scan

2. Index cleanup

3. Heap cleanup

Memory array limited to 1GB

autovacuum_work_mem

Before

Tuple IDs

[prune, then freeze]

1. Heap scan

2. Index cleanup

3. Heap cleanup

TIDStore implemented using Adaptive Radix Tree (ART)

autovacuum_work_mem

PostgreSQL 17

~20x less mem

[freeze and prune]

• Combine freezing and pruning steps
• Optimize vacuuming of relations with no indexes
• Significantly reduces WAL volume and efficiency

VACUUM performance and optimization

1. Heap scan – Pruning – Marking items as LP_DEAD

2. Heap cleanup – Cleaning items and mark as LP_UNUSED

1. Heap scan – Pruning – Marking items as LP_UNUSED

2. Heap cleanup – Cleaning items no update of line pointers (We save one WAL generation here)

Old vacuum approach

PostgreSQL 17

• Update to

pg_stat_progress_vacuum

VACUUM monitoring

Before

PostgreSQL 17

• New Streaming API
• Access relation data as a stream of buffers.
• Multiple blocks read with one system call.
• New GUC io_combine_limit, default value: 128KB.
• Heap sequential scans and TID range scans use the streaming API.
• Benefits
• Improved efficiency and performance.
• Reduced system calls.
• Future-proof for asynchronous I/O.

Streaming I/O Interface

Speeds up sequential scans and ANALYZE operations.

• Optimize WAL insertion lock acquisition/release with 64b Atomic Variables
• Reduced Contention: Less contention on LWLock wait list lock
• Improved Performance: Higher TPS in highly concurrent write workloads

​

Improved Write Throughput

High concurrency workloads see up to 2x better write throughput.

• Parallel BRIN Index Builds: BRIN indexes now support parallel builds
• Query Execution Optimizations: Improved performance for queries with IN clauses using B-tree indexes.

SELECT * FROM products WHERE product_id IN (101, 102, 103, 104);

​

• Using AVX-512 instructions for the bit counting function

​

Other performance improvements

Other improvements and new features

summarize_wal = on

Incremental backups

$PGDATA/pg_wal/summaries

walsummarizer

Background process

• Tracks modified blocks between two LSNs
• Can be enabled on primary or standby
• wal_level > minimal
• wal_summary_keep_time default 10 days

Incremental backups

pg_basebackup –D Sunday

Sunday

Monday

Tuesday

Sunday

Monday

Tuesday

WALs

t

Restore Point

WALs

pg_basebackup –D Monday --incremental=Sunday/backup_manifest

pg_basebackup –D Tuesday --incremental=Monday/backup_manifest

pg_combinebackup Sunday/ Monday/ Tuesday/ -o Combined/

Logical replication

• pg_createsubscriber utility to create a logical replica from a physical standby server
• Works on database level

Physical

replication

Primary

Standby

Logical

replication

Publisher

Subscriber

pg_createsubscriber

• Have pg_upgrade migrate valid logical slots and subscriptions

Logical replication

• Enable the failover of logical slots on standby: sync_replication_slots to enable failover (default false)
• Add logical replication failover control to CREATE/ALTER SUBSCRIPTION
• Default is false for failover option

CREATE SUBSCRIPTION my_subscription

CONNECTION 'host=primary_server dbname=mydb user=myuser password=mypassword'

PUBLICATION my_publication

WITH (

failover = true

);

ON LOGIN trigger

• New ON LOGIN event trigger
• New system variable event_triggers to disable all event triggers

CREATE FUNCTION on_login() RETURNS event_trigger

AS $$

BEGIN

-- Setting up some session context

END;

$$ LANGUAGE plpgsql;

​

CREATE EVENT TRIGGER on_login ON LOGIN EXECUTE FUNCTION on_login();

MAINTAN permission

• Granting MAINTAIN permission to particular object allows:
• ANALYZE
• CLUSTER
• REFRESH MATERIALIZED VIEW
• REINDEX
• VACUUM
• Alternatively grant pg_maintain role to maintain all objects

GRANT MAINTAIN ON sales.orders TO scott;

​

GRANT pg_maintain TO scott;

Safe search_path

• Following operations will use safe search_path = “pg_catalog, pg_temp”
• ANALYZE
• CLUSTER
• REFRESH MATERIALIZED VIEW
• REINDEX
• VACUUM
• Functions used by expression indexes and materialized views that need to reference non-default schemas must now specify a search_path during their creation.

CREATE FUNCTION my_function() RETURNS void AS $$

BEGIN

-- Function logic here

END;

$$ LANGUAGE plpgsql SET search_path TO myschema, public;

• ON_ERROR ignore: Discards error rows during COPY operation
• Default behavior: ON_ERROR stop
• LOG_VERBOSITY: Reports ignored error rows during COPY FROM
• Options: ‘default’ and ‘verbose’
• FORCE_NULL and FORCE_NOT_NULL: Easy specification for all columns
• Skipped Rows Reporting: Reports number of skipped rows during COPY FROM
• Appears in pg_stat_progress_copy.tuples_skipped

COPY command enhancements

New COPY options in PostgreSQL 17

COPY command enhancements

New COPY options in PostgreSQL 17

CREATE TABLE my_table (item_id int PRIMARY KEY);

​

COPY my_table FROM stdin (on_error ‘igrone’, log_verbosity verbose);

>>> 10

>>> xyz

>>> abc

>>> 40

>>> \.

NOTICE: skipping row due to data type incompatibility at line 2 for column item_id: "xyz"

NOTICE: skipping row due to data type incompatibility at line 3 for column item_id: "abc“

NOTICE: 2 rows were skipped due to data type incompatibility

COPY 2

​

SELECT * FROM my_table;

item_id

---------

10

40

(2 rows)

JSON_TABLE

Present JSON as a table

CREATE TABLE my_films ( js jsonb );

​

INSERT INTO my_films VALUES (

'{ "favorites" : [

{ "kind" : "comedy", "films" : [

{ "title" : "Bananas",

"director" : "Woody Allen"},

{ "title" : "The Dinner",

"director" : "Francis" } ] },

{ "kind" : "horror", "films" : [

{ "title" : "Psycho",

"director" : "Alfred Hitchcock" } ] },

{ "kind" : "thriller", "films" : [

{ "title" : "Vertigo",

"director" : "Alfred Hitchcock" } ] },

{ "kind" : "drama", "films" : [

{ "title" : "Yojimbo",

"director" : "Akira Kurosawa" } ] }

] }');

​

​

SELECT jt.* FROM

my_films,

JSON_TABLE (js, '$.favorites[*]' COLUMNS (

id FOR ORDINALITY,

kind text PATH '$.kind',

title text PATH '$.films[*].title' WITH WRAPPER,

director text PATH '$.films[*].director' WITH WRAPPER)) AS jt;

​

id | kind | title | director

----+----------+---------------------------+-----------------------------

1 | comedy | ["Bananas", "The Dinner"] | ["Woody Allen", "Francis"]

2 | horror | ["Psycho"] | ["Alfred Hitchcock"]

3 | thriller | ["Vertigo"] | ["Alfred Hitchcock"]

4 | drama | ["Yojimbo"] | ["Akira Kurosawa"]

(4 rows)

​

​

MERGE command enhancements

Now can do action if row exists in target but not in source (NOT MATCHED BY SOURCE)

​

MERGE INTO wines target

USING new_wine_list source

ON s.winename = target.winename

WHEN MATCHED AND target.stock != source.stock THEN

UPDATE SET stock = source.stock

WHEN NOT MATCHED BY TARGET THEN

INSERT VALUES(source.winename, source.stock)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

​

MATCHED

source

target

NOT MATHED BY TARGET

NOT MATHED BY SOURCE

WHEN NOT MATCHED BY SOURCE THEN DELETE | UPDATE | DO NOTHING

MERGE command enhancements

New RETURNING clause

​

MERGE INTO wines target

USING new_wine_list source

ON s.winename = target.winename

WHEN MATCHED AND target.stock != source.stock THEN

UPDATE SET stock = source.stock

WHEN NOT MATCHED BY TARGET THEN

INSERT VALUES(source.winename, source.stock)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

RETURNING merge_action(), target.*;

​

​

merge_action | id | winename | stock

-------------+----+------------+-------

UPDATE | 1 | Merlot | 10

UPDATE | 2 | Chardonnay | 2

DELETE | 3 | Pinot Noir | 10

INSERT | 4 | Riesling | 31

​

Thank You!