Hacker News Reader

Comment by francislavoie | Hacker News Reader

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.

View on HN

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.

With foreign keys, I can't use https://github.com/github/gh-ost for zero downtime migrations. Instant deal breaker, because schema changes are inevitable given business evolution.

you can always use views + triggers for a zero downtime migration, this is a non-issue

Can you expand on this? I'm curious about the steps.

Sounds like it's basically explained in the gh-ost readme 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"

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.

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.

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.

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.

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.

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.

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.

> 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-fo...

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

because programmers are bad at DBA jobs. DBAs don't care about business logic.

And some companies don't hire DBAs so programmers do the things programmers know/understand

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.

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

This is what I said when I just started coding.

Reply on news.ycombinator.com