Pinging a few people who may need to be aware of this: @Magnus @Pintoch @Multichill @matej_suchanek @Pasleim @Envlh @Ricordisamoa @MusikAnimal
Feel free to ping more people if you think they should be aware of this change!

Thanks for the ping! I am not using wb_terms in any of my projects. Good luck with the migration, it looks epic.

Posted notice to cloud-announce and cloud mailing lists.

In T221764#5135252, @bd808 wrote:

Posted notice to cloud-announce and cloud mailing lists.

thanks @bd808

Instead of migrating to a different schema for the database terms storage, some tools could also use the API instead, e. g. action=wbgetentities&props=labels|descriptions|aliases (get all terms, or limit to some languages) or action=wbformatentities (format HTML for the user – includes support for lexemes, senses, and forms, which aren’t even available in wb_terms, and also handles language fallbacks out of the box). This should be more robust, possibly easier to work with, and hopefully not significantly less performant for most tools (though some might still have to use the database).

Thanks for the ping! I don't use this table either, but it seems a huge and well prepared work. Good luck :-)

This breaks a couple of queries for me and at least one tool. Looking at the new schema: What happened with term_full_entity_id? Without the full entity id joining against the page related tables becomes impossibly slow. This query is already quite slow, but without the full entity id it will just break;

SELECT term_text AS painter, COUNT(term_text) AS paintercount FROM page
JOIN pagelinks AS colllink ON page_id=colllink.pl_from AND
colllink.pl_from_namespace=0 AND
colllink.pl_namespace=0 AND colllink.pl_title='Q18600731'
LEFT JOIN pagelinks AS creatorlink ON page_id=creatorlink.pl_from AND
creatorlink.pl_from_namespace=0 AND
creatorlink.pl_namespace=120 AND
creatorlink.pl_title='P170'
JOIN wb_terms ON page_title=term_full_entity_id AND
term_entity_type='item' AND
term_language='en' AND
term_type='description' AND term_text LIKE 'painting by %'
WHERE
page_namespace=0 AND
page_is_redirect=0 AND
creatorlink.pl_from IS NULL
GROUP BY term_text
ORDER BY COUNT(term_text) DESC, painter
LIMIT 300;

@Multichill I think this SPARQL query is more or less equivalent? (Except for the filtering out of “onbekend”, which I added as a bonus.)

SELECT ?itemDescription (COUNT(?item) AS ?count) WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "haswbstatement:P195=Q18600731 \"painting by\"";
                    mwapi:srlimit "max".
    ?title wikibase:apiOutput mwapi:title.
    # ?item wikibase:apiOutputItem mwapi:item. # causes an exception for some reason?
  }
  BIND(IRI(CONCAT(STR(wd:), ?title)) AS ?item) # workaround
  MINUS { ?item wdt:P170 ?creator. }
  MINUS { ?item schema:description "painting by onbekend"@en. } # “unknown”
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?itemDescription
ORDER BY DESC(?count)
LIMIT 300

I’ve dumped the current results at P8467 for your convenience; the query currently takes around 45 seconds. (It would probably be even faster if WikibaseCirrusSearch had a keyword for searching in descriptions, but currently it only indexes labels specially.)

Still, the fact that it’s no longer possible to join the page table on the terms might be important for other things. I’m not sure how much that’s currently accounted for.

That's assuming that this query is stand alone. It's combining the results with another SQL database (the mix'n'match one).
I'm quite unhappy about you guys changing tables again and dropping the full entity. Last time you did that, several things broke and the team completely ignored T114904 . This is very demotivating.

@Multichill

We understand this requires some work from tool builders. We unfortunately were forced into this situation as the current table design has reached its performance limits.

In the new schema design, we tried to follow, as much as possible, best practices on database schema design for maximum performance and space usage, which lead to different decisions, including two relevant ones to your concerns about the full entity id:

1. After normalizing the schema to avoid duplication as much as possible, we decided to link normalized terms (wbt_term_in_lang table) to entities in a table-per-entity basis (hence wbt_item_terms and wbt_property_terms, and later other entity types will have their own tables).
   
   The reason behind this decision is that different entity types might have orders of magnitude difference in their count (and their terms count). For example, properties now constitute about 0.001% of total entities compared to items in wikidata. If we put all of them in only one table, this might lead again to both duplication (due to a column that will be added then to identify the entity type) as well as limitation on possible optimizations we might want to do for certain entity types should they again reach some limits.

2. What followed is to that keeping full entity ids in each of these tables is redundant, and will only cost us more space usage that can build up to a big amount and can be saved easily.

If full entity ids are desired as an outcome of a query (in case they need to be joined with other tables/sub-queries), you can still build up the full id like:

SELECT CONCAT( 'Q', wbt_item_terms.wbit_item_id ), ...
FROM wbt_item_terms
...

We strongly advise not to bind directly to the new schema or any other database table whenever there are other alternatives through Wikidata APIs. This will increase the stability of your tools as Wikidata APIs are designed for public use while db schema is not.

In T221764#5155957, @alaa_wmde wrote:

SELECT CONCAT( 'Q', wbt_item_terms.wbit_item_id ), ...
FROM wbt_item_terms
...

That completely kills any performance left. I you look at my example query, you'll notice you're even not addressing my concern because I'm using it in a join.

@Multichill

I'm sorry I didn't address your exact query directly. To address this issue concretely, I created another task in the Backlog T222602 (subscribed you to it too).
We will check that solution in the test node and get back with some results.

Multichill I think a solution to your problems can be done on wikireplicas (or a similar level)- wikireplicas don't need to have the same structure than production, and additional tables or indexes can be done there. I don't think internal production needs should cater tool needs. That doesn't mean tool needs should not be provided, on the contrary, better query methods should be provided but I think they are different problems that should not be confused with one another. Better APIs should be made available with a stable interface, I 100% agree with that, but the database cannot be an interface that is guaranteed to be stable.

Note this lack of stability is documented at https://wikitech.wikimedia.org/wiki/Help:Toolforge/Database#Stability_of_the_mediawiki_database_schema While the database schema on HEAD is documented, there is close to no documentation about the actual database schema deployed.

I would prefer to work together to define and maintain a stable and flexible api for power users (including tools) rather than trying to keep up with all the changes every time there is an internal-only optimization. wb_terms is close to 1TB of data at the moment, no matter how it is done, querying that is not going to be fast.

Here's an update regarding the dates of test environment and migration of wb_terms table replacement solution.

Due to various complications that the developers in the Wikidata team have been working on solving over the last few weeks, we unfortunately will have to push the dates for when a test environment for tools builders will be ready, which was supposed to be ready today, and the following dates for starting migration of wb_terms data into the new schema in production.

The new dates are:

• 29th of May: Test environment for tool builders will be ready
• 12th of June: Property Terms migration starts
• 19th of June: Read property terms from new schema on Wikidata
• 26th of June: Item terms migration begins
• 3rd of July: Read item terms from one of the two schemas (as explained in this task)

As a reminder, if you want to discuss with the developers, ask questions and get help in order to update your tools, you can join the IRC Mediawiki meeting, today at 15:00 UTC on #wikimedia-tech.

Update: the test system is now in place: https://wikidata-termstore.wmflabs.org/index.php/Main_Page
To access to its database, you need to get the access granted. Feel free to add a comment to this ticket if you need it.

Hello all,

This is an update regarding the dates of migration.

When we originally planned the last schedule for migration in production, we must have overlooked the planned offsite for SRE (Software Reliability Engineering) team that overlaps with the first week of it (specifically, beginning to migrate property terms on 12th of June). We unfortunately will have to push the dates for starting migration of wb_terms data into the new schema in production another week into the future from the current dates.

The new dates are:

• 19th of June: Property Terms migration starts
• 26th of June: Read property terms from new schema on Wikidata
• 3rd of July: Item terms migration begins
• 10th of July: Read item terms from one of the two schemas

We apologize and thank you for your understanding!

Update: we are about to start migrating property terms this week. The original planned date was 19th of June. Due to WMF US Staff holiday, there will be no deployments today. Therefore, we are going to switch the migration on (write both stage) of property terms one day later than planned, on Thursday the 20th of June. All other dates regarding migration remain the same for the moment.

Thanks for the update @Lea_Lacroix_WMDE - I remember I talked to @alaa_wmde about waiting until we had the new db primary master in place for wikidata on some tasks. I have only been able to find this T219145#5088395 but I reckon we spoke about it somewhere else too.
By reading T221765 I guess this first migration will be only the 1% of all the items, and the rest are still marked as TBD, right?
Once we have swapped the current db master, we can proceed with the rest of the migration, is that still the plan?

Thanks!

Hey @Marostegui

By reading T221765 I guess this first migration will be only the 1% of all the items, and the rest are still marked as TBD, right?
Once we have swapped the current db master, we can proceed with the rest of the migration, is that still the plan?

Yes and yes :)

We migrate only first 1% of items. That will require ~17GB of extra disk space, which we agreed on being acceptable before having the new db primary master as we discussed on T220480.
The remaining 99% we will wait on until we have the new master, as that will require an extra of ~175GB which was not acceptable in the current situation (as discussed in T219145).

Is that still good to go from your side?

Hey @alaa_wmde!

Thanks for confirming, I just wanted to make sure the planning didn't change, as there have been many migration subtasks and it was hard to keep up with all the plans :-)
I will try to get the db master changed scheduled for July

In T221764#5268944, @Marostegui wrote:

Hey @alaa_wmde!

Thanks for confirming, I just wanted to make sure the planning didn't change, as there have been many migration subtasks and it was hard to keep up with all the plans :-)
I will try to get the db master changed scheduled for July

Yes it was quite an effort to keep up with things all over the place :D we gotta improve on that, probably by breaking down changes down into much smaller iterations.

It would be really cool if db master is changed that soon .. even August would be great. The sooner that happens, the sooner we drop wb_terms finally :)

In T221764#5267856, @Lea_Lacroix_WMDE wrote:

Update: we are about to start migrating property terms this week. The original planned date was 19th of June. Due to WMF US Staff holiday, there will be no deployments today. Therefore, we are going to switch the migration on (write both stage) of property terms one day later than planned, on Thursday the 20th of June. All other dates regarding migration remain the same for the moment.

Hi all! Apologies for another change of plans. We have only started migrating property terms on test.wikidata.org today as planned. Due to issues with the current deployment train, the code created in the last two weeks has not yet been deployed on Wikidata. As the current train carries including parts critical for the migration, we cannot start the migration on production wikdata servers yet.
We will start the migration of property terms on Wikidata on Monday June, 24th. Migration on test.wikidata.org has been running without issues so far, so we are not expecting inconveniences.

Other migration dates are not expected to be affected by this delay and remain as recently announced by @Lea_Lacroix_WMDE

Update regarding migration plans of terms on wikidata production

Hello all,

We have successfully put Wikidata production in the stage where property terms are stored in and written to both the old store (wb_terms table) and the new replacement store. Retrieving property terms is still being done using the old store.

The previously announced dates are no longer effective. No changes to tools are needed yet.

We will run it in several stages and there will be separate announcements regarding those stages to announce and inform about how to deal with it, in case it affects your work.

We will announce the dates for when the tools have to switch to reading from the new store, for property terms and for item terms separately, at least 6 weeks before we do the switch in production to give tool builders enough time to update and test their tools.