7 ms·

The Database Rant

44 points by arohner 18y ago

jrockway 18y ago
It sounds like his ideal database already exists; AllegroCache. It's an object database, but you can easily restrict yourself to certain data types to make it "relational", if that's what you're after. Some of the open source databases are almost as featureful; Elephant for CL and KiokuDB for Perl come to mind.
neilc 18y ago
Current databases don't give the developer a way to bypass the optimizer. Sure they do. Oracle and other commercial DBs provide "planner hints", which essentially force the optimizer to use a particular access method / join method for a given part of the plan. why does SQL decide that declarative style is the One True Way to get access to your data Well, part of the reason for a query optimizer is that if the physical representation of the data changes, the queries don't need to change. So that if you add or remove an index, you don't need to adjust all the queries on the table, for example. Obviously the query planner is imperfect, but planner hints are fairly common in practice. The further you stray from declarative queries, the more application changes are needed when physical database properties change. Datatypes don't match your PL If you want a strongly-typed database system that can be accessed from any language, you need the DB's type system to be distinct from any of the client language's type systems. This isn't always the right tradeoff, of course.
- arohner 18y ago
  part of the reason for a query optimizer is that if the physical representation of the data changes, the queries don't need to change. And that's a great reason to use the declarative query as the default. What I'm complaining about is when you need to eek out the last bit of performance out of a query, there should be another option. Sort of like dropping into assembly to optimize a tight loop. Of course you don't want to write the entire program in assembly. you need the DB's type system to be distinct from any of the client language's type systems. I don't agree. Just because your DB's type system uses say, Java, doesn't mean that other languages can't access the DB. Non-Java languages would access the DB the same way they do today, but at least the Java developers wouldn't have to convert.
iigs 18y ago
Have you looked at http://en.wikipedia.org/wiki/MonetDB http://en.wikipedia.org/wiki/MonetDB ? It exposes some of the DB guts at a different layer while leaving a SQL "porcelain" feature available for people who desire that.
yummyfajitas 18y ago
His point about composability is not a complaint about what sql does. It's merely a complaint that he thinks that function composition should be used to combine predicates, but that isn't how sql works. I.e., he wants "SELECT * from X where P(x)" to be a function acting in X. It isn't a function (or at least should not be thought of that way), it's a description of a set: {x : x in X, P(x)} The predicates ARE composable: {x : x in X, P(x)} intersect {x : x in X, Q(x)} == {x : x in X, P(x) and Q(x)}
- arohner 18y ago
  Author here. I was thinking in terms of an entire query being a function that returns data. Also notice that your set notation demonstrates composability, but the equivalent SQL does not.
  - neilc 18y ago
    How is your example SQL "not composable"? Notice that this solution is composed of the first two queries, however composing in SQL kills your performance, and this is generally not the right way to structure the query. It doesn't actually "kill" performance at all, as a typical SQL optimizer will rewrite the query to remove the subquery, or else to push the outer predicate down into the subquery (which for this query accomplishes basically the same thing). I was thinking in terms of an entire query being a function that returns data. A query returns a relation, and that relation can be used as the input relation to another query, either via a subquery or via views. That seems like pretty powerful composability to me.
    - davidmathers 18y ago
      A query returns a relation Actually it doesn't. That's one of the problems with SQL and part of the reason SQL databases aren't truly relational databases.
      - neilc 18y ago
        Actually it doesn't. Can you elaborate on what you mean? The presence of duplicates is orthogonal, if that's what you're referring to. The point is that the output of a query can be consumed by another query (either via views or subqueries), which enables query composition and logical data independence. SQL databases aren't truly relational databases. People who get hot and bothered about that are almost invariably cranks, in my experience.
