5 ms·

> that is the fault of you (or perhaps your ORM) for not writing a JOIN. It's your fault for not writing a join if you need a join. But that's not where the n+

> that is the fault of you (or perhaps your ORM) for not writing a JOIN. It's your fault for not writing a join if you need a join. But that's not where the n+1 problem comes into play. Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution. Given an idealized database, many queries is the proper solution to the problem. Of course, an idealized database doesn't exist, so we have to deal with the constraints of reality. This, in the case of Postgres, means moving database logic into the application. But that complicates the application significantly, having to take on the role that the database should be playing. But as far as SQLite goes, for all practical purposes you can think of it as an ideal database as it pertains to this particular issue. This means you don't have to move that database logic into your application, simplifying things greatly. Of course, SQLite certainly isn't ideal in every way. Tradeoffs, as always. But as far as picking the tradeoffs you are willing to accept for the typical "MVP", SQLite chooses some pretty good defaults.

Ensorceled 3y ago
We are still dealing with the fact that SQLite still HAS the n+1 "problem", it's just fast enough that it doesn't suffer from it. It's a very important distinction because, as you say, there are problem domains where you can't just "join the problem away".
- randomdata 3y ago
  Are we? "Suffer" was the word used right from the beginning for good reason. Computers aren't magic. I find no importance in pointing out that fact. Presumably everyone here already knows that. And if it turns out that they don't, who cares? That's their problem. Makes no difference to me.
  - Ensorceled 3y ago
    I agree that suffer is the right word, but unclear. You are getting down voted because a lot of people are interpreting to mean you are saying applications using sqlite don't have n+1 queries.
    - randomdata 3y ago
      1. At time of writing, there has been one downvote in the first comment, followed by one upvote in the subsequent comment. Not only does that not translate to "a lot of people", it was quite likely just one person. And unless that person was you, it is impossible to know what their intent was. I'm not sure what are you trying to add here. 2. Who gives a shit? If the "computers truly are magic" camp don't understand what I wrote, great! It wasn't written for them in the first place. If that compels them to use their time pressing a button instead of learning about how computers work, great! Not my problem. I'm not sure what you are trying to add here.
      - Ensorceled 3y ago
        > Who gives a shit? [...] I'm not sure what you are trying to add here. I guess nothing. You must be fun at design reviews.
        randomdata 3y ago
        Well, let's hope the "computers are magic" riff-raff never show up at the design reviews. Especially if they expect someone to explain to them the basics of computing without any reasonable offer of compensation in return. If those people show up here and put on a tantrum by pressing random buttons or whatever it was that you were trying to point out... Oh well? I wouldn't have even noticed if you didn't bring it up. What value you found in calling attention to their pointless tantrums is an interesting mystery, though!
mkleczek 3y ago
> Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. You can easily get hierarchical output format from Postgres with its JSON or XML aggregate functions. You can have almost all benefits of an embedded database by embedding your application in the database. Just change perspective and stop treating Postgres (or any other advanced RDBMS) as a dumb data store — start using it as a computing platform instead.
sgarland 3y ago
> Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. I don't know how precisely strict you expect a tree to be in RDBMS, but this [0] is as close as I can get. It has a hierarchy of product --> entity --> category --> item, with leafs along the way. In this example, I added two bands (Dream Theater [with their additional early name of Majesty], and Tool), along with their members (correctly assigning artists to the eras), and selected three albums: Tool's Undertow, with both CD and Vinyl releases, and Dream Theater's Train of Thought, and A Dramatic Turn of Events. The included query in the gist returns all available information about the albums present in a single query. No n+1. The inserts could likely be improved (for example, if you were doing these from an application, you could save IDs and then immediately reuse them; technically you could do that in pl/pgsql, but ugh), but they do work. This is also set up to model books in much the same way, but I didn't add any. > A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution. Joins are not a "hack," they are an integral part of the relational model. [0]: https://gist.github.com/stephanGarland/ec2d0f0bb54161898df668c1b48c8190 https://gist.github.com/stephanGarland/ec2d0f0bb54161898df66...
- randomdata 3y ago
  > Joins are not a "hack," they are an integral part of the relational model. Yes, joins are an essential part of the relational model, but we're clearly not talking about the relational model. The n+1 problem rears its ugly head when you don't have a relational model – when you have a tree-like model instead. > The included query in the gist returns all available information about the albums present in a single query. No n+1. No n+1, but then you're stuck with tables/relations, which are decidedly not in a tree-like shape. You can move database logic into your application to turn tables into trees, but then you have a whole lot of extra complexity to contend with. Needlessly so in the typical case since you can just use SQLite instead... Unless you have a really strong case otherwise, it's best to leave database work for databases. After all, if you want your application to do the database work, what do you need SQLite or Postgres for? Of course, as always, tradeoffs have to be made. Sometimes it is better to put database logic in your application to make gains elsewhere. But for the typical greenfield application that hasn't even proven that users want to use it yet, added complexity in the application layer is probably not a good trade. At least not in the typical case.
