When developing plugins that requires data storage, what's the pros and cons of using one method or another ?

The explanation given in the codex is not detailed:

Before jumping in with a whole new table, however, consider if storing your plugin's data in WordPress' Post Meta (a.k.a. Custom Fields) would work. Post Meta is the preferred method; use it when possible/practical.

  • FYI: MB Custom Table is a plugin that can store meta data to custom tables instead of WP's post meta table. – Anh Tran Dec 13 '17 at 10:17
up vote 26 down vote accepted

Well, if I take the hat of a WP script kiddie, my answer would be: use post_meta, always.

However, I happen to know a thing or two about databases, so my answer is: never, ever, ever, use an EAV (aka the post_meta table) to store data that you might to need to query.

On the index front, there are basically none worth using in meta tables. So, if you're storing data type XYZ and are hoping you query all posts that have XYZ with a value of 'abc', well... good luck. (See all of the users/roles/caps related tickets in the WP trac to give you an idea of how gory it can get.)

On the join front, you quickly crash into the limit at which the optimizer decides to use a generic algorithm instead of analyzing the query when there are multiple join criteria.

Thus, no, no, no, no. Don't ever, ever, ever use a meta. Unless what's you're storing is cosmetic and will never be part of a query criteria.

It breaks down to your app. If you're storing, say, the birthdate of a movie director, than big deal. Use a meta all you want. But if you're storing, say, the release date of a movie, you'd be nuts to not use a separate table (or adding columns to the posts table) and add an index to that column.

  • 1
    Yes, the plugins that i'm developing are handling custom data like events, news, press releases, Job offers ... From outside "WordPress World", using tables is not really an option. But the advice from the WordPress Codex, is a little bit confusing. How can serialized chunks of data be preferred to normalized/structured/indexed data? – Nassif Bourguig Dec 3 '10 at 21:35
  • 1
    If you ask the average WP dev, he'll likely answer "use a meta" or "use a taxonomy". And I agree, up to the point where you need to query against it. If so, and I believe it is your case, my only answer is, add the fields to the posts table, or create a separate table entirely. Else you're in for enormous performance issues when it comes to querying and, even more importantly for lists of nodes, top-n sorting. – Denis de Bernardy Dec 3 '10 at 21:38
  • 1
    Denis would you be able to elaborate on this a little more, I find it very informative but would love some more data, has anyone done tests?, what exactly are the major drawbacks and limitations, thanks. – Wyck Dec 4 '10 at 6:49
  • 5
    @Denis - Quite the passionate advocacy against postmeta, eh? You know you are going firmly against the orthodoxy and you'll fall out of the good graces of the high priests of church of code poetry if you persist in such talk, don't you? :-) But seriously don't you think you overstate just a bit? It really depends on if there are going to be tens of thousands of meta records or not. In many cases there simply are not enough records to worry about. One complex site I'm deploying has around 10,000 meta records with few new records planned, and it's fine (fyi, it's not a blog.) – MikeSchinkel Dec 4 '10 at 8:52
  • 1
    @Denis - Thanks for the comments. And don't get me wrong, I probably lean a lot more towards your perspective but the combination of 1.) an hour-long debate with Matt at WordCamp Birmingham over the merits of Pods-like fields and 2.) the simplicity of meta has be resign to focus my attentions on other issues I could potentially change. At WCB I came away realizing as long as Matt is in charge it won't change because (my guess is) Matt is so enamored with idea of fewer tables he won't allow himself to recognize the down sides of indexing on a 768 byte key. <sigh> – MikeSchinkel Dec 4 '10 at 9:39

If your plugin is going to have A LOT of data, then using the wp_postmeta is NOT a good idea as demonstrated below:

Taking Woocommerce as an example,

In a store with ~30,000 products, say there will be an average of ~40 post meta (attributes and everything) per product, 5 product images per product, which means there will be ~4 image meta for each image:

30,000 products x 40 meta each = 1,200,000 rows in wp_postmeta

+

30,000 products x 5 images each x 4 image meta for each = 600,000 rows in wp_postmeta

So with merely 30,000 products you are looking at having 1,800,000 rows in wp_postmeta.

If you add more properties to your products or your product images, this number will multiply.

The problem with that is twofold:

  • Self joins are very expensive with MySQL
  • wp_postmeta table is not indexed unless you are using later mysql versions (ie no FULLTEXT index for meta_value)

To give an example from an actual case:

SELECT meta_value FROM wp_postmeta WHERE meta_key LIKE '_shipping_city'

query, which selects shipping city from all order details comes at a whopping ~3 seconds on an entry level dedicated server even if there are 5-10 orders. This is because the query is run from among a wp_postmeta table which has ~3 million rows in live installation.

Even the home page comes quite slow, because the theme pulls various elements from wp_postmeta - sliders, a few review inserts, a few other meta. In general product listing is very slow, searches are similarly slow when listing products.

