How to Clean POSTMETA Data of Old Orders After Switching to WooCommerce HPOS

As of today there is no official guide for this simple need. If you have old orders more than 1000, you are right if you want to get rid of old orders – just to fasten your website.

So i decided to write my own script and apply to my web store with 30K order history.

Step1: I activated HPOS in dual-mode (aka safe mode).

Step2: 1 day passed and i disabled legacy mode and kept HPOS only

Step3: 1 more day passed and i deleted the oldest 5 orders from wp_postmeta.

Step4: Everything looked good, and i did the same for all orders.

Warning: You should take a backup before applying this code:

function delete_old_non_hpos_orders() {
    $query_args = array(
        'post_type' => 'shop_order',
        'post_status' => 'any',
        'posts_per_page' => 5,//change this number after making sure it is safe
        'orderby' => 'date',
        'order' => 'ASC',
    );

    $query = new WP_Query($query_args);

    global $wpdb;
    foreach ($query->posts as $post) {
        $order_id = $post->ID;
        // Check if the order exists in HPOS
        $exists_in_hpos = $wpdb->get_var("SELECT id FROM {$wpdb->prefix}wc_orders WHERE id = $order_id");
        if ($exists_in_hpos) {  
            echo $order_id.PHP_EOL;    
            $wpdb->delete("{$wpdb->prefix}postmeta", ['post_id' => $order_id]);
            $wpdb->update(
                "{$wpdb->prefix}posts",
                ['post_type' => 'shop_order_placehold'],
                ['ID' => $order_id]
            );
        }
    }
}
if(isset($_GET["delete_old_non_hpos_orders"])){
  add_action('wp',function(){
    if(!current_user_can('administrator'))return;
    delete_old_non_hpos_orders();
    die('Stop');
  });
}

Once you placed this code to your theme’s functions.php or as a snippet, you can run YOURWEBSITEURL/?delete_old_non_hpos_orders=yes URL to see the how it works.
Once you are done with that, you must delete the code.