| 1 |
============================ |
| 2 |
Database access optimization |
| 3 |
============================ |
| 4 |
|
| 5 |
Django's database layer provides various ways to help developers get the most |
| 6 |
out of their databases. This document gathers together links to the relevant |
| 7 |
documentation, and adds various tips, organized under a number of headings that |
| 8 |
outline the steps to take when attempting to optimize your database usage. |
| 9 |
|
| 10 |
Profile first |
| 11 |
============= |
| 12 |
|
| 13 |
As general programming practice, this goes without saying. Find out :ref:`what |
| 14 |
queries you are doing and what they are costing you <faq-see-raw-sql-queries>`. |
| 15 |
Use :meth:`.QuerySet.explain` to understand how specific ``QuerySet``\s are |
| 16 |
executed by your database. You may also want to use an external project like |
| 17 |
:pypi:`django-debug-toolbar`, or a tool that monitors your database directly. |
| 18 |
|
| 19 |
Remember that you may be optimizing for speed or memory or both, depending on |
| 20 |
your requirements. Sometimes optimizing for one will be detrimental to the |
| 21 |
other, but sometimes they will help each other. Also, work that is done by the |
| 22 |
database process might not have the same cost (to you) as the same amount of |
| 23 |
work done in your Python process. It is up to you to decide what your |
| 24 |
priorities are, where the balance must lie, and profile all of these as required |
| 25 |
since this will depend on your application and server. |
| 26 |
|
| 27 |
With everything that follows, remember to profile after every change to ensure |
| 28 |
that the change is a benefit, and a big enough benefit given the decrease in |
| 29 |
readability of your code. **All** of the suggestions below come with the caveat |
| 30 |
that in your circumstances the general principle might not apply, or might even |
| 31 |
be reversed. |
| 32 |
|
| 33 |
Use standard DB optimization techniques |
| 34 |
======================================= |
| 35 |
|
| 36 |
...including: |
| 37 |
|
| 38 |
* Indexes_. This is a number one priority, *after* you have determined from |
| 39 |
profiling what indexes should be added. Use |
| 40 |
:attr:`Meta.indexes <django.db.models.Options.indexes>` or |
| 41 |
:attr:`Field.db_index <django.db.models.Field.db_index>` to add these from |
| 42 |
Django. Consider adding indexes to fields that you frequently query using |
| 43 |
:meth:`~django.db.models.query.QuerySet.filter()`, |
| 44 |
:meth:`~django.db.models.query.QuerySet.exclude()`, |
| 45 |
:meth:`~django.db.models.query.QuerySet.order_by()`, etc. as indexes may help |
| 46 |
to speed up lookups. Note that determining the best indexes is a complex |
| 47 |
database-dependent topic that will depend on your particular application. |
| 48 |
The overhead of maintaining an index may outweigh any gains in query speed. |
| 49 |
|
| 50 |
.. _Indexes: https://en.wikipedia.org/wiki/Database_index |
| 51 |
|
| 52 |
* Appropriate use of field types. |
| 53 |
|
| 54 |
We will assume you have done the things listed above. The rest of this document |
| 55 |
focuses on how to use Django in such a way that you are not doing unnecessary |
| 56 |
work. This document also does not address other optimization techniques that |
| 57 |
apply to all expensive operations, such as :doc:`general purpose caching |
| 58 |
</topics/cache>`. |
| 59 |
|
| 60 |
Understand ``QuerySet``\s |
| 61 |
========================= |
| 62 |
|
| 63 |
Understanding :doc:`QuerySets </ref/models/querysets>` is vital to getting good |
| 64 |
performance with simple code. In particular: |
| 65 |
|
| 66 |
Understand ``QuerySet`` evaluation |
| 67 |
---------------------------------- |
| 68 |
|
| 69 |
To avoid performance problems, it is important to understand: |
| 70 |
|
| 71 |
* that :ref:`QuerySets are lazy <querysets-are-lazy>`. |
| 72 |
|
| 73 |
* when :ref:`they are evaluated <when-querysets-are-evaluated>`. |
| 74 |
|
| 75 |
* how :ref:`the data is held in memory <caching-and-querysets>`. |
| 76 |
|
| 77 |
Understand cached attributes |
| 78 |
---------------------------- |
| 79 |
|
| 80 |
As well as caching of the whole ``QuerySet``, there is caching of the result of |
| 81 |
attributes on ORM objects. In general, attributes that are not callable will be |
| 82 |
cached. For example, assuming the :ref:`example blog models |
| 83 |
<queryset-model-example>`: |
| 84 |
|
| 85 |
.. code-block:: pycon |
| 86 |
|
| 87 |
>>> entry = Entry.objects.get(id=1) |
| 88 |
>>> entry.blog # Blog object is retrieved at this point |
| 89 |
>>> entry.blog # cached version, no DB access |
| 90 |
|
| 91 |
But in general, callable attributes cause DB lookups every time: |
| 92 |
|
| 93 |
.. code-block:: pycon |
| 94 |
|
| 95 |
>>> entry = Entry.objects.get(id=1) |
| 96 |
>>> entry.authors.all() # query performed |
| 97 |
>>> entry.authors.all() # query performed again |
| 98 |
|
| 99 |
Be careful when reading template code - the template system does not allow use |
| 100 |
of parentheses, but will call callables automatically, hiding the above |
| 101 |
distinction. |
| 102 |
|
| 103 |
Be careful with your own custom properties - it is up to you to implement |
| 104 |
caching when required, for example using the |
| 105 |
:class:`~django.utils.functional.cached_property` decorator. |
| 106 |
|
| 107 |
Use the ``with`` template tag |
| 108 |
----------------------------- |
| 109 |
|
| 110 |
To make use of the caching behavior of ``QuerySet``, you may need to use the |
| 111 |
:ttag:`with` template tag. |
| 112 |
|
| 113 |
Use ``iterator()`` |
| 114 |
------------------ |
| 115 |
|
| 116 |
When you have a lot of objects, the caching behavior of the ``QuerySet`` can |
| 117 |
cause a large amount of memory to be used. In this case, |
| 118 |
:meth:`~django.db.models.query.QuerySet.iterator()` may help. |
| 119 |
|
| 120 |
Use ``explain()`` |
| 121 |
----------------- |
| 122 |
|
| 123 |
:meth:`.QuerySet.explain` gives you detailed information about how the database |
| 124 |
executes a query, including indexes and joins that are used. These details may |
| 125 |
help you find queries that could be rewritten more efficiently, or identify |
| 126 |
indexes that could be added to improve performance. |
| 127 |
|
| 128 |
Do database work in the database rather than in Python |
| 129 |
====================================================== |
| 130 |
|
| 131 |
For instance: |
| 132 |
|
| 133 |
* At the most basic level, use :ref:`filter and exclude <queryset-api>` to do |
| 134 |
filtering in the database. |
| 135 |
|
| 136 |
* Use :class:`F expressions <django.db.models.F>` to filter |
| 137 |
based on other fields within the same model. |
| 138 |
|
| 139 |
* Use :doc:`annotate to do aggregation in the database |
| 140 |
</topics/db/aggregation>`. |
| 141 |
|
| 142 |
If these aren't enough to generate the SQL you need: |
| 143 |
|
| 144 |
Use ``RawSQL`` |
| 145 |
-------------- |
| 146 |
|
| 147 |
A less portable but more powerful method is the |
| 148 |
:class:`~django.db.models.expressions.RawSQL` expression, which allows some SQL |
| 149 |
to be explicitly added to the query. If that still isn't powerful enough: |
| 150 |
|
| 151 |
Use raw SQL |
| 152 |
----------- |
| 153 |
|
| 154 |
Write your own :doc:`custom SQL to retrieve data or populate models |
| 155 |
</topics/db/sql>`. Use ``django.db.connection.queries`` to find out what Django |
| 156 |
is writing for you and start from there. |
| 157 |
|
| 158 |
Retrieve individual objects using a unique, indexed column |
| 159 |
========================================================== |
| 160 |
|
| 161 |
There are two reasons to use a column with |
| 162 |
:attr:`~django.db.models.Field.unique` or |
| 163 |
:attr:`~django.db.models.Field.db_index` when using |
| 164 |
:meth:`~django.db.models.query.QuerySet.get` to retrieve individual objects. |
| 165 |
First, the query will be quicker because of the underlying database index. |
| 166 |
Also, the query could run much slower if multiple objects match the lookup; |
| 167 |
having a unique constraint on the column guarantees this will never happen. |
| 168 |
|
| 169 |
So using the :ref:`example blog models <queryset-model-example>`: |
| 170 |
|
| 171 |
.. code-block:: pycon |
| 172 |
|
| 173 |
>>> entry = Entry.objects.get(id=10) |
| 174 |
|
| 175 |
will be quicker than: |
| 176 |
|
| 177 |
.. code-block:: pycon |
| 178 |
|
| 179 |
>>> entry = Entry.objects.get(headline="News Item Title") |
| 180 |
|
| 181 |
because ``id`` is indexed by the database and is guaranteed to be unique. |
| 182 |
|
| 183 |
Doing the following is potentially quite slow: |
| 184 |
|
| 185 |
.. code-block:: pycon |
| 186 |
|
| 187 |
>>> entry = Entry.objects.get(headline__startswith="News") |
| 188 |
|
| 189 |
First of all, ``headline`` is not indexed, which will make the underlying |
| 190 |
database fetch slower. |
| 191 |
|
| 192 |
Second, the lookup doesn't guarantee that only one object will be returned. |
| 193 |
If the query matches more than one object, it will retrieve and transfer all of |
| 194 |
them from the database. This penalty could be substantial if hundreds or |
| 195 |
thousands of records are returned. The penalty will be compounded if the |
| 196 |
database lives on a separate server, where network overhead and latency also |
| 197 |
play a factor. |
| 198 |
|
| 199 |
Retrieve everything at once if you know you will need it |
| 200 |
======================================================== |
| 201 |
|
| 202 |
Hitting the database multiple times for different parts of a single 'set' of |
| 203 |
data that you will need all parts of is, in general, less efficient than |
| 204 |
retrieving it all in one query. This is particularly important if you have a |
| 205 |
query that is executed in a loop, and could therefore end up doing many database |
| 206 |
queries, when only one was needed. So: |
| 207 |
|
| 208 |
Use ``QuerySet.select_related()`` and ``prefetch_related()`` |
| 209 |
------------------------------------------------------------ |
| 210 |
|
| 211 |
Understand :meth:`~django.db.models.query.QuerySet.select_related` and |
| 212 |
:meth:`~django.db.models.query.QuerySet.prefetch_related` thoroughly, and use |
| 213 |
them: |
| 214 |
|
| 215 |
* in :doc:`managers and default managers </topics/db/managers>` where |
| 216 |
appropriate. Be aware when your manager is and is not used; sometimes this is |
| 217 |
tricky so don't make assumptions. |
| 218 |
|
| 219 |
* in view code or other layers, possibly making use of |
| 220 |
:func:`~django.db.models.prefetch_related_objects` where needed. |
| 221 |
|
| 222 |
Don't retrieve things you don't need |
| 223 |
==================================== |
| 224 |
|
| 225 |
Use ``QuerySet.values()`` and ``values_list()`` |
| 226 |
----------------------------------------------- |
| 227 |
|
| 228 |
When you only want a ``dict`` or ``list`` of values, and don't need ORM model |
| 229 |
objects, make appropriate usage of |
| 230 |
:meth:`~django.db.models.query.QuerySet.values()`. |
| 231 |
These can be useful for replacing model objects in template code - as long as |
| 232 |
the dicts you supply have the same attributes as those used in the template, |
| 233 |
you are fine. |
| 234 |
|
| 235 |
Use ``QuerySet.defer()`` and ``only()`` |
| 236 |
--------------------------------------- |
| 237 |
|
| 238 |
Use :meth:`~django.db.models.query.QuerySet.defer()` and |
| 239 |
:meth:`~django.db.models.query.QuerySet.only()` if there are database columns |
| 240 |
you know that you won't need (or won't need in most cases) to avoid loading |
| 241 |
them. Note that if you *do* use them, the ORM will have to go and get them in |
| 242 |
a separate query, making this a pessimization if you use it inappropriately. |
| 243 |
|
| 244 |
Don't be too aggressive in deferring fields without profiling as the database |
| 245 |
has to read most of the non-text, non-``VARCHAR`` data from the disk for a |
| 246 |
single row in the results, even if it ends up only using a few columns. The |
| 247 |
``defer()`` and ``only()`` methods are most useful when you can avoid loading a |
| 248 |
lot of text data or for fields that might take a lot of processing to convert |
| 249 |
back to Python. As always, profile first, then optimize. |
| 250 |
|
| 251 |
Use ``QuerySet.contains(obj)`` |
| 252 |
------------------------------ |
| 253 |
|
| 254 |
...if you only want to find out if ``obj`` is in the queryset, rather than |
| 255 |
``if obj in queryset``. |
| 256 |
|
| 257 |
Use ``QuerySet.count()`` |
| 258 |
------------------------ |
| 259 |
|
| 260 |
...if you only want the count, rather than doing ``len(queryset)``. |
| 261 |
|
| 262 |
Use ``QuerySet.exists()`` |
| 263 |
------------------------- |
| 264 |
|
| 265 |
...if you only want to find out if at least one result exists, rather than ``if |
| 266 |
queryset``. |
| 267 |
|
| 268 |
But: |
| 269 |
|
| 270 |
.. _overuse_of_count_and_exists: |
| 271 |
|
| 272 |
Don't overuse ``contains()``, ``count()``, and ``exists()`` |
| 273 |
----------------------------------------------------------- |
| 274 |
|
| 275 |
If you are going to need other data from the QuerySet, evaluate it immediately. |
| 276 |
|
| 277 |
For example, assuming a ``Group`` model that has a many-to-many relation to |
| 278 |
``User``, the following code is optimal:: |
| 279 |
|
| 280 |
members = group.members.all() |
| 281 |
|
| 282 |
if display_group_members: |
| 283 |
if members: |
| 284 |
if current_user in members: |
| 285 |
print("You and", len(members) - 1, "other users are members of this group.") |
| 286 |
else: |
| 287 |
print("There are", len(members), "members in this group.") |
| 288 |
|
| 289 |
for member in members: |
| 290 |
print(member.username) |
| 291 |
else: |
| 292 |
print("There are no members in this group.") |
| 293 |
|
| 294 |
It is optimal because: |
| 295 |
|
| 296 |
#. Since QuerySets are lazy, this does no database queries if |
| 297 |
``display_group_members`` is ``False``. |
| 298 |
|
| 299 |
#. Storing ``group.members.all()`` in the ``members`` variable allows its |
| 300 |
result cache to be reused. |
| 301 |
|
| 302 |
#. The line ``if members:`` causes ``QuerySet.__bool__()`` to be called, which |
| 303 |
causes the ``group.members.all()`` query to be run on the database. If there |
| 304 |
aren't any results, it will return ``False``, otherwise ``True``. |
| 305 |
|
| 306 |
#. The line ``if current_user in members:`` checks if the user is in the result |
| 307 |
cache, so no additional database queries are issued. |
| 308 |
|
| 309 |
#. The use of ``len(members)`` calls ``QuerySet.__len__()``, reusing the result |
| 310 |
cache, so again, no database queries are issued. |
| 311 |
|
| 312 |
#. The ``for member`` loop iterates over the result cache. |
| 313 |
|
| 314 |
In total, this code does either one or zero database queries. The only |
| 315 |
deliberate optimization performed is using the ``members`` variable. Using |
| 316 |
``QuerySet.exists()`` for the ``if``, ``QuerySet.contains()`` for the ``in``, |
| 317 |
or ``QuerySet.count()`` for the count would each cause additional queries. |
| 318 |
|
| 319 |
Use ``QuerySet.update()`` and ``delete()`` |
| 320 |
------------------------------------------ |
| 321 |
|
| 322 |
Rather than retrieve a load of objects, set some values, and save them |
| 323 |
individual, use a bulk SQL UPDATE statement, via :ref:`QuerySet.update() |
| 324 |
<topics-db-queries-update>`. Similarly, do :ref:`bulk deletes |
| 325 |
<topics-db-queries-delete>` where possible. |
| 326 |
|
| 327 |
Note, however, that these bulk update methods cannot call the ``save()`` or |
| 328 |
``delete()`` methods of individual instances, which means that any custom |
| 329 |
behavior you have added for these methods will not be executed, including |
| 330 |
anything driven from the normal database object :doc:`signals </ref/signals>`. |
| 331 |
|
| 332 |
Use foreign key values directly |
| 333 |
------------------------------- |
| 334 |
|
| 335 |
If you only need a foreign key value, use the foreign key value that is already on |
| 336 |
the object you've got, rather than getting the whole related object and taking |
| 337 |
its primary key. i.e. do:: |
| 338 |
|
| 339 |
entry.blog_id |
| 340 |
|
| 341 |
instead of:: |
| 342 |
|
| 343 |
entry.blog.id |
| 344 |
|
| 345 |
Don't order results if you don't care |
| 346 |
------------------------------------- |
| 347 |
|
| 348 |
Ordering is not free; each field to order by is an operation the database must |
| 349 |
perform. If a model has a default ordering (:attr:`Meta.ordering |
| 350 |
<django.db.models.Options.ordering>`) and you don't need it, remove |
| 351 |
it on a ``QuerySet`` by calling |
| 352 |
:meth:`~django.db.models.query.QuerySet.order_by()` with no parameters. |
| 353 |
|
| 354 |
Adding an index to your database may help to improve ordering performance. |
| 355 |
|
| 356 |
Use bulk methods |
| 357 |
================ |
| 358 |
|
| 359 |
Use bulk methods to reduce the number of SQL statements. |
| 360 |
|
| 361 |
Create in bulk |
| 362 |
-------------- |
| 363 |
|
| 364 |
When creating objects, where possible, use the |
| 365 |
:meth:`~django.db.models.query.QuerySet.bulk_create()` method to reduce the |
| 366 |
number of SQL queries. For example:: |
| 367 |
|
| 368 |
Entry.objects.bulk_create( |
| 369 |
[ |
| 370 |
Entry(headline="This is a test"), |
| 371 |
Entry(headline="This is only a test"), |
| 372 |
] |
| 373 |
) |
| 374 |
|
| 375 |
...is preferable to:: |
| 376 |
|
| 377 |
Entry.objects.create(headline="This is a test") |
| 378 |
Entry.objects.create(headline="This is only a test") |
| 379 |
|
| 380 |
Note that there are a number of :meth:`caveats to this method |
| 381 |
<django.db.models.query.QuerySet.bulk_create>`, so make sure it's appropriate |
| 382 |
for your use case. |
| 383 |
|
| 384 |
Update in bulk |
| 385 |
-------------- |
| 386 |
|
| 387 |
When updating objects, where possible, use the |
| 388 |
:meth:`~django.db.models.query.QuerySet.bulk_update()` method to reduce the |
| 389 |
number of SQL queries. Given a list or queryset of objects:: |
| 390 |
|
| 391 |
entries = Entry.objects.bulk_create( |
| 392 |
[ |
| 393 |
Entry(headline="This is a test"), |
| 394 |
Entry(headline="This is only a test"), |
| 395 |
] |
| 396 |
) |
| 397 |
|
| 398 |
The following example:: |
| 399 |
|
| 400 |
entries[0].headline = "This is not a test" |
| 401 |
entries[1].headline = "This is no longer a test" |
| 402 |
Entry.objects.bulk_update(entries, ["headline"]) |
| 403 |
|
| 404 |
...is preferable to:: |
| 405 |
|
| 406 |
entries[0].headline = "This is not a test" |
| 407 |
entries[0].save() |
| 408 |
entries[1].headline = "This is no longer a test" |
| 409 |
entries[1].save() |
| 410 |
|
| 411 |
Note that there are a number of :meth:`caveats to this method |
| 412 |
<django.db.models.query.QuerySet.bulk_update>`, so make sure it's appropriate |
| 413 |
for your use case. |
| 414 |
|
| 415 |
Insert in bulk |
| 416 |
-------------- |
| 417 |
|
| 418 |
When inserting objects into :class:`ManyToManyFields |
| 419 |
<django.db.models.ManyToManyField>`, use |
| 420 |
:meth:`~django.db.models.fields.related.RelatedManager.add` with multiple |
| 421 |
objects to reduce the number of SQL queries. For example:: |
| 422 |
|
| 423 |
my_band.members.add(me, my_friend) |
| 424 |
|
| 425 |
...is preferable to:: |
| 426 |
|
| 427 |
my_band.members.add(me) |
| 428 |
my_band.members.add(my_friend) |
| 429 |
|
| 430 |
...where ``Band`` and ``Artist`` are models with a many-to-many relationship. |
| 431 |
|
| 432 |
When inserting different pairs of objects into |
| 433 |
:class:`~django.db.models.ManyToManyField` or when the custom |
| 434 |
:attr:`~django.db.models.ManyToManyField.through` table is defined, use |
| 435 |
:meth:`~django.db.models.query.QuerySet.bulk_create()` method to reduce the |
| 436 |
number of SQL queries. For example:: |
| 437 |
|
| 438 |
PizzaToppingRelationship = Pizza.toppings.through |
| 439 |
PizzaToppingRelationship.objects.bulk_create( |
| 440 |
[ |
| 441 |
PizzaToppingRelationship(pizza=my_pizza, topping=pepperoni), |
| 442 |
PizzaToppingRelationship(pizza=your_pizza, topping=pepperoni), |
| 443 |
PizzaToppingRelationship(pizza=your_pizza, topping=mushroom), |
| 444 |
], |
| 445 |
ignore_conflicts=True, |
| 446 |
) |
| 447 |
|
| 448 |
...is preferable to:: |
| 449 |
|
| 450 |
my_pizza.toppings.add(pepperoni) |
| 451 |
your_pizza.toppings.add(pepperoni, mushroom) |
| 452 |
|
| 453 |
...where ``Pizza`` and ``Topping`` have a many-to-many relationship. Note that |
| 454 |
there are a number of :meth:`caveats to this method |
| 455 |
<django.db.models.query.QuerySet.bulk_create>`, so make sure it's appropriate |
| 456 |
for your use case. |
| 457 |
|
| 458 |
Remove in bulk |
| 459 |
-------------- |
| 460 |
|
| 461 |
When removing objects from :class:`ManyToManyFields |
| 462 |
<django.db.models.ManyToManyField>`, use |
| 463 |
:meth:`~django.db.models.fields.related.RelatedManager.remove` with multiple |
| 464 |
objects to reduce the number of SQL queries. For example:: |
| 465 |
|
| 466 |
my_band.members.remove(me, my_friend) |
| 467 |
|
| 468 |
...is preferable to:: |
| 469 |
|
| 470 |
my_band.members.remove(me) |
| 471 |
my_band.members.remove(my_friend) |
| 472 |
|
| 473 |
...where ``Band`` and ``Artist`` are models with a many-to-many relationship. |
| 474 |
|
| 475 |
When removing different pairs of objects from :class:`ManyToManyFields |
| 476 |
<django.db.models.ManyToManyField>`, use |
| 477 |
:meth:`~django.db.models.query.QuerySet.delete` on a |
| 478 |
:class:`~django.db.models.Q` expression with multiple |
| 479 |
:attr:`~django.db.models.ManyToManyField.through` model instances to reduce |
| 480 |
the number of SQL queries. For example:: |
| 481 |
|
| 482 |
from django.db.models import Q |
| 483 |
|
| 484 |
PizzaToppingRelationship = Pizza.toppings.through |
| 485 |
PizzaToppingRelationship.objects.filter( |
| 486 |
Q(pizza=my_pizza, topping=pepperoni) |
| 487 |
| Q(pizza=your_pizza, topping=pepperoni) |
| 488 |
| Q(pizza=your_pizza, topping=mushroom) |
| 489 |
).delete() |
| 490 |
|
| 491 |
...is preferable to:: |
| 492 |
|
| 493 |
my_pizza.toppings.remove(pepperoni) |
| 494 |
your_pizza.toppings.remove(pepperoni, mushroom) |
| 495 |
|
| 496 |
...where ``Pizza`` and ``Topping`` have a many-to-many relationship. |