AlloyDB

AlloyDB combines the best of Google with one of the most popular open-source database engines, PostgreSQL, for superior performance, scale, and availability

Setup

Networking

AlloyDB supports network connectivity through private, internal IP addresses configured for private services access. To connect to AlloyDB from Hightouch, configure an SSH connection tunnel to go through a bastion host in your VPC. Read about more options here: https://cloud.google.com/alloydb/docs/connection-overview

SSH tunneling

Hightouch can connect directly to AlloyDB over the public internet or via an SSH tunnel. Since data is encrypted in transit via TLS, a direct connection is suitable for most use cases. You may need to set up a tunnel if your AlloyDB instance is on a private network or virtual private cloud (VPC).

Hightouch supports both standard and reverse SSH tunnels. To learn more about SSH tunneling, refer to Hightouch's tunneling documentation.

Required permissions

The user credentials must have one of the following IAM roles in the Cloud project you are using. If you don't have any of these roles, contact your Organization Administrator to request access.

• roles/alloydb.admin (the AlloyDB Admin predefined IAM role)
• roles/owner (the Owner basic IAM role)
• roles/editor (the Editor basic IAM role)

To create private services access configuration, you also must have these IAM permissions:

• compute.networks.list
• compute.addresses.create
• compute.addresses.list
• servicenetworking.services.addPeering

To ensure your credentials are correct, click Test connection. This confirms if Hightouch is able to properly connect to your database by running a simple SELECT query.

Syncing

Hightouch supports Upsert mode, with the option to delete removed rows, and Insert mode using the COPY FROM statement.

Record matching

Hightouch requires a unique identifier in the table you are syncing to in order to add, remove, and update rows. The main identifier column in your sync configuration must match on a UNIQUE or PRIMARY KEY column within your database.

You should see those columns as available options under the records matching section. If there are no fields in the dropdown, you need to add a unique column type to your table.

Column types

Hightouch works out of the box with all standard column types, including:

• BIGINT
• INT
• TEXT
• VARCHAR
• TIMESTAMPTZ
• BOOLEAN
• DECIMAL

If you see type errors, it may be because your SQL query is producing the wrong format.

Batch size

You can tune the number of rows that Hightouch upserts or inserts per query based on your needs and database threshold. The default is 1k rows per batch.

If you want to improve the sync's speed performance, you can increase the batch size to a higher number like 10k. However, keep in mind that AlloyDB will fail the entire batch of rows if it detects any erroneous row. If you suspect that you will have many bad rows, you should not have a high batch size. Also make sure you account for your database's capacity when increasing the batch size to avoid any locks.

Insert mode

Hightouch supports insert mode through the COPY FROM STDIN AlloyDB statement. This will load the queried rows into your table. However, AlloyDB will throw an error and reject the entire batch if any row already exists or the row contains a primary key or unique value that already exists.

The COPY FROM statement should be faster than the regular statement we use for Upsert mode so we recommend selecting Insert mode if you are only inserting rows and want to sync your data faster.