Affinite 0 comments

WooCommerce HPOS – High-Performance Order Storage

Introduction to HPOS
HPOS Database Tables
Product Lookup Tables
Additional Custom WooCommerce Tables
Comparison with the Legacy Storage
Compatibility Mode
Data Stored Outside the Default Tables
Key PHP Classes
Configuration and Settings
Migration to HPOS
Performance Characteristics

1. Introduction to HPOS

HPOS (High-Performance Order Storage) is the new WooCommerce order storage architecture that replaces the legacy system built on the WordPress posts tables.

1.1 Why HPOS?

Issues with the legacy system:

Orders stored as posts (wp_posts, post_type = 'shop_order')
All order data lives in wp_postmeta (billing, shipping, payments…)
Slow queries caused by expensive meta table JOINs
Inefficient indexing
Database bloat

HPOS benefits:

70-90% faster order queries
~40% smaller database footprint
Improved indexing — dedicated columns for frequently queried data
Normalized structure — separate tables for addresses, metadata, operational data
Direct column access — no meta JOINs required

1.2 File structure in code

Key implementation files:

/src/Internal/DataStores/Orders/
 CustomOrdersTableController.php    # HPOS feature orchestration
 OrdersTableDataStore.php          # CRUD operations on HPOS tables
 DataSynchronizer.php              # Posts ↔ HPOS synchronization
 LegacyDataHandler.php             # Legacy system compatibility
 OrdersTableQuery.php              # SQL query builder

/src/Database/Migrations/CustomOrderTable/
 PostsToOrdersMigrationController.php    # Migration orchestration
 PostToOrderTableMigrator.php           # Migrate posts → orders
 PostMetaToOrderMetaMigrator.php        # Migrate postmeta → orders_meta
 PostToOrderAddressTableMigrator.php    # Address extraction
 PostToOrderOpTableMigrator.php         # Operational data extraction

/src/Utilities/
 OrderUtil.php                     # HPOS helper utilities

2. HPOS Database Tables

HPOS uses four primary tables for order storage:

2.1 `wp_wc_orders` — Primary order table

Purpose: Stores core order information

Structure:

CREATE TABLE `wp_wc_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(20) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `tax_amount` decimal(26,8) DEFAULT 0,
  `total_amount` decimal(26,8) DEFAULT 0,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `billing_email` varchar(320) DEFAULT NULL,
  `date_created_gmt` datetime DEFAULT NULL,
  `date_updated_gmt` datetime DEFAULT NULL,
  `parent_order_id` bigint(20) unsigned DEFAULT NULL,
  `payment_method` varchar(100) DEFAULT NULL,
  `payment_method_title` text,
  `transaction_id` varchar(100) DEFAULT NULL,
  `ip_address` varchar(100) DEFAULT NULL,
  `user_agent` text,
  `customer_note` text,

  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `date_created` (`date_created_gmt`),
  KEY `customer_id_billing_email` (`customer_id`, `billing_email`(100)),
  KEY `billing_email` (`billing_email`(100)),
  KEY `type_status_date` (`type`, `status`, `date_created_gmt`),
  KEY `parent_order_id` (`parent_order_id`),
  KEY `date_updated` (`date_updated_gmt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Key column reference:

Column	Type	Description
`id`	bigint	Primary order key
`status`	varchar(20)	Order status (`wc-pending`, `wc-processing`, `wc-completed`, etc.)
`currency`	varchar(10)	Currency (CZK, USD, EUR…)
`type`	varchar(20)	Order type (`shop_order`, `shop_order_refund`, `shop_subscription`…)
`tax_amount`	decimal(26,8)	Total tax amount
`total_amount`	decimal(26,8)	Order total
`customer_id`	bigint	Customer ID (NULL for guests)
`billing_email`	varchar(320)	Billing email
`date_created_gmt`	datetime	Created date (GMT)
`date_updated_gmt`	datetime	Last updated date (GMT)
`parent_order_id`	bigint	Parent order (for refunds)
`payment_method`	varchar(100)	Payment method ID
`payment_method_title`	text	Payment method title
`transaction_id`	varchar(100)	Gateway transaction ID
`ip_address`	varchar(100)	Customer IP address
`user_agent`	text	Customer user agent
`customer_note`	text	Customer note

Implementation: src/Internal/DataStores/Orders/OrdersTableDataStore.php:3137-3232

2.2 `wp_wc_order_addresses` — Billing and shipping addresses

Purpose: Stores billing and shipping addresses in a normalized format

Structure:

CREATE TABLE `wp_wc_order_addresses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `address_type` varchar(20) DEFAULT NULL,
  `first_name` text,
  `last_name` text,
  `company` text,
  `address_1` text,
  `address_2` text,
  `city` text,
  `state` text,
  `postcode` text,
  `country` text,
  `email` varchar(320) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  UNIQUE KEY `address_type_order_id` (`address_type`, `order_id`),
  KEY `email` (`email`(100)),
  KEY `phone` (`phone`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Description:

Column	Type	Description
`id`	bigint	Primary address record key
`order_id`	bigint	FK to `wp_wc_orders.id`
`address_type`	varchar(20)	`'billing'` or `'shipping'`
`first_name`	text	First name
`last_name`	text	Last name
`company`	text	Company name
`address_1`	text	Street address
`address_2`	text	Address line 2
`city`	text	City
`state`	text	State/region
`postcode`	text	Postal code
`country`	text	Country (two-letter code)
`email`	varchar(320)	Email (billing = invoicing; shipping can differ)
`phone`	varchar(100)	Phone

Key points:

Each order has a maximum of two rows (one billing, one shipping)
UNIQUE KEY address_type_order_id enforces uniqueness
Optional Full Text Search index support

2.3 `wp_wc_order_operational_data` — Operational data

Purpose: Internal data used by WooCommerce features

Structure:

CREATE TABLE `wp_wc_order_operational_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `created_via` varchar(100) DEFAULT NULL,
  `woocommerce_version` varchar(20) DEFAULT NULL,
  `prices_include_tax` tinyint(1) DEFAULT 0,
  `coupon_usages_are_counted` tinyint(1) DEFAULT 0,
  `download_permission_granted` tinyint(1) DEFAULT 0,
  `cart_hash` varchar(100) DEFAULT NULL,
  `new_order_email_sent` tinyint(1) DEFAULT 0,
  `order_key` varchar(100) DEFAULT NULL,
  `order_stock_reduced` tinyint(1) DEFAULT 0,
  `date_paid_gmt` datetime DEFAULT NULL,
  `date_completed_gmt` datetime DEFAULT NULL,
  `shipping_tax_amount` decimal(26,8) DEFAULT 0,
  `shipping_total_amount` decimal(26,8) DEFAULT 0,
  `discount_tax_amount` decimal(26,8) DEFAULT 0,
  `discount_total_amount` decimal(26,8) DEFAULT 0,
  `recorded_sales` tinyint(1) DEFAULT 0,

  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id` (`order_id`),
  KEY `order_key` (`order_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Description:

Column	Type	Description
`order_id`	bigint	FK to `wp_wc_orders.id` (UNIQUE)
`created_via`	varchar(100)	Creation source (`checkout`, `admin`, `rest-api`, `store-api`…)
`woocommerce_version`	varchar(20)	WooCommerce version at creation time
`prices_include_tax`	tinyint(1)	Do prices include tax?
`coupon_usages_are_counted`	tinyint(1)	Have coupon usages been counted?
`download_permission_granted`	tinyint(1)	Were download permissions granted?
`cart_hash`	varchar(100)	Original cart hash
`new_order_email_sent`	tinyint(1)	Was the new order email sent?
`order_key`	varchar(100)	Unique authentication key (e.g. pay/view without login)
`order_stock_reduced`	tinyint(1)	Was stock reduced?
`date_paid_gmt`	datetime	Paid date
`date_completed_gmt`	datetime	Completed date
`shipping_tax_amount`	decimal(26,8)	Shipping tax amount
`shipping_total_amount`	decimal(26,8)	Total shipping cost
`discount_tax_amount`	decimal(26,8)	Discount tax amount
`discount_total_amount`	decimal(26,8)	Total discount
`recorded_sales`	tinyint(1)	Were sales recorded?

Key points:

One row per order (UNIQUE KEY on order_id)
Primarily boolean flags powering WooCommerce internals

2.4 `wp_wc_orders_meta` — Order metadata

Purpose: Flexible key-value storage for auxiliary data

Structure:

