3 ms·

> 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 produc

> 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.
- sgarland 3y ago
  n+1 can show up any time you have poorly modeled schema or queries. It’s quite possible to have a relational model that is sub-optimal; reference the fact that there are 5 levels of normalization (plus a couple extra) before you get into absurdity. I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. Do you have an example schema?
  - randomdata 3y ago
    > I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. That's simple: Not being an RDMBS, only an engine, is how it avoids the suffering. The n+1 problem is the result of slow execution. Of course, an idealize database has no time constraints, but the real world is not so kind. While SQLite has not figured out how to defy the laws of physics, it is able to reduce the time to run a query to imperceptible levels under typical usage by embedding itself in the application. Each query is just a function call, which are fast. Postgres' engine can be just as fast, but because it hides the engine behind the system layer, you don't interact with the engine directly. That means you need to resort to hacks to try and poke at the engine where the system tries to stand in the way. The hacks work... but at the cost of more complexity in the application.
    - dventimi 3y ago
      Compare and contrast the query execution stages of PostgreSQL and SQLite. How exactly do they work? Please be as precise as possible. Try to avoid imprecise terms like "simple", "suffering", "system layer", and "hack."
      - randomdata 3y ago
        For what purpose? I can find no source of value in your request.
        dventimi 3y ago

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.

sgarland 3y ago
n+1 can show up any time you have poorly modeled schema or queries. It’s quite possible to have a relational model that is sub-optimal; reference the fact that there are 5 levels of normalization (plus a couple extra) before you get into absurdity. I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. Do you have an example schema?
- randomdata 3y ago
  > I still would like to know how SQLite does not suffer from the same problems as any other RDBMS. That's simple: Not being an RDMBS, only an engine, is how it avoids the suffering. The n+1 problem is the result of slow execution. Of course, an idealize database has no time constraints, but the real world is not so kind. While SQLite has not figured out how to defy the laws of physics, it is able to reduce the time to run a query to imperceptible levels under typical usage by embedding itself in the application. Each query is just a function call, which are fast. Postgres' engine can be just as fast, but because it hides the engine behind the system layer, you don't interact with the engine directly. That means you need to resort to hacks to try and poke at the engine where the system tries to stand in the way. The hacks work... but at the cost of more complexity in the application.
  - dventimi 3y ago
    Compare and contrast the query execution stages of PostgreSQL and SQLite. How exactly do they work? Please be as precise as possible. Try to avoid imprecise terms like "simple", "suffering", "system layer", and "hack."
    - randomdata 3y ago
      For what purpose? I can find no source of value in your request.
      - dventimi 3y ago