Reconsider the separate field revision data tables

I'm sure there's an issue for this somewhere already, but can't find it at the moment.

iirc the original reason for having two tables was this:

- queries with conditions/sorts on field tables are going to mean temporary tables/filesorts

- having smaller tables for current values means less rows to go through.

http://posulliv.github.io/2013/01/08/norevisions-field/ is for write performance, which is nice if it's better, but not the same thing at all.

So before doing this, I'd want to see a View or EntityQuery with conditions/sorts on a couple of different fields or more, then a dataset of say 20,000 nodes and 100,000 revisions, and compare the EXPLAIN and/or benchmarks for executing that query.

The way to fix that performance issue properly would be to do automated denormalization of those queries. The last work I'm aware of on that was in 2011 though https://groups.drupal.org/node/145174

Tagging.

Here is the original issue #1279440: Deeply decouple revisions from Entity API and Node module
Thx yched for summarizing the issue so well.

@catch
Field table and a field revision table are identical if revisioning is turned off. In this case, a field revision table is not smaller in any way than its field counterpart, so views read queries on a field or a field revision table should have no difference. Write query performance could be doubled by not creating revisioning tables, and mysql cache can be halfed if we would not have those "duplicate"tables on entities without revisioning enabled.

Field revisioning tables are ok if you have enabled revisioning, but not when you have revisioning disabled. So, I think it is ok to have revisioning tables when someone enabled them.

Thx yched much appreciated. For some reason, I thought we have some kind of switch at admin/structure/types/manage/mycontenttype . I guess it was just a dream I mistook for reality over time :) I don't want to derail this issue, but adding a proposal for a followup issue:

It would be a feature request then to enable site admins to manually enable / disable revisioning for entities that support revisioning to avoid unnecessary overhead. (controlling revisionability)
Disabling would offer the deletion of all revisioing collected so far, and enabling would create a copy of the field table into field_revision table (or this can happen when a new revision is actually created one by one).

This issue is pretty close to an area I will hopefully start to work on soon: as briefly noted in #2068325: [META] Convert entity SQL queries to the Entity Query API, D8MI is planning to implement an enhanced SQL storage controller which will provide full multilingual support for base fields of all core entities at storage level. The plan is have it ready ASAP and then replace the current one with it, as soon as we are done with the entity query migration.

Our current storage controller supports 4 modes for storing base fielads:

1. base: simple entities are not revisionable nor translatable, hence they just need a base entity.
2. rev: revisionable entities need an additional entity_revision table.
3. mul: multilingual entities need an additional entity_field_data table to store field translations.
4. mulrev: multilingual and revisionable entities need an entity_revision, an entity_field_data and an entity_field_revision table (see also #2057401: Make the node entity database schema sensible).

Currently these modes are hardcoded in the entity definitions, depending on the entity business-logic requirements. One of my goals for the enhanced SQL storage controller is being able to switch among these modes: since it will need to be able to generate all the tables, not just the configurable field ones, switching among the 4 table layouts above when there is no data should be trivial. Implementing a migration code, making it possible to switch from one mode to another when data is already stored in the tables, should be feasible but might be contrib material, it depends on how much time we will have to work on that.

That said, I think that the best way to address this would be to provide field revision tables when the entity has revision support enabled and avoid them in other cases. We could do the same with translation support and avoid the langcode column for monolingual entity types.

Thoughts?

So I'm not sure how support for live reconfiguration of the schemas would work - be it backed on the UI or on the code (entity type definition). That smells like CCK's infamous dynamic db reshuffling & data juggling - only worse, since at the entity level instead of at the level of individual fields separately :-/.

As I said, the initial goal is supporting switching between modes only when no data has been created yet, which is how core Field API deals with settings that have an impact on the storage, correct? I didn't fully think through the actual triggering process (UI vs API), but being able to switch among those will certainly bring a perfomance gain when some of the features they allow for are not needed.

Implementing a migration reliably might be way harder and is indeed something like the CCK stuff, but I don't think that's the crucial part of the proposal. Instead we could certainly prevent a switch when data is available, at least in core.

Beta is out, this one could be closed

@andypost how having a beta solves this issue?

More appropriately, this should be set to 8.1.x. I don't think a refactor of the scale being proposed here would be considered release-blocking for 8.0.x.

Adding performance tag

As yched mentioned in #4

So yes, writing the same data in two tables when the entity type is not revisionable is kind of absurd and adds needless write costs

As commented in #1279440: Deeply decouple revisions from Entity API and Node module, entity *types* that don't support revisions (like user and taxonomy terms) already don't get the revision tables anymore.

So that part of the issue is resolved, and that's the part the other issue talked about.

The second part is restructuring the storage on how revisions are actually stored. That's still open but I don't think that will happen in 8.x as it would be a huge change. But contrib can experiment here by using different storage implementations.

queries with conditions/sorts on field tables are going to mean temporary tables/filesorts

That's generally correct if the assumption is that a join to a "field revisions" table needs to sort to get the latest revision. However, it would be possible to retain performance by either (1) always identifying the latest revision by a number like "0" or (2) adding a column like latest_revision that's set to TRUE for the latest one. That way, the join can be on the nid AND revision=0, for example. As long as there's an index on (nid, revision), it would work.

having smaller tables for current values means less rows to go through.

This is true in terms of data locality. There's a higher chance that contiguous data in a table without old revisions is useful -- and less chance in a table littered with old revisions.

Greetings,

Just to mention what may be obvious : D6 and prior only stored the current title/body/teaser in the revision table and only created a new revision if requested by the settings.

The more complex D7 logic increases the size of the database, which is probably not an issue really [though a lot of arguments to the contrary here: https://www.drupal.org/node/1279440 ].

Both methods work of course, though the D7 method seems safer (even if some odd database writing error occurred, the previous version remains in database). The D7 has another main advantage: the Admins have a copy of the original or re-written node easily accessible to work with in case a user makes an error (i.e. without needing to use filed backups), even if the Create Revisions is off.

For migration (D6 to D7; which I am doing right now), it is a bit more complex.

So though I prefer the D6 simplicity, rationally it seems like the D7 structure is safer/better.

EDIT : my assumption that the new structure keeps the original title/body/teaser is wrong. I tested and oddly all D7 content related tables (node, node_revision, field_data_body, field_revision_body) contain only the most recent title/body/teaser. Probably because the Revisions are off on my set-up, still this means the database has two copies of the titles and the body also is doubled.

Updating IS

Related issue: #3113639: The default table mapping and the SQL storage do not agree on whether non-revisionable bundle fields should get a revision table.

Actually, I think we can close this issue as obsolete, given that with the new entity storage revision field tables are not created for non-revisionable entity types.