(MZinchenko/Shutterstock)
PostgreSQL has emerged as the fastest-growing database globally in recent years, as companies increasingly seek out its versatility as a general-purpose data store, capable of handling a diverse array of workloads, including transactional processing, analytics, and artificial intelligence applications. As Postgres 17 is introduced in today’s era, users gain access to a multitude of innovative features, including enhanced vacuum capabilities, accelerated backup and restore processes, improved JSON table performance, support for subtransactions, and numerous minor upgrades and bug fixes.
PostgreSQL 17 introduces several enhancements designed to boost system-wide efficiency, including a novel internal memory structure for the vacuum process that can conserve up to 20 times more memory compared to previous releases. The latest iteration of our innovative model introduces a cutting-edge memory architecture, designed to accelerate the vacuum process while minimizing its impact on other workload dependencies, ensuring seamless operation and enhanced overall performance.
The write-ahead log (WAL) enhancements in Postgres 17 are expected to significantly boost throughput efficiency in high-concurrency workloads by potentially doubling performance, according to the PostgreSQL International Growth Group, with benefits also extending to SQL queries that utilize “IN” clauses and B-tree indexes, which can expect faster results. Parallel builds are facilitated by BRIN (block range index) indexes, while a question planner perceives diverse improvements. Using assistive technologies like SIMD and leveraging the capabilities of AVX-512 will significantly accelerate computational processes.
The developers of model 17 drew upon their collective expertise to introduce several innovative features, including support for block-level incremental backups, which significantly accelerates backup and recovery processes. In one remarkable instance, a Postgres backup that previously required a whopping 70 minutes to complete was astonishingly reduced to just 4 minutes – an impressive 95% decrease.
As Tom Kincaid, EDB’s senior VP of Database Servers and Instruments, emphasizes, the incremental backup feature takes center stage in this latest release. “It’s surprising that a technology like databases, which has been around for over two decades, still lacks this fundamental feature,” Kincaid suggested. Intrusions have traditionally utilized external tools to access databases and file systems directly. Now at its very essence.
The newly introduced “pg_combinebackup” utility empowers customers to seamlessly generate comprehensive backups from multiple incremental backups, thereby significantly reducing the time required for clients with large databases in the multi-terabyte range, as stated.
The EDB team’s contributions also played a key role in enhancing sub-transaction support in Postgres 17. In certain types of transactional workloads, particularly within the financial sector, a solitary transaction can often be decomposed into 100 or more individual sub-transactions. Earlier versions of the system struggled under the burden of processing numerous sub-transactions, causing the database to grind to a halt and become effectively unusable, as noted by Kincaid.
“When migrating large datasets from legacy databases to PostgreSQL, significant software modifications are typically required for efficient data transfer.” “With PostgreSQL 17’s enhancement, tuning the cache is simplified, eliminating the need for any changes to your existing infrastructure.”
PostgreSQL 17 introduces several JSON enhancements, including support for the SQL/JSON standard, which is part of the ISO SQL:2023 specification. SQL/JSON enables Postgres customers to leverage their stored JSON data with ease, unlocking a new dimension of queryability and flexibility since its introduction in 2012.
“It enables users to seamlessly convert JSON documents into relational tables,” said Kincaid. “When working with an extremely sophisticated JSON document, running it through the JSON data store enables seamless integration with traditional SQL functionality, allowing for the execution of complex queries and common table expressions.” For database builders who frequently work with SQL, delving into the intricacies of JSON and JSONPath may not be essential. They will then continue with their usual linguistic routine.
In a significant upgrade to Postgres 17, there’s been a substantial improvement made to logical replication. Prior to recent upgrades, major database enhancements necessitated the employment of logical replication slots, thereby demanding resynchronization efforts from clients. With the introduction of PostgreSQL model 17, logical replication slots are no longer necessary, thereby streamlining primary database upgrades and reducing complexity.
With the introduction of a brand-new failover management system on this launch, we’re elevating the use of logical replication to ensure prime availability, thereby making our clients’ Postgres deployments more robust and resilient in the face of potential outages. The Postgres group has introduced a novel command-line tool for converting physical replicas to logical replicas.
“According to Jonathan Katz, a key contributor to the PostgreSQL community, PostgreSQL 17 exemplifies the collective efforts of the global open-source organization, providing innovative features that cater to customers’ needs at every stage of their database development.” Whether PostgreSQL 17 is enhancing database scalability or introducing features that build upon a seamless developer experience, it undoubtedly elevates the art of data management.
