The postmeta vs custom tables call, decided on your numbers

Why does the postmeta vs custom tables debate keep failing you?#

Search the postmeta vs custom tables debate and every top result reads the same. First it declares a qualitative winner. Then it warns that a custom table is a one-way door. However, neither claim carries a single number. Because there is no number, the advice cannot tell you where your line sits. Therefore you leave with a verdict, not a decision.

The false binary also hides your real fear. In practice you are not afraid of postmeta or of custom tables. Instead you are afraid of picking wrong and being unable to undo it. Moreover, the one-way-door framing makes that fear worse. So this post refuses the framing. Instead of refereeing the debate, we are going to measure it.

What actually happens when meta_query stacks self-joins?#

To decide well, you need the mechanism, not a benchmark you must trust. wp_postmeta is an entity-attribute-value store. Each post owns many rows, one per meta key. Because the value lives in a LONGTEXT column, a filter on it scans and casts row by row. Therefore the cost is structural, and you can reason about it directly.

The EAV shape of wp_postmeta, in one schema#

Here is the table every plugin inherits. Notice the indexes it ships. There is one on post_id and one on meta_key. However, meta_value carries no index at all. Consequently a filter on a value has nothing to seek, so it reads and casts every candidate row.

-- The shape every plugin inherits: one tall, narrow table.
CREATE TABLE wp_postmeta (
  meta_id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  meta_key   VARCHAR(255)             DEFAULT NULL,
  meta_value LONGTEXT                 DEFAULT NULL,
  PRIMARY KEY (meta_id),
  KEY post_id  (post_id),
  KEY meta_key (meta_key(191))
);

-- Note what is NOT indexed: meta_value. A filter on a value scans and
-- casts LONGTEXT row by row, because the default schema never indexes it.

Because the value is unindexed by default, the shape rewards lookups by key and punishes filters by value. The core reference confirms this contract in the WordPress Metadata API documentation. In short, postmeta is a superb key-value bag and a poor filtered index. That single fact drives everything below.

Every AND clause is another self-join#

WP_Query turns each meta_query clause into its own join against wp_postmeta. Because the table joins to itself, three clauses mean three self-joins over the same fat table. As a result the planner probes the same rows again and again. The diagram traces one three-clause query as it fans out.

Notice that the fan-out is multiplicative, not additive. First each join filters candidate rows. Then the next join re-probes what survives. Consequently the rows the query must hold grow with every clause you stack. That growth is exactly what knees later.

Worked example: 2.1M rows, six meta keys, one report query#

Numbers make the shape concrete. Consider roughly 2.1 million products and about 17 million postmeta rows. A six-key filtered report becomes a six-way self-join. Compare its query plan against the same report on a typed, indexed table.

-- Filtered product report on wp_postmeta: 6 meta keys, relation AND.
-- ~2.1M products, ~17M postmeta rows. Each clause is one self-join.
EXPLAIN
SELECT p.ID
FROM wp_posts p
JOIN wp_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = 'price'
JOIN wp_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = 'color'
JOIN wp_postmeta m3 ON m3.post_id = p.ID AND m3.meta_key = 'in_stock'
JOIN wp_postmeta m4 ON m4.post_id = p.ID AND m4.meta_key = 'brand'
JOIN wp_postmeta m5 ON m5.post_id = p.ID AND m5.meta_key = 'rating'
JOIN wp_postmeta m6 ON m6.post_id = p.ID AND m6.meta_key = 'size'
WHERE p.post_type = 'product'
  AND CAST(m1.meta_value AS DECIMAL(10,2)) >= 20
  AND m2.meta_value = 'blue'
  AND m3.meta_value = '1'
  AND m4.meta_value = 'acme'
  AND m5.meta_value >= '4'
  AND m6.meta_value = 'L';

-- rows examined: ~11.4M    Using temporary; Using filesort
-- six lookups on (post_id, meta_key), then a value CAST per matched row.

-- The same report against a purpose-built, typed, indexed table.
-- One row per product, one composite index that covers the filter.
EXPLAIN
SELECT product_id
FROM wc_product_facts
WHERE price    >= 20.00
  AND color     = 'blue'
  AND in_stock  = 1
  AND brand     = 'acme'
  AND rating   >= 4
  AND size      = 'L';

