Replication from Postgres to ClickHouse can lose DELETEs for tables with a compound PK, if the same PeerDB batch includes a CREATE with overlapping columns

[danxmoran]

We've been seeing this behavior across multiple tables, but I just caught a concrete example.

Setup

In Postgres we have a process_user_roles table with a compound primary key:

ashbyhq_prd_main=> \d process_user_roles
                Table "public.process_user_roles"
        Column        |  Type   | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
 organization_id      | uuid    |           | not null |
 process_id           | uuid    |           | not null |
 user_id              | uuid    |           | not null |
 process_role_type_id | uuid    |           | not null |
 is_process_open      | boolean |           | not null | true
Indexes:
    "process_user_roles_pkey" PRIMARY KEY, btree (organization_id, process_id, user_id, process_role_type_id)

The table has REPLICA IDENTITY FULL

We are replicating the table to ClickHouse Cloud via the Postgres CDC ClickPipe. We're using a ReplacingMergeTree and have customized the table sort key to include a leading organization_id:

CREATE TABLE ashby.process_user_roles
(
    `organization_id` UUID,
    `process_id` UUID,
    `user_id` UUID,
    `process_role_type_id` UUID,
    `is_process_open` Bool,
    `_peerdb_synced_at` DateTime64(9) DEFAULT now64(),
    `_peerdb_is_deleted` Int8,
    `_peerdb_version` Int64
)
ENGINE = SharedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', _peerdb_version)
PRIMARY KEY (organization_id, process_id, user_id, process_role_type_id)
ORDER BY (organization_id, process_id, user_id, process_role_type_id)
SETTINGS allow_nullable_key = 1, index_granularity = 8192

The problem

We ~frequently see that when process_user_roles rows are deleted in Postgres, the deletion is not replicated to ClickHouse.

This produces inaccurate reports for our own customers, who then send us support tickets. Repeated instances of this have caused customer (and internal) frustration. We've attempted to work around this via tools/scripts that manually delete "orphaned" process_user_roles rows in ClickHouse, but the approach is not scalable, and it still typically requires a user to write in and notify us of an issue.

Example

Running this query:

select * from process_user_roles final
where organization_id = '868dcb5b-0628-48f1-b4f7-fed828a37e35'
and process_id = '3eccdad1-70a8-40f0-bfde-779533ea5b37'
and _peerdb_is_deleted = 0;

Currently produces:

organization_id	process_id	user_id	process_role_type_id	is_process_open	_peerdb_synced_at	_peerdb_is_deleted	_peerdb_version
868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	386db64e-2640-4033-9968-6dbf4d39216e	7a131700-d20b-4bb6-9922-bf1c56a10b4e	false	2026-04-06 22:24:56.748000000	0	1775514275517349036
868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	c744e0a1-bc06-4c96-9ab7-89b2f31b423f	7333e9f5-465c-472b-92e5-d4eb63ca354e	false	2026-04-06 22:24:56.748000000	0	1775514275517369024
868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	70c7eb72-4c69-43e0-b9ee-21fa8adf112d	acf6fdeb-8226-4bde-945e-4311db5932d6	false	2026-04-06 22:24:56.748000000	0	1775514275517358833
868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	4876d7a5-2066-4a11-ab61-046d06c5e0b5	acf6fdeb-8226-4bde-945e-4311db5932d6	true	2026-04-01 14:56:01.996000000	0	1775055337376245400

The last row of that result set should have been deleted. It no longer exists in Postgres:

ashbyhq_prd_main=> select * from process_user_roles where organization_id = '868dcb5b-0628-48f1-b4f7-fed828a37e35' and process_id = '3eccdad1-70a8-40f0-bfde-779533ea5b37';
           organization_id            |              process_id              |               user_id                |         process_role_type_id         | is_process_open
