MariaDB WordPress Housekeeping and Cleanup

Every WordPress (or other LAMP-stack CMS) installation running on MariaDB accumulates the same three kinds of bloat over time: expired transient cache entries in wp_options, spam comments that were never purged, and post revisions that pile up indefinitely. Left alone, these rows slow down every query that touches those tables and quietly grow the database file. This blueprint runs the cleanup as a scheduled, auditable job instead of a wp-admin plugin or a cron-triggered PHP script: it counts what is about to be removed, deletes it inside a single transaction, and reclaims the freed space with OPTIMIZE TABLE.

How it works

1. count_stale_rows (io.kestra.plugin.jdbc.mariadb.Query) runs one UNION ALL query that counts expired transients, spam comments, and post revisions older than revision_retention_days, and fetches all three counts in a single round trip.
2. purge_stale_data (io.kestra.plugin.jdbc.mariadb.Queries) runs the three matching DELETE statements. With transaction: true (the default), a failure on any one of the three statements rolls back all of them, so the database never ends up with only a partial cleanup applied.
3. reclaim_disk_space (io.kestra.plugin.jdbc.mariadb.Queries) runs OPTIMIZE TABLE against wp_options, wp_comments, and wp_posts to rebuild the tables and return the freed space to the filesystem instead of leaving it as unused page space.
4. notify (io.kestra.plugin.slack.notifications.SlackIncomingWebhook) reports the three row counts from step 1 (which match what was purged, since the delete predicates are identical to the count predicates). The errors block posts a separate Slack alert if any step fails, calling out that the transaction rolled back.

What you get

• A single transaction covering all three deletes, so a mid-run failure never leaves the database partially cleaned.
• A before-and-after row count for every housekeeping run, posted to Slack, instead of a silent cron job.
• Disk space actually reclaimed via OPTIMIZE TABLE, not just rows marked as deleted.
• A configurable revision retention window (revision_retention_days) instead of a hardcoded cutoff.

Who it's for

• Platform and ops teams running WordPress, Drupal, or other LAMP-stack applications on MariaDB.
• Teams who currently rely on a WP-CLI cron job or a database-cleanup plugin and want the same maintenance run with logging, retries, and alerting.
• Anyone who has watched wp_options or wp_posts grow for months and wants a repeatable, observable way to trim it back.

Why orchestrate this with Kestra

A WordPress cron job or a database-cleanup plugin can run the same DELETE statements, but it has no built-in transaction guarantee across multiple statements, no execution history to check what was purged last week, and no separate alert path when the job fails partway through. Kestra wraps the three deletes in one JDBC transaction via io.kestra.plugin.jdbc.mariadb.Queries, keeps a full log and row count for every run, and posts to a different Slack message on failure than on success, so a failed cleanup is never silent.

Prerequisites

• A MariaDB database backing a WordPress-style schema with wp_options, wp_comments, and wp_posts tables (adjust table names if your prefix differs).
• A database user with SELECT, DELETE, and ALTER (for OPTIMIZE TABLE) privileges on those tables.
• A Slack incoming webhook for notifications.

Secrets

• MARIADB_URL: JDBC URL for the database, for example jdbc:mariadb://db-host:3306/wordpress?allowMultiQueries=true. The allowMultiQueries=true parameter is required for the Queries tasks to run multiple statements.
• MARIADB_USERNAME / MARIADB_PASSWORD: credentials for the database user.
• SLACK_WEBHOOK_URL: Slack incoming webhook URL.

Quick start

1. Add the secrets above to your Kestra namespace, making sure the JDBC URL includes allowMultiQueries=true.
2. Adjust the table names in count_stale_rows and purge_stale_data if your installation uses a custom table prefix.
3. Run the flow once manually and confirm the Slack message matches what you expect before enabling the schedule.
4. Enable the weekly_housekeeping trigger (or change the cron to match your maintenance window).

How to extend

• Add a WHERE post_status != 'inherit' style condition or extra tables (for example, wp_postmeta orphan rows) as additional DELETE statements in purge_stale_data.
• Replace spam comment deletion with a soft-delete (UPDATE ... SET comment_approved = 'trash') if you want a recovery window before permanent removal.
• Feed the row counts from count_stale_rows into a io.kestra.plugin.core.flow.If task to skip the Slack notification when nothing was purged.
• Chain a cross-database sync task after reclaim_disk_space to mirror the cleaned tables into a reporting warehouse.

Links

• Plugin docs: https://kestra.io/plugins/plugin-jdbc-mariadb