31 ms·

Setting up SQL, creating a well-designed schema, and optimizing queries can be a complex, multi-step process. Databases often feel like black boxes, requiring s

Setting up SQL, creating a well-designed schema, and optimizing queries can be a complex, multi-step process. Databases often feel like black boxes, requiring significant training and engineering to get right. One needs to tweak both queries, schema and database configuration to get best results. So why do we need SQL? I know NoSQL came and went (was it nosql or no-relations? not sure...) but honestly I can't think of many things I can do with SQL that i CANNOT do easier in any programming language with a good library for indexing and retrieving data. Any good industrial strength key-value database like rocksdb, lmdb and others would provide these APIs with lots of bindings for multiple programming languages. Maybe we need a second coming of NoSQL (relational no-sql?) focused in providing good cross-language APIs so instead of compiling SQL into efficient programs that use the right indexes, we could just write our queries manually in code. Bad queries would be a lot easier to debug and fix this way, and it seems like this would require a lot less machinery.

beoberha 3y ago
The simple answer to this is that not only engineers write database queries. Personally, I’d be interested to see a system like the one you describe. Part of me thinks that SQL would still be simpler to reason about (though probably not to maintain).
- bobbylarrybobby 3y ago
  Polars is an interesting library in this space. A rust DataFrame with bindings in other languages, most notably python
Icathian 3y ago
I think you are drastically overestimating the ability of most devs to write performant code. Do you really think you're going to get better results asking devs who, by your measure cannot write good declarative SQL schemas and queries, to roll their own data storage and retrieval raw? RDBMS software is complicated in large part due to it needing to serve a wide range of use cases, so a custom-built version might end up simpler to use, but you can't sell me that Joe Backend-Dev is going to outperform however many million dev-hours have gone into Postgres.
- fiddlerwoaroof 3y ago
  I generally agree, but I think there’s a mistake here comparing something like Postgres to hand-rolled code: Postgres has to implement generally safe optimizations and so cannot take advantage of invariants that cannot be expressed in a way it understands. For a specific task, with well-understood data, I’d expect a typical programmer should be able to outperform Postgres with a bit of effort. The trade-off here is maintenance cost and a lack of flexibility due to specialization. So, IMO, one ought to reach for an SQL database first and then something else to optimize hot paths as determined by a profiler.
