I haven't had time to go through this but from the first look: Seven tables is way too many. Each new table adds overhead in our production specially that a lot of them doesn't seem to be needed? e.g. the counter table can go away and be replaced with an auto_increment id in the wishes and focus areas? Or phab tags table can be turned into a blob field in the respective tables? I'm also not sure about translation ones why simply not relying on page table and prefix index search there?

We definitely can't deploy this to all wikis, it will bring down s3. So I want to have a commitment that this won't ever be deployed to all wikis.

In T403559#11143892, @Ladsgroup wrote:

Seven tables is way too many. Each new table adds overhead in our production specially that a lot of them doesn't seem to be needed? e.g. the counter table can go away and be replaced with an auto_increment id in the wishes and focus areas?

It is identical to how WikiBase works. The issue is we need the generated ID before saving because it's used in the page title. We can't save the entity first then create the page, because the page itself is what dictates the entity, since in this extension, all data comes from wikitext.

Or phab tags table can be turned into a blob field in the respective tables?

We want to eventually be able to search via Phab tasks. That was the reason behind this normalization.

I'm also not sure about translation ones why simply not relying on page table and prefix index search there?

Similar to above, we want to be able to efficiently search translations. This is one the major points of us writing the extension. Extension:Translate is only a soft dependency, but even with it, we can't search through translations quickly enough. @Nikerabbit was in the room when we designed this part of the schema (though I don't want to imply he signed off or was aware of any further implementation details).

We definitely can't deploy this to all wikis, it will bring down s3. So I want to have a commitment that this won't ever be deployed to all wikis.

No problem :) We have no plans whatsoever to release anywhere but Meta. Beyond that, it is all theoretical and would be much further down the road.

If it makes things move along more quickly, it's simple enough for us to get rid of the Phab tasks table, if that helps. Though this would mean we'd need to fetch the values from wikitext instead, so the query mentioned in the task would involve N other queries to fetch wikipage content for each entity that is returned (inefficient).

Hi @MusikAnimal, Thanks for opening this task. We will certainly will take care of this task. Just wanted to call out that currently Data persistence team is understaffed and some being on-call this week. We already have planned prioritized work for this week and next. We will evaluate if there is any of the planned work can be paused to take on this early next week. For prioritization purposes. it would be really helpful if you can share some details on the hard timeline of 8th September in the task.

In general for any future requests, it would be really helpful if we can have these kind of tasks raised at least with 2 week notice for us to prioritize in our upcoming sprint and take care of it in timely manner. Thanks for your understanding.

In T403559#11144930, @Kappakayala wrote:

Hi @MusikAnimal, Thanks for opening this task. We will certainly will take care of this task. Just wanted to call out that currently Data persistence team is understaffed and some being on-call this week. We already have planned prioritized work for this week and next. We will evaluate if there is any of the planned work can be paused to take on this early next week. For prioritization purposes. it would be really helpful if you can share some details on the hard timeline of 8th September in the task.

September 8 was our goal, but I have learned today this has not been conveyed to our users yet. So I think – and especially out of respect for Data Persistence – we will need to delay this. I can't give a firm answer right now but perhaps we could aim for sometime by the end of September?

In general for any future requests, it would be really helpful if we can have these kind of tasks raised at least with 2 week notice for us to prioritize in our upcoming sprint and take care of it in timely manner. Thanks for your understanding.

Absolutely! I told Amir directly: our entire team, including two principle and three staff engineers, were apparently unaware of https://wikitech.wikimedia.org/wiki/Creating_new_tables. What I recall from the past is for "schema changes, you need a DBA; simple creation of new and relatively low-use tables, DBA invervention is not strictly required". Obviously that was wrong, and for that I deeply apologize. I have a meeting with Amir every month. This should have come up then. I know it sounds odd, but I was actually in such a comfort zone that I guess I was sort of excluding Amir as I didn't want to waste his time with our silly Meta-only schema 😛 Lesson learned!

The ideal time to contact DBAs would be to do it before schema is merged and any line of code relying on the schema is written (plus at least two weeks for the DBAs to find the time to review). That way, requesting changes would be much easier. For example, here are three different teams contacting to review their schema before anything is merged in this week: T402391: Suggested investigations: Create database tables, T402659: DBA review: new table for associating edits and events and T402231: [Reading Lists] Create hidden user preference to scope Reading Lists web UI to specific logged-in users. And all three had their schema changed after feedback.

I understand that the schema is well thought but the reason DBAs should get involved is us having the bigger picture on the infrastructure and our nuances. For example, I think the counter table should be avoided and it would have been much better to create a general purpose one in core and reuse that because I've been seeing many counter tables being requested and I would like to avoid creating more (but devs understandably wouldn't know that). Or for example, many devs are unaware of the overhead of creating new tables on the infra. No matter how small. My point is that it's not really a DBA sign-off, it's a design discussion involving many trade-offs.

On availability: I'm oncall this week and next. Plus being the only DBA with a pretty large backlog of maintenance work. The nature of my work is unpredictable (I might get pulled into a massive incident for weeks albeit hopefully unlikely to happen in the coming weeks) but as long as we don't have major issues I will do my best to prioritize this review. I want to say the Monday deadline is impossible.

I want to note that, you might have to change a lot of code so it's going to be some decent amount of work for you too. I understand this is suboptimal but making changes after the schema is deployed is much much more work since it'll involve data migration too.

Understood! We will never make this mistake again. We've been updating documentation too so others don't get stuck in this same situation, i.e. on mw:MediaWiki database policy and mw:Manual:Developing extensions. I think many of us, myself included, tend to naturally go to mediawiki.org for best practices, so it doesn't hurt to add more links to clarify WMF-specific processes (which justifiably are documented on wikitech and not mediawiki.org). And I guess we'll just get in the habit of checking Wikitech more often when searching for documentation.

I have removed communityrequests_phab_tasks from the list of tables. We had no immediate plans to make use of it, anyway. If/when we do, I imagine for simple searches we could instead query iwlinks to search for [[phab: (our extension enforces use of the interwiki prefix) and filter to pages under Community Wishlist/Wishes/, effectively accomplishing the same query but with existing tables.

I think the counter table should be avoided and it would have been much better to create a general purpose one in core and reuse that

That is a good idea! I would be interested in working on it, too. Do you recommend we work on this now? Our implementation is identical to WikiBase, which has been battle-tested, so I am semi-confident we can get something in Core in a reasonable amount of time that will work for any extension wanting to do the same thing. I don't know if we'd be able to switch WikiBase to use the new Core table, though. That would likely need to be a separate project.

Or for example, many devs are unaware of the overhead of creating new tables on the infra. No matter how small.

I must say I am genuinely confused about this. I assume there's a trade-off that must be carefully evaluated. For example, it is not feasible to do what we're trying to do using existing tables for translations, hence why we created two new tables for that.

If the main problem is too many tables, we could also combine communityrequests_wishes_translations and communityrequests_focus_areas_translations into the same table. Though, we may need to add an additional column to specify the entity type. Would that help?

I want to say the Monday deadline is impossible.

We have moved it to the end of September. Take your time :)

I want to note that, you might have to change a lot of code so it's going to be some decent amount of work for you too.

Well, we got ourselves into this mess, so it is what it is. I am eager to know what steps we can take to speed things up in hopes of us not only getting DBA approval but deploying by the end of the month.

You haven't mentioned any issue with communityrequests_tags yet. I really like it being a separate table, but I wanted to say we are also using MediaWiki categories, so although it would be a lot of very painful work, we can change it so that filtering by tag uses categorylinks instead. Then we don't need the new table at all. But, we were under the impression that once we have say 10K+ heavily tagged wishes, those complicated JOINs on categorylinks are going to be too slow (we'd need to do category unions akin to Petscan). Tim even joked that if we went this route, you might eventually come complaining to us, lol.

So keep in that mind as you review the schema.

This week I'll have to prepare the dbs for the dc switchover next week and since when it rains, it pours, we do have a major/complex bug I need to fix. So my responsiveness will be spotty.

For now, I do first impression reviews so it can move forward.

• Regarding counter, yes. Let's do it in core. My only note would be to add an extra column called shard (with smallint). So stuff like site_stats can move there too (for your case, the shard = 1) but some other counters can be sharded and only one row being picked at random (our of ten). The result will be just a sum of those rows.
  • Also there is a ticket for it already: T402854: Add a high-throughput counter system to MediaWiki
• Regarding tags, yeah let's keep the table. I'm against seven table but three or four should be fineTM
• My next suggestion is that if you could merge the translation tables to each other, just add a column on which type is.
  • I'm still not following exactly why these translations are needed. Can you just drop me a link to somewhere so I can read the reasoning? Sorry if you have posted it in details and I have missed it.
• I'm also thinking whether it'd be possible to merge communityrequests_wishes and communityrequests_focus_areas too. Some umbrella term: communityrequests_important_stuff for example (just kidding) and then add a tinyint column. That way if we want to introduce a third way or remove one type, it'd be easier and cleaner. The only downside would be that if the schema between these two is diverging too much, you'd have to resort to blob fields which is fine as long you're not indexing/filtering based on values in those blobs.
  • You could also make focus area simply a wish so you'd keep communityrequests_wishes but add is_focus column. or something like that.

I'm sorry if I missed something super obvious, I'm really struggling to find the time to examine the schema and the design in depth. Please 🐻 with me and my stupid questions/ideas.

Hehe all good! This feedback is great.

Regarding counter, yes. Let's do it in core. My only note would be to add an extra column called shard (with smallint). So stuff like site_stats can move there too (for your case, the shard = 1) but some other counters can be sharded and only one row being picked at random (our of ten). The result will be just a sum of those rows.

• Also there is a ticket for it already: T402854: Add a high-throughput counter system to MediaWiki

Great! We can continue discussion regarding this at T402854.

Regarding tags, yeah let's keep the table. I'm against seven table but three or four should be fineTM

Great, thanks :)

My next suggestion is that if you could merge the translation tables to each other, just add a column on which type is.

Can do!

I'm still not following exactly why these translations are needed. Can you just drop me a link to somewhere so I can read the reasoning? Sorry if you have posted it in details and I have missed it.

I don't know that I have a resource that is explanatory enough to link to, so I will try to explain here.

Product-wise, we basically want two things: (a) To be able to surface a list of wishes in the user's preferred language and following the language fallback chain (in case such translations don't exist in the preferred language), and (b) we want to be able to efficiently search them. There is also an engineering requirement of (c) that we should not have a hard dependency on Extension:Translate.

Extension:Translate has means to look up translations en masse and such, but we need to do so whilst also ensuring we only get those associated with the wishes or focus areas that match the search criteria. The translations themselves may also be part of the search criteria. Hence the solution was to store them in our own db.

@Samwilson, @Cparle or @tstarling may be able to provide more details. This part of our system was planned at the off-site way back in January.

I'm also thinking whether it'd be possible to merge communityrequests_wishes and communityrequests_focus_areas too. Some umbrella term: communityrequests_important_stuff for example (just kidding) and then add a tinyint column. That way if we want to introduce a third way or remove one type, it'd be easier and cleaner. The only downside would be that if the schema between these two is diverging too much, you'd have to resort to blob fields which is fine as long you're not indexing/filtering based on values in those blobs.

The schema is indeed a little different between the two, with wishes having two more columns than focus areas. Fortunately however there will comparatively very few FAs compared to wishes, so the NULL columns I assume should be fine.

You could also make focus area simply a wish so you'd keep communityrequests_wishes but add is_focus column. or something like that.

I think we could go with communityrequests_entities, though I do really like communityrequests_important_stuff 😛

I'll bring this back to the team and start filing tasks, and before we work on them, we'll run it by you again.

@Ladsgroup looking at the code here I'm not sure we need the join to page at all. If we don't need the join, and as a result can put these tables in x1, does that change your view on the table structure?

if you're deploying it to x1 and not joining with any core table, then feel free to keep translations table. x1 has much breathing room.

Okay, with those changes done (Thank you!). I have some final/minor thoughts:

• cr_vote_count and crc_value are INT, they could be mediumint which would be more than enough wrt max value: https://dev.mysql.com/doc/refman/8.4/en/integer-types.html but if you need to do a schema change for it, I suggest not doing it. One byte saved per row is really not worth a fully fleshed schema change.
• The translation table is using raw title which is can be fun given that pages can move and so on. Why not relying on page id instead? Similarly, I'm not fully understanding the reasoning behind crt_lang_title. You want to show the list of wishes based on language and ordered by title? Why? :D why not simply ordering based on entity id (where lang = xx)?

Two more notes just to double check:

• The tables needs to be created on metawiki database in x1.
• Please catalog them (T363581)

In T403559#11180516, @Ladsgroup wrote:

Okay, with those changes done (Thank you!). I have some final/minor thoughts:

• cr_vote_count and crc_value are INT, they could be mediumint which would be more than enough wrt max value: https://dev.mysql.com/doc/refman/8.4/en/integer-types.html but if you need to do a schema change for it, I suggest not doing it. One byte saved per row is really not worth a fully fleshed schema change.

It would require a schema change, but our schema isn't deployed to prod yet, so it's still "doable". If you still say it's OK to skip this, we will. There is also T404108 which needs to be fixed, otherwise we will have a lot of unused IDs.

• The translation table is using raw title which is can be fun given that pages can move and so on. Why not relying on page id instead? Similarly, I'm not fully understanding the reasoning behind crt_lang_title. You want to show the list of wishes based on language and ordered by title? Why? :D why not simply ordering based on entity id (where lang = xx)?

That is the translated entity title (i.e. "Title of my wish"), not the page title. Indeed we are using the page ID, which is the crt_entity column in the translation table. I assume that answers the second question, too. We want an alphabetically list of wishes in a specific language.

Two more notes just to double check:

• The tables needs to be created on metawiki database in x1.

I assume you'd handle this?

• Please catalog them (T363581)

Will do :) I'll attach the patch to this task.

Change #1188413 had a related patch set uploaded (by MusikAnimal; author: MusikAnimal):

[operations/puppet@production] tables-catalog: add CommunityRequests tables

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

Once the table catalog is merged, it has the sign off.

Change #1188413 merged by Ladsgroup:

[operations/puppet@production] tables-catalog: add CommunityRequests tables

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

It is merged. Feel free to create the tables.

Thank you! :) We'll look into it on Monday.