Field notes / WooCommerce core: database bloat and maintenance

Diagnostic WooCommerce core: database bloat and maintenance

Orphaned WooCommerce postmeta rows from deleted orders

An order gets deleted, but a handful of its rows in wp_postmeta do not go with it. Nothing points to them anymore, but they sit in the table anyway, quietly adding rows to a table that is already one of the biggest in most WooCommerce databases. Here is why the cleanup gets skipped and a small script that finds every orphan before you touch the database with a delete statement.

Python and Node.js Runs on a schedule Safe by default (dry run)

White signs on a metal rack — Photo by Anna Auza on Unsplash

The short answer

Postmeta rows only get cleaned up when a post is deleted the normal way, through wp_delete_post. A direct SQL delete, a bulk cleanup tool, or a crashed migration can remove the order itself while leaving its meta rows behind, orphaned, with a post_id that matches nothing. Run a small Python or Node.js script on a schedule that walks recent Stripe PaymentIntents, since Stripe still remembers metadata.order_id for orders that used to exist, and checks the WooCommerce REST API to see which of those order ids are now missing. Anything missing is an orphan candidate worth cleaning up. Full code, tests, and a dry run guard are below.

The problem in plain words

Every WooCommerce order is a WordPress post under the hood, or a row in a dedicated orders table when High Performance Order Storage (HPOS) is on, and every order carries a stack of extra data next to it: the Stripe PaymentIntent id, the billing address, the order total, custom fields from plugins. All of that extra data lives in wp_postmeta, tied to the order by its id.

When an order is deleted the way WordPress expects, through wp_delete_post, the framework also deletes every meta row attached to it. But that cleanup only happens when the deletion goes through that one function. Skip it, and the meta rows are never told the order is gone. They stay in the table, pointing at an id that no post will ever have again.

The post row is removed but the meta rows attached to it never hear about it, because the delete path that would have told them was skipped.

Why it happens

The WordPress core code for wp_delete_post makes cleaning up meta part of the normal flow, but plenty of common paths never call it. A few reasons orphaned postmeta rows build up:

A cleanup script or admin tool runs DELETE FROM wp_posts WHERE ID = ... directly against the database, which removes the post but has no idea wp_postmeta even exists.
A bulk order pruning plugin batches deletes for speed and skips the per-row hooks that would normally trigger meta cleanup.
A store migration or import script moves orders between environments and only copies wp_posts, or a failed migration leaves half the tables in an inconsistent state.
A crashed WP-Cron job or a killed process interrupts a bulk trash-and-delete run partway through, after the post row is gone but before its meta row is.
Stores on High Performance Order Storage that later move back to posts-based storage, or that ran a custom sync tool during migration, can leave stray rows from the transition.

The WordPress core ticket tracker has long-standing reports of meta rows left behind when posts are removed outside the standard delete function, and WooCommerce's own guidance on database maintenance calls out orphaned postmeta as one of the most common sources of table bloat in an aging store. See the citations at the end for the exact references.

The key insight

wp_postmeta has no foreign key to wp_posts. Nothing in MySQL stops a meta row from outliving its post. The table will not tell you when a row is orphaned, so the only way to know is to check, from the outside, whether the post_id it names is still real.

The fix, as a flow

We do not touch the database directly. We use Stripe as an outside record of "an order used to be here": the WooCommerce Stripe plugin writes metadata.order_id onto every PaymentIntent it creates, so Stripe's history still remembers order ids the shop itself may have forgotten. We list recent PaymentIntents, take each order id from the metadata, and ask the WooCommerce REST API whether that order still exists. Anything missing is reported as an orphan candidate, ready for a deliberate, separate database cleanup step.

The reporter never deletes a row itself. It hands the shop a precise, reviewed list, so the actual database cleanup stays a deliberate, separate step.

Build it step by step

Get access to both systems

You need a Stripe secret key and a WooCommerce REST API key pair (a consumer key and a consumer secret) with read access to orders. Create the WooCommerce key under WooCommerce, Settings, Advanced, REST API. Keep every value in environment variables, never in the file.

setup (shell)

pip install stripe requests

export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="90"
export DRY_RUN="true"   # start safe, this script only reports either way

setup (shell)

npm install stripe

export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="90"
export DRY_RUN="true"   // start safe, this script only reports either way

List the PaymentIntents that name an order

Ask Stripe for PaymentIntents created inside your lookback window. We page through all of them and keep only the ones whose metadata carries an order_id, since that field is our only outside link back to a WooCommerce order.

step2.py

import os, time, stripe

stripe.api_key = os.environ["STRIPE_SECRET_KEY"]

def recent_intents(lookback_days):
    since = int(time.time()) - lookback_days * 86400
    for intent in stripe.PaymentIntent.list(limit=100, created={"gte": since}).auto_paging_iter():
        if intent.metadata.get("order_id"):
            yield intent

step2.js

import Stripe from "stripe";
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

async function* recentIntents(lookbackDays) {
  const since = Math.floor(Date.now() / 1000) - lookbackDays * 86400;
  for await (const intent of stripe.paymentIntents.list({ limit: 100, created: { gte: since } })) {
    if (intent.metadata && intent.metadata.order_id) yield intent;
  }
}

Check whether the order still exists

Use the WooCommerce REST API to look up the order by id. Going through the REST API means the check works the same whether the store keeps orders as posts or has HPOS turned on, since WooCommerce handles the storage detail for you. A 404 here is the whole signal we are looking for.

step3.py

import os, requests
from requests.auth import HTTPBasicAuth

WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])

def get_order(order_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders/{order_id}", auth=AUTH, timeout=30)
    if r.status_code == 404:
        return None
    r.raise_for_status()
    return r.json()

step3.js

const WOO_URL = process.env.WOO_STORE_URL.replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
  `${process.env.WOO_CONSUMER_KEY}:${process.env.WOO_CONSUMER_SECRET}`
).toString("base64");

async function woo(path) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
    headers: { "Content-Type": "application/json", Authorization: AUTH },
  });
  if (res.status === 404) return null;
  if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
  return res.json();
}

Decide, with one pure function

Keep the decision in its own function that takes an order and an intent and returns an action. A pure function like this is easy to read and easy to test, which we do later. The rule is simple. If there is no order id to check, skip it. If the order is still there, it is fine. If it is gone, that is our orphan.

decide.py

def decide(order, intent):
    if intent is None:
        return ("skip", "no Stripe intent to check")
    order_id = intent.get("metadata", {}).get("order_id")
    if not order_id:
        return ("skip", "intent has no order_id in metadata")
    if order is None:
        return ("orphan", f"order {order_id} is gone but Stripe still references it")
    if str(order.get("id")) != str(order_id):
        return ("skip", "order id mismatch, not our concern here")
    return ("ok", "order still exists")

decide.js

export function decide(order, intent) {
  if (!intent) return ["skip", "no Stripe intent to check"];
  const orderId = intent.metadata && intent.metadata.order_id;
  if (!orderId) return ["skip", "intent has no order_id in metadata"];
  if (!order) return ["orphan", `order ${orderId} is gone but Stripe still references it`];
  if (String(order.id) !== String(orderId)) return ["skip", "order id mismatch, not our concern here"];
  return ["ok", "order still exists"];
}

Report the orphan, never delete it automatically

When the action is orphan, we only write a clear log line naming the order id and the Stripe PaymentIntent that still remembers it. Deleting the leftover row is a database job, a targeted DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts), run by hand once you trust the list, with a backup taken first. That is outside the blast radius a REST API script should ever have.

report.py

def report_orphan(order_id, intent, reason):
    log.warning(
        "Orphan candidate: order %s, PaymentIntent %s. %s",
        order_id, intent["id"], reason,
    )

report.js

function reportOrphan(orderId, intent, reason) {
  console.warn(`Orphan candidate: order ${orderId}, PaymentIntent ${intent.id}. ${reason}`);
}

Wire it together with a dry run guard

The loop ties every piece together. Even with DRY_RUN on, this script never writes to WooCommerce or Stripe, it only reads and reports. The flag is still there because the pattern is the same across every reconciler in this series, and because a future version of this script that also runs the actual cleanup SQL should default to the same safety. Run it on a schedule with cron, once a week is plenty for a slow-moving problem like this.

Run it safe

This reporter is read only by design. It never issues a delete, on Stripe, WooCommerce, or the database. Treat its output as a checklist, and run the actual wp_postmeta cleanup by hand, with a fresh backup, once you have reviewed the exact rows it names.

The full code

Here is the complete reporter in one file for each language. It reads settings from the environment, logs what it finds, respects the dry run flag, and is safe to run again and again because it never writes anywhere.

View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.

find_orphaned_postmeta.py

