Haki Benita

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

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…

Some a prime example of a service that naturally peaks at round hours. We have a habbit of never scheduling long running processes at round hours. Usually because they tend to be busier. https://hakibenita.com/sql-tricks-application-dba#dont-schedule-long-r…

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 found this explanation very helpful: https://hakibenita.com/python-dependency-injection (if you program in Python)

I don't know if it's the same for MySQL, but in Postgres a CTE would possibly force the planner to make less efficient choices [1]. Would be interesting if someone who knows more than me could clarify on this [1] https://hakibenita.com/be-careful-with-cte-in-postgre-sql

Here is an another article that expands on how Postgres stores text data. https://hakibenita.com/sql-medium-text-performance

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… 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/

CTE advice is somewhat questionable, as it is database specific. CTEs were for a very long time an optimization fence in PostgreSQL, were not inlined and behaved more like temporary materialized views. Only with release of PostgreSQL 12 some CTE inlining is happening - with limitations: not recursive, no side-effects and are only referenced once in a later part of a query. Mode info: https://hakibenita.com/be-careful-with-cte-in-postgre-sql

OP has a pretty interesting writeup about why DRF model serialization is slow. https://hakibenita.com/django-rest-framework-slow

He actually mentioned index de-duplication earlier: https://hakibenita.com/postgresql-unused-index-size#activating-b-tree-… If I had to guess, I would say that it doesn't accomplish anything (or as much as you'd think) for null values simply because there is no real data to store in either approach, you just have a bunch of pointers either way.

You might be interested in https://hakibenita.com/django-rest-framework-slow. If you weren't able to update to a 3.x version that has https://github.com/django/django/commit/a2c31e12da272acc76f3a3a0157fae… this might have bit you pretty hard.

The feed is here: https://hakibenita.com/feeds/all.atom.xml.