# Architecture Overview Source: https://docs.peerdb.io/architecture PeerDB is fundamentally made up of two parts: the platform and the connectors. The **platform** encompasses the essential services necessary to configure and run data querying and movement operations, which include the Nexus Query Layer and the Flow Data Transfer Component. * **Nexus Query Layer**: Located in the `nexus` subfolder in the root repository, Nexus is a Rust-implemented service that enables Postgres-compatible SQL querying across various data sources and sinks. It supports the PGWire protocol and can be horizontally scaled to manage high demand. * **Flow Data Transfer Component**: Stored in the `flow` subfolder of the root repository, Flow is written in Golang. It manages data transfer between sources and sinks, and consists of a Flow API and horizontally scalable Flow Workers. The **connectors** signify various data sources and sinks that PeerDB can interact with. They include databases like Postgres, MySQL (sources), and Bigquery, Snowflake (sinks). ### Detailed Components * **Nexus Query Layer**: This is the user interface to run SQL queries. It communicates with both data sources and sinks. * **Flow Data Transfer Component**: It has two parts - the Flow API that manages the process and the Flow Workers that perform the actual data transfer. * **Sources**: These are the databases from which data can be fetched, like Postgres and MySQL. * **Sinks**: These are the databases where data can be deposited, like Bigquery and Snowflake. ### Dependencies PeerDB has two primary dependencies: 1. **[Temporal Orchestration Engine](https://www.temporal.io/)**: This external service orchestrates the data transfer process within the Flow component. 2. **Catalog Database (Postgres)**: This database stores metadata related to PeerDB's operations. The main strengths of PeerDB are its scalability and flexibility, allowing for the addition of Nexus and Flow Worker instances to handle increased load efficiently. # Benchmarking Postgres Replication: PeerDB vs Airbyte Source: https://docs.peerdb.io/benchmarks/benchmarking-Postgres-Replication:-PeerDB-vs-Airbyte We at [PeerDB](https://www.peerdb.io/) are working on fast and simple data movement in and out of Postgres. Other data-movement tools also support the Postgres connector and have been investing in improving this. For example, [Airbyte](https://airbyte.com) has released a [series](https://airbyte.com/blog/replicate-postgres-datasets-of-any-size-in-airbyte) [of](https://airbyte.com/blog/postgres-replication-performance-benchmark-airbyte-vs-fivetran) blog posts demonstrating improvements they have made to their Postgres connector, leading to significant performance gains over [Fivetran](https://www.fivetran.com/). In the past few weeks, we spent some time running a benchmark captured in [this](https://airbyte.com/blog/postgres-replication-performance-benchmark-airbyte-vs-fivetran) Airbyte blog. The primary goal was to understand - how we stack up against other tools, how our existing features impact performance and how can we further improve our product. ## Assumptions Airbyte’s [benchmark](https://airbyte.com/blog/postgres-replication-performance-benchmark-airbyte-vs-fivetran) was performing a one-time transfer of a single large table from Postgres to Snowflake. Since we are moving data already present in the source table instead of incrementally moving fresh data, this is what we call a **Full Refresh** in Airbyte and an **Initial Snapshot** in PeerDB. Benchmarking a large migration like this is highly dependent not only on the performance of the migration platform but also on the performance of the source Postgres and target Snowflake instances. Network throughput between all these parts is also crucial. So we generated a dataset and tested PeerDB and Airbyte against it, using instances for Postgres and Snowflake which we felt best represented a production deployment. The entire infrastructure was positioned within a single AWS region, reducing network bottlenecks. More details on the setup can be found [**here**](https://github.com/PeerDB-io/ab-scale-testing#benchmark-setup)**.** ## Generating the Data While Airbyte provided the schema of the table, it wasn’t enough to generate a dataset, as we didn’t know the size of a row or the number of rows in the table. We decided to run the test on a table with 6 billion rows and a 1.5TB size. We worked out that each row should be around 230-235 bytes. We then arrived at a size for each variable length field that should get us the table size we wanted. We also converted one of the columns in the table to be a generated primary key because PeerDB currently [requires](https://docs.peerdb.io/usecases/real-time-cdc/postgres-to-snowflake#prerequisites) one for CDC mirroring. ```sql theme={null} CREATE TABLE IF NOT EXISTS public.xxxxx ( f0 BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, f1 BIGINT, f2 BIGINT, f3 INTEGER, f4 DOUBLE PRECISION, f5 DOUBLE PRECISION, f6 DOUBLE PRECISION, f7 DOUBLE PRECISION, f8 VARCHAR COLLATE pg_catalog.\"default\", f9 VARCHAR COLLATE pg_catalog.\"default\", f10 DATE, f11 DATE, f12 DATE, f13 VARCHAR COLLATE pg_catalog.\"default\", f14 VARCHAR COLLATE pg_catalog.\"default\", f15 VARCHAR COLLATE pg_catalog.\"default\" ) ``` After doing *all* this, we wrote a [small Rust program](https://github.com/PeerDB-io/ab-scale-testing/blob/main/src/main.rs) that could generate 6 billion rows and insert them into a Postgres instance we provisioned on AWS RDS. After some debugging, we were in business! ```rust theme={null} [2023-09-12T10:08:33Z INFO firenibble] Finished inserting 6000000000 rows in 12689.01s seconds [2023-09-12T10:08:33Z INFO firenibble] Throughput: 472850.12 ``` ```plaintext theme={null} postgres=> SELECT pg_size_pretty(pg_relation_size('public.xxxxx')); pg_size_pretty ---------------- 1578 GB (1 row) ``` ## Testing! Both AirByte and PeerDB have an Open Source offering and are available as Docker Compose Applications. So we decided to use these for our testing, on a sufficiently powerful AWS EC2 instance. To reiterate, we are only looking for the performance of the initial load, and not an incremental sync. ### Airbyte With Airbyte, we already knew from their numbers that a run could take multiple days. So after deploying Airbyte and setting up our connectors, we kicked off a run, checking in once every few hours. After an initial failure because of hitting a [timeout (of 72 hours)](https://github.com/airbytehq/airbyte/issues/7525#issuecomment-959778866), Airbyte completed successfully. It took **83 hours** to move the table to Snowflake. airbyte snowflake ### PeerDB PeerDB [implements parallelism for these heavy initial loads](https://blog.peerdb.io/parallelized-initial-load-for-cdc-based-streaming-from-postgres), and we launched five runs in total with 1, 8, 16, 32 and 48 parallel threads. With 32 and 48 threads, PeerDB moved over **1.5TB** of Postgres table in under **5 hours**. Even scaling down to 8 threads, we still see a runtime of under **9** hours. This performance derives from the optimizations PeerDB has done to make reads as efficient as possible and also the parallelism multiplier. Airbyte does not support parallelism at the moment. We decided to do an additional run with parallelism set to 1, as a fair comparison to Airbyte. With this, we got a run time of **43** hours. ## Results Airbyte took **83 hours** to move a 1.5TB table from Postgres to Snowflake. With a parallelism of 32 threads, PeerDB took 5 hours to do the same job. So PeerDB was **16x** faster. Even considering a single-threaded run, PeerDB is **\~2x** faster. We felt that a comparison with Fivetran was out of scope for this article. Airbyte had already shown that Fivetran was slower than Airbyte and we could expect a full refresh to take **150+ hours**. results ## What makes PeerDB faster? ### Parallelism One of the more obvious reasons is our early adoption of parallelism for long-running operations such as moving large tables from Postgres to Snowflake. We do this by logically partitioning the large table based on internal tuple identifiers (CTID) and parallelly streaming those partitions to Snowflake. The implementation is inspired by this DuckDb [**blog**](https://duckdb.org/2022/09/30/postgres-scanner.html#parallel). Based on the load you can put on the source Postgres database, you can [**configure**](https://docs.peerdb.io/sql/commands/create-mirror#mirror-for-cdc) the parallelism for the sync. More details can be found in [this](https://blog.peerdb.io/parallelized-initial-load-for-cdc-based-streaming-from-postgres) blog results Large table logically partitioned based on CTID ranges and streamed in parallel, reliably and efficiently to the target data-store. During the initial load, a snapshot connection needs to be maintained to ensure data consistency on the target. ### Batching The second is [configurable batching](https://docs.peerdb.io/sql/commands/create-mirror#mirror-for-cdc) while reading from Postgres and writing to Snowflake. If you have PeerDB running on a large machine you could have a larger batch size (ex: 1mill). This reduces the network roundtrips across Postgres, PeerDB and Snowflake thereby improving performance. Configurable batching also helps meet Snowflake's [recommendation](https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare#general-file-sizing-recommendations) for faster loads. In the above tests, we configured a batch size of \~750K to meet this recommendation. ### Binary format for data transfer This third is using [Avro](https://github.com/PeerDB-io/peerdb/pull/123) as the intermediary data format. Avro enables data to be stored in binary (compressed) format and still supports a [**wide variety of data types**](https://avro.apache.org/docs/1.10.2/spec.html#schema_complex) (unlike parquet). This enables fast data movement, without compromising data integrity. ### Glimpses into the future We are working on multiple other features incl. concurrent reading of the replication slot during the Initial Snapshot; parallelized writing of Change Data Capture to Target tables; and others to further improve performance ## Closing Remarks Keeping performance aside, we were quite impressed with Airbyte's web interface and a staggering variety of connectors. We would recommend it if the breadth of available connectors is more important to you. At PeerDB we instead have a laser focus on Postgres with the primary goal of providing the highest quality source and destination connectors for it. If Postgres is a central part of your data stack and you want to stream data from Postgres to data warehouses, message queues or storage engines faster, simpler and cheaper, **come talk to us** [**here**](https://www.peerdb.io/sign-up). You could also try out our free and open offering [here](https://github.com/PeerDB-io/peerdb). If you want to try benchmarking PeerDB and Airbyte yourself, we have put info about our benchmark [here](https://github.com/PeerDB-io/ab-scale-testing). # Postgres to ClickHouse: Data Modeling Tips Source: https://docs.peerdb.io/bestpractices/clickhouse_datamodeling [PeerDB](https://www.peerdb.io/) makes it fast and simple to replicate data from [Postgres](https://www.postgresql.org/) to [ClickHouse](https://clickhouse.com/). A common question from PeerDB users is how to model their data in ClickHouse after the replication process to maximize the benefits of ClickHouse. This question arises because ClickHouse and Postgres differ in data modeling, as each is a **purpose-built database** highly optimized for its specific workload -Postgres is a transactional (OLTP) database, while ClickHouse is an analytical (OLAP) columnar database. This guide walks you through essential data modeling concepts in ClickHouse for users coming from the Postgres world. ## ReplacingMergeTree table engine PeerDB maps PostgreSQL tables to ClickHouse using the [ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree) engine. ClickHouse performs best with append-only workloads and [does not recommend](https://clickhouse.com/docs/en/guides/developer/mutations) frequent UPDATEs. This is where ReplacingMergeTree is particularly powerful. `ReplacingMergeTree` supports workloads that involve both data ingestion and modifications. Each table is append-only, with user updates ingested as versioned INSERTs. The ReplacingMergeTree engine manages deduplication (merging) of rows in the background. This is one of the key factors that enables ClickHouse to deliver exceptional real-time ingestion performance. In PeerDB, both INSERTs and UPDATEs from Postgres are captured as new rows with different versions (using `_peerdb_version`) in ClickHouse. The `ReplacingMergeTree` table engine periodically handles deduplication in the background using the Ordering Key (ORDER BY columns), retaining only the row with the latest `_peerdb_version`. DELETEs from PostgreSQL are propagated as new rows marked as deleted (using the `_peerdb_is_deleted` column). The snippet below shows the target table definition for the `public_goals` table in ClickHouse. ```sql theme={null} clickhouse-cloud :) SHOW CREATE TABLE public_goals; CREATE TABLE peerdb.public_goals ( `id` Int64, `owned_user_id` String, `goal_title` String, `goal_data` String, `enabled` Bool, `ts` DateTime64(6), `_peerdb_synced_at` DateTime64(9) DEFAULT now(), `_peerdb_is_deleted` Int8, `_peerdb_version` Int64 ) ENGINE = SharedReplacingMergeTree ('/clickhouse/tables/{uuid}/{shard}', '{replica}', _peerdb_version) PRIMARY KEY id ORDER BY id SETTINGS index_granularity = 8192 ``` ## You might still see duplicates for rows—how should you handle them? ReplacingMergeTree clears out duplicates asynchronously in the background but doesn't guarantee the absence of duplicates. So, when you query the data, you might still see duplicates for the same row or primary key but with different versions. This is expected. To remove duplicates, you have a couple of approaches: ### Use FINAL in your queries ClickHouse has a unique modifier called [FINAL](https://clickhouse.com/docs/en/sql-reference/statements/select/from#final-modifier), which performs de-duplication (merging of rows) at query time. This de-duplication occurs after filtering (WHERE clause) but before aggregations (GROUP BY). A historical concern has been that FINAL can slow down query performance. While it does impact query performance to some extent, recent releases of ClickHouse have introduced [significant improvements](https://github.com/ClickHouse/ClickHouse/issues/11722) to enhance FINAL query performance. So, don’t hesitate to use the FINAL clause and evaluate how your queries perform. Below is an example of how to use the FINAL clause: ```sql theme={null} SELECT owner_user_id, COUNT(*) FROM goals FINAL WHERE enabled = true GROUP BY owner_user_id; ``` ### Use argMax to deduplicate rows at query time In ClickHouse, [argMax](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/argmax) is a powerful function for deduplicating rows dynamically during query execution. This is particularly useful when you need to retain the most recent or relevant record based on a versioning or timestamp column. For instance, if you're working with a table like `peerdb.public_goals`, where id is the primary key and `_peerdb_version` tracks versions, you can use argMax to select the row with the highest `_peerdb_version` for each `id`. This approach allows you to efficiently remove duplicates without altering the underlying data. You can then run your aggregations as a subquery over this deduplicated result set for further analysis. Below query is an example of using argMax ```sql theme={null} SELECT owned_user_id, COUNT(*) AS active_goals_count, MAX(ts) AS latest_goal_time FROM ( SELECT id, argMax(owned_user_id, _peerdb_version) AS owned_user_id, argMax(goal_title, _peerdb_version) AS goal_title, argMax(goal_data, _peerdb_version) AS goal_data, argMax(enabled, _peerdb_version) AS enabled, argMax(ts, _peerdb_version) AS ts, argMax(_peerdb_synced_at, _peerdb_version) AS _peerdb_synced_at, argMax(_peerdb_is_deleted, _peerdb_version) AS _peerdb_is_deleted, max(_peerdb_version) AS _peerdb_version FROM peerdb.public_goals WHERE enabled = true GROUP BY id ) AS deduplicated_goals GROUP BY owned_user_id; ``` ### Use WINDOW FUNCTIONS You can use ClickHouse's [window functions](https://clickhouse.com/docs/en/sql-reference/window-functions) to achieve similar deduplication by selecting the row with the highest `_peerdb_version` within each id partition. Here's an example: ```sql theme={null} SELECT owned_user_id, COUNT(*) AS active_goals_count, MAX(ts) AS latest_goal_time FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn FROM peerdb.public_goals WHERE enabled = true ) AS ranked_goals WHERE rn = 1 GROUP BY owned_user_id; ``` ### Use Views to simplify deduplication Encapsulate deduplication in a [view](https://clickhouse.com/docs/en/sql-reference/statements/create/view) to make it simple for BI tools to query the most up-to-date data. For example, use a window function in the view to keep only the latest version of each row: ```sql theme={null} CREATE VIEW goals AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn FROM peerdb.public_goals WHERE enabled = true ) WHERE rn = 1; ``` ```sql theme={null} SELECT owned_user_id, COUNT(*) AS active_goals_count, MAX(ts) AS latest_goal_time FROM goals GROUP BY owned_user_id; ``` ## Nullable Columns If you're coming from the Postgres world, one surprising aspect of ClickHouse is that it doesn’t store NULL values for columns unless you explicitly wrap the column types in [`Nullable`](https://clickhouse.com/docs/en/sql-reference/data-types/nullable). For example, instead of storing NULL for dates, ClickHouse stores `1970-01-01` as the default value, which might be unexpected. This behavior is due to the fact that storing NULLs can [impact](https://clickhouse.com/docs/en/sql-reference/data-types/nullable) query performance in ClickHouse, as it’s a columnar database. Hence, ClickHouse requires users to explicitly define `Nullable` types. In PeerDB, we’ve introduced a setting called `PEERDB_NULLABLE`, which, when set to `true`, automatically detects nullable columns in Postgres and marks them as `Nullable` in ClickHouse during the replication process. This means you don’t need to manually define `Nullable` types during replication. You can read more about this feature in the following [PR](https://github.com/PeerDB-io/peerdb/pull/2001). ## **Data Types** ClickHouse offers a wide variety of data types, ranging from numbers, text, timestamps, dates, and arrays to the recently introduced [JSON](https://github.com/ClickHouse/ClickHouse/issues/54864) type. Many of the data types in Postgres can be natively stored in ClickHouse without much modification. As a reference, here goes [the data type matrix](https://docs.peerdb.io/datatypes/datatype-matrix) we use at PeerDB when replicating data from Postgres to ClickHouse. ## The Ordering Key ### What is an Ordering Key? Choosing the right ordering key is crucial for query performance in ClickHouse. Defined by the `ORDER BY` clause when creating a table, the ordering key functions similarly to a index in Postgres but is optimized for analytics. Unlike Postgres, which uses a B-tree index with entries pointing to each row, ClickHouse uses Sparse Indexing: 1. **Data is sorted based on Ordering Key:** The ordering key ensures that data on disk is sorted according to the specified columns. This allows for better [compression](https://clickhouse.com/docs/en/data-compression/compression-in-clickhouse), as correlated values are stored together. 2. **Ordering Key also creates a sparse index:** The ordering key also creates a sparse index, storing only ranges of columns, with each entry pointing to a group of sorted rows. This keeps the index small, allowing ClickHouse to quickly identify relevant groups of rows using a binary search and execute queries efficiently. You can read more about this [here](https://clickhouse.com/docs/en/migrations/postgresql/designing-schemas#primary-ordering-keys-in-clickhouse). You can think of ordering keys as similar to [BRIN](https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BRIN) indexes in Postgres, but in ClickHouse, the data is automatically sorted based on the ordering key via asynchronous merging of parts, so you don’t need to handle sorting during data ingestion. ### Choosing an appropriate Ordering Key When selecting an ordering key, base your choice on the columns most frequently used in your query filters. **Prioritize columns that are commonly used in WHERE clauses, and order them in ascending sequence of cardinality**—starting with columns that have the fewest distinct values. This approach optimizes data compression and query performance. For a deeper understanding of this topic, refer to the detailed guide [here](https://clickhouse.com/docs/en/data-modeling/schema-design#choosing-an-ordering-key). ### **PRIMARY KEY vs Ordering Key** If you observe the table definition of `public_goals`, it has a `PRIMARY KEY`. You might be wondering how the `PRIMARY KEY` differs from the Ordering Key. Let us understand how they differ: 1. `PRIMARY KEY`, if specified, defines the columns in the sparse index, while the columns in the `ORDER BY` clause determine how the data is sorted on disk. They are also used for deduplicating data by the `ReplacingMergeTree`. 2. If the `PRIMARY KEY` isn't specified, the Ordering Key automatically becomes the `PRIMARY KEY` and defines the columns in the sparse index. **NOTE:** Columns in the `PRIMARY KEY` should always be prefixed in the Ordering Key. This ensures that the index aligns with the physical data order, maximizing query performance by minimizing unnecessary data scans. **An example where** `PRIMARY KEY` **could differ from Ordering Key** An example where you might have different `PRIMARY KEY` and `ORDER BY` columns is when your queries are primarily filtered on `customer_id` rather than `id`. In this case, you can define the `PRIMARY KEY` on just `customer_id` and the `ORDER BY` on `customer_id, id`. This approach ensures a smaller, more efficient sparse index for querying, while data deduplication occurs on `id`, ensuring no data is lost. **NOTE:** Unlike in Postgres, where the `PRIMARY KEY` is a B-tree index that guarantees uniqueness, in ClickHouse, it does not ensure uniqueness. Instead, it defines the columns that should be part of the sparse index. ### Modifying the Ordering Key Choosing the right [ordering key](https://clickhouse.com/docs/en/migrations/postgresql/designing-schemas#primary-ordering-keys-in-clickhouse) is crucial for query performance in ClickHouse, as it acts as an index when querying data. By default, PeerDB uses the PostgreSQL `PRIMARY KEY` to define the ordering key in ClickHouse tables, but you can change it using the following methods: ### Use materialized views You can use materialized views to create a new table with a different ordering key suitable for your workload. Include the primary key columns at the end of the ordering key to ensure proper deduplication, as ReplacingMergeTree uses the ORDER BY clause for deduplication, and including the primary key ensures that no data is lost. ```sql theme={null} CREATE MATERIALIZED VIEW goals_mv ENGINE = ReplacingMergeTree(_peerdb_version) ORDER BY (enabled, ts, id)  POPULATE AS SELECT * FROM peerdb.public_goals; ``` **NOTE:** After creating the materialized view, be sure to follow the steps described in the previous section on handling duplicates to ensure proper deduplication during query time. ### Predefine target tables with the desired Ordering Key To change the ordering key, you can predefine new tables with your desired Ordering Key and then swap them with the existing tables. Here's how you can do it: **1. Create a Dummy Mirror:** Create a dummy mirror in PeerDB to generate the default tables with the correct metadata columns and data types. **2. Create a New Table with the Desired Ordering Key:** Use the table created by PeerDB to define a new table with your desired ordering key. Include the primary key columns at the end of the ordering key to ensure proper deduplication. Here is an example: ```sql theme={null} CREATE TABLE public_events_new AS public_events ENGINE = ReplacingMergeTree(_peerdb_version) ORDER BY (user_id,id); ``` **3. Drop the Old Table:** ```sql theme={null} DROP TABLE public_events; ``` **4. Rename the New Table:** Rename the new table to the actual table ```sql theme={null} RENAME TABLE public_events_new TO public_events; ``` **5. Start MIRROR to Point to the New Table:** Configure the mirror to point to the actual table. PeerDB uses `CREATE TABLE IF NOT EXISTS` behind the scenes and continues to ingest data into the new table. ## Handling DELETEs As mentioned, DELETEs from PostgreSQL are propagated as new rows marked as deleted (using the `_peerdb_is_deleted` column). To exclude deleted rows from your queries, you can create row-level policies in ClickHouse based on the `_peerdb_is_deleted` column. Here’s an example: ```sql theme={null} CREATE ROW POLICY policy_name ON table_name FOR SELECT USING _peerdb_is_deleted = 0; ``` This policy ensures that only rows where `_peerdb_is_deleted` is 0 are visible when querying the table. ## How to handle updates for primary keys? To propagate primary key updates into ClickHouse, enable `PEERDB_CLICKHOUSE_ENABLE_PRIMARY_UPDATE` setting on your mirror. This instructs the PeerDB normalize step to generate a “delete + insert” pair for changed primary keys, using a sign column (in the `ReplacingMergeTree`) and a version column to remove the old record before inserting the new one, ensuring that your ClickHouse tables accurately reflect the latest state from the source system. It is highly uncommon for primary keys to be updated in Postgres. ## Conclusion I hope you enjoyed reading the guide. We aimed to cover the most common data-modeling challenges you might encounter when migrating from PostgreSQL to ClickHouse. In the next blog, I plan to dive into more advanced topics, such as joins, writing efficient SQL queries, and so on. If you want to give PeerDB and ClickHouse a try to start replicating data from Postgres to ClickHouse, please check out the links below or reach out to us directly! 1. [Try PeerDB Cloud for Free](https://auth.peerdb.cloud/signup) 2. [Try ClickHouse Cloud for Free](https://clickhouse.com/docs/en/cloud-quick-start) 3. [Talk to the PeerDB team directly](https://www.peerdb.io/sign-up) # Postgres to ClickHouse: Handling TOAST Columns Source: https://docs.peerdb.io/bestpractices/clickhouse_toast_columns When replicating data from PostgreSQL to ClickHouse, it's important to understand the limitations and special considerations for TOAST (The Oversized-Attribute Storage Technique) columns. This guide will help you identify and properly handle TOAST columns in your replication process. ## What are TOAST columns in PostgreSQL? TOAST (The Oversized-Attribute Storage Technique) is PostgreSQL's mechanism for handling large field values. When a row exceeds the maximum row size (typically 2KB, but this can vary depending on the PostgreSQL version and exact settings), PostgreSQL automatically moves large field values into a separate TOAST table, storing only a pointer in the main table. It's important to note that during Change Data Capture (CDC), unchanged TOAST columns are not included in the replication stream. This can lead to incomplete data replication if not handled properly. During the initial load (snapshot), all column values, including TOAST columns, will be replicated correctly regardless of their size. The limitations described in this guide primarily affect the ongoing CDC process after the initial load. You can read more about TOAST and its implementation in PostgreSQL here: [https://www.postgresql.org/docs/current/storage-toast.html](https://www.postgresql.org/docs/current/storage-toast.html) ## Identifying TOAST columns in a table To identify if a table has TOAST columns, you can use the following SQL query: ```sql theme={null} SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid WHERE c.relname = 'your_table_name' AND a.attlen = -1 AND a.attstorage != 'p' AND a.attnum > 0; ``` This query will return the names and data types of columns that could potentially be TOASTed. However, it's important to note that this query only identifies columns that are eligible for TOAST storage based on their data type and storage attributes. To determine if these columns actually contain TOASTed data, you'll need to consider whether the values in these columns exceed the size. The actual TOASTing of data depends on the specific content stored in these columns. ## Ensuring proper handling of TOAST columns To ensure that TOAST columns are handled correctly during replication, you should set the `REPLICA IDENTITY` of the table to `FULL`. This tells PostgreSQL to include the full old row in the WAL for UPDATE and DELETE operations, ensuring that all column values (including TOAST columns) are available for replication. You can set the `REPLICA IDENTITY` to `FULL` using the following SQL command: ```sql theme={null} ALTER TABLE your_table_name REPLICA IDENTITY FULL; ``` Refer to [this blog post](https://xata.io/blog/replica-identity-full-performance) for performance considerations when setting `REPLICA IDENTITY FULL`. ## Replication behavior when REPLICA IDENTITY FULL is not set If `REPLICA IDENTITY FULL` is not set for a table with TOAST columns, you may encounter the following issues when replicating to ClickHouse: 1. For INSERT operations, all columns (including TOAST columns) will be replicated correctly. 2. For UPDATE operations: * If a TOAST column is not modified, its value will appear as NULL or empty in ClickHouse. * If a TOAST column is modified, it will be replicated correctly. 3. For DELETE operations, TOAST column values will appear as NULL or empty in ClickHouse. These behaviors can lead to data inconsistencies between your PostgreSQL source and ClickHouse destination. Therefore, it's crucial to set `REPLICA IDENTITY FULL` for tables with TOAST columns to ensure accurate and complete data replication. ## Conclusion Properly handling TOAST columns is essential for maintaining data integrity when replicating from PostgreSQL to ClickHouse. By identifying TOAST columns and setting the appropriate `REPLICA IDENTITY`, you can ensure that your data is replicated accurately and completely. # Generated Columns: Gotchas and Best Practices Source: https://docs.peerdb.io/bestpractices/generated_columns When using PostgreSQL's generated columns in tables that are being replicated, there are some important considerations to keep in mind. These gotchas can affect the replication process and data consistency in your destination systems. ## The Problem with Generated Columns 1. **Not Published via pgoutput:** Generated columns are not published through the pgoutput logical replication plugin. This means that when you're replicating data from PostgreSQL to another system, the values of generated columns are not included in the replication stream. 2. **Issues with Primary Keys:** If a generated column is part of your primary key, it can cause deduplication problems on the destination. Since the generated column values are not replicated, the destination system won't have the necessary information to properly identify and deduplicate rows. ## Best Practices To work around these limitations, consider the following best practices: 1. **Recreate Generated Columns on the Destination:** Instead of relying on the replication process to handle generated columns, it's recommended to recreate these columns on the destination using tools like dbt (data build tool) or other data transformation mechanisms. 2. **Avoid Using Generated Columns in Primary Keys:** When designing tables that will be replicated, it's best to avoid including generated columns as part of the primary key. ## Upcoming improvements to UI In upcoming versions, we are planning to add a UI to help users with the following: 1. **Identify Tables with Generated Columns:** The UI will have a feature to identify tables that contain generated columns. This will help users understand which tables are affected by this issue. 2. **Documentation and Best Practices:** The UI will include best practices for using generated columns in replicated tables, including guidance on how to avoid common pitfalls. # Heartbeat Table For CDC Source: https://docs.peerdb.io/bestpractices/heartbeat **Disclaimer**: The below guide is more relevant if you are using read replicas or your Postgres instance is below Postgres 14. For primary Postgres instances of versions 14 and above, PeerDB now has inbuilt functionality to mitigate slot growth by default. Consider a scenario where you kick off a CDC mirror from PostgreSQL to a data warehouse to sync one table. The mirror creates a logical replication slot in PostgreSQL to stream changes. Now, this table rarely gets rows ingested to it on the source. However, the replication slot keeps growing because of the WAL logs generated by the changes in other tables. This can lead to the database disk getting filled up. This is common in development or staging environments where the database is not used heavily. In order to mitigate this, you can create a `heartbeat` table in the source database. ## Creating a `heartbeat` table on Postgres This table will have a single row that gets updated every minute. By including this table in the CDC mirror, PeerDB will pick up changes to this table, sync them and flush the slot periodically, keeping the slot size in check. The below is an example of what we're talking about. Ensure that the heartbeat table has [required permissions](/connect/postgres/rds_postgres#creating-peerdb-user-and-granting-permissions) to be a part of the mirror. ```sql theme={null} CREATE TABLE _peerdb_heartbeat ( id SERIAL PRIMARY KEY, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO _peerdb_heartbeat DEFAULT VALUES; -- Update the row every minute (this can be done with, say, pg_cron) UPDATE _peerdb_heartbeat SET last_updated = CURRENT_TIMESTAMP; ``` ### PeerDB Implementation PeerDB has the facility to perform periodic updates on your heartbeat table. This has the benefit of not needing to install an extension like pg\_cron. Currently, updates are done every 12 minutes. This will soon be configurable. PeerDB will require update permission on the heartbeat table: ```sql theme={null} GRANT UPDATE ON _peerdb_heartbeat TO ; ``` In PeerDB UI, head over to `Settings` in the sidebar. Search for `WAL` in the search bar on the right. Edit the current value fields of the following settings: * `PEERDB_ENABLE_WAL_HEARTBEAT`: Set this to `true` (no quotes or anything) * `PEERDB_WAL_HEARTBEAT_QUERY`: Set this to the update command to be run periodically: ```sql theme={null} UPDATE _peerdb_heartbeat SET last_updated = CURRENT_TIMESTAMP; ``` heartbeatsettings This sets up updates to the heartbeat table every 12 minutes. ### Manual Implementation Periodic update can also be done with the below pg\_cron command, for example: ```sql theme={null} -- updates every 30 seconds SELECT cron.schedule('*/30 * * * *', $$UPDATE _peerdb_heartbeat SET last_updated = CURRENT_TIMESTAMP$$); ``` ## Include the heartbeat table in the mirror Now, you can include it as part of the tables in the mirror either via the Create Mirror UI or through the SQL Layer as shown below. ```sql theme={null} CREATE MIRROR heartbeat_mirror FROM postgres_peer TO warehouse_peer WITH TABLE MAPPING ( public._peerdb_heartbeat: _peerdb_heartbeat_target, -- other tables ) WITH( ... ); ``` For an existing mirror, the [mirror can be edited](/features/edit-mirror) to add this table. # Defining the Ordering Key in ClickHouse differently from the Primary Key in Postgres Source: https://docs.peerdb.io/bestpractices/ordering-key-different PeerDB is now natively integrated with ClickHouse Cloud! We recently announced the Postgres CDC connector in ClickPipes, now available in Public Preview. This connector is fully powered by PeerDB. As a ClickHouse Cloud customer, you get a seamless, native experience for moving data from your Postgres databases to ClickHouse Cloud. Simply navigate to the **Data Sources** tab and add a Postgres source to start ingesting data. Deduplication inconsistencies in ReplacingMergeTree could occur when the Ordering Key in ClickHouse differs from the Primary Key in Postgres. In ClickHouse, the Ordering Key defines: a) the skip index and data order on disk, and b) the deduplication key - group of columns on which data de-duplication occurs. Users might select an Ordering Key that differs from the Primary Key in Postgres to optimize query performance, without realizing that this choice can lead to deduplication inconsistencies in the data. Below are scenarios where deduplication can go wrong: * **UPDATE on an Ordering Key Column:** For a row, if a column in the Ordering Key is updated, PeerDB replicates the latest version of that row from Postgres, as Postgres logical replication provides only the latest version by default. ClickHouse does not deduplicate this row with the previous version because the Ordering Key value has changed and is distinct from the previous version. This results in an additional row in ClickHouse compared to Postgres. * **DELETEs:**  If you delete a row, PeerDB replicates the deleted row with null values in all columns (including those in the Ordering Key) except for the Primary Key. This occurs because Postgres logical replication only provides the value of the Primary Key column for the row being deleted. As a result, ClickHouse does not deduplicate the existing row with the new row marked as deleted (soft delete), since the Ordering Key column values of the incoming deleted row are different from those of the existing row. This again results in an additional row in ClickHouse compared to Postgres. ### Prerequisites for the Ordering Key in ClickHouse to differ from the Primary Key in Postgres If you follow the steps below, there will be no deduplication inconsistencies, even if you define an Ordering Key in ClickHouse that differs from the Primary Key in Postgres: 1. **Primary Key in PG should be a suffix to the Ordering Key in Clickhouse:** Ensure that the Primary Key in Postgres is a suffix to the Ordering Key in ClickHouse. This is essential for accurate deduplication, as the Primary Key in Postgres represents the lowest granularity for a row. Defining an Ordering Key without the Primary Key can lead to lost rows. 2. **Define REPLICA IDENTITY as FULL or UNIQUE INDEX:** If you define the REPLICA IDENTITY of a table as FULL or as a UNIQUE INDEX on the columns in the Ordering Key, Postgres logical replication provides both the older and newer versions of the row, with the Ordering Key column values populated. This enables PeerDB to accurately handle the scenarios of updating an Ordering Key column and deletions. To ensure accurate de-duplication, PeerDB internally treats the update as a DELETE of the old row and an INSERT of the new row, allowing ClickHouse to manage deduplication correctly. Similarly, for deletions, PeerDB marks the older row with the appropriate Ordering Key columns as deleted, ensuring accurate deduplication. To Define REPLICA IDENTITY FULL, you could run the below command: ```sql theme={null} ALTER TABLE table_name REPLICA IDENTITY FULL; ``` **Impact of REPLICA IDENTITY FULL**: [This](https://xata.io/blog/replica-identity-full-performance) is a great blog regarding the impact on your instance if you define REPLICA IDENTITY FULL. **TL;DR** the impact from REPLICA IDENTITY FULL will likely be manageable as long as the replicated tables have a primary key! **OR** To define REPLICA IDENTITY using a UNIQUE INDEX, you can use the following commands: ```sql theme={null} -- Create an index on the Ordering Key columns if it doesn't exist CREATE UNIQUE INDEX orderingkey_index ON table_name (ordering_key_column1, ordering_key_column2, ...); -- Create index on the Ordering Key Column if it doesnt exists ALTER TABLE table_name REPLICA IDENTITY USING INDEX orderingkey_index; ``` # Upgrading the PostgreSQL Peer Of An Ongoing CDC Mirror Source: https://docs.peerdb.io/bestpractices/postgres-upgrade This is a guide on how you can upgrade your PostgreSQL instance that is part of an ongoing CDC mirror. For PeerDB Cloud users: Note that one of the steps below requires the intervention of [PeerDB support](https://slack.peerdb.io). ## Steps 1. Create a dummy table, and [add the table to all mirrors](/features/edit-mirror#adding-tables) ```sql theme={null} CREATE TABLE _peerdb_heartbeat ( id SERIAL PRIMARY KEY, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 2. Make sure ALL writes to the database stop. In other words, put the application in maintenance/downtime. 3. Add a row to the dummy table using : ```sql theme={null} INSERT INTO _peerdb_heartbeat DEFAULT VALUES; ``` 4. Wait for PeerDB to catch up. You can check the syncs of the mirrors in PeerDB UI in the **Overview** tab. 5. [Pause](/features/pause-mirror) all mirrors. 6. Save the name of your replication slot before dropping it, then upgrade your PostgreSQL instance. 7. **If you are using PeerDB OSS**: Set the `last_offset` field of the mirrors to 0 in the `metadata_last_sync_state` table in the `catalog` Postgres container. You can `psql` into that container and run : ```sql theme={null} UPDATE metadata_last_sync_state SET last_offset = 0 WHERE job_name = ; ``` for every mirror which has this Postgres instance as its source peer. **If you are using PeerDB Cloud**: Contact PeerDB Support on [PeerDB Slack](https://slack.peerdb.io). 8. After the upgrade is complete, create new logical replication slots for the mirrors. It is very important that the name of these slots are the same as the previous slots which the mirrors were using. If PeerDB created the replication slot of a mirror, then the slot name will be `peerflow_slot_`. If you provided the replication slot name, then you should use that name (as stored earlier in step 6). ```sql theme={null} -- If PeerDB created the replication slot: SELECT pg_create_logical_replication_slot('peerflow_slot_', 'pgoutput'); -- If you provided the replication slot name: SELECT pg_create_logical_replication_slot('', 'pgoutput'); ``` 9. Resume all mirrors. 10. Remove application from maintenance. 11. Check if the mirrors are syncing correctly. # BigQuery Setup Guide Source: https://docs.peerdb.io/connect/bigquery BigQuery as a **destination** is deprecated and no longer actively maintained. It remains fully functional and no code is currently being removed. For new mirrors, we recommend ClickHouse, ClickHouse Cloud, or Postgres as the destination. Note that BigQuery remains a **supported source**. 1. [Create a dedicated Service Account](https://cloud.google.com/iam/docs/keys-create-delete#iam-service-account-keys-create-console) for PeerDB through Google Cloud Console and specify `BigQuery Data Editor`, `BigQuery Data Viewer`, `BigQuery Job User`, `BigQuery Resource Viewer` 2. Add a key to the above created service account 3. Create a dataset dedicated to PeerDB with the name you prefer. 4. Using this service account key create the peer using PeerDB UI # ClickHouse Setup Guide Source: https://docs.peerdb.io/connect/clickhouse/clickhouse PeerDB is now natively integrated with ClickHouse Cloud! We recently announced the Postgres CDC connector in ClickPipes, now available in Public Preview. This connector is fully powered by PeerDB. As a ClickHouse Cloud customer, you get a seamless, native experience for moving data from your Postgres databases to ClickHouse Cloud. Simply navigate to the **Data Sources** tab and add a Postgres source to start ingesting data. This is a document on setting a Clickhouse peer for PeerDB. PeerDB supports ClickHouse as a target for data replication. ### Steps on setting up the ClickHouse peer 1. Create a separate database for the ClickHouse peer called peerdb. This is where tables will be auto-created and synced by PeerDB. ```sql theme={null} CREATE DATABASE peerdb; ``` Or if you have a ClickHouse cluster ```sql theme={null} CREATE DATABASE peerdb ON CLUSTER '{cluster}' ENGINE = Replicated('/clickhouse/databases/peerdb/{uuid}/', '{replica}'); ``` 2. Create a Clickhouse user for PeerDB with the following permissions: ```sql theme={null} -- We recommend creating a separate user for PeerDB CREATE USER peerdb_user IDENTIFIED BY ''; -- PeerDB needs to create tables and insert data into the tables. -- Drop table permission is needed for DROP MIRROR support GRANT INSERT, SELECT, DROP, CREATE TABLE ON peerdb.* to peerdb_user; -- PeerDB uses an intermediary S3 stage for performance GRANT CREATE TEMPORARY TABLE, s3 on *.* to peerdb_user; -- For automatic column-addition on the tables in the mirror GRANT ALTER ADD COLUMN ON peerdb.* to peerdb_user; ``` on ClickHouse cluster ```sql theme={null} CREATE USER peerdb_user IDENTIFIED BY '' ON CLUSTER '{cluster}'; GRANT INSERT, SELECT, DROP, CREATE TABLE ON peerdb.* to peerdb_user ON CLUSTER '{cluster}'; GRANT CREATE TEMPORARY TABLE, s3 on *.* to peerdb_user ON CLUSTER '{cluster}'; GRANT ALTER ADD COLUMN ON peerdb.* to peerdb_user ON CLUSTER '{cluster}'; ``` When selecting the ReplicatedReplacingMergeTree/ReplicatedMergeTree table engine you need change [default settings](https://clickhouse.com/docs/operations/settings/settings#database_replicated_allow_replicated_engine_arguments). ``` 2 ``` If you need to whitelist PeerDB IPs in Clickhouse Cloud, you can find the IPs of your PeerDB instance [here](/peerdb-cloud/ip-table). 3. ClickHouse has several ports exposed, and PeerDB requires to use a port that exposes the `ClickHouse native protocol` which by default is `9440` for the secure TLS-enabled port and `9000` for the non-TLS port. If the default ports have been changed, please adjust them accordingly. We do not recommend using the non-TLS enabled port for ClickHouse. If this is needed, please also check the `Disable TLS?` option. 4. If you are using PeerDB OSS, we use [MinIO](https://min.io/) as the internal transient stage. You might need to adjust your firewall rules to allow connections from ClickHouse to MinIO. In [PeerDB Cloud](https://app.peerdb.cloud/), we abstract all of this from you. If you run into issues, please reach out to [support@peerdb.io](mailto:support@peerdb.io) or join our Slack [channel](https://slack.peerdb.io). 5. Create the ClickHouse Peer through PeerDB UI Enter all the details `Name`, `Host`, `Port` (as determined above), `User`, `Password` and `Database`. 6. Click on `Validate` to ensure that the connection is successful. 7. Click on `Create` to create the ClickHouse Peer. ### Troubleshooting Here is a list of some validation errors that you might encounter and how to resolve them: **You may need to disable TLS** You may need to disable TLS. Validation errors such as the following may occur if the connection is not successful: ``` failed to ping to Clickhouse peer: tls: first record does not look like a TLS handshake ``` **EOF** If you see the following error, it means that the connection to the ClickHouse peer could not be established: ``` failed to open connection to Clickhouse peer: failed to ping to Clickhouse peer: EOF ``` Please try the following: 1. Ensure that the PeerDB IP is whitelisted in the ClickHouse server. If you are using PeerDB Cloud, you can find the IPs of your PeerDB instance [here](/peerdb-cloud/ip-table). 2. You may need to enable TLS. **Stuck on Validating...** This means the port entered is incorrect. Please ensure that the port is correct and the ClickHouse server is reachable from the PeerDB server. # ClickHouse Cloud Setup Guide Source: https://docs.peerdb.io/connect/clickhouse/clickhouse-cloud PeerDB is now natively integrated with ClickHouse Cloud! We recently announced the Postgres CDC connector in ClickPipes, now available in Public Preview. This connector is fully powered by PeerDB. As a ClickHouse Cloud customer, you get a seamless, native experience for moving data from your Postgres databases to ClickHouse Cloud. Simply navigate to the **Data Sources** tab and add a Postgres source to start ingesting data. This is a document on setting a [Clickhouse Cloud](https://clickhouse.com/cloud) peer for PeerDB. If you are using ClickHouse Cloud, we recommend using [PeerDB Cloud](https://app.peerdb.cloud/), which comes with free trial and highly competitive pricing and PeerDB Cloud is specifically designed for ClickHouse Cloud. ### Steps on setting up the ClickHouse Cloud peer Head over the **SQL Console** in the ClickHouse Cloud UI. #### Permissions 1. Create a separate database for the ClickHouse peer called peerdb. This is where tables will be auto-created and synced by PeerDB. ```sql theme={null} CREATE DATABASE peerdb; ``` 2. Create a Clickhouse user for PeerDB with the following permissions: ```sql theme={null} -- We recommend creating a separate user for PeerDB CREATE USER peerdb_user IDENTIFIED BY ''; -- PeerDB needs to create tables and insert data into the tables. -- Drop table permission is needed for DROP MIRROR support GRANT INSERT, SELECT, DROP, CREATE TABLE ON peerdb.* to peerdb_user; -- PeerDB uses an intermediary S3 stage for performance GRANT CREATE TEMPORARY TABLE, s3 on *.* to peerdb_user; -- For automatic column-addition on the tables in the mirror GRANT ALTER ADD COLUMN ON peerdb.* to peerdb_user; ``` #### Whitelist PeerDB Cloud IPs If you are using PeerDB Cloud, you need to whitelist the PeerDB Cloud IPs in the ClickHouse Cloud settings tab. You can find the IPs of your PeerDB instance [here](/peerdb-cloud/ip-table). 3. ClickHouse has several ports exposed, and PeerDB requires to use a port that exposes the `ClickHouse native protocol` which by default is `9440` for the secure TLS-enabled port and `9000` for the non-TLS port. If the default ports have been changed, please adjust them accordingly. We do not recommend using the non-TLS enabled port for ClickHouse. If this is needed, please also check the `Disable TLS?` option when creating the Clickhouse peer (as seen below). 4. If you are using [PeerDB OSS](https://github.com/PeerDB-io/peerdb), we use [MinIO](https://min.io/) as the internal transient stage. You might need to adjust your firewall rules to **allow connections from ClickHouse to MinIO**. In [PeerDB Cloud](https://app.peerdb.cloud/), we abstract all of this from you. If you run into issues, please reach out to [support@peerdb.io](mailto:support@peerdb.io) or join our Slack [channel](https://slack.peerdb.io). 5. Create the ClickHouse Peer through PeerDB UI Enter all the details `Name`, `Host`, `Port` (as determined above), `User`, `Password` and `Database`. 6. Click on `Validate` to ensure that the connection is successful. 7. Click on `Create` to create the ClickHouse Peer. ### Troubleshooting Here is a list of some validation errors that you might encounter and how to resolve them: **You may need to disable TLS** You may need to disable TLS. Validation errors such as the following may occur if the connection is not successful: ``` failed to ping to Clickhouse peer: tls: first record does not look like a TLS handshake ``` **EOF** If you see the following error, it means that the connection to the ClickHouse peer could not be established: ``` failed to open connection to Clickhouse peer: failed to ping to Clickhouse peer: EOF ``` Please try the following: 1. Ensure that the PeerDB IP is whitelisted in the ClickHouse server. If you are using PeerDB Cloud, you can find the IPs of your PeerDB instance [here](/peerdb-cloud/ip-table). 2. You may need to enable TLS. **Stuck on Validating...** This means the port entered is incorrect. Please ensure that the port is correct and the ClickHouse server is reachable from the PeerDB server. # CockroachDB Source Setup Guide Source: https://docs.peerdb.io/connect/cockroachdb This is a guide on how to set up a CockroachDB peer which you can use as a source for replication in PeerDB. It applies to self-hosted CockroachDB as well as CockroachDB Cloud. ## Supported CockroachDB versions PeerDB's test suite runs against CockroachDB v24.1, v25.4 (LTS) and v26.2. Older versions may work but are not tested. Snapshot timestamp protection (described below) requires v24.1 or later. Let's create a dedicated user for PeerDB with the necessary permissions. Connect to your CockroachDB cluster and run the following SQL commands: 1. Create a dedicated user for PeerDB: ```sql theme={null} CREATE USER peerdb_user WITH PASSWORD 'some-password'; ``` 2. Grant read access on the tables you want to replicate. The following example shows permissions for the `public` schema. Repeat these commands for each schema containing tables you want to replicate: ```sql theme={null} GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO peerdb_user; ``` 3. If you plan to use Change Data Capture (CDC), also grant the `CHANGEFEED` privilege on the tables you want to replicate: ```sql theme={null} GRANT CHANGEFEED ON ALL TABLES IN SCHEMA "public" TO peerdb_user; ``` 4. Mirror validation reads cluster settings to check that rangefeeds are enabled, which requires the `VIEWCLUSTERSETTING` privilege: ```sql theme={null} GRANT SYSTEM VIEWCLUSTERSETTING TO peerdb_user; ``` 5. Optionally, grant `REPLICATION` so PeerDB can protect the initial snapshot's timestamp from garbage collection during long loads (see [snapshot timestamp protection](#snapshot-timestamp-protection)). Without it, PeerDB logs a warning and the snapshot relies on `gc.ttlseconds` alone: ```sql theme={null} GRANT SYSTEM REPLICATION TO peerdb_user; ``` On insecure (non-TLS) clusters, the `root` user can connect without a password. In that case you can leave the password empty when creating the peer. PeerDB performs CDC from CockroachDB using [changefeeds](https://www.cockroachlabs.com/docs/stable/change-data-capture-overview), which require rangefeeds to be enabled on the cluster. See the [changefeed prerequisites](https://www.cockroachlabs.com/docs/stable/create-and-configure-changefeeds) in the CockroachDB docs for details. 1. On self-hosted CockroachDB, enable rangefeeds by running: ```sql theme={null} SET CLUSTER SETTING kv.rangefeed.enabled = true; ``` This requires the `admin` role or the `MODIFYCLUSTERSETTING` privilege. 2. On CockroachDB Cloud Standard and Basic clusters, rangefeeds are enabled by default and no action is needed. Mirror validation fails with an explanatory error if rangefeeds are disabled and the mirror does CDC. CockroachDB garbage-collects old row versions after the interval configured by the [`gc.ttlseconds`](https://www.cockroachlabs.com/docs/stable/configure-replication-zones#gc-ttlseconds) zone variable. PeerDB's changefeed cursor can only resume from a timestamp that has not yet been garbage collected. If PeerDB is down for longer than `gc.ttlseconds`, the cursor expires and the mirror needs a resync. The same window bounds the initial snapshot, which reads all tables `AS OF SYSTEM TIME` at one fixed timestamp. Mirror validation requires an effective `gc.ttlseconds` of at least 24 hours (86400 seconds) on every replicated table. The effective value is the minimum across the table's zone configuration and any partition zone configurations, since a partition-level setting overrides the table's. When validation fails, the error message names the table or partition and includes the exact `CONFIGURE ZONE` statement to raise the setting. For example, to allow up to 48 hours: ```sql theme={null} ALTER TABLE my_table CONFIGURE ZONE USING gc.ttlseconds = 172800; ``` See the [CockroachDB docs](https://www.cockroachlabs.com/docs/stable/configure-replication-zones#gc-ttlseconds) for default values, which differ between self-hosted and CockroachDB Cloud cluster types. On cluster types whose default is below 24 hours, raise the zone configuration for the replicated tables before creating the mirror. Increasing `gc.ttlseconds` causes CockroachDB to retain more row versions, which increases storage usage and can slow down reads on frequently updated tables. Pick a value that covers your expected downtime while accounting for these costs. TLS is enabled by default for CockroachDB peers. CockroachDB Cloud clusters and secure self-hosted clusters require it. 1. If your cluster's certificate is not signed by a publicly trusted certificate authority, provide the cluster's root CA certificate (`root_ca`). For CockroachDB Cloud, you can download the CA certificate from the cluster's **Connect** dialog in the Cloud Console. 2. If you connect through a load balancer or tunnel where the hostname you dial differs from the hostname in the server certificate, set **TLS Hostname** (`tls_host`) to the certificate's hostname. 3. For certificate-based client authentication, provide a client certificate and private key (`client_tls`). 4. For insecure self-hosted clusters, turn on **Disable TLS?** (`disable_tls`). `skip_cert_verification` disables server certificate verification and should only be used for testing. You can create the CockroachDB peer through the PeerDB UI or via SQL. **Using the UI:** 1. Head over to the PeerDB UI and click on **Create Peer**. Select **CockroachDB** as the source. 2. Fill in the connection details of your CockroachDB cluster, using the user you created earlier. The default port is `26257`. 3. For TLS clusters, provide the **Root Certificate** if needed. For insecure clusters, turn on **Disable TLS?**. 4. To connect through an SSH tunnel, turn on **Configure SSH Tunnel** and fill in the SSH host, port, user and credentials. 5. Click on **Validate** and once that's green, you can go ahead and click on **Create** to create the peer! **Using SQL:** ```sql theme={null} CREATE PEER cockroachdb_peer FROM COCKROACHDB WITH ( host = '', port = '26257', user = 'peerdb_user', password = '', database = '', root_ca = '' ); ``` See [Creating Peers](/sql/commands/create-peer#cockroachdb-peer) for the full list of options. ## How replication works CDC is the recommended mirror type for CockroachDB sources. A CDC mirror runs in two phases: 1. **Initial snapshot.** PeerDB captures a cluster logical timestamp and reads table data in consistent partitioned chunks using `AS OF SYSTEM TIME` queries at that timestamp, with watermark-based partitioning. 2. **Change streaming.** A sinkless changefeed then streams changes starting from that same timestamp, so the snapshot and the change stream line up exactly. Resolved timestamps from the changefeed drive checkpointing, so PeerDB can resume from where it left off after a restart. ### Delivery semantics Delivery is at least once. Each sync cycle resumes the changefeed from the last checkpointed resolved timestamp, so a bounded window of already-delivered events can be re-delivered, both between sync cycles and after restarts. ClickHouse destination tables use the ReplacingMergeTree engine with a version column, so re-delivered rows collapse to a single row on merge. ### Snapshot timestamp protection While the initial snapshot runs, PeerDB automatically attempts to protect the snapshot timestamp from garbage collection. The protection is refreshed periodically while the snapshot runs, released when the initial load completes, and expires on its own after a bounded window as a safety guard. Protection requires CockroachDB v24.1 or later, the `REPLICATION` privilege for the PeerDB user, and on v26.1 or later the `allow_unsafe_internals` session setting or its cluster-wide override. If protection cannot be engaged, PeerDB logs a warning and proceeds; the initial load then relies on `gc.ttlseconds` alone. ## Monitoring The CockroachDB connector emits the following metrics through PeerDB's [OpenTelemetry metrics](/metrics/native-metrics), with the standard flow name attributes: | Metric | Description | | ------------------------------ | ---------------------------------------------------------------------------------------- | | `cockroachdb_resolved_lag` | How many seconds the changefeed's resolved timestamp trails the current wall clock time. | | `cockroachdb_records_received` | Counter of changefeed data records received on the CDC pull path. | ## Connecting through a proxy Connection proxies with short idle timeouts (for example, haproxy defaults to around 30 seconds) can drop idle PeerDB connections between sync cycles. PeerDB reconnects automatically, but raising the proxy's idle timeout reduces reconnect noise in the logs. ## Limitations 1. Newly added columns are detected from changefeed data and propagated to the destination. Column drops, renames and type changes are not replicated and require a resync of the mirror. 2. `TRUNCATE` or `DROP` of a replicated table stops the mirror with a terminal error; recreate or resync the mirror afterwards. 3. Changefeeds with webhook or Kafka sinks are not supported; PeerDB only uses sinkless changefeeds. 4. If PeerDB is down for longer than the table's effective `gc.ttlseconds`, the changefeed cursor expires and the mirror needs a resync. # Confluent Cloud Setup Guide Source: https://docs.peerdb.io/connect/confluent-cloud Kafka as a destination (including the Confluent and Redpanda variants) is deprecated and no longer actively maintained. It remains fully functional and no code is currently being removed. For new mirrors, we recommend ClickHouse, ClickHouse Cloud, or Postgres as the destination. # Prerequisites To connect Confluent Cloud to PeerDB, you need a Confluent Cloud account with Kafka Server running. # Setup Instructions ## Gather the following details from Confluent Cloud ```toml theme={null} bootstrap.servers=:9092 security.protocol=SASL_SSL sasl.mechanisms=PLAIN sasl.username={{ CLUSTER_API_KEY }} sasl.password={{ CLUSTER_API_SECRET }} ```