"""Find WooCommerce postmeta rows that point at an order which no longer exists.

Every order keeps its Stripe link in postmeta, in the key `_stripe_intent_id`, or
in the `transaction_id` column when the plugin writes it there instead. When an
order is deleted straight from wp_posts (a manual cleanup script, a bad SQL DELETE,
a plugin that skips wp_delete_post's meta cleanup) the postmeta row can survive
with nothing left to attach to. That row is now orphaned: it takes up space, it can
resurface in stale reports, and on some pages it drags in a Stripe API call for an
order the shop can never show you.

This script does not scan the database directly. It walks Stripe PaymentIntents,
since Stripe is the durable record of "an order used to exist here", and checks the
WooCommerce REST API to see whether the order it points to is still there. Anything
missing is an orphan candidate. Read only by default. Run on a schedule.
"""
import os
import time
import logging
import stripe
import requests
from requests.auth import HTTPBasicAuth

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("find_orphaned_postmeta")

stripe.api_key = os.environ.get("STRIPE_SECRET_KEY", "sk_test_dummy")
WOO_URL = os.environ.get("WOO_STORE_URL", "https://example.com").rstrip("/")
AUTH = HTTPBasicAuth(
    os.environ.get("WOO_CONSUMER_KEY", "ck_dummy"),
    os.environ.get("WOO_CONSUMER_SECRET", "cs_dummy"),
)
LOOKBACK_DAYS = int(os.environ.get("LOOKBACK_DAYS", "90"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"


def intent_id_of(order):
    """The saved Stripe PaymentIntent id, from meta _stripe_intent_id or transaction_id."""
    for meta in order.get("meta_data") or []:
        if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
            return meta["value"]
    tid = order.get("transaction_id")
    return tid if tid and tid.startswith("pi_") else None


def decide(order, intent):
    """Pure decision: no I/O, only plain data in, one action out.

    order is None when the WooCommerce REST API has nothing at that id, which is
    exactly what happens when the post row was deleted but a Stripe PaymentIntent
    still carries metadata.order_id pointing at it. That is the orphan we report.
    """
    if intent is None:
        return ("skip", "no Stripe intent to check")
    order_id = intent.get("metadata", {}).get("order_id")
    if not order_id:
        return ("skip", "intent has no order_id in metadata")
    if order is None:
        return ("orphan", f"order {order_id} is gone but Stripe still references it")
    if str(order.get("id")) != str(order_id):
        return ("skip", "order id mismatch, not our concern here")
    return ("ok", "order still exists")


def recent_intents(lookback_days):
    since = int(time.time()) - lookback_days * 86400
    for intent in stripe.PaymentIntent.list(limit=100, created={"gte": since}).auto_paging_iter():
        if intent.metadata.get("order_id"):
            yield intent


def get_order(order_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/orders/{order_id}", auth=AUTH, timeout=30)
    if r.status_code == 404:
        return None
    r.raise_for_status()
    return r.json()


def report_orphan(order_id, intent, reason):
    """Read only: write a line to the log. This never deletes anything on its own.

    Cleaning the leftover postmeta rows is a database job (DELETE FROM wp_postmeta
    WHERE post_id NOT IN (SELECT ID FROM wp_posts)), which is outside what a REST
    API script should attempt. This function's job is to hand the shop a precise,
    reviewed list so that cleanup step is safe to run.
    """
    log.warning(
        "Orphan candidate: order %s, PaymentIntent %s. %s",
        order_id, intent["id"], reason,
    )


def run():
    orphans = 0
    for intent in recent_intents(LOOKBACK_DAYS):
        order_id = intent.metadata["order_id"]
        order = get_order(order_id)
        action, reason = decide(order, intent)
        if action != "orphan":
            continue
        log.info("Order %s: %s. %s", order_id, reason, "would report" if DRY_RUN else "reporting")
        report_orphan(order_id, intent, reason)
        orphans += 1
    log.info("Done. %d orphan candidate(s) found.", orphans)


if __name__ == "__main__":
    run()

find-orphaned-postmeta.js

/**
 * Find WooCommerce postmeta rows that point at an order which no longer exists.
 *
 * Every order keeps its Stripe link in postmeta, in the key `_stripe_intent_id`, or
 * in the `transaction_id` column when the plugin writes it there instead. When an
 * order is deleted straight from wp_posts (a manual cleanup script, a bad SQL DELETE,
 * a plugin that skips wp_delete_post's meta cleanup) the postmeta row can survive
 * with nothing left to attach to. That row is now orphaned: it takes up space, it
 * can resurface in stale reports, and on some pages it drags in a Stripe API call
 * for an order the shop can never show you.
 *
 * This script does not scan the database directly. It walks Stripe PaymentIntents,
 * since Stripe is the durable record of "an order used to exist here", and checks
 * the WooCommerce REST API to see whether the order it points to is still there.
 * Anything missing is an orphan candidate. Read only by default. Run on a schedule.
 *
 * Guide: https://www.allanninal.dev/woocommerce/orphaned-postmeta-rows/
 */
import Stripe from "stripe";
import { pathToFileURL } from "node:url";

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY || "sk_test_dummy");
const WOO_URL = (process.env.WOO_STORE_URL || "https://example.com").replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
  `${process.env.WOO_CONSUMER_KEY || "ck_dummy"}:${process.env.WOO_CONSUMER_SECRET || "cs_dummy"}`
).toString("base64");
const LOOKBACK_DAYS = Number(process.env.LOOKBACK_DAYS || 90);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

export function intentIdOf(order) {
  for (const meta of order.meta_data || []) {
    if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
  }
  const tid = order.transaction_id;
  return tid && tid.startsWith("pi_") ? tid : null;
}

/**
 * Pure decision: no I/O, only plain data in, one action out.
 *
 * order is null when the WooCommerce REST API has nothing at that id, which is
 * exactly what happens when the post row was deleted but a Stripe PaymentIntent
 * still carries metadata.order_id pointing at it. That is the orphan we report.
 */
export function decide(order, intent) {
  if (!intent) return ["skip", "no Stripe intent to check"];
  const orderId = intent.metadata && intent.metadata.order_id;
  if (!orderId) return ["skip", "intent has no order_id in metadata"];
  if (!order) return ["orphan", `order ${orderId} is gone but Stripe still references it`];
  if (String(order.id) !== String(orderId)) return ["skip", "order id mismatch, not our concern here"];
  return ["ok", "order still exists"];
}

async function woo(path) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
    headers: { "Content-Type": "application/json", Authorization: AUTH },
  });
  if (res.status === 404) return null;
  if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
  return res.json();
}