-- rows examined: ~3,200    no temporary, no filesort
-- one composite index range scan, typed columns, zero self-joins.

The postmeta plan examines millions of rows, then adds a temporary table and a filesort. Meanwhile the custom-table plan reads a few thousand rows through one composite index. In short, the gap is not small, and it widens with every clause. These figures are illustrative, order-of-magnitude values for the behaviour, not a published benchmark. To read a plan like this properly, see our guide to diagnosing MySQL performance bottlenecks in production.

Where the curve knees#

The cost does not rise in a straight line. First it climbs gently while the rows a query touches still fit cache. Then it knees hard once they do not. Because the working set spills to disk, each extra clause now costs far more than the last. The chart stacks the same query at one, two, four, eight, and sixteen clauses.

The knee is the whole point. Below it, postmeta is fine and a custom table would be wasted work. Above it, the self-joins collapse and you need a different read path. Therefore the only question that matters is where your own knee sits. That is what the next section computes.

What does your access pattern cost? Compute it.#

A general curve is not your curve. So enter your own numbers and watch the model recompute. Set your row count, your clause count, whether the value is indexed, and your read share. Then read which rung the postmeta vs custom tables math puts you on. This is the instrument the whole post promised.

Notice how the knee moves. Because an index cuts the rows each join touches, toggling it can push the knee out past your clause count entirely. In contrast, a write-heavy pattern pulls the recommendation back down the ladder. This is illustrative planning math, not a quote, so measure your own EXPLAIN before you commit to any rung.

The reversibility ladder: four staged moves, none of them one-way#

The one-way-door fear only holds if you treat this as a single bet. Instead, stage it. Each rung below is a small, reversible move. Therefore you climb only until the latency fits your budget, then you stop. Because every rung reverses on its own, it is genuinely never too late to change your mind.

Read the ladder as an escalation, not a menu. First you try the cheapest rung. Then you measure again. Because you only climb when the numbers force you, most sites stop at rung one or two. In practice the top rungs are for the few access patterns that genuinely knee.

Rung 1: the composite index you probably skipped#

Most postmeta slowness is a missing index, not a missing table. Because meta_value ships unindexed, a value filter has nothing to seek. So add a composite index and let a single-key filter range-scan a short prefix. The column order matters: the equality column comes first, then the value.

-- Rung 1: the composite index most sites skip. Fully reversible.
-- Left-prefix order matters: the equality column first, then the value.
ALTER TABLE wp_postmeta
  ADD INDEX idx_key_value (meta_key(32), meta_value(32));

-- A single-key filter now seeks by meta_key and range-scans a short
-- value prefix, instead of scanning and casting the whole table.
-- To reverse: DROP INDEX idx_key_value ON wp_postmeta;

This is the highest-leverage, lowest-risk move on the ladder. First it is one statement. Second it reverses with a single DROP INDEX. Consequently you should exhaust rung one before you consider anything heavier. Many reports that felt like a schema problem were only ever a missing index.

Rung 2: a stored generated column for the hot key#

Sometimes one filter does all the damage. In that case promote just that value. A stored generated column lifts a single meta_value into a typed, indexable column, while postmeta stays the source of truth. Because the column is derived, it cannot drift from the data underneath it.

-- Rung 2: promote one hot key to a typed, stored generated column.
-- Still postmeta semantics; the column is derived, not a second source.
ALTER TABLE wp_postmeta
  ADD COLUMN price_dec DECIMAL(10,2)
    GENERATED ALWAYS AS (
      CASE WHEN meta_key = 'price'
           THEN CAST(meta_value AS DECIMAL(10,2)) END
    ) STORED,
  ADD INDEX idx_price_dec (price_dec);

-- The hot price filter now hits a typed, indexed column.
-- To reverse: DROP the index and the generated column.

Now the hot filter hits a typed, indexed column instead of a LONGTEXT cast. MySQL documents the exact semantics in its generated columns reference. Moreover the move is reversible: drop the index and the column and you are back to plain postmeta. In short, rung two buys one indexed dimension without a migration.

