Exploring faster reads with Google AlloyDB PostgreSQL and Hasura GraphQL API
Google announced the launch of AlloyDB, a PostgreSQL compatible SQL database service in their I/O 2022 event. This is comparable to AWS Aurora PostgreSQL but Google claims better performance; 2x faster than Aurora, 4x faster than standard Postgres and even 100x faster for analytical workloads.
Update: On December 15, 2022, Google announced the general availability of AlloyDB and also announced the AlloyDB Partner Ecosystem. We are excited to announce that Hasura is a launch partner in this ecosystem.
Hasura should be able to connect to a fully compatible PostgreSQL database. In this post, we will explore connecting to AlloyDB step by step and finally make queries to see performance impact in comparison to standard Postgres.
Table of Contents
- Deploy GraphQL Engine on Hasura Cloud
- Create a AlloyDB Cluster
- Connect securely using AlloyDB auth proxy
- Add AlloyDB to Hasura Cloud Project
- Query performance
Deploy Hasura
The fastest and easiest way to try Hasura out is via Hasura Cloud. Click on the following button to deploy GraphQL engine on Hasura Cloud:
Create a AlloyDB Cluster
Head to https://cloud.google.com/alloydb to create a new cluster.
- Head to your Cloud Console AlloyDB page.
- Create a cluster
- Enable the required APIs in the next step
- Select the cluster type
- Configure your cluster with cluster ID, postgres password, region of choice, network path and click on
Continue
- Configure your primary instance with an instance ID and machine type.
- Click on
Create Cluster
.
The cluster creation takes sometime. Meanwhile, let us setup the Auth proxy so that we can securely connect to AlloyDB from an authorized instance.
Connect securely using AlloyDB auth proxy
The AlloyDB Auth proxy gives IAM based connection authorization with a secure and encrypted 256-bit AES cipher between your client and an AlloyDB instance.
The idea is to run the Auth proxy somewhere authorized to connect to AlloyDB instance (say a GCE instance with IAM / service account configured) and expose the authorized instance to the outside world (like Hasura Cloud) to be able to establish a connection.
Create GCE Instance
Create a Compute Engine VM that can connect to AlloyDB instances using private services access.
- Head to VM Instances page
- Select the project containing the AlloyDB instance you want to connect to.
- Click on Create instance, enter the name for the instance and set the following:
- Access scopes (located under Identity and API access): set to Allow full access to all Cloud APIs.
- Network interfaces: set to the VPC network configured for private services access to AlloyDB:
a. Expand Networking, disks, security, management, sole-tenancy.
b. Expand Networking.
c. If Network interfaces is not set to the VPC network configured for private services access, expand it and then set Network to the VPC network.
Tip: Select the region of GCE instance closer or same as Hasura Cloud project
Get IP Address of the AlloyDB Instance
Head to the cluster listing page for AlloyDB and get the private IP address of the instance. Now this will be used in the next step to run the auth proxy connecting to the AlloyDB postgres instance.
Run Auth Proxy
- Download the Auth proxy from here
- Make the client executable
chmod +x alloydb-auth-proxy
Since client host is a Compute Engine instance, you can use the Compute Engine default service account to authorize connections.
Run the following command to start the Auth proxy
./alloydb-auth-proxy "projects/<project-id>/locations/<region>/clusters/<alloydb-cluster-id>/instances/<alloydb-instance-id>" --address "0.0.0.0"
This would start the Auth proxy client and expose it to the public. Do note that this setup is a ephermeral one. You should run the auth proxy in a permanent mode (for example via docker detached mode).
Add Firewall Rule
Once the Auth proxy is running, we should add a firewall rule that allows connection from Hasura Cloud project to this AlloyDB instance.
Copy the Hasura Cloud IP for your project from the Hasura Cloud dashboard.
Create a new firewall rule for the network interface attached to the AlloyDB instance and allow tcp:port 5432 and in the source IPv4 range, specify the Hasura Cloud IP. For example: 154:176:149:52/32
.
Add AlloyDB to Hasura Cloud project
All the configuration to connect an AlloyDB postgres to Hasura cloud project is done. Let us form the connection string now.
postgresql://postgres:<postgres-password>@<ip-address-of-gce-instance>:5432/postgres
- Replace the postgres password
- Replace the IP address by copying the external IP of the GCE instance running the Auth proxy to AlloyDB.
Head to Hasura Cloud project details page and navigate to Env vars
.
Add a new ENV var called PG_DATABASE_URL
and give the above postgres connection string value.
Go back to the Hasura Console of the project and add this ENV var to the new data source.
This completes the hard part of the whole setup. Let us look at the query performance impact.
Query Performance
Comparing same query with Google Cloud SQL, AlloyDB and Aurora Postgres, all in the same region connected to a Hasura Cloud instance.
Notes on configuration
- AlloyDB - 8vCPU, 64GB
- Cloud SQL - 8vCPU, 64GB
- Postgres version: 14.2 (AlloyDB says Postgres 14 compatible).
- Hasura Cloud version - v2.8.4-cloud.1
- Region - us-central
Running pgbench to load sample data
We need to insert some large amounts of data into both the standard cloud SQL and AlloyDB instance. Making use of pgbench
, we inserted a million rows.
pgbench -h <postgres-host> -p 5432 -U postgres postgres -i -s 10
Fine tuning AlloyDB
- We need to enabe the AlloyDB Columnar Engine (CE). First, we need to adjust the instance so that the new
google_columnar_engine.enabled
flag is set toON
. This would restart the instance, so please be patient. - Next up, create the columnar engine extension
CREATE EXTENSION google_columnar_engine;
- Optionally set the
google_columnar_engine.memory_size_in_mb
to a larger value. - Load the table into the column store.
SELECT google_columnar_engine_add('pgbench_accounts');
GraphQL Query via Hasura
Let us track the table pgbench_accounts
to start making the GraphQL query. Finally make the following GraphQL query:
query {
pgbench_accounts {
aid
bid
filler
}
}
This will fetch all the 1M rows from the pgbench_accounts
.
There is a notable difference in query response times and improved execution times on the AlloyDB cluster instance.
We will update detailed benchmark numbers here soon, but roughly we saw a 3x improvement in response times.
Do you have a workload or a use case that runs analytical queries or heavy transactional queries? If so, AlloyDB might be worth a look for the performance gains.