We came to pretty much the same conclusions for our Django app. We ended up using the "connection created" signal to set SYNCHRONOUS=NORMAL, but I hope to see that supported natively in Django soon enough.
Honestly, this setup will keep us going for a long time. There are some design considerations that you need to keep in mind while structuring your Python code. For example, on a gp3 EBS volume, our mean single-write latency IN SQLite is about 100 microseconds, so in theory, if we can keep transactions short, the scale we can achieve on just SQLite is massive. We'll also get serialized isolation on writes and snapshot isolation on reads for "free." However, Django/Python are not super quick, so if we decorate all our Python functions that make writes with `@transaction.atomic` we end up with 10+ MILLIsecond locks on the DB, massively reducing throughput.
This is a price to pay for using SQLite, but honestly, even if we end up having to move to a proper RDBMS later, our database will probably be happier we wrote our code with transaction length in mind.
Honestly, this setup will keep us going for a long time. There are some design considerations that you need to keep in mind while structuring your Python code. For example, on a gp3 EBS volume, our mean single-write latency IN SQLite is about 100 microseconds, so in theory, if we can keep transactions short, the scale we can achieve on just SQLite is massive. We'll also get serialized isolation on writes and snapshot isolation on reads for "free." However, Django/Python are not super quick, so if we decorate all our Python functions that make writes with `@transaction.atomic` we end up with 10+ MILLIsecond locks on the DB, massively reducing throughput.
This is a price to pay for using SQLite, but honestly, even if we end up having to move to a proper RDBMS later, our database will probably be happier we wrote our code with transaction length in mind.