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.
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
_peerdb_version
within each id partition. Here’s an example:
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 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.
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:
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:
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
.
PRIMARY KEY
isn’t specified, the Ordering Key automatically becomes the PRIMARY KEY
and defines the columns in the sparse index.
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.
PRIMARY KEY
to define the ordering key in ClickHouse tables, but you can change it using the following methods:
CREATE TABLE IF NOT EXISTS
behind the scenes and continues to ingest data into the new table.
_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:
_peerdb_is_deleted
is 0 are visible when querying the table.
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.