Background

The user_properties table has grown reaching three digits millions of rows (T54777). Part of the growth is due to the lack of a mechanism to set different default values depending on the user type or time of registration. The proposal in T321527 aims to solve this problem by providing a way to dynamically set default values.

Objective

Design the database table, and merge a patch that creates it.

Additional information

Answers questions from https://wikitech.wikimedia.org/wiki/Creating_new_tables:

• Should this table be replicated to wiki replicas (does it contain private data)?: Yes, it does not contain private data.
• Size of the table (number of rows expected): Counting on enwiki there are ~1000 different user properties; so the table could quickly achieve that number of rows x 3, in the worst scenario where each property default is different for each type of user.
• Expected growth per year: The table should grow moderately adding 1-3 rows for each new default value defined.
• Expected amount of queries: writes will happen on account creation and from maintenance servers when running on-demand script to modify properties of existing users. Reads: TBD (not sure yet, we will probably add some cache layer).
• The release plan for the feature: TBD not sure. We might want to test the overall feature in beta and a subset of wikis before a broader release.