You cannot fix this via any normal means. You may put Elastic Search in your server and use an Elastic Search plugin in Wordpress, you may use redis/memcached, you may use a good page cache plugin, but in the end fundamental issue will remain - fetching any amount of data from a bloated wp_postmeta table will be slow, whenever it is done. On the server where i tested the solution i implemented below, all of these were installed and configured properly and optimized, and site worked agreeably ok for non logged in users or commonly done queries since caching plugins kicked in.

But the moment a logged in user tried to do something that was not commonly done or the crons, caching plugins or any other utility wanted to fetch actual data from db to cache it or do anything else, things went pig slow.

So i tried something else:

I coded a small plugin to take all product meta (postmeta for post type product) to a custom table generated by code. This plugin took all meta for each post and created a table by adding each meta as columns and inserting the values into each row. So, i turned the EAV format into a horizontal, flat relational format. I also had the plugin to delete postmeta from all moved products from the wp_postmeta table.

While im at it, i also moved attachment postmeta and all other post type's meta to their own tables.

Then i hooked into get_(post_type)_meta filter to override retrieval of metadata to serve them from new custom tables.

Now the same query from earlier, which took ~ 3 seconds to fetch from wp_postmeta takes ~0.006 seconds. The site now behaves as if it was a fresh WP installation.

....................

Naturally, doing things the Wordpress way is better. It is actually the norm.

However, it is also obvious knowledge that EAV table is very inefficient in scaling. It is infinitely flexible and lets you store any data, but the price you pay for that, is performance. Its a fundamental trade off.

In that context, its difficult to tell someone who is intending to have a heap ton of data and - god forbid - query/search on that data to use wp_postmeta table for sure. The performance hit will be great.

Using your custom tables will allow your data to pile up and still remain fast enough.

Just like how Pippin Williams, the creator of Easy Digital Downloads plugin mentioned he would use custom tables if he was just starting coding his plugin, if you are going to create something that will be used for long time or pile up a lot of data, its more efficient to use your custom tables if you design them well.

You must make sure that any other plugin/addon developer has means to hook into your plugin to manipulate your data before and after retrieval of the data. If you do that, then you are pretty solid.

  • 1
    Interesting stuff! One thing to clarify is that the mentioned "get_(post_type)_meta" filter is actually called "get_(meta-type)_metadata", where meta-type is either post, comment or user. So get_post_meta() will go through the get_post_metadata filter, regardless of the post type. The return value of the filter is what you want the final meta value to be. – Berend Feb 27 '17 at 6:03
  • get_(meta-type)_metadata -> indeed it works with all post types, and indeed the final function which is visited is get_post_metadata. However the filter works when you use it nevertheless. – unity100 Feb 27 '17 at 19:38

It depends on what you're doing. The WP way is to use the existing tables, as they've been designed to be flexible enough, however occasionally you'll reach a new class of data that can't be placed in an existing table, e.g. if you wanted category meta data, you could choose to create a wp_termsmeta table.

However, usually you can store your data quite comfortably in the different tables that exist, and where you store your data depends on what your plugin does.

  • For general plugin settings, use the get_option() API call - this will be cached also.
  • For plugin settings that a particular to an individual post, then use the custom meta data per post with get_post_meta(). This is usually plenty for what you need.

Caching is implemented within WordPress to speed up your response time also.

agreed with denis 100%. But there is a way around it.

The problem with using the post meta for values to be querried is when the values are array's etc. Such as this:

array(
'key1' => 'val 1',
'key2' => 'val 2'
);

This gets stored in the db as a serialised string, which will look something like this:

{array["key1"]...{}...}

So when you want to query all posts with array['key2'] = 'val 2' then wp has to pull every meta entry called array, unpack it, then test it, then go to the next. This will definately bring down your server if your site is successfull and has lots of posts, pages, custom posts etc.

The solution is project depending, and you'll see why. If you were to store the data as a var = val then wp will be able to search without having php to unpack every single test. To do this in the scenario above you would use some namespacing and store the meta keys:

_array_key1 = 'val 1';
_array_key2 = 'val 2';

then wp looking for key 2 with val 2 will be able to pull it straight away. This is project depending though. My current project relies on about 20 different dataTypes to be store with each custom post so the above would just create a massive table to search, seeing as how we are expecting 100's of thousands of posts. So in that scenario a custom table is the only way.

Hope this helps someone

For my FarmVille site :) I did both but never finished it because I sold it:

  1. I read the farmville xml and dumped the data in a custom table
  2. In WordPress I had custom fields automatically made for every field in that table (and some extra)
  3. Now worry on what happens if a value changes in either the table or on the other side: the custom field since they need to be continuously in sync

I did this because I wanted on the one hand have users edit the wordpress site by entering new farmville data e.g. "a cow costs 10 coins" BUT from the integration side: IF a change in the xml ment the cow now costs "20 coins" (via the front-end editing plugin) that would be given as option after it: so that either the XML OR the user was right (sort of wiki system).

So here is an example when using both.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.