Can I split a huge wp_postmeta table across different databases or servers?

WordPress Development Asked by Asifur Rahman on January 2, 2022

This question doesn’t have any codes, it’s more of a theoretical question. I need guidance on what method to look for. I’ve searched on google before but found nothing on this specific case.

I have 2 servers(CentOS) where I have MySQL database set up. In one server(server 1) I have WordPress installation(LAMP) with the database set up. The site is working. However, I want to split the database that is currently on server 1 and host it partially on server 2. Now, I know how to use a remote database in WordPress’ wp-config.php file but here I want to use two databases.

In this case, I can try a manual query from the second server. However the reason I want to split the databases because of a single table that includes more than a million entries. Because the table is also going to be split, and the table is wordpress’ default post meta table, and I wouldn’t know which posts are going to be in which database(more than million entries!), I can’t use a custom query. Also I know that default $wbdp only pulls data from 1 database.

To summarize this question, is there any way to use multiple WordPress Database without having a custom database query?

One Answer

No. This is not practically possible primarily because there is no single interface to this table where you could implement the functionality you need, and even if you could, it would introduce some extremely gnarly problems.

Why this is more or less impossible

What you're asking can be simplified to a question about if you could easily shard the wp_postmeta table in Wordpress. (This is relevant because if you could shard a table, it would be a much smaller step to the shards being on multiple databases / servers).

In sharding, some code has to be able to figure out where to look for the result according to some simple algorithm, and then be able to query only that place. For example, if you had 10 million people in a database, you could put them in different tables or different databases split by the first letter of their name*. This means every time the function to get all the information about a person ran, it would first find the right table according to the first letter of their name*.

The reason this is impossible is that there is no single interface to the posts and/or postsmeta table that would allow you to put this code in one place. To achieve what you want, every single place that touched the postmeta table, or did a join to the postmeta table would need altering to figure out which of your two places to look, according to some deterministic algorithm. You'd also have some extreme gnarly problems about figuring out joins (e.g. join across databases/servers is probably possible but sounds like it would quickly defeat any performance gain).

What are you really trying to do?

It sounds like you've got a problem and you think this is the solution, but this is almost definitely not a good solution to whatever your problem is (or any problem in a single Wordpress installation). Probably there's a simpler easier solution, or you've got some bad advice/information somewhere.

Why is it a problem that there's 1M rows? Are you just trying to optimise queries to run faster? Are you running out of disk space somewhere? Do you have some assumptions/limitations you didn't add to your question?

[*] Note you'd probably try to shard by something that created roughly equally sized pieces, and you might create some kind of index, but this is just an example, read the Wikipedia page for more info.

Answered by mozboz on January 2, 2022

Add your own answers!

Related Questions

CREATE TABLE with dbDelta does not create table

1  Asked on October 30, 2021 by fcd


how to add shortcode widget at end of main blog page

1  Asked on October 30, 2021 by shubhamk


Placing background image over section divider

1  Asked on October 30, 2021 by user191925


jQuery Click event is not fired at all

1  Asked on October 30, 2021 by islam-hanafi


Make the product page as homepage WooCommerce

0  Asked on October 30, 2021 by gabriel-borges


How to be sure to get last page version without ANY cache?

2  Asked on October 30, 2021 by j-bizmai


How to grab posts in Gutenberg Block?

1  Asked on October 30, 2021 by stephen-sabatini


Custom post type permalink returns bad url

1  Asked on October 30, 2021 by ncti


Menus don’t appear in admin area / WordPress Multisite

0  Asked on October 30, 2021 by user191642


Client side HTTP parameter pollution (reflected)

0  Asked on October 30, 2021 by divided


WP Ajax Function Always Returning undefined

1  Asked on October 30, 2021 by brian-bruman


Group Posts by Category in Monthly Archive

2  Asked on October 30, 2021 by zero-goki


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP