Hi @JMonton-WMF,

First off, thanks for the excellent proposal, it is very concise and easy to follow!

The primary concern I have is table 2:

CREATE TABLE analytics.pageview_trending_relative_per_wiki (
    wiki_id          TEXT,
    page_id          BIGINT,
    last_trending_at TIMESTAMP,
    PRIMARY KEY ((wiki_id), page_id)
) WITH default_time_to_live = 21600;  -- 6h — this TTL is the list's decay

This table seems have a very low cardinality; There will only be as many partitions as there are wikis. Do we know how many that is? For example, will this work against a subset of all wikis, and if so, how many? How many pages for each of these wikis? Does the hourly batch compute work against the entire set of pages for a wiki, or those matching some criteria?

Hi @Eevans, thanks for looking into it!

We don't have a lot of data yet to analyze, and the trending metric might change, so the behavior of the data could change.

This is working for all wikis, but it produces data only on "relative trending pages" meaning that only pages that have more views than usual will appear here. I'd expect more data on more viewed wikis, but other wikis will have some records too.

I'm working on enabling our Hive ingestion and I hope to be able to analyze the data better in the following days, but we have data from July 20th. I'm taking 6h of data here:

SELECT wiki_id ,count(page_id) as total from event.pageview_trending_relative_v1
WHERE 1=1
AND datacenter='eqiad'
AND year=2026
AND month=7
AND day=20
AND is_trending = true
AND dt > '2026-07-20T00:00:00Z'
AND dt <= '2026-07-20T06:00:00Z'
group by wiki_id
order by total desc;

262 wikis had records on that 6h block. Here is a partial list, ordered by total number of records. The number of records per wiki goes down, most of them have a few records only.

I also don't know if the metric will change after Product reviews it. I'm not sure if 65k trending pages is expected or acceptable, maybe thresholds will be moved and fewer pages will be considered trending, which will reduce the number of records per wiki.

I don't know if this is acceptable or not, I'll expect enwiki to have always the biggest number, and many wikis will always have 0 or a few records probably. Do you think we could do some improvements?

The end goal is to provide a list of pages with is_trending = true per wiki (GET /public/analytics/pageview_trending_relative_per_wiki/:wiki_id) and as I believe we cannot use this condition in Cassandra, I thought about this second table where we only send records where is_trending = true, and a TTL to remove them after some hours. But if there is a better way of doing it, we can work on it.

Following up on todays meeting, @Eevans do you sign off on table 1 implementation for the PoC? @JMonton-WMF can you confirm table 1 is sufficient for the evaluation?

If the first table is released, we can enable one of the 2 endpoints (GET /public/analytics/pageview_trending_relative_per_page/:wiki_id/:page_id) for the Attribution API, and if I recall correctly, this was the most important of the two, so my assumption is that it will at least allow one of the use cases.
We can work on improving the second table if needed meanwhile.

Maybe @GGoncalves-WMF can answer better about releasing one of the two endpoints first?

Let's try to start from First Principles: What we have here is a single dataset. Our dataset (ostensibly) contains an entry for every page, and records whether or not the page is trending, with a timestamp to indicate when that trending status was last updated. We require that this dataset be indexed according to it's trending status, so that we can query it for a list of pages where is_trending=true.

I think that describes the problem, someone correct me though if I've gotten something wrong.

I want to start from here because —the proposal, as written— seems to imply all of the above, but doesn't state it plainly, nuance can be important, and these tickets also serve as the decision of record (i.e. what we did, and how we arrived there).

For example: as-proposed, table no. 1 does not contain an actual entry for every page, but the proposal indicates that the absence of a page is equivalent to is_trending=false. That we don't maintain a full set of explicit entries is presumably due to limitations of the data we have to work with, how it is processed, etc. A compromise.

Table no. 2 is an inverted index of The Dataset (aka table no. 1), de-normalized to include the timestamp. It's aggressive TTL is because we don't have a practical way of updating corresponding index entries when the primary dataset changes. It's another compromise, one meant to bound the inherent incorrectness.

