4 ms·
> 95% of every web app, mobile app, or native app is just very basic stuff. But 5% isn't, and can absolutely derail a project. I'm a consultant that goes to v
by jiggawatts 6y ago
> 95% of every web app, mobile app, or native app is just very basic stuff.
But 5% isn't, and can absolutely derail a project.
I'm a consultant that goes to various orgs to help them with a huge variety of things, much of which boils down to fixing issues with "simple web apps".
I literally cannot remember the last time I saw any application, written in-house or by a vendor, that had even the basic set of essentially mandatory indexes in the database. I don't mean clever stuff like partitioning, or sorting tables by a column other than the primary key. I mean that most of these databases had no indexes at all on 100M row tables. Just a week ago a large software vendor argued with me, stating that it's absolutely fine to require 10 GB of parallel table scans to display one web form to one user!
That in my opinion stems from a lack of understanding of what a B-tree is, how it works, and how databases utilise it.
That's firmly in the "fundamentals of programming" that every developer writing "simple web apps" should know, and in my experience almost none do.
These people go about their work every day with a firmly held belief that the database server is essentially a magic black box that can never be understood.
Is that okay?
- raspasov 6y agoNo it's not ok. But the people who didn't add indices maybe even had all the basic training on DS and algos. But they were simply either incompetent or more likely simply careless and didn't give a sh_t. Because adding indices in the proper places requires a few minutes of PLANNING. Who has few minutes nowadays, when you can read HN? On top of that their management/leadership was careless, and didn't give a sh_t. In the end, the poor database tables were left all alone, in the dark, without indices :).
- jiggawatts 6y agoI once had a friendly debate with a database professional. I argued that these days with storage being so cheap, databases should automatically create indexes. E.g.: anything referenced in a view or a foreign key, and also anything that occurs regularly in production. He said he thought that was a bad idea, because a tiny fraction of the time it could cause a performance regression. Now, years later, Azure SQL Database has automatic indexing and I feel a little bit vindicated. He does have a point that this can go wrong, but compared to what the typical developer does, it basically can't make things worse.
- raspasov 6y agoYes, you have a point. I think there’s no fundamental limitation today why everything can’t be indexed. There’s definitely some overhead to updating the indices on every insert of course, but that’s probably a relatively fixed ~O(1) cost in most cases.
- redis_mlc 5y agoWhat you're saying doesn't usually make sense because: - indexes can be unique or non-unique - covering indexes use multiple columns - indexes take space and increase time for most non-read operations. For AWS EBS, there can be a non-trivial network latency. However, if you mean that the database should monitor for table scans and usually add indexes, then maybe. MariaDB (MySQL) has some new features that I suggested that allow exploration of indexes performance by enabling and disabling them. My understanding with Postgres is that write amplification is noticeable as indexes are added (every index has to be touched for changes, while MySQL's Innodb never did that.) Source: DBA.
- mewpmewp2 5y agoProbably because they used their gut feeling/intuition which stems from experience, so they started suggesting arguments to support their gut feeling. Their gut feeling may be correct, it's just that they might not be able to see all the arguments or edge cases that may make this indexing a bad idea so they are concerned about implementing out of the box. It's very typical when suggesting ideas that go against the usual best practices, status quo, but it's understandable since you actually should consider all the edge cases before moving away from known ways of doing things.
- bsder 6y ago> But 5% isn't, and can absolutely derail a project. Precisely. Do you know something about byte order? Can you set or reset a bit? Can you check that your if-statement conditions are exhaustive? Those are from a second-year class in most CS curricula. And I have seen people programming for 10+ years screw them up. Horribly. I have seen 10+ year programmers use "addition" for "logical or". And it works, mostly, until it doesn't. And it creates mysterious failures that they can't debug because they don't even have the concept that they need to hypothesize correctly. Most of the people whining about this stuff are whining about whiteboarding things like "graph algorithms"--and they're right. That's a stupid thing to test for since you rarely use graph algorithms unless you use them every day at which point you'll come up the curve anyhow. If I'm interviewing, I can ask really simple questions like the "set a bit" or "change from network to machine order" and still get the information I need. If you know the answer, that tells me only one thing--you've probably programmed enough that you hit it. I'll keep increasing the problem difficulties until you don't know the answer and have to think. To be fair, if I have to wind the difficulty up to graph algorithm questions, you've likely already passed the interview long ago.
- mewpmewp2 5y agoI have done 6+ years of practical, full stack development, and I have never had to set a bit... What? In which case? If I had learnt it 6+ years ago, I would have forgot it by now. And I actually don't know how to do it without Googling, yet I have been able to be top performer in my current company for many years, promoting very quickly and providing tons of value. Maybe they have assessed me wrong in some way. I have never ran into such a bug, and I have never had a debugging issue that took too long to solve because of my lack of knowledge of computer science fundamentals. To clarify what full stack development has been about for me: 1) Single page apps/UIs 2) Managing hosts/containers/load balancing/scaling 3) APIs, for the UI mostly 4) CI/CD 5) Databases, querying, setting up, configuring. Basically anything you need to spin up a web app from 0 to 1. These systems have provided actual value to customers and have been performant to scale with demands. They have been providing enough actual value to justify my salary many times over which can be proven using raw numbers. I think if I ever had to set a bit, it means I have chosen bad tech/framework/wrong abstraction for what I'm trying to build, which is much, much worse than not knowing how to set a bit, in my view. If the tech I chose required setting a bit and similar other lower level things, it sounds like it would take much, much more time to actually develop it and it would be much more error prone, making it ultimately a very bad decision.
- mewpmewp2 5y agoKnowing to use indexes you should know, and I know it from practical experience, not from learning comp science or theory. However I don't know sorting algorithms top of my head and their complexities, since database does the sorting and heavy lifting underneath. This is the key difference. When I have gone through FAANG level companies, I spent 2 weeks preparing and learning the fundamental theory, a year or two later I have forgotten most of this and I'd have to prepare like that again. It's interesting and can be useful to go through the material on how database engine exactly works underneath, but you definitely shouldn't have to memorise this long after you went through the material and you probably won't use this information in your real life, except for the interview process, of course. Pretty sure indexes are basic things you learn when setting up your database schema, and most ORMs provide very easy way to do that and go over it in their getting started section. They usually have indexes by default for ID fields and foreign key fields. I had no problems understanding having to add indexes to often queried fields and the trade off for having too many indexes. When I started learning how to actually use something like MySQL I had no clue what data structure indexes used underneath, but the concept itself is fairly simple compared to how b-tree works. The vendors you have met have been incompetent for some other reason as opposed to not knowing data structures.
- gfxgirl 5y agoI agree with you fundamentals are important. My point was, leetcode and leetcode style interview questions don't test that. As an example I don't think there's a single "leetcode" question related to database indices.