WooCommerce High-Performance Order Storage (HPOS)

WooCommerce High-Performance Order Storage (HPOS) is a game changer for every WooCommerce website. Enabling HPOS will store all order data in custom tables instead of the WordPress post and post meta tables. Long story short, everything will be a lot faster.

Apart from just informing you about the general workings of HPOS, for which there is already quite some content available, this article discusses the changes for Admin Columns for the WooCommerce list tables. We have also included a small benchmark with some SQL to give you a better feel for why HPOS is faster.

When will HPOS be available?

In this announcement, you can read that HPOS is available as an opt-in for WooCommerce 7.1 and it will be the default in WooCommerce 8.2. So, you have still a little time to test HPOS and make sure your theme or plugin is ready.

What changes with HPOS?

HPOS uses separate tables to store order and order-related data. Right now there are 4 new tables for orders (wc_orders), order addresses (wc_order_addresses), order internal state (wc_order_operational_data), and order meta (wc_orders_meta). All tables have fields that describe useful domain information about a part of the order, which is understandable and optimizes storage. And lastly, some of these fields have indexes on them that make looking them up blazing fast. All this makes HPOS (a lot more) performant, scalable, and easier to understand. WooCommerce has a page where these tables are described in detail.

What changes in Admin Columns?

In short, everything ‘orders’ just got a lot faster. We have seen up to a 5x speed improvement when performing sorting or filtering operations. We rewrote every WooCommerce column to support HPOS and found it even opened opportunities for a few new columns, which are almost ready for shipping:

Column	Type	Sort	Filter	Edit	Export
Discount tax amount	number
Shipping tax amount	number
Shipping total amount	number
Download permissions granted	yes/no
Order key	text
User-agent	text

For a full reference of all the WooCommerce order columns in Admin Columns, you can check the documentation. We’ll add the above columns as soon as they are released.

Benchmarking HPOS

Curious as we are, we have created a few benchmarks to measure the WooCommerce HPOS with the current WooCommerce storage. We compared the performance on general loading times, sorting, and filtering and showed the accompanying SQL for each scenario if relevant.

Sorting orders

We set up a simple test that compares sorting by the number of total sales amount per order with and without HPOS enabled. You can see both SQL queries that it runs to sort the orders by the total amount on the ‘Totals’ column. Our dataset contained identical 3500 orders.

SQL query using the Posts table:

SELECT wp_posts.ID, CAST(meta.meta_value AS DECIMAL(12,2)) AS total_amount
FROM wp_posts
LEFT JOIN wp_postmeta AS meta ON wp_posts.ID = meta.post_id AND meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
ORDER BY total_amount DESC

SQL query with HPOS enabled:

SELECT id, total_amount
FROM wp_wc_orders
ORDER BY total_amount DESC

And the result:

As you can see, no more costly (left) join of the post meta table, we can just use the pre-calculated value WooCommerce now supplies. This resulted in an over 5x faster sorting query.

Filtering orders

We also measured the difference when filtering orders by a specific country. Our dataset contained 3500 identical orders.

SQL query using the Posts table:

SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta AS country ON wp_posts.ID = country.post_id AND meta_key = '_billing_country'
WHERE wp_posts.post_type = 'shop_order'
AND country.meta_value = 'NL'

SQL query with HPOS enabled:

SELECT wp_wc_orders.id
FROM wp_wc_orders
INNER JOIN wp_wc_order_addresses AS address ON address.order_id = wp_wc_orders.id AND address.address_type = 'billing'
WHERE address.country = 'NL'

And the result:

Although there is still a join required, it’s optimized and is not hindered by non-relevant data from other types of posts. This resulted in an almost 4x faster filtering query.

List table loading time

We also compared the load times for the WooCommerce orders list table page in its entirety. For this test, we rendered an orders list table with 15 identical columns and displayed 250 orders with and without HPOS enabled. To get the cleanest result without too much WordPress overhead, we started timing just before ListTable::prepare_items() and stopped after ListTable::display().

And the result:

The WooCommerce orders table loads about 2x faster when HPOS is enabled.

Closing remarks

If you have a WooCommerce shop and need to scale (at some point), HPOS will get you a long way in keeping everything running smoothly. And if you rely on Admin Columns, you can benefit from even faster and new WooCommere columns. No reason not to use HPOS (and Admin Columns 😜).