End-to-end workload observability with Query Store for primary and replicas
Query performance doesn’t stop at the primary Most PostgreSQL architectures don’t run on a single node anymore. Reads get offloaded. Replica chains grow. And when performance issues hit, the hardest part is often simple: where did the queries actually run? With the latest query store capabilities in Azure Database for PostgreSQL flexible server, you can now capture workload executed not just on the primary, but also on read replicas—including cascading read replicas—and export the captured runtime stats, wait stats, and query text into Azure Monitor Logs (Log Analytics workspace / LAWS). See the real hotspot: isolate which node (primary vs replica) is slow. Know why: break down time by waits (CPU, I/O, locks) per query. Connect the dots: correlate query IDs to query text, and inspect sampled parameters locally in azure_sys on the primary when you need input context (parameters aren’t exported to LAWS). Centralize analysis: query everything with KQL in LAWS, across servers. What you’ll build This post walks through a reproducible demo that provisions a primary server, a read replica, and a cascading read replica, then runs a TPC-H–based workload across all three to generate query store data you can analyze locally and in Log Analytics. Enable query store capture (including query text) and parameter sampling for parameterized queries. Enable wait sampling so query store can record wait statistics. Export runtime stats, wait stats, and SQL text to LAWS using resource-specific tables. Validate capture on read replicas and cascading read replicas (not just the primary). Prerequisites Azure CLI logged in (az login) and permission to create a resource group, Log Analytics workspace, and PostgreSQL flexible servers. psql and curl available on your machine. PostgreSQL flexible server on General Purpose or Memory Optimized tier (query store and replicas aren’t supported on Burstable). PostgreSQL 14+ to test out cascading replicas. Networking: the script opens firewall access broadly for demos—tighten for production. Architecture (primary + replica chain + LAWS) You’ll deploy four resources: Primary server: read/write node. Read replica (level 1): read-only node created from the primary. Cascading read replica (level 2): read-only node created from replica level 1. Log Analytics workspace (LAWS): central place to query Query Store telemetry across all nodes. If Diagnostic Settings is properly configured, each server streams query store telemetry to LAWS—but how it’s kept locally differs by role. On the primary, query store data is recorded in-memory, then persisted locally in the azure_sys database, and then exported to LAWS. On read replicas (including cascading replicas), query store data is recorded in-memory only and then exported to LAWS. Bottom line: use LAWS for fleet-wide visibility, and use the primary’s azure_sys when you need deep local inspection (like parameter samples). Deploy the demo environment The fastest way to reproduce the scenario is to run the end-to-end bash script which you can download from https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-query-store/refs/heads/main/may2026/script/query_store_demo.sh Save the file to a local directory in your Linux shell, and name the file query_store_demo.sh. To invoke the script, at minimum, you must assign a string password for the administrator login of the instances of the flexible servers it creates, and invoke the script like this: ADMIN_PASSWORD=<Your_Strong_Password> ./query_store_demo.sh Optionally, you can also override default values for other environment variables used by the script: Variable Purpose Default SUBSCRIPTION_ID Azure subscription ID to use (current default subscription) BASE_NAME Base name for all resources (used in naming servers, resource groups, etc.) pgqswait{YYYYMMDDHHMMSS} RESOURCE_GROUP Azure resource group name rg-{BASE_NAME} LOCATION Azure region for resources southeastasia PRIMARY_SERVER Name of primary PostgreSQL server {BASE_NAME}-primary REPLICA_1 Name of first-level read replica {BASE_NAME}-readreplica REPLICA_2 Name of second-level cascading read replica {BASE_NAME}-cascadereadreplica LOG_ANALYTICS_WORKSPACE Log Analytics workspace name law-{BASE_NAME} LOG_ANALYTICS_LOCATION Azure region for Log Analytics workspace southeastasia ADMIN_USER PostgreSQL admin username pgadmin ADMIN_PASSWORD PostgreSQL admin password (REQUIRED) SKU_NAME PostgreSQL server SKU (compute tier) Standard_D4ds_v5 TIER PostgreSQL pricing tier GeneralPurpose STORAGE_SIZE Storage size in GB 64 VERSION PostgreSQL version (minimum 14 for cascading replicas) 17 PRIMARY_DATABASE Initial database name postgres SQL_BASE_URL Base URL for downloading SQL scripts https://raw.githubusercontent.com/Azure-Samples/azure-postgresql-query-store/refs/heads/main/may2026/script/query_store_demo.sh TPCH_DDL_URL URL for TPC-H schema DDL file {SQL_BASE_URL}/schema/tpch_ddl.sql WORKLOAD_REPETITIONS Number of times to execute each workload query (minimum 5) 10 AUTO_APPROVE Skip confirmation prompt and proceed automatically false If, for example, you want to not only pass the ADMIN_PASSWORD but also override the LOCATION, you could do it like this: ADMIN_PASSWORD=<Your_Strong_Password> LOCATION=canadacentral ./query_store_demo.sh In a bit over 1 hour, the script will do the following steps: Step 1 — Provision first part of the infrastructure The infrastructure provisioned in this phase consists of: A resource group in which all resources are deployed. An instance of Log Analytics workspace, where all flexible server instances will send their query store related logs. A primary (read-write) flexible server. Step 2 — Configure primary server Now it's time to configure one new server parameters on your primary server so that query store emits query text to LAWS, so that we can correlate quey IDs to something recognizable. Query IDs are great for aggregation—but you still need the SQL. Turn on query text emission so you can correlate runtime and waits back to the actual statement text. Do this by setting pg_qs.emit_query_text to on. Refer to our documentation to learn how to set the value of a server parameter. Step 3 — Provision second part of the infrastructure The infrastructure provisioned in this phase consists of: A read replica (read-only) whose source is the primary server. A cascade read replica (read-only), whose source is the previously created read replica. Notice that when read replicas are created, they inherit the server parameter values from their source server. Because we have configured query store related settings on the primary server already, the intermediate read replica inherits its server parameters from that primary, and the cascade read replica inherits them from the intermediate replica. Step 4 — Export query store to Log Analytics (LAWS) Now for the payoff, we want to stream the data to Log Analytics so you can query across nodes, build dashboards, and alert. The script configures diagnostic settings on the primary and both replicas to send logs to a Log Analytics workspace using resource-specific tables. This is the key to cross-node visibility: each server exports its own captured telemetry, and you can slice by resource in a single KQL query. Query store runtime stats: execution counts, elapsed time, and other performance counters. Query store wait stats: wait breakdown attributed to queries. Query store SQL text: query text to decode query IDs. Note: Query store parameter samples are not included in the Log Analytics export. Parameters are stored locally per server in azure_sys, and on read replicas azure_sys is read-only—so don’t depend on replicas for parameter inspection. LAWS receives runtime stats, wait stats, and query text. Diagnostics settings for an instance of flexible server can be configured via portal. In the resource menu, under Monitoring, select Diagnostic settings. Add a new diagnostic setting, select a destination Log Analytics workspace, and the individual log catergories which you want to stream to that LAWS, and save the changes. For Destination table it's highly recommended to use Resource specific (one table per signal with proper schema) over Azure diagnostics (legacy one table for everything). With Azure diagnostics, all logs from all resource types land into a single table (AzureDiagnostics). It's a wide table with many columns. New columns get added as services emit new fields. If the 500 column limit is hit, extra fields go into the AdditionalFields column (a dynamic JSON). Querying on attributes stored in that column might have huge performance and query cost impact. The schema is inconsistent and difficult to discover. You must always filter events in that table by ResourceType and Category. On the other hand, with Resource specific, logs are written to separate tables per resource type and category. Therefore, each table has a well-defined schema and columns are strongly typed. Tables are smaller and faster to query. Queries on these tables are simpler don't need filtering by ResourceType and Category. Performance-wise, they also support faster ingestion and faster querying. They also support selecting different table plans and retention settings for each table. And, more importantly, role-based access control (RBAC) permissions can be applied at table level, allowing you to control access to telemetry in a more granular way. This can also be configured using Azure CLI command az monitor diagnostic-settings create. Make sure that the --export-to-resource-specific parameter is set to true, which is the equivalent of selecting Resource specific for Destination table in portal UI. Setting this parameter to false, would mean that you want to use AzureDiagnostics as the destination table, which we don't recommend using. Step 5 — Run some workload In this phase the script loads a TPC-H schema and executes workload SQL across different nodes so that you can prove replica capture. Query it in Log Analytics Once the workload completed and data was streamed to Log Analytics, you can open your Log Analytics workspace, and start querying the relevant tables. If you don't know how to start issuing queries in a Log Analytics workspace, refer to Get started with log queries in Azure Monitor Logs. In your Log Analytics workspace, when you select Logs in the resource menu, you can access the Queries hub. By default, it should open automatically unless you have configured it to not show, in which case you can open by selecting Queries hub on the top right corner of the Logs home screen. If you add a filter in the queries hub for Resource type equals Azure Database for PostgreSQL Flexible Server, you'll be able to access multiple examples of queries which might help you get started querying the log categories we support for our service. You can run any of them by selecting Run on the summarization card that describes the query or, if you hover the mouse over the card, you can select Load to editor so that the query is copied over to the active query window, and you can run it or modified it further. Following, there are a few more query examples which can be useful to analyze the workload executed in this experiment. Top queries by total time (across all nodes) To get the list of 10 queries with higher duration from the ones that ran on any of the three nodes. KQL PGSQLQueryStoreRuntime | summarize total_time_ms = sum(TotalExecDurationMs) by QueryId, LogicalServerName | top 10 by total_time_ms desc Results Important: Results might be slightly different on each execution of the experiment. Where queries wait on each node List the most frequent wait events observed on user initiated queries across all nodes. KQL PGSQLQueryStoreWaits | join kind=inner (PGSQLQueryStoreRuntime) on QueryId | summarize total_waits_sampled = sum(Calls) by Event, EventType, LogicalServerName | order by total_waits_sampled desc Results Important: Results might be slightly different on each execution of the experiment. Decode query IDs (join runtime stats with SQL text) Top 20 queries the most frequent wait events observed on user initiated queries across all nodes. KQL PGSQLQueryStoreRuntime | join kind=inner (PGSQLQueryStoreQueryText) on QueryId | where QueryType == 'select' | project LogicalServerName, QueryId, TotalExecDurationMs, QueryText | top 20 by TotalExecDurationMs desc Results Important: Results might be slightly different on each execution of the experiment. Compare primary vs replicas (workload distribution) Find total number of query executions and accumulated duration of all those executions for each node. KQL PGSQLQueryStoreRuntime | summarize execs = sum(Calls), total_time_ms = sum(TotalExecDurationMs) by LogicalServerName | order by total_time_ms desc Results Important: Results might be slightly different on each execution of the experiment. Replica-only hotspots (find what’s slow off the primary) Find top 10 queries executed by their aggregated duration, focusing on what was executed on read replicas only. KQL let Replicas = dynamic(["pgqswait20260505220501-readreplica", "pgqswait20260505220501-cascadereadreplica"]); PGSQLQueryStoreRuntime | where LogicalServerName in (Replicas) | summarize total_time_ms = sum(TotalExecDurationMs) by QueryId, LogicalServerName | top 10 by total_time_ms desc Results Important: Results might be slightly different on each execution of the experiment. QPI now supports query store stats collected on replicas You can now use Query Performance Insight workbooks to analyze query store information not only on your primary server, as you were used to, but you can also get that valuable information on your read replicas. Why replica workload capture is a big deal This is the unlock: you can now answer performance questions in replica-heavy architectures without stitching together partial signals. Per-node truth: see the slow queries on the node where they actually ran (primary vs replica vs cascading replica). Faster root cause: runtime + waits gives you “slow” and “why” in one place. Replica tuning that sticks: identify replica-specific bottlenecks (I/O saturation, lock waits, CPU pressure) and tune with evidence. Centralized observability: export to LAWS so you can build dashboards, alerts, and cross-server comparisons with KQL. Unlock query visibility: Access query text without database permissions. Fine grain control on who can view query text: Using resource specific tables in LAWS, you can decide which users can access the table in which text of the queries is kept. Parameter-aware debugging: sampled parameters can help reproduce issues and explain plan changes, but they’re stored locally in azure_sys and not exported to LAWS. In practice, rely on the primary for parameter inspection (replicas have read-only azure_sys). Operational notes (quick but important) Expect a delay: Query store stats and LAWS ingestion aren’t instant. Give it a few minutes after running workload. Mind retention: Query store retention and Log Analytics retention are separate knobs. Tune them to balance troubleshooting value and cost. Production hygiene: don’t use wide-open firewall rules outside of a demo. Clean up When you’re done, delete the resource group: az group delete --name <RESOURCE_GROUP> --yes --no-wait Bottom line Query store in Azure Database for PostgreSQL flexible server now matches how modern architectures run—across primary, read replicas, and cascading replicas—and LAWS gives you a single place to query, compare, and act.Multitude builds resilient banking platform with PostgreSQL and MySQL on Azure
Expanding into new markets is usually a sign that things are going well. For digital banking platforms, however, growth brings a different kind of challenge - more customers, more data, and stricter expectations around availability, security, and regulatory compliance. At Multitude, we operate across 17 countries and deliver digital banking, credit services, payment processing, and regulatory reporting through a platform composed of more than 400 microservices. Each service encapsulates a defined business capability, including onboarding, risk assessment, collections, and compliance workflows. Historically, our services relied on on-premises PostgreSQL and MySQL environments deployed within our own data centers, where capacity scaled vertically on shared compute and storage resources. This model created contention between unrelated workloads and limited their ability to scale independently. Expanding capacity required adding or upgrading physical hardware, which involved demand forecasting, procurement, delivery coordination, and installation within the data center. Over time, continued growth amplified these architectural constraints. The database engines themselves remained reliable, but the surrounding infrastructure limited elasticity and domain-level isolation. As a result, sustained growth began to expose structural limits in the underlying infrastructure. "In a regulated financial environment, those constraints carried broader implications. Frameworks such as DORA and GDPR require predictable availability, controlled recovery procedures, and governed access to sensitive data. As workload demands increased, sustaining both growth and compliance required structural changes at the database layer. We decided that redesigning our data architecture was necessary to improve workload isolation, scalability, and governance alignment. Rearchitecting data boundaries with Azure Databases We initiated our architectural redesign by migrating database workloads to Microsoft Azure and standardizing on Azure Database for PostgreSQL and Azure Database for MySQL for core application services. Central to this redesign was the adoption of bounded contexts. Each bounded context represents a logical business domain and encapsulates the services and schemas required to support that capability. Each domain is owned and managed by a single team, aligning technical boundaries with team responsibility and accountability. Rather than maintaining a small number of large, shared database instances, we provisioned dedicated database instances aligned to defined business domains, establishing domain-level isolation at the database layer. Today, approximately 35 database instances support more than 400 microservices across the platform. Each instance may host multiple schemas serving related services within the same domain, while cross-domain database dependencies are intentionally avoided. This structure limits the blast radius of configuration changes or workload spikes and allows scaling adjustments to be applied within clearly defined domain boundaries. While the bounded context model was a strategic architectural decision, leveraging managed database services helped us implement it by drastically reducing the operational overhead of provisioning, scaling, and maintaining independent instances across domains. Azure Database for PostgreSQL and Azure Database for MySQL provide the managed capabilities required to sustain this model. Instances are provisioned according to the performance and storage requirements of each domain and can be adjusted as workload characteristics evolve. Compute and storage resources are scaled at the instance level, allowing capacity changes to be applied to a specific bounded context without affecting unrelated domains. Altogether, these architectural decisions balance domain-level isolation with operational manageability. A database-per-microservice pattern would significantly increase provisioning, monitoring, and lifecycle overhead without materially improving data ownership boundaries. By grouping related services within bounded contexts, we maintain clear domain alignment while keeping the number of database instances practical to operate. As a result, data boundaries, scaling behavior, and operational controls remain consistent with business domain structures across the platform. Operationalizing high availability and backup strategy To support availability, we deploy Azure Database for PostgreSQL and Azure Database for MySQL with zone-redundant high availability, placing primary and standby replicas in separate availability zones within the same Azure region. Replication preserves transactional consistency, and zone separation reduces exposure to localized infrastructure failures. We periodically exercise failover procedures as part of operational validation to confirm recovery behavior under defined conditions. Availability controls are complemented by a layered backup strategy. Azure Database for PostgreSQL and Azure Database for MySQL provide automated backups with a retention window of up to 35 days and point-in-time restore capabilities. These features allow us to restore a database to a specific timestamp within the retention window, supporting recovery from application-level errors or unintended data modifications without custom snapshot orchestration. Together, operational backups and governed archival retention address both short-term recovery and long-term compliance obligations. Restore operations require documented justification and follow established approval workflows, ensuring that recovery actions remain controlled, traceable, and auditable. We also enforce consistency through lifecycle management. Azure’s managed service model standardizes engine patching and version updates across environments, reducing configuration drift and minimizing manual coordination. By operating within the managed service boundary, the database team can focus on workload analysis, performance tuning, and capacity planning. For migration and synchronization scenarios, we use Azure Data Migration Service to orchestrate controlled cutovers between database environments. Engineers validate configuration and readiness before initiating synchronization, after which Azure-managed replication then maintains data alignment until final switchover. Provisioning decisions and structural modifications remain subject to internal governance approvals to preserve change control and oversight. By combining zone-redundant availability, structured recovery workflows, governed retention policies, and standardized lifecycle management, we operate a database layer engineered for resilience, auditability, and regulatory alignment at scale. Compliance as an architectural property For us, governance is embedded directly into how the platform operates, beginning at the identity layer. Access to Azure Database for PostgreSQL and Azure Database for MySQL integrates with Microsoft Entra ID, aligning database authentication with centrally managed corporate identities. Role-based access control is enforced through enterprise identity policies, providing centralized visibility into access assignments and authentication events across environments. These controls extend into production access management. Privileged access is approval-based and time-bound, and administrative roles are not permanently assigned. Access requests follow defined workflows, and all privileged actions are logged for review under established oversight procedures, ensuring traceability of operational interventions. Database isolation reinforces these identity controls. By aligning database instances with bounded contexts, each business domain maintains a discrete data boundary at the database layer. This structure limits lateral access across domains and confines sensitive data to clearly defined ownership scopes, simplifying monitoring and audit review. In a regulated financial environment, these architectural controls also support compliance requirements under frameworks such as DORA and GDPR. By embedding identity integration, domain isolation, and lifecycle controls directly into the platform architecture, governance becomes an operational property of the system rather than a separate procedural layer. The simplicity of this architecture is a strong driver for both auditability and security of the whole platform. Measurable impact across engineering teams and business outcomes Beyond improved stability, our ability to respond to growth has changed significantly since moving to Azure. In the past, expanding database capacity meant procuring hardware and planning installation in the data center. Now, capacity adjustments happen directly within Azure and can be applied to individual databases instances, allowing us to scale in near real time as workload demands change. Maintenance effort has also decreased. Managed patching, version alignment, and automated backups have reduced the need for manual coordination and reactive capacity management. Infrastructure-level tasks that once required continuous oversight are now handled within the managed service boundary. Our DBAs are now focused on improving performance and stability. We spend far less time maintaining the basics. Resilience by design The structural changes behind these results reflect a deliberate long-term strategy. Our database architecture now aligns with the operating model we expect to sustain over the next five years and beyond. Bounded contexts define discrete data domains, while Azure Database for PostgreSQL and Azure Database for MySQL provide managed high availability, scaling controls, and standardized lifecycle management across those domains. Identity integration and governed recovery procedures operate consistently across environments. With this architecture in place, Multitude scales responsibly in regulated markets while maintaining strict governance and availability standards. Expanding into new markets still means more customers and more data - but now our platform is designed to handle that success.
PostgreSQL as your Graph Database in the AI era
Enterprise data is full of hidden relationships: citation chains in legal docs, service dependencies, approval hierarchies, and drug interactions. These connections often hold the most valuable insight, but SQL-only and vector search can miss them. With Apache AGE now generally available, AI Functions in ai_extension in public preview, and graph visualization shipped in the PostgreSQL VS Code extension, Azure Database for PostgreSQL Flexible Server gives you everything you need to unlock this structural knowledge without adding a separate graph database to your stack. Why Graphs? Because Relationships Are the Blind Spot Vector search is great at finding similar content, but it’s weaker when the answer depends on relationships. For example, “does this new vendor contract conflict with existing obligations?” may return a relevant clause, but it won’t follow links across regions, obligation types, and counterparties to prove a real conflict. Graph queries fill that gap by following connections across your data to surface paths and patterns that flat retrieval can’t see. Combine graph traversal with AI-based extraction and you get knowledge graph powered retrieval: LLM answers grounded in both the text and the structure of your data. Enable Graph Queries Inside PostgreSQL Apache AGE adds graph capabilities to PostgreSQL. You can write graph queries in openCypher and run them right alongside your SQL. That means no separate graph database to provision, no cross-database data movement, and no external synchronization pipeline is required. If you materialize a graph from relational tables, that graph representation still needs to be maintained as the source data changes, but it can be managed inside PostgreSQL with the same enterprise security, transactions, and operational model. AGE is now generally available on Azure Database for PostgreSQL Flexible Server on PostgreSQL 16, PostgreSQL 17 and with support now extending to PostgreSQL 18. Key capabilities include: openCypher query language for expressive graph traversals ACID transactions - graph operations share PostgreSQL's full transactional guarantees BTREE and GIN indexes on graph properties for efficient querying at scale Mixed SQL + Cypher - JOIN graph results with relational tables in a single statement Here's what it looks like in practice: -- Load AGE extension LOAD 'age'; SET search_path = ag_catalog, "$user", public; -- Create a knowledge graph SELECT ag_catalog.create_graph('company_graph'); -- Query: Who should I talk to about the payment service? SELECT * FROM ag_catalog.cypher( 'company_graph', $$ MATCH (s:Service {name: 'PaymentService'}) <-[:OWNS]-(t:Team) <-[:MEMBER_OF]-(p:Person) WHERE p.active = true RETURN p.name, t.name, p.role $$ ) AS (person agtype, team agtype, role agtype); That single query traverses from a service to its owning team to its active members, a three-hop relationship that would require nested subqueries or recursive CTEs in pure SQL. 📘 Apache AGE Extension — Documentation | GA Announcement Blog Build Knowledge Graphs from Your Data The hardest part of working with graphs is building the graph in the first place. AI Functions in ai_extension (now in Public Preview) solve this by bringing LLM-powered intelligence directly into SQL. extract() is the standout for graph workflows. It discovers hidden relationships and entities from unstructured text, right inside a SQL query. Feed it contracts, support tickets, research papers, or any text-heavy data, and it pulls out the structured relationships you need to populate your knowledge graph. The other operators complement the graph pipeline: rank() re-ranks retrieval results with state-of-the-art models from Microsoft Foundry, is_true() evaluates natural-language filter conditions, and generate() produces LLM responses which are all callable from SQL. Together, they enable a powerful pattern: extract relationships, store them as a graph in AGE, query the graph, then rank and return results all within PostgreSQL. -- Extract relationships from a support ticket SELECT azure_ai.extract( description, ARRAY['relationships: object[] - {source, edge, target} triples'] ) FROM support_tickets; -- Returns: -- { -- "relationships": [ -- {"source": "API gateway", "edge": "CAUSED_FAILURE_IN", "target": "auth service"}, -- ... -- ] -- } 📘 AI Functions — Documentation | Deep Dive Blog Visualize Your Graph as You Build It The PostgreSQL extension for VS Code now renders graph visualizations automatically from Cypher queries. Run a query, see the nodes and edges. This makes exploring, debugging, and presenting your graph data dramatically easier requiring no separate visualization tool. 📘 PostgreSQL Extension for VS Code Build Graph-Augmented AI Applications and Agents with PostgreSQL Graph doesn’t work alone. Azure Database for PostgreSQL also includes pgvector + DiskANN for fast vector search, the Azure AI Extension to call LLMs from SQL, and MCP Server support to connect your database to AI agents. Together, they let you build end-to-end Graph-augmented RAG pipelines in one database right from extraction and embeddings to graph storage, retrieval, ranking, and generation. Use the PostgreSQL MCP Server to connect AI agents to your database, giving them the ability to traverse graphs, run vector searches, and answer questions grounded in your enterprise data. 📘 DiskANN How-To | Azure AI Extension | MCP Server Blog | Build AI Agents with PostgreSQL Why Build Your Graph on PostgreSQL? You already have the data. AGE adds graph features next to your existing tables. There’s no migration or separate graph store needed. You can use SQL and Cypher together in one query and transaction. One database instead of many. Keep relational data, graphs, json data, vectors, and LLM calls in one managed service. Simplify backups, security, and billing. Enterprise-grade from day one. Zone-redundant HA, automated geo-backups, Microsoft Entra ID auth, encryption at rest and in transit, all inherited automatically. Use Cases: Where Graph + AI Unlock What Tables and Vectors Alone Can’t Legal research — Extract citations and holdings, traverse precedent paths, and ground answers with the full citation chain (not just similar text) Fraud detection & AML — Build entity graphs from transactions and customer data, then use multi-hop traversals and AI summarization to explain suspicious rings, layering, and circular flows Enterprise knowledge & expertise routing — Construct a living knowledge graph from docs/tickets/repos; answer “who owns this?” and “who knows this?” with graph reasoning and ranked, source-backed responses IT incident triage & root cause analysis — Correlate incidents, services dependencies, deployments/config changes; traverse blast radius and generate an RCA narrative with supporting evidence Supply chain risk & resiliency — Extract supplier relationships from contracts and POs, model tier-N dependencies, and let copilots flag sole-source bottlenecks and propose mitigations Regulatory compliance automation — Link regulations, obligations, controls and systems/vendors; run impact analysis on new rules and auto-generate control mapping and gap assessment Life sciences knowledge discovery — Connect drugs, targets, pathways, indications, and adverse events; traverse causal chains and generate explainable interaction/contraindication summaries Customer support & case deflection — Turn tickets, product telemetry, and KB articles into a graph; retrieve via graph paths and generate step-by-step resolutions Get Started: Try It Now We've built solution accelerators so you can see graph in action on Azure Database for PostgreSQL today: Resource Link Contract Intelligence Platform https://github.com/james-tn/graph/tree/main/contract_intelligence Agentic Shop aka.ms/agentic-shop GraphRAG Legal Research Copilot github.com/Azure-Samples/graphrag-legalcases-postgres Build Your Own Advanced AI Copilot github.com/Azure-Samples/postgres-sa-byoac GraphRAG + Docker + AI Agents github.com/Azure-Samples/postgreSQL-graphRAG-docker Microsoft Learn — Implement GraphRAG Lab aka.ms/mslearn-graphrag AGE MCP Server (Claude Desktop / VS Code) github.com/rioriost/age_mcp_server Enabling Apache AGE takes minutes : navigate to Server Parameters in the Azure Portal, enable AGE under azure.extensions and shared_preload_libraries, save, and run CREATE EXTENSION age CASCADE;. That's it, your PostgreSQL database is now a graph database. Don't Miss the Edges The most valuable knowledge in your data isn't sitting in rows, it's hiding in the connections between them. These relationships are the structural context that makes AI applications genuinely intelligent. Enable graph on your Azure Database for PostgreSQL today, and start surfacing the relationships your data has been hiding all along.PgBouncer Best Practices in Azure Database for PostgreSQL – Part 1
Introduction Connection pooling is critical for scaling PostgreSQL workloads efficiently, especially in managed environments like Azure Database for PostgreSQL. PgBouncer, a lightweight connection pooler, helps manage thousands of client connections without overwhelming the database. Connection pooling is very important when managing multiple concurrent database requests, as PostgreSQL uses a process-per-connection model, which means too many active connections can: Increase context switching overhead Consume excessive CPU/memory Degrade performance under load PgBouncer addresses this by limiting active server connections and queuing the additional client requests. However, misconfiguring key settings such as default_pool_size can still lead to CPU/memory pressure, connection bottlenecks, and degraded performance. Careful planning and tuning are essential to avoid these pitfalls. Understanding connection pools Before diving into tuning, it’s important to understand how PgBouncer organizes connections: PgBouncer creates a separate pool for each unique (database, user) combination. For example: If you have 2 application roles/users connecting to 2 databases. In this scenario, PgBouncer will allocate 4 pools. Each pool maintains its own number of connections, determined by default_pool_size. So, the total number of potential server connections is: number_of_pools × default_pool_size This is why sizing default_pool_size correctly is critical. Azure PgBouncer defaults Azure Database for PostgreSQL comes with preconfigured PgBouncer settings optimized for most workloads. Understanding these defaults is essential before making any tuning changes: pool_mode: TRANSACTION (default in Azure; best for most workloads) default_pool_size: 50 (range: 1–4950) max_client_conn: 5000 (range: 1–50000) Transaction mode support for prepared statements PgBouncer now enables support for PostgreSQL PREPARED STATEMENTS when combined together with TRANSACTION mode pooling. Previously, in transaction mode cached plans were difficult to manage, as there was no way for PgBouncer to confirm whether a new connection allocated from the pool would benefit from any cached plans generated from prior PREPARED STATEMENT operations. To work around this scenario, PgBouncer now provides a parameter which controls how many globally cached plan statements remain in memory for any pooled connection to leverage. max_prepared_statements: 200 (range: 0-5000) PostgreSQL connection limits For large tiers (e.g., 96 vCores), the default max_connections is 5000, with 15 reserved for system use. That means 4985 user connections are available. For more details, see maximum connection. Sizing best practices Proper sizing ensures optimal performance and resource utilization. Here’s how to approach it: 1. Use transaction pooling Start by confirming that pool_mode = TRANSACTION is enabled. This is already the Azure default and provides the best pooling efficiency for most web applications. If your application is using prepared statements, ensure you configure max_prepared_statements accordingly. 2. Determine your maximum active concurrent database operations (max_concurrent_ops) Next, you need to estimate how many total concurrent active PostgreSQL backends your instance can maintain: For CPU-bound OLTP workloads: keep max_concurrent_ops near 1.5x -2x the number of CPU vCores. For I/O-heavy workloads: stay slightly higher than vCore count. Rule of thumb for 96 vCores: max_concurrent_ops ≈ 144–192. 3. Divide across pools Once you’ve estimated your max_concurrent_ops value, the next step is to distribute your capacity across all connection pools. default_pool_size ≈ max_concurrent_ops / number_of_pools Example: max_concurrent_ops = 144 number_of_pools = 4 default_pool_size = 144 / 4 = 36 Sample configuration To illustrate how these calculations translate into real-world settings, here’s a sample PgBouncer configuration tuned for a scenario with four pools and an Active_Backend_Target of 144. pool_mode = transaction default_pool_size = 36 ; tuned for 4 pools max_client_conn = 5000 Quick reference table For quick planning, the following table provides starting recommendations based on common Azure Database for PostgreSQL SKU sizes. Use these as a baseline and adjust according to your workload metrics. SKU Size Memory Default max_connections Pools Suggested max_concurrent_ops Starting default_pool_size 8 vCores 32 GiB 3437 2 12–16 6–12 16 vCores 64 GiB 5000 2 24–32 12–20 32 vCores 128 GiB 5000 2 48–64 30–40 48 vCores 192 GiB 5000 2 72–92 40–60 64 vCores 256 GiB 5000 2 96–128 50–70 96 vCores 384–672 GiB 5000 2 144–192 60–80 For all tiers ≥16 vCores, max_connections is capped at 5000 (with 15 reserved for system use). Notes: default_pool_size = max_concurrent_ops / number_of_pools These values are starting recommendations. You should validate them against actual workload metrics and adjust gradually. Always ensure: (number_of_pools × default_pool_size) < max_connections − 15 (reserved system slots) Monitoring and tuning After applying your configuration, continuous monitoring is key. Here’s how: Use PgBouncer metrics in Azure Monitor to track active, idle, and waiting connections. Run SHOW POOLS; for real-time stats; watch cl_waiting vs sv_idle. For detailed monitoring and management, visit the Admin Console. Recommended Alerts: Alert if waiting client connections > 0 while idle server connections = 0 (indicates pool exhaustion—consider increasing default_pool_size). Alert if active server connections approach the configured default_pool_size (may indicate need for tuning). Alert if max_client_conn utilization exceeds 80% (risk of client-side connection errors). Tip: If waiting client connections grow while idle server connections are zero, increase default_pool_size cautiously. Review performance regularly and adjust gradually. Common pitfalls Avoid these mistakes when configuring PgBouncer: Changing pool mode to SESSION by default: transaction pooling is better for most apps. Session mode will not release connections until the session is ended. Ignoring pool count: multiplying a large default_pool_size by many pools can exhaust connections. Confusing max_client_conn with Postgres capacity: PgBouncer can accept many more clients than the server concurrent processes can support, any client connections not being processed will be waiting for resources. Tuning without data: always review metrics before changes. Conclusion Choosing the right default_pool_size in Azure Database for PostgreSQL with PgBouncer is about balancing performance and resource efficiency. With built-in PgBouncer in Flexible Server, you can enable connection pooling with a single parameter making it easy to get started quickly. The default settings are optimized for most workloads, and as your requirements grow, you can further tune parameters like default_pool_size and max_connections to suit your needs. By understanding your workload, estimating an active concurrent operations, dividing it across pools while respecting PostgreSQL limits, and continuously monitoring and adjusting based on real data, you can achieve a stable, scalable, and cost-effective environment. Further reading For more in-depth guidance and real-world scenarios on PgBouncer configuration and tuning in Azure Database for PostgreSQL, explore the following resources: Leverage built-in PgBouncer in Flexible Server Monitoring PgBouncer in Azure PostgreSQL Flexible Server Identify and solve connection performance in Azure Postgres Not all Postgres connection pooling is equal Connection handling best practice with PostgreSQLGeneral Availability Refresh: Mirroring Azure Database for PostgreSQL in Microsoft Fabric
Introduction We are excited to announce the General Availability Refresh of Mirroring Azure Database for PostgreSQL in Microsoft Fabric. This update introduces several new capabilities designed to reduce friction, improve transparency, and increase trust in PostgreSQL data for analytics and AI workloads. Database professionals, DBAs, and developers can now leverage a more robust, flexible, and transparent solution for integrating PostgreSQL data in Microsoft Fabric. Mirroring Azure Database for PostgreSQL enables seamless integration of transactional data into Microsoft Fabric, supporting advanced analytics and AI scenarios. Previously, users faced limitations in data type support, operational requirements, and troubleshooting transparency. The General Availability Refresh directly addresses these challenges, delivering a more reliable and user-friendly experience. Native Data Type Support One of the most significant enhancements is support for PostgreSQL native data types, including JSON and JSONB. Users can now mirror complex, semi-structured data without conversion, preserving the full fidelity of source data. Additionally, transparent replication to varchar(max) or varbinary is now supported, ensuring that even custom or less common types can be accurately mirrored in Fabric. Previously, data type limitations often required workarounds or manual transformations, introducing complexity and risk. With this update, data flows more naturally from PostgreSQL to Fabric, streamlining analytics and AI pipelines and reducing the time spent on data preparation. Mirroring now preserves PostgreSQL-native types—including JSON and JSONB—without requiring schema transformations or type coercion. This ensures: Full fidelity replication of semi-structured data Elimination of intermediate serialization (e.g., string encoding) Tables containing JSON/JSONB columns are mirrored into Fabric with their structure intact, enabling: Direct querying of nested JSON fields Consistent schema representation across source and analytical layers Once mirrored, JSON content can be queried natively within Fabric workloads, allowing: Hybrid analytical scenarios (relational + semi-structured) Use of familiar SQL patterns for JSON traversal and extraction Flexible Server High Availability Support for PG versions earlier than 17 The refresh expands support for Azure Database for PostgreSQL Flexible Server with high availability enabled on versions earlier than 17. This means organizations can leverage mirroring with HA configurations without needing to upgrade their database engine, improving operational flexibility and minimizing disruption. In the past, mirroring required specific PostgreSQL versions, limiting adoption for organizations with established HA deployments. Now, the broader compatibility allows teams to maintain their preferred configurations and benefit from seamless data integration in Fabric. Improved Transparency: Enhanced Error Messaging and Dedicated PostgreSQL UDFs Transparency is critical for troubleshooting and maintaining trust in data pipelines. The General Availability Refresh introduces enhanced error messaging, providing clear and actionable insights into replication issues. Dedicated PostgreSQL user-defined functions (UDFs) further support monitoring and diagnostics, enabling users to quickly identify and resolve problems. --Validates that all system and configuration requirements are met before starting CDC mirroring. SELECT * FROM azure_cdc.check_prerequisites(); -- Quickly verify which extension version is deployed — critical for troubleshooting SELECT azure_cdc.azure_cdc_version(); -- Returns a detailed list of errors and issues detected during CDC operations SELECT * FROM azure_cdc.get_health_status('', ''); -- Scans every eligible user table in the database and returns the mirroring readiness status for each SELECT * FROM azure_cdc.get_all_tables_mirror_status(); These features streamline troubleshooting, reduce downtime, and empower teams to maintain robust mirroring operations. The improvements are a substantial step forward from previous releases, which offered limited visibility into replication health and errors. Unblocking Mirroring for servers with Read Replica enables We also removed existing block for creating Mirrored databases on Flexible Servers with Read Replicas created. Now primary servers with one or more Read Replicas can serve as source for Mirroring their databases in Fabric with no limitations. Conclusion The new capabilities significantly reduce friction for analytics and AI workloads in Microsoft Fabric. By removing technical barriers and increasing operational simplicity, the General Availability Refresh empowers organizations to unlock the full potential of their PostgreSQL data for advanced analytics and AI applications. The General Availability Refresh of Mirroring Azure Database for PostgreSQL in Microsoft Fabric delivers critical enhancements: native data type support, expanded Flexible Server HA compatibility, replication identity improvements, and better transparency. Together, these features make mirroring more robust, flexible, and trustworthy for analytics and AI workloads. We encourage technical professionals, DBAs, and developers to adopt these new capabilities and explore how they can transform data integration in Microsoft Fabric. For more information, visit the official documentation and resources: Azure Database for PostgreSQL Documentation Microsoft Fabric DocumentationConnection Scaling in Elastic Clusters
As your applications grow, you need to manage database connections carefully to keep performance predictable and reliable. Azure Database for PostgreSQL Elastic Clusters, powered by Citus, support horizontal scaling by distributing data and queries across multiple nodes. This raises a key question: how do connections behave as you add more clients, grow the cluster, or upgrade node specifications? In this post, you’ll see how connection handling behaves in Elastic Clusters through controlled benchmarks across a small set of configurations. Core count: 2 and 4 Cluster node count: 4 and 8 Using these setups, we measured how throughput, latency, and resource usage change as connection counts increase for different workloads. The goal is not just to show numbers, but to explain why those numbers behave the way they do. We deliberately chose small SKUs (2 cores/8 GB and 4 cores/32 GB) because it is easier to observe how a smaller compute responds as connection counts grow. These results help you understand: What influences connection capacity When scaling up helps more than scaling out How to tune your cluster to match real workloads Future posts will expand this analysis to more configurations. For now, these insights can help you make informed decisions about cluster sizing and connection management. What you'll learn: How single-shard and multi-shard queries behave under increasing connection loads The impact of scaling up (more cores per node) vs. scaling out (more nodes) When PgBouncer helps—and when it hurts Practical limits imposed by memory, CPU, and connection parameters Configuration guidelines for different workload patterns How Elastic Clusters Handle Client and Internal Connections Before we dive into the performance results, let’s first look at how Elastic Clusters handle connections and execute queries. Cluster Architecture: Where Connections Go In Elastic Clusters, data is distributed across multiple nodes using Citus. Each node can accept client connections and execute queries. This is true even when the data lives on another node. This design allows you to scale horizontally while staying compatible with PostgreSQL. When your application opens a connection, the flow looks like this: The client connects through a load balancer on port 7432 The load balancer routes the connection to any available node That node executes the query and talks to other nodes if needed From the client’s point of view, this looks like a single server. Behind the scenes, however, the node can open additional internal connections to efficiently fetch distributed data. Query Types Determine Connection Use How many connections a query consumes depends on what kind of query you run. Single-Shard Queries Single-shard queries target data that lives on exactly one node. A typical example is a lookup by the distribution key. If you connect to the node that owns the data, the query runs locally If you connect to a different node, Citus uses an internal connection to fetch the data Multi-Shard (Fan-Out) Queries Multi-shard queries need data from multiple nodes. Aggregations and analytical queries often fall into this category. For these queries: The connected node has internal connections to many nodes Each node processes its local shards in parallel Results are sent back and combined As a result, one client connection can fan out into many internal connections. In both cases, when Citus needs to fetch data from another node, it first attempts to reuse a cached internal connection. If none is available, it creates a new connection and caches it. Important note: Explicit transactions change the behavior of single-shard queries. When a query runs inside a BEGIN … END block, Citus sends extra commands (BEGIN TRANSACTION + assign ID, COMMIT) to the worker node alongside the actual query. These extra network roundtrips add some coordination work compared to auto‑commit mode. Key Configuration Parameters Elastic Clusters expose configuration parameters that control how connections are created, cached, and limited. To understand connection scaling—or to tune it effectively—you need to know what these parameters do and when they matter. This section focuses on a subset; see the Citus blog posts and documentation for more details. Parameter Scope Description Typical use case Default max_connections Cluster wide Total connections allowed Set based on resources Varies by SKU citus.max_client_connections Per-node Connections allowed from clients Limit client load per node Varies by SKU citus.max_cached_conns_per_worker Per client connection Cached internal connections Control parallelism in fan-out queries 1 citus.max_adaptive_executor_pool_size Per client connection Max connections for parallel multi-shard execution Control parallelism in fan-out queries Varies by SKU, 1 or 16 In PostgreSQL, Memory Plays a Key Role in Connection Scaling: Idle connections typically use 2–5 MB Active connections often use 10–20 MB, depending on work_mem and query behavior Some Parameters Only Affect Certain Query Types: Parameters like citus.max_adaptive_executor_pool_size only affect multi-shard queries. Internal Connection Caching Is Critical: Internal connection caching has a large impact on performance. Setting citus.max_cached_conns_per_worker to 0 forces Citus to open new connections repeatedly This leads to additional connection setup work and reduces overall throughput Values greater than 1 only help multi-shard workloads by allowing more parallelism In practice, disabling internal caching is usually a bad idea. SKU Choice Changes Which Limits You Hit First Smaller nodes tend to reach memory thresholds sooner Larger nodes may hit connection limits before CPU or memory saturation How We Benchmarked To understand how connections scale in Elastic Clusters, we ran a set of controlled benchmarks using standard PostgreSQL tooling. Test Environment Tool: pgbench (PostgreSQL's standard benchmarking utility), running on a VM in the same region and zone as the cluster Dataset: Distributed pgbench_accounts table with a scale factor of 3000 (~38 GB) and distribution column aid (account ID). Setup commands: # Create table structure pgbench -i -I "dt" # Distribute the accounts table psql -c "SELECT create_distributed_table('pgbench_accounts', 'aid');" # Populate with data pgbench -i -I "gvp" -s 3000 # Add index for multi-shard queries CREATE INDEX index_bid ON pgbench_accounts(bid); Workloads Single-Shard Query: The single-shard workload targets one shard using the distribution key. \set aid random(1, 100000 * scale) BEGIN; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; END; Multi-Shard Query: The multi-shard workload aggregates data across multiple shards. \set bid random(1, scale) BEGIN; SELECT sum(abalance) FROM pgbench_accounts WHERE bid = :bid; END; How the Benchmarks Run Each benchmark: Ran for 600 seconds Used 16 pgbench worker threads Varied the number of client connections (-c) per run For each cluster configuration, we gradually increased the client count. We stopped when we observed connection or out-of-memory errors. Test Matrix We evaluated combinations of core counts, node counts, and query types: Workload Configuration tested Single-shard 2-core/4-node, 2-core/8-node, 4-core/4-node, 4-core/8-node Multi-shard 2-core/4-node, 2-core/8-node, 4-core/4-node, 4-core/8-node Single-shard with PgBouncer 2-core/4-node, 2-core/8-node Single-Shard Query Performance Single‑shard queries are a good model for OLTP workloads. They represent indexed lookups that target a single row (or a small set of rows). Massive Throughput Gains with Scaling Peak throughput climbed from ~11.4k TPS (2c4n) to ~48.3k TPS (4c8n), a 4.3× increase with quadruple cores and double the nodes. Higher Concurrency = Saturation Point Each configuration has an optimal operating point for throughput. Near-Linear Scaling Doubling cores roughly doubled throughput (≈2.4–2.5× gains), and doubling nodes added ~70–75% more TPS. Key Takeaways from Single-shard Experiments 1. Near-Linear Scaling with Resources Doubling CPU cores approximately doubled throughput, and doubling node count added 70-75% more throughput: Configuration Peak TPS Peak Clients CPU at Peak 2-core, 4-node 11,400 64 ~95% 2-core, 8-node 19,400 192 ~93% 4-core, 4-node 27,500 128 ~97% 4-core, 8-node 48,300 224 ~90% What this means: Adding compute resources effectively increases capacity. The slight sub-linearity when adding nodes (1.7× instead of 2×) is because the extra round-trips from explicit transactions only affect remote shard queries and the fraction for remote queries grows with the cluster size. In a 4-node cluster, 75% of queries hit a remote shard; in a 8-node cluster, 87.5% do. Since each remote query inside a BEGIN … END block pays the extra round-trip cost, the aggregate overhead increases as more nodes are added. 2.Saturation Points Vary by Configuration Each cluster configuration reaches peak throughput at a specific client count. Beyond this point, additional clients lead to: Throughput plateau Latency rises as the system operates near maximum capacity Increased contention and context switching Smaller configurations reach peak throughput sooner (64 clients for 2c4n), while larger ones handle several hundred concurrent clients before reaching peak. 3. Memory Constrains Maximum Connections, Not Node Count A key insight is that adding nodes does not necessarily increase total client capacity when the workload is constrained by memory limits. 2-core clusters: Maximum ~500 concurrent clients (both 4-node and 8-node) 4-core clusters: Maximum ~1000 concurrent clients (both 4-node and 8-node) Why? Each node maintains roughly client_count total connections: client_count / node_count external client connections (via load balancer) Remaining connections are cached internal Citus connections Once memory capacity is fully utilized, adding nodes alone does not always raise client capacity. External connections drop per node, but internal connections replace them, so each node still carries roughly the same load—and still can run out of memory. 4. CPU Utilization Reaches 90-97% at Peak All configurations fully utilized available CPU at peak throughput, confirming CPU as the primary throughput bottleneck (with memory limiting connection capacity separately). 5. Latency Characteristics Latency remains low (<5-10ms) until the system approaches saturation. Larger clusters maintain better latency under load: 4c8n cluster: Sub-5ms latency up to ~200 clients 2c4n cluster: Latency exceeds 10-20ms once saturated (~64 clients) Practical implication: Right-sizing your cluster provides headroom for traffic spikes without latency degradation. Single-Shard Query Performance with PgBouncer On Elastic Clusters, you can enable PgBouncer using server parameters. After enabling it, you connect to PgBouncer instances through the load balancer on port 8432. This connection pooling layer allows the cluster to handle far more concurrent client connections PgBouncer instances: One per node, behind load balancer Pool size: 50 connections per node (default) Pooling mode: Transaction pooling Eliminated Connection Limits Handled thousands of concurrent clients without out-of-memory errors Slightly Lower Peak TPS ~10% reduction in maximum throughput due to pooling overhead Linear Latency Growth Predictable queuing behavior once pool saturates Key Takeaways from Single-shard Experiments with PgBouncer 1. Graceful handling of high connection counts Without PgBouncer, 2‑core clusters reached memory capacity around 500 clients. With PgBouncer enabled, tests successfully ran with 1000+ concurrent clients. Throughput plateaued as the pool saturated, but the system remained stable. 2. Throughput-Latency Trade-Off Once the connection pool fills (~50 active connections per node), additional clients queue: Throughput stabilizes at the pool's processing capacity Latency increases with queue depth Predictable, graceful behavior under high load 3. When to use PgBouncer Recommended for: Applications with bursty connection patterns (many short-lived connections) High connection counts that exceed node memory capacity Workloads where occasional queuing latency is acceptable Not recommended for: Applications requiring maximum throughput from steady workloads Long-running transactions (incompatible with transaction pooling) Scenarios where every millisecond of latency matters Multi-Shard Query Performance Multi-shard (fan-out) queries aggregate or join data across multiple nodes, representing analytical or reporting workloads. Massive Throughput Gains with Scaling Scaling up and out dramatically improved fan-out query throughput – from ~52 TPS to ~1008 TPS on the largest (≈20× gain). Low Concurrency Saturation Multi-shard queries peaked at low client counts—just 8 clients for 2-core clusters and ~96 for 4-core, 8-node setups. Latency Improves with Scale Larger clusters maintained sub-100 ms latency under higher concurrency, while smaller ones degraded quickly. Memory & I/O Bottlenecks Sufficient memory is crucial for fan-out queries, as memory starvation causes throughput to plateau well before CPU is fully utilized. Key Takeaways from Multi-shard Experiments 1. Lower Absolute Throughput Compared to Single-shard Workloads Even the best-performing configuration (4c8n at ~1000 TPS) achieves ~2% of single-shard throughput (~48k TPS). This reflects the inherent complexity of the analytical fan-out queries: cross-node data aggregation, significantly large amount of data retrieval. 2. Scaling Provides Dramatic Gains While absolute TPS remains modest, the 20× improvement from smallest to largest demonstrates that multi-shard workloads benefit enormously from scaling. Configuration Peak TPS Peak Clients Latency at Peak 2-core, 4-node 52 8 ~150ms 2-core, 8-node 168 8 ~50ms 4-core, 4-node 489 32 ~65ms 4-core, 8-node 1,008 96 ~95ms 3. Saturates at Low Concurrency Multi-shard queries reach peak throughput at fewer concurrent clients than single-shard queries: 2-core clusters: Saturate at just 8 clients 4-core, 8-node: Saturates around 96 clients After these points, the system maintains stable TPS, with latency rising as load increases. 4. Memory and I/O Bottlenecks Dominate Small Configurations The 2-core configurations (8 GB RAM per node) showed clear resource pressure: Memory pressure: Working sets exceeded available RAM, causing paging High IOPS: Thousands of disk operations per second indicated swapping to disk Throughput ceiling: Memory availability capped TPS before CPU was fully utilized In contrast, 4-core configurations (32 GB RAM per node) kept working sets in memory, achieving much higher throughput with minimal I/O. Key insight: For multi-shard workloads, sufficient memory is more important than CPU cores. Adequate memory provisioning is essential to unlock full performance. 5. Latency Escalates Rapidly Under Overload All configurations delivered fast response times (<50ms) at low loads. Once saturated: 2c4n cluster: Latency increased noticeably under sustained overload 4c8n cluster: Remained under 100ms until approaching the 96-client saturation point Practical implication: For multi-shard query workloads, over-provision resources to maintain consistent and predictable latency. Conclusion Connection scaling in Azure Database for PostgreSQL Elastic Clusters is a multifaceted challenge that depends on workload characteristics, cluster configuration, and resource constraints. Key takeaways from our benchmarking on read workloads: For Single-Shard OLTP-like Workloads: Scaling provides near-linear throughput gains (4.3× from 2c4n to 4c8n) Memory, not node count, determines maximum concurrent connections CPU becomes the throughput bottleneck at peak load PgBouncer trades ~10% throughput for almost unlimited connection scalability For Multi-Shard OLAP-like Workloads: Throughput operates at a different scale than single‑shard workloads Relative gains from scaling are massive (20× improvement observed) Memory sufficiency is critical—adequate RAM is essential to maintain strong performance Saturation occurs at low concurrency; keep client counts conservative General Principles: Scale up (higher SKU) to support more connections and memory-intensive queries Scale out (more nodes) to increase aggregate throughput and data capacity Use PgBouncer to manage connection bursts and exceed node memory limits Monitor continuously and adjust based on actual workload patterns By understanding these dynamics and applying the decision frameworks provided, you can architect Elastic Clusters that deliver optimal performance, reliability, and cost-efficiency for your specific application requirements. References and Resources https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-elastic-clusters https://learn.microsoft.com/en-us/postgresql/citus https://www.postgresql.org/docs/current/runtime-config-connection.html https://www.postgresql.org/docs/current/pgbench.html Analyzing the Limits of Connection Scalability in Postgres | Microsoft Community HubMarch 2026 Recap: Azure Database for PostgreSQL
Hello Azure community, March was packed with major feature announcements for Azure Database for PostgreSQL. From the general availability of SSDv2, cascading read replicas, to online migration and new monitoring capabilities for logical replication slots to help ensure slots are preserved, this update brings a range of improvements to performance, scale, and reliability. Features SSDv2 - Generally Available Cascading Read replica - Generally Available Online migration using PgOutput plugin - Generally Available Google AlloyDB as a migration source - Generally Available EDB Extended Server as a migration source - Generally Available Logical replication slot synchronization metrics - Preview Defender Security Assessments - Preview New enhancements in the PostgreSQL VS Code Extension Latest PostgreSQL minor versions: 18.3, 17.9, 16.13, 15.17, 14.22 New extension support for PostgreSQL 18 on Azure Database for PostgreSQL Guide on PostgreSQL Buffer Cache Analysis, query rewriting and elastic clusters SSDv2 - Generally Available Premium SSD v2 is now generally available for Azure Database for PostgreSQL Flexible Server, delivering significant performance and cost-efficiency improvements for I/O‑intensive workloads. It offers up to 4× higher IOPS, lower latency, and improved price‑performance. With independent scaling of storage and performance, you only pay for what you need. Premium SSD v2 supports storage scaling up to 64 TiB, with performance reaching 80,000 IOPS and 1,200 MiB/s throughput, without tying performance to disk size. IOPS and throughput can be adjusted instantly, with no downtime. Additionally, built‑in baseline performance at no additional cost ensures consistent performance even for smaller deployments, making Premium SSD v2 a strong choice for modern, high‑demand PostgreSQL applications. For details about the Premium SSD v2 release, see the GA Announcement Blog and documentation Cascading read replica - Generally available Cascading read replicas are now generally available, giving customers greater flexibility to create read replicas from existing read replicas. This capability supports up to two levels of replication and up to 30 read replicas in total, with each read replica able to host up to five cascading replicas. With cascading read replicas, you can more effectively distribute read traffic across multiple replicas, deploy regional or hierarchical read replicas closer to end users, reduce read latency, and improve overall query performance for read‑heavy workloads. In addition, we’ve rolled out switchover support for both intermediate and cascading read replicas, making it easier to manage replica topologies. Learn more about cascading read replicas through our documentation and a detailed blog walkthrough. Online migration using PgOutput plugin - Generally Available The new addition of the PgOutput plugin helps make your Online migration to Azure more robust and seamless. The native "Out-of-the-Box" support that PgOutout offers is more suited for Online Production migrations compared to other logical decoding plugins. PgOutput offers higher throughput and superior performance compared to other logical decoding plugins ensuring your Online migration has very limited downtime. PgOutput also offers fine-grained filtering using Publications where you can migrate specific tables and filter by specific operations. For more details about this update, see the documentation. Google AlloyDB as a migration source - Generally Available Google AlloyDB is now supported as a source in Azure Database for PostgreSQL Migration Service. You can use this capability to migrate your AlloyDB workloads directly to Azure Database for PostgreSQL, using either offline or online migration options. This support helps you move your PostgreSQL databases to Azure with confidence, while taking advantage of Azure’s flexibility and scalability. To know more about this feature, visit our documentation. EDB Extended Server as a migration source - Generally Available Azure Database for PostgreSQL Migration Service now supports EDB Extended Server as a migration source. This enables you to migrate EDB Extended Server workloads to Azure Database for PostgreSQL using both offline and online migration methods. With this addition, you can transition PostgreSQL databases to Azure smoothly and benefit from the scale and flexibility of the Azure platform. For more details about this update, see the documentation. Logical replication slot sync status metric - Preview You can now monitor whether your logical replication slots are failover‑ready using the new logical_replication_slot_sync_status metric, now in preview. This metric provides a simple binary signal indicating whether logical replication slots are synchronized across High availability (HA) primary and standby nodes. It helps you quickly assess failover readiness without digging into replication internals especially valuable for CDC pipelines such as Debezium and Kafka, where data continuity during failover is critical. Learn more about logical replication metrics in the documentation. Defender Security Assessments - Preview In March, we introduced two new Microsoft Defender for Cloud CSPM security recommendations for Azure Database for PostgreSQL Flexible Server, now available in public preview: Geo-redundant backups should be enabled for PostgreSQL Servers require_secure_transport should be set to "on" for PostgreSQL Servers These integrated assessments continuously evaluate database configuration settings against security best practices, helping customers proactively identify and manage security posture risks for their Azure PostgreSQL servers while maintaining alignment with internal and industry standards. Additional security posture assessments for Azure PostgreSQL will be introduced as they become available. To learn more, refer to the reference table for all data security recommendations in Microsoft Defender for Cloud. New enhancements in the PostgreSQL VS Code Extension The March release (v1.20) of the PostgreSQL VS Code extension delivers new server management capabilities, enhanced query plan analysis, visual improvements, and a batch of bug fixes. Clone Server: You can now clone an Azure PostgreSQL Flexible Server directly from within the extension. The clone operation is available from the server management UI, allowing you to duplicate a server configuration including region, SKU, and settings without leaving VS Code. Entra ID Authentication for AI-Powered Schema Conversion: The Oracle-to-PostgreSQL migration experience now supports Microsoft Entra ID authentication for Azure OpenAI connectivity, replacing API key–based authentication. This enables enterprise-grade identity management and access control for AI-powered schema conversion workflows. Query Plan Visualization Improvements: The Copilot-powered “Analyze with Copilot” feature for query plans has been improved with more relevant optimization recommendations and smoother SQL attachment handling during plan analysis. Apache AGE Graph Visualizer Enhancements: The graph visualizer received a visual refresh with modernized edge rendering, a color-coded legend, and a new properties pane for exploring element details. Object Explorer Deep Refresh: The Object Explorer now supports refreshing expanded nodes in place, so newly created tables and objects appear immediately without needing to disconnect and reconnect. Settings Management: The extension now supports both global user settings and local .vscode/settings.json, providing more robust connection settings management across configuration sources. Bug Fixes: This release includes numerous bug fixes across script generation (DDL for triggers, materialized views, and functions), IntelliSense (foreign table support), JSON data export, query execution, and server connectivity. Latest PostgreSQL minor versions: 18.3, 17.9, 16.13, 15.17, 14.22 Azure PostgreSQL now supports the latest PostgreSQL minor versions: 18.3, 17.9, 16.13, 15.17, and 14.22. These updates are applied automatically during planned maintenance windows, ensuring your databases stay up to date with critical fixes and reliability improvements, with no manual action required. This is an out-of-cycle release that addresses regressions identified in the previous update. The release includes fixes across replication, JSON functions, query correctness, indexing, and extensions like pg_trgm, improving overall stability and correctness of database operations. For details about the minor release, see the PostgreSQL announcement. New extension support for PostgreSQL 18 on Azure Database for PostgreSQL Azure Database for PostgreSQL running PostgreSQL 18 now supports extensions that enable graph querying, in‑database AI integration, external storage access, and scalable vector similarity search, expanding the types of workloads that can be handled directly within PostgreSQL. Newly supported extensions include: AGE (Apache AGE v1.7.0): Adds native graph data modeling and querying capabilities to PostgreSQL using openCypher, enabling hybrid relational–graph workloads within the same database. azure_ai: Enables direct invocation of Microsoft Foundry models from PostgreSQL using SQL, allowing AI inference and embedding generation to be integrated into database workflows. azure_storage: Provides native integration with Azure Blob Storage, enabling PostgreSQL to read from and write to external storage for data ingestion, export, and hybrid data architectures. pg_diskann: Introduces disk‑based approximate nearest neighbor (ANN) indexing for high-performance vector similarity search at scale, optimized for large vector datasets with constrained memory. Together, these extensions allow PostgreSQL on Azure to support multi-model, AI‑assisted, and data‑intensive workloads while preserving compatibility with the open‑source PostgreSQL ecosystem. Guide on PostgreSQL buffer cache analysis, query rewriting We have rolled out two new blogs on PostgreSQL buffer cache analysis and PostgreSQL query rewriting and subqueries. These blogs help you better understand how PostgreSQL behaves under the hood and how to apply practical performance optimizations whether you’re diagnosing memory usage, reducing unnecessary disk I/O, or reshaping queries to get more efficient execution plans as your workloads scale. PostgreSQL Buffer Cache Analysis This blog focuses on understanding PostgreSQL memory behavior through shared_buffers, the database’s primary buffer cache. Using native statistics and the pg_buffercache extension, it provides a data‑driven approach to evaluate cache efficiency, identify when critical tables and indexes are served from memory, and detect cases where disk I/O may be limiting performance. The guide offers a repeatable methodology to support informed tuning decisions as workloads scale. PostgreSQL Query Rewriting and Subqueries This blog explores how query structure directly impacts PostgreSQL execution plans and performance. It walks through common anti‑patterns and practical rewrites such as replacing correlated subqueries with set‑based joins, using semi‑joins, and pre‑aggregating large tables to reduce unnecessary work and enable more efficient execution paths. Each scenario includes clear explanations, example rewrites, and self‑contained test scripts you can run. Azure Postgres Learning Bytes 🎓 How to create and store vector embeddings in Azure Database for PostgreSQL Vector embeddings sit at the core of many modern AI applications from semantic search and recommendations to RAG‑based experiences. But once you generate embeddings, an important question follows: how do you generate and store them in your existing database server? With Azure Database for PostgreSQL, you can generate and store vector embeddings directly alongside your application data. By using the `azure_ai` extension, PostgreSQL can seamlessly integrate with Azure OpenAI to create embeddings and store them in your database. This learning byte walks you through a step‑by‑step guide to generating and storing vector embeddings in Azure Database for PostgreSQL. Step 1: Enable the Azure AI extension Azure Database for PostgreSQL supports the azure_ai extension, which allows you to call Azure OpenAI service. Connect to your database and run: CREATE EXTENSION IF NOT EXISTS azure_ai; Step 2: Create (or use existing) Azure OpenAI resource You need an Azure OpenAI resource in your subscription with an embedding model deployed. In the Azure portal, create an Azure OpenAI resource. Deploy an embedding model (for example, text-embedding-3-small). Azure OpenAI provides the endpoint URL and API key Step 3: Get endpoint and API key Go to your Azure OpenAI resource in the Azure portal. Select Keys and Endpoint. Copy: Endpoint API Key (Key 1 or Key 2) Step 4: Configure Azure AI extension with OpenAI details Store the endpoint and key securely inside PostgreSQL SELECT azure_ai.set_setting( 'azure_openai.endpoint', 'https://<your-endpoint>.openai.azure.com' ); SELECT azure_ai.set_setting( 'azure_openai.subscription_key', '<your-api-key>' ); Step 5: Generate an embedding SELECT LEFT( azure_openai.create_embeddings( 'text-embedding-3-small', 'Sample text for PostgreSQL Lab' ):: text, 100 ) AS vector_preview; Step 6: Add a vector column Add a vector column to store embeddings (example uses 1536‑dimensional vectors): ALTER TABLE < table - name > ADD COLUMN embedding VECTOR(1536); Step 7: Store the embedding Update your table with the generated embedding: UPDATE < table - name > SET embedding = azure_openai.create_embeddings( 'text-embedding-3-small', content ); Conclusion That’s a wrap for our March 2026 recap. This month brought a set of meaningful updates focused on making Azure Database for PostgreSQL more performant, reliable, and scalable whether you’re modernizing workloads, scaling globally, or strengthening your security posture. We’ll be back soon with more exciting announcements and key feature enhancements for Azure Database for PostgreSQL, so stay tuned! Your feedback is important to us, have suggestions, ideas, or questions? We’d love to hear from you: https://aka.ms/pgfeedback. Follow us here for the latest announcements, feature releases, and best practices: Microsoft Blog for PostgreSQL.Combining pgvector and Apache AGE - knowledge graph & semantic intelligence in a single engine
Inspired by GraphRAG and PostgreSQL Integration in Docker with Cypher Query and AI Agents, which demonstrated how Apache AGE brings Cypher based graph querying into PostgreSQL for GraphRAG pipelines. This post takes that idea further combining AGE's graph traversal with pgvector's semantic search to build a unified analytical engine where vectors and graphs reinforce each other in a single PostgreSQL instance. This post targets workloads where entity types, relationship semantics, and schema cardinality are known before ingestion. Embeddings are generated from structured attribute fields; graph edges are typed and written by deterministic ETL. No LLM is involved at any stage. You should use this approach when you have structured data and need operational query performance, and deterministic, auditable, sub-millisecond retrieval. The problem nobody talks about the multi database/ multi hop tax If you run technology for a large enterprise, you already know the data problem. It is not that you do not have enough data. It is that your data lives in too many places, connected by too many fragile pipelines, serving too many conflicting views of the same reality. Here is a pattern that repeats across industries. One team needs to find entities "similar to" a reference item — not by exact attribute match, but by semantic meaning derived from unstructured text like descriptions, reviews, or specifications. That is a vector similarity problem. Another team needs to traverse relationships trace dependency chains, map exposure paths, or answer questions like "if this node is removed, what downstream nodes are affected?" That is a graph traversal problem. Meanwhile, the authoritative master data of IDs, attributes, pricing, transactional history already lives in Postgres. Now you are operating three databases. Three bills. Three sets of credentials. Three backup strategies. A fragile ETL layer stitching entity IDs across systems, breaking silently whenever someone adds a new attribute to the master table. And worst of all, nobody can ask a question that spans all three systems without custom application code. Azure PostgreSQL database can already do all three jobs. Two extensions pgvector for vector similarity search and Apache AGE extension for graph traversal bringing these capabilities natively into the database. No new infrastructure. No sync pipelines. No multi database tax! This post walks through exactly how to combine them, why each piece matters at scale, and what kinds of queries become possible when you stop treating vectors and graphs as separate concerns. The architecture: Two extensions, One engine pgvector adds a native vector data type and distance operators (<=>, <->, <#>) with HNSW and IVFFlat index support. pg_diskann adds a third index type that keeps the index on disk instead of in memory, enabling large scale vector search without proportional RAM. example 1 - to run a product similarity query such as the one below which corelates products sold across multiple markets which are related (cosine similarity). - The limit clause in sub query limits the similarity search to closest 1 product recommendation - High similarity score of > 0.75 (aka 75% similarity in embeddings) -- Table DDL - for illuatration purposes only CREATE TABLE IF NOT EXISTS products ( id SERIAL PRIMARY KEY, sku TEXT UNIQUE NOT NULL, name TEXT NOT NULL, brand TEXT NOT NULL, category TEXT NOT NULL, subcategory TEXT, market TEXT NOT NULL, region TEXT, description TEXT, ingredients TEXT, avg_rating FLOAT DEFAULT 0.0, review_count INT DEFAULT 0, price_usd FLOAT, launch_year INT, status TEXT DEFAULT 'active', embedding vector(384) ); SELECT us.name AS us_product, us.brand AS us_brand, in_p.name AS india_match, in_p.brand AS india_brand, Round((1 - (us.embedding <=> in_p.embedding))::NUMERIC, 4) AS similarity FROM products us cross join lateral ( SELECT name, brand, embedding FROM products WHERE market = 'India' AND category = us.category ORDER BY embedding <=> us.embedding limit 1 ) in_p WHERE us.market = 'US' AND us.category = 'Skincare' AND us.avg_rating >= 4.0 AND round((1 - (us.embedding <=> in_p.embedding))::NUMERIC, 4)> 0.75 ORDER BY similarity DESC limit 20; AGE adds a cypher() function that executes cypher queries against a labeled property graph stored in the database managed and maintained under the ag_catalog schema. Vertices and edges become first class PostgreSQL rows with agtype properties. The age extension supports MATCH, CREATE, MERGE, WITH, and aggregations. example 2 - to run a product similarity query such as the one below which returns common products sold via multiple retail channels. SET search_path = ag_catalog, "$user", public; SELECT * FROM cypher('cpg_graph', $$ MATCH (p:Product)-[:SOLD_AT]->(walmart:RetailChannel {name: 'Walmart'}) MATCH (p)-[:SOLD_AT]->(target:RetailChannel {name: 'Target'}) MATCH (b:Brand)-[:MANUFACTURES]->(p) RETURN b.name AS brand, p.name AS product, p.category AS category, p.market AS market, p.price_usd AS price ORDER BY p.category, b.name $$) AS (brand agtype, product agtype, category agtype, market agtype, price agtype); The critical point and takeaway here is that both extensions participate in the same query planner and executor. A CTE that calls pgvector's <=> operator can feed results into a cypher() call in the next CTE all within a single transaction, sharing all available processes and control the database has to offer. Finally, you are looking at code that looks like - CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS age; SET search_path = ag_catalog, "$user", public; SELECT create_graph('knowledge_graph'); The bridge: pgvector → Apache AGE This is the architectural centrepiece where the mechanism that turns vector similarity scores into traversable graph edges. Without this “bridge” pgvector and AGE are two isolated extensions. Why bridge at all? pgvector answers: "What is similar to X?" AGE answers: "What is connected to Y, and how?" These are fundamentally different questions operating on fundamentally different data structures. pgvector works on a flat vector space and every query is a distance calculation against an ANN index. AGE works on a labelled property graph where every query is a pattern match across typed nodes and edges. What if now the question is – What is like X and connected to Y and how? This is where the bridge gets activated comes into life. This takes cosine similarity distance scores from pgvector and writes them as SIMILAR_TO edges in the AGE property graph turning a distance computation into a traversable relationship. Once similarity is an edge, cypher queries can then combine it with structural edges in a single declarative pattern. for ind_prod_id, us_prod_id, similarity in pairs: execute_cypher(cur, f""" MATCH (a:Product {{product_id: { ind_prod_id }}}), (b:Product {{product_id: { us_prod_id }}}) CREATE (a)-[:SIMILAR_TO {{score: {score:.4f}, method: 'pgvector_cosine'}}]->(b) CREATE (b)-[:SIMILAR_TO {{score: {score:.4f}, method: 'pgvector_cosine'}}]->(a) """) The cypher() function translates Cypher into DML against ag_catalog tables under the hood, these are plain PostgreSQL heap inserts just like another row. The score property is the edge weight on the SIMILAR_TO relationship. Its value is the similarity score computed from pgvector using cosine similarity, so a higher score means the two products are more semantically similar. The method property is metadata on that same edge. It records how the score was produced. In this case, pgvector_cosine is just a string label indicating that the relationship was derived using pgvector based cosine similarity. Cosine similarity is symmetric, but property graph traversal is directional i.e. MATCH (a)-[:SIMILAR_TO]->(b) won't find the reverse path unless both directional edges exist. Why this combination matters One backup strategy. One monitoring stack. One connection pool. One failover target. One set of credentials. One database restore considerations - for teams already running Az PostgreSQL databases in production this adds capabilities without adding any net new infrastructure. Unified cost model The planner assigns cost estimates to index scan for both execution engines using the same cost framework it uses for B-tree lookups and sequential scans. It can decide whether to use the HNSW index or fall back to a sequential scan based on table statistics and server parameters. As you have learnt so far, there is no separate storage or database engine to learn. Bringing all this knowledge together Examples 1 and 2 were all about native vector search and native graph search example in a classic product catalog scenario, respectively. Now, let’s bring this to life - What if now the question is – What is like X and connected to Y and how? In this use case - pgvector finds the cross market matches (as shown in example 1), then Cypher checks which of those matches are sold at both Walmart and Target: SET search_path = ag_catalog, "$user", public; -- Cross-market matching (pgvector) → Retail channel overlap (graph) WITH cross_market AS ( SELECT us.id AS us_id, us.name AS us_product, us.brand AS us_brand, in_p.id AS india_id, in_p.name AS india_match, in_p.brand AS india_brand, ROUND((1 - (us.embedding <=> in_p.embedding))::numeric, 4) AS similarity FROM products us CROSS JOIN LATERAL ( SELECT id, name, brand, embedding FROM products WHERE market = 'India' AND category = us.category ORDER BY embedding <=> us.embedding LIMIT 1 ) in_p WHERE us.market = 'US' AND us.category = 'Skincare' AND us.avg_rating >= 4.0 AND ROUND((1 - (us.embedding <=> in_p.embedding))::numeric, 4) > 0.75 ), dual_channel AS ( SELECT (pid::text)::int AS product_id, brand::text AS brand FROM cypher('cpg_graph', $$ MATCH (p:Product)-[:SOLD_AT]->(w:RetailChannel {name: 'Walmart'}) MATCH (p)-[:SOLD_AT]->(t:RetailChannel {name: 'Target'}) MATCH (b:Brand)-[:MANUFACTURES]->(p) RETURN p.product_id AS pid, b.name AS brand $$) AS (pid agtype, brand agtype) ) SELECT cm.us_product, cm.us_brand, cm.india_match, cm.india_brand, cm.similarity, CASE WHEN dc.product_id IS NOT NULL THEN 'Yes' ELSE 'No' END AS india_match_at_walmart_and_target FROM cross_market cm LEFT JOIN dual_channel dc ON dc.product_id = cm.india_id ORDER BY cm.similarity DESC LIMIT 20; Conclusion The Azure PostgreSQL database ecosystem has quietly assembled the components for a unified semantic + structural analytics engine in form of extensions. pgvector with pg_diskann delivers production grade approximate nearest-neighbour search with ANN indexes. Apache AGE delivers cypher based property graph traversal. Together with a “bridge,” they enable query patterns that are impossible in either system alone and they do it within the ACID guarantees, operational tooling, and SQL vocabulary knowledge you already have. Stop paying for three databases when one will do!Cascading Read Replicas Now Generally Available!
We’re excited to announce the General Availability of cascading read replicas in Azure Database for PostgreSQL. This capability allows you to create read replicas for your Azure Database for PostgreSQL instance not only from a primary server, but also from existing read replicas, enabling multi‑level replication chains. Coordinating read‑heavy database workloads across multiple regions can be challenging, especially when you’re trying to deliver low‑latency read response experiences to users spread across different geographic locations. One effective way to address this is by placing read replicas closer to where your users are, allowing applications to serve read requests with significantly reduced latency and improved performance. What are cascading read replicas? With cascading read replicas, you can scale read‑intensive workloads more effectively, distribute read traffic efficiently, and support advanced deployment topologies such as globally distributed applications. Each read replica can act as a source for additional replicas, forming a tree‑like replication structure. For example, if your primary server is deployed in one region, you can create direct replicas in nearby regions and then cascade additional replicas to more distant locations. This approach helps spread read traffic evenly while minimizing latency for users around the world. We support up to 2 levels of replication with this feature. Level 1 will be all the read replicas and level 2 will be cascading read replicas. Why use cascading read replicas? Improved scalability Cascading read replicas support multi‑level replication, making it easier to handle high volumes of read traffic without overloading a single instance by scaling up to 30 read replicas. Geographic distribution By placing replicas closer to your global user base, you can significantly reduce read latency and deliver faster, more responsive application experiences. Efficient read traffic distribution Distributing read workloads across multiple replicas helps balance load, improving overall performance and reliability. Additionally, cascading read replicas offer operational flexibility. If you observe replication lag, you can perform a switchover operation between a cascading read replica with its source or intermediate replica, helping you maintain optimal performance and availability for your replicas. How does replication work with cascading read replicas? The primary server acts as a source for the read replica. Data is asynchronously replicated to these replicas. When we add cascading replicas, the previous replicas act as a data source for replication. In the diagram above, “primary-production-server” is the primary server with three read replicas. One of these replicas, “readreplica01”, serves as the source for another read replica, “readreplica11” which is a cascading read replica. With cascading read replicas, you can add up to five read replicas per source and replicate data across two levels, as shown in the diagram. This allows you to create up to 30 read replicas in total five read replicas directly from the primary server, and up to 25 additional replicas at the second level (each second-level replica can have up to five read replicas). If you notice replication lag between an intermediate read replica and a cascading read replica, you can use a switchover operation to swap “readreplica01” and “readreplica11”, helping reduce the impact of lag. To learn more about cascading read replicas, please refer to our documentation: Cascading read replicas Deploying cascading read replicas on Azure portal Navigate to the “Replication” tab and then click on “Create replica” highlighted in red as shown below: After creating a read replica as the below screenshot shows that you have 1 read replica that is attached to the primary instance. Click on the created replica and navigate to the replication tab, source server is “read-replica-01” and we will be creating a cascading read replica under this. Once cascading read replica is created you can see the role of “read-replica-01” has now changed to Source, Replica. You can perform site swap operation by clicking on the promote button for cascading read replica. Deploy cascading read replica with terraform: Before you start, make sure you have: An existing primary PostgreSQL Flexible Server At least one read replica already created from the primary AzureRM provider with latest version Proper permissions on the Azure subscription and resource group Configure the AzureRM Provider: Start by configuring the AzureRM provider in your Terraform project. terraform { required_providers { azurerm = { source = "hashicorp/azurerm" version = "~> 3.80" } } } provider "azurerm" { features {} } Reference the existing read replica server using the data block to reference the replica server. data "azurerm_postgresql_flexible_server" "source_replica" { name = "my-read-replica-1" resource_group_name = "my-resource-group" } Now create a new PostgreSQL Flexible Server and point it to the replica using create_source_server_id. resource "azurerm_postgresql_flexible_server" "cascading_replica" { name = "my-cascading-replica" resource_group_name = "my-resource-group" location = data.azurerm_postgresql_flexible_server.source_replica.location version = data.azurerm_postgresql_flexible_server.source_replica.version delegated_subnet_id = data.azurerm_postgresql_flexible_server.source_replica.delegated_subnet_id private_dns_zone_id = data.azurerm_postgresql_flexible_server.source_replica.private_dns_zone_id create_mode = "Replica" create_source_server_id = data.azurerm_postgresql_flexible_server.source_replica.id storage_mb = 32768 sku_name = "Standard_D4s_v3" depends_on = [ data.azurerm_postgresql_flexible_server.source_replica ] } Apply the Terraform Configuration terraform init terraform plan terraform apply Key Considerations Cascading read replicas allow for up to 5 read replicas and two levels of replication. Creating cascading read replicas is supported in PostgreSQL version 14 and above. Promote operation is not supported for intermediate read replicas with cascading read replicas. Conclusion Cascading read replicas in Azure Database for PostgreSQL offer a scalable way to distribute your read traffic across the same and different regions, reducing the read workload on primary database. For globally distributed applications, this can improve read latency as well as resilience and performance. This design supports horizontal scaling as your application demand grows, ensuring you can handle a high volume of read requests without compromising speed. Get started with this feature today and scale your read workloads.