So in a perfect world, we'd probably maintain the full set of all pages, with an explicit trending flag, and explicit timestamp. The trending flag would be indexed transparently, and that indexing would be atomic and isolated. Honestly, in a perfect world I think we'd want to store a value that indicated a pages relative trending (say 0-1.0), and queries would use a predicate of >= {trending_threshold}, because from a data perspective, we've created something of a disconnect between the thing that decides what does/does not qualify as "trending", and the otherwise canonical source of what is trending (the datastore).

Ok, so we don't live in a perfect world, but I'd be remiss if I didn't point out that MariaDB might be an option here. One table (basically table no. 1) with an index on is_trending would likely get us most of the way there. If there is no way of explicitly transitioning a page from trending to not, then a query against the timestamp would work (and if so, probably without an indexed boolean field). Perhaps that is in fact, all of the way there.

Another way of approaching this, given the volatile nature of how it is modeled right now, would be to use volatile storage (Redis or Memcache). This could be done either with the rich ecosystem of caching abstractions in MediaWiki, or directly.

For the proposal as it is: Table no. 2 is problematic as it isn't distribute-able, as modeled, and short of something terribly tortured, I'm not sure we can fix it. That TTL combined with the volume of writes is also uncharacteristic of all the other workloads on this cluster (i.e. it could require a lot of IO to make tombstone GC work reliably).

Table no. 1 by itself is OK, I think, but if the indexing of is_trending is coming either way, then it would seem wise to solve for it now (or at least to know how it will be solved).

In T432352#12214749, @Eevans wrote:

Let's try to start from First Principles: What we have here is a single dataset. Our dataset (ostensibly) contains an entry for every page, and records whether or not the page is trending, with a timestamp to indicate when that trending status was last updated. We require that this dataset be indexed according to it's trending status, so that we can query it for a list of pages where is_trending=true.

I think that describes the problem, someone correct me though if I've gotten something wrong.

This describes the situation correctly, and the problem of the second endpoint (second table). The first endpoint doesn't need to query pages where is_trending=true, it only needs to query page_id=xxxxx to know if it's trending or not.

For example: as-proposed, table no. 1 does not contain an actual entry for every page, but the proposal indicates that the absence of a page is equivalent to is_trending=false. That we don't maintain a full set of explicit entries is presumably due to limitations of the data we have to work with, how it is processed, etc. A compromise.

Yes, we are filtering out pages with less than 50 views per hour. This filter reduces the amount of data from TBs per hour to a few GBs.

Table no. 2 is an inverted index of The Dataset (aka table no. 1), de-normalized to include the timestamp. It's aggressive TTL is because we don't have a practical way of updating corresponding index entries when the primary dataset changes. It's another compromise, one meant to bound the inherent incorrectness.

So in a perfect world, we'd probably maintain the full set of all pages, with an explicit trending flag, and explicit timestamp. The trending flag would be indexed transparently, and that indexing would be atomic and isolated. Honestly, in a perfect world I think we'd want to store a value that indicated a pages relative trending (say 0-1.0), and queries would use a predicate of >= {trending_threshold}, because from a data perspective, we've created something of a disconnect between the thing that decides what does/does not qualify as "trending", and the otherwise canonical source of what is trending (the datastore).

The trending_threshold exists already, it is used in Flink, but if it helps, it could be moved to a different place. The source data has a z-score used to decide whether is_trending is false or true. We could store z-score rather than is_trending.

Ok, so we don't live in a perfect world, but I'd be remiss if I didn't point out that MariaDB might be an option here. One table (basically table no. 1) with an index on is_trending would likely get us most of the way there. If there is no way of explicitly transitioning a page from trending to not, then a query against the timestamp would work (and if so, probably without an indexed boolean field). Perhaps that is in fact, all of the way there.

The process already updates records where is_trending=true become is_trending=false. There was a comment about the TTL because there is a small corner case that could leave a record with is_trending=true and not update it to false. The corner case only appears if a page becomes trending, and suddenly it gets a big drop on views during 5 minutes, making it passing from trending to less than 50 views during the last hour. This won't happen often, and as you said, a look at the timestamp will tell us this had happened. I can give more details about how this should happen to be an issue.

