3 ms·

Postgres complicates the application in several ways. In particular, Postgres suffers from the n+1 problem, while SQLite does not. That requires a significant a

Postgres complicates the application in several ways. In particular, Postgres suffers from the n+1 problem, while SQLite does not. That requires a significant amount of added complexity in the application to hack around. Why over engineer the application before it has proven itself as something anyone even wants to use? Let's face it, the large majority of software written gets thrown away soon after it is created. I already hear you saying that you know of a library that provides a perfect abstraction to hide all those details and complexities, making the choice between Postgres and SQLite just a flip of a switch away. Great! But then what does Postgres bring to the table for you to choose it over SQLite? If you truly prove a need for it in the future for whatever reason, all you need to do is update the configuration.

tangjurine 3y ago
> Postgres suffers from the n+1 problem, while SQLite does not. ?
- deleted 3y ago
  [deleted]
- randomdata 3y ago
  Indeed.
  - emseetech 3y ago
    It’s worth elaborating: N+1 Queries Are Not A Problem With SQLite https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Queries%20Are%20Not%20A%20Problem%20With%20SQLite&text=The%20SQLite%20database%20runs%20in,SQLite%20is%20not%20the%20problem https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Que....
- javcasas 3y ago
  Meaning insertion sort does not suffer from the problems that made us construct quicksort because we optimized the insertion a lot (at a tradeoff that we will not specify).
sgarland 3y ago
This is a misunderstanding of the n+1 problem, which is exacerbated by SQLite's deceptive phrasing of the issue: > In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag. While the above is true on its own, this is _not_ the typical definition of n+1. The n+1 problem is caused by poor schema design, badly-written queries, ORM, or a combination of these. If you have two tables with N rows, and your queries consist of "SELECT id FROM foo; SELECT * FROM bar WHERE id = foo.id_1...", that is not the fault of the DB, that is the fault of you (or perhaps your ORM) for not writing a JOIN.
- randomdata 3y ago
  > 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".

tangjurine 3y ago

> Postgres suffers from the n+1 problem, while SQLite does not. ?

deleted 3y ago
[deleted]
randomdata 3y ago
Indeed.
- emseetech 3y ago
  It’s worth elaborating: N+1 Queries Are Not A Problem With SQLite https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Queries%20Are%20Not%20A%20Problem%20With%20SQLite&text=The%20SQLite%20database%20runs%20in,SQLite%20is%20not%20the%20problem https://www.sqlite.org/np1queryprob.html#:~:text=N%2B1%20Que....
javcasas 3y ago
Meaning insertion sort does not suffer from the problems that made us construct quicksort because we optimized the insertion a lot (at a tradeoff that we will not specify).

sgarland 3y ago

This is a misunderstanding of the n+1 problem, which is exacerbated by SQLite's deceptive phrasing of the issue: > In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag. While the above is true on its own, this is _not_ the typical definition of n+1. The n+1 problem is caused by poor schema design, badly-written queries, ORM, or a combination of these. If you have two tables with N rows, and your queries consist of "SELECT id FROM foo; SELECT * FROM bar WHERE id = foo.id_1...", that is not the fault of the DB, that is the fault of you (or perhaps your ORM) for not writing a JOIN.

randomdata 3y ago
> 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".