Postgres horizontal scaling with elastic clusters on Azure Database for PostgreSQL Flexible server
Elastic clusters (Preview) is a Flexible server feature that enables a database to be scaled out horizontally beyond a single node. Powered by the open-source Citus extension developed by Microsoft, elastic clusters offer horizontal scale out through row and schema-based sharding capabilities. With elastic clusters, you can future proof horizontal scale out, adding nodes as needed as the application grows and outgrows the capacity of a single node. By providing a unified endpoint to the cluster, elastic clusters eliminate the need for complex application-layer sharding, significantly simplifying the management of growing Flexible Server deployments. Online shard management and isolation capabilities are a core part of the services offloading this burden from the application stack. With elastic clusters, scaling out your applications has never been easier or more efficient. Benefits of using elastic clusters (Preview) Familiarity: It’s the same Flexible server you know and love, with the same feature set. Horizontal scaling: Grow your cluster out by adding more Flexible servers – lifting the single node limitation for your application growth. Simplicity: Offload complexity of sharding from your application layer to the managed service. Cost efficiency: You are not paying more than you would for the same amount of Flexible servers as nodes in your elastic cluster with the benefit of simplified management of the fleet. AI ready: Modern AI applications require storage of vast amounts of vectorized data. Their data models, however, are usually simple and scale out very well with sharding models provided by elastic clusters. What are elastic clusters? The elastic clusters feature of Azure Database for PostgreSQL Flexible server is a managed offering of horizontal scaling, powered by the Citus extension. Elastic clusters handle managing and configuring multiple PostgreSQL instances as a single resource, setting up the nodes, and providing cluster level choices for high availability, disaster recovery, compute and storage and resiliency. Nodes in your cluster reside in the same availability zones for optimal performance and the various Flexible server features and capabilities to become cluster aware such as backups, metrics, Entra ID authentication to just name a few. Every node in the elastic cluster is capable of handling DML, you can run your read and write queries on any node on the cluster, and it will automatically fetch the data from their current placement. In many distributed systems, often all queries go through a single node which can make it a bottleneck. With Elastic Clusters, however, when you connect via port 7432, the workload is automatically distributed across all nodes. What is sharding? Sharding is the act of splitting data into logical groups, that can then be moved into separate machines and retrieved individually. Sharding also supports queries that need data from two or more different shard groups living on different nodes. Such queries would be computed individually by each node and then rolled up by the initiating node before providing the result. You can also read more about sharding models here: Sharding models - elastic clusters Schema-based sharding Schema-based sharding is the most intuitive way to understand how this works. Every table in a distributed schema becomes its own shard. Shards can be co-located - moved around between nodes as a group. If your application creates a schema for every tenant in the system (or a microservice) all the data will live together on the same node and even if it moves around, the system will route queries to the correct node. The benefit is a pure architectural lift-and-shift for any application that already uses schema based sharding. Suddenly your legacy application can scale across many machines without a single line of code being changed. This is assuming that your application already uses schema based sharding but for database per tenant models the conversion to a schema-based model is easy. Follow the Tutorial: Design for microservices with elastic cluster to try schema-based sharding yourself! Row-based sharding Row-based sharding is different and requires some DDL modifications and choice of distribution key but with those few changes, it is a very scalable method when there is a need for high density packing of tenants. One way to think about row-based sharding is to paint parallels to table partitioning. With partitioning you decide what column can split the data into sensible chunks that can be queried in isolation – greatly reducing the amount of data you need to weed through to get results. A good example is partitioning by year, knowing that you rarely reach to past year. You then need to make sure that your queries all use the partition key in the WHERE clause, because without it the system doesn’t know how to filter out (prune) partitions that don’t hold the required data – neglecting the benefit of the partitioning. As with partitioning, row-based sharding required determining a distribution key. Selecting a good key can be a challenge itself – something with good cardinality (so data can be spread out), not skewed (so that data doesn’t fully land into one big shard) and logically splitting your tenants (avoiding cross tenant queries). Typical good distribution keys are tenant_id and device_id (to fan out writes) but this will heavily depend on your application. If you go back to the partitioning example, year was a great partition key, but it is a terrible distribution key. It has low cardinality, and only one node per year would be taking writes by not leveraging spreading out data to multiple shards. Instead of distributing on year, use a key with better cardinality in your system – you can still leverage partitioning on top of distribution as partitioned tables can be distributed. Having selected the key, the next step is easy. Just distribute the tables and this is as simple as calling: SELECT create_distributed_table('accounts', 'account_id'); SELECT create_distributed_table('campaigns', 'campaign_id'); There are many table types in elastic clusters, you can read more about them here: Table types - elastic clusters As with schema based sharding, each tenant is assigned into a shard but unlike row-based sharding a shard can be shared among tenants. Like with partitioning you can then think of a shard as a smaller PostgreSQL table and if you put all of them together – you would get the whole thing. In the same vein, as with partitioning if queries ran by the application miss the distribution key in their WHERE clause – they would start hitting all nodes in the cluster to find the required data. In some cases, such fan-out queries are desired and a form of implementing parallelization of query execution among all cluster nodes. In most cases, however, it is more efficient to have queries that filter down to one node. The biggest benefit of row-based sharding is the density of how tight tenants can be packed on the same machine. It’s as good as it gets, which is rows in the same table – the drawback, is the work up front required to decide on the schema and potential query changes in the application. Follow either tutorial: Design multitenant database with elastic cluster or Design a real-time dashboard with elastic cluster, to try out row-based sharding yourself! When things get hot There will be times where either the system needs room to grow (CPU, Storage, connections), or a specific tenant becomes very noisy (making other tenants experience degraded performance). Elastic clusters allow you to address both. In the case of capacity (either CPU, Storage, maximum connections) being reached, just grow your cluster by adding nodes and call trigger online rebalancer. Without blocking your existing workloads, data will be redistributed among the available nodes (including the new node) by disk size, shard count or any strategy that you decide to implement yourself. This is as easy as connecting to the cluster and issuing: SELECT citus_rebalance_start(); You may not need to rebalance depending on how you sharded your data. The new node will be immediately used for new inserts as soon as it becomes online and that may be just enough to redistribute the load on your cluster. When one tenant throws a party, you can decide to move them out to their own node though, sometimes it is easier to move the well behaving tenants if they happen to store less data than the noisy one. With elastic clusters this can be as easy as running: SELECT citus_schema_move('wideworldimporters','10.54.0.254', 7003); Using elastic clusters in Azure Database for PostgreSQL Flexible server Step 1: Create an elastic cluster Begin by setting up a new instance of elastic cluster with Azure Database for PostgreSQL Flexible server Azure portal Quickstart: Create elastic cluster Select your cluster size, up to 10 nodes can be deployed during preview. Review your settings after confirming the compute and storage configuration. Step 2: Choosing a sharding model Citus on Flexible server offers two sharding models: row-based sharding and schema-based sharding. You can learn more about sharding models here: Sharding models - elastic clusters Step 3: Follow one of the tutorials For row-based sharding: Design multitenant database with elastic cluster Design a real-time dashboard with elastic cluster For schema-based sharding: Design for microservices with elastic cluster Frequently Asked Questions In what regions are elastic clusters available? Elastic clusters are currently available in the following regions: East Asia East US North Europe UK South West US West US 3 Please see Limitations of elastic clusters for an up-to date list. Will all features of Flexible server be available on elastic clusters? We aim to feature parity, some cluster incompatible extensions like TimescaleDB may not be available. Other features may need a bit of work to become cluster aware. Are there any limitations I should be aware of? Please see Limitations of elastic clusters Ready to dive in? Get started for free with an Azure free account Azure Database for PostgreSQL Quickstart: Create elastic cluster with Azure portal Learn more Elastic clusters with PostgreSQL Flexible server Distributed Postgres. At any scale. - Citus Data citusdata/citus: Distributed PostgreSQL as an extensionConnection 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 HubDistribute PostgreSQL 18 with Citus 14
The Citus 14.0 release is out and includes PostgreSQL 18 support! We know you've been waiting, and we've been hard at work adding features we believe will take your experience to the next level, focusing on bringing the Postgres 18 exciting improvements to you at distributed scale. The Citus database is an open-source extension of Postgres that brings the power of Postgres to any scale, from a single node to a distributed database cluster. Since Citus is an extension, using Citus means you're also using Postgres, giving you direct access to the Postgres features. And the latest of such features came with Postgres 18 release! PostgreSQL 18 is a substantial release: asynchronous I/O (AIO), skip-scan for multicolumn B-tree indexes, uuidv7(), virtual generated columns by default, OAuth authentication, RETURNING OLD/NEW, and temporal constraints. For those of you who are interested in upgrading to Postgres 18 and scaling these new features of Postgres: you can upgrade to Citus 14.0! Let's take a closer look at what's new in Citus 14.0. Postgres 18 support in Citus 14.0 Citus 14.0 introduces support for PostgreSQL 18. This means that just by enabling PG18 in Citus 14.0, all the query performance improvements directly reflect on the Citus distributed queries, and several optimizer improvements benefit queries in Citus out of the box! Among the many new features in PG 18, the following capabilities enabled in Citus 14.0 are especially noteworthy for Citus users. To learn more about how you can use Citus 14.0 + PostgreSQL 18, as well as currently unsupported features and future work, you can consult the Citus 14.0 Updates page, which gives you detailed release notes. PostgreSQL 18 highlights that benefit Citus clusters Because Citus is implemented as a Postgres extension, the following PG18 improvements benefit your distributed cluster automatically, no Citus-specific changes needed. Faster scans and maintenance via AIO Postgres 18 adds an asynchronous I/O subsystem that can improve sequential scans, bitmap heap scans, and vacuuming—workloads that show up constantly in shard-heavy distributed clusters. This means your Citus cluster can benefit from faster table scans and more efficient maintenance operations without any code changes. You can control the I/O method via the new io_method GUC: -- Check the current I/O method SHOW io_method; Better index usage with skip-scan Postgres 18 expands when multicolumn B-tree indexes can be used via skip scan, helping common multi-tenant schemas where predicates don't always constrain the leading index column. This is particularly valuable for Citus users with multi-tenant applications where queries often filter by non-leading columns. -- Multi-tenant index: (tenant_id, created_at) -- PG18 skip-scan lets this query use the index even without tenant_id SELECT * FROM events WHERE created_at > now() - interval '1 day' ORDER BY created_at DESC LIMIT 100; uuidv7() for time-ordered UUIDs Time-ordered UUIDs can reduce index churn and improve locality; Postgres 18 adds uuidv7(). This is especially useful for distributed tables where you want predictable ordering and better index performance across shards. -- Use uuidv7() as a time-ordered primary key CREATE TABLE events ( id uuid DEFAULT uuidv7() PRIMARY KEY, tenant_id bigint, payload jsonb ); SELECT create_distributed_table('events', 'tenant_id'); OAuth authentication support Postgres 18 adds OAuth authentication, making it easier to plug database auth into modern SSO flows often a practical requirement in multi-node deployments. This simplifies authentication management across your Citus coordinator and worker nodes. What Citus 14 adds for PostgreSQL 18 compatibility While the highlights above work out of the box, PG18 also introduces new SQL syntax and behavior changes that require Citus-specific work parsing/deparsing, DDL propagation across coordinator + workers, and distributed execution correctness. Here's what we built to make these work end-to-end. JSON_TABLE() COLUMNS PG18 expands SQL/JSON JSON_TABLE() with a richer COLUMNS clause, making it easy to extract multiple fields from JSON documents in a single, typed table expression. Citus 14 ensures the syntax can be parsed/deparsed and executed consistently in distributed queries. CREATE TABLE pg18_json_test (id serial PRIMARY KEY, data JSON); SELECT jt.name, jt.age FROM pg18_json_test, JSON_TABLE( data, '$.user' COLUMNS ( age INT PATH '$.age', name TEXT PATH '$.name' ) ) AS jt WHERE jt.age BETWEEN 25 AND 35 ORDER BY jt.age, jt.name; Temporal constraints Postgres 18 adds temporal constraint syntax that Citus must propagate and preserve correctly: WITHOUT OVERLAPS for PRIMARY KEY / UNIQUE PERIOD for FOREIGN KEY CREATE TABLE temporal_rng ( id int4range, valid_at daterange, CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); SELECT create_distributed_table('temporal_rng', 'id'); CREATE FOREIGN TABLE ... LIKE Postgres 18 supports CREATE FOREIGN TABLE ... LIKE, letting you define a foreign table by copying the column layout (and optionally defaults/constraints/indexes) from an existing table. Citus 14 includes coverage so FDW workflows remain compatible in distributed environments. -- Copy column layout from an existing table CREATE FOREIGN TABLE my_ft (LIKE my_local_table EXCLUDING ALL) SERVER foreign_server OPTIONS (schema_name 'public', table_name 'my_local_table'); Generated columns (Virtual by Default) PostgreSQL 18 changes generated column behavior significantly: Virtual by default: Generated columns are now computed on read rather than stored, reducing write amplification Logical replication support: New publish_generated_columns publication option for replicating generated values CREATE TABLE events ( id bigint, payload jsonb, payload_hash text GENERATED ALWAYS AS (md5(payload::text)) VIRTUAL ); SELECT create_distributed_table('events', 'id'); VACUUM/ANALYZE ONLY semantics Postgres 18 introduces ONLY for VACUUM and ANALYZE so you can explicitly target only the parent of a partitioned/inheritance tree without automatically processing children. Citus 14 adapts distributed utility-command behavior so ONLY works as intended. -- Parent-only: do not recurse into partitions/children VACUUM (ANALYZE) ONLY metrics; ANALYZE ONLY metrics; Constraints: NOT ENFORCED + partitioned-table additions Postgres 18 expands constraint syntax in several ways that Citus must parse/deparse and propagate across coordinator + workers: CHECK constraints can be marked NOT ENFORCED FOREIGN KEY constraints can be marked NOT ENFORCED NOT VALID foreign keys on partitioned tables DROP CONSTRAINT ONLY on partitioned tables ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT ENFORCED; ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers(id) NOT ENFORCED; DML: RETURNING OLD/NEW Postgres 18 lets RETURNING reference both the previous (old) and new (new) row values in INSERT/UPDATE/DELETE/MERGE. Citus 14 preserves these semantics in distributed execution. UPDATE t SET v = v + 1 WHERE id = 42 RETURNING old.v AS old_v, new.v AS new_v; COPY expansions PG18 adds two useful COPY improvements that Citus 14 supports in distributed queries: COPY ... REJECT_LIMIT: set a threshold for how many rows can be rejected before the COPY fails, useful for resilient bulk loading into sharded tables COPY table TO from materialized views: export data directly from materialized views -- Tolerate up to 10 bad rows during bulk load COPY my_distributed_table FROM '/data/import.csv' WITH (FORMAT csv, REJECT_LIMIT 10); MIN()/MAX() on arrays and composite types PG18 extends MIN() and MAX() aggregates to work on arrays and composite types. Citus 14 ensures these aggregates work correctly in distributed queries. CREATE TABLE sensor_data ( tenant_id bigint, readings int[] ); SELECT create_distributed_table('sensor_data', 'tenant_id'); -- Now works with array columns SELECT MIN(readings), MAX(readings) FROM sensor_data; Nondeterministic collations PG18 extends LIKE and text-position search functions to work with nondeterministic collations. Citus 14 verifies these work correctly across distributed queries. sslkeylogfile connection parameter PG18 adds the sslkeylogfile libpq connection parameter for dumping SSL key material, useful for debugging encrypted connections. Citus 14 allows configuring this via citus.node_conn_info so it works across inter-node connections. Planner fix: enable_self_join_elimination PG18 introduces the enable_self_join_elimination planner optimization. Citus 14 ensures this works correctly for joins between distributed and local tables, avoiding wrong results that could occur in early PG18 integration. Utility/Ops plumbing and observability Citus 14 adapts to PG18 interface/output changes that affect tooling and extension plumbing: New GUC file_copy_method for CREATE DATABASE ... STRATEGY=FILE_COPY EXPLAIN (WAL) adds a "WAL buffers full" field; Citus propagates it through distributed EXPLAIN output New extension macro PG_MODULE_MAGIC_EXT so extensions can report name/version metadata New libpq parameter sslkeylogfile support via citus.node_conn_info Diving deeper into Citus 14.0 and distributed Postgres To learn more about Citus 14.0, you can: Check out the 14.0 Updates page to get the detailed release notes. As of this release, Citus documentation is now hosted on Microsoft Learn. With Citus 14, elastic clusters will soon support PostgreSQL 18, now available in Azure Database for PostgreSQL. You can stay connected on the Citus Slack and visit the Citus open source GitHub repo to see recent developments as well. If there's something you'd like to see next in Citus, feel free to also open a feature request issue :)Exciting things on the horizon for PostgreSQL fans @ Ignite 2025
If you’re passionate about PostgreSQL or just curious about what’s new, you’ll want to join us at Microsoft Ignite 2025. We have a packed lineup, including sessions exploring cutting-edge features and exclusive giveaways at the PostgreSQL on Azure booth. Haven’t registered yet? Now’s the time – sign up for Microsoft Ignite and start building your schedule. Below are the must-see PostgreSQL on Azure activities, with highlights of what you’ll learn at each. Add these to your agenda today. Sessions can fill up fast! Theater sessions: get a first look, fast I know from experience that attention spans can start to wane after hours-long keynotes, content-rich sessions, and conference socializing. Luckily, we have a couple of theater sessions that offer snackable but substantial information in less time than it will take to grab lunch. And they’re located conveniently on the main conference floor. PostgreSQL on Azure: Your launchpad for intelligent apps and agents (THR705) - See how we’re making PostgreSQL AI-aware for developers to drive app and agent innovation. Includes a demo of vector similarity search, semantic operators baked into Postgres, and more! Simplifying scale-out of PostgreSQL for performant multi-tenant apps (THR706) - Discover a smarter, simpler way to scale PostgreSQL using the new Elastic Clusters feature. If your app or service is growing fast (or you want it to!), add this breakout to learn how Azure makes it easier to scale Postgres and keep it reliable. These talks are a great way to sample what’s new and decide where to dive deeper. Plus, they’re fun and demo-heavy, and who doesn’t love a good demo? Breakout sessions: a deep dive into Postgres innovations Led by Azure product leaders and executives from organizations driving innovation backed by PostgreSQL, these breakout sessions will dive into the coolest new capabilities and real-world use cases. If you want rich, technical content and more live demos, these are for you. Build mission-critical apps that scale with PostgreSQL on Azure (BRK127) - Get a closer look at the next generation of PostgreSQL on Azure. Add this session, if you’re curious about how we’re taking Postgres to the next level to support your mission-critical AI workloads. Modern data, modern apps: Innovation with Microsoft Databases (BRK134) - Gain insider knowledge on the latest innovations across open-source, SQL, and NoSQL databases, and understand how Microsoft’s integrated database portfolio supports next-gen innovation. Nasdaq Boardvantage: AI-driven governance on PostgreSQL and AI Foundry (BRK137) - Discover how a Fortune 100 merges trust with cutting-edge AI leveraging Azure’s AI-enriched and enterprise-ready solutions, including Azure Database for PostgreSQL, Azure Database for MySQL, Azure AI Foundry, Azure Kubernetes Service (AKS), and API Management. AI-assisted migration: The path to powerful performance on PostgreSQL (BRK123) - A before and after migration journey from Oracle to Azure Database for PostgreSQL. See how the new AI-assisted migration experience delivers conversion in a few clicks and minimal downtime. The blueprint for intelligent AI agents backed by PostgreSQL (BRK130) - If you’re into AI development, this session will spark ideas on bridging the gap between raw data and AI reasoning. You’ll leave with practical tips to turbocharge your AI agents with PostgreSQL. Each breakout session is 45 minutes with live demos and Q&A, so you’ll get plenty of detail and interaction with Postgres experts. Hands-on lab: experience coding with Azure superpowers Do you learn best by doing? Then our guided workshop, Build advanced AI agents with PostgreSQL (Lab515), is for you. In each 75-minute session, you’ll get to create a fully functional AI-powered application backed by PostgreSQL on Azure with step-by-step guidance and expert insight on the latest innovations enabling intelligent app development. All the tools and instructions you’ll need are provided. Labs have limited capacity, so be sure to reserve your seat for any of the four labs in advance. This lab is a great way to understand how all the pieces come together on Azure. And you’ll gain practical skills you can apply to your own projects, whether it’s customer support bots, intelligent search in your app, or any scenario where PostgreSQL + AI collide. Expert meet-up booth: meet the team, grab some swag If you still want more Postgres (or a little Postgres souvenir), you can stop by the PostgreSQL on Azure Expert Meetup booth in the Ignite Hub. This will be our homebase on the show floor, where you can: Meet the team: I’ll be there in person, along with engineers, program managers, cloud solution architects, and advocates from our team. Whether you have a burning technical question, want to share feedback, or need guidance for your specific use case, come chat with us. Get a quick demo re-run: Sometimes a 5-minute demo is worth a thousand words, especially after you’ve sat through all those words already in a keynote. The booth will have a monitor and a live environment so we can walk you through select use cases if you have questions - no appointment needed. Swag and giveaways: Ah yes, the goodies! We know conference swag is part of the fun, so we’ve got some special PostgreSQL-themed giveaways at the booth. I won’t spoil all the surprises, but rumor has it there are some limited-edition items up for grabs. Network with peers: The expert meet-up area is also a magnet for PostgreSQL enthusiasts. You might bump into other attendees at the booth who are tackling similar projects or challenges. Ignite is about community as much as content, so come by and spark up a conversation. Meet you there? Ignite is our largest event of the year. We love sharing what we’ve been working on and, most of all, hearing from you, the community. So, on behalf of the Azure for PostgreSQL team, thank you for your interest and support. We can’t wait to show you what’s new and to help you continue to succeed with Postgres. See you in San Francisco!PostgreSQL and the Power of Community
PGConf NYC 2025 is the premier event for the global PostgreSQL community, and Microsoft is proud to be a Platinum sponsor this year. The conference will also feature a keynote from Claire Giordano, Principal PM for PostgreSQL at Microsoft, who will share our vision for Postgres along with lessons from ten PostgreSQL hacker journeys.
