hypertable_approximate_size()

Get the approximate total disk space used by a hypertable

Since 2.14.0

Get the approximate total disk space used by a or , that is, the sum of the size for the table itself including s, any indexes on the table, and any toast tables. The size is reported in bytes. This is equivalent to computing the sum of total_bytes column from the output of hypertable_approximate_detailed_size function.

When a name is provided, the function transparently looks up the backing and returns its statistics instead.

This function relies on the per backend caching using the in-built storage manager layer to compute the approximate size cheaply. The PG cache invalidation clears off the cached size for a when DML happens into it. That size cache is thus able to get the latest size in a matter of minutes. Also, due to the backend caching, any long running session will only fetch latest data for new or modified s and can use the cached data (which is calculated afresh the first time around) effectively for older s. Thus it is recommended to use a single connected backend session to compute the approximate sizes of s to get faster results.

For more information about using s, including size partitioning, see the hypertable section.

Samples

Get the approximate size information for a .

SELECT * FROM hypertable_approximate_size('devices');
 hypertable_approximate_size
-----------------------------
                        8192

Get the approximate size information for all s.

SELECT hypertable_name, hypertable_approximate_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)
  FROM timescaledb_information.hypertables;

Get the approximate size information for a .

SELECT hypertable_approximate_size('device_stats_15m');

 hypertable_approximate_size
-----------------------------
                        8192

Arguments

The syntax is:

SELECT hypertable_approximate_size('<hypertable_name>');

Name	Type	Default	Required	Description
hypertable	REGCLASS	-	✔	Hypertable or continuous aggregate to show size of.

Returns

Column	Type	Description
hypertable_approximate_size	BIGINT	Total approximate disk space used by the specified , including all indexes and TOAST data

NULL is returned if the function is executed on a non- relation.