CareerByteCode
ByteBlogby CareerByteCode

AlloyDB for PostgreSQL: Speed Test Against Cloud SQL

What you build: AlloyDB and Cloud SQL at the same small size, the same data and two tests: small transactions and a report query. Every step tested.

AlloyDB for PostgreSQL: Speed Test Against Cloud SQL

✍️ Translate this blog into Hindi, Tamil or Telugu

Google Cloud has two managed PostgreSQL services: Cloud SQL for PostgreSQL, the classic one, and AlloyDB for PostgreSQL, built for higher performance and mixed workloads. Which is faster? Marketing pages answer with big multipliers measured on big machines. In this lab we create both at the same small size (2 vCPUs), load the same data, and run the same two tests from the same client: many small transactions, and one report query. The result is more interesting than "X is faster".

Who it is for: developers and DBAs choosing a managed PostgreSQL on Google Cloud. Time: about 1 hour. Cost: both databases at the smallest size for about 25 minutes, covered by the free trial credit. Delete them right after: databases cost by the hour.

Every command below was run for real by the CareerByteCode Team, and every output is the real result we got. The tests ran from a small VM in the same VPC and zone as both databases.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

Part A: the two services in plain English

The test setup
One client VM, one AlloyDB primary and one Cloud SQL instance, 2 vCPUs each, private IPs in the same VPC
  • Cloud SQL for PostgreSQL: standard PostgreSQL that Google runs for you: backups, patches, high availability. Sizes from tiny to large.
  • AlloyDB for PostgreSQL: PostgreSQL-compatible, with Google's own storage layer, a bigger memory cache and a columnar engine for analytics. Sold as a cluster with a primary instance (and optional read pools).
  • Private service access: both get private IPs in your VPC through a reserved range and a peering with Google's network.
  • pgbench: PostgreSQL's own benchmark tool. -i creates test tables (scale 20 = 2 million account rows); a normal run fires small read-and-write transactions from several clients and reports TPS (transactions per second) and average latency.
  • Real-life example: a city car and an SUV of the same engine size. On short trips in town one can be quicker; on a long load the other pulls ahead. You only know by driving both on the same road.

Before you start

A Google Cloud project with billing linked (the free trial works) and a small VM in the default network with gcloud and the PostgreSQL client (we use the same lab VM as in our other database labs). Cloud Shell is outside your VPC and cannot reach private database IPs.

Part B: the lab

Step 1: private service access

# Both databases get PRIVATE IPs in our VPC. Google runs them in its own network, so we reserve a range and peer once
gcloud services enable alloydb.googleapis.com sqladmin.googleapis.com servicenetworking.googleapis.com > /dev/null && echo "AlloyDB, Cloud SQL and Service Networking APIs on"
gcloud compute addresses create lab-psa-range --global --purpose=VPC_PEERING --prefix-length=20 --network=default 2>&1 | tail -1
gcloud services vpc-peerings connect --service=servicenetworking.googleapis.com --ranges=lab-psa-range --network=default 2>&1 | tail -1
gcloud services vpc-peerings list --network=default --format="value(peering,reservedPeeringRanges.list())"
Operation "operations/acat.p2-PROJECT_NUMBER-4935c75f-78fa-498d-9715-9b73fd0ca8ef" finished successfully.
AlloyDB, Cloud SQL and Service Networking APIs on
Created [https://www.googleapis.com/compute/v1/projects/your-project-id/global/addresses/lab-psa-range].
Operation "operations/pssn.p24-PROJECT_NUMBER-a9010da1-8184-4913-ae99-9f2ecef1024b" finished successfully.
servicenetworking-googleapis-com	lab-psa-range

Step 2: create both, the same size

Why: a fair test needs the same vCPUs. AlloyDB: a cluster plus a 2 vCPU zonal primary (no standby). Cloud SQL: Enterprise edition, 2 vCPUs and 8 GB memory, private IP only. Both are created at the same time (in the background), which takes about 5 minutes. The password is made in the lab and never printed:

# Create both, the same size: 2 vCPUs. AlloyDB = a cluster + a primary instance. Cloud SQL = one instance. (10 to 15 minutes)
openssl rand -hex 16 > .dbpass   # a lab-only password, never printed
gcloud alloydb clusters create lab-alloy --region=us-east1 --network=default --password="$(cat .dbpass)" 2>&1 | tail -1
gcloud alloydb instances create lab-alloy-1 --cluster=lab-alloy --region=us-east1 --instance-type=PRIMARY --cpu-count=2 --availability-type=ZONAL 2>&1 | tail -1 &
gcloud sql instances create lab-sql --database-version=POSTGRES_16 --edition=ENTERPRISE --cpu=2 --memory=8GB --region=us-east1 --no-assign-ip --network=default --root-password="$(cat .dbpass)" 2>&1 | tail -1 &
wait
gcloud alloydb instances list --format="table(name.basename(),state,machineConfig.cpuCount)"
gcloud sql instances list --format="table(name,databaseVersion,settings.tier,state)"
..........done.
lab-sql  POSTGRES_16       us-east1-c  db-custom-2-8192  -                10.87.192.3      RUNNABLE
..........done.
NAME         STATUS  CPU_COUNT
lab-alloy-1  READY   2
NAME     DATABASE_VERSION  TIER              STATUS
lab-sql  POSTGRES_16       db-custom-2-8192  RUNNABLE

Step 3: load the same data into both

Why: pgbench -i -s 20 creates the standard pgbench tables with 2 million account rows in each database:

# Load the same test data into both: pgbench creates 4 tables (scale 20 = 2 million account rows)
sudo apt-get install -y -qq postgresql-contrib > /dev/null 2>&1; pgbench --version
A=$(gcloud alloydb instances describe lab-alloy-1 --cluster=lab-alloy --region=us-east1 --format="value(ipAddress)"); S=$(gcloud sql instances describe lab-sql --format="value(ipAddresses[0].ipAddress)")
echo "$A" > .alloy-ip; echo "$S" > .sql-ip
for h in $A $S; do PGPASSWORD=$(cat .dbpass) pgbench -h $h -U postgres -i -s 20 -q postgres 2>&1 | grep -E "done in" | sed "s/^/$( [ $h = $A ] && echo AlloyDB || echo 'Cloud SQL'): /"; done
pgbench (PostgreSQL) 15.20 (Debian 15.20-0+deb12u1)
AlloyDB: done in 4.90 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 3.68 s, vacuum 0.14 s, primary keys 1.05 s).
Cloud SQL: done in 4.65 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 2.76 s, vacuum 0.35 s, primary keys 1.52 s).
Create and load
AlloyDB READY with 2 vCPUs, Cloud SQL RUNNABLE with 2 vCPUs, 2 million rows loaded into each

