Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ Best Practices/ Best Practices for VACUUM/ FAQ/ How Do I Estimate the Current Table or Database Space Bloat Ratio?
Updated on 2025-05-29 GMT+08:00
View PDF
Share

How Do I Estimate the Current Table or Database Space Bloat Ratio?

  • Method 1: If possible, use INSERT INTO SELECT to fully import data from the old table to the new table. Estimate the space bloat ratio based on the sizes of the old and new tables. For example: 
    gaussdb=# create table t4(like t3);
CREATE TABLE
gaussdb=# insert into t4 select * from t3;
INSERT 0 85376
gaussdb=# select pg_table_size('t3'::regclass) / pg_table_size('t4'::regclass);
     ?column?
------------------
 7.87027797576622
(1 row)
  • Method 2: Run the \d command to view the table structure, estimate the average length of tuples, and multiply the average length by the number of active tuples to estimate the space occupied by the tables. Bloat ratio ≈ Table size/(Number of active rows × Average length of a single row) Average length of a single row ≈ Sum of lengths of all columns + Row pointer length (4 bytes) + tuple header length (24 bytes) You can use COUNT(*) or the pg_stat_get_live_tuples function to obtain the number of active rows.

    For example:

    gaussdb=# select pg_table_size('t3'::regclass);
 pg_table_size
---------------
      90456064
(1 row)
gaussdb=# \d t3;
          Table "public.t3"
 Column |      Type      | Modifiers
--------+----------------+-----------
 a      | integer        |
 b      | character(100) |

gaussdb=# select count(1) from t3;
 count
-------
 85376
(1 row)

    In the preceding example, the total length of all columns is 104 bytes (100 bytes + 4 bytes), the number of valid rows is 85,376, and the valid space is approximately 85376 × (104 + 4 + 24) = 11269632. Bloat ratio ≈ 90456064/11269632 = 8, that is, the space has bloated by nine times, which is approximately equal to the result of method 1.

Parent topic: FAQ