Skip to main content
Version: 2.x

gp_relsizes_stats

The gp_relsizes_stats module collects and stores the on-disk sizes of relation files in Apache Cloudberry. It scans the database directories on the segments, records the size and modification time of each relation file, and exposes views that aggregate these figures per table and per schema.

Statistics can be collected periodically by a background worker, or on demand by calling a function. Naptime parameters let you spread the file scan over time to limit the I/O load.

Build and install​

gp_relsizes_stats is built and installed by default with Apache Cloudberry. No configure option is required.

To build and install only this module from the source tree:

make -C gpcontrib/gp_relsizes_stats
make -C gpcontrib/gp_relsizes_stats install

Enable the module​

  1. Add the library to shared_preload_libraries and restart the cluster. The periodic background worker is registered only when the library is preloaded:

    gpconfig -c shared_preload_libraries -v "'gp_relsizes_stats'" --skipvalidation
    gpstop -ra

    If shared_preload_libraries already lists other libraries, keep them in the value.

  2. Create the extension in each database for which you want to collect statistics:

    CREATE EXTENSION gp_relsizes_stats;

    The extension is marked as trusted, so a non-superuser who has the CREATE privilege on the database can create it. The extension creates the relsizes_stats_schema schema. The role that creates the extension is granted USAGE on the schema and SELECT on its tables and views, with grant option.

  3. Optional: enable periodic collection. It is off by default:

    gpconfig -c gp_relsizes_stats.enabled -v on --skipvalidation
    gpstop -u

How collection works​

  • The main background worker connects to the postgres database. When gp_relsizes_stats.enabled is on, it runs a collection cycle and then sleeps for gp_relsizes_stats.restart_naptime.
  • A collection cycle goes through all databases except template0, template1, diskquota, and gpperfmon. For each database it starts a dynamic background worker. Databases in which the extension is not created are skipped.
  • For each database, the worker rebuilds segment_file_map, replaces the content of segment_file_sizes with a fresh scan of the database directories on all segments (the default tablespace and user tablespaces), and, if gp_relsizes_stats.save_history is on, refreshes table_sizes_history.
  • Each collection cycle needs free background worker slots. If a worker cannot be registered, the error message hints at increasing max_worker_processes. If this error occurs in the periodic worker, the worker exits and does not start again until the cluster is restarted.

Configuration parameters​

All parameters can be set only in the server configuration and take effect after a configuration reload. The naptime parameters take plain integers in milliseconds. Values with a unit, such as '6h', are not accepted.

ParameterTypeDefaultDescription
gp_relsizes_stats.enabledbooleanoffEnables periodic collection by the background worker.
gp_relsizes_stats.save_historybooleanonSaves table sizes to relsizes_stats_schema.table_sizes_history during collection.
gp_relsizes_stats.restart_naptimeinteger (ms)21600000Time between two collection cycles. The default is 6 hours. Range: 0 to 2147483647.
gp_relsizes_stats.database_naptimeinteger (ms)0Total pause that is spread between databases in one cycle. The value is divided by the number of databases, and the worker sleeps for the result after each database. Range: 0 to 2147483647.
gp_relsizes_stats.file_naptimeinteger (ms)1Pause after each file during the file scan. Range: 0 to 2147483647.

SQL objects​

All objects are created in the relsizes_stats_schema schema.

Functions​

FunctionReturnsDescription
relsizes_collect_stats_once()voidRuns one collection cycle for all databases, without the naptime pauses. It does not check gp_relsizes_stats.enabled.
get_stats_for_database(dboid oid, fast bool)TABLE (segment integer, relfilenode oid, filepath text, size bigint, mtime bigint)Runs on all segments. Scans the directories of the database dboid and returns one row per relation file. When fast is true, the scan does not pause between files. It is used by the collection process.

Tables​

segment_file_map: maps relations to their relation file nodes on each segment. Append-optimized, randomly distributed.

ColumnTypeDescription
segmentintegerSegment content ID.
reloidoidOID of the relation.
relfilenodeoidRelation file node on that segment.

segment_file_sizes: the result of the last file scan. Append-optimized, randomly distributed.

ColumnTypeDescription
segmentintegerSegment content ID.
relfilenodeoidRelation file node, parsed from the file name.
filepathtextPath of the file.
sizebigintFile size in bytes.
mtimebigintLast modification time of the file, as a Unix timestamp.

table_sizes_history: table sizes with the date of collection. Randomly distributed.

ColumnTypeDescription
insert_datedateDate on which the row was collected.
nspnametextSchema name.
relnametextTable name.
sizebigintTable size in bytes.
mtimetimestampLatest modification time among the files of the table.

Views​

table_files: one row per file of each table (relation kinds r and p, excluding the pg_global tablespace), including the files of TOAST tables, TOAST indexes, and the auxiliary relations of append-optimized tables. Indexes on the table and the block directory of append-optimized tables are not included, so the sizes in table_sizes are not the total relation sizes.

ColumnTypeDescription
nspnamenameSchema name.
relnamenameTable name. For rows with own_file = false, this is the name of the root partitioned table.
segmentintegerSegment content ID.
relfilenodeoidRelation file node.
filepathtextPath of the file. NULL if the relation has no file on disk.
kindtextOne of main, toast, toast_idx, ao, ao_vm, ao_vm_idx.
sizebigintFile size in bytes, 0 if the relation has no file.
mtimebigintModification time as a Unix timestamp, 0 if the relation has no file.
own_filebooleantrue if the file belongs to the relation named in relname. false if the file belongs to a descendant partition and is attributed to the root partitioned table.

table_sizes: total size per table. For a root partitioned table, the size includes all its descendant partitions. Each partition also appears as its own row.

ColumnTypeDescription
nspnamenameSchema name.
relnamenameTable name.
sizenumericSum of file sizes in bytes.
mtimetimestamp with time zoneLatest modification time among the files of the table.

namespace_sizes: total size per schema. Only rows with own_file = true are summed, so partitions are not counted twice.

ColumnTypeDescription
nspnamenameSchema name.
sizenumericSum of file sizes in bytes.

Example​

Collect statistics on demand and check the size of a table:

CREATE EXTENSION gp_relsizes_stats;

CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INT,
date_of_birth DATE
);

INSERT INTO employees (first_name, last_name, department_id, date_of_birth) VALUES
('John', 'Doe', 1, '1988-06-15'),
('Jane', 'Smith', 2, '1990-07-20'),
('Emily', 'Jones', 1, '1985-08-30');

SELECT relsizes_stats_schema.relsizes_collect_stats_once();

SELECT size FROM relsizes_stats_schema.table_sizes_history WHERE relname = 'employees';
size
-------
65536
(1 row)

Check the size of a schema:

SELECT size FROM relsizes_stats_schema.namespace_sizes WHERE nspname = 'public';

To allow another role to read the statistics:

GRANT USAGE ON SCHEMA relsizes_stats_schema TO user2;
GRANT SELECT ON ALL TABLES IN SCHEMA relsizes_stats_schema TO user2;

Usage notes​

  • The periodic background worker runs only when the library is loaded through shared_preload_libraries. relsizes_collect_stats_once() also works without preloading the library.
  • File sizes are collected on the segments only. Files on the coordinator are not scanned.
  • The sizes in segment_file_sizes and in the views reflect the last collection, not the current state of the files.
  • Each collection deletes all rows of table_sizes_history in the database and inserts the current table sizes with the current date. The table therefore holds the result of the latest collection that ran with gp_relsizes_stats.save_history turned on.
  • Partitioned tables (root and intermediate levels) have no files on disk. They appear in table_files with a size of 0.
  • The module is tested only on Linux.