I only have one instance running on my AWS RDS as Psql DB. The size of my DB, which has twenty tables but all the data amounts to 10MB. As for the screenshot I added, it says the month-end estimate of my RDS storage will be close to reaching the allocated limit. I'm not understanding how 10MB DB can balloon up to 11.80GB.
I went through the AWS RDS forums and one possible problem would be automatically created error logs. But I went through my logs and it's about 100 logs of 3.0KB each. So I doubt that's the culprit.
I'm not even remotely close to understanding what the possible issue could be.
Update 1, responding to @PhilTM and @Dave Rix:
To determine the size of my DB I ran the query below:
SELECT *, pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS INDEX , pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS TABLE FROM ( SELECT *, total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes FROM ( SELECT c.oid, nspname AS table_schema, relname AS TABLE_NAME , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r' ) a ) a;
As for the size of the backup I haven't configured it to include extra backups. The automatic backup had always been turned on (since that's default). But the customer doesn't get charged extra for letting the default automatic backup to work per :
https://aws.amazon.com/rds/pricing/ "Backup Storage Backup storage is the storage associated with your automated database backups and any user-initiated DB Snapshots you have taken. Increasing your backup retention period or taking additional database snapshots increases the backup storage consumed by your database. There is no additional charge for backup storage up to 100% of your provisioned database storage for an active DB Instance.* After the DB Instance is terminated, backup storage is billed at $0.095 per GB-month. Additional backup storage: $0.095 per GB-month * For example, if you have an active DB Instance with 10GB-month of provisioned database storage, we will provide up to 10GB-month of backup storage at no additional charge. Based upon our experience as database administrators, the vast majority of databases require less raw storage for a backup than for the primary data set, meaning that most customers will never pay for backup storage. "
Just to be sure I turned off the automated backups but the usage increments daily despite the fact that the size of my DB hasn't changed much.
As for getting the size of the WAL file, AWS does not allow the user to get one, assuming the link below is correct: Get WAL files from AWS RDS PostgreSQL instance
To answer @Dave Rix, I've had this RDS instance for close to six months now. Also, it's not the estimate I'm worried about. It's the "Month-to-date actual usage". I have not used 11.8GB of storage.
Update 3, The issue has been answered per the link below: https://forums.aws.amazon.com/thread.jspa?threadID=196444
It seems that with the "free-tier w/ 20GB storage/m", when I have a single instance running, aws increments 20GB / 31 (or 30) daily making it out to close to 20GB by the end of the month. So no problem here. Thank you both.