Problem/Motivation
https://www.drupal.org/project/json_entity_storage is a contrib sandbox that implements cross-database compatible document storage for content entities by @amateescu.
It relies on (at least) the following core issues:
#3343634: Add "json" as core data type to Schema and Database API
#3618770: Add support for virtual tables (SQL Views) to the DB Schema Definition API
#3586688: Add support for generated columns to the DB Schema Definition API (fixed)
Drupal's field storage is currently either on the base/field data tables for base/bundle fields, or in dedicated tables for multiple cardinality base fields and all configured fields. The main reason for this is that configured fields can change cardinality from 1 to 2 and we don't want to do a data migration as a result of a configuration change in the UI.
In 11.4/11.5 we did #3562172: Single cardinality entity fields are now loaded from the database at once and related issues to reduce the number of database queries necessary to load field data from field tables, but this logic is extremely complex and fairly error prone, we've already fixed a couple of regressions as a result of that work. Also with document storage we can reduce 3-4 queries per ::loadMultiple() down to 1 probably.
Also, although that work reduced the number of queries for entity loading, it did not reduce the number of joins for listing queries via views and entity queries.
If you have a listing query with conditions on one or two fields, and sorts on one or two others, then you get into a classic denormalized MySQL query problem, usually resulting in temporary tables created (on disk with large data sets), table scans, filesorts etc., these are the kinds of queries that can take multiple seconds to complete.
JSON entity storage was blocked for some time on the limitations of JSON indexes between database engines, the contrib module fixes that via a new approach using SQL virtual columns and tables (SQL views). These are additional columns, generated and synced by the database engine itself, containing the results of JSON expressions against the JSON field. As such, views and entity queries can run against those regular columns. For single cardinality fields, they can all be on the base table, massively reducing joins. For multiple cardinality fields we'll still need separate tables, but complex queries will still have less joins overall if other conditions/sorts are against the base table.
So this can not only simplify the entity schema down to one or two real tables + some virtual tables and columns, but also loading and listing queries.
Steps to reproduce
Proposed resolution
1. Commit the blockers
2. Add the new module as experimental
3. Test the new module against some real sites (if only locally / on development servers)
4. Add/document how to do the data migration from the current storage to document storage - we should be able to make this so the migration happens in the background, while listing queries go against the current storage, and then we flip the active storage when the data has all been written, and finally drop the old tables.
5. Once the new module is stable, used for new sites by default, and the migration path is tested, we can extract the current per-field storage to a module, then move that module to contrib, to support sites that haven't migrated yet.
Remaining tasks
As well as the hard blockers, we should also probably do #3577321: [PP-1] Use a JSON column for config entity queries first since that could be done as an in-place update and will use the same virtual column idea we'll be relying upon here.
Comments
Comment #2
catchAdding tags.
Comment #3
catch