Change Details

In T416170 we concluded to change the Wikibase GraphQL `searchItems` backend to use MariaDB instead of the current CirrusSearch-based approach. Doing so will allow us to implement additional features like range queries and sorting more easily. The general plan is to store statement data in secondary database tables, similar to what we already do for labels, descriptions and aliases of statements and properties. ## Design the schema Since there is a large amount of data involved, getting the schema right from the start is important. We can come up with an initial suggestion and outline the use cases, and then request for DBA feedback. Guidelines for creating new tables can be found here: https://wikitech.wikimedia.org/wiki/Creating_new_tables Brief description of the schema that worked well for [the prototype](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/1301375): * Main table * Contains one row for every stored statement * Columns: statement id (PK), subject id, property id, rank, value type * Indexes * Subject id * Property id * Per-type tables (at least one per value type, possibly dedicated tables for some data types) * Contain one row for every statement that has a value (i.e. not novalue/somevalue) * Columns * Statement id (PK) * Subject id (repeated for better indexing) * Property id (repeated for better indexing) * Value: depending on the type, this is split across multiple columns for complex values, and including normalized values for types that support conversion like quantity and time * Indexes * Property id, value * A value leading index could be added to query by value alone ## Populating the tables We need to create scripts to populate the tables, and then we need to run them on wikidata.org. ### Create the scripts These should probably work similar to the ones for the secondary terms store, e.g. [rebuildItemTerms](https://gerrit.wikimedia.org/g/mediawiki/extensions/Wikibase/+/727bf99ebd4939d6258b6a23747d8333f4d2a3e3/repo/maintenance/rebuildItemTerms.php#26) and have similar CLI options: * from-id/to-id to iterate over * batch-size * sleep time * (?) data/value type(s) to determine what kinds of statements to store ### Populate tables on wikidata.org To fill the new tables with data of all existing items, we need to run the maintenance scripts created in the previous step for wikidata.org. This will take quite some time, depending on the amount of data involved, i.e. whether we want to store all statements, or only a subset. For comparison, initially populating the secondary terms tables took several months. ## Updating logic Once the tables are populated, every relevant item edit should result in an update to the secondary statements store. The exact logic is TBD, but we can get inspiration from how terms are stored [in deferred updates](https://gerrit.wikimedia.org/g/mediawiki/extensions/Wikibase/+/727bf99ebd4939d6258b6a23747d8333f4d2a3e3/repo/includes/Content/ItemHandler.php#167). ## Migrate GraphQL `searchItems` to query MariaDB instead At this point we can create a new search engine implementation that queries MariaDB instead of CirrusSearch. We will probably keep both implementations initially with the new implementation behind a config flag, so that we can control the point at which we switch it over and roll it back if something goes wrong.

In T416170 we concluded to change the Wikibase GraphQL `searchItems` backend to use MariaDB instead of the current CirrusSearch-based approach. Doing so will allow us to implement additional features like range queries and sorting more easily. The general plan is to store statement data in secondary database tables, similar to what we already do for labels, descriptions and aliases of statements and properties. ## Design the schema See {T434870}. ## Populating the tables We need to create scripts to populate the tables, and then we need to run them on wikidata.org. ### Create the scripts These should probably work similar to the ones for the secondary terms store, e.g. [rebuildItemTerms](https://gerrit.wikimedia.org/g/mediawiki/extensions/Wikibase/+/727bf99ebd4939d6258b6a23747d8333f4d2a3e3/repo/maintenance/rebuildItemTerms.php#26) and have similar CLI options: * from-id/to-id to iterate over * batch-size * sleep time * (?) data/value type(s) to determine what kinds of statements to store ### Populate tables on wikidata.org To fill the new tables with data of all existing items, we need to run the maintenance scripts created in the previous step for wikidata.org. This will take quite some time, depending on the amount of data involved, i.e. whether we want to store all statements, or only a subset. For comparison, initially populating the secondary terms tables took several months. ## Updating logic Once the tables are populated, every relevant item edit should result in an update to the secondary statements store. The exact logic is TBD, but we can get inspiration from how terms are stored [in deferred updates](https://gerrit.wikimedia.org/g/mediawiki/extensions/Wikibase/+/727bf99ebd4939d6258b6a23747d8333f4d2a3e3/repo/includes/Content/ItemHandler.php#167). ## Migrate GraphQL `searchItems` to query MariaDB instead At this point we can create a new search engine implementation that queries MariaDB instead of CirrusSearch. We will probably keep both implementations initially with the new implementation behind a config flag, so that we can control the point at which we switch it over and roll it back if something goes wrong.

In T416170 we concluded to change the Wikibase GraphQL `searchItems` backend to use MariaDB instead of the current CirrusSearch-based approach. Doing so will allow us to implement additional features like range queries and sorting more easily. The general plan is to store statement data in secondary database tables, similar to what we already do for labels, descriptions and aliases of statements and properties. ## Design the schema Since there is a large amount of data involved, getting the schema right from the start is important. We can come up with an initial suggestion and outline the use cases, and then request for DBA feedback. Guidelines for creating new tables can be found here: https://wikitech.wikimedia.org/wiki/Creating_new_tables Brief description of the schema that worked well for [the prototype](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/1301375): * Main table * Contains one row for every stored statement * Columns: statement id (PK), subject id, property id, rank, value type * Indexes * Subject id * Property id * Per-type tables (at least one per value type, possibly dedicated tables for some data types) * Contain one row for every statement that has a value (i.e. not novalue/somevalue) * Columns * Statement id (PK) * Subject id (repeated for better indexing) * Property id (repeated for better indexing) * Value: depending on the type, this is split across multiple columns for complex values, and including normalized values for types that support conversion like quantity and time * Indexes * Property id, value * A value leading index could be added to query by value aloneSee {T434870}. ## Populating the tables We need to create scripts to populate the tables, and then we need to run them on wikidata.org. ### Create the scripts These should probably work similar to the ones for the secondary terms store, e.g. [rebuildItemTerms](https://gerrit.wikimedia.org/g/mediawiki/extensions/Wikibase/+/727bf99ebd4939d6258b6a23747d8333f4d2a3e3/repo/maintenance/rebuildItemTerms.php#26) and have similar CLI options: * from-id/to-id to iterate over * batch-size * sleep time * (?) data/value type(s) to determine what kinds of statements to store ### Populate tables on wikidata.org To fill the new tables with data of all existing items, we need to run the maintenance scripts created in the previous step for wikidata.org. This will take quite some time, depending on the amount of data involved, i.e. whether we want to store all statements, or only a subset. For comparison, initially populating the secondary terms tables took several months. ## Updating logic Once the tables are populated, every relevant item edit should result in an update to the secondary statements store. The exact logic is TBD, but we can get inspiration from how terms are stored [in deferred updates](https://gerrit.wikimedia.org/g/mediawiki/extensions/Wikibase/+/727bf99ebd4939d6258b6a23747d8333f4d2a3e3/repo/includes/Content/ItemHandler.php#167). ## Migrate GraphQL `searchItems` to query MariaDB instead At this point we can create a new search engine implementation that queries MariaDB instead of CirrusSearch. We will probably keep both implementations initially with the new implementation behind a config flag, so that we can control the point at which we switch it over and roll it back if something goes wrong.