[Philip-J-Fry]

He's talking about FK constraints right?

We do not use them for write performance concerns.

There's plenty of nice features of the DB we're not allowed to use under the excuse of "performance". But I'm told this by people who live and breathe SQL, so I trust them and I hope they have evidence to back it up. Because a lot of these features we're not allowed to use would make our lives 100x easier if we could!

[connormcd]

If you've got a bit of time to spare, here's a 30min walk through of physical design in the database. https://youtu.be/x0P4zAptTiA

In this talk, we start at 10,000 transactions per second, and just by altering the design we get to 20,000 transactions per second... all on a 5 year old laptop.

And at no time did we ditch any constraints (primary or foreign). The claim that you can't get performance with constraints on a database is a myth

[bawolff]

I mean, was that 20,000 transactions that are likely to have lock contention related to foreign key constraints? Because otherwise you are measuring the wrong thing.

[connormcd]

What kind of lock contention is that? (In Oracle) you'd have to be (a) manipulating the values of a primary key and (b) choosing not to index the child FK column.

Even without (b) I'd be asking "Why are you altering primary keys?" because it pretty much aint a primary key anymore if you're doing that :-)