andr 18y ago
SQL was invented in the 70s for business analysts to write reports without having to go bug the programmers. So what? Unix was invented in the 70s because Ken Thompson wanted to play Space Travel. [1] Queries are not composable. Per your example, you want to throw out SQL because using AND and OR doesn't feel right. I'd love to see you compose a 10-line SQL query. Also, views ARE query composition. PL/SQL is primitive by today's standards, yet it's the only game in town for certain classes of features, like stored procedures and triggers. Untrue. Oracle, the creators of PL/SQL, now let you write stored procedures in any JVM or .NET language (so Jython, Clojure, etc.). MS SQL supports .NET stored procedures. PostgreSQL supports Tcl, Perl and Python. Also, your alternative suggestion of a simple and stupid data store DOES involve fetching all the data from the database into RAM and operating on it in your application. You can already do that with SQL. It's not obvious which queries are slow. Untrue. MySQL can log slow queries and help you analyze them in real time using tools like mytop. I assume similar tools are available for all databases. When you write code it's not always obvious if it will be slow, either. No back door for map, filter, reduce Isn't the WHERE clause filter? Isn't GROUP BY very similar to reduce? SELECT UPPER(firstname) is map? With the ability to define custom functions in different languages that's a fairly powerful tool. Also, comparisons between MapReduce and SQL are misguided, because MapReduce does not operate on tabular data. Finally, your solution is based entirely on the idea that people will prefer Lisp to SQL. SQL is not perfect for everything, but if you want to suggest a better alternative, start with a solid argument. [1] http://en.wikipedia.org/wiki/Unix#History http://en.wikipedia.org/wiki/Unix#History
- tvon 18y ago
  Please quote with '>', not by indenting.
  - tokenadult 18y ago
    My convention is to quote the submitted article with quotation marks, like this: "SQL was invented in the 70s for business analysts to write reports without having to go bug the programmers." And then I quote the parent comment with the HN convention of preceding and following the quoted text with asterisks to italicize it, like this: Please quote with '>', not by indenting. But, yeah, definitely not by indenting.
russell 18y ago
Any attempt to throw away SQL is misguided. If you are going to do server side development, you must know SQL. Thinking in sets is hard work, but you must learn it. For the most part fancy ORM's just get in the way. The solution is not less SQL; it's more SQL. Stored procedures are your friends. When I write a complex query, I develop and test it entirely in SQL, before I write a line of procedural code. It's way easier. The thing about query optimization is that the optimizer stops when it thinks going on will take longer to perfect it than executing what it has. If you have a query that takes too long look at the query plan. Break up the query into smaller queries and use temporary tables. Give it hints. Dates are often poorly handle in language libraries. Store them as UTC in the database and pound them into shape in code. Apologies for the counter rant.
- davidmathers 18y ago
  Any attempt to throw away SQL is misguided. Maybe not throw away, but why should we have only 1 relational language? Functional programmers can choose between scheme, ocaml and haskell. Why should we be stuck with what is in so many ways the COBOL of relational languages as our only option?
  - russell 18y ago
    What I meant was that, as far as I have seen, the tools that try to replace SQL by some OO intermediary, give you poorer abstractions that limit what you can do. SQL seems clunky, but I think the core is quite well done considering when it comes from. OTOH a relational relational database doesn't fit all needs. They scale pretty well, but when you get to gigantic data sets, they don't do so well and things like map reduce do a better job. Or cases where you are streaming massive amounts of data and you need performance, not ACID transaction reliability. Actually, we are more in agreement than disagreement.
    - neilc 18y ago
      They scale pretty well, but when you get to gigantic data sets, they don't do so well and things like map reduce do a better job. I don't think the evidence supports that. See the recent MR vs. parallel DB comparison, for example: http://database.cs.brown.edu/sigmod09/ http://database.cs.brown.edu/sigmod09/ There isn't a good massively parallel open source SQL implementation right now, but that is just a matter of engineering -- SQL databases have been shown to scale to massive data volumes (e.g. Fox Interactive have ~200 TB of user data in Greenplum, which is a parallel DB based on Postgres).
