@jcrespo Sorry for the ping, hoping this one is a simple yes/no/try-this-instead. For most wikis I don't think altering cu_changes will be much of an issue, but on enwiki there are 17,705,197 rows (at the time of writing). So the main questions are if it is even feasible to alter this table, and if so, how long the migration would take. Many thanks!

This is not a simple question, you are basically asking me what I think on a design of a dataset/schema change I do not know much about. I will have to study the code and structure;,any extra links will speed up the process.

In T155734#2962890, @jcrespo wrote:

This is not a simple question, you are basically asking me what I think on a design of a dataset/schema change I do not know much about. I will have to study the code and structure;,any extra links will speed up the process.

Thanks for the quick reply! Not sure how much code you need to see, but here's a synopsis:

• Basically, the [[ https://phabricator.wikimedia.org/diffusion/ECHU/browse/master/cu_changes.pg.sql | cu_changes ]] table is a copy of [[ https://phabricator.wikimedia.org/diffusion/MW/browse/master/maintenance/tables.sql;15a2c3797320020dc0fb8fa3d6e2964e357733de$1069-1137 | recentchanges ]], with some added info like the user agent. What's missing that we would really like to have is a copy of rc_old_len (int) and rc_new_len (int).
• The data is copied via the [[ https://phabricator.wikimedia.org/diffusion/ECHU/browse/master/extension.json;ef4ca3223329cd45ec3db992e257af0792bd8189$35-37 | RecentChange_save ]] hook, so cu_changes gets written to every time recentchanges gets written to. The code where it does the actual copying (for edits) can be found here.
• Data in cu_changes that is older than 90 days (as specified by $wgCUDMaxAge) is purged.
  • If $wgPutIPinRC is set, the CheckUser extension will also purge such rows in recentchanges, though I'm not really sure why because it seems this is also handled by the RecentChangesUpdateJob. That aside, I'm not aware of any other core tables that the CheckUser extension modifies.
• There are also hooks to store logged actions like emails and account creation in cu_changes, but for those obviously old and new lengths of a revision will not apply, and be given a zero value.

Is there any other information you need? Let me know :) As I was collecting this for you, I'm noticing other possible implications caused by the cu_changes table being locked. For instance, the AbuseFilter extension writes to cu_changes directly via updateCheckUserData so that information is available to CheckUsers. We may need to temporarily remove that hook as well. Obviously this investigation is in the early stages, but if there is a "not gonna happen" as far as altering the main cu_changes table, I won't need to dive any deeper.

The following may sound like a noob question, but please have into account I wasn't familiar at all with this extension's persistent layer at all- not with the extension inner workings itself.

Basically, the cu_changes table is a copy of recentchanges

Why are some columns duplicated from rc, log, revisions? Is it because rows don't correspond 1:1 to rc/revision/logging rows, because it can have different purge policies or because the queries done require denormalization, or for simplification of the queries/reads?

In T155734#2963266, @jcrespo wrote:

Why are some columns duplicated from rc, log, revisions? Is it because rows don't correspond 1:1 to rc/revision/logging rows, because it can have different purge policies or because the queries done require denormalization, or for simplification of the queries/reads?

I think as far as edits go they more or less do correspond 1:1, but yes given CU involves sensitive data I imagine the authors kept in mind that for some third party wikis privacy is not as important and they'd rather be able to run checks on older data. On top of that we need the user agent which isn't stored in recentchanges, and finally an efficient way to query for an IP range, which is accomplished with the cuc_ip_hex column (hence why we're piggybacking off of it for T145912)

I don't think this is well thought, and I see some proposals here are not properly designed, at least from the point of view of WMF. Comment should be store on the comment store, there is a lot of redundant data to recentchanges, etc.- I don't think this should go on without a rethinking- at least from the WMF perspective (I have no opinion on mediawiki for non-WMF setups).

As I'm sure you recall

Don't be so sure :-), but kudos to me from the past, I guess. Thank you, I was just reviewing old requests, it is nice things are ok now.