5 ms·

I don't think the article could have said it much better. SQL is super powerful and makes much sense in so many ways. Nearly all apps have a relational structu

I don't think the article could have said it much better. SQL is super powerful and makes much sense in so many ways. Nearly all apps have a relational structure to them and SQL is a reasonable way to interact with them. Some of my favorite conversations from the Postgres community 5-6 years back were when they were talking about a time when Postgres was being disrupted. The gray bearded DBAs (Hi Berkus and others) were talking about JSON like it's fad and how it's going to come to pass. They were saying so because they'd heard about this disruption before... there was this new thing XML and these document databases were so much more powerful. They waited a few years and added an XML datatype [1]... And then XML databases came and passed. At first they scoffed a bit on JSON as the new hip thing, but came around a little in Postgres 9.2, and then in 9.4 [2] we all really got what we wanted. SQL has never been a particularly elegant language, but it's always been a powerful one. The lone knock against has either been usability or scalability [3]. On the usability side that's a bit of a hard one as well it's not super pretty. But once you understand it's foundations around relational algebra or relational calculus you're so far ahead. On the scalability side there are definitely some options such as Citus, but also core Postgres is massively improving here. [1] https://www.postgresql.org/docs/9.3/static/datatype-xml.html https://www.postgresql.org/docs/9.3/static/datatype-xml.html [2] https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/ https://blog.codeship.com/unleash-the-power-of-storing-json-... [3] https://www.citusdata.com/blog/2017/02/16/citus61-released/ https://www.citusdata.com/blog/2017/02/16/citus61-released/

unclebucknasty 9y ago
I think I'm one of those graybeards. I see it in so many things tech. It's a pattern, and once you've seen it repeat a half-dozen times and also gain a depth of experience over that time, you can actually recognize when something represents genuine progress vs yet another passing fad. Spoiler alert: those that are most rabidly promoted are often the latter. But, if you try to raise the point in the midst of the latest fad, you generally get shouted down. So, you wait until the less-jaded figure it out...again. It was plainly obvious for NoSQl, just as it now is for SPAs (or at least our current approach). Don't believe me? Wait 5 years. And, the thing with SQL that's so much like other tech is that we largely figured it out years ago. Improvements have been incremental at best and, frequently, not improvements at all.
- craigkerstiens 9y ago
  Oh, I generally agree. I didn't mean the graybeard in a bad way at all. I feel a bit 50-50 on some of the reactions to JSON as a new thing at the time. But, also the practicality of how JSONB finally came to exist, the foundation it was built on and the new index types[1] in Postgres to leverage it felt like it came in the right time and in the right way. [1] http://www.craigkerstiens.com/2017/03/12/getting-started-with-jsonb-in-postgres/ http://www.craigkerstiens.com/2017/03/12/getting-started-wit...
  - wolco 9y ago
    It is greybeard or graybeard?
    - Wildgoose 9y ago
      American spelling is gray, English/UK spelling is grey. Easy way to remember is use "a" for "American" in the American spelling, and "e" (for English) in UK (and presumably the rest of the Commonwealth).
      - dwringer 9y ago
        In American English, both "grey" and "gray" are considered acceptable and it's not uncommon to see both used here in my part of the US. Personally I usually write "grey" but if it's a particularly dark shade I might say "gray" - but that's personal preference rather than any convention I've seen elsewhere. EDIT: There do seem to be a couple of blogs that come up in Google pushing the idea of a difference between US/UK spellings, but that's definitely not something I or most people I know grew up with. Other search results indicate that there is only a modest correlation with location. Perhaps it goes more one-way, with both being in common use in the US but "grey" exclusively preferred in the UK?
      - deleted 9y ago
        [deleted]
  - unclebucknasty 9y ago
