Hacker News Reader

Comment by brightball | Hacker News Reader

I don't disagree that SQL can be improved. It's one of the biggest reasons I use Postgres in the first place because there are so many improvements available on top of SQL.

All that said...SQL is pretty darn effective. As a language, it's the true backbone of the internet today. It's readable, explicit, fairly concise and naturally translates to how data should be broken down for efficient storage...or make some trade-offs to allow for more efficient retrieval.

There are differences with different vendor implementations...but that's what different vendors are for - to find things the other guys are doing wrong and improving on them to build a better product.

I wish the folks luck in their work to improve things, but the language I've been able to rely on consistently over the last 17 years or so has been SQL...and I've worked with a lot of languages. SQL is the one that gets the most done reliably and lets me down the least often.

View on HN

Afaict, the state of SQL as a grammar with tooling is kind of pathetic.

As a standardized language, it doesn’t really exist; everyone implements numerous extensions, and almost no one is fully ansi compliant

Almost all formatters attempt to be generic (believing standardization exists), and fail to support the full grammar for any dialect.

Across the board, all parsers have pathetic error message support (error on line 3, which is actually just the start of the statement).

The schema offers type constraints, but querying/ide’s extract no value from that (that is, types are statically specified/constrained, but query editors all pretend its fully dynamic)

Theres a lot of awkward nonsense, like where clauses are parsed before the select in most parsing engines, causing alias usage to fail without wrapping in a subselect/with clause

The grammars themselves are an inconsistent, ad-hoc mess

The grammar is also unnecessarily context-dependent (eg from must follow select, and where after that), making programmatic composition unnecessarily difficult

I don’t know how much of the tooling issue is a result of SQL as a language versus the history itself, but I can at least confirm that trying to parse multiple dialects is absolute hell, which would at least explain the sorry state of affairs for eg formatters.

But the majority of its expressive power derives from the relational algebra, and has nothing to do with the SQL grammar, and thats the majority of its value. It seems obvious to me that at the very least the compositional issue of SQL, and its self-inconsistent grammar, should be vulnerable to near-lossless improvement without too much struggle, though I can’t say what the alternative would actually look like.

But it seems like its riddled with a lot of unnecessary flaws

Actually standards compliance is really good these days. There are a lot of custom functions that are hard to do without in some reporting applications but behavior of SQL clauses is consistent across every engine.

JetBrains tooling does use schema metadata for it's autocompletion. I'm not sure though if it's anywhere close to full-on Haskell autocompletion in the Atom editor (very fiddly and prone to break on minor version changes, I must say).

Offtopic, but I am genuinely curious now about whether Atom's Haskell autocompletion is significantly better than in other editors like VSCode, or (n)vim with things like YouCompleteMe/NeoComplete/Deocomplete etc. Have you used other editors? What is your opinion?

The SQL spec is actually hidden away behind some book or something you have to purchase.

I was experimenting around with creating an Entity Framework equivalent in Typescript and really wanted to create a SQL AST for use under the hood(optimizing queries, SQL push down, etc). Ended up using the PostgreSQL types and a Ruby plugin that binds some PostgreSQL libs to work on my POC. Crazy town.

Big boys SQL is relatively close to the standard.

I’ve been writing some pretty ambitious Hive queries at work lately.

As I learn more about SQL and pull off more complex queries, my respect for it deepens. To have such power and support so many use cases with so few constructs is really an engineering feat. It’s timeless for a reason.

Some of my relatively common access patterns are awkward to express, but they can still be expressed in a few lines + a CTE or two, which is really impressive for a language so small.

This is not to say we can’t do better. But SQL has achieved a deep resonance with its problem space that most tools don’t even come close to. The brightest minds and most effective tooling shops in our field would be lucky merely to do as well.

It supports so many use cases primarily because of the small number of fundamental "constructs".

I mean, if I gave you protons, electrons and neutrons, you could build the universe out of them!

That doesn't make sense, if I give you more things (I'm avoiding saying 'atom', but you know what I mean) then it's not the case that you can suddenly do less.

Expressiveness isn't inversely correlated to number of constructs.

Let's say you gave me an atom. In this situation, you can't create other atoms. When you give me protons, electrons and neutrons, I can create any atom I like, in addition to anything else in the universe. Thus, when you give me an atom, you actually reduce the possibilities somewhat.

