How to query the database to find the size of all page drafts per space
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
Confluence administrators may want to audit draft page usage on their instance.
Solution
The following query will identify the number of pages with a status of "draft" and the total size they take up in the database, per space:
Postgres
select
count(content.contentid) as number_of_drafts,
pg_size_pretty(sum(pg_column_size(bodycontent.body))) as total_size_of_drafts,
spaces.spacename as space_name
from bodycontent
inner join content on (content.contentid = bodycontent.contentid)
inner join spaces on (content.spaceid = spaces.spaceid)
where bodycontent.contentid in
(select contentid from CONTENT where CONTENT_STATUS = 'draft' and CONTENTTYPE = 'PAGE')
GROUP BY space_name
ORDER BY number_of_drafts DESC, space_name;
MySQL
SELECT
COUNT(CONTENT.CONTENTID) AS NUMBER_OF_DRAFTS,
SUM(LENGTH(BODYCONTENT.BODY)) AS TOTAL_SIZE_OF_DRAFTS,
SPACES.SPACENAME AS SPACE_NAME
FROM BODYCONTENT
INNER JOIN CONTENT ON (CONTENT.CONTENTID = BODYCONTENT.CONTENTID)
INNER JOIN SPACES ON (CONTENT.SPACEID = SPACES.SPACEID)
WHERE BODYCONTENT.CONTENTID IN
(SELECT CONTENTID FROM CONTENT WHERE CONTENT_STATUS = 'draft' and CONTENTTYPE = 'PAGE')
GROUP BY SPACE_NAME
ORDER BY NUMBER_OF_DRAFTS DESC, SPACE_NAME;
The query above is written for PostgresSQL and MySQL databases and may require adjustment for other platforms.
Last modified on Jun 22, 2023
Powered by Confluence and Scroll Viewport.