> ## Documentation Index
> Fetch the complete documentation index at: https://artie.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Preventing WAL growth on RDS Postgres

> Learn how to prevent Write-Ahead Log (WAL) growth issues in your AWS RDS Postgres database, especially for low-traffic or test environments.

Last updated: 03/26/2025

<Accordion title="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:

  1. Every database change is first written to the WAL
  2. The changes are then applied to the actual database files
  3. 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
</Accordion>

<Accordion title="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](https://www.morling.dev/blog/insatiable-postgres-replication-slot/), 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.
</Accordion>

## 🛡️ 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

1. Create and configure the heartbeat table:

```sql theme={null}
-- 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';
```

2. Enable heartbeats in your pipeline's advanced settings.

### Troubleshooting Guide

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

<Steps>
  <Step title="Table Existence">
    Verify `test_heartbeat_table` exists in your database
  </Step>

  <Step title="Publication Configuration">
    ```sql theme={null}
    -- 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';
    ```
  </Step>

  <Step title="Permission Issues">
    Confirm the service account has proper write permissions
  </Step>

  <Step title="Long-Running Queries">
    ```sql theme={null}
    -- 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';
    ```
  </Step>
</Steps>

<Accordion title="Additional Best Practices">
  To further protect your RDS instance, implement these measures:

  1. **Monitoring**
     * Set up alerts for `free_storage_space`
     * Monitor WAL growth rates

  2. **Database Configuration**
     * Set appropriate `statement_timeout`
     * Configure `max_slot_wal_keep_size` (default is -1 for unlimited)
     * Enable storage autoscaling ([AWS Guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIOPS.StorageTypes.html#USER_PIOPS.ModifyingAutoscaling))
</Accordion>

<Accordion title="Useful Diagnostic Commands">
  ```sql theme={null}
  -- 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;
  ```
</Accordion>