Log Insights in Minutes: A Simpler pgBadger Workflow
Sometimes the fastest way to understand a PostgreSQL workload is not another dashboard. It is a good log report. pgBadger is a PostgreSQL log analysis tool that turns raw PostgreSQL logs into an interactive HTML report. It helps summarize query activity, connection patterns, errors, temporary files, lock waits, autovacuum activity, and more. Earlier guidance for generating pgBadger reports from Azure Database for PostgreSQL Flexible Server focused on exporting logs through Diagnostic Settings, storing them in a storage account, and then using tools such as BlobFuse and jq to extract PostgreSQL log lines from JSON files. That workflow is still useful when customers centralize logs across multiple servers. However, if you are already using the Server logs feature in Azure Database for PostgreSQL Flexible Server, there is a much simpler path. In this post: You’ll learn how to generate a pgBadger HTML report from Azure Database for PostgreSQL Flexible Server by downloading native PostgreSQL .log files directly from the Azure portal. No storage account, BlobFuse mount, or JSON extraction required. Fast path Configure log_line_prefix . Enable Server logs for download. Download the PostgreSQL .log files. Run pgBadger with the matching prefix. Open pgbadger-report.html . Why use this workflow? With Server logs, you can download native PostgreSQL .log files directly from the Azure portal and run pgBadger locally. Older path Simpler path in this blog Diagnostic Settings → Storage account → BlobFuse → JSON extraction → pgBadger Server logs → Download .log files → pgBadger Area Older Diagnostic Settings workflow Server logs workflow Export path Diagnostic Settings to storage account Download .log files directly from the portal Format JSON payloads need extraction Native PostgreSQL .log files Extra tooling BlobFuse and jq JSON parsing None Best suited for Centralized or multi-server logging Quick per-server analysis Outcome Flexible, but more setup Faster path to pgBadger Recommended: Use the Server logs workflow when you want a fast, low-friction way to generate a pgBadger report from one Azure Database for PostgreSQL Flexible Server. When should you use this workflow? Use this workflow when... Use Diagnostic Settings when... You need a quick report for one Flexible Server. You centralize logs from many servers. You want to run pgBadger locally. You need long-term retention or workspace-level querying. You want to avoid JSON extraction. You already have automated log export pipelines. Before you start A machine where you can install or run pgBadger. A working Perl runtime. Git Bash on Windows, so the multi-line shell commands work as shown. Portal access to your Azure Database for PostgreSQL Flexible Server. Permission to update server parameters and enable Server logs. Important: pgBadger can only analyze what PostgreSQL logs capture. To populate query timing and slow-query sections in the report, enable log_min_duration_statement before collecting logs. Logs collected before that change will not include duration data. Workflow overview Task Type Rough effort Install or prepare pgBadger One-time setup per analysis machine 5–10 minutes Configure log_line_prefix One-time setup per server 2–3 minutes Enable Server logs One-time setup per server 2–3 minutes Download logs and run pgBadger Repeatable 2–5 minutes Install or prepare pgBadger on the machine where you will analyze logs. Configure log_line_prefix so pgBadger can parse each log line. Enable Server logs, so PostgreSQL logs are available for download. Download the logs and run pgBadger locally. 💡Pro tip: Start with a narrow log window first. Use one or two hourly log files, confirm the report looks right, and then expand the analysis window if needed. Step 1: Install pgBadger Before generating a report, you need pgBadger available on the machine where you plan to analyze the downloaded PostgreSQL log files. Run this on a Linux VM, WSL, or another Linux-based environment where you can install packages. Note: Azure Cloud Shell may work for quick testing, but package installation and build-tool availability can vary by session. For repeatable analysis, use a Linux VM, WSL, or another environment you control. Copy and run sudo apt-get update && sudo apt-get install -y git perl make gcc && \ git clone https://github.com/darold/pgbadger.git && \ cd pgbadger && \ perl Makefile.PL && \ make && \ sudo make install && \ pgbadger -V What good looks like: The install command completes successfully and pgbadger -V returns the installed pgBadger version. Step 2: Configure log_line_prefix This is a one-time server configuration step. The log_line_prefix parameter controls the beginning of each PostgreSQL log line. pgBadger uses this prefix to extract useful fields such as timestamp, user, database, and process ID. In the Azure portal, open your Flexible Server and go to Server parameters. Search for: Parameter log_line_prefix Set this value %m user=%u db=%d pid=%p: Then select Save. In Server parameters, confirm that the custom value is saved for log_line_prefix . Figure 1: Set log_line_prefix so pgBadger can correctly parse timestamp, user, database, and process ID from each log line. Prefix tokens Token Meaning %m Timestamp with milliseconds %u Username %d Database name %p Process ID After this change, log lines should look like this: Example log line 2026-06-22 19:00:00.070 UTC user=pgadmin db=highcpu pid=3805603: LOG: statement: SELECT 1 FROM pg_extension WHERE extname='pg_stat_statements' The matching pgBadger prefix for this log format is: Matching pgBadger prefix %m user=%u db=%d pid=%p: You will use this same value later in the pgBadger command. What good looks like: The server parameter is saved, and new PostgreSQL log lines begin with timestamp, user, database, and process ID fields that match the pgBadger prefix. Step 3: Enable Server logs for download This is also a one-time setup step. In the Azure portal, open your Flexible Server and go to Server logs. Enable: Portal setting Capture logs for download Set the retention period based on how long you want logs to remain available for download. For example, a 7-day retention period keeps logs available for download for 7 days. In Server logs, enable Capture logs for download and choose the retention window. Figure 2: Enable Capture logs for download and set a retention period long enough to cover the analysis window you want to inspect. What good looks like: After Server logs are enabled, hourly PostgreSQL log files appear in the Server logs blade and can be downloaded from the Azure portal. Once enabled, hourly log files appear in the Server logs blade. The files are named by date and hour, for example: Example log files postgresql_2026_06_22_19_00_00.log postgresql_2026_06_22_20_00_00.log Step 4: Download and organize the logs locally From the Server logs page, select the .log files for the time window you want to analyze and download them. For example, to analyze activity between 19:00 and 21:00 UTC, download: Example files to download postgresql_2026_06_22_19_00_00.log postgresql_2026_06_22_20_00_00.log On your local machine, create a folder for that analysis window. A simple convention is to use the Mon-DD format. Folder name Jun-22 Place the downloaded .log files inside that folder. Your local folder structure should look like this: Folder structure pgbadger-13.1/ pgbadger Jun-22/ postgresql_2026_06_22_19_00_00.log postgresql_2026_06_22_20_00_00.log Step 5: Generate the pgBadger report Open Git Bash from the folder where pgBadger is located. For example, if pgBadger is inside the pgbadger-13.1 folder, open Git Bash from that folder. # Action Command 1 Set the folder FOLDER=Jun-22 2 Confirm files ls -lh ./$FOLDER 3 Run pgBadger Use the full command below. Copy and run FOLDER=Jun-22 ls -lh ./$FOLDER perl -X ./pgbadger -f stderr \ --prefix '%m user=%u db=%d pid=%p:' \ ./$FOLDER/*.log \ -o ./$FOLDER/pgbadger-report.html Command breakdown Part of command Purpose perl -X ./pgbadger Runs pgBadger and suppresses non-critical Perl warnings. -f stderr Parses PostgreSQL stderr log files. --prefix '%m user=%u db=%d pid=%p:' Matches the log_line_prefix set on the server. ./$FOLDER/*.log Analyzes every .log file in the selected folder. -o ./$FOLDER/pgbadger-report.html Writes the HTML report into the same folder. When the command completes successfully, you should see output like this: Expected output Parsed 12134249 bytes of 12134249 (100.00%), queries: 26684, events: 83 LOG: Ok, generating html report... What good looks like: pgBadger finishes parsing the logs and creates pgbadger-report.html in the selected folder. Step 6: Open the report Open the generated report: Copy and run start ./$FOLDER/pgbadger-report.html The report opens in your default browser. The final report is created here: Generated report path Jun-22/pgbadger-report.html What the report can show The pgBadger report gives you a quick view into the workload shape for the selected log window. For example, in a sample run across two hourly log files, pgBadger summarized: Total number of queries. Number of unique normalized queries. Query traffic over time. Events such as errors and fatal messages. Session and connection patterns. Once the report opens, start with Global Stats to confirm the time range, total queries, normalized queries, and query peak. Figure 3: Start with Global Stats to validate the selected time range, total query count, normalized query count, and query peak. Query volume and normalized queries Many raw queries can often reduce to a smaller number of normalized query patterns. This helps identify whether the workload is spread across many different query shapes or dominated by a smaller set of repeated statements. Example: In this sample run, 26,684 queries reduced to 59 normalized query shapes. That suggests the workload is mostly a small set of repeated statements, which can help focus tuning effort. Traffic patterns The SQL Traffic section helps identify spikes, quiet periods, and workload changes over time. Figure 4: Use SQL Traffic to identify query spikes, quiet periods, and workload changes during the selected log window. Figure 5: Review the query breakdown to compare read vs. write volume and query-type distribution for the selected Server logs window. For example, if the report shows a steady baseline followed by a sharp spike, that spike can be correlated with application activity, batch jobs, synthetic tests, or operational events during the same time window. Query duration If query duration shows 0 ms or the slow query sections are empty, it usually means duration logging was not enabled when the logs were collected. In that case, pgBadger can still show query counts and events, but it cannot calculate the slowest queries, total execution time, average duration, or maximum duration. To unlock those timing sections, enable log_min_duration_statement , collect fresh logs, and rerun pgBadger. What pgBadger cannot infer from missing logs pgBadger reports are only as complete as the log data you provide. If PostgreSQL did not log duration, lock waits, temporary files, or autovacuum activity during the selected time window, pgBadger cannot reconstruct those details later. To analyze... Enable before collecting logs Slow queries log_min_duration_statement Lock waits log_lock_waits Temporary files log_temp_files Autovacuum activity log_autovacuum_min_duration Repeatable copy/paste block Reusable command block Change only FOLDER for each new analysis window. Copy and run FOLDER=Jun-22 ls -lh ./$FOLDER perl -X ./pgbadger -f stderr \ --prefix '%m user=%u db=%d pid=%p:' \ ./$FOLDER/*.log \ -o ./$FOLDER/pgbadger-report.html start ./$FOLDER/pgbadger-report.html For another date, change only this line: Update this value FOLDER=Jun-22 Examples: Example folder values FOLDER=Jun-23 FOLDER=Jul-01 FOLDER=Aug-15 Optional: Improve report quality pgBadger can only analyze the information captured in PostgreSQL logs. The default logs may be enough for query frequency, connection activity, and errors. For deeper performance troubleshooting, consider enabling additional logging parameters based on your scenario. Scenario Parameter Suggested value Notes Slow query analysis log_min_duration_statement 1000 Logs statements slower than 1 second. Short controlled test log_min_duration_statement 0 Logs every statement. Use carefully. Lock troubleshooting log_lock_waits on Helps identify lock waits. Temporary file analysis log_temp_files 0 Logs all temporary files. Autovacuum visibility log_autovacuum_min_duration 0 Useful during focused analysis. Useful parameters include: Recommended logging parameters log_lock_waits = on log_temp_files = 0 log_autovacuum_min_duration = 0 To capture query durations, configure: Duration logging log_min_duration_statement = 1000 This logs statements that run longer than 1000 milliseconds. For short test runs, you can temporarily use: Short test run only log_min_duration_statement = 0 Caution: Use log_min_duration_statement = 0 carefully on busy production servers. It logs every statement and can generate a large volume of logs. Duration matters: If duration logging is not enabled, pgBadger can still show query counts and events, but slowest-query, total duration, average duration, and maximum duration sections will be limited or empty. Common mistakes and quick fixes Symptom Likely cause Fix Report is empty Prefix mismatch Match --prefix with log_line_prefix . No duration data Duration logging was not enabled Set log_min_duration_statement before collecting logs. No files visible Server logs disabled or retention expired Enable capture and check retention. pgBadger command fails pgBadger is not in the current folder or path Run pgbadger -V to confirm installation. Common troubleshooting FAQs 1. Report is created but empty This usually means the pgBadger prefix did not match the actual log format. Check the first few lines: Copy and run head -5 ./$FOLDER/*.log Make sure the pgBadger --prefix matches the server’s log_line_prefix . 2. Report shows queries but no duration PostgreSQL logged statements but did not log durations. Enable one of the following, collect fresh logs, and rerun pgBadger: Parameter options log_min_duration_statement = 1000 # or temporarily for testing log_min_duration_statement = 0 3. No .log files are visible Confirm that Server logs are enabled: Portal setting Capture logs for download Also check the retention period. If the retention period has expired, older logs may no longer be available for download. 4. pgBadger command fails Confirm that pgBadger is available in the current folder or installed in your path. Copy and run pgbadger -V If you are running pgBadger from the local folder, use: Copy and run perl -X ./pgbadger Summary For customers already using Azure Database for PostgreSQL Flexible Server logs, the pgBadger workflow is straightforward: Install pgBadger. Configure log_line_prefix . Enable Server logs for download. Download the .log files. Place them in a local date-based folder. Run pgBadger with the matching prefix. Open pgbadger-report.html . Bottom line: Server logs give you the shortest path from Azure Database for PostgreSQL Flexible Server logs to a pgBadger report. Download the native .log files, run pgBadger with the matching prefix, and open the generated HTML report. References pgBadger - source and documentation GitHub pgBadger - project site Azure - Download server logs from the portal Flexible Server Azure - Logging concepts Flexible Server Azure - Configure server parameters via the portal PostgreSQL - log_line_prefix and logging parametersGeneric Best Practices for HikariCP with Azure Database for PostgreSQL
Author: Mohamed Baioumy Technology: Azure Database for PostgreSQL (Flexible Server & Single Server) Category: Connectivity | Performance | Application Design Introduction Connection pooling is a critical component of application performance when connecting to Azure Database for PostgreSQL. Creating a new PostgreSQL connection is an expensive operation that consumes CPU, memory, and networking resources. Reusing existing connections through a connection pool significantly reduces connection latency, improves throughput, and helps applications scale more efficiently. Many Java applications use HikariCP, one of the most popular high-performance JDBC connection pools. While HikariCP provides excellent performance out of the box, improperly configured connection pool settings can lead to issues such as: Connection pool exhaustion Stale or invalid connections Increased connection acquisition latency Excessive connection creation and destruction Database resource contention Application timeouts This article summarizes generic guidance and best practices for configuring HikariCP when working with Azure Database for PostgreSQL Flexible Server and Azure Database for PostgreSQL Single Server. Understanding Key HikariCP Parameters 1. Maximum Lifetime (maxLifetime) The maxLifetime property controls how long a connection can remain in the pool before HikariCP retires it and creates a new one. Why It Matters Connections can become stale over time due to: Network interruptions Infrastructure updates Connection state changes TCP idle behavior Recycling connections periodically helps prevent applications from using long-lived connections that may no longer be healthy. Recommended Practice Avoid configuring the value too low. When maxLifetime is set aggressively, HikariCP continuously destroys and recreates connections, resulting in: Additional authentication overhead Increased connection establishment latency Higher CPU utilization Reduced application throughput A reasonable starting point is: spring.datasource.hikari.maxLifetime=1800000 30 minutes (1,800,000 ms) is commonly used and aligns well with many production workloads. Depending on workload characteristics, values between 30 minutes and 1 hour are generally suitable Avoid maxLifetime=300000 (5 minutes) This often causes unnecessary connection churn without providing additional benefits. 2. Minimum Idle Connections (minimumIdle) The minimumIdle setting defines how many idle connections HikariCP should keep ready for immediate use. Why It Matters A pool with available idle connections can serve application requests immediately without waiting for new connections to be established. However, maintaining too many idle connections consumes unnecessary database resources. Recommended Practice For most workloads: minimumIdle = maximumPoolSize Or minimumIdle slightly lower than maximumPoolSize This ensures sufficient connections are already available during traffic spikes while avoiding excessive connection creation delays. Example maximumPoolSize=20 minimumIdle=15 Avoid maximumPoolSize=20 minimumIdle=20 only when the application experiences long periods of inactivity and conserving resources is more important than immediate responsiveness. 3. Idle Timeout (idleTimeout) The idleTimeout property determines how long an unused connection remains in the pool before being removed. Why It Matters Connections that sit idle for extended periods consume resources on both: The application server Azure Database for PostgreSQL However, removing idle connections too quickly causes the application to repeatedly establish new connections. Recommended Practice Keep the default value unless there is a specific requirement. spring.datasource.hikari.idleTimeout=600000 which equals: 10 minutes (600,000 ms) This setting provides a good balance between resource utilization and responsiveness. [Re: EXT: R...0040002947 | Outlook] The timeout should also be comfortably longer than any expected short application idle periods. Avoid idleTimeout=10000 (10 seconds) Such aggressive settings often result in unnecessary connection creation cycles. 4. Maximum Pool Size (maximumPoolSize) This parameter determines the maximum number of concurrent database connections the application can maintain. Why It Matters This is often the most important HikariCP setting. If the Pool Is Too Small Applications may experience: Connection is not available, request timed out because all available connections are already in use. Similar scenarios have been observed during customer investigations involving Hikari pool exhaustion. If the Pool Is Too Large Applications can overwhelm the database server with excessive concurrent sessions, resulting in: Connection contention Increased context switching Higher memory consumption Reduced overall performance Recommended Practice Pool size should be based on: Database compute configuration CPU core count Query execution duration Application concurrency requirements Workload characteristics There is no universal value that fits every workload. Start conservatively: maximumPoolSize=10 or maximumPoolSize=20 maximumPoolSize=20 and increase only after load testing demonstrates a need for additional concurrency. Fixed-Size Pool Recommendation For many production workloads, a fixed-size pool provides the simplest and most predictable behavior. Configure: maximumPoolSize=20 minimumIdle=20 or omit minimumIdle entirely so it defaults to maximumPoolSize. HikariCP commonly recommends maintaining a fixed-size pool for responsiveness during demand spikes. Benefits Faster connection acquisition Predictable performance Reduced connection creation latency Better handling of traffic spikes When using a small fixed-size pool, there is often little need to aggressively tune: minimumIdle idleTimeout Instead, simply recycle connections using: maxLifetime maxLifetime Additional Recommendations Enable TCP Keepalive One common cause of stale connections is network devices silently dropping inactive TCP sessions. For PostgreSQL applications, consider enabling TCP keepalive: tcpKeepAlive=true tcpKeepAlive=true The HikariCP project specifically recommends enabling TCP keepalive to prevent rare situations where pools can lose valid connections. Monitor Connection Usage Track: Active connections Idle connections Connection acquisition time Pool exhaustion events Database connection counts These metrics help identify whether pool sizing is appropriate. Investigate Long-Running Queries Connection pool problems are often symptoms rather than root causes. A frequent scenario is: A query becomes slow. Connections remain occupied longer. The pool becomes exhausted. Applications start timing out. When analyzing HikariCP issues, always review: Query performance Blocking situations Database resource utilization Application connection handling logic Sample Production Configuration spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.minimumIdle=15 spring.datasource.hikari.maxLifetime=1800000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.keepaliveTime=60000 spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.minimumIdle=15 spring.datasource.hikari.maxLifetime=1800000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.keepaliveTime=60000 This configuration provides a solid starting point for many Azure Database for PostgreSQL workloads and can be adjusted based on application-specific requirements. a { text-decoration: none; color: #464feb; } tr th, tr td { border: 1px solid #e6e6e6; } tr th { background-color: #f5f5f5; } Conclusion HikariCP is extremely efficient when configured appropriately. The goal is not to maximize the number of connections, but rather to maintain a healthy balance between application responsiveness and database resource consumption. As a general rule: Use a reasonable maxLifetime (30–60 minutes) Keep enough idle connections available for traffic spikes Avoid aggressive idleTimeout values Size the pool based on workload characteristics, not guesses Consider fixed-size pools for predictable performance Monitor connection usage and query performance regularly By following these practices, applications connecting to Azure Database for PostgreSQL can achieve improved scalability, lower latency, and more reliable connectivity. References Connection pooling best practices - Azure Database for PostgreSQL Performance best practices for using Azure Database for PostgreSQL – Connection Pooling HikariCP Documentation and Pool Sizing GuidanceAnnouncing new security, maintenance and analytics features for PostgreSQL at Microsoft Build 2026
At Microsoft Build 2026, we’re announcing a major wave of PostgreSQL innovation across Azure. Alongside the public preview of Azure HorizonDB, we’re delivering a broad set of enhancements for our fully managed open-source PostgreSQL service: Azure Database for PostgreSQL flexible server. These updates span performance, analytics, security, operations, resilience and migration - helping you build faster, operate with more control, secure your workloads, and modernize with confidence. Here’s a quick tour of the top flexible server announcements at Build 2026. Feature Highlights pg_duckdb Extension pg_ivm Extension Defender Security assessments temporal_tables Extension Cross-tenant CMK Automatic Entra token refresh libraries New Powershell module: Az.PostgreSQLFlexibleServer More control over planned maintenance Pre-Upgrade validation checks New Built-in Grafana dashboards Chaos Studio supports Azure Database for PostgreSQL AI-assisted Oracle to PostgreSQL migration Migration Service for Azure Database for PostgreSQL improvements (EDB, AlloyDB) Performance, Scale & Analytics pg_duckdb Extension Generally Available The pg_duckdb extension enables you to accelerate high-performance analytics and data-intensive applications with DuckDB’s SQL engine running inside your Postgres server. We’re pleased to announce pg_duckdb is now generally available in Azure Database for PostgreSQL. The latest version builds on the preview with the latest DuckDB engine improvements and optimized performance. This version adds vectorized execution for faster analytical queries, delivering significant improvements in aggregation performance, along with new support for writing to Azure Blob Storage and querying Parquet data directly from PostgreSQL. These capabilities enable high-performance analytics on your external data and simplify data processing workflows. Learn more: pg_duckdb. pg_ivm Extension Generally Available Materialized views are a useful way to optimize performance for queries that run regularly, but if underlying data becomes stale the result set needs to be recomputed. With the pg_ivm extension you can automatically maintain materialized views as the underlying data changes. This is particularly valuable for large datasets with small incremental changes that need real-time freshness, like dashboards, catalog analytics and SaaS usage reporting. We are pleased to announce the pg_ivm extension is now generally available in Azure Database for PostgreSQL. Learn more: pg_ivm. Security, Auditing & Identity Defender security assessments Preview Microsoft Defender Security Assessments for Azure Database for PostgreSQL enables continuous evaluation of your database security posture, helping identify vulnerabilities and misconfigurations across server and database configurations. Previously limited to reactive threat detection, in the latest preview release, Defender now provides proactive, risk-based insights through assessments tailored to PostgreSQL-specific best practices, delivering more relevant and actionable guidance. This helps you strengthen your security baseline, prioritize remediation, and align with best practices and compliance requirements. Learn more: https://aka.ms/Defender-Assessments-for-PG-Preview temporal_tables Extension Generally Available We’ve had many customer requests to support the temporal_tables extension, which provides built-in support for tracking and querying historical changes to data over time. Temporal tables are now generally available in Azure Database for PostgreSQL. With this extension enabled you can easily perform time-based queries, audit data changes, and maintain historical records without building custom tracking logic, simplifying application development and compliance scenarios. Learn more: temporal_tables Cross-tenant CMK Preview Azure Database for PostgreSQL now supports cross-tenant customer-managed keys (CMK) in public preview, allowing you to encrypt your data at rest using an Azure Key Vault key that resides in a separate Microsoft Entra tenant from the database service. This feature is designed for SaaS providers and enterprises that need to maintain strict separation of duties and ownership of encryption keys, enabling you to retain full control over key lifecycle management while PostgreSQL runs in a service provider’s tenant. Learn more: Data encryption at rest in Azure Database for PostgreSQL Automatic Entra token refresh libraries Preview We’re making it easier to use Entra ID authentication with Azure Database for PostgreSQL throughout the application stack by introducing new token refresh libraries for .NET, JavaScript, and Python. With Entra ID, access tokens are short-lived which can make managing their lifecycle complex in real-world applications. Developers need to be aware of token refresh and build additional handling around token expiration, connection retry, and session continuity. These new libraries remove that friction. By handling Entra token refresh seamlessly in the background, they allow applications to stay connected without interruption and with no custom logic required. The result is a simpler development experience and more resilient applications, especially for long-running or connection-heavy workloads. Across languages, the libraries provide a consistent and streamlined way to adopt secure, passwordless authentication, helping teams focus more on building their applications and less on managing authentication. Learn more: .NET, JavaScript, and Python. Operations, Maintenance & Monitoring New Powershell module: Az.PostgreSQLFlexibleServer Generally Available We’re excited to introduce the newly renamed Az.PostgreSQLFlexibleServer PowerShell module, delivering a streamlined experience for managing Azure Database for PostgreSQL with PowerShell. Building on the capabilities of the previous Az.PostgreSql module, the updated module aligns with the new features in the 2026-01-01 preview REST API. This module brings support for PostgreSQL 18, elastic clusters for scalable workloads and a range of enhancements designed to simplify management and improve performance. Whether you're provisioning new deployments or managing complex environments, this module ensures you can take full advantage of the latest platform capabilities directly from PowerShell. To learn more, visit our official documentation on PowerShell: Az.PostgreSql Module | Microsoft Learn More control over planned maintenance Generally Available We’ve seen many requests to provide more control when a maintenance update is applied to Azure Database for PostgreSQL. Sometimes when a critical workload is running you want to apply the maintenance when you’re ready. Announcing general availability this week, we’re building on the existing System and Custom maintenance window options and adding new self-service maintenance capabilities to the Azure portal. You can now reschedule upcoming maintenance updates for up to two weeks and apply maintenance on demand at a time that suits you. You can also view scheduled maintenance and review your server’s maintenance history after updates are complete. These options help you better align maintenance with your business schedules, reduce disruption during critical workload periods, and minimize the need for support-driven deferral requests. CLI and API support are coming soon. Learn more: https://aka.ms/azure-postgres-reschedule-maintenance Pre-Upgrade validation checks Preview Major version upgrades are critical for staying current with PostgreSQL features, security updates, and performance improvements, but you often discover blockers only after starting the upgrade workflow. Pre-Upgrade Validation Checks lets you validate upgrade readiness before initiating the actual upgrade by running Azure-specific upgrade checks and PostgreSQL pg_upgrade --check validations independently. The shift is simple: you can identify and fix upgrade blockers before the upgrade window begins. The feature surfaces actionable issues across configurations, extensions, dependencies, replication slots, event triggers, and other upgrade-sensitive objects. You can fix blockers, re-run validation until all checks pass, and proceed with the upgrade with greater predictability. Learn more: https://aka.ms/pg-flex-upgrade-checks New Built-in Grafana dashboards Generally Available Grafana dashboards are now built directly into the Azure portal for Azure Database for PostgreSQL - no setup, no extra cost, and no separate service to manage. You can open your PostgreSQL resource in the portal and immediately access prebuilt dashboards for key health and performance signals such as CPU, memory, storage, IOPS, connections, transactions, and availability. The key value is metrics + logs in one place. You can quickly correlate performance spikes with PostgreSQL logs, understand what changed, and troubleshoot faster using the familiar Grafana experience. Dashboards can also be customized, saved to your subscription, and shared across teams for ongoing operations. Learn more: https://aka.ms/azure-postgres-dashboards-grafana Resilience & Business Continuity Chaos Studio supports Azure Database for PostgreSQL Preview No matter how much you prepare, you only really know how good your database disaster recovery plan is when something breaks. With Chaos Studio support for Azure Database for PostgreSQL, you can simulate zone-down scenarios on PostgreSQL HA-enabled instances and validate the resilience of your mission-critical workloads. With Chaos Studio integration, you can proactively test failover behavior and gain confidence in how your applications respond to real-world zonal failures. This feature is currently available through a gated private preview. To get started, submit your subscription details using the form. Once reviewed, our team will enable the feature for your subscription, with guidance to help you begin testing. Getting started is simple: Create a Chaos Studio workspace via the Chaos Studio portal and configure your subscription, resource group, and region. Define the scope and assign the required managed identity and permissions. Review and verify your workspace setup. Browse available scenarios and select the PostgreSQL zone-down scenario. Configure the test (name, duration), then run it from My Library to begin validating failover behavior. With just a few steps, you’ll be able to simulate real-world failure conditions and gain confidence in your application’s resilience. To get started, please submit your details using this link: Private Preview Support for Chaos Studio Migration & Modernization AI-assisted Oracle to PostgreSQL migration Generally Available AI-assisted migration tooling has dramatically lowered the bar for moving between different databases and is changing the way people look at the return on investment for migration. The VS Code PostgreSQL extension comes with AI-Assisted migration tooling which converts Oracle schema and application code to Azure Database for PostgreSQL. This tooling uses GitHub Copilot, Microsoft Foundry, and custom Language Model tools to convert Oracle schema, database code and client applications into the PostgreSQL equivalents, and validates every change against a running flexible server instance. Learn more: Schema conversion, App conversion. Migration Service for Azure Database for PostgreSQL improvements (EDB, AlloyDB) Generally Available We’ve added AlloyDB and EDB Extended Server as new sources for migrating to PostgreSQL in the Azure Database for PostgreSQL Migration Service, with support for both online and offline migration support. Learn more: Migrate from AlloyDB, Migrate from EDB. Looking ahead That wraps up the Build 2026 announcements for Azure Database for PostgreSQL flexible server. There are also many great PostgreSQL technical sessions at Build this week, covering cloud-native app & AI development and migration. To find out more, here's a link to the Build session catalog for PostgreSQL sessions: https://aka.ms/Postgres-on-Azure_Build-2026. We'll continue to build out our roadmap over the coming months to deliver on your asks to improve the performance, security and stability of your PostgreSQL workloads. Check the Microsoft Blog for PostgreSQL for a regular monthly recap where we share the latest enhancements and product updates.SELECT * FROM build2026_sessions WHERE postgres = true;
Microsoft Build 2026 is around the corner, and this year it’s shaping up to be a big one for PostgreSQL experts and enthusiasts. If you’re a developer working with Postgres, or just love exploring new database technology, there's plenty to get excited about. Microsoft’s new cloud-first evolution of PostgreSQL, Azure HorizonDB, alongside sessions featuring Azure Database for PostgreSQL, will highlight how Postgres is powering the next wave of AI-driven applications. A new horizon in Postgres Build 2026 arrives at a time when the role of databases in modern apps is evolving rapidly. From enabling AI model integration to scaling seamlessly across the cloud, PostgreSQL developers today are dealing with more complex demands than ever. That’s why Azure HorizonDB – Microsoft’s new cloud-native PostgreSQL service – is generating so much buzz ahead of Build. What is Azure HorizonDB? In short, it’s a reimagined version of PostgreSQL designed for cloud-scale performance and AI-era workloads. Azure HorizonDB, introduces a distributed architecture that decouples compute and storage, delivering sub-millisecond latencies and three times the throughput of self-managed Postgres at massive scale. It aims to preserve Postgres’s beloved features and SQL ecosystem while adding next-generation capabilities: built-in vector indexing for high-speed AI/ML retrieval, the ability to run AI models and vector operations directly in the database, and multi-zone replication for resilience. For Postgres developers, this means less time stitching together external data stores or machine learning services – and more time building powerful apps on a unified platform that’s both familiar and built for the future. The bottom line: Microsoft Build 2026 is an ideal opportunity for developers to see Azure HorizonDB in action, learn best practices for modern PostgreSQL architectures, and understand how to leverage Postgres in new scenarios like generative AI and multi-agent applications. Read on for a rundown of sessions covering these topics, complete with what you’ll learn from each one. Top sessions for PostgreSQL databases on Azure Below are key sessions tailored for PostgreSQL users and those interested in Azure HorizonDB, with session types and highlights of what you’ll gain by attending. 🎤 Breakout: From Rows to Reasoning: Designing Databases for AI Apps and Agents (BRK223, 45 min, in-person and digital options) Discover how to architect databases that can power tomorrow’s intelligent applications. This technical breakout will show how AI-ready databases can move beyond plain transactions. You’ll see live demos of integrating transactional, analytical, and vector data in one unified platform, with Azure’s new database capabilities, including Azure HorizonDB. Learn how to simplify your stack by eliminating separate analytics engines or vector stores. The session will highlight patterns that reduce data movement and latency so your apps can efficiently reason over live data with minimal complexity. 🧪 Hands-on lab: Create Advanced Postgres-Powered Agentic Apps with Azure HorizonDB (LAB511, 75 min, in person and digital options) Roll up your sleeves and get hands-on building a real multi-agent AI application with Postgres. In this advanced lab, you’ll create a production-ready AI agent powered by Azure HorizonDB as an all-in-one data, search, and intelligence layer. Experiment with retrieval-augmented generation (RAG) by combining semantic vector search (DiskANN) with traditional SQL queries right inside the database. Implement hybrid search and agent workflows without resorting to external vector databases or glue code – thanks to HorizonDB’s built-in vector indexing and in-database AI model capabilities. This lab is perfect for developers who want to experience how HorizonDB can simplify your stack and boost performance for AI-driven apps. Multiple hands-on labs are offered to suite your schedule. 💻 Demo: Simplify App Dev with Cloud-Native PostgreSQL in Azure HorizonDB (DEM364, 25 min, in-person and digital options) See how to cut your development time and complexity with built-in AI and search features in Postgres. This fast-paced demo shows how Azure HorizonDB helps eliminate the need for separate search engines and AI services by pulling those capabilities straight into PostgreSQL. Expect to learn how you can run hybrid vector + keyword queries using SQL, integrate AI models directly from within the database, and apply full-text search (BM25) and semantic ranking to get smarter results. If you’re eager to deliver intelligent apps faster, with fewer moving parts, this session will show how HorizonDB simplifies your architecture without sacrificing performance. ⚡Lightning Talk: Cloud-Native PostgreSQL, Rebuilt for Scale: Azure HorizonDB (LTG413, 15 min, in-person only) Get a rapid-fire introduction to the architecture behind HorizonDB’s eye-popping performance. This short talk dives into how HorizonDB re-architects core PostgreSQL to deliver effortless scale out and blazing speed. Learn how decoupled compute and storage, predictive caching, and multi-region replication combine to achieve sub-millisecond query latencies and 3× higher throughput than standard Postgres. If you care about performance tuning and high-scale database design, don’t miss this quick primer on the tech under HorizonDB’s hood. 👥 Interactive Table Talk: Scaling PostgreSQL for AI Apps: Patterns and Tradeoffs (TT622, 45 min, in-person only) Bring your questions and ideas to this collaborative discussion. In this open round-table session with community and Microsoft experts, you’ll explore architecture patterns for scaling PostgreSQL to meet the demands of agent-based and AI-driven applications. Discuss real-world strategies for handling vector embeddings in Postgres, unifying relational and document data, integrating with AI models, and more. Compare the trade-offs between different scaling approaches – from monolithic to microservices, sharding strategies, and new technologies like HorizonDB – and learn where each design shines or struggles in production. Come ready to share your experiences and learn from others in the room. ▶️ On-Demand: Smarter PostgreSQL Migrations to Power Modern, Intelligent Apps (OD822, 30 min, digital only) Planning to migrate to Postgres or move your databases to Azure? Start here. This on-demand session focuses on new tools and proven strategies to migrate large-scale databases to Azure Database for PostgreSQL quickly and safely. You’ll see AI-assisted migration tools in action that minimize downtime and risk when moving terabytes of data. Just as importantly, you’ll learn how migrating to Azure unlocks advanced capabilities – from boosted performance and enhanced security to AI-ready features – helping you turn your newly migrated data into intelligent apps and services. On-demand session will be available to stream on the first day of Build. Meet the team: PostgreSQL expert meetups If you’re attending Build in person, stop by the Expert Meetup (EMU) area and head to the relational cloud databases booth. This is your chance to talk directly with the engineers and product teams behind PostgreSQL on Azure. Bring your questions about architecture decisions, scaling patterns, migrations, AI workloads, or anything else on your mind. Whether you want to sanity-check a design, dig deeper into something you saw in a session, or give direct feedback, the EMU space is designed for exactly that convo. How to get the most out of Build (and what to do next) With so much great content lined up, how do you decide where to start? It really depends on what you’re most excited about: Curious about AI and agentic apps: Start with From Rows to Reasoning, then go deeper with the Simplify App Dev with HorizonDB demo or get hands-on at the Azure HorizonDB labs to see how these ideas work in practice. Performance and scale are your focus: The short Lightning Talk on HorizonDB’s cloud-native architecture and the Table Talk on scaling Postgres will both provide unique insights and pro tips for running Postgres at massive scale. Planning a migration to PostgreSQL on Azure: Watch the Smarter PostgreSQL Migrations on-demand session to learn how to migrate large workloads with minimal downtime, and the benefits you can unlock after moving to Azure. Looking for real answers to your specific questions: Make time for the PostgreSQL Expert Meetup area to connect directly with the team. No matter which sessions you choose, Build 2026 promises to be an exciting event for the PostgreSQL developer community. Browse the session catalog, save the sessions that match your interests, and we’ll see you at Build.Real-World Success Stories with PostgreSQL on Azure
Organizations rarely leap into cloud migrations or AI-powered systems overnight. They progress in deliberate stages, establishing a reliable data foundation, optimizing for performance, and then accelerating innovation. Across healthcare, financial services, and AI startups, companies are navigating this journey on Azure Database for PostgreSQL: a fully managed, enterprise-ready PostgreSQL environment with 58% lower total cost of ownership (TCO) compared to on-premises deployments. This post walks through real customer stories that span the full arc, from lift-and-shift migration to production-grade AI agent development, illustrating how Azure Database for PostgreSQL supports scalability, performance, security, and AI-readiness at every stage. Migrating with Confidence: Apollo Hospitals & August AI Apollo Hospitals operates a network of more than 74 hospitals and needed to move beyond a legacy on-premises Oracle system that had become difficult to manage and couldn't keep pace with growing data volumes. IT teams were spending their time on maintenance rather than innovation. Apollo migrated its core hospital information system backend to Azure Database for PostgreSQL. Working with partner Quadrant Technologies, the team lifted and shifted critical applications while using Azure DevOps to orchestrate CI/CD pipelines and Azure Application Insights for telemetry and observability. The results: 99.95% availability across hospital systems Database transactions executing within 5 seconds 40% reduction in deployment times via modern CI/CD pipelines Decreased operational overhead, freeing IT staff for higher-value work With a stable, scalable PostgreSQL backend in place, Apollo is now exploring real-time analytics and AI-enabled tools like Microsoft 365 Copilot to advance patient care. "We saw Azure Database for PostgreSQL as the right foundation for the future. It's open, cost-effective, and capable of supporting the hospital information system we built in-house." — Shankar Krishna A., General Manager of IT, Apollo Hospitals Apollo's experience is not unique. August AI, a healthcare-tech startup offering an AI-driven medical companion, migrated its entire stack to Azure—with Azure Database for PostgreSQL storing mission-critical patient data while meeting strict compliance requirements such as HIPAA. The result: scaling from roughly 500,000 users to 3.5 million+ users worldwide, with zero downtime during the cutover, completed in just three months. As Founder and CEO Anuruddh Mishra noted: "We receive a log of queries that are not performing optimally, and within a couple of minutes we can optimize that query with PostgreSQL on Azure and move on". Modernizing at Scale: Nasdaq Migration is often the first step. Nasdaq demonstrates what becomes possible when organizations modernize their architecture on a scalable data foundation. To improve its Nasdaq Boardvantage platform—used by corporate boards to collaborate on governance documents—Nasdaq re-architected on Azure. The team containerized services with Azure Kubernetes Service (AKS) and adopted Azure Database for PostgreSQL alongside Azure Database for MySQL as persistent data stores for governance workloads. This architecture provided the flexibility, performance, and security required for a multitenant platform handling sensitive board materials. With the data layer in place, Nasdaq integrated Microsoft Foundry and Azure OpenAI to deliver AI-powered summarization and workflow automation. The measurable outcomes: 60% reduction in reading time through AI-powered document summarization 25% decrease in administrative preparation time across board workflows Up to 97% accuracy in AI-generated summaries and meeting minutes A reusable AI framework established for future extensibility "Both Azure Database for PostgreSQL and Azure Database for MySQL gave us the right balance of performance, security, and control. The governance workloads we handle are unique, so we needed something that could meet those isolation and encryption requirements." — Scott Ellison, Vice President of Technology, Nasdaq Building Intelligent Applications: SubgenAI and OpenAI Azure Database for PostgreSQL now supports native vector search via pgvector, high-performance DiskANN indexing, semantic operators and AI model management, and integrated graph capabilities for relationship reasoning—making it a production-ready foundation for intelligent applications. SubgenAI, a European generative AI company, built its flagship platform Serenity Star on Azure Database for PostgreSQL and Microsoft Foundry to transform AI agent development from a code-heavy, fragmented process into a streamlined, no-code experience. A core technical requirement: the platform's retrieval-augmented generation (RAG) system needs efficient vector search against embedded content while maintaining enterprise-grade reliability. After evaluating several database options, SubgenAI chose Azure Database for PostgreSQL with pgvector for its accurate and scalable vector similarity search. Serenity Star customers can now: Launch AI agents in as little as 15 minutes Cut coding and development time by 50% Resolve most AI agent queries in under 60 seconds [ "With Microsoft and Azure Database for PostgreSQL we have total control and an environment that is truly dynamic and can adapt to the evolution we're looking for." — Julia Schröder Langhaeuser, VP of Product Serenity Star, SubgenAI At the extreme end of scale, OpenAI runs PostgreSQL on Azure to support production systems behind ChatGPT. As write scalability limits emerged on an initially unsharded single primary instance, OpenAI offloaded write-heavy operations to other systems and optimized read workloads using PgBouncer for connection pooling. The Azure Database for PostgreSQL team responded by developing the elastic clusters feature, enabling horizontal scaling through row-based and schema-based sharding. The team reduced connection latency from approximately 50 ms to under 5 ms, scaled reads horizontally with multiple replicas, and improved reliability by prioritizing critical requests—all achieved by a small team making systematic optimizations on open-source PostgreSQL. "After all the optimization we did, we are super happy with Postgres right now for our read-heavy workloads. It's really scalable and reliable." — Bohan Zhang, Member of the Technical Staff, OpenAI Meeting You Where You Are Beyond these stories, organizations like BMW Group (cloud-native applications at global scale), Ahold Delhaize (highly available retail applications), Mott MacDonald (an AI agent accelerating onboarding and spreading best practices across 220,000 employees), and Multitude (scaling responsibly in regulated environments) all run on Azure Database for PostgreSQL. The service offers 99.99% availability with automatic failover and SLA, independent compute and storage scaling, and intelligent performance recommendations, available across 60+ Azure regions. Developer tooling including the PostgreSQL extension for Visual Studio Code with GitHub Copilot further accelerates productivity. Whether you are planning your first migration or building production AI agents, these stories share a clear signal: Azure Database for PostgreSQL delivers a scalable, secure, AI-ready data foundation at every stage of growth. Explore full customer stories in depth in the eBook: Customer Success Stories with Azure Database for PostgreSQL.
Native Database/Query Monitoring with Azure Database for PostgreSQL
Monitoring PostgreSQL Database Engine The PostgreSQL database engine provides multiple administrative views, which can be leveraged to capture metrics of activities inside of the database engine including, but not limited to, database activities, query activities, execution time, database resource locks etc for initial and deep analysis on database & query performance. Database Administrators should use these views and their corresponding metrics to perform initial level analysis. Critical insights can be gathered around database activity, performance and system activity for reactive and proactive analysis. In this blog, I am going to show the important administrative views and their corresponding SQL statements for capturing database and query details. The following section provides details about the statistics views and their usage. It contains the most important and mostly used statistics views, there are other views available that can be used to get into further details. NOTE: The queries and flows provided below are a few of the ways for each scenario. Eventually, the final approach will depend on and be dictated by the issue at hand or its symptoms. Pre-requisites The following parameters should be enabled in Azure Database for PostgreSQL “Server parameters” before using the statistics views: track_activities track_counts track_io_timing pg_stat_statements.track = ALL The following extensions should be installed before using the stats: create extension pg_stat_statements; create extension pgstattuple; NOTE: All SQL queries mentioned below are as per PostgreSQL version 18. Schema & Data for Analysis All the "Example Scenario" mentioned in the analysis queries are based on the following schema and corresponding volumetrics: Table Name No. of rows customer 993,814 invoice 20,229,119 orders 4,497,292 product 2,818,000 Statistics Views and SQL Queries for Analysis The following SQL statements can be used to perform 1 st level monitoring on the database, tables and queries. Note that the output counters and metrics from the SQL statements represent cumulative data since the last server restart. The statistics should be reset to zero before performing troubleshooting or performance tuning exercises. Use the below mentioned SQL statements to reset statistics at database, table and IO level. Database level stats reset SELECT pg_stat_reset(); IO level stats reset. SELECT pg_stat_reset_shared('io'); Reset pg_stat_statements metrics per user, query and database id or for the entire database - pg_stat_statements contain query level metrics for a database, user or a single query. We can reset the statistics at these individual levels. If any of the parameters are not specified, the default value 0 is used for each of them and the statistics that match with other parameters will be reset. "userid", "dbid" (Database ID), "queryid" can be fetched from pg_roles (column - oid), pg_stat_database (column - datid) and pg_stat_activity (column - query_id) respectively. SELECT pg_stat_statements_reset(userid, dbid, queryid); SELECT pg_stat_statements_reset(); SQL Activity Monitoring: This query uses pg_stat_activity statistics view, which contains details of current activities in the database server. It can be used to track queries in transactions, transaction execution time, wait event details if any etc. The WHERE clause can be used to filter activities based on client IP address (client_addr), application name (application_name), user (usename) who has submitted the query and text of the SQL statement. SELECT datname AS dbname, pid, usename as username, application_name, client_addr as client_ip_addr, xact_start AS transaction_start_time, query_start AS query_start_time, wait_event_type AS db_waitign_on, wait_event AS waiting_activity, CAST(query AS varchar(100)) AS sql_query, backend_type AS db_operation FROM pg_stat_activity WHERE <conditions>; Interpreting output: This output should be used to find overall activity in your database. username, application_name, client_ip_addr - The user, application and client IP address from where the query is being executed. transaction_start_time - When transaction started (using BEGIN keyword or corresponding API). query_start_time - Start time of currently active query in the transaction. wait_event and wait_event_type columns output the events on which the query is waiting, if applicable. Refer to the official links for details on wait events and wait event types. db_operation - Type of database operation that is being performed by the application. E.g. autovacuum, parallel worker, client backend, etc. Example Scenario: Application team is complaining about higher load in "retail_db" database and it is causing multiple SQL statements to run slow, team wants to know whether ad-hoc SQL statements are being run using "psql" client by application developers. As a DBA, you want to look at the entire database activity - no. of SQL statements, who are running to SQLs and which client are they coming from. Above mentioned SQL can be used with filters on "application_name" (value = "psql") and "dbname" (value = "retail_db"). It generates the following output: Analysis: From the above output, it can be seen that "psql" client is indeed getting used from a few IP addresses. Next Step: pg_terminate_backend(<pid>) can be used to kill the applications with pid 22210, 22209 and 21993. Similarly, the same SQL statement can be used to look at overall SQL activities in individual database. SQL Statement Level monitoring This is one of the most important SQL statements that describes operations at query level. It provides execution time detail of queries, read time, write time, disk read and disk write. Output of this query should serve as one of the starting point of query troubleshooting and query optimization. SELECT queryid , CAST(query AS varchar(100)) AS sql_stmt, calls AS no_of_executions, total_exec_time, min_exec_time, max_exec_time, mean_exec_time AS avg_exec_time, rows AS rows_fetched, shared_blks_hit AS blocks_read_from_buffer, shared_blks_read AS blocks_read_from_disk, shared_blks_written AS blks_written_to_disk, shared_blk_read_time, shared_blk_write_time FROM pg_stat_statements WHERE query LIKE '%<sql-stmt>%'; Interpreting output: Output of this query can be used to dive deeper into SQL performance issues. sql_stmt - Provides the SQL statement that is being run. This is the query that is being analyzed for tuning. no_of_executions - How many times this SQL statement has been executed so far (since the reset of pg_stat_statements). total_exec_time - This is the execution time for the query combining all runs. e.g. if the query was run 10 times and each time took 1.5s, then total_exec_time would be 15s. min_exec_time - Min. time the query took to execute. This can be considered as the benchmark execution time for this query. max_exec_time - Max. time the query took to execute. avg_exec_time - Avg. execution time of the query. This time should fall within the query SLA. rows_fetched - No. of rows needed by application (SQL query) blocks_read_from_buffer & blocks_read_from_disk - Data blocks (8KB size) read from PostgreSQL shared memory and from disk respectively. shared_blk_read_time & shared_blk_write_time - Total time spent in reading from and writing to disk. This time is included in total_exec_time. If ratio of shared_blk_read_time or shared_blk_write_time or both with total_exec_time is higher, then IO is causing high execution time in this query. If the ratio is less then other complex SQL operations like join, order by, group by, functions etc. are taking longer to execute. Example Scenario: We will be using the same SQL statement that was used in the example scenario of "3. Application-wise IO Activities". But in this case, using "SQL Statement Level monitoring" we will look at the execution details of the query rather than IO usage. Following is the output of first execution (after server restart - shared memory was empty) of the SQL statement Analysis: Here is the observation, execution time of the query was 154.81 seconds. Total rows needed by application was 4,497,292 out of which 341 8KB blocks were read from shared memory (PostgreSQL bufferpool) and 58504 8KB blocks were read from disk. Following is the output after 2nd execution (after resetting pg_stat_statements): Analysis: The query was executed 4 times. Execution time got reduced to 95 seconds, but it is still high. Even though all rows (4497292 x 4) were read from shared memory (buffer pool) as blocks_read_from_disk was zero, execution time is very high. Next Step: Even if the execution time is found to be within SLA, the query needs to be analyzed for its execution plan. Use EXPLAIN ANALYZE command to analyze the access plan or execution path of the query. Query optimization must be done for this SQL statement. Table-level Bloat Statistics PostgreSQL uses MVCC (Multi Version Concurrency Control) to avoid read and write conflicts, which means one application can read rows while they are being modified by another application. This is done by storing multiple versions of the same row. But over time older versions, which are not needed by applications, become overhead as they occupy space. This is known as "Bloating". It can cause both storage and performance issue. The query mentioned below provides bloating percentage on a table, which should be monitored on a regular basis and DBAs should decide on the autovaccuum settings. SELECT (table_len/1024/1024) AS table_size_mb, tuple_count AS no_of_live_rows, (tuple_len/1024/1024) AS live_row_size_mb, dead_tuple_count AS no_of_dead_rows, (dead_tuple_len/1024/1024) AS dead_row_size_mb, tuple_percent AS live_rows_percent, dead_tuple_percent AS dead_rows_percent, free_percent AS free_space_percent, (((table_len – tuple_len)/table_len)*100) AS bloat_percent FROM pgstattuple('<table-name>'); Interpreting output: table_size_mb - This is the total table size, including bloating. no_of_live_rows - No. of actual usable rows in the table. live_row_size_mb - This is the actual size of the table. This is the amount of data in this table that can be used by applications. no_of_dead_rows - No. of irrelevant rows in the table. dead_row_size_mb - Size of irrelevant rows in the table. This is the amount of data that is not needed by any application, but is still there in the table and occupying storage. This is the data that has to be removed from the table. bloat_percent - Percentage of dead rows occupying the table. If it goes beyond 25-30%, manual VACUUM should be used to remove bloating. Example Scenario: invoice_history table contains more than 20 M rows and has a size of 1630 MB. Deleting records from a table does not reduce the size in PostgreSQL because of MVCC support to keep multiple versions of the same row. The deleted rows still occupy storage space, it is known as bloating as shown below. "Table level Bloat Statistics" can be used to find the bloat percentage of a table. Next Step: Aggressive AUTOVACUUM has to be configured to remove bloating on time. As AUTOVACUUM does not free up storage space, manual FULL VACUUM has to be executed to reclaim storage space. Note that full vacuum takes exclusive lock on tables. Bloat percentage changed after executing full vacuum. Table Bloat using Stat table Table bloating information using statistics table. SELECT schemaname, relname AS tblname, n_live_tup AS live_rows, n_dead_tup AS dead_rows, ((n_dead_tup - n_live_tup)/100) AS tbl_bloat_percent, last_vacuum AS last_manual_vacuum, last_autovacuum FROM pg_stat_all_tables WHERE relname = '<tblname>'; Interpreting output: Same as "Table-level Bloat Statistics". Database Activity Monitoring This SQL statement provides row level counters of DML statements for a database. SELECT datname AS dbname, blks_read AS total_no_of_read_ops, blks_hit AS no_of_blks_read_from_bufferpool, tup_returned AS no_of_rows_scanned, tup_fetched AS no_of_rows_fetched, tup_inserted AS no_of_rows_inserted, tup_updated AS no_of_rows_updated, tup_deleted AS no_of_rows_deteled, deadlocks AS no_of_deadlocks, (blk_read_time/1000) AS blk_read_time_s, (blk_write_time/1000) AS blk_write_time_s FROM pg_stat_database; Interpreting output: Output shows the impact of DML operations in a database. total_no_of_read_ops - This is the total block read request executed by the database engine. no_of_blks_read_from_bufferpool - Out of total read requests, how many blocks were already there in memory or shared_mem. no_of_rows_scanned & no_of_rows_fetched - Total no. of rows read from the database and the total no. of rows actually needed by application. Higher difference in the two metrics should call for query tuning using better indexes, join strategies etc. blk_read_time_s & blk_read_write_s - Time taken in seconds, to perform read and write operations. no_of_deadlocks - Higher no. of deadlocks should be investigated. Execution of application SQL queries might have to be re-looked if higher deadlocks create performance or application issues. Index-level Bloat Statistics – Live rows, dead rows, bloat This is similar to query no. 8, but it provides bloating information at index level. SELECT (index_size/1024/1024) AS idx_size_mb, (index_size/8192) AS index_pages, empty_pages, deleted_pages, ((index_pages - deleted_pages)/index_pages) * 100 AS idx_bloat_percent FROM pgstatindex('<idx-name>'); Interpreting output: The output is similar to table-level bloat statistics, but it is for indexes. Monitor IO activities Output of the following SQL statement shows the overall IO (Input/Output) activities for a database. It can be used to infer whether database workload is read heavy or write heavy. For read workloads, database level bufferpool_hit_ratio metrics can be used to increase or decrease shared memory configuration parameter. SELECT backend_type , object, context AS reason_for_io, reads AS no_of_reads, (read_bytes/1024) AS read_data_kb, read_time AS read_time_ms, writes AS no_of_writes, (write_bytes/1024) AS write_data_kb, write_time AS write_time_ms, writebacks AS no_of_blocks_to_storage, writeback_time AS block_to_storge_write_time_ms , hits AS no_of_reads_from_bufferpool, 100 * (hits / (hits + reads)) AS bufferpool_hit_ratio FROM pg_stat_io; Interpreting output: The output showcases database level IO metrics. backend_type - Type of database operation that is being performed by the application. E.g. autovacuum, parallel worker, client backend, etc. reason_for_io - corresponding IO operation. Following are the possible output values to look out for. normal - Read from or write to shared buffers. vacuum - IO used by VACUUM operation. bulkread & bulkwrite - Disk read and write operations. no_of_reads - Total read operations. read_data_kb - Amount of data read (in KB) across all operations. no_of_writes - Total write operations. write_data_kb - Amount of data written (in KB) across all operations. read_time_ms & write_time_ms - Duration of all read & write operations. Example Scenario: Extending the previous example, application team is complaining about overall performance of the database. As a DBA you want to find out the overall read and write operations to confirm whether it is causing slowness in the database. It will give an idea about how busy the database has been. "Monitor IO Activities" SQL can be used to find out the trigger of higher IO activities in the database. Following is the output: Analysis: Output above shows that most of the read and write operations are emanating from applications ("backend_type" = "client backend"). This gives an idea of database usage by the applications. Next Step: If resource utilization of this database is high, we can create read replica to offload the reads and increase server memory and CPU to handle the load. Or else, if possible, application throttling can be infused by reducing the no. of max connections or using application level throttling. Application-wise IO Activities Query no. 2 above provides database level IO, the following SQL statement can be used to track application level IO summary. As there is to one-to-one mapping between application id in pg_stat_activity and pg_stat_io, backend_type is the only way to relate these two admin views. SELECT psa.datname AS dbname, pid, psa.usename AS username, psa.application_name, psa.client_addr as client_ip_addr, psa.xact_start AS transaction_start_time, psa.query_start AS query_start_time, psa.wait_event_type AS db_waitign_on, psa.wait_event AS waiting_activity, CAST(psa.query AS varchar(100)) AS sql_query, state as current_state, psa.backend_type AS db_operation, psi.context AS reason_for_io, psi.reads AS no_of_reads, psi.writes AS no_of_writes FROM pg_stat_activity psa JOIN pg_stat_io psi ON psa.backend_type = psi.backend_type WHERE psa.query LIKE '%<sql-stmt>%'; Interpreting output: username, application_name and client_ip_addr column output can be used to track user who submitted the query, application from where the query was submitted and IP address of the application server from where query was executed. transaction_start_time & query_start_time show the execution start time of transaction and currently active query inside of the transaction. These metrics should be used to find the execution time of transaction and queries inside of the transaction. wait_event and wait_event_type columns output the events on which the query is waiting, if applicable. Refer to the official links for details on wait events and wait event types. reason_for_io column outputs corresponding IO operation. Following are the possible output values to look out for. normal - Read from or write to shared buffers. vacuum - IO used by VACUUM operation. bulkread & bulkwrite - Disk read and write operations. no_of_reads & no_of_writes - Total no. of read and write operations by the query. Example Scenario: So far we have been looking at database-level activities. In most of the cases, we need to track application level activities like read, write operations, application elapsed time (how long the query is getting executed for). These are some of the critical information that DBA should use while troubleshooting query performance or application slowness issue. Whereas pg_stat_statements should be the go to place to look at SQL or application-level statistics, above SQL statement can be used to monitor application level IO operations. Executed SQL statement : SELECT c.cust_id, c.cust_email, c.cust_type, o.order_no, o.order_date FROM customer c, orders o WHERE c.cust_id = o.cust_id ORDER BY o.order_date DESC; Output of the query can be filtered using partial SQL statement in the WHERE clause or using PID of the SQL statement as shown below. Following is the output of the query above (showing only relevant columns): Analysis: Highlighted output above shows 3 types of IO operations - normal, bulkwrite and bulkread. "normal" means that client is reading from buffer, but "bulkread" and "bulwrite" indicates read from disk and write to disk (temporary file write for sorting!). Next Step: SQL statement should to be analysed further for index usage, high disk read and write IO. EXPLAIN (with ANALYZE) statement can be used to understand query behaviour. Other monitoring SQL statements should be used to dig deeper into the execution details. Table & Index Operations The following SQL statement can be used to capture table-level DML operations and its corresponding index read operations. Output of this query can be used to create index if sequential scans on a table is high, also, how an application is impacting the table rows using INSERT, UPDATE, DELETE statements. SELECT t.schemaname AS tabschema, t.relname AS tabname, t.seq_scan AS no_of_seq_scan, t.seq_tup_read AS seq_scan_rows_read, t.n_tup_ins AS rows_inserted, t.n_tup_upd AS rows_updated, t.n_tup_del AS rows_deleted, t.n_live_tup AS total_active_rows, t.n_dead_tup AS total_dead_rows, i.schemaname AS idxschema, i.indexrelname AS idxname, i.idx_tup_read AS idx_rows_read, i.idx_tup_fetch AS idx_rows_fetched FROM pg_stat_all_tables t JOIN pg_stat_all_indexes i ON t.relid = i.relid AND t.relname = i.relname ; Interpreting output: It is almost similar to database activity monitoring except for the following columns. total_dead_rows - Total no. of rows that are not relevant for any transaction in PostgreSQL. total_active_rows - Total no. of actual rows in the table. If the ratio of dead rows vs active rows is higher, it shows VACUUM is not working properly. It can be fixed by running manual vacuum or changing the configuration of AUTOVACUUM. Table level Read IO This SQL statement provides critical metrics on read operations for all or individual tables. heap_blks_read and heap_blks_hit should be used to find out the disk read vs memory read in tables. Higher disk read indicates memory crunch and can be a factor in deciding whether to increase shared memory size. SELECT schemaname AS tabschema, relname AS tabname, heap_blks_read AS no_of_tab_blks_read, heap_blks_hit AS no_of_buffer_blks_read, idx_blks_read AS no_of_idx_blks_read, idx_blks_hit AS no_of_buffer_idx_blks_read FROM pg_statio_all_tables WHERE relname = '<table-name>'; Lock Information Data modification by applications or system commands locks rows and tables. Other application trying to modify the same row will get into waiting state thereby increasing the response time. Following SQL statement can be used to find out what type of locks is being held by applications on all or specific tables. SELECT db.datname AS dbname, tbl.relname AS tblname, lck.pid, lck.locktype , mode AS lock_mode, CASE lck.granted WHEN True THEN 'lock_held' WHEN False THEN 'lock_waiting' ELSE 'not_known' END AS lock_status, lck.waitstart AS lock_wait_start_time FROM pg_database db JOIN pg_locks lck ON lck.database = db.oid JOIN pg_class tbl ON lck.relation = tbl.oid WHERE tbl.relname NOT LIKE '%pg_%' AND tbl.relname = '<tablename>'; Interpreting output: Output rows contain database name, table name, process id of the query and the following fields. locktype - Object of the lock. E.g. table, page, row (tuple) etc. lock_status - Whether the lock has been granted to this application or application is waiting for lock. lock_wait_start_time - If lock waiting state, then when the wait was started. Example Scenario: Following SQL statement takes a lock on "orders" table as it is getting executed inside of a transaction which has not been either committed or rolled back. "Lock Information" SQL can be used to find out who is holding lock on a table as shown below: Next Step: If lock needs to be released then the application with pid 15505 must be terminated using pg_terminate_backend(15505). Who is Holding and who is Waiting for Locks If locks are being held long enough to block a critical application, it becomes imperative to terminate the application holding the lock. Following SQL statement provides information about applications waiting for locks and applications holding those locks so that necessary actions can be taken by DBA. WITH lock_holder AS (SELECT db.datname AS dbname, tbl.relname AS tblname, pid, locktype , mode AS lock_mode FROM pg_database db JOIN pg_locks lck ON lck.database = db.oid JOIN pg_class tbl ON lck.relation = tbl.oid WHERE granted = true), lock_waiter AS (SELECT db.datname AS dbname, tbl.relname AS tblname, pid, locktype , mode AS lock_mode FROM pg_database db JOIN pg_locks lck ON lck.database = db.oid JOIN pg_class tbl ON lck.relation = tbl.oid) SELECT lh.pid AS pid_holding, lh.locktype AS holding_lock_type, lh.lock_mode AS holding_lock_mode, lh.tblname AS holding_lock_table, lw.pid AS pid_waiting, lw.locktype AS waiting_lock_type, lw.lock_mode AS waiting_lock_mode, lw.tblname AS waiting_lock_table FROM lock_holder lh JOIN lock_waiter lw ON lh.tblname = lw.tblname WHERE lw.tblname='<tbl-name>'; Interpreting output: This is an extension of query 11 above. Following are the output columns: pid_holding - Application that is holding lock. holding_lock_type - Object (table, page, row) that has been locked. holding_lock_mode - Lock mode that is being held. holding_lock_table - Table that is being locked. pid_waiting - Application that is waiting for lock. waiting_lock_type - Object (table, page, row) that is waiting for lock. waiting_lock_mode - Lock mode that is needed. waiting_lock_table - Lock on table that is waiting. Example Scenario: There can be scenarios where multiple applications can take write lock on the same rows at the same time. Depending on lock_timeout, one of the applications has to wait for that duration. Criticality of an application might dictate the terms of waiting on locks and DBAs have to terminate application holding the lock. Following is an application that is updating rows in a transaction, but has not released the lock. And following application has timed out because of lock wait. Next Step: Using above query we can find out which application is waiting and who is holding lock on a table as shown below. Depending on priority one of the applications can be terminated by DBA. Diagnostic Flow: The following chart can be used to decide which admin view to use and when. It also shows how Azure Monitor and PostgreSQL admin views work in tandem at any analysis scenarios. Summary: PostgreSQL provides helpful administrative views that can be used by DBAs (Database Administrators) to perform initial level of analysis at engine side before looking at any infrastructure level issues. The initial analysis can be useful in relating infrastructure issues with database performance as well, if there is any.
End-to-end workload observability with Query Store for primary and replicas
Query performance doesn’t stop at the primary Most PostgreSQL architectures don’t run on a single node anymore. Reads get offloaded. Replica chains grow. And when performance issues hit, the hardest part is often simple: where did the queries actually run? With the latest query store capabilities in Azure Database for PostgreSQL flexible server, you can now capture workload executed not just on the primary, but also on read replicas—including cascading read replicas—and export the captured runtime stats, wait stats, and query text into Azure Monitor Logs (Log Analytics workspace / LAWS). See the real hotspot: isolate which node (primary vs replica) is slow. Know why: break down time by waits (CPU, I/O, locks) per query. Connect the dots: correlate query IDs to query text, and inspect sampled parameters locally in azure_sys on the primary when you need input context (parameters aren’t exported to LAWS). Centralize analysis: query everything with KQL in LAWS, across servers. What you’ll build This post walks through a reproducible demo that provisions a primary server, a read replica, and a cascading read replica, then runs a TPC-H–based workload across all three to generate query store data you can analyze locally and in Log Analytics. Enable query store capture (including query text) and parameter sampling for parameterized queries. Enable wait sampling so query store can record wait statistics. Export runtime stats, wait stats, and SQL text to LAWS using resource-specific tables. Validate capture on read replicas and cascading read replicas (not just the primary). Prerequisites Azure CLI logged in (az login) and permission to create a resource group, Log Analytics workspace, and PostgreSQL flexible servers. psql and curl available on your machine. PostgreSQL flexible server on General Purpose or Memory Optimized tier (query store and replicas aren’t supported on Burstable). PostgreSQL 14+ to test out cascading replicas. Networking: the script opens firewall access broadly for demos—tighten for production. Architecture (primary + replica chain + LAWS) You’ll deploy four resources: Primary server: read/write node. Read replica (level 1): read-only node created from the primary. Cascading read replica (level 2): read-only node created from replica level 1. Log Analytics workspace (LAWS): central place to query Query Store telemetry across all nodes. If Diagnostic Settings is properly configured, each server streams query store telemetry to LAWS—but how it’s kept locally differs by role. On the primary, query store data is recorded in-memory, then persisted locally in the azure_sys database, and then exported to LAWS. On read replicas (including cascading replicas), query store data is recorded in-memory only and then exported to LAWS. Bottom line: use LAWS for fleet-wide visibility, and use the primary’s azure_sys when you need deep local inspection (like parameter samples). Deploy the demo environment The fastest way to reproduce the scenario is to run the end-to-end bash script which you can download from https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-query-store/refs/heads/main/may2026/script/query_store_demo.sh Save the file to a local directory in your Linux shell, and name the file query_store_demo.sh. To invoke the script, at minimum, you must assign a string password for the administrator login of the instances of the flexible servers it creates, and invoke the script like this: ADMIN_PASSWORD=<Your_Strong_Password> ./query_store_demo.sh Optionally, you can also override default values for other environment variables used by the script: Variable Purpose Default SUBSCRIPTION_ID Azure subscription ID to use (current default subscription) BASE_NAME Base name for all resources (used in naming servers, resource groups, etc.) pgqswait{YYYYMMDDHHMMSS} RESOURCE_GROUP Azure resource group name rg-{BASE_NAME} LOCATION Azure region for resources southeastasia PRIMARY_SERVER Name of primary PostgreSQL server {BASE_NAME}-primary REPLICA_1 Name of first-level read replica {BASE_NAME}-readreplica REPLICA_2 Name of second-level cascading read replica {BASE_NAME}-cascadereadreplica LOG_ANALYTICS_WORKSPACE Log Analytics workspace name law-{BASE_NAME} LOG_ANALYTICS_LOCATION Azure region for Log Analytics workspace southeastasia ADMIN_USER PostgreSQL admin username pgadmin ADMIN_PASSWORD PostgreSQL admin password (REQUIRED) SKU_NAME PostgreSQL server SKU (compute tier) Standard_D4ds_v5 TIER PostgreSQL pricing tier GeneralPurpose STORAGE_SIZE Storage size in GB 64 VERSION PostgreSQL version (minimum 14 for cascading replicas) 17 PRIMARY_DATABASE Initial database name postgres SQL_BASE_URL Base URL for downloading SQL scripts https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-query-store/refs/heads/main/may2026/script/query_store_demo.sh TPCH_DDL_URL URL for TPC-H schema DDL file {SQL_BASE_URL}/schema/tpch_ddl.sql WORKLOAD_REPETITIONS Number of times to execute each workload query (minimum 5) 10 AUTO_APPROVE Skip confirmation prompt and proceed automatically false If, for example, you want to not only pass the ADMIN_PASSWORD but also override the LOCATION, you could do it like this: ADMIN_PASSWORD=<Your_Strong_Password> LOCATION=canadacentral ./query_store_demo.sh In a bit over 1 hour, the script will do the following steps: Step 1 — Provision first part of the infrastructure The infrastructure provisioned in this phase consists of: A resource group in which all resources are deployed. An instance of Log Analytics workspace, where all flexible server instances will send their query store related logs. A primary (read-write) flexible server. Step 2 — Configure primary server Now it's time to configure one new server parameters on your primary server so that query store emits query text to LAWS, so that we can correlate quey IDs to something recognizable. Query IDs are great for aggregation—but you still need the SQL. Turn on query text emission so you can correlate runtime and waits back to the actual statement text. Do this by setting pg_qs.emit_query_text to on. Refer to our documentation to learn how to set the value of a server parameter. Step 3 — Provision second part of the infrastructure The infrastructure provisioned in this phase consists of: A read replica (read-only) whose source is the primary server. A cascade read replica (read-only), whose source is the previously created read replica. Notice that when read replicas are created, they inherit the server parameter values from their source server. Because we have configured query store related settings on the primary server already, the intermediate read replica inherits its server parameters from that primary, and the cascade read replica inherits them from the intermediate replica. Step 4 — Export query store to Log Analytics (LAWS) Now for the payoff, we want to stream the data to Log Analytics so you can query across nodes, build dashboards, and alert. The script configures diagnostic settings on the primary and both replicas to send logs to a Log Analytics workspace using resource-specific tables. This is the key to cross-node visibility: each server exports its own captured telemetry, and you can slice by resource in a single KQL query. Query store runtime stats: execution counts, elapsed time, and other performance counters. Query store wait stats: wait breakdown attributed to queries. Query store SQL text: query text to decode query IDs. Note: Query store parameter samples are not included in the Log Analytics export. Parameters are stored locally per server in azure_sys, and on read replicas azure_sys is read-only—so don’t depend on replicas for parameter inspection. LAWS receives runtime stats, wait stats, and query text. Diagnostics settings for an instance of flexible server can be configured via portal. In the resource menu, under Monitoring, select Diagnostic settings. Add a new diagnostic setting, select a destination Log Analytics workspace, and the individual log catergories which you want to stream to that LAWS, and save the changes. For Destination table it's highly recommended to use Resource specific (one table per signal with proper schema) over Azure diagnostics (legacy one table for everything). With Azure diagnostics, all logs from all resource types land into a single table (AzureDiagnostics). It's a wide table with many columns. New columns get added as services emit new fields. If the 500 column limit is hit, extra fields go into the AdditionalFields column (a dynamic JSON). Querying on attributes stored in that column might have huge performance and query cost impact. The schema is inconsistent and difficult to discover. You must always filter events in that table by ResourceType and Category. On the other hand, with Resource specific, logs are written to separate tables per resource type and category. Therefore, each table has a well-defined schema and columns are strongly typed. Tables are smaller and faster to query. Queries on these tables are simpler don't need filtering by ResourceType and Category. Performance-wise, they also support faster ingestion and faster querying. They also support selecting different table plans and retention settings for each table. And, more importantly, role-based access control (RBAC) permissions can be applied at table level, allowing you to control access to telemetry in a more granular way. Note: If you want to see any of the images in this article in better quality, click on them to see them in their original size. This can also be configured using Azure CLI command az monitor diagnostic-settings create. Make sure that the --export-to-resource-specific parameter is set to true, which is the equivalent of selecting Resource specific for Destination table in portal UI. Setting this parameter to false, would mean that you want to use AzureDiagnostics as the destination table, which we don't recommend using. Step 5 — Run some workload In this phase the script loads a TPC-H schema and executes workload SQL across different nodes so that you can prove replica capture. Query it in Log Analytics Once the workload completed and data was streamed to Log Analytics, you can open your Log Analytics workspace, and start querying the relevant tables. If you don't know how to start issuing queries in a Log Analytics workspace, refer to Get started with log queries in Azure Monitor Logs. In your Log Analytics workspace, when you select Logs in the resource menu, you can access the Queries hub. By default, it should open automatically unless you have configured it to not show, in which case you can open by selecting Queries hub on the top right corner of the Logs home screen. If you add a filter in the queries hub for Resource type equals Azure Database for PostgreSQL Flexible Server, you'll be able to access multiple examples of queries which might help you get started querying the log categories we support for our service. You can run any of them by selecting Run on the summarization card that describes the query or, if you hover the mouse over the card, you can select Load to editor so that the query is copied over to the active query window, and you can run it or modified it further. Following, there are a few more query examples which can be useful to analyze the workload executed in this experiment. Top queries by total time (across all nodes) To get the list of 10 queries with higher duration from the ones that ran on any of the three nodes. KQL PGSQLQueryStoreRuntime | summarize total_time_ms = sum(TotalExecDurationMs) by QueryId, LogicalServerName | top 10 by total_time_ms desc Results Important: Results might be slightly different on each execution of the experiment. Where queries wait on each node List the most frequent wait events observed on user initiated queries across all nodes. KQL PGSQLQueryStoreWaits | join kind=inner (PGSQLQueryStoreRuntime) on QueryId | summarize total_waits_sampled = sum(Calls) by Event, EventType, LogicalServerName | order by total_waits_sampled desc Results Important: Results might be slightly different on each execution of the experiment. Decode query IDs (join runtime stats with SQL text) Top 20 queries the most frequent wait events observed on user initiated queries across all nodes. KQL PGSQLQueryStoreRuntime | join kind=inner (PGSQLQueryStoreQueryText) on QueryId | where QueryType == 'select' | project LogicalServerName, QueryId, TotalExecDurationMs, QueryText | top 20 by TotalExecDurationMs desc Results Important: Results might be slightly different on each execution of the experiment. Compare primary vs replicas (workload distribution) Find total number of query executions and accumulated duration of all those executions for each node. KQL PGSQLQueryStoreRuntime | summarize execs = sum(Calls), total_time_ms = sum(TotalExecDurationMs) by LogicalServerName | order by total_time_ms desc Results Important: Results might be slightly different on each execution of the experiment. Replica-only hotspots (find what’s slow off the primary) Find top 10 queries executed by their aggregated duration, focusing on what was executed on read replicas only. KQL let Replicas = dynamic(["pgqswait20260505220501-readreplica", "pgqswait20260505220501-cascadereadreplica"]); PGSQLQueryStoreRuntime | where LogicalServerName in (Replicas) | summarize total_time_ms = sum(TotalExecDurationMs) by QueryId, LogicalServerName | top 10 by total_time_ms desc Results Important: Results might be slightly different on each execution of the experiment. QPI now supports query store stats collected on replicas You can now use Query Performance Insight workbooks to analyze query store information not only on your primary server, as you were used to, but you can also get that valuable information on your read replicas. Why replica workload capture is a big deal This is the unlock: you can now answer performance questions in replica-heavy architectures without stitching together partial signals. Per-node truth: see the slow queries on the node where they actually ran (primary vs replica vs cascading replica). Faster root cause: runtime + waits gives you “slow” and “why” in one place. Replica tuning that sticks: identify replica-specific bottlenecks (I/O saturation, lock waits, CPU pressure) and tune with evidence. Centralized observability: export to LAWS so you can build dashboards, alerts, and cross-server comparisons with KQL. Unlock query visibility: Access query text without database permissions. Fine grain control on who can view query text: Using resource specific tables in LAWS, you can decide which users can access the table in which text of the queries is kept. Parameter-aware debugging: sampled parameters can help reproduce issues and explain plan changes, but they’re stored locally in azure_sys and not exported to LAWS. In practice, rely on the primary for parameter inspection (replicas have read-only azure_sys). Operational notes (quick but important) Expect a delay: Query store stats and LAWS ingestion aren’t instant. Give it a few minutes after running workload. Mind retention: Query store retention and Log Analytics retention are separate knobs. Tune them to balance troubleshooting value and cost. Production hygiene: don’t use wide-open firewall rules outside of a demo. Clean up When you’re done, delete the resource group: az group delete --name <RESOURCE_GROUP> --yes --no-wait Bottom line Query store in Azure Database for PostgreSQL flexible server now matches how modern architectures run—across primary, read replicas, and cascading replicas—and LAWS gives you a single place to query, compare, and act.When PostgreSQL v17 Chooses the Wrong Plan: A Deep Dive into CTEs with Data Skew
Common Table Expressions or CTEs provide temporary named result set that can be referenced within single complex SQL statements. CTE offer great flexibility in breaking down complex SQL queries into multiple readable parts and enables recursion. CTEs allows materialized result set which allows users to refer the result set multiple times. PostgreSQL 17 propagates column statistics from MATERIALIZED CTEs into the parent query, allowing the planner to estimate hash aggregation and join cardinalities more accurately. However, when queries combine highly skewed data distributions with runtime randomness (for example, ORDER BY random() LIMIT 1), this increased planner confidence can sometimes lead to execution plans that are correct in theory but catastrophic for specific runtime values. What is Data Skew and how it affects plans? Data skew is an uneven distribution of values in a column, where one or a few values occur far more often than the rest. To choose an efficient execution plan, PostgreSQL’s planner estimates row counts using statistics such as n_distinct, histograms, and most-common-values (MCV) frequencies stored in pg_statistic. If these estimates are off—especially if selectivity is overestimated—query performance can degrade significantly. Baseline Query and Observed Planner Behavior To gauge the performance impact of data skew with PGv17, we generated data into 3 tables relating to Accounts, Purchases and Audit. This baseline query selects a single random HIGH_RISK account and retrieves matching rows from a large audit_logs table via two materialized CTEs. Original Query with selected_account as materialized ( select account_id from accounts where account_type = 'HIGH_RISK' order by random() limit 1 ), audit_purchases as materialized ( select distinct p.account_id from purchases p join selected_account sa on p.account_id = sa.account_id ) select al.* from audit_logs al where exists ( select 1 from audit_purchases ap where al.account_id = ap.account_id); PostgreSQL 14 Execution Plan PostgreSQL 14 treats materialized CTEs as optimization fences and does not propagate detailed statistics into the parent query. As a result, the planner assumes relatively small CTE result sets and consistently favors nested loop joins with parameterized index scans. While these estimates are not strictly accurate, this conservative planning approach prevents full-table scans and avoids catastrophic performance under extreme data skew. In this workload, PostgreSQL 14 completes the query in approximately 7 ms. QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=1412.94..212757485.16 rows=1500600064 width=27) (actual time=5.226..7.192 rows=30 loops=1) Buffers: shared hit=277 read=13 CTE selected_account -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.460..4.460 rows=1 loops=1) Buffers: shared hit=271 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.459..4.459 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=271 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.011..3.533 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 CTE audit_purchases -> HashAggregate (cost=317.44..355.57 rows=3813 width=4) (actual time=4.735..4.741 rows=1 loops=1) Group Key: p.account_id Batches: 1 Memory Usage: 217kB Buffers: shared hit=274 read=1 -> Nested Loop (cost=0.44..292.52 rows=9966 width=4) (actual time=4.722..4.726 rows=10 loops=1) Buffers: shared hit=274 read=1 -> CTE Scan on selected_account sa (cost=0.00..0.02 rows=1 width=4) (actual time=4.461..4.461 rows=1 loops=1) Buffers: shared hit=271 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..192.84 rows=9966 width=4) (actual time=0.260..0.262 rows=10 loops=1) Index Cond: (account_id = sa.account_id) Heap Fetches: 0 Buffers: shared hit=3 read=1 -> HashAggregate (cost=85.79..87.79 rows=200 width=4) (actual time=4.743..4.744 rows=1 loops=1) Group Key: ap.account_id Batches: 1 Memory Usage: 40kB Buffers: shared hit=274 read=1 -> CTE Scan on audit_purchases ap (cost=0.00..76.26 rows=3813 width=4) (actual time=4.737..4.742 rows=1 loops=1) Buffers: shared hit=274 read=1 -> Index Scan using idx_account_audit on audit_logs al (cost=0.58..817780.34 rows=24600001 width=27) (actual time=0.480..2.438 rows=30 loops=1) Index Cond: (account_id = ap.account_id) Buffers: shared hit=3 read=12 Planning Time: 1.121 ms Execution Time: 7.253 ms (37 rows) PostgreSQL 17 Execution Plan PostgreSQL 17 correctly estimates that the audit_purchases CTE may contain thousands of rows and that joining it with audit_logs could produce tens of millions of matches. Based on these estimates, the planner selects a Hash Semi Join with a Sequential Scan on audit_logs, which is optimal when many rows are expected to match. However, at runtime the selected account produces only ~30 matching audit rows. Despite this, PostgreSQL must execute the chosen plan fully, scanning millions of rows from disk. This results in an execution time of 521,625 ms (~8.7 minutes). Importantly, this behavior is not a cost estimation bug—the estimates are statistically correct. The failure occurs because the PostgreSQL 17 planner estimates a large matching result set based on the cardinality of values produced by the audit_purchases CTE, and consequently chooses a plan optimized for that estimated volume rather than for the single runtime-selected account. QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=1691.60..93353890.16 rows=3001396992 width=26) (actual time=86577.858..521625.286 rows=30 loops=1) Hash Cond: (al.account_id = ap.account_id) Buffers: shared hit=28879 read=22040419 CTE selected_account -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.486..4.487 rows=1 loops=1) Buffers: shared hit=274 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.485..4.486 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=274 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.021..3.551 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 CTE audit_purchases -> HashAggregate (cost=658.72..673.28 rows=1456 width=4) (actual time=4.907..4.912 rows=1 loops=1) Group Key: p.account_id Batches: 1 Memory Usage: 73kB Buffers: shared hit=277 read=1 -> Nested Loop (cost=0.44..606.86 rows=20742 width=4) (actual time=4.898..4.902 rows=10 loops=1) Buffers: shared hit=277 read=1 -> CTE Scan on selected_account sa (cost=0.00..0.02 rows=1 width=4) (actual time=4.487..4.487 rows=1 loops=1) Buffers: shared hit=274 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..399.42 rows=20742 width=4) (actual time=0.410..0.411 rows=10 loops=1) Index Cond: (account_id = sa.account_id) Heap Fetches: 0 Buffers: shared hit=3 read=1 -> Seq Scan on audit_logs al (cost=0.00..52082989.92 rows=3001396992 width=26) (actual time=0.012..286755.571 rows=3001400050 loops=1) Buffers: shared hit=28602 read=22040418 -> Hash (cost=29.12..29.12 rows=1456 width=4) (actual time=4.919..4.920 rows=1 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 17kB Buffers: shared hit=277 read=1 -> CTE Scan on audit_purchases ap (cost=0.00..29.12 rows=1456 width=4) (actual time=4.908..4.911 rows=1 loops=1) Buffers: shared hit=277 read=1 Planning: Buffers: shared hit=197 Planning Time: 1.831 ms Execution Time: 521625.433 ms (38 rows) Forced nested loop Disabling hash joins at the session level forces PostgreSQL 17 to revert to a nested loop plan using index scans. This diagnostic step completes in ~8 ms, confirming that index-based execution is sufficient for the actual runtime workload. However, disabling planner features is not suitable for production use and serves only to validate the root cause. QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=1677.62..3361930001.36 rows=3001396992 width=26) (actual time=5.513..8.194 rows=30 loops=1) Buffers: shared hit=277 read=12 CTE selected_account -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.524..4.524 rows=1 loops=1) Buffers: shared hit=271 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.523..4.523 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=271 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.014..3.608 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 CTE audit_purchases -> HashAggregate (cost=658.72..673.28 rows=1456 width=4) (actual time=4.890..4.894 rows=1 loops=1) Group Key: p.account_id Batches: 1 Memory Usage: 73kB Buffers: shared hit=274 read=1 -> Nested Loop (cost=0.44..606.86 rows=20742 width=4) (actual time=4.882..4.885 rows=10 loops=1) Buffers: shared hit=274 read=1 -> CTE Scan on selected_account sa (cost=0.00..0.02 rows=1 width=4) (actual time=4.525..4.525 rows=1 loops=1) Buffers: shared hit=271 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..399.42 rows=20742 width=4) (actual time=0.354..0.356 rows=10 loops=1) Index Cond: (account_id = sa.account_id) Heap Fetches: 0 Buffers: shared hit=3 read=1 -> HashAggregate (cost=32.76..47.32 rows=1456 width=4) (actual time=4.896..4.899 rows=1 loops=1) Group Key: ap.account_id Batches: 1 Memory Usage: 73kB Buffers: shared hit=274 read=1 -> CTE Scan on audit_purchases ap (cost=0.00..29.12 rows=1456 width=4) (actual time=4.892..4.895 rows=1 loops=1) Buffers: shared hit=274 read=1 -> Index Scan using idx_account_audit on audit_logs al (cost=0.58..1782455.82 rows=52656088 width=26) (actual time=0.615..3.283 rows=30 loops=1) Index Cond: (account_id = ap.account_id) Buffers: shared hit=3 read=11 Planning Time: 0.184 ms Execution Time: 8.252 ms (37 rows) Fix Strategies While the fix isn’t straightforward, we attempt to modify the query plan through query rewrite using following methods: Lateral Join with Offset 0 Limit on Purchases Using subqueries Lateral Join with Offset 0 Adding OFFSET 0 inside a LATERAL subquery introduces an optimizer barrier that prevents join reordering. This forces PostgreSQL to execute a parameterized nested loop, passing the runtime account ID into an index scan on audit_logs. With this rewrite, PostgreSQL 17 executes the query in 6.519 ms, a 99.9988% improvement over the original plan. with selected_account as materialized ( select account_id from accounts where account_type = 'HIGH_RISK' order by random() limit 1 ), audit_purchases as materialized ( select p.account_id from purchases p join selected_account sa on p.account_id = sa.account_id ) select al.* from audit_purchases ap join lateral ( select * from audit_logs al where al.account_id = ap.account_id offset 0) al on true; Execution plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=1578.44..42162640233.98 rows=1092192577296 width=26) (actual time=4.980..6.481 rows=300 loops=1) Buffers: shared hit=403 read=12 CTE selected_account -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.296..4.297 rows=1 loops=1) Buffers: shared hit=271 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.295..4.296 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=271 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.013..3.357 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 CTE audit_purchases -> Nested Loop (cost=0.44..606.86 rows=20742 width=4) (actual time=4.585..4.588 rows=10 loops=1) Buffers: shared hit=274 read=1 -> CTE Scan on selected_account sa (cost=0.00..0.02 rows=1 width=4) (actual time=4.298..4.298 rows=1 loops=1) Buffers: shared hit=271 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..399.42 rows=20742 width=4) (actual time=0.286..0.287 rows=10 loops=1) Index Cond: (account_id = sa.account_id) Heap Fetches: 0 Buffers: shared hit=3 read=1 -> CTE Scan on audit_purchases ap (cost=0.00..414.84 rows=20742 width=4) (actual time=4.587..4.591 rows=10 loops=1) Buffers: shared hit=274 read=1 -> Index Scan using idx_account_audit on audit_logs al (cost=0.58..1506157.19 rows=52656088 width=26) (actual time=0.040..0.185 rows=30 loops=10) Index Cond: (account_id = ap.account_id) Buffers: shared hit=129 read=11 Planning: Buffers: shared hit=8 Planning Time: 0.238 ms Execution Time: 6.519 ms (31 rows) Limit on Purchases Applying LIMIT 1 to the audit_purchases CTE bounds its cardinality to a single row. This makes nested loops cheaper than hash joins during planning. PostgreSQL 17 selects an index‑driven execution plan and completes in 7.742 ms, yielding a 99.9985% improvement. with selected_account as materialized ( select account_id from accounts where account_type = 'HIGH_RISK' order by random() limit 1 ), audit_purchases as materialized ( select p.account_id from purchases p join selected_account sa on p.account_id = sa.account_id limit 1 ) select al.* from audit_logs al where exists (select 1 from audit_purchases ap where al.account_id=ap.account_id); Execution plan QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=972.07..933697.99 rows=24600001 width=27) (actual time=5.693..7.686 rows=30 loops=1) Buffers: shared hit=276 read=13 CTE selected_account -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.816..4.817 rows=1 loops=1) Buffers: shared hit=271 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.816..4.817 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=271 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.014..3.825 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 CTE audit_purchases -> Limit (cost=0.44..0.47 rows=1 width=4) (actual time=5.133..5.134 rows=1 loops=1) Buffers: shared hit=274 read=1 -> Nested Loop (cost=0.44..292.52 rows=9966 width=4) (actual time=5.132..5.132 rows=1 loops=1) Buffers: shared hit=274 read=1 -> CTE Scan on selected_account sa (cost=0.00..0.02 rows=1 width=4) (actual time=4.817..4.817 rows=1 loops=1) Buffers: shared hit=271 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..192.84 rows=9966 width=4) (actual time=0.313..0.313 rows=1 loops=1) Index Cond: (account_id = sa.account_id) Heap Fetches: 0 Buffers: shared hit=3 read=1 -> HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=5.138..5.139 rows=1 loops=1) Group Key: ap.account_id Batches: 1 Memory Usage: 24kB Buffers: shared hit=274 read=1 -> CTE Scan on audit_purchases ap (cost=0.00..0.02 rows=1 width=4) (actual time=5.135..5.135 rows=1 loops=1) Buffers: shared hit=274 read=1 -> Index Scan using idx_account_audit on audit_logs al (cost=0.58..686726.47 rows=24600001 width=27) (actual time=0.552..2.534 rows=30 loops=1) Index Cond: (account_id = ap.account_id) Buffers: shared hit=2 read=12 Planning Time: 0.216 ms Execution Time: 7.742 ms (35 rows) Using subqueries Rewriting the query using scalar subqueries converts the selected account ID into an InitPlan. The resulting value is then used as a runtime parameter in index scans against audit_logs. This eliminates join reordering opportunities and guarantees a parameterized access path. Both scalar subquery variants complete in approximately 7.3–7.6 ms, achieving over 99.9986% improvement. Subquery Example 1 select * from audit_logs al where al.account_id in (select p.account_id from purchases p where p.account_id=( select account_id from accounts where account_type = 'HIGH_RISK' order by random() limit 1)); Execution plan QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Semi Join (cost=972.02..1302907.98 rows=24600001 width=27) (actual time=5.257..7.260 rows=30 loops=1) Buffers: shared hit=277 read=12 InitPlan 1 (returns $0) -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.612..4.613 rows=1 loops=1) Buffers: shared hit=271 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.611..4.611 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=271 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.012..3.674 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 -> Index Scan using idx_account_audit on audit_logs al (cost=0.58..686755.98 rows=24600001 width=27) (actual time=5.067..7.059 rows=30 loops=1) Index Cond: (account_id = $0) Buffers: shared hit=274 read=11 -> Materialize (cost=0.44..242.67 rows=9966 width=4) (actual time=0.006..0.006 rows=1 loops=30) Buffers: shared hit=3 read=1 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..192.84 rows=9966 width=4) (actual time=0.187..0.188 rows=1 loops=1) Index Cond: (account_id = $0) Heap Fetches: 0 Buffers: shared hit=3 read=1 Planning Time: 0.160 ms Execution Time: 7.286 ms (24 rows) Subquery Example 2 select * from audit_logs al where al.account_id=(select distinct p.account_id from purchases p where p.account_id=( select account_id from accounts where account_type = 'HIGH_RISK' order by random() limit 1)); Execution plan QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using idx_account_audit on audit_logs al (cost=972.04..1507128.65 rows=52656088 width=26) (actual time=5.510..7.637 rows=30 loops=1) Index Cond: (account_id = (InitPlan 2).col1) Buffers: shared hit=277 read=12 InitPlan 2 -> Limit (cost=971.44..971.46 rows=1 width=4) (actual time=4.901..4.902 rows=1 loops=1) Buffers: shared hit=274 read=1 InitPlan 1 -> Limit (cost=971.00..971.00 rows=1 width=12) (actual time=4.455..4.456 rows=1 loops=1) Buffers: shared hit=271 -> Sort (cost=971.00..996.00 rows=10000 width=12) (actual time=4.455..4.455 rows=1 loops=1) Sort Key: (random()) Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=271 -> Seq Scan on accounts (cost=0.00..921.00 rows=10000 width=12) (actual time=0.018..3.522 rows=10000 loops=1) Filter: (account_type = 'HIGH_RISK'::text) Rows Removed by Filter: 40000 Buffers: shared hit=271 -> Index Only Scan using idx_purchases_account on purchases p (cost=0.44..399.42 rows=20742 width=4) (actual time=4.900..4.900 rows=1 loops=1) Index Cond: (account_id = (InitPlan 1).col1) Heap Fetches: 0 Buffers: shared hit=274 read=1 Planning: Buffers: shared hit=4 Planning Time: 0.154 ms Execution Time: 7.663 ms (25 rows) Query execution summary The table below summarizes execution time improvements across all tested rewrites relative to the original PostgreSQL 17 and 14 query execution plans. Setup / Rewrite Execution Time (ms) Speed‑up Factor (×) PG17 original (Hash Semi Join + Seq Scan) 521,625.433 1.0× PG14 original query shape 7.253 71,919× Forced nested loop (hashjoin off – diagnostic) 8.252 63,212× LATERAL JOIN + OFFSET 0 6.519 80,016× LIMIT on purchases 7.742 67,376× Subquery – IN with InitPlan (Example 1) 7.286 71,593× Subquery – scalar subquery (Example 2) 7.663 68,071× Key Takeaways While PostgreSQL 17’s planner improvements are correct, intentional, and beneficial for most workloads. However, queries that combine CTEs, runtime randomness, and heavily skewed data can cause the planner to select globally optimal plans that are locally inefficient. When runtime values drastically narrow result sets, query rewrites that force parameterized index scans—such as LATERAL joins, scalar subqueries, or bounded CTEs—provide predictable and stable performance.
