WooCommerce HPOS: Architecture, Migration, and Operational Boundaries

The Problem: EAV Storage at Scale

WooCommerce historically stored orders as shop_order posts in wp_posts with all attributes — customer email, billing address, line items, payment tokens — serialized into wp_postmeta rows keyed by meta_key. This Entity-Attribute-Value (EAV) pattern works for small catalogs but collapses under join pressure: a query for "completed orders from customer X in January" requires scanning millions of postmeta rows, joining back to wp_posts, then filtering by post_status and post_date. At 100k+ orders, p95 latency routinely exceeds 2 seconds.

Takeaway: High Performance Order Storage (HPOS) replaces the EAV schema with dedicated tables and proper indexes. Migration is a one-way operational decision after cleanup; plan the compatibility window and verification steps before enabling.

Schema Design: Smallest Suitable Tables

HPOS introduces six core tables (prefixed with wp_wc_ by default):

Table	Purpose	Key Indexes
wp_wc_orders	Order header: ID, parent, status, currency, dates, customer_id	PRIMARY (id), status, date_created_gmt, customer_id
wp_wc_order_addresses	Billing/shipping split into typed columns	order_id, address_type (billing|shipping)
wp_wc_order_operational_data	Payment method, transaction ID, cart hash, download permissions	order_id
wp_wc_order_meta	Remaining non-indexed meta (notes, custom fields)	order_id, meta_key
wp_wc_order_product_lookup	Denormalized line items for product-level reporting	order_id, product_id, variation_id, sku
wp_wc_order_stats	Pre-aggregated reporting rows (one per order)	date_created_gmt, status, customer_id, type

Order IDs remain stable primary keys (wp_wc_orders.id = legacy wp_posts.ID), enabling multi-store sync without ID remapping.

Trust and Data Boundaries

The abstraction layer is WC_Order_Data_Store_HPOS, implementing the same interface as the legacy WC_Order_Data_Store_CPT. Code using WC_Order methods — get_id(), save(), get_billing_email() — continues working unchanged.

Boundary violation: Direct SQL on wp_postmeta with meta_key LIKE '_order_%' or meta_key = '_billing_email' bypasses the data store. These queries return stale or empty results once HPOS is enabled without the compatibility layer. Plugins must migrate to WC_Order_Query with HPOS-native arguments (customer_id, billing_email, status) or use the compatibility views (see below).

Compatibility Layer: Temporary Mirror

Define WC_HPOS_COMPAT constant in wp-config.php or set the woocommerce_hpos_enabled option to yes while keeping WC_HPOS_COMPAT=1:

// wp-config.php
define('WC_HPOS_COMPAT', true);

This mirrors writes to both HPOS tables and legacy wp_posts/wp_postmeta. Read queries against legacy tables stay current. Cost: every order write executes two insert/update cycles, adding ~15-30ms latency per checkout. Treat this as a migration window, not a permanent state. Target: all plugins updated to HPOS-aware APIs, then disable WC_HPOS_COMPAT.

Migration Strategy: CLI with Verification Gates

Run on the application server (not the database host) with WP-CLI and WooCommerce CLI installed. Requires manage_woocommerce capability (admin).

# 1. Pre-flight: confirm current state
wp wc hpos status
# Output includes: enabled, migrated, synced, cleanup_completed

Start migration in batches (default 100 orders/batch):

wp wc hpos migrate --batch-size=200

Monitor progress via Tools → Scheduled Actions (action wc_hpos_migrate). The process is resumable; re-run the same command after interruption.

Verification gate 1 — row count parity:

# Run on database host or via wp db query
SELECT COUNT(*) FROM wp_wc_orders;
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'shop_order' AND post_status != 'auto-draft';
# Must match exactly

Verification gate 2 — index usage:

EXPLAIN SELECT * FROM wp_wc_orders 
WHERE status = 'wc-completed' 
  AND date_created_gmt > '2024-01-01';
# Expect: type=ref, key=status_date_created_gmt, rows << total

Verification gate 3 — sync completion:

wp wc hpos status
# synced: true, cleanup_completed: false

Only after all three gates pass, run cleanup (irreversible):

wp wc hpos cleanup
# Drops legacy shop_order rows from wp_posts/wp_postmeta

Operational Checks

• Reporting query health: wp_wc_order_stats drives admin dashboards. Run EXPLAIN on typical date-range + status queries weekly.
• Compatibility sync cron: Action wc_hpos_sync_compatibility_tables should show "Complete" with zero pending in Scheduled Actions. If stuck, legacy plugins are writing to postmeta directly.
• Debug log watch: Enable WP_DEBUG_LOG and grep for wc_hpos or postmeta writes during checkout. Any legacy meta insert indicates a non-migrated plugin.
• Disk headroom: Migration duplicates order data (~30% DB size increase). Ensure 2x current DB size free before starting on stores >50k orders.

Failure Modes and Detection

Mode	Symptom	Detection	Resolution
Plugin writes postmeta directly	Order visible in admin, missing from wp_wc_orders until sync cron runs	Scheduled Actions: wc_hpos_sync_compatibility_tables backlog grows	Update plugin or add HPOS write via WC_Order_Data_Store_HPOS::update_order_meta()
Incomplete migration	Row count mismatch; orphan orders in HPOS or legacy	wp wc hpos status shows migrated: false; count query mismatch	Re-run wp wc hpos migrate; investigate failed batches in wp_wc_order_meta for error logs
HPOS disabled after cleanup	Orders inaccessible — legacy tables dropped	Admin shows zero orders; wp_wc_orders exists but woocommerce_hpos_enabled=off	Restore from pre-cleanup database backup only
Meta query fallback	WC_Order_Query with meta_query on _billing_email works but scans compatibility view	Slow query log shows full table scan on wp_postmeta view	Rewrite query to use billing_email argument (HPOS-native)

Rollback Window

Rollback is only possible before cleanup:

wp wc hpos revert
# Restores legacy posts, disables HPOS, keeps HPOS tables intact

After wp wc hpos cleanup, legacy shop_order rows are gone. Reverting requires full database restore. Document the cleanup timestamp and backup location in runbooks.

Design Change Triggers

Re-evaluate the architecture when:

1. Order volume > 100k and postmeta join p95 > 2s — HPOS is the designed fix.
2. Search by product SKU or customer email without full-text plugins — wp_wc_order_product_lookup.sku and wp_wc_orders.billing_email indexes serve this natively.
3. Multi-store import/export requiring stable order IDs — HPOS preserves id as primary key across migrations.
4. Subscriptions/Bookings/Memberships on older versions — Subscriptions <4.0, Bookings <2.0, Memberships <1.26 force compatibility mode, negating gains. Upgrade those extensions first.

Verification Checklist for Production Cutover

1. wp wc hpos status → all flags true except cleanup_completed.
2. EXPLAIN on top 3 reporting queries shows index seeks, not scans.
3. Load test checkout (10 concurrent) with WC_HPOS_COMPAT=0 — zero legacy meta writes in debug.log.
4. Scheduled Actions: wc_hpos_sync_compatibility_tables shows Complete, 0 pending.
5. Order count parity verified via SQL (see Migration Strategy).
6. Backup taken after migration, before cleanup.

Only then run wp wc hpos cleanup and disable WC_HPOS_COMPAT.