I'm ok with moving the design to a different storage, I also think that this could be a single table, properly indexed, in MariaDB. I think the blockers for that could be the time to deliver and the infrastructure. The goal is to provide a couple of endpoints for the Attribution API, and I believe the Cassandra -> Data Gateway is a proven path for Attribution API, but I personally don't know how the infra would look like if we want to move this to MariaDB or to a cache abstraction layer.

Maybe @Ottomata you have some thoughts on this?

Maybe @GGoncalves-WMF can answer better about releasing one of the two endpoints first?

About the relative priority of the read patterns: a lookup by page id returning the "current" (hours-delayed) trending status is the priority here, and is what MediaWiki Platform is specifically asking for. I've updated the MVP definition to make that explicit.

I'm perfectly fine to remove "filtering pages by their trending status" from scope (also updated the doc to that effect). That's an educated guess for what other internal adopters of the API will ask for, but since scoping this MVP, we now have plans to evaluate this API more thoroughly, so we can consider that query pattern in the context of all the other feedback we'll get and work on what's most impactful at that point.

I'm perfectly fine to remove "filtering pages by their trending status"

BTW this kind of filtering is exactly what CirrusSearch weighted tags is for.

The process already updates records where is_trending=true become is_trending=false.

But this must be possible to use weighted tags. If <50 views corner case is a problem, then weighted tags won't work. You'd need a TTL for the data somehow.

I personally don't know how the infra would look like if we want to move this to MariaDB or to a cache abstraction layer.

It would look kinda like this:
https://wikitech.wikimedia.org/wiki/User:Ottomata/Manual_MediaWiki_Job_Submission

But instead of submitting a NullJob, we'd implement a Job that does what we need: update the MariaDB table or write to the appropriate MW caches.

Change #1326920 had a related patch set uploaded (by Ottomata; author: Ottomata):

[mediawiki/extensions/WikimediaEvents@master] Pageview Relative Trending - update job and storage

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

PoC of a MW JobQueue update to store RelativePageTrending details in a MW Object cache.

Here's a quick estimate of write volume, with the intention of using JobQueue to update a MW store (MainStash or an extension MariaDB table) in mind.

• a trending update event is about 500 bytes of uncompressed json. A Job update event will be about the same size.
• at any given time, enwiki has about 65000 pages currently trending.
• The streaming trending job emits trending update events every 5 minutes (window slide)
• a 'batch' of trending updates for all wikis is about 5K events.

We could produce 5K updates from the streaming job every 5 minutes, or we could produce 5K updates once per hour from the data lake.

The trending state can be stored locally per wiki. I'm don't know how many of those 5K updates are for enwiki, but let's just use 5K as a per wiki upper bound for this estimate.

We intend to produce a MW Job event for each trending update event. We could consider batching many updates in one job, but we'd prefer to avoid that complexity if we can. Let's assume 1 Job event per trending update.

• Streaming option: 5K trending update bursts every 5 minutes.
• Batch option: 5K trending update bursts every hour

FWIW, @JMonton-WMF is working on a solution for the <50 pageviews edgecase in T435291: Relative Trending - Fix records always trending. described in T432352#12215114

FYI slack thread discussing this here

cc @Ladsgroup

The object cache approach seems reasonable to me (provided there are no concerns on the MW side).

Using Object Cache

So, we are changing the approach, rather than using Cassandra, we'll use Object Cache on Mediawiki.

This means that we are not longer using the path of Kafka (eqiad.pageview.trending.relative.v1) -> Gobblin -> Datalake -> Cassandra
and we'll use: Kafka (new topic) -> Mediawiki Extension -> Object Cache

The source data is the same, but the new topic will have a different schema, so it can be used to write into the Object Cache e.g:

