Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A terrible schema from a clueless programmer

rachelbythebay.com

Post reply on HN
Print view View original

291–300 of 493 posts

Re: A terrible schema from a clueless programmer

#291

Post by necovek »

hinkley wrote: post #230
wdr1 wrote: post #109

Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games. This was the first time regular people could go buy tickets for events & they had been lining up overnight at Bank of China locations through the country. We were down for over a day before we called it off. Apparently this led to minor upheaval at several locations in Beijing & riot police wer…

The one that always got us is much more mundane. Deleting a row requiring an index in every table with a FK to avoid tables scans. Near as I can tell, we assume there is some bit of magic built into the foreign key concept that handles this for us, but that is not the case.

Foreign keys are naturally referencing primary keys, which have their own (unique) indexes by default. And there's some extra magic with CASCADE.

But it seems like I am missing your point: care to expand on it so I can learn about the gotcha as well? What are you attempting to do, and what's making the performance slow?

Re: A terrible schema from a clueless programmer

#292

Post by lern_too_spel »

nlitened wrote: post #279

> The rub is that instead of just being slow, it also cost a fair amount of money because this crazy vendor system charged by the row or somesuch. So, by scanning the whole table, they touched all of those rows, and oh hey, massive amounts of money just set ablaze! Why _the hell_ is nobody mentioning that using a database that charges per row touched is absolute insanity? When has it become so normal that nobody ment…

If the amount charged isn't proportional to the work done, the cloud provider would quickly go out of business.

Re: A terrible schema from a clueless programmer

#294

Post by colanderman »

alksjoflkajsom wrote: post #191

People in the comments are getting (rightfully) outraged about the poor understanding of indexing, but I'm a little surprised that everyone here doesn't seem to understand normalization either. The original schema is perfectly normalized and is already in 3NF: none of the columns shown has a dependence on any of the other columns outside of the primary key (in other words, if you knew eg the values of the ip, helo, a…

Came here to say this. Whatever may have been true of InnoDB 20 years ago, don't follow this article's advice for any modern relational database.

To add: Not sure about MySQL, but `varchar`/`text` in PostgreSQL for short strings like those in the article is very efficient. It basically just takes up space equaling the length of the string on disk, plus one byte [1].

[1] https://www.postgresql.org/docs/current/datatype-character.h...

Re: A terrible schema from a clueless programmer

#295

Post by thehappypm »

tomsmeding wrote: post #3

Why exactly would it be so bad to just put a suitable index on the table containing strings? The time complexity of the resulting search would be the same, so I assume there will be some constant factor slowdowns. Is it that indices over string fields are stored inefficiently on disk? (If so, can that not be fixed in the db engine directly?) Or is this fine today but wasn't fine 15 years ago?

At the worst you could concatenate the strings and have a single primary key.. or hash the string concatenation.

Re: A terrible schema from a clueless programmer

#296

Post by mixmastamyk »

hn_throwaway_99 wrote: post #235
darkwater wrote: post #161

Earlier quoted context omitted.

She explained the problem, the first not working solution and the second working solution as they really happened in 2002 as an example . The real point is the last part of the post. And it was not "how to properly implement an sql based filter for open proxies in your MTA".

I get all that. But 2020 version of this person still does not understand the problem , and she is arguing that she does while oddly self-deprecating the inexperienced version of herself, who arguably had a better solution to begin with.

Yes, I think an alternate design was found that didn't hit as many MySQL limitations as the previous one. This improved performance and was a win. But the post-mortem diagnosis was lacking.

Re: A terrible schema from a clueless programmer

#297

Post by mixmastamyk »

08-15 wrote: post #234
darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

In this blog post, senior engineer Rachel talks nonsense about normalization and promotes a bafflingly complicated solution as superior to a simple one, without identifying the actual fix, which appears to have happened accidentally. In other words, with enough empathy and patience, a clueless rookie can grow into a clueless senior engineer! Rachel usually makes more sense than that. That's why people are nitpicking…

Hah, pretty accurate and kinda funny, but could be nicer. I still make mistakes.

Re: A terrible schema from a clueless programmer

#298

Post by [deleted] »

webstrand wrote: post #5

While the normalized version is more compact and doesn't store redundant data, it _also_ needs an index on the four columns or it'll have to check every row in the table. A similar index added to the original denormalized table would have given comparable query performance. The table schema isn't terrible, it's just not great. A good first-pass to be optimized when it's discovered to be overly large.

[deleted]

Re: A terrible schema from a clueless programmer

#299

Post by gavinray »

tkot wrote: post #281
gavinray wrote: post #88

Earlier quoted context omitted.

This has been the advice given to me by Postgres experts in a similar scenario: "If you want to efficiently fuzzy-search through a combination of firstname + lastname + (etc), it's faster to make a generated column which concatenates them and index the generated column and do a text search on that." (Doesn't have to be fuzzy-searching, but just a search in general, as there's a single column to scan per row rather th…

Would it not prevent some optimizations based on statistics regarding the data distribution, like using the most selective attribute to narrow down the rows that need to be scanned? I'm assuming there are 2 indexes, 1 for each column that gets combined. Let's say you know the lastname (Smith) but only the first letter of the firstname (A) - in the proposed scenario only the first letter of the firstname helps you nar…

I won't pretend to be an expert in this realm, but see here:

https://www.postgresql.org/docs/current/textsearch-tables.ht...

Specifically, the part starting at the below paragraph, the explanation for which continues to the bottom of the page:

  "Another approach is to create a separate tsvector column to hold the output of to_tsvector. To keep this column automatically up to date with its source data, use a stored generated column. This example is a concatenation of title and body, using coalesce to ensure that one field will still be indexed when the other is NULL:"
I have been told by PG wizards this same generated, concatenated single-column approach with an index on each individual column, plus the concatenated column, is the most effective way for things like ILIKE search as well.

But I couldn't explain to you why

Re: A terrible schema from a clueless programmer

#300

Post by duxup »

mwcampbell wrote: post #270
twic wrote: post #217

Earlier quoted context omitted.

I don't think it's purely nerd-sniping. If your story is "at first you are bad, but then you get good", but your example is of a case where you did something fine but then replaced it with something worse, that rather undermines the story.

If even someone with Rachel's level of experience still doesn't know all the minutiae of database optimization, I think that just amplifies her point about the importance of mentoring novices.

I recall reading a rant on another site about someone so upset they had to deal with clueless noobs at work.

They then went on to list the errors that this brand new entry level employee had made when writing ... an authentication system ...

I was more than a little shocked when I realized they were serious and hadn't realized the issue was sending the new entry level guy to do that job alone.

Post reply on HN