For certain Confluence versions, you may encounter performance issues related to orphaned drafts. The problem relates to Confluence and its (1) inability to index drafts and (2) accumulation of orphaned drafts.

Atlassian Notes

See Atlassian Answers for some background on the problem.

As the number of orphaned drafts grows, you may see an impact on site performance. To remedy this issue, you can remove orphaned drafts from your system.

View Orphaned Drafts

To gather a complete view of orphaned drafts, run the following query.

View Orphaned Drafts
select count(*) from CONTENT where CONTENTTYPE='DRAFT' and DRAFTPAGEID='0'

Remove Orphaned Drafts

See Atlassian's documentation for removing orphaned drafts.

Be sure to backup your home directory and database first.

If you continue to experience performance issues, you may need to remove old drafts from your system.

View Drafts

To see a list of drafts, run the following queries.

View All Drafts
select count(*) from CONTENT where CONTENTTYPE='DRAFT'
View Drafts by Last Modification Date
select * from CONTENT where CONTENTTYPE='DRAFT' and LASTMODDATE < '2014-03-31' 

Remove Old Drafts

After determining (1) the number of drafts in your system and (2) patterns that hint at draft usage, you can determine a date range from which to remove drafts. 

Before deleting any drafts, you should inform users and backup your home directory and database first.

  1. Stop Confluence.
  2. Delete all drafts that are older than certain date. For example:

    delete from CONTENT where CONTENTTYPE='DRAFT' and LASTMODDATE < '2014-08-31'
  3. Restart Confluence.