Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The challenges of supporting foreign key constraints

planetscale.com

Post reply on HN
Print view View original

11–20 of 51 posts

Re: The challenges of supporting foreign key constraints

#11

Post by padre24 »

guptamanan100 wrote: post #9
padre24 wrote: post #8

Earlier quoted context omitted.

This was a great read, thanks. Are there any plans to support recursive CTEs? What are the technical challenges there?

Thank you for the compliment! We recently started adding support for CTEs in Vitess! You can check out 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 r…

Awesome news! We work with hierarchical data so it was a non starter for us.

Re: The challenges of supporting foreign key constraints

#13

Post by guptamanan100 »

padre24 wrote: post #11
guptamanan100 wrote: post #9

Earlier quoted context omitted.

Thank you for the compliment! We recently started adding support for CTEs in Vitess! You can check out 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 r…

Awesome news! We work with hierarchical data so it was a non starter for us.

Oh, I see, that's unfortunate! Hopefully, we can remedy that though as soon as possible.

Re: The challenges of supporting foreign key constraints

#14

Post by chrisjc »

obviyus wrote: post #6

The 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!

Thanks for the recommendation.

https://www.youtube.com/@PlanetScale

Subscribed.

Re: The challenges of supporting foreign key constraints

#15

Post by _a_a_a_ »

I'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.

Re: The challenges of supporting foreign key constraints

#16

Post by rdoherty »

_a_a_a_ wrote: post #15

I'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…

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

Re: The challenges of supporting foreign key constraints

#17

Post by salawat »

rdoherty wrote: post #16
_a_a_a_ wrote: post #15

I'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…

Once 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 i…

Perhaps we shouldn't be collecting and retaining such large datasets that these issues become such a pressing problem?

Re: The challenges of supporting foreign key constraints

#18

Post by paulddraper »

salawat wrote: post #17
rdoherty wrote: post #16

Earlier quoted context omitted.

Once 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 i…

Perhaps we shouldn't be collecting and retaining such large datasets that these issues become such a pressing problem?

Interesting, elaborate.

Re: The challenges of supporting foreign key constraints

#19

Post by phkahler »

_a_a_a_ wrote: post #15

I'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…

>> In today's accelerated and rapid development cycles, engineers find that they need to make schema changes sometimes on a daily basis.

Surely that's a bad design, and capability to support it is enabling continued bad design.

Re: The challenges of supporting foreign key constraints

#20

Post by saltcured »

shlomi-noach wrote: post #5
jtriangle wrote: post #4

Earlier quoted context omitted.

So 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

Yes, 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 val…

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

Post reply on HN