3 ms·

Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency

by abound 2y ago

Not sure I understand this point, how does SQLite fix the N+1 query problem? Just by having the data co-located with the app and avoiding the round-trip latency hit? If so, I'd argue you still have N+1 problems, you just won't notice them until N gets a bit larger.

tptacek 2y ago
https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html
- abound 2y ago
  Ah cool, thanks for the link! For others, the short-ish answer is that doing hundreds of SQL queries in response to a request (loading nested timeline elements in their case) in SQLite is fine because of the lack of networking/IPC overhead. The nature of N+1 queries is unchanged.
  - plorkyeran 2y ago
    The other half of it that the sqlite page doesn't mention is that sqlite lacks query engine optimizations which would make one large query faster than many smaller queries. If you had a hypothetical in-process version of postgres which didn't have any IPC or networking overhead you'd still see benefits from performing fewer larger queries when using it because the query planner adds some overhead to small queries but makes complex queries faster.
    - notpushkin 2y ago
      I'm wondering how would it perform if we can compile https://pglite.dev/ https://pglite.dev/ as a native library and use it as an in-process Postgres... I know Node folks already use it as a Wasm module, so it shouldn’t be too tricky?
emseetech 2y ago
Right, but it effectively solves it (even if not theoretically). Most applications won't come close to encountering the N+1 problem on Sqlite, whereas it comes early on in server-based databases.
tracker1 2y ago
I would suggest that sometimes you want N+1 with a collapsed data set (JSON column data) if you have limited request size, separate stores/service and/or otherwise have clear primary key lookups for the secondary data. I've seen these types of queries run faster with separate lookups, especially depending on caching in environments where the lookup would mean a DBMS connecting to another DBMS for the extra data much more slowly.

by abound 2y ago

tptacek 2y ago

https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html

abound 2y ago
Ah cool, thanks for the link! For others, the short-ish answer is that doing hundreds of SQL queries in response to a request (loading nested timeline elements in their case) in SQLite is fine because of the lack of networking/IPC overhead. The nature of N+1 queries is unchanged.
- plorkyeran 2y ago
  The other half of it that the sqlite page doesn't mention is that sqlite lacks query engine optimizations which would make one large query faster than many smaller queries. If you had a hypothetical in-process version of postgres which didn't have any IPC or networking overhead you'd still see benefits from performing fewer larger queries when using it because the query planner adds some overhead to small queries but makes complex queries faster.
  - notpushkin 2y ago
    I'm wondering how would it perform if we can compile https://pglite.dev/ https://pglite.dev/ as a native library and use it as an in-process Postgres... I know Node folks already use it as a Wasm module, so it shouldn’t be too tricky?

emseetech 2y ago

Right, but it effectively solves it (even if not theoretically). Most applications won't come close to encountering the N+1 problem on Sqlite, whereas it comes early on in server-based databases.

tracker1 2y ago

I would suggest that sometimes you want N+1 with a collapsed data set (JSON column data) if you have limited request size, separate stores/service and/or otherwise have clear primary key lookups for the secondary data. I've seen these types of queries run faster with separate lookups, especially depending on caching in environments where the lookup would mean a DBMS connecting to another DBMS for the extra data much more slowly.