AlloyDB versus PostgreSQL: a performance review

On 12 May 2022, Google launched AlloyDB, a managed relational DBMS compatible with PostgreSQL 14. In other words, almost every command working on PostgreSQL 14 works on AlloyDB too.

According to Andi Gutmans, AlloyDB offers superior performance, availability, and scale for enterprise workloads.

If we look at these DBMSs from the compatibility perspective, most DDL commands work on both PostgreSQL and AlloyDB with the same syntax. Also, commands for granting privileges to PostgreSQL roles, transforming tables into views, and some PostgreSQL extensions including pg_hint_plan and pg_cron are available in AlloyDB.

Here you can find a list of all AlloyDB supported extensions. Yet, some of them, for example, google_db_advisor are only specific to AlloyDB and cannot be used with the “vanilla” Postgres. This particular extension, however, is quite similar to pg_idx_advisor available in PostgreSQL.

Nevertheless, I noticed some differences between AlloyDB and PostgreSQL. I’d suggest you to have a look at top three of them:

• In AlloyDB, there are additional predefined roles with permissions such as roles/alloydb.viewer, roles/alloydb.client and others.
• Both AlloyDB and PostgreSQL support OLAP and OLTP workloads, just like other HTAP database management systems. However, AlloyDB offers a columnar storage engine specifically for OLAP jobs.
• It is possible to set up write pools with AlloyDB without sharding your tables over many nodes. For instance, the same set of tables in the test database is replicated over many computing servers. This difference is debatable as you can also set up Postgres write pools via CitusDB. So even if you want to scale OLAP workloads in PostgreSQL, you need to enable the citus extension.

Performance biases

Generally, I advise our blog readers not to solely rely on running pgbench to honor AlloyDB as the fastest one or vice versa. There are various ways to optimize PostgreSQL over AlloyDB using other third-party tools.

To make my benchmarking environment look like a production one, I allowed other OS utilities and services to share computing resources with AlloyDB and PostgreSQL.

Additionally, I expected initial read-only queries to have higher latencies than subsequent queries as PostgreSQL uses the file system cache/memory cache, while AlloyDB relies on multi-level caches such as memory cache/ultrafast cache.

Although the Google Database team designed AlloyDB to replicate data blocks over multiple zones for fault tolerance, It’s still possible to replicate block storage for PostgreSQL especially in a Kubernetes environment using Longhorn.

Difference between TPS and latency average metrics

Let me pay your attention on latency average and transaction per second (TPS) metrics. Both of them may be important in different contexts.

Transaction per second

TPS (transaction per second) means the number or amount of transactions executed over a duration per test and calculated per second. TPS also contributes to response time.

Latency average

Network latency is the time needed to let some data get to its destination across the network. For a DBMS server like PostgreSQL, latency is usually the time that it takes a request to receive a complete response.

Performance metrics

For this testing, I decided to benchmark both AlloyDB and PostgreSQL 14 using pgbench, a standard benchmarking tool for database management systems compatible with PostgreSQL.

Benchmarking for INSERTs

Below you can find the specifications and results of my benchmarks done on INSERT operations.

AlloyDB server characteristics

vCPU: 4
RAM/Memory: 32GB
Location: europe/west2
Database Size: 800MB

In this section, we benchmark both database systems with pgbench using the command below. Let me explain its legend: the -c flag defines the number of concurrent sessions, the -j flag specifies the number of threads. Finally, the -T flag specifies that our benchmark testing runs for 600 seconds (10 minutes).

pgbench -h 10.44.96.11 -p 5432 -U postgres -c 500 -j 2 -P 60 -T 600 becnhmark_test

AlloyDB performance results

pgbench (14.3 (Debian 14.3-1.pgdg110+1), server 14.2)
starting vacuum…end.
progress: 60.0 s, 2630.5 tps, lat 166.586 ms stddev 156.974
progress: 120.0 s, 3073.5 tps, lat 162.570 ms stddev 164.057
progress: 180.0 s, 2913.8 tps, lat 171.673 ms stddev 164.896
progress: 240.0 s, 2943.4 tps, lat 169.784 ms stddev 172.131
progress: 300.0 s, 3096.1 tps, lat 161.562 ms stddev 171.058
progress: 360.0 s, 3029.1 tps, lat 164.917 ms stddev 154.557
progress: 420.0 s, 3058.1 tps, lat 163.488 ms stddev 151.244
progress: 480.0 s, 2968.1 tps, lat 168.497 ms stddev 166.300
progress: 540.0 s, 3022.8 tps, lat 165.341 ms stddev 161.815


progress: 600.0 s, 2951.8 tps, lat 169.369 ms stddev 172.815
transaction type:
scaling factor: 150
query mode: simple
number of clients: 500
number of threads: 2
duration: 600 s
number of transactions actually processed: 1781729
latency average = 166.378 ms
latency stddev = 163.876 ms
initial connection time = 7266.206 ms
tps = 3003.173404 (without initial connection time)

PostgreSQL performance results

pgbench (14.3 (Debian 14.3-1.pgdg110+1))
starting vacuum...end.
progress: 60.0 s, 2080.7 tps, lat 222.255 ms stddev 373.018
progress: 120.0 s, 1662.8 tps, lat 293.276 ms stddev 480.482
progress: 180.0 s, 1656.0 tps, lat 294.377 ms stddev 519.658
progress: 240.0 s, 2019.1 tps, lat 237.450 ms stddev 405.434
progress: 300.0 s, 2171.7 tps, lat 224.276 ms stddev 392.388
progress: 360.0 s, 1745.2 tps, lat 278.980 ms stddev 498.448
progress: 420.0 s, 753.1 tps, lat 555.726 ms stddev 1899.953
progress: 480.0 s, 153.5 tps, lat 3638.325 ms stddev 5756.107