In reality, it's not just about the small number of constructs, but also about how fundamental they are. In one sense, atoms are less fundamental than protons/neutrons/electrons, and therefore reduce the number of possible creations.

> Let's say you gave me an atom. In this situation, you can't create other atoms. When you give me protons, electrons and neutrons, I can create any atom I like, in addition to anything else in the universe. Thus, when you give me an atom, you actually reduce the possibilities somewhat.

> In reality, it's not just about the small number of constructs, but also about how fundamental they are. In one sense, atoms are less fundamental than protons/neutrons/electrons, and therefore reduce the number of possible creations.

I think what OJFord is getting at is, what if you had more subatomic particles to build with?

The more fundamental you go, the lesser number of things you need to form a "complete set".

Thus, whatever the complete set is for subatomic particles, it will most certainly have lesser members than for the complete set of atoms.

Getting back to my original point, SQL operates at a fundamental enough level that you do not need a great number of "constructs" to create a query that gives you whatever it is that you need.

I'm not saying it's perfect, but was just supplementing the OPs observation that SQL has surprisingly few "constructs".

I think your premise might be flawed.

Are we just assuming that protons and neutrons automatically pull in the quarks, neutrinos, other leptons and force carriers as well? Does the electron have a photon dependency? What about dark matter?

If not, then the our three particles are internally inconsistent and woefully incapable of building a universe. Otherwise, our set of dependencies is essentially the whole darn universe anyway.

Same goes for atoms. Give me enough and I can build a star or particle accelerator to create whatever elements or fundamental particles I want.

Wow, it was a simplistic passing metaphor. I was not expecting to get into the complexities of physics, but simply that if you operate at a more fundamental level, you can generally address a greater range of problems.

The point is that your metaphor is broken and incorrect. GP's point stands. Abstractions are leaky.

Even leaky abstractions can be useful, if they are taken in context. If you choose not to understand the spirit of my comment, and take its meaning literally to the point of it having no utility for you, then so be it. A significant number of upvotes suggest GP's point may be moot, and, dare I say, pedantic.

Also, the GPs suggestion of building an accelerator is as silly as someone writing an assembly compiler via a mush of SQL. It probably can be done, but it's not in the spirit of the topic!

Fair point. Abstractions, leaky or otherwise, are helpful! It does seem a bit like we've successfully abstracted the conversation into oblivion though. hehe

Anyway, I feel like your original comment and my reply are kind of doing the same thing at the meta-conversation level. We're stretching a metaphor in order to make some counterpoint argument. My intent with the excessive pedantry was to highlight the farce of conflating rough metaphor with substantive insight.

Anyway, internet conversations are hard. Thanks for engaging!

In your analogy, SQL is the periodic table.

Depends on whether photons exist or not.

Radiation makes the universe a lot more interesting.

I think you mean the relational model is timeless. SQL is simply just not a great language, for an otherwise great idea.

Blindly asserting that SQL is bad without providing any argument or proof does not contradict OP's opinion on SQL.

The article itself gives a pretty decent description of how the language falls down.. and the ggp’s expression of his impression pretty trivially just maps to the relational model/algebra.... so all necessary arguments have been made :-)

really? the article has a handful of strange edge cases that you'll almost never smack into in practice, and that's cause to say the language is crap?

Given the vast usage of SQL and its overwhelming popularity, if there were real problems with the language then there would be a lot more noise about them. But most people seem to be happy with it.

If it's not broken, let's not fix it, eh?

The only edge cases I could find in the article were the discussion of nulls... which aren’t so much an edge case as they are just an ever-present problem that no one really notices until it hurts (its not at all difficult to write a broken-on-null query). Is there something else you’re referring to? Afaict, everything else was just trivially observable aspects of the language

>Given the vast usage of SQL and its overwhelming popularity, if there were real problems with the language then there would be a lot more noise about them. But most people seem to be happy with it.

In what universe does popularity imply quality? Certainly not the one I’m in — Avengers endgame is apparently #2 gross worldwide ever :-)

I think the point he is making is that why hasn't anything better come up to better represent the relational model in over 40 years?

There are other models as well eg datalog, but popularity is a factor of many things — a major factor with databases is that historically the engine is far more important than the language; you would expect users to choose an engine, and whatever language came with it. Which implies that the decision is on the producer side, not the consumers.