vomjom 18y ago
I was all prepared to rant about how wrong he was, but he convinced me. I've had all kinds of problems with PostgreSQL because its planner would choose a poor query plan in certain situations. Specifically, we don't have a good way of estimating random page fetches for index scans on multi-column indexes. I also had this weird problem suddenly occur. When one of my tables got to a certain size (about 40 million rows), Postgres started giving a completely different query plan that it thought was faster. The query execution time went from a few minutes to many hours. It took me all day to debug this problem because you simply don't expect the query plan to suddenly change like that. To tell Postgres that you want the query to run in a different way, you must turn off certain things, like disabling sequential table scans or bitmap scans. You can't tell it specifically how you want it to execute. The reason is pretty simple: SQL is a declarative language. You declare want results you want, and the server figures out how it'll get you there. I think the author is right. We need a simple language where we can tell the server exactly how we want to get the result. This is essential, because, like the author said, finding the optimal query plan is NP-hard. In practice, most programs run only a few queries. It'd be worth the time invested to try out a few query plans and find out which one is the best and stick with that over having the SQL server replan the query almost every time you submit it. There could even be third party planners where it could take SQL input and return the resulting executable s-expr if you want to retain that functionality. This way, the development of planners remains separate from the development of the database.
zandorg 18y ago
I recently made a GUI for a search utility which is filled in, and then it builds an SQL query from the GUI, and then displays the results of the query in another GUI. My point is that you can map a search to whatever language you like - whichever is best for that utility. Plus a friend told me about Linq, which is an interesting evolution from SQL. It lets you break up queries over several cores (though that's just because it's iterative in nature).
vozoscuro 18y ago
how about a 10+ table join on f(any function)?
TweedHeads 18y ago
Those who complain about SQL just haven't used Oracle, fully.
jasonkester 18y ago
I never went through this phase myself, but I see a lot of developers stuck in it. It's this sort of exasperated anger towards a technology that the author has tried his best to understand but failed. For some reason some people never really wrap their head around Relational Databases, Normalization, and SQL. They seem to not be wired for it. And it understandably pisses them off. It's people just like this author that are behind all those terrible Object Relational Mapper projects that try to pretend that your data isn't really data. It's just an attempt to escape the obligation of truly understanding how databases work and how they can help you. It's sadly ironic when you think about it.
- arohner 18y ago
  No, I'm pretty sure I understand the technology. I've written thousands of lines of queries, triggers, stored procedures. I've read the source to Postgres. In the article, I specifically say ORMs are a terrible idea. I specifically say Relational databases, data integrity, foreign key constraints, declarative queries and ACID are all good things. I forgot to mention normalization, but that's good too. My point is that SQL is a terrible interface to get to all of the good things about RDBMs.

44 points by arohner 18y ago

It sounds like his ideal database already exists; AllegroCache. It's an object database, but you can easily restrict yourself to certain data types to make it "relational", if that's what you're after. Some of the open source databases are almost as featureful; Elephant for CL and KiokuDB for Perl come to mind.

neilc 18y ago

Current databases don't give the developer a way to bypass the optimizer. Sure they do. Oracle and other commercial DBs provide "planner hints", which essentially force the optimizer to use a particular access method / join method for a given part of the plan. why does SQL decide that declarative style is the One True Way to get access to your data Well, part of the reason for a query optimizer is that if the physical representation of the data changes, the queries don't need to change. So that if you add or remove an index, you don't need to adjust all the queries on the table, for example. Obviously the query planner is imperfect, but planner hints are fairly common in practice. The further you stray from declarative queries, the more application changes are needed when physical database properties change. Datatypes don't match your PL If you want a strongly-typed database system that can be accessed from any language, you need the DB's type system to be distinct from any of the client language's type systems. This isn't always the right tradeoff, of course.

arohner 18y ago
part of the reason for a query optimizer is that if the physical representation of the data changes, the queries don't need to change. And that's a great reason to use the declarative query as the default. What I'm complaining about is when you need to eek out the last bit of performance out of a query, there should be another option. Sort of like dropping into assembly to optimize a tight loop. Of course you don't want to write the entire program in assembly. you need the DB's type system to be distinct from any of the client language's type systems. I don't agree. Just because your DB's type system uses say, Java, doesn't mean that other languages can't access the DB. Non-Java languages would access the DB the same way they do today, but at least the Java developers wouldn't have to convert.

iigs 18y ago

Have you looked at http://en.wikipedia.org/wiki/MonetDB http://en.wikipedia.org/wiki/MonetDB ? It exposes some of the DB guts at a different layer while leaving a SQL "porcelain" feature available for people who desire that.

yummyfajitas 18y ago

