Page MenuHomePhabricator

Okay to use `user_properties` table for high frequency/volume options?
Closed, ResolvedPublic

Description

I've been working on this task T226068 and had a question related to it for the DBA team.

In short, we are running a promo campaign on the mobile site (e.g. https://en.m.wikipedia.org) for a new feature. We want to show a drawer promoting the feature to each logged in user on the mobile site, but they should only see the drawer once. One of the solutions we were considering was to create a record in the user_properties table to mark when a logged in user has seen the drawer so that they would not see it again.

However, this would effectively mean that there would be a record created for each logged in user of the mobile site. Would adding this number of records (also considering frequency) to the user_properties table be okay?

Event Timeline

Marostegui added a subscriber: Marostegui.

With "record" you mean a new column on the user_properties table or a new row?
The table right now looks like:

CREATE TABLE `user_properties` (
  `up_user` int(10) unsigned NOT NULL,
  `up_property` varbinary(255) NOT NULL DEFAULT '',
  `up_value` blob,
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `user_properties_property` (`up_property`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

So I am not sure what you exactly mean with a new record there.

In general, I am not sure maintaining such a temporary flag on the database for a concrete period of time is a good idea, I would consider some other alternatives if possible. The user_properties table on enwiki (to provide a table on a big wiki) is already 21GB disk on size, which is not huge, but already quite big.
What would happen once this project is done? Would you clean up all the new rows/columns?

@Marostegui Thank you for your response! I should add that for this particular ticket, we are using localStorage instead of the user_properties table to mark when the user has seen the drawer. However, the reason I ask this question still is because the use of the user_properties table was definitely considered, and I think it would be helpful to know its allowed usage in the future should similar tickets arise as I'm not clear on its allowed usage. So thank you for helping clarify this.

With "record" you mean a new column on the user_properties table or a new row?

A row would be inserted into the user_properties table for each logged in user who had seen the drawer. For example it, for a user with id=1, it might look like:

INSERT INTO user_properties (up_user, up_property, up_value) VALUES ( '1', 'amc-outreach-drawer-campaign', '~');

This presence of this row (value of up_value doesn't really matter) in the user_properties table would be used to mark that the user had seen the drawer so that we wouldn't show the drawer more than once to the same user.

What would happen once this project is done? Would you clean up all the new rows/columns?

My team and I had not discussed that, but I think cleaning the rows after the campaign would be a good idea.

In general, I am not sure maintaining such a temporary flag on the database for a concrete period of time is a good idea, I would consider some other alternatives if possible

Besides using localStorage which has flaws of its own (e.g. it is tied to the user's device/browser instead of to the user's account so if they use another browser/device they will see the drawer again; the use of user_properties is specific to the user's account ), do you have any alternative recommendations of a key/value store that would be better suited for this situation?

@Marostegui Thank you for your response! I should add that for this particular ticket, we are using localStorage instead of the user_properties table to mark when the user has seen the drawer. However, the reason I ask this question still is because the use of the user_properties table was definitely considered, and I think it would be helpful to know its allowed usage in the future should similar tickets arise as I'm not clear on its allowed usage. So thank you for helping clarify this.

I am sorry, but I don't really know what localStorage refer to, can you provide more details?.
My only advice is not to use the user_properties table for something that is temporal and that will be running just for a few days/weeks, Specially if it won't be cleaned up.
Also, how will this be deployed? Will the INSERTs happen once the user logs in? How will this be rolled out? I am trying to make sure there won't be a huge amount of inserts at once if this gets enabled at once and all the already logged users have to log a row into a given table.

Besides using localStorage which has flaws of its own (e.g. it is tied to the user's device/browser instead of to the user's account so if they use another browser/device they will see the drawer again; the use of user_properties is specific to the user's account ), do you have any alternative recommendations of a key/value store that would be better suited for this situation?

I am not sure if the user_properties table was designed to hold data like the one you might want to store for temporary projects, maybe someone with more expertise and context about MW can help here @Anomie or @Krinkle maybe?

I am sorry, but I don't really know what localStorage refer to, can you provide more details?

localStorage is a web api to store data on the user's computer (similar to cookies).

Also, how will this be deployed? Will the INSERTs happen once the user logs in? How will this be rolled out? I am trying to make sure there won't be a huge amount of inserts at once if this gets enabled at once and all the already logged users have to log a row into a given table.

If we had used the user_properties table, the plan was to have a config flag be in control of turning it on. When the drawer shows, an ajax request would be sent to the backend and that would then perform the INSERT. Thus, the INSERT could happen in one of the following scenarios after the feature was turned on :

  1. An already logged in user loads a page on the mobile site
  2. An anonymous user logs into their account on the mobile site

Note: The INSERT could only happen once per user so only one of those scenarios would trigger the INSERT per user.

Removing the DBA tag as there is nothing else for us here - but I will remain subscribed just in case.

I am not sure if the user_properties table was designed to hold data like the one you might want to store for temporary projects, maybe someone with more expertise and context about MW can help here @Anomie or @Krinkle maybe?

user_properties is designed to hold things like user preferences, so this use isn't far off. The main drawbacks seem to be those already identified: a flood of inserts (via API calls to action=options, presumably) as many readers encounter the feature when it's first deployed, and the unlikelihood of the obsolete rows being cleaned up once the outreach is done.

There's also the possibility that using Special:Preferences/reset would reset this flag too, but that seems a minor concern.

nray claimed this task.

We went with using localStorage for AMC Outreach (awhile ago) so I think this ticket can be closed now. I think some of the questions raised here are conceptually similar enough to our new work on the Vector sidebar as part of the Desktop Improvements project T246427 which I will raise on that task. Thank you everyone for your participation!