Rung 3: a hybrid lookup table that shadows postmeta#

When several clauses knee together, one column is not enough. So build a narrow shadow table for just the keys you filter on. Because postmeta remains the source of truth, the shadow is a read accelerator, not a second database. This is the staged middle move most posts pretend does not exist.

-- Rung 3: a narrow shadow table for just the keys you filter on.
-- postmeta stays the source of truth; this is a read accelerator.
CREATE TABLE wp_product_facts (
  post_id  BIGINT UNSIGNED NOT NULL,
  price    DECIMAL(10,2),
  color    VARCHAR(32),
  in_stock TINYINT(1),
  PRIMARY KEY (post_id),
  KEY idx_filter (price, color, in_stock)
);

-- Keep it in sync on the save_post and updated_post_meta hooks, or
-- rebuild it in a batch. Drop the table to reverse; postmeta is intact.

Keep the shadow in sync on the save_post and updated_post_meta hooks, or rebuild it in a batch job. Because the write path touches one extra row, a read-heavy pattern absorbs the cost easily. However a write-heavy pattern pays for it on every save, which is exactly why the calculator pulls write-heavy patterns back a rung. To reverse, drop the table and postmeta is untouched.

Rung 4: a full custom table behind a get_post_meta shim#

This is the endpoint the debate frames as the whole choice. Yet it is only the top rung, reached when reads dominate and the self-joins have already collapsed. The trick that makes it reversible is the shim. Because a get_post_metadata filter intercepts the read, the custom table can own the data while every caller keeps calling get_post_meta.

// Rung 4: a full custom table read through a get_post_meta shim.
// The custom table owns the data; the shim keeps callers unchanged.
add_filter('get_post_metadata', function ($value, $post_id, $meta_key) {
    static $columns = ['price', 'color', 'in_stock', 'brand'];

    if (!in_array($meta_key, $columns, true)) {
        return $value; // fall through to core postmeta for everything else
    }

    $row = ProductFacts::forPost($post_id); // one indexed custom-table read
    return [$row->$meta_key];               // shaped like get_post_meta output
});

// Callers still call get_post_meta($id, 'price', true). Nothing downstream
// knows the data moved. Remove the filter to fall back to postmeta.

Nothing downstream knows the data moved. First the filter checks whether the key lives in the custom table. Then it either reads one indexed row or falls through to core postmeta. The wpdb class reference covers the direct queries the table backing this uses. Remove the filter and you fall straight back to postmeta, so even the top rung is not a one-way door.

WooCommerce HPOS: they measured, moved, and kept a shim#

This is not a theory. WooCommerce hit the exact wall this post describes. Order search grew slow because orders lived in posts and postmeta, so every filter fanned out into meta joins. Therefore they built High-Performance Order Storage, a set of dedicated, indexed order tables. Crucially, they did not slam a one-way door.

Read the last two rows as the lesson. First HPOS ships a synchronization mode that keeps the old post tables current. Second it keeps a compatibility layer, so plugins reading order meta the old way still work. The HPOS developer documentation details the migration and the fallback. In short, the biggest custom-table move in the ecosystem was measured, staged, and reversible.

When a custom table is the wrong call#

There is one more case to name. Sometimes the real problem is not the schema at all. If a single instance is saturated across the whole application, no index or side table rescues it. Instead you need read replicas, caching, or a different data store. This ladder tunes one access pattern. It does not resize an under-provisioned database.

Making the postmeta vs custom tables call before it is too late#

The debate hands you a verdict. This post hands you an instrument. First compute where your own knee sits. Then climb the ladder only as far as the latency forces you. Because every rung reverses on its own, the phrase "before it is too late" is a false alarm. It is never too late when the move is staged and reversible.

If you are shaping a busy WordPress backend, the same instinct applies to structuring a WordPress backend at scale, where cross-plugin data collides in one shared namespace. For the read path underneath it, our guide to diagnosing MySQL performance bottlenecks in production shows how to read the plan the calculator estimates. Measure first, then move one rung at a time.