ClickPipes for Postgres FAQ

### 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.

Also note that by default, Postgres doesn't send column values of columns that aren't part of the primary key or replica identity during DELETE operations. If you want to capture the full row data during DELETEs, you can set the [REPLICA IDENTITY](https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY) to FULL.

For more details, refer to:

- [ReplacingMergeTree table engine best practices](https://docs.peerdb.io/bestpractices/clickhouse_datamodeling#replacingmergetree-table-engine)
- [Postgres-to-ClickHouse CDC internals blog](https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips)

### Can I update primary key columns in PostgreSQL?[​](#can-i-update-primary-key-columns-in-postgresql "Direct link to Can I update primary key columns in PostgreSQL?")

Note

Primary key updates in PostgreSQL can't be properly replayed in ClickHouse by default.

This limitation exists because `ReplacingMergeTree` deduplication works based on the `ORDER BY` columns (which typically correspond to the primary key). When a primary key is updated in PostgreSQL, it appears as a new row with a different key in ClickHouse, rather than an update to the existing row. This can lead to both the old and new primary key values existing in your ClickHouse table.

Note that updating primary key columns isn't a common practice in PostgreSQL database design, as primary keys are intended to be immutable identifiers. Most applications avoid primary key updates by design, making this limitation rarely encountered in typical use cases.

There is an experimental setting available that can enable primary key update handling, but it comes with significant performance implications and isn't recommended for production use without careful consideration.

If your use case requires updating primary key columns in PostgreSQL and having those changes properly reflected in ClickHouse, please reach out to our support team at [[email protected]](/cdn-cgi/l/email-protection#8befe9a6e2e5ffeeecf9eaffe2e4e5f8a6f8fefbfbe4f9ffcbe8e7e2e8e0e3e4fef8eea5e8e4e6) to discuss your specific requirements and potential solutions.

### Do you support schema changes?[​](#do-you-support-schema-changes "Direct link to Do you support schema changes?")

Please refer to the [ClickPipes for Postgres: Schema Changes Propagation Support](/docs/integrations/clickpipes/postgres/schema-changes) page for more information.

### What are the costs for ClickPipes for Postgres CDC?[​](#what-are-the-costs-for-clickpipes-for-postgres-cdc "Direct link to What are the costs for ClickPipes for Postgres CDC?")

For detailed pricing information, please refer to the [ClickPipes for Postgres CDC pricing section on our main billing overview page](/docs/cloud/reference/billing/clickpipes).

### My replication slot size is growing or not decreasing; what might be the issue?[​](#my-replication-slot-size-is-growing-or-not-decreasing-what-might-be-the-issue "Direct link to My replication slot size is growing or not decreasing; what might be the issue?")

If you're noticing that the size of your Postgres replication slot keeps increasing or isn't coming back down, it usually means that **WAL (Write-Ahead Log) records aren't being consumed (or "replayed") quickly enough** by your CDC pipeline or replication process. Below are the most common causes and how you can address them.

1. **Sudden Spikes in Database Activity**

   - Large batch updates, bulk inserts, or significant schema changes can quickly generate a lot of WAL data.
   - The replication slot will hold these WAL records until they're consumed, causing a temporary spike in size.
2. **Long-Running Transactions**

   - An open transaction forces Postgres to keep all WAL segments generated since the transaction began, which can dramatically increase slot size.
   - Set `statement_timeout` and `idle_in_transaction_session_timeout` to reasonable values to prevent transactions from staying open indefinitely:

     ```
     SELECT
         pid,
         state,
         age(now(), xact_start) AS transaction_duration,
         query AS current_query
     FROM
         pg_stat_activity
     WHERE
         xact_start IS NOT NULL
     ORDER BY
         age(now(), xact_start) DESC;
     ```

     Use this query to identify unusually long-running transactions.
3. **Maintenance or Utility Operations (e.g., `pg_repack`)**

   - Tools like `pg_repack` can rewrite entire tables, generating large amounts of WAL data in a short time.
   - Schedule these operations during slower traffic periods or monitor your WAL usage closely while they run.
4. **VACUUM and VACUUM ANALYZE**

   - Although necessary for database health, these operations can create extra WAL traffic—especially if they scan large tables.
   - Consider using autovacuum tuning parameters or scheduling manual VACUUM operations during off-peak hours.
5. **Replication Consumer Not Actively Reading the Slot**

   - If your CDC pipeline (e.g., ClickPipes) or another replication consumer stops, pauses, or crashes, WAL data will accumulate in the slot.
   - Ensure your pipeline is continuously running and check logs for connectivity or authentication errors.

For an excellent deep dive into this topic, check out our blog post: [Overcoming Pitfalls of Postgres Logical Decoding](https://blog.peerdb.io/overcoming-pitfalls-of-postgres-logical-decoding#heading-beware-of-replication-slot-growth-how-to-monitor-it).

### How are Postgres data types mapped to ClickHouse?[​](#how-are-postgres-data-types-mapped-to-clickhouse "Direct link to How are Postgres data types mapped to ClickHouse?")

ClickPipes for Postgres aims to map Postgres data types as natively as possible on the ClickHouse side. This document provides a comprehensive list of each data type and its mapping: [Data Type Matrix](https://docs.peerdb.io/datatypes/datatype-matrix).

### Can I define my own data type mapping while replicating data from Postgres to ClickHouse?[​](#can-i-define-my-own-data-type-mapping-while-replicating-data-from-postgres-to-clickhouse "Direct link to Can I define my own data type mapping while replicating data from Postgres to ClickHouse?")

Currently, we don't support defining custom data type mappings as part of the pipe. However, note that the default data type mapping used by ClickPipes is highly native. Most column types in Postgres are replicated as closely as possible to their native equivalents on ClickHouse. Integer array types in Postgres, for instance, are replicated as integer array types on ClickHouse.

### How are JSON and JSONB columns replicated from Postgres?[​](#how-are-json-and-jsonb-columns-replicated-from-postgres "Direct link to How are JSON and JSONB columns replicated from Postgres?")

JSON and JSONB columns are replicated as String type in ClickHouse. Since ClickHouse supports a native [JSON type](/docs/sql-reference/data-types/newjson), you can create a materialized view over the ClickPipes tables to perform the translation if needed. Alternatively, you can use [JSON functions](/docs/sql-reference/functions/json-functions) directly on the String columns. We're actively working on a feature that replicates JSON and JSONB columns directly to the JSON type in ClickHouse. This feature is expected to be available in a few months.