--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+-----------------
 868dcb5b-0628-48f1-b4f7-fed828a37e35 | 3eccdad1-70a8-40f0-bfde-779533ea5b37 | 386db64e-2640-4033-9968-6dbf4d39216e | 7a131700-d20b-4bb6-9922-bf1c56a10b4e | f
 868dcb5b-0628-48f1-b4f7-fed828a37e35 | 3eccdad1-70a8-40f0-bfde-779533ea5b37 | 70c7eb72-4c69-43e0-b9ee-21fa8adf112d | acf6fdeb-8226-4bde-945e-4311db5932d6 | f
 868dcb5b-0628-48f1-b4f7-fed828a37e35 | 3eccdad1-70a8-40f0-bfde-779533ea5b37 | c744e0a1-bc06-4c96-9ab7-89b2f31b423f | 7333e9f5-465c-472b-92e5-d4eb63ca354e | f
(3 rows)

Investigation

I found the deletion record for the referenced row in our _peerdb_raw_mirror_* table, so I believe logical replication is working as intended. I ran this query:

select * from _peerdb_raw_mirror_5b90d5a1__e328__4aaf__945c__e39f9b35a42f
where _peerdb_destination_table_name = 'process_user_roles'
and JSONExtractString(_peerdb_data, 'organization_id') = '868dcb5b-0628-48f1-b4f7-fed828a37e35'
and JSONExtractString(_peerdb_data, 'process_id') = '3eccdad1-70a8-40f0-bfde-779533ea5b37'
and JSONExtractString(_peerdb_data, 'user_id') = '4876d7a5-2066-4a11-ab61-046d06c5e0b5'
limit 1;

Which returned a single row:

_peerdb_uid	_peerdb_timestamp	_peerdb_destination_table_name	_peerdb_data	_peerdb_record_type	_peerdb_match_data	_peerdb_batch_id	_peerdb_unchanged_toast_columns
78e9e08f-f67f-4fa3-99ba-1be47401c524	1775487530152639807	process_user_roles	{"is_process_open":true,"organization_id":"868dcb5b-0628-48f1-b4f7-fed828a37e35","process_id":"3eccdad1-70a8-40f0-bfde-779533ea5b37","process_role_type_id":"acf6fdeb-8226-4bde-945e-4311db5932d6","user_id":"4876d7a5-2066-4a11-ab61-046d06c5e0b5"}	2	{"is_process_open":true,"organization_id":"868dcb5b-0628-48f1-b4f7-fed828a37e35","process_id":"3eccdad1-70a8-40f0-bfde-779533ea5b37","process_role_type_id":"acf6fdeb-8226-4bde-945e-4311db5932d6","user_id":"4876d7a5-2066-4a11-ab61-046d06c5e0b5"}	631026

I then searched for other "relevant" rows with the same _peerdb_batch_id:

select * from _peerdb_raw_mirror_5b90d5a1__e328__4aaf__945c__e39f9b35a42f
where _peerdb_batch_id = 631026
and _peerdb_destination_table_name = 'process_user_roles'
and JSONExtractString(_peerdb_data, 'organization_id') = '868dcb5b-0628-48f1-b4f7-fed828a37e35'
and JSONExtractString(_peerdb_data, 'process_id') = '3eccdad1-70a8-40f0-bfde-779533ea5b37';

Which returned two rows, the deletion & a new insertion:

_peerdb_uid	_peerdb_timestamp	_peerdb_destination_table_name	_peerdb_data	_peerdb_record_type	_peerdb_match_data	_peerdb_batch_id	_peerdb_unchanged_toast_columns
78e9e08f-f67f-4fa3-99ba-1be47401c524	1775487530152639807	process_user_roles	{"is_process_open":true,"organization_id":"868dcb5b-0628-48f1-b4f7-fed828a37e35","process_id":"3eccdad1-70a8-40f0-bfde-779533ea5b37","process_role_type_id":"acf6fdeb-8226-4bde-945e-4311db5932d6","user_id":"4876d7a5-2066-4a11-ab61-046d06c5e0b5"}	2	{"is_process_open":true,"organization_id":"868dcb5b-0628-48f1-b4f7-fed828a37e35","process_id":"3eccdad1-70a8-40f0-bfde-779533ea5b37","process_role_type_id":"acf6fdeb-8226-4bde-945e-4311db5932d6","user_id":"4876d7a5-2066-4a11-ab61-046d06c5e0b5"}	631026
7728bb81-8295-4a77-a590-5e249a8a477a	1775487530152646667	process_user_roles	{"is_process_open":true,"organization_id":"868dcb5b-0628-48f1-b4f7-fed828a37e35","process_id":"3eccdad1-70a8-40f0-bfde-779533ea5b37","process_role_type_id":"acf6fdeb-8226-4bde-945e-4311db5932d6","user_id":"70c7eb72-4c69-43e0-b9ee-21fa8adf112d"}	0		631026

