ClickPipes for Postgres FAQ

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/postgres/faq.md)

### Are transaction rollbacks replicated to ClickHouse?[​](#are-transaction-rollbacks-replicated "Direct link to Are transaction rollbacks replicated to ClickHouse?")

No. CDC replicates only committed transactions. Rolled-back transactions are never sent to ClickHouse.

### Can I keep data in ClickHouse longer than in my source Postgres?[​](#retain-data-longer-in-clickhouse "Direct link to Can I keep data in ClickHouse longer than in my source Postgres?")

Yes. Your source Postgres and destination ClickHouse have independent retention. For example, you can keep only 3 months of data in Postgres while retaining the full history in ClickHouse. Deleting old rows in Postgres generates DELETE events that are replicated to ClickHouse, so if you want to preserve historical data you should either exclude DELETEs from your [publication](/docs/integrations/clickpipes/postgres/faq#ignore-delete-truncate) or handle them at the query layer.

### How can I enrich data as it flows from Postgres to ClickHouse?[​](#data-enrichment "Direct link to How can I enrich data as it flows from Postgres to ClickHouse?")

Use [materialized views](/docs/materialized-views) on top of your CDC destination tables. Materialized views in ClickHouse act as insert triggers, so each row replicated from Postgres can be transformed, joined with lookup tables, or enriched with additional columns before being written to a final target table.

### Can I replicate from multiple Postgres instances into one or more ClickHouse services?[​](#multi-region-multi-source "Direct link to Can I replicate from multiple Postgres instances into one or more ClickHouse services?")

Yes. You can create separate ClickPipes from different Postgres instances (including across AWS regions) into one or more ClickHouse services. For example, you could send data from a regional Postgres to a local ClickHouse cluster for low-latency analytics, and simultaneously to a centralized ClickHouse cluster in another region for consolidated reporting. Keep in mind that cross-region setups incur AWS cross-region data transfer costs and additional network latency.

### How does idling affect my Postgres CDC ClickPipe?[​](#how-does-idling-affect-my-postgres-cdc-clickpipe "Direct link to How does idling affect my Postgres CDC ClickPipe?")

If your ClickHouse Cloud service is idling, your Postgres CDC ClickPipe will continue to sync data, your service will wake-up at the next sync interval to handle the incoming data. Once the sync is finished and the idle period is reached, your service will go back to idling.

As an example, if your sync interval is set to 30 mins and your service idle time is set to 10 mins, Your service will wake-up every 30 mins and be active for 10 mins, then go back to idling.

### How are TOAST columns handled in ClickPipes for Postgres?[​](#how-are-toast-columns-handled-in-clickpipes-for-postgres "Direct link to How are TOAST columns handled in ClickPipes for Postgres?")

Please refer to the [Handling TOAST Columns](/docs/integrations/clickpipes/postgres/toast) page for more information.

### How are generated columns handled in ClickPipes for Postgres?[​](#how-are-generated-columns-handled-in-clickpipes-for-postgres "Direct link to How are generated columns handled in ClickPipes for Postgres?")

Please refer to the [Postgres Generated Columns: Gotchas and Best Practices](/docs/integrations/clickpipes/postgres/generated_columns) page for more information.

### Do tables need to have primary keys to be part of Postgres CDC?[​](#do-tables-need-to-have-primary-keys-to-be-part-of-postgres-cdc "Direct link to Do tables need to have primary keys to be part of Postgres CDC?")

For a table to be replicated using ClickPipes for Postgres, it must have either a primary key or a [REPLICA IDENTITY](https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY) defined.

- **Primary Key**: The most straightforward approach is to define a primary key on the table. This provides a unique identifier for each row, which is crucial for tracking updates and deletions. You can have REPLICA IDENTITY set to `DEFAULT` (the default behavior) in this case.
- **Replica Identity**: If a table doesn't have a primary key, you can set a replica identity. The replica identity can be set to `FULL`, which means that the entire row will be used to identify changes. Alternatively, you can set it to use a unique index if one exists on the table, and then set REPLICA IDENTITY to `USING INDEX index_name`.
  To set the replica identity to FULL, you can use the following SQL command:

```
ALTER TABLE your_table_name REPLICA IDENTITY FULL;
```

REPLICA IDENTITY FULL also enables replication of unchanged TOAST columns. More on that [here](/docs/integrations/clickpipes/postgres/toast).

Note that using `REPLICA IDENTITY FULL` can have performance implications and also faster WAL growth, especially for tables without a primary key and with frequent updates or deletes, as it requires more data to be logged for each change. If you have any doubts or need assistance with setting up primary keys or replica identities for your tables, please reach out to our support team for guidance.

It's important to note that if neither a primary key nor a replica identity is defined, ClickPipes won't be able to replicate changes for that table, and you may encounter errors during the replication process. Therefore, it's recommended to review your table schemas and ensure that they meet these requirements before setting up your ClickPipe.

### Do you support partitioned tables as part of Postgres CDC?[​](#do-you-support-partitioned-tables-as-part-of-postgres-cdc "Direct link to Do you support partitioned tables as part of Postgres CDC?")

Yes, partitioned tables are supported out of the box, as long as they have a PRIMARY KEY or REPLICA IDENTITY defined. The PRIMARY KEY and REPLICA IDENTITY must be present on both the parent table and its partitions. You can read more about it [here](https://blog.peerdb.io/real-time-change-data-capture-for-postgres-partitioned-tables).

### Can I connect Postgres databases that don't have a public IP or are in private networks?[​](#can-i-connect-postgres-databases-that-dont-have-a-public-ip-or-are-in-private-networks "Direct link to Can I connect Postgres databases that don't have a public IP or are in private networks?")

Yes! ClickPipes for Postgres offers two ways to connect to databases in private networks:

1. **SSH Tunneling**

   - Works well for most use cases
   - See the setup instructions [here](/docs/integrations/clickpipes/postgres#adding-your-source-postgres-database-connection)
   - Works across all regions
2. **AWS PrivateLink**

   - Available in three AWS regions:
     - us-east-1
     - us-east-2
     - eu-central-1
   - For detailed setup instructions, see our [PrivateLink documentation](/docs/knowledgebase/aws-privatelink-setup-for-clickpipes)
   - For regions where PrivateLink isn't available, please use SSH tunneling

### How do you handle UPDATEs and DELETEs?[​](#how-do-you-handle-updates-and-deletes "Direct link to How do you handle UPDATEs and DELETEs?")

ClickPipes for Postgres captures both INSERTs and UPDATEs from Postgres as new rows with different versions (using the `_peerdb_` version column) in ClickHouse. The ReplacingMergeTree table engine periodically performs deduplication in the background based on the ordering key (ORDER BY columns), retaining only the row with the latest `_peerdb_` version.

DELETEs from Postgres are propagated as new rows marked as deleted (using the `_peerdb_is_deleted` column). Since the deduplication process is asynchronous, you might temporarily see duplicates. To address this, you need to handle deduplication at the query layer.