progress: 540.0 s, 226.0 tps, lat 2086.820 ms stddev 2713.653
progress: 600.0 s, 273.4 tps, lat 1959.130 ms stddev 3167.165
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 500
number of threads: 2
duration: 600 s
number of transactions actually processed: 764994
latency average = 383.694 ms
latency stddev = 1176.790 ms
initial connection time = 2427.792 ms
tps = 1276.377292 (without initial connection time)

Performance analysis

Unlike AlloyDB, our PostgreSQL server processed 764,994 transactions with a throughput of 1,276 transactions per second (tps).

So AlloyDB processed 1,781,729 transactions and achieved the average latency of 166 ms for all concurrent sessions as compared to PostgreSQL with 383 ms average latency, which means that AlloyDB writes faster than PostgreSQL.

Reads without indexes

The following benchmarks are for reads that don’t use indexes.

AlloyDB server characteristics

vCPU: 4
RAM/Memory: 32GB
Location: London 
Database Size: 800MB

AlloyDB performance results

starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 4
duration: 600 s
number of transactions actually processed: 15702800
latency average = 3.822 ms
tps = 26167.429075 (including connections establishing)
tps = 26168.939066 (excluding connections establishing)

Performance analysis

For reads without indexes, PostgreSQL processed 21,258,148 transactions and executed 35,431 transactions per second with the average latency of 2.822 ms. AlloyDB processed 15,702,800 transactions and executed 26,167 transactions per second.

I had to use a high-memory CPU to optimize PostgreSQL to prevent disk access since we have already executed read queries. So PostgreSQL had a lower latency compared to AlloyDB, though the difference is not significant.

50% reads/writes without indexes

This benchmark consists of both reads and writes coming in equal proportion.

AlloyDB server characteristics

vCPU: 8
RAM/Memory: 64GB
Location: London 
Database Size: 800MB

starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 8
duration: 600 s
number of transactions actually processed: 21373
latency average = 2821.828 ms
tps = 35.438027 (including connections establishing)
tps = 35.440935 (excluding connections establishing)
SQL script 1: <builtin: select only>
- weight: 1 (targets 50.0% of total)
- 10628 transactions (49.7% of total, tps = 17.622016)

- latency average = 10.865 ms
- latency stddev = 60.733 ms
SQL script 2: bench.sql
- weight: 1 (targets 50.0% of total)
- 10745 transactions (50.3% of total, tps = 17.816011)
- latency average = 5577.735 ms
- latency stddev = 1968.252 ms

PostgreSQL server characteristics

vCPU: 8
RAM/Memory: 64GB
Location: London 
Database Size: 800MB

pgbench (14.4 (Debian 14.4-1.pgdg110+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 8
duration: 600 s
number of transactions actually processed: 36531
latency average = 1646.819 ms
initial connection time = 56.594 ms
tps = 60.723115 (without initial connection time)
SQL script 1: <builtin: select only>
- weight: 1 (targets 50.0% of total)
- 18169 transactions (49.7% of total, tps = 30.201152)
- latency average = 5.190 ms
- latency stddev = 12.368 ms

SQL script 2: bench.sql
- weight: 1 (targets 50.0% of total)
- 18361 transactions (50.3% of total, tps = 30.520301)
- latency average = 3263.349 ms
- latency stddev = 417.708 ms

Performance analysis

Here I benchmarked PostgreSQL and AlloyDB to check how each database management system performs when processing 50% of reads and writes.

I used the same trick here to give PostgreSQL an advantage over AlloyDB. PostgreSQL achieved an average latency of 1,646 ms whilst AlloyDB’s average latency reached 2,821 ms. A high-memory CPU allowed PostgreSQL to have lower latency than AlloyDB.

Read-only queries with indexes

This benchmark consists of reads that use B-tree indexes.

AlloyDB server characteristics

vCPU: 16
RAM/Memory: 64GB
Location: London 
Database Size: 800MB

Performance results

starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 16
duration: 600 s
number of transactions actually processed: 22435730
latency average = 1.100 ms
tps = 37389.452242 (including connections establishing)
tps = 37394.376013 (excluding connections establishing)

PostgreSQL server characteristics

vCPU: 16
RAM/Memory: 64GB
Location: London 
Database Size: 800MB

Performance results

pgbench (14.4 (Debian 14.4-1.pgdg110+1))
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 8
duration: 600 s
number of transactions actually processed: 45314735
latency average = 2.324 ms
initial connection time = 58.467 ms
tps = 75527.463749 (without initial connection time)

Performance analysis

AlloyDB processed 22,435,730 transactions and completed 37,389 transactions per second with an average latency of 1.100 ms.

On the other hand, PostgreSQL processed 45,314,735 transactions with an average latency of 2.324 ms. Postgres executed 75,527 transactions per second.

In this case, pgbench populated both database systems with B-tree indexes. I decided not to enable Postgres with a high memory CPU and used the standard CPUs provided by Google Cloud. I expected AlloyDB to have lower latency than Postgres because PostgreSQL had to retrieve records from disk whilst AlloyDB had to do so from its “ultrafast cache”.

Conclusion

Despite AlloyDB proving it’s faster than Postgres per the benchmark results from pgbench, it’s possible to tune PostgreSQL to perform well using the high memory CPU offered by Google Cloud.
For your information, no performance tuning took place during any of these benchmarks.

If you need expert help to optimize PostgreSQL or AlloyDB performance, consider our services.

Michael Aboagye