How Powerful Is PostgreSQL?

By Ruohang Feng（[@Vonng](https://github.com/Vonng）| Wechat Column | 2022-08-22

Tags:

Last time, we analyzed StackOverflow survey data to explain why PostgreSQL is the most successful database.

This time, let’s rely on performance metrics to see just how powerful “the most successful” PostgreSQL really is. We want everyone to walk away feeling, in the words of a certain meme, “I know the numbers.”

TL;DR

If you’re curious about any of the following questions, this post should be helpful:

How fast is PostgreSQL, exactly?
Point-read queries (QPS) can exceed 600k, and in extreme conditions can even hit 2 million. For mixed read-write TPS (4 writes + 1 read in each transaction), you can reach 70k+ or even as high as 140k.
How does PostgreSQL compare with MySQL at the performance limit?
Under extreme tuning, PostgreSQL’s point-read throughput beats MySQL by a noticeable margin. In other metrics, they’re roughly on par.
How does PostgreSQL compare with other databases?
Under the same hardware specs, “distributed/NewSQL” databases often lag far behind classic databases in performance.
What about PostgreSQL versus other analytical databases in TPC-H?
As a native hybrid transaction/analysis (HTAP) database, PostgreSQL’s analytical performance is quite impressive.
Are cloud databases or cloud servers actually cost-effective?
It turns out you could purchase a c5d.metal server outright (and host it yourself for 5 years) for about the cost of renting it on the cloud for 1 year. Meanwhile, a similarly provisioned cloud database of the same spec could cost about 20 times as much as a raw EC2 box in 1 year.

All detailed test steps and raw data are on: github.com/Vonng/pgtpc

PGBENCH

Technology evolves at breakneck speed. Although benchmark articles are everywhere, it’s tough to find reliable performance data that reflect today’s newest hardware and software. Here, we used pgbench to test the latest PostgreSQL 14.5 on two types of cutting-edge hardware specs:

We ran four different hardware setups: two Apple laptops and three AWS EC2 instances, specifically:

A 2018 15-inch top-spec MacBook Pro using an Intel 6-core i9
A 2021 16-inch MacBook Pro powered by an M1 Max chip (10-core)
AWS z1d.2xlarge (8C / 64G)
AWS c5d.metal (96C / 192G)

All are readily available commercial hardware configurations.

pgbench is a built-in benchmarking tool for PostgreSQL, based on a TPC-B–like workload, widely used to evaluate PostgreSQL (and its derivatives/compatibles). We focused on two test modes:

Read-Only (RO)
A single SQL statement that randomly selects and returns one row from a table of 100 million rows.
Read-Write (RW)
Five SQL statements per transaction: 1 read, 1 insert, and 3 updates.

We used s=1000 for the dataset scale, then gradually increased client connections. At peak throughput, we tested for 3-5 minutes and recorded stable averages. Results are as follows:

No	Spec	Config	CPU	Freq	S	RO	RW
1	Apple MBP Intel 2018	Normal	6	2.9GHz - 4.8GHz	1000	113,870	15,141
2	AWS z1d.2xlarge	Normal	8	4GHz	1000	162,315	24,808
3	Apple MBP M1 Max 2021	Normal	10	600MHz - 3.22GHz	1000	240,841	31,903
4	AWS c5d.metal	Normal	96	3.6GHz	1000	625,849	71,624
5	AWS c5d.metal	Extreme	96	3.6GHz	5000	1,998,580	137,127

Read-Write

Chart: Max read-write TPS on each hardware

Chart: TPS curves for read-write transactions

Read-Only

Chart: Max point-read QPS on each hardware

Chart: Point-read QPS vs. concurrency

These results are jaw-dropping. On a 10-core Apple M1 Max laptop, PostgreSQL hits ~32k TPS (read-write) and ~240k QPS (point lookups). On an AWS c5d.metal production-grade physical server, it goes up to ~72k TPS and ~630k QPS. With extreme config tuning, we were able to push it to 137k TPS and 2 million QPS on a single machine. Yes, a single server can do that.

By way of rough scale reference: Tantan (a major dating/social networking app in China) has a global TPS of ~400k across its entire PostgreSQL fleet. These new laptops or a few top-spec servers (costing around 100k RMB) could potentially support a large-scale app’s DB load. That’s insane compared to just a few years ago.

A Note on Costs

Take AWS c5d.metal in Ningxia region as an example. It’s one of the best overall compute options, coming with local 3.6TB NVMe SSD storage. There are seven different payment models (prices below in RMB/month or RMB/year):

Payment Model	Monthly	Up-Front	Yearly
On-Demand	31,927	0	383,124
Standard 1-yr, no upfront	12,607	0	151,284
Standard 1-yr, partial	5,401	64,540	129,352
Standard 1-yr, all upfront	0	126,497	126,497
Convertible 3-yr, no upfront	11,349	0	136,188
Convertible 3-yr, partial	4,863	174,257	116,442
Convertible 3-yr, all upfront	0	341,543	113,847

Effectively, annual costs range from about 110k to 150k RMB, or 380k at on-demand retail. Meanwhile, buying a similar server outright and hosting it in a data center for 5 years might cost under 100k total. So yes, the cloud is easily ~5x more expensive if you only compare raw hardware costs. Still, if you consider the elasticity, discount programs, and coupon offsets, an EC2 instance can be “worth it,” especially if you self-manage PostgreSQL on it.

But if you want an RDS for PostgreSQL with roughly the same specs (the closest is db.m5.24xlarge, 96C/384G + 3.6T io1 @80k IOPS), the monthly cost is ~240k RMB, or 2.87 million RMB per year, nearly 20 times more than simply running PostgreSQL on the same c5d.metal instance yourself.

AWS cost calculator: https://calculator.amazonaws.cn/

SYSBENCH

So PostgreSQL alone is impressive—but how does it compare to other databases? pgbench is built for PostgreSQL-based systems. For a broader look, we can turn to sysbench, an open-source, multi-threaded benchmarking tool that can assess transaction performance in any SQL database (commonly used for both MySQL and PostgreSQL). It includes 10 typical scenarios like:

oltp_point_select for point-read performance
oltp_update_index for index update performance
oltp_read_only for transaction mixes of 16 queries
oltp_read_write for a mix of 20 queries in a transaction (read + write)
oltp_write_only for a set of 6 insert/update statements

Because sysbench can test both MySQL and PostgreSQL, it provides a fair basis for comparing their performance. Let’s start with the most popular face-off: the world’s “most popular” open-source RDBMS—MySQL—vs. the world’s “most advanced” open-source RDBMS—PostgreSQL.

Dirty Hack

MySQL doesn’t provide official sysbench results, but there is a third-party benchmark on MySQL.com claiming 1 million QPS for point-reads, 240k for index updates, and about 39k TPS for mixed read-write.

From: https://www.mysql.com/why-mysql/benchmarks/mysql/

That approach is somewhat “unethical” if you will—because reading the linked article reveals that they turned off all major safety features to get these numbers: no binlog, no fsync on commit, no double-write buffer, no checksums, forcing LATIN-1, no monitoring, etc. Great for scoreboard inflation, not so great for real production usage.

But if we’re going down that path, we can similarly do a “Dirty Hack” for PostgreSQL—shut off everything that ensures data safety—and see how high we can push the scoreboard. The result? PostgreSQL point-reads soared past 2.33 million QPS, beating MySQL’s 1M QPS by more than double.

Chart: “Unfair” Benchmark—PG vs. MySQL, everything turned off

PostgreSQL’s “extreme config” point-read test in progress

To be fair, MySQL’s test used a 48C/2.7GHz machine, whereas our PostgreSQL run was on a 96C/3.6GHz box. But because PostgreSQL uses a multi-process model (rather than MySQL’s multi-thread model), we can sample performance at c=48 to approximate performance if we only had 48 cores. That still gives ~1.5M QPS for PG on 48 cores, 43% higher than MySQL’s best number.

We’d love to see MySQL experts produce a benchmark on identical hardware for a more direct comparison.

Chart: Four sysbench metrics from MySQL’s “Dirty Hack,” with c=48 concurrency

In other tests, MySQL also reaches impressive extremes. oltp_read_only and oltp_update_non_index are roughly on par with PG’s c=48 scenario, and MySQL even beats PostgreSQL by a bit in oltp_read_write. Overall, aside from a resounding win for PostgreSQL in point-reads, the two are basically neck and neck in these “unfair” scenarios.

Fair Play

In terms of features, MySQL and PostgreSQL are worlds apart. But at the performance limit, the two are close, with PostgreSQL taking the lead in point-lookups. Now, how about next-generation, distributed, or “NewSQL” databases?

Most “Fair Play” database vendors who show sysbench benchmarks do so in realistic, production-like configurations (unlike MySQL’s “dirty hack”). So let’s compare them with a fully production-configured PostgreSQL on the same c5d.metal machine. Production config obviously reduces PG’s peak throughput by about half, but it’s more appropriate for apples-to-apples comparisons.

We collected official sysbench numbers from a few representative NewSQL database websites (or at least from detailed 3rd-party tests). Not every system published results for all 10 sysbench scenarios, and the hardware/table sizes vary. However, each test environment is around 100 cores with ~160M rows (except OB, YB, or where stated). That should give us enough to see who’s who:

Database	PGSQL.C5D96C	TiDB.108C	OceanBase.96C	PolarX.64C	Cockroach	Yugabyte
oltp_point_select	1,372,654	407,625	401,404		336,000	95,695
oltp_read_only	852,440		279,067	366,863		52,416
oltp_read_write	519,069	124,460	157,859	177,506		9,740
oltp_write_only	495,942		119,307			9,090
oltp_delete	839,153					67,499
oltp_insert	164,351				112,000	6,348
oltp_update_non_index	217,626	62,084				11,496
oltp_update_index	169,714	26,431				4,052
select_random_points	227,623
select_random_ranges	24,632
Machine	c5d.metal	m5.xlarge x3 i3.4xlarge x3 c5.4xlarge x3	ecs.hfg7.8xlarge x3 ecs.hfg7.8xlarge x1	Enterprise	c5d.9xlarge x3	c5.4xlarge x3
Spec	96C / 192G	108C / 510G	96C / 384G	64C / 256G	108C / 216G	48C / 96G
Table	16 x 10M	16 x 10M	30 x 10M	1 x 160M	N/A	10 x 0.1M
CPU	96	108	96	64	108	48
Source	Vonng	TiDB 6.1	OceanBase	PolarDB	Cockroach	YugaByte

Chart: sysbench results (QPS, higher is better) for up to 10 tests

Chart: Normalized performance per core across different databases

Shockingly, the new wave of distributed NewSQL databases lags across the board. On similar hardware, performance can be one order of magnitude behind classic relational databases. The best among them is actually still PolarDB, which uses a classic primary-standby design. This begs the question: Should we re-examine the distributed DB / NewSQL hype?

In theory, distributed databases always trade off complexity (and sometimes stability or functionality) for unlimited scale. But we’re seeing that they often give up a lot of raw performance too. As Donald Knuth famously said: “Premature optimization is the root of all evil.” Opting for a distributed solution for data volumes you don’t actually need (like Google-scale, multi-trillion-row problems) could also be a form of premature optimization. Many real-world workloads never come close to that scale.

TPC-H Analytical Performance

Perhaps the distributed DB argument is: Sure, we’re behind in TP, but we’ll shine in AP. Indeed, many distributed databases pitch an “HTAP” story for big data. So, let’s look at the TPC-H benchmark, used to measure analytical database performance.

TPC-H simulates a data warehouse with 8 tables and 22 complex analytical queries. The performance metric is typically the time to run all 22 queries at a given data scale (often SF=100, ~100GB). We tested TPC-H with scale factors 1, 10, 50, and 100 on a local laptop and a small AWS instance. Below is the total runtime of the 22 queries:

Scale Factor	Time (s)	CPU	Environment	Comment
1	8	10	10C / 64G	Apple M1 Max
10	56	10	10C / 64G	Apple M1 Max
50	1,327	10	10C / 64G	Apple M1 Max
100	4,835	10	10C / 64G	Apple M1 Max
1	13.5	8	8C / 64G	z1d.2xlarge
10	133	8	8C / 64G	z1d.2xlarge

For a broader view, we compared these results to other databases’ TPC-H data found on official or semi-official tests. Note:

Some use a different SF (not always 100).
Hardware specs differ.
We’re not always quoting from official sources.

So, it’s only a rough guide:

Database	Time	S	CPU	QPH	Environment	Source
PostgreSQL	8	1	10	45.0	10C / 64G M1 Max	Vonng
PostgreSQL	56	10	10	64.3	10C / 64G M1 Max	Vonng
PostgreSQL	1,327	50	10	13.6	10C / 64G M1 Max	Vonng
PostgreSQL	4,835	100	10	7.4	10C / 64G M1 Max	Vonng
PostgreSQL	13.51	1	8	33.3	8C / 64G z1d.2xlarge	Vonng
PostgreSQL	133.35	10	8	33.7	8C / 64G z1d.2xlarge	Vonng
TiDB	190	100	120	15.8	120C / 570G	TiDB
Spark	388	100	120	7.7	120C / 570G	TiDB
Greenplum	436	100	288	2.9	120C / 570G	TiDB
DeepGreen	148	200	256	19.0	288C / 1152G	Digoal
MatrixDB	2,306	1000	256	6.1	256C / 1024G	MXDB
Hive	59,599	1000	256	0.2	256C / 1024G	MXDB
StoneDB	3,388	100	64	1.7	64C / 128G	StoneDB
ClickHouse	11,537	100	64	0.5	64C / 128G	StoneDB
OceanBase	189	100	96	19.8	96C / 384G	OceanBase
PolarDB	387	50	32	14.5	32C / 128G	Aliyun
PolarDB	755	50	16	14.9	16C / 64G	Aliyun