CREATE TABLE `wp_wc_orders_meta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` text,

  PRIMARY KEY (`id`),
  KEY `meta_key_value` (`meta_key`, `meta_value`(100)),
  KEY `order_id_meta_key_meta_value` (`order_id`, `meta_key`, `meta_value`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Description:

Column	Type	Description
`id`	bigint	Primary meta record key
`order_id`	bigint	FK to `wp_wc_orders.id`
`meta_key`	varchar(255)	Meta key name
`meta_value`	text	Meta value (may be serialized)

Typical metadata keys:

_customer_user              → Registered customer ID
_order_currency            → Order currency
_billing_*, _shipping_*    → Duplicated address fields (for compatibility)
_payment_method            → Payment method ID
_payment_method_title      → Payment method label
_order_key                 → Unique order key
_order_total               → Order total
_order_tax                 → Total tax
_order_shipping            → Shipping cost
_cart_discount             → Cart discount
_transaction_id            → Transaction ID
_customer_ip_address       → Customer IP address
_customer_user_agent       → Customer user agent
_created_via               → Created via
_prices_include_tax        → Prices include tax flag
_date_completed            → Completion timestamp
_date_paid                 → Payment timestamp
_recorded_sales            → Recorded sales flag

Key points:

Unlimited metadata records per order
Structure identical to wp_postmeta
Values from “internal_meta_keys” are persisted in dedicated columns across the other tables

3. Product Lookup Tables

WooCommerce relies on denormalized lookup tables for fast product queries and analytics.

3.1 `wp_wc_product_meta_lookup` — Fast product access

Purpose: Denormalized data for product filtering and search

Structure:

CREATE TABLE `wp_wc_product_meta_lookup` (
  `product_id` bigint(20) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `global_unique_id` varchar(100) DEFAULT NULL,
  `virtual` tinyint(1) DEFAULT 0,
  `downloadable` tinyint(1) DEFAULT 0,
  `min_price` decimal(19,4) DEFAULT NULL,
  `max_price` decimal(19,4) DEFAULT NULL,
  `onsale` tinyint(1) DEFAULT 0,
  `stock_quantity` double DEFAULT NULL,
  `stock_status` varchar(100) DEFAULT 'instock',
  `rating_count` bigint(20) DEFAULT 0,
  `average_rating` decimal(3,2) DEFAULT 0.00,
  `total_sales` bigint(20) DEFAULT 0,
  `tax_status` varchar(100) DEFAULT 'taxable',
  `tax_class` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`product_id`),
  KEY `sku` (`sku`),
  KEY `virtual` (`virtual`),
  KEY `downloadable` (`downloadable`),
  KEY `stock_status` (`stock_status`),
  KEY `stock_quantity` (`stock_quantity`),
  KEY `onsale` (`onsale`),
  KEY `min_max_price` (`min_price`, `max_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Description:

Column	Type	Description
`product_id`	bigint	PK, FK to `wp_posts.ID` (`post_type='product'`)
`sku`	varchar(100)	Product SKU
`global_unique_id`	varchar(100)	Global identifier (e.g. GTIN, ISBN)
`virtual`	tinyint(1)	Is the product virtual?
`downloadable`	tinyint(1)	Is it downloadable?
`min_price`	decimal(19,4)	Minimum price (for variations)
`max_price`	decimal(19,4)	Maximum price
`onsale`	tinyint(1)	Is it on sale?
`stock_quantity`	double	Quantity in stock
`stock_status`	varchar(100)	Stock status (`instock`, `outofstock`, `onbackorder`)
`rating_count`	bigint	Rating count
`average_rating`	decimal(3,2)	Average rating (0.00–5.00)
`total_sales`	bigint	Total sales count
`tax_status`	varchar(100)	Tax status (`taxable`, `shipping`, `none`)
`tax_class`	varchar(100)	Tax class

Use cases:

Product filtering in wp-admin
Frontend search and sorting
Stock availability checks
Sale listing pages

Updates: Automatically refreshed whenever a product changes

Implementation: includes/class-wc-install.php:1891-1915

3.2 `wp_wc_order_stats` — Order statistics

Purpose: Denormalized data for reporting and analytics

Structure:

CREATE TABLE `wp_wc_order_stats` (
  `order_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT 0,
  `date_created` datetime DEFAULT NULL,
  `date_created_gmt` datetime DEFAULT NULL,
  `date_paid` datetime DEFAULT NULL,
  `date_completed` datetime DEFAULT NULL,
  `num_items_sold` int(11) DEFAULT 0,
  `total_sales` double DEFAULT 0,
  `tax_total` double DEFAULT 0,
  `shipping_total` double DEFAULT 0,
  `net_total` double DEFAULT 0,
  `returning_customer` tinyint(1) DEFAULT 0,
  `status` varchar(200) DEFAULT NULL,
  `customer_id` bigint(20) unsigned DEFAULT 0,

  PRIMARY KEY (`order_id`),
  KEY `date_created` (`date_created`),
  KEY `customer_id` (`customer_id`),
  KEY `status` (`status`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Description:

Column	Type	Description
`order_id`	bigint	PK, FK to the order
`parent_id`	bigint	Parent order (for refunds)
`date_created`	datetime	Created date (site timezone)
`date_created_gmt`	datetime	Created date (GMT)
`date_paid`	datetime	Paid date
`date_completed`	datetime	Completed date
`num_items_sold`	int	Total items sold
`total_sales`	double	Gross sales
`tax_total`	double	Total tax
`shipping_total`	double	Total shipping
`net_total`	double	Net revenue (after refunds)
`returning_customer`	tinyint(1)	Returning customer flag
`status`	varchar(200)	Order status
`customer_id`	bigint	Customer ID

Use cases:

WooCommerce Analytics (Admin > Analytics)
Revenue reporting
Customer lifetime value
Charts and dashboards

3.3 `wp_wc_order_product_lookup` — Order line items (analytics)

Purpose: Denormalized line items for fast reporting

Structure:

CREATE TABLE `wp_wc_order_product_lookup` (
  `order_item_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `variation_id` bigint(20) unsigned DEFAULT 0,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `product_qty` int(11) NOT NULL,
  `product_net_revenue` double DEFAULT 0,
  `product_gross_revenue` double DEFAULT 0,
  `coupon_amount` double DEFAULT 0,
  `tax_amount` double DEFAULT 0,
  `shipping_amount` double DEFAULT 0,
  `shipping_tax_amount` double DEFAULT 0,

  PRIMARY KEY (`order_item_id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  KEY `customer_id` (`customer_id`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Use cases:

Top product reports
Revenue by product
Purchase pattern analysis

3.4 `wp_wc_order_tax_lookup` — Tax analytics

Structure:

CREATE TABLE `wp_wc_order_tax_lookup` (
  `order_id` bigint(20) unsigned NOT NULL,
  `tax_rate_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `shipping_tax` double DEFAULT 0,
  `order_tax` double DEFAULT 0,
  `total_tax` double DEFAULT 0,

  PRIMARY KEY (`order_id`, `tax_rate_id`),
  KEY `tax_rate_id` (`tax_rate_id`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Use cases:

Tax reports by rate
Validating tax calculations

3.5 `wp_wc_order_coupon_lookup` — Coupon analytics

Structure:

CREATE TABLE `wp_wc_order_coupon_lookup` (
  `order_id` bigint(20) unsigned NOT NULL,
  `coupon_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `discount_amount` double DEFAULT 0,

  PRIMARY KEY (`order_id`, `coupon_id`),
  KEY `coupon_id` (`coupon_id`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Use cases:

Coupon performance analysis
Most-used coupons

4. Additional custom WooCommerce tables

4.1 `wp_woocommerce_sessions` – Session management

Purpose: Stores customer session data (cart, checkout state)

CREATE TABLE `wp_woocommerce_sessions` (
  `session_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `session_key` char(32) NOT NULL,
  `session_value` longtext NOT NULL,
  `session_expiry` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`session_id`),
  UNIQUE KEY `session_key` (`session_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.2 `wp_woocommerce_api_keys` — REST API keys

Purpose: Authentication for the WooCommerce REST API

CREATE TABLE `wp_woocommerce_api_keys` (
  `key_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `permissions` varchar(10) NOT NULL,
  `consumer_key` char(64) NOT NULL,
  `consumer_secret` char(43) NOT NULL,
  `nonces` longtext,
  `truncated_key` char(7) NOT NULL,
  `last_access` datetime DEFAULT NULL,

  PRIMARY KEY (`key_id`),
  KEY `consumer_key` (`consumer_key`),
  KEY `consumer_secret` (`consumer_secret`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.3 `wp_woocommerce_payment_tokens` — Stored payment methods

Purpose: Tokens for repeat payments (saved cards)

CREATE TABLE `wp_woocommerce_payment_tokens` (
  `token_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gateway_id` varchar(200) NOT NULL,
  `token` text NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` varchar(200) NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,

  PRIMARY KEY (`token_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.4 `wp_woocommerce_log` — System logs

Purpose: WooCommerce logging (errors, debug info)

CREATE TABLE `wp_woocommerce_log` (
  `log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `level` smallint(4) NOT NULL,
  `source` varchar(200) NOT NULL,
  `message` longtext NOT NULL,
  `context` longtext,

  PRIMARY KEY (`log_id`),
  KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.5 `wp_woocommerce_order_items` — Order items

Purpose: Order line items (products, shipping, fees)

CREATE TABLE `wp_woocommerce_order_items` (
  `order_item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_item_name` text NOT NULL,
  `order_item_type` varchar(200) NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`order_item_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Item types:

line_item — product
shipping — shipping
tax — tax rate
fee — fee
coupon — coupon

4.6 `wp_woocommerce_order_itemmeta` — Item metadata

Purpose: Metadata for order items

CREATE TABLE `wp_woocommerce_order_itemmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_item_id` bigint(20) unsigned NOT NULL,
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,

  PRIMARY KEY (`meta_id`),
  KEY `order_item_id` (`order_item_id`),
  KEY `meta_key` (`meta_key`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.7 `wp_woocommerce_tax_rates` — Tax rates

Purpose: Defines tax rates by location

CREATE TABLE `wp_woocommerce_tax_rates` (
  `tax_rate_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tax_rate_country` varchar(2) DEFAULT NULL,
  `tax_rate_state` varchar(200) DEFAULT NULL,
  `tax_rate` varchar(8) DEFAULT NULL,
  `tax_rate_name` varchar(200) DEFAULT NULL,
  `tax_rate_priority` bigint(20) unsigned DEFAULT NULL,
  `tax_rate_compound` int(1) DEFAULT 0,
  `tax_rate_shipping` int(1) DEFAULT 1,
  `tax_rate_order` bigint(20) unsigned DEFAULT NULL,
  `tax_rate_class` varchar(200) DEFAULT NULL,

  PRIMARY KEY (`tax_rate_id`),
  KEY `tax_rate_country` (`tax_rate_country`),
  KEY `tax_rate_state` (`tax_rate_state`(2)),
  KEY `tax_rate_class` (`tax_rate_class`(10)),
  KEY `tax_rate_priority` (`tax_rate_priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.8 `wp_woocommerce_tax_rate_locations` — Tax rate locations

CREATE TABLE `wp_woocommerce_tax_rate_locations` (
  `location_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_code` varchar(200) DEFAULT NULL,
  `tax_rate_id` bigint(20) unsigned DEFAULT NULL,
  `location_type` varchar(40) DEFAULT NULL,

  PRIMARY KEY (`location_id`),
  KEY `tax_rate_id` (`tax_rate_id`),
  KEY `location_type_code` (`location_type`(10), `location_code`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.9 `wp_woocommerce_shipping_zones` — Shipping zones

CREATE TABLE `wp_woocommerce_shipping_zones` (
  `zone_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(200) DEFAULT NULL,
  `zone_order` bigint(20) unsigned DEFAULT NULL,

  PRIMARY KEY (`zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.10 `wp_woocommerce_shipping_zone_locations` — Zone locations

CREATE TABLE `wp_woocommerce_shipping_zone_locations` (
  `location_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_id` bigint(20) unsigned DEFAULT NULL,
  `location_code` varchar(200) DEFAULT NULL,
  `location_type` varchar(40) DEFAULT NULL,

  PRIMARY KEY (`location_id`),
  KEY `location_id` (`location_id`),
  KEY `location_type_code` (`location_type`(10), `location_code`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.11 `wp_woocommerce_shipping_zone_methods` — Shipping methods

CREATE TABLE `wp_woocommerce_shipping_zone_methods` (
  `zone_id` bigint(20) unsigned NOT NULL,
  `instance_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `method_id` varchar(200) NOT NULL,
  `method_order` bigint(20) unsigned DEFAULT NULL,
  `is_enabled` tinyint(1) DEFAULT 1,

  PRIMARY KEY (`instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.12 `wp_wc_webhooks` — Webhook definitions

CREATE TABLE `wp_wc_webhooks` (
  `webhook_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(200) NOT NULL,
  `name` text NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `delivery_url` text NOT NULL,
  `secret` text NOT NULL,
  `topic` varchar(200) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_created_gmt` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `date_modified_gmt` datetime NOT NULL,
  `api_version` smallint(4) NOT NULL,
  `failure_count` smallint(10) NOT NULL DEFAULT 0,
  `pending_delivery` tinyint(1) NOT NULL DEFAULT 0,

  PRIMARY KEY (`webhook_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.13 `wp_wc_download_log` — Download log

CREATE TABLE `wp_wc_download_log` (
  `download_log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `permission_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `user_ip_address` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`download_log_id`),
  KEY `permission_id` (`permission_id`),
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

4.14 `wp_wc_product_download_directories` — Download directories

CREATE TABLE `wp_wc_product_download_directories` (
  `url_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(256) NOT NULL,
  `enabled` tinyint(1) DEFAULT 0,

  PRIMARY KEY (`url_id`),
  KEY `url` (`url`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

5. Comparison with the legacy storage

5.1 Legacy system (pre-HPOS)

Core principle:

Orders stored as WordPress posts (post_type = 'shop_order')
All data lives in wp_posts and wp_postmeta

Table structure:

wp_posts
 ID (order ID)
 post_type = 'shop_order'
 post_status = 'wc-completed', 'wc-processing', ...
 post_date (created date)
 post_title (e.g. "Order #12345")
 post_excerpt (customer note)
 post_author (always 0 for orders)

wp_postmeta (all order data)
 _billing_first_name
 _billing_last_name
 _billing_address_1
 _billing_city
 ... (20+ billing meta entries)
 _shipping_first_name
 ... (20+ shipping meta entries)
 _order_total
 _order_tax
 _order_shipping
 _payment_method
 _transaction_id
 _customer_user
 _order_key
 ... (50+ additional meta keys)

Sample query:

-- Legacy: Fetch customer orders
SELECT p.ID
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id AND pm.meta_key = '_customer_user'
WHERE p.post_type = 'shop_order'
AND pm.meta_value = '123'
ORDER BY p.post_date DESC;

-- Requires additional JOINs for each meta field (email, total, status...)

5.2 HPOS system (new)

Core principles:

Orders stored in dedicated tables
Normalized structure
Dedicated columns for frequently queried data

Sample query:

-- HPOS: Fetch customer orders
SELECT id, billing_email, total_amount, status, date_created_gmt
FROM wp_wc_orders
WHERE customer_id = 123
ORDER BY date_created_gmt DESC;

-- No JOINs, direct column access

5.3 Comparison table

Aspect	Legacy (Posts)	HPOS	Difference
Main table	`wp_posts`	`wp_wc_orders`	Dedicated table
Metadata	`wp_postmeta`	`wp_wc_orders_meta` + dedicated columns	Far fewer JOINs
Addresses	`wp_postmeta` (40+ rows)	`wp_wc_order_addresses` (2 rows)	~95% less overhead
Operational data	`wp_postmeta` (15+ rows)	`wp_wc_order_operational_data` (1 row)	Structured format
Query performance	Baseline	70-90% faster	No meta JOINs
Database size	Baseline	~40% smaller	Normalization + indexes
Indexing	Generic post indexes	Specialized indexes	Better query plans
Scalability	Challenging beyond ~10k orders	Linear growth	Tuned for high volume
Compatibility	100% with WordPress ecosystem	Requires HPOS-aware extensions	Transition period

5.4 Concrete examples of the differences

Example 1: Loading an order with addresses

Legacy:

-- 1 query for the post
SELECT * FROM wp_posts WHERE ID = 12345 AND post_type = 'shop_order';

-- 1 query for all metadata (50-100 rows)
SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = 12345;

-- PHP loops through metadata and maps them to object properties
-- Total: 2 queries, 50-100 postmeta rows, slow PHP processing

HPOS:

-- 1 query for core data
SELECT * FROM wp_wc_orders WHERE id = 12345;

-- 1 query for addresses (2 rows)
SELECT * FROM wp_wc_order_addresses WHERE order_id = 12345;

-- 1 query for operational data (1 row)
SELECT * FROM wp_wc_order_operational_data WHERE order_id = 12345;

-- 1 query for metadata (custom/plugin metadata only)
SELECT meta_key, meta_value FROM wp_wc_orders_meta WHERE order_id = 12345;

-- Total: 4 queries, smaller data footprint, faster processing

Example 2: Searching by email

Legacy:

-- Slow query with meta JOIN
SELECT p.ID
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
  AND pm.meta_key = '_billing_email'
  AND pm.meta_value = 'zakaznik@example.com';

-- Time: ~500 ms for 10k orders
-- Index: Hard to index meta_value efficiently

HPOS:

-- Fast query leveraging an index
SELECT id
FROM wp_wc_orders
WHERE billing_email = 'zakaznik@example.com';

-- Time: ~5 ms for 10k orders (100× faster!)
-- Index: Direct index on billing_email column

Example 3: Filtering by date and status

Legacy:

-- Complex query
SELECT p.ID
FROM wp_posts p
WHERE p.post_type = 'shop_order'
  AND p.post_status = 'wc-completed'
  AND p.post_date >= '2025-01-01'
ORDER BY p.post_date DESC
LIMIT 20;

-- Issues:
-- - post_status includes the 'wc-' prefix
-- - post_date is not GMT
-- - No composite index for this pattern

HPOS:

-- Optimized query
SELECT id
FROM wp_wc_orders
WHERE status = 'completed'
  AND date_created_gmt >= '2025-01-01'
ORDER BY date_created_gmt DESC
LIMIT 20;

-- Benefits:
-- - Uses the type_status_date composite index
-- - GMT for consistency
-- - Clean status value (no prefix)

6. Compatibility mode

Compatibility Mode keeps the legacy and HPOS stores in sync during the transition period.

6.1 How does compatibility work?

Core concept:

Dual storage — data lives in both systems simultaneously
Authoritative source — one system acts as the master
Automatic synchronization — changes propagate to the other system

6.2 Operating modes

A. Legacy Mode (default)

Authoritative: wp_posts + wp_postmeta
Secondary: wp_wc_orders* (if present)

All writes go to the posts tables first
HPOS tables update in the background
Legacy functionality preserved 100%

B. HPOS Mode (recommended)

Authoritative: wp_wc_orders*
Secondary: wp_posts + wp_postmeta (if sync enabled)

All writes target the HPOS tables first
Posts tables refresh in the background (for compatibility)
Performance up to 90% better

C. HPOS Only Mode (future)

Authoritative: wp_wc_orders*
Secondary: none

HPOS tables only
Posts tables DO NOT update
Maximum performance
Requires every plugin to be HPOS compatible

6.3 Synchronization mechanism

Implementation: src/Internal/DataStores/Orders/DataSynchronizer.php

Trigger events:

Order creation

Order update

Order deletion

6.4 Sync modes

Configuration: woocommerce_custom_orders_table_background_sync_mode

A. Interval Mode (recommended)

Value: 'interval'
Behavior: Synchronize every X minutes (via WP-Cron)

Minimal performance impact
May introduce short-lived inconsistencies (a few minutes)
Suitable for production

B. Continuous Mode

Value: 'continuous'
Behavior: Sync on shutdown hooks

Faster synchronization
Higher performance cost
Good fit for smaller stores

C. Off Mode

Value: 'off'
Behavior: Manual sync only

No automatic sync
Only suitable for testing
Requires manual sync runs

6.5 Detecting inconsistencies

Method: DataSynchronizer::get_ids_of_orders_pending_sync()

Inconsistency types:

ID_TYPE_MISSING_IN_ORDERS_TABLE = 0      // Order in posts, missing in HPOS
ID_TYPE_MISSING_IN_POSTS_TABLE = 1       // Order in HPOS, missing in posts
ID_TYPE_DIFFERENT_UPDATE_DATE = 2        // Different updated date
ID_TYPE_DELETED_FROM_ORDERS_TABLE = 3    // Deleted from HPOS, still in posts
ID_TYPE_DELETED_FROM_POSTS_TABLE = 4     // Deleted from posts, still in HPOS

Conflict resolution:

The authoritative table ALWAYS wins
The secondary table gets overwritten
Log a warning for manual review

6.6 Migration workflow with compatibility

Step 1: Enable HPOS (Settings > Advanced > Features)
       ↓
       State: Legacy Mode (posts authoritative)
       HPOS tables created but not yet used

Step 2: Turn on compatibility sync
       ↓
       State: Legacy Mode + Sync ON
       Data starts copying into HPOS

Step 3: Wait for the migration to finish
       ↓
       Progress: 0% → 100%
       Tools > WooCommerce > Status > Data Stores

Step 4: Switch HPOS to authoritative
       ↓
       State: HPOS Mode + Sync ON
       HPOS is now the primary source

Step 5: Test (for several days/weeks)
       ↓
       Monitor errors, validate functionality

Step 6: Disable sync (optional)
       ↓
       State: HPOS Only Mode
       Maximum performance, posts stop updating

6.7 Monitoring synchronization status

Admin UI: WooCommerce > Status > Data Stores

Shows:

Order count in the posts table
Order count in the HPOS tables
Orders still pending synchronization
Recent errors

Programmatic access:

use Automattic\WooCommerce\Utilities\OrderUtil;

// Is HPOS enabled?
$hpos_enabled = OrderUtil::custom_orders_table_usage_is_enabled();

// Is synchronization enabled?
$sync_enabled = OrderUtil::is_custom_order_tables_in_sync();

// Which table is authoritative?
$authoritative_table = OrderUtil::get_table_for_orders();
// Returns: 'wp_wc_orders' or 'wp_posts'
// ... existing code ...

6.8 Issues and fixes in compatibility mode

Issue 1: Slow order saves

Cause: Dual writes hitting both table sets

Resolution: Use Interval Mode instead of Continuous

Issue 2: Data inconsistencies

Cause: Synchronization error

Resolution: Run the WC CLI command: wp wc cot sync

Issue 3: High database load

Cause: Synchronizing too frequently

Resolution: Increase the sync interval (e.g., from 5 to 15 minutes)

Issue 4: Plugin still uses posts

Cause: Extension is not HPOS compatible

Resolution: Keep sync enabled and contact the plugin author

7. Data not stored in the default tables

After moving to HPOS, certain data is no longer stored or stored differently in the legacy WooCommerce tables.

7.1 Data NEVER synchronized into the posts table

Even with compatibility mode enabled, the following data is not present in wp_posts:

A. Structured address data

Legacy:

wp_postmeta:
- _billing_first_name
- _billing_last_name
- _billing_address_1
- _billing_city
-- ... (20 entries for billing)
- _shipping_first_name
-- ... (20 entries for shipping)

HPOS:

wp_wc_order_addresses:
- 1 record for billing (address_type = 'billing')
- 1 record for shipping (address_type = 'shipping')

wp_postmeta: NOT synchronized (if HPOS authoritative)

Impact:

Extensions reading _billing_* metadata directly from postmeta WILL NOT work
They must rely on the WooCommerce API: $order->get_billing_first_name()

B. Operational flags

Legacy:

wp_postmeta:
- _order_stock_reduced
- _download_permission_granted
- _new_order_email_sent
- _recorded_sales
- _coupon_usages_are_counted

HPOS:

wp_wc_order_operational_data:
- order_stock_reduced (tinyint column)
- download_permission_granted
- new_order_email_sent
- recorded_sales
- coupon_usages_are_counted

wp_postmeta: NOT synchronized

C. Date of operations

Legacy:

wp_postmeta:
- _date_paid (timestamp)
- _date_completed (timestamp)

HPOS:

wp_wc_order_operational_data:
- date_paid_gmt (datetime)
- date_completed_gmt (datetime)

wp_postmeta: NOT synchronized

7.2 Data stored twice (for compatibility)

When sync is enabled, these data points exist in BOTH systems:

A. Core order metadata

wp_wc_orders_meta + wp_postmeta:
- _customer_user
- _order_currency
- _payment_method
- _payment_method_title
- _transaction_id
- _order_key
- _order_total
- _order_tax
- _order_shipping

Reason: Backwards compatibility with legacy plugins

B. Status information

wp_wc_orders.status <-> wp_posts.post_status

HPOS: 'completed'
Posts: 'wc-completed' (with prefix)

7.3 Data NOT STORED anywhere after HPOS migration

A. Post-specific data (if sync is off)

Lost data:

wp_posts:
- post_content (usually empty for orders)
- post_excerpt (customer_note moved to wp_wc_orders.customer_note)
- post_title (e.g. "Order #12345")
- post_name (slug)
- post_author (always 0)
- post_parent (duplicated with parent_order_id)
- comment_count
- ping_status, comment_status
- menu_order
- guid

Impact: Minimal, this data was not being used

B. Order revisions

In Legacy:

wp_posts: post_type = 'revision', post_parent = order_id

In HPOS:

NO revisions!

Impact: Cannot revert order to previous state (this functionality was not present in WC)

C. Custom post metadata from plugins

Problem:

// Old plugin stores custom metadata
add_post_meta($order_id, '_custom_plugin_data', 'value');

// After HPOS migration:
// - If sync ON: Will be in wp_postmeta (but NOT automatically updated)
// - If sync OFF: DATA LOST!

Resolution:

// HPOS compatible code
$order = wc_get_order($order_id);
$order->update_meta_data('_custom_plugin_data', 'value');
$order->save();
// → Saved to wp_wc_orders_meta (and wp_postmeta if sync ON)

7.4 Data NOT AUTOGENERATED

A. Lookup tables for analytics

Problem:

wp_wc_order_stats
wp_wc_order_product_lookup
wp_wc_order_tax_lookup
wp_wc_order_coupon_lookup

Not automatically populated after HPOS migration!

Resolution:

# WP-CLI regeneration
wp wc tool run regenerate_product_lookup_tables --user=admin
wp wc tool run clear_transients --user=admin

Admin UI:

WooCommerce > Status > Tools > Regenerate shop thumbnails
WooCommerce > Status > Tools > Clear template cache

B. Product lookup tables

Problem:

wp_wc_product_meta_lookup may be outdated

Resolution:

// Automatic regeneration on product save
$product = wc_get_product($product_id);
$product->save();

7.5 Metadata NOT STORED in wp_wc_orders_meta

The following “internal” metadata ARE NOT in the meta table (they are in columns):

// OrdersTableDataStore.php internal_meta_keys
$internal_keys = array(
    '_order_currency',           // → wp_wc_orders.currency
    '_prices_include_tax',       // → wp_wc_order_operational_data.prices_include_tax
    '_customer_user',            // → wp_wc_orders.customer_id
    '_order_key',                // → wp_wc_order_operational_data.order_key
    '_billing_email',            // → wp_wc_orders.billing_email
    '_billing_*',                // → wp_wc_order_addresses (billing row)
    '_shipping_*',               // → wp_wc_order_addresses (shipping row)
    '_payment_method',           // → wp_wc_orders.payment_method
    '_payment_method_title',     // → wp_wc_orders.payment_method_title
    '_transaction_id',           // → wp_wc_orders.transaction_id
    '_customer_ip_address',      // → wp_wc_orders.ip_address
    '_customer_user_agent',      // → wp_wc_orders.user_agent
    '_created_via',              // → wp_wc_order_operational_data.created_via
    '_date_paid',                // → wp_wc_order_operational_data.date_paid_gmt
    '_date_completed',           // → wp_wc_order_operational_data.date_completed_gmt
    '_cart_hash',                // → wp_wc_order_operational_data.cart_hash
    '_order_stock_reduced',      // → wp_wc_order_operational_data.order_stock_reduced
    '_download_permissions_granted', // → wp_wc_order_operational_data.download_permission_granted
    '_recorded_sales',           // → wp_wc_order_operational_data.recorded_sales
    '_recorded_coupon_usage_counts', // → wp_wc_order_operational_data.coupon_usages_are_counted
    '_order_shipping',           // → wp_wc_order_operational_data.shipping_total_amount
    '_order_shipping_tax',       // → wp_wc_order_operational_data.shipping_tax_amount
    '_cart_discount',            // → wp_wc_order_operational_data.discount_total_amount
    '_cart_discount_tax',        // → wp_wc_order_operational_data.discount_tax_amount
    '_order_total',              // → wp_wc_orders.total_amount
    '_order_tax',                // → wp_wc_orders.tax_amount
);

Consequence:

// Not working:
$order->get_meta('_billing_first_name'); // Returns empty

// Working:
$order->get_billing_first_name(); // Returns value from wp_wc_order_addresses

7.6 Comparison table of storages

Data	Legacy (Posts)	HPOS	HPOS + Sync	HPOS Only
Basic info	wp_posts	wp_wc_orders	Both	wp_wc_orders
Addresses	wp_postmeta (40 rows)	wp_wc_order_addresses (2 rows)	Both	wp_wc_order_addresses
Operational data	wp_postmeta (15 rows)	wp_wc_order_operational_data (1 row)	HPOS only	wp_wc_order_operational_data
Custom metadata	wp_postmeta	wp_wc_orders_meta	Both	wp_wc_orders_meta
Order items	wp_woocommerce_order_items	wp_woocommerce_order_items	Same	wp_woocommerce_order_items
Item metadata	wp_woocommerce_order_itemmeta	wp_woocommerce_order_itemmeta	Same	wp_woocommerce_order_itemmeta
Analytics	Not generated	Not generated	Must be regenerated	Must be regenerated

7.7 Checklist for migration

Data to verify:

[ ] Order totals match (posts vs HPOS)
[ ] Complete billing/shipping addresses
[ ] Payment methods transferred correctly
[ ] Transaction IDs preserved
[ ] Order notes unchanged (they are in wp_comments!)
[ ] Custom plugin metadata transferred
[ ] Analytics lookup tables regenerated
[ ] Old plugins functioning (if sync ON)
[ ] Full Text Search indexes created (if required)

WP-CLI check:

# Check number of orders
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type='shop_order'"
wp db query "SELECT COUNT(*) FROM wp_wc_orders"

# Check for inconsistencies
wp wc cot verify-cot-data

# Synchronization
wp wc cot sync

8. Key PHP classes

8.1 CustomOrdersTableController

File: src/Internal/DataStores/Orders/CustomOrdersTableController.php

Purpose: Central orchestration of HPOS functionality

Key methods:

class CustomOrdersTableController {
    // Checks if HPOS is authoritative source
    public function custom_orders_table_usage_is_enabled(): bool;

    // Returns the correct data store (HPOS or Legacy)
    public function get_orders_data_store(): string;

    // Checks if cache is enabled
    public function hpos_data_caching_is_enabled(): bool;

    // Adds admin tools for HPOS
    public function add_hpos_tools(): void;

    // Processes HPOS settings update
    public function process_updated_option( $option, $old_value, $value ): void;
}

Constants:

const CUSTOM_ORDERS_TABLE_USAGE_ENABLED_OPTION =
    'woocommerce_custom_orders_table_enabled';

const ORDERS_DATA_SYNC_ENABLED_OPTION =
    'woocommerce_custom_orders_table_data_sync_enabled';

const USE_DB_TRANSACTIONS_OPTION =
    'woocommerce_use_db_transactions_for_custom_orders_table_data_sync';

const HPOS_FTS_INDEX_OPTION =
    'woocommerce_hpos_fts_index_enabled';

Filter hooks:

// Changes data store for orders
add_filter('woocommerce_order_data_store',
    [$controller, 'get_orders_data_store']);

// Changes data store for refunds
add_filter('woocommerce_order-refund_data_store',
    [$controller, 'get_orders_refund_data_store']);

8.2 OrdersTableDataStore

File: src/Internal/DataStores/Orders/OrdersTableDataStore.php

Purpose: CRUD operations on HPOS tables

Key methods:

class OrdersTableDataStore extends \WC_Abstract_Order_Data_Store {
    // Loads order from HPOS
    public function read( &$order ): void;

    // Creates a new order
    public function create( &$order ): void;

    // Updates an existing order
    public function update( &$order ): void;

    // Deletes an order
    public function delete( &$order, $args = array() ): void;

    // Returns SQL schema for HPOS tables
    public static function get_database_schema(): string;

    // Maps properties to columns
    protected function get_all_order_column_mappings(): array;

    // Works with addresses
    protected function sync_order_address_to_order_address_table( $order ): void;

    // Works with operational data
    protected function sync_order_operational_data_to_order_table( $order ): void;
}

Internal meta keys (columns, not metadata):

protected $internal_meta_keys = array(
    '_order_currency',
    '_prices_include_tax',
    '_customer_user',
    '_billing_email',
    '_billing_first_name',
    // ... (see section 7.5)
);

Usage:

// Automatically used when HPOS is enabled
$order = wc_get_order(12345);
// → Internally calls OrdersTableDataStore::read()

8.3 DataSynchronizer

File: src/Internal/DataStores/Orders/DataSynchronizer.php

Purpose: Synchronizes between posts and HPOS tables

Key methods:

class DataSynchronizer {
    // Gets IDs of orders pending sync
    public function get_ids_of_orders_pending_sync(
        int $limit,
        array $order_types = array()
    ): array;

    // Synchronizes specific orders
    public function process_batch( array $order_ids ): BatchProcessingResult;

    // Gets total count of pending syncs
    public function get_total_pending_count(): int;

    // Checks if sync is active
    public function data_sync_is_enabled(): bool;

    // Gets sync mode (interval/continuous/off)
    public function get_background_sync_mode(): string;
}

Constants for inconsistency types:

const ID_TYPE_MISSING_IN_ORDERS_TABLE = 0;
const ID_TYPE_MISSING_IN_POSTS_TABLE = 1;
const ID_TYPE_DIFFERENT_UPDATE_DATE = 2;
const ID_TYPE_DELETED_FROM_ORDERS_TABLE = 3;
const ID_TYPE_DELETED_FROM_POSTS_TABLE = 4;

Sync modes:

const BACKGROUND_SYNC_MODE_INTERVAL = 'interval';
const BACKGROUND_SYNC_MODE_CONTINUOUS = 'continuous';
const BACKGROUND_SYNC_MODE_OFF = 'off';

Usage:

$synchronizer = wc_get_container()
    ->get( DataSynchronizer::class );

// Number of pending syncs
$pending = $synchronizer->get_total_pending_count();

// Manual sync batch
$order_ids = [12345, 12346, 12347];
$result = $synchronizer->process_batch( $order_ids );

8.4 OrderUtil (Helper class)

File: src/Utilities/OrderUtil.php

Purpose: Utility functions for HPOS

Key methods:

class OrderUtil {
    // Is HPOS authoritative?
    public static function custom_orders_table_usage_is_enabled(): bool;

    // Is datastore cache enabled?
    public static function custom_orders_table_datastore_cache_enabled(): bool;

    // Is order object cache enabled?
    public static function orders_cache_usage_is_enabled(): bool;

    // Is sync active?
    public static function is_custom_order_tables_in_sync(): bool;

    // Returns table name for orders
    public static function get_table_for_orders(): string;

    // Returns table name for metadata
    public static function get_table_for_order_meta(): string;

    // Count of orders by type
    public static function get_count_for_type( string $type ): int;

    // Is HPOS active (at all)?
    public static function is_custom_order_tables_usage_enabled(): bool;
}

Usage:

use Automattic\WooCommerce\Utilities\OrderUtil;

// Check HPOS
if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
    echo "HPOS is authoritative source";
} else {
    echo "Posts table is authoritative";
}

// Table name
$table = OrderUtil::get_table_for_orders();
// → 'wp_wc_orders' or 'wp_posts'

$meta_table = OrderUtil::get_table_for_order_meta();
// → 'wp_wc_orders_meta' or 'wp_postmeta'

// Number of orders
$count = OrderUtil::get_count_for_type( 'shop_order' );

8.5 DatabaseUtil

File: src/Internal/Utilities/DatabaseUtil.php

Purpose: Low-level DB operations

Key methods:

class DatabaseUtil {
    // Wrapper for WordPress dbDelta
    public static function dbdelta( string $sql ): array;

    // Checks for missing tables
    public static function get_missing_tables(): array;

    // Safely deletes table
    public static function drop_database_table( string $table ): void;

    // Creates primary key
    public static function create_primary_key(
        string $table,
        string $column
    ): void;

    // Converts value for DB
    public static function format_object_value_for_db(
        $value,
        string $type
    );

    // Checks FTS index
    public static function fts_index_on_order_address_table_exists(): bool;
}

8.6 Migration class

PostsToOrdersMigrationController

File: src/Database/Migrations/CustomOrderTable/PostsToOrdersMigrationController.php

Purpose: Orchestrates entire migration

Key methods:

class PostsToOrdersMigrationController {
    // Starts migration
    public function migrate_orders( int $batch_size = 100 ): MigrationResult;

    // Count of remaining orders to migrate
    public function count_orders_to_migrate(): int;

    // Migration status
    public function get_migration_status(): array;

    // Rollback migration
    public function rollback_migration(): void;
}

PostToOrderTableMigrator

File: src/Database/Migrations/CustomOrderTable/PostToOrderTableMigrator.php

Purpose: Migrates data from wp_posts to wp_wc_orders

PostMetaToOrderMetaMigrator

File: src/Database/Migrations/CustomOrderTable/PostMetaToOrderMetaMigrator.php

Purpose: Migrates metadata

PostToOrderAddressTableMigrator

File: src/Database/Migrations/CustomOrderTable/PostToOrderAddressTableMigrator.php

Purpose: Extracts addresses to wp_wc_order_addresses

PostToOrderOpTableMigrator

File: src/Database/Migrations/CustomOrderTable/PostToOrderOpTableMigrator.php

Purpose: Extracts operational data

8.7 LegacyDataHandler

File: src/Internal/DataStores/Orders/LegacyDataHandler.php

Purpose: Backward compatibility with Legacy code

Key methods:

class LegacyDataHandler {
    // Converts HPOS order to Legacy format
    public function convert_order_to_legacy_format( $order ): array;

    // Converts Legacy format to HPOS
    public function convert_legacy_format_to_order( array $data ): WC_Order;

    // Maps Legacy meta keys
    public function get_legacy_meta_key_mappings(): array;
}

9. Configuration and settings

9.1 WordPress Options (wp_options)

All HPOS related options:

// === Main settings ===

// Is HPOS authoritative source?
'woocommerce_custom_orders_table_enabled'
// Values: 'yes' | 'no'
// Default: 'no'

// Is synchronization active?
'woocommerce_custom_orders_table_data_sync_enabled'
// Values: 'yes' | 'no'
// Default: 'yes' (when HPOS is first enabled)

// Were HPOS tables created?
'woocommerce_custom_orders_table_created'
// Values: 'yes' | 'no'
// Internal flag

// === Synchronization ===

// Background sync mode
'woocommerce_custom_orders_table_background_sync_mode'
// Values: 'interval' | 'continuous' | 'off'
// Default: 'interval'

// Sync interval (in seconds)
'woocommerce_custom_orders_table_background_sync_interval'
// Values: int (e.g., 300 = 5 minutes)
// Default: 300

// Use DB transactions for sync?
'woocommerce_use_db_transactions_for_custom_orders_table_data_sync'
// Values: 'yes' | 'no'
// Default: 'yes'

// Isolation level for transactions
'woocommerce_db_transactions_isolation_level_for_custom_orders_table_data_sync'
// Values: 'READ UNCOMMITTED' | 'READ COMMITTED' | 'REPEATABLE READ' | 'SERIALIZABLE'
// Default: 'REPEATABLE READ'

// === Performance ===

// Is HPOS datastore cache enabled?
'woocommerce_hpos_datastore_caching_enabled'
// Values: 'yes' | 'no'
// Default: 'yes'

// Is order object cache enabled?
'woocommerce_orders_cache_enabled'
// Values: 'yes' | 'no'
// Default: 'yes'

// === Full Text Search ===

// Is FTS index enabled?
'woocommerce_hpos_fts_index_enabled'
// Values: 'yes' | 'no'
// Default: 'no'

// === Migration Status ===

// Migration status (serialized array)
'woocommerce_custom_orders_table_migration_status'
// Contains: pending_count, migrated_count, errors, last_batch_time

// Last X migration errors
'woocommerce_custom_orders_table_migration_errors'
// Serialized array

9.2 Activating HPOS via Admin UI

Path: WooCommerce > Settings > Advanced > Features

Section: “Custom data stores”

Options:

High-performance order storage

Enable compatibility mode

Background sync mode

Make HPOS authoritative

9.3 Programmatic configuration

Checking status:

use Automattic\WooCommerce\Utilities\OrderUtil;

// Is HPOS enabled?
$hpos_enabled = OrderUtil::custom_orders_table_usage_is_enabled();

// Is sync enabled?
$sync_enabled = get_option(
    'woocommerce_custom_orders_table_data_sync_enabled'
) === 'yes';

// Sync mode
$sync_mode = get_option(
    'woocommerce_custom_orders_table_background_sync_mode',
    'interval'
);

// Is cache enabled?
$cache_enabled = OrderUtil::custom_orders_table_datastore_cache_enabled();

// FTS indexes?
$fts_enabled = get_option(
    'woocommerce_hpos_fts_index_enabled'
) === 'yes';

Changing settings:

// Activate HPOS
update_option( 'woocommerce_custom_orders_table_enabled', 'yes' );

// Turn on sync
update_option( 'woocommerce_custom_orders_table_data_sync_enabled', 'yes' );

// Change sync mode
update_option(
    'woocommerce_custom_orders_table_background_sync_mode',
    'continuous'
);

// Change sync interval (10 minutes)
update_option(
    'woocommerce_custom_orders_table_background_sync_interval',
    600
);

// Turn on FTS indexes
update_option( 'woocommerce_hpos_fts_index_enabled', 'yes' );

// IMPORTANT: After changing, flush rewrite rules
flush_rewrite_rules();

9.4 WP-CLI commands

Available commands:

# === HPOS status ===

# Check if HPOS tables are active
wp option get woocommerce_custom_orders_table_enabled

# Check number of orders in HPOS
wp db query "SELECT COUNT(*) FROM wp_wc_orders"

# Check number of orders in posts
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type='shop_order'"

# === Migration ===

# Migrate orders to HPOS (batch)
wp wc cot sync --batch-size=100

# Verify data after migration
wp wc cot verify-cot-data

# Rollback migration (DANGEROUS!)
# This function may not exist in all versions

# === Tools ===

# Regenerate product lookup tables
wp wc tool run regenerate_product_lookup_tables --user=admin

# Clear WC cache
wp wc tool run clear_transients --user=admin

# Clear WC session
wp wc tool run clear_sessions --user=admin

# === Debug ===

# Check pending sync count
wp eval "echo wc_get_container()->get( Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer::class )->get_total_pending_count();"

# Show HPOS config
wp eval "
\$controller = wc_get_container()->get( Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController::class );
echo 'HPOS Enabled: ' . ( \$controller->custom_orders_table_usage_is_enabled() ? 'Yes' : 'No' ) . PHP_EOL;
"

9.5 Constants in wp-config.php

Enforcing settings via wp-config:

// wp-config.php

// === Enforcing HPOS mode ===

// Enforcing HPOS authoritative (ignoring admin settings)
define( 'WC_FORCE_HPOS_ENABLED', true );

// Enforcing Legacy mode (disabling HPOS)
define( 'WC_FORCE_HPOS_DISABLED', true );

// === Developer constants ===

// Allow changing storage during pending sync (DANGEROUS!)
define( 'WC_ALLOW_CHANGING_ORDERS_STORAGE_WHILE_SYNC_PENDING', true );

// HPOS debug mode
define( 'WC_HPOS_DEBUG', true );

// Log all HPOS operations
define( 'WC_LOG_HPOS_OPERATIONS', true );

Usage:

// In code:
if ( defined( 'WC_FORCE_HPOS_ENABLED' ) && WC_FORCE_HPOS_ENABLED ) {
    // HPOS enforced via wp-config
}

9.6 Filter hooks for customization

// === Data Store Selection ===

// Change data store for orders
add_filter( 'woocommerce_order_data_store', function( $store ) {
    // Return custom implementation
    return 'MyCustomOrderDataStore';
} );

// Change data store for refunds
add_filter( 'woocommerce_order-refund_data_store', function( $store ) {
    return 'MyCustomRefundDataStore';
} );

// === Synchronization ===

// Change sync batch size
add_filter( 'woocommerce_orders_cot_migration_batch_size', function( $size ) {
    return 50; // Default: 100
} );

// Add custom log before sync
add_action( 'woocommerce_before_order_sync_to_posts', function( $order_id ) {
    // Custom log
}, 10, 1 );

// Add custom log after sync
add_action( 'woocommerce_after_order_sync_to_posts', function( $order_id ) {
    // Custom log
}, 10, 1 );

// === Migration ===

// Modify SQL schema before creating tables
add_filter( 'woocommerce_orders_table_schema', function( $schema ) {
    // Add custom columns/indexes
    return $schema;
} );

// Hook after HPOS tables are created
add_action( 'woocommerce_after_orders_table_created', function() {
    // Custom init logic
} );

// === Cache ===

// Disable HPOS cache for specific order
add_filter( 'woocommerce_hpos_cache_order_' . $order_id, '__return_false' );

// === Full Text Search ===

// Modify FTS columns
add_filter( 'woocommerce_hpos_fts_columns', function( $columns ) {
    $columns[] = 'custom_column';
    return $columns;
} );

10. Migrating to HPOS

10.1 Preparing for migration

Checklist:

[ ] Database backup (REQUIRED!)

[ ] Update WooCommerce to latest version

[ ] Check plugin compatibility

[ ] Test on staging environment

[ ] Monitor performance

[ ] Inform team

10.2 Migration process (Recommended)

Phase 1: Activating HPOS in Compatibility mode

Step 1.1: Enable HPOS in admin

WooCommerce > Settings > Advanced > Features
→ Check "Enable the high-performance order storage feature"
→ Save changes

Result:

HPOS tables created
Still NOT USED (posts is still authoritative)

Verification:

wp db query "SHOW TABLES LIKE 'wp_wc_orders%'"
# Should return:
# - wp_wc_orders
# - wp_wc_order_addresses
# - wp_wc_order_operational_data
# - wp_wc_orders_meta

Phase 2: Starting migration

Step 2.1: Enable compatibility mode

WooCommerce > Settings > Advanced > Features
→ Check "Enable compatibility mode"
→ Select "Interval" sync mode
→ Save changes

Result:

Background migration starts
Data starts copying to HPOS
Posts table remains authoritative

Step 2.2: Monitor progress

WooCommerce > Status > Data Stores

Shows:

Orders pending sync: 1,234
Orders in posts table: 10,000
Orders in HPOS tables: 8,766
Last sync: 2 minutes ago

CLI monitoring:

# Monitor progress in real-time
watch -n 10 'wp eval "
\$sync = wc_get_container()->get( Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer::class );
echo \"Pending: \" . \$sync->get_total_pending_count() . PHP_EOL;
"'

Estimated time:

~100 orders/min (depends on server)
10,000 orders = ~100 minutes
100,000 orders = ~17 hours

Tip: Speed up migration by changing sync mode to “Continuous” (temporarily)

Phase 3: Verifying migration

Step 3.1: Wait for pending sync to drop to 0

WooCommerce > Status > Data Stores
→ Orders pending sync: 0

Step 3.2: Verify data

# Number of orders should be the same
wp db query "SELECT COUNT(*) as posts_count FROM wp_posts WHERE post_type='shop_order'"
wp db query "SELECT COUNT(*) as hpos_count FROM wp_wc_orders"

Step 3.3: Check integrity

wp wc cot verify-cot-data

Expected output:

Verifying HPOS data integrity...
 All orders migrated
 No missing addresses
 No missing operational data
 Metadata counts match
Success: HPOS data is consistent

Step 3.4: Manual check

-- Random sample of orders
SELECT
    p.ID as post_id,
    o.id as hpos_id,
    p.post_status as post_status,
    o.status as hpos_status,
    pm_billing.meta_value as post_email,
    o.billing_email as hpos_email
FROM wp_posts p
LEFT JOIN wp_wc_orders o ON p.ID = o.id
LEFT JOIN wp_postmeta pm_billing ON p.ID = pm_billing.post_id AND pm_billing.meta_key = '_billing_email'
WHERE p.post_type = 'shop_order'
ORDER BY RAND()
LIMIT 10;

Expected result: All values should be identical

Phase 4: Switching to HPOS authoritative

WARNING: From this point, HPOS is the primary source!

Step 4.1: Switch to authoritative source

WooCommerce > Settings > Advanced > Features
→ In "High-performance order storage" section, change:
  "Authoritative data store" → "High-performance order storage"
→ Save changes

Or CLI:

wp option update woocommerce_custom_orders_table_enabled yes

Result:

HPOS is now authoritative
Changes are saved to HPOS
Posts table updates in the background (if sync ON)

Step 4.2: Verify switch

use Automattic\WooCommerce\Utilities\OrderUtil;

if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
    echo " HPOS is authoritative";
} else {
    echo " Still running Legacy mode";
}

Step 4.3: Test creating an order

1. Create a test order in admin
2. Verify it exists in wp_wc_orders
3. Verify it exists (if sync ON) in wp_posts

Phase 5: Testing (critical phase!)

Test checklist:

Features to test:

[ ] Creating an order (checkout)
[ ] Editing an order in admin
[ ] Changing order status
[ ] Adding order note
[ ] Creating a refund
[ ] Sending emails (order completed, etc.)
[ ] Generating invoices (if plugin)
[ ] Exporting orders
[ ] WooCommerce Analytics
[ ] REST API queries
[ ] Plugin integration (payment gateways, shipping, etc.)

CLI tests:

# Test REST API
wp eval "
\$order = wc_create_order();
\$order->set_billing_email('test@test.com');
\$order->set_total(100);
\$order->save();
echo 'Order ID: ' . \$order->get_id() . PHP_EOL;
echo 'Table: ' . \Automattic\WooCommerce\Utilities\OrderUtil::get_table_for_orders() . PHP_EOL;
"

Test duration: At least 7 days on production with active traffic

Phase 6: Disabling synchronization (optional)

WARNING: Disabling sync cannot be easily rolled back!

When to disable sync:

After at least 7 days without issues
All HPOS compatible plugins
You want maximum performance

Step 6.1: Disable compatibility mode

WooCommerce > Settings > Advanced > Features
→ Uncheck "Enable compatibility mode"
→ Save changes

Result:

Posts table stops updating
Only HPOS tables are used
~50% faster order saves

Step 6.2: Monitoring

Monitor error logs
Check plugin functionality
Verify no plugins report errors

Phase 7: Cleanup (optional)

WARNING: Deleting data from posts is IRREVERSIBLE!

When to perform cleanup:

After at least 30 days without issues
All systems stable
You have a fresh backup

Options:

A. Partial cleanup (recommended)

-- Delete only postmeta (leave posts intact)
DELETE pm FROM wp_postmeta pm
INNER JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'shop_order'
  AND pm.meta_key LIKE '\_%';

-- Saves: ~60% space
-- Risk: Low (posts remain for reference)

B. Full cleanup (risky)

-- Delete all order posts
DELETE FROM wp_posts WHERE post_type = 'shop_order';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

-- Saves: ~90% space
-- Risk: High (cannot be restored without backup)

CLI cleanup:

# DANGEROUS! Perform only with fresh backup!
wp db query "
DELETE p, pm
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
"

Post-cleanup:

Optimize tables

10.3 Rollback plan

What to do when rolling back:

Severe issues in HPOS mode
Incompatible plugins
Performance issues

Rollback from Phase 4-5 (HPOS authoritative)

Step 1: Switch back to Legacy

WooCommerce > Settings > Advanced > Features
→ "Authoritative data store" → "WordPress posts storage"
→ Save changes

Or CLI:

wp option update woocommerce_custom_orders_table_enabled no

Step 2: Verify sync

# Wait for pending sync to drop to 0
wp eval "
\$sync = wc_get_container()->get( Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer::class );
echo \$sync->get_total_pending_count();
"

Step 3: Test

Create a test order
Verify it exists in wp_posts

Rollback from Phase 6 (Sync off)

Problem: Sync off = posts table is outdated!

Solution:

Step 1: Enable sync

wp option update woocommerce_custom_orders_table_data_sync_enabled yes

Step 2: Reverse sync (HPOS → Posts)

# This command may not exist in the current version of WC!
# May be necessary to create a custom script
wp wc cot sync --direction=to-posts

Step 3: Wait for sync to complete

Step 4: Switch back to Legacy

wp option update woocommerce_custom_orders_table_enabled no

Full rollback (restoring from backup)

Most reliable method:

# 1. Stop web (maintenance mode)
wp maintenance-mode activate

# 2. Restore database from backup
wp db import backup-before-hpos.sql

# 3. Disable HPOS
wp option delete woocommerce_custom_orders_table_enabled
wp option delete woocommerce_custom_orders_table_data_sync_enabled

# 4. Delete HPOS tables (optional)
wp db query "DROP TABLE IF EXISTS wp_wc_orders"
wp db query "DROP TABLE IF EXISTS wp_wc_order_addresses"
wp db query "DROP TABLE IF EXISTS wp_wc_order_operational_data"
wp db query "DROP TABLE IF EXISTS wp_wc_orders_meta"

# 5. Flush cache
wp cache flush

# 6. Reactivate web
wp maintenance-mode deactivate

10.4 Troubleshooting migration

Issue 1: Migration freezes

Symptoms:

Pending sync does not increase
Status > Data Stores shows the same hour number

Diagnosis:

# Check cron
wp cron event list | grep woocommerce

# Check error log
tail -f /path/to/wp-content/debug.log

Resolution:

A. Restart WP Cron

wp cron event run --all

B. Manual sync batch

wp wc cot sync --batch-size=50

C. Change sync mode to Continuous (temporarily)

wp option update woocommerce_custom_orders_table_background_sync_mode continuous

Issue 2: Inconsistent data

Symptoms:

Different number of orders in posts vs HPOS
Missing orders

Diagnosis:

-- Orders in posts but not in HPOS
SELECT p.ID
FROM wp_posts p
LEFT JOIN wp_wc_orders o ON p.ID = o.id
WHERE p.post_type = 'shop_order'
  AND o.id IS NULL;

-- Orders in HPOS but not in posts
SELECT o.id
FROM wp_wc_orders o
LEFT JOIN wp_posts p ON o.id = p.ID
WHERE p.ID IS NULL;

Resolution:

# Re-sync specific orders
wp eval "
\$order_ids = [12345, 12346, 12347];
\$sync = wc_get_container()->get( Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer::class );
\$sync->process_batch( \$order_ids );
"

Issue 3: Missing addresses

Symptoms:

Empty billing/shipping fields in HPOS

Diagnosis:

-- Orders without addresses
SELECT o.id, o.billing_email
FROM wp_wc_orders o
LEFT JOIN wp_wc_order_addresses a ON o.id = a.order_id
WHERE a.id IS NULL;

Resolution:

# Re-migrate addresses
wp eval "
\$order_ids = [12345]; // IDs with missing addresses
foreach ( \$order_ids as \$order_id ) {
    \$order = wc_get_order( \$order_id );
    if ( \$order ) {
        \$order->save(); // Triggers address sync
    }
}
"

Issue 4: DB lock timeout

Symptoms:

Error: Lock wait timeout exceeded; try restarting transaction

Cause: Large volume of concurrent transactions

Resolution:

A. Increase DB timeout

SET GLOBAL innodb_lock_wait_timeout = 120; -- Default 50

B. Decrease batch size

wp option update woocommerce_orders_cot_migration_batch_size 25

C. Turn off transactions (last resort)

wp option update woocommerce_use_db_transactions_for_custom_orders_table_data_sync no

11. Performance characteristics

11.1 Benchmark results

Test environment:

10,000 orders
WordPress 6.4
WooCommerce 8.4+
MySQL 8.0
PHP 8.1

Test 1: Loading one order

// Legacy (Posts)
$start = microtime(true);
$order = wc_get_order(12345);
$email = $order->get_billing_email();
$total = $order->get_total();
$time_legacy = microtime(true) - $start;
// Time: ~25ms

// HPOS
$start = microtime(true);
$order = wc_get_order(12345);
$email = $order->get_billing_email();
$total = $order->get_total();
$time_hpos = microtime(true) - $start;
// Time: ~8ms

// Speedup: 3.1x

SQL queries:

Legacy:

-- 1. Load post
SELECT * FROM wp_posts WHERE ID = 12345 AND post_type = 'shop_order';

-- 2. Load all metadata (50-100 rows)
SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = 12345;

-- Total: 2 queries, ~100 rows

HPOS:

-- 1. Load basic data
SELECT * FROM wp_wc_orders WHERE id = 12345;

-- 2. Load addresses (2 rows)
SELECT * FROM wp_wc_order_addresses WHERE order_id = 12345;

-- 3. Load operational data (1 row)
SELECT * FROM wp_wc_order_operational_data WHERE order_id = 12345;

-- 4. Load metadata (only custom, 0-10 rows)
SELECT meta_key, meta_value FROM wp_wc_orders_meta WHERE order_id = 12345;

-- Total: 4 queries, ~13 rows

Conclusion: HPOS faster due to smaller data volume, despite more queries

Test 2: Listing 20 orders

// Legacy
$orders = wc_get_orders([
    'limit' => 20,
    'orderby' => 'date',
    'order' => 'DESC',
]);
// Time: ~180ms

// HPOS
$orders = wc_get_orders([
    'limit' => 20,
    'orderby' => 'date',
    'order' => 'DESC',
]);
// Time: ~45ms

// Speedup: 4x

Explain analysis:

Legacy:

EXPLAIN SELECT p.ID
FROM wp_posts p
WHERE p.post_type = 'shop_order'
ORDER BY p.post_date DESC
LIMIT 20;

-- type: ref
-- rows: 10,000 (full scan)
-- Extra: Using where; Using filesort

HPOS:

EXPLAIN SELECT id
FROM wp_wc_orders
ORDER BY date_created_gmt DESC
LIMIT 20;

-- type: index
-- rows: 20 (limit optimized)
-- Extra: Using index
-- Key: date_created (dedicated index)

Test 3: Searching by email

// Legacy
$orders = wc_get_orders([
    'billing_email' => 'customer@example.com',
]);
// Time: ~420ms (10k orders)

// HPOS
$orders = wc_get_orders([
    'billing_email' => 'customer@example.com',
]);
// Time: ~12ms

// Speedup: 35x (!!)

SQL:

Legacy:

-- Slow meta JOIN
SELECT p.ID
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'shop_order'
  AND pm.meta_key = '_billing_email'
  AND pm.meta_value = 'customer@example.com';

-- EXPLAIN:
-- type: ALL (full table scan on postmeta!)
-- rows: 150,000+ (all postmeta records)

HPOS:

-- Direct index lookup
SELECT id
FROM wp_wc_orders
WHERE billing_email = 'customer@example.com';

-- EXPLAIN:
-- type: ref
-- rows: ~5 (only matching rows)
-- Key: billing_email (dedicated index)

Test 4: Filtering by status and date

// Legacy
$orders = wc_get_orders([
    'status' => 'completed',
    'date_created' => '>=' . strtotime('-30 days'),
    'limit' => -1,
]);
// Time: ~850ms

// HPOS
$orders = wc_get_orders([
    'status' => 'completed',
    'date_created' => '>=' . strtotime('-30 days'),
    'limit' => -1,
]);
// Time: ~95ms

// Speedup: 9x

HPOS advantage:

Composite index type_status_date perfectly optimized for this pattern
No meta JOINs

Test 5: Updating an order

// Legacy (without sync)
$order = wc_get_order(12345);
$order->set_status('completed');
$order->save();
// Time: ~35ms

// HPOS (without sync)
$order = wc_get_order(12345);
$order->set_status('completed');
$order->save();
// Time: ~18ms

// Speedup: 1.9x

// HPOS (with sync)
// Time: ~55ms (slower due to dual writes!)

Recommendation: After stabilizing HPOS, turn off sync for maximum performance

11.2 Comparison table

Operation	Legacy	HPOS (sync ON)	HPOS (sync OFF)	Speedup
Loading 1 order	25ms	12ms	8ms	3.1x
Listing 20 orders	180ms	60ms	45ms	4x
Searching by email	420ms	18ms	12ms	35x
Filter status + date	850ms	120ms	95ms	9x
Updating an order	35ms	55ms	18ms	1.9x
Creating an order	120ms	180ms	85ms	1.4x
Admin order listing (paginated)	650ms	180ms	120ms	5.4x
WC Analytics dashboard	2,500ms	850ms	600ms	4.2x

Summary:

Read operations: 3-35x faster
Write operations:
Admin UI: 4-5x faster

11.3 Database size

10,000 orders:

Table	Legacy	HPOS
wp_posts (orders)	15 MB	0 MB (or 2 MB if sync)
wp_postmeta (order meta)	180 MB	0 MB (or 50 MB if sync)
wp_wc_orders	–	8 MB
wp_wc_order_addresses	–	12 MB
wp_wc_order_operational_data	–	5 MB
wp_wc_orders_meta	–	25 MB
Total	195 MB	50 MB (74% savings!)

With sync ON: ~145 MB (26% savings)

Without sync: ~50 MB (74% savings)

11.4 Scalability

Tested on:

100,000 orders

Legacy:

Admin order listing: 5-8 seconds
Searching: 2-4 seconds
Database: ~2 GB
RAM usage (PHP): 256 MB for admin

HPOS:

Admin order listing: 800ms – 1.5s
Searching: 50-150ms
Database: ~500 MB
RAM usage (PHP): 128 MB for admin

Speedup: 6-8x

1,000,000 orders

Legacy:

Unusable (timeouts, out of memory)
Requires custom indexes and pagination

HPOS:

Admin order listing: 1.5-3 seconds
Searching: 150-300ms
Database: ~5 GB
Scales linearly

Conclusion: HPOS is the only solution for high-volume businesses

11.5 Cache efficiency

Object Cache (Redis/Memcached):

Legacy:

// Cache key contains serialized postmeta array
// → Large cache entry size
// → Poor hit rate
Cache size per order: ~15 KB

HPOS:

// Cache key contains only structured data
// → Smaller cache entries
// → Better hit rate
Cache size per order: ~3 KB

Result: HPOS has 80% cache hit rate vs 55% for Legacy

11.6 Index coverage

Legacy posts table:

-- Indexes on wp_posts
PRIMARY KEY (ID)
KEY type_status_date (post_type, post_status, post_date, ID)
KEY post_parent (post_parent)
KEY post_author (post_author)

-- Problem: post_status contains 'wc-' prefix
-- → Reduces index efficiency

HPOS:

-- Indexes on wp_wc_orders (optimized!)
PRIMARY KEY (id)
KEY status (status)                          -- Clean status
KEY date_created (date_created_gmt)         -- GMT for consistency
KEY customer_id_billing_email (customer_id, billing_email(100))  -- Composite
KEY billing_email (billing_email(100))      -- Email search
KEY type_status_date (type, status, date_created_gmt)  -- Multi-filter
KEY parent_order_id (parent_order_id)       -- Refunds
KEY date_updated (date_updated_gmt)         -- Sync

Coverage:

Legacy: ~60% of queries use index
HPOS: ~95% of queries use optimized index

11.7 Recommendations for performance

For small businesses (<1,000 orders/month):

HPOS provides smaller benefit
Consider staying on Legacy (simpler)
Or HPOS + sync ON (for future scalability)

For medium-sized businesses (1,000-10,000 orders/month):

HPOS highly recommended
Sync ON for first 30 days
Then sync OFF for maximum performance

For large businesses (>10,000 orders/month):

HPOS REQUIRED (Legacy scales poorly)
Sync OFF after stabilization
Consider dedicated MySQL server
Implement external object cache (Redis)

For enterprise (>100,000 orders/month):

HPOS + MySQL read replicas
HPOS + sharding (custom implementation)
Full Text Search indexes enabled
Aggressive caching strategies

Conclusion

HPOS (High-Performance Order Storage) represents a significant evolution of WooCommerce database architecture. Transition from generic WordPress posts system to dedicated, normalized tables brings:

Main advantages:

70-90% faster read operations
40% smaller database
Linear scalability
Modern SQL design with optimized indexes

Key components:

4 main HPOS tables (orders, addresses, operational_data, meta)
5+ lookup tables for analytics
Robust synchronization mechanism
Complete backward compatibility via compatibility mode

Recommended migration process:

Backup database
Test on staging
Activate HPOS with compatibility mode
Background migration (automatic)
Switch to HPOS authoritative
Disable sync (optional, after 30 days)

HPOS is now production-ready and recommended for all new and existing stores. Compatibility with the WordPress ecosystem is guaranteed via the sync mechanism, which can be safely turned off once the system stabilizes.

WooCommerce HPOS – complete guide

Home / Blog / WooCommerce HPOS – complete guide

WooCommerce HPOS – High-Performance Order Storage

Table of Contents

1. Introduction to HPOS

1.1 Why HPOS?

1.2 File structure in code

2. HPOS Database Tables

2.1 wp_wc_orders — Primary order table

2.2 wp_wc_order_addresses — Billing and shipping addresses

2.3 wp_wc_order_operational_data — Operational data

2.4 wp_wc_orders_meta — Order metadata

3. Product Lookup Tables

3.1 wp_wc_product_meta_lookup — Fast product access

3.2 wp_wc_order_stats — Order statistics

3.3 wp_wc_order_product_lookup — Order line items (analytics)

3.4 wp_wc_order_tax_lookup — Tax analytics

3.5 wp_wc_order_coupon_lookup — Coupon analytics

4. Additional custom WooCommerce tables

4.1 wp_woocommerce_sessions – Session management

4.2 wp_woocommerce_api_keys — REST API keys

4.3 wp_woocommerce_payment_tokens — Stored payment methods

4.4 wp_woocommerce_log — System logs

4.5 wp_woocommerce_order_items — Order items

4.6 wp_woocommerce_order_itemmeta — Item metadata

4.7 wp_woocommerce_tax_rates — Tax rates

4.8 wp_woocommerce_tax_rate_locations — Tax rate locations

4.9 wp_woocommerce_shipping_zones — Shipping zones

4.10 wp_woocommerce_shipping_zone_locations — Zone locations

4.11 wp_woocommerce_shipping_zone_methods — Shipping methods

4.12 wp_wc_webhooks — Webhook definitions

4.13 wp_wc_download_log — Download log

4.14 wp_wc_product_download_directories — Download directories

5. Comparison with the legacy storage

5.1 Legacy system (pre-HPOS)

5.2 HPOS system (new)

5.3 Comparison table

5.4 Concrete examples of the differences

Example 1: Loading an order with addresses

Example 2: Searching by email

Example 3: Filtering by date and status

6. Compatibility mode

6.1 How does compatibility work?

6.2 Operating modes

A. Legacy Mode (default)

B. HPOS Mode (recommended)

C. HPOS Only Mode (future)

6.3 Synchronization mechanism

6.4 Sync modes

A. Interval Mode (recommended)

B. Continuous Mode

C. Off Mode

6.5 Detecting inconsistencies

6.6 Migration workflow with compatibility

6.7 Monitoring synchronization status

6.8 Issues and fixes in compatibility mode

Issue 1: Slow order saves

Issue 2: Data inconsistencies

Issue 3: High database load

Issue 4: Plugin still uses posts

7. Data not stored in the default tables

7.1 Data NEVER synchronized into the posts table

A. Structured address data

B. Operational flags

C. Date of operations

7.2 Data stored twice (for compatibility)

A. Core order metadata

B. Status information

7.3 Data NOT STORED anywhere after HPOS migration

A. Post-specific data (if sync is off)

B. Order revisions

C. Custom post metadata from plugins

7.4 Data NOT AUTOGENERATED

A. Lookup tables for analytics

B. Product lookup tables

7.5 Metadata NOT STORED in wp_wc_orders_meta

7.6 Comparison table of storages

7.7 Checklist for migration

8. Key PHP classes

8.1 CustomOrdersTableController

2.1 `wp_wc_orders` — Primary order table

2.2 `wp_wc_order_addresses` — Billing and shipping addresses

2.3 `wp_wc_order_operational_data` — Operational data

2.4 `wp_wc_orders_meta` — Order metadata

3.1 `wp_wc_product_meta_lookup` — Fast product access

3.2 `wp_wc_order_stats` — Order statistics

3.3 `wp_wc_order_product_lookup` — Order line items (analytics)

3.4 `wp_wc_order_tax_lookup` — Tax analytics

3.5 `wp_wc_order_coupon_lookup` — Coupon analytics

4.1 `wp_woocommerce_sessions` – Session management

4.2 `wp_woocommerce_api_keys` — REST API keys

4.3 `wp_woocommerce_payment_tokens` — Stored payment methods

4.4 `wp_woocommerce_log` — System logs

4.5 `wp_woocommerce_order_items` — Order items

4.6 `wp_woocommerce_order_itemmeta` — Item metadata

4.7 `wp_woocommerce_tax_rates` — Tax rates

4.8 `wp_woocommerce_tax_rate_locations` — Tax rate locations

4.9 `wp_woocommerce_shipping_zones` — Shipping zones

4.10 `wp_woocommerce_shipping_zone_locations` — Zone locations

4.11 `wp_woocommerce_shipping_zone_methods` — Shipping methods

4.12 `wp_wc_webhooks` — Webhook definitions

4.13 `wp_wc_download_log` — Download log

4.14 `wp_wc_product_download_directories` — Download directories