{
  "$schema": "/mediawiki/job/1.0.0",
  "meta": {
    "stream": "mediawiki.job.pageviewTrendingRelativeUpdate",
    "dt": "2026-08-19T10:40:00Z",
    "id": "1b9d...",
    "request_id": "page-trending-enwiki-407832-1755600000000"
  },
  "database": "enwiki",
  "type": "pageviewTrendingRelativeUpdate",
  "params": {
    "requestId": "page-trending-enwiki-407832-1755600000000",
    "action": "set",
    "page_id": 407832,
    "ttl": 86400,
    "cache_value": {
      "is_trending": true,
      "page_id": 407832,
      "view_count": 250,
      "view_count_avg": 3.85,
      "view_count_max": 12,
      "view_count_stddev": 2.31,
      "window_baseline_duration": "PT1H",
      "window_start_dt": "2026-08-19T09:40:00Z",
      "window_end_dt": "2026-08-19T10:40:00Z",
      "z_score": 2.66
    } 
  } 
}

The Flink application will have 2 different outputs. This new output will also produce a reduced number of records, rather than producing an event per page every 5 minutes, it will produce only records when the is_trending status change. This should reduce the amount of data many times, as most of the records are just refreshing the status of the trending, without changing it.

Mediawiki Extension

A Mediawiki extension will read the topic eqiad.mediawiki.pageviewTrendingRelativeUpdateJob and will set or unset the Object cache
Draft already here (https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaEvents/+/1326920)

Do you have an estimate of number of writes or reads per second to the mainstash?

@Ottomata added some numbers here: https://phabricator.wikimedia.org/T432352#12231296

Writes will be 5k every 5 minutes at most. I believe that number will look 10 or 100 times lower after some changes that we can hopefully deploy next week. But we could consider 5k every 5 minutes as upper bound.

I don't think we have an estimate for reads, this is part of hypothesis and MVP, and the Attribution API will use this data, but we don't know how often they will use the data. Maybe @GGoncalves-WMF has some information about reads?

Abut Read rate:

From this Slack thread: https://wikimedia.slack.com/archives/C0AFFARN2ER/p1787321403947449 it looks like we could assume 20 request/second as upper bound.

Overall, that shouldn't be a problem but you have two options:

• memcached: Can take a lot more but there is no guarantee the data will be there, it's obviously a cache so the host might go out of rotation, etc. OTOH, it has a lot more capacity, faster etc.
• mainstash: Can take decent amount and provides some guarantee that the data will be there (it copies it to two different clusters). Your numbers are well withing the limits. So no problem there.

If you want to go with mainstash, that's okay but I'm slightly worried that it has per-page key. That could mean a lot of queries in a read. Give me a bit to understand the code a bit better.

In T432352#12241834, @JMonton-WMF wrote:

Abut Read rate:

From this Slack thread: https://wikimedia.slack.com/archives/C0AFFARN2ER/p1787321403947449 it looks like we could assume 20 request/second as upper bound.

Slack link doesn't work for me. It's a link to the channel (at least to me). The PoC doesn't have any read paths. How are you planning to read these data?

We are preparing this data for the Attribution API and their team, which I believe will be consumed by Enterprise. We are only the producers, and they will be the readers.

From the conversation in Slack, they shared a Grafana dashboard that shows the number of requests the Attribution API receives, but it isn't very used yet.
They were estimating a number of requests similar to the number of edits per second, which is around 20, and half of those could be done to the metric we are trying to write.

Maybe @pmiazga can help us here better, or ensure I haven't said anything wrong, as they own the side that will do the reads.

Thanks. I assume there aren't that many trending articles in a wiki (less than let's say 10K). My suggestion is to rework the patch into this:

• Each wiki will have a blob of list of trending articles (as list of page ids), store it in in mainstash, if you want to check if an article is trending, load that blob and check against the list. On top of that, cache it in memcached with let's say 1m TTL and then invalidate it every time you change and store a new value in mainstash (there is a method for that in WAN class).

Would that work?

That's correct, we don't have yet exact numbers, but on some tests there were around 7k ~ 15k trending pages over all wikis.

My concern with that approach is that it will change both the process we have for writes and the read pattern. if I'm not wrong, it won't change the number of reads or writes, only the schema we store, and I assume the benefit comes from the number of keys stored, not the number of reads/writes.

The original requirements document is this Prep Pantry. The main MVP we are chasing is a per-page lookup.

