Hacker News Reader

A major benefit is declarative constraints. If you declare something UNIQUE, it's going to be unique no matter what. No race conditions, no "whoops, I guess there was a bug in version 4.7.3 of the application that didn't properly check for an existing entry, and it was silently fixed later in some refactoring, but we still have duplicate entries floating around".

Same for foreign keys and check constraints.

Queries are pretty intelligent, also, and mostly just work efficiently. You are right that some queries don't, and you have to know about the internals to make it do what you want. But here's what you are missing in that story: your data lives for a long time across many different applications. If you program everything yourself imperatively, you are bound to either over-engineer or under-engineer a lot of queries. The over-engineered pieces will look like premature optimization, clutter your code and tests, and be a time sink. The under-engineered pieces will come back to haunt you when your data grows and changes. Declarative relational systems actually do a pretty darn good job of handling this problem with a cost-based optimizer. Is it perfect? No, but it's an improvement in an awful lot of cases.

Also, you are ignoring places where databases do a good job with queries, like parallelization. It's easy to say that you'll just write all the queries imperatively yourself, but a lot of people don't want to spend the time to do the harder stuff even if it really matters for performance.

> What's the point of being declarative if common tasks require us to bend over backwards to design our schema/queries/indices/hints in exactly the right way,

Because 99% of the time you don't notice that it just works. 99% of the time you don't have to bend your query or schema to make it work.

99% is a strong claim. Maybe 70%? If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing.

For each query I had to optimize, I wrote dozens, if not hundreds, that worked without any intervention. 99% is just a rough guess.

> If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing.

Thats very unspecific, but getting 5000 rows from a table should take a few milliseconds on modern hardware.

This is why decoupling RDBMS from the application is rarely ever useful, and why no app really changes databases anyway unless there's a massive cost or technical problem.

As a long time PostgreSQL user, I'd say that decoupling has been very useful for most of the last two decades, but it's quickly becoming irrelevant for reasons that should be obvious here.

The app I’ve been working on for the past 24 years is currently on its third DBMS, and I would be unwilling to bet on whether it’s on its last DBMS.

There is zero SQL code in our app above a very low level. All high-level code calls through a DB API, and all SQL code is behind that abstraction layer.

That said, each one of these transitions was a “pull up stakes and move” kind of thing. We never tried to support multiple back-ends at once. So on those grounds, I think this is probably a good move for GitLab.

If you're using basic ANSI/92 SQL syntax then it'll work pretty much anywhere, especially with a decent ORM, but as soon as you start using any of the interesting features of a particular database, then you're locked in.

Supporting multiple databases at this point is usually not economically feasible.

That's the thing: it's super tempting to use nonstandard features. On our last SQL transition, we were most bitten by use of data types the new DBMS didn't support and by changes in the way "standard" text data types behaved between the two. (Just as GitLab was, on that last one.)

That's why I don't regret putting all of our SQL behind that DB layer: to port to the new DBMS, we just had to rewrite parts of that layer, rather than go on a hunt through the whole application for one-off SQL calls, as would probably occur if we didn't enforce this discipline.

But why prioritize portability over better functionality at all? Why did you move databases so often?

Not OP, but over 24 years some major db upgrades may as well be an entirely new db. In addition database vendors go out of business and when their assets are bought, the buyer is often buying customer base and not so much the obligation to keep developing a separate product forever. E.g. IBM and Oracle have each bought lots of their prior competition, and sometimes they only offer customers an upgrade path to a different product. Each time something like that happens you are going to re write your db support anyway.

> But why prioritize portability over better functionality at all?

It's not just about portability, or even primarily. It's simply good application layering to put the DB code behind a layer. Intermingling SQL code in your application logic is just as bad as intermingling GUI code in your core app logic.

But even that aside, we haven't felt the lack from putting such code a single function call layer away. I suppose it may give us a slight amount of pause when we consider whether it's worth creating another function in that helper library, but that's probably a good thing. If we decide we can get our work done by calling one of the existing functions, then why write yet another DB access function?

Example: if a table is known to have at most 5 rows, and the query that would allow the DB to return just the one we actually want is complicated, and it's almost never called, why not call the existing "SELECT all" one and iterate over the rows? The performance hit of being inefficient will be immeasurable.

> Why did you move databases so often?

That's once every 8 years on average. A lot changes in the computing world over that span.

The first change did coincidentally happen about 8 years after the company was founded, and it was because much better DBMS tech became available to us, within the constraints we had on choosing.

That DBMS lasted us for the next 14 years. By that time, enough had changed in the computing world that it was worth swapping again. The new one runs about twice as fast, takes far less code space, and is simpler to use all around than the prior one.

We'd have to switch again this year in order to maintain the mean of once every 8 years. My sense is that this is more likely a nonlinearly increasing growth curve, so that the next switch will be closer to 20 years than 14, if it happens at all.

We might be talking about different things. Of course using an ORMs and Query constructors to programmatically generate SQL is the best option instead of handwritten code everywhere, but that's a different issue.

What we're discussing isn't the SQL code, it's SQL syntax and features of a particular database over another. JSONB, partial indexing, text search, extensions, etc. in Postgres are not available in MySQL, and not using those features just to have the ability to switch or run on multiple databases is almost never worth the effort.

Simple CRUD apps with an ORM can probably do so, but otherwise most business apps shouldn't avoid using functionality for some "what-if" scenario. Having to switch databases after a decade is just a normal development item if it needs to happen, and usually coincides with heavy rewrites anyway, so I don't see much value in pre-planning for that at the architectural level.

I have migrated apps from one database to another several times. The decoupling was very helpful.

I have also seen instances where the decoupling helped us abstract out differences between different versions of the same database product during upgrades.

I've more often seen the app change from on top of the DB, than the DB change from under the app. Actually I'm not sure I've ever seen the latter happen IRL. App rewrite/replacement but DB remaining? Several times. Network message format/tech change? Several times. DB even going from one SQL server or one NoSQL server to another of the same general sort? Never. I know it does happen but I've stopped bothering to give it so much as a thought when designing a system. DB techs are more likely to be added on and maybe, maybe some earlier one(s) replaced over time alongside rewrites, than to be swapped out from under an app directly.

Yeah, SQL in applications that need to be performant and responsive is a huge pain and I have found it to not be worth the effort.

If you don't need performance or responsiveness though you can save a lot of effort except in very simple cases. I've seen it shine in quickly built proof of concept applications and in ad-hoc analytics queries.

I tend to prefer to treat SQL databases as dumb and generic as possible... you can go a LONG way supporting multiple databases until you need something more complex. For most organizations, supporting more than a single SQL variant is just painful. The only time I ever saw, for example, Microsoft Data Application Blocks in a useful scenario was an enterprise app that had to support MS-SQL and Oracle. Most of the variance was custom stored procedures for each named the same with the same signatures... It actually really sucked.

I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do.

I'd rather use MS-SQL or PostgreSQL than mySQL.

> I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default.

Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc).

There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.

While I am glad to hear it... I've been very happy with mssql and postgres, with little chance of giving mysql/mariadb much in the way of future opportunity. I've just been burned too many times with other open and commercial options that give me what I expect.

It can also be made to work for highly-responsive but relatively small data sets: At Justin.tv, the scaling issues we hit with the first-generation video system were net splits and an increasing percentage of processor time being devoted to synchronization. The replacement system used a single PostgreSQL server (with a hot backup) to hold the real-time status of the entire system: Where every channel was being put into the system, the entire replication graph for every channel, active BGP routes, and the current load of every server and network link.

Whenever a viewer showed up to the website, we’d do a relatively complicated query against this database to figure out which server to direct them to for the channel they wanted to watch. The net result was a system that showed video sooner, with less latency, and more cheaply than any of our competitors were able to achieve. I don’t know what their setup looks like these days, but this basic setup took them from before the Twitch.tv rebrand all the way to their Amazon acquisition.

There was a lot of tuning that we had to do to make this work (like running off of a ramdisk, and having a pool of read-only clones), and it certainly had its hair-tearing-out moments, but in retrospect it was absolutely the right choice. We kept expecting it to fall over, but the plan to shard the database onto multiple servers collected dust for years because there was always a way to get the performance gains we needed through basic tuning.

> vaguely-English-like syntax that's completely unlike any other computer language

COBOL is also English like.

I'm not negative about SQL. I'd rather write queries in SQL than having to learn for the nth time how to write SQL in language X with ORM/module Y. I'm doing it with Django, Rails and Phoenix in the same week since a long time now and it feels a bit pointless. But then there is the deserialization in the data structures of the language. I just wish those libraries would be smarter and know how to handle some hypothetical code like

    sql("select * from users").each |user| do
      something(user)
    end

And be able to handle joins.

The best experience I know of in this regard is from some packages in ML languages, where the string literal query you write gets sent _at compile time_ to a database engine for parsing, using the database's API to determine the query's expected return type (column names and types).

They're most used in F#, which calls them type providers:

https://github.com/fsprojects/FSharp.Data.SqlClient

https://hackage.haskell.org/package/postgresql-typed

You're conflating the nitty gritty of schema design (which I agree with you is not that declarative) with the usual case of "I know what I mean why doesn't the machine know what I mean" which is different.

You're still expected ask for what you want specifically.

> It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries.

"I know how to express what I mean in such a way that I can target two very different platforms like JS and the JVM, why isn't there something as powerful for SQL?"

"One does not simply use an ORM against multiple database types."