[cube00]

> To resolve this, we ended up choosing to leave foreign key constraints unenforced on a few large tables.

> We reasoned this was likely safe, as Retool’s product logic performs its own consistency checks, and also doesn’t delete from the referenced tables, meaning it was unlikely we’d be left with a dangling reference.

I was holding my breath here and I'm glad these were eventually turned back on.

Nobody should ever rely on their own product logic to ensure consistency of the database.

The database has features (constraints, transactions, etc) for this purpose which are guaranteed to work correctly and atomically in all situations such as database initiated rollbacks that your application will never have control over.

[oppositelock]

It's difficult to make a blanket statement like this.

I've built some very high throughput Postgres backed systems in my years, and doing application side foreign key constraints (FKC) does have its benefits. Doing this client side will result in constraints that are usually, but not always in sync with data. However, this kind of almost-consistency lets you do much higher throughput queries. An FKC is a read on every write, for example, and does limit write throughput. Of course, this isn't ok for some workloads, and you do proper FKC in the DB, but if you don't need absolute consistency, you can make writes far cheaper.

[javajosh]

The trade-offs between foreign key constraints vs none are almost identical to the trade-offs between static typing vs dynamic typing. Nowadays people realize that when they turn off these features is that they'll eventually have to re-implement them later.

[WJW]

