Allow to turn off PostgreSQL archive_mode parameter when automatic backups are disabled

Greeting

Hi Yoni,

Thanks for sharing your query about PostgreSQL’s archive_mode in RDS. It’s clear you’re facing challenges with storage utilization due to WAL archiving when automatic backups are disabled. I understand how frustrating it can be to manage these situations, especially with heavy DML workloads filling up storage. Let’s break this down and work toward a practical solution or workaround for now. 😊

---

Clarifying the Issue

You’ve pointed out that PostgreSQL’s archive_mode cannot be turned off in Amazon RDS, even when automatic backups are disabled. This behavior leads to WAL (Write-Ahead Log) files accumulating and consuming storage unnecessarily, especially during high DML operations. Your reference to SQL Server’s SIMPLE recovery mode is a great analogy—having the option to disable archiving when backups are off would indeed simplify things.

AWS RDS enforces certain PostgreSQL settings, including archive_mode, to maintain reliability and support disaster recovery. While this behavior can be restrictive, there are effective alternatives that can provide similar control over storage and performance. Let’s explore a recipe for handling this effectively.

---

Key Terms

• WAL (Write-Ahead Logging): A mechanism to ensure data durability by recording changes before they are applied to the database.
• archive_mode: A PostgreSQL parameter that controls whether WAL files are archived for point-in-time recovery (PITR).
• DML (Data Manipulation Language): Operations like INSERT, UPDATE, and DELETE that modify data in the database.
• RDS Parameter Group: A collection of database parameters that can be applied to RDS instances to control behavior.

---

The Solution (Our Recipe)

Steps at a Glance:

1. Adjust WAL retention with wal_keep_size or max_wal_size.
2. Monitor WAL file accumulation using CloudWatch metrics.
3. Optimize DML operations to reduce excessive WAL generation.
4. Consider migrating to Amazon Aurora PostgreSQL for greater control over WAL management.
5. Evaluate cost implications and long-term scalability of solutions.

---

Step-by-Step Guide:

1. Adjust WAL Retention with wal_keep_size or max_wal_size:
   • Although you cannot turn off archive_mode, you can limit WAL retention by modifying these parameters in your RDS Parameter Group.
   • Example:

     ALTER SYSTEM SET wal_keep_size = '512MB';

     Ensure the parameter group is applied and restart the database if necessary.

---

2. Monitor WAL File Accumulation Using CloudWatch Metrics:
   • Track RDS metrics like DiskQueueDepth and FreeStorageSpace to identify when WAL files are accumulating.
   • Set up alarms to notify you before storage runs out:

     aws cloudwatch put-metric-alarm \
       --alarm-name "Low-Free-Storage-Alarm" \
       --metric-name FreeStorageSpace \
       --namespace AWS/RDS \
       --statistic Average \
       --threshold 20 \
       --comparison-operator LessThanThreshold \
       --dimensions Name=DBInstanceIdentifier,Value=your-db-instance \
       --evaluation-periods 1 \
       --period 300 \
       --unit Megabytes \
       --alarm-actions arn:aws:sns:region:account-id:topic-name

---

3. Optimize DML Operations to Reduce Excessive WAL Generation:
   • If feasible, batch your DML operations to reduce the frequency of WAL generation.
   • Use unlogged tables for temporary or transient data:

     CREATE UNLOGGED TABLE temp_data (id SERIAL, name TEXT);

   • Archive unnecessary WAL files manually to avoid filling up storage:

     aws s3 cp /path/to/wal s3://your-wal-archive-bucket/

---

4. Consider Migrating to Amazon Aurora PostgreSQL:
   • Amazon Aurora offers more flexibility with WAL management and storage optimization. Its architecture minimizes unnecessary WAL accumulation, making it an excellent alternative for databases with high transaction loads.
   • Aurora’s pay-as-you-go pricing model can also provide cost benefits for scaling workloads.
   • Learn more about Aurora’s features and compatibility: Amazon Aurora for PostgreSQL Documentation.

---

5. Evaluate Cost Implications and Scalability:
   • While adjusting WAL settings or migrating to Aurora can be effective, ensure you evaluate the associated costs. For example:
     • Aurora’s architecture might reduce storage costs over time by minimizing unnecessary WAL storage.
     • Using CloudWatch alarms may introduce minor additional charges but is crucial for proactive monitoring.
   • Assess your database’s transaction load and future growth to select the most cost-efficient and scalable option.

---

Closing Thoughts

While it’s not currently possible to disable archive_mode in RDS, these strategies provide practical and effective alternatives to control WAL accumulation. Additionally, exploring solutions like Aurora PostgreSQL can enhance performance and scalability for high-transaction environments. If you feel this limitation hinders your workflow, consider submitting feedback to AWS through the AWS Support Center.

Here are some helpful documentation links to deepen your understanding:

• Modifying a DB Parameter Group
• Monitoring Amazon RDS Metrics with CloudWatch
• Amazon RDS for PostgreSQL Documentation
• Amazon Aurora for PostgreSQL Documentation

---

Farewell

I hope this helps, Yoni! Feel free to share your thoughts or ask for further clarification if needed. Your effort to understand and optimize PostgreSQL in RDS is commendable, and I’m happy to assist. Best of luck with your database operations! 😊

---

Cheers,

Aaron 😊