PostgreSQL

Power internal tools, in-app experiences, and more

Supported syncing

Connect to PostgreSQL

Go to the Destinations overview page and click the Add destination button. Select PostgreSQL and click Continue. If you're not using a tunnel, you can then authenticate Hightouch to PostgreSQL by entering the following fields:

• Host: The hostname or IP address of your PostgreSQL server. You don't need to include the https://.
• Port: The port number of your PostgreSQL server. The default port number is 5432, but yours may be different.
• Database: This specifies the database to use when Hightouch executes queries in PostgreSQL. This is different than the host, but your host address might contain your database name.
• User: The user that has access through Hightouch to the database and tables you want to sync to. It's best to create a new user specifically for Hightouch access. Do not use the root user.
• Password: The password for the user specified above.

To ensure your credentials are correct, click Test connection. This confirms if Hightouch can connect to your database by running a basic SELECT query.

Required permissions

The user you use to authenticate must have the following permissions:

• Add, update, and delete (if applicable) rows in your sync's table.
• View the following tables which are used for gathering metadata to set up the sync:
  • INFORMATION_SCHEMA.COLUMNS
  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  • INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

SSH tunneling

Hightouch can connect directly to PostgreSQL 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 PostgreSQL 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.

Sync configuration

Once you've set up your PostgreSQL destination and have a model to pull data from, you can set up your sync configuration to begin syncing data. Go to the Syncs overview page and click the Add sync button to begin. Then, select the relevant model and the PostgreSQL destination you want to sync to.

Hightouch supports Upsert mode, with the option to delete removed rows, and Insert mode using the COPY FROM STDINstatement. This statement loads queried rows into your table faster than upsert mode. However, using this statement, PostgreSQL rejects the entire batch if any row contains a primary key or unique value that already exists.

Record matching

Hightouch requires you select a unique identifier in the table you are syncing to. The model column you select must match a UNIQUE, PRIMARY KEY column within PostgreSQL. If a field that satisfies these conditions still doesn't appear in the dropdown menu, ensure that you assigned the required permissions to the Postgres user.

You can see columns that fit this criteria as available options in record matching section.

If there are no fields in the dropdown, you must add a unique type column to your PostgreSQL table. Then, click the refresh icon to access the newly created column.

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 model is producing the wrong format. If so, use typecasting to resolve the issue.

Array support

Hightouch supports arrays of standard JSON objects (JSON or JSONB) but doesn't support arrays of USER-DEFINED type JSON objects.

Because insert mode uses the COPY FROM STDIN statement, it doesn't support the array of JSONB column type (jsonb[]). Instead, use a JSONB column type, which natively supports arrays.

Field mapping

You can sync columns from your model to PostgreSQL fields.

Batch size and sleep interval

You can tune the number of rows Hightouch upserts or inserts per query based on your needs and database threshold. The default is 1000 rows per batch. You can also optionally enter a custom sleep interval in milliseconds (ms) between batches. This default is 100 ms.

If you want to increase the sync's speed, you can increase the batch size and lower the sleep interval. Keep in mind that PostgreSQL fails the entire batch of rows if it detects any erroneous row. If you suspect that you will have many bad rows, don't use a high batch size. To avoid locks, ensure you account for your database's capacity when increasing the batch size.

Delete behavior

The delete behavior you select dictates what to do when a row no longer appears in your model's query results. You have the following options:

Tips and troubleshooting

Common errors

If you encounter an error or question not listed below and need assistance, don't hesitate to reach out. We're here to help.

Canceling statement due to statement timeout

This error occurs when the required execution time of your PostgreSQL query exceeds the timeout limit for the database. To address the error, increase the timeout by executing the following query:

set statement_timeout = '300 s'; -- 300 seconds, 5 minutes

Be sure to adjust the time to be as long as it takes for your query to execute.

Live debugger

Hightouch provides complete visibility into the API calls made during each of your sync runs. We recommend reading our article on debugging tips and tricks to learn more.

Sync alerts

Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.