Field notes / WooCommerce core: database bloat and maintenance

Repair WooCommerce core: database bloat and maintenance

Persistent cart data never cleared

Every time a logged in customer touches their cart, WooCommerce saves a copy to a row in wp_usermeta called the persistent cart, so the cart survives a logout or a new device. Nothing ever deletes that row. Years later, a store can have hundreds of thousands of customer accounts each holding a stale cart full of products that were discontinued long ago. Here is why it happens and a small script that finds the stale carts and clears them.

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

Gray shopping cart — Photo by David Clarke on Unsplash

The short answer

WooCommerce writes a customer's cart to user meta key _woocommerce_persistent_cart_<blog_id> so it survives across sessions, but it never removes that meta once the cart is abandoned or checked out. Run a small Python or Node.js job on a schedule that lists customers from the WooCommerce REST API, checks how long it has been since each one last ordered, and clears the persistent cart meta for anyone who has gone quiet past a threshold. Full code, tests, and a dry run guard are below.

The problem in plain words

When a signed in shopper adds something to their cart, WooCommerce keeps a copy of that cart attached to their user account, not just their browser session. That is a nice feature. Add a shirt on your phone, come back on your laptop, and the shirt is still there. The store does this by writing the cart contents into wp_usermeta under a key like _woocommerce_persistent_cart_1, updated on every add, remove, or quantity change.

The trouble is what happens after that. If the shopper checks out, the cart empties and the meta row updates to reflect an empty cart, which is fine. But if the shopper just leaves, closes the tab, and never comes back, that row keeps whatever they left in it, forever. There is no expiry, no cron job, and no admin screen that clears it. A store with ten years of history and a few hundred thousand registered customers ends up with a few hundred thousand of these rows, many pointing at products that were deleted seasons ago.

The cart is saved the moment it changes. Nothing ever removes it once the customer stops shopping, so the row accumulates for the life of the account.

Why it happens

This is a known and long documented behavior in WooCommerce core, not a bug that was introduced recently. A few reasons it goes unnoticed for years:

The feature that saves the cart, WC_Cart_Session::persistent_cart_update, is designed to make carts durable, not disposable, so nothing about it was ever meant to expire.
There is no core cron event, no scheduled action, and no wp-admin tool that walks wp_usermeta looking for stale _woocommerce_persistent_cart_* rows.
Each row is small, so a handful of them never register as a problem. It is only after years of registrations that the table noticeably grows and query plans start favoring full scans over the index.
The saved cart can reference product or variation IDs that were deleted long ago, so even if a customer did return, WooCommerce quietly drops the missing items instead of restoring a broken cart, leaving a small trail of orphaned references behind either way.

Store owners usually find this the hard way, during a database size audit or a slow admin user list screen, when a support agent asks why wp_usermeta is nineteen gigabytes on a shop that barely sells a hundred orders a month. See the citations at the end for the exact reports.

The key insight

A persistent cart is only useful while the customer might still come back for it. Once enough time has passed with no order and no fresh cart activity, the saved cart is not a feature anymore, it is just dead weight in wp_usermeta. A cleanup job is a safety net that runs on a schedule, checks how long a customer has been quiet, and clears the row once it is safely past being useful.

The fix, as a flow

We do not touch the live cart or checkout. We add a job that runs weekly, lists customers through the WooCommerce REST API, and for each one looks at the persistent cart meta already exposed on the customer object plus their most recent order date. If the cart meta holds actual line items and the customer has been inactive past a threshold, we clear that one meta value on the customer, the same effect as if their cart had simply expired.

The cleanup job only clears carts that both hold real items and belong to a customer who has been inactive past the threshold. Everything else is left alone.

Build it step by step

Get access to the store

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

setup (shell)

pip install requests

export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export STALE_DAYS="180"
export DRY_RUN="true"   # start safe, change to false to write

setup (shell)

npm install node-fetch

export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export STALE_DAYS="180"
export DRY_RUN="true"   // start safe, change to false to write

List customers and read their cart meta

The WooCommerce REST API returns a customer's meta_data array, which includes the persistent cart key when it is set. We page through every customer and only keep the ones where that meta actually holds cart line items, since an empty or missing cart needs no cleanup.

