(MZinchenko/Shutterstock)
Postgres has been the fastest growing database in the world the past few years, as organizations look to plain vanilla data store for a variety of workloads, from transaction processing to analytics and AI. With today’s introduction of Postgres 17, Postgres users gain a handful of new capabilities, including improved vacuum functions, faster backup/restore, new JSON-table functionality, and support for sub-transactions, amid hundreds of smaller enhancements and bug fixes.
There are several enhancements 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 releases. According to the PostgreSQL Global Development Group press release on the new version, the new memory structure will speed the vacuum process while lessening the impact that critical vacuum processes have on other workloads.
Postgres is the most popular database, according to a recent Stack Overflow survey
Enhancements to the write-ahead-log (WAL) in Postgres 17 will contribute to a potential doubling of throughput performance in high-concurrency workloads, the PostgreSQL Global Development Group says, 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 various enhancements. Support for SIMD (single instruction/multiple data) will accelerate computations, including the use of AVX-512.
The folks at 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, EDB’s senior vice president of database servers and tools, incremental backup is the biggest feature in this release. “It’s odd to see database has been around for 20 plus years doesn’t have this,” Kincaid told BigDATAwire. “It’s always been done with external tools that look at the database, look at the file system. But now it’s in the core.”
The new “pg_combinebackup” utility also enables customers to create full backups out of several incremental backups, which will save large amounts of time for customers with larger databases in the multiple terabyte range, he said.
EDB developers also contributed to the improvements in sub-transaction support in Postgres 17. With certain types of transactional workloads, particularly in financial services, a single transaction may be broken up into 100 or more sub-transactions. In previous releases, the database would clow to a crawl trying to process those sub-transactions, effectively rendering the database unusable, Kincaid said.
“And so you’d have to do a lot of application changes when migrating a lot of these applications from legacy databases to migrate them to Postgres successfully,” he said. “With this [enhancement in Postgres 17], you tune the cache and you don’t have to change the application at all.”
Postgres 17 also brings various JSON enhancements, including support for SQL/JSON standard that’s part of the ISO SQL: 2023 spec. SQL/JSON will improve the ability of Postgres users to analyze data they have stored as JSON using regular mechanisms (the database has supported JSON since 2012).
“It effectively allows you to transform a JSON document into a relational table,” Kincaid said. “If you have this very complex JSON document, you run this the JSON table functionality, and now you can join it and do all sorts of your 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 complexities around JSON and JSON path. They can just continue to work in their normal language.”
Another big enhancement in Postgres 17 is an improvement in logical replication. In previous releases, when doing a major database upgrade, customers had to use logical replication slots, which required resynching 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 for high availability and make customers’ Postgres deployments more resilient. The Postgres community has also created a new command line tool for converting physical replicas into logical replicas.
“PostgreSQL 17 highlights how the global open source community, which drives the development of PostgreSQL, builds enhancements that help users at all stages of their database journey,” Jonathan Katz, a member of the PostgreSQL core team, said in the PostgreSQL Global Development Group press release. “Whether it’s improvements for operating databases at scale or new features that build on a delightful developer experience, PostgreSQL 17 will enhance your data management experience.”
Related Items:
Postgres Rolls Into 2024 with Massive Momentum. Can It Keep It Up?
EDB Puts Postgres in the Middle of Analytics Workflow with New Lakehouse Stack
Source link
lol