Which finally implies that usage popularity is not adequately explained by user preference, because you wouldn’t expect user language preference to have significant impact on the decision making process.

But regardless, are you also going to claim that C, Oracle, IBM, Microsoft, etc were optimal in their respective fields, during their respective eras, because they held total market domination? Incumbency is one hell if a drug..

Bandwaggoning and deffering to the status quo is not argument for quality; there are too many factors involved beyond quality itself. Fixable flaws in the language have been pointed out and not been addressed in this conversation chain. Alternatives languages exist, both academically and practically; they don’t come alone. Its not a competition in a void, the ecosystem has to move together. This occurs in every aspect of tech (did you ever wonder if there could be an OS other than unix and window styles?).

Asking why SQL was never beaten out in popularity is a hugely different question from whether there can be a better language than SQL, with largely unrelated amswers(Oracle/IBM were hardly fair players in anything they did)

> There are other models as well eg datalog, but popularity is a factor of many things

The lack of popularity is also a reflection of how the string of next best things have failed to actually deliver on their promise, accompanied by the lack of a rational argument to adopt them instead of using a time-tested technology.

What time-tested technology are you referring to? I'm only talking about the SQL language -- not the relational model, not the RDBMS engine, not the drivers.

The SQL language is just an API to the total engine; the majority of its value derives from the relational model; the value of the relational algebra is not being questioned. Only the particular interface to describe the relational algebra.

The power of the JVM is not the power of the Java language. You can have other languages make use of the same power Java has access to by targetting the JVM, and as a programming community we accept that just fine (and we also accept that despite Java having many known flaws as a language, its status as a first-party Oracle interface to the JVM, and its position within the status quo, makes it extremely difficult to upend; but that hardly implies Java is some perfect language, as most HN users will trivially acknowledge).

In the same fashion, the SQL language is (ideally) decoupled from the RDBMS engine; it can be replaced. But in practice, they're not so decoupled, and SQL has been consistently the only first-class interface to the engine, so like Java, it (can) enjoys far greater ubiquity and stability from the quality of the underlying tech, than the language itself may deserve.

So once again; popularity/stability of the SQL language is not (necessarily) exemplary of the quality of the SQL language. It's much more likely that it represents the value of the RDBMS, and the relational model (which I don't think anyone is arguing against), and the SQL language enjoys a free ride by being the only interface thats even offered.

I mean hell, just read the article. It has trivially observable flaws in its design and semantics (like stuffing a 3VL logic into a 2VL language). EdgeQL may or may not be the optimal solution, and I'm not arguing (or interested in arguing) one way or the other on that, but I don't see how anyone can reasonably argue that SQL's ubiquity shows its perfection, when it's so clearly imperfect (because those flaws are being very directly pointed out).

You still haven't explained why SQL in particular has ruled over its problem domain for over 40 years. For example, in systems programming over that same timespan we've had assembly, c, c++ and rust.

Why is SQL the exception to the rule? You talk about popularity, but its really a question about stability. Remember, SQL is not that popular, as the NOSQL movement and comments like yours prove.

> For example, in systems programming over that same timespan we've had assembly, c, c++ and rust.

I don't think you've picked a very good example here of a field where the lingua franca has progressed over time. C still dominates nearly all systems programming, against all odds. It dominates operating system development, driver development, embedded systems development, pretty much anything that demands an extremely high degree of efficiency.

I agree with your overall point regarding SQL and its superior stability, but C is an example of where popularity won out against many better options over time. It's not like Rust was the first mainstream attempt to create safer languages for systems development either. There's been many over the years designed to fill the same niche, with more modern features, tools and goals. For better or worse, C still dominates the landscape for reasons entirely independent of whether or not it was truly the best tool for the every task.

Also, I'd contend that your claim regarding the popularity of NoSQL is incorrect. If you only talk to web developers writing Javascript, you'd get the impression that NoSQL is taking over the world. But the reality is that amongst pretty much every other demographic, SQL is still highly regarded as the ideal technology.

>You still haven't explained why SQL in particular has ruled over its problem domain for over 40 years

Well yes, because I argued that it was irrelevant to the original question: can SQL be improved?

Additionally I actually did address the question of SQL’s stability, at least partially: it’s not SQL thats so stable, but the relational model. SQL just happened to be IBM’s, and IBM was highly successful pushing its DB around, and Oracle (kind-of) cloned it to push their DB around with less contention, and so it went on. But its the RDBMS engine that primarily pushes a DB’s value; The SQL language is a ride-along.

