[HN Gopher] How to Get Foreign Keys Horribly Wrong
___________________________________________________________________
How to Get Foreign Keys Horribly Wrong
Author : Bogdanp
Score : 46 points
Date : 2025-07-15 11:36 UTC (3 days ago)
(HTM) web link (hakibenita.com)
(TXT) w3m dump (hakibenita.com)
| jihadjihad wrote:
| > Django will implicitly add an index on a ForeignKey field
| unless explicitly stated otherwise.
|
| This is nice to know if you're using Django, but as important to
| note is that neither Postgres nor SQLAlchemy / Alembic will do
| this automatically.
| rrauenza wrote:
| How can we determine if an index can be satisfied by a constraint
| index?
|
| For example, does the FK need to be the first field in a unique
| together?
| cogman10 wrote:
| This sort of thing hasn't really done much to make me like ORMs.
|
| It seems like a lot of code to generate the tables in the first
| place and you STILL need to read the output scripts just to
| ensure the ORM isn't generating some garbage you didn't want.
|
| That seems like a lot of extra effort when a simple migration
| service (such as liquibase) could do the same work running SQL
| directly. No question on "which indexes are getting created and
| why". No deep knowledge of Django interactions with sql. Instead,
| it's just directly running the SQL you want to run.
| teaearlgraycold wrote:
| I would say automatic migration generation isn't a necessary or
| particularly important part of an ORM. ORMs are there to map
| your database relational objects to your client language's
| objects.
| cjs_ac wrote:
| I think the person you're replying to is arguing for using
| some sort of database migration library _without_ using an
| ORM library. It 's the same position I came to recently.
| teaearlgraycold wrote:
| Yes but they seem to have switched because they didn't like
| ORM-generated migration code. I think that's a bad reason
| to switch because it wasn't an important part of ORMs in
| the first place. Basically, I want to know why they were
| even using ORMs before.
|
| I don't want to go without an ORM because I'll end up
| making one ad-hoc anyway. I'm not going to do work on raw
| tuples in my application code.
| Tostino wrote:
| I'd call it an anti-feature for most long-lived projects that
| will end up needing migrations through its lifetime.
|
| I go the liquibase route for migrations, and just use the
| mapping portion of any ORM.
| pphysch wrote:
| Most(?) devs nowadays are introduced to database migration
| tools as a DX feature.
|
| "Wow, 1-2 command and my app and database are in sync!"
|
| In reality, migration tools are 100% about data loss
| prevention.
|
| If you do not care about data loss, updating your schema is
| trivial, just drop everything and create. Dev environments
| should be stateless anyways, using separate data "fixtures"
| when needed.
|
| Data loss itself is a highly nuanced topic. Some data is
| replaceable, some might be protected in a separate store. So
| I agree that ORMs should challenge the assumption that
| automatic migration tools need to be part of their kitchen
| sink.
| teaearlgraycold wrote:
| I like that they provide the basic structure of how to
| apply yet unseen migrations. But they don't need to
| generate the SQL at all. You quickly learn to never trust
| the generated code. It always needs to be manually
| reviewed.
| wvenable wrote:
| I _do_ read my migration scripts generated from an ORM to make
| sure my source code is correct.
|
| Liquibase starts with "Write your database change code in your
| preferred authoring tool in SQL, YAML, JSON, or XML." So
| instead of just having my ORM generate that and I just have to
| read them to ensure correctness, I have to _manually_ write
| change scripts instead? I don 't see how that's is comparable.
|
| Liquibase could certainly come in after I have some SQL scripts
| generated from my ORM and do whatever it does.
| aidos wrote:
| I've done a lot of interviewing and I've discovered that many
| devs (even experienced ones) don't understand the difference
| between indexes and foreign keys.
|
| My assumption is that people have used orms that automatically
| add the index for you when you create a relationship so they just
| conflate them all. Often they'll say that a foreign key is needed
| to improve the performance and when you dig into it, their mental
| model is all wrong. The sense they have is that the _other_ table
| gets some sort of relationship array structure to make lookups
| fast.
|
| It's an interesting phenomenon of the abstraction.
|
| Don't get me wrong, I love sqlalchemy and alembic but probably
| because I understand what's happening underneath so I know the
| right way to hold it so things are efficient and migrations are
| safe.
| whyowhy3484939 wrote:
| Very strange if you ask me and disturbing. I don't know if I'd
| let such a dev touch a database. Of course nowadays we just
| vibe code and YOLO everything, but still. This is making me
| feel old.
| bevr1337 wrote:
| > their mental model is all wrong.
|
| Is it? In Postgres, all FK references must be to a column with
| a PK or unique constraint or part of another index.
| Additionally, Postgres and Maria (maybe all SQL?) automatically
| create indexes for PKs and unique constraints. There's a high
| likelihood that a foreign key is already indexed _in the other
| table_.
|
| Generally, I agree with your statement. Adding a FK won't
| magically improve performance or create useful indices. But,
| the presence of a FK or refactoring to support a FK does
| (tangentially) point back to that index.
| ak39 wrote:
| By definition, a FK has to reference a PK in the "parent".
| UltraSane wrote:
| Yes. Not understanding the difference means you really don't
| understand the relational model. It would be like a network
| engineer not understanding the difference between IP and MAC
| addresses.
| Fishkins wrote:
| Huh, that's interesting. Mixing indexes and FKs is a major
| conceptual error.
|
| FWIW, I've also asked everyone I've interviewed in the past
| decade about indexes and FKs. Most folks I've talked to seem to
| understand FKs. They're often fuzzier on the details of
| indexes, but I don't recall anyone conflating the two.
| dakiol wrote:
| Is this for real? I don't know why anyone would deal with such
| amount of incidental complexity (django orm) when one can just
| use plain sql.
| twelve40 wrote:
| why is this so surprising? every place i worked at, going back
| probably 6 jobs, was using an ORM (django, hibernate, or even a
| self-built one), they went on to get acquired by Twitter,
| Microsoft, Uber etc, so not completely stupid or obscure. Even
| if you have a personal dislike of ORMs, if you ever work
| with/for another team with an exiting codebase and a DB,
| chances are you will have to work with one.
___________________________________________________________________
(page generated 2025-07-18 23:00 UTC)