Vt71fcAqt7 3y ago
>relational no-sql Do you mean something like edgeDB?[0] Or do you mean some non-declarative language completely? I don't see the latter making much sense. The issue with SQL for me is the "natural language" which quickly loses all intended readabilty when you have SELECT col1, col2 FROM (SELECT * FROM ... WHERE 1=0 AND ... which is what edgeDB is trying to solve. [0]https://edgedb.com/ https://edgedb.com/
- cmrdporcupine 3y ago
  "It's like a relational database with an object-oriented data model" Dear god. Send these people back to a decent computer science program to study what the relational model actually is. They're embarrassing themselves. Or I'm embarrassed for them. Talk about abusing terms and throwing the baby out with the bath water. SQL is awful. It's an aberration (but a successful one, so). BUT the relational model is beautiful. AND these people clearly don't know what relational means. They appear to have made what in the old days would have been called a "network" database, where the relationships are hard coded in the data itself, aka pointers (they're calling them "links"). Which is anathema to the relational model which is based on sets of sets where the relationships emerge from the query (with hints from schema). The relational model does not have "links" or pointers. Its "keys" are really a suggestion (or constraint) but not a hard link. Relations are sets of sets (tuples) where those tuples can be joined arbitrarily and so it represents a fundamentally more flexible model where the relationships are not fixed, but emergent from the query. It was the existence of databases like this back in the 60s and 70s, and the intrinsic problems with them, that led to Codd's development of the relational model in the first place, as a way to free data from hardcoded relationships. "Graph relational" as a term could mean something I guess (see RelationalAI's product for example, though they don't use that term). But this is not this. "Object identity" is fundamentally philosophically opposed to the relational data model. Identity in the relational algebra is available through the comparison of arbitrary tuples emerging from operations, but is not intrinsic to any "row" or "tuple" or "object". ARGH. Buzzword marketing, incoherent conceptually. The world needs a successful non-SQL relational database, but this is not what this is.
nine_k 3y ago
OK, you got a good KV store, and a good indexing library in your favorite language. You now write special-case procedural code for each retrieval and each update. Eventually you get tired of copy-pasting such code with slight modifications; you factor things out. You make them generic. You end up with a poorly specified "language" for making queried, joins, projections. You notice how sometimes performance requires different approaches, and bake the logic tp choose them into your library. Then you realise that there are cases when you need two related updates either succeed or fail together, not halfway. Transactions. This all has been tried many times in 1990s when RDBMSes were pricey or otherwise unattainable. Paradox engine, various stuff on top of Berkeley DB, etc. MySQL is) or at least was) an SQL engine on top of a KV storage engine. Now we have Postgres that fills most small- and median-scale needs out of the box. A modern RDBMS is one of the most powerful and versatile tools in developer's hands, and usually works amazingly well even with stock settings. Usually it by far outperforms makeshift replacements to it. Learn to operate an RDBMS, learn to wield this power. It's very much humanly possible, and is much nicer experience than learning, say, C++. Then maybe you will have less of a desire to replace it with manually written imperative code.
theLiminator 3y ago
Imo lazy dataframe libraries (like polars or datafusion) give you most of what you need in OLAP SQL but without the pain.
paulddraper 3y ago
> Setting up SQL You'll have to set up something. > creating a well-designed schema You'll have to design your schema well. (Or else.) > optimizing queries can be a complex, multi-step process. You'll have to optimize your queries. (Or else.) --- I don't see why SQL is more owrk than the alternative.
iddan 3y ago
I thought like this until I discovered Prisma and EdgeDB. They provide all the niceties of a NoSQL / graph databases with the robustness of battle tested SQL databases
RobinL 3y ago
I found this comment in a previous discussion insightful (https://news.ycombinator.com/item?id=34580448 https://news.ycombinator.com/item?id=34580448) This simple command `UPDATE users SET preference = 'blue' WHERE id = 123` virtually contains: * concurrency control * statistics, which will help: * execution plans evaluation, which will reduce IO cost with the help of: * (several categories of) indexes * type checking * data invariants checking * point in time recovery * enables dataset-wide backup strategies * ISO standard way of structuring data * an interface that empowers business people And that's just a few items off the top of my mind.

beoberha 3y ago

The simple answer to this is that not only engineers write database queries. Personally, I’d be interested to see a system like the one you describe. Part of me thinks that SQL would still be simpler to reason about (though probably not to maintain).

bobbylarrybobby 3y ago
Polars is an interesting library in this space. A rust DataFrame with bindings in other languages, most notably python

Icathian 3y ago

I think you are drastically overestimating the ability of most devs to write performant code. Do you really think you're going to get better results asking devs who, by your measure cannot write good declarative SQL schemas and queries, to roll their own data storage and retrieval raw? RDBMS software is complicated in large part due to it needing to serve a wide range of use cases, so a custom-built version might end up simpler to use, but you can't sell me that Joe Backend-Dev is going to outperform however many million dev-hours have gone into Postgres.

fiddlerwoaroof 3y ago
I generally agree, but I think there’s a mistake here comparing something like Postgres to hand-rolled code: Postgres has to implement generally safe optimizations and so cannot take advantage of invariants that cannot be expressed in a way it understands. For a specific task, with well-understood data, I’d expect a typical programmer should be able to outperform Postgres with a bit of effort. The trade-off here is maintenance cost and a lack of flexibility due to specialization. So, IMO, one ought to reach for an SQL database first and then something else to optimize hot paths as determined by a profiler.

Vt71fcAqt7 3y ago

>relational no-sql Do you mean something like edgeDB?[0] Or do you mean some non-declarative language completely? I don't see the latter making much sense. The issue with SQL for me is the "natural language" which quickly loses all intended readabilty when you have SELECT col1, col2 FROM (SELECT * FROM ... WHERE 1=0 AND ... which is what edgeDB is trying to solve. [0]https://edgedb.com/ https://edgedb.com/

cmrdporcupine 3y ago
"It's like a relational database with an object-oriented data model" Dear god. Send these people back to a decent computer science program to study what the relational model actually is. They're embarrassing themselves. Or I'm embarrassed for them. Talk about abusing terms and throwing the baby out with the bath water. SQL is awful. It's an aberration (but a successful one, so). BUT the relational model is beautiful. AND these people clearly don't know what relational means. They appear to have made what in the old days would have been called a "network" database, where the relationships are hard coded in the data itself, aka pointers (they're calling them "links"). Which is anathema to the relational model which is based on sets of sets where the relationships emerge from the query (with hints from schema). The relational model does not have "links" or pointers. Its "keys" are really a suggestion (or constraint) but not a hard link. Relations are sets of sets (tuples) where those tuples can be joined arbitrarily and so it represents a fundamentally more flexible model where the relationships are not fixed, but emergent from the query. It was the existence of databases like this back in the 60s and 70s, and the intrinsic problems with them, that led to Codd's development of the relational model in the first place, as a way to free data from hardcoded relationships. "Graph relational" as a term could mean something I guess (see RelationalAI's product for example, though they don't use that term). But this is not this. "Object identity" is fundamentally philosophically opposed to the relational data model. Identity in the relational algebra is available through the comparison of arbitrary tuples emerging from operations, but is not intrinsic to any "row" or "tuple" or "object". ARGH. Buzzword marketing, incoherent conceptually. The world needs a successful non-SQL relational database, but this is not what this is.

nine_k 3y ago

OK, you got a good KV store, and a good indexing library in your favorite language. You now write special-case procedural code for each retrieval and each update. Eventually you get tired of copy-pasting such code with slight modifications; you factor things out. You make them generic. You end up with a poorly specified "language" for making queried, joins, projections. You notice how sometimes performance requires different approaches, and bake the logic tp choose them into your library. Then you realise that there are cases when you need two related updates either succeed or fail together, not halfway. Transactions. This all has been tried many times in 1990s when RDBMSes were pricey or otherwise unattainable. Paradox engine, various stuff on top of Berkeley DB, etc. MySQL is) or at least was) an SQL engine on top of a KV storage engine. Now we have Postgres that fills most small- and median-scale needs out of the box. A modern RDBMS is one of the most powerful and versatile tools in developer's hands, and usually works amazingly well even with stock settings. Usually it by far outperforms makeshift replacements to it. Learn to operate an RDBMS, learn to wield this power. It's very much humanly possible, and is much nicer experience than learning, say, C++. Then maybe you will have less of a desire to replace it with manually written imperative code.

theLiminator 3y ago

Imo lazy dataframe libraries (like polars or datafusion) give you most of what you need in OLAP SQL but without the pain.

paulddraper 3y ago

> Setting up SQL You'll have to set up something. > creating a well-designed schema You'll have to design your schema well. (Or else.) > optimizing queries can be a complex, multi-step process. You'll have to optimize your queries. (Or else.) --- I don't see why SQL is more owrk than the alternative.

iddan 3y ago

I thought like this until I discovered Prisma and EdgeDB. They provide all the niceties of a NoSQL / graph databases with the robustness of battle tested SQL databases

RobinL 3y ago

I found this comment in a previous discussion insightful (https://news.ycombinator.com/item?id=34580448 https://news.ycombinator.com/item?id=34580448) This simple command `UPDATE users SET preference = 'blue' WHERE id = 123` virtually contains: * concurrency control * statistics, which will help: * execution plans evaluation, which will reduce IO cost with the help of: * (several categories of) indexes * type checking * data invariants checking * point in time recovery * enables dataset-wide backup strategies * ISO standard way of structuring data * an interface that empowers business people And that's just a few items off the top of my mind.