[xupybd]

No foreign keys to make migrations easier. That doesn't sound like the best trade off to me.

Having the database constrained as much as possible makes maintenance so much easier. Many bugs don't escape into production as they're caught by the database constraints. Those that do get out do less damage to the data.

I know scale comes with trade offs but that seems extreme to me.

[derekperkins]

I'm a Vitess maintainer and I feel the same way. I don't plan to use any of the Online DDL because you'll have to pry my foreign keys out of my cold, dead hands. I understand the reasoning and limitations, but like you, the trade-off isn't worth it to me.

[anglinb]

I'm so curious, so you maintain Vitess but don't use it personally?

[derekperkins]

I do use it in production and have for years, just not the online schema changes. It's fantastic and FKs are supported in a single shard, which we use heavily.

[romero-jk]

If you take out online schema changes and sharding, what's the use case for vitess?

[derekperkins]

Architected correctly, there's minimal need for cross-shard foreign keys. A common use case is sharding by tenant/customer id, which means that all records for a single customer live on a single shard. That lets you have all the FKs that you want, and any operations for that customer happen on a single shard, which gives you maximum speed and transactional guarantees.

[Something1234]

If you take sharding out what's the point of using anything other than a normal sql database?

[derekperkins]

Vitess offers a lot of quality of life improvements over stock MySQL, including built-in backups to S3/GCS, managed replication, plus soon to be auto-failover detection. Additionally, with vreplication, you can do some pretty powerful materialized views that aren't possible with MySQL. Finally, Vitess Messaging is an awesome way to do async work, allowing for transaction guarantees where you can ack a message, do data work, then add to another queue, all without having to deal with weird side effects.

[leetrout]

What are your thoughts on Citus and Cockroach where foreign keys are still supported when creating partitioned clusters?

Is due to fundamental differences in postgres vs innodb?

[derekperkins]

Vitess still supports foreign keys (single shard), using MySQL, just not with the Online DDL functionality.

I think Cockroach tries to be a little too magical, which is great for starting up a cluster, but I think you can architect for much better performance with Vitess and owning your sharding model. I'm also very happy to use InnoDB, one of the most battle tested db engines to ever exist, while Cockroach is currently rewriting theirs from scratch. At the distributed level, I don't know of any massive scale adopters of Cockroach yet, though I'm not 100% looped in, so forgive me if I'm ignorant of them. On the other hand, Vitess has seen adopters like Slack, GitHub, Square, HubSpot, YouTube, with many more in various stages of adoption.

I feel like Citus might be trying to be too many things and so hasn't gotten the traction that Vitess has. Vitess has nailed OLTP at scale, while Citus is trying to also do OLAP and be a single source. That's the holy grail, but I'm not sure that any technology is close to handling both of those well yet.

[dhosek]

It's possible during a migration to drop a constraint, make the update and restore the constraint. If a schema migration tool doesn't automate this or at least permit it, it's not a good schema migration tool.

[cerved]

oh no, foreign keys are useless because it's the apps responsibility to delete

/s