Is it a good practice to store auth session in the database?

Question

I created a login system that in addition to being used on a website, will also be used in mobile applications.

As on cell phones I want to keep the user logged in until he chooses to log out, I did not use the authentication for sessions in PHP.

So I thought it would be better to store the login sessions in the database, for each user request, to verify if the authentication token is still valid.

But I don't know if this is a good practice. Since every time the user updates the screen in the browser, or sends any application request to the system, he will make a query to verify that the login is still active and then make another query to search for what the user requested.

My concern is whether this will become too slow, for a system that could have between 900 million and 1,5 billion users, since the database will have many more requests and verification queries in addition to the normal query requested by the user.

Below is the current structure of my database. I would also like tips if my structure is very wrong.

Answer 1

Yes, it's a good practice to store session information in an application's main transactional database. A great many web applications work this way at large scale.

If you have the skills to do so, you might consider setting things up so session information is stored in a separate database that's not dependent on data in your transactional database. This separate database needs just one table:

 login_token   PK
 key           PK
 value

The session_id is the value of the login_token session cookie, a large hard-to-guess random value your web app sends to each logged-in user's browser. For example, if my user id were 100054 the session table might contain these rows for me.

 2EwZzPJdigVlrwtkFC5qoe97YE0EBddJ user_id    10054
 2EwZzPJdigVlrwtkFC5qoe97YE0EBddJ user_name  ojones

Why use this key/value design? It is easily ported to a high-performance key/value storage system like Redis. It's simple. And, to log me off and kill my session all you need is

     DELETE FROM session WHERE login_token = '2EwZzPJdigVlrwtkFC5qoe97YE0EBddJ'

(You asked for feedback on your table design. Here is mine: Use INT or BIGINT values for primary keys in tables you expect to become large. VARCHAR values are a poor choice for primary keys because index lookup and row insertion are substantially slower. CHAR(n) values are a slightly better choice, but still slower than integers. The session table only covers presently logged in users.)

And, I'll repeat my comment. Don't waste too much time today on designing your new system so it can run at the scale of Twitter or Facebook (~ 10**9 users). At this stage of your project, you cannot know where your performance bottlenecks will lie when you run at that scale. And it will take you a decade, at the very least, to get that many users. By then you'll have hundreds of developers working on your system. If you hire them wisely, most of them will be smarter than you.

How do I know these things? Experience, wasted time, and systems that did not scale up even when I designed them to do that.