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.Detailed Explanation: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.

🛡️ Preventing 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.

Setup Steps

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';

Enable heartbeats in your pipeline’s advanced settings.

Troubleshooting Guide

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

Table Existence

Verify test_heartbeat_table exists in your database

Publication Configuration

-- 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';

Permission Issues

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';

Additional Best Practices

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)

Useful Diagnostic Commands

-- 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;

​🛡️ Preventing WAL Growth with Heartbeats

​Setup Steps

​Troubleshooting Guide

🛡️ Preventing WAL Growth with Heartbeats

Setup Steps

Troubleshooting Guide