All "movements" have some merit, or they wouldn't exist in the first place. Nobody starts a "movement" just because. It exists because it solves something. "NoSQL" was a solution to something. Today you can benefit from best of all worlds, and still NoSQL has a place and a use case, just like anything else that exists.
>and still NoSQL has a place and a use case, just like anything else that exists.
What actually makes you believe that it's the NoSQL that has "some use cases" and relational databases are "default ones" instead of NoSQL/no-relational by default?
I don't think there is (or should be) a "default". Default selections suggest they are picked "just because". There is a problem/use-case and a tool to solve it. Sometimes it's relational, sometimes it's nosql, sometimes it's both or even none...
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?
Yeah, while this is not optimal (ip should be converted into integer, time should be ts), the table would be small (as old entries could be safely deleted). The only real issue is the lack of indices.
Yes. A proper mail implementation won't randomize it, so it should be consistent. Depending on the design of the validation stages it might only need a case-normalized lookup to the string index table and that id as part of the final multi-column unique index, and in that last case only to convey to the database the expectations to optimize around (and require the data adhere to).
Thanks! I was really surprised to find it myself. I used HN in some sample code for analyzing how SSL tickets are reused and was surprised to see an IPv4 address come back.
Early InnoDB* had pretty strict limits on varchar indexes and was not the most efficient. I don't remember the details but it's entirely possible the single-table format Rachel described ran head on into those limitations.
Also remember indexes take space, and if you index all your text columns you'll balloon your DB size; and this was 2002, when that mattered a lot more even for text. Indexes also add write and compute burden for inserts/updates as now the DB engine has to compute and insert new index entries in addition to the row itself.
Finally, normalizing your data while using the file-per-table setting (also not the default back then) can additionally provide better write & mixed throughout due to the writes being spread across multiple file descriptors and not fighting over as many shared locks. (The locking semantics for InnoDB have also improved massively in the last 19 years.)
* Assuming the author used InnoDB and not MyISAM. The latter is a garbage engine that doesn't even provide basic crash/reboot durability, and was the default for years; using MyISAM was considered the #1 newbie MySQL mistake back then, and it happened all the time.
Indexes take space, except for the clustered index.
An important distinction.
If the point of this table is always selecting based on IP, m_from, m_to, then clustering on those columns in that order would make sense.
Of course, if it's expected that a lot of other query patterns will exist then it might make sense to only cluster on one of those columns and build indexes on the others.
To me it seems that spreading it over four tables would lead to a lot more potential read locks while the big combined table is waiting for a join on each of the others, and some process is trying to insert on the others and link them to the main. This is assuming they were using foreign keys and the main table 4-column index was unique.
InnoDB uses row-level locking, and foreign keys are (usually) a great feature to ensure data integrity. But using multiple foreign keys from tables `a`,`b` as a composite index for table `x` can cause deadlock if both are being updated in rapid succession, because an update on `a` gets a lock on `x`, which needs a read lock on `b` which is waiting for the lock from `a` to be released. I try to never structure multiple foreign keys as a composite index.
indexes take space, and if you index all your text columns you'll balloon your DB … also add write and compute burden for inserts/updates as now the DB engine has to compute and insert new index entries in addition to the row itself.
Indexes didn’t go away with these extra tables, they live in the ‘id’ field of each one. They also probably had UNIQUE constraint on the ‘value’ field, spending time on what you describe in the second half of my citation.
I mean, that should have saved some space for non-unique strings, but all other machinery is still there. And there are 4 extra unique constraints (also an index) in addition to 4 primary keys. Unless these strings are long, the space savings may turn out to be pretty marginal.
Right, indexes don't "go away" as you normalize, but the amount of data indexed drastically shifts and four unique indexes are pretty much guaranteed to be smaller in size than one massive combinatorial index of (all, four, possible, values). Not just in the case where any duplicates exist, but just alone in terms of the database's overhead in things like field delimiters and page metadata. While the specifics will vary a lot with specific DB implementations, generally with varchars involved you can usually assume a worst-case in terms of field delimiting (and from there sometimes a worst-case in average page size and how that impacts B-Tree balancing).
In general, Indexes alone can't save you from a normalization problems. From certain points of view an Index is itself another form of denormalization and while it is very handy form of denormalization, heavily relying on big composite indexes (and the "natural keys" they represent) makes normalization worse and you have to keep such trade-offs in mind just as you would any other normalization planning.
It is theoretically possible a database could do something much smarter and semi-normalized with for instance Trie-based "search" indexes, but specifically in 2002 so far as I'm aware of most of the databases at the time such indexes were never the default and didn't have great multi-column support and would have been expensive to compute if you did turn them own. Even such "smart" Indexes would likely still have suggested you normalize first.
> using MyISAM was considered the #1 newbie MySQL mistake back then, and it happened all the time.
The author mentions that these events took place in 2002. At that time the site was likely still running MySQL 3 and InnoDB was very new and considered experimental. Sure MySQL 4.0 had shipped in 2002 and InnoDB was a first class citizen, but back then upgrades from one version of MySQL to another weren't trivial tasks. You also tended to wait until the .1 release before making that leap.
So in fairness for the folks who originally set up that database MyISAM was likely their only realistic option.
I looked after and managed a fleet of MySQL servers from back then and for many years afterwards and even then it wasn't until MySQL 5 that we fully put our trust in InnoDB.
But IP addresses are actually _numbers_, and you can also save them as bytes 32 bits for ip4, 128 bits for ip6 addresses. (Postgresql has a native datatype 'inet' which supports ip6 since 7.4, if you're using postgres and save an IP address in a character string, you're doing it wrong) If you would save the IP as number and put an index on it, all your queries would be blazing fast!
Normalization is important for deduplication, not only to index and compare a few short numbers instead of a few long string: those host names and email addresses are long and often repeated.
But if it’s guaranteed to be 1:1, why? The two implementations (normalized and denormalized) in that case should be completely isomorphic, quirks of the DB engine aside.
I think the article describes the solution that was used and not necessarily the best solution. If they made the “obvious” mistake in the first case they mightn’t arrive at the best solution in the second. Adding the single index and using the four tables have the same worst case complexity for reads, O(log n), with different constants and massively beat scanning at O(n).
It may be that the second solution wasn’t the best, or that it was better for write performance or memory usage—this was 2002 after all. It may also be the case that the cardinal it’s of some columns was low in such a way that the four table solution was better, but maybe getting the columns in the right order for a multi column index could do that too.
Either way, I don’t think it really matters that much and doesn’t affect the point of the article.
Let's say the article is a critique to some system by someone that is not much better at databases. As many people also pointed out, a bad solution was replaced with a bad solution that works better for someone.
I had the same initial reaction in reading the post. My assumption was that indexing would have sufficiently sped up the query speed problem.
However, normalizing the fields that contain repetitive data into separate tables could create significant space savings since the full text for each column would not need to stored for each row. Instead of 20-30 bytes per email address, a 4 byte (assuming 32-bit era) OID is stored in its place.
It's pretty easy to imagine how quickly the savings would add up, and that it would be very helpful in the era before SSDs or even 1TB HDDs existed.
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.
For eg: if there are multiple emails from same domain name, then domain name column will have same entry multiple times.
When domain is moved to sub table, main table still has duplicate entries, in form of foreign keys, while domain table has unique entry for each domain name
Sorry, no. The original schema was correct, and the new one is a mistake.
The reason is that the new schema adds a great deal of needless complexity, requires the overhead of foreign keys, and makes it a hassle to change things later.
It's better to stick the the original design and add a unique index with key prefix compression, which all major databases do these days. This means that the leading values gets compressed out and the resulting index will be no larger and no slower than the one with foreign keys.
If you include all of the keys in the index, then it will be a covering index and all queries will hit the index only, and not the heap table.
It’s not that easy: you need to consider the total size and cardinality of the fields potentially being denormalized, too. If, say, the JOINed values fit in memory and, especially, if the raw value is much larger than the key it might be the case that you’re incurring a table scan to avoid something which stays in memory or allows the query to be satisfied from a modest sized index. I/O isn’t as cheap if you’re using a SAN or if you have many concurrent queries.
The inverse of your statement is also true. Denormalizing the database to avoid duplicating data will not necessarily improve performance in an RDBMS - it might even make it worse.
And even if this is somehow not an ideal use of the database, it's certainly far from "terrible", and to conclude that the programmer who designed it was "clueless" is insulting to that programmer. Even if that programmer was you, years ago (as in the blog post).
Moreover, unless you can prove with experimental data that the 3rd-normal-form version of the database performs significantly better or solves some other business problem, then I would argue that refactoring it is strictly worse.
There are good reasons not to use email addresses as primary or foreign keys, but those reasons are conceptual ("business logic") and not technical.
To a degree, this is true though. An engineer's salary is a huge expense for a startup, it's straight up cheaper to spend more on cloud and have the engineer work on the product itself. Once you're bigger, you can optimize, of course.
If this is a case from real life that you have seen, you should definitely elaborate more on it. Otherwise, you are creating an extreme example that has no significance in the discussion, as I could create similarly ridiculous examples for the other side.
There are always costs and benefits to decisions. It seems that are you only looking at the costs and none of the benefits?
Depends on the provider. I've checked the pricing of 4 top relational database cloud vendors (Google, Amazon, Azure, IBM) and they all charge for the number of CPU cores and RAM you buy, not by the number of rows scanned like PlanetScale did in the post you refer to. They'll be more expensive than buying your own server but not nearly what PlanetScale charges for full table scan queries.
She doesn’t mention the write characteristics of the system but she implies that it was pretty write heavy.
In that case it’s not obvious to me that putting a key prefix index on every column is the correct thing to do, because that will get toilsome very quick in high write loads.
Given that she herself wrote the before and after systems 20 years ago and that the story was more about everyone having dumb mistakes when they are inexperienced perhaps we should assume the best about her second design?
One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available.
But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They are costly and will be costly if in tech industry, we continue to boot experienced engineers… the tacit knowledge those engineers have gained wi ll not be passed on and this means more people have to figure things out by themselves
You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB.
We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.
> Adding an index now increases the insert operation cost/time
that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert".
the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.
>One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s
Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that.
I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a message back from a DBA saying "do this, it's better".
If you have one, more power to you. I'm currently making a good living as a freelance DBA/SRE for startups. With a team of 3-5 devs, some of which frontend and/or mobile devs, proper database knowledge is definitely thin on the ground in some places.
In 2002, you started seeing major increases in query run time with as little as 5 joins. Once you hit six you had to start thinking about rearchitecting your data or living with slow response times.
There was a lot of pressure to relax 3NF as being too academic and not practical.
Around then, I had a friend who was using a pattern of varchar primary keys so that queries that just needed the (unique) name and not the metadata could skip the join. We all acted like he was engaging in the Dark Arts.
> When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available.
To my understanding, for example Firebird/Interbase had automatic key prefix compression as far back as early 2000s at the very least. I don't believe you could even turn it off.
I agree. In the "new" FK-based approach, you'll still need to scan indexes to match the IP and email addresses (now in their own tables) to find the FKs, then do one more scan to match the FK values. I would think this would be significantly slower than a single compound index scan, assuming index scans are O(log(n))
Key thing is to use EXPLAIN and benchmark whatever you do. Then the right path will reveal itself...
> Sorry, no. The original schema was correct, and the new one is a mistake.
Well, you also save a space by doing this (though presumably you only need to index the emails as IPs are already 128 bits).
But other than that, I'm also not sure why the original schema was bad.
If you were to build individual indexes on all four rows, you would essentially build the four id tables implicitly.
You can calculate the intersection of hits on all four indexs that match your query to get your result. This is linear in the number of hits across all four indexes in the worst case but if you are careful about which index you look at first, you will probably be a lot more efficient, e.g. (from, ip, to, helo).
Even with a multi index on the new schema, how do you search faster than this?
> Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships?
> Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue.
> My point is: EVERYONE goes through this, particularly if operating in a vacuum with no mentorship, guidance, or reference points. Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?
(It seems a lot of folks are getting nerd-sniped by the set-up and missing the moral of the story, eh?)
I don't know the actual numbers, but it's been pointed out that at any given time something like half of all programmers have been doing it less than five years, for decades now.
That, plus the strident ignorance of past art and practice, seem to me to bring on a lot of issues.
I feel like that phrase has been there forever, and I'm exactly 35. Will this happen more often as I grow older? Ugh. Feels weird. Maybe also a bit depressing.
It was weird seeing all the spongebob squarepants memes take over the internet when I was too old to ever grow up with that. I turned 28 in 1999 when that first aired. That was my "holy shit I'm so old" moment when that finally trickled up into my awareness as someone nearly turning 40 or so.
Same age as you. I have a lot of friends in the Philippines. I swear the Facebook employment info of half the people in the Philippines says they work at the Krusty Krab. (And for most filipinos, the internet = facebook.) For some reason I never asked what that meant, and for many years I thought that was just some odd joke, and was vaguely puzzled about how widespread it is. Eventually I happened on the Spongebob connection!
As a 48 year old who has no idea who an awful lot of modern celebrities are, I'm constantly wondering if it is because I'm just old now or if its because there are just way more celebrities these days due to the large parasocial celebrity class that didn't really exist when I was younger.
I just spent several minutes trying to solve that resistor problem (or a simplified version with adjacent nodes) before giving up and deciding I’ll look up other people’s analyses when I get the time. Definitely a good example.
> (It seems a lot of folks are getting nerd-sniped by the set-up and missing the moral of the story, eh?)
The narrative should lead to the conclusion. If I told you the story of the tortoise and the hare in which the hare gets shot by a hunter, then said the moral is "perseverance wins", you'd be rightfully confused.
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.
What a completely nonresponsive "response". To the best of my knowledge, no one has ever even tried to use auth9n or auth8n. But that's not where the 'n' in authn comes from. authn and authk are both equally distinct from authz.
You appear to be unfamiliar with the extremely common, and problematic, abbreviation "i18n" for "internationalization".
For my part, I have not encountered "authn", "authz", or "authk" before. But prior discussion did not even mention the word "authorization", and that seemed worth bringing out in the open.
So, not nonresponsive, just responsive to things you weren't personally interested in.
You get my upvote both for having a friendly response, and because your answer is what my brain filled in and I’m glad I don’t need to learn a new shorthand.
OR we could demand they get expensive irrelevant degrees and be geniuses at coding by the time they graduate with no relevant skills! Hey, in fact, let’s just do that instead. :)
> but your example is of a case where you did something fine but then replaced it with something worse
They way I see it is that it was a case of something that wasn't working, then replaced with something that was working.
I don't know what your metrics about "good" or "bad" are but eventually they got a solution that covered their use cases and the solution was good enough for stakeholders and that is "good".
With yes the added confounding factors of how often our industry seems to prefer to hire youth over experience, and subsequently how often experience "drains" to other fields/management roles/"dark matter".
Yep. Folks are getting lost in the weeds discussing indexing of database tables. That's _totally_ beside the point here.
The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema.
Unfortunately, "operating in a vacuum with no mentorship, guidance, or reference points", is normal for many folks. She's talking about the trenches of SV, it's even worse outside of that where the only help you might get is smug smackdowns on stackoverflow (or worse, the DBA stackexchange) for daring to ask about such a "basic problem".
I’d be a lot more sympathetic if the major RDMSes didn’t have outstanding and thorough reference manuals or that there weren’t a mountain of books on the subject that cover, among other things, the topic of indexing and its importance. MySQL’s manual, for example, has covered this subject from the very beginning: https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html (I don’t have the 3.x manuals handy but it was there back then too).
It’s not clear from the article whether the author spent any time studying the problem before implementing it. If she failed to do so, it is both problematic and way more common than it ought to be.
“Ready, fire, aim”
But you know what they say: good judgment comes from experiences and experience comes from bad judgment.
Compounding the problem here is that the author now has much more experience and in a reflective blog post, still got the wrong answer.
IMO the better lesson to take away here would have been to take the time getting to know the technology before putting it into production instead of jumping head first into it. That would be bar raising advice. The current advice doesn’t advise caution; instead it perpetuates the status quo and gives “feel good” advice.
I couldn't disagree more with this comment. No amount of reading documentation teaches you how to build production systems. You progress much faster by getting your hands dirty, making mistakes, and learning from them.
The challenge of writing good software is not about knowing and focusing on the perfection every gory detail, it's about developing the judgement to focus on the details that actually matter. Junior engineers who follow your advice will be scared to make mistakes and their development will languish compared to those who dive in and learn from their mistakes. As one gains experience it's easy to become arrogant and dismissive of mistakes that junior engineers make, but this can be extremely poisonous to their development.
I couldn't disagree more with this comment. No amount of getting your hands dirty and making mistakes teaches you how to learn from your mistakes, nor changes the impact of the mistakes. You also progress much faster by learning from other peoples' mistakes, this is why written language is so powerful.
Honestly, I think your comment is okay minus the fact that you're trying to highlight such hard disagreement with a sentiment that people should read the fucking manual. Really, you couldn't disagree MORE?
There is definitely value in RTFM. There are cases where making mistakes in production is not acceptable, and progressing by "making mistakes" is a mistake on its own. I don't think the case in this article sounds like one of those, but they do exist (e.g. financial systems (think about people losing money on crypto exchanges), healthcare, or, say, sending rockets into space). In many cases, making mistakes is fine in test systems, but completely, absolutely, catastrophically unacceptable in production. Although, I refuse to blame the junior engineer for such mistakes, I blame management that "sent a boy to do a man's job" (apologies for a dated idiom here).
(As an aside, overall, I disagree with many of the comments here nitpicking the way the author solved the problem, calling her "clueless", etc. I really don't care about that level of detail, and while I agree the solution does not seem ideal, it worked for them better than the previous solution.)
> No amount of getting your hands dirty and making mistakes teaches you how to learn from your mistakes...
Categorically wrong.
Mistakes, failure, and trial and error are very much a part of developing skills. If you're not making mistakes, you're also not taking enough risks and thus missing out on opportunities for growth.
Respectfully, I believe you are disagreeing with an argument that I am not making. It's not an either-or scenario. Both access to and reliance on reference materials and the ability to safely experiment are part of the professional engineer's toolbox. I can say this with confidence because I was a junior engineer once - it's not like I'm speaking without experience.
Everyone - new and experienced developers alike - should have a healthy fear of causing pain to customers. Serving customers - indirectly or otherwise - is what makes businesses run. As a principal engineer today my number one focus is on the customer experience, and I try to instill this same focus in every mentee I have.
Does that mean that junior developers should live in constant fear and decision paralysis? Of course not.
That's where the mentor - the more experienced and seasoned engineer - comes in. The mentor is roughly analogous to the teacher. And the teaching process uses a combination of a mentor, reference materials, and the lab process. The reference materials provide the details; the lab process provides a safe environment in which to learn; and the mentor provides the boundaries to prevent the experiments from causing damage, feedback to the mentee, and wisdom to fill in the gaps between them all. If any of these are absent, the new learner's development will suffer.
Outstanding educational systems were built over the last 3 centuries in the Western tradition using this technique, and other societies not steeped in this tradition have sent their children to us (especially in the last half-century) to do better than they ever could at home. It is a testament to the quality of the approach.
So no, I'm not advocating that junior developers should do nothing until they have read all the books front to back. They'd never gain any experience at all or make essential mistakes they could learn from if they did that. But junior developers should not work in a vacuum - more senior developers who both provide guidance and refer them to reference materials to study and try again, in my experience, lead to stronger, more capable engineers in the long term. "Learning to learn" and "respect what came before" are skills as important as the engineering process itself.
The thing is, specifically, that the OP did NOT have a mentor. They had a serious problem to solve, pronto, and knew enough to take a crack at it. OK, the initial implementation was suboptimal. So what? It's totally normal, learn and try again. Repeat.
It would be nice if every workplace had a orderly hierarchy of talent where everyone takes care to nurture and support everyone else (especially new folks). And where it's OK to ask questions and receive guidance even across organizational silos, where there are guardrails to mitigate accidents and terrible mistakes. If you work in such an environment, you are lucky.
It is far more common to have sharp-elbow/blamestorm workplaces which pretend to value "accountability" but then don't lift a finger to support anyone who takes initiative. I suspect that at the time Rachelbythebay worked in exactly this kind of environment, where it simply wasn't possible for an OPS person to go see the resident database expert and ask for advice.
Right. Hence my concern about the lack of helpful advice other than “accept yourself”[1]: Neophyte engineers lacking guardrails and local mentors should fall back to what they learned in school: experiment, use your books, and ask questions from the community. Mentors can be found outside the immediate workplace, after all.
And when it comes to taking production risks, measure twice and cut once, just like a good carpenter.
[1] Of course you should accept yourself. But that alone won’t advance the profession or one’s career.
Being able to do that is a luxury that many do not enjoy in nose-to-the-grindstone workplaces. You make an estimate (or someone makes it for you), then you gotta deliver mentor or no mentor, and whether you know the finer points of database design/care-and-feeding or not.
There's something good to be said for taking action. Rachelbythebay did just fine. No one died, the company didn't suffer, it was just a problem, it got corrected later. Big whoop.
But it's also about constantly asking yourself "how can this be done better? What do I not know that could improve it?"
I learned probably 80% of what I know about DB optimization from reading the Percona blog and S.O. The other 20% came from splashing around and making mistakes, and testing tons of different things with EXPLAIN. That's basically learning in a vacuum.
You can't really search a problem if you don't suspect its existence. At this point you might feel you have everything you need to start the implementation, how do you guess?
Yes and no and this post highlights a subtle issue with mentorship (which I think is important): Technology does not stand still. What was true in 2002, might not be true today. While adopting the naïve approach was detrimental back then, today databases recognise that this happens and provide easy workarounds to get you out of trouble that didn't exist back then.
I've experienced this just by switching languages. C# had many articles dedicated to how much better StringBuilder was compared to String.Concat and yet, other languages would do the right thing by default. I would give advice in a totally different language about a problem that the target language did not have.
As the song goes:
"Be careful whose advice you buy but be patient with those who supply it
Advice is a form of nostalgia, dispensing it is a way of fishing the past
From the disposal, wiping it off, painting over the ugly parts
That might be true if you just take blanket advice. The key is to find out why say StringBuilder is better than String.Concat. If you understand the implementation details and tradeoffs involved, this makes the knowledge much more applicable in the future. The core concepts in technology do not move nearly as fast as individual projects, libraries, frameworks, languages, etc...
You wrote: <<While adopting the naïve approach was detrimental back then, today databases recognise that this happens and provide easy workarounds to get you out of trouble that didn't exist back then.>>
Do you have a specific example that would help in the case described in Rachel's blog post?
I am still making (and finding) occasional indexing and 3NF mistakes. In my experience, it is always humans finding and fixing these issues.
If you repeated use concat to build up a string, the amount of time grows exponentially. This is because the string I copied each time you concat. Note that the + operator on strings gets turned into a call to concat.
I was wondering about it in the context of "yet, other languages would do the right thing by default". Repeatedly concatenating to the same string (as opposed to concatenating an array of strings in one go) would be slow in any language I know of, unless you allocate a larger buffer up front, which is what StringBuilder does.
Some languages have mutable strings, but you would still need to allocate a sufficiently larger buffer if you want to add strings in a loop.
Repeatedly concatting a string is the fastest way I am aware of to build a longer one in Javascript. (This has been deliberately optimized for) I believe PHP this might also be the case for, or at least very fast. Perl might be pretty fast at this but I could be wrong.
I know JS's case somewhat well and the lesson there is not the comment such as above that "it just does the right thing". JS doesn't use a "String Builder" under the hood with a lot of string + calls, but instead that JS does subtly the "wrong" thing as an optimization: in the .NET CLR strings are always, always immutable (or they are not strings according to guarantees that the CLR makes). JS lives in a single language VM that makes no such guarantees, and often lives in strictly single threaded worlds where the VM doesn't have to guarantee the immutability of strings to anyone at the binary memory layout level, so most JS VMs are free to do the "wrong thing" (from the .NET CLR perspective) and just mutably alter strings under the hood as a deliberate optimization.
There's a lot of interesting conflations of "right" versus "wrong" in this this thread. There's the C# "best practices" "right" versus "wrong" of knowing when to choose things like StringBuilder over string.Concat or string's + operator overloading (which does just call string.Concat under the hood). There's the "does that right/wrong" apply to other languages question? (The answer is far more complex than just "right" and "wrong".) There's the VM guarantees of .NET CLR's hard line "no string is mutable" setting a somewhat hard "right" versus "wrong" and other language's VMs/interpreters not needing to make such guarantees to themselves or to others. None of these are really ever "right" versus "wrong", almost all of them are trade-offs described as "best practices" and we take the word "best" too strongly especially when we don't know/examine/explore the trade-off context it originated from (and sometimes wrongly assuming that "best" means "universally the best").
I don't think StringBuilder is faster specifically because it allocates a large buffer. Pretty much every language with growable data structures can already grow any such structure pretty fast, including arrays, maps, and mutable strings. They already have a bunch of pre-set constants about stuff like how big to allocate for the initial empty one and how much more to allocate every time you overflow to balance speed and memory efficiency. It's faster because it's mutable and keeps adding new data to the existing buffer until it gets too large.
The cost is not the allocation per se, the cost is copying the bytes. When concatenating two strings, the bytes from both strings are copied into the new string. If you repeatedly concatenate, the bytes end up getting copied many times. E.g if you concatenate 100 strings by appending one by one, the bytes in the first string is copied 99 times, the bytes in the second string is copied 98 times and so on.
Using a StringBuilder, the strings are only copied once when copied into the buffer. If the buffer need to grow, the whole buffer is copied into a larger buffer, but if the buffer grows by say doubling the size, then this cost is amortized, so each string on average is still only copied twice at worst.
Faster yet is concatenating an array of strings in a single concat operation. This avoids the buffer resize issue, since the necessary buffer size is known up front. But this leads to the subtle issue where a single concat is faster than using a StringBuilder, while the cost for multiple appends grows exponentially for concat but only lineary for StringBuilder.
In languages that have immutable string semantics and can detect that they are the exclusive owner of a string, they can modify them in place. For reference counting languages like Python, they can easily determine they are the exclusive owner by checking for a reference count of 1. See:
Rust's ownership system ensures that there cannot exist mutable and immutable references to the same object at the same time. So if you have a mutable reference to a string it is ok to modify in place.
I have not confirmed either the Python example or the Rust example too deeply, since I'm replying to 5 day old comment. But the general principal holds. GC languages like C# and Java don't have easy ways to check ownership so they always copy strings when mutating them. Maybe I'll write a blogpost on this in the future.
I agree, and would even go one step further and say the first implementation was a decent first pass. Sometimes the 'awful' implementation is good enough and your time is better used on something else. However, this pattern can sometimes bite you in the long term. As you can have tons of little 'hacks' all over the place and people become afraid to touch them, or worse copy from them.
This also nicely shows one of the fun things with table scans. They look decent at first then perf goes crappy. Then you get to learn something. In this case it looks like she used normalization to scrunch out the main table size (win on the scan rate, as it would not be hitting the disk as much with faster row reads). It probably also helped just on the 'to' lookups. Think about how many people are in an org, even a large company has a finite number of people but they get thousands of emails a week. That table is going to be massively smaller than keeping it in every row copied over and over. Just doing the 'to' clause alone would dump out huge amounts of that scan it was doing. That is even before considering an index.
The trick with most SQL instances is do less work. Grab less rows when you can. Use smaller tables if you can. Throw out unnecessary data from your rows if you can. Reduce round trips to the disk if you can (which usually conflicts with the previous rules). Pick your data ordering well. See if you can get your lookups to be ints instead of other datatypes. Indexes usually are a good first tool to grab when speeding up a query. But they do have a cost, on insert/update/delete and disk. Usually that cost is less than your lookup time, but not always. But you stick all of that together and you can have a DB that is really performant.
For me the fun one was one DB I worked in they used a GUID as the primary key, and therefore FK into other tables. Which also was the default ordering key on the disk. Took me awhile to make them understand why the perf on look up was so bad. I know why they did it and it made sense at the time to do it that way. But long term it was holding things back and ballooning the data size and crushing the lookup times.
How did I come by all of this? Lots of reading and stubbing my toe on things and watching other do the same. Most of the time you do not get a 'mentor'. :( But I sure try to teach others.
… I appreciate her sentiment at the end there that we should try to change the status quo (which I think does suck), but I'm not sure how much power the average employee has over it. Most employers I've worked at seem loathe to retain anyone past about 2 years. (E.g., currently I'm in the 95'th percentile, after just over 2 years.) IME it takes about 6 months to really learn how a company's systems work to where to proficiency (yes, six months.) which means we're spending ~25% of the time "training", where "training" is usually someone trying something crazy and failing, and getting corrected as opposed to some structured form of learning. Oftentimes the systems that these engineers are stumbling I think are the internal ones; they need more features, or refactoring to account for organic growth, but since they're not shiny new customer facing features they'll get exactly 0 priority from PMs. The engineers who build experience working with these systems and actually know what needs to change and how without breaking the existing use cases … are about to leave the company, since nobody is retained beyond ~2 years.
I also find my own team is usually strapped for resources, normally, people. (Usually politely phrased as "time".) Yes, one has to be wary of mythical man-month'ing it, but like my at my last employ we had essentially 2 of us on a project that could have easily used at least one, if not two more people. Repeat across every project and that employer was understaffed by 50-100%, essentially.
Some company just went for S-1, and they were bleeding cash. But they weren't bleeding it into new ventures: they were bleeding it into marketing. Sure, that might win you one or two customers, but I think you'd make much stronger gains with new products, or less buggy products that don't drive the existing customers away.
Also there's an obsession with "NIT" — not invented there — that really ties my hands as an engineer. Like, everything has to be out sourced to some cloud vendor provider whose product only fits some of the needs and barely, and whose "support" department appears to be unaware of what a computer is. I'm a SWE, let me do my thing, once in a while? (Yes, where there's a good fit for an external product, yes, by all means. But these days my job is 100% support tickets, and like 3% actual engineering.)
What's funny is there seems to be a lot of debate among grizzled veterans here about whether her old solution is actually better than the new. We're getting into index types, engine implementations, normal forms, etc. And really, this is a relatively simple development problem that I can easily imagine on an interview test.
Now, imagine a new engineer, just getting started trying to make sense of it all, yet being met with harsh criticism and impatience.
Maybe that was exactly her point--to get everyone debating over such a relatively trivial problem to communicate the messages: Good engineering is hard. Everyone is learning. Opinions can vary. Show some humility and grace.
The way she kept referring to the programmer made me suspicious that that would be the ending. I personally really like that as a way of conveying the message of us all having growing pains and learning the hard way from some inefficient code.
I know I've also had these encounters (specifically in DBs), and I'm sure there are plenty more to come.
1. Me from six months ago. Clueless about a lot of stuff, made some odd technical choices for obscure reasons, left a lot of messes for me to deal with.
2. Me six months from now. Anything I don't have time for now, I can leave for him. If I was being considerate I'd write better docs for him to work with, but I don't have the time for that either.
This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form".
Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know if a more normalized DB structure would be warranted, but from her description I would say it wasn't.
And IIUC, the larger point, made by the twist ending, is that we shouldn't be so critical, but try to actually help newbies learn what we insist that they should know.
It seemed to me the point was that if the industry remains designed to push out experienced engineers that there won't be anyone to mentor the engineers that are just beginning their careers. Further that we will bend the productivity of the field downward and have less effective systems.
In what way is the industry designed that way now? Maybe some get sucked into management roles, but I don’t see any evidence of a broad design to push them out.
Role count pyramids, volume and inflexibility of working hours, abusive management techniques, a constant treadmill of the new hotness, more...
Some offices avoid many of the challenging patterns but not the vast majority. A lot of it is natural result of the newness of the industry and the high stakes of investment. Many of the work and organizational model assumptions mean that offering flexibility to employees complicates the already challenging management role. Given the power of those roles, this means workers largely must fit into the mold rather than designing a working relationship that meets all needs. As the needs change predictably over time a mounting pressure develops.
I'm only 17 years in but it has gotten harder and harder to find someone willing to leave me to simply code in peace and quiet and dig my brain into deep and interesting problems.
I have mixed feelings about this. On one hand, I appreciate the larger point, and pointing out mistakes again goes against that point. On the other, the post had so many technical details it overshadowed the larger point, especially for something that wasn't obviously broken.
I wouldn't get too vehement on it. Knowing how to normalize isn't a bad thing. And, if you intricately know your write and read needs, you may be able to assist the work more appropriately.
Yes, the proposed ‘better’ structure basically amounts to building your own indexes. Normally, I’d assume it is better to use the RDBMS’s own engine to do that, don’t roll your own.
There may well be some subtlety to the indexing capabilities of MySQL I’m unaware of though - could easily imagine myself making rookie mistakes like assuming that it has same indexing capabilities. So, to the post’s point - if I were working on a MySQL db I would probably benefit from an old hand’s advice to warn me away from dangerous assumptions.
On the other hand I also remember an extremely experienced MS SQL Server DBA giving me some terrible advice because what he had learned as a best practice on SQL Server 7 turned out to be a great way to not get any of the benefits of a new feature in SQL Server 2005.
As a former SQL Server DBA, most SQL Server DBAs are absolute gurus on 1 or 2 specific versions of SQL server, and massively ignorant about anything newer.
It's a strange role where, in order to do your job well, you kind of have to hyper-specialize in the specific version of the tech that your MegaCorp employer uses, which is usually a bit older, because upgrading databases can be extremely costly and difficult.
The same O(N) -> O(log N) improvement for queries, yes. The constant factor on the separate tables might be better. It’s also a more complicated design.
Sure would! I think it would be marginally better, in fact, because you would just need to look at the index rather than five tables. Access would be more local.
Moving the data into another table would still require indexes: one on the original table's column (which now stores the new id) and one on the new table's primary key.
In most cases I'd expect just adding an index to the original table to be more efficient, but it depends on the type of the original column and if some data could be de-duplicated by the normalization.
Given this is a "from the start of her career" story, I'm guessing she was running similar versions of mysql to the versions I started with, and if my guess is correct then probably not.
On anything you're likely to be deploying today, just throwing a compound index at it is likely quite sufficient though.
And indexed this, with only single WHERE in the query.
I don't understand at all how multiple tables thing would help compared to indices, and the whole post seemed kind of crazy to me for that reason. In fact if I had to guess multiple tables would've performed worse.
That is if I'm understanding the problem correctly at all.
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 than multiple)
But also yeah I think just a compound UNIQUE constraint on the original columns would have worked
I'm pretty sure that the degree of normalization given in the end goes also well beyond 3rd-Normal-Form.
I think that is 5th Normal Form/6th Normal Form or so, almost as extreme as you can get:
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 narrow down the search to records starting with the letter (WHERE combined LIKE "A%SMITH"), you will have to check all the rows where firstname starts with "A" even if their lastname is not Smith. If there are two separate indexed columns the WHERE clause will look like this:
WHERE firstname like "A%" AND lastname = "Smith"
so the search can be restricted to smaller number of rows.
Of course having 2 indexes will have its costs like increased storage and slower writes.
Overall the blog post conveys a useful message but the table example is a bit confusing, it doesn't look like there are any functional dependencies in the original table so pointing to normalization as the solution sounds a bit odd.
Given that the query from the post is always about concrete values (as opposed to ranges) it sounds like the right way to index the table is to use hash index (which might not have been available back in 2002 in MySql).
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.
Oh, so by fuzzy search you mean full text search, not just a simple 'LIKE A%' pattern - it's an entirely different kind of flying altogether (https://www.youtube.com/watch?v=3qNtyfZP8bE).
I don't know what kind of internal representation is used to store tsvector column type in PG, but I think GIN index lookups should be very simple to parallelize and combine (I believe a GIN index would basically be a map [word -> set_of_row_ids_that_contain_the_word] so you could perform them on all indexed columns at the same time and then compute intersection of the results?). But maybe two lookups in the same index could be somehow more efficient than two lookups in different indexes, I don't know.
I'm still sceptical about the LIKE/ILIKE scenario though.
"WHERE firstname LIKE 'A%' and lastname LIKE 'Smith' "
can easily discard all the Joneses and whatnot before even looking at the firstname column, whereas
"WHERE combined_firstname_and_lastname LIKE 'A%SMITH' " will only be able to reject "Andrew Jones" after reading the entire string.
>I'm pretty sure that the degree of normalization given in the end goes also well beyond 3rd-Normal-Form.
> I think that is 5th Normal Form/6th Normal Form or so, almost as extreme as you can get
The original schema is also in 5NF, unless there are constraints we aren't privy too.
5NF means you can't decompose a table into smaller tables without loss of information, unless each smaller table has a unique key in common with the original table (in formal terms, no non-trivial join dependencies except for those implied by the candidate key(s)).
The original table appears to meet this constraint: you could break it down into, e.g., four tables, one mapping the quad id to the IP address, one mapping it to the HELO string, etc. However, each of these would share a unique constraint with the original table, the quad id; hence the original table is in 5NF.
As for 6NF, I don't think the revised schema meets that: 6NF means you can't losslessly decompose the table at all. In the revised schema, the four tables mapping IP id to IP address etc. are in 6NF, but the table mapping quad id to a unique combination of IP id, HELO id, etc. is not: it could be decomposed into four tables similarly to how the original table could be.
(Interestingly, if the original table dropped the quad ID column and just relied on a composite primary key of IP address, HELO string, FROM address and TO address, it would be in 6NF.)
Also, the "better" solution assumes IPv4 addresses and is not robust to a sudden requirements change to support IPv6. Best to keep IP address as a string unless you really, REALLY need to do something numerical with one or more of the octets.
Based on my experience I disagree. If there is a native data type that represents your data, you should really use it.
It will make sure that incorrect data is detected at the time of storage rather than at some indeterminate time in the future and it will likely be more efficient than arbitrary strings.
And in case of IP addresses, when you are using Postgres, it comes with an inet type that covers both ipv4 and ipv6, so you will be save from requirement changes
Keeping IP addresses as string isn't trivial. You can write those in many ways. Even with IPv4. IPv6 just brings new variations. And when talking about migration to IPv6 you have to decide if you keep IPv4 Addresses as such or prefix with ::ffff:.
In the end you have to normalize anyways. Some databases have specific types. If not you have to pick a scheme and then ideally verify.
Yeah, I'm not clear on how multiple tables fixed it other than allowing you to scan through the main table faster.
Multiple tables could be a big win if long email addresses are causing you a data size problem, but for this use case, I think a hash of the email would suffice.
Well she did say she had indexes on the new table. It could have been fixed with an index on the previous table, but a new table with indexes also fixed it.
Right, and when you do that, you don't even need a RDBMS. An key-value store would suffice. This essentially just becomes a set! Redis or memcached are battle-tested workhorses that would work even better than a relational DB here.
But this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn't be comfortable using in production.
> this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn't be comfortable using in production.
Indeed; her problem looks to have been pre-memcached.
Yeah… technically that’s (the solution) normalization, but really not a bad design originally. If you never want a particular atom of data repeated, yes you have to normalize each column, and that is efficient in the long run for storage size, but in more normal data storage that still means a lot of joins and you still want indices. On a modern SSD you could use that one table, throw on some indices as you suggest and not notice it again until you hit millions of rows.
Anything you are doing conditional logic or joins on in SQL usually needs some sort of index.
Because as shocking as this might sound like unlike the characters in the movie Matrix I don't have a built-in INET_NTOA function in my retina to see the string form of the IP addresses when glancing at table rows of a large table looking for patterns or a certain IP.
Many. You also expect me to remember how to spell INET_NTOA and on what fields to use it on. What if I wanted do a quick "SELECT * FROM"? I barely know how to spell English words what makes you think I'm going to remember how to spell INET_NTOA.
I realize you’re describing a general problem and not just this particular example, but fwiw NTOA and friends got a lot easier for me once I learned enough that I stopped trying to “remember how to spell” them and started thinking about them in terms of the underlying concepts, like “Network (as in big-endianness) to ASCII”. Learning the significance of the term “address family” was a big clue for me for self-directed learning in this space :)
Based on this piece of old documentation I found [0] for MySQL 3.23 (the most recent version in 2002 as far as I can tell), certain types of indices were only available on certain types of engines. Furthermore, columns were restricted to 255 characters, which may be too short for some of the fields saved in the database.
Modern databases abstract away a lot of database complexity for things like indices. It's true that these days you'd just add an index on the text column and go with it. Depending on your index type and data, the end result might be that the database turns the table into third normal form by creating separate lookup tables for strings, but hides it from the user. It could also create a smarter index that's less wasteful, but the end result is not so dissimilar. Manually doing these kinds of optimisations these days is usually a waste of effort or can even cause performance issues (e.g. that post on the front page yesterday about someone forgetting to add an index because mysql added them automatically).
All that doesn't mean it was probably a terrible design back when it was written. We're talking database tech of two decades ago, when XP had just come out and was considered a memory hog because it required 128MB of RAM to work well.
The fact remains that whether the text column existed on her original table, or whether it was pulled out to a normalized table, literally all of the same constraints would apply (e.g. max char length, any other underlying limitations of indexing).
The issue is that her analysis of what the issue was with her original table is completely wrong, and it's very weird given that the tone her "present" self is that it's so much more experienced and wise than her "inexperienced, naive" self.
My point is that she should give her inexperience self a break, all that was missing from her original implementation were some indexes.
I used MySql a lot back then, had many multi-column indexes, and never had an issue.
More importantly, given the degree of uniqueness likely to be present in many of those columns (like the email addresses), she could have gotten away with not indexing on every column.
Your first sentence was, I'm afraid, very much not a universal experience.
Your second is quite possibly true, but would have required experimentation to be sure of, and at some point "doing the thing that might be overkill but will definitely work" becomes a more effective use of developer time, especially when you have a groaning production system with live users involved.
I'd say there's very little performance gain in normalizing (it usually goes the other way anyway: normalize for good design, avoiding storing multiple copies of individual columns; de-normalize for performance).
I'm a little surprised by the tone of the article - sure, there were universities that taught computer science without a database course - but it's not like there weren't practical books on dB design in the 90s and onward?
I guess it's meant as a critique of the mentioned, but not linked other article "being discussed in the usual places".
> All that doesn't mean it was probably a terrible design back when it was written. We're talking database tech of two decades ago, when XP had just come out and was considered a memory hog because it required 128MB of RAM to work well.
A lot of this stuff was invented in the 70s, and was quite sophisticated by 2000. It just wasn't free, rather quite expensive. MySQL was pretty braindead at the time, and my recollection is that even postgres was not that hot either. We've very lucky they've come so far.
Consider the rows `a b c d e` and `f b h i j`.
How many times will `b` be stored in the two formulations? 2 and 1, right? The data volume has a cost.
Consider the number of cycles to compare a string versus an integer. A string is, of course, a sequence of numbers. Given the alphabet has a small set of symbols you will necessarily have a character repeated as soon as you store more values than there are characters. Therefore the database would have to perform at least a second comparison. I imagine you're correctly objecting that in this case the string comparison happens either way but consider how this combines with the first point. Is the unique table on which the string comparisons are made smaller? Do you expect that the table with combinations of values will be larger than the table storing comparable values? Through this, does the combinations table using integer comparisons represent a larger proportion of the comparison workload? Clearly she found that to be the case. I would expect it to be the case as well.
The article declared that it was running in production and satisfying the requirement. Do you have a different definition of working? I say that to clarify what I was attempting to communicate by "it worked".
I think you're remarking about the relationship between normalized tables and indexes. That relationship does exist in some databases.
GP's point is that the final schema has a primary key index on the main table that solved the problem, and then it has a bunch of useless additional tables. The solution was just to add a primary key index on the main table. Adding the additional tables just slows down the query because it now has to do five index lookups instead of one.
Using IDs may lead to a performance gain if it leads to a smaller database: less bytes to read/write/cache => less I/O volume, and better cache hits compensating the potentially higher amounts of seeks on mass storage. This is especially true when seeks are quick (SSD...).
Therefore as long as the size data type (C language's "sizeof") used for an ID is inferior to the average size of the column contents then using an ID will very probably lead to a performance gain.
On some DB states and usage patterns (where commonly used data+index cannot fit in RAM: the caches (DB+OS) hit ratios are < to .99) this gain will be somewhat proportional (beware of diminishing returns) to the value of the ratio (total data+index size BEFORE using IDs)/(total data+index size AFTER using IDs).
Creating queries then becomes more difficult (one has to use 'JOIN'), however there are ways alleviate this: using views, "natural join"...
Some modern DB engines let you put data into an index, in order to spare an access to the data when the index is used (Postgresql: see the "INCLUDE" parameter of the "CREATE INDEX"). As far as I understand using a proper ID (<=> on average smaller than the data it represents) will also lead to a gain(?)
I usually store IPv4 as unsigned int, and IPv6 as two 64 bit unsigned ints. But supporting both variants require that I have an a table for each type and a glue table to reference the record in the correct table. Storing simple stuff quickly turns complicated…
I especially feel the proposed solution could be problematic given that the data is supposed to be transient. How are keys deleted from the "id | value" tables when rows are deleted from the main table? Does it just keep accumulating rows or is there something like a GC query running occasionally that deletes all unreferenced values?
As a 25 year “veteran” programmer, I’m glad I’m not the only one that likes the original schema (with indexes added). A fully normalized table is a lot more difficult to read, troubleshoot, and reason about. You would need a pretty good query to poke around the data.
When I first started using postgres I was amazed at how much of what I thought was "database knowledge" was actually "working around limitations of the versions of mysql I'd been using" knowledge.
These days, sure, the original schema plus indices would work fine on pretty much anything I can think of. Olde mysqls were a bit special though.
I was kinda surprised by the last chapter, suddenly this is about 'advancing in programming as a woman', and it's over! Like the last few chapters make sense in that view.
What I want to know is how she went from a strong database format (ID + data, keyed) into a weak database model (not a database... is a key-value store) which is likely to be much slower, but also, this is what happens to varchars under the hood already (the rows only hold pointers, to where the strings/objects are placed, rather than inlining varchars) (at least in litesql!), so optimally it's only two dereferences instead of one..
Like, eh, this programmer went through an anti-learning process somehow, and came up with a senseless optimisation. The post ends midway through the learning!
Feels like you could just concatenate and hash the 4 values with MD5 and store the hash and time.
Edit: I guess concatenate with a delimiter if you're worried about false positives with the concat. But it does read like a cache of "I've seen this before". Doing it this way would be compact and indexed well. MD5 was fast in 2002, and you could just use a CRC instead if it weren't. I suppose you lose some operational visibility to what's going on.
Yup, we do this at work for similar purposes and it works a-ok. We also have some use cases that follow the original “problem” schema and they work fine with the correct indices involved.
My guess is that in 2002 there were some issues making those options unappealing to the Engineering team.
When we do this in the realm of huge traffic then we run the data through a log stream and it ends up in either a KV store, Parquet with SQL/Query layer on top of it, or hashed and rolled into a database (and all of the above of there are a lot of disparate consumers. Weee Data Lakes).
This is also the sort of thing I’d imagine Elastic would love you to use their search engine for.
For the 2021 version, you'd just generate a bloom filter/cuckoo hash from all the data gathered by your spamhaus database periodically. Make a separate one for each value in your tuple and your score would be the number of the sub-hashes that matched.
From the post, you'd have to be rebuilding it every 15 minutes:
> A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through.
I'm not super familiar with this stuff, but I believe you could then use a key-value store with automatic expiry like Redis for automatic pruning and faster lookups.
Heh at the end I think I may have been called out for my harsh response to the post being referenced at the start here on HN since it seemed to raise some hackles. Borrowing from other social media terminology here, "subtweeted" but by a blog post, not sure how I feel about that.
Honestly it almost makes me feel compelled to start writing more for the industry but to be honest, my opinion is most ideas are not great, or not novel, up to and including my own. So writing about them seems likely not to be beneficial. I guess you could argue I could let the industry be the judge of that though.
Err. Wat. The original schema was (mostly) fine. It had no indexes. The second schema looks like it tried to work around lack of database optimization features. It's in no way "better" from a data design standpoint.
A database with good string index support isn't doing string comparisons to find selection candidates - at least, not initially.
I wouldn't bet on these indexes optimizing anything back then. MySQL was legendary that while implementing the necessary standards (to some degree) it was neither reliable nor efficient.
The quote was "database optimization features" and the scope was "MySQL as of 2002".
Of course, even my old DBase II handbook talks about indexes - all that's old hat. MySQL had them, too.
MySQL also used to have a long-earned reputation as a toy database though, and MySQL in 2002 was right within the timeframe where it established that reputation. So yeah, you could add indexes, but did they speed things up (as in them being an "optimization feature")? Public opinion was rather torn on that.
I was there and I don't remember it like you describe. MySQL was certainly not a toy database in 2002. I learned PHP from a book called "PHP and MySQL". They went together like white on rice. Every forum software used it. Hell, even today Uber uses MySQL at a VERY large scale to power their schemaless database.
I was there, too, and I wasn't alone in my assessment:
https://www.databasejournal.com/features/mysql/article.php/2... (to pick a contemporary example using that exact word) suggests that the 2003 release of mysql4 ends the "toy status". Others might have had different thresholds, but that reputation was hard earned and back then still pretty apropos (to a declining degree over time because MySQL caught up). I lost data to MySQL more than once (if memory serves but it's been a while: MyISAM was a mess, the InnoDB integration not ready yet)
To handwave two extreme positions of those days (because I'm too lazy to look up ancient forums where such stuff has been discussed at length), it's very possible that different groups approached things differently:
Members of a PHP User Group probably didn't consider MySQL a toy but the best thing since sliced bread, given how closely aligned PHP was to MySQL back then (it got more varied over time, but that preference can still be felt in PHP projects)
OTOH when you had a significant amount of perl coders in your peer group, it was rather likely that some of them could go on for days about how _both_ PHP and MySQL are toys when anybody dared to talk about PHP (which ties in neatly with the original post and its reference about "The One")
What I got from this post is a headache after reviewing the wikipedia entries on 3NF (all all of it's dependencies: 1NF, 2NF, also 3.5NF). Man the language is dense.
j/k, always nice to review some of this "basic" stuff (that you barely use in everyday work but it's good to have it in the back of your head when changing something in the db schema)
Assuming this needs to be optimized for massive scale, just hash the values to a single indexed field.
And use something other than an RDBMS. Put the hash in Redis and expire the key; your code simply does an existence check for the hash. You could probably handle gmail with a big enough cluster.
Fair enough, but I think the issue here is recognizing that the underlying business operation is a hit test on an expiring hash key. You could have used MySQL as a key/value store or looked for something more specialized.
The moral of this post really falls flat coming from this author, most of whose posts are snarky, passive-aggressive rants where she rants about someone's alleged incompetence or something similar.
It seems pretty relevant to me that the author mostly writes snarky articles which directly contradict the moral of this story.
For the record, I totally agree with the moral of the story. But we can't just blindly ignore context, and in this case the context is that the author regularly writes articles where someone else is ridiculed because the author has deemed them incompetent.
In a similar vein, no one would just ignore it and praise Zuckerberg if he started discussing the importance of personal privacy on his personal blog.
To me it really clashed with the original post which concludes we need guidance/mentorship/reference points. Most of the comments in Hackernews are constructive or genuinely curious, don't just dismiss the author and instead provide things to learn.
For the author to then write a reply and dismiss all these comments as comments that are really saying "I am THE ONE. You only need to know me. I am better than all of you." is just plain rude and completely counter to their own point.
> It's a massive problem, and we're all partly responsible. I'm trying to take a bite out of it now by writing stuff like this.
suggests to me that maybe she has recently had a change of heart about such rants. I'm willing to give her the benefit of the doubt, because we all need to give each other the benefit of the doubt these days.
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 the sector. TBH about this last point IDK the situation nowadays in Silicon Valley, but in Europe my limited experience is that ageism is not that bad; kt's actually difficult to find seasoned developers.
I don't buy it. If the only point was managing rookie errors, etc., the blog post shouldn't have been 15~ paragraphs of technical discussion and 2 paragraphs at the bottom of "my point is ..." You can't advocate that people just ignore 80% of the article because they're "not the point of the post."
I'm sure a good chunk of tech workers are worried that when an influential blogger writes something like this a couple hundred developers will Google 3NF and start opening PRs.
It's a narrative. A story. She told it in that way to lure people who get hooked on technical discussion, and then make the point that they probably would have missed or ignored. Without a narrative example, she could have just said "I made mistakes too, live and learn" but she chose to provide an example before presenting the thesis.
And hey, what's so bad about people learning about 3NF? Are you not supposed to know what that is until you're some mythical ninth level DBA?
Sure, I don't mind that the hook, and controversial posts often create the most educational discussion. I only take exception to the idea that we shouldn't debate it because it's "not the point." (specifically for technical advice; being over-pedantic is a thing).
I certainly don't mean to criticize the author or say that they shouldn't have posted the article. If articles needed to be 100% correct then no-one has any business writing them. And it's still open to debate whether the article is even wrong or misleading.
> Are you not supposed to know what that is until you're some mythical ninth level DBA?
I think the world might be a better place if people _did_ wait until they were ninth level DBAs :p
Jokes aside, I have no problem with people learning 3NF. I'm more concerned that people can be too quick to take this sort of rule-of-thumb advice to heart (myself included). And in my opinion, database optimization is too complex to fit well into simple rules like "normalize when your query looks like this." My only 'rules' for high-frequency/critical tables and queries is "test, profile, load test, and take nothing for granted." But I know just enough to know that I don't know enough about SQL databases to intuit performance.
The problem with her point is that it doesn't go anywhere. Mentorship is obviously good and ageism is obviously bad, and she doesn't have specific suggestions on how to solve any of the problems, so the hook is the most substantial thing to talk about.
If she told it to lure people who get hooked on technical discussion, then it’s not fair for you to complain about said people analyzing said technical discussion.
But if she told it to show people who get hooked on technical discussion that this often makes them miss a more important point, then they certainly proved her right.
I’m happy the hn comments are nitpicking, I’m not particularly well versed in database schemas but while reading it I was going “???????” and it’s good to know I’m not going crazy
Yeah I was confused because the new design looked like an obvious anti-pattern to me, in contrast to adding an index to the original table. And then I was wondering whether I had a completely false understanding of what database normalization means (especially since this is the first time I’d heard of 3NF).
The reason "the details are important" here are not because of the nitty gritty around what mistakes a "novice" programmer made.
They are important because the present incarnation of the author is making all the wrong diagnoses about the problems with the original implementation, despite doing it with an air of "Yes, younger me was so naive and inexperienced, and present me is savvy and wise".
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.
I think she inadvertently made a different point, which is that even experienced developers sometimes misunderstand the problem and make mistakes.
Or an even better argument: you don't need to actually understand the problem to fix it, often you accidentally fix the problem just by using a different approach.
I would argue instead that this comment thread is making the point that people forget that things that work now wouldn't've worked then and design decisions have to be made based on the database engine you're running in production.
Yes, we won't know because she didn't try it. And we know she didn't try it because the problem she described is table scans, not "indexed strings are somehow slow in MySQL circa 2002".
Or she did try it and it didn't work - or researched the question and figured, quite reasonably based on my experience of mysql in that era, it probably wouldn't work - and kept the post to only the original problem and the final approach to avoid having even more technical details in a post that wasn't really about those.
I agree that we don't know, but it seems a little unfair to her to treat every unknown as definitely being the most negative of the possibilities.
I really don't understand your general thrust here. MySql certainly had lots of issues in 2003, but being able to support multi-column indexes was not one of them. Her analysis is simply wrong - it is wrong now and was wrong then. Here is the doc from MySql v3.23:
7.4.2 Multiple-Column Indexes
MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist
of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 7.4.1,
“Column Indexes”).
A multiple-column index can be considered a sorted array containing values that are created by
concatenating the values of the indexed columns.
MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known
quantity for the first column of the index in a WHERE clause, even if you do not specify values for the
other columns
As I said already: "mysql could index strings" and "using a compound index over four varchar columns would've worked out well" are significantly different propositions.
To be more verbose about it - there is an important difference between "can be created" and "will perform sufficiently well on whatever (likely scavenged) hardware was assigned to the internal IT system in question."
I wouldn't be surprised if the "server" for this system was something like a repurposed Pentium 233 desktop with a cheap spinning rust IDE drive in it, and depending on just how badly the spammers were kicking the shit out of the mail system in question that's going to be a fun time.
A composite index is literally just concatenating the fields and then indexing that value. This is not technology that was out of reach of MySQL in 2002 and there is no reason to presume it was so when TFA clearly described the problem as a complete lack of indexes.
Just because the author was intending to get across a certain point, doesn't mean the implementation details aren't worth discussing too. I don't think many people here would disagree with the central point, so what's there to discuss about it? I think it's uncharitable to assume that everyone talking about the implementation details is "missing the point".
The real meaning being that people learn and gain experience over time? Is this really something we need to read two pages of text to find out? I think people are justifiably miffed at having read two pages for such a trivial message. Not only that, but these "stupid implementation details" could seriously mislead someone who really is a clueless newbie.
I think you are leaping to conclusions here. It is possible that most people want to keep HN focused on technical conversations and use this as an opportunity to learn something (I certainly do), which is why you are seeing many more comments on the technical aspects.
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 implementation details.
Meanwhile, Rachel posted an update. Apparently, the whole section about how "the system got reworked" doesn't describe the proper solution, but her own (Rookie Rachel's) attempt to fix it.
As a 48 year old who lives in San Diego (so not Silicon Valley but geographically much closer than Europe) and still works as a programmer and who still enjoys coding and continually fights any attempt to put me into any sort of management role...
I think ageism is a valid concern but its also not a black and white issue because while I know quite a few older folks like me that are still constantly keeping up to date I've also known some people my age or just a little bit older who basically stagnated into irrelevance as experts in now discarded technology who never really moved on, so the line between what is ageism and what is people just not bringing value to the table anymore can be a bit blurry.
I'm by no means trying to suggest ageism isn't an issue in tech, I do see echoes of it when I look around and if I suddenly lost the network of people I work with, have worked with in the past, etc, who understand I'm still very technically 'flexible' for an older person I'm pretty sure it would make it difficult to find a job in the practical sense of getting pre-filtered out often just based on the age thing. So my heart really goes out to people who do find themselves in situations where they are capable but can't find work due to age primarily.
But, uh, the issue is somewhat complicated and very case by case, I think.
There's probably also some sense of people trying to paint the lesson of "sometimes rookie errors aren't actually errors, and your seniors will tell you you're doing something wrong, when in actuality their solution is worse than what you came up with."
Which I guess is saying the article's setup story makes the exact opposite point that it's trying to?
I think the 'terrible schema' thing is a secondary issue. The important take away for me was this:
> Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?
Maybe I’ll change my mind in 5 years, but I have a hard time believing engineers over the age of 35 get chased off. What actually seems to be the case is that the field skews young because it’s rapidly growing, and older engineers choose to retire early because they can afford to.
Which results in the same problems but the root cause is different.
I’m pretty sure all the people who are criticizing the database design haven’t read the ending. The article isn’t about the schema, it’s about helping those entry level programmers
It would be more effective at making that point if it didn’t have the confused and arguably incorrect section in the middle. If as a writer, the audience misses your point, maybe it’s a problem with your writing rather than the audience.
The problem is that the technical narrative in the post contributes to imposter syndrome and gaslights entry level programmers into thinking they’re doing database design wrong.
The one thing I would add here is that the devlead (or equivalent - there always is someone) should have added at least one test. Not a unit test or 99% co drags test but a "does it do what we want" test - a golden path test.
I came across something like this with an apprentice recently. I let the lack of a repeatable test pass because speed / time / crunch etc and the obvious how can that possibly fail code of course failed. The most basic test would have caught it in the same way some basic performance testing would have helped rachel however many years back.
Without knowing which database engine (MySQL comes with more than one) she was using, nor the testing that she actually performed, what makes you say that?
I remember versions of mysql whose string indexing was sufficiently limited on every available backend that this would've been the correct change.
Hell, back in ... 2001? I think? I ended up making exactly the same set of changes to an early database design of mine, for pretty much the same stated reasons and with very similarly pleasant effects on its performance.
imo “clueful”/clueless is more about being able to sense situations where there is something to be known rather than knowing everything upfront all the time (which is obviously impossible)
The initial design was fine and btw it did not violate 3NF, actually (if you add auto increment pk). A single composite index would’ve most likely solved all problems
> The observation was that we could probably store the IP address, HELO string, FROM address and TO address in a table, and send back a 4xx "temporary failure" error the first time we saw that particular tuple (or "quad"). A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through.
I've run into this form of greylisting, it's quite annoying. My service sends one-time login links and authorization codes that expire in 15 minutes. If the email gets delayed, the user can just try again, right? Except I'm using AWS SES, so the next email may very well come from a different address and will get delayed again.
* security, an attacker has more time to intercept and use the links and codes.
* UX, making the user wait 15+ minutes to do certain actions is quite terrible.
I've had a couple support requests about this. The common theme seems to be the customer is using Mimecast, and the fix is to add my sender address in a whitelist somewhere in their Mimecast configuration.
> The first time you encounter something, you're probably going to make some mistakes. There's a post going around tonight about how someone forgot to put an index on some database thing and wound up doing full table scans (or something like that).
Which post is referenced here?
I also believe this to be a tooling issue. It's often opaque what ends up being run after I've done something in some framework (java jpa, django queries whatever). How many queries (is it n+1 issues at bay?), how the queries will behave etc. Locally with little data everything is fine, until it blows up in production. But you may not even notice it blowing up in production, because that relies on someone having instrumented the db and push logs+alarms somewhere. So it's easy to remain clueless.
492 comments
[ 4.2 ms ] story [ 530 ms ] thread>and still NoSQL has a place and a use case, just like anything else that exists.
What actually makes you believe that it's the NoSQL that has "some use cases" and relational databases are "default ones" instead of NoSQL/no-relational by default?
Also is helo field even needed?
Even better, use a database with a proper inet datatype. That way you get correctness, space efficiency, and ability to intelligently index.
In 2021 I’d recommend just turning off the IPv6 allocation or deleting it from DNS like this site does.
This is the funniest IPv6 excuse I've heard on HN.
https://ipv6excuses.com/
Also remember indexes take space, and if you index all your text columns you'll balloon your DB size; and this was 2002, when that mattered a lot more even for text. Indexes also add write and compute burden for inserts/updates as now the DB engine has to compute and insert new index entries in addition to the row itself.
Finally, normalizing your data while using the file-per-table setting (also not the default back then) can additionally provide better write & mixed throughout due to the writes being spread across multiple file descriptors and not fighting over as many shared locks. (The locking semantics for InnoDB have also improved massively in the last 19 years.)
* Assuming the author used InnoDB and not MyISAM. The latter is a garbage engine that doesn't even provide basic crash/reboot durability, and was the default for years; using MyISAM was considered the #1 newbie MySQL mistake back then, and it happened all the time.
Indexes take space, except for the clustered index.
An important distinction.
If the point of this table is always selecting based on IP, m_from, m_to, then clustering on those columns in that order would make sense.
Of course, if it's expected that a lot of other query patterns will exist then it might make sense to only cluster on one of those columns and build indexes on the others.
Indexes didn’t go away with these extra tables, they live in the ‘id’ field of each one. They also probably had UNIQUE constraint on the ‘value’ field, spending time on what you describe in the second half of my citation.
I mean, that should have saved some space for non-unique strings, but all other machinery is still there. And there are 4 extra unique constraints (also an index) in addition to 4 primary keys. Unless these strings are long, the space savings may turn out to be pretty marginal.
In general, Indexes alone can't save you from a normalization problems. From certain points of view an Index is itself another form of denormalization and while it is very handy form of denormalization, heavily relying on big composite indexes (and the "natural keys" they represent) makes normalization worse and you have to keep such trade-offs in mind just as you would any other normalization planning.
It is theoretically possible a database could do something much smarter and semi-normalized with for instance Trie-based "search" indexes, but specifically in 2002 so far as I'm aware of most of the databases at the time such indexes were never the default and didn't have great multi-column support and would have been expensive to compute if you did turn them own. Even such "smart" Indexes would likely still have suggested you normalize first.
The author mentions that these events took place in 2002. At that time the site was likely still running MySQL 3 and InnoDB was very new and considered experimental. Sure MySQL 4.0 had shipped in 2002 and InnoDB was a first class citizen, but back then upgrades from one version of MySQL to another weren't trivial tasks. You also tended to wait until the .1 release before making that leap.
So in fairness for the folks who originally set up that database MyISAM was likely their only realistic option.
I looked after and managed a fleet of MySQL servers from back then and for many years afterwards and even then it wasn't until MySQL 5 that we fully put our trust in InnoDB.
It may be that the second solution wasn’t the best, or that it was better for write performance or memory usage—this was 2002 after all. It may also be the case that the cardinal it’s of some columns was low in such a way that the four table solution was better, but maybe getting the columns in the right order for a multi column index could do that too.
Either way, I don’t think it really matters that much and doesn’t affect the point of the article.
However, normalizing the fields that contain repetitive data into separate tables could create significant space savings since the full text for each column would not need to stored for each row. Instead of 20-30 bytes per email address, a 4 byte (assuming 32-bit era) OID is stored in its place.
It's pretty easy to imagine how quickly the savings would add up, and that it would be very helpful in the era before SSDs or even 1TB HDDs existed.
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.
It depends - if this was the full use-case then maybe a single table is actually a pretty good solution.
while main table did not have any such guarantees, so, there will be a lot of indexing and duplication in atleast 3 (of 4 sub tables)
Moreover, indexing would be much faster and efficient as there are only unique values in sub tables
For eg: if there are multiple emails from same domain name, then domain name column will have same entry multiple times.
When domain is moved to sub table, main table still has duplicate entries, in form of foreign keys, while domain table has unique entry for each domain name
The reason is that the new schema adds a great deal of needless complexity, requires the overhead of foreign keys, and makes it a hassle to change things later.
It's better to stick the the original design and add a unique index with key prefix compression, which all major databases do these days. This means that the leading values gets compressed out and the resulting index will be no larger and no slower than the one with foreign keys.
If you include all of the keys in the index, then it will be a covering index and all queries will hit the index only, and not the heap table.
But strictly from a performance aspect, I agree it's a wash if both were done correctly.
Moreover, unless you can prove with experimental data that the 3rd-normal-form version of the database performs significantly better or solves some other business problem, then I would argue that refactoring it is strictly worse.
There are good reasons not to use email addresses as primary or foreign keys, but those reasons are conceptual ("business logic") and not technical.
There are always costs and benefits to decisions. It seems that are you only looking at the costs and none of the benefits?
There was a whole thread yesterday about how a dude found out that it isn't: https://briananglin.me/posts/spending-5k-to-learn-how-databa... (also mentioned in the RbtB post)
But your point is well-taken. Hardware is cheap.
In that case it’s not obvious to me that putting a key prefix index on every column is the correct thing to do, because that will get toilsome very quick in high write loads.
Given that she herself wrote the before and after systems 20 years ago and that the story was more about everyone having dumb mistakes when they are inexperienced perhaps we should assume the best about her second design?
But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They are costly and will be costly if in tech industry, we continue to boot experienced engineers… the tacit knowledge those engineers have gained wi ll not be passed on and this means more people have to figure things out by themselves
We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.
Adding an index now increases the insert operation cost/time and adds additional storage.
If insert speed/volume is more important than reads keep the indexes away. Replicate and create an index on that copy.
that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert".
the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.
Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that.
I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a message back from a DBA saying "do this, it's better".
There was a lot of pressure to relax 3NF as being too academic and not practical.
Around then, I had a friend who was using a pattern of varchar primary keys so that queries that just needed the (unique) name and not the metadata could skip the join. We all acted like he was engaging in the Dark Arts.
To my understanding, for example Firebird/Interbase had automatic key prefix compression as far back as early 2000s at the very least. I don't believe you could even turn it off.
Key thing is to use EXPLAIN and benchmark whatever you do. Then the right path will reveal itself...
Did everyone on HN miss that the database in question was whichever version of MySQL existed in 2002?
Well, you also save a space by doing this (though presumably you only need to index the emails as IPs are already 128 bits).
But other than that, I'm also not sure why the original schema was bad.
If you were to build individual indexes on all four rows, you would essentially build the four id tables implicitly.
You can calculate the intersection of hits on all four indexs that match your query to get your result. This is linear in the number of hits across all four indexes in the worst case but if you are careful about which index you look at first, you will probably be a lot more efficient, e.g. (from, ip, to, helo).
Even with a multi index on the new schema, how do you search faster than this?
> Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships?
> Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue.
> My point is: EVERYONE goes through this, particularly if operating in a vacuum with no mentorship, guidance, or reference points. Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?
I don't know the actual numbers, but it's been pointed out that at any given time something like half of all programmers have been doing it less than five years, for decades now.
That, plus the strident ignorance of past art and practice, seem to me to bring on a lot of issues.
that is an excellent term.
I could have jumped on it and played it but I figured just another toy craze, it'll be over before long.
Maybe a bit of both.
^H^H^H^H^Hitic
There, FTFY.
https://xkcd.com/1053/
The narrative should lead to the conclusion. If I told you the story of the tortoise and the hare in which the hare gets shot by a hunter, then said the moral is "perseverance wins", you'd be rightfully confused.
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.
I've always seen authn. Where'd you pick this usage up?
For my part, I have not encountered "authn", "authz", or "authk" before. But prior discussion did not even mention the word "authorization", and that seemed worth bringing out in the open.
So, not nonresponsive, just responsive to things you weren't personally interested in.
They way I see it is that it was a case of something that wasn't working, then replaced with something that was working.
I don't know what your metrics about "good" or "bad" are but eventually they got a solution that covered their use cases and the solution was good enough for stakeholders and that is "good".
With yes the added confounding factors of how often our industry seems to prefer to hire youth over experience, and subsequently how often experience "drains" to other fields/management roles/"dark matter".
The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema.
Unfortunately, "operating in a vacuum with no mentorship, guidance, or reference points", is normal for many folks. She's talking about the trenches of SV, it's even worse outside of that where the only help you might get is smug smackdowns on stackoverflow (or worse, the DBA stackexchange) for daring to ask about such a "basic problem".
It’s not clear from the article whether the author spent any time studying the problem before implementing it. If she failed to do so, it is both problematic and way more common than it ought to be.
“Ready, fire, aim”
But you know what they say: good judgment comes from experiences and experience comes from bad judgment.
Compounding the problem here is that the author now has much more experience and in a reflective blog post, still got the wrong answer.
IMO the better lesson to take away here would have been to take the time getting to know the technology before putting it into production instead of jumping head first into it. That would be bar raising advice. The current advice doesn’t advise caution; instead it perpetuates the status quo and gives “feel good” advice.
The challenge of writing good software is not about knowing and focusing on the perfection every gory detail, it's about developing the judgement to focus on the details that actually matter. Junior engineers who follow your advice will be scared to make mistakes and their development will languish compared to those who dive in and learn from their mistakes. As one gains experience it's easy to become arrogant and dismissive of mistakes that junior engineers make, but this can be extremely poisonous to their development.
Honestly, I think your comment is okay minus the fact that you're trying to highlight such hard disagreement with a sentiment that people should read the fucking manual. Really, you couldn't disagree MORE?
There is definitely value in RTFM. There are cases where making mistakes in production is not acceptable, and progressing by "making mistakes" is a mistake on its own. I don't think the case in this article sounds like one of those, but they do exist (e.g. financial systems (think about people losing money on crypto exchanges), healthcare, or, say, sending rockets into space). In many cases, making mistakes is fine in test systems, but completely, absolutely, catastrophically unacceptable in production. Although, I refuse to blame the junior engineer for such mistakes, I blame management that "sent a boy to do a man's job" (apologies for a dated idiom here).
(As an aside, overall, I disagree with many of the comments here nitpicking the way the author solved the problem, calling her "clueless", etc. I really don't care about that level of detail, and while I agree the solution does not seem ideal, it worked for them better than the previous solution.)
Categorically wrong.
Mistakes, failure, and trial and error are very much a part of developing skills. If you're not making mistakes, you're also not taking enough risks and thus missing out on opportunities for growth.
Everyone - new and experienced developers alike - should have a healthy fear of causing pain to customers. Serving customers - indirectly or otherwise - is what makes businesses run. As a principal engineer today my number one focus is on the customer experience, and I try to instill this same focus in every mentee I have.
Does that mean that junior developers should live in constant fear and decision paralysis? Of course not.
That's where the mentor - the more experienced and seasoned engineer - comes in. The mentor is roughly analogous to the teacher. And the teaching process uses a combination of a mentor, reference materials, and the lab process. The reference materials provide the details; the lab process provides a safe environment in which to learn; and the mentor provides the boundaries to prevent the experiments from causing damage, feedback to the mentee, and wisdom to fill in the gaps between them all. If any of these are absent, the new learner's development will suffer.
Outstanding educational systems were built over the last 3 centuries in the Western tradition using this technique, and other societies not steeped in this tradition have sent their children to us (especially in the last half-century) to do better than they ever could at home. It is a testament to the quality of the approach.
So no, I'm not advocating that junior developers should do nothing until they have read all the books front to back. They'd never gain any experience at all or make essential mistakes they could learn from if they did that. But junior developers should not work in a vacuum - more senior developers who both provide guidance and refer them to reference materials to study and try again, in my experience, lead to stronger, more capable engineers in the long term. "Learning to learn" and "respect what came before" are skills as important as the engineering process itself.
The thing is, specifically, that the OP did NOT have a mentor. They had a serious problem to solve, pronto, and knew enough to take a crack at it. OK, the initial implementation was suboptimal. So what? It's totally normal, learn and try again. Repeat.
It would be nice if every workplace had a orderly hierarchy of talent where everyone takes care to nurture and support everyone else (especially new folks). And where it's OK to ask questions and receive guidance even across organizational silos, where there are guardrails to mitigate accidents and terrible mistakes. If you work in such an environment, you are lucky.
It is far more common to have sharp-elbow/blamestorm workplaces which pretend to value "accountability" but then don't lift a finger to support anyone who takes initiative. I suspect that at the time Rachelbythebay worked in exactly this kind of environment, where it simply wasn't possible for an OPS person to go see the resident database expert and ask for advice.
And when it comes to taking production risks, measure twice and cut once, just like a good carpenter.
[1] Of course you should accept yourself. But that alone won’t advance the profession or one’s career.
Being able to do that is a luxury that many do not enjoy in nose-to-the-grindstone workplaces. You make an estimate (or someone makes it for you), then you gotta deliver mentor or no mentor, and whether you know the finer points of database design/care-and-feeding or not.
There's something good to be said for taking action. Rachelbythebay did just fine. No one died, the company didn't suffer, it was just a problem, it got corrected later. Big whoop.
I learned probably 80% of what I know about DB optimization from reading the Percona blog and S.O. The other 20% came from splashing around and making mistakes, and testing tons of different things with EXPLAIN. That's basically learning in a vacuum.
I've experienced this just by switching languages. C# had many articles dedicated to how much better StringBuilder was compared to String.Concat and yet, other languages would do the right thing by default. I would give advice in a totally different language about a problem that the target language did not have.
As the song goes:
"Be careful whose advice you buy but be patient with those who supply it
Advice is a form of nostalgia, dispensing it is a way of fishing the past
From the disposal, wiping it off, painting over the ugly parts
And recycling it for more than it's worth"
Do you have a specific example that would help in the case described in Rachel's blog post?
I am still making (and finding) occasional indexing and 3NF mistakes. In my experience, it is always humans finding and fixing these issues.
https://docs.microsoft.com/troubleshoot/dotnet/csharp/string...
Some languages have mutable strings, but you would still need to allocate a sufficiently larger buffer if you want to add strings in a loop.
There's a lot of interesting conflations of "right" versus "wrong" in this this thread. There's the C# "best practices" "right" versus "wrong" of knowing when to choose things like StringBuilder over string.Concat or string's + operator overloading (which does just call string.Concat under the hood). There's the "does that right/wrong" apply to other languages question? (The answer is far more complex than just "right" and "wrong".) There's the VM guarantees of .NET CLR's hard line "no string is mutable" setting a somewhat hard "right" versus "wrong" and other language's VMs/interpreters not needing to make such guarantees to themselves or to others. None of these are really ever "right" versus "wrong", almost all of them are trade-offs described as "best practices" and we take the word "best" too strongly especially when we don't know/examine/explore the trade-off context it originated from (and sometimes wrongly assuming that "best" means "universally the best").
Using a StringBuilder, the strings are only copied once when copied into the buffer. If the buffer need to grow, the whole buffer is copied into a larger buffer, but if the buffer grows by say doubling the size, then this cost is amortized, so each string on average is still only copied twice at worst.
Faster yet is concatenating an array of strings in a single concat operation. This avoids the buffer resize issue, since the necessary buffer size is known up front. But this leads to the subtle issue where a single concat is faster than using a StringBuilder, while the cost for multiple appends grows exponentially for concat but only lineary for StringBuilder.
https://github.com/python/cpython/blob/main/Objects/unicodeo...
Rust's ownership system ensures that there cannot exist mutable and immutable references to the same object at the same time. So if you have a mutable reference to a string it is ok to modify in place.
I have not confirmed either the Python example or the Rust example too deeply, since I'm replying to 5 day old comment. But the general principal holds. GC languages like C# and Java don't have easy ways to check ownership so they always copy strings when mutating them. Maybe I'll write a blogpost on this in the future.
This also nicely shows one of the fun things with table scans. They look decent at first then perf goes crappy. Then you get to learn something. In this case it looks like she used normalization to scrunch out the main table size (win on the scan rate, as it would not be hitting the disk as much with faster row reads). It probably also helped just on the 'to' lookups. Think about how many people are in an org, even a large company has a finite number of people but they get thousands of emails a week. That table is going to be massively smaller than keeping it in every row copied over and over. Just doing the 'to' clause alone would dump out huge amounts of that scan it was doing. That is even before considering an index.
The trick with most SQL instances is do less work. Grab less rows when you can. Use smaller tables if you can. Throw out unnecessary data from your rows if you can. Reduce round trips to the disk if you can (which usually conflicts with the previous rules). Pick your data ordering well. See if you can get your lookups to be ints instead of other datatypes. Indexes usually are a good first tool to grab when speeding up a query. But they do have a cost, on insert/update/delete and disk. Usually that cost is less than your lookup time, but not always. But you stick all of that together and you can have a DB that is really performant.
For me the fun one was one DB I worked in they used a GUID as the primary key, and therefore FK into other tables. Which also was the default ordering key on the disk. Took me awhile to make them understand why the perf on look up was so bad. I know why they did it and it made sense at the time to do it that way. But long term it was holding things back and ballooning the data size and crushing the lookup times.
How did I come by all of this? Lots of reading and stubbing my toe on things and watching other do the same. Most of the time you do not get a 'mentor'. :( But I sure try to teach others.
I also find my own team is usually strapped for resources, normally, people. (Usually politely phrased as "time".) Yes, one has to be wary of mythical man-month'ing it, but like my at my last employ we had essentially 2 of us on a project that could have easily used at least one, if not two more people. Repeat across every project and that employer was understaffed by 50-100%, essentially.
Some company just went for S-1, and they were bleeding cash. But they weren't bleeding it into new ventures: they were bleeding it into marketing. Sure, that might win you one or two customers, but I think you'd make much stronger gains with new products, or less buggy products that don't drive the existing customers away.
Also there's an obsession with "NIT" — not invented there — that really ties my hands as an engineer. Like, everything has to be out sourced to some cloud vendor provider whose product only fits some of the needs and barely, and whose "support" department appears to be unaware of what a computer is. I'm a SWE, let me do my thing, once in a while? (Yes, where there's a good fit for an external product, yes, by all means. But these days my job is 100% support tickets, and like 3% actual engineering.)
Now, imagine a new engineer, just getting started trying to make sense of it all, yet being met with harsh criticism and impatience.
Maybe that was exactly her point--to get everyone debating over such a relatively trivial problem to communicate the messages: Good engineering is hard. Everyone is learning. Opinions can vary. Show some humility and grace.
I know I've also had these encounters (specifically in DBs), and I'm sure there are plenty more to come.
I would not expect her to be like this from what I've read on her blog so I was surprised. Well written!
That's definitely not true anymore, if it ever was. Senior/staff level engs with 20+ years of experience are sought after and paid a ton of money.
A lot of the basic issues beginners go through can be mitigated by paying attention in class (and/or having formal education in the first place).
I’ll grant that there are other, similar, things that everyone will go through though.
My own was manually writing an ‘implode’ function in Javascript because I didn’t know ‘join’ existed.
1. Me from six months ago. Clueless about a lot of stuff, made some odd technical choices for obscure reasons, left a lot of messes for me to deal with.
2. Me six months from now. Anything I don't have time for now, I can leave for him. If I was being considerate I'd write better docs for him to work with, but I don't have the time for that either.
Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know if a more normalized DB structure would be warranted, but from her description I would say it wasn't.
Some offices avoid many of the challenging patterns but not the vast majority. A lot of it is natural result of the newness of the industry and the high stakes of investment. Many of the work and organizational model assumptions mean that offering flexibility to employees complicates the already challenging management role. Given the power of those roles, this means workers largely must fit into the mold rather than designing a working relationship that meets all needs. As the needs change predictably over time a mounting pressure develops.
I'm only 17 years in but it has gotten harder and harder to find someone willing to leave me to simply code in peace and quiet and dig my brain into deep and interesting problems.
(Some subtleties on projected values and such, but the point stands.)
There may well be some subtlety to the indexing capabilities of MySQL I’m unaware of though - could easily imagine myself making rookie mistakes like assuming that it has same indexing capabilities. So, to the post’s point - if I were working on a MySQL db I would probably benefit from an old hand’s advice to warn me away from dangerous assumptions.
On the other hand I also remember an extremely experienced MS SQL Server DBA giving me some terrible advice because what he had learned as a best practice on SQL Server 7 turned out to be a great way to not get any of the benefits of a new feature in SQL Server 2005.
Basically, we’re all beginners half the time.
It's a strange role where, in order to do your job well, you kind of have to hyper-specialize in the specific version of the tech that your MegaCorp employer uses, which is usually a bit older, because upgrading databases can be extremely costly and difficult.
In most cases I'd expect just adding an index to the original table to be more efficient, but it depends on the type of the original column and if some data could be de-duplicated by the normalization.
On anything you're likely to be deploying today, just throwing a compound index at it is likely quite sufficient though.
But I think she's if anything been in the industry longer than I have and the versions of mysql I first ran in production were a different story.
So you could've had a indexed column of "fingerprint", like
ip1_blahblah_evil@spammer.somewhere_victim1@our.domain
And indexed this, with only single WHERE in the query.
I don't understand at all how multiple tables thing would help compared to indices, and the whole post seemed kind of crazy to me for that reason. In fact if I had to guess multiple tables would've performed worse.
That is if I'm understanding the problem correctly at all.
But also yeah I think just a compound UNIQUE constraint on the original columns would have worked
I'm pretty sure that the degree of normalization given in the end goes also well beyond 3rd-Normal-Form.
I think that is 5th Normal Form/6th Normal Form or so, almost as extreme as you can get:
https://en.wikipedia.org/wiki/Database_normalization#Satisfy...
The way I remember 3rd Normal Form is "Every table can stand alone as it's own coherent entity/has no cross-cutting concerns".
So if you have a "product" record, then your table might have "product.name", "product.price", "product.description", etc.
The end schema shown could be described as a "Star Schema" too, I believe (see image on right):
https://en.wikipedia.org/wiki/Star_schema#Example
This Rachel person is also much smarter than I am, and you can make just about anything work, so we're all bikeshedding anyways!
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 narrow down the search to records starting with the letter (WHERE combined LIKE "A%SMITH"), you will have to check all the rows where firstname starts with "A" even if their lastname is not Smith. If there are two separate indexed columns the WHERE clause will look like this:
WHERE firstname like "A%" AND lastname = "Smith"
so the search can be restricted to smaller number of rows.
Of course having 2 indexes will have its costs like increased storage and slower writes.
Overall the blog post conveys a useful message but the table example is a bit confusing, it doesn't look like there are any functional dependencies in the original table so pointing to normalization as the solution sounds a bit odd.
Given that the query from the post is always about concrete values (as opposed to ranges) it sounds like the right way to index the table is to use hash index (which might not have been available back in 2002 in MySql).
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:
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
I don't know what kind of internal representation is used to store tsvector column type in PG, but I think GIN index lookups should be very simple to parallelize and combine (I believe a GIN index would basically be a map [word -> set_of_row_ids_that_contain_the_word] so you could perform them on all indexed columns at the same time and then compute intersection of the results?). But maybe two lookups in the same index could be somehow more efficient than two lookups in different indexes, I don't know.
I'm still sceptical about the LIKE/ILIKE scenario though. "WHERE firstname LIKE 'A%' and lastname LIKE 'Smith' " can easily discard all the Joneses and whatnot before even looking at the firstname column, whereas "WHERE combined_firstname_and_lastname LIKE 'A%SMITH' " will only be able to reject "Andrew Jones" after reading the entire string.
> I think that is 5th Normal Form/6th Normal Form or so, almost as extreme as you can get
The original schema is also in 5NF, unless there are constraints we aren't privy too.
5NF means you can't decompose a table into smaller tables without loss of information, unless each smaller table has a unique key in common with the original table (in formal terms, no non-trivial join dependencies except for those implied by the candidate key(s)).
The original table appears to meet this constraint: you could break it down into, e.g., four tables, one mapping the quad id to the IP address, one mapping it to the HELO string, etc. However, each of these would share a unique constraint with the original table, the quad id; hence the original table is in 5NF.
As for 6NF, I don't think the revised schema meets that: 6NF means you can't losslessly decompose the table at all. In the revised schema, the four tables mapping IP id to IP address etc. are in 6NF, but the table mapping quad id to a unique combination of IP id, HELO id, etc. is not: it could be decomposed into four tables similarly to how the original table could be.
(Interestingly, if the original table dropped the quad ID column and just relied on a composite primary key of IP address, HELO string, FROM address and TO address, it would be in 6NF.)
It will make sure that incorrect data is detected at the time of storage rather than at some indeterminate time in the future and it will likely be more efficient than arbitrary strings.
And in case of IP addresses, when you are using Postgres, it comes with an inet type that covers both ipv4 and ipv6, so you will be save from requirement changes
In the end you have to normalize anyways. Some databases have specific types. If not you have to pick a scheme and then ideally verify.
foo@example.com other@example.net foo@example.co mother@example.net
Btw, storing just the domains, or inverting the email strings, would have speed up the comparison
Multiple tables could be a big win if long email addresses are causing you a data size problem, but for this use case, I think a hash of the email would suffice.
But this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn't be comfortable using in production.
Indeed; her problem looks to have been pre-memcached.
Anything you are doing conditional logic or joins on in SQL usually needs some sort of index.
I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue?
If you want to search for range, you can do:
Modern databases abstract away a lot of database complexity for things like indices. It's true that these days you'd just add an index on the text column and go with it. Depending on your index type and data, the end result might be that the database turns the table into third normal form by creating separate lookup tables for strings, but hides it from the user. It could also create a smarter index that's less wasteful, but the end result is not so dissimilar. Manually doing these kinds of optimisations these days is usually a waste of effort or can even cause performance issues (e.g. that post on the front page yesterday about someone forgetting to add an index because mysql added them automatically).
All that doesn't mean it was probably a terrible design back when it was written. We're talking database tech of two decades ago, when XP had just come out and was considered a memory hog because it required 128MB of RAM to work well.
[0]: http://download.nust.na/pub6/mysql/doc/refman/4.1/en/create-...
The issue is that her analysis of what the issue was with her original table is completely wrong, and it's very weird given that the tone her "present" self is that it's so much more experienced and wise than her "inexperienced, naive" self.
My point is that she should give her inexperience self a break, all that was missing from her original implementation were some indexes.
Being able to look up each id via a single-string unique index would've almost certainly worked much better in those days.
More importantly, given the degree of uniqueness likely to be present in many of those columns (like the email addresses), she could have gotten away with not indexing on every column.
Your second is quite possibly true, but would have required experimentation to be sure of, and at some point "doing the thing that might be overkill but will definitely work" becomes a more effective use of developer time, especially when you have a groaning production system with live users involved.
I'd say there's very little performance gain in normalizing (it usually goes the other way anyway: normalize for good design, avoiding storing multiple copies of individual columns; de-normalize for performance).
I'm a little surprised by the tone of the article - sure, there were universities that taught computer science without a database course - but it's not like there weren't practical books on dB design in the 90s and onward?
I guess it's meant as a critique of the mentioned, but not linked other article "being discussed in the usual places".
A lot of this stuff was invented in the 70s, and was quite sophisticated by 2000. It just wasn't free, rather quite expensive. MySQL was pretty braindead at the time, and my recollection is that even postgres was not that hot either. We've very lucky they've come so far.
However...
Consider the rows `a b c d e` and `f b h i j`. How many times will `b` be stored in the two formulations? 2 and 1, right? The data volume has a cost.
Consider the number of cycles to compare a string versus an integer. A string is, of course, a sequence of numbers. Given the alphabet has a small set of symbols you will necessarily have a character repeated as soon as you store more values than there are characters. Therefore the database would have to perform at least a second comparison. I imagine you're correctly objecting that in this case the string comparison happens either way but consider how this combines with the first point. Is the unique table on which the string comparisons are made smaller? Do you expect that the table with combinations of values will be larger than the table storing comparable values? Through this, does the combinations table using integer comparisons represent a larger proportion of the comparison workload? Clearly she found that to be the case. I would expect it to be the case as well.
No it didn't. Someone else put an index on the main table and that worked.
I think you're remarking about the relationship between normalized tables and indexes. That relationship does exist in some databases.
Please say more if I'm missing your point.
Therefore as long as the size data type (C language's "sizeof") used for an ID is inferior to the average size of the column contents then using an ID will very probably lead to a performance gain.
On some DB states and usage patterns (where commonly used data+index cannot fit in RAM: the caches (DB+OS) hit ratios are < to .99) this gain will be somewhat proportional (beware of diminishing returns) to the value of the ratio (total data+index size BEFORE using IDs)/(total data+index size AFTER using IDs).
Creating queries then becomes more difficult (one has to use 'JOIN'), however there are ways alleviate this: using views, "natural join"...
Some modern DB engines let you put data into an index, in order to spare an access to the data when the index is used (Postgresql: see the "INCLUDE" parameter of the "CREATE INDEX"). As far as I understand using a proper ID (<=> on average smaller than the data it represents) will also lead to a gain(?)
This is what the post is about.
And btw, if they hadn't normalized those tables, it'd instead have been pointed out to them that their "post is bizzare..."
These days, sure, the original schema plus indices would work fine on pretty much anything I can think of. Olde mysqls were a bit special though.
What I want to know is how she went from a strong database format (ID + data, keyed) into a weak database model (not a database... is a key-value store) which is likely to be much slower, but also, this is what happens to varchars under the hood already (the rows only hold pointers, to where the strings/objects are placed, rather than inlining varchars) (at least in litesql!), so optimally it's only two dereferences instead of one..
Like, eh, this programmer went through an anti-learning process somehow, and came up with a senseless optimisation. The post ends midway through the learning!
https://news.ycombinator.com/newsguidelines.html
Questionable use of past tense here.
Edit: I guess concatenate with a delimiter if you're worried about false positives with the concat. But it does read like a cache of "I've seen this before". Doing it this way would be compact and indexed well. MD5 was fast in 2002, and you could just use a CRC instead if it weren't. I suppose you lose some operational visibility to what's going on.
My guess is that in 2002 there were some issues making those options unappealing to the Engineering team.
When we do this in the realm of huge traffic then we run the data through a log stream and it ends up in either a KV store, Parquet with SQL/Query layer on top of it, or hashed and rolled into a database (and all of the above of there are a lot of disparate consumers. Weee Data Lakes).
This is also the sort of thing I’d imagine Elastic would love you to use their search engine for.
> A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through.
Honestly it almost makes me feel compelled to start writing more for the industry but to be honest, my opinion is most ideas are not great, or not novel, up to and including my own. So writing about them seems likely not to be beneficial. I guess you could argue I could let the industry be the judge of that though.
A database with good string index support isn't doing string comparisons to find selection candidates - at least, not initially.
What a bizarrely confident article.
That's a perfection description of mysql as of 2002.
http://web.archive.org/web/20020610031610/http://www.mysql.c...
Of course, even my old DBase II handbook talks about indexes - all that's old hat. MySQL had them, too.
MySQL also used to have a long-earned reputation as a toy database though, and MySQL in 2002 was right within the timeframe where it established that reputation. So yeah, you could add indexes, but did they speed things up (as in them being an "optimization feature")? Public opinion was rather torn on that.
https://www.databasejournal.com/features/mysql/article.php/2... (to pick a contemporary example using that exact word) suggests that the 2003 release of mysql4 ends the "toy status". Others might have had different thresholds, but that reputation was hard earned and back then still pretty apropos (to a declining degree over time because MySQL caught up). I lost data to MySQL more than once (if memory serves but it's been a while: MyISAM was a mess, the InnoDB integration not ready yet)
To handwave two extreme positions of those days (because I'm too lazy to look up ancient forums where such stuff has been discussed at length), it's very possible that different groups approached things differently:
Members of a PHP User Group probably didn't consider MySQL a toy but the best thing since sliced bread, given how closely aligned PHP was to MySQL back then (it got more varied over time, but that preference can still be felt in PHP projects)
OTOH when you had a significant amount of perl coders in your peer group, it was rather likely that some of them could go on for days about how _both_ PHP and MySQL are toys when anybody dared to talk about PHP (which ties in neatly with the original post and its reference about "The One")
[bonus: more recent example: http://www.backwardcompatible.net/145-Why-is-MySQL-still-a-t...]
j/k, always nice to review some of this "basic" stuff (that you barely use in everyday work but it's good to have it in the back of your head when changing something in the db schema)
And use something other than an RDBMS. Put the hash in Redis and expire the key; your code simply does an existence check for the hash. You could probably handle gmail with a big enough cluster.
That super-normalized schema looks terrible.
It seems pretty relevant to me that the author mostly writes snarky articles which directly contradict the moral of this story.
For the record, I totally agree with the moral of the story. But we can't just blindly ignore context, and in this case the context is that the author regularly writes articles where someone else is ridiculed because the author has deemed them incompetent.
In a similar vein, no one would just ignore it and praise Zuckerberg if he started discussing the importance of personal privacy on his personal blog.
To me it really clashed with the original post which concludes we need guidance/mentorship/reference points. Most of the comments in Hackernews are constructive or genuinely curious, don't just dismiss the author and instead provide things to learn.
For the author to then write a reply and dismiss all these comments as comments that are really saying "I am THE ONE. You only need to know me. I am better than all of you." is just plain rude and completely counter to their own point.
> It's a massive problem, and we're all partly responsible. I'm trying to take a bite out of it now by writing stuff like this.
suggests to me that maybe she has recently had a change of heart about such rants. I'm willing to give her the benefit of the doubt, because we all need to give each other the benefit of the doubt these days.
If you'd please review https://news.ycombinator.com/newsguidelines.html and stick to the rules when posting here, we'd appreciate it.
Edit: you've been breaking the site guidelines repeatedly. We ban accounts that do that. I don't want to ban you, so please stop doing this!
Edit: typos
I'm sure a good chunk of tech workers are worried that when an influential blogger writes something like this a couple hundred developers will Google 3NF and start opening PRs.
And hey, what's so bad about people learning about 3NF? Are you not supposed to know what that is until you're some mythical ninth level DBA?
I certainly don't mean to criticize the author or say that they shouldn't have posted the article. If articles needed to be 100% correct then no-one has any business writing them. And it's still open to debate whether the article is even wrong or misleading.
> Are you not supposed to know what that is until you're some mythical ninth level DBA?
I think the world might be a better place if people _did_ wait until they were ninth level DBAs :p
Jokes aside, I have no problem with people learning 3NF. I'm more concerned that people can be too quick to take this sort of rule-of-thumb advice to heart (myself included). And in my opinion, database optimization is too complex to fit well into simple rules like "normalize when your query looks like this." My only 'rules' for high-frequency/critical tables and queries is "test, profile, load test, and take nothing for granted." But I know just enough to know that I don't know enough about SQL databases to intuit performance.
They are important because the present incarnation of the author is making all the wrong diagnoses about the problems with the original implementation, despite doing it with an air of "Yes, younger me was so naive and inexperienced, and present me is savvy and wise".
Or an even better argument: you don't need to actually understand the problem to fix it, often you accidentally fix the problem just by using a different approach.
I agree that we don't know, but it seems a little unfair to her to treat every unknown as definitely being the most negative of the possibilities.
7.4.2 Multiple-Column Indexes MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 7.4.1, “Column Indexes”). A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns. MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns
To be more verbose about it - there is an important difference between "can be created" and "will perform sufficiently well on whatever (likely scavenged) hardware was assigned to the internal IT system in question."
I wouldn't be surprised if the "server" for this system was something like a repurposed Pentium 233 desktop with a cheap spinning rust IDE drive in it, and depending on just how badly the spammers were kicking the shit out of the mail system in question that's going to be a fun time.
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 implementation details.
And in that context, everything makes sense.
I think ageism is a valid concern but its also not a black and white issue because while I know quite a few older folks like me that are still constantly keeping up to date I've also known some people my age or just a little bit older who basically stagnated into irrelevance as experts in now discarded technology who never really moved on, so the line between what is ageism and what is people just not bringing value to the table anymore can be a bit blurry.
I'm by no means trying to suggest ageism isn't an issue in tech, I do see echoes of it when I look around and if I suddenly lost the network of people I work with, have worked with in the past, etc, who understand I'm still very technically 'flexible' for an older person I'm pretty sure it would make it difficult to find a job in the practical sense of getting pre-filtered out often just based on the age thing. So my heart really goes out to people who do find themselves in situations where they are capable but can't find work due to age primarily.
But, uh, the issue is somewhat complicated and very case by case, I think.
Which I guess is saying the article's setup story makes the exact opposite point that it's trying to?
> Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?
Which results in the same problems but the root cause is different.
I came across something like this with an apprentice recently. I let the lack of a repeatable test pass because speed / time / crunch etc and the obvious how can that possibly fail code of course failed. The most basic test would have caught it in the same way some basic performance testing would have helped rachel however many years back.
It's hard this stuff :-)
So the real lesson is to be very careful as to who you listen to
Hell, back in ... 2001? I think? I ended up making exactly the same set of changes to an early database design of mine, for pretty much the same stated reasons and with very similarly pleasant effects on its performance.
We detached this subthread from https://news.ycombinator.com/item?id=29140272.
I've run into this form of greylisting, it's quite annoying. My service sends one-time login links and authorization codes that expire in 15 minutes. If the email gets delayed, the user can just try again, right? Except I'm using AWS SES, so the next email may very well come from a different address and will get delayed again.
* security, an attacker has more time to intercept and use the links and codes.
* UX, making the user wait 15+ minutes to do certain actions is quite terrible.
I've had a couple support requests about this. The common theme seems to be the customer is using Mimecast, and the fix is to add my sender address in a whitelist somewhere in their Mimecast configuration.
Which post is referenced here?
I also believe this to be a tooling issue. It's often opaque what ends up being run after I've done something in some framework (java jpa, django queries whatever). How many queries (is it n+1 issues at bay?), how the queries will behave etc. Locally with little data everything is fine, until it blows up in production. But you may not even notice it blowing up in production, because that relies on someone having instrumented the db and push logs+alarms somewhere. So it's easy to remain clueless.