get_post_meta() on post with large amount of meta keys uses too much memory

[joehoyle]

I think it's likely there has been a ticket on this in the past, but I couldn't find one so here goes!

If I have a post that have say 25k post meta entries, and I call get_post_meta( $id, 'foo', true ) then update_meta_cache is going to cause all post metas to be loaded into memory.

In most cases I can see this makes sense, however I recently had an issue where I was storing contest votes as meta keys, a post had 30k votes, and would take about 70MB of memory to get any meta key of that post (even if I didn't want votes). This is fairly edgecase, but the idea that all meta for a post is always loaded even if you don't want it is perhaps over presumptuous as a dev may be storing a large amount of data in meta.

With persistant object caching this is somewhat worse, as the object cache store gets very large.

I am not proposing this functionality is removed, maybe just a filter or something in ​http://core.trac.wordpress.org/browser/trunk/wp-includes/meta.php#L524 so one could either adjust the query or perhaps pass an array of meta keys to exclude from the sql query

[wonderboymusic]

get_post_meta is a bad choice here, you should probably be using a custom SQL SELECT using COUNT and GROUP BY. Votes and ratings aren't what those EAV tables are designed to store. I would suggest creating a join table

[joehoyle]

Replying to wonderboymusic:

get_post_meta is a bad choice here, you should probably be using a custom SQL SELECT using COUNT and GROUP BY. Votes and ratings aren't what those EAV tables are designed to store. I would suggest creating a join table

Hey, could you explain why the meta tables are not meant for something like vote tracking on a post? A vote seems to fit the scheme quite well, post | vote | vote_info and doing a count for total votes?

How would you see an ideal scheme for that?

Also, the voting was an example of large amounts of data -- of course it's not limited to that. With update_meta_cache it's not viable to put any amount of data you don't want permanently in memory, as of course it's not garbage collection due to the object cache.

[scribu]

There have been similar requests for user meta: ​http://core.trac.wordpress.org/ticket/15458#comment:1

[chriscct7]

post meta is designed to store a few unique pieces of information about a post, particularly that which is used in querying or used often (thus why all meta keys are cached). Instead, votes should be in their own table, and then you could store, for example, the name of the table as the post meta for that post to link the votes table to the post. Suggesting close as invalid

[joehoyle]

I'm happy for this to be closed as invalid, this is somewhat of an edge case also. If anyone disagrees, feel free to voice it!

[xParham]

I am running into a similar issue with only 10 meta entries per post where one of the entries is ~2-3MB large. Requesting any single one of the meta entries results in pulling in all meta entries which is not desired for example on a post listing page.

Also in my case, using memcached with the default max object size of 1MB, update_meta_cache() failed to cache the meta entries and then accessing individual meta entries using the magic method of the post object was making a new DB query for every single meta access, 300 queries for accessing 10 meta entries for 30 posts.

I think it would be great if we can implement a mechanism to exclude some meta keys from the query in update_meta_cache().

Maybe the update_post_meta_cache parameter of WP_Query can accept arrays of meta keys to include or exclude, which will then passed to update_meta_cache()? Or maybe as @joehoyle originally suggested, a filter in update_meta_cache() so one can adjust the query?

[dd32]

one of the entries is ~2-3MB large.

In that case, I'd be looking at alternate storage methods, WordPress just isn't designed to have meta_values which are that large.
Without knowing what the content of the field is, you're probably going to need to look at using a custom table or filesystem storage for blobs that size in all honesty.