41–50 of 51 posts
Earlier quoted context omitted.
The Venn diagram of people needing sharding and still using foreign keys is probably empty.
Worth noting, the competing products from Neon and Cockroach Labs support FKs.
Neon isn't in the same league at all so I think what they support doesn't matter.
Earlier quoted context omitted.
Worth noting, the competing products from Neon and Cockroach Labs support FKs.
Cockroach supports it, but like for MySQL, PG, PlanetScale, or most DBs at scale, you shouldn't use them. Neon isn't in the same league at all so I think what they support doesn't matter.
Earlier quoted context omitted.
Awesome, thank you. That's kind of what I was thinking, I'm glad you confirmed it. How exactly did you sync or data from PG -> Clickhouse? I was considering using something like Airbyte, but then I thought this may actually be complex if PG rows are updating/deleting it means I also need to sync single rows (or groups of rows) to clickhouse, and I wasn't sure how the support was for that.
What I did in my case was setup stream replication to send over the Postgres WAL to another service that would update a ClickHouse cluster. Essentially, every time the WAL file is closed, a batch of all the SQL commands that were committed are sent over the wire. It might be easier to find some "change data capture" product that will do that for you though (like Airbyte). I can't give any recommendations here, howeve…
[0] - https://www.peerdb.io/
Earlier quoted context omitted.
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 diffi…
This is why in our design PlanetScale will not take upon itself to do this three step change. The user is more than welcome to break this into three different (likely they'll be able to make it in just two) schema changes. But then the user takes ownership of handling unchecked references.
> making data changes that might look invalid until all data changes are done
In effect, the data _will be_ invalid, and potentially for many hours.
Now, it's true that if the user messed up the data in between, then adding the foreign key constraint will fail, in both PostgreSQL and in MySQL. To me, this signals more bad news, because now the user has to scramble to clean up whatever incorrect data they have, before they're able to complete their schema change and unblock anyone else who might be interested in modifying the table.
Personally, my take is to not use foreign key constraints on large scale databases. It's nice to have, but comes at a great cost. IMHO referential data integrity should be handled, gracefully, by the app. Moreover, referential integrity is but one aspect of data integrity/consistency. There are many other forms of data integrity, which are commonly managed by the app, due to specific business logic. I think the app should own the data as much as it can. My 2c.
Earlier quoted context omitted.
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…
I have a rule not to revert migrations in production. If things have gone wrong enough that you need to rollback then there are all sorts of ways that trying to go back again could be bad news. My main worry is that I’d end up with 3 classes of data; pre migration, changed / added post migration and post revert. It’s probably fine in most cases, but that could take quite some unpicking.
As you complete the migration, PlanetScale keeps your old table, and continues to sync any further incoming changes to the table, back to the old table. They're kept in sync, apart of course from what incompatible schema changes they may have. As you revert, we flip the two, placing your old table back, but now not only with the old data, but also with all the newly accumulated data.
I completely appreciate the roll-forward approach. That's something we do for code deployments. Except when we don't, when there's that particular change where the best approach is to revert a commit, revert a PR. I think of schema deployments in the same way. Reverts will be rare, hopefully, but they can save the day.
Earlier quoted context omitted.
Worth noting, the competing products from Neon and Cockroach Labs support FKs.
Cockroach supports it, but like for MySQL, PG, PlanetScale, or most DBs at scale, you shouldn't use them. Neon isn't in the same league at all so I think what they support doesn't matter.
Earlier quoted context omitted.
Worth noting, the competing products from Neon and Cockroach Labs support FKs.
Cockroach supports it, but like for MySQL, PG, PlanetScale, or most DBs at scale, you shouldn't use them. Neon isn't in the same league at all so I think what they support doesn't matter.
Tell that to the people who have been successfully building massive databases in RDBMS for decades making extensive use of FKs and FK constraints.
Earlier quoted context omitted.
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 diffi…
Valid points! In my experience, when someone has foreign key constraints in their database, they tend to develop their apps in "trusting" way. Meaning, the app trusts the DB to maintain referential integrity. When you do the three step breakdown, you remove that integrity, and the app doesn't know any better: it keeps feeding the database with data, the database says "fine", and the app assumes referential integrity…
Is that mechanism unique to PostgreSQL? Or is it just that this transaction is impractical for you, due to the wall clock duration and how it impacts other use of the DB?
Earlier quoted context omitted.
Cockroach supports it, but like for MySQL, PG, PlanetScale, or most DBs at scale, you shouldn't use them. Neon isn't in the same league at all so I think what they support doesn't matter.
Also would be useful to add a disclaimer that you work for PlanetScale.
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.