PostgreSQL

Power internal tools, in-app experiences, and more

Setup

Required permissions

If you're not using a tunnel, your settings for the PostgreSQL destination should contain:

• Host: The URL or IP address for your PostgreSQL host. You do not need to include the https://
• Port: Your PostgreSQL port. The default- as is typical for PostgreSQL - is 5432.
• Database: The name of your database. This is different than the host, but your host address might contain your database name.
• Username: The user that has access through Hightouch to the database and tables you want to sync to. it's recommended you create a new user specifically for Hightouch access; do not use the root user.
• Password: The user's password.

The user credentials must be able to:

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

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

Compatible versions

The minimum supported Postgres version is 9.5 (Hightouch relies on ON CONFLICT for updating rows).

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 will 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 we upsert or insert 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 Postgres 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 Postgres statement. This will load the queried rows into your table. However, Postgres 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.