step2.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"])
CART_META_PREFIX = "_woocommerce_persistent_cart_"

def customers():
    page = 1
    while True:
        r = requests.get(
            f"{WOO_URL}/wp-json/wc/v3/customers",
            params={"per_page": 50, "page": page, "orderby": "registered_date"},
            auth=AUTH, timeout=30,
        )
        r.raise_for_status()
        batch = r.json()
        if not batch:
            return
        for customer in batch:
            yield customer
        page += 1

def cart_meta_of(customer):
    for meta in customer.get("meta_data") or []:
        if str(meta.get("key", "")).startswith(CART_META_PREFIX):
            return meta
    return None

step2.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");
const CART_META_PREFIX = "_woocommerce_persistent_cart_";

async function* customers() {
  let page = 1;
  while (true) {
    const res = await fetch(
      `${WOO_URL}/wp-json/wc/v3/customers?per_page=50&page=${page}&orderby=registered_date`,
      { headers: { Authorization: AUTH } }
    );
    if (!res.ok) throw new Error(`Woo customers returned ${res.status}`);
    const batch = await res.json();
    if (!batch.length) return;
    for (const customer of batch) yield customer;
    page++;
  }
}

function cartMetaOf(customer) {
  for (const meta of customer.meta_data || []) {
    if (String(meta.key || "").startsWith(CART_META_PREFIX)) return meta;
  }
  return null;
}

Find out how long the customer has been quiet

We ask the WooCommerce REST API for the customer's most recent order. If there is one, its date is the best signal of recent activity. If there is none, we fall back to the account's registration date, since a customer who registered and never ordered has been quiet since day one.

step3.py

def last_activity(customer):
    r = requests.get(
        f"{WOO_URL}/wp-json/wc/v3/orders",
        params={"customer": customer["id"], "per_page": 1, "orderby": "date", "order": "desc"},
        auth=AUTH, timeout=30,
    )
    r.raise_for_status()
    orders = r.json()
    if orders:
        return orders[0]["date_created"]
    return customer.get("date_created")

step3.js

async function lastActivity(customer) {
  const res = await fetch(
    `${WOO_URL}/wp-json/wc/v3/orders?customer=${customer.id}&per_page=1&orderby=date&order=desc`,
    { headers: { Authorization: AUTH } }
  );
  if (!res.ok) throw new Error(`Woo orders returned ${res.status}`);
  const orders = await res.json();
  if (orders.length) return orders[0].date_created;
  return customer.date_created;
}

Decide, with one pure function

Keep the decision in its own function that takes the cart meta and a quiet-days number 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 cart meta, skip it. If the cart is empty, skip it. If the customer has not been quiet long enough, skip it. Otherwise, clear it.

decide.py

def cart_has_items(cart_meta):
    value = (cart_meta or {}).get("value")
    if not value or not isinstance(value, dict):
        return False
    cart = value.get("cart")
    return bool(cart)

def decide(cart_meta, days_quiet, stale_days):
    if cart_meta is None:
        return ("skip", "no persistent cart meta")
    if not cart_has_items(cart_meta):
        return ("skip", "cart meta is empty")
    if days_quiet is None or days_quiet < stale_days:
        return ("skip", "customer has not been quiet long enough")
    return ("clear", f"quiet for {days_quiet} days, past the {stale_days} day threshold")

decide.js

export function cartHasItems(cartMeta) {
  const value = cartMeta ? cartMeta.value : null;
  if (!value || typeof value !== "object") return false;
  return Boolean(value.cart && Object.keys(value.cart).length);
}

export function decide(cartMeta, daysQuiet, staleDays) {
  if (!cartMeta) return ["skip", "no persistent cart meta"];
  if (!cartHasItems(cartMeta)) return ["skip", "cart meta is empty"];
  if (daysQuiet === null || daysQuiet === undefined || daysQuiet < staleDays) {
    return ["skip", "customer has not been quiet long enough"];
  }
  return ["clear", `quiet for ${daysQuiet} days, past the ${staleDays} day threshold`];
}

Clear the stale cart meta

When the action is clear, update the customer through the REST API and set the persistent cart meta value to an empty string. That is the same shape WooCommerce writes when a cart is emptied at checkout, so nothing about the customer account looks broken, the cart is simply gone the next time they sign in.

