Querying the Table Size

Querying the Table Size

The following table lists the scenarios for common table size query, based on the query scope.

1. Prepare a test table.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   DROP TABLE IF EXISTS user_info; CREATE TABLE IF NOT EXISTS user_info ( id BIGSERIAL, user_name VARCHAR(50) NOT NULL, gender CHAR(1), age INT, phone VARCHAR(20) NOT NULL, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) DISTRIBUTE BY HASH(phone); CREATE UNIQUE INDEX IF NOT EXISTS idx_user_info_phone ON user_info(phone); INSERT INTO user_info (user_name, gender, age, phone) SELECT 'User_' || gs, CASE WHEN random() > 0.5 THEN 'M' ELSE 'F' END, floor(18 + random() * 40)::INT, '138' || lpad(gs::TEXT, 8, '0') FROM generate_series(1, 10000) AS gs;

2. Query the total disk space of the table, including indexes, data files, and column-store auxiliary tables. (The following statements are equivalent.)

   1 2 3 4

   SELECT * FROM pg_total_relation_size('user_info'); ---Method 1: query by table name SELECT * FROM pg_total_relation_size('user_info'::regclass); ---Method 2: The system automatically converts the table name to an OID for query. SELECT oid FROM PG_CLASS WHERE relname = 'user_info'; ---Method 3: The OID is queried first, and then used for query. SELECT * FROM pg_total_relation_size(oid);

   

3. The unit of the result is byte. To convert it to MB or KB, use the pg_size_pretty function.

   1	SELECT pg_size_pretty(pg_total_relation_size('user_info'));

   

4. Query the disk space used by a specified table, excluding table indexes and toast.

   1 2 3

   SELECT * FROM pg_relation_size('user_info'); SELECT * FROM pg_relation_size('user_info'::regclass); SELECT * FROM pg_relation_size(oid);

   

5. Query the disk space used by a table, excluding indexes (but including toast, free space mapping, and visibility mapping).

   1 2 3

   SELECT * FROM pg_table_size('user_info'); SELECT * FROM pg_table_size('user_info'::regclass); SELECT * FROM pg_table_size(oid);

   

6. Query the total disk space used by the indexes of a specified table.

   1 2 3 4

   SELECT * FROM pg_indexes_size('user_info'); SELECT * FROM pg_indexes_size('user_info'::regclass); SELECT * FROM pg_indexes_size(oid); SELECT pg_size_pretty(pg_indexes_size('user_info'));

   

Querying the Sizes of Partitions and Partitioned Indexes

You can query the size of a partitioned table using method described in Querying the Table Size. The following steps describe how to query the size of a partition or partitioned index.

1. Prepare a test partitioned table and data.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

   DROP TABLE IF EXISTS customer_address; CREATE TABLE customer_address ( ca_address_sk INTEGER NOT NULL , ca_address_id CHARACTER(16) NOT NULL , ca_street_number CHARACTER(10) , ca_street_name CHARACTER varying(60) , ca_street_type CHARACTER(15) , ca_suite_number CHARACTER(10) ) DISTRIBUTE BY HASH (ca_address_sk) PARTITION BY RANGE(ca_address_sk) ( PARTITION P1 VALUES LESS THAN(2450815), PARTITION P2 VALUES LESS THAN(2451179), PARTITION P3 VALUES LESS THAN(2451544), PARTITION P4 VALUES LESS THAN(MAXVALUE) ); CREATE INDEX customer_address_idx ON customer_address (ca_address_sk) LOCAL ( PARTITION ca_address_sk_idx1, PARTITION ca_address_sk_idx2, PARTITION ca_address_sk_idx3, PARTITION ca_address_sk_idx4 ); INSERT INTO customer_address ( ca_address_sk, ca_address_id, ca_street_number, ca_street_name, ca_street_type, ca_suite_number ) SELECT 2450000 + gs, lpad(('ADDR' || gs)::TEXT, 16, '0'), lpad((gs % 1000)::TEXT, 10, ' '), 'Street_' || gs, CASE WHEN random() > 0.5 THEN 'St' ELSE 'Ave' END, lpad(gs::TEXT, 10, '0') FROM generate_series(1, 10000) AS gs;

2. Query the disk space used by a partition, excluding indexes. The following two methods are equivalent. In method 2, you can query the OID of the table and that of the partition through the PGXC_GET_STAT_ALL_PARTITIONS view, and then query the result based on the OIDs.

   1 2 3

   SELECT * FROM pg_partition_size('customer_address','p1'); ---Method 1: Query the result by table name and partition name. SELECT relid,partid,partname FROM PGXC_GET_STAT_ALL_PARTITIONS WHERE relname = 'customer_address'; ---Method 2: Query the OID of the table and that of the partition, and then query the result based on the OIDs. SELECT * FROM pg_partition_size(Table OID,Partition OID);

   

