​
Mitigate slot growth with a heartbeat table

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.

In order to mitigate this, you can create a heartbeat table in the source database. 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.

​
Implementation

The below is an example of what we’re talking about. Ensure that the heartbeat table has required permissions to be a part of the mirror.

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;

Periodic update can be done with the below pg_cron command:

-- updates every 30 seconds
SELECT cron.schedule('*/30 * * * *', $$UPDATE _peerdb_heartbeat SET last_updated = CURRENT_TIMESTAMP$$);

Now, you can include it as part of the tables in the mirror either via the Create Mirror UI (table selector) or through the SQL Layer as shown below.

CREATE MIRROR heartbeat_mirror
FROM postgres_peer
TO warehouse_peer
WITH TABLE MAPPING (
public._peerdb_heartbeat: _peerdb_heartbeat_target,
-- other tables
)
WITH(
 ...
);
Datatype matrixOverview