The process computing the metric on streaming is also producing one event per page, which we wanted to translate 1:1 to writes. The object we wanted to store in cache is not just trending, but some extra metrics, as we might change the definition of trending in the future: e.g.:

json
{
     "is_trending": true,
      "page_id": 407832,
      "view_count": 250,
      "view_count_avg": 3.85,
      "view_count_max": 12,
      "view_count_stddev": 2.31,
      "window_baseline_duration": "PT1H",
      "window_start_dt": "2026-08-19T09:40:00Z",
      "window_end_dt": "2026-08-19T10:40:00Z",
      "z_score": 2.66
}

If we change to store an array with all trending pages, I assume we'll store only the page_id, which maybe works for the consumer, but we'll also need to modify the streaming pipeline to keep state per wiki with all trending pages. It is doable in Flink, just wanted to mention that it will require changes.

I'd prefer to keep the page_id lookup if there aren't major benefits on storing the whole list per key, or if the consumer has issues with consuming the whole list, but the approach would be doable too.

FYI - what I'm currently working on is https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaCustomizations/+/1328186 - this is the trending.top which is fetched from AQS and has a list of 100 top articles. We cache this list per wiki ( ~100 items, on each wiki ), once a day there should be a job that triggers the fetch and stores information in MainStash.

Storing everything entire list in cache is nice ( this is what we do for trending.top) but if the array can have 7k ~ 15k items as mentioned above, deserialization of such big array might be time-consuming. I don't see a problem with MediaWiki fetching it per page_id.

To be clear, I mean it should be per-wiki (not per -page). Which based on your numbers should reduce the size?

ah, sorry. My bad, missed the part that 7k-15k items for all wikis - I thought it's per wiki - that's why I said that this list a bit too big.

I don't have that much expertise with BagOStuff/MainStash to be able to say whether per page would be better approach. IMHO storing one item per page should work, but I didn't work with MainStash too much.

Let me bring @Krinkle as a tie-breaker; We have 7k - 15k ints to store, those ints are page_ids of trending articles. We want to store this information in the MainStash. the question which approach would be better:

• for each wiki - store like 1K keys, one key for each page_id, this way the lookup would based by key that contains the page_id.
• do we store a map with 1K keys, indexed by page_id, and then the lookup would be done by retrieving the map and doing array_key_exists.

We had a meeting yesterday to talk about this topic, following @Ladsgroup recommendations, we are going to change the behavior to write per wiki_id rather than per page_id. Writing per page_id could cause too many writes, and it doesn't solve the future use case about getting the whole list of trending pages.
Writing by wiki_id, we can store an array with all trending page_ids inside. To avoid a lot of noise, we'll emit only one event per wiki_id every 5 minutes. By doing so, we can store the objects in Object Cache (MainStash) and we can also save them in Memcache. Every new object invalidates the old one, the list of trending pages will always be the full list, if a page isn't inside, it means the page is not trending.

With this approach we won't produce any other indicator than is_trending = true/false but it wasn't a requirement either. The Datalake still has all the information related to trending pages, including their z-scores, view counts, thresholds and more.

So, in summary, the actions we are doing now are:

• Change the Mediawiki Extension we created to receive the array of page_ids and store it on Object Cache and Memcache. It should invalidate old records when a new record arrives.
• Change our Flink application to produce a new output, flushed every 5 minutes, keyed by wiki_id and containing the full list of trending pages.
  • Introduce a new metric in Prometheus to know the number of page_ids on each array, so we can monitor if the array of page_ids become too big.

For the page_id lookup (main scenario): a reader will retrieve the whole array of page_ids and perform a search to find if a page is trending or not. We don't expect to have more than 5k ~ 7k page ides on enwiki, but the definition of the metric can change and these numbers could change.
For the future use case, get the list of trending pages: The reader only needs to retrieve the wiki_id key that comes with the whole list.

Change our Flink application to produce a new output, flushed every 5 minutes, keyed by wiki_id and containing the full list of trending pages.

Q: I assume we will still produce the same per page_id trending event stream we are now. Its just the JobQueue events we produce will be per wiki_id every 5 minutes. Is that correct?

