26 ms·

IMO foreign key constraints are bad; they make migrations and maintenance a nightmare. It's better to have application-level processes in place to ensure data c

IMO foreign key constraints are bad; they make migrations and maintenance a nightmare. It's better to have application-level processes in place to ensure data consistency. I don't agree that "no foreign key support" is a good reason to not use a particular solution. Pricing concerns are valid though.

Closi 3y ago
Depends how important consistency is - application-level only will mean it can potentially silently fail if there is a bug.
leononame 3y ago
That is a strange take tbh. For me, ensuring data integrity at the database level gives me a lot of peace of mind. Migrations are actually safer and easier because the database will complain if something's wrong, and at the application level I can do an opportunistic insert and if the FK is not valid, I'll get an error, which is really nice if I don't need the related record at all so I don't need to fetch it first.
- francislavoie 3y ago
  With foreign keys, I can't use https://github.com/github/gh-ost https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.
  - lazy_moderator1 3y ago
    you can always use views + triggers for a zero downtime migration, this is a non-issue
    - oblio 3y ago
      Can you expand on this? I'm curious about the steps.
      - nijave 3y ago
        Sounds like it's basically explained in the gh-ost readme https://github.com/github/gh-ost#how https://github.com/github/gh-ost#how I think it amounts to "use views to decouple access to the table with a fixed interface" and "use triggers for migrating data between tables"
    - nijave 3y ago
      It sounds like gh-ost is supposed to offer more control over the process but I question how many companies need that level of sophistication in DB migrations.
  - mijoharas 3y ago
    On postgres at least you can split creating the foreign key, and validating it into separate steps and ensure you have zero downtime Migrations. No idea about MySQL though, so maybe it's different there.
  - leononame 3y ago
    True, but going from there to "foreign keys are bad" is still quite a jump. There are other options available for migrations as well, but I understand that it can become a trade-off at scale, especially if availability and uptime are more important than data integrity. But as I said, it's a trade-off and I suppose in most situations you won't have noticeable downtime due to migrations or you can even avoid downtime altogether without ditching foreign keys.
  - dncornholio 3y ago
    Then don't use it. A migration tool that has no foreign key support is not a good migration tool. And it's even a worse reason to tell other people FK's are bad.
    - richbell 3y ago
      GitHub famously has a hard time managing their database and it's a source of frequent outages. Though, to be fair, they're also working with a massive volume of traffic.
  - evanelias 3y ago
    There are other options for zero downtime schema changes in MySQL and MariaDB. You can use the database's built-in support for instant DDL (algorithm=instant) for adding and dropping columns. And then use Percona's pt-online-schema-change for all other cases, since it supports foreign keys. Or if you're using a physical replication setup -- for example AWS Aurora without any traditional binlog replicas -- you can get away with using the database's built-in "online DDL" (algorithm=inplace, lock=none) for quite a few other cases. Historically the main problem with that method is that it causes replication lag, but that's a non-issue if you're not using logical replication in the first place. So then you only need to fall back to pt-osc for the rarer ALTER cases which don't support online DDL. MariaDB 10.8+ also has an option called binlog_alter_two_phase, which can allow you to use online DDL without causing much replication lag.
richbell 3y ago
There are situations where not using foreign keys makes sense. Most of the time, there is no benefit to not using them, only risk. Your database is the last line of defense for data integrity.
mijoharas 3y ago
How do you find fkey constraints making maintenance or migrations a nightmare? I've always found them reasonably pleasant to use on postgres.
- est 3y ago
  because programmers are bad at DBA jobs. DBAs don't care about business logic.
  - nijave 3y ago
    And some companies don't hire DBAs so programmers do the things programmers know/understand
- paulryanrogers 3y ago
  As tables scale you may need to partition them. FKs also introduce a performance it on writes and DDL. In some DDL cases a table rewrite may be required, such as if FKs cascade.