pjungwir 9y ago
Hi Craig! Postgres refers to itself as an "object-relational database", but do you think the "object" part is meaningful today, or just a label left over from the 90s? As best as I can tell, it just means that Postgres has table inheritance, which is great for partitioning but has never been all that feature-ful. (I guess it is improving a lot with pg 10 though!) Is there any theory or academic research on what an "object-relational database" might mean? Or just a buzzword (like Snodgrass titling his book "Time-Oriented Database Applications", a la object-oriented)? (Edit to add: In other words, is "object-relational" something that still gives some kind of principled direction to the project, something that informs decisions made by the core contributors?) I sort of wonder if nowadays with JSON Postgres has become a "document-relational database". :-)
- ww520 9y ago
  Mike Stonebraker started Ingres to build a relational database. After Ingres, he and Larry Rowe wanted to see what could be done to add the OO aspect and other complex data types in addition to the relational model; OO was starting to take off back then. They started the Postgres project (Post-Ingres) in UCB to research on object-relational database. It turned out the object-relational part was better done on the client side, evidenced with the legions of client side object-relational libraries. The relational part sticks around and becomes the better part of the PostgreSQL database. Edit: the object part was to build complex data types from simple type in the relational model, and had the database to manage the storage and querying of the complex object. E.g. build Location object from (x: int, y: int), and array of Location[].
  - e12e 9y ago
    I'll note that postgres is (sort-of) a storage option for one of the (rather) early object databases, the much maligned, yet perhaps under-used zodb: http://www.zodb.org/ http://www.zodb.org/ Still evolving in its old age, along with postgres: http://www.newtdb.org/en/latest/how-it-works.html http://www.newtdb.org/en/latest/how-it-works.html Other than that I'm aware of gemstone/s: https://gemtalksystems.com/products/gs64/ https://gemtalksystems.com/products/gs64/ Which can be paired with the maglev ruby implementation for similar (but presumably higher performing, more featurefull) experience with distributed objects in ruby: http://maglev.github.io/ http://maglev.github.io/ I suppose that with jsonb, and a node/js server, one could indeed argue that postgres is (again) an object oriented db - but I don't think anyone would push that argument too strongly.
misterbowfinger 9y ago
SQL is easily the best option at the moment, but unlike the experimentation in programming languages that we've seen in the last 10 years or so, we've yet to see much experimentation in query languages (as far as I know!) ReQL, what RethinkDB uses, was an interesting direction because it allows people to start reasoning about a particular query. This, IMO, is the biggest thing that SQL lacks - the ability to reason about a query. I'd love to see a way to extend SQL that solves this. The other thing that really, really sucks is that SQL has some terrible documentation and tutorials. Even for PG, for example, it just mentally takes a long time to parse through the documentation to understand how to use a function that solves your use case. This might just be a lack of Stack Overflow posts out there, but it's definitely not as friendly as, say, finding answers for Ruby code.
x0x0 9y ago
Some of the resurgence of sql has to be the size of data you could fit on a single machine / fake with a distributed system increased. But postgres I think continues to be at the forefront: first, the article misstated slightly; jsonb gives you not only full text search (who needs that?) but indexing all the way down. So you can eg say I want records where "field1 -> field2 has an element named md5 with a given value...". It turns out that a lot of plain normalized tabular data plus a medium about of schema-free jsonb really is the perfect combination for most purposes.
- danudey 9y ago
  The biggest change for us was SSDs coming down in price. Whereas before I might need four read slaves to ensure that at peak load I'm handling all my transactions within X ms, now I can guarantee it on one server. More importantly, in our industry where we're vastly more write-constrained than read-constrained and we're faced with e.g. MySQL not being able to easily spread writes over multiple servers simply, the appeal of something like MongoDB or Cassandra with built-in sharding and rebalancing to spread out both reads and writes sounds very appealing. And again, I can move from a giant complicated, expensive, heat-producing multi-disk raid10 to a pair of SSDs in RAID1 (or better) and easily meet my iops requirements. Without being able to upgrade to SSDs I think we would have been looking into other systems like Cassandra a lot sooner, but right now we can pretty easily throw some money at the problem and it goes away.
hvidgaard 9y ago
> SQL has never been a particularly elegant language I hear that often. But SQL is close to a 1-to-1 mapping with sets of data. It's refreshing to use something as math like when programming. I find that elegant.
- pjmlp 9y ago
  I like that it feels very declarative and that the imperative extensions are all kind of Ada like.
- kazagistar 9y ago
  You might find the criticisms in this article interesting (on the jooq blog, a java query builder library). https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
  - hvidgaard 9y ago
    Indeed that is valid criticism. I suppose after using SQL for a number of years, the logical order is just ingrained. I would be nice for it to be required to be written in the logical order though.
- matwood 9y ago
  I agree. Of course any language has its warts, but when dealing with data I don't know of a better language than SQL.
- xeonoex 9y ago
  There's a lot of simple improvements that could be made to the syntax. Some DBMSs (mostly Postgres) do make syntax improvements, but I'll never understand why Oracle still doesn't support "is <not> distinct from". If I want to do a join where null == null is true, I have to write (NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL)) instead of "a is not distinct from b". The query structure is odd as well. I think it should more like from <tables> [declare] <calculations, scalar variables> [group by] [where] select Where declare is a section to declare variables (or virtual variables), so that you do not have to rewrite a calculation every time it is used. You could also declare scalar variables there. There's also no reason to have both a where and having clause.