You make this claim as if this happens to every company sooner or later, but if a company the size of GitHub can still do without ( https://github.com/github/gh-ost/issues/331#issuecomment-266...) it does become a little bit of a "you do not have google problems" type discussion.

(Perhaps you do have such problems, I don't know where you work! But 99%+ of companies don't have such problems and never will.)

[bigbizisverywyz]

>But 99%+ of companies don't have such problems and never will.

Not sure where you get your metrics, but I would say a more general rule would be that the more people work on an evolving product that includes code and schema changes, then the more you need db constraints to enforce what it means to have correct data.

If only 1 or 2 people are involved in a db that changes pretty infrequently then possibly in the long term you can get away with it.

But if you have a constantly evolving product which must carry new features, logic changes and additions to the schema, then I would say you definitely need db constraints - FK and column. It only takes a few different developers to decide that T,F,Y,N,TRUE,FALSE,YES,NO,Null,NULL,None all mean the same thing, and you've got a slowly evolving mess on your hands.

[mb7733]

I don't think that is a valid comparison. Static typing doesn't reduce throughput. It's almost the opposite.

[javajosh]

That's true. They are similar in that they enforce consistency, but yes one takes resources in the steady state.

[phphphphp]

Does that pattern you describe require any considerations when writing code? I’m thinking of applications I’ve worked on where events are triggered by change, and so a database rolling back independent of my application would be a nightmare. I treat the database as a place to store data, not an authority: the application is the authority. Do you approach it differently? Thanks!

[brightball]

The database is the only place that can be the authority because the application can have race conditions. It’s the only way to guarantee data integrity.

[grogers]

There's no way to specify every single application specific constraint directly in the database. Race conditions are not present when using locking reads (select ... for update, or DB specific shared locking selects) or serializable isolation level, which are the typical way of enforcing application level constraints.

[kijin]

ON DELETE CASCADE can be dangerous when used with applications that expect to be notified of deletions, like in your case.

Ideally, everything that needs to change when a row is deleted would be changed automatically and atomically using database-side constraints and triggers. In practice, applications often need to sync state with external services that the database knows nothing about, so I understand your concerns.

ON DELETE RESTRICT, on the other hand, will result in errors just like any other query error that you can handle in your application. Nothing happened, so there's nothing to be notified of.

[icedchai]

You'd be surprised. I used to work on a product where the lead developer made sure foreign keys were NOT enabled on production. They were only "allowed" in dev. Some teams have a strict "no foreign keys" rule.

[tatersolid]

Does this mental giant know that modern databases use foreign keys to optimize queries, sometimes even eliding JOIN operations entirely if the schema, foreign keys, and other indexes make it safe to do so?

I’ve seen certain large-table queries improve by 10x when foreign keys were added.

[icedchai]

Hah. In his defense, this was MySQL 5.x... He also refused to allow "datetime" or "timestamp" datatypes anywhere. All timestamps were stored as bigints.

[evanelias]

These are actually very common restrictions at many of the companies with the largest/busiest MySQL installations in the world, typically OLTP workloads with extreme query rates.

Avoiding FKs enables sharding, reduces write locking time, and greatly simplifies online schema changes. These are essential requirements at extreme scale. (and in response to GP's point, at least in MySQL, FK constraints strictly harm performance; although the underlying index improves performance, you can have that without the FK constraint)

As for bigints for time values: storing UTC unix timestamps in a bigint avoids some issues with unexpected timezone conversions and DST conversions, as well as (in older mysql versions) unwanted auto-update behavior for the first timestamp column in a table. This one tends to be more of a "reduce support burden on the database team" type of requirement -- the risk of datetime or timestamp issues goes up as the number of product engineers massively outnumbers the db engineers, or once you have product engineers in many different timezones, data centers in many timezones, etc.

Of course, there are major trade-offs with these decisions. And they may or may not make sense for your former company's size and situation. But in any case these restrictions are not particularly unusual for MySQL.

[icedchai]

Totally... This is all valid. This MySQL install was over 10 years ago! At a previous company (about 15 years ago) we used FKs with MySQL and they did cause issues doing all the things you described.

[arthurcolle]

That is the dumbest thing I've ever heard

[icedchai]

I agree. This same guy also had other interesting database practices. I eventually quit because it was so bad.

[jasfi]

True, but the DB constraints often aren't enough. I like to have a verify program to check that the DB is as expected.

[weird-eye-issue]

You don't trust DB constraints?

[rattray]

They're limited in what they can express; your application often has invariants you want to enforce/maintain that can't be (performantly) expressed with DB constraints, and must be validated another way.

As great as it can be to enforce rules within the database, a lot of them usually end up needing to be enforced at the application layer instead. Especially when performance at scale comes into play.

[brightball]

I think it’s a balance. Transactions + Constraints can enforce most things but there will certainly be things that can only be verified in the app.

My goal is always to verify what I can in the database to minimize potential data cleanup. In my experience, app only verification always leads to future time investments to clean up the mess.

[jasfi]

Make no mistake, I think DB constraints are a best practice.

[WJW]

They have their place, but also their limits.

[PeterisP]

DB constraints can verify an important but inherently limited, simplified subset of data integrity.

For a crude example, it's trivial for DB constraints verify (via a foreign key constraint) that all your contracts belong to some customer, but very difficult for DB constraints to verify that all your currently active contracts belong to a currently active customer, even if the definition of 'active' is some relatively simple business logic.

So in my experience it's not that rare to have some code-based data integrity tests that run various sanity checks on production data to verify things that DB constraints can not.

[deathanatos]

Depends on the database, sometimes the database config, as to whether they'll actually be enforced or not, or in what situations data might evade enforcement of the constraints…

Applies to vendors, too. Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404. Had an AWS RDS instance; query on it returned no results. Adding the "don't use the index" index hint caused it to return data. (Allegedly, this bug was fixed, but we had migrated off by that point, so I never got to confirm it.)

Conversely, I do like DB constraints, because if the DB constraint doesn't exist, then I guarantee you the production DB has a row that is a counter-example to whatever constraint you think the data should obey…

[majewsky]

> Had some data in Rackspace Files where "list files" would say X existed, but "GET X" got you a 404.

Well yes, Rackspace Files (aka OpenStack Swift) is eventually consistent. It says so literally in the first sentence of the documentation [1]. But this discussion is about relational databases with ACID guarantees, where the C is literally "consistent".

[1] https://docs.openstack.org/swift/latest/