Disk space utilization
PostgreSQL cluster disks store:
- temporary files;
- data files;
- transaction logs (WALs);
- logical replication slots;
- DBMS logs;
- system files required for PostgreSQL to function.
You can keep track of the amount of disk space used.
When the amount of disk space used grows, you can check:
If the cluster disk is 80% full, a notification will appear in the dashboard and will be sent to the Account Owner's email and those users subscribed to the Services and Services notification category.
If the cluster disk is 95% full or more, the cluster will enter DISK_FULL
status and will be read-only. To make the cluster read-write , clear the disk or scale the cluster and select a configuration with a larger disk size.
View the size of temporary files
Temporary files can be used for sorting, hashing, and temporarily storing query results. To see the total size of temporary files in the database, use SQL query to pg_stat_database
view:
SELECT datname, temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM pg_stat_database;
Example output:
datname | temp_files | temp_bytes
--------+--------------+----------------
mydb | 2 | 16384
postgres| 1 | 8192
Here:
datname
— database name;temp_files
— number of temporary files in this database;temp_bytes
— size of temporary files in bytes.
The temp_files and temp_bytes
fields include all temporary files since the cluster was created. Data is only reset after restoring from backup or after a crash. Use the values of these fields to keep track of changes in the total size of temporary files.
The size of the temporary tables created by a particular query can be obtained using the EXPLAIN ANALYZE command.