Hacker News Reader

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.

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-wit...

It is greybeard or graybeard?

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).

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?

>I didn't mean the graybeard in a bad way at all.

Oh, no offense taken. What you read in my comment was genuine recognition on my part that I've likely crossed over into the graybeard camp--both in experience and disposition.

>I feel a bit 50-50 on some of the reactions to JSON as a new thing at the time.

Yeah, there have certainly been real improvements in tech that are perfectly reasonable. And, actually, I think it's the incremental improvements that add up over time which actually comprise the bulk of our real progress.

I think where things get off track is when we make sudden pronouncements that we must now cease doing everything we've done heretofore with tech Y in favor of new and shiny tech X. Every use case. Period. Whether it's killing XML because, JSON or killing SQL because, NoSQL or adopting J2EE for every "enterprise" app, or never writing Vanilla JS again because, Framework Fill-in-The-Blank; the list goes on.

There's just a tendency in the tech community to lurch in different directions with this extreme vigor. But, when you really think about it, once the foundations are laid there just aren't many quantum leaps that truly merit such strong reactions.

And, what's really strange is that we somehow manage to overlook glaring weaknesses in the new tech, and likewise disregard (or even impugn) the strengths of the tech we're displacing in ways that are obviously unfavorable. For instance, suddenly deciding we don't need ACID transactions. What?

You see that stuff and it's an automatic, "yeah, this will all blow over soon". We then generally a.) forget it ever happened or b.) settle into assigning the proper use-cases, adopting/integrating the best properties of the tech, and moving on. Incremental.

My beard does in fact have some gray ;) , and I've never stopped beating the SQL drum. One other thing you learn is that data always outlive whatever app is using it today. I want my data to be stored in a safe way, and self contained apart from whatever flavor of the month app is generating/using it today. RDBMSs are perfect for this.

> 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.

I like that it feels very declarative and that the imperative extensions are all kind of Ada like.

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-tr...

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.

I agree. Of course any language has its warts, but when dealing with data I don't know of a better language than SQL.

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.

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.

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.

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". :-)

The ability to partition/distribute an object is far detached from Postgres or an object-relational database. I'll be the first to admit I'm rusty on some of my college days of what a true object/relational database means, and while Postgres breeds itself as that more at its core is ACID[1] which focuses on both some of the relationship of the data and consistency between it.

As things have evolved it hasn't actually given up on ACID or the relational nature as even with JSON you can have complex constraints and guarantees about your data. The Postgres community has simply applied a lot of the underlying principles to what has been build on top.

[1] https://brandur.org/acid

> 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?

I think it's largely a leftover - the things that made it "object" back then are partially not used much, partially deprecated, partially removed, partially repurposed (inheritance as a partitioning mechanism).

You could perhaps just do a s/object/multi-modal with a focus on/. But that's a bit awkward ;). There's a bit document store (json, xml), arguably some graph (recursive queries), plenty people just use it for KV, ...

Except that maybe it's precisely because it was meant as an OO database that you can now enjoy jsonb or geography datatype that are pretty complex.

Also a lot of OO mechanisms become really useful without being clearly identified. For instance every table automatically generate an object definition (aka composite type aka rowtype) that you can use anywhere to ensure that a return type conforms to this definition. You can also check and validate single row well ahead of an insert attempts filter out and store unmatching data structure in an generic human readable rowtype while accepting correctly formated entries.

PS: I don't now if my examples were good or understandable but heck I tried, feel free to correct me if needed ;)

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[].

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/

Still evolving in its old age, along with postgres:

http://www.newtdb.org/en/latest/how-it-works.html

Other than that I'm aware of gemstone/s:

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/

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.

I suppose you could say Postgres did end up as an object-relational database still, composite UDT’s, array’s and domains are all features that match the original vision.

I think that is a great way of thinking about it! Composite types and user-defined types are very useful and it makes sense to see them as inspired by object-oriented ideas.

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.

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.