How to reduce the size of Synchrony tables
From Confluence 6.0 to 6.15, Synchrony tables are append-only. As the tables increase in size they may become too large for some databases. There are also cases where content reconciliation may fail and it is not possible to publish new content.
Note: In Confluence 7.0 and later, two scheduled jobs are available to evict Synchrony data on a regular basis. See How to remove Synchrony data for more information.
The Synchrony tables can be truncated to resolve the issue.
Please note that turning Collaborative editing OFF and ON again may remove any unsaved drafts that were not synchronized between Confluence and Synchrony tables. You may refer to this page - How Do Drafts Work on Confluence for detailed information regarding drafts. Please carefully follow the steps below to avoid that situation.
Alwaysyour data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
- To avoid losing unsaved changes, take one of the actions below during a maintenance window:
- Have the users publish or discard the changes on the editor before the maintenance, or
- Put Confluence on read-only mode, or
- Remove Confluence from the Load Balancer or Reverse proxy to avoid users accessing the application and making changes (recommended approach)
- Since contents from active editor sessions are synchronized between Confluence and Synchrony tables every 30 seconds, wait for a few minutes to allow this sync to finish
- Turn OFF Collaborative Editing
- Stop Confluence
- Truncate the tables:
Don't do this while collaborative editing is on
TRUNCATE TABLE "EVENTS"; TRUNCATE TABLE "SECRETS"; TRUNCATE TABLE "SNAPSHOTS";
- If running Confluence with MS SQL Server, consider shrinking the database file to recoup disk space.
- Check the following KB articles from Microsoft about this operation and talk to your DBA.
- Start Confluence
- Turn ON collaborative editing.
The strategy of removing access to the application and waiting a few minutes should be enough to have the data in the tables above synchronized with the Confluence tables (
BODYCONTENT). With copies of the drafts saved, it is safe to clean the Synchrony tables. Also, it is important to stop Confluence as well because any change outside the editor also affects those tables. Marking tasks as completed and adding inline comments are examples of such actions.
In the event that you skip one of the steps above and end up losing any draft changes, use the following KB to recover them from the database backup: