Live data from Hacker News

Ask HN: Do you use foreign keys in relational databases?

news.ycombinator.com

221–230 of 251 posts

Re: Ask HN: Do you use foreign keys in relational databases?

#221

Earlier quoted context omitted.

On point 3 it should be noted that it's almost always a mistake to optimize for scale at the start of a projects lifetime. There will be exceptions, but in general this is true. You can always migrate that data to a more useful format if you find it starts hurting you at scale, if you start with the assumption you need the scale you're hurting yourself in the here and now for theoretical future benefit. > The real re…

This perspective only makes sense of you assume that designing a scalable system requires MORE work. My experience is that designing a scalable system requires LESS work if you and your team have the right skillset. In most cases, I can build a scalable system faster than I can build a non-scalable one with the same feature set. It would make no sense for me to implement the lesser alternative if it requires the same…

I'm always leery of people who claim to be senior and have never spent 3-5 years on the same system, and this attitude is why.

It takes at least that long to really start surfacing the design errors that were made that kills productivity long-term in a system. As a result I very often will claim the difference between a skilled and unskilled developer is the ability for a system they built to be reasonable after 5+ years without everyone involved wanting to rebuild the entire thing from scratch.

IOW, this is a fundamental difference in perspective. I was speaking to creating systems that are maintainable over the long haul by actively trying to control complexity. You're speaking speed of initial development.

Rich Hickey went on a small rant in one of his videos (I think the one describing datomic, but could be wrong) in which he pointed out many things that are fast initially will hurt long-term. I agree with that sentiment wholeheartedly.

The fact that you called the less complex alternative the "lesser" alternative speaks volumes. It honestly feels like the whole "mongodb is webscale" devbro culture rearing its ugly head.

Re: Ask HN: Do you use foreign keys in relational databases?

#222

Earlier quoted context omitted.

> It's clear you have never work with a lot of data. Sure, I only wrote an open source distributed pub/sub system with channel-based sharding which has been used by thousands of companies to support hundreds of thousands of concurrent users, but I guess 'lots of data' is a relative term.

That has nothing to do with data or data modeling.

Well that was just my hobby and side-gig... As part of my day jobs, I also worked on many projects with different databases including MySQL, Postgres, SQLite, MongoDB. I also implemented a side project (a distributed financial transaction processing system) using RethinkDB with per-table sharding and replication which runs on Kubernetes with statefulsets for persistence with automatic deployment and autoscaling and automatic database shard re-balancing with high availability with eventual consistency; I used a 2-phase commit algorithm for certain operations to achieve reliability in the event of write failure; as not to rely on atomic database transactions. I also did a course on relational database modeling at university (focused on ER diagrams and database normalization). I worked in the blockchain sector. I wrote a stateful, quantum-resistant blockchain from scratch including the cryptographic signature algorithm which uses an improved Lamport OTS variant suggested by Ralf Merkle and which uses a Merkle Signature Tree for key reuse and I contributed to the front end too. I also wrote a deterministic, fork-resistant, idempotent, heterogeneous multi-chain, chain-to-chain decentralized exchange. I also lead a team which wrote a P2P networking library with decentralized routing and efficient propagation of messages to peers belonging to the same subnets - Nodes in the network organized themselves into an unstructured, partial mesh topology with peer shuffling to avoid eclipse attacks but still retained the ability to form subnets based on the features they supported. But still, "a lot of data" a relative term.

Re: Ask HN: Do you use foreign keys in relational databases?

#223
post #36

