How I Migrated a 40,000-Order WooCommerce Store to HPOS Without Losing a Single Order

Last month a client running a UK pet supplies store asked me why WooCommerce kept showing a banner about "High-Performance Order Storage." They had 40,000 orders, a dozen plugins, and custom code hooking into order data. They wanted to know if they should switch.

The short answer: yes, you have to. HPOS is now the default for new WooCommerce installations and the old wp_posts-based order storage is being phased out. Every WooCommerce update pushes harder towards migration. The question isn't whether to migrate — it's how to do it without breaking your store.

Here's exactly how I handled it.

Understanding what HPOS actually changes

WooCommerce has historically stored orders as a WordPress custom post type. Every order was a row in wp_posts, and every piece of order metadata — billing address, shipping method, payment tokens — lived in wp_postmeta. This worked, but it meant order queries competed with regular post queries, and the wp_postmeta table became enormous on busy stores.

HPOS moves order data into dedicated tables: wp_wc_orders, wp_wc_orders_meta, wp_wc_order_addresses, and others. The schema is purpose-built for ecommerce — billing email gets its own indexed column instead of being buried in a key-value meta table.

The performance difference on large stores is real. On this client's site, the WooCommerce orders admin page went from a 4-second load to under 800ms after migration.

Step 1: Audit every plugin for compatibility

Before touching anything, I needed to know which plugins would break. WooCommerce has a built-in compatibility checker — go to WooCommerce > Settings > Advanced > Features and look at the HPOS option. If it's greyed out, click "View and manage" to see which plugins have declared themselves incompatible.

But here's the thing: not declaring compatibility isn't the same as being incompatible. Many plugins simply haven't added the compatibility flag yet but work fine with HPOS. And some plugins that declare compatibility still break in edge cases.

I ran a more thorough check. First, I searched the codebase for any direct post meta access on orders:

grep -r "get_post_meta\|update_post_meta\|delete_post_meta" wp-content/plugins/ \
  --include="*.php" -l | sort

Then I looked specifically for raw SQL queries hitting the posts table for order data:

grep -rn "wp_posts.*shop_order\|wp_postmeta.*_order_\|wp_postmeta.*_billing_\|wp_postmeta.*_shipping_" \
  wp-content/plugins/ --include="*.php" | head -50

This turned up three problems:

1. A custom reporting plugin querying wp_postmeta directly for order totals
2. A shipping integration using get_post_meta() to read tracking numbers
3. Custom theme code in functions.php using get_post_meta() to display order info on a "My Account" page

Step 2: Fix the custom code

The fix pattern is the same every time. Replace direct post meta calls with WooCommerce CRUD methods:

// Before (breaks with HPOS)
$tracking = get_post_meta($order_id, '_tracking_number', true);
$email = get_post_meta($order_id, '_billing_email', true);

// After (works with both storage backends)
$order = wc_get_order($order_id);
$tracking = $order->get_meta('_tracking_number');
$email = $order->get_billing_email();

For the reporting plugin, the raw SQL queries needed rewriting to use wc_get_orders():