async function* recentIntents(lookbackDays) {
  const since = Math.floor(Date.now() / 1000) - lookbackDays * 86400;
  for await (const intent of stripe.paymentIntents.list({ limit: 100, created: { gte: since } })) {
    if (intent.metadata && intent.metadata.order_id) yield intent;
  }
}

/**
 * Read only: write a line to the log. This never deletes anything on its own.
 *
 * Cleaning the leftover postmeta rows is a database job (DELETE FROM wp_postmeta
 * WHERE post_id NOT IN (SELECT ID FROM wp_posts)), which is outside what a REST
 * API script should attempt. This function's job is to hand the shop a precise,
 * reviewed list so that cleanup step is safe to run.
 */
function reportOrphan(orderId, intent, reason) {
  console.warn(`Orphan candidate: order ${orderId}, PaymentIntent ${intent.id}. ${reason}`);
}

export async function run() {
  let orphans = 0;
  for await (const intent of recentIntents(LOOKBACK_DAYS)) {
    const orderId = intent.metadata.order_id;
    const order = await woo(`/orders/${orderId}`);
    const [action, reason] = decide(order, intent);
    if (action !== "orphan") continue;
    console.log(`Order ${orderId}: ${reason}. ${DRY_RUN ? "would report" : "reporting"}`);
    reportOrphan(orderId, intent, reason);
    orphans++;
  }
  console.log(`Done. ${orphans} orphan candidate(s) found.`);
}

if (import.meta.url === pathToFileURL(process.argv[1]).href) {
  run().catch((err) => { console.error(err); process.exit(1); });
}

Add a test

The decision rule is the part most worth testing, because it decides which orders get named as orphan candidates. Because we kept decide pure, the test needs no network and no Stripe account. It just feeds in plain objects and checks the action.

test_postmeta_orphan_decide.py

from find_orphaned_postmeta import decide, intent_id_of


def intent(**over):
    base = {"id": "pi_1", "metadata": {"order_id": "501"}}
    base.update(over)
    return base


def test_orphan_when_order_missing():
    assert decide(None, intent())[0] == "orphan"


def test_ok_when_order_still_exists():
    order = {"id": 501}
    assert decide(order, intent())[0] == "ok"


