azure database for postgresql
191 TopicsMore power, more resilience, better value: Azure Database for PostgreSQL at Ignite 2026
The Postgres on Azure team is announcing a new set of capabilities for Azure Database for PostgreSQL flexible server at Ignite 2026. Our priorities are guided by what matters most to enterprises running PostgreSQL in production: more power for demanding workloads, higher resiliency for critical data, and better value at every Compute tier. This week we're announcing new compute SKUs (including our first Arm-based option), faster storage, immutable backups, more flexible disaster recovery options, and new fleet management features that make it easier to run Postgres at scale. Here's a quick tour of what's new. Feature Highlights New Compute SKUs Enhanced Storage capabilities Resiliency & recovery Engine & extensions Networking Fleet management New Compute SKUs Each new generation of processor brings new performance characteristics which, depending on your workload, deliver improved price-performance. This week we’re announcing support for new compute SKUs that offer more power and better value at every level, from burstable v2 for your dev/test workloads and lower cost “diskless” SKUs to Azure’s latest high-powered Arm CPUs. Burstable v2 Burstable v2 Compute SKUs are based on AMD's third Generation EPYC™ 7763v processor, bringing better performance for Azure Database for PostgreSQL dev/test workloads with more powerful CPUs, higher network bandwidth and storage throughput. In internal testing, Basv2 delivered up to 2.8x higher throughput and 64% lower average latency than v1. This means work will be completed faster at the same busting utilization percentage, providing better value as well as performance compared to v1. Find out more: Basv2 series, Compute options in flexible server. Cobalt 200 Arm-based SKU Azure Database for PostgreSQL now supports the Azure Cobalt 200 VM SKU, using Microsoft's custom Arm-based processor built around the Arm Neoverse V3 Compute Subsystems. Cobalt 200 is built for cloud-native workloads and gives you a new price-performance option for Postgres. Initial testing has shown outstanding performance compared to similar x86-64 SKUs, particularly for in-memory analytical workloads. Find out more: Azure Cobalt 200 VMs Now Available. Intel/AMD v5 & v6 “diskless” SKUs Compared to Intel & AMD v4 Compute SKUs, v5 and v6 deliver more transactions and use less CPU to do it. If you’re running your PostgreSQL workload on a v3 or v4 SKU, you will improve performance with better value, especially for CPU-constrained workloads, by upgrading your Compute SKU to v5 or later (see our recent blog post for some examples: Better Price Performance: Azure Postgres V3 & V5 Compute Compared). This week, we’re announcing new Intel & AMD v5 and v6 diskless SKUs, which are provided without a temporary disk at a lower price point. Traditional Compute SKUs include a local temp disk. The temp disk is used for temporary files created when sorts, joins and index builds exceed available memory. How does this affect PostgreSQL workloads? Temp disk SKUs are still recommended for larger analytical workloads that generate temporary files, but if you’re running an OLTP workload, or an analytical workload that fits in memory, the new diskless SKUs perform as well as their temp disk counterparts at a lower cost. What happens if temporary files need to be generated for larger workloads? The "diskless" SKUs will still work well, with temp files written to attached storage and sharing IO with data operations. Our initial testing with 10GB and 30GB analytical workloads for example showed each CPU generation performance better, with the lower cost diskless SKUs performance as well that the SKUs with temp disks. Find out more: Azure VM sizes with no local temporary disk Confidential compute v6 SKU Azure Database for PostgreSQL now supports the latest generation of Confidential Compute SKUs, delivering enhanced security and high performance for sensitive workloads. Powered by Intel TDX and AMD SEV-SNP, confidential computing helps protect data while it’s actively being processed within a hardware-backed trusted execution environment, complementing protection for data at rest and in transit. Intel-based SKUs are initially available in 5 regions and AMD-based SKUs in 23 regions, with availability continuing to expand. Learn more: Azure confidential computing in Azure Database for PostgreSQL flexible server Coming soon: Intel/AMD v7 SKUs Another set of SKUs to look forward are the v7 SKUs, based on Intel’s Xeon® 6 6973PC (Granite Rapids) and AMD's 5th Generation EPYC™ 9005 processors. Scale up to 372 vCPUs and 2.8 TiB memory the most demanding analytic workloads with very large dataset. We’ll be making these SKUs available for Azure Database for PostgreSQL in the next few weeks. Enhanced Storage capabilities This week, we’re dramatically increasing the performance of flexible server storage options, as well as improving the usability of SSDv2. Ultra Disk Very large high-traffic transactional systems, high-ingest logging and telemetry platforms and other extremely latency-sensitive applications can benefit from storage that supports high IOPS. For the most demanding I/O-intensive workloads, Azure Ultra Disk is now generally available for Azure Database for PostgreSQL. Ultra Disk offers up to 400K IOPS with 10 GB/s throughput and consistently low latency (with support for up to 64TB storage). Learn more: Ultra Disk storage option in Azure Database for PostgreSQL flexible server SSDv2 auto-growth As data size grows DBAs need to monitor storage usage to avoid disk-full scenarios. With storage auto-grow, additional disk storage is automatically added when available storage drops below pre-defined thresholds, reducing the risk of running out. This feature is now generally available for high-performance SSDv2 storage. Learn more: Configure storage autogrow in Azure Database for PostgreSQL flexible server Resiliency & recovery Immutable point-in-time restore (PITR) backups - Preview Ransomware and accidental deletion are top concerns for data teams and often form a part of regulatory compliance requirements for resiliency. With PITR Immutable backups, now in preview, Azure Database for PostgreSQL helps protect your point-in-time recovery backups from being modified or deleted during their retention period by isolating them from the server's lifecycle. Even if the server is deleted, protected recovery points remain available throughout their configured retention period, providing an additional layer of protection for disaster recovery and resiliency. Learn more: Azure Database for PostgreSQL backup Long-term daily backups of any size – Preview The latest version of Azure Backup for PostgreSQL (v2) brings physical snapshot-based backups to long-term retention (LTR), supporting backups up to 64 TB (up from 1 TB) and daily backups (up from weekly). With this preview, you can retain any size backups for up to 10 years, providing greater scale and flexibility for long-term data protection and compliance needs. Learn more: Long-term retention Chaos Studio zone-down scenario You only know how effective your disaster recovery strategy is when something breaks, and if there’s a problem, you don’t want to find out during an outage. Azure Chaos Studio allows you to test disaster scenarios such as “zone-down” by simulating an outage – breaking things in a controlled fashion. The Azure Chaos Studio zone-down scenario for Azure Database for PostgreSQL is now generally available. Test workload resilience by triggering forced failover on healthy, high-availability-enabled servers and assessing application recovery. Learn more: Chaos Studio documentation. Cross-subscription read replicas You can now create read replicas in a different Azure subscription from the primary server. This provides more flexibility in disaster recovery architecture design, workload isolation and organizational boundaries, such as keeping DR environments in a separate subscription. Engine & extensions PostgreSQL 19 on day zero, with major version upgrade Azure Database for PostgreSQL flexible server supports PostgreSQL 19 on the same day as the community release. You can try the new release on a managed service without waiting. During Preview, PostgreSQL 19 is available in East Asia. PostgreSQL 19 engine enhancements available in preview on Azure Database for PostgreSQL flexible server Existing servers get a clear path forward. With major version upgrade (MVU), you can move eligible flexible server instances to PostgreSQL 19 in place. There's no dump-and-restore and no rebuilding your environment. 1 st day support also covers the extensions you use most. Key extensions, including pgvector, azure_ai, DiskANN, PostGIS, and pg_duckdb, are available alongside PostgreSQL 19. You get the new engine and the extension ecosystem together, not one at a time. Learn more: PostgreSQL 19 on Azure Citus CDC support in Azure Database for PostgreSQL elastic clusters - Preview Scenarios like real-time analytics and event sourcing require live changes to flow from databases to dashboards and downstream processing. This can be challenging for distributed tables. Change Data Capture (CDC) enables real-time streaming of data updates to downstream systems using PostgreSQL's logical decoding framework. Citus CDC enables this streaming when data is sharded across multiple nodes by intercepting shard-level WAL changes and mapping them back to the original distributed table name, while normalizing shard names. This feature is now available in preview with elastic clusters in Azure Database for PostgreSQL flexible server. Learn more: Citus Change Data Capture (CDC) Networking VNet to private endpoint network reconfiguration Production workloads need a secure networking environment, and Azure Database for PostgreSQL offers private network configuration options based on VNet integration where the database server runs in a virtual network you control, and Private Link where your database server can be reached by a private endpoint in a designated subnet. Previously it was difficult to switch from VNet integration to Private Link, which was challenging if for example you needed to change your secure networking architecture to meet new security requirements. You can now migrate directly existing VNet-integrated servers to private endpoint networking with the general availability of VNet to private endpoint network reconfiguration. Learn more: Change from virtual network integration to a private endpoint-capable network configuration Fleet management Database Hub: one place for your whole database estate Database Hub is generally available. It gives you a single, tenant-wide view of your databases across Azure PostgreSQL, Azure SQL, and Azure Cosmos DB. You can see what you have, how healthy it is, and where it's exposed. Inventory: See every database across engines and subscriptions in one place. Health: Spot problems across your fleet without checking each server separately. Security posture: Find gaps early, such as auditing that isn't enabled or customer-managed key (CMK) warnings. With GA, Database Hub is ready for your database server estate. Whether you run a handful of Postgres servers or thousands of instances across multiple engines, you can manage your entire estate of reacting server by server. Try now: Database Hub Wrapping up From ARM-based compute and Ultra Disk to immutable backups and fleet-level management, this week’s announcements are about giving you more power, more resilience and better value from Postgres on Azure. For more PostgreSQL and MySQL and announcements this week, including general availability of Azure Horizon DB and the latest AI innovations check out these blog posts: Decisions, decisions: Your guide to PostgreSQL at Microsoft Ignite 2026 HorizonDB on Azure and Fabric Postgres Built for AI: Introducing the Latest AI Innovations in Azure HorizonDB Complete Oracle to PostgreSQL Migration: Move Your Schema, Data, and Application Code If you're at Ignite, do drop by to meet our team at the PostgreSQL & MySQL on Azure station at the Hub. I hope to see you there!469Views2likes0CommentsHorizonDB: PostgreSQL for enterprises, developers, and agents on Azure and Fabric
Today at Microsoft Ignite, we’re thrilled to announce the general availability of Azure HorizonDB and the public preview of Microsoft Fabric HorizonDB, expanding what’s possible with PostgreSQL on Azure. HorizonDB brings a new cloud-native architecture to PostgreSQL, delivering new levels of performance, scalability, and availability for building AI-powered applications or modernizing demanding enterprise workloads. The role of PostgreSQL is growing, and so is the range of users and companies that depend on it. Enterprises are standardizing on it for critical workloads, developers are choosing it to build new applications, and AI agents are increasingly using it to create, retrieve, and act on data. Each brings different needs, from resilience and security to instant provisioning, developer flexibility, and agent-driven workflows. We built HorizonDB to meet those needs, on Azure and in Microsoft Fabric. Built for mission-critical enterprise workloads Azure HorizonDB is built for the most demanding PostgreSQL workloads, where performance and availability matter most. It delivers up to 8x faster OLTP performance than self-managed PostgreSQL* and supports databases up to 128 TB. Every database is resilient across availability zones by default, with sub-millisecond commit latency and failover in under five seconds, providing high availability without impacting application performance. * Based upon internal, standardized benchmarking, running on high availability and on comparable hardware. HammerDB TPROC-C workload, 95K Warehouses, 512 Users, Azure Edsv6 Virtual Machines with 64 vCores, PostgreSQL 17.11, Korea Central, Availability Zone 1 and 2 HorizonDB integrates with Azure security controls for identity, networking, and data protection. Microsoft Entra ID authentication centralizes identity-based access to the database, helping teams apply consistent identity governance across their Azure environment. Private endpoints provide database connectivity through Azure virtual networks, keeping those connections within the enterprise network boundary. Customer-managed keys give organizations control over the encryption keys that protect their data, in line with their key management policies. Together, these capabilities support sensitive workloads on Azure while preserving the security practices already in place. “Our AI ambitions depend on secure, reliable, high-performance API access to LSEG’s data. Azure HorizonDB meets our enterprise security requirements while providing a PostgreSQL foundation for our existing API services and the MCP layer powering new AI experiences.” – Grzegorz Giedrojc, Director of Engineering, London Stock Exchange Group Serverless PostgreSQL in Fabric, from first query to OneLake Developers have always built applications on databases, but agents are now building them at an entirely different speed. Agents spin up databases for prototypes, experiments, and short-lived tasks, then drop them when the work is done. Most of these workloads start small, run in bursts, and sit idle between uses, which makes their compute needs hard to predict and inefficient to run continually. Microsoft Fabric HorizonDB is serverless and built for exactly these scenarios, with: Rapid provisioning: Databases provision in seconds so developers and agents can start building immediately. Automatic pause: Compute pauses after five minutes of inactivity to avoid keeping resources running between tasks. On-demand resume: Compute resumes when the next query arrives without manual intervention. Fabric HorizonDB is also the default database for Fabric apps, where the Rayfin SDK brings PostgreSQL, data APIs, authentication, functions, storage, and observability together in a managed application backend. Instead of assembling and connecting these services separately, developers and agents can focus on application logic and the data it needs, moving from first idea to a running application without operating the backend underneath it. Once those applications are running, they need more than their own operational data. Fabric HorizonDB now supports translytical capabilities on OneLake to operationalize lake data, bringing the scale and performance of Fabric's analytical engines to live transactions through: Translytical queries: PostgreSQL queries are transparently converted to run on Fabric Data Warehouse, using distributed, scale-out execution on CPU- or GPU-powered processing. Analytical queries can run an order of magnitude faster than they would natively in PostgreSQL. Full and partial pushdowns are supported, so a single query can efficiently join PostgreSQL tables with data in OneLake. Mirroring: Fabric HorizonDB tables are replicated into OneLake by default and are automatically converted to Delta format, so data is ready for translytical application workloads and large-scale analytics across Fabric without building data integration pipelines. Synced Tables: Enriched data from Fabric Data Warehouse flows back into HorizonDB (reverse ETL) for low-latency, high-concurrency serving to applications and agents. Snapshot sync handles periodic refreshes such as daily scores or aggregates, while the continuous sync option keeps tables current as source data changes. Together, serverless compute and translytical capabilities on OneLake enable developers and agents to build Fabric apps that start small, scale with demand, and act on live transactions and analytical insights in the same application, all through one PostgreSQL API. With no pipelines to build or infrastructure to manage, every application can use the full value of data in OneLake from day one. “As settlement cycles shorten and financial services become more automated, resolving payment and trade exceptions requires more than the current transaction state. Fabric HorizonDB enables us to connect operational data with historical insights in OneLake to provide firms with the broader business context to resolve exceptions faster and make more informed decisions.” – Jennings Balavari, Founder and CEO of Opsen AI Build retrieval, reasoning, and AI workflows, all in PostgreSQL AI agents need more than a place to store data. They need a data platform that combines relevant context and relationship-based reasoning with integrated AI workflows and tools designed for agents. HorizonDB consolidates these capabilities in PostgreSQL so applications can do more with operational data without stitching together specialized databases and disparate services. Retrieve the right context. HorizonDB combines vector and full-text search to capture both semantic meaning and precise keyword matches. Applications can retrieve relevant context directly in PostgreSQL without separate search or vector databases. pgvector support: Existing vector applications work as they are. DiskANN indexing: Embedding search scales with up to 1.5x higher throughput than HNSW at comparable recall and up to 5x smaller indexes, reducing storage and memory requirements. BM25 ranking: pg_textsearch ranks text results by keyword relevance to find exact names, identifiers, and technical terms. Hybrid search with RRF: Reciprocal Rank Fusion functions combine vector and keyword results based on their positions in each ranked list. This helps retrieve relevant context that either search method alone might miss. “HorizonDB is changing how we think about the boundary between operational data and intelligence. Instead of creating another datastore for every new capability, we’re exploring how relational state, relationships, semantic retrieval, and AI can coexist around the same trusted operational truth.” - Dylan Addington, Enterprise Architect, Pediatric Associates Family of Companies Reason across relationships. HorizonDB supports graph modeling and traversal through Apache AGE, with significant new optimizations to this extension for faster queries and more efficient graph updates. The AGE extension API is unchanged, so existing applications can run on HorizonDB and just work without code changes. Compared with community Apache AGE: Simple reads: Resolve direct dependencies, linked entities, and ownership up to 3,900x faster (LDBC SNB Interactive workload). Complex reads: Run multi-hop queries across connected data up to 512x faster (LDBC SNB Interactive workload). Graph builds and refreshes: Run MERGE and batch upserts up to 190x faster with about 6x lower peak memory (internal Microsoft testing). Faster graph queries reduce the time agents spend following relationships across connected data. A supply chain agent can trace a delayed component through suppliers, shipments, and affected orders. A support agent can connect a customer’s case to related accounts, products, and known issues. By combining graph traversal with vector search and relational queries, agents can reason over how data is connected as well as what it contains. Integrate models and scale AI workflows. Building scalable AI applications requires models and data processing to work together without a separate orchestration layer. HorizonDB integrates model access and AI execution with operational data through: AI Model Management: Connect applications to the models they need and manage model access from the database. AI Functions: Invoke models from PostgreSQL for tasks such as embedding generation, extraction, and content generation. AI Pipelines: Compose these functions into durable, asynchronous workflows that scale AI processing across data. For example, a pipeline can call external models to enrich new rows as they arrive or keep a knowledge graph current as the underlying data changes. “Our team built an entire RAG retrieval pipeline in Azure HorizonDB without an application tier. Chunking through reranking all ran in SQL.” - Brian Steele, Chief AI Officer – Field, Coretek Connect agents to HorizonDB with tools and skills: Microsoft’s PostgreSQL MCP Server and PostgreSQL Skills combine database connectivity with task-specific guidance for agents. PostgreSQL MCP Server: Provides a standard way to discover and invoke HorizonDB capabilities through the Model Context Protocol. PostgreSQL Skills: Supply reusable knowledge and instructions for common database tasks. Modern developer tools for HorizonDB, powered by VS Code More than 70% of developers already work in Visual Studio Code, according to the 2026 Stack Overflow Developer Survey. The PostgreSQL extension for VS Code brings HorizonDB development, management, and troubleshooting into the editor, so teams can understand their cluster, tune queries, and inspect data and AI workflows without switching tools. Cluster topology: A clear view of each HorizonDB cluster shows its pools and nodes, including the writer and read-only nodes, their availability zones, compute configuration, high availability status, and parameter group sync state. AI-assisted query optimization: Query plans render as interactive graphs with performance hotspots highlighted. Developers can ask GitHub Copilot to analyze a plan or individual operator and suggest optimizations, using live database schema context. Monitoring and debugging: Metrics, wait events, top queries, and active sessions are available alongside cluster topology, helping developers investigate slow queries and blocking relationships. Queries can be opened directly in the SQL editor or plan visualizer for deeper analysis. Knowledge graph visualization: Apache AGE Cypher results render as interactive node-edge graphs, making relationships in the data easier to explore. AI pipeline visualization: Developers can follow AI workflows from source data through chunking, embedding, extraction, generation, and ranking. Modernize Oracle and PostgreSQL workloads on HorizonDB For existing PostgreSQL workloads, the path to HorizonDB is direct. The new Azure Database Migration Service moves PostgreSQL databases from AWS, Google Cloud, on-premises, and Azure virtual machines to HorizonDB, with online migration of schema and data plus built-in validation and monitoring. Oracle migrations are a different challenge. Beyond moving the data, teams have to convert schemas, PL/SQL packages, and the application code that depends on them, then prove the result behaves the same. That makes an Oracle migration a software engineering project, not a database task. That's why we built the tooling into the PostgreSQL extension for VS Code, where migration teams already have version control, code review, and CI/CD pipelines. Schema and application changes stay in lockstep, with every conversion versioned, reviewed, and regression-tested on each commit. The extension brings AI-assisted conversion into that workflow with: Schema conversion: Reads the Oracle data dictionary and uses models in Microsoft Foundry to generate PostgreSQL DDL. It then compiles the results against a PostgreSQL database and flags objects that need manual changes in a conversion report. Application conversion: AI-assisted conversion identifies Oracle-specific dependencies in SQL scripts, stored procedure calls, and Java code, then proposes changes for PostgreSQL compatibility. It builds on the output of schema conversion, so the updated code matches the converted object names and types. Developers review and validate each change before accepting it in VS Code, GitHub Copilot CLI, or existing modernization workflows such as Modernize CLI. Data movement: Once the converted schema is deployed, Azure Database Migration Service moves the Oracle data into HorizonDB. Offline migration is now in preview, and online migration is coming soon. The tools handle the repetitive conversion work, so engineers can focus on review, testing, and the business logic that needs human judgment. Ready to get started with HorizonDB? Azure HorizonDB is generally available today in 13 Azure regions across the Americas, Europe, and Asia Pacific, with more regions coming online soon. Microsoft Fabric HorizonDB is available in public preview across Fabric regions worldwide. You can get started today by creating a new HorizonDB instance using the Azure portal, API’s, or the Visual Studio Code extension for PostgreSQL to begin exploring these capabilities firsthand. Azure HorizonDB Documentation: Microsoft Azure HorizonDB Documentation Additional Ignite Content For a closer look at the capabilities in this post, see our companion blogs on AI with Azure HorizonDB, covering hybrid search, graph, and AI pipelines, and migrating to HorizonDB, covering the new Azure Database Migration Service and Oracle migration in VS Code. HorizonDB AI: Azure HorizonDB: Data, Transactions, and AI in One Postgres Database HorizonDB Migrations: Complete Oracle to PostgreSQL Migration: Move Your Schema, Data, and Application Code HorizonDB is one part of our PostgreSQL investment on Azure. To see what else is new, read the Azure Database for PostgreSQL Ignite blog, and for the full set of database announcements, read the Azure Databases Ignite blog. Azure Database for PostgreSQL: More power, more resilience, better value: Azure Database for PostgreSQL at Ignite 2026 Azure Databases: Every intelligent experience starts with data3.3KViews4likes0CommentsComplete Oracle to PostgreSQL Migration: Move Your Schema, Data, and Application Code
Jonathon Frost, Principal Group Product Manager, PostgreSQL at Microsoft Moving your Oracle workloads to PostgreSQL on Azure just got a lot easier. Schema conversion, data migration, and AI-assisted application conversion now work together as one connected path to Azure Database for PostgreSQL flexible server and Azure HorizonDB. Convert your schema with the PostgreSQL extension for VS Code, move your data with Azure Database Migration Service (Azure DMS), and update your application code with the new migrate-oracle-app skill in the postgres-skills plugin. Your database and application teams can now work through the migration together, from the first converted object to the application that uses it. To see how the pieces fit together, we’ll follow an example order-processing service through all three stages: schema, data, and application code. We’ll convert its orders table, move the order data, and update the Java code that retrieves paid orders, all in a nonproduction rehearsal of the migration. Schema: convert your Oracle schema Connect and run discovery on your application’s Oracle database Schema conversion is built right into the PostgreSQL extension for VS Code. Create a migration project, connect to your Oracle database, and select the schemas your application uses. Discovery then checks your Oracle permissions and inventories the selected schemas, showing which objects it found, which it will extract, and which are excluded or invalid. It also lists dependencies on schemas you didn’t select, with a diagram of the references, so you can confirm the scope before extraction begins. Behind the scenes, schema conversion agents use your Microsoft Foundry model to translate the Oracle definitions into PostgreSQL. The tool then compiles the results in a scratch database and runs available static checks, such as plpgsql_check for PL/pgSQL routines. Translation and compilation work together across your tables, constraints, indexes, sequences, views, triggers, and PL/SQL packages, procedures, and functions. Configure your Azure destination Next, choose the scratch database where the tool validates the converted schema. Select Azure HorizonDB or Azure Database for PostgreSQL flexible server as the target type, then pick the connection and database. Validating on the same service you plan to run means the schema, data, and application stages all build on one environment. Give the conversion an empty or dedicated database of its own. Align its PostgreSQL major version, extensions, permissions, encoding, and locale with your planned deployment; readiness checks run as soon as you select the database and make no changes. Finally, connect your Microsoft Foundry model deployment using an API key or Microsoft Entra ID. Then select Migrate. The tool extracts your Oracle definitions and converts them in chunks, and the project view tracks progress as it goes. For our example order service, the orders table packs several common mapping decisions into a few columns. Here’s the Oracle definition: CREATE TABLE sales.orders ( order_id NUMBER(12,0) NOT NULL, placed_at DATE NOT NULL, total_amount NUMBER(12,2) NOT NULL, status VARCHAR2(20 CHAR) NOT NULL, CONSTRAINT orders_pk PRIMARY KEY (order_id) ); And here’s a representative PostgreSQL conversion, created in the sales schema: CREATE TABLE sales.orders ( order_id numeric(12,0) NOT NULL, placed_at timestamp(0) without time zone NOT NULL, total_amount numeric(12,2) NOT NULL, status varchar(20) NOT NULL, CONSTRAINT orders_pk PRIMARY KEY (order_id) ); Each choice preserves the values your application expects. The numeric(12,0) type keeps the identifier’s declared precision, and numeric(12,2) keeps amounts exact to the cent. Oracle DATE includes a time of day, so timestamp(0) without time zone carries the full wall-clock value into PostgreSQL. VARCHAR2(20 CHAR) becomes varchar(20), which PostgreSQL also measures in characters. Back in the project view, you can follow extraction and conversion progress across the whole schema: Review, refine, and deploy When conversion finishes, the HTML conversion report opens automatically with your discovery, extraction, and conversion results. Conversion Object Explorer then lets you compare each object’s Oracle and PostgreSQL definitions side by side and read its conversion notes, for converted and not-converted objects alike. Select Ask to have GitHub Copilot explain a conversion and its dependencies, or Fix to refine the object’s PostgreSQL file with Copilot’s help. Your PL/SQL converts too. Each package member becomes its own PostgreSQL routine: in the example below, the assign_manager procedure in hr_pkg becomes the function hr_pkg$assign_manager . Review each converted routine, then exercise the business operations it supports, such as assigning an order number or calculating an order total. When the schema is ready, select Setup instruction on the Conversion summary. The Setup Experience generates deployment instructions, PowerShell and Bash runners, and supporting SQL. Keep the setup directory inside the complete migration project, since the runners reference the converted deployment SQL next to it. From the setup directory, check your destination’s readiness first: bash setup.sh --validate-only To deploy into a database that already exists, use the existing-database mode: bash setup.sh --database-mode existing --validate-only A validate-only run checks connectivity, the PostgreSQL version, encoding and locale, permissions, target-schema occupancy, and extension availability, then writes a validation report without changing the destination. When the report looks good, run the same command without --validate-only to deploy the converted schema to your nonproduction destination. You now have a PostgreSQL schema ready for the data rehearsal. Schema conversion also produces coding notes that describe the Oracle patterns it found and how each one maps to PostgreSQL. Keep them with the deployed object names, types, and routine signatures. Together they form a shared contract between your teams: Azure DMS maps data into the same schema, and the application skill uses the notes to line up code changes with the objects you actually deployed. Data: move your data with Azure DMS The Oracle route in Azure DMS, now in Public Preview, supports offline migration from Oracle 19c and later to both Azure Database for PostgreSQL flexible server and Azure HorizonDB. With your converted schema deployed, DMS moves the rows from Oracle into the tables you prepared. Start with a rehearsal that uses representative data and your chosen Azure destination. For production, plan a migration window around four steps: pause writes to the source, copy the data, validate the results, and switch the application over. Pause scheduled jobs and integrations too, so the source data stays stable throughout the move. Select your prepared flexible server or HorizonDB resource and database, then configure the migration runtime and destination access. Use the same target in the application rehearsal, so schema, data, and code all come together in one environment. Azure DMS supports public and private network paths, along with Oracle connections over TLS. Configure connectivity to both endpoints: DNS and routing for private access, or firewall rules for public access. Set up certificate trust for TLS, then run the connection tests to confirm each route. Map schemas and follow the copy In the Azure portal, choose Oracle as the source, your Azure destination as the target, and offline mode. Then map each source schema to the target database and schema from your conversion project. For our order service, that maps Oracle’s SALES schema to the reviewed PostgreSQL sales schema. Start the migration and watch table-level progress and row counts as DMS copies the data. The monitoring view gives the whole team a shared picture of the run. Keep the migration record with the schema revision, so every rehearsal builds on the same configuration. Validate the order data After the copy, run the same read-only query against the paused Oracle source and the PostgreSQL destination. Grouping orders by status gives a quick comparison of row counts, total value, and date range for each group: SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS order_total, MIN(placed_at) AS first_order_at, MAX(placed_at) AS last_order_at FROM sales.orders GROUP BY status ORDER BY status; Pair these totals with lookups of specific orders by key. Counts confirm the size of each group, while totals and timestamps confirm the business values. Include fractional amounts, character data, and date boundaries that represent your workload. Finish the load by checking constraints, indexes, privileges, and statistics on the destination. If your application uses sequences or identity columns, advance each one past the highest loaded key before you enable writes. The copied orders also make great fixtures for the application work ahead. Pick a few orders with known totals and statuses, and write down the results you expect when the application retrieves or updates them. That gives the code conversion a concrete set of business operations to verify. App Code: convert your Oracle application Add migration skills to your coding agent The new migrate-oracle-app skill completes the journey inside your application repository. It ships in Microsoft’s postgres-skills plugin and gives your coding agent migration workflows, scripts, language guidance, and PostgreSQL tools. The skill uses your deployed schema and the coding notes from schema conversion to guide changes to the code that calls the database. The skill covers Java, .NET, Python, C, and C++, with toolchain guidance for discovery, builds, tests, integration, and packaging. For our order service, we’ll follow a small Java query through the conversion. In GitHub Copilot CLI, install the complete plugin from its marketplace: copilot plugin marketplace add microsoft/postgres-skills copilot plugin install postgres-skills@postgres-skills You’ll need Node.js 22 or later, npm and npx, Git, Copilot access, and your application’s toolchain, which for our Java service means the JDK and Maven. Add Docker for PostgreSQL integration tests, and start a new agent session after you install the plugin. In Copilot CLI, the skill reaches PostgreSQL through the bundled postgres-mcp server. In VS Code Copilot, it uses the PostgreSQL extension’s MCP tools. Either way, save a connection profile in the provider your host uses. For this example, we saved a read-only profile named orders-rehearsal that points to the orders_pg database and its sales schema. The profile supplies the connection, while the schema and coding notes connect that target to the source repository. Assess with the schema conversion context Pass the extracted Oracle DDL with oracle-ddl-folder and the reviewed coding notes with notes. You can point notes at the coding_notes.md file itself, or at a migration project directory, where the skill looks for results/application_guidance/coding_notes.md. This assessment prompt brings together the order-service repository, the saved PostgreSQL profile, and the schema review artifacts: /migrate-oracle-app mode=assess codebase=. postgres-connection=orders-rehearsal database=orders_pg schema=sales oracle-ddl-folder="./schema-review" notes="./schema-review/coding_notes.md" Assessment starts by finding where the application actively uses Oracle and identifying its language and toolchain. It traces database calls, SQL, driver dependencies, and test coverage to build a focused conversion plan. Read-only checks against your PostgreSQL target then match those code references to the deployed objects and routine signatures. For the order service, ask the agent to trace the order query, its parameter binding, its result types, and any package calls through the reviewed mappings. Then open .github/migrate-oracle-app/assessment/assessment-report.md in the repository. The report brings the Oracle inventory, target mappings, test discovery, and baseline results together in one place, with machine-readable assessment data alongside it, giving the team a concrete starting point for the code changes. Convert the query and its tests The skill has three modes: assess builds the plan, convert applies a current assessment, and assess-and-convert runs both in one pass. In convert mode, the skill previews source changes for your approval. Edits land in your existing Git worktree, ready for your team’s usual diff review and commit process. For our order service, we’ll focus the conversion on the JDBC driver, parameter binding, and tests while keeping the API contract intact. This prompt turns on the tests option: /migrate-oracle-app mode=convert codebase=. postgres-connection=orders-rehearsal database=orders_pg schema=sales oracle-ddl-folder="./schema-review" notes="./schema-review/coding_notes.md" options=tests The tests option adds a test baseline, generated unit and integration tests, and fixes for affected tests. The skill also discovers and runs the project’s existing tests. PostgreSQL integration tests run in a disposable Docker environment, and you can separately authorize tests that write to the target database. Here’s the order service’s read path for paid orders on the Oracle JDBC driver. It binds the status through an Oracle named parameter, :status, using the driver’s OraclePreparedStatement extension: String sql = "SELECT order_id, total_amount FROM sales.orders " + "WHERE status = :status ORDER BY order_id"; try (PreparedStatement ps = oracleConn.prepareStatement(sql)) { ps.unwrap(OraclePreparedStatement.class) .setStringAtName("status", "PAID"); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { orders.add(new Order(rs.getLong("order_id"), rs.getBigDecimal("total_amount"))); } } } After conversion, the same method runs on the PostgreSQL JDBC driver with a standard positional parameter: String sql = "SELECT order_id, total_amount FROM sales.orders " + "WHERE status = ? ORDER BY order_id"; try (PreparedStatement ps = pgConn.prepareStatement(sql)) { ps.setString(1, "PAID"); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { orders.add(new Order(rs.getLong("order_id"), rs.getBigDecimal("total_amount"))); } } } The PostgreSQL JDBC driver binds ? placeholders by position, so the Oracle-specific setStringAtName call becomes a standard setString(1, "PAID"). The query keeps its columns and ordering, and getBigDecimal returns the numeric(12,2) amounts exactly. For production list endpoints, add paging or streaming. Keep decimal values and the API’s agreed serialization unchanged. Follow the reviewed object mappings, package the PostgreSQL JDBC driver (org.postgresql:postgresql) with the application, and use the skill’s language guidance for timestamps, transaction boundaries, and connection pooling. Run the application workflow on PostgreSQL Testing is built into the conversion. Docker-based PostgreSQL integration tests exercise the application’s real driver paths, and read-only checks confirm that the code matches the destination’s actual objects and capabilities. With your authorization, runtime tests can also exercise the target environment as part of the rehearsal. Match the PostgreSQL test image to your target’s major version, then run the service-specific checks against your chosen Azure resource, using its deployed objects, extensions, application identity, and connection configuration on flexible server or HorizonDB. Bring the order fixtures from the data rehearsal into these tests: an unpaid order, a paid order with a fractional total, and an order at a timestamp boundary. Run the same application operations and compare the results with the expected business behavior. That continuity turns schema, data, and code into one connected migration. The conversion report at .github/migrate-oracle-app/conversions/conversion-report.md collects the file diffs and results for code conversion, build and packaging, unit tests, PostgreSQL integration tests, and target validation. It gives the database and application teams one shared place to review the release. Bring Oracle migration into Modernize CLI Java and .NET teams already using Modernize CLI can run Oracle-to-PostgreSQL application migration there too, now in Public Preview. The source-based workflow follows three steps (assess, plan create, and plan execute) and runs during development, well ahead of the production data-migration window. It can use exported PostgreSQL definitions to carry object names, type mappings, and routine signatures into assessment and conversion. To bring the converted schema into a Java or .NET application, place its schema-only PostgreSQL definitions in the application workspace and point .github/modernize/oracle-postgres.json at that folder: { "postgres-ddl-folder": "database/postgres", "options": ["tests"] } Run modernize assess with --format html, and supply an assessment YAML file that sets analysisCoverage: full through --assess-config. The assessment produces an HTML aggregate report, an Oracle-to-PostgreSQL component report, and Markdown planning context. Then pass the assessment report path that the run prints to modernize plan create with --assess-file-path. The plan uses migration-oracle-to-postgresql-app-java for Java or migration-oracle-to-postgresql-app-dotnet for .NET. Review the plan, then run modernize plan execute to convert the code. Modernize tracks task progress, code edits, build and test results, and local commits as it works. Here too, the tests option adds a test baseline, unit tests, disposable PostgreSQL integration tests, and fixes for affected tests, all exercising real driver and repository paths against the converted objects. Then bring the converted application into the Azure rehearsal described above, so schema, data, and code are validated together. One connected journey from Oracle to PostgreSQL With your schema, data, and application rehearsals complete, you’re ready for the production window: pause, copy, validate, and switch. Choose Azure Database for PostgreSQL flexible server or Azure HorizonDB, start with an application your team knows well, and get your first Oracle workload running on PostgreSQL in Azure. Happy migrating! Migration resources Schema Conversion PostgreSQL extension for VS Code Oracle schema conversion (Microsoft Learn) Data Movement Oracle to Azure Database for PostgreSQL flexible server - Azure DMS documentation Oracle to Azure HorizonDB - Azure DMS tutorial App Code Conversion Migration guide migrate-oracle-app skill Modernize CLI Integration Modernize CLI overview (Microsoft Learn) Install and get started (Microsoft Learn) Modernize CLI command reference (Microsoft Learn)313Views1like0CommentsEnd-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.PostgreSQL Bloated Tables
Introduction Bloat refers to the excess, wasted disk space occupied by "dead rows" (tuples) within tables and indexes that no longer hold active data. This occurs because PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent transactions, which ensures transaction snapshot isolation. Every UPDATE/DELETE Operation doesn't immediately delete old records from the table(data pages), but marks them with xmin/xmax markers (where xmin is the transaction ID that created the record, and xmax is the transaction ID that deleted or updated it). As a result, old version records remain physically in the database; the system doesn't discard anything immediately, it simply marks the last version as "deleted" with the appropriate xmax value. This accumulates multiple versions of the same record, of which ultimately only the newest is active, while the rest are "dead" data requiring future cleanup (VACUUM). So, instead of modifying or deleting data directly in place, updates and deletes leave the original data on disk and create new versions (Updates) elsewhere, meaning the physical file size doesn't shrink. VACUUM removes these dead row versions and makes their space reusable within the table, so it should run regularly (autovacuum) on frequently updated tables. Plain VACUUM allows normal reads and writes and usually retains reclaimed space within the table rather than returning it to the operating system. However, if the last pages contain no live rows, it can shorten the file and return that space to the operating system, while VACUUM FULL will rebuild the table and it’s indexes, returning the unused space to the operating system. MVCC, xmin/xmax, and Snapshot Isolation in PostgreSQL PostgreSQL uses the Multi-Version Concurrency Control (MVCC) mechanism to prevent parallel working requests (transactions) from interfering with each other's work. The foundation of MVCC is that each transaction sees a consistent "snapshot" of the database, a picture of records at the moment of its start. Database table bloat There are multiple factors than can contribute to database table bloat, which can impact queries performance and waste storage making your database grow indefinitely: Long-Running Transactions: Active or idle transactions freeze the visibility horizon (xmin), preventing VACUUM from freeing space occupied by dead rows. Delayed Autovacuum: Aggressive write workloads running faster than default or poorly tuned autovacuum settings leave dead space unrecovered. Replication & Standby Lags: Delayed logical replication slots or hot_standby_feedback on replicas force the primary database to hold old row versions. Mass Batch Deletions: Large single operation deletes free page space that PostgreSQL cannot shrink back from the OS without a heavy maintenance command like VACUUM FULL or tools like pg_repack / pg_squeeze. The diagram below illustrates how a PostgreSQL healthy table looks like comparing to a bloated table. How bloat impacts performance Disk space consumption: Bloat occupies excess disk space, and reading/writing the same amount of real data requires more I/O operations. Accordingly, disk usage and backup time increase because PostgreSQL has to operate on a large volume, while part of the data is effectively useless: Disk space consumption: Bloat occupies excess disk space, and reading/writing the same amount of real data requires more I/O operations. Accordingly, disk usage and backup time increase because PostgreSQL has to operate on a large volume, while part of the data is effectively useless. Increased I/O: The database must scan more physical data from disk to find the actual, active data. When PostgreSQL executes a query, it reads the required data pages, or blocks, from storage into memory. When those pages are heavily populated with dead tuples, fewer useful rows fit within each block. As a result, PostgreSQL may need to scan more physical data blocks to retrieve the same amount of active data.This additional scanning increases I/O activity, which can slow down queries and put extra pressure on storage resources. In simple terms, the database has to work through unnecessary data before finding the rows that actually matter. CPU overhead: PostgreSQL relies on Multi-Version Concurrency Control (MVCC) to determine which tuple versions are visible to each transaction. During a query, PostgreSQL evaluates tuple visibility according to the transaction's snapshot. When many dead tuples are present, more obsolete tuple versions may need to be examined while retrieving active data. This additional processing consumes CPU resources and increases the overall cost of read operations. Reduced cache efficiency: Bloated tables take up more memory space, reducing the amount of data that can be cached for faster access. Having data pages in memory does not completely eliminate the performance impact of dead tuples. Dead tuples still consume space within those pages, meaning that fewer active rows can be stored in the same amount of cached memory. As PostgreSQL scans the pages, it must evaluate tuple visibility and may need to examine more pages to find the active rows required by the query. Over time, this reduces the effective capacity of the buffer cache, increases memory pressure, and can contribute to additional cache misses and storage reads. Bloat can slow index lookups and scans, which will show up in slowly increasing query times and changing query plans. The impact of dead tuples is not limited to table data; indexes can become bloated as well. As rows are updated or deleted, obsolete index entries can remain until PostgreSQL's maintenance mechanisms clean them up. As these entries accumulate, the index grows and becomes less efficient. A larger index means PostgreSQL may need to examine more index pages to locate the required rows, increasing cache pressure and potentially causing more storage I/O. Once a matching entry is found, PostgreSQL may still need to visit the corresponding table page to verify tuple visibility. This additional work increases the cost of index scans and can further degrade query performance. Slower queries: All of these factors combine to make queries slower and less efficient. How to Monitor Table Bloat One of the most popular and efficient tools for monitoring bloated tables in PostgreSQL is pgstattuple extension. Once installed, you can use the pgstattuple function to get an overview of a particular table SELECT * FROM pgstattuple('your_table_name'); This query will return several key metrics: table_len: Total size of the table/index. tuple_count: Number of tuples (rows) in the table. tuple_len: Total size of all tuples. dead_tuple_count: Number of dead tuples, which contribute to bloat. dead_tuple_len: Total size occupied by dead tuples. SELECT * FROM pgstattuple('test'); table_len tuple_count tuple_len tuple_percent dead_tuple_count dead_tuple_len dead_tuple_percent free_space free_percent 2656870400 60000000 2160000000 81.3 0 0 0 7789300 0.29 Apart from pgstattuple, you can follow other methods to monitor your Postgres bloat, for example, pg_stat_user_tables provides statistics about access to user tables, which can help indirectly identify bloat. If you observe that seq_scan is increasing for a table, it might be an indication that the table's indexes are not performing optimally due to bloat. Other very useful query can be found in Show database bloat - PostgreSQL wiki. How to fix PostgreSQL bloats Rebuilding tables The most effective way to reclaim space in PostgreSQL is to rebuild the table, which physically rewrites the table's data and indexes, eliminating unused space and fragmentation. To rebuild a table and reorganize its physical space we can use native PostgreSQL feature vacuum full, however it requires exclusive lock on the table which, depending on the table size, can have a significant impact on your application availability. A special consideration regarding large object, is a specific vacuumlo process that can be used to remove orphaned large objects from a PostgreSQL database before a vacuum full can be done against pg_largeobjetcs. There are other options that can be used online such as pg_repack and pg_squeeze extensions. For both these extensions the target table must have either a PRIMARY KEY or a UNIQUE index on a NOT NULL column for the operation to be successful. Rebuilding Indexes In PostgreSQL B-tree index pages that become completely empty can be reclaimed and reused. However, partially emptied pages may still lead to inefficient space utilisation. For example, if most index entries on a page are deleted but a small number remain, the page continues to occupy space. As a result, workloads that repeatedly delete most, but not all, index entries across many key ranges can gradually accumulate sparsely populated index pages and lead to index bloat. For these usage patterns, periodic REINDEX should be considered to restore more efficient space utilisation. Optimizing indexing Regularly review and remove unnecessary, overlapping, or redundant indexes to prevent their contribution to bloat. We can leverage on Azure PostgreSQL Flexible Server index tuning feature for recommendations. Partition large tables Bulk data loads and deletions can be handled more efficiently through TABLE PARTITIONING, provided the partitioning strategy is designed around the workload. Instead of deleting large volumes of rows, an entire partition can be removed using DROP TABLE or detached with ALTER TABLE DETACH PARTITION. This is significantly faster than performing a bulk DELETE and avoids the additional VACUUM work that large deletions can generate Fine tuning autovacuum If autovacuum does not run frequently or aggressively enough, dead tuples can accumulate, causing PostgreSQL to consume additional disk space and potentially leading to significant table bloat. By tuning autovacuum thresholds and execution settings to match your workload, particularly for tables with heavy DML activity, you can ensure that dead space is reclaimed sooner and made available for reuse. Difference between dead tuples and reusable free space Characteristic Dead Tuples Reusable Free Space Definition Deleted or obsolete row versions that are no longer valid for new transactions. Valid empty space within a page that has been cleared of dead metadata. Reusability Cannot be overwritten by new INSERT or UPDATE operations. Can be immediately reused for incoming row versions. Visibility Trapped by the system until active older transactions finish using them. Available to the engine and registered in the Free Space Map (FSM). How to Resolve A VACUUM cycle purges dead tuples, turning them into Free Space. A VACUUM FULL or pg_repack is required to shrink the file and return it to the OS. Overhead of Diagnostics and Maintenance Bloat maintenance is not free. Both identifying bloat and reclaiming disk space can consume significant database resources, so these operations should be planned carefully on production systems. pgstattuple diagnostics: Detailed inspection of large tables can generate considerable read I/O and buffer activity. For routine monitoring, lightweight statistics such as n_dead_tup from pg_stat_user_tables are generally better suited to identifying potential problematic tables, while deeper inspection can be reserved for targeted investigations. VACUUM FULL: Reclaiming disk space with VACUUM FULL requires rewriting the table and holding an ACCESS EXCLUSIVE lock. This can make the table unavailable to concurrent workloads and requires additional disk space while the new copy is created. pg_repack: For environments where prolonged blocking is unacceptable, pg_repack provides an online alternative that allows normal activity during most of the rebuild. However, the operation still consumes I/O, storage and WAL resources and may require brief locking during its final stages. Test case Let’s run a test case to show how bloated tables and bloated indexes can impact storage usage on a PostgreSQL database, as well as impact on query performance. This test was run in an Azure PostgreSQL Flexible Server Version 16.15 with a compute size Standard_D2ds_v5 (2 vCores, 8 GiB memory, 3750 max iops) / 256 GiB. postgres=> CREATE DATABASE bloat; CREATE DATABASE postgres=> \c bloat bloat=> SELECT pg_size_pretty(pg_database_size('bloat')) AS db_size; db_size 7719 kB bloat=> CREATE TABLE test (col1 INT, col2 INT, col3 INT); CREATE TABLE bloat=> INSERT INTO test (col1,col2,col3) SELECT i,i,i FROM generate_series(1, 600000000) AS t(i); bloat=> select count(*) from test; count 600000000 bloat=> CREATE UNIQUE INDEX idx1 ON test (col1); bloat=> SELECT pg_size_pretty(pg_database_size('bloat')) AS db_size; db_size 37 GB bloat=> SELECT pg_size_pretty(pg_total_relation_size('public.test')) AS table_total_size; table_total_size 37 GB bloat=> SELECT pg_size_pretty(pg_relation_size('public.test')) AS table_size; table_size 25 GB bloat=> SELECT pg_size_pretty(pg_relation_size('public.idx1')) AS index_size; index_size 12 GB bloat=> SELECT schemaname, relname, n_dead_tup, n_live_tup, round(n_dead_tup::float/n_live_tup::float*100)dead_pct, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze FROM pg_stat_all_tables WHERE n_live_tup > 0 AND schemaname='public' AND relname ='test'; schemaname | relname | n_dead_tup | n_live_tup | dead_pct | autovacuum_count | last_vacuum | last_autovacuum | last_autoanalyze | last_analyze ------------+---------+------------+------------+----------+------------------+-------------+-----------------+------------------+-------------- public | test | 0 | 600000000 | 0 | 0 | | | | bloat=> SELECT * FROM pgstattuple('test'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -------------+-------------+-------------+---------------+------------------+----------------+--------------------+------------+-------------- 26568654848 | 600000000 | 21600000000 | 81.3 | 0 | 0 | 0 | 77844016 | 0.29 In above output from pg_stat_all_tables, we can see table test with no dead_tuples (n_dead_tup) and from pgstattuple output we confirm it with dead_tuple_count and see there’s very low free space available as well. Server-wide storage usage increases after table test and index idx1 creation bloat=> CREATE TABLE test2 AS SELECT * FROM test; CREATE TABLE bloat=> SELECT pg_size_pretty(pg_relation_size('public.test2')) AS table_size; table_size 25 GB bloat=> \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+-------+-------+-------+-------------+---------------+-------+------------- public | test | table | edu | permanent | heap | 25 GB | public | test2 | table | edu | permanent | heap | 25 GB | (2 rows) bloat=> \d test Table "public.test" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- col1 | integer | | | col2 | integer | | | col3 | integer | | | Indexes: "idx1" UNIQUE, btree (col1) bloat=> \d test2 Table "public.test2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- col1 | integer | | | col2 | integer | | | col3 | integer | | | Server-wide storage usage increases after table test2 creation bloat=> \timing Timing is on. bloat=> EXPLAIN ANALYZE SELECT t.col1, t2.col2 FROM test t JOIN test2 t2 ON t.col1 = t2.col1 AND t2.col1 = 6777731; QUERY PLAN Nested Loop (cost=1000.58..6369286.70 rows=1 width=8) (actual time=120379.691..120439.792 rows=1 loops=1) -> Index Only Scan using idx1 on test t (cost=0.57..2.59 rows=1 width=4) (actual time=0.820..0.824 rows=1 loops=1) Index Cond: (col1 = 6777731) Heap Fetches: 0 -> Gather (cost=1000.00..6369284.10 rows=1 width=8) (actual time=120378.867..120438.962 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on test2 t2 (cost=0.00..6368284.00 rows=1 width=8) (actual time=113739.478..120360.332 rows=0 loops=3) Filter: (col1 = 6777731) Rows Removed by Filter: 200000000 Planning Time: 0.944 ms Execution Time: 120439.911 ms bloat=> DELETE FROM test2 WHERE col1 < 6777731; DELETE 6777730 bloat=> DELETE FROM test2 WHERE col1 > 6777731; DELETE 593222269 bloat=> SELECT COUNT(*) FROM test2; count ------- 1 Time: 162497.229 ms (02:42.497) bloat=> SELECT schemaname, relname,n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze FROM pg_stat_all_tables WHERE relname IN ('test2'); schemaname | relname | n_dead_tup | n_live_tup | autovacuum_count | last_vacuum | last_autovacuum | last_autoanalyze | last_analyze ------------+---------+------------+------------+------------------+-------------+-------------------------------+-------------------------------+-------------- public | test2 | 151260968 | 0 | 2 | | 2026-10-28 15:42:04.591895+00 | 2026-10-28 15:42:08.369063+00 | public | test | 0 | 600000140 | 1 | | 2026-10-19 14:39:09.677403+00 | 2026-10-19 14:39:27.062848+00 | (1 row) bloat=> SELECT schemaname, relname,n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze FROM pg_stat_all_tables WHERE relname IN ('test2'); schemaname | relname | n_dead_tup | n_live_tup | autovacuum_count | last_vacuum | last_autovacuum | last_autoanalyze | last_analyze ------------+---------+------------+------------+------------------+-------------+-------------------------------+-------------------------------+-------------- public | test2 | 0 | 0 | 3 | | 2026-10-28 15:52:23.885401+00 | 2026-10-28 15:42:08.369063+00 | (1 row) bloat=> SELECT * FROM pgstattuple('test2'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -------------+-------------+-----------+---------------+------------------+----------------+--------------------+-------------+-------------- 26568818688 | 1 | 36 | 0 | 0 | 0 | 0 | 26464870540 | 99.61 (1 row) Time: 266381.968 ms (04:26.382) bloat=> SELECT pg_size_pretty(pg_relation_size('public.test2')) AS table_size; table_size 25 GB (1 row) Time: 4.848 ms As we can see even after rows deletion and vacuum performed to remove dead tuples, storage hasn’t decreased. bloat=> EXPLAIN ANALYZE SELECT t.col1, t2.col2 FROM test t JOIN test2 t2 ON t.col1 = t2.col1 AND t2.col1 = 6777731; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.57..3243266.60 rows=1 width=8) (actual time=99702.040..126103.260 rows=1 loops=1) -> Index Only Scan using idx1 on test t (cost=0.57..2.59 rows=1 width=4) (actual time=1.605..1.609 rows=1 loops=1) Index Cond: (col1 = 6777731) Heap Fetches: 0 -> Seq Scan on test2 t2 (cost=0.00..3243264.00 rows=1 width=8) (actual time=99700.432..126101.645 rows=1 loops=1) Filter: (col1 = 6777731) Planning Time: 0.645 ms Execution Time: 126103.317 ms (8 rows) Time: 126107.410 ms (02:06.107) bloat=> VACUUM FULL test2; Time: 126024.034 ms (02:06.024) bloat=> SELECT pg_size_pretty(pg_relation_size('public.test2')) AS table_size; table_size 8192 bytes (1 row) Time: 3.604 ms bloat=> SELECT * FROM pgstattuple('test2'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 8192 | 1 | 36 | 0.44 | 0 | 0 | 0 | 8120 | 99.12 (1 row) Time: 3.682 ms bloat=> EXPLAIN ANALYZE SELECT t.col1, t2.col2 FROM test t JOIN test2 t2 ON t.col1 = t2.col1 AND t2.col1 = 6777731; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.57..3.62 rows=1 width=8) (actual time=0.033..0.036 rows=1 loops=1) -> Index Only Scan using idx1 on test t (cost=0.57..2.59 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1) Index Cond: (col1 = 6777731) Heap Fetches: 0 -> Seq Scan on test2 t2 (cost=0.00..1.01 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1) Filter: (col1 = 6777731) Planning Time: 0.143 ms Execution Time: 0.067 ms (8 rows) Time: 3.663 ms Now after vacuum full storage has decreased as expected. Note: Row versions may still be required by active snapshots and cannot be removed. Long-running transactions or other snapshot-retention conditions can therefore limit space reclamation. VACUUM FULL also requires an exclusive lock and may wait for transactions accessing the table to finish. Always investigate these conditions before running maintenance. test2: Before and after VACUUM FULL Metric Before VACUUM FULL (after autovacuum) After VACUUM FULL Physical table size 26,568,818,688 bytes (reported as 25 GB) 8192 bytes Query execution time (EXPLAIN ANALYZE) 126103.317 ms 0.067 ms Both measurements concern test2 with one live row. Execution times are the server-side values reported by EXPLAIN ANALYZE, not the client-reported elapsed times. These individual test runs illustrate the effect of the rebuild in this scenario and are not a general performance benchmark. Conclusion PostgreSQL table bloat is not simply a matter of counting dead tuples. Autovacuum can remove obsolete row versions and make their space available for reuse without substantially reducing the physical table size. In this test, test2 contained only one live row and no dead tuples after autovacuum, yet still occupied approximately 25 GB, with 99.61% of its space reported as free. The query continued to scan this large physical footprint, demonstrating why a low dead-tuple count alone does not establish that a table is space-efficient. “Rewriting” test2 with VACUUM FULL reduced the table to 8192 bytes, and the recorded query execution time fell from 126103.317 ms to 0.067 ms. These results illustrate the benefit of reclaiming excessive unused space in this specific scenario as the performance benefit for other workloads will vary. The comparison concerns a table reduced to a single row and it does not isolate index bloat or establish a general benchmark. The practical approach is to prevent avoidable bloat first: tune autovacuum for the workload, investigate long-running transactions and replication-related retention, and review indexing and partitioning strategies. Use lightweight statistics to identify candidates for investigation, then targeted diagnostics to assess dead tuples, reusable free space and physical size alongside query behaviour. When a rebuild is justified, choose VACUUM FULL or a supported online alternative according to locking tolerance, available storage and resource capacity. Successful maintenance should address the cause of bloat as well as its symptoms, and its benefit should be verified through before-and-after measurements. References https://learn.microsoft.com/azure/postgresql/troubleshoot/how-to-autovacuum-tuning https://www.postgresql.org/docs/current/sql-vacuum.html https://www.postgresql.org/docs/current/pgstattuple.html https://www.postgresql.org/docs/current/sql-vacuum.html https://github.com/reorg/pg_repack https://learn.microsoft.com/azure/postgresql/troubleshoot/how-to-perform-fullvacuum-pg-repack https://github.com/cybertec-postgresql/pg_squeeze https://www.postgresql.org/docs/current/sql-reindex.html https://www.postgresql.org/docs/current/ddl-partitioning.html https://wiki.postgresql.org/wiki/Show_database_bloat308Views1like0CommentsPostgreSQL 18 EXPLAIN Improvements
PostgreSQL 18 makes EXPLAIN more useful for real-world performance troubleshooting by exposing more of what happens during query execution. Beyond plan shape and execution time, it provides better visibility into buffer activity, repeated index access, memory and disk usage, WAL behavior, parallel execution, and window-function processing. This makes it easier to identify the actual source of a performance issue and make more targeted tuning decisions. In this blog, we’ll explore the key EXPLAIN improvements in PostgreSQL 18 and see how they can help diagnose performance issues more effectively. What’s New in PostgreSQL 18 EXPLAIN 1) BUFFERS Is Now Automatic with EXPLAIN ANALYZE Before PostgreSQL 18, EXPLAIN ANALYZE did not show buffer stats unless you explicitly added BUFFERS. EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5001; EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 5001; PostgreSQL 18 makes EXPLAIN ANALYZE more useful by including buffer statistics automatically. This helps reveal what execution time alone cannot: where the data is coming from. shared hit=120 read=12500 → disk-heavy shared hit=12500 read=120 → cache-heavy When investigating performance, correlate time, rows/loops, and buffer activity. The following example creates a large table with multiple rows per customer and uses an index to retrieve one customer’s rows. We then run the same EXPLAIN ANALYZE query in PostgreSQL 17 and PostgreSQL 18. -- Setup CREATE TABLE buffer_test (id INT, customer_id INT); INSERT INTO buffer_test SELECT x, (x % 10000) + 1 FROM generate_series(1,1000000) x; CREATE INDEX ON buffer_test(customer_id); VACUUM ANALYZE buffer_test; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM buffer_test WHERE customer_id = 5001; EXPLAIN Output PG 17 Bitmap Heap Scan on buffer_test (actual rows=100 loops=1) Recheck Cond: (customer_id = 5001) Heap Blocks: exact=100 -> Bitmap Index Scan on buffer_test_customer_id_idx (actual rows=100 loops=1) Index Cond: (customer_id = 5001) Planning Time: 0.076 ms Execution Time: 0.225 ms (7rows) EXPLAIN output PG 18 Bitmap Heap Scan on buffer_test (actual rows=100.00 loops=1) Recheck Cond: (customer_id = 5001) Heap Blocks: exact=100 Buffers: shared hit=103 -> Bitmap Index Scan on buffer_test_customer_id_idx (actual rows=100.00 loops=1) Index Cond: (customer_id = 5001) Index Searches: 1 Buffers: shared hit=3 Planning: Buffers: shared hit=6 Planning Time: 0.097 ms Execution Time: 0.142 ms (12 rows) In the PostgreSQL 18 output, focus on Buffers: shared hit and shared read to determine whether the query is primarily using cached pages or reading from storage. A high read count may indicate an I/O-heavy execution, while a high hit count indicates that most pages were already available in shared buffers. A query with substantial physical reads may be more I/O-sensitive, while high shared hits indicate the required pages were already cached. 2) Index Search Counts (Index Searches) PostgreSQL 18 adds Index Searches to show how many separate index searches were performed by an index scan. This is especially useful for nested loop joins, where an inner index scan may be searched repeatedly for rows produced by the outer side. The following example joins customers_idx with orders_idx. The query retrieves 10 customers, and for each customer PostgreSQL searches the orders index for matching rows. This makes it easy to see how PostgreSQL 18 exposes repeated index access that was not directly visible in PostgreSQL 17. -- Setup CREATE TABLE customers_idx ( id INT PRIMARY KEY ); CREATE TABLE orders_idx ( id INT, customer_id INT ); INSERT INTO customers_idx SELECT x FROM generate_series(1,1000) x; INSERT INTO orders_idx SELECT x, (x % 1000) + 1 FROM generate_series(1,100000) x; CREATE INDEX orders_customer_idx ON orders_idx(customer_id); VACUUM ANALYZE customers_idx; VACUUM ANALYZE orders_idx; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT c.id, o.id FROM customers_idx c JOIN orders_idx o ON o.customer_id = c.id WHERE c.id <= 10; EXPLAIN Output PG 17 Nested Loop (actual rows=1000 loops=1) -> Index Only Scan using customers_idx_pkey on customers_idx c (actual rows=10 loops=1) Index Cond: (id <= 10) Heap Fetches: 0 -> Bitmap Heap Scan on orders_idx o (actual rows=100 loops=10) Recheck Cond: (c.id = customer_id) Heap Blocks: exact=1000 -> Bitmap Index Scan on orders_customer_idx (actual rows=100 loops=10) Index Cond: (customer_id = c.id) Planning Time: 0.305 ms Execution Time: 0.666 ms (11 rows) EXPLAIN Output PG 18 Nested Loop (actual rows=1000.00 loops=1) Buffers: shared hit=1023 -> Index Only Scan using customers_idx_pkey on customers_idx c (actual rows=10.00 loops=1) Index Cond: (id <= 10) Heap Fetches: 0 Index Searches: 1 Buffers: shared hit=3 -> Bitmap Heap Scan on orders_idx o (actual rows=100.00 loops=10) Recheck Cond: (c.id = customer_id) Heap Blocks: exact=1000 Buffers: shared hit=1020 -> Bitmap Index Scan on orders_customer_idx (actual rows=100.00 loops=10) Index Cond: (customer_id = c.id) Index Searches: 10 Buffers: shared hit=20 Planning: Buffers: shared hit=77 read=3 Planning Time: 0.362 ms Execution Time: 0.590 ms (19 rows) Compare Index Searches with loops, actual rows, and Buffers. Here, the inner index is searched 10 times, exposing the repeated index probes performed by the nested loop. A high Index Searches count can reveal expensive repeated index access and help determine whether the join strategy, indexing, or query structure should be reviewed. 3) Fractional Actual Row Counts EXPLAIN now supports fractional row averages for repeatedly executed nodes, for example: rows=0.25 loops=1000 This is not “partial rows”; it is average output per execution. It helps reveal behavior in: nested loops parameterized scans correlated subqueries selective joins The following example uses a correlated subquery that executes repeatedly but returns a row only for a fraction of those executions. PostgreSQL 18 preserves fractional actual rows values, providing a more accurate picture of how often the node produces a result. -- Setup: CREATE TABLE frac_outer (id INT PRIMARY KEY); CREATE TABLE frac_inner (id INT PRIMARY KEY,value TEXT); INSERT INTO frac_outer SELECT x FROM generate_series(1,1000) x; -- Only 1 out of every 4 outer rows has a match INSERT INTO frac_inner SELECT x, 'match' FROM generate_series(4,1000,4) x; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT o.id, (SELECT i.value FROM frac_inner i WHERE i.id = o.id) FROM frac_outer o; EXPLAIN Output PG 17: Seq Scan on frac_outer o (actual rows=1000 loops=1) SubPlan 1 -> Index Scan using frac_inner_pkey on frac_inner i (actual rows=0 loops=1000) Index Cond: (id = o.id) Planning Time: 0.082 ms Execution Time: 0.790 ms (6 rows) EXPLAIN Output PG 18 Seq Scan on frac_outer o (actual rows=1000.00 loops=1) Buffers: shared hit=1255 SubPlan 1 -> Index Scan using frac_inner_pkey on frac_inner i (actual rows=0.25 loops=1000) Index Cond: (id = o.id) Index Searches: 1000 Buffers: shared hit=1250 Planning: Buffers: shared hit=27 Planning Time: 0.190 ms Execution Time: 0.752 ms (11 rows) Compare actual rows with loops. An actual rows=0.25 over 1,000 loops indicates that the node returns a row on average once every four executions. This is useful when troubleshooting nested loops and correlated subqueries where rounding the average to zero can hide meaningful execution behavior. 4) Better Memory/Disk Visibility for More Nodes PostgreSQL 18 extends memory and disk reporting to Material, Window Aggregate, and CTE-related nodes. This helps identify when growing intermediate results start using disk and cause query performance to degrade. Rather than immediately increasing work_mem, use EXPLAIN ANALYZE to determine which node is consuming resources and why. The following query calculates a running total over one million rows with work_mem intentionally limited to 1 MB. This creates memory pressure and causes the Sort operation to spill to disk, making it easier to see how PostgreSQL 18 identifies resource usage at individual plan nodes. -- Setup CREATE TABLE memory_test (id INT, dept_id INT, amount NUMERIC); INSERT INTO memory_test SELECT x, (x % 10) + 1, random() * 1000 FROM generate_series(1,1000000) x; VACUUM ANALYZE memory_test; SET work_mem = '1MB'; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT id, dept_id,amount, SUM(amount) OVER ( PARTITION BY dept_id ORDER BY id) FROM memory_test; EXPLAIN Output PG 17 WindowAgg (actual rows=1000000 loops=1) -> Sort (actual rows=1000000 loops=1) Sort Key: dept_id, id Sort Method: external merge Disk: 28592kB -> Seq Scan on memory_test (actual rows=1000000 loops=1) Planning Time: 0.159 ms Execution Time: 1069.011 ms (7 rows) EXPLAIN Output PG 18 WindowAgg (actual rows=1000000.00 loops=1) Window: w1 AS (PARTITION BY dept_id ORDER BY id) Storage: Memory Maximum Storage: 17kB Buffers: shared hit=6371, temp read=10716 written=10781 -> Sort (actual rows=1000000.00 loops=1) Sort Key: dept_id, id Sort Method: external merge Disk: 28592kB Buffers: shared hit=6371, temp read=10716 written=10781 -> Seq Scan on memory_test (actual rows=1000000.00 loops=1) Buffers: shared hit=6370 Planning Time: 0.055 ms Execution Time: 1129.466 ms (12 rows) The WindowAgg uses only 17 KB, while the Sort spills about 28 MB to disk. This clearly identifies the Sort, rather than the WindowAgg, as the source of the disk spill. This additional visibility helps pinpoint the resource-intensive node before making tuning changes such as increasing work_mem. 5) Better WAL Visibility (buffers full) For write-heavy statements, PostgreSQL already supports: EXPLAIN (ANALYZE, WAL) UPDATE ...; PostgreSQL 18 adds another signal: how often WAL buffers became full. Look at WAL as a set: Records : WAL records generated Fpi : Full-page images written to WAL Bytes : Total WAL volume generated buffers full : Number of times WAL buffers became full rows modified: Number of rows affected by the operation This is useful for bulk UPDATE/DELETE/INSERT diagnostics and replication-impact analysis. The following example updates 250,000 rows after a checkpoint, generating substantial WAL activity. -- Setup CREATE TABLE wal_test (id INT PRIMARY KEY,status INT,payload TEXT); INSERT INTO wal_test SELECT x, 0, repeat('x', 500) FROM generate_series(1,500000) x; VACUUM ANALYZE wal_test; CHECKPOINT; EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF) UPDATE wal_test SET status = 1 WHERE id <= 250000; EXPLAIN Output PG 17 Update on wal_test (actual rows=0 loops=1) WAL: records=753424 fpi=17365 bytes=187303863 -> Index Scan using wal_test_pkey on wal_test (actual rows=250000 loops=1) Index Cond: (id <= 250000) Planning Time: 0.231 ms Execution Time: 2105.951 ms (6 rows) EXPLAIN Output PG 18 Update on wal_test (actual rows=0.00 loops=1) Buffers: shared hit=1821461 dirtied=34716 written=17355 WAL: records=753424 fpi=17365 bytes=184498617 buffers full=11293 -> Index Scan using wal_test_pkey on wal_test (actual rows=250000.00 loops=1) Index Cond: (id <= 250000) Index Searches: 1 Buffers: shared hit=17353 Planning: Buffers: shared hit=14 dirtied=1 Planning Time: 0.187 ms Execution Time: 2149.051 ms (11 rows) Check buffers full alongside WAL records, FPIs, and bytes to identify how often WAL buffers became full during the write operation. In PostgreSQL 18, inspect records, fpi, bytes, and buffers full together with the number of affected rows. These values help assess the WAL cost of a bulk operation and its possible effect on write throughput, storage, and replication. Frequent buffers full events can indicate sustained WAL pressure, but should be evaluated alongside total WAL volume and execution behavior 6) Improved Parallel Bitmap Heap Scan Stats PostgreSQL 18 adds worker cache statistics for parallel bitmap heap scans, improving per-worker visibility in parallel plans. When analyzing parallel execution, go beyond Workers Planned and Workers Launched and correlate worker-level behavior with buffers and row flow. The following example uses a Parallel Bitmap Heap Scan to retrieve matching rows through multiple processes. PostgreSQL 18 exposes additional worker-level statistics, allowing the activity of individual workers to be compared with the overall parallel scan. -- Setup CREATE TABLE pb_test AS SELECT x AS id, x % 10 AS category FROM generate_series(1,100000) x; CREATE INDEX pb_test_category_idx ON pb_test(category); VACUUM ANALYZE pb_test; SET max_parallel_workers_per_gather = 2; SET min_parallel_table_scan_size = 0; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET enable_seqscan = off; SET enable_indexscan = off; EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS OFF, TIMING OFF) SELECT * FROM pb_test WHERE category < 3; EXPLAIN Output PG 17 Gather (actual rows=30000 loops=1) Output: id, category Workers Planned: 2 Workers Launched: 1 Buffers: shared hit=443 read=27 -> Parallel Bitmap Heap Scan on public.pb_test (actual rows=15000 loops=2) Output: id, category Recheck Cond: (pb_test.category < 3) Heap Blocks: exact=443 Buffers: shared hit=443 read=27 Worker 0: actual rows=0 loops=1 -> Bitmap Index Scan on pb_test_category_idx (actual rows=30000 loops=1) Index Cond: (pb_test.category < 3) Buffers: shared read=27 Query Identifier: 6421728296517627451 Planning: Buffers: shared hit=9 Planning Time: 0.065 ms Execution Time: 12.218 ms (19 rows) EXPLAIN Output PG 18 Gather (actual rows=30000.00 loops=1) Output: id, category Workers Planned: 2 Workers Launched: 1 Buffers: shared hit=445 read=27 -> Parallel Bitmap Heap Scan on public.pb_test (actual rows=15000.00 loops=2) Output: id, category Recheck Cond: (pb_test.category < 3) Heap Blocks: exact=443 Buffers: shared hit=445 read=27 Worker 0: actual rows=0.00 loops=1 Buffers: shared hit=2 -> Bitmap Index Scan on pb_test_category_idx (actual rows=30000.00 loops=1) Index Cond: (pb_test.category < 3) Index Searches: 1 Buffers: shared read=27 Query Identifier: 6365259442062613493 Planning: Buffers: shared hit=9 Planning Time: 0.085 ms Execution Time: 17.366 ms (21 rows) Workers Planned and Workers Launched alone do not show how much useful work each worker performed. Worker-level statistics can reveal cases where work distribution is uneven or a launched worker contributes very little. 7) Disabled Plan Nodes Are Explicitly Identified When experimenting with planner toggles: SET enable_seqscan = off; SET enable_hashjoin = off; SET enable_nestloop = off; PostgreSQL 18 can indicate disabled nodes more clearly in plan output, making “what-if” diagnostics easier to interpret. The following example disables sequential scans and executes a leading-wildcard search that has no suitable B-tree index access path. PostgreSQL still needs to perform a sequential scan, but PostgreSQL 18 makes this explicit in the plan with Disabled: true. -- Setup CREATE TABLE disabled_test ( id INT, notes TEXT); INSERT INTO disabled_test SELECT x, CASE WHEN x % 100 = 0 THEN 'order checked manually' ELSE 'normal order' END FROM generate_series(1,100000) x; VACUUM ANALYZE disabled_test; SET enable_seqscan = off; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT * FROM disabled_test WHERE notes LIKE '%checked%'; EXPLAIN Output PG 17: Seq Scan on disabled_test (actual rows=1000 loops=1) Filter: (notes ~~ '%checked%'::text) Rows Removed by Filter: 99000 Planning Time: 0.065 ms Execution Time: 7.045 ms (5 rows) EXPLAIN Output PG 18: Seq Scan on disabled_test (actual rows=1000.00 loops=1) Disabled: true Filter: (notes ~~ '%checked%'::text) Rows Removed by Filter: 99000 Buffers: shared hit=642 Planning: Buffers: shared hit=8 Planning Time: 0.096 ms Execution Time: 6.992 ms (9 rows) This makes planner experiments easier to interpret because you can distinguish between a normally selected plan node and one PostgreSQL used even though that planner method had been disabled. 8) Better Window Function Visibility Plans now expose additional details about window-function arguments. Combined with richer Window Aggregate memory/disk reporting, this improves troubleshooting for analytical workloads using: ROW_NUMBER() RANK() SUM() OVER (...) AVG() OVER (...) LAG() / LEAD() The following example uses LAG() to retrieve the previous value within each department. PostgreSQL 18 exposes more information about the window itself, making it easier to associate the window function with its partitioning, ordering, and resource usage. -- Setup CREATE TABLE window_test (id INT,dept_id INT,amount NUMERIC); INSERT INTO window_test VALUES (1, 1, 100), (2, 1, 200), (3, 1, 300), (4, 2, 150), (5, 2, 250); VACUUM ANALYZE window_test; EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF) SELECT id, dept_id,amount, LAG(amount, 1, 0) OVER ( PARTITION BY dept_id ORDER BY id ) AS previous_amount FROM window_test; EXPLAIN Output PG 17 WindowAgg (actual rows=5 loops=1) Output: id, dept_id, amount, lag(amount, 1, '0'::numeric) OVER (?) -> Sort (actual rows=5 loops=1) Output: id, dept_id, amount Sort Key: window_test.dept_id, window_test.id Sort Method: quicksort Memory: 25kB -> Seq Scan on public.window_test (actual rows=5 loops=1) Output: id, dept_id, amount Query Identifier: -674688654798365229 Planning Time: 0.083 ms Execution Time: 0.038 ms (11 rows) EXPLAIN Output PG 18 WindowAgg (actual rows=5.00 loops=1) Output: id, dept_id, amount, lag(amount, 1, '0'::numeric) OVER w1 Window: w1 AS (PARTITION BY window_test.dept_id ORDER BY window_test.id) Storage: Memory Maximum Storage: 17kB Buffers: shared hit=38 -> Gather Merge (actual rows=5.00 loops=1) Output: id, dept_id, amount Workers Planned: 1 Workers Launched: 1 Buffers: shared hit=38 -> Sort (actual rows=2.50 loops=2) Output: id, dept_id, amount Sort Key: window_test.dept_id, window_test.id Sort Method: quicksort Memory: 25kB Buffers: shared hit=38 Worker 0: actual rows=0.00 loops=1 Sort Method: quicksort Memory: 25kB Buffers: shared hit=37 -> Parallel Seq Scan on public.window_test (actual rows=2.50 loops=2) Disabled: true Output: id, dept_id, amount Buffers: shared hit=1 Worker 0: actual rows=0.00 loops=1 Query Identifier: -8082484858136532801 Planning: Buffers: shared hit=13 Planning Time: 0.106 ms Execution Time: 13.623 ms (28 rows) Check the Window, Output, and Storage fields to understand the window definition, function arguments, and WindowAgg memory usage. Instead of displaying only OVER (?), PostgreSQL 18 makes it easier to identify the applicable window definition and correlate it with resource consumption. PostgreSQL 17 and Earlier vs PostgreSQL 18 Area Earlier behavior PostgreSQL 18 Buffers with ANALYZE Usually requested explicitly Included automatically Index diagnostics Loops/time based interpretation Adds Index Searches Row precision Less precise averages Fractional row counts Material/Window/CTE resource visibility Limited Expanded memory/disk details WAL diagnostics records/fpi/bytes Adds buffers full Parallel bitmap visibility Less worker cache detail Worker cache statistics Disabled nodes Less explicit Explicitly indicated Conclusion With PostgreSQL 18, EXPLAIN provides deeper visibility into query execution through automatic buffer statistics, index search counts, fractional row reporting, improved memory and WAL details, worker-level cache statistics, and clearer window-function information. These enhancements make it easier to identify bottlenecks and make evidence-based tuning decisions.200Views2likes0CommentsDecisions, decisions: Your guide to PostgreSQL at Microsoft Ignite 2026
Microsoft Ignite returns to San Francisco and online from November 17 to 20, 2026, and PostgreSQL is all over the agenda. Postgres has become the database of choice for modern application development, and more organizations are now relying on it to modernize the systems they already run and to power the AI apps and agents they are building next. Whether you are a technology leader shaping your data strategy, an architect weighing your options, or a developer who wants to see what's possible, we've done the decision making for you just this once. Below are 20 sessions planned for Ignite with PostgreSQL at their core that you can add to your schedule. Plus, we’ve highlighted a few activations and surprises you should plan to check out if you want to learn more about Postgres, engage with the experts, and potentially win an Xbox. One more thing: we have some exciting news to share for PostgreSQL at Microsoft. Tune in on Day 1, Tuesday, November 17, and keep an eye on this blog. Before you build your schedule Ignite has several session formats, and not all of them are streamed. Breakouts are 45 minutes, streamed live for the digital audience and available on demand. Theater sessions (25 minutes) are in person only but recorded for on-demand viewing. Lightning talks (15 minutes), table talks (45 minutes) and hands-on labs (75 minutes) are in-person experiences and are not recorded, so plan to be in the room. Labs require an RSVP. All times below are Pacific Time. Use the session scheduler in the Ignite catalog to save your favorites: Start with the big picture then find the right fit If you're new to PostgreSQL on Azure, or you're deciding where your next workload should land, start here. BRK362 | Data Foundations for Modern Apps, AI, and Agents Wednesday, Nov 18, 9:00 AM Join Microsoft engineering leaders to see how the database portfolio across Azure and Microsoft Fabric helps organizations modernize mission-critical applications while building a new generation of intelligent experiences. BRK366 | Database SOS: Choosing the right engine for your job Thursday, Nov 19, 1:30 PM Experts from across the Azure databases team compare database engines and share how to pick the right one for your next project. THR923 | What's new for open source data on Azure? Thursday, Nov 19, 1:30 PM Catch the most important open-source data updates on Azure in one technical briefing, including enhancements to Azure HorizonDB and Azure Database for PostgreSQL, and learn which capabilities matter most for your next modernization decision. THR884 | Two PostgreSQL services, one confident architecture decision Wednesday, Nov 18, 2:15 PM Which PostgreSQL on Azure service is the right fit for your workload? See how Azure Database for PostgreSQL compares to Azure HorizonDB across scale, availability, AI integration, operations and modernization needs. TT1865 | Bring your workload, leave with the best Postgres fit Tuesday, Nov 17, 2:00 PM Bring a real workload and pressure-test your architecture with peers and PostgreSQL experts. Compare requirements for scale, availability, migration, AI, operations and cost, and leave with a clearer decision framework, not a generic product pitch. Build AI apps and agents on PostgreSQL For teams turning operational data into AI experiences, these sessions show how to keep the stack simple and the data trusted. BRK370 | Build integrated AI apps with Azure HorizonDB Tuesday, Nov 17, 4:30 PM See how Azure HorizonDB brings PostgreSQL data together with AI functions, durable AI pipelines, vector and hybrid search, semantic ranking and knowledge graphs, through a technical walkthrough and live demo. BRK359 | Build intelligent PostgreSQL apps with Microsoft Fabric HorizonDB Tuesday, Nov 17, 3:15 PM Discover a Fabric-native PostgreSQL experience that lets applications query operational and analytical data through a single PostgreSQL experience, reducing data movement and ETL complexity while delivering real-time insights with built-in governance. THR881 | Microsoft Fabric HorizonDB: One PostgreSQL endpoint for AI apps Thursday, Nov 19, 11:05 AM Build intelligent applications without stitching together databases, analytics platforms and AI services. See how one PostgreSQL endpoint can unify transactional and analytical workloads so you can query live operational data and large-scale analytics together. LTG1254 | 5 ways to simplify your Postgres AI data pipelines on Azure Tuesday, Nov 17, 2:00 PM You can't ship AI features if pipelines break, drift or need constant glue code. Learn five PostgreSQL patterns to keep content fresh, generate embeddings and serve context to your applications. LTG1251 | Do you really need a separate vector database? Wednesday, Nov 18, 9:50 AM Choose a retrieval architecture based on the problem, not the label. In 15 minutes, learn when PostgreSQL-native vector, keyword and hybrid search can support an AI application, when graph context adds value, and which signals truly justify a separate service. LTG1252 | See Azure HorizonDB come alive in VS Code Wednesday, Nov 18, 1:20 PM Watch the PostgreSQL developer experience get faster right inside VS Code. This live demo shows schema exploration, querying and AI-assisted development for Azure HorizonDB without leaving your editor. LAB692 | Build a grounded AI agent with Azure HorizonDB for PostgreSQL Tuesday, Nov 17, 6:15 PM; Wednesday, Nov 18, 12:00 PM; Thursday, Nov 19, 2:45 PM; Friday, Nov 20, 9:00 AM Your agent is only as good as the data behind it. Build a working agent that retrieves trusted context from operational PostgreSQL data: create an AI pipeline, generate embeddings, combine vector and keyword retrieval, and connect the results to an agent workflow. Modernize and migrate with confidence Moving off a legacy database or running a large Postgres fleet? These sessions focus on lowering risk and moving faster. BRK451 | Accelerate Your Move to PostgreSQL with AI-Assisted Migration Thursday, Nov 19, 9:00 AM Modernizing databases doesn't have to be complex. Learn how AI-assisted and built-in tools simplify assessment, schema and code conversion, and data migration, and see a live demo of how to identify blockers early and build a repeatable modernization path. BRK368 | Postgres in Production on Azure: Lessons in Fleet Modernization Thursday, Nov 19, 11:30 AM Running PostgreSQL at scale requires performance, security, resilience and cost efficiency. Explore the latest innovations in Azure Database for PostgreSQL, including fleet management, elastic clusters, next-generation compute and storage, and immutable backups, and leave with proven patterns and real-world insights TT1702 | Eliminate your biggest Oracle migration blocker Thursday, Nov 19, 2:25 PM Bring your hardest Oracle migration question. Discuss assessment gaps, unsupported objects, conversion, validation, downtime and cutover with Microsoft experts and peers, and leave with practical next steps for moving to PostgreSQL on Azure. LTG1257 | Upgrade smarter with next-gen Postgres on Azure Thursday, Nov 19, 3:00 PM Upgrades stall when compatibility and rollback risk aren't proven early. See how to turn your next major-version upgrade on Azure Database for PostgreSQL into a repeatable test plan that checks extensions, performance, operations and rollback. Run PostgreSQL securely and reliably at scale For leaders and operators responsible for uptime, compliance and performance. BRK361 | Azure HorizonDB: Deep Dive into Architecture and Internals Wednesday, Nov 18, 4:15 PM Join the engineering and product leaders behind Azure HorizonDB for a look under the hood of a cloud-native PostgreSQL service: the storage engine, disaggregated compute and storage, replication, recovery design, and the elasticity that delivers predictable performance and resilience at scale. THR882 | Stop tuning databases, start optimizing with AI Thursday, Nov 19, 10:10 AM Most organizations don't have a database performance problem; they have an expertise bottleneck. See a new approach that combines observability, diagnosis, optimization recommendations and controlled remediation in a single AI-powered experience, while you keep governance and control. LTG1258 | Break your Postgres before production causes a breakdown Tuesday, Nov 17, 4:00 PM Validate recovery before an outage becomes the test. Watch a controlled PostgreSQL failure exercise and trace how backup, restore, immutability, geo-redundancy and retention choices affect your recovery objectives. TT1866 | Lock it down: Running PostgreSQL in regulated environments Tuesday, Nov 17, 4:00 PM Compare practical approaches for operating PostgreSQL under security, compliance and recovery requirements, covering identity, FIPS mode, immutable backups, long-term retention and confidential virtual machines. Bring your constraints and hear how peers turn policy into architecture. Play Decision Point: A game for Postgres Between sessions, come put your instincts to the test. Decision Point: A game for Postgresis a browser-based game that puts you in the role of a lead engineer racing to get an AI application launch-ready using Azure HorizonDB in VS Code. You don't need to be a database expert to play. Every round is a real-world decision, and every decision counts. In the "Ship the AI Agent" scenario, you're the lead engineer for Basket IQ, an AI shopping agent that is about to launch. You'll work through three engineering incidents and make the architecture and optimization calls that shape your Launch Readiness score. You'll find Decision Point in The Hub at various spots: Azure Community AMD booth #6044 Azure HorizonDB booth #AZ-11 See you at Ignite Whether you join us in San Francisco or virtually, we can't wait to connect. Register and explore the full catalog, stop by the Azure HorizonDB booth in the Expert Meet-Up area to meet the PostgreSQL team, and follow Microsoft for PostgreSQL on LinkedIn and X for live updates from the event. And don't forget to play Decision Point.282Views2likes0CommentsPrivate Database, Public Tools - Securing Azure Database for PostgreSQL Flexible Server Behind MCP
You locked Azure Database for PostgreSQL Flexible Server behind a private network. Congratulations, but a firewall can’t tell what your MCP server is about to do. One poisoned document, one over trusting agent, and your privileged app hands over the whole corporate summary. Politely. Legitimately. No breach required. The fix isn’t more walls. Verify who’s really asking. Swap raw SQL for tight business tools. Lock data down with least privilege and row level security. Treat the LLM as untrusted, and audit every decision. Stop asking “is the database private?” Start asking: who can make it talk, and what can walk out?207Views1like0CommentsAn Index Scan Is Only Half the Story: On-Disk Ordering Affects Performance
Same Query, Same Index, Different Performance I recently worked with a customer planning a migration from on-premises PostgreSQL to Azure Database for PostgreSQL. During performance testing, they noticed something troubling: a query that completed in tens of milliseconds would now take 1-2 seconds in the Azure environment being evaluated. The query was using an index scan, and running it again was fast. But once the relevant data was evicted from cache, the long execution time came back. Naturally, the conversation turned toward storage performance. There was a clear difference in IOPS consumption, with the to-be-production server having 10-20% more IOPS consumption than a server they stood up for comparison purposes. A cold-cache query execution that performed a single Index Scan pulled over 100 pages from heap, whereas the other server only pulled 60 pages. The natural inclination was to think that somehow bloating and/or fragmentation was the cause, so they tried VACUUM FULL but did not see any improvement. That’s when they turned to us. Is the Index Really the Problem? The primary concern that the customer had – an Index Scan was not performing as well as expected – surfaced two hypotheses: something was wrong with the index, (i.e., bloat, leaf density, fragmentation), or something was wrong with the storage. Digging in with pg_stattuple and pg_statindex , we didn’t see anything glaringly wrong with leaf density or fragmentation. We also saw nothing from our telemetry that anything was wrong with the storage. With the usual suspects out of the picture, we needed to get back to the basic principles of indexing. It is important to remember that when it comes to indexing a table, a regular index scan uses the index to locate matching tuples, then visits the table’s heap pages to retrieve them. The index is ordered; the heap doesn’t have to be. Suppose a query returns 200 rows. If those rows live on five heap pages, Postgres can retrieve them with relatively little page access. If they are scattered across 100 pages, retrieving the same 200 rows requires a much larger working set. Both queries can say Index Scan in the execution plan, but that doesn’t mean they do equivalent work. When those pages are cached, the difference can be easy to miss. When they need to be read from storage, the extra page accesses become much more expensive. Correlation Was the Hint One useful clue was in pg_stats . Using generic table and column names here: SELECT correlation FROM pg_stats WHERE schemaname = 'public' AND tablename = 'order_items' AND attname = 'order_id'; correlation describes how closely a column’s logical ordering follows the table’s physical ordering. Values near 1 or -1 indicate strong ordering; values near 0 indicate little relationship. Postgres uses this statistic when estimating index scan costs. In this case, the slower environment had a correlation around 0.037 , while the faster performance-test environment was around 0.571 . That certainly suggested a layout difference! But correlation is a sampled, column-wide statistic. It doesn’t tell us exactly how many heap pages contain the rows for one particular lookup. A table can have groups of related rows packed together without those groups appearing in globally sorted order. We needed to measure the thing the query actually cared about. The Query That Made It Click Postgres exposes a system column called ctid , which identifies a tuple’s physical location as (block_number, tuple_number) . Extracting the block number lets us count the distinct heap pages containing our matching rows: SELECT count(*) AS rows_found, count(DISTINCT split_part( trim(both '()' FROM ctid::text), ',', 1 )) AS distinct_heap_pages FROM public.order_items WHERE order_id = 12345; This example assumes an ordinary, non-partitioned table. For a partitioned table, count distinct (tableoid, block_number) pairs instead, since different partitions can have the same block numbers. Also, ctid is a diagnostic tool here, not a permanent row identifier – updates and table rewrites can change it. For two representative lookup values, these were the actual counts, with the lookup values anonymized: Lookup Rows returned Slower environment: heap pages Faster environment: heap pages A 157 119 33 B 200 101 5 There it was. For lookup B, the slower environment’s matching rows occupied about 20 times as many heap pages. These counts aren’t a complete accounting of query I/O – index pages and other accesses still matter – but they directly expose the heap footprint of the result. The index wasn’t failing to find the rows. The rows were simply spread across far more pages once it found them. How Did the Rows Get So Scattered? The customer had loaded data into the Azure environment in parallel. That helped load throughput, but the concurrent loading interleaved rows belonging to different lookup values. Related rows that had been packed together in the source environment no longer landed together in the destination heap. Parallel loading doesn’t inherently guarantee a scattered table. The outcome depends on how work is divided and how inserts reach the heap. In this customer’s case, though, the loading method explained the observed layout difference. Recreating the same index didn’t restore that locality. A B-tree orders its own entries; building or rebuilding it does not reorder the underlying table. This also explained the cache behavior. Repeat executions benefited from cached pages, masking the cost of the larger footprint. Once those pages were evicted, the query had to fetch them again. More scattered data also meant more pages competing for cache space. Fix the Layout, Then Measure Again VACUUM FULL wouldn't solve this layout problem, as it rewrites the table to reclaim space from dead tuples and compact the heap, but it doesn't sort the surviving rows by the lookup key. It might reduce the page count if there is bloat, but related rows can still be interleaved with unrelated rows afterward. The issue here was less about fragmentation or bloat and more about which tuples were arranged together on disk. The next step was to reorganize the heap around the lookup key. CLUSTER can rewrite a table in index order, but it takes an ACCESS EXCLUSIVE lock, so it needs careful scheduling. The customer used pg_repack with --order-by to reorganize the data and reported improved performance on a restored test instance. That’s not a maintenance-free operation either: check extension support, free space, and locking requirements before choosing it. Afterward, refresh statistics with ANALYZE , repeat the distinct-page query, and compare execution plans and timings under comparable cache conditions. Running the diagnostic itself warms pages, so don’t treat the immediately following execution as a cold-cache test! Neither clustering nor repacking permanently maintains the ordering. Future writes can erode locality, so the migration loading strategy and ongoing workload both deserve attention. Final Thoughts Before blaming storage latency, check how many pages each environment needs to read for the same query. For this customer, correlation prompted us to examine the heap layout. Counting unique heap pages showed just how scattered the matching rows were. The lesson wasn’t “don’t load in parallel” or “Azure needs a bigger disk.” Migrating the same rows and indexes doesn’t necessarily migrate the same physical layout. An index scan is only half the story!293Views2likes0Comments