PostgreSQL 17
PostgreSQL 17 release date
The most recent iteration of the most sophisticated open source database in the world, PostgreSQL 17, was released on 29 September 2024, according to a statement from the PostgreSQL Global Development Group.
Building on decades of open source development, PostgreSQL 17 enhances performance and scalability while adjusting to new patterns in data storage and access. Significant overall performance improvements are included in this PostgreSQL release, including improved query execution for indexes, storage access optimizations and enhancements for high concurrency workloads, a redesigned memory management implementation for vacuum, and speedups in bulk loading and exports. New workloads and essential systems alike can benefit from PostgreSQL 17’s features, which include improvements to the developer experience with the SQL/JSON JSON_TABLE command and logical replication changes that make managing high availability workloads and major version upgrades easier.
PostgreSQL 17 features
PostgreSQL 17’s latest features are now accessible via Cloud SQL.
Google Cloud is announcing that PostgreSQL 17 is now supported in Cloud SQL, with a ton of new features and beneficial improvements in five important areas:
- Safety
- Experience as a developer
- Performance
- Equipment
- Observability
We go into great detail about these topics in this blog article, offering helpful advice and real-world examples to help you get the most of PostgreSQL 17 on Cloud SQL
Increased protection
PG_maintain role and MAINTAIN privilege
The MAINTAIN permission, introduced in PostgreSQL 17, enables you to conduct maintenance operations on database objects, including VACUUM, ANALYZE, REINDEX, and CLUSTER, even if you are not the object’s owner. This gives you more precise control over database upkeep duties.
Additionally, PostgreSQL 17 adds a predefined role called pg_maintain that enables you to do maintenance actions on all relations without explicitly having MAINTAIN privileges on those objects.
Improvements to the developer experience
MERGE… COMING BACK
A potent addition to PostgreSQL 17, the MERGE command enables programmers to carry out conditional updates, inserts, or deletions in a single SQL expression. By using fewer distinct queries, this command not only makes data handling easier but also boosts performance.
Create a regular PostgreSQL table from JSON input
PostgreSQL 17’s JSON_TABLE function makes working with JSON data easier by introducing a more user-friendly method of converting it into a regular table format. For converting JSON documents into tabular form, JSON_TABLE provides a simpler and more standardized mechanism than previous approaches like json_to_recordset(), which can be difficult to use.
Enhancements in performance
Better vacuum memory structure
To store tuple IDs during VACUUM operations, PostgreSQL 17 provides TidStore, a new and more effective data structure. This greatly lowers the amount of memory used by replacing the earlier array-based method. By using this method, the 1GB memory consumption limit while vacuuming the table is also removed.
A few new columns have been added to the pg_stat_progress_vacuum system view to offer more information on the vacuum process, and the names of a few existing columns have been modified.
Enhanced I/O efficiency
Multiple consecutive blocks can now be read from disk into shared buffers with a single system call with an improvement made to the ReadBuffer API in PostgreSQL 17.
Because it lowers the overhead associated with many separate read operations, this innovation is especially helpful for workloads that require reading numerous consecutive blocks. Additionally, it facilitates the rapid update of planner statistics in the ANALYZE process.
To manage your maximum I/O size for operations that combine I/O, PostgreSQL 17 also adds io_combine_limit. By default, 128kB is used.
Better handling of IS [NOT] NULL
Optimizations are introduced in PostgreSQL 17 to minimize the needless evaluation of IS NULL and IS NOT NULL clauses. By eliminating redundant tests, this modification improves query efficiency and speeds up operations, particularly in complex queries or when many conditions include NULL values.
Because of this, PostgreSQL doesn’t actually need to examine the table data because it can instantly ascertain that the criterion “id IS NULL” will never be true. Since all entries in the id column are assured to be non-null, the One-Time Filter: false implies that the criteria “id IS NULL” is not satisfied for any rows.
Enhancements to the tooling
Better Verbosity Control and COPY Error Handling
With features like ON_ERROR and LOG_VERBOSITY, PostgreSQL 17 enhances the COPY command. During data import processes, these parameters enable you to better understand skipped rows and handle problems more gently.
cat sample_data.csv
1,John,30
2,Mary,abc
3,Sam,25
4,Amy,35
Since age (an integer) is the third column in this case, “abc” is an invalid data type for integers.
Import the data with below command
postgres=> \COPY test_copy from sample_data.csv (ON_ERROR ignore, LOG_VERBOSITY verbose , format csv);
NOTICE: skipping row due to data type incompatibility at line 2 for column age: “abc “
NOTICE: 1 row was skipped due to data type incompatibility
COPY 3
Additionally, PostgreSQL 17 adds a new column to the pg_stat_progress_copy view called tuples_skipped. This field indicates how many tuples were missed due to the presence of faulty data.
pg_dump, pg_dumpall, and pg_restore using the option –filter
A more precise control over which items are included or excluded in a dump or restoration process is made possible by PostgreSQL 17’s –filter option.
Using the –transaction-size option in pg_restore
With the addition of the –transaction-size option to the pg_restore command, you can commit after processing a predetermined number of items. For more manageable transactions, you can divide the restore operation into smaller sets of objects by using the –transaction-size option.
Improved observability
pg_wait_events system view
Information about events that are making processes wait is available through the new pg_wait_events system view. Finding performance bottlenecks and resolving database problems can both benefit from this.
A basic query ought to resemble this:
SELECT * FROM pg_wait_events LIMIT 5;
You may learn a lot about your PostgreSQL database’s performance and pinpoint areas for development by combining pg_wait_events with pg_stat_activity.
postgres=> select
psa.datname,
psa.usename,
psa.state,
psa.wait_event_type,
psa.wait_event,
psa.query,
we.description
from
pg_stat_activity psa
join pg_wait_events we on psa.wait_event_type = we.type
and psa.wait_event = we.name;
This is an example of the output from the aforementioned command, which contains shortened rows for relevance and projected columns:
|wait_event_type | query | description
+—————-+————————————-+—————————-
| Client | update pgbench_accounts set abalance=300; | Waiting to read data from the client
| IPC | checkpoint; | Waiting for a checkpoint to start
. . .
pg_stat_checkpointer system view
The pg_stat_checkpointer system view gives useful details about the activity and performance of the checkpoint process, including how often checkpoints occur, how much data is written during checkpoints, and how long it takes to finish checkpoints.
Run the following query to get insights on checkpointer activity:
SELECT * FROM pg_stat_checkpointer;
By returning a record with a variety of checkpoint process metrics, this query enables you to track and evaluate the effectiveness of checkpoints within the PostgreSQL instance.
In brief
In conclusion, Cloud SQL for PostgreSQL 17 offers notable improvements in tooling, observability, performance, security, and developer experience. The purpose of these improvements is to increase database management capabilities and optimize database operations. For a comprehensive list of new features and specific details, consult the official release notes.
To take advantage of these potent updates, it urges you to test out Cloud SQL PostgreSQL 17 right now.