I wondered why these two would mix in the same batch. Looking in our application code, I found that whenever we need to change the user_id values associated with a process_id in the table, we do the following in a single transaction:

1. Bulk DELETE all rows with the target process_id and with an "outdated" user_id
2. Bulk INSERT news rows with updated process/user ID pairs

In our ORM, it looks like this:

await this.saveNowOrLaterInTransaction(async (t) => {
  // delete users with their roles that aren't in those that should be set
  const destroyedCount = await _ProcessUserRoles.destroy({
    where: {
      organizationId: this.authorizationContext.organizationId,
      processId: application.id,
      [Op.and]: userRoles.map((r) => ({ [Op.not]: r })),
    },
    transaction: t,
});

  // add those that might be missing
  const createdRoles = await _ProcessUserRoles.bulkCreate(
    userRoles.map((userRole) => ({
      organizationId: this.authorizationContext.organizationId,
      processId: application.id,
      ...userRole,
      isProcessOpen: application.isOpen(),
    })),
    {
      transaction: t,
      ignoreDuplicates: true,
    }
  );
}, transaction);

More data

I pulled the full batch of rows from the relevant PeerDB batch targeting process_user_roles:

select * from _peerdb_raw_mirror_5b90d5a1__e328__4aaf__945c__e39f9b35a42f
where _peerdb_batch_id = 631026
and _peerdb_destination_table_name = 'process_user_roles';

See it attached as a gzip CSV: process_user_roles_full_batch.csv.gz

[saisrirampur]

I tried to reproduce this issue from scratch on an empty table, but was not able to. However note that this was on a more recent PeerDB version (shared my exact ClickPipe details internally) cc @ilidemi @danxmoran

Exact Postgres commands I ran:

CREATE TABLE public.process_user_roles (
    organization_id uuid NOT NULL,
    process_id uuid NOT NULL,
    user_id uuid NOT NULL,
    process_role_type_id uuid NOT NULL,
    is_process_open boolean NOT NULL DEFAULT true,
    CONSTRAINT process_user_roles_pkey
        PRIMARY KEY (organization_id, process_id, user_id, process_role_type_id)
);

ALTER TABLE public.process_user_roles REPLICA IDENTITY FULL;

Then I created the ClickPipe on this empty table and waited for the initial sync to complete.

Seed data:

INSERT INTO public.process_user_roles (
    organization_id,
    process_id,
    user_id,
    process_role_type_id,
    is_process_open
)
VALUES
('868dcb5b-0628-48f1-b4f7-fed828a37e35', '3eccdad1-70a8-40f0-bfde-779533ea5b37', '4876d7a5-2066-4a11-ab61-046d06c5e0b5', 'acf6fdeb-8226-4bde-945e-4311db5932d6', true),
('868dcb5b-0628-48f1-b4f7-fed828a37e35', '3eccdad1-70a8-40f0-bfde-779533ea5b37', '386db64e-2640-4033-9968-6dbf4d39216e', '7a131700-d20b-4bb6-9922-bf1c56a10b4e', false),
('868dcb5b-0628-48f1-b4f7-fed828a37e35', '3eccdad1-70a8-40f0-bfde-779533ea5b37', 'c744e0a1-bc06-4c96-9ab7-89b2f31b423f', '7333e9f5-465c-472b-92e5-d4eb63ca354e', false);

After the seed rows were replicated, I ran this transaction:

BEGIN;

DELETE FROM public.process_user_roles
WHERE organization_id = '868dcb5b-0628-48f1-b4f7-fed828a37e35'
  AND process_id = '3eccdad1-70a8-40f0-bfde-779533ea5b37'
  AND user_id = '4876d7a5-2066-4a11-ab61-046d06c5e0b5'
  AND process_role_type_id = 'acf6fdeb-8226-4bde-945e-4311db5932d6';

