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

171–180 of 464 posts

Re: Things I wished more developers knew about databases

#171

Post by jacques_chester »

pbowyer wrote: post #68
JamesSwift wrote: post #59

Earlier quoted context omitted.

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…

Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that. For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.

I found Kimball's book to be interesting and helpful.

I think the Kimball approach gets bogged down in temporal evolution (Slowly Changing Dimension type 1? 2? 3? 4? 5?), but that's more about the underlying absence of meaningful bitemporalism than the dimensional schema approach per se.

Re: Things I wished more developers knew about databases

#172

Post by acdha »

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…

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

Re: Things I wished more developers knew about databases

#173

Post by outworlder »

taffer wrote: post #89
crazygringo wrote: post #52

Earlier quoted context omitted.

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…

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.

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.

Re: Things I wished more developers knew about databases

#174

Post by cookiecaper »

arvinsim wrote: post #10
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…

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.

Re: Things I wished more developers knew about databases

#175

Post by Matrixik »

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…

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

Re: Things I wished more developers knew about databases

#176

Post by Dowwie »

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

Re: Things I wished more developers knew about databases

#177

Post by chaps »

user5994461 wrote: post #165
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…

was the go library fixed?

No clue!

Re: Things I wished more developers knew about databases

#178

Post by taffer »

UncleMeat wrote: post #162
taffer wrote: post #136

Earlier quoted context omitted.

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

They do. And language designers seek to smooth the edges and develop ways to encourage devs to write clear and intuitive code. When a language says "well, these are hairy questions that you should just figure out" we tend to criticize those languages unless they have clear reasons for that decision. It should be obvious that something is thread-compatible. "Well, if you model it in pi-calculus it is easy" is a crappy…

In which language is it obvious not to put money into a float? In which language is it obvious if I want a synchronizedSortedMap or an arrayList?

There are simply things you have to learn to use a technology. If you want to write Java, you need to know what a variable is, what a for loop is and what Inheritance/Interfaces are. Likewise, in the case of SQL, it means you need to know concepts like normalization, ACID and joins. Just poking around until the code works won't do it.

Re: Things I wished more developers knew about databases

#179

Post by AndrewKemendo »

vsareto wrote: post #12
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…

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…

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.

Post reply on HN