[wp-trac] [WordPress Trac] #64696: Real time collboration effectively disables persistent post caches while anyone edits a post

[WordPress Trac]

#64696: Real time collboration effectively disables persistent post caches while
anyone edits a post
--------------------------------------+--------------------------
 Reporter:  peterwilsoncc             |       Owner:  (none)
     Type:  defect (bug)              |      Status:  new
 Priority:  normal                    |   Milestone:  7.0
Component:  Posts, Post Types         |     Version:  trunk
 Severity:  normal                    |  Resolution:
 Keywords:  has-patch has-unit-tests  |     Focuses:  performance
--------------------------------------+--------------------------

Comment (by JoeFusco):

 Building on the discussion here and @mindctrl's analysis in comment 51 -
 sync updates and awareness are as different as posts and sessions. One is
 append-only content ordered by cursor, the other is ephemeral per-user
 state that gets overwritten every second. Storing both in one table with
 an event_type column means the UNIQUE KEY (room, client_id) that prevents
 simultaneous awareness writes from overwriting each other can't coexist
 with sync update rows, where a single client writes many rows per room.

 This is what led us to two tables, each a general-purpose primitive:

 **wp_collaboration** - append-only message passing

 ||= ''Column'' =||= ''Type'' =||= ''Notes'' =||
 || **id** || BIGINT unsigned, auto_increment, Primary Key || Cursor for
 polling ||
 || **room** || VARCHAR(191) || Unhashed room identifier
 (`$max_index_length` for utf8mb4 index compatibility) ||
 || **type** || VARCHAR(32) || update, sync_step1, sync_step2, compaction
 ||
 || **client_id** || VARCHAR(32) || Originating client ||
 || **update_value** || LONGTEXT || Opaque payload ||
 || **created_at** || DATETIME || For cron cleanup (7-day TTL) ||

 Indexes: KEY room (room, id), KEY created_at (created_at)

 **wp_awareness** - per-client presence

 ||= ''Column'' =||= ''Type'' =||= ''Notes'' =||
 || **id** || BIGINT unsigned, auto_increment, Primary Key || ||
 || **room** || VARCHAR(191) || Unhashed room identifier
 (`$max_index_length` for utf8mb4 index compatibility) ||
 || **client_id** || VARCHAR(32) || One row per client per room ||
 || **wp_user_id** || BIGINT unsigned || WordPress user ID ||
 || **update_value** || LONGTEXT || Awareness payload ||
 || **created_at** || DATETIME || Updated on every write, used for expiry
 ||

 Indexes: UNIQUE KEY room_client (room, client_id), KEY room_created_at
 (room, created_at), KEY created_at (created_at)

 The multisite upgrade path is gated - `wp_is_collaboration_enabled()`
 checks both the option and db_version, so the tables are never touched
 before the upgrade runs.

 Cleanup is a single cron: collaboration rows older than 7 days, awareness
 rows older than 60 seconds.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/64696#comment:53>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform