Add support for adding metadata in bulk

[johnbillion]

Related: #34848

This ticket aims to introduce functions for bulk inserting metadata in a manner that's more performant than calling the add_*_meta() functions multiple times per object. This is particularly beneficial during large imports or data generation where add_*_meta() is called many times for a large number of users, posts, comments, or terms.

This can be achieved by inserting all the metadata for the object in one INSERT query and only deleting the object's meta cache once. The existing filters and actions that are triggered when calling the add_*_meta() functions individually should still all be fired as expected, once for each meta key.

The performance benefit should grow as the number of rows to insert increases, because both the number of database queries and the number of cache deletions for the object should remain constant (O(1)) rather than increasing correspondingly to the number of rows (O(n)).

That said, there does appear to be a sweet spot after which the performance of one large INSERT query starts to degrade, although according to the PHPBench tests I've added it still remains overall more performant than multiple queries. Some more testing is needed across different payload sizes and database versions.

Trac ticket: https://core.trac.wordpress.org/ticket/59269

[swissspidy]

#34848 already covers adding meta in bulk according to the patches, so what‘s the difference?

[johnbillion]

Yes the patches on that ticket mostly cover adding (and deleting) metadata in bulk but the ticket itself is about updating metadata in bulk. Adding is much more straight forward than updating, hence splitting it out into a separate ticket.

[namith.jawahar]

Wouldn't overloading add_*_meta() functions maintain cohesiveness with similar functions in core instead of introducing a new function.

Eg : get_*_meta()

[johnbillion]

Functions with multiple signatures is poor design, it creates ambiguity and type safety issues. It also makes it difficult to determine whether a function is going to behave properly without resorting to checking the version of WordPress is in use. With the new functions there is no ambiguity and developers can use function_exists( 'bulk_add_post_meta' ) to easily check that the function exists prior to using it.

[namith.jawahar]

I used this code in two custom importers and it works great. We do require bulk delete too when this is introduced as both functionality are often (always?) required together in real world projects.

It might be a good idea to do insert, delete and “update” (by insert for new and looped update function calls for actual updates) together instead of just bulk add for it to be really useful.

[johnbillion]

Thanks for testing @namithjawahar! Let's keep this ticket focused, lest it never makes it in like #34848 so far hasn't.

• Bulk adding is straight forward
• Bulk deletion is probably straight forward but please open a separate ticket for that
• Bulk updating is being handled in #34848

[namith.jawahar]

That’s reasonable, I can create a ticket PR for delete if that’s not already in there. I really think the three functions needs to go in together in the same release cycle to be of max benefits to devs.

Should we have a way to allow for adding multiple entries for the same meta key?

Maybe? I haven't had a need for it myself. Could be useful, but it would complicate the function signature as it wouldn't be able to support an associative array of meta key => meta value elements.

This ticket was featured in today's 6.9 Bug Scrub. I'd be happy if you could check out @aaronjorbin's feedback.

[westonruter]

Changes requested: ​https://github.com/WordPress/wordpress-develop/pull/5128#pullrequestreview-3303799241