8 ms·
Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time
by mtts 4y ago
Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them.
Another classic is the “joins are slow” argument, which I believe goes back to a period in the late 1990s when in one, not highly regarded at the time, database, namely MySQL, they were indeed slow. But the reason “everyone” knew about this was precisely the oddness of this situation: in fact RDBMSes are highly optimized pieces of software that are especially good at combining sets of data. Much better than ORMs, anyway, or, god forbid, whatever you cobble together on your own.
There is, in my mind, only one valid reason to not use foreign keys in a database schema. If your database is mostly write only, the additional overhead of generating the indexes for the foreign keys may slow you down a little (for reading, these very same foreign keys in fact speed things up quite considerably). Even in such a case, however, I’d argue you’re doing it wrong and there should be a cache of some sort before things are written out in bulk to a properly setup RDBMS.
- tluyben2 4y agoWe used to do large setups at companies for what was then called intra and extranets begin 00s. These were very read/write intensive as the staff and partner staff would be on there basically all the time during office hours and data was not great for caching as data changed a lot especially in some companies like large hospitals and universities. We used mysql (I cannot remember why) and we did a lot of performance testing at that time; we removed all joins which made everything a lot faster. This is no longer the case now but indeed many people still believe it ; not (only) because they saw or tried it back then, but also because it’s less strain on the brain to just do single table selects and use not FKs or joins.
- function_seven 4y agoBack in the day I was forced to ditch FKs in my MySQL application, because I needed a FULLTEXT index on one of my columns, and MySQL only supported that type of index on MyISAM tables (this was on 5.x or something). MyISAM didn't do foreign keys. It was a pretty central table, and the inability to use FKs there kinda spread outward.
- edmundsauto 4y agoDid you consider making a 1-1 relationship on a new table that only had the FULLTEXT column? Curious how you evaluated the trade offs
- function_seven 4y agoI can't remember how much time I spent thinking about it, but if I were to reenact my state of mind at the time, I probably concluded something like, "Without transactions, I'll have to write more code to make sure the ID in both tables stays in sync, and I'll have to send 2 separate INSERTs (sequentially) for every record added, and if the first one fails, I need to handle that, and if the 2nd one fails, I need to handle that differently, and... fuck it. I'll just promise to be good and not use FKs" Or something. I can't remember the details, but I was (and still am) very averse to complexity in my application code.
- pharmakom 4y agoHow about when the ID in a FK column has been generated outside the RDBMS but the target of the ID has not been written yet?
- ashkulz 4y agoYou can use DEFERRABLE INITIALLY DEFERRED constraints so that the check happens when the transaction is committed.
- GoblinSlayer 4y agoI assume the target is externally generated too, thus can be legitimately absent.
- Cthulhu_ 4y agoI had this when importing test data; I found it acceptable (since it was just in development) to temporarily turn off FK checking.
- Semaphor 4y ago> Much better than ORMs I recently migrated to EntityFramework Core (from the non-core version) and I’m actually impressed. Most SQL is pretty much what I’d write by hand. Now granted, if there are complex joins, subqueries and stuff, I don’t even try wrangling the ORM to somehow give me that output, but still. I feel more comfortable just using EF than I used to.
- tailspin2019 4y agoAnother vote for EF Core here. It’s superb.
- rowanG077 4y agoYep entity framework is truly amazing. If you have used that ORM you never go back. You still need to sometimes make your own query for perf or other needs. But it's quite rare in my experience. Most of the time when I had performce issues it isn't EF. It's a missed index or higher level query issue.
- moonchrome 4y agoMy main problem with Entity Framework is the magic underneath. Like simple operation x = Ef.Find(xid) x.Name = "something" y = Ef.Find(xid) what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ? Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added as you're firing off other related queries, until eventually it does not (usually in production only). It's a really really complex system designed to look simple and pave over important details with "works most of the time" defaults.
- geraldwhen 4y agoThis is why I like TypeORM. There is no magic, and every command maps 1:1 with a database operation. No weird caching, no auto saves. Just an object mapper that you can use when you want and ignore when you need to.
- fifticon 4y ago
- tailspin2019 4y ago> RDBMSes are highly optimized pieces of software > Much better than ORMs These two things are not mutually exclusive though right? It’s entirely possible to have a lightweight and relatively transparent ORM which makes full use of the underlying RDBMS.
- sverhagen 4y agoYeah, I was going to say something similar. But ORMs get blamed for obscuring what's going on, to the point that a developer may end up doing some sort of inefficient 1-to-n lookup that would've indeed been much better off as a SQL JOIN. I use JPA/Hibernate professionally, as a decision maker, but I don't think I'm in either camp entirely. ORMs aren't a magic wand, but they do help you standardize the boilerplate that you'd end up with one way or the other, in most cases.
- tailspin2019 4y agoI definitely see that, and ORMs (particularly older ones) have historically made it easy to shoot yourself in the foot. But, everything is an abstraction, and I tend to think that if you use any abstraction, you need to have at least a little bit of knowledge about what’s happening in the layer beneath it. So using an ORM will not be an optimal experience if you don’t know how the underlying RDBMS works. And effectively using an RDBMS directly still requires a bit of knowledge about the layer below that level of abstraction too (eg how underlying query optimisation works etc). It’s possible to implement both incorrectly and get bad results and the opposite is true too
- ljm 4y agoAgreed, and there's a lot you can gain from an ORM/query builder just in terms of ergonomics or niceness for the 80% use-case. Doing intensive string manipulation to put your query together becomes painful, fast, especially when you're dealing with optional parts like ordering, limiting, filtering, pagination, etc. It's also incredibly easy to slip in an injection vulnerability as you do that (especially if you're new to programming). Just don't use it as a crutch because the declarative nature of SQL is vastly more powerful than an imperative wrapper and you'll be at a loss for only knowing the conventions and opinions of your ORM of choice.
- sonthonax 4y ago> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.
- lupire 4y agoTo be fair thata a reasonable approach if the database is at its monolithic scaling limit in CPU but not IO, while the clients can scale horizontally to more machines. Unlikely in practice, though.
- skyde 4y agooh so your talking about using the database as a file system and moving all the query logic in the client
- AdamN 4y agoI remember getting beers with somebody in the aughts who claimed that he saw an entire website where the url was the key and the webpage was the value in an Oracle database. Any code was SQL operations inside the value field.
- boatsie 4y agoIsn’t that effectively what a CMS is?
- brightball 4y agoI once had a coworker who dreamed of that exact setup.
- NoSorryCannot 4y agoThat's amazing!
- 10x-dev 4y agoAre joins in a 5NF database now as fast as querying a denormalized database?
- vbezhenar 4y agoOne reason to avoid FK is when your database is partitioned to multiple servers, but that's obvious, I guess, and it's not really RDBMS anymore.
- bartread 4y ago> Another classic is the “joins are slow” argument Along with the "indexes slow down INSERTs and UPDATEs" argument that you touch on. I mean, it is literally true that indexes make writes slightly slower, and an excessive quantity of indexes (which I have seen) can slow down writes enough to cause problems. But - in general - the slowdown is irrelevant compared with the overhead of querying a table that contains 2 billion rows using, oh, I don't know, a table scan because you don't have even a single index (I have also seen this).
- axylos 4y ago> Your coworkers argument about FKs making data migrations difficult is one of them. Got any arguments to back up this bald assertion? In particular, I'd love to hear more about how to manage schema migrations on large tables with FK's without incurring lengthy locks or downtime. Betting the answer is going to involve some variation on "well, don't do that" which is when I'll rest my case.
- roguas 4y agoThere are tools for live migrations for most popular databases. Also a lot of Postgres DDL is very fast and/or capable of happening live.
- radicality 4y agoA lot of it depends on the use case. For example, Facebook - one of the largest (if not the largest) deployments of mysql does not allow any FK constrains. There’s multiple reasons, but one of those is better predictability of db operational perf - a row delete should delete just the row and not potentially trigger N cascading deletes.
- goto11 4y agoCascading deletes is a separate from FK constraints. You can have FK constraints without cascading deletes.
- pindab0ter 4y agoI don't understand “a row delete should delete just the row and not potentially trigger N cascading deletes”. If you want that to not happen, then define that in the database definition. It sounds like you're saying that a core piece of functionality is somehow ‘wrong’, even though that same functionality can be used to make the desired bahviour for this exact use case explicit?
- skyde 4y agofacebook data model is a Graph where each row store one object “comment” or one association “comment is with post id” between objects . They made an query and indexing system on top of it to make it fast called TAO. Without it you need to send a distinct SQL query pet parent object to get list of associated child object which would be awfuly slow.
- radicality 4y agoNon-tao use cases of mysql at FB also cannot use FK constraints (or ‘triggers’).
- skyde 4y agoby "no FK contraints" do you mean "no join using index" or you simply mean foreign key violation is not checked.
- Gordonjcp 4y ago> in fact RDBMSes are highly optimized pieces of software that are especially good at combining sets of data. Much better than ORMs, anyway, ORMs are just a wrapper around RDBMSes. If your ORM is producing incredibly stupid SQL to query the DB with, you might want to check that you're not modelling your data in a stupid way. I am by no means an expert, but in general I have found that if the ORM is doing something particularly crazy, it's because my underlying assumptions about the data model is wrong.