And once more to be clear: its longevity is not a result of SQL’s quality, but the quality of the relational model. Thus its popularity, and stability (its also not that stable, in that its heavily extended by everyone in arbitrary fashion), is irrelevant to the original question.

>SQL is not that popular, as the NOSQL movement

If people are trying to make use of NoSQL because they want to avoid the SQL language (not the relational model), they’ve made a grave mistake in understanding their technologies; I don’t think such a naive opinion should be considered relevant to the equation.

If they’ve chosen NoSQL to avoid the relational model, then their choice says nothing about the SQL language.

I think the historical truth is that Oracle was first to market, and IBM just adopted SQL so it would not risk being waaaaaaaaay too late to the market "party". [Darwen, "Why are there no relational DBMS's"]

> If people are trying to make use of NoSQL because they want to avoid the SQL language (not the relational model), they’ve made a grave mistake in understanding their technologies; I don’t think such a naive opinion should be considered relevant to the equation.

Yes, they are trying to avoid the relational model by avoiding SQL because SQL has basically been the face of the relational model for 40 years. if there was something better, they'd use that. Nothing better has come up, and i don't know why either.

Better things have come up. See the projects list at http://www.thethirdmanifesto.com/ .

but no-one is using them, so are they actually better? (philosphical question, also pertains to a ton of better options... I mourn BeOS)

The particular meaning of "better" here was "better at representing the relational model".

If you want to believe that popularity is a measure or reliable indicator of quality, I can't be bothered with you.

PS I have a worked-out example on my site of what it takes to enforce a business rule "no one has a salary higher than his manager's" in SQL (> 100 LOC) and in SIRA_PRISE (one single relatively simple formula of the RA to declare). You decide which is "better".

Much of the comments (and the article) criticize SQL as being non-standard and difficult to learn. These critiques have been around as long as SQL has.

However, there is a more insidious problem with SQL: it's all too easy to write SQL statements that have O(N^2) complexity. A simple JOIN can easily result in O(N^2) complexity, yet there aren't easy tools to identify these performance issues. As a result, as a database grows, things that once were executed quickly take forever.

I'd like to see the end of joins, replaced with something that is more explicit about what is happening under the hood.

Your concerns are warranted.

However. You are wrong tying the problem to joins. I remember an analyst who launched a SELECT COUNT because he was just curious about the number of rows in the table. No joins involved but users did suffer. Elsewhere in this thread I've seen the problem be tied to table scans, and that's also wrong. A table scan isn't a problem if it's a 5-page table. As Darwen often argued : why are people always only lamenting about those couple of tables with millions of rows ? Why should we deprive users of the power of relational algebra if their database simply aren't that big ?

It's a matter of determining the cost of the data access strategy (regardless of JOIN/EXCEPT/what_have_you) and (implementing a protocol for) capping it at runtime (or earlier if possible). No need for language changes here.

> Your concerns are warranted. However. You are wrong tying the problem to joins. ... It's a matter of determining the cost of the data access strategy

I was using joins as the common example. Sure, there are many other ways of using SQL to shoot yourself in the foot, but most of the issues I've run into seem to come from joins.

There are real problems with COBOL and, yet, it's used in the most critical parts of our societal infrastructure. People just learned to live with the problems and are by now completely oblivious to them.

SQL is too broken to even try fixing.

there's a reason why not everything in SQL is a set and it's that you can do a lot of mathematical work from inside the engines. this was largely overlooked in the article and undefined what operations between set of multiple cardinality would require and if this would result back to what they call unwieldy runtime errors when doing math over set of mismatched cardinality.

>there's a reason why not everything in SQL is a set and it's that you can do a lot of mathematical work from inside the engines

I don’t see the relationship between those two things; or rather, what the latter even means. Can you expand?

Also, apparently the “sets” described in the article are actually multisets aka bags [0] — so the same semantics as any other RDBMS/SQL. No idea why they’d confound the two, especially when set vs bag semantics is a well-discussed topic in the literature..

[0] https://edgedb.com/docs/edgeql/overview/#everything-is-a-set

something like this

SELECT StudentID, Name, (SELECT COUNT(x) FROM StudentExam WHERE StudentExam.StudentID = Student.StudentID) / (SELECT COUNT(x) FROM StudentEnrollmetnPeriods WHERE StudentEnrollmetnPeriods.StudentID = Student.StudentID) AS ExamsTakenPerYear FROM Student ORDER BY ExamsTaken DESC;

