No space left on device in Amazon Aurora for PostgreSQL

No space left on device error is very common in Amazon Aurora for PostgreSQL which mainly occurs when local storage is exhausted on the DB instance due to which processes could not write to file.

Error message : “could not write to file ‘base/pgsql_tmp/pgsqltxxxxxx’: No space left on device”

Operations that alter large tables, add indexes on large tables or that perform large SELECT queries with complex JOINs or GROUP BY or ORDER BY clauses or any sort of large temporary operation can lead to these errors.

DB instances that are in Amazon Aurora clusters have two types of storage:
– Storage used for persistent data (shared cluster volume), this can expand upto 128 TB.
– Local storage for each Aurora instance in the cluster, based on the instance class.

Local storage size is bound to the instance class and can be changed only by moving to a larger DB instance class.

The following table shows the maximum amount of temporary storage available for each Aurora PostgreSQL DB instance class as per official document. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html#AuroraPostgreSQL.Managing.TempStorage

DB instance class Maximum temporary storage available (GiB)
—————– —————————————————————————
db-x2g-16xlarge 1829
db-x2g-12xlarge 1606
db-x2g-8xlarge 1071
db-x2g-4xlarge 535
db-x2g-2xlarge 268
db-x2g-xlarge 134
db-x2g-large 67
db.r6g.16xlarge 1008
db.r6g.12xlarge 756
db.r6g.8xlarge 504
db.r6g.4xlarge 252
db.r6g.2xlarge 126
db.r6g.xlarge 63
db.r6g.large 32
db.r5.24xlarge 1500
db.r5.16xlarge 1008
db.r5.12xlarge 748
db.r5.8xlarge 504
db.r5.4xlarge 249
db.r5.2xlarge 124
db.r5.xlarge 62
db.r5.large 31
db.r4.16xlarge 960
db.r4.8xlarge 480
db.r4.4xlarge 240
db.r4.2xlarge 120
db.r4.xlarge 60
db.r4.large 30
db.t4g.large 16.5
db.t4g.medium 8.13
db.t3.large 16
db.t3.medium 7.5

On CloudWatch, you can check the FreeLocalStorage metrics which will show you the available local storage on our DB instance.

You can also check the space consumed by temporary files using below command:

select datname, temp_files , pg_size_pretty(temp_bytes) as temp_file_size  FROM   pg_stat_database order by temp_bytes desc;

To overcome this issue, you can reboot the writer-instance, which will remove any temporary objects from the local storage by terminating any dangling transaction. If still facing this issue then you can scale up the instance-class to increase the local-storage. The amount of local storage for each instance class is mentioned in the above official document. For more information you can check below.

https://aws.amazon.com/premiumsupport/knowledge-center/postgresql-aurora-storage-issue/