How to find the size of Tables in Confluence Database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This document will help us identify the size of Database tables in Confluence. This is useful for some admins who want to know which table is taking maximum space and want to cleanup the Database..
This document is just for informational purpose. The cleanup of the Database should be done after consultation with Atlassian Support Team.
Solution
We can find the size of the database tables by running some SQL queries. Based on the result, we can then get the tables which takes up maximum space and then plan for the cleanup if required. Below are the queries which can be used to fetch the information.
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "ANUJCONFLUENCE"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;SELECT
relname AS "Table",
pg_size_pretty (
pg_total_relation_size (C .oid)
) AS "total_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND C .relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY
pg_total_relation_size (C .oid) DESC;select segment_name AS Tablename,sum(bytes)/1024/1024 AS SIZEINMB from dba_segments
where segment_type='TABLE' AND TABLESPACE_NAME='CONF650'
group by segment_name
ORDER BY SIZEINMB DESC