Things I wished more developers knew about databases | Hacker News Reader

Things I wished more developers knew about databases

medium.com

1,011 pointszdw445 comments

Here's a fun bug I had a few years ago -

Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we had on the project was using Go to connect to the database to write some simple reports, but each report took hours to run, and often had to sleep for 30+ minutes. So, after a while, pgbouncer would kill their connection, and their report would die. No other application did this among the many that we had connect to that DB, so it was definitely strange.

Found out pretty early on in troubleshooting it that they had no mechanism to keep the connection alive, which makes total sense for why his app died. So, they put the library standard keepalive function in a loop if the report wasn't doing anything.. but that didn't fix it.. it made no friggin' sense. After bashing my head against that for a while, I finally threw my hands up and asked if they could just run a "SELECT 1" as a keepalive instead of whatever the Go library was doing. Got a bit of pushback, but just told him to do it and walked away. That ended up fixing the problem.

Turns out the Go library was trying to be clever in its keepalives (can't remember what it was doing exactly), in that it made some silly assumptions that there was nothing in the middle managing connections.

I like to think that dev learned a lot about trust in "magical" libraries after that.

Go sql/database uses its own connection pool. But still that shouldn't create any problems. I have seen the reverse where apps that assumed temporary tables stick around from statement to statement without an explict `txn` (which regular postgres connections don't need) clearly failed. But I have not seen the issue you talk about.

My wild guess would be that the Go code never closed the result/rows which caused either a pool exhaustion / left the connections hanging and eventually got a timeout. Consider this similar to how http client's need to close the response.Body or else connections can't be reused.

At a previous employer, I was forced to use a certain 3rd party ODBC library. Under certain circumstances, it would just do `exit(1)`, with nothing even to stderr. Very frustrating and annoying to debug/fix. Had I physically known the developer responsible for that behavior, I'd probably have faced murder charges and plead temporary insanity.

"Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live.

I usually maintain my own code, so the as-if is true. "

http://wiki.c2.com/?CodeForTheMaintainer

Lol

The database/sql package isn’t very magical. The comment about magical library makes me think it was some other package.

Any sufficiently advanced technology is indistinguishable from magic. If your source of knowledge is stack overflow or a youtube video posted on linkedin you already live in a world filled with magic.

They may have been using one of the PG specific Go libraries instead too. eg https://github.com/jackc/pgx

>> That ended up fixing the problem

Interesting take, since you're still stuck with "simple reports" that take hours to run. What about writing more efficient queries, adding indexes, normalizing the DB... ?

Wouldn't be the first developer to just SELECT * with a thousand joins and then sort data in app(!).

Or even worst/better, thousand of SELECT with joins returning one result and then sort in app.

This sounds like the Hibernate Execution plan I found one time when it wasn't configured properly.

It amazes me the complexity of solutions these days, when 20 years ago almost everything ran in relational databases and query tuning was usually the solution to performance problems.

Because we got rid of DBAs in favor of “big data” developers that never learned much about SQL in the first place.

Because 64gb of ram is really cheap these days.

It no longer makes sense to tune your queries, or to wait weeks\months for the vendor to tune their queries, when you can just slap a few sticks in and call it a day.

> What about writing more efficient queries, adding indexes, normalizing the DB... ?

Adding indexes seems like the best effort/reward benefits of those.

In any case, I can easily imagine the following workflow:

1. Kick off report after first logging in in the morning

2. Get on with your day

3. Check report after first coffee break

I've heard about some database drivers/middleware being smart and optimizing/caching queries like "SELECT 1" to save the round trip, so now I do something like "SELECT now()" or similar to do health checks.

Go code often reinvents/reimplements a lot of things from scratch, reintroducing problems that have been addressed long ago in other systems.

It's like this new trend, let's rewrite everything in Go to be cool. Financially makes little to no sense.

Let’s not just target Go with that sentiment, it applies almost universally, just in varying degree.

Counterpoint: how is anyone supposed to learn, if not from their mistakes? We might worry about the blast radius, but there’s no compression algorithm for experience.

Well, for one, you can learn from other people's mistakes, which is better than learning from your own, because then you don't have to feel the pain.

Go is a deserving target for this criticism because the language itself deliberately made a lot of the mistakes other language communities made and learned from, like weak typing[1] and naive garbage collection algorithms. Literally if you opened an undergraduate textbook on either topic you'd see much better ways to do things. But early adopters argued vehemently that Go was simple and didn't need those things.

It does seem like Go is learning from their mistakes here: they've introduced precise garbage collection and it seems like some form of generic or template types are inbound in the next few releases. Perhaps in a few years Go will be a language I am willing to work in. But it would have been nice if a new language which already had these problems worked out had become popular, instead Go, which has reached popularity through hype rather than technical merit[2].

Tracking the history of template/generic types has been somewhat humorous: you can almost see it in this article[3] where the author starts in with the title "Who needs generics!", goes on to describe some frankly horrible ways to get around the lack of generics (it's amazing how complex Go's simplicity can be!) and finally backpedals in an update ("I am the last one to balk at the idea of generics in Go."). I don't mean this to be picking on the author here though--I've seen this history played out on other blogs and in the comments of Hacker News as well.

[1] Before you flame me on this, ask yourself if you can articulate the difference between strong and static types, because if you can't, you don't have the prerequisite knowledge to have an opinion on this.

[2] It's worth noting that the decisions made in Go probably have merit within the context of Google. The problem is that most Go users aren't at Google, and have different problems than Google, so the tradeoffs made by Go are nonsensical for their use cases.

[3] https://appliedgo.net/generics/

The question was and is rhetorical. People don’t inwardly digest the mistakes of others. And anthropomorphising a language? Most peculiar.

None of this makes NIH a less than widespread phenomenon.

The readers of this forum often do know their type theory. Gatekeeping otherwise won’t go over well, it just reads like an arrogant insult from someone utterly lacking in self-awareness and accustomed to presuming themselves the smartest person in the room with the only relevant opinion. Ironically, given the subtopic, much like Google often does.

Another option is to learn from the mistakes of others.

The problem is you don't even know where to learn about the mistakes of others until you make the mistake yourself and in making the mistake you get some clue was to what to search for that then uncovers the mistakes of others.

People rarely post their mistakes to stack overflow.

Most programming language implementations don't try to avoid using libc.

OK. Rust, too.

I.e. https://fasterthanli.me/blog/2020/i-want-off-mr-golangs-wild...

You lose a lot of allure of Go if "the world" is not implemented in it (mostly safety) — I think that is the core reason everything is reimplemented in Go.

Of course, the real reason is the same reason even Go exists — we always think we can do something better the next time :) But hindsight is not 20/20 when it comes to software development.

> It's like this new trend, let's rewrite everything in Go to be cool. Financially makes little to no sense.

Ha. This is maybe the archetypal software dev story.

React? Java once? C++ for OO?

guess what other shiny new language is doing that, too...

Zig?

I was thinking Rust, but in reality it's probably all of them :)

Go also commits another un-Unix-y sin, in my opinion, in that it doesn't respect kernel keepalive. In other words, you can't use sysctl to configure it. Every single Go app has its own behaviour, requiring a recompile to change.

As far as remember, Go didn't enable TCP keepalives until 2018.

Nodejs Sequelize is transparently doing this when ping connection.

That aside, I wonder why you need to keep the connection alive for > 30 mins, while usually sql con is short lived. Why can't you just close and reopen them, is it temporary table?

Not the GP, but I assume transactional consistency was important for the report, hence the need to keep the connection alive. That's a pretty common situation.

Does it means the report take the tables hostage for 30+ minutes? How can transactional consistency be achieved without write lock? Temporary table?

Reporting can typically be split into two transactions. One long-running readonly transaction with snapshot consistency obtaining the data for the report and a separate transaction which publishes the result.

MVCC.

SQL connections can be arbitrarily long-lived. ETL processes and other data-intensive jobs often take multiple hours.

OP said it need to sleep for 30+ minutes. Is it common for connection to idle for half hours for etl processes?

Interesting I’ve run into similar issues when we put network load balancer from aws in front of a db. It has a fixed tcp connection timeout so similar if the query takes a long time it’ll disconnect. We fixed this at the socket level by ensuring our linux syscnf was set to keep connections alive at an interval below network load balancer. Was a tricky problem to figure out.

Tangential: any good sql libraries in go? gorm lacks in features like prepared statements, batch inserts and we end up knitting raw SQL queries.

which library?

Sounds like pgx.

Spent forever debugging this myself.

https://github.com/jackc/pgx/issues/494

was the go library fixed?

No clue!

> The most senior developer (24yo or so)

I see the problem there.

I've seen older developers that call themselves senior, but lack basic knowledge. I've seen younger developers, wise well beyond their years. Age simply isn't a big factor in how you judge a developer.

Just because you saw a few exception does not mean the rule does not hold in general. Or are you saying most people don't learn with time (a corollary of your theory that age is not a big factor)?

I would say that I have seen no discernible pattern, so I have learnt that it is imprudent to judge a developer by their age. People do learn over time, but some people learn more "cogently" than others, i.e. some get more out of one year's worth of learning than others get in 10.

> most people don't learn with time

I think this is correct. The curve flattens with age (in my opinion).

> you saw a few exception

I think the seniors you met (the knowledgeable ones) /were/ the exceptions. Most seniors I have seen have convinced themselves that they know all there is to know.

Most people don’t magically grow wiser with time. They need to be in an environment where they can grow, otherwise they’ll emerge just as stupid as before.

Shameless age discrimination. Very smart people exist at all ages. I've seen 21yo junior grad developers outperform 45yo 'senior' developers.

It's unlikely (but not impossible) that a 24 year old developer has had much of the experience needed to be classed as properly senior.

Seniority is not about how smart you are. And most programmers of any age are neither prodigies nor geniuses.

Do not accuse people without evidence! I'm all against age discrimination.

..and when you see a company where the age cluster around a very small area you can tell age discrimination is happening. Which might very well be the case of the parent post.

It's even more complex as you cant even say dev X > dev Y for all tasks. So the age thing is even more silly. After so many years you start forgetting the stuff you did (in detail) in the first years anyway.

You're bringing up the exceptions that prove the rule as if they disprove it.

Age is a just a number. John Carmack created Doom in his early 20s.

That's a weird take. You don't think Carmack 2020 is wiser than Carmack 1993?

He is currently working on artificial general intelligence.

Every good rule has an exception.

Why not judge people by the result of their work, instead of their age?

"that there was nothing in the middle managing connections"

Found the real bug, and it wasn't in the library.

Nothing about go is magical, and you probably solved the problem blindly.

Wow, okay. You alright over there?

Edit, because meh: I'm making no claims about go itself. No idea what makes you think that's what I'm saying, since I'm clearly talking about a library, and not even any stdlibs. "Magic" is just a term useful for describing systems that sweep much of their abstractions under the carpet in a way that probably has gotchas. Granted, the term itself is magical.

In terms of fixing the problem, I knew for a fact that the keepalives that I was seeing were nothing like what I've seen in the past, at many companies, across Oracle, postgres, and MySQL, all who've implemented "SELECT 1" for the sake keep alives, by devs who've been in the field for much longer than me. The suggestion was by no means blind, unless you consider implementing a widely used method for this exact purpose, "blind". Had I gone a different route in fixing the problem within the stable, existing system, it would have likely broken many of the other database connections by many teams. I'll pass on that, since frankly, even ignoring the risk of such a change, the dev should have done the investigation themselves.

Your post was unnecessarily aggressive and seems to come from me having struck a nerve somehow. Genuinely hoping you're doing alright. Peace.

