Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go (opens in new tab)

I wasn't aware of Joist until I read a comment of yours somewhere else in this comment section, interesting stuff! I think Joist is the closest thing I've seen to an actual fix to this problem. The way you use the JS event loop as your 'scope' is pretty clever, and satisfies most use-cases I think.

It aligns pretty closely to what we do internally. We don't use classes or annotations to do entity definitions, instead we'll have something like this:

    const User = {
        fields: {
            id: defineIdField(),
            email: defineField('string', false), // false = NOT NULL
        },
        manyToOne: {
            role: { entity: 'Role', nullable: false },
        },
    };

Then the way to query and save is something like this:

    datasource.inTransaction(async (orm) => {
        const users = await orm.findMany('User', { where: sql`...`, orderBy: sql`email asc` });
    
        // Await in this loop might look bad, but only blocks for the first user
        for (const user of users) {
            console.log(user.email, (await user.role).name);
        }

        const adminRole = await orm.findOne('Role', 1); // by ID, throws if not found
        return orm.saveMany('User', users.map(u => ({ ...u, role: adminrole }));
    });

Which looks different from what Joist is doing, but the fetching mechanics under the hood are very similar I think. Our 'scope' is just somewhat more explicit, since we don't use the event loop mechanics of JS but rather use the inTransaction callback as the boundary.

(The reason we haven't open sourced this yet is that I'd like to iron out some of the ugly parts first, like the string entity identifiers, and the forced usage of an id column, and the lack of type safe where clauses right now)

Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain.

If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results.

My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations).

Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.

They seem quite comparable in terms of features. The way they do relationships is a little different though. Sqlboiler is a little more ORM in its approach with relationships- it adds methods to retrieve relationships and it adds optional fields to its models. Jet on the other hand expects you to write out a type-safe join and then will load relationships into your struct automatically if you define a struct with the appropriate fields. I prefer the Jet approach- there's not ORM, just SQL and something that loads SQL results into structs. There is down-side that the loading into structs is not fully type-safe once you write custom structs rather than use generated models although I am not sure if that is even possible in sqlboiler.

sqlboiler shows a lot of examples of using strings for queries which is odd because they seem to support type-safe queries as well.

This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations.

Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched.

The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.

I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.

SQL is easy to understand from the perspective of syntax, but quickly becomes challenging to understand depending on the data model how to actually get what you want out of a query. I have spent many years working in SQL across different server products (PostgreSQL, MySQL, Microsoft SQL Server, and others), and I still sometimes find myself struggling to build the most efficient query for task. Most of the time, it's not SQL that's the problem, it's a broken data model that preceded me (often created by an ORM) that I am forced to work with.

All that said, while it may be painful sometimes, learning it can at least let you poke your ORM more towards the right direction, or give you an opportunity to identify the hot spots where it's worth investing the effort to write a more efficient query directly, much like where sometimes it makes sense to write some subset of an application in assembly for performance, while most of the time you're better off just letting the compiler optimize for you.

For the problem you're talking about above, you're trying to avoid a Cartesian explosion, which is probably best handled through query splitting as you mentioned, but it depends on the database engine how best to approach that. For all its warts, Microsoft SQL Server is pretty good about things like the ability to use query splitting within the context of a single transaction to make it serializable and ensure data consistency (although that can come with its own challenges for performance).

The example you provided is nearly exactly the case where I would expect an ORM to produce a bad query, where-as a direct query build would be significantly more performant because you can incorporate a clear understanding of the data model and use limits and ordering to minimize the size of the result set you need to deal with.

> Some of us just aren't smart enough for sql.

I've heard this all my career. And I went through several phases with SQL including the banging rocks phase.

No, some of us just haven't had time to improve. I wrote my first sql in the early 2000s. I wasn't aware of window functions and lateral joins until 10 years later. Took me another couple of years to start using CTEs.

I don't even write SQL that much. I know enough SQL to make ORMs just do what I want.

It's tricky because in some cases you might be able to batch all the queries up front, but in others you will only know the IDs you need to fetch after you get one or more intermediate results back and apply business logic to arrive at a decision.

As of today there's no silver bullet beyond having a solid and principles-first understanding of your database and related infrastructure.

This might be true for some APIs but if you're letting the caller control what they fetch, you don't really have that luxury. An extreme example is Graphql-based APIs but even Rest-based APIs might allow some flexibility to decide what they fetch.

I can assure you that brandur knows SQL. :-)

(I don't know him personally, but have been following his blog for years.)

What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'".

You want to write this logic in your preferred backend language, Go/TS/C/etc.

This works fine, in the /account/X endpoint (just load the account, and apply the logic).

But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts)

As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic.

Your options are:

1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins)

2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious)

3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do).

Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).

Without two phases: figure out what you need for the resource, execute query, how can you avoid N+1 problem, raw sql or not.

TFA is using raw SQL (via sqlc) if you read carefully. And no, raw SQL doesn't get rid of N+1 issues.

That's also 101 entities. That's a half a thousand values if A and B have ~5 attributes. That's too much data. No human can make use of it and no application should offer it. Good ones won't. They'll impose limits and when they do they'll naturally also be imposing a limit on the size of the N+1 problem. That's why I say that in my opinion, it's overblown. You're welcome to form your own opinion, of course.

Again, the number of network calls will scale with the total data payload of the overall request, but the requested total data payload should never be large. It's effectively capped. Therefore, the number of network calls is effectively capped. The N+1 SQL call multiplying factor may be 2, or 10, but it shouldn't be 100 or 1000. That's what I mean when I say I think the problem is overblown. It's not that it isn't a problem--for some people--but that it's not a problem that can grow without bounds.

Put another way, while the N+1 problem scales the number of SQL calls exponentially, it also scales the data payload the same way. Large payloads shouldn't occur and won't occur if we take steps to ensure that they don't. If we do, then we're also pretty much guaranteeing large batches of SQL statements will likewise be limited.

SQL injection is totally optional. If you use prepared statements, which are easier to use than formatting arguments yourself, it’s not a possibility.