About 5 seconds for each: loading speed is the same in practice.

Step 4: test 1, many small transactions

Why: this is what most apps do: short reads and writes from many connections. 8 clients for 60 seconds on each database:

# Test 1, many small transactions (like an app): 8 clients for 60 seconds each, same client VM, same data
for d in alloy sql; do h=$(cat .$d-ip); n=$([ $d = alloy ] && echo AlloyDB || echo "Cloud SQL")
  PGPASSWORD=$(cat .dbpass) pgbench -h $h -U postgres -c 8 -j 2 -T 60 postgres 2>&1 | grep -E "^(tps|latency average)|rror|invalid" | sed "s/ (without initial connection time)//; s/^/$n: /"; done
AlloyDB: latency average = 7.382 ms
AlloyDB: tps = 1083.701225
Cloud SQL: latency average = 5.636 ms
Cloud SQL: tps = 1419.460428

At this small size, Cloud SQL did more transactions per second (1,419 against 1,084) with lower average latency (5.6 ms against 7.4 ms). For short write transactions, the extra layers that help AlloyDB at scale do not pay off on 2 vCPUs.

Step 5: test 2, one report query

Why: reports and dashboards read many rows at once. We group 2 million rows by branch, three times on each database (the first run reads from disk, the next ones from memory):

# Test 2, one big report query (like analytics): group 2 million rows. Run 3 times, keep each time
Q="SELECT bid, count(*), sum(abalance), avg(abalance) FROM pgbench_accounts GROUP BY bid ORDER BY bid LIMIT 3;"
for d in alloy sql; do h=$(cat .$d-ip); n=$([ $d = alloy ] && echo AlloyDB || echo "Cloud SQL")
  for i in 1 2 3; do echo "$n run $i: $(PGPASSWORD=$(cat .dbpass) psql -h $h -U postgres -d postgres -qAt -c '\timing on' -c "$Q" | grep -oE 'Time: [0-9.]+ ms')"; done; done
AlloyDB run 1: Time: 597.644 ms
AlloyDB run 2: Time: 297.000 ms
AlloyDB run 3: Time: 290.849 ms
Cloud SQL run 1: Time: 2305.922 ms
Cloud SQL run 2: Time: 473.168 ms
Cloud SQL run 3: Time: 458.493 ms

Here AlloyDB was faster: about 290 ms against about 460 ms once the data was in memory, and much faster on the first, cold run (0.6 seconds against 2.3 seconds). We did not switch on AlloyDB's columnar engine, which is built to speed up exactly this kind of query further.

Two tests, two winners
Small transactions: Cloud SQL 1,419 TPS vs AlloyDB 1,084; report query: AlloyDB about 290 ms vs Cloud SQL about 460 ms

Break it: connect from outside the VPC

Both databases have private IPs only. From our PC (outside the VPC), a connection to AlloyDB's port 5432 simply times out:

