Category: All posts
Dec 12, 2024
The next major PostgreSQL release (PostgreSQL 17) is scheduled for September.
In 2023, PostgreSQL regained the attention it deserves as a rock-solid relational database. It was voted the most popular DB in the Stack Overflow Developer Survey and named database management system of the year by DB-Engines. Here at Timescale, we also consolidated our status as fierce PostgreSQL fans: besides having built Timescale on PostgreSQL, we believe PostgreSQL is evolving as a platform and becoming the bedrock for the future of data. So, excuse us for being a bit excited about PostgreSQL 17.
In its latest releases, we’ve watched PostgreSQL develop toward higher performance, scalability, security, and compatibility while introducing new features to meet the evolving needs of users and applications, especially enterprise ones. The improvements to privilege administration, logical replication, and monitoring are examples of that. More importantly, during this time, we contributed, managed commitfests, and created new features and products to expand it—from boosting real-time aggregation by 50,000 % to powering production AI applications.
In this blog post, we gathered Timescale contributors and enthusiasts to discuss a few of the most exciting PostgreSQL 17 commits. As we count the days until September, we’ll also examine PostgreSQL’s direction for this release. Finally, we’ll share some of our commits, as we help build up PostgreSQL as a versatile development platform for everything.
Looking at the several PostgreSQL 17 commits, Aleksander Alekseev, long-time PostgreSQL contributor and Timescaler, says significant changes to modernize PostgreSQL are underway. “I believe the future of Postgres is bright,” he notes, adding that “new people are joining the project.” Perhaps influenced by the new wave of contributors, the changes to PostgreSQL 17 reflect the project’s commitment to embracing modern methodologies and adapting to the ever-evolving tech landscape
One such notable change in version 17, says Aleksander, is the decision to drop support for AIX, an operating system developed by IBM. AIX, while historically significant, has seen declining usage in recent years, prompting PostgreSQL to reallocate resources towards supporting more widely adopted platforms. This strategic move enables PostgreSQL to focus on enhancing compatibility with modern operating systems.
While they may seem more focused today, the PostgreSQL community's efforts to make PostgreSQL a solid database for modern data needs were already visible in previous versions, including the current one, PostgreSQL 16. As a specific example, Aleksander mentions the transition from Autotools to the Meson build system. Autotools, a long-standing suite of tools for configuring, building, and installing software packages, has been a stalwart in the development process of PostgreSQL.
However, with the advent of Meson, a contemporary build system known for its simplicity, speed, and scalability, PostgreSQL managed to streamline its development workflows. Meson offers advantages such as improved performance, easier maintenance, and better cross-platform compatibility, which PostgreSQL currently extends to its users.
Now that we’ve seen where PostgreSQL 17 is headed, let’s discuss some of the commits that have caught our 👀.
Suggested by Timescaler and PostgreSQL contributor Fabrízio de Mello, pg_createsubscriber is a new PostgreSQL 17 tool that allows users to create a new logical replica from a physical standby server. “The main advantage of this tool over a common logical replication setup is the initial data copy, which can take longer on large databases and have side effects, like autovacuum issues, due to the long-running transaction to copy data from one server to another. This tool will also help reduce the catchup phase,” explains Fabrízio.
While ALTER TABLE is a well-known statement that changes the structure of a PostgreSQL table, PostgreSQL 17 comes along with two new commands: MERGE PARTITIONS and SPLIT PARTITIONS. As the name indicates, these new DDL commands merge or split several partitions. “The current implementation has certain limitations though,” says Aleksander. “It works as a single process and holds the ACCESS EXCLUSIVE LOCK on the parent table during all operations. This is why the new DDL commands are not advisable for large partitioned tables under a high load,” he adds.
“This is another feature worth mentioning,” says Aleksander. Adding support for incremental file system backup in PostgreSQL enhances the database's ability to perform efficient and effective backups. Incremental backups only save changes made since the last backup (full or incremental). This significantly reduces the volume of data to be backed up compared to full backups, which capture the entire database. And since incremental backups involve less data, the backup process is faster, minimizing the impact on system performance and reducing downtime.
Developed by Robert Haas, Jakub Wartak, and Tomas Vondra, this commit has been struggling with stability issues, as explained by Robert on his blog. “Hopefully it won’t be reverted (as many other commits this month),” comments Aleksander.
Picked by two Timescalers, Fabrízio and our head of Developer Advocacy, James Blackwood-Sewell, this commit by Hou Zhijie, Shveta Malik, and Ajin Cherian lets high-availability PostgreSQL use logical replication and not lose downstream data in case of a failover. Enabling the failover of logical replication slots in PostgreSQL enhances the robustness and reliability of logical replication setups by allowing logical slots to be transferred and maintained across different database instances.
“This commit by Ashutosh Bapat is another good one,” notes Aleksander. Allowing the EXPLAIN command to report optimizer memory usage in PostgreSQL provides valuable insights into the resources consumed by the query planner and optimizer during the preparation of query execution plans.“It will allow the developer to choose the query that uses less memory,” explains Aleksander. This makes it especially helpful for those trying to fine-tune PostgreSQL’s performance.
Any on this list, really
Bruce Momjian has always been an inspiration to us—bow tie included—so we can safely say that any of the contributions on this list, which Aleksander describes as “overall performance improvements” make us excited about getting our hands on the new PostgreSQL version.
In total, 90 commits (3.5 percent of all commits) were authored, co-authored, and/or reviewed by Timescalers during the PostgreSQL 17 cycle. 😎We’re not going to bother you by going over all of them, but we asked our team of upstreamers to name some of their personal favorites.
“Personally, I’m most excited about the series of patches that moved SLRU (simple least recently used) caches to the 64-bit indexes,” says Aleksander. While we’re not there yet, this opens the path to 64-bit XIDs, which will mitigate the problem of XID wraparound certain users face under specific workloads, such as mixing long-living OLAP (online analytical processing) transactions and OLTP (on-line transaction processing) workloads on the same PostgreSQL instance.
Another Timescaler who contributed to PostgreSQL was database architect Mats Kindahl. Mats helped with refactoring to ensure transitive comparisons in PostgreSQL, which brings several benefits to users. Transitive comparisons allow for more concise and intuitive query expressions, improve query optimization, enhance index usage, and facilitate data modeling, as developers can define relationships between entities more naturally.
Mats also worked on the introduction of standard_ExplainOneQuery in PostgreSQL 17. This addition helps ensure consistent behavior when adding explain hooks, making it easier to predict and understand the effects of explain hooks on query explanation. Developers can focus on implementing specific hooks without worrying about the nuances of query explanation behavior, leading to more efficient development processes and facilitating query performance tuning.
On the reviewing front, Aleksander reviewed (along with other contributors) the partial merge of UUIDv7 support authored by Andrey Borodin. “While there are several UUIDv7 implementations available, the UUIDv7 standard is currently in draft condition,” explains Aleksander, adding that PostgreSQL will only support when the standard is finalized. Once it’s fully supported by PostgreSQL, UUIDv7 will help make time-based queries more efficient.
Here you have it, a reflection on the direction of PostgreSQL 17, the new updates we’re excited about, and some of the contributions we made. If like us, you want to carry on (or start) building on PostgreSQL, give Timescale a try. Features like hypertables (automatically partitioned PostgreSQL tables), continuous aggregates (automatically refreshed materialized views), and advanced data management techniques will significantly enhance PostgreSQL's ability to manage your most demanding workloads effectively.
If you want to expand PostgreSQL’s capabilities while using the PostgreSQL you know and love, create a free Timescale account today.
