Skip to content

Instantly share code, notes, and snippets.

@mguilherme
Last active July 23, 2018 13:15
Show Gist options
  • Save mguilherme/a6a956ecf4cdd194a58797c7f457c909 to your computer and use it in GitHub Desktop.
Save mguilherme/a6a956ecf4cdd194a58797c7f457c909 to your computer and use it in GitHub Desktop.
Script to measure the size of Postgres Table row and Data page
WITH cteTableInfo AS
(
SELECT
COUNT(1) AS ct
,SUM(length(t::text)) AS TextLength
,'PUBLIC.MY_TABLE'::regclass AS TableName
FROM PUBLIC.MY_TABLE AS t
)
,cteRowSize AS
(
SELECT ARRAY [pg_relation_size(TableName)
, pg_relation_size(TableName, 'vm')
, pg_relation_size(TableName, 'fsm')
, pg_table_size(TableName)
, pg_indexes_size(TableName)
, pg_total_relation_size(TableName)
, TextLength
] AS val
, ARRAY ['Total Relation Size'
, 'Visibility Map'
, 'Free Space Map'
, 'Table Included Toast Size'
, 'Indexes Size'
, 'Total Toast and Indexes Size'
, 'Live Row Byte Size'
] AS Name
FROM cteTableInfo
)
SELECT
unnest(name) AS Description
,unnest(val) AS Bytes
,pg_size_pretty(unnest(val)) AS BytesPretty
,unnest(val) / ct AS bytes_per_row
FROM cteTableInfo, cteRowSize
UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'TotalRows', ct, NULL, NULL FROM cteTableInfo
UNION ALL SELECT 'LiveTuples', pg_stat_get_live_tuples(TableName), NULL, NULL FROM cteTableInfo
UNION ALL SELECT 'DeadTuples', pg_stat_get_dead_tuples(TableName), NULL, NULL FROM cteTableInfo;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment