### Description of the problem
See parent task T54777. In short, several extensions are setting user options from `LocalUserCreated` to provide users with different defaults based on their user type and time of registration (see more detailed list in **Option 1**). This has lead to accumulate millions of rows in the user_properties table and it will keep growing for every new registered user until we implement a solution for it.
### Proposed solutions
**Option 1**
Splitting out @tstarling's proposal from T54777#7724456 into it's own task so it's easier to reference it:
> I'm thinking about this in terms of the cost of deployment of IP masking. It's proposed to have a user and globaluser row for "temporary" accounts, and there will be a lot of temporary accounts. But probably most extensions will want to treat temporary accounts the same as anonymous users, leaving them with default preferences. Delivering welcome banners and the like is probably best done after the user explicitly creates an account.
>
> So I'm not sure we really need this, but I still had better do a brain dump in case it's needed now or in the future.
>
> Currently several extensions are setting user options from LocalUserCreated. Here's what they are trying to achieve:
>
> * The default preference value for new users may be a value different from the default for existing users.
> * The default for new users may change when a new version of the extension is deployed.
> * The default for new users may change when a configuration variable changes.
> * New users may be assigned to a random A/B test bucket and then receive different preferences depending on their bucket.
> * Possibly the default should be set based on global newness rather than local account autocreation.
> * The default for new users may later become the default for everyone, or vice versa.
>
> My idea for efficiently achieving those requirements is to have extensions statically declare new user preferences. Have a new table which holds these declarations, say user_property_default:
>
> ```lang=sql
> CREATE TABLE user_property_default (
> upd_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
> upd_property VARBINARY(255) NOT NULL,
> upd_user_type INT UNSIGNED NOT NULL,
> upd_min_user INT UNSIGNED NOT NULL,
> upd_min_bucket INT UNSIGNED NOT NULL,
> upd_value BLOB,
> PRIMARY KEY (upd_id),
> UNIQUE KEY (upd_property, upd_user_type, upd_min_user, upd_min_bucket)
> );
> ```
>
> To figure out a default user preference value for a given user, you search for a user_property_default row with a minimum user less than the given user_id:
>
> ```lang=sql
> SELECT upd_value FROM user_property_default
> WHERE upd_property='$prefname'
> AND upd_user_type='$my_type'
> AND upd_min_user <= '$my_id'
> AND upd_min_bucket <= '$my_bucket'
> ORDER BY upd_min_user DESC, upd_min_bucket DESC
> LIMIT 1;
> ```
>
> When a new user is created, the configured declaration is compared against the current (highest upd_min_user) value in the database for each preference. If the declaration has changed, a new row is inserted into the database with upd_min_user being the user_id of the user being created.
>
> The user bucket would just be "hash(user_id) mod 1000" or something similar. Most user_property_default rows would have upd_min_bucket=0 and so would catch all buckets. If you insert a row with upd_min_bucket=990 then it will only take effect for 1% of users. There would always be a fallback with upd_min_bucket=0 so that the search doesn't continue back to previous upd_min_user values.
>
> upd_user_type would be a small integer to allow the default to depend on autocreate flag and "temporary" status.
>
> Sprinkle in some caching and stampede protection and I think it would mostly work. If the declared default changed depending on the request parameters, it would cause a bit of a mess, and preventing that would come down to code review.
(See the last few comments in {T54777} for further discussion, including whether the table should be normalized, whether we could use configuration instead of DB rows, or whether the functionality could be simulated based on user ID hashes.)
**Option 2**
Splitting out @Urbanecm_WMF 's proposal from T321527#9182033:
>>! In T321527#9182033, @Urbanecm_WMF wrote:
> Thought: Maybe it would make sense to instead have time-based defaults set in the configuration (via `UserGetDefaultOptions`, for example), and when the time is to disable A/B testing, simply make a code change to remove the registration-based defaults and be done? Granted, the timestamp of A/B testing start might not (and probably will not be) same in all installations, but even then, the default options can be managed from a MW config variable as well. I imagine this can live in `extension.json`, similar to how defaults are configured now. This can look like this:
>
> ```lang=json
> {
> ...,
> "DefaultUserOptions": {
> "same-default-for-everyone": "default value for all users",
> "bucketed-defaults": "fallback default value"
> },
> "BucketedDefaultUserOptions": {
> "bucketed-defaults": {
> "20080101000000": "default value for users who registered after January 01, 2008",
> "20230101000000": "default value for users who registered after January 02, 2023"
> }
> }
> }
> ```
#### Some potential use cases:
* {T38316}
* {T40796}
* {T288161}
* {T300919}
* Echo (T54777#9139837; see also {T333531})
* {T304538} (partially, see T304538#7917961)
#### Is this request urgent or time sensitive?
Yes. This issue is in production and it will likely start causing DB malfunctioning in the wikis with the most registered users.