The latest iteration of the open-source PostgreSQL relational database system is making waves with its release today, bringing forth a suite of enhancements tailored for both seasoned developers and high-end users. As part of its regular quarterly updates, PostgreSQL introduces major features during the September/October timeframe, and this year is no exception.
Tom Kincaid, the vice president of database development at EnterpriseDB (EDB), expressed his enthusiasm for this release, highlighting its compelling nature compared to previous versions. The standout feature? Incremental backup, which Kincaid describes as a true game-changer.
Incremental Backup
With the introduction of incremental backup, only the changes made since the last backup are transferred, significantly reducing bandwidth usage and storage costs. While PostgreSQL has previously relied on third-party providers for incremental backup solutions and offered WAL Archiving, the new version presents a robust backup mechanism that minimizes operational disruption. This update also boasts dramatically improved recovery times, a crucial factor for users concerned about the traditionally slow recovery processes associated with backups.
In preliminary tests conducted by EDB, recovery times improved from an average of 78 minutes to just 4 minutes, making PostgreSQL 17 a more viable option for larger databases, particularly those handling extensive data workloads such as AI and time-series processing.
Studier Replication
Another noteworthy addition is the enhancement of replication slots, which ensures database consistency during failovers. Unlike physical replication, which mirrors the database server exactly, logical replication transmits database commands sequentially to a secondary location, which can operate on a different architecture. This flexibility is particularly beneficial for data warehouses that pull data from source databases.
In previous versions, if a primary server failed, the logical replication stream would cease, necessitating a reseeding of the replication stream and potentially resulting in data loss. However, with PostgreSQL 17, the replication stream continues seamlessly with the standby server, eliminating operational losses during failover scenarios.
Hella JSON Support
PostgreSQL 17 also enhances its support for JSON data types, allowing users to query JSON data using standard SQL statements. The newly introduced json table feature enables JSON data to be rendered into a table format, which can then be queried through SQL’s FROM clause, presenting the data in a relational view. This improvement is part of PostgreSQL’s ongoing commitment to extensibility, which has contributed to its growing popularity among developers.
Kincaid noted that the PostgreSQL core development team worked diligently to implement these JSON compatibility features, aligning with the SQL 2023 standard that specifies JSON types for query languages. This focus on JSON functionality aims to further solidify PostgreSQL’s appeal among developers.
Hardier Subtransactions
EDB has also contributed to the robustness of PostgreSQL 17 by addressing long-standing issues with subtransactions, particularly in high-transaction environments like finance. The redesign of the sub-transaction cache has resulted in a remarkable 100x performance improvement for migrations involving nested transactions, a common requirement in the financial sector.
For a comprehensive overview of all the features included in PostgreSQL 17, interested parties can refer to the release notes.