# Break it: connect from a machine OUTSIDE the VPC (our PC). Private IPs are not reachable from the internet
A=$(gcloud alloydb instances describe lab-alloy-1 --cluster=lab-alloy --region=us-east1 --format='value(ipAddress)')
timeout 10 bash -c "echo > /dev/tcp/$A/5432" 2>/dev/null; echo "connect to port 5432 from the PC: exit code $? (124 = timed out, no route from outside)"
connect to port 5432 from the PC: exit code 124 (124 = timed out, no route from outside)
🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →

Fix it: connect from inside

Apps on VMs, GKE, or Cloud Run with a VPC connector reach the private IP directly:

# Fix it: connect from inside the VPC (the lab-shell VM), the way apps on VMs, GKE or Cloud Run with a VPC connector do
PGPASSWORD=$(cat .dbpass) psql -h $(cat .alloy-ip) -U postgres -d postgres -qAt -c "select 'connected to ' || split_part(version(), ' ', 1) || ' ' || split_part(version(), ' ', 2) || ', ' || pg_size_pretty(pg_database_size('postgres'))"
connected to PostgreSQL 18.4, 326 MB
Private network
From the PC: timed out; from the VM: connected; then everything deleted

The connection works from the VM in the same network (the AlloyDB instance reports its PostgreSQL version and the database size).

What the numbers mean (and do not mean)

Cloud SQL (2 vCPU)AlloyDB (2 vCPU)
Load 2 million rows4.7 s4.9 s
Small transactions, 8 clients1,419 TPS, 5.6 ms1,084 TPS, 7.4 ms
Report query, cold2,306 ms598 ms
Report query, warmabout 460 msabout 290 ms
  • This is one short run at the smallest size. Results change with machine size, data size, connection count and settings. AlloyDB's published advantages are measured on bigger machines.
  • The two also ran different PostgreSQL major versions (we asked Cloud SQL for 16; AlloyDB used its default), which can affect results.
  • The lesson for your interview or your design review: test your own workload. A simple pgbench run and your three slowest queries tell you more than any vendor chart.

Clean up (delete everything)

Why: both databases cost by the hour. We delete both at the same time, then the peering and the reserved range (the peering can only go once both databases are fully deleted, which took about 20 minutes in our run):

# Clean up right away (databases cost by the hour): AlloyDB cluster (with its instance), Cloud SQL, the peering range
gcloud alloydb clusters delete lab-alloy --region=us-east1 --force --quiet 2>&1 | tail -1 &
gcloud sql instances delete lab-sql --quiet 2>&1 | tail -1 &
wait; rm -f .dbpass .alloy-ip .sql-ip
for i in $(seq 1 20); do gcloud services vpc-peerings delete --service=servicenetworking.googleapis.com --network=default --quiet > /dev/null 2>&1 && break; sleep 30; done
gcloud compute addresses delete lab-psa-range --global --quiet 2>&1 | tail -1
echo "left: $(gcloud alloydb clusters list --format='value(name)' | wc -l) AlloyDB clusters, $(gcloud sql instances list --format='value(name)' 2>/dev/null | wc -l) Cloud SQL instances, $(gcloud compute addresses list --filter='name=lab-psa-range' --format='value(name)' | wc -l) reserved ranges"
Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/your-project-id/instances/lab-sql].
..........done.
Deleted [https://www.googleapis.com/compute/v1/projects/your-project-id/global/addresses/lab-psa-range].
WARNING: The following filter keys were not present in any resource : name
left: 0 AlloyDB clusters, 0 Cloud SQL instances, 0 reserved ranges

FAQ

So which one should I choose? Cloud SQL for typical apps, small to medium sizes and the lowest entry price (it also has shared-core tiers for tiny projects). AlloyDB when you need high throughput at larger sizes, mixed transactions and analytics, read pools, or its AI features (vector search, in-database model calls).

Why did AlloyDB lose test 1? At 2 vCPUs, its storage and caching design adds work for short write transactions. Its strengths show with more vCPUs, more memory for its cache, and analytical queries.

What is the AlloyDB columnar engine? An in-memory column store that speeds up analytical queries without changing them. It is switched on with database flags; we did not test it here.

Can I connect from my laptop? Not to a private IP directly. Use the AlloyDB Auth Proxy or Cloud SQL Auth Proxy through a machine in the VPC, or a VPN.

Why are the cleanup times so long? Deleting the databases is quick to start, but the network peering can only be removed after Google has fully removed them.

Troubleshooting

You seeWhyFix
A connection times outYou are outside the VPCConnect from a VM in the same network, or use the Auth Proxy
Cannot modify allocated ranges in CreateConnectionA peering exists with other rangesUse the existing range, or update the peering with all ranges
pgbench prints nothingAn option error hidden by 2>/dev/nullKeep errors visible (2>&1), as in Step 4
Deleting the peering failsThe databases are still being deletedWait and retry, as the clean-up does
Results change between runsCaches, background work, short testsRun longer, repeat 3 times, compare medians

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →Cloud SQL for PostgreSQL: Create, Connect and Back Up⏱ 12 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →