How to find the largest attachment files in your Confluence instance

Summary

If your instance is taking too much disk space or you'd like to check your attachment sizes to enforce some restrictions, you might want to check which users or pages have the biggest attachments. For versions of Confluence 5.4 or earlier you might have attachments stored in the database, otherwise they will be stored in your <confluence_home>/attachments/ver003 folder unless you upgraded from a previous version and did not migrate the attachments over to the filesystem.

It's easy to check by going to ⚙ -> General configuration -> Attachment Storage.

Solution

ℹ️ The examples below are for a PostgreSQL database, but your DBA can translate them to another DB engine if needed.

• If your attachments are stored in the database, you can run the following query, which will return the size of the attachment in bytes, the attachment ID, title, the user who uploaded it, and the page to which it was attached:

  SELECT octet_length(a.data) AS SIZE, a.attachmentid, c.title AS attachmentTitle, u.username AS guilty, co.title AS pageTitle FROM ATTACHMENTDATA AS a JOIN CONTENT AS c ON c.contentid = a.attachmentid JOIN USER_MAPPING AS u ON u.user_key = c.creator JOIN CONTENT AS co ON c.pageid = co.contentid ORDER BY SIZE DESC

• If you are storing the attachments in the file system, you can run this slightly modified query:

  SELECT DISTINCT c.contentid, c.spaceid, c.title AS attachmentTitle, u.username AS uploadedBy, co.title AS pageTitle, cn.longval AS bytes, s.spacekey as spacekey, s.spacename as spacename FROM CONTENT AS c JOIN USER_MAPPING AS u ON u.user_key = c.creator JOIN CONTENT AS co ON c.pageid = co.contentid JOIN spaces AS s ON c.spaceid=s.spaceid JOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid WHERE c.contenttype = 'ATTACHMENT' AND cn.longval IS NOT NULL AND cn.propertyname = 'FILESIZE' ORDER BY cn.longval DESC;