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

331–340 of 464 posts

Re: Things I wished more developers knew about databases

#331

Post by kyllo »

fendy3002 wrote: post #295
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…

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?

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

Re: Things I wished more developers knew about databases

#332

Post by Izkata »

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…

> Imagine a programming language designed like SQL.

Okay:

  const { ABC, XYZ } = source_table.get(id = 5);
Oh, wait...

Re: Things I wished more developers knew about databases

#333

Post by randomdata »

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…

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

Re: Things I wished more developers knew about databases

#334

Post by gfxgirl »

rimliu wrote: post #329
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.

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.

Re: Things I wished more developers knew about databases

#335

Post by projektfu »

jiggawatts wrote: post #317
strbean wrote: post #96

Earlier quoted context omitted.

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

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

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

Re: Things I wished more developers knew about databases

#336

Post by fnord77 »

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.

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

Re: Things I wished more developers knew about databases

#337

Post by randomdata »

brightball wrote: post #56
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…

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…

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

Re: Things I wished more developers knew about databases

#338

Post by quickthrower2 »

0xy wrote: post #321
eeZah7Ux wrote: post #215

Earlier quoted context omitted.

> The most senior developer (24yo or so) I see the problem there.

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

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.

Re: Things I wished more developers knew about databases

#339

Post by yen223 »

Drakar1903 wrote: post #160
crazygringo wrote: post #114

Earlier quoted context omitted.

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…

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.

The answer to the performance-related ones is simple. It always depends on the your query, your indexes, and your data. The same query can produce wildly different query plans if you have different data (or even if you have the same data, but the engine decided to sample different rows!)

Best thing you can do is to learn how to read EXPLAIN ANALYZE results.

Post reply on HN