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

31–40 of 51 posts

Re: The challenges of supporting foreign key constraints

#31

Post by Knufferlbert »

bob1029 wrote: post #23

Every 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…

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

Re: The challenges of supporting foreign key constraints

#32

Post by financltravsty »

Exuma wrote: post #26

Can 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 dup…

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

Re: The challenges of supporting foreign key constraints

#33

Post by Exuma »

financltravsty wrote: post #32
Exuma wrote: post #26

Can 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 dup…

Postgres 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…

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.

Re: The challenges of supporting foreign key constraints

#34

Post by financltravsty »

Exuma wrote: post #33
financltravsty wrote: post #32

Earlier quoted context omitted.

Postgres 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…

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, however.

Re: The challenges of supporting foreign key constraints

#35

Post by ranting-moth »

bob1029 wrote: post #23

Every 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…

You should definitely put that on your CV because it is incredibly important information.

Re: The challenges of supporting foreign key constraints

#36

Post by _a_a_a_ »

saltcured wrote: post #30
dylan604 wrote: post #25

Earlier quoted context omitted.

I agree with your push back. Even in DEV, I'm not making daily schema changes. in fact, I hate schema changes and go back and forth on if the change is really necessary. sure, changes do become necessary, but sheesh, daily is a sign to me that something else needs to be looked at in the dev cycle. like, is nobody forward thinking enough to come up with a workable schema. is the requirements truly being made by the se…

On the other hand, a reluctance to do schema changes often just leads to a de facto schemaless system if new concepts start getting hacked into generic fields instead of properly modeled. When you make schema evolution really easy to do with no downtime, you can also start doing phased deployments where you think in terms of backwards-compatibility. First you add some new/optional parts to the schema. Then you update…

Maybe proper releases, running on different machines, might be a better option most of the time.

Re: The challenges of supporting foreign key constraints

#37

Post by AYBABTME »

irq-1 wrote: post #24
leetrout wrote: post #10

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

> At this time, foreign key constraint support is limited to unsharded/single shard databases.

The Venn diagram of people needing sharding and still using foreign keys is probably empty.

Re: The challenges of supporting foreign key constraints

#38

Post by winrid »

Exuma wrote: post #26

Can 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 dup…

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

Re: The challenges of supporting foreign key constraints

#39

Post by nick-sta »

Exuma wrote: post #26

Can 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 dup…

Have a look into singlestore - it seems like a nice fit for this use case.

Re: The challenges of supporting foreign key constraints

#40

Post by leetrout »

AYBABTME wrote: post #37
irq-1 wrote: post #24

Earlier quoted context omitted.

> At this time, foreign key constraint support is limited to unsharded/single shard databases.

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.
Post reply on HN