Hacker News Reader

Comment by Scarbutt | Hacker News Reader

The lack of FKs is a turn off for most apps.

I can understand that, for myself I don't miss them. An index on the column is just fine for me and personally I prefer to manage that in the application layer (I know I'm probably not in the majority). Lack of FKs is what allows for their scaling technique (using Vitess) from what I understand. I'll say for my use it's not been an issue but I do understand that migrating an app that does need/use them might be hard/impossible without other big changes in the code.

Looks like a must-have thing that is also missing from planetscale is point-in-time recovery.

PiTR is like the primary reason for us using a managed DB, it's indeed a weird and fatal omission from a managed DB offering.

Vitess supports PITR, so I'm sure it'll land in Planetscale sooner or later

Database-enforced FKs are an overrated crutch anyway. Apply the third normal form, utilize transactions correctly, and you won't ever need them.

Strongly disagree. They allow you to declaratively say what data is allowed. No system where devs write code is going to be infallible, no matter how much people want it to be true. Show me a database without FKs and I'll show you a database with orphaned rows and insistent data.

That being said, depending on the data you're working with, you may be fine with that trade-off.

> utilize transactions correctly

This seems hard to do in Vitess or PlanetScale given the technically READ UNCOMMITTED isolation [1] when cross shard, and, in scaled deployments, will still require experimental 2PC [2] cross shard transaction. So, like, yeah, if you had serializable isolation, then transactions might save you so long as your code isn't buggy, but, literally the reason the system doesn't implement them is because it doesn't have isolated transactions.

[1]: https://news.ycombinator.com/item?id=22170416#22177783 [2]: https://vitess.io/docs/13.0/reference/features/two-phase-com...

In a production environment with well architected sharding, that rarely comes up in practice. It will be designed so that 99% of your operations happen on a single shard, both for performance and for transactional guarantees. That's often a customer/tenant id, and it's rare in practice that you will be performing cross-customer transactions.

Vitess, the underlying tech, only disallows FKs if you use online schema changes. They have to be on the same logical shard, and it just uses standard MySQL FKs. Hopefully Planetscale allows them in the future, as long as you're willing to give up OSC. We've been running Vitess for years with that trade-off and it works great.

> They have to be on the same logical shard

This is a pretty major limitation. Part of the reason to use Vitess is to scale out. It is often very valuable to have a small number of root elements in a star schema and to have foreign keys which ladder up to them.

That's a different problem also solvable with Vitess. For those smaller tables, you can define them as a reference table, then have the same rows on every shard, so you can continue to have FKs to all of those.

https://vitess.io/docs/13.0/user-guides/vschema-guide/advanc...

When you're choosing your sharding keys, you want to design it where the bulk of your operations happen on a single shard, often a tenant/customer id. That guarantees that all customer data lands on a single shard, with FKs across every table you want.

We're running on ~40 shards across 6 keyspaces, and there are very few cases where we can't use FKs.

Reply on news.ycombinator.com