azure database for postgresql
191 TopicsHorizonDB: 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)313Views1like0CommentsMore 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!470Views2likes0CommentsPostgreSQL 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!294Views2likes0CommentsPostgreSQL Performance Bottlenecks: Managing Connections and Read-Intensive Workloads
Introduction PostgreSQL is a robust and highly scalable database system, but as applications grow in size and complexity, performance challenges can begin to emerge. Slow queries, high resource consumption, and inefficient configuration can create bottlenecks that affect query execution and the overall responsiveness of an application. Identifying and addressing these bottlenecks is essential for maintaining consistent PostgreSQL performance, particularly as workloads increase. In this blog, we’ll explore two common performance challenges: managing a high number of database connections and handling read-intensive workloads. We’ll look at how excessive connections can affect PostgreSQL performance and discuss approaches for efficiently scaling read workloads. Too many connections can slow PostgreSQL down The Problem: A high number of concurrent connections can increase memory and CPU usage, potentially affecting overall database performance. The Root Cause: PostgreSQL uses a process-based architecture, where each client connection is handled by a dedicated backend process. As the number of connections grows, the associated resource overhead can become significant. The Solution: Configure max_connections according to the available server resources and workload requirements and use a connection pooler to efficiently manage application connections at scale. In Postgresql servers establishing a connection is a very expensive operation that requires server resources, and when not properly handled, can lead to several performance issues with resource consumption. This is attributed to the fact that each new connection requires forking of the OS process and a new memory allocation for the connection. As a result, transactional applications frequently opening and closing the connections at the end of transactions can experience higher connection latency, resulting in lower database throughput (transactions per second) and overall higher application latency. Using a connection pooling mechanism when designing applications can significantly reduce connection latency by reusing existing connections and enables higher database throughput on the server. With connection pooling, a fixed set of connections are established at the startup time and maintained. This also helps reduce the memory fragmentation on the server that is caused by the dynamic new connections established on the database server. Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call. It is therefore recommended to leverage connection pooling, i.e., PgBouncer, which is a built-in feature in Azure PostgreSQL Flexible Server. Architecture The diagram illustrates how connection pooling with PgBouncer can reduce the number of direct connections to PostgreSQL. Without connection pooling, each application establishes connections directly to the database, which can increase resource usage as the number of clients grows. With PgBouncer acting as an intermediary, application connections are pooled and a smaller set of database connections can be reused, helping PostgreSQL handle connection-heavy workloads more efficiently. Additionally, consider setting idle/timeout parameters which can reduce the number of unnecessary connections established to your server and make a huge difference on your server resources availability: idle_in_transaction_session_timeout statement_timeout lock_timeout idle_session_timeout Scaling read-intensive workloads with Azure Read Replicas The Problem: Read-heavy workloads can place significant pressure on the primary PostgreSQL server, potentially affecting performance as application traffic and query volume increase. The Root Cause: When read and write operations are handled by the same primary server, resource-intensive or high-volume read queries compete with write operations for available database resources. The Solution: Use PostgreSQL read replicas to offload read traffic from the primary server and introduce a routing layer, such as Virtual endpoints or Pgpool-II, to direct read queries to replicas while keeping write operations on the primary. Read-heavy applications often reach a point where vertically scaling the primary is no longer the most efficient answer. Azure Database for PostgreSQL read replicas provide separate read-only servers, but the application still needs a routing strategy. Flow Destination Purpose INSERT / UPDATE / DELETE / DDL Primary writer Preserve write semantics SELECT Read replica Offload read-intensive work Important distinction: This is read scaling, not write scaling. Read replicas do not reduce the write-capacity burden on the primary, and replication is asynchronous, so applications must tolerate some lag. Virtual endpoints, which is a built-in feature in, can provide stable connection points for Azure Database for PostgreSQL, helping applications maintain consistent connection strings when server roles change after a promotion. Writer endpoint (read/write): Always points to the current primary server, ensuring that write operations are directed to the correct server after a role change. Read-only endpoint: Can point to either a read replica or the primary server. It targets one server at a time. This is a valid option when the application can route read and write operations through separate connections. However, the read-only endpoint targets a single server at a time and does not distribute read traffic across multiple replicas. Virtual endpoints simplify connection management by allowing applications to use consistent endpoints while the underlying server roles can change. Pgpool-II can sit between clients and the database endpoints, inspect eligible SQL statements, and distribute read traffic while keeping writes on the primary. Useful when the application cannot separate read and write traffic itself, or when read workloads need to be distributed across multiple replicas. However, this approach introduces an additional component that needs to be deployed, configured, and managed. Architecture Below diagram illustrates Pgpool-II as a single PostgreSQL endpoint between an application and Azure Database for PostgreSQL: Writes and consistency-sensitive reads go to the primary. Eligible SELECT queries are distributed across two read replicas. Pgpool-II handles SQL-aware routing, load balancing, and backend health checks. Overall, it demonstrates how read-heavy workloads can scale without requiring the application to select individual database servers. Install Pgpool-II on Ubuntu VM The following section describes the installation steps for Pgpool-II version 4.7.2 on an Ubuntu VM version 24.04.4. Package names and paths can vary by Ubuntu and Pgpool-II version, so test in a non-production environment first. We will install Pgpool-II and psql client tools and check the connection from the VM to involved Postgresql servers. Afterwards we will configure Pgpool-II by setting the main configuration file and the authentication files needed to connect to Postgresql servers. Prerequisites and assumptions: An Azure Database for PostgreSQL Flexible Server primary and at least one read replica. Network connectivity and DNS resolution from the client host to every database endpoint on TCP 5432. A PostgreSQL login for application access plus suitable accounts for Pgpool-II health and streaming-replication checks. TLS validation appropriate to the environment. A workload that can tolerate asynchronous replica lag for the reads that will be offloaded. All the examples below are performed from a bash shell: sudo apt install -y curl ca-certificates gnupg lsb-release # Optional: PostgreSQL client from the PGDG repository sudo install -d /usr/share/postgresql-common/pgdg sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \ | sudo tee /etc/apt/sources.list.d/pgdg.list sudo apt update sudo apt install -y postgresql-client-17 pgpool2 sudo systemctl enable --now pgpool2 Validate the database endpoints first Test and validate the connection from the VM client to Postgres servers to resolve endpoint, authentication, TLS, and firewall issues before introducing Pgpool-II. export PGUSER="<app_user>" export PGDATABASE="postgres" export PGPASSWORD="<password>" psql "host=<primary>.postgres.database.azure.com port=5432 sslmode=require" -c "select pg_is_in_recovery(), inet_server_addr();" psql "host=<replica>.postgres.database.azure.com port=5432 sslmode=require" -c "select pg_is_in_recovery(), inet_server_addr();" Expected result: pg_is_in_recovery() is false on the primary and true on a read replica. Configure Pgpool-II for read scaling Below is a minimal illustrative of main Pgpool-II configuration file. Review all defaults in your installed version before production use. Configuration file # /etc/pgpool2/pgpool.conf backend_clustering_mode = 'streaming_replication' # - pgpool Connection Settings - listen_addresses = '*' port = 9999 # - pgpool Communication Manager Connection Settings - pcp_listen_addresses = 'localhost' pcp_port = 9898 # - Backend Connection Settings - backend_hostname0 = '<primary>.postgres.database.azure.com' backend_port0 = 5432 backend_weight0 = 0 backend_application_name0 = 'primary' backend_hostname1 = '<replica-1>.postgres.database.azure.com' backend_port1 = 5432 backend_weight1 = 1 backend_application_name1 = 'replica1' # Add more replicas as backend_hostname2, backend_port2, and so on. load_balance_mode = on connection_cache = on sr_check_user = '<check_user>' health_check_user = '<check_user>' ssl = on Why primary weight 0? This example intentionally sends load-balanced reads only to replicas. If your consistency or capacity design should allow reads on the primary, assign it a non-zero relative weight instead. An example of pgpool.conf changed configuration: backend_clustering_mode = streaming_replication listen_addresses = '*' port = 9999 pcp_listen_addresses = ‘*’ pcp_port = 9898 backend_hostname0 = 'pgpool-prim.postgres.database.azure.com' backend_port0 = 5432 backend_weight0 = 0 backend_application_name0 = 'primary' backend_hostname1 = 'pgpool-repl1.postgres.database.azure.com' backend_port1 = 5432 backend_weight1 = 1 backend_application_name1 = 'repl1' backend_hostname2 = 'pgpool-repl2.postgres.database.azure.com' backend_port2 = 5432 backend_weight2 = 1 backend_application_name2 = 'repl2' pool_passwd = 'pool_passwd' ssl = on ssl_ca_cert_dir = '/etc/ssl/certs/' load_balance_mode = on read_only_function_list = 'inet_server_addr(),pg_is_in_recovery()' sr_check_user = 'edu' sr_check_password = '' health_check_user = 'edu' Authentication files Pgpool-II primarily uses two authentication configuration files to manage connections and privileges: pool_hba.conf and pool_passwd. Similar to PostgreSQL's native pg_hba.conf, this file handles client-to-Pgpool-II access control. It defines which hosts, IP ranges, databases, and users are allowed to connect, and specifies the required authentication method (such as trust, md5, scram-sha-256). Avoid broad 0.0.0.0/0 trust or password rules. Restrict source networks, prefer SCRAM where supported, and control file ownership and permissions. # /etc/pgpool2/pool_hba.conf host all all <trusted-client-cidr> scram-sha-256 When using password-based methods like md5 or scram-sha-256, Pgpool-II cannot look up user passwords directly from the PostgreSQL system catalogs in real-time. Instead, it references the pool_passwd file to verify client credentials before passing the connection to the backend server. This file is populated using commands like pg_md5 or pg_enc. # /etc/pgpool2/pool_passwd cd /etc/pgpool2 sudo pg_enc -m -k /etc/pgpool2/.pgpoolkey -f /etc/pgpool2/pgpool.conf -u <app_user> -p By using SCRAM authentication, we need to provide a protected encryption key used by pg_enc. This file is by default located in the users home directory but can be referenced by environment variable PGPOOLKEYFILE. # /etc/pgpool2/.pgpoolkey sudo sh -c 'umask 077; printf %s '<encryption-key>' > /etc/pgpool2/.pgpoolkey' sudo chmod 600 /etc/pgpool2/.pgpoolkey Note: Pgpool-II commonly runs as the postgres operating-system user and it will look for the pgpoolkey under its home directory. Though not used for standard database traffic, pcp.conf is a third critical authentication file used by Pgpool-II. It contains credentials that authorize administrators to execute remote management commands via the Pgpool Control Protocol (PCP) toolset. This file contains user ID and his password for pgpool communication manager authentication and store username:md5(password). # /etc/pgpool2/pcp.conf echo -n '<pcp_password>' | pg_md5 ## Add the generated hash, for example: <pcp_user>:<md5_hash> sudo chmod 600 /etc/pgpool2/pcp.conf ## Optional client password file: ~/.pcppass echo 'localhost:9898:<pcp_user>:<pcp_password>' > ~/.pcppass chmod 600 ~/.pcppass After changes the pgpool-II service needs to be restarted sudo systemctl restart pgpool2 sudo systemctl status pgpool2 --no-pager PCP administration quick reference In Pgpool-II, as mentioned above, PCP stands for Pgpool Control Protocol, which is an administrative interface and command set used to manage and monitor a running Pgpool-II instance. Pgpool-II management operations, such as checking node status or detaching nodes, are available through PCP commands, independently of normal PostgreSQL client connections. The examples below use PCP's default port, 9898, and a dedicated PCP account. Limit access to the local host or a controlled administration network. Common command pattern: pcp_<command> -h localhost -p 9898 -U <pcp_user> -w [command options] Choose -W when you want the utility to ask for the PCP password. Choose -w for unattended execution; in that case the credential must already be available through ~/.pcppass or the file named by PCPPASSFILE. Task Command Operational note List configured node count pcp_node_count -h localhost -p 9898 -U <pcp_user> -w Returns the number of backend entries known to the current Pgpool-II configuration; the total is not limited to nodes that are presently attached. Show one node pcp_node_info -h localhost -p 9898 -U <pcp_user> -w -n 1 -v Reports Pgpool-II's view of the selected backend, together with backend status and role details available for that node. Show all nodes pcp_node_info -h localhost -p 9898 -U <pcp_user> -w -a -v Useful before and after maintenance to compare all configured backends. Health-check statistics pcp_health_check_stats -h localhost -p 9898 -U <pcp_user> -w -n 1 Returns the accumulated health-check results and timing information for the requested backend ID. Show effective configuration pcp_pool_status -h localhost -p 9898 -U <pcp_user> -w Prints the configuration values currently reported by Pgpool-II. Detach a node gracefully pcp_detach_node -h localhost -p 9898 -U <pcp_user> -w -n 1 -g Requests a controlled detach and allows active client work to drain until Pgpool-II's configured limits are reached. Detach a node immediately pcp_detach_node -h localhost -p 9898 -U <pcp_user> -w -n 1 Removes the selected backend from Pgpool-II service immediately; established client sessions can be interrupted. Attach a node pcp_attach_node -h localhost -p 9898 -U <pcp_user> -w -n 1 Returns a configured backend to the Pgpool-II pool. Check connectivity, expected role and replication health before doing so. Show child processes pcp_proc_info -h localhost -p 9898 -U <pcp_user> -w -a -v Provides details about Pgpool-II worker processes and their connection slots. Reload configuration pcp_reload_config -h localhost -p 9898 -U <pcp_user> -w Asks the local Pgpool-II instance to reread its configuration; settings that require a restart remain unchanged until restart. Safe detach and attach workflow Check the current topology with pcp_node_info -a -v and confirm the target node ID. If possible, detach with -g so active clients can drain. Remember that detach changes Pgpool-II state only; it does not stop the PostgreSQL backend. Perform the planned maintenance and validate the backend directly, including DNS, TLS, authentication, pg_is_in_recovery(), and replication health. Use pcp_attach_node only when the backend is safe to rejoin with the expected role and acceptable replication state. Otherwise use the documented recovery procedure for your topology. Re-run pcp_node_info -a -v and SHOW POOL_NODES, then open fresh application sessions to validate routing. Important: pcp_attach_node does not repair or resynchronise a PostgreSQL replica. It only returns an existing backend to Pgpool-II service. Avoid pcp_promote_node for Azure Database for PostgreSQL read replicas because Azure role changes should be performed through Azure-supported promotion or failover operations, then reconciled with the Pgpool-II configuration and state. An example of pcp administration command: psql -p 9999 -h localhost -U edu -d postgres -c "show pool_nodes;" node_id hostname port status pg_status lb_weight role pg_role select_cnt load_balance_node replication_delay last_status_change 0 pgpool-prim.postgres.database.azure.com 5432 up unknown 0.000000 primary unknown 10 false 0 2026-09-15 16:48:00 1 pgpool-repl1.postgres.database.azure.com 5432 up unknown 0.500000 standby unknown 5 true 0 2026-09-15 16:48:00 2 pgpool-repl2.postgres.database.azure.com 5432 down unknown 0.500000 standby unknown 0 false 0 2026-09-15 16:53:58 pcp_attach_node -n 2 psql -p 9999 -h localhost -U edu -d postgres -c "show pool_nodes;" node_id hostname port status pg_status lb_weight role pg_role select_cnt load_balance_node replication_delay replication_state replication_sync_state last_status_change 0 pgpool-prim.postgres.database.azure.com 5432 up unknown 0.000000 primary unknown 0 false 0 2026-09-15 16:48:00 1 pgpool-repl1.postgres.database.azure.com 5432 up unknown 0.500000 standby unknown 0 true 0 2026-09-15 16:48:00 2 pgpool-repl2.postgres.database.azure.com 5432 up unknown 0.500000 standby unknown 0 false 0 2026-09-15 16:53:58 Check if load balancing works A single long-lived connection can remain assigned to one load-balancing node. Therefore, test both routing correctness and distribution across multiple new sessions. Check What to verify Backend status Primary and replicas appear up, with the expected roles. SELECT distribution select_cnt increases on intended read backends. Write routing A controlled INSERT/UPDATE reaches the primary. Read-after-write Application paths requiring fresh data remain on the primary or otherwise handle lag. Failure behaviour Replica loss, Pgpool-II restart, and recovery are tested without assuming automatic correctness. psql -h localhost -p 9999 -U <app_user> -d postgres -c "show pool_nodes;" for i in $(seq 1 20); do psql -h localhost -p 9999 -U <app_user> -d postgres -Atc "select inet_server_addr(), pg_is_in_recovery();" done psql -h localhost -p 9999 -U <app_user> -d postgres -c "show pool_nodes;" An example of expected output: node_id hostname port status pg_status lb_weight role pg_role select_cnt load_balance_node replication_delay replication_state replication_sync_state last_status_change 0 pgpool-prim.postgres.database.azure.com 5432 up unknown 0.000000 primary unknown 0 false 0 2026-09-28 09:35:07 1 pgpool-repl1.postgres.database.azure.com 5432 up unknown 0.500000 standby unknown 9 true 0 2026-09-28 09:35:07 2 pgpool-repl2.postgres.database.azure.com 5432 up unknown 0.500000 standby unknown 11 false 0 2026-09-28 09:35:07 Troubleshooting quick reference Symptom Likely area to inspect All reads go to primary load_balance_mode, weights, query eligibility, transaction state, and backend roles FATAL: unable to get session context Pgpool-II authentication files, pool_passwd, key permissions, user mapping, and service logs Replica shown down DNS, firewall, TLS, credentials, health checks, and PCP state Uneven read distribution Session reuse, backend weights, number of client sessions, and statement_level_load_balance Stale results Replica lag and application consistency requirements Performance worsened after adding middleware Separate query execution time from time waiting for a connection; compare representative tests through and around the proxy Consistency and routing caveats Read replicas are asynchronously updated. A replica can return older data than the primary. A SELECT inside a transaction might be routed differently after a write, at serializable isolation, or when it contains functions Pgpool-II cannot safely classify as read-only. Session-level load balancing does not guarantee an even per-query distribution when applications keep connections open for long periods. Health detection and query routing are not the same as a complete high-availability design for the Pgpool-II tier itself. A single Pgpool-II VM is a single point of failure. Customer-managed middleware remains the customer’s operational responsibility, including patching, monitoring, capacity, certificates, secrets, and recovery procedures. Production checklist Benchmark with realistic concurrency, transaction boundaries, prepared statements, and connection lifetimes. Define which application paths tolerate eventual consistency, and which must read from the primary. Monitor Pgpool-II backend state, SELECT counters, process utilization, logs, health checks, and replica lag. Run Pgpool-II redundantly if the endpoint is business-critical; test the load balancer or failover mechanism in front of it. Use TLS, least privilege, restricted network access, protected secrets, and documented certificate rotation. Test primary maintenance, replica unavailability, Pgpool-II restart, DNS changes, and application retry behaviour. Keep Pgpool-II and operating-system packages patched, and review release notes before upgrades. What problem does each technology solve? These technologies address different bottlenecks in different scenarios. Choose the component that matches the problem first and combine them only when the workload has clearly distinct requirements. Technology Primary problem solved Best fit Important limitation Virtual Endpoints Stable writer and read-only connection points when Azure PostgreSQL server roles change. Applications that can use separate read/write and read-only connection strings and want role changes without changing those strings. The read-only endpoint targets one server at a time; it does not inspect SQL or distribute reads across multiple replicas. Pgpool-II SQL-aware read/write routing, read distribution across replicas, connection reuse, and backend health visibility. A single PostgreSQL endpoint when the application cannot split traffic itself or reads must be distributed across multiple replicas. Customer-managed middleware adds deployment, patching, monitoring, security, capacity, and availability responsibilities. PgBouncer Lightweight connection pooling that reduces connection churn and backend-connection pressure. Many short-lived or idle client connections, especially when the built-in Flexible Server endpoint is suitable. It is not a SQL-aware read/write router across Azure read-replica endpoints, and transaction pooling requires compatibility testing. Azure Managed Redis Application-level caching that serves reusable data before another PostgreSQL query is required. Frequently repeated, cacheable reads with explicit freshness, expiry, and invalidation rules. Requires application integration and a deliberate consistency, eviction, security, and cost design. Decision guide If the main problem is… Start with… Why Connection strings must remain stable when primary and replica roles change. Virtual Endpoints The writer and read-only endpoints abstract the underlying server names after supported role changes. The application can already separate writes from reads and needs one read target. Virtual Endpoints They provide dedicated writer and read-only connection points without adding customer-managed SQL-routing middleware. One SQL endpoint must route writes to the primary and distribute eligible reads across multiple replicas. Pgpool-II It can inspect eligible statements and apply read distribution in a streaming-replication topology. Short-lived or excessive idle PostgreSQL connections are consuming resources. PgBouncer Connection pooling directly reduces connection churn and the number of server-side backend connections. Frequently requested objects can be served without querying PostgreSQL. Azure Managed Redis Cached values can satisfy repeated reads before they reach the primary or replicas. Conclusion PostgreSQL performance problems should be addressed at the layer where they originate. PgBouncer is the most direct option for reducing connection churn and controlling backend-connection pressure. Azure Database for PostgreSQL read replicas add read capacity, while Virtual Endpoints provide stable writer and read-only connection points for applications that can separate those traffic paths. When the application cannot perform that separation, or when eligible reads must be distributed across multiple replicas through a single PostgreSQL endpoint, Pgpool-II provides SQL-aware routing, load balancing, connection reuse, and backend health visibility. That flexibility comes with additional operational responsibility, so the Pgpool-II tier must be secured, monitored, patched, capacity-tested, and designed for availability. Azure Managed Redis complements these database-side approaches by serving reusable data before a PostgreSQL query is issued. It is valuable for cacheable reads, but it requires explicit application logic for freshness, expiry, invalidation, and failure handling. The strongest design is therefore not the one with the most components. It is the one that identifies the actual bottleneck and introduces the smallest appropriate capability: Virtual Endpoints for stable role-based connection points, Pgpool-II for SQL-aware routing across replicas, PgBouncer for connection efficiency, and Azure Managed Redis for application-level caching. Validate the chosen design with realistic concurrency, replica lag, failover, session behaviour, and application consistency requirements before production adoption. References Azure Database for PostgreSQL server parameters: https://learn.microsoft.com/azure/postgresql/parameters/concepts-parameters Azure Database for PostgreSQL connection pooling strategy: https://learn.microsoft.com/azure/postgresql/connectivity/concepts-connection-pooling-best-practices PgBouncer in Azure Database for PostgreSQL: https://learn.microsoft.com/azure/postgresql/connectivity/concepts-pgbouncer PgBouncer feature compatibility: https://www.pgbouncer.org/features.html Azure Database for PostgreSQL read replicas: https://learn.microsoft.com/azure/postgresql/read-replica/concepts-read-replicas https://learn.microsoft.com/azure/postgresql/read-replica/concepts-read-replicas-virtual-endpoints Pgpool-II documentation: https://www.pgpool.net/about https://www.pgpool.net/docs/4.7/html/intro-whatis.html https://www.pgpool.net/docs/4.7/html/auth-methods.html https://www.pgpool.net/docs/4.7/html/runtime-config-load-balancing.html https://www.pgpool.net/docs/4.7/html/pcp-commands.html Azure Managed Redis documentation: https://learn.microsoft.com/azure/redis Azure Cache for Redis retirement guidance: https://azure.microsoft.com/products/cache349Views3likes0Comments