dncornholio 3y ago
This is what I said when I just started coding.
branko_d 3y ago
> It's better to have application-level processes in place to ensure data consistency. Good luck doing that correctly and with good performance, especially in concurrent and multi-client system. https://stackoverflow.com/questions/20842756/sql-indirect-foreign-key/20873843#20873843 https://stackoverflow.com/questions/20842756/sql-indirect-fo...

Closi 3y ago

Depends how important consistency is - application-level only will mean it can potentially silently fail if there is a bug.

leononame 3y ago

That is a strange take tbh. For me, ensuring data integrity at the database level gives me a lot of peace of mind. Migrations are actually safer and easier because the database will complain if something's wrong, and at the application level I can do an opportunistic insert and if the FK is not valid, I'll get an error, which is really nice if I don't need the related record at all so I don't need to fetch it first.

francislavoie 3y ago
With foreign keys, I can't use https://github.com/github/gh-ost https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.
- lazy_moderator1 3y ago
  you can always use views + triggers for a zero downtime migration, this is a non-issue
  - oblio 3y ago
    Can you expand on this? I'm curious about the steps.
    - nijave 3y ago
      Sounds like it's basically explained in the gh-ost readme https://github.com/github/gh-ost#how https://github.com/github/gh-ost#how I think it amounts to "use views to decouple access to the table with a fixed interface" and "use triggers for migrating data between tables"
  - nijave 3y ago
    It sounds like gh-ost is supposed to offer more control over the process but I question how many companies need that level of sophistication in DB migrations.
- mijoharas 3y ago
  On postgres at least you can split creating the foreign key, and validating it into separate steps and ensure you have zero downtime Migrations. No idea about MySQL though, so maybe it's different there.
- leononame 3y ago
  True, but going from there to "foreign keys are bad" is still quite a jump. There are other options available for migrations as well, but I understand that it can become a trade-off at scale, especially if availability and uptime are more important than data integrity. But as I said, it's a trade-off and I suppose in most situations you won't have noticeable downtime due to migrations or you can even avoid downtime altogether without ditching foreign keys.
- dncornholio 3y ago
  Then don't use it. A migration tool that has no foreign key support is not a good migration tool. And it's even a worse reason to tell other people FK's are bad.
  - richbell 3y ago
    GitHub famously has a hard time managing their database and it's a source of frequent outages. Though, to be fair, they're also working with a massive volume of traffic.
- evanelias 3y ago
  There are other options for zero downtime schema changes in MySQL and MariaDB. You can use the database's built-in support for instant DDL (algorithm=instant) for adding and dropping columns. And then use Percona's pt-online-schema-change for all other cases, since it supports foreign keys. Or if you're using a physical replication setup -- for example AWS Aurora without any traditional binlog replicas -- you can get away with using the database's built-in "online DDL" (algorithm=inplace, lock=none) for quite a few other cases. Historically the main problem with that method is that it causes replication lag, but that's a non-issue if you're not using logical replication in the first place. So then you only need to fall back to pt-osc for the rarer ALTER cases which don't support online DDL. MariaDB 10.8+ also has an option called binlog_alter_two_phase, which can allow you to use online DDL without causing much replication lag.

richbell 3y ago

There are situations where not using foreign keys makes sense. Most of the time, there is no benefit to not using them, only risk. Your database is the last line of defense for data integrity.

mijoharas 3y ago

How do you find fkey constraints making maintenance or migrations a nightmare? I've always found them reasonably pleasant to use on postgres.

est 3y ago
because programmers are bad at DBA jobs. DBAs don't care about business logic.
- nijave 3y ago
  And some companies don't hire DBAs so programmers do the things programmers know/understand
paulryanrogers 3y ago
As tables scale you may need to partition them. FKs also introduce a performance it on writes and DDL. In some DDL cases a table rewrite may be required, such as if FKs cascade.

dncornholio 3y ago

This is what I said when I just started coding.

branko_d 3y ago

> It's better to have application-level processes in place to ensure data consistency. Good luck doing that correctly and with good performance, especially in concurrent and multi-client system. https://stackoverflow.com/questions/20842756/sql-indirect-foreign-key/20873843#20873843 https://stackoverflow.com/questions/20842756/sql-indirect-fo...