Time to say goodbye to yet another series. But before we do so, let’s once again ask the question: “how can I make Django database queries faster?” Today I’m going to show another aspect of this problem, namely leveraging the hardware architecture for optimizing Django queries. Let’s create a read replica and set up the project for it. Ready?
Architecture setup
For this example I’m going to use AWS RDS service since it’s easy to spin off. We’re going to have 2 databases: 1 master node for write operations and 1 slave for read operations.
Note: I am allowing public access for this database. If you’re using this for production you should limit it to the internal AWS network.
As you can see there’s postgres-master and postgres-replica. In case of PostgreSQL streaming replication is used. One interesting thing that you can see in the picture above is that the 2 instances are set in different availability zones, which are separate physical locations. So in addition to performance upgrades we improve our infrastructure reliability.
Configure Django for Using Read Replicas
First, we need to updatee your DATABSES setting:
DATABASES = {
'default': {},
'master': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'postgres_master',
'USER': 'postgres',
'PASSWORD': 'postgres',
'HOST': 'postgres-master.c9a9ehc0s9ms.eu-central-1.rds.amazonaws.com',
'PORT': '5432',
},
'replica': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'postgres_master',
'USER': 'postgres',
'PASSWORD': 'postgres',
'HOST': 'postgres-replica.c9a9ehc0s9ms.eu-central-1.rds.amazonaws.com',
'PORT': '5432',
}
}
Note: both databases are named the same way. Initially I thought to give the replica a _replica suffix, but it doesn’t make any sense. Read replica should be 1:1 replica including its name.
Cool. But now Django will not know how it should route the traffic. We also need to set routers:
DATABASE_ROUTERS = [
'bookshop.db_routers.ExampleRouter'
]
As you can probably guess, the ExampleRouter is a class with a few methods defined that can help us customize how the traffic is routed. It should implement methods that you can find here.
class ExampleRouter:
def db_for_read(self, model, **hints):
return 'replica'
def db_for_write(self, model, **hints):
return 'master'
def allow_relation(self, obj1, obj2, **hints):
return True
def allow_migrate(self, db, app_label, model_name=None, **hints):
return True
One more thing and we’re ready to go. We need to run migrations and for this we need to specify the database we want to use (otherwise Django will attempt to use DEFAULT database that we have empty):
python src/manage.py migrate --database=master Operations to perform: Apply all migrations: admin, auth, contenttypes, reviews, sessions Running migrations: Applying contenttypes.0001_initial... OK Applying auth.0001_initial... OK Applying admin.0001_initial... OK Applying admin.0002_logentry_remove_auto_add... OK Applying admin.0003_logentry_add_action_flag_choices... OK Applying contenttypes.0002_remove_content_type_name... OK Applying auth.0002_alter_permission_name_max_length... OK Applying auth.0003_alter_user_email_max_length... OK Applying auth.0004_alter_user_username_opts... OK Applying auth.0005_alter_user_last_login_null... OK Applying auth.0006_require_contenttypes_0002... OK Applying auth.0007_alter_validators_add_error_messages... OK Applying auth.0008_alter_user_username_max_length... OK Applying auth.0009_alter_user_last_name_max_length... OK Applying auth.0010_alter_group_name_max_length... OK Applying auth.0011_update_proxy_permissions... OK Applying auth.0012_alter_user_first_name_max_length... OK Applying reviews.0001_initial... OK Applying reviews.0002_auto_20201129_1839... OK Applying reviews.0003_auto_20201130_2105... OK Applying reviews.0004_auto_20201201_2051... OK Applying sessions.0001_initial... OK
Did it really succeed? Let’s try to see what tables are in both databases.
➜ ~ psql --dbname=postgres_master --host=postgres-master.c9a9ehc0s9ms.eu-central-1.rds.amazonaws.com --username=postgres
Password for user postgres:
psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1), server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres_master=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------------+-------+----------
public | auth_group | table | postgres
public | auth_group_permissions | table | postgres
public | auth_permission | table | postgres
public | auth_user | table | postgres
public | auth_user_groups | table | postgres
public | auth_user_user_permissions | table | postgres
public | django_admin_log | table | postgres
public | django_content_type | table | postgres
public | django_migrations | table | postgres
public | django_session | table | postgres
public | reviews_author | table | postgres
public | reviews_book | table | postgres
public | reviews_book_authors | table | postgres
public | reviews_category | table | postgres
public | reviews_review | table | postgres
Cool. And did it replicate?
➜ ~ psql --dbname=postgres_master --host=postgres-replica.c9a9ehc0s9ms.eu-central-1.rds.amazonaws.com --username=postgres
Password for user postgres:
psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1), server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres_master=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------------+-------+----------
public | auth_group | table | postgres
public | auth_group_permissions | table | postgres
public | auth_permission | table | postgres
public | auth_user | table | postgres
public | auth_user_groups | table | postgres
public | auth_user_user_permissions | table | postgres
public | django_admin_log | table | postgres
public | django_content_type | table | postgres
public | django_migrations | table | postgres
public | django_session | table | postgres
public | reviews_author | table | postgres
public | reviews_book | table | postgres
public | reviews_book_authors | table | postgres
public | reviews_category | table | postgres
public | reviews_review | table | postgres
(15 rows)
Has Our Django Query Optimization Succeeded?
Let’s set breakpoints in db_for_read and db_for_write methods and try to create a Category through an API call.
And on GET method:
We can also verify that the data is available in both databases:
➜ ~ psql --dbname=postgres_master --host=postgres-replica.c9a9ehc0s9ms.eu-central-1.rds.amazonaws.com --username=postgres
Password for user postgres:
psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1), server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres_master=> SELECT * FROM reviews_category
postgres_master-> ;
id | name
----+-----------------
1 | Science Fiction
2 | Science Fiction
(2 rows)
postgres_master=> \q
➜ ~ psql --dbname=postgres_master --host=postgres-master.c9a9ehc0s9ms.eu-central-1.rds.amazonaws.com --username=postgres
Password for user postgres:
psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1), server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres_master=> SELECT * FROM reviews_category;
id | name
----+-----------------
1 | Science Fiction
2 | Science Fiction
(2 rows)
postgres_master=>
That’s all. Subscribe to the newsletter to receive notifications about new posts.
Additional links
I know that this post (and the series) barely scratched the surface. However, I hope that I got your attention and that you might want to start looking at your own solutions. Where to go now?
- PostgreSQL docs – they cover a lot more topics like connection pooling, load balancing and alternative methods of cluster replication.
- Django docs on using multiple databases.
- AWS docs on read replication – go through it since each engine is a little bit different.
- AWS blog post on replication best practices – go through it. There’s a ton of useful information that will make your brain melt.
- Previous blog posts for part 1, part 2 and part 3.
- Project source code on GitLab