When wp_postmeta Stops Being the Right Place for Plugin Data

A shop with about 30,000 published products added a plugin that records a delivery estimate against every variation it can price. Three months later the same plugin's admin report times out at 30 seconds. Nothing in the report is complicated: it groups products by the carrier the estimate came from and counts them.

The plugin author had used update_post_meta(), which is the obvious choice when you are writing a plugin. It fits the tutorial you followed, it works on a fresh install, and it costs nothing to prototype. What it does not do is answer a question that starts with WHERE.

I have watched this exact migration get attempted for the wrong reasons, too. Teams move order notes into a bespoke table because postmeta feels cheap and legacy, then discover they have taken on schema versioning, multisite activation and backup growth in exchange for a query that was never slow. The two mistakes look symmetric from the outside and they are not. So here is the line I use.

wp_postmeta is a general-purpose EAV table, and that is the whole problem

A metadata table stores one property per row. A post with a title, a price and a SKU is one row in wp_posts and three rows in wp_postmeta, each carrying post_id, meta_key, meta_value and nothing else of substance. WordPress core uses this for post fields that did not earn a column: the edit-last timestamp, the page template, the thumbnail id, the page template, the menu order.

That shape is genuinely good at four things, and you should not move data out of it when all four are true:

• The data is sparse. Most objects do not have the key, and a null column would be wasted space.
• The data belongs to one object. You look it up by post_id and you are done.
• The key set is arbitrary or plugin-defined. Core does not need to know your key names in advance.
• Core already joins the table for you. wp_query, the REST API and the editor all reach postmeta without extra work from you.

A page template, a featured flag, a serialised settings blob scoped to one page: postmeta. That is what it is for, and a custom table there buys you nothing but obligations.

Now invert one of the four conditions. If the data is written thousands of times a day, or if you need to find objects by value rather than by object, the table is the wrong container and no amount of indexing discipline fixes it. EAV tables make the common case cheap and the analytical case expensive, and postmeta is no exception. That is the "long, narrow table" complaint in one sentence: three columns, nearly all of them noise, and a row count that scales with attributes multiplied by objects.

post_id and meta_key live in separate indexes, and meta_value lives in none

On a stock WordPress 6.6 install the relevant part of SHOW CREATE TABLE wp_postmeta looks like this:

PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key(191))

Two narrow secondary indexes, each on one column, and the value column is not in either of them. The reasons are historical and sensible. meta_value is a LONGTEXT, and an index needs a prefix length to be useful on a text column, which makes the index enormous and still useless for the comparisons people actually write.

What that means in practice depends entirely on which direction your query travels. Give the optimiser a post_id and it does a fast index lookup, then a handful of row reads. Give it a meta_key and it does the same on the key index, which is why WP_Query with a simple key-only meta clause stays quick even on a large table.

Now ask for a sort. This is the shape that ends up in the wild:

$query = new WP_Query( array(
    'post_type'      => 'product',
    'posts_per_page' => 24,
    'meta_key'       => '_delivery_estimate_days',
    'orderby'        => 'meta_value_num',
    'order'          => 'ASC',
    'meta_query'     => array(
        array(
            'key'     => '_delivery_estimate_days',
            'value'   => 10,
            'compare' => '<=',
            'type'    => 'NUMERIC',
        ),
    ),
) );

The key index can find the rows where that key exists. It cannot put them in order, because the index is on meta_key text, not on the value, and MySQL has no way to walk rows in value order from that index. So it matches every row with that key, reads the join back to wp_posts for the status and type filters, materialises the result, and sorts it in a temporary table before applying LIMIT 24. The sort is where the 30 seconds go. A filesort over a few hundred thousand matched rows, on a table that is still being written to during the sort, on a shared host, is exactly as slow as it sounds.

The table that quietly becomes the largest one you own

Postmeta does not grow because any single row is large. It grows because row counts multiply, and several ordinary plugin behaviours multiply them hard.

The same property that makes the table slow to sort also makes it slow to fill. One logical object, one logical attribute, one row. A product with a carrier, a handling time, a cutoff hour and a fallback rate has four rows, and a variable product with twenty variations has eighty. Nothing in that is unusual or badly written; it is the shape of the model doing what the model does.

