Join our Discord Server
Adesoji Alu Adesoji brings a proven ability to apply machine learning(ML) and data science techniques to solve real-world problems. He has experience working with a variety of cloud platforms, including AWS, Azure, and Google Cloud Platform. He has a strong skills in software engineering, data science, and machine learning. He is passionate about using technology to make a positive impact on the world.

PostgreSQL 17 Released: Boosting Performance and Enhancing Developer Experience

3 min read

Postgresql 17
The PostgreSQL Global Development Group has unveiled PostgreSQL 17, the latest iteration of the world’s most advanced open-source database. Building on over 35 years of engineering excellence and a vibrant global community, PostgreSQL 17 introduces significant performance enhancements, scalability improvements, and new features tailored for both experienced developers and growing data management needs.

Why PostgreSQL 17 Stands Out

PostgreSQL 17 continues to solidify its reputation as a reliable, robust, and extensible data management system. This release delivers substantial performance gains, particularly in memory management, I/O operations, and query execution. It also enriches the developer experience with enhanced JSON support and advanced SQL capabilities. Whether you’re managing mission-critical systems or developing cutting-edge applications, PostgreSQL 17 provides the tools and optimizations to streamline your data management workflows.
Postgresql 17 Options

Key Features of PostgreSQL 17

Here are the top features that make PostgreSQL 17 a must-update for your database systems:

1. MERGE Command with RETURNING Support

Streamlining Conditional Data Modifications

The MERGE command, introduced in PostgreSQL 15, has been significantly enhanced in PostgreSQL 17 with the addition of the RETURNING clause. This feature allows developers to handle conditional data modifications more efficiently by retrieving the rows affected by the MERGE operation in a single query. This reduces the need for multiple queries and simplifies complex workflows.

Example:



CREATE TABLE hero (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  hero_name TEXT UNIQUE
);

-- Insert a new hero or update an existing one based on hero_name
MERGE INTO hero h
USING (VALUES ('Dahunsi', 'Ajeet', 'Adesoji')) v(first_name, last_name, hero_name)
ON h.hero_name = v.hero_name
WHEN MATCHED THEN
  UPDATE SET first_name = v.first_name, last_name = v.last_name
WHEN NOT MATCHED THEN
  INSERT (first_name, last_name, hero_name)
  VALUES (v.first_name, v.last_name, v.hero_name)
RETURNING merge_action(), *;
Output:


merge_action | id | first_name | last_name | hero_name
-------------|----|------------|-----------|-----------
UPDATE       | 1  | Dahunsi      | Ajeet    | Adesoji

This command either updates the existing hero’s first and last names or inserts a new hero if no match is found, all while returning the modified rows.

2. Enhanced JSON Functions

Simplifying Management of Semi-Structured Data

PostgreSQL has long been a pioneer in JSON support among relational databases, and version 17 takes it a step further by expanding its SQL/JSON functionality. The introduction of the JSON_TABLE function allows developers to convert JSON data directly into relational table format, enabling seamless SQL queries on JSON data without manual transformation.

Example:



SELECT *
FROM json_table(
'[
  {"product": "Laptop", "details": {"price": 1200, "stock": 25}},
  {"product": "Smartphone", "details": {"price": 800, "stock": 50}},
  {"product": "Tablet", "details": {"price": 500, "stock": 40}}
]',
'$[*]'
COLUMNS (
  product_name TEXT PATH '$.product',
  price INT PATH '$.details.price',
  stock INT PATH '$.details.stock'
)
) AS jt;

Output:

sql

product_name | price | stock
-------------|-------|-------
Laptop       | 1200  | 25
Smartphone   | 800   | 50
Tablet       | 500   | 40

This functionality makes it easier to handle JSON data within PostgreSQL, enhancing data interoperability and simplifying complex data manipulations.

3. Improved Performance

Accelerating Queries and Operations

PostgreSQL 17 introduces several performance optimizations that make queries faster and operational tasks more efficient. Key improvements include:
  • Vacuum Process Enhancements: A new internal memory structure reduces memory consumption by up to 20x, speeding up vacuum operations and freeing up shared resources.
  • I/O Layer Optimizations: High concurrency workloads benefit from up to 2x better write throughput due to improved write-ahead log (WAL) processing. The new streaming I/O interface accelerates sequential scans and the ANALYZE command.
  • Query Execution Boosts: Enhanced support for SIMD (Single Instruction/Multiple Data) operations, including AVX-512 for the bit_count function, and optimizations for B-tree indexes and parallel BRIN index builds speed up query execution.

Contributions by Neon Engineers

As active contributors to the PostgreSQL community, Neon engineers have made several noteworthy contributions to PostgreSQL 17, enhancing its performance and feature set:
  • EXPLAIN (SERIALIZE): Provides insights into query serialization costs for better performance tuning.
  • BRIN Parallel Index Builds: Supports parallel construction of BRIN(Block Range INdex) indexes, reducing build times on large datasets.
  • B-Tree Performance Improvements: Optimizes queries with large IN lists, enhancing performance for queries involving extensive value lists.

Logical Replication Enhancements

PostgreSQL 17 simplifies high availability and version upgrades by enhancing logical replication. Notably, users no longer need to drop logical replication slots during major version upgrades, making the process smoother. Additionally, the new pg_createsubscriber tool allows for converting physical replicas into logical replicas, providing more flexibility and resilience in replication setups.
Postgresql 17 Features

Advanced Security and Operational Features

Security and operational management have also seen significant improvements:
  • New TLS Option (sslnegotiation): Facilitates direct TLS handshakes using ALPN, improving secure connection performance.
  • pg_maintain Role: A new role for managing maintenance operations, improving role-based security management.
  • Incremental Backups and Enhanced pg_dump: Supports incremental backups, while pg_dump now includes a –filter option for selective data dumping.
    • Source code improvements: Neon Engineers contributed to various optimizations and refactorings in the Postgres codebase, enhancing the overall stability and performance of the system​—e.g. by removing AIX support and the configure options –disable-thread-safety and –with-CC. (Heikki Linnakangas) .
    • Improvements in libpq/psql: Postgres 17 also comes with various enhancements to libpq and psql. These improvements mean a more robust and feature-rich developer experience when interacting with Postgres from the command line or via applications. (Tristan Partin, Tom Lane)
    • Event Triggers and AMCheck Validation: Adds granular control over session-level operations and thorough validation checks for B-tree indexes, enhancing database integrity and reliability.
    • Postgresql 17 Security

    Get Started with PostgreSQL 17

    PostgreSQL 17 is available for download now. To explore the full list of new features and enhancements, check out the PostgreSQL 17 Release Notes.

    Useful Links:

    Conclusion

    With the release of PostgreSQL 17, database administrators, developers, and organizations gain access to powerful new features and performance enhancements. Whether it’s the MERGE command, advanced JSON support, or improved logical replication, PostgreSQL 17 delivers exceptional value, making it the database of choice for modern applications.

Have Queries? Join https://launchpass.com/collabnix

Adesoji Alu Adesoji brings a proven ability to apply machine learning(ML) and data science techniques to solve real-world problems. He has experience working with a variety of cloud platforms, including AWS, Azure, and Google Cloud Platform. He has a strong skills in software engineering, data science, and machine learning. He is passionate about using technology to make a positive impact on the world.

Leave a Reply

Join our Discord Server
Index