Using gp_relsizes_stats in Yandex MPP Analytics for PostgreSQL

The gp_relsizes_stats extension is designed to collect statistics on the physical sizes of files and tables across all Greenplum® cluster segments.

Key features:

• Automatic statistics collection via a background worker.
• Simultaneous monitoring of all cluster segments.
• Flexible performance settings to control system load.
• Tracking the history of table size changes.
• Option to manually run statistics collection when needed.

Extension components:

• Main background worker, which, if enabled, collects statistics on a regular basis. The worker runs in the postgres database.
• Workers for specific databases.
• Statistics collection function which scans the file system to calculate file sizes. The function runs on each segment.

Data schemaData schema

Installing the extension creates a schema named relsizes_stats_schema with the following tables.

Main tables:

Views for analysis:

Extension settingsExtension settings

To update these settings, contact support.

Installing gp_relsizes_stats in a Greenplum® clusterInstalling gp_relsizes_stats in a Greenplum® cluster

1. Connect to the database as the owner or a user with the CREATE permission in the database and run this command:

   CREATE EXTENSION gp_relsizes_stats;
   

2. Make sure the extension has been installed:

   SELECT extname FROM pg_extension;
   

   This will display a list of extensions installed in the DB:

         extname
   -------------------
    ...
    gp_relsizes_stats
   

Use casesUse cases

As an example, let’s collect statistics for a call center’s database. To do this, prepare the source data:

1. Create a schema named call_center:

   CREATE SCHEMA call_center;
   

2. Create a table named operators in the call_center schema:

   CREATE TABLE call_center.operators (
       id INTEGER PRIMARY KEY,
       operator_name varchar (20),
       operator_id INTEGER
   );
   

3. Insert the generated data into the operators table:

   INSERT INTO call_center.operators (id, operator_name, operator_id)
   SELECT 
       generate_series(1, 1000) AS id,
       'Operator ' || generate_series(1, 1000) AS operator_name,
       10000 + generate_series(1, 1000) AS operator_id;
   

4. Run statistics collection manually:

   SELECT relsizes_stats_schema.relsizes_collect_stats_once();
   

Table sizesTable sizes

To output the 20 largest tables in the database, run this command:

SELECT nspname, relname,
       pg_size_pretty(size) as size_pretty,
       mtime
FROM relsizes_stats_schema.table_sizes
ORDER BY size DESC
LIMIT 20;

To output the size of the operators table, run this command:

SELECT relname, pg_size_pretty(size) as size_pretty
FROM relsizes_stats_schema.table_sizes
WHERE nspname='call_center' AND relname='operators';

Schema sizesSchema sizes

To output all schema sizes in the database, run this command:

SELECT nspname, pg_size_pretty(size) as size_pretty
FROM relsizes_stats_schema.namespace_sizes
ORDER BY size DESC;

To output the size of the call_center schema, run this command:

SELECT nspname, pg_size_pretty(size) as size_pretty
FROM relsizes_stats_schema.namespace_sizes
WHERE nspname='call_center';

Table growth historyTable growth history

To output the history of the operators table size changes, run this command:

SELECT insert_date, pg_size_pretty(size) as size_pretty
FROM relsizes_stats_schema.table_sizes_history
WHERE nspname='call_center' AND relname='operators'
ORDER BY insert_date;

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.