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

431–440 of 464 posts

Re: Things I wished more developers knew about databases

#431

Post by mike22 »

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…

I’ve been casually trying to knock into the black box of MySQL’s internals with no success yet. I have queries that do GROUP BYs across multiple unindexed columns in frequently large tables, so it ends up with temp tables on disk and filesort. Can someone point to the source code for processing this kind of GROUP BY situation?

Re: Things I wished more developers knew about databases

#432

Post by inopinatus »

kerkeslager wrote: post #428
inopinatus wrote: post #416

Earlier quoted context omitted.

No, but it’s a trap. If someone answers an obviously rhetorical question, they’re inadvertently demonstrating a predilection for engaging the construction, not the substance, of a statement, and almost certainly missing the ironic subtext. I’d be happy to repeat my assertion though. People don’t inwardly digest the mistakes of others, which is why educators on safety-critical topics such as those mentioned must go to…

> No, but it’s a trap. If someone answers an obviously rhetorical question, they’re inadvertently demonstrating a predilection for engaging the construction, not the substance, of a statement, and almost certainly missing the ironic subtext. That's a pretty self-aggrandizing analysis of the situation. From my perspective, I got you to make the statement, "People don’t inwardly digest the mistakes of others", which so…

> ? "I got you to make the statement" ?

That's quite the signal of bad faith debate. I don't think it's my own aggrandizement in play here. Quite the reverse. c.f. remarks passim re. hubris. So there the conversation must end.

Re: Things I wished more developers knew about databases

#433

Post by raarts »

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.

You're bringing up the exceptions that prove the rule as if they disprove it.

Re: Things I wished more developers knew about databases

#434

Post by amznthrowaway5 »

goto11 wrote: post #376
amznthrowaway5 wrote: post #330

Earlier quoted context omitted.

That's misleading because it's too simplistic. A smart person could spend 10 years gaining real, legitimate experience and they could still be eclipsed by someone with little experience but much more talent.

Talent is no substitute for experience.

I'd say it's mostly the other way around, experience can't substitute for high natural cognitive ability. Of course a person still needs experience, but people with high cognitive ability don't need nearly as much, and people with less cognitive ability will hit thresholds of capability much more quickly. A lot of people live in a bubble of people with similar ability so they don't grasp the true importance of ability. And fakers who learn nothing year over year but have "years of experience", extremely common in this industry, don't like being told there are 14 year olds way more capable than them at their own jobs.

Re: Things I wished more developers knew about databases

#436

Post by kerkeslager »

inopinatus wrote: post #432
kerkeslager wrote: post #428

Earlier quoted context omitted.

> No, but it’s a trap. If someone answers an obviously rhetorical question, they’re inadvertently demonstrating a predilection for engaging the construction, not the substance, of a statement, and almost certainly missing the ironic subtext. That's a pretty self-aggrandizing analysis of the situation. From my perspective, I got you to make the statement, "People don’t inwardly digest the mistakes of others", which so…

> ? "I got you to make the statement" ? That's quite the signal of bad faith debate. I don't think it's my own aggrandizement in play here. Quite the reverse. c.f. remarks passim re. hubris. So there the conversation must end.

The guy who thinks he "trapped" me with a rhetorical question accuses me of arguing in bad faith? Okay...

All I did was get you to say clearly what you believe. If what you believe is so embarrassing that it's a sign of bad faith to get you to say it in clearly, maybe believe better things?

Re: Things I wished more developers knew about databases

#437

Post by skdv »

jwdunne wrote: post #390
Izkata wrote: post #326

Earlier quoted context omitted.

There's another step that could be added there, too: After the ALTER VIEW, V could be slowly incrementally updated over however long you need to back-populate AmtGBP, and the views will continue to just work the whole time. Once done, V_A can be simplified to remove the ISNULL and Amt, then Amt dropped from V. That way you don't get build-up of cruft over the years, and the experience isn't interrupted for the migrat…

Is there anything you recommend for handling SQL definitions in version control, development and production envs? For production, I created a command on the app that loads the stored procedures into the DB idempotently on each deployment/configuration. This won’t work if the app server scales but allowed us to store stored procs in VC. For development, we ran the command on each page load as a sort of hacky “live rel…

Web frameworks like Rails/Django use the idea of migrations to make changes to the database. The idea is that you have a set of migration scripts like: migrations/1765_create_table_users.sql migrations/2891_store_procedure_x.sql migrations/5892_change_store_procedure_x.sql

(.sql/.rb/.py, it doesn't matter).

And you have a "migrations" table in your database that contains the numbers of the migrations that have been run:

  select * from migrations;
      version
  ----------------
   1765
   2891
Every time you deploy to production automatically check which scripts in your db/migrations folder don't exist in the migrations table and run them. (In the current example, you'd run the 5892_change_store_procedure_x.sql that hasn't been run yet).

How to do with functions/procedures?

You commit the function definitions in a functions folder to your version system like:

  db/functions/report_x.sql
  CREATE or REPLACE function report_x() returns ...
When you change this file, nothing happens, you need to create a migration to re-run this code once. In rails migrations would be:

  class UpdateReportXFun

Re: Things I wished more developers knew about databases

#438

Post by jwdunne »

skdv wrote: post #437
jwdunne wrote: post #390

Earlier quoted context omitted.

Is there anything you recommend for handling SQL definitions in version control, development and production envs? For production, I created a command on the app that loads the stored procedures into the DB idempotently on each deployment/configuration. This won’t work if the app server scales but allowed us to store stored procs in VC. For development, we ran the command on each page load as a sort of hacky “live rel…

Web frameworks like Rails/Django use the idea of migrations to make changes to the database. The idea is that you have a set of migration scripts like: migrations/1765_create_table_users.sql migrations/2891_store_procedure_x.sql migrations/5892_change_store_procedure_x.sql (.sql/.rb/.py, it doesn't matter). And you have a "migrations" table in your database that contains the numbers of the migrations that have been r…

Yeah, I’m aware of that, thank you. I was wondering if there was a way with a faster feedback loop and allowed for bug fixes without creating a new migration.

Re: Things I wished more developers knew about databases

#439

Post by [deleted] »

bdamm wrote: post #34
mgkimsal wrote: post #25

Earlier quoted context omitted.

> 2 years working on a non-trivial backend should expose one to these problems. You can be exposed to them, but without understanding them, and experiencing both good, bad, and really bad 'solutions' to them, and understand the impact (on the business, on the code, on security, on maintainability, etc)... you just can't really get all that in 2 years. I know plenty of people who've been 'exposed' to certain type of t…

I wish I could slap anyone who gives a hoot about tabs vs spaces. Fortunately modern languages like go are removing the version control problem that not caring about style and using auto-formatting IDEs produces.

[deleted]

Re: Things I wished more developers knew about databases

#440

Post by justinclift »

harikb wrote: post #200
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 sql/database uses its own connection pool. But still that shouldn't create any problems. I have seen the reverse where apps that assumed temporary tables stick around from statement to statement without an explict `txn` (which regular postgres connections don't need) clearly failed. But I have not seen the issue you talk about. My wild guess would be that the Go code never closed the result/rows which caused eithe…

They may have been using one of the PG specific Go libraries instead too. eg https://github.com/jackc/pgx
Post reply on HN