How to find the size of Tables in Confluence Database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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.
MySQL Syntax | Please replace the TABLE_SCHEMA with your schema name
1
2
3
4
5
6
7
8
9
10
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;PostGres SQL syntax
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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;Oracle Syntax | Replace the Tablespace with your TableSpace
1
2
3
4
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 DESCWas this helpful?