Skip to main content
Disk space utilization
Last update:

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 monitor 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 Control Panel and will be sent to the email of the Account Owner and those users subscribed to notification category Services and Services.

If the cluster disk is 95% or more full, the cluster will go to DISK_FULL status and will be read-only. For the cluster to work on read and write, clean disk or scale cluster and select a configuration with a larger disk size.

View the size of temporary files⁠

Temporary files can be used to sort, hash, and temporarily store query results. To see the total size of temporary files in the database, use a SQL query against the 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 — the name of the database;
  • temp_files — the number of temporary files in this database;
  • temp_bytes — size of temporary files in bytes.

The temp_files and temp_bytes fields account for 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 with the EXPLAIN ANALYZE command.

Check consumers of logical replication slots⁠

Logical replication slots are used to continuously replicate data from one database to another. Logical replication slots must always have a consumer. If there is no consumer, the file volume will grow.

Learn more about managing logical replication slots in the Subscription article of the PostgreSQL documentation.

To check if logical replication slots have consumers, submit a SQL query to the pg_replication_slots view:

SELECT slot_name,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag,active 
FROM pg_replication_slots;

Example output:

slot_name | replicationslotlag | active
-----------------+--------------------+--------
myslot1 | 129 GB | f
myslot2 | 704 MB | t
myslot3 | 624 MB | t

Here:

  • slot_name — logical replication slot name;
  • replicationslotlag — the amount of WAL files that will not be automatically deleted at checkpoints and that consumers of the logical replication slot can use;
  • active — Boolean value indicates whether the logical replication slot is in use. The value of f indicates that the slot has no consumer.

If you have PostgreSQL version 13 or higher, you can limit the maximum size of stored WAL files using the max_slot_wal_keep_size parameter. Note that when using this option, the transaction log may be deleted before the consumer reads the changes from the logical replication slot.

"Dead" motorcades⁠

When you update rows in a table (UPDATE) or delete them (DELETE), tuples are not actually deleted from disk, but new versions of them are created. Older versions of tuples will be referred to as "dead tuples". This versioning is necessary to implement the MVCC (Multi-Version Concurrency Control) process. Even though the strings are changed in one transaction, other active transactions may continue to see the old version of the strings. "Dead" tuples can only be deleted when all active transactions are closed.

"Dead" tuples will also be formed on the replica if you are using logical replication.

Check "dead" tuples⁠

If there are many "dead" tuples, they can take up a significant amount of disk space. To check the number of "dead" tuples, use the pg_stat_all_tables view.

An example SQL query for the pg_stat_all_tables view:

SELECT * FROM pg_stat_all_tables WHERE relname='test';

Example output:

-[ RECORD 1 ]----------+------------------------------
relid | 16395
schemaname | public
relname | test
seq_scan | 3
seq_tup_read | 5280041
idx_scan |
idx_tup_fetch |
n_tup_ins | 2000000
n_tup_upd | 0
n_tup_del | 3639911
n_tup_hot_upd | 0
n_live_tup | 1635941
n_dead_tup | 1999952
n_mod_since_analyze | 3999952
last_vacuum |
last_autovacuum | 2023-02-16 04:49:52.399546+00
last_analyze | 2023-02-09 09:44:56.208889+00
last_autoanalyze | 2023-02-16 04:50:22.581935+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 1
autoanalyze_count | 1

Here n_dead_tup is the number of "dead" tuples.

Delete "dead" tuples⁠

To remove "dead" tuples, use the VACUUM operation or configure AUTOVACUUM. These operations remove unused versions of rows in tables and indexes and mark the space free for future use. However, this disk space is not returned to the operating system except in the special case where one or more pages at the end of the table are completely free and an exceptional table lock can be obtained. For more information about VACUUM and AUTOVACUUM operations, see the Routine Vacuuming article in the PostgreSQL documentation.

Clean disk⁠

Open the transaction transaction_read_only = no and remove unnecessary data using one of the queries:

  • DROP TABLE — deletes the structure (data, privileges, indexes, constraints, triggers). Use when completely deleting a table with data and structure:

    BEGIN;
    SET transaction_read_only = no;
    DROP TABLE table_name;
    COMMIT;

  • TRUNCATE TABLE — deletes the contents of the table, while the structure is preserved. Works faster than DROP TABLE. Use when deleting all rows of a table while preserving the table structure:

    BEGIN;
    SET transaction_read_only = no;
    TRUNCATE TABLE table_name;
    COMMIT;

  • DELETE — use to delete specific rows.

    For your information

    We do not recommend using a DELETE FROM table WHERE ... query to clean up a disk. This query can create large size samples on large tables and place them on disk. The remaining free disk space may run out completely, causing problems with PostgreSQL and the need to manually restore its operation.