Which distributed PostgreSQL database is tops in terms of transaction processing throughput? It’s a very good query, and Microsoft tried to seek out solutions when it commissioned GigaOM to benchmark its Azure Cosmos DB for PostgreSQL providing in opposition to contenders from Cockroach and Yugabyte.

PostgreSQL is much from new, however its recognition has skyrocketed in recent times as builders and designers have rediscovered the advantages of the open supply relational database. Lots of the new PostgreSQL workloads have landed on the cloud, the place AWS, Google Cloud, and Microsoft Azure have created their very own PostgreSQL cloud database companies.

Plain vanilla PostgreSQL scales vertically on a single laptop footprint, however engineering teams have sought to develop horizontally scalable variations of the database that may run in a distributed style. CitusData, Cockroach Labs, and Yugabyte every have developed distributed databases which might be wire-compliant with PostgreSQL. The cloud giants have additionally adopted swimsuit, with Google delivering a PostgreSQL interface for its Spanner database service. AWS has additionally been hinting at a globally scalable model of Aurora, its PostgreSQL-compatible database, though nothing has come to market but.

Microsoft Azure’s entry into this horserace is Azure CosmosDB for PostgreSQL, which makes use of Citus below the covers to attain horizontal scalability.

As a way to drum up assist for its product, Microsoft just lately commissioned GigaOM to benchmark its Citus-powered distributed PostgreSQL database in opposition to two comparable managed service choices: CockroachDB Devoted and Yugabyte Managed. The plan initially was to together with the PostgreSQL interface for Spanner within the check, however the providing “didn’t present the Postgres compatibility required to run the benchmark,” GigaOM mentioned in its April 18, 2023 report.

Supply: GigaOM benchmark

The benchmark checks, which had been based mostly on GigaOM’s derivation of the business commonplace TPC-C benchmark, sought to gauge how the three relational databases carried out below load. GigaOM wished to make use of the HammerDB software to create the workload for all three databases. Nevertheless, CockroachDB wasn’t appropriate, so it makes use of datasets utilized by Cockroach for its TPC-C testing as a substitute.

The benchmark simulated the applying workload for a real-world firm that strikes shopper product items and operates bodily warehouses (versus information warehouses–that is OLTP nation, not OLAP). On the 1,000 warehouse stage, the databases are requested to deal with SQL queries concerning 30 million clients, 100 million gadgets, 30 million orders, and 300 million order line gadgets. Checks had been additionally carried out on the 10,000 and 20,000 warehouse ranges.

GigaOM says it did the very best it may to measurement the cloud environments for these checks. The Cosmos DB for PostgreSQL ran in Microsoft Azure (clearly) whereas CockroachDB Devoted and YugabyteDB Managed ran in AWS. Each CockroachDB and YugabyteDB got 14 employee nodes, every with 16 digital CPUs, 64 GB of RAM, and a pair of,048 GB of storage (stable state, presumably). No info was supplied for the coordinator node for these databases.

Cosmos DB for PostgreSQL was given 12 employee nodes, every with 16 vCores, 128 GB of RAM (twice the quantity of RAM as its opponents), and a pair of,048 GB of storage. The coordinator node was a single 32 vCore occasion with 128 GB of RAM and 512 GB of storage. GigaOM tweaked the default Cosmos DB for PostgreSQL setting for employee reminiscence to 16MB and set “pg_stat_statements.monitor” to “none,” it says in its report. “These settings will not be configurable for the fully-managed variations of YugabyteDB and CockroachDB,” it says.

Supply: GigaOM benchmark

The benchmark outcomes report exhibits Azure CosmosDB for PostgreSQL profitable all the classes which might be talked about within the report. (In case you’re new to database benchmarks, that may shock you.)

For instance, within the “greatest new orders per minute” class, Azure CosmosDB for PostgreSQL trounced its opponents, with a 1.05 million NOPM ranking in comparison with 178,000 for CockroachDB and 136,000 for YugabyteDB. (NOPM is taken into account the equal of transactions per minute,” an ordinary TPC-C metric.) These greatest NOPM figures had been generated on the 20,000 warehouse stage. Nevertheless, Azure CosmosDB for PostgreSQL’s greatest NOPM determine was from the 1,000 warehouse check (GigaOM ran the ten,000 and 20,000 warehouse checks after discovering the server utilization had been solely round 20% for the 1,000 warehouse check.)

“Azure Cosmos DB for PostgreSQL achieved over 5 occasions extra throughput than the CockroachDB Devoted and YugabyteDB Managed configurations…” GigaOM says in its report. “On at the present time, for this explicit workload, with these particular configurations, Azure Cosmos DB for PostgreSQL had greater throughput than CockroachDB and YugabyteDB.”

By way of the whole price of the configuration, Azure CosmosDB for PostgreSQL (not surprisingly) comes out the winner, with a $34.91 per hour price to run the infrastructure on Azure versus $62.17 per hour to run the CockroachDB setup on AWS and $57.63 per hour to run the YugabyteDB setup on AWS. By way of month-to-month prices, the Microsoft choice was significantly lower than its two opponents, the report exhibits.

Marco Slot, a principal software program engineer at Microsoft, supplied some caveats and shade to the GigaOM benchmark in a June 21 weblog put up.

“Benchmarking databases, particularly at massive scale, is difficult–and comparative benchmarks are even tougher,” he wrote.

Slot says one of many cause why Azure Cosmos DB for PostgreSQL is so quick is because of an idea in Citus referred to as “co-location.”

“To distribute tables, Citus requires customers to specify a distribution column (also referred to as the shard key), and a number of tables could be distributed alongside a standard column,” Slot writes. “That means, joins, overseas keys, and different relational operations on that column could be totally pushed down.”

Additionally benefiting Staff Microsoft is the potential in Citus to “scope” transactions and saved procedures to at least one particular distribution column worth, which permits them to be “totally delegated to one of many nodes of the cluster,” thereby boosting scalability, Slot says.

In the long run, it’s about tradeoffs, Slot says.

“The choice to increase Postgres (as Citus did), fork Postgres (as Yugabyte did), or reimplement Postgres (as CockroachDB did) can also be a trade-off with main implications on the top consumer expertise, some good, some dangerous,” he says. “CockroachDB and Yugabyte make completely different trade-offs and don’t require a distribution column. Engineers like speaking in regards to the CAP theorem, although in actuality there are various hundreds of difficult trade-offs between response time, concurrency, fault-tolerance, performance, consistency, sturdiness, and different elements.”

However each software is completely different, after all, and every consumer ought to determine for themselves which tradeoffs they’re prepared to make.

Associated Gadgets:

Google Cloud Offers Spanner a PostgreSQL Interface

Distributed PostgreSQL Settling Into Cloud

Reworking PostgreSQL right into a Distributed, Scale-Out Database