works because the result from aggregate are scalar and thus math operations always meaningful, albeit this in particular might not be the most brilliant example, there's definitely a case for running such operations on the server, because you might filter on the results, say, like

"select all student with less than 2 exam per year average"

at which point either you have the distinction between scalar and set in the language itself and you can filter invalid queries at the parsing level or you have to do the checks at runtime when two set are in an operation with mixed cardinality, halt the query and throw an error.

edit: count(x) because I don't know how to escape asteriks

Set and scalar should be regarded as data types in a more traditional programming language, and treating a 1-element set as a scalar is a type coercion that works as much as other type coercions: only if the data matches expectations.

you're missing the point: it's not about coercion or data types per se, it's whether the error happens at parsing or runtime; of course the software has ways to figure out intention but is it something we actually want?

it's not coincidence that the article complains about the same thing but in reverse:

> This is legal, but only if the subquery returns not more than one row. Otherwise, an error would be raised at run time.

except the 'fix' reintroduce it in a way that's subtler and way hard to detect because now everything is a set even when the intent is to have a scalar.

Almost anything written by Date and/or Darwen during the last 30 years is stock full of arguments and proofs that SQL is a horrendously poor language. pls don't come complaining that all of those arguments are not replicated here.

I'll give you just one : in SQL, you can write "WHERE 4 > (SELECT COUNT() FROM ... WHERE ... )" but you cannot write "WHERE (SELECT COUNT() FROM ... WHERE ... ) < 4" [Darwen, "The askew Wall"].

Or iow, for certain specific kinds of 'a' and 'b', you can write "a < b" but not "b > a". Do you actually know ANY language that exposes that kind of thing ??? REALLY ???

Hive queries are written in HiveQL, not SQL. I used to write a lot of Hive and Impala queries, and going back to plain SQL is disappointing.

While based on SQL, HiveQL does not strictly follow the full SQL-92 standard, just like all the other SQL dialects out there. hiveQL is SQL.

That's a fair argument, but from personal experience HiveQL has some pretty major features added on that make it "have such power and support so many use cases" in a way that other common variants like the one used by MySQL doesn't.

A lot of HiveQL's power comes from extending the language, not from the use of a small set of timeless features.

What is your favourite HiveQl extension?

This shows that humans can learn languages and get motivated by mastering them. This does not tell anything about the consistency, composability or orthogonality if SQL. Those qualities affecting the newcomers effort to learn it.

Having used splunk's query language... I'd hope that in general vendors start with an assumption of SQL support and maybe try tweaking it at the edges rather than burning the house down - SQL has survived this long because it's extremely expressive and any replacement for it is going to need to match that expressiveness.

All that said I think it was originally structured to partially be a human readable language and it fails pretty hard at that - that's a facet I'm sure smart people could revise to make more natural.

To be fair, Splunk excels at dealing with unstructured logs, which is in many ways a harder problem. Obviously in a perfect world, all our data would be structured, which would make querying much simpler.

Typically the earlier part of your pipeline turns the logs into data records via regex captures. So you could have had a pipeline of what are essentially shell commands that produce a table that SQL operates on.

I don't buy that, splunk has a clear concept of columns and rows and could have chosen to expose support for a SQL like grammar for assembling them - they instead chose a less structured format that makes data assembly quite difficult.

More legitimately splunk may simply be unable to deliver performant data expression if a user is typing a query complex enough to justify SQL.

I used to work for Splunk. Querying Splunk with SQL is completely plausible, and something that Splunk has made a number of attempts at over the years.

The problem isn't SQL. It's that Splunk's query engine is tied up internally with a "grammar" that is a direct port of a shell pipeline into C++ with no intermediate representation or anything a compiler guy would recognize as a grammar. There was no design, no mathematical underpinning to it.

Splunk's unstructured log capabilities are really domain knowledge about making them semistructured as fast as possible: token indexing, a lot of effort on recognizing character encodings and timestamps intelligently, looking for key=value pairs, and letting people write regexes to extract fields themselves. The query language isn't somehow designed for a different data model.

