[abound]

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]

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

[abound]

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]

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]

I'm wondering how would it perform if we can compile 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]

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]

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.