Yes, the regular output doesn't change, we create a new output eqiad.mediawiki.job.pageviewTrendingRelativeUpdate with the new format per wiki_id.

The Flink application doesn't need to read the source twice, it reads the source and creates 2 outputs.

The data in Kafka will look like:

{
	"$schema": "/mediawiki/job/1.0.0",
	"meta": {
		"stream": "mediawiki.job.pageviewTrendingRelativeUpdate",
		"dt": "2026-09-02T14:10:00Z",
		"id": "426a30f5-65da-4400-9419-27dfc2002879",
		"request_id": "page-trending-dewiki-1788358200000"
	},
	"database": "dewiki",
	"type": "pageviewTrendingRelativeUpdate",
	"params": {
		"requestId": "page-trending-dewiki-1788358200000",
		"page_ids": [
			477790,
			701190,
			6217474
		],
		"ttl": 86400
	}
}

This sounds good, only a minor nitpick - lets keep it as a map ( PHP Assoc array, just use array_flip() in the mediawiki job ) - in case when array can have up to 7k elements - it's gonna be much faster to do isset($map[$pageId]) instead of full lookup in_array($pageId, $array).

Also, if we start storing it immediately as a map, it's gonna be much easier to assign them some additional values in the future.

Ok, I'll work on that tomorrow, thanks!

I've doing some real tests to analyze how many messages and page_ids we'll send in different scenarios.

Sending all wiki_ids was sending more than 1000 events every 5 minutes where most of them were empty arrays (no trending pages at all). On a new version, and I believe this is the final one, we are sending around 200 events every 5 minutes, only wiki_ids that have trending pages or had trending pages before (we filter out wikis that had no trending pages in the last batch and have no trending pages on the current one)

We are also seeing that the page_ids array for enwiki is around 3000 page_ids.

Just for the record, the old approach about sending one request per page_id was sending around around 700 messages every 5 minutes:

But the page_id approach is more prone to change if the trending formula changes.

Change #1335890 had a related patch set uploaded (by JavierMonton; author: JavierMonton):

[mediawiki/extensions/WikimediaEvents@master] Pageview Relative Trending - update job and storage

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

In case someone could review it, this is the patch that will write into MainStash and Memcached:

https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaCustomizations/+/1337930

Change #1337930 had a related patch set uploaded (by JavierMonton; author: JavierMonton):

[mediawiki/extensions/WikimediaCustomizations@master] Pageview Relative Trending - update job and storage

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

Change #1335890 abandoned by JavierMonton:

[mediawiki/extensions/WikimediaEvents@master] Pageview Relative Trending - update job and storage

Reason:

Moved to https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaCustomizations/+/1337930

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

Change #1326920 abandoned by JavierMonton:

[mediawiki/extensions/WikimediaEvents@master] Pageview Relative Trending - update job and storage

Reason:

Moved to https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaCustomizations/+/1337930

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

@pmiazga did a review of https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaCustomizations/+/1337930 last week and we implemented his suggestions. He raised a concern about writing in both MainStash and Memcached, or if we should write only in MainStash.

Could you @Ladsgroup please review https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaCustomizations/+/1337930 or help clarify if writing in both MainStash and Memcached is ok? This is the only remaining patch blocking the completion of the Hypothesis IR2.1.2a, and we'd like to move this forward this week.

Thanks!

Change #1337930 merged by jenkins-bot:

[mediawiki/extensions/WikimediaCustomizations@master] Pageview Relative Trending - update job and storage

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

The patch has been merged and tested in Beta:

var_dump(MediaWiki\MediaWikiServices::getInstance()->get( 'WikimediaCustomizations.PageviewTrendingRelativeStore')->isTrending('800'));
bool(true)

var_dump(MediaWiki\MediaWikiServices::getInstance()->get( 'WikimediaCustomizations.PageviewTrendingRelativeStore' )->getTrending());
array(51) {
  [456]=>
  array(0) {
  }
  [800]=>
  array(0) {
  }
  [3620]=>
  array(0) {
  }
  [17451]=>
  array(0) {
  }

Thanks to everyone for the help on this ticket.