His point about composability is not a complaint about what sql does. It's merely a complaint that he thinks that function composition should be used to combine predicates, but that isn't how sql works. I.e., he wants "SELECT * from X where P(x)" to be a function acting in X. It isn't a function (or at least should not be thought of that way), it's a description of a set: {x : x in X, P(x)} The predicates ARE composable: {x : x in X, P(x)} intersect {x : x in X, Q(x)} == {x : x in X, P(x) and Q(x)}

arohner 18y ago
Author here. I was thinking in terms of an entire query being a function that returns data. Also notice that your set notation demonstrates composability, but the equivalent SQL does not.
- neilc 18y ago
  How is your example SQL "not composable"? Notice that this solution is composed of the first two queries, however composing in SQL kills your performance, and this is generally not the right way to structure the query. It doesn't actually "kill" performance at all, as a typical SQL optimizer will rewrite the query to remove the subquery, or else to push the outer predicate down into the subquery (which for this query accomplishes basically the same thing). I was thinking in terms of an entire query being a function that returns data. A query returns a relation, and that relation can be used as the input relation to another query, either via a subquery or via views. That seems like pretty powerful composability to me.
  - davidmathers 18y ago
    A query returns a relation Actually it doesn't. That's one of the problems with SQL and part of the reason SQL databases aren't truly relational databases.
    - neilc 18y ago
      Actually it doesn't. Can you elaborate on what you mean? The presence of duplicates is orthogonal, if that's what you're referring to. The point is that the output of a query can be consumed by another query (either via views or subqueries), which enables query composition and logical data independence. SQL databases aren't truly relational databases. People who get hot and bothered about that are almost invariably cranks, in my experience.

andr 18y ago

SQL was invented in the 70s for business analysts to write reports without having to go bug the programmers. So what? Unix was invented in the 70s because Ken Thompson wanted to play Space Travel. [1] Queries are not composable. Per your example, you want to throw out SQL because using AND and OR doesn't feel right. I'd love to see you compose a 10-line SQL query. Also, views ARE query composition. PL/SQL is primitive by today's standards, yet it's the only game in town for certain classes of features, like stored procedures and triggers. Untrue. Oracle, the creators of PL/SQL, now let you write stored procedures in any JVM or .NET language (so Jython, Clojure, etc.). MS SQL supports .NET stored procedures. PostgreSQL supports Tcl, Perl and Python. Also, your alternative suggestion of a simple and stupid data store DOES involve fetching all the data from the database into RAM and operating on it in your application. You can already do that with SQL. It's not obvious which queries are slow. Untrue. MySQL can log slow queries and help you analyze them in real time using tools like mytop. I assume similar tools are available for all databases. When you write code it's not always obvious if it will be slow, either. No back door for map, filter, reduce Isn't the WHERE clause filter? Isn't GROUP BY very similar to reduce? SELECT UPPER(firstname) is map? With the ability to define custom functions in different languages that's a fairly powerful tool. Also, comparisons between MapReduce and SQL are misguided, because MapReduce does not operate on tabular data. Finally, your solution is based entirely on the idea that people will prefer Lisp to SQL. SQL is not perfect for everything, but if you want to suggest a better alternative, start with a solid argument. [1] http://en.wikipedia.org/wiki/Unix#History http://en.wikipedia.org/wiki/Unix#History

tvon 18y ago
Please quote with '>', not by indenting.
- tokenadult 18y ago
  My convention is to quote the submitted article with quotation marks, like this: "SQL was invented in the 70s for business analysts to write reports without having to go bug the programmers." And then I quote the parent comment with the HN convention of preceding and following the quoted text with asterisks to italicize it, like this: Please quote with '>', not by indenting. But, yeah, definitely not by indenting.

russell 18y ago

Any attempt to throw away SQL is misguided. If you are going to do server side development, you must know SQL. Thinking in sets is hard work, but you must learn it. For the most part fancy ORM's just get in the way. The solution is not less SQL; it's more SQL. Stored procedures are your friends. When I write a complex query, I develop and test it entirely in SQL, before I write a line of procedural code. It's way easier. The thing about query optimization is that the optimizer stops when it thinks going on will take longer to perfect it than executing what it has. If you have a query that takes too long look at the query plan. Break up the query into smaller queries and use temporary tables. Give it hints. Dates are often poorly handle in language libraries. Store them as UTC in the database and pound them into shape in code. Apologies for the counter rant.

