Preventing WAL growth on RDS Postgres

Last updated: 03/26/2025

What is WAL?

Write-Ahead Logging (WAL) is Postgres’s built-in mechanism for ensuring data integrity and enabling change data capture (CDC). Here’s how it works:

Every database change is first written to the WAL
The changes are then applied to the actual database files
This approach ensures data durability and enables reliable replication

While WAL is essential for data integrity and replication, uncontrolled WAL growth can lead to:

Replication slot overflow
Database storage exhaustion
Potential database downtime

Why is WAL growth an issue only on RDS?

TL;DR: AWS RDS uses internal “heartbeats” that generate WAL entries every 5 minutes, which can cause significant storage issues on idle databases.Details:As explained by Gunnar Morling in his blog post, AWS RDS writes a heartbeat to an internal rdsadmin table every 5 minutes. Here’s why this matters:

Each WAL segment is 64MB by default
Each heartbeat creates a new WAL segment
On idle databases, this means:
- 64MB of WAL growth every 5 minutes
- ~18.4GB of WAL growth per day
- Potential replication slot overflow if left unchecked

This is particularly problematic for:

Test databases
Low-traffic environments
Idle databases

The issue doesn’t affect active databases because their WAL is constantly being drained by regular data changes.

Prevent WAL growth with heartbeats

This solution is specifically designed for low-traffic or idle databases. Active databases don’t need this feature as their WAL naturally resets with regular data changes.

Set up

Create and configure the heartbeat table:

-- Create the heartbeat table
CREATE TABLE test_heartbeat_table (id text PRIMARY KEY, ts timestamp);

-- Grant necessary permissions
GRANT SELECT, UPDATE ON TABLE test_heartbeat_table TO artie;

-- Insert initial record
INSERT INTO test_heartbeat_table (id, ts) VALUES (1, NOW());

-- Once enabled, Artie will run this command every 5 minutes to update the heartbeat table:
UPDATE test_heartbeat_table set ts = now() where id = '1';

In your pipeline’s source settings, turn on Enable heartbeats under Postgres settings.

Troubleshooting

If you’re still experiencing WAL growth after enabling heartbeats, check these common issues:

Table exists

Verify test_heartbeat_table exists in your database

Publication

-- Check if the table is included in your publication
-- For Artie pipelines, the publication name should default
-- to `dbz_publication` (unless changed under Pipeline advanced settings)
SELECT pubname, tablename
FROM pg_publication_tables
WHERE tablename = 'test_heartbeat_table';

Permissions

Confirm the service account has proper write permissions

Long-running queries

-- Check for queries that might block replication
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';

More safeguards

To further protect your RDS instance, implement these measures:

Monitoring
- Set up alerts for free_storage_space
- Monitor WAL growth rates
Database configuration
- Set appropriate statement_timeout
- Configure max_slot_wal_keep_size (default is -1 for unlimited)
- Enable storage autoscaling (AWS guide)

Diagnostic queries

Dropping Artie’s replication slot discards changes Artie hasn’t read yet, so the tables need a backfill. Pause the pipeline first.

-- View all replication slots
SELECT * FROM pg_replication_slots;

-- Remove a replication slot
SELECT pg_drop_replication_slot('REPLICATION_SLOT_NAME');

-- Check replication slot size
SELECT
  slot_name,
  wal_status,
  pg_size_pretty(
    pg_wal_lsn_diff(
      pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
  active,
  restart_lsn 
FROM pg_replication_slots;

​Prevent WAL growth with heartbeats

​Set up

​Troubleshooting

Prevent WAL growth with heartbeats

Set up

Troubleshooting