The two largest sources of bloat.
REVISIONS
WordPress stores a copy of a post every time it is saved, indefinitely.
A post edited fifty times has fifty rows in wp_posts, each with metadata.
LIMITING THEM
In wp-config.php:
define('WP_POST_REVISIONS', 3);Three is ample. This prevents future growth.
REMOVING EXISTING ONES
Use a maintenance plugin, which removes the associated metadata as well.
Deleting directly from wp_posts leaves orphaned rows in wp_postmeta.
TRANSIENTS
Temporary cached values stored in wp_options, with an expiry.
Expired ones are supposed to be cleaned and frequently are not, particularly where cron is not running.
REMOVING THEM
A maintenance plugin, or a query removing rows whose names contain _transient_ and are expired.
Back up first.
THE CRON CONNECTION
Transient cleanup depends on scheduled tasks. On our servers a real cron job is required, since HTTP wp-cron is blocked.
If transients accumulate endlessly, cron is the underlying cause.
AFTERWARDS
Optimise the affected tables.
