Aurora RDS (Free Tier) Storage issue

Your Aurora PostgreSQL instance showing high storage usage despite having a small database is a common issue that can be caused by several factors.

First, it's important to understand that the storage space in Aurora is used for more than just your database data. It also includes:

1. Transaction logs (WAL logs)
2. Temporary tables or files created by PostgreSQL transactions
3. Database logs (error files)
4. Other system files that support the consistent state of the RDS instance

For Aurora specifically, there's a distinction between the storage you see reported and what's actually being used by your data. Aurora maintains a storage layer that's separate from the compute instances, and the reported storage often includes pre-allocated space for the database engine.

To investigate and potentially resolve this issue:

1. Monitor your storage usage with the CloudWatch metric "FreeStorageSpace" to track available space.

2. Check for large log files that might be consuming space. Even with auto backups disabled, the system still generates various logs.

3. Look for temporary files that might be created during operations. If your instance has limited working memory (which is likely with a t4g.micro instance), operations like sorting or index creation might be writing to disk.

4. Check for bloat or improper removal of dead rows in your database tables.

If you need to free up space, you could:

• Adjust the log retention settings
• Increase the work_mem parameter if temporary files are the issue
• Run maintenance operations like VACUUM on your PostgreSQL database to reclaim space

If the issue persists, you might need to consider upgrading to a larger instance class that provides more local storage.
Sources
PROBLEM WITH STORAGE OF MY POSTGRES RDS INSTANCE | AWS re:Post
Troubleshooting storage issues in Aurora PostgreSQL - Amazon Aurora

You can check storage distribution for your instance. Just to check all the possible cause please refer below document [+] https://repost.aws/knowledge-center/diskfull-error-rds-postgresql

Verify all options from above document and see if you are able to identify the cause. Once you are able to identify the cause you can cleanup them ad get space released.

If you are not able to get that and you want to reduce storage forcefully. you can create new instance and using export import you can migrate your data and then delete older one.

Hope it helps and if it does, I would appreciate if answer can be accepted so that community can benefit for clarity when searching for similar enquiries in repost/aws guide.

Thank you for your kind message and helping me in this case. I've checked the suggested steps, but I couldn't identify what causing the large disk space usage. I did the following steps. I Run the suggested SQLs from DBeaver:

1. Temporary files shows 0 value to all,
2. SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ; No results from select
3. Cross region replicas: No results from select
4. Checked WAL SELECT pg_size_pretty(pg_current_wal_lsn() - '0/0'::pg_lsn) AS wal_size; 647GB, I've no idea what this indicates, my max storage is 20gb
5. Checked replica slots empty query
6. checked slots by the recommended query shows no results, empty query
7. checked orphaned files only shows 8mb
8. Checked real size of the database, by running the suggested query: To get the real size of the database, run the following query: 7,4mb

Do you have anything else in mind what causing the high storage? I feel kinda lost to be honest :/

It seems you are using RDS PostgreSQL not Aurora.

=> Please see WAL file and calculate size :

SELECT * FROM pg_ls_waldir();

=> Check Log file size using sql query :

SELECT * FROM pg_ls_logdir() ORDER BY modification DESC;

=> Share me the screenshot of your Freestorage cloudwatch metrics

Thank you so much for helping me and confirming the query. Since I've created the DB at 24th of May the Storage space shows 18gb used, which is weird. Based on the query you provided I've checked the WAL files the result is the following:

Name |Value | --------------+-------+ total_wal_size|2176 MB| wal_file_count|34 |

it takes more than 2gb, for the past 24 hours. I think these files are kept for a day, but I can't confirm. However these files are large, taking more than 2gb, still doesn't explain the high storage usage.

Do you have any recommendations how to remove or limit the WAL files?

Thanks for sharing screenshot of your Freestorage Space. That clarify most of doubts in this investigation. Please find my inline comment below for your queries.

Que 1. Why my RDS instance has 18 GB of space utilized out of 20 GB ? Ans -> From the screenshot, I can see only 2 GB space is utilized and 18 GB space is Free. The graph you see is "Free Storage Graph" not "Used space graph". This graph show space that is free and I can see around 18 GB is Free.

Que 2. Do you have any recommendations how to remove or limit the WAL files? Ans -> You can control the number of WAL using parameter wal_keep_segments (or wal_keep_size in PostgreSQL version 13 and later). But I would not recommend changing this parameter without your DBA consult. You can read more about this parameter in below documents.

[+] https://postgresqlco.nf/doc/en/param/wal_keep_segments/ [+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.Replication.ReadReplicas.html

One more parameter is there to control the size of WAL "max_wal_size" but these parameters are important in database performance and operation hence I would recommend getting things verified from DBA before making any change to these parameters. Also please refer below document to know more about max_wal_size.

[+] https://postgresqlco.nf/doc/en/param/max_wal_size/ [+] https://aws.amazon.com/blogs/database/best-practices-for-migrating-an-oracle-database-to-amazon-rds-postgresql-or-amazon-aurora-postgresql-target-database-considerations-for-the-postgresql-environment/

Regarding removal of WAL, You should not remove it manually. Well in RDS you do not have any option to do as well.

Hope it helps and if it does, I would appreciate if answer can be accepted so that community can benefit for clarity when searching for similar enquiries in repost/aws guide.