3. Query the disk space used by a partitioned index.

   1 2 3

   SELECT * FROM pg_partition_indexes_size('customer_address','p1'); SELECT relid,partid,partname FROM PGXC_GET_STAT_ALL_PARTITIONS WHERE relname = 'customer_address'; SELECT * FROM pg_partition_indexes_size(Table OID,Partition OID);

   

Querying the Sizes of Hot and Cold Tables

You can use the function for querying the size of an ordinary table to query the sizes of hot and cold tables. For details, see Querying the Table Size. However, the query result contains the size of cold data. To query the size of cold and hot data separately, use pg_lifecycle_table_data_distribute. To query the sizes of all hot and cold tables, use pg_lifecycle_node_data_distribute.

1. Prepare test data:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

   DROP TABLE IF EXISTS lifecycle_table; CREATE TABLE lifecycle_table(i int, val text) WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:100') PARTITION BY RANGE (i) ( PARTITION P1 VALUES LESS THAN(5), PARTITION P2 VALUES LESS THAN(10), PARTITION P3 VALUES LESS THAN(15), PARTITION P8 VALUES LESS THAN(MAXVALUE) ) ENABLE ROW MOVEMENT; INSERT INTO lifecycle_table(i, val) SELECT (generate_series % 100), -- 0 to 99, covering all partitions 'lifecycle_val_' || generate_series FROM generate_series(1, 10000); DROP TABLE IF EXISTS cold_hot_table; CREATE TABLE cold_hot_table ( W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_ID CHAR(15) , W_SUITE_NUMBER CHAR(10) ) WITH (ORIENTATION = COLUMN, storage_policy = 'LMT:30') DISTRIBUTE BY HASH (W_WAREHOUSE_ID) PARTITION BY RANGE(W_STREET_ID) ( PARTITION P1 VALUES LESS THAN(100000), PARTITION P2 VALUES LESS THAN(200000), PARTITION P3 VALUES LESS THAN(300000), PARTITION P4 VALUES LESS THAN(MAXVALUE) )ENABLE ROW MOVEMENT; INSERT INTO cold_hot_table ( W_WAREHOUSE_ID, W_WAREHOUSE_NAME, W_STREET_NUMBER, W_STREET_NAME, W_STREET_ID, W_SUITE_NUMBER ) SELECT -- Warehouse ID, with a fixed length of 16 characters lpad((generate_series % 1000000)::text, 16, '0'), -- Warehouse name 'WAREHOUSE_' || generate_series, -- 10-digit street number lpad((random() * 9999999999)::bigint::text, 10, '0'), -- Street name 'STREET_' || (random() * 10000)::int, -- Partitioned fields: 0 to 400,000, evenly distributed and automatically allocated to P1, P2, P3, and P4 (generate_series % 400000)::text, -- 10-digit room number lpad((random() * 9999999999)::bigint::text, 10, '0') FROM generate_series(1, 5000);

2. Query the size of cold and hot data in a table.

   1	SELECT * FROM pg_lifecycle_table_data_distribute ('lifecycle_table');

   

3. Query the size of cold and hot data in all cold and hot tables.

   1	SELECT * FROM pg_lifecycle_node_data_distribute() ORDER BY tablename;

   

Querying the Database Size

You can use pg_database_size to query the disk space used by a database with a specified name.

You can query the database size by name or OID.

• In DWS 9.1.1.200 or later, set the GUC parameter fast_obs_dbsize_method to 2 to quickly calculate the database size in the storage-compute decoupled architecture. This improves performance by more than tenfold, compared with earlier versions.
• In versions earlier than DWS 9.1.1.200, if there is only one database and tablespace in the cluster, the OBS bucket size is used to calculate the OBS space. For details, see the description of fast_obs_dbsize_method in GUC Parameters Related to the Table Size. This method offers better performance, but the result includes the size of the backup set.

1. Query the size of a database with a specified name.

   1	SELECT pg_database_size('gaussdb');

   

2. Query the size of a database with a specified OID. Use PG_STAT_DATABASE to query the OID of the database.

   SELECT datid FROM PG_STAT_DATABASE WHERE datname = 'gaussdb';
   SELECT pg_database_size(2147483821);

   

Querying Other Sizes (CU Size)

You can use the pg_obs_file_size function to query the CU size of a V3 table on OBS.

You can use the pgxc_get_cstore_dirty_ratio function to query the CU, delta, and cudesc dirty page rates and sizes of an HStore Opt table on each DN.