In EWD1123, Dijkstra showed that the relational calculus and the regularity calculus (which governs regexes) are basically the same thing. My takeaway from that is that the relational model can be reinterpreted as a model over anything you want to match and manipulate with regexes by just changing the field selectors.

> I use Postgres […] SQL is pretty darn effective.

FWIW postgres used to have its own query language derived from QUEL[0] rather than SQL.

And findings that SQL is kinda shit are not exactly recent, e.g. C.J. Date's "A Critique of the SQL Language" (1983) lists the following sections

* lack of orthogonality: expressions

* lack of orthogonality: builtin functions

* lack of orthogonality: miscellaneous items formal definition

* mismatch with host languages

* missing function

* mistakes

* aspects of the relational model not supported

The conclusion was, obviously, prescient:

> if SQL is adopted on a wide scale in its present fortm~ then we will to some degree have missed the relational boat~ or at least failed to capitalize to the fullest possible extent on the potential of the relational model. That would be a pity, because we had an opportunity to do it right, and with a little effort we could have done so. The question is whether it is now too late. I sincerely hope not.

SQL succeeded not because it's "pretty darn effective" but because IBM decided on it (at a time where it drove technology) and Oracle are great at sales and marketing (whereas Ingres definitely wasn't).

[0] https://en.wikipedia.org/wiki/QUEL_query_languages

> I use Postgres in the first place because there are so many improvements available on top of SQL.

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.

Doesn't every major RDBMS support JSON out of the box these days?

(Last I checked atleast) JSON isn't as well supported on MySQl or MSSQL compared to PG.

In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.

MSSQL indexes and provides dot notation/object query from 2016 forward, schemas are supported as well.

SQL:2016 includes support for JSON: https://en.wikipedia.org/wiki/SQL:2016

Interesting, I wonder which databases actually implement SQL:2016 (and JSON as standardized). JSON support in Postgres predates SQL:2016 in any case.

I think Oracle does. My impression is that PostgreSQL sort-of "invented" JSON support in the db, then Oracle (and probably others) added JSON support with a different syntax, and then Oracle got their version defined into the SQL standard. I'm half-guessing here, but it's based on what I've gleaned from Markus Winand's [0] excellent compatibility tables in his slides [1].

[0] https://modern-sql.com/