primeblue 9y ago
XML databases lol
- sctb 9y ago
  We've already asked you to please post substantively, so we've banned the account. We're happy to unban accounts if you email us at hn@ycombinator.com and we believe you'll post within the guidelines in the future. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
jbergens 9y ago
You do have the impedance mismatch to OO languages, which are probably used in 90% of all applications. Farter more, I don't really think that all systems have relational data, it's just possible to store it in that format. Sometimes 3 document types are easier to handle from code than 30-60 tables.

unclebucknasty 9y ago

I think I'm one of those graybeards. I see it in so many things tech. It's a pattern, and once you've seen it repeat a half-dozen times and also gain a depth of experience over that time, you can actually recognize when something represents genuine progress vs yet another passing fad. Spoiler alert: those that are most rabidly promoted are often the latter. But, if you try to raise the point in the midst of the latest fad, you generally get shouted down. So, you wait until the less-jaded figure it out...again. It was plainly obvious for NoSQl, just as it now is for SPAs (or at least our current approach). Don't believe me? Wait 5 years. And, the thing with SQL that's so much like other tech is that we largely figured it out years ago. Improvements have been incremental at best and, frequently, not improvements at all.

craigkerstiens 9y ago
Oh, I generally agree. I didn't mean the graybeard in a bad way at all. I feel a bit 50-50 on some of the reactions to JSON as a new thing at the time. But, also the practicality of how JSONB finally came to exist, the foundation it was built on and the new index types[1] in Postgres to leverage it felt like it came in the right time and in the right way. [1] http://www.craigkerstiens.com/2017/03/12/getting-started-with-jsonb-in-postgres/ http://www.craigkerstiens.com/2017/03/12/getting-started-wit...
- wolco 9y ago
  It is greybeard or graybeard?
  - Wildgoose 9y ago
    American spelling is gray, English/UK spelling is grey. Easy way to remember is use "a" for "American" in the American spelling, and "e" (for English) in UK (and presumably the rest of the Commonwealth).
    - dwringer 9y ago
      In American English, both "grey" and "gray" are considered acceptable and it's not uncommon to see both used here in my part of the US. Personally I usually write "grey" but if it's a particularly dark shade I might say "gray" - but that's personal preference rather than any convention I've seen elsewhere. EDIT: There do seem to be a couple of blogs that come up in Google pushing the idea of a difference between US/UK spellings, but that's definitely not something I or most people I know grew up with. Other search results indicate that there is only a modest correlation with location. Perhaps it goes more one-way, with both being in common use in the US but "grey" exclusively preferred in the UK?
    - deleted 9y ago
      [deleted]
- unclebucknasty 9y ago

pjungwir 9y ago

Hi Craig! Postgres refers to itself as an "object-relational database", but do you think the "object" part is meaningful today, or just a label left over from the 90s? As best as I can tell, it just means that Postgres has table inheritance, which is great for partitioning but has never been all that feature-ful. (I guess it is improving a lot with pg 10 though!) Is there any theory or academic research on what an "object-relational database" might mean? Or just a buzzword (like Snodgrass titling his book "Time-Oriented Database Applications", a la object-oriented)? (Edit to add: In other words, is "object-relational" something that still gives some kind of principled direction to the project, something that informs decisions made by the core contributors?) I sort of wonder if nowadays with JSON Postgres has become a "document-relational database". :-)

ww520 9y ago
Mike Stonebraker started Ingres to build a relational database. After Ingres, he and Larry Rowe wanted to see what could be done to add the OO aspect and other complex data types in addition to the relational model; OO was starting to take off back then. They started the Postgres project (Post-Ingres) in UCB to research on object-relational database. It turned out the object-relational part was better done on the client side, evidenced with the legions of client side object-relational libraries. The relational part sticks around and becomes the better part of the PostgreSQL database. Edit: the object part was to build complex data types from simple type in the relational model, and had the database to manage the storage and querying of the complex object. E.g. build Location object from (x: int, y: int), and array of Location[].
- e12e 9y ago
  I'll note that postgres is (sort-of) a storage option for one of the (rather) early object databases, the much maligned, yet perhaps under-used zodb: http://www.zodb.org/ http://www.zodb.org/ Still evolving in its old age, along with postgres: http://www.newtdb.org/en/latest/how-it-works.html http://www.newtdb.org/en/latest/how-it-works.html Other than that I'm aware of gemstone/s: https://gemtalksystems.com/products/gs64/ https://gemtalksystems.com/products/gs64/ Which can be paired with the maglev ruby implementation for similar (but presumably higher performing, more featurefull) experience with distributed objects in ruby: http://maglev.github.io/ http://maglev.github.io/ I suppose that with jsonb, and a node/js server, one could indeed argue that postgres is (again) an object oriented db - but I don't think anyone would push that argument too strongly.

