View pg_table_bloat

Creates a view to monitor table bloat. Partitioned tables are excluded as they do not contain data and therefore are not affected by bloat, the partitions are included as they are tables.

This view needs pgstattuple to be installed. When the extension is not installed, the view will not be installed by the extension.

Columns

Column Name	datatype	Description
schema_name	name	The name of the schema.
table_name	name	The name of the table.
table_owner	name	The name of the role that owns the table.
table_length_in_bytes	bigint	The physical table size in bytes.
tuple_count	bigint	The number of live tuples.
tuple_length_in_bytes	bigint	The total length of live tuples in bytes.
tuple_percent	double precision	The percentage of live tuples compared to dead tuples.
dead_tuple_count	bigint	The number of dead tuples.
dead_tuple_length_in_bytes	bigint	The total length of dead tuples in bytes.
dead_tuple_percent	double precision	The percentage of dead tuples compared to live tuples.
free_space	bigint	The total free space in bytes of this table.
free_percent	double precision	The percentage of free space of this tuple compared to used space.

Example

SELECT *
FROM pg_table_bloat;

Result:

schema_name	table_name	table_owner	table_length_in_bytes	tuple_count	tuple_length_in_bytes	tuple_percent	dead_tuple_count	dead_tuple_length_in_bytes	dead_tuple_percent	free_space	free_percent
timetravel	timetravel_2024	stefanie	28573696	163366	18787090	65.75	2	230	0.0	7975832	27.91
timetravel	timetravel_part_vals	stefanie	8192	1	48	0.59	0	0	0.0	8112	99.02
timetravel	timetravel_pk	stefanie	4431872	100000	4000000	90.26	0	0	0.0	16724	0.38