Foreign key constraints are easy to remove if they become a problem but almost impossible to add once data integrity problems arise (and I've never seen them not arise in projects without FK constraints). The small number of people with high enough scale that they can't use them know who they are, the rest of us need to think carefully when performing database migrations and reason out the order of operations require…

> sounds like a good idea anyway? Heck, yes. If you are butting against constraints then you may have misunderstood the problem. I don't mind people turning constrains off for a mass migration under the following conditions: • This is not production, or if it is production you are in a maintenance window during which you have exclusive access to this DB • The data you are piling in will be verified against the constr…

RDBMSes don't do a lot of extra stuff, if they throw an error it's usually for a reason.

Re: Ask HN: Do you use foreign keys in relational databases?

#224

Earlier quoted context omitted.

This perspective only makes sense of you assume that designing a scalable system requires MORE work. My experience is that designing a scalable system requires LESS work if you and your team have the right skillset. In most cases, I can build a scalable system faster than I can build a non-scalable one with the same feature set. It would make no sense for me to implement the lesser alternative if it requires the same…

I'm always leery of people who claim to be senior and have never spent 3-5 years on the same system, and this attitude is why. It takes at least that long to really start surfacing the design errors that were made that kills productivity long-term in a system. As a result I very often will claim the difference between a skilled and unskilled developer is the ability for a system they built to be reasonable after 5+ y…

I tend to prefer combining data at the last moment on the client side rather than having it pre-combined on the server side (I prefer REST philosophy over GraphQL). It's probably because I'm web-application focused and so scalability and concurrency is far more important to me than raw execution time. Maybe if I was a data scientist or embedded systems developer, I would care more about execution time. I've met people like that. But IMO performant scripts tend to be the result of more optimizations which makes them harder to maintain as the underlying engines or hardware changes.

Re: Ask HN: Do you use foreign keys in relational databases?

#225
post #193

Earlier quoted context omitted.

A lot of it depends on the use case. For example, Facebook - one of the largest (if not the largest) deployments of mysql does not allow any FK constrains. There’s multiple reasons, but one of those is better predictability of db operational perf - a row delete should delete just the row and not potentially trigger N cascading deletes.

facebook data model is a Graph where each row store one object “comment” or one association “comment is with post id” between objects . They made an query and indexing system on top of it to make it fast called TAO. Without it you need to send a distinct SQL query pet parent object to get list of associated child object which would be awfuly slow.

Non-tao use cases of mysql at FB also cannot use FK constraints (or ‘triggers’).

Re: Ask HN: Do you use foreign keys in relational databases?

#226
post #193

Earlier quoted context omitted.

facebook data model is a Graph where each row store one object “comment” or one association “comment is with post id” between objects . They made an query and indexing system on top of it to make it fast called TAO. Without it you need to send a distinct SQL query pet parent object to get list of associated child object which would be awfuly slow.

Non-tao use cases of mysql at FB also cannot use FK constraints (or ‘triggers’).

by "no FK contraints" do you mean "no join using index" or you simply mean foreign key violation is not checked.

Re: Ask HN: Do you use foreign keys in relational databases?

#227

Earlier quoted context omitted.

My main problem with Entity Framework is the magic underneath. Like simple operation x = Ef.Find(xid) x.Name = "something" y = Ef.Find(xid) what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ? Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added…

This example is incomplete. We need to see what the enclosing transaction scope looks like.

That's sort of my point - when you see a random dbcontext read inside a function you have no idea what the fetch will actually do. It might just return an object that was already fetched elsewhere in the context and modified but not saved. It might return the first value. It will automatically plug related entities into navigation collections - even if they are queried completely independently.

Re: Ask HN: Do you use foreign keys in relational databases?

#228

Earlier quoted context omitted.

My main problem with Entity Framework is the magic underneath. Like simple operation x = Ef.Find(xid) x.Name = "something" y = Ef.Find(xid) what is y.Name ? Even though you didn't save anything to the database yet ? And the second Find didn't actually refresh from the database ? Oh and the random bugs where people improperly include related entities but it somehow ends up working because they are automatically added…

Once you move beyond trivial cases you really need to spend time understanding the principles behind the ORM you're using. They are always a very leaky abstraction, there is not really a way around that. In this case the important part to know is that the DbContext represents the unit of work and "knows" Entities you previously queried on it. That's very useful, but also can hide bugs like you mentioned with the Incl…

>They're also very complex and to make the best use of them you do need to understand both SQL and some basics on how your specific ORM generates this SQL.

I think the biggest pitfall is how it maps object model to SQL.

The thing people fear about SQL query generation - IMO it's a non issue - when you identify hotspots you write your query manually, tools for that are there, it's easy to do retroactively and >90% of the code won't be the critical path.

DbContext is basically shared mutable state between your entire execution scope, and worst of all it makes it non-obvious.

Re: Ask HN: Do you use foreign keys in relational databases?

#229

Earlier quoted context omitted.

I'm always leery of people who claim to be senior and have never spent 3-5 years on the same system, and this attitude is why. It takes at least that long to really start surfacing the design errors that were made that kills productivity long-term in a system. As a result I very often will claim the difference between a skilled and unskilled developer is the ability for a system they built to be reasonable after 5+ y…

I tend to prefer combining data at the last moment on the client side rather than having it pre-combined on the server side (I prefer REST philosophy over GraphQL). It's probably because I'm web-application focused and so scalability and concurrency is far more important to me than raw execution time. Maybe if I was a data scientist or embedded systems developer, I would care more about execution time. I've met peopl…

This has nothing to do with raw execution time.

Re: Ask HN: Do you use foreign keys in relational databases?

#230

Earlier quoted context omitted.

> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

Are joins in a 5NF database now as fast as querying a denormalized database?

Depends. Denormalized means the database contains redundant data. If a query have to scan 10x or 100x as many rows due to redundant data, it is obviously going to be slower. But it is hard to say anything general since denormalization will make some queries faster and other queries slower.
Post reply on HN