Skip to main content

Disk space usage in a PostgreSQL TimescaleDB cluster

The disks of a PostgreSQL TimescaleDB cluster store:

  • temporary files;
  • data files;
  • transaction logs (WAL);
  • logical replication slots;
  • DBMS logs;
  • system files required for PostgreSQL TimescaleDB.

In cloud databases, part of the disk space is reserved for system needs. The total amount of reserved space depends on the version of PostgreSQL.

PostgreSQL 17 and belowPostgreSQL 18
Reserve for the file system4% of disk size4% of disk size

Reserve for the operating system, service components, and logs

8 GB15 GB

The reserved portion of disk space is not available for hosting databases. Keep this in mind when choosing a configuration line.

You can track disk usage using disk full notifications and metrics. Learn more about metrics in the Cluster, node, and database monitoring PostgreSQL TimescaleDB instructions.

When the amount of used disk space grows, you can check:

When the cluster disk is 95% full or more, the cluster switches to the DISK_FULL status and becomes read-only. This is necessary to prevent complete locking or corruption of the cluster due to lack of free space. To make the cluster read-write, clean up the disk or scale the cluster and select a configuration with a larger disk size than in the previous configuration.

Disk full notifications

Disk full notifications are sent to the email addresses of the Account Owner and users who are subscribed to the notification category "Services and products". Notifications are sent when the disk is 80% and 95% full.

View temporary file size

Temporary files can be used for sorting, hashing, and temporary storage of query results. To view the total size of temporary files in a database, use an SQL query to the pg_stat_database view:

SELECT datname, temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM   pg_stat_database;

Sample output:

datname | temp_files   | temp_bytes
--------+--------------+----------------
mydb    |      2       |    16384
postgres|      1       |    8192

Where:

  • 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 account for all temporary files since cluster creation. The data is reset only after restoring from a backup or after an abnormal termination. Use the values of these fields to track changes in the total size of temporary files.

The size of temporary tables created by a specific query can be obtained using the EXPLAIN ANALYZE command.

Check consumers of logical replication slots

Logical replication slots are used for continuous data replication from one database to another. Logical replication slots must always have a consumer. If there is no consumer, file size 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, run an 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;

Sample output:

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

Where:

  • slot_name — name of the logical replication slot;
  • replicationslotlag — size of WAL files that will not be automatically deleted during checkpoints and that consumers of the logical replication slot can use;
  • active — boolean value indicating whether the logical replication slot is in use:
    • f — the slot has no consumer;
    • t — the slot has a 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 parameter, the transaction log may be deleted before the consumer reads the changes from the logical replication slot.

Dead tuples

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

Dead tuples will also be created on the replica if you use 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, you can use the PostgreSQL TimescaleDB extension pgstattuple or the pg_stat_all_tables view.

Example of an SQL query to the pg_stat_all_tables view:

SELECT * FROM pg_stat_all_tables WHERE relname='test';

Sample 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.

Comparison of dead tuple removal methods

To remove "dead" tuples, you can use the standard VACUUM, VACUUM FULL commands, or repack tables and indexes using the pg_repack extension.

VACUUMVACUUM FULLpg_repack
Cleans the table of dead tuples✓✓✓
Reduces file size✗✓✓
Completely locks the table✗✓Only during two short periods at the start and end of the extension's operation

Learn more about the VACUUM and VACUUM FULL commands in the Routine Vacuuming article of the PostgreSQL documentation.

Learn more about the pg_repack extension and its functions in the pg_repack documentation.

Repack tables and indexes using pg_repack

warning

Run pg_repack during periods of minimum load on the cluster, as pg_repack creates an additional load. Learn more about tracking the cluster state in the Cluster, node, and database monitoring PostgreSQL TimescaleDB instructions.

To work with the pg_repack extension, a client of the same name is used. The client is installed on the host from which you connect to the cluster PostgreSQL TimescaleDB.

  1. Add the pg_repack extension to the database.

  2. Determine the version of the pg_repack extension:

    2.1. Connect to the cluster. When connecting, specify the name of the database where you added the pg_repack extension.

    2.2. Determine the extension version:

    SELECT
      extname AS extension,
      extversion AS version,
      extnamespace::regnamespace AS schema
    FROM pg_extension
    WHERE extname = 'pg_repack';

  3. Download and install the pg_repack client of the same version as the extension.

  4. Make sure there is enough free disk space. A full repack of tables requires free disk space approximately twice the size of the tables and indexes being processed. For example, if the total size of the processed tables and indexes is 1 GB, an additional 2 GB of disk space will be required.

  5. Make sure that the processed table has a primary key (PRIMARY KEY) or a unique index (UNIQUE INDEX).

  6. Repack tables and indexes in the database:

    pg_repack -k -h <host> -p <port> \
      -U <user> \
      -d <database_name> \
      -t <table_name> \
      -i <index_name>

    Specify:

    • <host> — node DNS address;
    • <port> — connection port;
    • <user> — database username;
    • <database_name> — database name;
    • optional: -t <table_name>, where <table_name> is the table name. Use this parameter if you need to repack an individual table. To repack multiple tables, specify the required number of -t parameters — one for each table;
    • optional: -i <index_name>, where <index_name> is the index name. Use this parameter if you need to repack an individual index. To repack multiple indexes, specify the required number of -i parameters — one for each index. If an index belongs to a table that you have already specified via the -t parameter, you do not need to specify it separately via the -i parameter — the index will be automatically repacked along with the table.

    If you do not specify the -t and -i parameters, pg_repack repacks all tables and indexes in the specified database.

Clean up disk

For your information

We do not recommend using the DELETE FROM table WHERE query to clean up disk space. This query can create large result sets on large tables and store them on disk. The remaining free disk space may run out completely, leading to issues with PostgreSQL TimescaleDB and the need to restore its operation manually.

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

  • DROP TABLE — completely deletes the table: data, structure, indexes, constraints, triggers.

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

  • TRUNCATE TABLE — deletes all rows from the table. Works faster than DELETE.

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

  • DELETE — deletes the rows specified in the WHERE condition.