What’s new with Postgres at Microsoft, 2024 edition

In this post you’ll get a bird’s eye view of all the Postgres work happening at Microsoft—with highlights for each of the workstreams from the last 8 months. And there’s a lot of ground to cover. Our PostgreSQL database investments encompass both Azure and open source, the Postgres core plus the broader Postgres ecosystem, plus PG community work too.

 

For anyone who still thinks of Microsoft as only a SQL Server company, well, that may be our past—but our present and future very much include PostgreSQL too.

 

Why does this post exist? Because of this conversation:

 

[Friend]
It’s hard to keep track of all the Postgres work at Microsoft. Maybe you can write a blog post.

[Me]
Ok.

[Me]
So here is the first version of this “What’s new with Postgres at Microsoft” post, published late last Aug 2023.

[Also Me]
Where do the months go? How have 8 months gone by since I published the previous version of this post?

 

Pulling together all these highlights is so much fun because I get to interview some of the many super-smart Postgres developers in our team.

 

Ok, so let’s dive into specifics.

 

The Microsoft workstreams covered in this post includes open-source contributions to the PostgreSQL core; development on Postgres extensions such as Citus, work on key utilities in the PG ecosystem such as PgBouncer, pgcopydb, and Patroni; our community work around events such as the upcoming POSETTE: An Event for Postgres (free and virtual, happening Jun 11-13) as well as talks and podcasts—and of course, our work on the managed database service, Azure Database for PostgreSQL.

 

This table of contents follows the layout of this meticulously hand-made infographic, starting with the top-left box and proceeding counter-clockwise. Each of these links will take you straight to whichever sections you’re most interested in.   Or you can grab an espresso and read the post in its entirety.

 

• Azure Database for PostgreSQL – Flexible Server
• Contributing to Postgres open source
• Citus open source extension
• Postgres ecosystem
• Postgres community work
• Azure Cosmos DB for PostgreSQL

 

Figure 1: This infographic-style diagram gives you a visual outline of the different Postgres workstreams underway at Microsoft as of May 2024, sharing highlights of just some of the work we’ve done over the last 8 months since I published the previous August 2023 version of this “What’s new with Postgres at Microsoft” post. For many of the open source bullets in the diagram above, please know our contributions were not made in isolation, rather, our PG17 work involved collaboration with engineers and community members across the global PostgreSQL community.

Azure Database for PostgreSQL – Flexible Server

 

Our team’s flagship PostgreSQL managed database service is called Azure Database for PostgreSQL – Flexible Server.

 

Flexible Server meets the needs of myriad customers—from large enterprises to small to medium-sized businesses to early-stage startups—and has been growing in its capability, month after month after month.

 

Highlights of new features rolled out in the last 8 months

 

