Managing your Drupal database revision table

Issue

The remarkable Node revision functionality in Drupal creates data protection and is beneficial for any organization. However, editors add, create and update content all the time; this can make node revisions unwieldy, causing a Drupal database volume to fill with items that are out of date and not needed.

Resolution

To negate revision tables from becoming excessively bloated, you can delete older revisions. However, to find out the size of your revision tables, two options are open to a developer.

1. Use a tool like Workbench

2. Log in via SSH and use `drush sqlc` to access the Mysql prompt. 

As an example using `drush sqlc`  this is what you may see in your testing. Firstly display all databases to find the schema that will need evaluating.

mysql> SHOW DATABASES;

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| thedrupaldb        | 
+--------------------+
2 rows in set (0.00 sec)

Run the command below changing "thedruapaldb" to a schema of your choice.

SELECT TABLE_NAME AS "Table Name", 
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'thedrupaldb'

Find the node revision and field revision tables and review their corresponding sizes. In the example below the sizes are very large.

| field_revision_field_archived    | 668596 | 209616.00 | 
| field_revision_field_author      | 0      | 128.00    | 
| field_revision_field_banner_body | 8      | 128.00    | 
... 
| field_revision_field_blog_body   | 4652   | 59520.00  | 
... 
| node_revision.                   | 689476 | 210384.00 |
...

To automate the removal of older content revisions in order to minimise the size of your database, we recommend the Node Revision Delete module.

When installing the module, you will see these settings per Content Type:

We strongly recommend configuring the module as depicted above in order to keep revision tables' sizes in check.

Configuring a time-based rule can still result in a huge number of revisions assuming your editorial team makes a lot of changes during the configured time period.

As always, please test the module before moving it into production as once data is removed from older revisions it cannot be reclaimed in any way other than restoring older database backups.