Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Things I wished more developers knew about databases

medium.com

Post reply on HN
Print view View original

301–310 of 464 posts

Re: Things I wished more developers knew about databases

#301

Post by nitwit005 »

LoSboccacc wrote: post #37
stronglikedan wrote: post #19

Earlier quoted context omitted.

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

Re: Things I wished more developers knew about databases

#302

Post by jiggawatts »

falcolas wrote: post #7

(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. Obser…

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!

Re: Things I wished more developers knew about databases

#303

Post by inopinatus »

kerng wrote: post #276
chaps wrote: post #149

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 appl…

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.

Re: Things I wished more developers knew about databases

#304

Post by nuclearnice1 »

kerng wrote: post #276
chaps wrote: post #149

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 appl…

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.

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

Re: Things I wished more developers knew about databases

#305

Post by atombender »

chaps wrote: post #149

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 appl…

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.

Re: Things I wished more developers knew about databases

#306

Post by karmakaze »

crazygringo wrote: post #52
wenc wrote: post #16

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 b…

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 programm…

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

Re: Things I wished more developers knew about databases

#307

Post by ivanhoe »

falcolas wrote: post #7

(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. Obser…

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.

Re: Things I wished more developers knew about databases

#308

Post by karmakaze »

Viliam1234 wrote: post #211
corey_moncure wrote: post #42

Earlier quoted context omitted.

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 dispos…

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 d…

I don't know why people think this. After much thought, it's because they read chronologically which isn't how SQL works. It doesn't 'execute' in the written sequence.

The entire statement is a logical one like a mathematical equation. You wouldn't think that the meaning of (a + b) * (c + d) implies that (a + b) has to be done first. Maybe in some computer languages it may like early C compilers.

Given that: "select ABC.XYZ" is perfectly well formed, it's like a forward reference declaration where ABC is the alias for a table-like thing and XYZ is the alias for the column-like thing. I mostly read my SQL inside-out, starting with uncorrelated subqueries.

Another pet peeve: using the keyword INNER or OUTER is just noise.

Re: Things I wished more developers knew about databases

#309

Post by [deleted] »

chaps wrote: post #149

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 appl…

[deleted]
Post reply on HN