12 ms·
The challenges of supporting foreign key constraints
- shlomi-noach 3y agoPost author here, happy to answer technical questions.
- guptamanan100 3y agoI am the other post-author, and I am available too.
- padre24 3y agoThis was a great read, thanks. Are there any plans to support recursive CTEs? What are the technical challenges there?
- guptamanan100 3y agoThank you for the compliment! We recently started adding support for CTEs in Vitess! You can check out https://github.com/vitessio/vitess/pull/14321 https://github.com/vitessio/vitess/pull/14321 if you want to see some technical details of the implementation. For now, we have added preliminary support by converting them to derived tables internally, but we believe that we need to make CTEs first-class citizens themselves of query planning, specifically because recursive CTEs are very hard to model as derived tables. Once we make that change, we can look towards supporting recursive CTEs. This however will take some time, but then, all good things do!
- padre24 3y agoAwesome news! We work with hierarchical data so it was a non starter for us.
- derekperkins 3y agoIf you don't need recursive CTEs on sharded databases, they'll work today. We're actively using them
- guptamanan100 3y agoOh, I see, that's unfortunate! Hopefully, we can remedy that though as soon as possible.
- jtriangle 3y agoSo how do you deal with orphaned child rows when reverting? I assume it's up to your users to deal with them or not? This very much seems like a clever automation for chosing when to care about foreign key constraints and not outright enforcement
- shlomi-noach 3y agoYes, you got that right! If you drop a foreign key constraint from a child table, and then follow up to INSERT/DELETE rows on parent and child in such way that is incompatible with foreign key constraints, and then revert, then the child, now again with the foreign key constraint, can have orphaned rows. It's as if you did `SET FOREIGN_KEY_CHECKS=0` and manipulated the data unobstructed. The schema itself remains valid, and some rows do not comply. It's worth noting that MySQL has no problem with this kind of situation. It never cares about the existence of orphaned rows. It only cares about not letting you creating them in the first place, and it cares about cleaning up. But it doesn't blow up if orphaned rows do exist. They will just become ghosts.
- saltcured 3y agoComing from a PostgreSQL worldview, I find this confusing. To me a foreign key constraint is about the referential integrity of a table, not an insert-time rule that can have loopholes to leave invalid data in the table. If the constraint is in place, I should be able to trust that any queryable data satisfies the constraint. Also from my PostgreSQL-infused worldview, it seems to me you are making your life too difficult by requiring a migration to make "one change" to a table or view. The brute force idiom I've seen for schema migrations is to break it into phases: 1. drop departing foreign key constraints 2. restructure table columns/types and values 3. add new foreign key constraints This is a bit like running with constraints deferred while making data changes that might look invalid until all data changes are done. But, it defers expression of the new constraints until the table structures are in place to support their definitions too, so it isn't just about deferring enforcement. The same strategy can be used for import scenarios to support schemas where there are circular foreign key reference constraints. I.e. tables are not in a strict parent-child hierarchy.
- obviyus 3y agoThe Planetscale blog consistently puts out such high quality posts. I can highly recommend following their YouTube channel as well. I’ve learned a ton from the incredibly well made videos Aaron puts out!
- guptamanan100 3y agoYep! Aaron is great, isn't he!!? I make it a point to watch all the videos he puts out. We appreciate your support!
- chrisjc 3y agoThanks for the recommendation. https://www.youtube.com/@PlanetScale https://www.youtube.com/@PlanetScale Subscribed.
- leetrout 3y agoEvery time people would champion vitess / PS as a drop in fix this the caveat that would slow them down or stop them. This feature truly is a game-changer for adoption IMO. Congrats on shipping to all involved!
- shlomi-noach 3y agoThank you!
- irq-1 3y ago> At this time, foreign key constraint support is limited to unsharded/single shard databases.
- AYBABTME 3y agoThe Venn diagram of people needing sharding and still using foreign keys is probably empty.
- _a_a_a_ 3y agoI've not met planetscale before and didn't understand why you're doing this. From the site: "Changing a table's schema is one of the most challenging problems in relational databases, and in MySQL in particular. In today's accelerated and rapid development cycles, engineers find that they need to make schema changes sometimes on a daily basis." Okay, but is this really true? Is it really necessary to have no downtime except in a very few cases? It's honestly rare to have systems that absolutely must stay up all the time, but allowing fiddling with the schema as well just seems excessive. I'd also say as software dev I'd be very uncomfortable with a system like yours that claims to maintain consistency while running and changing the schema, short of a formal proof.
- rdoherty 3y agoOnce your schema changes take more than a few minutes, yes. There's a lot of toil and burden if you need to take down your application every time you need a schema change. Announcements, coordination with internal teams and customers and then coordinating with other engineers. We aren't talking about zero downtime here, but continual, recurring downtime due to schema changes. Once you have beyond a few million rows in a normal RDBMS, schema changes can take minutes to hours depending on the type. Do this a few times per month and you now have 'lots' of downtime and you are blocking other engineering work from happening. It eventually becomes so much of a hassle that engineers don't want to do schema changes, blocking feature work. The more seamless and painless you can make them, the better.
- salawat 3y agoPerhaps we shouldn't be collecting and retaining such large datasets that these issues become such a pressing problem?
- paulddraper 3y agoInteresting, elaborate.
- klooney 3y agoI feel like you're implying that this is caused by personal data collection and tracking, but it's not- you can get there pretty easily, in a small to medium sized app, with just user tables, or users + things configured. The giant data lakes for vacuuming up tracking data generally never do schema migrations at all.
- bob1029 3y agoEvery time I turn on FK constraints I wind up regretting it and ripping them out of the schema. Not one time have I ever ran a non-query, received a constraint violation, and thought to myself "thank god, what a relief". Any time I am doing something that could violate some logical constraint, I am probably trying to fix something that is already fucked. The safety nannies simply add insult to injury at this stage. I will delete broken rows in whatever order I please. If constraint violations are adding value to your solution, you probably have a far more severe problem repeatedly slamming into the wall somewhere.
- perrygeo 3y agoStrange, I was just reflecting this morning how grateful I am that FK constraints are respected. I got a FK constraint violation and without it I would have introduced a bug in my app. Specifically deleting X was doing to delete many child Ys. If there are any downstream queries that assume the relationship between X and Y, and you accidentally violated that contract, wouldn't you WANT the database to tell you? Without a FK constraint in place, I would just have to know that I needed to update hundreds of lines of (incidentally) dependent code! It's roughly analagous to the static vs dynamic typing debate. Do you want to discover FK violations at runtime in the future? Or head them off at insert time? Either way, you cannot sweep referential integrity under the rug... you only shift the burden from one place to another. So I thank my database daily for it's work enforcing FK constraints. Otherwise I'd have to write that code! You can't ignore the lifecycle of references.
- falserum 3y agoThe benefit is the 100% guarantee that queried child has a parent. If code has many maintainers, pinky promises to keep referential integrity are bound to be broken eventually. Humans like shortcuts.
- Knufferlbert 3y agoWithout knowing details, I can only assume you are misunderstanding something. I and everyone I worked with have bugs prevented by FK constraints. They prevent getting data to be in bad state, instead of it piling up and expensively fixing it afterwards. Not once have I thought "I wouldn't have had this problem without FKs" and every time I thought "oh yeah, I forgot this path, that would have been a problem". Having to write code that can handle foreign key violations because the DB doesn't check it is a major pain. (we use Cassandra for example, so there is a "foreign key" usually from a PG row to a Cassandra row, obviously that can't be enforced on DB level so application code has to do the work) As for deleting/updating data, FKs can be a bit annoying, but postgresql for example has two (possibly more) options. 1) The (possibly dangerous) cascade delete, which will traverse the FKs basically for you and deletes them 2) The check FKs (and other constraints) on commit. I.e. instead of checking every delete/update statement causes FKs violations, it'll check at the end, after having done all the delete/update statements if there are any FK violations. (or update statements). Called deferrable constraints.
- Exuma 3y agoCan someone help me with a suggestion? Ive been researching databases now for several days straight, the choices are overwhelming but I've pretty much narrowed my use case down to an RDBMS system. I need to essentially handle 100's of millions of "leads" (and 10s of millions per day) which can make up any number of user fields. over 1B total I need to resolve duplicate leads either in realtime or near realtime. A duplication can occur across a combination of 1 or more fields, so basically OLTP type operations (select, update, delete on single rows) I do need to run large OLAP queries as well across all data I've looked at things like scylla and whatnot but they seem too heavy duty for my volume. it's not like i need to store trillions of messages like discord in some huge event log. I was considering these 3 options... 1. planetscale 2. citus 3. cockroachdb I havent really narrowed it down further than this, but i liked the idea of still having RDBMS features without needing to worry about storage and scaling with just sheer write volume. It seemed i could then do my basic OLTP stuff that i need, and citus had a cool demo how some OLAP query on 1B rows ran in 20s with 10 nodes, and that also fits a reasonable time for queries (BI tools will be used for that)
- winrid 3y agoPretty much anything will work at that scale depending on your SLAs. You could use Mongo with the higher compression option, add a couple indexes, and be golden. Just do the reporting off a live secondary. I store billions of documents in Mongo on unimpressive hardware (64gb ram, GP3 EBS), adding millions a day. Mongo isn't super fast at aggregations, though... What kind of aggregation queries? Can you do pre-aggregation? Citus would probably be my pick if you want SQL. Feel free to email.
- nick-sta 3y agoHave a look into singlestore - it seems like a nice fit for this use case.
- financltravsty 3y agoPostgres with upserts (and triggers if your de-duplication logic can't be handled on the backend)? OLAP works here, but is not great depending on how fast you need info to be available. If you're generating reports, Postgres is fine as long as your queries are properly optimized, and you can get them within minutes for massive workloads. If you need near-instant (sub 1s) results, I would recommend you sync your RDBMS to a columnar database like ClickHouse, and let the better data layout work in your favor, rather than trying to constrain a row-based DB to act like it's not. Otherwise, both are rock solid and simple to use. I've dealt with more intensive workloads than you mentioned, with the same use-case and Postgres worked very well. ClickHouse never had a problem.