4 ms·

I hear you and I 70% agree. I’ve also been using Prisma for a project recently and I’ve been finding it quite good. It layers several useful features on top of

I hear you and I 70% agree. I’ve also been using Prisma for a project recently and I’ve been finding it quite good. It layers several useful features on top of sql, like schema management (checked in to git), code gen (incl typescript types), db connection management, serialization / deserialization from JavaScript objects to sql rows and back, and it lets you follow foreign keys. If you want, you can also easily just make raw sql queries. That works fine too. Sql is a very old programming language, and it’s showing its age. The design is wacky - does GROUP BY go before or after ORDER BY in the query? Every command has custom, seemingly random words which are actually parameters. They are placed at totally arbitrary, hard to remember spots in the command. And what, we make our program output a string that gets immediately parsed by the sql server? What a horrible, stupid way to do RPC. It also gives terrible error messages if you get it wrong. And it’s awful to learn and debug. All of that is a huge pity, because modern databases can do so, so much. Doing logic inside the database can often be an order of magnitude faster than doing it in client code. SQL is a fantastic way to represent your data. One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql.

ninetyninenine 2y ago
>One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql. The problem here is these low level optimizations that the database is doing is freaking complicated. There's a lot of intelligence going on in the background and it's different depending on the database. We can solve it with a low level common IR but that IR will be extremely complex. And that IR will likely give different performance profiles for the same code on different databases. I can see this being solved for one database. But not across all databases and a common api. We sort of do have a common api and we paid for it with the trade off of it being a easier high level language on top of the part of the web that needs to be most optimized.
- bpicolo 2y ago
  > can solve it with a low level common IR Wouldn't that basically just be a query plan? The language is the easy part. Determining the optimal query plan based on rough statistics about the data and the DBs ability to go about that is the hard part. There are more general purpose query optimization libs out there, like Calcite
- josephg 2y ago
  > I can see this being solved for one database. But not across all databases and a common api. Database client libraries are already database-specific. Why boil the ocean? We could start with one database with this very easily until we figured out a good IR for queries. It could even be database-version specific if you wanted, through the use of wasm. Make the database client library embed a wasm VM. Then on startup, the database sends any connected client application the latest query optimizer / whatever logic in a compiled wasm bundle. This would allow the database to make improvements to the IR with successive versions without needing to update all the client libraries out there.
avianlyric 2y ago
> One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql. Erm, that’s already true. Proper clients will be parsing SQL locally and sending representations of the query, and its parameters separately. They may often be using a wire-format that’s very close to textual SQL, but parsing that SQL is hardly the slow part of executing a query. DB CPU load doesn’t come from parsing SQL, it comes from the query planning process, and the actual work of reading and filtering data. The planning process alone is extremely complex as the DB will be using all manner of statistical data collected about the type of data stored, and exactly how it’s stored, and how it various columns do or don’t correlate with each other, in order to estimate the best possible way of performing constructing the query plan. A process that factors in additional elements like the relative speeds of reading from disk, vs memory, and CPU cache, and how that varies with working set size. In addition, the real CPU load comes from the actual execution of that query. Streaming data off disks into memory, building various temporary in-memory data structures to accelerate joins and filters, there’s a lot of bytes that need to be shuffled around, and modern DB codebase are now optimising for the number of CPU cycles needed to operate tight inner loops. None of this work can be moved to the client, not unless you have a mechanism of streaming GB/s of data to your client on every query. To think of SQL as just an RPC protocol completely misses the point of SQL, or the incredible engineering involved in modern query planners. There a many reasons to replace SQL if something better, but the idea that it’s an inefficient RPC protocol, and that clients should have more fine-grained control of the planning and execution of queries, really isn’t one of them.
- josephg 2y ago
  > To think of SQL as just an RPC protocol completely misses the point of SQL, or the incredible engineering involved in modern query planners. There a many reasons to replace SQL if something better, but the idea that it’s an inefficient RPC protocol, and that clients should have more fine-grained control of the planning and execution of queries, really isn’t one of them. But SQL is used as an RPC protocol & wire format for getting data in and out of the database. Basically all web apps built on top of a sql database craft SQL strings for SELECT and UPDATE commands. The queries are very repetitive - since they're crafted once and run with each web request. I'm sure there's a lot of unnecessary work related to doing that over and over again constantly - and if that work isn't parsing strings, its generating the query plan. The format is also really awkward to use. SQL doesn't play very nice with most modern languages - since you generally either write the SQL string by hand, and then the types are opaque to the language. Or you get an ORM to do it - and then you can't easily customize the SQL string itself. Its complicated, but one approach to solving this would be to bundle up all that query planning code into a wasm blob and send it to database clients when they connect. The blob would contain any complex statistical code that the database uses. This would allow applications to create and save a query plan from a query string, and reuse it with subsequent queries. The wasm bundle would also contain all the binary serialization & deserialization code. Databases could then continue to evolve their query planners as they do today - but performance would be much better. And it would open the door to richer, lower level APIs for applications to customize & understand the query plans. And, I'm sure, figure out what types will be expected and returned from any given query.

