1 of 33

Django Gotchas

How I brought down The Black Tux website

2 of 33

Django Gotchas

How I brought down The Black Tux website TWICE

3 of 33

1.

The Exclude Issue

3

4 of 33

THE GOAL

​

Don’t show items that have been deleted.

4

‘’

5 of 33

Soft Delete

Pros

  • Restore functionality
  • Archive feature
  • Track history

Cons

  • Doing the database’s job
  • Extra filtering to remember
  • Large tables of obsolete data

5

Set a flag to indicate that a record has been deleted instead of removing it from the database.

6 of 33

The Fix

Original Code

​

items = self.user_event_look_items.all()

​

Updated Code

​

items = self.user_event_look_items.exclude( order_look_products__state=DELETED)

​

​

6

7 of 33

Ship it!

7

8 of 33

Revert

8

9 of 33

What SQL is Django running?

SELECT ... FROM "app_usereventlookitem"

WHERE ("app_usereventlookitem"."deleted" = 0 AND

"app_usereventlookitem"."user_event_look_id" = 'UUID');

​

​

​

​

​

9

print self.user_event_look_items.all().query

10 of 33

What SQL is Django running?

SELECT ... FROM "app_usereventlookitem"

WHERE ("app_usereventlookitem"."deleted" = 0

AND "app_usereventlookitem"."user_event_look_id" = 'UUID'

AND NOT ("app_usereventlookitem"."id"

IN (SELECT U1."user_event_look_item_id" AS Col1

FROM "app_orderlookproduct" U1

WHERE (U1."state" = 1

AND U1."user_event_look_item_id" IS NOT NULL))));

10

print self.user_event_look_items.exclude( order_look_products__state=DELETED).query

11 of 33

What SQL is Django running?

SELECT ... FROM "app_usereventlookitem"

LEFT OUTER JOIN "app_orderlookproduct"

ON ( "app_usereventlookitem"."id" = "app_orderlookproduct"."user_event_look_item_id" )

WHERE ("app_usereventlookitem"."deleted" = 0

AND "app_usereventlookitem"."user_event_look_id" = 'UUID'

AND ("app_orderlookproduct"."state" = 0

OR "app_orderlookproduct"."id" IS NULL));

​

11

print self.user_event_look_items.filter(

Q(order_look_products__state=OK)|Q(order_look_products__isnull=True)).query

12 of 33

Compare SQL Query Performance

​

EXPLAIN ANALYZE <QUERY>

12

‘’

13 of 33

Analyze Original Query

Index Scan using ... (cost=0.43..68.75 rows=10 width=80) (actual time=0.011..0.011 rows=0 loops=1)

Index Cond: (user_event_look_id = 'UUID'::uuid)

Filter: (deleted = 0)

​

​

​

​

​

​

​

13

14 of 33

Analyze “Exclude” Query

Index Scan using ... (cost=0.43..1339602.13 rows=5 width=80) (actual time=0.527..0.527 rows=0 loops=1)

Index Cond: (user_event_look_id = 'UUID'::uuid)

Filter: ((deleted = 0) AND (NOT (SubPlan 1)))

SubPlan 1

-> Materialize (cost=0.00..165457.18 rows=793794 width=16)

-> Seq Scan on app_orderlookproduct u1 (cost=0.00..157612.21 rows=793794 width=16)

Filter: ((user_event_look_item_id IS NOT NULL) AND (state = 1))

​

​

​

​

​

​

​

​

14

15 of 33

Analyze “Or” Query

Nested Loop Left Join (cost=5.19..1779.82 rows=8 width=80) (actual time=0.014..0.014 rows=0 loops=1)

Filter: ((app_orderlookproduct.state = 0) OR (app_orderlookproduct.id IS NULL))

-> Index Scan using ... (cost=0.43..68.75 rows=10 width=80) (actual time=0.013..0.013 rows=0 loops=1)

Index Cond: (user_event_look_id = 'UUID'::uuid)