misterbowfinger 9y ago

SQL is easily the best option at the moment, but unlike the experimentation in programming languages that we've seen in the last 10 years or so, we've yet to see much experimentation in query languages (as far as I know!) ReQL, what RethinkDB uses, was an interesting direction because it allows people to start reasoning about a particular query. This, IMO, is the biggest thing that SQL lacks - the ability to reason about a query. I'd love to see a way to extend SQL that solves this. The other thing that really, really sucks is that SQL has some terrible documentation and tutorials. Even for PG, for example, it just mentally takes a long time to parse through the documentation to understand how to use a function that solves your use case. This might just be a lack of Stack Overflow posts out there, but it's definitely not as friendly as, say, finding answers for Ruby code.

x0x0 9y ago

Some of the resurgence of sql has to be the size of data you could fit on a single machine / fake with a distributed system increased. But postgres I think continues to be at the forefront: first, the article misstated slightly; jsonb gives you not only full text search (who needs that?) but indexing all the way down. So you can eg say I want records where "field1 -> field2 has an element named md5 with a given value...". It turns out that a lot of plain normalized tabular data plus a medium about of schema-free jsonb really is the perfect combination for most purposes.

danudey 9y ago
The biggest change for us was SSDs coming down in price. Whereas before I might need four read slaves to ensure that at peak load I'm handling all my transactions within X ms, now I can guarantee it on one server. More importantly, in our industry where we're vastly more write-constrained than read-constrained and we're faced with e.g. MySQL not being able to easily spread writes over multiple servers simply, the appeal of something like MongoDB or Cassandra with built-in sharding and rebalancing to spread out both reads and writes sounds very appealing. And again, I can move from a giant complicated, expensive, heat-producing multi-disk raid10 to a pair of SSDs in RAID1 (or better) and easily meet my iops requirements. Without being able to upgrade to SSDs I think we would have been looking into other systems like Cassandra a lot sooner, but right now we can pretty easily throw some money at the problem and it goes away.

hvidgaard 9y ago

> SQL has never been a particularly elegant language I hear that often. But SQL is close to a 1-to-1 mapping with sets of data. It's refreshing to use something as math like when programming. I find that elegant.

pjmlp 9y ago
I like that it feels very declarative and that the imperative extensions are all kind of Ada like.
kazagistar 9y ago
You might find the criticisms in this article interesting (on the jooq blog, a java query builder library). https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
- hvidgaard 9y ago
  Indeed that is valid criticism. I suppose after using SQL for a number of years, the logical order is just ingrained. I would be nice for it to be required to be written in the logical order though.
matwood 9y ago
I agree. Of course any language has its warts, but when dealing with data I don't know of a better language than SQL.
xeonoex 9y ago
There's a lot of simple improvements that could be made to the syntax. Some DBMSs (mostly Postgres) do make syntax improvements, but I'll never understand why Oracle still doesn't support "is <not> distinct from". If I want to do a join where null == null is true, I have to write (NOT (a <> b OR a IS NULL OR b IS NULL) OR (a IS NULL AND b IS NULL)) instead of "a is not distinct from b". The query structure is odd as well. I think it should more like from <tables> [declare] <calculations, scalar variables> [group by] [where] select Where declare is a section to declare variables (or virtual variables), so that you do not have to rewrite a calculation every time it is used. You could also declare scalar variables there. There's also no reason to have both a where and having clause.

primeblue 9y ago

XML databases lol

sctb 9y ago
We've already asked you to please post substantively, so we've banned the account. We're happy to unban accounts if you email us at hn@ycombinator.com and we believe you'll post within the guidelines in the future. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

jbergens 9y ago

You do have the impedance mismatch to OO languages, which are probably used in 90% of all applications. Farter more, I don't really think that all systems have relational data, it's just possible to store it in that format. Sometimes 3 document types are easier to handle from code than 30-60 tables.