def test_skip_when_no_intent():
    assert decide({"id": 501}, None)[0] == "skip"


def test_skip_when_intent_has_no_order_id():
    assert decide(None, intent(metadata={}))[0] == "skip"


def test_skip_when_order_id_mismatch():
    order = {"id": 999}
    assert decide(order, intent())[0] == "skip"

find-orphaned-postmeta.test.js

import { test } from "node:test";
import assert from "node:assert/strict";
import { decide, intentIdOf } from "./find-orphaned-postmeta.js";

const intent = (over = {}) => ({ id: "pi_1", metadata: { order_id: "501" }, ...over });

test("orphan when order missing", () => {
  assert.equal(decide(null, intent())[0], "orphan");
});

test("ok when order still exists", () => {
  assert.equal(decide({ id: 501 }, intent())[0], "ok");
});

test("skip when no intent", () => {
  assert.equal(decide({ id: 501 }, null)[0], "skip");
});

test("skip when intent has no order_id", () => {
  assert.equal(decide(null, intent({ metadata: {} }))[0], "skip");
});

test("skip when order id mismatch", () => {
  assert.equal(decide({ id: 999 }, intent())[0], "skip");
});

Case studies

Bulk cleanup gone wrong

The pruning plugin that skipped the hooks

A store installed a plugin promising to "clean up old orders fast" ahead of a busy season. It ran a bulk SQL delete against thousands of trashed orders to save time, which removed the post rows but never touched their postmeta. Six months later, wp_postmeta had grown past four million rows and nightly backups were taking twice as long.

The reporter run against ninety days of Stripe history turned up a small, recent sample of the pattern, enough to confirm the theory. A one-time audit query against the full table found the rest, and a reviewed database cleanup brought the table back down.

Failed migration

The store move that half-finished

A shop moved hosts mid-migration and the import script crashed partway through a batch of order deletions on the old server, which was meant to be decommissioned right after. The crash left the posts gone on the source database but the postmeta import to the new one had already copied rows tied to orders that were being deleted concurrently.

Running the reporter after the move surfaced a short, exact list of order ids with no matching order, which the team cross-checked against their migration log before running a single cleanup pass.

What good looks like

After you run this once and clean the table it finds, run the reporter again on a slow schedule, weekly is plenty. Orphaned postmeta rows build up quietly over months, not minutes, so catching a handful early is far easier than facing a multi-million row table during your next big migration or plugin audit.

FAQ

What is an orphaned postmeta row in WooCommerce?

It is a row in wp_postmeta whose post_id points to a post, usually an order, that no longer exists in wp_posts. The order was deleted but its meta rows, like the Stripe intent id or the order total, were never cleaned up, so they sit in the table with nothing to attach to.

Why does this happen if WordPress is supposed to clean up meta on delete?

wp_delete_post does clean up meta when it runs the normal way, but a direct SQL DELETE against wp_posts, a bulk cleanup plugin, a failed migration, or a crashed cron job can remove the post row without ever calling that function, leaving the meta behind.

Is it safe to delete orphaned postmeta rows with a script?

Only after you confirm the parent post is really gone and not just temporarily missing from an index. Start in dry run mode, review the exact list of candidates the script finds, and run the actual database cleanup as a separate, deliberate step.

Related field notes

Citations

On the problem:

WordPress Trac: postmeta rows left behind when a post is deleted outside the standard delete function. core.trac.wordpress.org/ticket/38804
WordPress Developer Reference: wp_delete_post and how it triggers meta and term relationship cleanup. developer.wordpress.org/reference/functions/wp_delete_post
WPBeginner: how to clean orphaned postmeta and other leftover rows from a WordPress database. wpbeginner.com

On the solution:

Stripe API: list PaymentIntents with auto pagination and a created filter. docs.stripe.com/api/payment_intents/list
WooCommerce REST API: retrieve a single order and get a 404 for a deleted one. woocommerce.github.io/woocommerce-rest-api-docs
WooCommerce docs: database maintenance and keeping the postmeta and options tables lean. woocommerce.com/document/database-maintenance

Stuck on a tricky one?

If you have a bug in WooCommerce, WooCommerce Subscriptions, or the WooCommerce Stripe gateway that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.

Contact me on LinkedIn

Did this clear out your database bloat?

If this saved you a slow backup or a scary migration, you can buy me a coffee. It is the best way to keep these field notes free and growing.

Buy me a coffee on Ko-fi

Back to all WooCommerce and Stripe field notes