News for hackers. Stuff that matters.
Re:Many small queries are efficient in SQLite by simonw on Tuesday February 16, 2021 @10:48AM (#26154972)
An interesting consequence of this characteristic of SQLite is that it makes it a really good fit for GraphQL.

GraphQL has the N+1 queries problem pretty much baked into it. You can work around it to a certain extent with a lot of effort implementing data loader patterns, but someone will still likely be able to cow up with a query that triggers a cascade of SELECTs.

Or, because "many small queries are efficient in SQLite", you could use that instead.

I built a Datasette plugin that exposes a GraphQL API to an existing SQLite database and it works so well that I switched from being a GraphQL skeptic to a GraphQL advocate.

https://simonwillison.net/2020/Aug/7/datasette-graphql/

Here's an example nested GraphQL query that executes 32 select queries. It's pretty snappy: https://datasette-graphql-demo.datasette.io/graphql?query=%7...

Re:Many small queries are efficient in SQLite by tehlike on Tuesday February 16, 2021 @11:06AM (#26155181)
hasura does fairly good query optimization using postgres json_agg, that's my preferred way - if more graphql engines did the same that'd be amazing.
Re:Many small queries are efficient in SQLite by rtorr on Tuesday February 16, 2021 @11:14AM (#26155305)
It does not have that problem baked into it since it relies on resolvers. It would have to be a choice to have that problem.
Re:Many small queries are efficient in SQLite by allknowingfrog on Tuesday February 16, 2021 @01:01PM (#26156774)
This seems a little pedantic. It may not be literally required by the technology, but GraphQL certainly makes it easier to introduce N+1 queries than a traditional REST API.
Re:Many small queries are efficient in SQLite by a-priori on Tuesday February 16, 2021 @01:19PM (#26156978)
In practice, I find REST APIs also have N+1 problems the same way GraphQL does. They just get spread across multiple requests.
Re:Many small queries are efficient in SQLite by Groxx on Tuesday February 16, 2021 @02:51PM (#26157975)
If anything I'd say REST is dramatically worse in this respect. There's no structure for nested queries (or anything except thing/:id really), so the only broadly compatible option is to pull every piece as a separate GET request.

Sure, you can use query params... but there's no implied support nor semantics, so one site will do one thing and another will do something from a completely different universe of architectural patterns, while a third will just have nothing, and there's no way to reconcile those in a consistent way.

Re:Many small queries are efficient in SQLite by travellingprog on Tuesday February 16, 2021 @03:43PM (#26158578)
You don't need to have 1:1 equivalence between API resources and database models, though. For example, you can create an API resource called TweetActivity, and then the backend code for GET /tweet-activity could put together a database query that grabs all the tweet likes, comments and basic commenter info (name, profile image), from different database tables, into a single new object. You can give that object the same ID as the tweet itself, and you can put a cache layer around that endpoint to, for example, save that response for the next 1 minute.

That being said, one thing you give up is providing a standard way for the client to specify which fields it needs returned. For example, a client might want to dig deeper into the commenter profile info. GraphQL's resolvers architecture opens up that possibility immediately.

Re:Many small queries are efficient in SQLite by Groxx on Tuesday February 16, 2021 @04:07PM (#26158822)
Agreed on pretty much all points, but you've just described a way to optimize an N+1 system without changing the fundamental N+1 aspect. The caller is still making N+1 requests and incurring round-trip latencies (end-to-end cost can at best be reduced to 2 with parallelism), and the server still has to receive N+1 requests. REST has no semantics to change this.
Re:Many small queries are efficient in SQLite by wongarsu on Tuesday February 16, 2021 @12:14PM (#26156158)
In a GraphQL API backed by a single database you could write resolvers that analyze the entire query and rephrase it as a single database query. But I'm not aware of anyone doing this. The common/straight forward solutions resolve each layer after each other, which you can optimize to 1 DB query per layer of the GraphQL query using dataloader patterns, with further optimizations for known common patterns.
Re:Many small queries are efficient in SQLite by heneryville on Tuesday February 16, 2021 @12:35PM (#26156440)
Some people do try to generate a single SQL query that covers all nested resolves. See Join Monster [1]. I'm skeptical that it would ever work well in SQL.

Datomic, being close to a graph database, makes constructing a single deep query for all resolvers fairly straight forward. This is the approach my team is taking now. It's worked quite a bit better than my DataLoader biased intuition suggested.

[1] https://join-monster.readthedocs.io/en/latest/

Re:Many small queries are efficient in SQLite by topspin on Tuesday February 16, 2021 @12:28PM (#26156344)
> But I'm not aware of anyone doing this.

I have, for a bespoke internal application. PostgreSQL is actually capable of expressing GraphQL queries as (rather elaborate) SQL queries. You end up generating queries that use a lot of LATERAL sub-selects. The risk is generating queries with poor performance, and that risk is high enough that I don't think this is a viable approach for complex applications.

Which is a shame.

Re:Many small queries are efficient in SQLite by rtorr on Tuesday February 16, 2021 @12:21PM (#26156236)
Yeah, I think there is a perception that people want direct queries into their database, but that is actually not the correct way to think about graphql.
Re:Many small queries are efficient in SQLite by ubercore on Tuesday February 16, 2021 @11:51AM (#26155904)
A straightforward implementation certainly would have that problem. You have to put a decent amount of effort in to reduce query count to fill a nested query graph.
Re:Many small queries are efficient in SQLite by rtorr on Tuesday February 16, 2021 @12:24PM (#26156284)
Not really. A straightforward implementation has nothing to do with database usage. A resolver is an endpoint. You can leverage that like you can in any other http backend server.
Re:Many small queries are efficient in SQLite by ubercore on Tuesday February 16, 2021 @01:45PM (#26157268)
Nested resolvers can incur extra queries as you follow down the tree, unless you put extra effort into pre-fetching what child nodes need, or some kind of data loader as GP suggested.

  query {
    parent {
      child {
        field
      }
    }
  }

A straightforward, naive implementation would resolve `parent`, then a resolver for `child` would execute, then for `field`. If `child` is a table linked by an FK, a second query would be executed, unless you pre-fetch the results through a join when resolving `parent`.
Re:Many small queries are efficient in SQLite by ex3ndr on Tuesday February 16, 2021 @02:21PM (#26157670)
No, replacing sqlite with leveldb/rocksdb/lmdb will give you ~10-50x improvement.
Re:Many small queries are efficient in SQLite by pdimitar on Tuesday February 16, 2021 @06:49PM (#26160457)
I'm interested. Any links with benchmarks or example applications demonstrating the difference?