Revision copies. Every autosave that creates a revision copies the post's meta to the revision row, so a page edited forty times can carry forty sets of the same keys. Third-party keys that are serialised arrays, where one write is one row but the payload is a product graph. Orphaned rows left behind by plugins that called delete_post_meta() for the keys they knew about and forgot the rest. WooCommerce's own footprint is the largest single contributor on most stores: orders, order items and products all carry metadata, and on a high-volume shop with HPOS disabled the postmeta table can pass a gigabyte while every other table sits in the hundreds of megabytes.

There is a second-order cost that rarely gets mentioned. Deleting rows does not shrink the InnoDB tablespace in any way you can rely on; space is reused for later inserts inside the same file. So a table that grows and shrinks continuously settles at its high-water mark, and meta_id keeps climbing because the auto-increment counter never resets. You feel that in backup size and restore time long before you feel it in queries.

So the maintenance question is not "is postmeta big". It is "which writer is adding rows fastest, and does anybody query what it writes".

A custom table only pays when the query shape earns it

Two query shapes justify leaving postmeta. The first is a lookup on a value with high cardinality: selecting or joining rows by a column that has thousands of distinct values. The second is aggregation, where you group or sum rather than fetch. Neither is well served by an EAV table, and both are ordinary SELECT problems once the data has real columns.

Here is the delivery-estimate plugin rewritten as a table. Note the covering index: it carries the three columns the reporting query filters and groups on, in the order it uses them, so the count can be answered from the index alone.

function wpsi_install_estimates_table() {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $table   = $wpdb->prefix . 'delivery_estimates';
    $collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        product_id bigint(20) unsigned NOT NULL,
        variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
        carrier varchar(32) NOT NULL,
        estimate_days smallint(5) unsigned NOT NULL,
        estimated_at datetime NOT NULL,
        PRIMARY KEY  (id),
        KEY carrier_days (carrier, estimate_days),
        KEY product_lookup (product_id, variation_id)
    ) {$collate};";

    dbDelta( $sql );
}

Reads become a prepared statement against a real column, and the database can use carrier_days to group without touching the table:

$table = $wpdb->prefix . 'delivery_estimates';

$rows = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT carrier, COUNT(*) AS total, MIN(estimate_days) AS fastest
         FROM {$table}
         WHERE estimated_at >= %s
         GROUP BY carrier",
        gmdate( 'Y-m-d H:i:s', strtotime( '-30 days' ) )
    )
);

The trade is legible in one table. Read it as a set of costs, not as a winner.

That HPOS cell deserves a plain sentence. WooCommerce's High-Performance Order Storage moves orders out of wp_posts and into its own tables, so on an HPOS-enabled store a raw query that joins wp_postmeta to wp_posts by shop_order returns nothing useful. If you keep order-related values in postmeta and want to survive that migration, you have to read and write them through the order object or the CRUD layer rather than with get_post_meta(). A custom table sidesteps the question entirely, at the price of no longer appearing in any WooCommerce admin screen or export without extra code.

The limit is ownership, and it is not a small word

A custom table does not have a maintainer. Core maintains postmeta: it ships the schema, it upgrades it, it handles multisite, and it will still be there in five years. The moment you write CREATE TABLE, every one of those responsibilities becomes yours.

Schema changes need a version option and a comparison against dbDelta() on an upgrade hook, because your table will need a column you did not think of. Uninstall needs explicit cleanup, and the correct behaviour depends on the plugin: dropping the table on deactivate will eventually destroy somebody's data after a botched update, so most teams drop on uninstall only and leave the rows on deactivate. Multisite needs its own loop. Registering a table on activation of a network-activated plugin creates it for the current site, not for the network, so you run the installer once per site with get_sites() and switch_to_blog(), and you handle sites created after activation with a wp_initialize_site callback.

The other cost is invisible until you look for it: nothing else in WordPress knows your table exists. No REST endpoint, no revision copy, no export, no trash, no post-delete cascade. You write each of those, and you write them again when somebody asks why deleted products still have estimates.

What to do on Monday

1. List the meta keys your plugin writes, with a row count and a write rate for each. Two numbers, one line per key.
2. For every key, write down the query that reads it. If every read starts from a known post id, leave it in postmeta and stop reading this article.
3. If any read sorts or groups on the value, capture the EXPLAIN output before you change anything. A filesort line in that output is your justification.
4. Move one key at a time, behind a version option, and backfill with a batched INSERT ... SELECT rather than a loop over update_post_meta().
5. Keep the read path behind a single accessor function so the storage choice stays a decision you can reverse.
6. Write the uninstall routine in the same commit as the CREATE TABLE. If it is not in that commit, it is not going to be written.