Your postmeta is huge brother

Before HPOS, WooCommerce stored orders as post_type = shop_order in the wp_posts table and all order data in wp_postmeta.
Now with HPOS, orders live in:

• wp_wc_orders

• wp_wc_order_addresses

• wp_wc_order_operational_data

• wp_wc_orders_meta

But WooCommerce does not automatically delete the old shop_order posts or their postmeta (for backward compatibility). That’s why your wp_postmeta is still bloated.

Safe Cleanup Steps

• Always back up your database before running any delete queries.

1. Check how many old orders are still in wp_posts

SELECT COUNT(*) 
FROM wp_posts 
WHERE post_type = 'shop_order';

If you’ve fully migrated to HPOS, you don’t need these anymore.

2. Remove old order postmeta

This query deletes all postmeta records tied to old shop_order posts:

DELETE pm
FROM wp_postmeta pm
INNER JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'shop_order';

3. Remove old order posts

DELETE FROM wp_posts
WHERE post_type = 'shop_order';

4. Optimize the table

OPTIMIZE TABLE wp_postmeta;

Stage Cleanup - its optional:

Because you’ve got 9M+ rows, deleting everything in one go can lock tables and time out.
Do it in batches like this:

DELETE pm
FROM wp_postmeta pm
INNER JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'shop_order'
LIMIT 50000;

Run multiple times until rows are gone. Cheers.