Haki Benita writes detailed articles on SQL, PostgreSQL performance, Django, and Python, with practical examples and benchmarks.
Commenters pick up on specifics such as the MERGE tip, cite his SQL tricks for application DBAs, and list him among good personal blogs.
I've been building in Django for a long time and I love the framework, but some things have always bugged me:
- Typing is exceptionally painful...and getting proper type safety across a Django project is a fight
- the ORM leaks throughout your project: it's easy to chain ORM methods in a view, a serializer, a template tag, a Celery task, etc and N+1s end up being difficult to find
- models become god objects: custom managers, save overrides, signal handlers, computed properties, validation logic, your models attract everything until they're 500 lines of tangled responsibilities.
- tests become slow over time because you either hit the DB for everything or mock too aggressively: this is because there's no clear encapsulated layers.
and a ton of other stuff including aesthetics...
Anyway, the short version of my solution is to push all ORM work into repositories that return Pydantic DTOs. Services get repos via constructor injection and contain pure business logic with zero model imports. Your views become one-liners. Every layer boundary is typed, and each layer is independently testable.
Some of the opinions:
- Prefixed ULID primary keys: double-clickable Stripe-style IDs like ord_01jq3v... on every model.
- Models are dumb but aesthetic: Meta first, fields in a strict order (identifiers, times, status, domain, relations), all indexes and constraints declared explicitly in Meta, zero business logic.
- Repositories return DTOs, never querysets so the ORM never leaks…
Lately, I've been studying machine learning, from point zero, with a focus on time series analysis. Two months in already completed a course on Python and another book on Pandas, several hundreds of hours later in the fourth chapter in a book I paid for on deep learning and time series analysis they provided me with the most important information I needed: there is no evidence deep learning works better than traditional statistical analysis using classical methods like SARIMA and ETS. Sure, great, if an academic is interested in theory hopefully making a breakthrough, however, the rest of us who are interested in applied should stick with the classical methods.
I was going to write a lot here but I'll keep it short.
What I discovered is that everything I want to do can best be done in PostgreSQL. It's one thing to do data analysis in a Python notebook and another in an environment that works dynamically on a server. My first guess was to do the heavy lifting in Python with Numpy, Pandas, and machine learning and have the node server -- instead of Django and if I'm learning a new web framework it will be Phoenix -- execute the Python scripts through stdin / stdout. Since started I learned that I don't need machine learning and that I can do the calculations inside PostgreSQL sometimes orders of magnitude faster than in Python.
I'm using TimescaleDB which provides the postgresSQL time_bucket() function and with chunks should scale very well. First I tried to integrate it…
Old is a harsh word, maybe mature would be a better fit, not everything new and shiny is gold, and yet not everything old sucks.
Not arguing here about types and Pydantic being faster than the built in ModelSerializers. However, for serializer speed improvements and performance in DRF I would advise dropping ModelSerializers and either going for Serializers or plain dict. Haki Benita has a beautiful article on that [0]. I was able to accomplish sub 200 response times on a fairly large response from tables that had tens of millions of records.
I think you have no objective reason other than your styling and rather personal preference for function based views?
[0] - https://hakibenita.com/django-rest-framework-slow
I want to stress the importance of not using
id int SERIAL
If you are on a somewhat recent version of postgres, please do yourself a favor and use:
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
An "identity column", the part here:
https://hakibenita.com/postgresql-unknown-features#prevent-setting-the-value-of-an-auto-generated-key
You might think this is trivial -- but SERIAL creates an "owned" (by a certain user) sequence behind the scenes, and so you run into massive headaches if you try to move things around.
Identity columns don't, and avoid the issue altogether.
https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/
The most interesting thing for me in this article was the mention of `MERGE` almost in passing at the end.
> I'm not a big fan of using the constraint names in SQL, so to overcome both limitations I'd use MERGE instead:
```
db=# MERGE INTO urls t
USING (VALUES (1000004, 'https://hakibenita.com')) AS s(id, url)
ON t.url = s.url
WHEN MATCHED THEN UPDATE SET id = s.id
WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.id, s.url);
MERGE 1
```
I use `insert ... on conflict do update ...` all the time to handle upserts, but it seems like merge may be more powerful and able to work in more scenarios. I hadn't heard of it before.
So many - I'm going to share a few that come to mind quickly, rather than agonizing over picking the best possible results.
Tim Bray: https://www.tbray.org/ongoing/
Julia Evans: https://jvns.ca/
Tom MacWright: https://macwright.com/
Jacob Kaplan-Moss: https://jacobian.org/
Lea Verou: https://lea.verou.me/
Will Larson: https://lethain.com/featured/
Camille Fournier: https://skamille.medium.com/
Haki Benita: https://hakibenita.com/
Brandur Leach: https://brandur.org/articles