1. Create a storage-compute decoupled V3 table and import data.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   DROP TABLE IF EXISTS test_t5; CREATE TABLE test_t5 ( id integer not null, data integer, age integer ) WITH (ORIENTATION =COLUMN, COLVERSION =3.0) DISTRIBUTE BY ROUNDROBIN; INSERT INTO test_t5 (id, data, age) SELECT id, (random() * 10000)::integer, -- A random integer ranging from 0 to 10,000 (20 + random() * 60)::integer -- A random age ranging from 20 to 80 FROM generate_series(1, 10000) AS t(id); UPDATE test_t5 SET age = 25 WHERE id = 10;

2. Query the size of the CU file stored in OBS for the V3 table.

   1	SELECT * FROM pg_obs_file_size('test_t5');

   

3. If the V3 table is a partitioned table, you can also query the size of the CU file stored in OBS for a specified partition.

   1	SELECT * FROM pg_obs_file_size('Table name','Partition name');

4. Create an Hstore_opt table.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   DROP TABLE IF EXISTS hstore_opt_table_demo; CREATE TABLE hstore_opt_table_demo( t_code character varying(20), t_gisid character varying(800), t_datatime timestamp(6) without time zone, t_gmid character varying(64) ) WITH (orientation=column, enable_hstore_opt=on) --This configuration is used by default when a table is created. DISTRIBUTE BY hash (t_gmid) --Distribution key, which can be a primary key or an associated column. PARTITION BY range (t_datatime) -- Partition key ( partition p2024_1 start('2024-01-01') end ('2024-06-01') every (interval '1 month'), partition p2024_7 start('2024-06-01') end ('2024-12-31') every (interval '1 month') ); INSERT INTO hstore_opt_table_demo (t_code, t_gisid, t_datatime, t_gmid) VALUES ('T001','GIS001','2024-01-01 10:00:00','M001'), ('T002','GIS002','2024-01-02 10:00:00','M002'), ('T003','GIS003','2024-01-03 10:00:00','M003'), ('T004','GIS004','2024-01-04 10:00:00','M004'), ('T005','GIS005','2024-01-05 10:00:00','M005'), ('T006','GIS006','2024-01-06 10:00:00','M006'), ('T007','GIS007','2024-01-07 10:00:00','M007'), ('T008','GIS008','2024-01-08 10:00:00','M008'), ('T009','GIS009','2024-01-09 10:00:00','M009'), ('T010','GIS010','2024-01-10 10:00:00','M010');

5. Query the CU, delta, and cudesc dirty page rates and sizes of the table on each DN.

   1	SELECT * FROM pgxc_get_cstore_dirty_ratio('hstore_opt_table_demo');

   

6. Query the CU, delta, and cudesc dirty page rates and sizes of a specified partition on each DN.

   1	SELECT * FROM pgxc_get_cstore_dirty_ratio('hstore_opt_table_demo','p2024_1_4');

   

GUC Parameters Related to the Table Size

Best Configuration Practices for Querying the Size of a V3 Table

1. The result returned by a function used to query a V3 table size includes the size of the CU file stored on OBS.
2. The pg_obs_file_size function can be used to query only the size of the OBS file for a V3 table.
3. In version 9.1.0.100, the method with fast_obs_tablesize_method set to 2 is used by default to query a V3 table. This method delivers the best performance. However, it estimates the cudesc table and provides a lower accuracy than that of the method with fast_obs_tablesize_method set to 0 (which delivers the highest accuracy but the poorest performance).
4. In version 9.1.1.200, the accuracy and performance of V3 table size query can be as good as that of V2 table size query, on condition that the OBS list optimization feature is enabled. (This feature is enabled by default for new installation and disabled by default for upgrade. To enable it, set the GUC parameter enable_pg_db_file to on. When the feature is enabled, the pg_db_file system catalog is available only after autovacuum rebuilds it.)
5. In version 9.1.1.100, if there is only one database in the cluster, you can calculate the size of a database that contains V3 tables by obtaining the OBS bucket information, which delivers good performance. If there are multiple databases, you need to traverse files on OBS, which results in poor performance. In 9.1.1.200 and later versions, the database size calculation performance improves by 10 times compared with 9.1.1.100, on condition that OBS list optimization is enabled.

Best Configuration Practices for Querying the Size of a Cold or Hot Table

1. When querying the size of a cold or hot table, if you use a common table size query function (see Querying the Table Size), the query result includes the size of cold data.
2. To query the size of hot data and that of cold data separately, you can use the pg_lifecycle_table_data_distribute function. For details, see Querying the Sizes of Hot and Cold Tables.