davidmathers 18y ago
Any attempt to throw away SQL is misguided. Maybe not throw away, but why should we have only 1 relational language? Functional programmers can choose between scheme, ocaml and haskell. Why should we be stuck with what is in so many ways the COBOL of relational languages as our only option?
- russell 18y ago
  What I meant was that, as far as I have seen, the tools that try to replace SQL by some OO intermediary, give you poorer abstractions that limit what you can do. SQL seems clunky, but I think the core is quite well done considering when it comes from. OTOH a relational relational database doesn't fit all needs. They scale pretty well, but when you get to gigantic data sets, they don't do so well and things like map reduce do a better job. Or cases where you are streaming massive amounts of data and you need performance, not ACID transaction reliability. Actually, we are more in agreement than disagreement.
  - neilc 18y ago
    They scale pretty well, but when you get to gigantic data sets, they don't do so well and things like map reduce do a better job. I don't think the evidence supports that. See the recent MR vs. parallel DB comparison, for example: http://database.cs.brown.edu/sigmod09/ http://database.cs.brown.edu/sigmod09/ There isn't a good massively parallel open source SQL implementation right now, but that is just a matter of engineering -- SQL databases have been shown to scale to massive data volumes (e.g. Fox Interactive have ~200 TB of user data in Greenplum, which is a parallel DB based on Postgres).

vomjom 18y ago

I was all prepared to rant about how wrong he was, but he convinced me. I've had all kinds of problems with PostgreSQL because its planner would choose a poor query plan in certain situations. Specifically, we don't have a good way of estimating random page fetches for index scans on multi-column indexes. I also had this weird problem suddenly occur. When one of my tables got to a certain size (about 40 million rows), Postgres started giving a completely different query plan that it thought was faster. The query execution time went from a few minutes to many hours. It took me all day to debug this problem because you simply don't expect the query plan to suddenly change like that. To tell Postgres that you want the query to run in a different way, you must turn off certain things, like disabling sequential table scans or bitmap scans. You can't tell it specifically how you want it to execute. The reason is pretty simple: SQL is a declarative language. You declare want results you want, and the server figures out how it'll get you there. I think the author is right. We need a simple language where we can tell the server exactly how we want to get the result. This is essential, because, like the author said, finding the optimal query plan is NP-hard. In practice, most programs run only a few queries. It'd be worth the time invested to try out a few query plans and find out which one is the best and stick with that over having the SQL server replan the query almost every time you submit it. There could even be third party planners where it could take SQL input and return the resulting executable s-expr if you want to retain that functionality. This way, the development of planners remains separate from the development of the database.

zandorg 18y ago

I recently made a GUI for a search utility which is filled in, and then it builds an SQL query from the GUI, and then displays the results of the query in another GUI. My point is that you can map a search to whatever language you like - whichever is best for that utility. Plus a friend told me about Linq, which is an interesting evolution from SQL. It lets you break up queries over several cores (though that's just because it's iterative in nature).

vozoscuro 18y ago

how about a 10+ table join on f(any function)?

TweedHeads 18y ago

Those who complain about SQL just haven't used Oracle, fully.

jasonkester 18y ago

I never went through this phase myself, but I see a lot of developers stuck in it. It's this sort of exasperated anger towards a technology that the author has tried his best to understand but failed. For some reason some people never really wrap their head around Relational Databases, Normalization, and SQL. They seem to not be wired for it. And it understandably pisses them off. It's people just like this author that are behind all those terrible Object Relational Mapper projects that try to pretend that your data isn't really data. It's just an attempt to escape the obligation of truly understanding how databases work and how they can help you. It's sadly ironic when you think about it.

arohner 18y ago
No, I'm pretty sure I understand the technology. I've written thousands of lines of queries, triggers, stored procedures. I've read the source to Postgres. In the article, I specifically say ORMs are a terrible idea. I specifically say Relational databases, data integrity, foreign key constraints, declarative queries and ACID are all good things. I forgot to mention normalization, but that's good too. My point is that SQL is a terrible interface to get to all of the good things about RDBMs.