[1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example

(Edited for formatting.)

Postgres 12, whose development has reached feature freeze and is in a stability period, has added some support for JSON path as of SQL:2016: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

The release is planned for roughly next September/October.

Wait, what major relational databases don't support JSON? Oracle and Sql Server definitely do. Does MySQL not support JSON?

SQLite (the most popular RDBMS today) doesn't, unless you count extensions. And of course, none of them use the same syntax or functions or data types.

While it is technically an optional extension in sqlite, it is part of the main source distribution, and very easy to enable.

And why wouldn't you count SQLite extensions? Its JSON1 extension is practically ubiquitous.

If I recall correctly, Oracle's support for JSON is not at the same level as PostgreSQL. By that I mean seamlessly store JSON as a data type like any other. In Oracle JSON is stored as VARCHAR/CLOB and then you implement tests to validate whereas in PostgreSQL and MSSQL is its own data type.

Similar story with SQL Server. I believe JSON support is a bit better in SQL Server 2019, but still far lacking behind that of Postgres.

Hi, MySQL supports JSON (it has a native JSON datatype) but it also support NoSQL (CRUD operations). For more details, check https://www.slideshare.net/lefred.descamps/pyconx-python-mys... or https://www.slideshare.net/lefred.descamps/oracle-code-roma-...

The various extensions / plugins that allow for custom data types, indexes, use of multiple programming languages to write functions, ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function, varieties of powerful search capabilities, etc

It's got a lot of stuff going on in there.

> ability to use a foreign data wrapper to connect to Redis and build a VIEW out of the result or push data out to Redis/Memcached with a database function,

This sounds amazing. Does anyone have a link to some docs for this?

That "most" is a pretty loaded word! Last I checked, even CREATE INDEX is not part of any ANSI or ISO SQL specification. (That's why every RDBMS has such different features and syntax for this.) Good luck building a system with just "standard SQL".

Why should it be? Indexing is an implementation detail for each RDBMS, so it doesn’t make sense to add it to the language spec.

It does make sense when the goal of the language spec is interoperability, and it's something that everyone has to do, in practice, no matter the platform.

Call it a "hint", if you will, the meaning of which is implementation defined. But standardize the syntax for pete's sake.

Interoperability is achieved if the same query with the same inputs yields the same result. "In the same amount of time" (which is the part that indexes are aimed at) is manifestly not part of that picture, and that's by design.

And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?

That sounds backwards to me. It's only lack of standardization that forces it to be an "implementation detail". To any application that needs to be able to query data in reasonable time, it's fundamental and necessary functionality.

> even CREATE INDEX is not part of any ANSI or ISO SQL specification

That's correct, and that's why PostgreSQL has ADD CONSTRAINT.

The constraints describe the actual schema of the data; INDEX is an DBMS-specific implementation* for improving performance (including index types, etc.).

* Those since the standard does not yet cover some thing partial unique constraints, these have to be done as INDEX in PostgreSQL.

SQL has nothing to say about the layer of physical implementation, and that's where INDEX belongs (as does STOGROUP [DB2] and what have you).

That's not a bug, that's a feature. Deriving from the deliberate intent to make physical independence (which was simply nonexistent at the time the model was conceived) a reality.

I feel like Postgres is so powerful I could damn near build an entire web app backend with JUST Postgres (i'm only sorta kidding here). If that's standard SQL, well then I really like standard SQL :-)

You think you're kidding.

https://github.com/PostgREST/postgrest

https://wiki.postgresql.org/wiki/Python

https://github.com/plv8/plv8

https://www.graphile.org/

Graphile looks quite intriguing! any experience with it? Recommended?

I'm using Graphile in deployment at the moment, and it works quite nicely. Granted, we have a limited amount of users thus far, so I don't really know how well it handles big loads, but it allowed us to get off the ground with a PoC _really_ quickly.

I would also have a look at Hasura GraphQLEngine. Have used it now for various projects and is extremely nice, especially with the superb subscriptions support and eventing for the odd requirement that cant be handled within Postgres

I'm keeping an eye on https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"

I've done pretty much this a few times, and it's amazing if you're working in the context of enterprise data systems that need to provide extensive capabilities to a "small" userbase (i.e. concurrent in the thousands). You just need a small shim layer for security, to transform results sets, and handle browser -> database connectivity.

Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.

Thank you for posting this. I have been thinking about using something along these lines for awhile now. Next project that fits the bill I will see about building a proof of concept implementation and go from there. Have you had any exposure to any of the other projects similar to PostgREST that you have any thoughts about?

I've played with Graphile, it's not a bad choice if you are already invested in GraphQL. I'm not a GraphQL fan for a number of reasons, but it's a definitely a solid project.

> you just need to parse incoming sql to sanitize it and make sure there is no role escalation

"Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql".

Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.

You don't have to do full statement parsing, you basically just have to do a limited parse that looks for the various ways that someone could execute a set role statement. As long as you don't let a user execute set role, you use db roles for user accounts, you have a reasonable statement timeout in place to prevent DOS, and your postgres security model is tight (the big gotcha is not to allow access to untrusted code), this approach works fine.

A good tool for this purpose is https://github.com/JavaScriptor/js-sql-parser as it will fail to parse complex statements that are likely to include an attack vector.

In terms of authorization, you can either create per user connection pools if using web sockets and log the user in directly that way (which makes things easy) or if you must use rest, use a single connection pool with a master user then use some form of token to tell the shim who to set role to before executing the query.

Same. As a data scientist, I have seen multiple 'analytics workbench' solutions come, go and occasionally stay - Clementine (what is now IBM SPSS), Stata, SAS and its many variants, Statistica and in the recent years, tools built off and designed to make working with data using Python/R.

But, the common workhorse tool that has stayed strong through all these has been the common SQL. Elegant, simple, powerful and thoroughly reliable, it is my primary go-to tool. In an otherwise changing ecosystem, its simplicity and reliability is a boon. Yes, it is primarily because of the nostalgic familiarity but I also believe it continues to be extremely powerful, one that will serve you very well.

I came here to write something similar, but you said it better than I could. Sure, SQL has the problems that the article mentions and their solution looks nice on the surface, but SQL has worked much better for me than any alternatives, especially if it’s slightly extended SQL like in Postgres, as you mention, and any attempts to improve it will always be an uphill battle as SQL is pervasive and well supported. That doesn't mean people shouldn't try, but it does mean that their likelihood of success depends on factors other than whether the solution is an improvement over SQL or not.

Reply on news.ycombinator.com