(MZinchenko/Shutterstock)
Postgres has been the fastest-growing database in the world in recent years, as organizations seek a simple data warehouse for a variety of workloads, from transaction processing to analytics and artificial intelligence. With today’s introduction of Postgres 17, Postgres users get a handful of new capabilities, including improved vacuum features, faster backup/restore, new JSON table functionality, and support for subtransactions, among hundreds of improvements smaller and bug fixes.
There are several improvements in Postgres17 that will bring system-wide performance improvements, including the new internal memory structure for the vacuum process, which consumes up to 20 times less memory than previous versions. According to the PostgreSQL Global Development Group Press Release In the new version, the new memory structure will speed up the vacuum process while reducing the impact that critical vacuum processes have on other workloads.
Postgres is the most popular database, according to a recent Stack Overflow survey
Improvements to write-ahead logging (WAL) in Postgres 17 will contribute to a potential doubling of performance in high-concurrency workloads, says the PostgreSQL Global Development Group, while standard SQL queries that use “IN” clauses and B tree indexes will see faster results. BRIN (block range index) indexes will support parallel builds, while the query planner sees several improvements. SIMD (single instruction/multiple data) support will speed up calculations, including the use of AVX-512.
the people of EnterpriseDB contributed to several of the new features in version 17, including support for block-level incremental backups, which should significantly speed up backups and recoveries. In one case, a Postgres backup that previously took 70 minutes was reduced to just four minutes, a 95% reduction.
According to Tom Kincaid, senior vice president of database tools and servers at EDB, incremental backup is the most important feature of this release. “It’s strange to see a database that’s been around for more than 20 years not have this,” Kincaid said. BigDATAwire. “It has always been done with external tools that analyze the database and the file system. But now it is at the core.”
The new “pg_combinebackup” utility also allows customers to create full backups from multiple incremental backups, which will save huge amounts of time for customers with larger databases in the multi-terabyte range, he said.
EDB developers also contributed to improvements in subtransaction support in Postgres 17. With certain types of transactional workloads, particularly in financial services, a single transaction can be split into 100 or more subtransactions. In previous versions, the database would slow down trying to process those subtransactions, effectively rendering the database unusable, Kincaid said.
“Therefore, you would have to make a lot of application changes when migrating many of these applications from legacy databases to successfully migrate them to Postgres,” he said. “With this (improvement in Postgres 17), you adjust the cache and you don’t have to change the application at all.”
Postgres 17 also brings several JSON improvements, including support for the SQL/JSON standard that is part of the ISO SQL:2023 specification. SQL/JSON will improve the ability of Postgres users to parse data they have stored as JSON using mechanisms common (the database supports JSON since 2012).
“It allows you to effectively transform a JSON document into a relational table,” Kincaid said. “If you have this very complex JSON document, you run the JSON table functionality and now you can join in and do all kinds of standard SQL and run CTEs (common table expressions) on it. Database developers who do a lot with SQL don’t have to learn all the intricacies related to JSON and the JSON path. “They can continue working in their usual language.”
Another big improvement in Postgres 17 is an improvement to logical replication. In previous versions, when performing a major database upgrade, clients had to use logical replication slots, which required resynchronizing data. With version 17, logical replication slots are no longer required, simplifying major database upgrades.
A new failover control in this release will improve the use of logical replication to achieve high availability and make customers’ Postgres deployments more resilient. The Postgres community has also created a new command-line tool to convert physical replicas to logical replicas.
“PostgreSQL 17 highlights how the global open source community, which drives PostgreSQL development, creates enhancements that help users at every stage of their database journey,” said Jonathan Katz, member of the PostgreSQL core team. , in the PostgreSQL Global Development Group. Press release. “Whether it’s improvements to operating databases at scale or new features that build on a developer-friendly experience, PostgreSQL 17 will improve your data management experience.”
Related articles:
Postgres enters 2024 with enormous momentum. Can he continue like this?
EDB Puts Postgres in the Middle of the Analytics Workflow with New Lakehouse Stack
