PostgreSQL 17 Preview on Azure Postgres Flexible Server

[varun-dhawan]

We are thrilled to announce the preview release of PostgreSQL 17 on Azure Database for PostgreSQL Flexible Server! This release brings a host of new features and enhancements that promise to elevate your PostgreSQL experience. Our goal with this preview is to provide an opportunity for early testing into the new capabilities of PostgreSQL 17^ and enable customers to start testing these features before we transition into general availability.
​

What’s New in PostgreSQL 17?​

PostgreSQL 17 continues the tradition of innovation with several key features aimed at improving database performance, manageability, and developer experience. Here are some of the highlights:
​

• Getting Insights into Memory Usage via EXPLAIN: PostgreSQL 17 introduces an enhancement to the EXPLAIN command that reports the memory usage of the query planner during the preparation of execution plans. This information helps identify queries that consume excessive memory during the planning phase, aiding in query optimization and resource management.
  
  

  -- For example:
  EXPLAIN ANALYZE SELECT * FROM my_table WHERE id < 100;
  
  -- The new EXPLAIN output now includes a line like:
  Planning Memory Usage: 10,240 kB

• Improvements to the Vacuum Process: Say goodbye to bloat! The enhanced vacuum process in PostgreSQL 17 ensures better space management and less interference with concurrent transactions, which means smoother database operations.
  ​
• Enhanced JSON Functions: PostgreSQL 17 introduces new capabilities to simplify how you work with JSON data, including support for the JSON_TABLE function. This function allows you to convert JSON data directly into a relational table format, making it effortless to query and analyze JSON data using standard SQL without additional transformations.
  
  
  

  -- For example, say we have a JSON array that lists service regions and their availability:
  SELECT *
  FROM json_table(
    '[
       {"region": "East US", "services": {"compute": "Available", "database": "Limited"}},
       {"region": "West Europe", "services": {"compute": "Available", "database": "Available"}},
       {"region": "Southeast Asia", "services": {"compute": "Limited", "database": "Available"}}
     ]',
    '$[*]'
    COLUMNS (
      region_name TEXT PATH '$.region',
      compute_status TEXT PATH '$.services.compute',
      database_status TEXT PATH '$.services.database'
    )
  ) AS region_info;
  
  -- This query will produce the following output:
  
  | region_name    | compute_status | database_status |
  | -------------- | -------------- | --------------- |
  | East US        | Available      | Limited         |
  | West Europe    | Available      | Available       |
  | Southeast Asia | Limited        | Available       |

  
  
  ​
• Dynamic Logical Replication: With support for dynamically changing the replication set without restarting or reconfiguring, PostgreSQL 17 simplifies the process of managing logical replication and enables more flexibility for your replication strategy.
  

  -- For instance, you can now use:
  ALTER PUBLICATION my_publication ADD TABLE new_table;

These are just a few of the exciting features introduced in PostgreSQL 17, and we can’t wait for you to try them out!​

Celebrating Azure Postgres Contributions to PostgreSQL 17
​

The Azure Postgres team made 410 commits to the PostgreSQL 17 release, focusing on key improvements like I/O combining, query planner optimization, memory usage reduction, and better performance for partitioned tables. These contributions help make PostgreSQL 17 faster and more efficient, ensuring PostgreSQL delivers the best experience with the latest community innovations. To learn more about Microsoft’s contributions to the PostgreSQL open-source community, check out our blog: What’s new with Postgres at Microsoft, 2024 edition.
​

Roadmap and What’s Next​

This preview release is just the beginning. Our team is actively working on providing Major Version Upgrade support for PostgreSQL 17, with the preview expected early next year and general availability soon after. Our goal is to ensure that customers can seamlessly upgrade to PostgreSQL 17 without downtime, leveraging the latest features and performance improvements.


We encourage you to try out PostgreSQL 17 on Azure Database for PostgreSQL Flexible Server and share your feedback by reaching out to us at AskAzureDBforPostgreSQL@microsoft.com. Stay tuned for more updates as we bring you closer to general availability!

^ Postgres 17 preview is available in "East Asia" starting immediately with more regions coming soon.​

Continue reading...