apply.py

def clear_cart(customer_id, meta_key):
    requests.put(
        f"{WOO_URL}/wp-json/wc/v3/customers/{customer_id}",
        json={"meta_data": [{"key": meta_key, "value": ""}]},
        auth=AUTH, timeout=30,
    ).raise_for_status()

apply.js

async function clearCart(customerId, metaKey) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3/customers/${customerId}`, {
    method: "PUT",
    headers: { "Content-Type": "application/json", Authorization: AUTH },
    body: JSON.stringify({ meta_data: [{ key: metaKey, value: "" }] }),
  });
  if (!res.ok) throw new Error(`Woo customer update returned ${res.status}`);
}

Wire it together with a dry run guard

The loop ties every piece together. Notice the dry run guard. On the first few runs, leave DRY_RUN on so the script only reports what it would clear. Read the output, trust it, then switch it off to let it write. Run it on a schedule with cron once a week.

Run it safe

Always start with DRY_RUN=true. Clearing cart meta is reversible in the sense that the customer just builds a new cart, but you still want to see the plan before it acts, especially the first time on a store with a large customer table.

The full code

Here is the complete cleanup job in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, and is safe to run again and again because it only ever clears carts that are both non-empty and past the quiet threshold.

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

clear_stale_carts.py

"""Clear stale WooCommerce persistent cart meta from wp_usermeta.
Run on a schedule. Safe to run again and again.
"""
import os
import logging
from datetime import datetime, timezone
import requests
from requests.auth import HTTPBasicAuth

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

WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
STALE_DAYS = int(os.environ.get("STALE_DAYS", "180"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"

CART_META_PREFIX = "_woocommerce_persistent_cart_"


def customers():
    page = 1
    while True:
        r = requests.get(
            f"{WOO_URL}/wp-json/wc/v3/customers",
            params={"per_page": 50, "page": page, "orderby": "registered_date"},
            auth=AUTH, timeout=30,
        )
        r.raise_for_status()
        batch = r.json()
        if not batch:
            return
        for customer in batch:
            yield customer
        page += 1


def cart_meta_of(customer):
    for meta in customer.get("meta_data") or []:
        if str(meta.get("key", "")).startswith(CART_META_PREFIX):
            return meta
    return None


def last_activity(customer):
    r = requests.get(
        f"{WOO_URL}/wp-json/wc/v3/orders",
        params={"customer": customer["id"], "per_page": 1, "orderby": "date", "order": "desc"},
        auth=AUTH, timeout=30,
    )
    r.raise_for_status()
    orders = r.json()
    if orders:
        return orders[0]["date_created"]
    return customer.get("date_created")


def days_since(iso_date):
    if not iso_date:
        return None
    dt = datetime.fromisoformat(iso_date.replace("Z", "+00:00"))
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return (datetime.now(timezone.utc) - dt).days


def cart_has_items(cart_meta):
    value = (cart_meta or {}).get("value")
    if not value or not isinstance(value, dict):
        return False
    cart = value.get("cart")
    return bool(cart)


def decide(cart_meta, days_quiet, stale_days):
    if cart_meta is None:
        return ("skip", "no persistent cart meta")
    if not cart_has_items(cart_meta):
        return ("skip", "cart meta is empty")
    if days_quiet is None or days_quiet < stale_days:
        return ("skip", "customer has not been quiet long enough")
    return ("clear", f"quiet for {days_quiet} days, past the {stale_days} day threshold")


def clear_cart(customer_id, meta_key):
    requests.put(
        f"{WOO_URL}/wp-json/wc/v3/customers/{customer_id}",
        json={"meta_data": [{"key": meta_key, "value": ""}]},
        auth=AUTH, timeout=30,
    ).raise_for_status()


def run():
    cleared = 0
    for customer in customers():
        cart_meta = cart_meta_of(customer)
        days_quiet = days_since(last_activity(customer)) if cart_meta else None
        action, reason = decide(cart_meta, days_quiet, STALE_DAYS)
        if action == "skip":
            continue
        log.info("Customer %s: %s. %s", customer["id"], reason, "would clear" if DRY_RUN else "clearing")
        if not DRY_RUN:
            clear_cart(customer["id"], cart_meta["key"])
        cleared += 1
    log.info("Done. %d customer(s) %s.", cleared, "to clear" if DRY_RUN else "cleared")


if __name__ == "__main__":
    run()

clear-stale-carts.js

/**
 * Clear stale WooCommerce persistent cart meta from wp_usermeta.
 * Run on a schedule. Safe to run again and again.
 */
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");
const STALE_DAYS = Number(process.env.STALE_DAYS || 180);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

const CART_META_PREFIX = "_woocommerce_persistent_cart_";

async function* customers() {
  let page = 1;
  while (true) {
    const res = await fetch(
      `${WOO_URL}/wp-json/wc/v3/customers?per_page=50&page=${page}&orderby=registered_date`,
      { headers: { Authorization: AUTH } }
    );
    if (!res.ok) throw new Error(`Woo customers returned ${res.status}`);
    const batch = await res.json();
    if (!batch.length) return;
    for (const customer of batch) yield customer;
    page++;
  }
}

function cartMetaOf(customer) {
  for (const meta of customer.meta_data || []) {
    if (String(meta.key || "").startsWith(CART_META_PREFIX)) return meta;
  }
  return null;
}

async function lastActivity(customer) {
  const res = await fetch(
    `${WOO_URL}/wp-json/wc/v3/orders?customer=${customer.id}&per_page=1&orderby=date&order=desc`,
    { headers: { Authorization: AUTH } }
  );
  if (!res.ok) throw new Error(`Woo orders returned ${res.status}`);
  const orders = await res.json();
  if (orders.length) return orders[0].date_created;
  return customer.date_created;
}

function daysSince(isoDate) {
  if (!isoDate) return null;
  const dt = new Date(isoDate.endsWith("Z") || isoDate.includes("+") ? isoDate : `${isoDate}Z`);
  return Math.floor((Date.now() - dt.getTime()) / 86400000);
}

function cartHasItems(cartMeta) {
  const value = cartMeta ? cartMeta.value : null;
  if (!value || typeof value !== "object") return false;
  return Boolean(value.cart && Object.keys(value.cart).length);
}

function decide(cartMeta, daysQuiet, staleDays) {
  if (!cartMeta) return ["skip", "no persistent cart meta"];
  if (!cartHasItems(cartMeta)) return ["skip", "cart meta is empty"];
  if (daysQuiet === null || daysQuiet === undefined || daysQuiet < staleDays) {
    return ["skip", "customer has not been quiet long enough"];
  }
  return ["clear", `quiet for ${daysQuiet} days, past the ${staleDays} day threshold`];
}

async function clearCart(customerId, metaKey) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3/customers/${customerId}`, {
    method: "PUT",
    headers: { "Content-Type": "application/json", Authorization: AUTH },
    body: JSON.stringify({ meta_data: [{ key: metaKey, value: "" }] }),
  });
  if (!res.ok) throw new Error(`Woo customer update returned ${res.status}`);
}

async function run() {
  let cleared = 0;
  for await (const customer of customers()) {
    const cartMeta = cartMetaOf(customer);
    const daysQuiet = cartMeta ? daysSince(await lastActivity(customer)) : null;
    const [action, reason] = decide(cartMeta, daysQuiet, STALE_DAYS);
    if (action === "skip") continue;
    console.log(`Customer ${customer.id}: ${reason}. ${DRY_RUN ? "would clear" : "clearing"}`);
    if (!DRY_RUN) await clearCart(customer.id, cartMeta.key);
    cleared++;
  }
  console.log(`Done. ${cleared} customer(s) ${DRY_RUN ? "to clear" : "cleared"}.`);
}

run().catch((err) => { console.error(err); process.exit(1); });

Add a test

The decision rule is the part most worth testing, because it decides whether real customer data gets touched. Because we kept decide pure, the test needs no network and no live store. It just feeds in plain objects and checks the action.

test_decide.py

from clear_stale_carts import decide


def cart_meta(**over):
    base = {"key": "_woocommerce_persistent_cart_1", "value": {"cart": {"abc123": {"quantity": 1}}}}
    base.update(over)
    return base


def test_clear_when_quiet_past_threshold():
    assert decide(cart_meta(), 200, 180)[0] == "clear"


def test_skip_when_no_meta():
    assert decide(None, 200, 180)[0] == "skip"


