Mirth Postgres Table Size By Channel

MirthPostgresTableSizeByChannel.psql

-- 20201125T1403 EST - Fix bug in xpath not correctly matching values to correct channel
-- 20201125T1430 EST - Updated formatting only using: http://poorsql.com/ with minor hand edits
-- 20201125T1432 EST - Removed CID from final output as it was only there for debugging xpath issues
-- 20210321T1140 EST - Postgres 13+ XPATH works differently see: https://gist.github.com/MichaelLeeHobbs/40b4b7cf70ecbe30b73eed763367e626
--
-- Jon Bartels
-- Similar query for SQL Server is here - https://stackoverflow.com/questions/58942371/get-space-used-by-mirth-per-channel-in-sql-server
--

SELECT NAME AS CHANNEL_NAME
	, TOTAL_BYTES
	, CHANNEL_ID
	, PRUNE_ENABLED
	, PRUNE_DAYS
	, ARCHIVE_ENABLED
	, TABLE_SCHEMA
	, TABLE_NAME
	, MIRTH_ID
	, MIRTH_TYPE
	, ROW_ESTIMATE
	, INDEX_BYTES
	, TOAST_BYTES
	, TABLE_BYTES
FROM (
	WITH RECURSIVE PG_INHERIT(INHRELID, INHPARENT) AS (
			SELECT INHRELID, INHPARENT
			FROM PG_INHERITS
			UNION
			SELECT CHILD.INHRELID, PARENT.INHPARENT
			FROM PG_INHERIT CHILD, PG_INHERITS PARENT
			WHERE CHILD.INHPARENT = PARENT.INHRELID
			)
		, PG_INHERIT_SHORT AS (
			SELECT *
			FROM PG_INHERIT
			WHERE INHPARENT NOT IN (SELECT INHRELID FROM PG_INHERIT)
			)
	SELECT TABLE_SCHEMA
		, TABLE_NAME
		, NULLIF(REGEXP_REPLACE(TABLE_NAME, '\D', '', 'g'), '')::NUMERIC AS MIRTH_ID
		, SUBSTRING(TABLE_NAME FROM 'd_(\D+)\d+') AS MIRTH_TYPE
		, ROW_ESTIMATE
		, PG_SIZE_PRETTY(TOTAL_BYTES) AS TOTAL_BYTES
		, PG_SIZE_PRETTY(INDEX_BYTES) AS INDEX_BYTES
		, PG_SIZE_PRETTY(TOAST_BYTES) AS TOAST_BYTES
		, PG_SIZE_PRETTY(TABLE_BYTES) AS TABLE_BYTES
	FROM (
		SELECT *, TOTAL_BYTES - INDEX_BYTES - COALESCE(TOAST_BYTES, 0) AS TABLE_BYTES
		FROM (
			SELECT C.OID
				, NSPNAME AS TABLE_SCHEMA
				, RELNAME AS TABLE_NAME
				, SUM(C.RELTUPLES) OVER (PARTITION BY PARENT) AS ROW_ESTIMATE
				, SUM(PG_TOTAL_RELATION_SIZE(C.OID)) OVER (PARTITION BY PARENT) AS TOTAL_BYTES
				, SUM(PG_INDEXES_SIZE(C.OID)) OVER (PARTITION BY PARENT) AS INDEX_BYTES
				, SUM(PG_TOTAL_RELATION_SIZE(RELTOASTRELID)) OVER (PARTITION BY PARENT) AS TOAST_BYTES
				, PARENT
			FROM (
				SELECT PG_CLASS.OID
					, RELTUPLES
					, RELNAME
					, RELNAMESPACE
					, PG_CLASS.RELTOASTRELID
					, COALESCE(INHPARENT, PG_CLASS.OID) PARENT
				FROM PG_CLASS
				LEFT JOIN PG_INHERIT_SHORT ON INHRELID = OID
				WHERE RELKIND IN ('r', 'p')
				) C
			LEFT JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
			ORDER BY TOTAL_BYTES DESC
			) A
		WHERE OID = PARENT
		) A
	) TABLE_SIZES
LEFT JOIN D_CHANNELS ON D_CHANNELS.LOCAL_CHANNEL_ID = TABLE_SIZES.MIRTH_ID
LEFT JOIN CHANNEL ON CHANNEL.ID = D_CHANNELS.CHANNEL_ID
LEFT JOIN (
	SELECT (XPATH('string/text()', ENTRY)) [1]::TEXT AS CID
		, (XPATH('com.mirth.connect.model.ChannelMetadata/enabled/text()', ENTRY)) [1]::TEXT::boolean AS PRUNE_ENABLED
		, (XPATH('com.mirth.connect.model.ChannelMetadata/pruningSettings/pruneMetaDataDays/text()', ENTRY)) [1]::TEXT::INT AS PRUNE_DAYS
		, (XPATH('com.mirth.connect.model.ChannelMetadata/pruningSettings/archiveEnabled/text()', ENTRY)) [1]::TEXT::boolean AS ARCHIVE_ENABLED
	FROM (
		SELECT UNNEST(XPATH('/map/entry', VALUE::XML)) AS ENTRY
		FROM CONFIGURATION
		WHERE CATEGORY = 'core' AND NAME = 'channelMetadata'
		) X
	) AS M ON M.CID = D_CHANNELS.CHANNEL_ID;

Similar query for SQL Server is here - https://stackoverflow.com/questions/58942371/get-space-used-by-mirth-per-channel-in-sql-server