[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):

 Replying to [comment:57 peterwilsoncc]:

 > @JoeFusco My reading of Matt's comment above is that the addition of one
 table has been authorized for the current and future RTC features. It's a
 very rare occurrence so we're not going to be able to slide in two when
 it's possible to use one.

 Agreed - one table for sync, no argument there.

 > No matter where awareness is stored, it's going to generate a lot of
 rows. If we use transients, they get added to the options table, if we use
 the RTC table, they get added there.

 This is the key question. Awareness generates rows no matter where it
 lives - the difference is what guarantees that storage provides and what
 it costs the host table.

 > I think it better to put awareness in the RTC table but maybe we can
 lean on the transients pattern and use a persistent cache if it's
 available.

 Persistent object cache as the primary store for awareness - fully agree.
 It's fast, atomic, and ephemeral by nature. No DB writes for awareness
 when cache is available.

 The open question is the fallback when persistent object cache isn't
 available. Transients fall back to `wp_options`, and for awareness that
 means:

 * One write per client per room every poll interval (~1 second)
 * Short TTL (30-60 seconds), so rows expire and accumulate until lazy
 garbage collection runs
 * On multisite, these writes hit per-site `wp_X_options` tables - already
 the most bloated table on most installs

 At 10 sub-sites with 2 editors each, that's 20 transient writes/sec across
 10 `wp_options` tables. At 50 sub-sites, it's 100+. These fall directly
 out of the polling interval.

 Putting awareness in the single collaboration table has a different cost.
 Sync needs multiple rows per `(room, client_id)`, so a UNIQUE KEY on those
 columns can't exist in a shared table. Without it, concurrent awareness
 writes produce duplicate rows instead of being prevented - requiring
 application-level deduplication rather than preventing duplicates at the
 schema level.

 > Re: Over queries, anything that allows us to limit the queries is an
 improvement over the current post meta implementation. My understanding is
 the query will be something along the lines of `SELECT * FROM table WHERE
 cursor > /* cursor position on last request, 0.25 seconds ago */ AND event
 = sync`. In most cases, that means that over query is one row and PHP is
 probably more efficient for filtering than adding a column and an index
 for said column.

 Agreed - PHP-side filtering makes sense given the row counts from a cursor
 query. No need for an index on event type.

 So for no-cache sites, the options are:

 ||= Option =||= Issue =||
 || Transients → `wp_options` || High-frequency ephemeral writes to a table
 designed for low-frequency config data ||
 || Single collaboration table || No UNIQUE KEY — concurrent awareness
 writes produce duplicates instead of being prevented ||
 || Require persistent object cache || Excludes a significant portion of
 the install base ||
 || Small dedicated awareness table || Correct by constraint, bounded,
 trivial cron cleanup ||

 This isn't a preference for two tables — it's that every fallback path for
 awareness without cache makes something else worse. If there's a fifth
 option I'm not seeing, I'd welcome it.

 Does the original one table guidance apply to the no-cache fallback path
 specifically?  The scope is narrow: bounded rows (one per client per
 room), 60-second TTL, {{{@access private}}}, feature-gated.

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