def test_skip_when_cart_is_empty():
    assert decide(cart_meta(value={"cart": {}}), 200, 180)[0] == "skip"


def test_skip_when_not_quiet_long_enough():
    assert decide(cart_meta(), 30, 180)[0] == "skip"


def test_skip_when_days_quiet_is_none():
    assert decide(cart_meta(), None, 180)[0] == "skip"

decide.test.js

import { test } from "node:test";
import assert from "node:assert/strict";
import { decide } from "./decide.js";

const cartMeta = (over = {}) => ({
  key: "_woocommerce_persistent_cart_1",
  value: { cart: { abc123: { quantity: 1 } } },
  ...over,
});

test("clear when quiet past threshold", () => {
  assert.equal(decide(cartMeta(), 200, 180)[0], "clear");
});

test("skip when no meta", () => {
  assert.equal(decide(null, 200, 180)[0], "skip");
});

test("skip when cart is empty", () => {
  assert.equal(decide(cartMeta({ value: { cart: {} } }), 200, 180)[0], "skip");
});

test("skip when not quiet long enough", () => {
  assert.equal(decide(cartMeta(), 30, 180)[0], "skip");
});

test("skip when days quiet is null", () => {
  assert.equal(decide(cartMeta(), null, 180)[0], "skip");
});

Case studies

Nineteen years of registrations

The store that never looked at wp_usermeta

A store running since the early WooCommerce days had over 300,000 registered customers, most of whom had shopped once and never returned. A database size audit found that persistent cart rows alone made up close to four gigabytes of wp_usermeta, many pointing at variations that had been deleted for years.

Running the cleanup job with a 365 day quiet window in dry run first produced a clear list of what would be cleared. After a real run, the table shrank noticeably and the customer admin list, which had been getting slower every year, loaded faster.

Migration prep

The migration that stalled on row count

A team migrating to a new host hit a wall exporting wp_usermeta because of sheer row count, and persistent cart entries were a large share of it. Most belonged to accounts that had not ordered in years.

They ran the cleanup job with a 180 day threshold ahead of the migration, cutting the export size enough that the migration finished in the maintenance window they had booked.

What good looks like

After this runs on a schedule, persistent cart data stops growing without bound. Active shoppers keep the exact feature they always had, their cart follows them across devices, while quiet accounts stop holding onto carts nobody is coming back for. Keep it running even after a first big cleanup, since new stale carts form every week.

FAQ

Why does WooCommerce keep a persistent cart in wp_usermeta forever?

WooCommerce saves a logged in customer's cart to user meta so it survives across devices and sessions. It writes that meta on every cart change but has no built in job that clears it once the cart is abandoned or the customer stops shopping, so the row just sits there.

Is it safe to delete persistent cart meta with a script?

Yes, when the script only targets carts that are older than a set number of days and skips any customer who has shopped recently. Removing the meta does not touch orders or products, it only clears the saved cart, and the customer simply gets an empty cart on their next visit. Start in dry run mode to review the list before it writes.

How often should the cleanup job run?

Once a week or once a month is enough for most stores. Persistent carts do not need second by second attention, so a light weekly sweep keeps wp_usermeta small without any real risk.

Related field notes

Citations

On the problem:

WooCommerce core source: the persistent cart is saved to user meta on every session update, with no expiry logic. github.com/woocommerce/woocommerce
WooCommerce docs: how the persistent cart feature keeps a logged in customer's cart across sessions. woocommerce.com/document/introduction-to-woocommerce-cart-page
WordPress support discussion: wp_usermeta growing large from old persistent cart rows on long running stores. wordpress.org/support/topic/wp_usermeta-table-very-large

On the solution:

WooCommerce REST API: retrieve and update a customer, including the meta_data array. woocommerce.github.io/woocommerce-rest-api-docs
WooCommerce REST API: list orders filtered by customer to find the last activity date. woocommerce.github.io/woocommerce-rest-api-docs
WordPress developer reference: update_user_meta and the usermeta table structure. developer.wordpress.org/reference/functions/update_user_meta

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 shrink your database?

If this saved you a slow admin screen or a painful 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 field notes