Filter: (deleted = 0)

-> Bitmap Heap Scan on app_orderlookproduct (cost=4.76..170.58 rows=42 width=24)

Recheck Cond: (app_usereventlookitem.id = user_event_look_item_id)

-> Bitmap Index Scan on app_orderlookproduct_27afb0de (cost=0.00..4.75 rows=42 width=0)

Index Cond: (app_usereventlookitem.id = user_event_look_item_id)

​

​

​

​

​

​

​

​

15

16 of 33

Summary

Index Scan using ... (cost=0.43..68.75 rows=10 width=80) (actual time=0.011..0.011 rows=0 loops=1)

​

​

​

​

​

​

​

​

​

Index Scan using ... (cost=0.43..1339602.13 rows=5 width=80) (actual time=0.527..0.527 rows=0 loops=1)

​

​

​

​

​

​

​

​

​

Nested Loop Left Join (cost=5.19..1779.82 rows=8 width=80) (actual time=0.014..0.014 rows=0 loops=1)

​

​

​

​

​

​

​

​

​

16

Original

“Exclude”

“Or”

17 of 33

Ship it again!

17

18 of 33

Takeaways

  • Be skeptical of complex exclude queries
  • Use “.query” to view the SQL generated
  • Compare different approaches with “explain analyze”

18

19 of 33

2.

Adding Columns with Defaults

19

20 of 33

THE GOAL

​

Add a field to the User table. If a value is not provided for the field, set a default value.

20

‘’

21 of 33

Super Simple with Django

  • Add 1 line to the User model

​

registration_role = models.CharField(max_length=64, default=REGISTRATION_ROLE_CREATOR)

  • Generate the migration

​

migrations.AddField(

model_name='user',

name='registration_role',

field=models.CharField(default=b'creator',

max_length=64),

),

​

​

21

22 of 33

Done & Done

22

23 of 33

Nope & Nope

23

24 of 33

What SQL is the migration running?

​

A database default is not set. Defaults are

controlled by Django application logic.

​

​

./manage.py sqlmigrate <app_label> <migration_name>

​

​

24

25 of 33

Warning

​

Adding a default to a column at the time of column creation triggers an entire table rewrite.

25

26 of 33

Step 1 - Create the column

  • Add the column as a nullable field

​

registration_role = models.CharField(null=True,

max_length=64)

  • Generate the migration

​

migrations.AddField(

model_name='user',

name='registration_role',

field=models.CharField(null=True, max_length=64),

),

​

​

26

27 of 33

Step 2 - Populate new rows with data

  • Add the application logic for setting the field
  • Set the default value

​

registration_role = models.CharField(null=True,

default=REGISTRATION_ROLE_CREATOR)

  • Do NOT generate the migration

​

​

27

28 of 33

Step 3 - Backfill

  • Add data for all the older rows
  • Do via a method of your choice
    • Python script
    • SQL
    • etc

​

Keep in mind, if you’re backfilling using “UPDATE” it’s best to batch them. Large updates will create a table-wide lock.

​

28

29 of 33

Step 4 - Apply the Default Migration

  • Generate the migration

​

migrations.AlterField(

model_name='user',

name='registration_role',

field=models.CharField(default=b'creator', max_length=64))

​

​

29

30 of 33

Good News 🎉

​

For Postgres versions 11+ setting defaults at the time of column creation isn’t a problem.

30

31 of 33

Takeaways

  • Creating columns and setting defaults at the same time can cripple high volume tables
  • Adding a default to a new column is a multistep process
  • Use “sqlmigrate” to view the SQL generated by migrations

31

32 of 33

Closing Thoughts

  • Django’s abstractions and “helpfulness” make it very easy to get into trouble as applications get more complex.
  • It’s important to be mindful of the underlying mechanics to seemingly simple code changes.

32

33 of 33

Thanks!

Any questions?

You can find me at

sandra@theblacktux.com

​

In partnership with PyLadies LA and The Black Tux 7.17.2019

33