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.

• Deploy GraphQL Engine on Hasura Cloud
• Create a AlloyDB Cluster
• Connect securely using AlloyDB auth proxy
  • Create GCE Instance
  • Run auth proxy
  • Add Firewall Rule
• Add AlloyDB to Hasura Cloud Project
• Query performance

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:

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.

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.

1. Head to VM Instances page
2. Select the project containing the AlloyDB instance you want to connect to.
3. 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.

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.

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 to ON. 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.