INSERT INTO public.process_user_roles (
    organization_id,
    process_id,
    user_id,
    process_role_type_id,
    is_process_open
)
VALUES
('868dcb5b-0628-48f1-b4f7-fed828a37e35', '3eccdad1-70a8-40f0-bfde-779533ea5b37', '70c7eb72-4c69-43e0-b9ee-21fa8adf112d', 'acf6fdeb-8226-4bde-945e-4311db5932d6', true)
ON CONFLICT DO NOTHING;

COMMIT;

Results on Postgres and ClickHouse matched:

postgres=# SELECT * FROM public.process_user_roles;
           organization_id            |              process_id              |               user_id                |         process_role_type_id         | is_process_open
--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+-----------------
 868dcb5b-0628-48f1-b4f7-fed828a37e35 | 3eccdad1-70a8-40f0-bfde-779533ea5b37 | 386db64e-2640-4033-9968-6dbf4d39216e | 7a131700-d20b-4bb6-9922-bf1c56a10b4e | f
 868dcb5b-0628-48f1-b4f7-fed828a37e35 | 3eccdad1-70a8-40f0-bfde-779533ea5b37 | c744e0a1-bc06-4c96-9ab7-89b2f31b423f | 7333e9f5-465c-472b-92e5-d4eb63ca354e | f
 868dcb5b-0628-48f1-b4f7-fed828a37e35 | 3eccdad1-70a8-40f0-bfde-779533ea5b37 | 70c7eb72-4c69-43e0-b9ee-21fa8adf112d | acf6fdeb-8226-4bde-945e-4311db5932d6 | t
(3 rows)

select * from public_process_user_roles final
where organization_id = '868dcb5b-0628-48f1-b4f7-fed828a37e35'
and process_id = '3eccdad1-70a8-40f0-bfde-779533ea5b37'
and _peerdb_is_deleted = 0;

868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	70c7eb72-4c69-43e0-b9ee-21fa8adf112d	acf6fdeb-8226-4bde-945e-4311db5932d6	true	2026-04-29 00:03:50.038000000	0	1777421026767689393
868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	386db64e-2640-4033-9968-6dbf4d39216e	7a131700-d20b-4bb6-9922-bf1c56a10b4e	false	2026-04-29 00:01:55.894000000	0	1777420912562333709
868dcb5b-0628-48f1-b4f7-fed828a37e35	3eccdad1-70a8-40f0-bfde-779533ea5b37	c744e0a1-bc06-4c96-9ab7-89b2f31b423f	7333e9f5-465c-472b-92e5-d4eb63ca354e	false	2026-04-29 00:01:55.894000000	0	1777420912562341266

[saisrirampur]

I also tried repeated swaps as below and still the same story.

DO $$
DECLARE
    i int;
    old_user uuid;
    new_user uuid;
BEGIN
    FOR i IN 1..100 LOOP
        old_user := CASE WHEN i % 2 = 1
            THEN '70c7eb72-4c69-43e0-b9ee-21fa8adf112d'::uuid
            ELSE '4876d7a5-2066-4a11-ab61-046d06c5e0b5'::uuid
        END;

        new_user := CASE WHEN i % 2 = 1
            THEN '4876d7a5-2066-4a11-ab61-046d06c5e0b5'::uuid
            ELSE '70c7eb72-4c69-43e0-b9ee-21fa8adf112d'::uuid
        END;

        DELETE FROM public.process_user_roles
        WHERE organization_id = '868dcb5b-0628-48f1-b4f7-fed828a37e35'
          AND process_id = '3eccdad1-70a8-40f0-bfde-779533ea5b37'
          AND user_id = old_user
          AND process_role_type_id = 'acf6fdeb-8226-4bde-945e-4311db5932d6';

        INSERT INTO public.process_user_roles
        VALUES (
            '868dcb5b-0628-48f1-b4f7-fed828a37e35',
            '3eccdad1-70a8-40f0-bfde-779533ea5b37',
            new_user,
            'acf6fdeb-8226-4bde-945e-4311db5932d6',
            true
        )
        ON CONFLICT DO NOTHING;
    END LOOP;
END $$;

[danxmoran]

We now believe this is not a PeerDB issue, but instead a surprising behavior when using a sub-query in a DELETE that runs on multiple replicas. Thanks for the detailed investigation - closing this!