I presume they took it as an attack on Go. Truth is, it's an attack on the library developer who themselves may have found their keep-alive solution by stumbling blindly on it.

Select 1 is a really hacky keepalive that relies on several assumptions, I'm not surprised it wasn't expected.

I have no affection towards go or any language. They're just tools. You sounded elitist calling something magic and pointing out someone's age as part of your point. And your passive aggressiveness to my response is proof of it. I "genuinely" hope you're doing alright too.

Heh, I wasn't being passive aggressive, I was being serious. Take a breath, man.

Also "magic" is not a new term: https://en.wikipedia.org/wiki/Magic_(programming)

(The 80/20 rule applies below, some developers do care)

Developers... just don't care. They want to spin up an ORM, point it at a URI, and forget about it.

I've fought this for over a decade now as a DBA, SRE, DevOps, and architect. Most of the developers don't want to deal with anything infrastructure-wise; they want to spend all the time they can just focusing on the problem they're writing software to solve.

Observeability, reliability, scalability - these are all words that are translated into either "someone else's problem" or "unproductive busywork" in their minds.

Many interests are pulling developers' attention in several different areas all the time.

Database, security, accessibility, performance, infrastructure, tooling and productivity, business concerns, workflow processes (agile), language concerns, new things

All of these like to say "if only the developer could do $MY_AREA better, they'd be better developers and we'd have better software". Each of them wants to pile on more requirements for what devs ought to know.

Let's say we agree that devs should know all of these things. After you tally up every area's demand, you're probably looking at a 10 year timeline before someone's decent in all of these areas by spending approx. 1 year on each of these areas doing meaningful work (as opposed to contrived tutorials).

Even then, things change constantly, so you'd have to continually practice all of these things. Who knows if we'll add a new category next year? Then it becomes an 11 year timeline.

At some point, developer responsibility has to stop.

I have done exactly this and found it to be a frustrating and thoroughly thankless exercise. No stakeholder for development teams care about any of this, and your allocation of shared resources plummets as people realize they can dump problems on your team and instead focus their attention on the ones who do not care.

I don't agree. If you're designing data structures in a code base you shoulder some of the responsibility for the persistence characteristics of that data.

There's a lot of devs that think database design is the same as starting a new ORM class and generating a migration file.

> Database, security, accessibility, performance, infrastructure, tooling and productivity, business concerns, workflow processes (agile), language concerns, new things

Yes, these are all things that devs should strive to know as much about as possible. Software isn't easy. It takes a long time to become an expert. 10 years sounds about right.

> Who knows if we'll add a new category next year?

Skill domains do come and go, but I think the ones you've listed are solid staples of web development for the past decade and likely will still be for a decade more.

> I don't agree. If you're designing data structures in a code base you shoulder some of the responsibility for the persistence characteristics of that data.

There's usually not a relationship that goes the other way though, for example, developers don't tell DBAs to pick up code so they can write the models in our language in addition to the underlying SQL. This highlights a trend of increasing responsibilities pushed onto the developer.

>Yes, these are all things that devs should strive to know as much about as possible. Software isn't easy. It takes a long time to become an expert. 10 years sounds about right.

But in some cases it's specialists designating what an expert developer should know. It's giving away control in some respects. This turns into new job requirements and a higher barrier for entry. The growth will need to stop at some point.

> There's usually not a relationship that goes the other way though, for example, developers don't tell DBAs to pick up code so they can write the models in our language in addition to the underlying SQL. This highlights a trend of increasing responsibilities pushed onto the developer.

Yes, because as a developer you're the one who has the responsibility of implementing the business requirements of the app. There's no trend here; this is the way it's always been. The buck stops with development for a lot of things. The developer is in a unique position to respond to many incidents because they have an intimate understanding of how the business requirements wed with the technology in ways someone like a DBA does not.

DBA's have plenty of responsibilities of their own, such as handling a 3AM alarm that goes off when some part on the application starts hammering the DB from some poorly designed N+1 query problem in the codebase. Often times when the DBA tries to teach the developer it's because he's sick of getting those 3AM wake-up calls.

> But in some cases it's specialists designating what an expert developer should know. It's giving away control in some respects. This turns into new job requirements and a higher barrier for entry. The growth will need to stop at some point.

That's a strange mindset to have. Different jobs have different technologies, and as a developer you learn how to work with them. The specialists aren't designating anything, the job you're responsible for is.

Agree with this. There's the whole DevOps nowdays as well, which basically just shifts what used to be an entirely separate, full-time, role onto the developer. Adding DBA to that sounds like it would benefit noone, except perhaps business owners looking for short-term savings at the expense of productivity, ala open-office floor plans.

> There's a lot of devs that think database design is the same as starting a new ORM class and generating a migration file.

Yes, this. Add to the list those who have drunk the DDD koolaid and the database is "merely an implementation detail".

as someone with 6 yoe and who considers himself a decent developer, I am looking for ways which can help me to be so-called expert developer-- I don't mean to say it mockingly.

Genuinely interested in the advice which can help to gain more than I will on my own with my current trajectory.

10, 11 year timeline to what?

You should be constantly learning everything about the whole stack so that you can actually build functional, reliable, manageable and maintainable systems.

I expect a competent developer to be able to build a modern multi-page web application, with a HTML/JS front end, relational database back end, appropriately configured certificates and DNS/CNAME/URL, build basic uptime and application monitoring and do a basic SQL ETL data retrieval process.

That seems like a reasonable bar, and while the specific tools have changed over the years, that stack is basically the same as it's been since the 90s.

> 10, 11 year timeline to what?

To be decent at each of those areas I listed.

Why those 10 specific things?

Those are all areas I've seen "Developers should learn X" calls-to-action.

Sometimes it's outright loathing over developers that don't know specific topics. Other times it's wishful thinking, or maybe a nice-to-have.

If you, as a developer, read all of those posts, they probably all make fair points about the importance of knowing each of those things as they relate to development. You decide there is some merit to learning it.

So you decide to make a to-do list to go learn each of those topics, because you want to listen to the blog posts and be a good developer, and do some real work to prove you know it. That's what's going to take you a while.

You seem to be contradicting yourself without realizing it.

> You should be constantly learning everything

This is an in-progress action i.e. the developer is still learning.

> I expect a competent developer to be able to build

This is now considering a "learned" developer.

I am not sure you are making the point you think you are making. The point I think you are trying to make is your expectations of what an experience developer should know. But, you seem to be expressing it as what a new developer should be doing.

While the core discussion of the article might be in regards to what developers do and do not know, I can't help but notice that a developer knowing about something does not necessarily allow them to be productive in that area of their knowledge (especially relative to another co-worker with both the knowledge and the dedicated focus in that area).

Also very important to note: the comment you are addressing seems to be referring to knowledge that is local (particular customer necessity/problems, particular architecture choices for infrastructure, particular product design decisions, particular ways to answer the quirky CEO/CTO in a way that they understand, etc.). There is a lot of locale-based knowledge that a developer must learn at a company/job/project and can even change over time (temporal-locales).