• Postgres 16 support: Less than 2 months from the GA of the PostgreSQL 16 open source release, Postgres 16 support was made generally available (GA) in Azure Database for PostgreSQL – Flexible Server. And while major version upgrade support for Postgres 16 took a bit longer, the good news is that major version upgrade for Postgres 16 is now available in Preview, too.
  
  
• Private Link: With the GA of Private Link in Azure Database for PostgreSQL, traffic between your virtual network and our PostgreSQL service navigates the Microsoft backbone network, which means you no longer need to expose your service to the public internet. Now generally available (GA), this feature has been very much in demand by many of you who are enterprise customers. (More details in the docs.)
  
  
• Multi-region disaster recovery / GeoDR: The GA of our “Geo-Disaster recovery with read replicas” includes 2 major capabilities (explained in this blog post virtual endpoints and “promote to primary server”.
  
  Virtual endpoints mean you don’t need to change connection strings in your application. And “promote to primary server” helps to minimize downtime when manually promoting read replicas to primary—such as when you’re in the face of some type of regional disruption. The term most people use to describe this feature is “hassle-free”.
  
  
• TLS version 1.3 support: TLS stands for “Transport Layer Security” and is the modern version of SSL, a key component in security for client-server communications. Once you set the ssl_min_protocol_version parameter to the value of TLSv1.3, Azure Database for PostgreSQL – Flexible Server will mandate the use of TLS 1.3 for all of your client connections. In addition to giving you stronger security, TLS 1.3 also improves performance during the encryption process. Tip: Our security experts strongly recommend you use the latest version of TLS to encrypt your connections to Flexible Server. (More details in TLS documentation.)
  
  
• Microsoft Defender integration: Security matters to all of us, and we all need to increase our defenses to stay ahead of the bad actors. We added Microsoft Defender for Cloud support so you can detect anomalous activities that might indicate harmful attempts to access your database. When enabled, this capability provides proactive anomaly detection; real-time security alerts; guided resolution steps; and integration with Microsoft Sentinel. (More details in documentation.)
  
  
• pgvector 0.6.1 extension support: The pgvector extension to Postgres enables you to store and search vectors in Postgres, which in turn makes PostgreSQL a powerful vector database, enabling all sorts of generative AI capabilities. So, yes, we support pgvector in Flexible Server on Azure—as of the writing of this blog post, pgvector 0.6.1 is supported.  
  
  
• azure_ai extension (Preview): With the azure_ai extension to Azure Database for PostgreSQL, you can use Azure OpenAI directly from Azure Database for PostgreSQL. This means (quoting from a blog post I wrote a few months ago) “you can generate text embeddings by using SQL queries to call into both Azure OpenAI and Azure AI Language services—without needing a separate application layer.” Here’s a 5-minute demo I made, and here’s the announcement blog post from Nov 2023.  
  
  
• Real-time text translation (Preview): The azure_ai extension now includes real-time text translation capabilities using Azure AI Translator. The translation is done in real-time and the translated text can be used immediately or stored in Postgres for future use. You can filter out swear words, too. (More details in the announcement blog post about how to use this feature.)
  
  
• Real-time ML prediction (Preview): Introduced in Mar 2024, the azure_ai extension enables you to invoke machine learning models hosted on Azure Machine Learning, in real-time. The announcement blog post explains how to use this new feature—and it’s relevant for you if you’re building applications to do fraud detection, product recommendations, transportation routing, equipment maintenance, or healthcare patient predictions, among others.
  
  
• Migration service, both online & offline: The migration service in Azure Database for PostgreSQL (overview here) is useful for anyone looking to onboard onto Flexible Server. Offline migration to Flex is supported from Single Server; from RDS for PostgreSQL; from on-prem; or from Azure VMs. And if you’re migrating from Single Server to Flex Server, online migration is also supported, giving you a seamless setup—plus continuous operations with zero downtime.   
  
  
• Major version upgrade support for Postgres 16 (Preview): In-place major version upgrade—which uses the popular pg_upgrade capability from core Postgres—enables you to upgrade existing Flexible Servers to newer versions of Postgres with minimal downtime and a simplified upgrade process. With the addition of Postgres 16, major version upgrade is now supported to upgrade to versions 16, 15, 14, 13, and 12. (More details in the documentation.)
   
• Major version upgrade logging: When enabled, this feature gives you access to detailed Postgres upgrade logs during major version upgrades—and gives you access to the PG_Upgrade_Logs either via the Azure Portal or via the CLI. (More details in the documentation.)  
  
  
• Server Logs with CLI support: In November 2023, we enhanced the Server Logs feature for Flexible Server, in both the portal and the CLI. The updated server logs feature is now easy to enable (and disable) through the Azure portal. Also, you can configure the retention period with options ranging between 1 to 7 days. Additionally, you can access and download your server logs from the Azure portal or you can also download service logs by using the CLI.
  
  
• Grafana Monitoring integration: The Grafana Dashboard for Monitoring with Azure Database for PostgreSQL is just as good as this blog post makes it sound. For those of you who love Grafana it’s worth downloading from the Azure Grafana Gallery. With it, you can monitor your Flex Server database’s availability, active connections, CPU utilization, and storage metrics. Also, there’s a seamless integration between Azure Monitor and Grafana.
  
  
• 30 new monitoring metrics: Over the last 8 months, over 30 new monitoring metrics have been added for Flexible Server. This monitoring concepts page in the documentation spells out what the default metrics are (captured minutely, stored for 93 days, queryable in 30 day intervals)—as well as what the enhanced metrics are (disabled by default.) Also there are autovacuum metrics! And a table that outlines the options for visualizing your Flexible Server metrics: in the Azure Portal, with Azure Monitor’s metrics explorer, and with Grafana.
  
  
• New regions in Italy, Israel, Norway, Poland, UAE, and USA: In Sep 2023, we introduced support for 3 new regions: Norway West, Poland Central, and US Gov Texas. Then in Jan 2024, we added support for Italy North, Israel Central, and UAE Central. This support for 60 regions is part an ongoing effort to give you localized cloud services in as many parts of the world as possible, enabling you to meet business and regulatory obligations—as well as requirements for in-country disaster recovery, where needed. And, we plan to add 8-10 more regions in the next 12 months. You can find the list of supported regions in the documentation.
  
  
• Premium SSD v2 (Preview): The description of “cutting-edge technology with the most advanced general-purpose block storage solution with unparalleled price-performance” is chock full of adjectives, but I’ve seen the (not yet published) performance benchmarks and the results are impressive. More details about Premium SSD v2 are in the docs, including comparisons between Premium SSD v2 vs. Premium SSD. Also, Premium SSD v2 gives you a max disk size of 64 TiB.
  
  
• Storage autogrow: The optional storage autogrow feature does what it says: it automatically increases the size of the provisioned storage of your Flexible Server when storage consumption reaches 80% or 90%, depending on the size of the disk. Thresholds are spelled out clearly in the documentation.
  
  
• Near-zero downtime scaling: With near-zero downtime scaling, the server restart has been reduced to less than 30 seconds after modifying your storage or compute tiers, hence the moniker “near-zero”. This feature kicks in when you scale compute and storage (scaled independently, or scaled together.) It’s available in all public regions for non-HA servers. And for HA-enabled servers, near-zero downtime scaling is currently enabled for a limited set of regions, with more regions to be enabled in a phased manner in the future.

 

Free trial for kicking the tires of Azure Database for PostgreSQL

 

If you’re looking for a free trial for Flexible Server, this “Use an Azure free account” docs page walks you through how to get 750 hours (monthly) of Burstable B1MS instance with 32 GB of storage and 32 GB of backup storage for the first 12 months.

 

What’s your Single Server migration plan?

 

If you’re still running on the first-generation PostgreSQL managed service called “Single Server” (you know, the one that doesn’t run on Linux), then you already know:

 

• Retirement of Single Server: was announced in March of 2023 and will be retired on 28 March 2025.
  
  
• Flexible Server performance advantages: If you’re looking for more incentive to make the switch to Flexible Server, maybe these 3^rd-party performance benchmarks (using the popular HammerDB benchmarking tooling) will help motivate. The results of this benchmark: Flexible Server processes orders 4.71 times faster than Single Server—and can do 2.85 times more tasks at the same time.  
  
  
• Online (and offline) migration tooling: And these online migration tools (Preview) have been helping lots of customers make the move from Single Server to Flex.

 

Our Azure team is hiring!

And... our Postgres team on Azure is hiring!

 

Contributing to Postgres open source

 

Microsoft has been hiring and growing a team of Postgres open source contributors since 2019—with a focus of contributing to the Postgres core.

 

From the previous Aug 2023 version of this blog post, our Microsoft commitment to sponsoring the ongoing development of Postgres remains unchanged:

 

In order to thrive, an open source ecosystem needs commercial support as well as volunteer efforts. Even open source developers need to eat! For the Postgres open source ecosystem to flourish, companies like Microsoft need to support the project by funding development in the Postgres core. Which we do.

 

PostgreSQL is a complex piece of software that runs mission-critical workloads across the globe. To provide the best possible experience on Azure, it follows that we need to thoroughly understand how it works. By having PostgreSQL committers and contributors on our team, they can share knowledge internally across different orgs, or directly answer internal questions regarding incidents or extension development.

 

Because today's cloud operates at a scale most on-prem solutions never encountered, unique cloud data center problems, often relating to performance, now require special attention. Our in-house team of deep Postgres experts are focused on tackling these cloud-scale issues upstream, in the Postgres core. Another benefit: our team's Postgres expertise gives Azure customers confidence in our cloud database services, too.

 

Commercial funding of PostgreSQL developers has another benefit: it gives developers the long-term stability to pursue the big things, the groundbreaking changes that are super important to the future. In particular, the Postgres contributor team at Microsoft is focused on some big architectural changes (example: Asynchronous IO) that you wouldn’t be able to do without the funding for a full-time, multi-year effort.

 

Two (exciting) Postgres contributor updates on Microsoft team

Our Postgres open source contributor team is continuing to grow.

 

• Amit Langote, Postgres committer, has joined the Postgres team at Microsoft! (Yes, Amit is the engineer who committed the json_table work into Postgres 17.)

 

• Melanie Plageman, Postgres contributor extraordinaire on our team, has accepted the invitation to become a PostgreSQL committer. Melanie’s history of contribution and collaboration in the Postgres community made this a well-earned promotion. Many of us echo the sentiment of these words from Álvaro Herrera to congratulate Melanie in her new PG committer role: “May your commits be plenty and your reverts rare ”.   
  
  

And ... our Postgres contributor team is hiring!

 

Highlights of Postgres 17 contributions

 

There is so much goodness in PostgreSQL 17, which hit code freeze last month in April and is expected to GA later this calendar year, usually in Sep or Oct—with a beta release that typically lands in July or August.

 

Highlights of the over 300 commits to PG17 authored or co-authored by members of our team are below.

Attribution is part of the culture in the Postgres open source community, so it must be said that among the many contributions our team made to PG17, the work was done with collaboration from PG contributors around the world, both inside and outside Microsoft.

 

Also want to give a shout-out to our team of Postgres committers (a “committer” is an engineer who has the “commit bit” to merge changes into the Postgres core, equivalent to to the term “maintainer” in other open source projects)—because not only did our Postgres committers commit their own work in Postgres 17, but almost a third of their commits were made on behalf of other developers.

Streaming I/O in Postgres 17

 

• Streaming I/O with I/O combining: This new capability added to Postgres 17 is the start of something big. Streaming I/O (with I/O combining) introduces a whole new paradigm into Postgres—and is an important step toward a future of asynchronous I/O in Postgres. Now asynchronous I/O will NOT be available in Postgres 17, but this Streaming I/O work will still improve performance for users (initially when it comes to sequential scans and ANALYZE, as explained in the next bullets.)
  
  Historically, Postgres reads data 1 page at a time. With the new Streaming I/O capability in PG17, Postgres can look further ahead and see what’s coming. So instead of reading 1 page at a time, Postgres can combine those pages and do a single read of 16 pages. This means reading 128K instead of 8K in a single read. To enable this, a new GUC has been added in PG17 called io_combine_limit which has a default setting of 128K. (link to commit for API for streaming I/O / io_combine_limit commit)
  
  When talking to Thomas Munro, one of the authors of Streaming I/O in Postgres 17, he said this about the project:
  
  

  “It's a big project, and it's hard work to find a pathway that gives incremental benefits through digestible improvements. Each piece has got to make sense on its own. The new stream abstraction already enables I/O combining and advice-based prefetching in 17 as of the time of writing (it hasn't shipped yet, so watch this space to see if it sticks), but the real story is that it paves the way for a fully modernized I/O stack. That's the kind of vision that takes long term funding, that Microsoft is bringing to the PostgreSQL community.”

  
  If you want to dive deeper into this new capability you have 3 places to look:
  
  
  • Video of talk at PGConf.EU: Andres Freund, who originally proposed the idea of I/O streams as a core abstraction—and provided invaluable feedback on the concrete patches eventually proposed to PostgreSQL—gave a talk at PGConf.EU in Prague in Dec 2023 titled: “The path to using AIO in Postgres”.
    
    
  • Upcoming talk at PGConf.dev: Thomas Munro, one of the authors of Streaming I/O from Microsoft, will be giving a talk at the upcoming talk at PGConf.dev 2024 conference in Vancouver that will go deeper on this topic, titled: Streaming I/O and Vectored I/O.
    
    
  • 5mins of Postgres: Lukas Fittl of pganalyze has recorded a short bite-sized overview of the new feature: Waiting for Postgres 17: Streaming I/O for sequential scans & ANALYZE.
    
    
  
  

  True fact: it was micro-benchmarking the performance of this PG17 Streaming I/O work that started the absurd chain of coincidences that led Andres Freund to discover the xz utils backdoor. Anyone who knows Andres is not surprised he decided to investigate sshd processes that were using a surprising amount of cpu. Maybe that's the moment Andres got the first of many "that's weird" feelings that fueled his investigation. Thank you Andres!
  

• Streaming I/O in sequential scans: Thanks to this new ability to do I/O combining and bigger 128K reads (hence fewer system calls too), some SELECT queries tied to sequential scans will be faster in Postgres 17. A significant amount of refactoring went into making sequential scans take advantage of the new streaming I/O API to become what the developers call “async friendly” and while the real motivation is to pave the way to asynchronous I/O in the future, it’s quite nice that some PG17 users will see performance benefits in sequential scans, too! (link to commit)
  
  
• Streaming I/O in ANALYZE: To save you the trouble of looking it up in the docs, “ANALYZE collects statistics about the contents of tables in Postgres, and stores the results in the pg_statistic catalog.” These statistics are important and get used by the query planner.  And in Postgres 17, ANALYZE is the first user for Streaming I/O with random streams, which doesn’t benefit from I/O combining but does benefit from prefetching. The benefit to users: ANALYZE table_name will be faster in Postgres 17. (link to commit)
  
  

Query Planner Improvements in Postgres 17

 

• Query Planner to use Merge Append to efficiently UNION queries: This change in PG17 can improve performance significantly if you have a query with a UNION clause. In particular, this change is especially helpful if the top-level UNION contains a LIMIT node that limits the output rows to a small subset of the unioned rows. How is this possible? With this change, Postgres will be able to use presorted input in order to eliminate duplications—where previously, the Postgres query planner had to use a Hash Aggregate or had to sort the entire Append result. (link to commit)  
  
  
• Query Planner to better handle redundant IS [NOT] NULL: When you create a table you can create a column, select the name and type and whether it can allow NULLS or not—and you can put a NOT NULL constraint on the column. When you write a query you might write WHERE column IS NOT NULL and before this PG17 change, Postgres would always evaluate even if it knew there couldn’t be any NULLs in the table. As of Postgres 17, Postgres is a lot smarter when a column has the constraint IS NOT NULL and avoids doing unnecessary work in that scenario. (link to commit)
  
  

Performance Improvements in PG17

 

• Vacuum WAL volume decrease & performance improvements: WAL in Postgres is the “write-ahead log” which is used to ensure data integrity and to support backups, point-in-time recovery, and replication. New to Postgres 17, vacuum will now produce less WAL by volume in terms of number of bytes—thereby taking up less space on disk and speeding up replay. Vacuum which freezes tuples may emit 30% less WAL—and writing and syncing these WAL records may take up to 15% less time. (link to commit)
  
  
• Reduce memory usage in sort & incremental sort by using a bump memory allocator: The benefit of this change is that Postgres will use less memory for doing sorts, so things don’t have to go to disk because work_mem is full, which means improved sort and incremental sort performance. Also, when Postgres data is more compact in memory, that means Postgres can use CPU caches more efficiently too. (link to bump memory allocator commit / link to use of bump memory allocator for tuplesorts)
  
  
• Improve memory allocation performance: These improvements to the 4 different types of memory allocators in Postgres improve the performance of everything—slightly. The way this was implemented was to optimize for the most common code paths which are described in the commits as “hot” paths—as compared to cold paths which are less common and would generally require a malloc anyway so the cold paths would be slower anyway. (link to allocset commit / link to generation and slab commit.)
  
  
• Query planner improvements for highly-partitioned tables: By speeding up Bitmapset processing by removing trailing zero words in PG17, the Postgres query planner speed doubled in some of the test cases. In particular, this optimization can make the query planner twice as fast for workloads with a lot of partitioned tables. And, I’m told there is more work that can be done in this area to improve performance even further in the future. (link to Bitmapset commit)
  
  
• libpq performance optimization: The detailed name of this feature is “avoid needless large memcpys in libpq socket writing”, and the bottom line is that this improvement makes libpq more efficient. The result: improved performance when clients have large amounts of data in one message— such as a SELECT outputting large (>8k) variable length columns such as text; or a big COPY TO STDOUT; or in pg_basebackup. (link to commit)
  
  
• libpq query cancellation: Prior to Postgres 17, if you used an event loop in your application to send multiple queries at the same time (such as when using the "async" feature that many programming languages have) then sending a query cancellation would block this event loop—and thus all your queries—until the cancellation was done. Thanks to this commit, this not a problem anymore in PG 17! More details in this blog post on the Dalibo site: Improved query cancellation in PostgreSQL 17 and Psycopg 3.2—clearly people are happy that you can now cancel in-progress queries in a non-blocking manner. In addition, also new to Postgres 17, the connections over which cancel requests are sent will now be encrypted, if the original connection was encrypted. (link to commit)
  
  
• Reduce memory usage for JIT: Just-in-Time (JIT) compilation in Postgres can make your queries insanely fast for certain workloads, particularly if you are running expression-heavy queries that are CPU-bound. However, there was an issue with JIT that caused it to leak a lot of memory that was causing some people to turn off JIT. With this fix, which some people are celebrating, you can turn JIT back on, and new users won’t have to turn it off. (link to commit) 
  
  
• pg_upgrade performance: This improvement in PG17 makes pg_upgrade runs faster, especially during the compatibility check phase, which many users like you may run over and over again to make sure your cluster is ready to be upgraded—or to make sure nothing problematic has snuck into your Postgres cluster as you’re preparing to upgrade. Speedup of the pg_upgrade check varies depending on the Postgres version being upgraded from, but will typically be 2x or better.
  
  For those of you who are Flexible Server customers of Azure Database for PostgreSQL, this improvement will also benefit major version upgrades to Postgres 17 once it’s available. (link to commit / link to mailing list discussion)

 

Developer experience in PG17

 

• pg_buffercache_evict test tool: A superuser-only developer test utility. From the commit, “When testing buffer pool logic, it is useful to be able to evict arbitrary blocks. This function can be used in SQL queries over the pg_buffercache view to set up a wide range of buffer pool states. Of course, buffer mappings might change concurrently so you might evict a block other than the one you had in mind, and another session might bring it back in at any time.  That's OK for the intended purpose of setting up developer testing scenarios.”
  
  This pg_buffercache_evict tool will enable future enhancements to memory plasticity, which will be explored in this upcoming talk at PGConf.dev by Krishnakumar Ravi and Palak Chaturvedi.  (link to commit)
  
  
• Meson build system maintenance: Lots of ongoing maintenance on the newer build system for Postgres, called Meson. Meson is an open source build system that is faster, multiplatform, modern, cleaner, popular. And Meson is much more user friendly when compared to the venerable autoconf and make-based system, which has served PostgreSQL very well for decades but is starting to show its age.
  
  
• Postgres CI maintenance: In Postgres 17 there are myriad commits to maintain the Postgres CI that was first adopted in Postgres 15. As you would expect, the Postgres CI continues to be absolutely transformative. With Cirrus CI, every commit you push into your GitHub repo (cloned from PostgreSQL) will get tested across 4 operating systems, along with extra checks—catching a lot of problems early. The result: reviewers can focus on higher level architectural questions, and the build farm (which runs the test suites after things are committed) no long turns red as much as it used to.

 

Postgres 17 release notes (first draft!)

 

Hot off the press, while I was writing this blog post, Bruce Momjian of the Postgres core team published the first draft of the Postgres 17 release notes in the “PostgreSQL devel” docs branch. While these PG17 release notes are still being worked on and will definitely change, they give a taste of what’s to come.

 

Citus open source

 

Citus is an open source extension to Postgres (open source repo on GitHub) that gives you the superpower of distributed tables. Who uses Citus? People with data-intensive applications that need more compute, memory, or scale than they can get from a single Postgres node.   

 

The tagline for the open source project is that “Citus gives you the Postgres you love, at any scale.”  

 

And is Citus popular? There are almost 10,000 stars on GitHub—maybe your star can be the one that pushes Citus over the edge to hit 10K. ️

 

New Citus open source features in last 8 months

 

• Postgres 16 support: Published on the Citus Open Source Blog, this Citus 12.1 release blog post announced the Citus support of Postgres 16 in Citus 12.1, within just one week of the PG16 release. (More details in the Citus 12.1 release notes.)   
  
  
• PG16: JSON aggregate support: As of Citus 12.1, Citus now supports and parallelizes the new JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregates. (Code example in the 12.1 release notes.)
  
  
• PG16: DEFAULT in COPY: By using the new DEFAULT option in PG16 with COPY FROM, you can control in which rows you want to insert the default value of a column (vs. inserting a defined, non-default value.) And as of Citus 12.1, this new DEFAULT option is supported and propagated to the nodes in a distributed cluster. (Code example in the 12.1 release notes.)
  
  
• PG16: more DDL propagation: Citus now propagates new CREATE TABLE, VACUUM, and ANALYZE options to worker nodes in the distributed cluster. And according to the 12.1 release notes, Citus can propagate the STORAGE attribute if it is specified when creating a new table. In addition, Citus can now propagate BUFFER_USAGE_LIMIT, PROCESS_MAIN, SKIP_DATABASE_STATS and ONLY_DATABASE_STATS options in VACUUM and/or ANALYZE.
  
  
• ICU collation rule propagation: Prior to Postgres 16, Citus already supported distributed collations. So with the PG16 addition of custom ICU collation rules that can be created using the new “rules” option in CREATE COLLATION, Citus just needed to support the propagain of this new PG16 collation “rules” option. (Details in 12.1 release notes.)
  
  
• Support TRUNCATE triggers on Citus foreign tables: Those of you who care about audit logging were probably pleased to see Postgres 16 add support for TRUNCATE triggers for foreign tables. With Citus 12.1 you can use the new TRUNCATE triggers features with Citus foreign tables too. (More details in the announcement blog post.)  
  
  
• Combine query-from-any-node with load balancing: PG16 added a new load balancing feature in libpq that lets you specify load_balance_hosts and set it to random. This new libpq load balancing feature makes it easy to load balance in combination with the Citus query-from-any-node feature. (More details in the 12.1 release notes.)
  
  
• Distributed schema move: Citus 12.1 includes some schema-based sharding improvements, including the new citus_schema_move() function, which enables you to move a distributed schema to a different node. (See 12.1 release notes for more details.)
• GRANT ... ON DATABASE propagation: More schema-based sharding improvements in Citus 12.1: now you can propagate GRANT/REVOKE ON DATABASE commands. (Code examples in the release notes.)
  
  
• Distributed schema table from local table when identity column: Before Citus 12.1 it was not possible to create a distributed schema table from a local table if it uses an identity column. Code example of how you can take advantage of this new feature is in the 12.1 blog post.
  
  
• Citus dev containers!: From the Citus commit, the new Citus devcontainer “allows for quick generation of isolated development environments, either local on the machine of a developer or in a cloud, like GitHub Codespaces.” With the introduction of Citus dev containers, it’s now much easier to setup the Citus development environment, making it easier for new contributors. Detailed how-to instructions are in the Contributing.md in the Citus repo.

 

Postgres ecosystem

 

• Patroni 3.2 and 3.3: Patroni is the most popular High Availability (HA) solution for Postgres. It helps you deploy, manage, and monitor HA clusters using streaming replication—and it’s open source. Alexander Kukushkin from our team is the technical lead and collaborates with engineers from different companies on Patroni—also, Alexander gave a recent talk at Nordic PGDay 2024 titled “Step-by-step Patroni cooking guide.”
  
  In Patroni 3.2, notable new features include priority failover; generating Patroni configuration from a Postgres cluster not yet managed by Patroni; and making permanent physical replication slots on standby nodes. In Patroni 3.3, notable features include improved visiblity of pending Postgres restart; and possibility to run standby nodes without replication by replaying WAL only from archive.
  
  
• PgBouncer 1.21.0, 1.22.0, and 1.22.1: PgBouncer is a popular open source connection pooler—and in the last 8 months there have been 3 notable PgBouncer releases that our team has contributed to. I love the “names” the PgBouncer team gives to their releases.
  
  PgBouncer 1.21.0 is called “The one with prepared statements” which adds support for protocol-level named prepared statements, which Jelte tells me was one of the most requested features for PgBouncer. With 1.21.0, JDBC works out of the box, npgsql (a .NET client) works out of the box, and you no longer need to turn off PgBouncer when using prepared statements. Instead you just enable prepared statement support in PgBouncer—and queries are faster in most scenarios where you run the same query over and over again, especially if those SQL queries are large queries.
  
  How fast? According to the changelog for 1.21.x, “in synthetic benchmarks this feature was able to increase query throughput anywhere from 15% to 250%, depending on the workload.”
  
  PgBouncer 1.22.0 is called “DEALLOCATE ALL” and PgBouncer 1.22.1 is called “It’s summer in Bangalore.” (More details in the 1.22.x changelog.)  
  
  
• pgcopydb 0.14 and 0.15: The pgcopydb utility (open source repo on GitHub) automates running pg_dump and pg_restore between two running Postgres servers. Example use cases include migrations to newer hardware, migrations to a newer instances—and also Postgres major upgrades. And our Migration service in Azure Database for PostgreSQL is built on top of pgcopydb, making it relevant to those of you who run on Azure too. There are boatloads of improvements in the v0.15 release and the v0.14 release, mostly about being able to cater to more use cases. Also some memory usage fixes.  
  
  Dimitri Fontaine’s inputs on what to highlight about recent changes in pgcopydb:
  
  “I have been asked a lot about how to resume operations when using pgcopydb, and some users wanted to have a better grasp of how we use snapshots and replication slots and their impact on the ability to resume operations. The new documentation chapter Resuming Operations (snapshots) covers that in details. Oh, the new tutorial is a great place to get started with pgcopydb too.”
  
  
• HLL and TopN: HyperLogLog (HLL) and TopN are both approximation algorithms, sometimes called sketch algorithms. HLL is used to solve the count-distinct problem. Our team maintains the HLL open source extension and the primary change in this time period was to add Postgres 16 support. The TopN extension, which we also maintain, is used to calculate the top values according to some criteria.
  
  
• activerecord-multi-tenant: Our team maintains the activerecord-multi-tenant gem which makes it easy for multi-tenant Ruby on Rails applications to use row-based sharding with Citus. (Whereas for schema-based sharding you can use the acts_as_tenant gem, on which activerecord-multi-tenant is based.)
  
  
• django-multitenant: Similar to activerecord-multi-tenant, this library our team maintains is for multi-tenant applications that want to use row-based sharding—the difference is that this library is for Python and Django applications. (For schema-based sharding there are other libraries you can use that are maintained by the community, with django-tenants being the most popular.)

 

Postgres community work

 

Given my work as head of Postgres open source community initiatives at Microsoft, it’s no surprise: contributing to Postgres in ways beyond code is near and dear to my heart. I’ve even given a few talks about it.

 

Listed below are highlights of the community work that our Postgres team at Microsoft has contributed in the last 8 months.

 

• Serve on Postgres organizing & talk selection teams: The awesome Postgres community conferences—which happen all around the world—are an opportunity for knowledge sharing, learning, and networking. And if you take advantage of it, the in-person hallway track can open up all sorts of doors for you in the Postgres world. And members of our PG team at Microsoft have served the organization teams &/or the talk selection teams in these Postgres community events in the last 8 months:
  
  
  • PGConf.EU 2023
  • PGConf NYC 2023 and 2024
  • FOSDEM PGDay 2024
  • Nordic PGDay 2024
  • PGDay Chicago 2024
  • PGConf.dev 2024
    
    
• Sponsor Postgres conferences: Postgres conferences need financial support or they simply won’t happen. And Microsoft is proud to be able to sponsor all of these Postgres events for the Postgres community over the last 8 months:
  
  
  • PGConf NYC 2023 – Platinum sponsor
  • PGConf EU 2023 – Platinum sponsor
  • PGConf India 2024 – Diamond sponsor
  • Nordic PGDay 2024 – Supporter sponsor
  • pgDay Paris 2024 – Supporter sponsor
  • PGConf Germany – Platinum sponsor
  • Postgres Conference Silicon Valley – Partner sponsor
  • PGDay Chicago – Gold sponsor
  • PGConf.dev 2024 – Gold sponsor
    
    
• POSETTE: An Event for Postgres, happening Jun 11-13: Organized by our Postgres team at Microsoft, POSETTE is a free & virtual developer event, now in its 3rd year, formerly called Citus Con. This year’s event will take place online Jun 11-13. With 4 livestreams, 4 keynotes, 38 talks, 44 amazing speakers—there’s guaranteed to be something for everyone. 
  
  Check out the schedule to see what people are so excited about, and be sure to save the date. You can also add specific livestream(s) to your calendar:
  
  
  • Livestream 1
  • Livestream 2
  • Livestream 3
  • Livestream 4
  
  Of course you can always watch the talks on YouTube after the livestreams are over, at your leisure, at 2X speed—but then you’ll miss the opportunity to ask the speakers questions via live text chat while the livestream is happening.
  
  If you’re curious, there’s a blog post for why we changed the name to POSETTE. And in the interest of transparency, a blog post about the process for POSETTE talk selection too.  
  
  
• Host monthly podcast for developers who love Postgres: This monthly podcast for developers who love Postgres started as a pre-event for Year 2 of Citus Con, hence the original name “Path To Citus Con”. The focus is on the human side of Postgres and open source, and we often explore how people in the Postgres community got their start: as developers, as Postgres users, or as Postgres contributors. You can find all 15 past episodes online (or on your favorite podcast platform, as well as on YouTube.) Oh, and we record LIVE on Discord and it’s quite fun to participate in the live chat that happens in parallel to the live recording.
  
  
• So many blog posts: You can find many of our Postgres team’s blog posts on Microsoft Tech Community as well as on the Citus Open Source Blog. (And yes, we syndicate our open source blog posts to Planet Postgres.)
  
  
• Conference talks at PG events: Both in-person and virtually, our Postgres teams have been active on the conference circuit. How active? Our engineers and subject matter experts delivered 49 talks in the 8 months since I published the previous version of this “what’s new” blog post last August.
  
  Later in May, Postgres people on our team will be presenting 6 different sessions at PGConf.dev in Vancouver. And some of my teammates will be presenting virtually at POSETTE in June!
  
  
• Citus monthly technical newsletter: Our monthly Citus technical newsletter includes links to latest blog posts and releases of the Citus extension. And it’s easy to join the Citus newsletter.
  
  
• Citus Slack for Q&A: If you’re a Citus open source user, you can join our Slack for Q&A about the Citus extension and distributed PostgreSQL.
   
• PGSQL Phriday contributions: Ryan Booz from Redgate started PGSQL Phriday, a monthly community blog event for the Postgres community. It seems like it started yesterday but there have been 16 blogging events so far so clearly it’s been happening for more than a year. I participated in PGSQL Phriday #014 organized by Pavlo Golub, all about PostgreSQL Events, with this post, an Illustrated Guide to Postgres at PASS Data Summit 2023.  

 

Figure 2: POSETTE: An Event for Postgres 2024 is a free and virtual developer event, organized by our Postgres team at Microsoft and now in its 3rd year. Be sure to check out the schedule and then add your favorite livestream to your calendar. The benefit of attending the livestream is the chance to ask the speakers questions via live text chat.

 

Azure Cosmos DB for PostgreSQL

 

Azure Cosmos DB for PostgreSQL is a distributed Postgres database service geared toward workloads that need a multi-node database cluster.

 

Typical workloads for a distributed PostgreSQL database include multi-tenant SaaS, real-time analytics apps such as timeseries, and hybrid transactional and analytical applications.

 

This “Product updates” page in the docs is a good page to bookmark and is the comprehensive source for new capabilities in Azure Cosmos DB for PostgreSQL. But let’s walk through just a few highlights...

 

What’s new in Azure Cosmos DB for PostgreSQL in the last 8 months?

 

Azure Cosmos DB for PostgreSQL is a distributed Postgres service powered by the Citus extension to Postgres—which is geared toward data-intensive applications that need the scale and performance of a multi-node distributed Postgres database cluster.

 

In the last 8 months, Azure Cosmos DB for PostgreSQL has added GA support for:

• Postgres 16
• 32TiB storage for multi-node clusters
• Customer Managed Keys (CMK) in all regions
• Geo-redundant backup & restore
• EntraID authentication in addition to Postgres roles

 

This Release Notes page in the Azure documentation has even more details about new capabilities in Azure Cosmos DB for PostgreSQL.    

 

Microsoft ❤️ Postgres

 

In putting together this post I was struck by all the places our Postgres team is contributing to Postgres: first by offering a popular managed Postgres database service on Azure—and also by the ways we contribute to PostgreSQL with code, architecture, reviews, bug reports, commitfest management, CVEs, testing, extensions, ecosystem tooling, conference sponsorships, conference talks, organizing events, and all the rest of the ways we contribute beyond code too. The list goes on. Not to mention the long-term architectural investments in Postgres.

And there’s more! I didn’t even mention that Andres Freund serves on the Postgres core team.

When Daniel Gustafsson and I were looking at all the metrics about the team’s PG17 open source work, he summarized it well, “...Microsoft employees are involved in all aspects of Postgres.”