#25399: Improve performance for user lookups by adding database index
-------------------------------------+-------------------------------------
Reporter: SimonSteinberger | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: contrib.auth | Version: master
Severity: Normal | Resolution: invalid
Keywords: user, database, | Triage Stage:
index | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by MarkusH):
* status: new => closed
* version: 1.8 => master
* resolution: => invalid
Old description:
> Having a user database table with millions of entries, even a simple
> look-up by username (or email address) takes a rather long time and
> causes a significant overhead on servers. For example, the Django
> database query for a user signing in looks roughly like this:
>
> SELECT * FROM "accounts_user" WHERE
> UPPER("accounts_user"."email"::text) = UPPER('[email protected]')
>
> The problem is, without a database index, the string transformation and
> comparison takes a lot of resources and even with a powerful
> infrastructure, such a query may take over a second when having a million
> or more users. With the growing user base, the query gets slower an
> slower.
>
> By adding a database index, the process takes only about a microsecond.
> Using PostgreSQL, we've simply added the index manually, which works
> fine:
>
> CREATE INDEX accounts_user_username_upper ON accounts_user
> (UPPER(username));
>
> Yet, this is a crucial part of most web applications and therefore, it
> would probably make sense if Django created the index automatically for
> the integrated User and AbstractUser classes. Or at least, the issue
> could be pointed out clearly in the docs.
New description:
Having a user database table with millions of entries, even a simple look-
up by username (or email address) takes a rather long time and causes a
significant overhead on servers. For example, the Django database query
for a user signing in looks roughly like this:
{{{
SELECT * FROM "accounts_user" WHERE UPPER("accounts_user"."email"::text) =
UPPER('[email protected]')
}}}
The problem is, without a database index, the string transformation and
comparison takes a lot of resources and even with a powerful
infrastructure, such a query may take over a second when having a million
or more users. With the growing user base, the query gets slower an
slower.
By adding a database index, the process takes only about a microsecond.
Using PostgreSQL, we've simply added the index manually, which works fine:
{{{
CREATE INDEX accounts_user_username_upper ON accounts_user
(UPPER(username));
}}}
Yet, this is a crucial part of most web applications and therefore, it
would probably make sense if Django created the index automatically for
the integrated User and AbstractUser classes. Or at least, the issue could
be pointed out clearly in the docs.
--
Comment:
The two examples you give are invalid: both, emails and usernames are case
sensitive, either by their standard or by Django's default implementation.
Furthermore, Django already has a unique constraint on the username which
is the primary identifying data point after the user's id. I don't think
adding an index on the email makes much sense for Django's default
implementation. If email is your primary field to authenticate a user
against you should already make sure the value is unique which implies
having an index.
--
Ticket URL: <https://code.djangoproject.com/ticket/25399#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/074.dff3dbe1518e0d8f35ff2a57e2dd2dca%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
