PostgreSQL Heartbeats

In low-traffic or intermittent databases, the log can become stale due to inactivity, which may cause the Connector to miss change events. Heartbeats (enabled by default) help mitigate this.

However, in PostgreSQL, excessive log growth and retention can also occur, consuming more database disk space than necessary.

To mitigate this, a heartbeat table can be created in the source database which the heartbeat process — running within the Connector — periodically updates. This generates a change event in PostgreSQL, keeping the replication slot active and ensuring logs do not grow out of proportion.

Setup Heartbeats

-- Create the streamkap schema
CREATE SCHEMA IF NOT EXISTS streamkap;

-- Switch to the streamkap schema
SET search_path TO streamkap;

-- Create the heartbeat table with id, text, and last_update fields
CREATE TABLE streamkap_heartbeat (
    id SERIAL PRIMARY KEY,
    text TEXT,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grant permission to the Streamkap user
GRANT USAGE ON SCHEMA streamkap TO STREAMKAP_USER;
GRANT SELECT, INSERT, UPDATE ON TABLE streamkap_heartbeat TO STREAMKAP_USER;

-- Insert the first row into the heartbeat table
INSERT INTO streamkap_heartbeat (text) VALUES ('test_heartbeat');

📘

Heartbeat tables and PostgreSQL publications

If the streamkap_pub publication created during PostgreSQL Setup was for specific tables e.g. CREATE PUBLICATION streamkap_pub FOR TABLE table1, table2, table3, ...; instead of FOR ALL TABLES;, you must add the heartbeat table to the publication ALTER PUBLICATION streamkap_pub ADD TABLE streamkap.streamkap_heartbeat;.

Verify

• Look for an entry in the heartbeat table with an updated timestamp after some minutes to verify this is working.