@DannyH: What is the difference to T100858 ?

That's the design ticket -- we're still discussing design there, so I made a clean ticket that I can talk with the developers about in a meeting today.

Change 223321 had a related patch set uploaded (by Matthias Mullie):
Duplicate workflow_last_update_timestamp to page_props

https://gerrit.wikimedia.org/r/223321

Instead of duplicating relevant watchlist data to Flow cluster, we're probably better off duplicating relevant Flow data (only update timestamp, I think) to page_props in core db.
We can then execute a query to core's slaves to get the exact topics we want to see, like:

$user = \User::newFromId( 1 );
$timestamp = wfTimestampNow();

$dbr = wfGetDB( DB_SLAVE );
$res = $dbr->select(
	array( 'watchlist', 'page', 'page_props' ),
	array( 'wl_namespace', 'wl_title' ),
	array(
		'wl_user' => $user->getId(),
		'pp_sortkey < ' . $dbr->addQuotes( $timestamp ),
		// join conds
		'wl_namespace = page_namespace',
		'wl_title = page_title',
		'pp_page = page_id',
	),
	__METHOD__,
	array(
		// pp_sortkey is a float & imprecise, it's useful since it's indexed,
		// but we need to sort on pp_value as well for accuracy for where
		// pp_sortkey is too imprecise to distinguish individual values
		'ORDER BY' => 'pp_sortkey DESC, pp_value DESC',
		'LIMIT' => 10,
	)
);

And with those results, we can load the rest of the details from Flow storage.

Won't this query filesort because you're ordering by pp_value which is unindexed?

I assumed it would still use the index, but looks like it doesn't.

I've submitted a patch to change pp_sortkey from FLOAT to DOUBLE, which would have the precision to store full timestamp, so we wouldn't need the additional pp_value sort in there.
https://gerrit.wikimedia.org/r/#/c/223552/

This is a spike. The outcome is generally knowledge rather than code.

Do you want the patch above to be merged? Is it still relevant if we don't end up doing the personal feed?

We're definitely going to do the personal feed, unless the spike revealed a fatal flaw.

My question is not if we want the personal feed but if the patch is relevant without it. It informs where the patch belongs and when it should be merged. If the patch is the first step in implementing the personal feed then it should be attached to the first task in implementing the personal feed and the spike could be moved to done.

I'm just trying to keep the board clean with actionable tasks.

Ah, yes, good point.

For the spike to be done, we need the core DB change to go through, otherwise this approach won't work.
But I mostly dragged this to "needs review" to see if the rest agrees with this proposal (https://phabricator.wikimedia.org/T104268#1434497 & patches), or get feedback if I've missed something obvious.

The Flow patch is mostly a by-product of trying out to see if it could work and doesn't have to be merged right away. We probably shouldn't merge that one until we actually start working on this.

Looks good to me, but we need a DBA to agree to do the pp_sortkey schema change (https://gerrit.wikimedia.org/r/#/c/223552/)

Change 223321 abandoned by Matthias Mullie:
Duplicate workflow_last_update_timestamp to page_props

https://gerrit.wikimedia.org/r/223321