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

401–410 of 464 posts

Re: Things I wished more developers knew about databases

#401

Post by vb6sp6 »

devonkim wrote: post #391
collyw wrote: post #361

Earlier quoted context omitted.

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.

Re: Things I wished more developers knew about databases

#402

Post by throwaway_pdp09 »

hobs wrote: post #400
throwaway_pdp09 wrote: post #190

Earlier quoted context omitted.

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

I would not throw someone at CTEs who is using SQL Server - just use temp tables for each component you would be CTE-ing, CTE's dont get any benefit from re-use except from a code perspective, whereas composing your sets into temp tables will often get you exactly what you want, individual sets that you can re-use throughout your code.

Very good point indeed. I'll add to that, that you can trivially examine temporary tables wheres CTEs don't have that transparency. Thanks!

Edit: I'm going to clarify this. It is a mistake to throw CTEs at a beginner but to be clear, CTEs have very important advantages over the using temp tables to hold intermediate results. It comes down to efficiency.

MSSQL only optimises within a single statement; it does not optimise across statements, so if you have a several queries comprising a single CTE, the optimiser has plenty to get its teeth into and may produce a much more efficient query plan.

Also a query plan of a complex CTE can (depending on what you're doing) end up being a straightforward pipeline where one result feeds into another into another and finally gets spat out at the end. That can be very efficient. If you use temp tables you spool intermediate results (which may be large), re-read, spool into another temp table etc. If you're working with a large data set that can use up a lot of memory, and if it's large enough that it has to spill to disk... oh dear.

So for beginners, yes, as you get more expertise, CTEs are the way to go (depending, of course, on various factors)

(Conversely, temp tables do have a definite cardinality, whereas queries in a CTE are estimates and can be badly out leading to very poor query plans. So temp tables can work to your advantage here).

Re: Things I wished more developers knew about databases

#403

Post by kerkeslager »

inopinatus wrote: post #303
kerng wrote: post #276

Earlier quoted context omitted.

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/

Re: Things I wished more developers knew about databases

#404

Post by CodesInChaos »

fendy3002 wrote: post #378
zbentley wrote: post #320

Earlier quoted context omitted.

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.

Re: Things I wished more developers knew about databases

#405

Post by chaostheory »

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…

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

Re: Things I wished more developers knew about databases

#406

Post by [deleted] »

kerkeslager wrote: post #403
inopinatus wrote: post #303

Earlier quoted context omitted.

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

[deleted]

Re: Things I wished more developers knew about databases

#407

Post by MaxBarraclough »

wenc wrote: post #198
TremendousJudge wrote: post #82

Earlier quoted context omitted.

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

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

What do you make of tools like LoopBack which automatically map REST to SQL (without you having to write code for each mapping)? https://loopback.io/doc/en/lb4/Database-connectors.html

Re: Things I wished more developers knew about databases

#408

Post by inopinatus »

kerkeslager wrote: post #403
inopinatus wrote: post #303

Earlier quoted context omitted.

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

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.

Re: Things I wished more developers knew about databases

#409

Post by giantrobot »

vb6sp6 wrote: post #401
devonkim wrote: post #391

Earlier quoted context omitted.

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.

Tuning queries still makes plenty of sense. Slow/expensive queries need extra infrastructure like a memory cache server or more app servers to paper over the inefficiency.

That's a bunch of added effort that might as well be spent on understanding your database. Even something simple like using materialized views can significantly increase performance of expensive queries.

Re: Things I wished more developers knew about databases

#410

Post by kerkeslager »

inopinatus wrote: post #408
kerkeslager wrote: post #403

Earlier quoted context omitted.

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

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

> The question was and is rhetorical.

The fact that you ask a question not expecting a direct answer is not proof that a direct answer does not exist.

> People don’t inwardly digest the mistakes of others.

In my spare time, I'm a rock climber, and mistakes in my rope systems can kill me. The same is true in mycology, firearms, airplane piloting, civil engineering. If you really feel that you can only learn from your own mistakes then I guess it's lucky for you that you've chosen to learn in a field where the stakes aren't life and death.

> The readers of this forum often do know their type theory.

That's true. The same is not true for the many Gophers who repeatedly claim that Go has a strong type system, which is who that comment was directed at.

Post reply on HN