05 Jul 2024
Since its introduction, Sqlite has not been considered as a viable alternative to other RDBMS like MySQL and PotgreSQL. Main reason behind this is its bad performance under concurrency.
But with the introduction of WAL mode and various other improvements in the recent times, things are changing for Sqlite. For some use cases, it is now considered on par or even better than the alternatives because of its simple setup, low maintenance cost and low latency.
Django comes with Sqlite as the default database. But there used to be a warning in the official docs against using it in production. It was meant to be only for development. That warning is not there anymore. It means Django now considers it good enough.
But Sqlite is not tuned for performance by default. It requires some configurations for optimal performance. And Django also, by default, does not do it for us. We need to do those configurations ourselves.
Sqlite provides something called PRAGMA statements which can be used to finetune the database according to our needs. We need to enable following 4 PRAGMAS for a better performance:
PRAGMA journal_mode = WAL
This PRAGMA significantly increases its ability to handle high concurrency, hence removing one of its major drawback.
PRAGMA synchronous = NORMAL
With journal_mode = WAL it makes more sense to use the less expensive synchronous = NORMAL instead of the default synchronous = FULL.
PRAGMA foreign_keys = ON
To enforce foreign key constraints, we need this PRAGMA as Sqlite does not enforce those constraints by default.
PRAGMA busy_timeout = 20000
By default Sqlite does not wait before throwing the database lock errors. This PRAGMA makes it wait for the given milliseconds duration before throwing the error.
It is not possible to set these PRAGMAS in settings.py file where the database configuration resides. One good place to do it is the apps.py file of any app.
This is how the apps.py file should look like.
from Django.apps import AppConfig
from Django.db.backends.signals import connection_created
class Mainapp(AppConfig):
default_auto_field = "Django.db.models.BigAutoField"
name = "mainapp"
def ready(self):
def enable_Sqlite_pragmas(sender, connection, **kwargs):
"""Enable the necessary Sqlite pragmas."""
cursor = connection.cursor()
cursor.execute("PRAGMA journal_mode = WAL;")
cursor.execute("PRAGMA synchronous = NORMAL;")
cursor.execute("PRAGMA foreign_keys = ON;")
cursor.execute("PRAGMA busy_timeout = 20000;")
connection_created.connect(enable_Sqlite_pragmas)
This ensures that these PRAGMAs are set whenever a database connection is created.
This is all we need to finetune Sqlite for production.
The following videos do a good job of explaining why Sqlite should be considered for production
DjangoCon Europe 2023 | Use Sqlite in production
GopherCon 2021: Ben Johnson - Building Production Applications Using Go & Sqlite
You can read through the following resources for a better understading of PRAGMAs