User data and write-ahead logging (WAL) data occupy most of the disk space in ApsaraDB PolarDB PostgreSQL-compatible edition.

Query the disk space occupied by user data

Query the disk space occupied by the user data in databases and tables:

  • Databases

    Run the \l command on your PostgreSQL client to view the disk space occupied by the data in each database. To perform this operation, the system must traverse the directory tree. This causes high CPU overhead and I/O overhead.

  • Tables

    You can use your client to connect to a database in which the data occupies a large amount of disk space. Then, you can run the \d command to view the disk space occupied by the data from each table in the database.

Query the disk space occupied by WAL logs

Execute the following SQL statement to view the disk space occupied by WAL logs.
SELECT pg_size_pretty(SUM(size)) FROM pg_ls_waldir();
In most cases, WAL logs occupy a large amount of disk space due to the following reasons:
  • The wal_keep_segments parameter is set to an excessively large value. The wal_keep_segments parameter specifies the minimum number of retained WAL files.

    Solution: You can execute the SHOW wal_keep_segments statement to check the parameter value. You can also execute the SHOW wal_segment_size statement to view the size of each WAL file. The size can be used to estimate the minimum space that is occupied by WAL files.

  • When the archive_mode parameter is set to on, WAL files are not archived at a proper point in time.

    Solution: You can check whether the archive_command parameter is properly configured.

  • The synchronization latency for the pg_replication_slots view is long or an unused replication slot exists.

    Solution: Check whether the replication slot is connected to the database.

  • The checkpoint process does not flush a specific amount of data in WAL logs to disks at proper points in time.

    Solution: Clear the data at proper points in time.