ninetyninenine 2y ago

bpicolo 2y ago
> can solve it with a low level common IR Wouldn't that basically just be a query plan? The language is the easy part. Determining the optimal query plan based on rough statistics about the data and the DBs ability to go about that is the hard part. There are more general purpose query optimization libs out there, like Calcite
josephg 2y ago
> I can see this being solved for one database. But not across all databases and a common api. Database client libraries are already database-specific. Why boil the ocean? We could start with one database with this very easily until we figured out a good IR for queries. It could even be database-version specific if you wanted, through the use of wasm. Make the database client library embed a wasm VM. Then on startup, the database sends any connected client application the latest query optimizer / whatever logic in a compiled wasm bundle. This would allow the database to make improvements to the IR with successive versions without needing to update all the client libraries out there.

avianlyric 2y ago

> One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql. Erm, that’s already true. Proper clients will be parsing SQL locally and sending representations of the query, and its parameters separately. They may often be using a wire-format that’s very close to textual SQL, but parsing that SQL is hardly the slow part of executing a query. DB CPU load doesn’t come from parsing SQL, it comes from the query planning process, and the actual work of reading and filtering data. The planning process alone is extremely complex as the DB will be using all manner of statistical data collected about the type of data stored, and exactly how it’s stored, and how it various columns do or don’t correlate with each other, in order to estimate the best possible way of performing constructing the query plan. A process that factors in additional elements like the relative speeds of reading from disk, vs memory, and CPU cache, and how that varies with working set size. In addition, the real CPU load comes from the actual execution of that query. Streaming data off disks into memory, building various temporary in-memory data structures to accelerate joins and filters, there’s a lot of bytes that need to be shuffled around, and modern DB codebase are now optimising for the number of CPU cycles needed to operate tight inner loops. None of this work can be moved to the client, not unless you have a mechanism of streaming GB/s of data to your client on every query. To think of SQL as just an RPC protocol completely misses the point of SQL, or the incredible engineering involved in modern query planners. There a many reasons to replace SQL if something better, but the idea that it’s an inefficient RPC protocol, and that clients should have more fine-grained control of the planning and execution of queries, really isn’t one of them.

josephg 2y ago
> To think of SQL as just an RPC protocol completely misses the point of SQL, or the incredible engineering involved in modern query planners. There a many reasons to replace SQL if something better, but the idea that it’s an inefficient RPC protocol, and that clients should have more fine-grained control of the planning and execution of queries, really isn’t one of them. But SQL is used as an RPC protocol & wire format for getting data in and out of the database. Basically all web apps built on top of a sql database craft SQL strings for SELECT and UPDATE commands. The queries are very repetitive - since they're crafted once and run with each web request. I'm sure there's a lot of unnecessary work related to doing that over and over again constantly - and if that work isn't parsing strings, its generating the query plan. The format is also really awkward to use. SQL doesn't play very nice with most modern languages - since you generally either write the SQL string by hand, and then the types are opaque to the language. Or you get an ORM to do it - and then you can't easily customize the SQL string itself. Its complicated, but one approach to solving this would be to bundle up all that query planning code into a wasm blob and send it to database clients when they connect. The blob would contain any complex statistical code that the database uses. This would allow applications to create and save a query plan from a query string, and reuse it with subsequent queries. The wasm bundle would also contain all the binary serialization & deserialization code. Databases could then continue to evolve their query planners as they do today - but performance would be much better. And it would open the door to richer, lower level APIs for applications to customize & understand the query plans. And, I'm sure, figure out what types will be expected and returned from any given query.