Globally-applicable knowledge like frontend, backend, and general CS concepts are for sure a reasonable expectation of an experienced developer. But, there is a delicate balance a developer must take in the real working world that is subject to not attempting to master every aspect of the product/business (especially if it overlaps with someone else's job/focus) just because you have a high-level understanding of the global concepts. In other words, it is not necessarily efficient for a developer to know every aspect of every language and every database in the company unless that actually buys the company more customers and money.

I would expect any decent manager to understand this very basic principle. Everyone in the company trying to be a master at everyone else's job does not help the company make more money. Being reasonable about expectations in the moment is also a critical asset of working together to make money. :)

hes not contradicting himself, the fact is there are a large percentage of developers who just don't care. its not a matter of still learning. they just don't care to learn.

> I expect a competent developer to be able to build a modern multi-page web application, with a HTML/JS front end, relational database back end, appropriately configured certificates and DNS/CNAME/URL, build basic uptime and application monitoring and do a basic SQL ETL data retrieval process.

What does all that have to do with OP's assertion that expecting application developers to understand that nuances of DB's listed in the article is noble, but unrealistic? A "competent developer" could fulfill your requirements and still not understand the implications of time drift or how to scale horizontally or other deep topics. Applications developers are the hub to many spokes, but expecting them to have deep knowledge across all technologies is unrealistic just as it is to expect a DBA to have a deep understanding of how a certain application framework works.

Exactly. Without abstractions, developers would never be able to get anything done. Every task would be dozens of new rabbit holes.

The problem is that all abstractions are leaky in some way or another. If you're going to use an abstraction, you should at least learn enough about it to know where to put the buckets.

Not really. That is exactly the point the article is trying to make. Developers need to care about these things - _enough to know who to go get help from_. That is the minimum. Also 10 years is an exaggeration. 2 years working on a non-trivial backend should expose one to these problems.

From what I have seen, products built without caring about these will usually get rebuilt a year from the original release - either by the same company or by a competitor who killed them.

> 2 years working on a non-trivial backend should expose one to these problems.

You can be exposed to them, but without understanding them, and experiencing both good, bad, and really bad 'solutions' to them, and understand the impact (on the business, on the code, on security, on maintainability, etc)... you just can't really get all that in 2 years.

I know plenty of people who've been 'exposed' to certain type of tech problems, and the solutions they decide on are objectively really bad for any metric other than "stop this error from showing up on the screen right now".

I've been doing this for a bit over 25 years, professionally now, and... there's a lot I don't 'get' with current stuff. But I've seen and lived enough projects, in enough different situations, to have a good idea of impact of tech decisions, and to understand how to make tradeoff decisions.

I had someone call me up to 'fix' a problem in a system I'd given then 15 years earlier. It was still running, more or less the same, and... spelunking your own code 15 years later gives you a new perspective on the impact and value of decisions you make. Many of the things people get hung up on (code style, tabs/spaces/etc, particular naming conventions, etc) provide pretty much no value in digging in to old code that no one has touched or thought about in a decade. Correct comments, sample data, repeatable tests hold so much more value, but are harder to get people to commit to following through on.

I wish I could slap anyone who gives a hoot about tabs vs spaces. Fortunately modern languages like go are removing the version control problem that not caring about style and using auto-formatting IDEs produces.

> You can be exposed to them, but without understanding them, and experiencing both good, bad, and really bad 'solutions' to them, and understand the impact (on the business, on the code, on security, on maintainability, etc)... you just can't really get all that in 2 years.

I don't think this is about becoming expert. This is about learning at least very basics in multiple areas so that you are not completely clueless and know issues exist. When you redefine requirements to "2 years active learning worth of knowledge", you moved goalpost quite far.

> I've been doing this for a bit over 25 years, professionally now, and... there's a lot I don't 'get' with current stuff. But I've seen and lived enough projects, in enough different situations, to have a good idea of impact of tech decisions, and to understand how to make tradeoff decisions.

This is being senior.

>Not really. That is exactly the point the article is trying to make. Developers need to care about these things - _enough to know who to go get help from_.

That isn't it. They aren't telling you to learn about database details for the sole purpose of "DBA handles databases. Go ask the DBA database questions".

It's more than that. They're offloading specific knowledge onto the dev and then making them accountable for it. It's a reaction to common questions and an attempt to answer them all at once by teaching devs answers to common questions. This is a noble goal, but it's problematic in aggregate from multiple perspectives.

The aggregate goal of all of these areas trying to teach developers their own specialties is to make developers the masters of low-hanging fruit.

> Database, security, accessibility, performance, infrastructure, tooling and productivity, business concerns, workflow processes (agile), language concerns, new things

Hammers, nails, tape measures, saws, levels, reading blueprints, adhesives... If you want to be a professional, you need to learn the tools of your trade. Being able to work directly with a SQL database is a foundational capability in the software development trade. ORMs are a mental crutch that are overused to the detriment of many systems.

Speed squares are a mental crutch that are overused to the detriment of many structures...

Look, I actually agree with you about ORMs, but come on, this is a pretty bad take on the problem.

The issue is rarely the tool itself, it's the changing requirements around how it's used.

You can have the best hammer swing in the world, and maybe you sling the best tape ever. But if the building codes are revamped in non-trivial ways every 6 months, you're still going to want someone spending dedicated time understanding that. If your job is to assemble the stairs, you focus on that instead of wasting time asking why this blueprint happens to place the stairs at a slightly different angle, or why some places demand kiln dried timber vs simple construction grade.

Your area of expertise is NOT the building code, it's integrating the stairs into the rest of the structure and actually having people walk up them.

>If you want to be a professional, you need to learn the tools of your trade.

The tools of the trade has constantly been expanding. It's not set in stone as you imply.

It's like me telling a carpenter that they now also must become an electrician, window installer, insulation installer, HVAC installer, steelworker, concrete worker, brick layer, and security system installer.

Your new job title is "Fullstack Building Developer" instead of "Carpenter". We can't afford to have 10 specialists that all do a great job, we can only afford 1 person doing a poor/mediocre job in 10 things.

Perhaps development work has reached complexity where it demands full-time attention?

Perhaps there is a reason why DBA, SRE, Devops and Architects are separate roles?

> Perhaps there is a reason why DBA, SRE, Devops and Architects are separate roles?

It's mainly an artifact of the way we've broken up degree tracks, and the boundaries that each group is taught to stay inside, lest any particular group actually ends up culpable for a failure.

Make fun of the "full-stack rockstar ninja" all you want, but the reality is that it is possible to have a functional understanding of all of these areas. It's just a matter of having the confidence and doing the legwork.

To the extent that more of your people have a working knowledge of these domains, you'll not only have a better end product, but a much easier time getting stuff done.

There's room for hyper-specialized expert consultants in each field, of course, but the myth of the myth of the full-stack developer exists primarily for political convenience. Most of this stuff is not any harder than the rest of it, and can be learned by anyone willing to sit down and learn it.

A DBA can't fix a developer's code. A good 3/4 of the list linked in TFA are things a DBA or Architect can't affect.

At some point, a developer needs to be responsible for how they use a tool that's been provided for them. Abstractions break.

What, you're not a full-stack rockstar ninja?

Hell Yes. Break it down further: the front end demands that. You could split further into front end architecture, front end UI performance (DOM etc.), web performance (taking into account network etc.), tooling. Even getting NPM to play nice could become a full time expertise!

The "developer". Can do anything that involves smashing at a keyboard! It's like calling a politician a "talker". Oh well you could do ground control for the next moon launch then, that's just a "talker".

As a developer: I do care, but it's hard for me to focus on building software if I also need to think about DBA tasks, DevOps tasks, and so on. These things all take time, patience, and energy. I've just spent most of today running and re-running a CloudFormation template to create a SQL database. Most. Of. A. Day. It's partly because I'm not a DevOps expert and even if I wanted to be one, that would also take time, patience and energy.

There's a reason people have specializations...

> It's partly because I'm not a DevOps expert and even if I wanted to be one, that would also take time, patience and energy.

It's this exact divergence that creates the disconnect. If someone doesn't understand and doesn't have to care about the whole experience, they're going to focus on their side and stop when their side is good enough.

On the other hand, if that same someone is going to be regularly developing the application, making changes to the database, and triggering deployments, they will find a way to make the process flow. They'll make it adaptable enough that it's not a day-long pain any time they need to run a migration or spin up a test DB.

The whole toolkit can and should be available because every piece of the stack opens up new possibilities. You want, and at least at some level, can have, people who know this well enough to make good use of it. Nitpicking over "not my specialization" is the antithesis of a smooth engineering process.

Developers... are punished for caring.

Stubborn people like me keep doing the right thing, but the fact is that there are kudos and recognition for implementing a new feature. There is nothing for keeping servers from crashing, and only a little for reducing server count if you wait for things to get bad first.

The problem with doing things right the first time is nobody appreciates how hard it was. And you will sometimes get questioned about your loyalty and your competence to do the job.

This kind of "top-to-bottom" architecture approach reminds me of Apple. They have this notion that you can't provide a good product if you control only the software or only the hardware. You need full control of both, designing in the synergies to produce a really top-notch outcome.

If you read the blogs by large shops like Google, CloudFlare, or Facebook, they do the same thing on the server side. They design the software for the failure modes of the hardware, and conversely the hardware is designed to be low cost in full knowledge that the software can tolerate high failure rates.

Why am I talking about hardware? Because in a typical n-Tier design, "The Database" is just one of the pancake layers between the metal and the Internet. Every layer matters, and every layer interacts with the others. Hearing developers call themselves "full stack" is hilarious to me. They're basically saying that they know 2 out of about 20 layers! Do they know about load balancing persistence? The security tradeoffs made by TLS 1.3 zero-RTT? Cache-control headers? Disk latencies? Automatic scale-out? Virtual machine affinity and anti-affinity rules? Backup and restore? Etc...

The original post about how various databases treat transaction isolation modes is a tiny, tiny fraction of what a typical developer ought to know about the "layers below the web server" but likely doesn't. For example, about 50-70% of bespoke software I've seen in the wild either doesn't use database indexes at all, or uses them ineffectively. About 80% of websites either do not use cache-control headers, or if they do, they'll often end up with front-end errors due to the caching layers violating the consistency of data coming in from the database. Sure, it's a maxim of the industry that cache invalidation is hard, but there are workarounds such as constructing URLs based on the content hash.

All of the fancy transaction isolation modes, to me, is wishful thinking. Get more developers to use indexes first, and then come back and teach them the esoteric stuff after that!

Application developers don’t care about data - they want the database to be a dumb storage unit because they only care about their application functionality.

If you can convince your application developers of the prime importance of data, they will start caring about their database.

Applications come and go but data lives forever.

You’d be surprised how much rank and file developers can care about observability and reliability. The key to unlocking this is making them responsible for how their code runs in production by adding them to the on call schedule.

Too bad there isn't an equivalent on-call punishment for management and others around the dev process who also make mistakes and bad decisions.

As far as I can tell, they get paid more for doing less.

> Observeability, reliability, scalability - these are all words that are translated into either "someone else's problem" or "unproductive busywork" in their minds.

The root of the problem is the same reason why Google keeps creating completely brand new applications instead of just maintaining and improving their existing ones. Maintenance is not rewarded. Anything existing is not rewarded. Management only focuses on new customers brought in by new features or product asap. Management doesn't care about maintenance; they care about growth, so developers have no real interest in it. Words tend to be empty. If you want to see how maintenance is really valued, look at the company's promotion system.

All of those things are unproductive until after your project is successful which is usually not a guarantee when building the software.

You're not gonna need it.

Observeability, reliability, scalability - really easy to bolt on once you are successful.

There's a middle ground, and a DBA turned SRE turned Architect is probably ok with compromises, those are all roles where "it depends" is a bylaw.

I sometimes feel in the minority. I love databases. When I work in the Ruby on Rails ORM ActiveRecord I can actually visualize the SQL it is generating in my head and also do all sorts of tricks when needed.

That's the key. ORMs get a bad name but most of the time you just want to display a list of things, or one thing in more depth or maybe create a new thing.

ORMs unfortunately, have a habit of getting in the way when you want to do something they don't natively support. When they just ignore things the database provides people just end up reinventing the wheel. Rails' implementation of enums is a good example of this.

There's truth to this, but there are also reasons for this.

First, it may be fair to say that infrastructure is in an other domain of problem solving than structuring and implementing program logic. (Speed requirements and crunch time tend to lock a person in a single domain.) Second, those developers, who do understand the importance of infrastructure, tend quite naturally to care for those levels which are closer to them and which they actually may understand (like the programming language and how it actually implements things, standard libraries, dedicated libraries, the OS, any middleware). Finally, SQL via networks (and all the flavors this may come in) is hard, there's a reason for why there are specialists.

Speed of development, of course, is orthogonal to concerns about infrastructure. Which may be a major concern. Communication (a constant source of enterprise horror stories) and general awareness (on all levels) may help. These are not problems that can be solved on a single side of the implementation. And then, there's certainly some kind of "developer heroism", which doesn't especially help.

(I guess, I've successfully managed to have messed up with everyone? ;-) )

Probably because they don't need to think about those things.

I started my career as an Embedded Software Engineer, and memory allocation, and clock cycles hand to be managed. Our software ran on systems with limited memory, and had to fit in one 60hz cycle. We supported VAX system that used VAX Floating point, and had to be cognizant of both floating point conversions AND endian byte encoding.

These days, such concepts are basically just trivia answers for interview questions.

I had to think about those things because it was required. System software would crash, and debugging it on an expensive government flight sim 1000 miles away was impossible.

Perhaps for the developers you work with, they don't need to think about those things because they're not required to. After all they have access to a dedicated DBA/SRE/DevOps/Architect guru.

Not meaning to come off as trite, but I know my response will sound like I am.

But, what you described is exactly why senior devs/engineers make 2x, 3x, 4x a junior.

Seniors make more because they have that extra knowledge you're talking about that can take a decade or more to accumulate. The idiosyncrasies between different DBMS or even languages or even specific versions of languages. That knowledge doesn't come cheap. It literally takes upon years to develop.

This is partially why I think bootcamps are bullshit. I'm sorry, but there is no way to become a competent full-stack developer, especially if you're not targeting web/browser. Give me someone with 3 months of bootcamp C++, I'd be surprised if they can get a nontrivial program to link, let alone compile.

For those devs the solution is that DBAs provide stored procedures and predefined views and block them from accessing anything else. Or address it on the app level, have someone knowledgeable create repositories with all the queries, and mandate the rest of team to stick to using only these predefined methods. Either way, people who don't care about infrastructure should have their access to that infrastructure maximally limited and wrapped in safety nets. For both sides to sleep better.

Depends on the organization.

Thankfully in most places I have worked so far you wouldn't go very far being an 'X Developer'.

Everyone has always been required to be an expert in at least two domains, those T and Pi shaped concepts.

So I have very seldom met those kind of devs that only want to spin up an ORM.

>Observeability, reliability, scalability - these are all words that are translated into either "someone else's problem" or "unproductive busywork" in their minds.

hence why you work in a team.. everyone provide their domain expertise.. we don't need dba to be dictating FE devs on strategies for immutable state management in the UI.

add to that "do not want to declare/worry about types", "cant be bothered about RAM usage/cache coherency/etc", "concurrency" etc. etc. All of those are "premature optimizations".

> All of those are "premature optimizations".

And they are, until they aren't. And I really do mean that in both directions: Much of the time, the simplest, dumbest, most naive solution is 100% fine for realistic load for the forseeable future. And then some of the time it breaks terribly and you do need to spend effort optimizing it, whatever that entails.

I don’t care and that’s why I use raw sql lol. I don’t want to learn some orm bs

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data.

Part of it may be due to the fact SQL isn't really a programming language but a declarative DSL for manipulating sets and tables. Things like GROUP BYs and PARTITION BYs (window functions) that come naturally to mathematical types/functional programmers are less intuitive to procedural programmers.

I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it's more attuned to a programmatic mindset.

(this is not universally true of course -- many programmers have no issues with SQL at all.)

I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it.

IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database.

Your goal was to accomplish as much as possible in a single query and then to simply return the results of that query to the interface. That meant formatting numbers or currency in your SQL. Optimizing inserts or updates to be handled in a single query. Grouping, counting, left/inner joins, having statements to filter on aggregate results. More than 1 or 2 queries for the primary area of the screen was both a rare and foreign experience.

And then ORMs started to slowly integrate themselves into the flow of various frameworks to automate the repetitive things around CRUD tasks. Then to address scaling & bloat problems we saw an uptick in REST APIs, microservices that further made those ORMs the norm...and then many developers started actively trying to stay within those API constraints to an almost religious degree which led to a nested payload becoming acceptable fueling the whole "NoSQL" situation, along with the idea that it was somehow better to repeat the same data thousands of times over.

A whole lot of people pushed back against this and eventually, it mostly ran its course. I've often seen resistance to SQL to be driven by fear of SQL more so than anything else. As soon as people get a basic comfort level with SQL, it become almost automatic.

> The application language was a pass through later between the client and the database.

This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with arcane and inscrutable logic

There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures)

Here's a powerful mindset trick: think of SQL views as an sort of a REST API, but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical).

I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API that outperformed REST at scale -- it's called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables/schemas were to change, your consumers don't care as long as they can access the SQL View.

SQL views are also composable: you can build SQL views on top of other SQL views, and any changes made in the base views are propagated throughout. Need to add/transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I've built many systems by composing SQL views and they're very maintainable and very flexible. They're kind of like function compositions but on tabular data.

The rule of thumb is: always access a database through a view, never the underlying raw tables. In computer science, a great many maintainability issues are alleviated through a layer of abstraction/indirection, and SQL views provide exactly that.

This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers (webapps, analytics backends, Tableau, ML tools, etc.) The "API" remains consistent throughout.

This is trying to avoid the reality that the data is in a database. A database is not just a flat storage area that can be poked by code from outside. It has a lot of relationships and integrity constraints that need to be maintained by the DB itself, and all this is expressed in SQL/stored procs. Trying to avoid this is basically removing the power of a DB and converting it into just an expensive and cumbersome storage area.

But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable.

I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code.

Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.

But going too far the other way is how you end up with performance 100-1000x worse than it should be. Which is an actual thing that happens quite often in the wild. Also often ends up heavily dependent on some ORM or framework, making rewrites or multi-client DB access dangerous and painful.

This is the exact fear of SQL that's being talked about. You can make a mess of anything but its not hard to have a well maintained relational DB with minimal cruft.

In my observations, ORM use has a perspicuous relationship to piles of arcane spaghetti code.

Not to mention, 50%+ of ORM managed DB schemas that I've observed don't have proper constraints, indexes, relationships, etc. Because the developers using the ORM think it's a magical tool that makes understanding SQL and relational databases optional.

There is an entire world between ORM and PL/SQL. Programmatically constructing SQL statements is also a thing. Just because someone writes SQL does not mean SQL needs to be spread through out code or that we need to have lot of logic in PL/SQL. Of course, there will be cases where a store procedure is desired (any kind of validation that cannot be expressed as fkeys, canonicalization of some core data components etc). I worked on DBs writing SQL for several years with only minimal code as stored procedures.

Of course the database vendor and culture also plays a role. We were primarily a mysql/postgresql shop

Given how strong typed PL/SQL is, based on Ada, graphical tooling like SQL Developer exists, I wonder why it is much worse than either Java or PHP.

High performance SQL code should stay on the database, instead of doing wire round trips.

> I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it.

That pretty much describes my career, except it was more than 2-3 weeks. I agree there is value in being familiar with the ins and outs of SQL, but...

> As soon as people get a basic comfort level with SQL, it become almost automatic.

I don't know about that so much. I find that as soon as I need to write something much more complex than a simple select statement I am all but guaranteed to get caught by SQLs many gotchas. Eventually you realize the mistake, and that experience provides the knowledge to know how to correct it, but a good language helps guide you away from being trapped by those mistakes in the first place.

SQL is not a good language. It was clever for its time, but we've learned a lot about language theory in the many decades since. It is a travesty that we haven't put more effort into designing a modern database query language. To use SQL in 2020 is like writing software in COBOL when you could be writing software in Rust. Where is the declarative query language equivalent of Rust, Haskell, etc.?

The NoSQL movement was supposed to be about improving on query languages, but sadly it soon turned into "NotRelational" instead, which killed off any momentum away from SQL that was built.

>Where is the declarative query language equivalent of Rust, Haskell, etc.?

Maybe Cypher from Neo4j?

> I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it.

I did this (actually for longer), but it always washes away.

One challenge for me is I don't see an incentive to learn complex SQL setups unless absolutely necessary. Since I tend to work at startups, development velocity is way more important than optimized codebases. I'd rather write the "dumb" solution that nearly every engineer can grok than the "elegant" solution that goes above people's heads.

That aggregate function, yes, I know I can do it 100% in SQL. However, it's much easier for me to selectively pull parts into memory and modify them with language tools I work with everyday. When the junior needs to modify that function, they can do it with tools they're most familiar with.

With the whole shelter-at-home thing, I had a chance to work on a simple app for my kids. One of the corollaries of "simple" was avoiding an ORM.

Implementing logic is single SQL queries can definitely be a bit challenging, but I thought it was also quite rewarding and liberating - Raw SQL is incredibly powerful!

At first I missed for loops.

Then I was empowered and did not miss for loops.

Now I still miss for loops.

I have a side-project to solve Kakuro puzzles with SQL logic, not because it's useful, but because it seemed possible and distinctively challenging.

I'll show off the POC if I ever get around to smoothing the rough edges.

Here is one nice example: https://sivers.org/pg

> I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it.

i did tons of sql couple of years ago on a reporting team. Now I do android dev fulltime and don't remember any SQL beyond basics, highly doubt it will all come back to me if i tried.

it doesn't matter, you'll google the details. many people aren't aware that sql isn't just a stupid way of filtering tables and can do things server-side that you'd spend hours implementing and testing on the application side with worse performance in the end most of the time.

SQL is very much like CSS to me.

It's declarative, the primitives seem entirely non-intuitive, it often takes a lot of fiddling to get what you want, the behind-the-scenes execution is mostly a black box, and while it's supposed to work the same on different implementations (of browsers/databases), there are tons of little gotcha quirks.

All in all, they're both entirely different skill sets from traditional programming, and also where experience counts for a ton more than just normal logical thinking.

> It's declarative, the primitives seem entirely non-intuitive, it often takes a lot of fiddling to get what you want, the behind-the-scenes execution is mostly a black box, and while it's supposed to work the same on different implementations (of browsers/databases), there are tons of little gotcha quirks.

My reaction to that is that it's similar in a different way: everyone needs to use it but many developers don't take it seriously, avoid learning how it works, and then complain that it's unintuitive (i.e. not something they had already known) and hard to use because they're basically just poking randomly until they get close enough to what they want.

It's true that not every implementation is the same but … where else is that not true? This is also true of operating systems, filesystems, every library implementing a standard format (“Why doesn't this PDF open in …”), etc. I would have trouble supporting the belief that databases are an outlier in this regard.

If I write a C program, especially with the appropriate compiler warnings enabled, or some filesystem code, there is a high chance that it will work across platforms with no further coding necessary. Anecdotally, the same cannot be said for CSS or SQL.

It doesn't matter much for SQL, because I always know what database I'm using, but for CSS, it's a massive pain.

That's a charming belief about C which is only true if you work on the same operating system, processor, and don't do anything complicated. If those are not true, as I've experienced many times over the years, you will learn otherwise about things like differences in floating point behaviour, memory allocation and access patterns, which filesystem and/or locking semantics, etc.

In other words, it's about as true as it is for CSS and SQL, where that kind of simple use is also stable.

When you look at SQL from a logical/set-based perspective, it is by no means unintuitive. Basically, all you do is join all the tables you need and then filter out everything you don't need and maybe do an aggregation here and there.

How about the following:

- When to use JOIN vs a subquery?

- When is a subquery actually a correlated subquery? Will this destroy your performance? Or is it a critical feature?

- Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?

- When do you use WHERE vs HAVING?

- Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?

Etc. etc. The basic concepts are simple, but the implementation details quickly become very complex, particularly when you're ensuring high performance with indices, and making sure the query uses the indices.

And all of the questions I pose above have clear answers... but the answers certainly aren't obvious from SQL basic concepts.

And also, while JOIN seems like it ought to be intuitive, in real life it seems like it's like pointers in C -- some people get it pretty quickly, other people struggle forever.

Like everything else, write it the simple/elegant way then profile it and tweak if you have to.

Once you're at the point where you have to worry about these things, tuning the SQL is still probably much less complex than writing the query in your app language or figuring out how a NOSQL db can do these joins.

> The basic concepts are simple, but the implementation details quickly become very complex

True, but those kinds of questions come up in every language: Should I use an array or a dictionary? Should people have references to projects or should projects have references to people or both? Is money a float, an int, a decimal or should I write my own money class? Should I memoize the results? Is it thread-safe?

As you can see, this could go on forever, pretty much for any language.

I found the jooq blog (not affiliated) excellent for understanding the concepts of SQL properly.

This article is a great starting point:

https://blog.jooq.org/2016/03/17/10-easy-steps-to-a-complete...

there are many other articles worth reading on there

You need to spend time working with the language and understanding how the set based operations used in SQL work. Declarative languages means you can express things in multiple ways and get the same results. This is an incredibly important aspect of SQL

>the basic concepts are simple, but the implementation details quickly become very complex

This is no different than programming. Assuming that SQL doesn't have complexity because you can only SELECT, INSERT, UPDATE or DELETE is going to have you banging your head against the wall. Tackle the complexity in SQL like you'd tackle the complexity in your programming language of choice; read the docs, work through examples, and read how other people solve the problem. There's ton out there for SQL

>When do you use WHERE vs HAVING?

HAVINGs allow you to add a condition to an aggregate function. So SUM(myColumn) > 5 would be something you put in a HAVING clause. Honestly, this is pretty clear cut.

>Should you put constraints in the JOIN or in the WHERE? Will the distinction drastically affect performance?

The first thing to understand is a condition in a join versus a where might return a different result set, specifically on anything other than an INNER join. The impact to performance will depend on the rest of your query, your data, and your index coverage. For simple cases, there is likely no difference. For complex ones, there may be an impact

> Is the NULL from the join because no joined row was found, or because the joined row had a NULL value itself?

An inner join shouldn't produce a null. That's why it's an inner join, as the data needs to exist in both places. If you want to "test" whether a join found a row, look at the field you were joining to and see if it's a non-null value. Nulls won't join to Nulls unless you've change some settings in most RDBMS. If you're looking at other fields to determine the presence of a row from a join, make sure you're looking at a non-nullable field.

>When to use JOIN vs a subquery?

A better way to phrase this would be when to just join the table, vs writing a sub query and joining to that. When is a question of the complexity of the query and performance characteristics, and that can't be answered in the abstract. The most important thing is that in a large number of cases you can do both, and knowing how to express things in both ways is powerful.

>And all of the questions I pose above have clear answers

No they don't. Any time you're wondering about how different SQL impacts performance, there's absolutely a huge "it depends" angle on it, because how you've structured the tables, index coverage, and the volume of data, can have a significant impact. This is why DBAs still have jobs, because the database is an incredibly complex system. You seem to be complaining that SQL shouldn't be complex, yet are not willing to accept that it is more complex that you've assumed it to be. It's complex. You don't need to know everything if your just a dev, but don't just assume it's simple.

>while JOIN seems like it ought to be intuitive

I'd check the diagram here -> https://stackoverflow.com/questions/13997365/sql-joins-as-ve... Half those joins aren't needed as you can re-order a right join into a left join. For 95% of development Inner joins and left joins are all you need. The other 5% is an outer join and that's mainly needed in report writing, not app development.

I will be very glad if you actually answer these questions, in a separate comment. I'm driven to write this by nothing but the desire to know.

I had way less trouble in college with relational algebra, compared with SQL. SQL is by no means intuitive. Projection, which is what you do last, comes first in the select statement. Then there are all the join types.

Relational algebra, which is what SQL is ultimately based on, is much more elegant.

And with CSS, basically all you do is tell the browser how things look.

Conceptually yes. The devil is in the details: tables, flexbox, grid, div-soup, inconsistent naming, etc.

SQL can do a lot more though.

Triggers, functions, procedures, access control.....

Plus indices, views, synonyms, constraints, and the entire concept of data normalization...

Turing complete!

This is... one of the best comparisons I've seen and sums up the reason why I dislike SQL as well (although I know how to use it).

Sometimes it really feels like you're trying to give instructions to someone via chat which gets Google translated to chinese, japanese and russian on the way - it's this very lossy communication channel where you need to tweak the language "just so" to get maximum performance. I think it's no wonder that newer DB designes opted for more direct and tailored APIs.

Databases are quite transparent about what they do. The impression of "just so"-ness is a property of the observer, not the system.

> it often takes a lot of fiddling to get what you want

and yet, for any nontrivial operations, opting for a few simple K:V stores instead means one basically ends up implementing an unrolled SQL engine processing loop to do the application-specific select statements you would otherwise need.

there's a reason SQLite took over from dbm files ...

The main difference is that with CSS you can have unintended interactions.

With SQL you get unintended query plans, but it always returns the same rows (if it doesn't time out).

I’ve been casually trying to knock into the black box of MySQL’s internals with no success yet. I have queries that do GROUP BYs across multiple unindexed columns in frequently large tables, so it ends up with temp tables on disk and filesort. Can someone point to the source code for processing this kind of GROUP BY situation?

SQL feels to me like bash or regular expressions. You can do amazing things if you do it full time. But if you do it only a few times per month or year you quickly forget all the subtleties and it gets hard to understand even the stuff you wrote half a year ago. I guess in the end things have become so complex that as a dev you can’t be good at everything. I often wish there were dedicated database guys but if you have one usually that person is sysadmin and won’t help much with coding against the database.

This echos my experience. I've written some complicated SQL queries, but I do it rarely enough that I always have to re-learn a lot of it.

There have been a number of instances where I have had to look something up seemingly for the first time, found a good Stack Overflow answer, and then chuckled to myself because I had already upvoted that exact answer at some point in the past. SQL queries are a pretty common source of this, along with uncommon git and terminal commands.

I tend to disagree, since I find SQL orders of magnitude more intuitive than regular expressions, having no problem picking it up once every couple of months without requiring to fallback on books or cheat sheets for implementing the majority of queries. Sure, every now and then we have to optimize a query or implement a complex data extraction pattern forcing us to study how to do it, but for me this is unusual.

The key aspect of SQL that makes it easy for me is readability. Once you know the basics you can understand what a query is doing just by looking at it. Regular expressions notation on the other hand are really though to read, and indeed I keep always forgetting its syntax.

Common table expressions were all I needed to write <a href="https://modern-sql.com/use-case/literate-sql">literate SQL</a> and make it perfectly readable when I come back. But I was doing it almost full time so I wasn't forgetting much either.

I'm an SQL hater in remediation.

In a given week I might work with all of the following: SQL, C#, Python, JS (Kendo, Vue, React), XSL, bash, and more. I'm a quick learner and I pick things up fast, always have, I've got a deadline and I don't have the time or capacity to fully internalize the minutiae of all the technologies I have to work with. In other words I depend on the tools to show me the options at my disposal and construct syntactically correct expressions.

What I really dislike about SQL is common to most systems I dislike, where the tools aren't discoverable to me. In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause. So I have to build up my statement in this weird spiral pattern where I change something deeper in before I know what I can SELECT in the first place. The ability to give tables shorthand names with MS-SQL, i.e.

`left join [dbo].[sometable] st`

...is very helpful but I gather this isn't common to all dialects. Working with XML in SQL is a nightmare as the tool cannot tell you whether you can do obvious things like pass an sql:variable into nodes() until you actually query the server. (spoiler: you can't.)

I vastly prefer the functional programming approach to working with data e.g. C#'s lambda style linq. The tool shows me all the pieces I have to work with, and all I have to do is piece them together the right way.

Reading SQL is like reading German, where the last word in a long sentence determines the meaning of the entire sentence.

An SQL statement starts with "select ABC.XYZ", but you have no idea what it means, because only one screen later it is written that "ABC" is actually an alias for "T_ACCOUNT_BUSINESS_CREDITS" or something. The logical order would be "from ... where ... select ...".

Imagine a programming language designed like SQL. It would look approximately like this:

  function foo(a) {
    return s;
    b = join(a);
    s = concatenate("[", b, "]");
    by the way, a is list of strings, b is string, s is string;
    also, function "join" is imported from "lists", and "concatenate" is imported from "string" module;
    actually, don't return the result, just tell me how many characters it would have;
  }

> In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause

No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls.

The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary.

If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them).

As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.

May I propose, for your long expression of frustration, a simple fix. Don't use left join. Instead select x.x,x.y,y.x,y.z from x,y where x.x=y.z and ...;

This is the same operation but without the screwup syntax. The whole notion of left join style syntax is an abomination and - excepting for those who have internalized it to the detriment of normal discourse - represents needless cognitive load.

I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need.

When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the month, even when there were zero sales. Or another query asking "Which days didn't have sales?" and listing the days. I haven't found a way to do this in-database. I end up answering in code based on the data I fetch.

Definitely feel it's me not SQL, but not found the answer.

Your example is a fun one and not too hard to do.

Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets.

In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sales. Set 1 could be a "numbers table" or generated with something like "generate_series()" in PostgreSQL. Set 2 is made by summarizing the data in a sales table by date using "GROUP BY" and "SUM".

Then you're just looking at JOIN'ing those sets and COAELSCE'ing the NULL returned from days when there are no sales into 0.

The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting database). Once you reimagine the data in the OLAP style then these kinds of queries are simplistic.

EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.

For anything other than the absolute simplest case you should have two databases. One for OLTP (the application(s)) and one for OLAP (the reporting).

If your reporting queries are hard or complex it's because you did a bad job architecting the reporting tables. Reporting queries should almost always be the simplest form of query if you designed your warehouse properly.

Days with sales is easy with a simple SUM or COUNT and GROUP BY.

Days without sales is easy if you think about this dimensionally.

  SELECT dim_days.day_of_month,
    COUNT(fact_orders.order_id)
  /*
    Select from dim_days first because you want every day.
  */
  FROM dim_days
  /*
    Outer join to the fact table to pull in the data you have and add that to your dates.
  */
  LEFT JOIN fact_orders
    ON dim_days.calendar_day = fact_orders.order_day
  /*
    Filter results by the desired range.
  */
  WHERE dim_days.month = 4
    AND dim_days.year = 2020
  GROUP BY dim_days.day_of_month;

As I continue to say:

"SQL is easy. Data is hard."

You can (IMO) pretty succinctly enumerate all the days in a month with a recursive CTE. The example in the following link does it for weekdays, but it's the same idea: https://www.sqlservertutorial.net/sql-server-basics/sql-serv...

A more functional mindset can definitely help here. Think of your "sales per day" model in terms of starting with a sequence of days--startingDay up to startingDay + n--as the input to a function that maps to an aggregate of that day's activity.

Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."

Some of the most fun I've ever had coding has been creating "complex" SQL queries. The syntax is something that can only be overcome with memorization, but it becomes second-nature fairly quickly.

Until I started thinking about SQL as manipulating sets it never "clicked". Once it did, though, a whole world of applications filled with mismatched procedural thinking mapped on to SQL was revealed to me, and my own work became much easier.

Despite our industry's habit of treating mathy whiteboard things as indicative of one's "programming talent", real application of type, set, and graph theory are not tested nearly as much as they end up being in practice, and I think that developers' reticence to get deep into SQL is symptomatic of the weaknesses there.

The first time I accidentally typed "select 8 from users..." and, instead of an error, it returned a column called "8" with a bunch of rows with the value "8", my mind was blown.

> I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it's more attuned to a programmatic mindset.

I suspect it was mostly because the tree-like structures people were trying to represent are an absolute pain to work with when you have to shove them into two dimensional rows and columns. I doubt SQL itself had much to do with it as ORMs were already all the rage when Mongo emerged. But ORMs only slightly improve on papering over the data structure impedance mismatch. Most of the pain points present with using SQL directly remain in ORMs when it comes to this problem.

With Mongo you can just throw the tree at it and it will happily store it and nicely give it back again. Which eventually leads to its own set of problems due to how it handles said tree internally, but that's why it has fallen out of favour and SQL databases are the new hotness again.

You'll notice the frontrunners in the NoSQL movement of the time weren't relational databases with a different query language. They all took different approaches to dealing with data itself. Sometimes, humorously, they even maintained SQL as the query language. NoSQL didn't come to mean "No SQL" at all, but rather "Not Relational".

As an aside, while it might go a bit outside the spirit of SQL and introduces its own challenges, I remain somewhat amazed that we haven't seen a standard way emerge to query tree-like structures to better reflect the needs of a fairly common use case. You can kind of get there in unconventional ways, like using json_agg in Postgres, but that all seems pretty hacky.

I couldn't agree more. I think people underestimate how much complexity got introduced thanks to the object-relational mismatch.

It would be really nice if there were databases that attempted to expose a data interface that is more tree-friendly. That's why I'm keeping an eye out on projects like EdgeDB and DGraph, and even Hasura.

I think you're onto something with the observation about what type of developers take to SQL. We have a spectrum in which people think more like how the processor operates to people who think more in classes of problems and their solutions.

We run into problems when someone with a proclivity to one side meets a problem best solved by thinking on the other side of the spectrum. An example is a young dev in my organization who was given requirements for a new app that required a data store. Not being comfortable with SQL or relational databases, he chose a document store. It wasn't a good fit. Very quickly requirements expanded and caused his code to balloon into a mess of nested loops with lots of if-checks. Performance has progressively decayed as well. A simple multi-table join with filtering would have knocked off 2/3 of the code.

TFA isn't even about SQL the language at all though, it's about the scalability and reliability characteristics of databases, especially in a distributed environment.

> I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it's more attuned to a programmatic mindset.

Well, it's more attuned to the dynamically typed mindset, sure. Programmers who understand the value of static type systems should understand the value of relational schemas.

I disagree. There's more to relationships that typing. I'm very pro-dynamic languages and still chafe at static typing but the wonder of the relational model fits nicely with my liking for declarative and functional approaches.

(EDIT - and as another data point I dislike SQL's syntax. The semantics are bearable but the syntax just makes my brain melt)

I used to write SQL at Google/FB, and I did and still do struggle with it.

I think it's because I usually touch SQL like once a month and by the time I need it I already forgot everything :shrug:

Part of the issue is that a complicated database can handle the same SQL query many different ways based on indexes and other configurations.

This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code.

The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really need plugins for compilers because ORM libraries often are harder to learn than the database itself.

> The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really need plugins for compilers because ORM libraries often are harder to learn than the database itself.

Actually in past year I discovered that JetBrains IDEs (in my case PyCharm) have nice feature that seems like not everyone is aware. It is due to their integration with DataGrip. If you configure the IDE to connect to your database, it will start looking for SQL in your strings, it will then do code highlighting, autocomplete table names and fields. If you use refactoring it will understand SQL, if you refactor database it will produce migration statements. It appears to solve all the issues that ORM supposed to solve, and you still have full control since SQL didn't need to be abstracted from you.

Edit: this video shows how it works in action (forwarded to a relevant part): https://youtu.be/_FlpiNno088?t=2868

>This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code.

My problem is that it is like some sort of black magic to me. If I write a complex query I have no idea if what is spit back to me is actually what I want. The only way is seeding lots of records and then manually checking that each filter and calculation is doing what I want.

In code complex things can be broken down into more simple items. Then I can reason about and test those building blocks into something I understand and am confident that is working as intended.

>programming languages are very poor at bridging the difference between the SQL domain and the language domain

Depends a lot on the language. I've lost countless hours to things like JOOQ trying to figure out how to get it to do what I want, or express the query in its quirky not-quite-right DSL, plus dealing with mappings, pojos, auto-generation, and so on.

However, on the other hand, in a dynamic language with just enough support to move the result of your queries into a map of key/value, I feel no friction at all. I'm using little more than a simple jdbc wrapper in Clojure and even after months on the project, I'm still just continuously stoked with seamless the whole thing is.

The thing that really worries me when I'm writing SQL is the possibility that the query planner will get frisky and choose some disastrously slow join order, but only once in a while. I've run into way too many hard-to-reproduce performance issues like this.

Having the database automatically figure out how to run a query is a great feature, but most of the time I'd happily just write explicit nested loops for the sake of predictability.

When I was in undergrad I was part of program that was heavily programming focused, but was actually part of the business school. Several of the classes I took were heavily SQL focused, with at least one class that had every single assignment requiring extensive designing database schemas and writing SQL. Now, years later, I still think that those classes were some of the most valuable to my career as a programmer.

What's interesting to me is that apparently the CS program at my university hardly did anything with SQL, and I notice too that most programmers I meet "in the wild" are lacking in SQL skills, as you mentioned. It's led to some interesting interview situations where I really struggle with any questions about algorithms (my college courses didn't cover algorithms at all) while the interviewer will tell me that I have the best SQL skills of anyone they interviewed.

Speaking with others that went to other universities, I've heard that it's similar elsewhere for the "business/programming" to include SQL classes but eschew algorithms, while CS programs will ignore SQL but focus heavily on algorithms. It seems to me like both programs could benefit from meeting in the middle a bit.

I have a very similar background. My degrees are in Business Computer Systems and we had SQL hammered into us. We learned how to program with Java and .NET, but all from a very high level. It wasn't until a couple years into my career after school that I started learning DS+A fundamentals on my own. It blew my mind. I agree that there should be more of a mid-point available.

This is partly why I love LINQ: it's a more C# flavoured way of expressing queries. And it works on objects as well as databases.

Most ORMs are bad for queries (pull over all the objects and look at their properties!), but LINQ will actually turn your code into SQL under the hood with some remarkable machinery.

>Most ORMs are bad for queries (pull over all the objects and look at their properties!)

i would change that to ORMs CAN be bad for queries. most ORMs are super configurable and can be changed only to pull specific stuff.

i would argue that most ORMs are a plus to productivity because for most of cases, you just need simple querying (select * from, simple updates, simple deletes).

also, if you really need the power of raw sql you an just use that -- and even then, for most advanced ORMs (django's, for example) exposes a LOT of really advanced sql stuff in python, which, for a lot of developers, is a lot more expressive.

Which ORMs are bad for queries? Most popular ORMs these days expose most of sql in a language-specific DSL (and some allow you to splice in bits of raw sql in a semi-structured way as an escape hatch). Sure, you still need to learn to "think in sql", to use these effectively, but the ORM has not been the problem for a long time in most languages.

Not doubting there are bad ORMs but none of the ones I've dived into is quite that bad (SQL Alchemy and Django's ORM).

I sometimes wonder if the debate about ORM is mainly driven by some traumatic horror witnessed at some point in every developer's career. If we judged programming languages by the same metric... Oh... Actually we kind often do that too!

I struggle with lack of experience with SQL by always being told that I should always use an ORM or I would regret it in the future when I would change database technology. I'm in the future now and spend a lot of time debbuging the ORM and the sql statements it produces, when I could split that work in half by not using the orm at all. Would also have a lot more experience with sql so there would probably be less bugs in the first place.

If you're writing a CRUD application, an ORM saves a lot of headaches.

If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong.

As an example, very, very few ORMs can make the distinction between

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
          AND bar.category_id = 5
      LEFT JOIN baz
        ON bar.id = baz.bar_id
    ...

and

    SELECT ...
    FROM foo
      LEFT JOIN bar
        ON foo.id = bar.foo_id
      LEFT JOIN baz
        ON bar.id = baz.bar_id
          AND bar.category_id = 5
    ...

(And if you get into things like analytic queries, just forget about it.)

Swapping out database platforms is pretty deep into YAGNI territory for most. Unless you know you are selling an on-prem software product to some customers who will demand MSSQL and others who will demand Oracle, or whatever, this "swap databases" justification for scrupulously using an ORM is not well grounded in reality.

Switching out the db under a long-in-the-tooth application is going to painful, no matter what. An ORM will not save you.

From my experience it's just unfamiliarity with the SQL language. I've spent thousands of hours writing PHP, Java, Scala, Kotlin, Python and Go and doing fairly complex things with them since I started programming.

SQL maybe a few hundred hours? And off and on again rather than constantly so I don't always remember beyond the basics. The concepts make sense to me but the language always feels foreign.

I'm pretty sure I would be good at SQL if I devoted time to it, because I've worked in other declarative DSLs and generally outperformed other programmers using them, but basically every place I've ever worked at that used SQL extensively had already employed SQL experts that just handled the problems on that level so that it didn't seem worthwhile bothering with it.

In the minds of many developers, SQL is not a "real programming language". That's why they don't see the value of spending the time learning it. Many also believe that SQL is something that they should better avoid, using an ORM, and let it be handled by libraries written by "experts". All of this contributes to developers not having a firm grasp of SQL.

Yet they are unwittingly willing to learn a different DSL, the ORM's DSL.

I don't think it's as elaborate as you're saying -- the clearest road into a productive job for many years has been "join a fast growing tech company who uses <cool_framework> to do the magic, so the tech team is always building more business logic". When you've been working with (eg:) ActiveRecord and Ruby on your database data, direct SQL just doesn't come up until you're trying to wrangle more efficiency in your queries or do more complicated joins.

It certainly makes sense for all developers to learn more SQL! But it just hasn't been part of the daily work requirement for many.

As for the attraction of noSQL databases, I think perhaps it's more of a new-hotness trend rather than a serious technical decision. NoSQL has its place, but like most of this kind of tooling, it's often applied incorrectly because of a lack of thinking through the business logic.

I agree that this is in part why nosql became popular. I'm quite experienced with systems programming but databases were never central to my interests.

So when I needed one for my personal stuff, I went with CouchDB, because it's just a very nice RESTful API around a data structure I understand well.

As a programmer who is admittedly attracted to Mongo, a large part of it is simply its ease of integration. I'd happily spend more time familiarizing myself with SQL if it were less of a pain to integrate into my projects.

The first time ever using Mongo I had a cloud cluster connected and working in about 10 minutes after signing up. Trying to integrate SQLite took me around 1.5 hours before it was functional. To this day I have yet to set up a real cloud SQL database because the one time I tried it with PostreSQL I just couldnt get it to work.

Is initial integration cost really the most important metric you look at when designing a system that will probably run for years and will have to be maintained and scaled?

However most of the time we'll only deal with simple queries such as select where and join. More complex example is just group by and simple aggregation such as max min count.

People struggling with sql because they usually don't know easier way to query that they resulting to use more complex query. For example `select where id in` and `case when` are both powerful query that many don't know or under utilized.

With `select where in` for example, we can do object mapping / join on application level instead db level.

SQL definitely solves a lot of performance issues for typical users who think they can do better

but throwing strings over the wire, trying to get as much as possible without crossing cardinalities and ignoring the n query problem makes SQL at scale disastrous

Devs always get one of these things wrong and not being able to use my programming language in the query makes some things hard to express

Datalog and Datomic are definitely worth learning https://youtu.be/Pz_NvY1kw6I

could you clarify what throwing strings over the wire and crossing cardinalities means in this context?

I find SQL INSERT statement not intuitive. I can understand why SQL requires me to declare the field names and then the values of a new row that I am inserting; but it would've been a huge time saver if SQL had a key-value dictionary-like syntax:

    INSERT INTO "my_table"
       "col1": value1,
       "col2": value2,
       ...

And when you need to insert more than one row, you repeat the column name over and over again for each row - not a good idea either.

MySQL supports a insert/set statement:

    INSERT INTO tbl SET a = 1, b = 2;

It depends pretty much on the quality of one's degree.

Thankfully we had one semester course on databases, all the respective layers, got to implement B+Tree indexes in C, and a GUI application using Oracle's Forms their 90's Visual Basic variant, but using PL/SQL instead.

When I first encountered SQL, my immediate reaction was to start writing my own lightweight ORM (two days later I learned ORMs were a thing that already existed) because I took one look at that syntax and decided it was insane to work with directly.

I definitely don't think in SQL.

That said, I like working w SQL databases -- just please give me a battle tested ORM...

Does it mean that my years of job of analyst writing SQL queries might be a valuable treasure if I were to apply for some dev job?

SQL really is a programming language though. It's just not imperative.

Developers do not want to deal with data in 2D tables all the time. The expressive power SQL offers for manipulating these 2D tables is pretty good, but it can't compare to the ease of working with data not confined to that shape, which is what devs are used to from "real" languages - whether imperative, pure functional, or whatever.

In my mind the best example of this is grouping.

Just like most languages now have "Map" and "Filter" functions in their standard libraries, many now also have some form of "GroupBy" function for working with lists and objects in memory. Invariably the meaning of this is that it takes:

  1. A list of Xs

  2. A key function from an X to a Y, where Ys can be tested for equality

And spits out:

A list of pairs (Y, Xs), each pair containing:

  1. A key value Y

  2. A list of Xs from the input list matching that key

That list-of-(keys and sublists) can then be fed into whatever .Map(), .Filter(), etc. you want to use next. It is perfectly good for simple aggregates like "give me the total salary we're paying each department", but it's also great for stuff like "give me the 3 highest-paid employees within each department". It just makes sense - map over your groups, sorting each sub-list by salary descending and taking the first 3. Nice clean pure functional stuff.

By contrast in SQL, the first problem is super easy - group by department, select sum(salary). The second problem trips a lot of people up because suddenly you aren't really using GROUP BY anymore, even though the thing you're doing is still a "grouping" task. I think the main way to do it is to use ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) to get a ranking number for each employee then select only those with ranking <= 3. Why do I think that's worse?

1. If you were to describe to a person what you're trying to accomplish, the first step would be the same for problems 1 and 2 - get the employees for each department. But the queries in SQL are not alike and do not appear to share the same first step.

2. There needed to be dedicated extra language syntax to do this, instead of having one function (not even special syntax) that gives you the essence of grouping - which other functions then aggregate/massage further into whatever you need. This makes it harder to learn and harder to pick the right tool for a given query job.

3. Locality is almost fundamental to programming. I like writing the inner dept.OrderByDescending(e => e.Salary).Take(3) in the real programming language, because I can think locally to "this department's employees" and even down to "this employee's salary". In SQL, you're a mile above the data tying things together with push pins and string by ID, row number, etc. You rarely get to think locally.

4. In the same vein as above, structured data maps better to how I think. When I write data code, the metaphors in my head involve places and containers - drawers, shelves, moving things around. I am organizing the objects into boxes, going into each box, and picking my top 3. I am not marking each object with a number then going over them all as a big set and tossing out the ones with numbers greater than 3.

5. When I get my result from this query, I will probably want to work with it in the nested-list form, even if only to display it with merged cells for the the department (so I don't repeat every department name 3 times). Funny enough, if not using an ORM, I'll end up using my language's GroupBy to massage the flat rows back into this form.

Despite all this I want even less to do with NoSQL. I don't want to chuck blobs of JSON into the DB and end up with duplicate data, orphan data, ill-defined schemas, data that's only efficiently queryable one way, etc. I really do want to store my data in a relational form, and a normalized one where possible. I am happy with my tables. I just would love to query them in a LINQ-like language that is capable of representing and manipulating nested data structures -- and returning them to my application as such.

Learn about modelling. Database is more than just storing data. Drink less koolaid of NoSQL, any NoSQL. It is trading initial result with future development time. SQL has been battlefield tested. No amount of "convenience" is more convenient than learning the fundamentals.

I'm disappointed that there isn't more criticism of the SQL language. The whole NoSQL buzz got me excited, then turned out to actually mean NoRelational.

It is wild that we are still using a language that looks and feels like COBOL, and any criticism is met with drive-by disapproval (downvotes and no comments) or an argument about why relational databases are important.

SQL is a deeply flawed language by standards that are pretty much universal today - we on HN discuss them daily on posts about new programming languages. For example, one of the top level comments here:

> In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause. So I have to build up my statement in this weird spiral pattern where I change something deeper in before I know what I can SELECT in the first place.

If this were the case for a new language post on HN, the author would get run out of town for this reason alone. And yet, the person who wrote that comment feels the need to hedge by saying they are "an SQL hater in remediation".

Relational data does require a mode of thinking that many programmers are not practiced with. Wouldn't it help to have a language for working with it that isn't outright terrible?

SQL stood the test of time. SQL is widely adopted. Once you get the hang of it, it can be applied on a wide range of RDBMS.

I would argue that countless productivity has been lost to learning yet another query language for yet another NoSQL db. Mongo has its own query language. Cassandra has its own. Neo4j has its own. What not. Guess what, few engineers need these to solve their actual problem. Be it building an application, a library, a SaaS, a tool with wide database support. The yet another query language is an imaginary solution to an imaginary problem (most of the time). Your problem isn't big enough to use whatever NoSQL of choice.

When developers starting out, they get attracted to technologies with the most marketing money. They beat around the bush of learning things that might not matter anymore 5 years down the road.

Is english the best language? No, not even close. Should one learn it? Probably.

Is esperanto a better language? I don't know, maybe. People invent it for a reason (a problem to solve) after all. Should one learn it? Probably no.

> SQL stood the test of time. SQL is widely adopted.

I'm highly dubious of "it is widely used so there" arguments. I think in the case of SQL, the fact that ORMs and SQL generators are ubiquitous are evidence that a huge proportion of engineers would rather not write it, and part of why it is still widely used (most of the time, most people can avoid actually touching it).

> I would argue that countless productivity has been lost to learning yet another query language for yet another NoSQL db. Mongo has its own query language. Cassandra has its own. Neo4j has its own. What not. Guess what, few engineers need these to solve their actual problem.

This is exactly one of the points I addressed in my comment... none of those are even relational databases. And this is where the discussion ends up every time someone says SQL sucks.

> Is english the best language? No, not even close. Should one learn it? Probably.

The vast majority of the time, when you are using English, you have no control over the receiving end. The vast majority of time, when you are using SQL, you / your org grabbed an SQL RDBMS to use.

Furthermore, I don't see why a modern, reasonable query language couldn't transpile to SQL easily when necessary, making all path-dependency / adoption arguments void.

> Wouldn't it help to have a language for working with it that isn't outright terrible?

There is a language like that, and it's downright elegant. It's called LINQ, and it's built into the C# language. It inverts FROM and SELECT, which lets it reliably support autocomplete. LINQ was built using concepts from category theory/relational theory and is widely used in C# to make code tighter and simpler (even though some eschew it because simple for loops are faster). It also transpiles to SQL via Linq-to-SQL. It looks like this:

  var results = from c in collection
                where c.Fld1 < 10
                select new {c.Fld1, c.Fld2};

It's the closest thing we have to SQL that Typescript is to Javascript.

That said, most people who access databases in C# still either write SQL or use an ORM like EF or Dapper.

Databases speak SQL natively for better or for worse. And the SQL language, despite its flaws, is actually mathematically very deep because it's been developed and extended over so many years. See [2] for some serious SQL-fu. To replicate all this functionality into a new language that transpiles into performant SQL is a huge undertaking.

SQL is one of those incumbents that is extremely hard to displace, partly because it's so ubiquitous and that in practice, not everyone finds outright terrible.

True, it's not good, but it's not terrible. I write it day in and day out. The analogy I can think of is the Erlang language -- it doesn't have a pretty syntax but it solves the concurrency problem really well. SQL for better or for worse solves the complex analytic query problem really well.

[1] https://en.wikipedia.org/wiki/Language_Integrated_Query

[2] https://modern-sql.com/slides

In regards to 'select' before 'from', there could be a similar argument to be made for declaring imports at the top of a file. When you write a program, you may not know what libraries you need. Variable declaration at the beginning of a function is also a common pattern.

The SQL language stood the test of time where as COBOL did not. I think it says something about how well it was designed.

I strongly suspect that developers have such a hard time with it is because of ORM. ORM trains you to think of the database as objects. Another poster mentioned that treating databases like sets made SQL click. I think that is a good way of thinking about SQL.

To be fair, SQL does have some archaic things in there that make it annoying to work with. However, once it clicks, it's fits naturally with how a relational db works.

>Variable declaration at the beginning of a function is also a common pattern

What language requires you to declare variables at the beginning? C even stopped doing that. People choose to do that, but ime that's after the writing phase, to make it more readable

>When you write a program, you may not know what libraries you need

Sort of -- the difference is that when I'm writing code in my IDE, I know all database objects available to me. It's in the schema. A library I import once (to the project itself), and the IDE can always assist from then-on. But SQL is designed such that despite the library (schema) being imported, the IDE can't actually assist, unless I write the code out of order (eg start with SELECT * FROM table and then start working)

>However, once it clicks, it's fits naturally with how a relational db works

I think you've misunderstood the complaint -- the relational language is a very strong concept, which has stood the test of time, and is difficult to complain about -- it does its job well, and fits naturally with how a relational db works.

SQL the language however is:

A hodgepodge of random keywords tossed about in a totally inconsistent fashion (eg postgres overlay: OVERLAY('Txxxxas' PLACING 'hom' FROM 2 FOR 4) -- postgres at least generally offers a consistent comma-separated syntax for every functions

has weird and technically unnecessary limitations (like SELECT being evaluated after the WHERE clause, so you can't use the aliases defined in select clause)

putting SELECT before the FROM (disabling IDE auto-complete support)

the stuffing of a 3-value logic system into a 2-value logic interface, so boolean operations break silently and produce nonsense in the face of a database with NULL values, because no mapping of NOT (TRUE, FALSE, NULL) AND NULL makes sense. [0]

It generally composes very poorly, leading to redundant, long and convoluted queries

The MODEL is fantastic -- the language is not.

[0] https://sigmodrecord.org/publications/sigmodRecord/1709/pdfs...

I agree. The SQL language is in desperate need of an "upgrade" to a proper functional language. It's missing so many basic features that it's just painful.

For example, why do I have to repeat expressions in the SELECT, GROUP BY and ORDER BY clauses!?

E.g:

    SELECT 
        LEFT(Foo,4) as Prefix,
        COUNT(1) as N
    FROM MyTable
    GROUP BY LEFT(Foo,4)
    ORDER BY LEFT(Foo,4)

This gets really obnoxious for complex expressions. I mean sure, I can break things out into functions, or use the WITH clause, but both of those often end up being more verbose, not less. This is solved in functional languages such as Haskell with the "where" or "let" clauses (not to be confused with the SQL WHERE filter).

I wish I could do something like:

    SELECT 
        Prefix,
        COUNT(1) as N
    FROM MyTable
    GROUP BY Prefix
    ORDER BY Prefix
    LET Prefix = LEFT(Foo,4)

Similarly, it's crazy to me that most SQL dialects don't allow relational functions to be passed around as proper data types. This kind of thing comes up a lot when implementing row-level security.

I'd like to be able to write "view functions" that can take any relation as an input, as long as it contains some column, and then add on some joins or filters based on that column. Imagine you have a bunch of tables that link to the "Customers" table via a "CustomerID" column or whatever. It would be great to be able to natively write a query that says "AddCustomerName" or "FilterDisabledCustomers". These would take an existing relation (table/view/function) as an input, and return a new relation as the output with an extra column or filtered rows.

This kind of modular style made up of small chunks of code that can be elegantly composed is just not possible or very messy in most SQL platforms. It's typical of most modern functional languages, and I think the "next gen" query language that replaces SQL will look more like Haskell and less like COBOL.

   Select Prefix, count(1) as n
   From (select LEFT(Foo,4) as Prefix, * 
         from mytable)
   GROUP BY Prefix
   ORDER BY Prefix

> Learn about modelling.

So true. I worked at a database-centric place for a while, so I read up on database stuff to fit in.

Learning about first, second, and third normal forms was very enlightening. If you learn these thoroughly enough that they are second nature, it really helps you see modeling mistakes that you might be making. It just becomes a lot easier to think clearly about how to lay out data. Just like there are code smells, if you learn normalization, you will immediately detect data structure smells.

Third normal form is not always the one and only right answer for modeling data, but it's a pretty good starting point from which you can refine and adjust if needed. Just because you understand normalization doesn't mean you have to do it all the time, of course. But if your data isn't normalized, it should be for a specific reason (performance), not by accident or because you don't know how to keep it organized. It is one of those things where knowing the rules gives you the freedom to know when it's right to break them.

It's even useful when thinking about organizing data in RAM. The situation is a little bit different because in RAM you follow pointers instead of doing joins. But there are still cases where it helps. For example, you might have one big struct, and you realize it should be two different structs because you are filling the same (redundant) data into multiple instances. And you know how to fix it.

Another very useful concept from modeling is choosing keys. One of the lessons of databases is that if you don't use synthetic keys, you're going to have a bad time. When you choose real-world data (like first name and last name as a key for a table of people), you're going to have problems like non-uniqueness.

Relational databases aren't the last word on how to organize and store data, but there are just a lot of good ideas that pick up if you learn about them.

Any tips where to look for db modelling learning resources?

I actually don't know. There was this previous discussion https://news.ycombinator.com/item?id=22324691. Might be some gems there.

For me, it is the 3rd normal form and the adage of "Normalize till it hurts, denormalize till it works".

> AUTOINCREMENT’ing can be harmful

I'll add that they should never be trusted to not jump around either! I imagine everyone makes this mistake at least once in their life.

There is a very high chance that the database will skip a few numbers from time to time. You will then have someone from an accounting department asking where Record #XX is.

There's a simple fix - Don't you ever expose primary keys to the users. Ever. Seriously, ever.

A primary key is not an order id, it isnt a person identifier, it isnt a paycheck - its a thing the database should be using behind the scenes.

All of the things I just mentioned change - besides the primary key.

You'll never have this problem if you separate your business logic from your keys.

This is why I'm skeptical of the suggestion to prefer a natural primary key like a username. It works fine... until they day the business asks for changeable usernames because BigClient is now LargeClient and can't stand anything to still have their old brand identity.

Even in the natural key's playground (in my mind) of data warehousing, you still have to manage changing dimensions, and the inevitable reality that your fact table was actually another dimension table all long.

Quite. Not only does it protect you from architecture pain later, but it also allows you to create business-keys that humans have an easier time reading, comparing, and typing.

The implicit requirement of a global always-online single master counter is great until it isn't. Perhaps one day it becomes a performance bottleneck, or you have to support systems that create provisional items offline, or some law or client-contract stipulates everything for them must live in a daughter-system hosted inside/not-inside a particular country...

I'd posit that autoincrements are fine for primary keys, but primary keys aren't fine for auditing.

how would you generate hole-free sequences for use cases like that of parents (invoicing)?

If it was a business requirement that you have perfectly sequential invoice numbers with no gaps, do it at the application level, not at the storage level.

Let the database do what it's great at doing: efficiently store and retrieve data.

A database is also exceptionally good at doing transactional stuff like atomically incrementing something. I'd even say: This is something that belongs in the database and not in some brittle application logic.

Not the OP but perhaps if only accounting cared about it you could assign these IDs separately as some asynchronous single threaded process. This would only work if insert rates were low enough or the assignment of the gapless sequence only needed to be eventually concistent over some window of time (i.e. before next payroll we must have assigned gapless IDs to all the new user accounts)

You could do something like this.

Create a table with two columns:

1. auto-incrementing primary key

2. integer for the actual number you're generating, let's call it 'IDValue'

Seed the new table with a single row with IDValue set to one less than your minimum value (say 0), then use the following process to generate a new number:

1. Insert a new row into the table, with a known invalid value (e.g. -1) for IDValue (note this must not be the same as the IDValue from your initial row)

2. Get the primary key of the newly inserted row

3. Get all the rows from the table (in primary key order) with primary key < the new id. This will consist of one or more rows of prior valid values (or just the initial seed), followed by one or more rows that are either valid or invalid values (other clients may be running through this process concurrently and finishing at different times) - something like this:

Key / IDValue

61 / 1119

62 / 1120

64 / 1121

65 / -1

67 / 1123

70 / -1

71 / -1

(your row is the next one after this)

4. Your new IDValue == the last valid IDValue in that set of rows + the number of rows between that and your new row + 1 - update your row with this new value - in the above example, 1123 + 2 + 1 - i.e. 1126

5. Delete the first unbroken sequence of valid rows except for the latest one, to keep the table small but leave at least one valid IDValue (IDValues 1119 and 1120 in the above example) - just something like DELETE FROM table WHERE Id < 64 (in this case) should be safe.

The database takes care of atomically creating rows which is the tricky bit, and then you can generate your own number at your leisure regardless of gaps in the Key numbering sequence.

Usually, you don't. What kind of messed up requirement is that? Do you want the sky to be green at sunrise tomorrow too?

On the exceptional case where the user is on the clear and there is an unavoidable reason to create this beast, you create a separate numbering application, that uses database transactions that are independent from the ones of the main application and only cares about numbering. It will still have holes, but few enough that you can manually inspect every so often and explain why they happened.

If you look at the documentation for something like "create sequence", databases often mention that the database will reserve blocks of numbers so that they can hand out values from memory (and also without coordinating with peer databases). There's normally a way to turn that caching off.

But you may still need to check what the behavior is during a transaction, as you could have two requests trying to add a bunch of rows at the same time.

Triggers are a great option in many situations.

Auto-increments are fine for primary keys, until they aren't. I think the list of items from the linked article are things that may cause delayed problems.

Since when did Medium.com offer to log you in with your Google account like this?

http://www.jaruzel.com/files/medium-google.png

I've got uBlock and Cookie Cleaner running - but it's clearly doing some sort of cross-site shenanigans to create this pop up.

Time to stop visiting Medium.com I think.

Since a long time ago. This is old news. iFrame sandboxing is now a two-way street, depending on configuration. Not only can an iFrame be prevented from accessing the parent frame, a parent frame can be prevented from accessing the child frame. It is still vulnerable to clickjacking, but to reduce the impact of that, using one-tap sign up only allows the most basic Google permissions. https://news.ycombinator.com/item?id=17044518 https://developers.google.com/identity/one-tap/web

For a long time. I once logged in and was limited to 3 articles per month!

Thankfully, the 3-article limit only applies to logged in users, and once I logged out, it was no longer applied

I've been seeing that for ages, months at least, maybe years.

> Time to stop visiting Medium.com I think.

Click the X and move on. It's helpful to be able to sign up for a site so quickly with such integration.

"The fastest way to access to a row in a database is by its primary key. If you have better ways to identify records, sequential IDs may make the most significant column in tables a meaningless value. Please pick a globally unique natural primary key (e.g. a username) where possible."

Has anyone had a problem due to surrogate keys?

I can agree with everything in the article except this one.

>Has anyone had a problem due to surrogate keys?

There's one problem with surrogate keys: they are not convenient to users (too long and not meaningful).

There are two problems with natural primary keys, and you are guaranteed to hit one of them at some point.

1. It turns out your key isn't actually unique. To resolve the collision you have to replace natural key with a surrogate for one of conflicting entities. Which is not always possible without risk of another collision.

2. It turns out your key isn't persistent. You have to change it for some entities, but you can't because of so many FKs.

The article mentions auto-incrementing keys, not surrogates. Not the same thing at all. Not all incrementing keys are surrogates and not all surrogates are incrementing keys.

Also, your problem number 1 is a problem whether the natural key in question is the "primary" one or not. Certainly if you choose the wrong natural key then you'll have to fix that - that's why you should take care to make a wise choice of natural key regardless of whether you are also using a surrogate.

There's one problem with surrogate keys: they are not convenient to users (too long and not meaningful).

You shouldn't expose those keys to users though.

Natural primary keys are trading one problem that can be solved with an index for another one that is not easily solved.

> You are lucky if 99.999% of the time network is not a problem.

This reminds me of one time I was having networking issues (around a CRUD GUI). Oddly, I identified the issue the (business) day before my users did. I was working on a rather large change (OS updates, 32->64 bit) and noticed that multicast updates broke. The timing was funny. I was working towards a minimal reproducible example already, and I had a small test app that showed that multicast was broke on my PC. So, I ran down and tried the app on their computers, and it worked... between the two of them, but not the company et large.

That's when I vaguely remembered an email for Network Ops about a router change on my user's floor over a weekend. I went by the Ops team (they didn't like email for some reason) and told them what I was seeing.

The short of it was, yeah, the router change screwed my 2 users on the floor (they got put on the printer VLAN by accident, which didn't receive multicast). Separately, the issue I saw was a bug in how we built a 3rd party lib on Windows that provided 64-bit multicast support.

THE thing I wished more developers knew about databases: they are badass when it comes to data manipulation.

Stop sucking all data and manipulate it in your language of choice. Tell you DBA what you want done and let her do it for you.

Really, DB's may look like they are the special needs kid in the chain, but they're magnificent powerhouses when it comes to 90% of what you are trying to do to data.

The most important and overlooked characteristic is that most RDBMS use b-tree for their table-space, meaning all the operations (including search / lookup) are O(log n). For online (OLTP) applications, this means you will have to shard sooner or later (assuming your audience is growing)

I agree with your first statement, but could you explain how b-trees logically lead to sharding (at some growth point)? What storage structure doesn't lead to sharding eventually?

I would add: when using SQL, the default transaction isolation level is probably not what you'd expect.

One thing I've noticed that in the medium term of a software service (2-5 years) is that your software should have the ability to do double-writes to and flip reads between two different datastores.

That will afford migration with as close to transparent migration with as reduced a downtime or no downtime.

That gets tricky if one of the writes fails.

Generally you get two phase commit involved here, or an asynch mirror that you can restore over if there's failure.

Two phase commit makes a promise it can't keep. CAP theorem is not amused.

It's slow, complex, a leaky abstraction, and ahould be avoided for better consistency models.

That sort of flexibility does not sound as though it would scale well as the data get big.

Define "big." Double writing in some form is the only option for live migration and you either bolt it on later or plan for it now.

When the overhead of the double writing becomes substantial enough affect the architecture.

wat. particularly today with logical replication this is pretty much unnecessary.

Best book on the subject: https://www.goodreads.com/book/show/23463279-designing-data-...

One more book on the subject, possibly even closer related to the field of databases (should cover many of the items mentioned in the post): https://www.databass.dev

I just want my databases to be normalised, and my queries optimised.

Stuff like Django CMS is terribly architected, so code is ping-ponging between DB and code.

This would have been a non-issue if all the APIs were built with batching in mind, and the data was normalised in the first place.

Just to be clear you are talking about the CMS and not django itself right? Django itself is fantastic.

An odd thing that happened to me yesterday with the PostgreSQL ODBC driver (psqlODBC) v9.3.400. It wouldn't let me insert a string longer than 255 characters long into a character varying field into a local v9.4 database on windows using a recordset update. I didn't have a problem pasting it in via pgadmin. Altered the field to text and the problem went away. I've a suspicion that there is a limit on text in the tens of thousands of characters length too though despite both those fields being essentially the same thing and limitless.

That sounds like a bug in the driver. I'm pretty sure varchar on postgres is just an alias for text, which has no limit.

edit: varchar(n) will issue an error past n bytes, varchar without n is the same as text. character varying(n) is like varchar(n).

https://www.postgresql.org/docs/9.4/datatype-character.html

I learned SQL working on a "database as a product". The database was filled with medical ontologies. It was a perfect environment for learning. We were always looking for obscure things in that database, and rarely changed data, just selects for days. In the end I once wrote a query spanning about 100 lines that used common table expressions and found it quite maintainable.

Where can an SQL beginner find such a database to experiment with? I was fortunate because my job provided me this database to experiment with, but what about those who are not so fortunate?

CTEs are great. It's such a nicer syntax than subqueries.

There seems to be a wealth of information here. I wonder, though, if the goal would be better served breaking this up into a series. Or perhaps applying the 80/20 rule to the list to come up with truly “a few” items that will have the highest leverage. Otherwise, I think it will provoke a lot of discussion and analysis from people who are experts in databases, but it might remain impenetrable for the core audience: the majority of developers who ignore this stuff (according to the author.)

I could be wrong but it seems to me that she is confusing not storing state with Idempotent calls to the db. Everything you store in the db is some form of state.

Many years ago I've read some chapters from Itzik Ben-Gan's "Inside Microsoft SQL Server 2008 T-SQL Querying." It's an excellent book to anyone who wants to know how things work under the hood. While the title is in fact about SQL Server there so many things explained out there that apply to almost any SQL engine that is a worth the purchase.

As a developer, I am glad there are other people in the world who understand this.

These sort of articles are very effective, if you nail at least one thing in your checklist the reader does not know or is confident about, it often encourages them to continue reading further. I know I did.

In my experience, if you think of a database as a 3rd party API that you do not control sitting somewhere unknown on the internet, your expectations become more reasonable.

“The best programmers started out as system administrators.”

“The best system administrators started out as programmers.”

Optimally, have one foot in each grave. They call that devops, these days.

Nice! Perfect timing. I just started writing a new greenfield project and decided to use Postgres instead of a NoSQL after five years of NoSQL.

You don't have to worry about data if you just have a datum.

I love a good old database

Reply on news.ycombinator.com