int_19h 3y ago
If you need to query over a tree data structure, then that's what WITH RECURSIVE is for, and it's present in both DBMS. If you additionally need the result of that query to be hierarchical itself, then you can easily have PG generate JSON for you.
dventimi 3y ago
> No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. A trivial amount of lateral joins plus JSON aggregates will give you a relation with on record, containing a nested JSON value with a perfectly adequate tree structure, with perfectly adequate performance, in databases that support these operations. There are solutions to these problems. One only needs to willingness to accept them.
- randomdata 3y ago
  Of course. I know reading is hard, but nobody has suggested there aren't solutions. Obviously there are, else Postgres would be pretty much unusable. But the question remains: Why resort to hacks that require extra complexity in the application when you can just as easily use the database as it was designed to be used? What are you getting in return?

Ensorceled 3y ago

We are still dealing with the fact that SQLite still HAS the n+1 "problem", it's just fast enough that it doesn't suffer from it. It's a very important distinction because, as you say, there are problem domains where you can't just "join the problem away".

randomdata 3y ago
Are we? "Suffer" was the word used right from the beginning for good reason. Computers aren't magic. I find no importance in pointing out that fact. Presumably everyone here already knows that. And if it turns out that they don't, who cares? That's their problem. Makes no difference to me.
- Ensorceled 3y ago
  I agree that suffer is the right word, but unclear. You are getting down voted because a lot of people are interpreting to mean you are saying applications using sqlite don't have n+1 queries.
  - randomdata 3y ago
    1. At time of writing, there has been one downvote in the first comment, followed by one upvote in the subsequent comment. Not only does that not translate to "a lot of people", it was quite likely just one person. And unless that person was you, it is impossible to know what their intent was. I'm not sure what are you trying to add here. 2. Who gives a shit? If the "computers truly are magic" camp don't understand what I wrote, great! It wasn't written for them in the first place. If that compels them to use their time pressing a button instead of learning about how computers work, great! Not my problem. I'm not sure what you are trying to add here.
    - Ensorceled 3y ago
      > Who gives a shit? [...] I'm not sure what you are trying to add here. I guess nothing. You must be fun at design reviews.
      - randomdata 3y ago
        Well, let's hope the "computers are magic" riff-raff never show up at the design reviews. Especially if they expect someone to explain to them the basics of computing without any reasonable offer of compensation in return. If those people show up here and put on a tantrum by pressing random buttons or whatever it was that you were trying to point out... Oh well? I wouldn't have even noticed if you didn't bring it up. What value you found in calling attention to their pointless tantrums is an interesting mystery, though!

mkleczek 3y ago

sgarland 3y ago

> Often in the real world you need tree-like structures, which are fundamentally not able to be represented by a table/relation. No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. I don't know how precisely strict you expect a tree to be in RDBMS, but this [0] is as close as I can get. It has a hierarchy of product --> entity --> category --> item, with leafs along the way. In this example, I added two bands (Dream Theater [with their additional early name of Majesty], and Tool), along with their members (correctly assigning artists to the eras), and selected three albums: Tool's Undertow, with both CD and Vinyl releases, and Dream Theater's Train of Thought, and A Dramatic Turn of Events. The included query in the gist returns all available information about the albums present in a single query. No n+1. The inserts could likely be improved (for example, if you were doing these from an application, you could save IDs and then immediately reuse them; technically you could do that in pl/pgsql, but ugh), but they do work. This is also set up to model books in much the same way, but I didn't add any. > A join is part of one possible hack to workaround to the problem, but not the mathematically ideal solution. Joins are not a "hack," they are an integral part of the relational model. [0]: https://gist.github.com/stephanGarland/ec2d0f0bb54161898df668c1b48c8190 https://gist.github.com/stephanGarland/ec2d0f0bb54161898df66...

randomdata 3y ago
> Joins are not a "hack," they are an integral part of the relational model. Yes, joins are an essential part of the relational model, but we're clearly not talking about the relational model. The n+1 problem rears its ugly head when you don't have a relational model – when you have a tree-like model instead. > The included query in the gist returns all available information about the albums present in a single query. No n+1. No n+1, but then you're stuck with tables/relations, which are decidedly not in a tree-like shape. You can move database logic into your application to turn tables into trees, but then you have a whole lot of extra complexity to contend with. Needlessly so in the typical case since you can just use SQLite instead... Unless you have a really strong case otherwise, it's best to leave database work for databases. After all, if you want your application to do the database work, what do you need SQLite or Postgres for? Of course, as always, tradeoffs have to be made. Sometimes it is better to put database logic in your application to make gains elsewhere. But for the typical greenfield application that hasn't even proven that users want to use it yet, added complexity in the application layer is probably not a good trade. At least not in the typical case.

int_19h 3y ago

If you need to query over a tree data structure, then that's what WITH RECURSIVE is for, and it's present in both DBMS. If you additionally need the result of that query to be hierarchical itself, then you can easily have PG generate JSON for you.

dventimi 3y ago

> No amount of joining can produce anything other than a table/relation. The n+1 problem is introduced when you try to build those types of structures from tables/relations. A trivial amount of lateral joins plus JSON aggregates will give you a relation with on record, containing a nested JSON value with a perfectly adequate tree structure, with perfectly adequate performance, in databases that support these operations. There are solutions to these problems. One only needs to willingness to accept them.

randomdata 3y ago
Of course. I know reading is hard, but nobody has suggested there aren't solutions. Obviously there are, else Postgres would be pretty much unusable. But the question remains: Why resort to hacks that require extra complexity in the application when you can just as easily use the database as it was designed to be used? What are you getting in return?