Eradicating N+1s: The Two-Phase Data Load and Render Pattern in Go

Post by dventimi » Wed, May 29, 2024, 5:23 AM UTC

superice wrote: post #47

dventimi wrote: post #19

I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.

Oh it definitely isn't. Data volume may be small, but latency will kill you, especially if your database server isn't on the same machine as your application. If you fetch an entity A with a one-to-many to entity B, with a typical size of 100 B's for every A, you are looking at 101 separate queries if you access any relations on B, which assuming 1ms of network latency give you a lowerbound of 100ms for an operation…

Post by stephen » Wed, May 29, 2024, 5:29 AM UTC

superice wrote: post #32

tedunangst wrote: post #25

Earlier quoted context omitted.

Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-anothe... For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it…

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

Post by superice » Wed, May 29, 2024, 5:54 AM UTC

stephen wrote: post #53

superice wrote: post #42

I see all these comments stating 'oh ORMs are bad' and 'just write some SQL'. Yes, you should probably not be afraid of SQL, and yes, using an ORM for everything is probably not great, and no, ORMs aren't a full replacement for writing SQL occasionally, but taking an extremist pro-SQL point-of-view is not doing any favors to this debate. There are very real reasons why writing raw SQL is a pain. You can make argument…

Definitely appreciate your "sometimes ORMs/sometime raw SQL" pragmatic stance. I agree! > then if you join on both you now are loading A * B * C rows I'm realizing it's not actually written up in our proper docs, but the "cross join lateral" trick is pretty neat: https://joist-orm.io/blog/nextjs-sample-app#join-based-prelo... This will return A number of (author) rows, where each author row as a `_b` column that is a…

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

    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 }));
    });

Post by Tarean » Wed, May 29, 2024, 8:15 AM UTC

superice wrote: post #46

jiggawatts wrote: post #44

Earlier quoted context omitted.

The fundamental problem is with the wire protocol. It’s inherently tabular, but should actually be more like a binary JSON format, with hierarchical representation for joins to avoid repeating the data. Better yet, the wire formats should have efficient representations for things like column store compression.

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…

Post by superice » Wed, May 29, 2024, 9:00 AM UTC

jiggawatts wrote: post #57

superice wrote: post #46

Earlier quoted context omitted.

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…

> I'll typically serialize the data fetched to send out over HTTP again That's a whole other can of worms, so let's just ignore it for a second. I've found that in a sense, the converse is the case, where back-end protocols significantly pre-date HTTP and "haven't learned the hard lessons" around things like cookies, caching, load-balancing, etc... Ask yourself this: How many SQL database platforms can provide a redi…