Debian Codegraph @

docs/topics/db/optimization.txt

raw
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.