postmeta vs. custom tables: making the call before it’s too late

<?php
$query = new WP_Query([
    'post_type'   => 'listing',
    'posts_per_page' => 20,
    'meta_query'  => [
        'relation' => 'AND',
        [
            'key'     => 'price',
            'value'   => [200000, 450000],
            'type'    => 'NUMERIC',
            'compare' => 'BETWEEN',
        ],
        [
            'key'     => 'bedrooms',
            'value'   => 3,
            'type'    => 'NUMERIC',
            'compare' => '>=',
        ],
        [
            'key'     => 'city',
            'value'   => 'Austin',
            'compare' => '=',
        ],
    ],
]); ?>

<?php global $wpdb;
$ids = $wpdb->get_col($wpdb->prepare("
    SELECT post_id
    FROM {$wpdb->prefix}listing_details
    WHERE city = %s
      AND bedrooms >= %d
      AND price BETWEEN %d AND %d
    ORDER BY price ASC
    LIMIT 20
", 'Austin', 3, 200000, 450000)); ?>

<?php 
function my_plugin_create_listing_details_table(): void
{
    global $wpdb;
    $table   = $wpdb->prefix . 'listing_details';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE {$table} (
        post_id BIGINT UNSIGNED NOT NULL,
        city VARCHAR(100) NULL,
        bedrooms SMALLINT UNSIGNED NULL,
        price DECIMAL(12,2) NULL,
        PRIMARY KEY (post_id),
        KEY city_bedrooms_price (city, bedrooms, price)
    ) {$charset}";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}
add_action('plugins_loaded', 'my_plugin_create_listing_details_table'); ?>

<?php
function my_plugin_sync_listing_details(int $post_id): void
{
    if (get_post_type($post_id) !== 'listing') {
        return;
    }
    global $wpdb;
    // Existing postmeta fields are still written exactly as before —
    // this just mirrors the same values into the new table.
    $wpdb->replace(
        $wpdb->prefix . 'listing_details',
        [
            'post_id'  => $post_id,
            'city'     => get_post_meta($post_id, 'city', true),
            'bedrooms' => (int) get_post_meta($post_id, 'bedrooms', true),
            'price'    => (float) get_post_meta($post_id, 'price', true),
        ],
        ['%d', '%s', '%d', '%f']
    );
}
add_action('save_post_listing', 'my_plugin_sync_listing_details', 20); ?>

<?php
WP_CLI::add_command('listings validate', function() {
    global $wpdb;
    // How many listings have a 'city' postmeta entry?
    $meta_count = (int) $wpdb->get_var("
        SELECT COUNT(DISTINCT post_id)
        FROM {$wpdb->postmeta}
        WHERE meta_key = 'city'
          AND meta_value != ''
    ");
    // How many rows are in the new table?
    $table_count = (int) $wpdb->get_var("
        SELECT COUNT(*) FROM {$wpdb->prefix}listing_details
    ");
    WP_CLI::log("postmeta entries : {$meta_count}");
    WP_CLI::log("custom table rows: {$table_count}");
    if ($meta_count === $table_count) {
        WP_CLI::success("Counts match. Safe to switch reads.");
    } else {
        $diff = $meta_count - $table_count;
        WP_CLI::warning("Mismatch: {$diff} rows still need migrating. Re-run backfill.");
    }
}); ?>

<?php
class Listing_Query
{
    /**
     * Returns filtered listing IDs.
     * Reads from the custom table when the migration flag is on,
     * falls back to meta_query when it's off.
     */
    public function getIds(string $city, int $minBeds, float $minPrice, float $maxPrice): array
    {
        if (get_option('my_plugin_use_custom_table', false)) {
            return $this->queryCustomTable($city, $minBeds, $minPrice, $maxPrice);
        }
        return $this->queryPostmeta($city, $minBeds, $minPrice, $maxPrice);
    }
    private function queryCustomTable(string $city, int $beds, float $min, float $max): array
    {
        global $wpdb;
        return $wpdb->get_col($wpdb->prepare("
            SELECT l.post_id
            FROM {$wpdb->prefix}listing_details l
            INNER JOIN {$wpdb->posts} p ON p.ID = l.post_id
            WHERE l.city      = %s
              AND l.bedrooms  >= %d
              AND l.price     BETWEEN %f AND %f
              AND p.post_status = 'publish'
              AND p.post_type   = 'listing'
            ORDER BY l.price ASC
        ", $city, $beds, $min, $max));
    }
    private function queryPostmeta(string $city, int $beds, float $min, float $max): array
    {
        $query = new WP_Query([
            'post_type'      => 'listing',
            'fields'         => 'ids',
            'posts_per_page' => -1,
            'meta_query'     => [
                'relation' => 'AND',
                ['key' => 'city',     'value' => $city],
                ['key' => 'bedrooms', 'value' => $beds, 'type' => 'NUMERIC', 'compare' => '>='],
                ['key' => 'price',    'value' => [$min, $max], 'type' => 'NUMERIC', 'compare' => 'BETWEEN'],
            ],
        ]);
        return $query->posts;
    }
} ?>

# Enable — reads now come from the custom table
wp option update my_plugin_use_custom_table 1
# Something wrong? Roll back instantly — no code deploy needed
wp option update my_plugin_use_custom_table 0

<?php
// Remove the dual-write hook — add this once you're confident
// the custom table is the sole source of truth.
remove_action('save_post_listing', 'my_plugin_sync_listing_details', 20);
// Optionally clean up the now-redundant postmeta rows in a
// batched WP-CLI command — never in a page-load hook.
WP_CLI::add_command('listings cleanup-postmeta', function() {
    global $wpdb;
    $keys = ['city', 'bedrooms', 'price'];
    foreach ($keys as $key) {
        $deleted = $wpdb->delete(
            $wpdb->postmeta,
            ['meta_key' => $key],
            ['%s']
        );
        WP_CLI::log("Deleted {$deleted} postmeta rows for key '{$key}'");
    }
    WP_CLI::success("Postmeta cleanup complete.");
}); 
?>

-- Check what MySQL is actually doing with your meta_query
EXPLAIN
SELECT p.ID
FROM wp_posts p
INNER JOIN wp_postmeta city_meta
    ON city_meta.post_id = p.ID
    AND city_meta.meta_key = 'city'
    AND city_meta.meta_value = 'Austin'
INNER JOIN wp_postmeta beds_meta
    ON beds_meta.post_id = p.ID
    AND beds_meta.meta_key = 'bedrooms'
    AND beds_meta.meta_value >= '3'
INNER JOIN wp_postmeta price_meta
    ON price_meta.post_id = p.ID
    AND price_meta.meta_key = 'price'
    AND price_meta.meta_value BETWEEN '200000' AND '450000'
WHERE p.post_type   = 'listing'
  AND p.post_status = 'publish';
-- Red flags in the output:
-- type: ALL or type: ref with large 'rows' estimate
-- Extra: Using filesort, Using temporary
-- key: NULL on any of the postmeta joins

Column type decisions and why they matter

<?php
dbDelta("CREATE TABLE {$table} (
    post_id  BIGINT UNSIGNED NOT NULL,
    city     VARCHAR(100)    NULL,
    state    VARCHAR(100)    NULL,
    bedrooms SMALLINT UNSIGNED NULL,
    price    DECIMAL(12,2)   NULL,
    lat      DECIMAL(10,7)   NULL,
    lng      DECIMAL(10,7)   NULL,
    PRIMARY KEY (post_id),
    -- Composite index for the most common filter combination
    KEY city_beds_price (city, bedrooms, price),
    -- State-level archive pages
    KEY state_price (state, price),
    -- Geo bounding-box pre-filter before distance calculation
    KEY lat_lng (lat, lng)
) {$charset}");
?>