// Before (direct DB query)
global $wpdb;
$results = $wpdb->get_results("
    SELECT p.ID, pm.meta_value as total
    FROM {$wpdb->posts} p
    JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
    WHERE p.post_type = 'shop_order'
    AND pm.meta_key = '_order_total'
    AND p.post_date >= '2025-01-01'
");

// After (WooCommerce CRUD, paginated to avoid N+1 and memory issues)
$page     = 1;
$per_page = 500;

do {
    $orders = wc_get_orders( [
        'date_created' => '>=2025-01-01',
        'limit'        => $per_page,
        'page'         => $page,
        'return'       => 'objects',
    ] );

    foreach ( $orders as $order ) {
        $total = $order->get_total();
        // Do something with $total...
    }

    $page++;
} while ( count( $orders ) === $per_page );

For the shipping plugin, I contacted the developer. They'd already released an HPOS-compatible update two months prior — the client just hadn't updated.

Step 3: Stage and migrate

I never migrate on production first. The process:

1. Full backup — database and files. I use UpdraftPlus to S3, plus a manual mysqldump for belt and braces.

2. Clone to staging — exact replica of production, same PHP version, same server config.

3. Enable compatibility mode — this is the crucial step. In WooCommerce settings, enable "High-Performance Order Storage" and also enable "Enable compatibility mode." This tells WooCommerce to keep both datastores in sync while you verify everything works.

4. Run the sync via WP-CLI — the admin UI sync is painfully slow on large stores. WP-CLI is dramatically faster:

wp wc hpos sync --batch-size=500

For 40,000 orders this took about 25 minutes. On stores with hundreds of thousands of orders, expect hours or days.

5. Monitor progress — check how many orders are still pending:

wp wc hpos count_unmigrated

Step 4: Verify data integrity

This is the step most guides skip, and it's the most important one. WooCommerce provides a verification command:

wp wc hpos verify_cot_data --verbose

On the newer WooCommerce versions, the equivalent command is:

wp wc hpos verify_data --verbose

This compares every order across both datastores and flags discrepancies. On this client's store, it found 12 orders with mismatched metadata — all caused by the old shipping plugin writing directly to wp_postmeta after the sync had already run.

To fix the mismatched orders, I re-synced just those specific orders:

wp wc hpos verify_data --re-migrate

Then verified again. Zero discrepancies.

I also ran a manual spot check — picked 20 orders at random and compared the data in the admin UI against a direct database query on the new wp_wc_orders table:

SELECT id, billing_email, total_amount, date_created_gmt
FROM wp_wc_orders
WHERE id IN (12345, 12346, 12347)
ORDER BY id;

Everything matched.

Step 5: Test everything that touches orders

With compatibility mode still on, I tested every order-related workflow:

• New order placement (guest and logged-in)
• Payment processing (Stripe and PayPal)
• Subscription renewals (WooCommerce Subscriptions)
• Refund processing
• Order status emails
• Shipping label generation
• The custom reporting page
• WooCommerce REST API order endpoints
• CSV order exports

Two issues surfaced. The custom reporting page was still using a cached query that bypassed the CRUD layer — I'd missed one function. And the CSV export plugin needed updating to its latest version.

Step 6: Go live and disable compatibility mode

Once staging was clean, I repeated the entire process on production:

1. Enabled HPOS + compatibility mode
2. Ran wp wc hpos sync during a quiet period (Sunday evening)
3. Verified with wp wc hpos verify_data --verbose
4. Tested all critical workflows
5. Monitored for 48 hours with both datastores in sync

After two days of clean operation, I disabled compatibility mode. This makes the HPOS tables the sole authoritative datastore. The old wp_posts and wp_postmeta order data is still there but no longer written to.

What to watch out for

A few gotchas I've seen across multiple HPOS migrations:

Deactivated plugins with custom post types. If you have WooCommerce Subscriptions or Bookings deactivated during migration, their related order data can get corrupted. Make sure every order-related plugin is active before you sync.

The wp_wc_customer_lookup corruption bug. There's a known issue (GitHub #60214) where the migration can incorrectly map customer IDs, causing mismatched customer data in the lookup table. After migration, spot-check a few customer records:

SELECT cl.email, cl.first_name, om.meta_value AS billing_email
FROM wp_wc_customer_lookup cl
JOIN wp_wc_orders o ON cl.customer_id = o.customer_id
JOIN wp_wc_orders_meta om ON o.id = om.order_id
WHERE om.meta_key = '_billing_email'
  AND cl.email != om.meta_value
LIMIT 10;

Tracking and analytics plugins. Many GA4 and Facebook Pixel plugins still read order data via get_post_meta(). After enabling HPOS, check your conversion tracking is still firing correctly. The data loss is silent — you won't see errors, you'll just see fewer conversions in your analytics.

Redis object cache interactions. If you're running Redis (and you should be on a WooCommerce store), flush the object cache after completing the migration. Stale cached order objects can cause confusing behaviour.

wp cache flush

Was it worth it?

For a 40,000-order store, the performance improvement was significant. Admin order list loads dropped from 4 seconds to under a second. Order search became near-instant. The wp_postmeta table — previously the biggest table in the database at 2.1 million rows — is no longer involved in order queries at all.

More importantly, HPOS is where WooCommerce development is heading. New features are being built around it. Staying on the old posts-based storage means falling behind on updates and compatibility. The migration isn't optional anymore — it's a matter of when, not if.

If you're managing WooCommerce stores and haven't started planning HPOS migrations, now is the time. And if you'd rather not deal with the audit, sync, verification, and testing yourself — that's exactly what a maintenance plan covers.

---

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for UK agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans → Get in Touch →