Last updated: 04/17/2024

​
Enable heartbeats for idle databases

You only need to enable this feature if your database is low traffic or idle for long periods of time, which are primarily test databases. This feature is not necessary for active databases because the WAL growth will reset as soon as there are data changes from the table(s) you are observing.

  1. To turn this on, you’ll need to run the following script:
CREATE TABLE test_heartbeat_table (id text PRIMARY KEY, ts timestamp);
-- Grant access to the heartbeat table
GRANT UPDATE ON TABLE test_heartbeat_table TO artie_transfer;
-- Then insert one row into this table.
-- Artie's periodic pings will be this:
-- UPDATE test_heartbeat_table set ts = now() WHERE id = '1';
-- Such that we never end up adding additional rows.
INSERT INTO test_heartbeat_table (id, ts) VALUES (1, NOW());
  1. Then click Enable heartbeats in deployment advanced settings.

​
If you are still seeing WAL growth with heartbeats enabled

  1. Does your heartbeats table (test_heartbeat_table) actually exist?
  2. Is the heartbeats table included in your Postgres publications?
-- pubname should be `dbz_publication` unless you changed it under deployment settings
SELECT pubname, tablename FROM pg_publication_tables WHERE tablename = 'test_heartbeat_table';
  1. Does the service account have permissions to write to the table?
  2. Are there any long-running queries that may prevent your replication slot from being advanced?
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '1 minute';

Was this page helpful?

Enable BigQuery PartitioningTables without primary key(s)