Things I wished more developers knew about databases • Hacker News Board

Re: Things I wished more developers knew about databases

#141

Post by ratww » Wed, Apr 22, 2020, 5:14 PM UTC

frosted-flakes wrote: post #92

treis wrote: post #58

Earlier quoted context omitted.

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

Yes, SQL's biggest fault is that it's not very composable. Complex queries end up being long and repetitive, and the order of the parts of a query is totally unintuitive (it should go something like: FROM, GROUP BY, SELECT, ORDER BY rather than SELECT, FROM, GROUP BY, ORDER BY, which makes autocompletion hard).

CTEs can help a bit with composition (and readability)

Re: Things I wished more developers knew about databases

#142

Post by jtdev » Wed, Apr 22, 2020, 5:17 PM UTC

TremendousJudge wrote: post #82

brightball wrote: post #56

Earlier quoted context omitted.

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…

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

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.

Re: Things I wished more developers knew about databases

#143

Post by MathCodeLove » Wed, Apr 22, 2020, 5:19 PM UTC

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…

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.

Re: Things I wished more developers knew about databases

#144

Post by izacus » Wed, Apr 22, 2020, 5:21 PM UTC

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…

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.

Re: Things I wished more developers knew about databases

#145

Post by setr » Wed, Apr 22, 2020, 5:22 PM UTC

takeda wrote: post #117

frosted-flakes wrote: post #92

Earlier quoted context omitted.

Yes, SQL's biggest fault is that it's not very composable. Complex queries end up being long and repetitive, and the order of the parts of a query is totally unintuitive (it should go something like: FROM, GROUP BY, SELECT, ORDER BY rather than SELECT, FROM, GROUP BY, ORDER BY, which makes autocompletion hard).

QUEL[1] was like that, but thanks to Oracle SQL won. [1] https://en.wikipedia.org/wiki/QUEL_query_languages

One thing I never understood is that the SQL language, and its alternatives, share the same theoretical IR -- the relational algebra -- it shouldn't be that difficult to implement for postgres/mysql alternative relational languages like QUEL or Datalog. Or even just a simplified SQL with a sane, consistent syntax.

I know PG has a bunch of procedural-language alternatives, but afaik, no relational-language alternatives.

At the same time, I'd also expect it to not be that difficult to transpile from say mysql to postgres, yet there's very little in that space, at least in open source (there is however many ORMS that map to either mysql or postgres..)

Both of which, amongst the procedural-languages, you'd find a hundred transpilers (JS->C) and VM-languages (Clojure,Scala,etc on jvm) implemented even by bored/curious students. Which makes my suspicion that there's no technical blocker, just a cultural one.

That is, QUEL losing to SQL, such that you can't find any implementation, is an absurd concept -- it should be available on postgres (perhaps requiring some special starting keyword) -- but somehow it is not.

Re: Things I wished more developers knew about databases

#146

Post by izacus » Wed, Apr 22, 2020, 5:23 PM UTC

jayd16 wrote: post #132

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…

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.

In reality it rarely works this way - there's plenty of systems which are falling apart due to "death of thousand cuts" type issues. You run a profiler and most of the queries are slow and there's no one obvious part to optimize - because developers over the years ignored basic optimizations and there are inefficiencies everywhere.

E.g., for a quick practice run, try optimizing Wordpress without making it a static page via caching - how many queries will you have to optimize and how much of a codebase rewrite will it be to make it significantly more performant?

Re: Things I wished more developers knew about databases

#147

Post by a_c » Wed, Apr 22, 2020, 5:24 PM UTC

strbean wrote: post #96

a_c wrote: post #20

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

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.

Re: Things I wished more developers knew about databases

#148

Post by izacus » Wed, Apr 22, 2020, 5:24 PM UTC

MathCodeLove wrote: post #143

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…

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

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?

Re: Things I wished more developers knew about databases

#149

Post by chaps » Wed, Apr 22, 2020, 5:24 PM UTC

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.

Re: Things I wished more developers knew about databases

#150

Post by anotheracct_ » Wed, Apr 22, 2020, 5:25 PM UTC

dominotw wrote: post #65

brightball wrote: post #56

Earlier quoted context omitted.

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. 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's unfortunate that your education failed you so much.