Quick Tip: Get Size of Revisions in WordPress

One thing that you might not think of when watching the size of a large WordPress site grow, is unnecessary data in the database. With the introduction of the block editor years ago, there has been a large increase in the number of revisions a post makes when being edited.

This can create a lot of revisions in the database if you’re not setting a limit.

If you’d like to do a quick and dirty audit of your revision data, you can use a very ugly SQL query like this:

SELECT COUNT( ID ) as revision_count, ( SUM( CHAR_LENGTH( ID ) ) + SUM( CHAR_LENGTH( post_author ) ) + SUM( CHAR_LENGTH( post_date ) ) + SUM( CHAR_LENGTH( post_date_gmt ) ) + SUM( CHAR_LENGTH( post_content ) ) + SUM( CHAR_LENGTH( post_title ) ) + SUM( CHAR_LENGTH( post_excerpt ) ) + SUM( CHAR_LENGTH( post_status ) ) + SUM( CHAR_LENGTH( comment_status ) ) + SUM( CHAR_LENGTH( ping_status ) ) + SUM( CHAR_LENGTH( post_password ) ) + SUM( CHAR_LENGTH( post_name ) ) + SUM( CHAR_LENGTH( to_ping ) ) + SUM( CHAR_LENGTH( pinged ) ) + SUM( CHAR_LENGTH( post_modified ) ) + SUM( CHAR_LENGTH( post_modified_gmt ) ) + SUM( CHAR_LENGTH( post_content_filtered ) ) + SUM( CHAR_LENGTH( post_parent ) ) + SUM( CHAR_LENGTH( guid ) ) + SUM( CHAR_LENGTH( menu_order ) ) + SUM( CHAR_LENGTH( post_type ) ) + SUM( CHAR_LENGTH( post_mime_type ) ) + SUM( CHAR_LENGTH( comment_count ) ) ) as post_size FROM wp_posts WHERE post_type='revision' GROUP BY post_type ORDER BY revision_count DESC;Code language: JavaScript (javascript)

This will give you the number of revisions you have, and the approximate amount of data its using in the database:

You can see now a very large WordPress site can amass a lot of unnecessary data in its database over a number of years. 2.8 Gigabytes of revisions is a lot of stuff if you’re never going to use them again.