5 ms·

Worked at a bank years ago. One of their DBAs eschewed FKs in production. Databases were designed with FKs which were enforced in Dev and QA. If your app surviv

Worked at a bank years ago. One of their DBAs eschewed FKs in production. Databases were designed with FKs which were enforced in Dev and QA. If your app survived testing (automated, QA team ... the entire gamut) without producing FK violation exceptions, your app could be promoted to production, where FKs were not enforced, making things pretty fast. I feel like this was a stopgap on the way to eliminating FKs. I have no idea what his roadmap was because I wasn't in that group. But his process made for good thought fodder.

OJFord 7y ago
Hm, that is 'good thought fodder'. My feeling on it though is that it just shifts all the faith to your test data being true to not only does but conceivably could happen in prod. All very well testing with a bunch of constraints, but if it turns out one actually isn't exercised and then happens in production? It also means you can't use them for validation or whatever. Which, I don't know, maybe you shouldn't anyway, but if you turn them off in prod you can't.
munk-a 7y ago
This approach is quite scary to me and I would have argued very vocally against their rejections of FKs. There are ways to rely on replication for read serving, periodic disabling of FKs during batch inserts, FK integrity checks on replications, ... these can all address the performance issues inherent in FKs, it's also (generally) quite possible to attempt to architecturally disentangle too large networks of interdependent data in an effort to reduce how large any single data store you are relying on will get. But disabling FKs in production is... eh. I'd much rather see the opposite, declare the FKs on a testing environment NOT VALID, run application code, VALIDATE CONSTRAINT those you'd marked and see if anything is in violation - ensuring that application code won't (in the normal course of operation) generally hit FK constraints can greatly reduce your performance hit from having them... then in prod you can turn them on and be confident in your comparably lighter performance loss to data integrity.
- barrkel 7y ago
  FKs add read locks to referenced rows. It limits concurrency and it is observable. FKs constrain your ability to incrementally widen 32-bit FKs once you go over 2 billion rows, if you start out with 32-bit PKs. Two concrete reasons to avoid FKs in production, or at least disable them for longer running transactions. I think this perspective is something you only get once you've run bigger databases in production.
  - spookthesunset 7y ago
    This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it is a permeant truth that applies to all database servers.
    - barrkel 7y ago
      FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.
      - jeltz 7y ago
        Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row. PostgreSQL has implemented this minimum level of necessary locking for quite many years now.
        barrkel 7y ago
        I never said anything about locking out reads. Did I? FWIW, I just tested in Postgres. Locks like I said it does: A: create table parent(id int, value int, unique(id)); A: create table child(id int, parent_id int references parent(id)); A: insert into parent values (1, 10); A: begin; A: insert into child values (1, 1); B: begin; B: select 1 from parent where id = 1 for update; B: (blocks) The situation in MySQL is worse because it'll block updates on any field, not just row locks. I have specific experience of this due to use of database locks at the application level to avoid deadlocks (different lock orders) and inconsistent updates (updates based on reads across multiple tables that may have separate racing updates) by locking rows up front. For understandable schema reasons, what is logically a parent entity is the natural thing to lock, but for understandable performance reasons, FKs to the parent entity are distributed through some fairly large tables.
asdkhadsj 7y ago
A neat idea for sure. However I'd be concerned that suddenly you have two different applications. Example, if you delete a row with a cascading deletion elsewhere, that will work on dev, but on prod it would leave dangling data. Though, I imagine you could simply not use cascading behaviors.. Neat idea though. Scary, but neat hah.
- jagged-chisel 7y ago
  No DELETEs - it's banking! But seriously, there are a host of issues one must overcome for this plan to work. And there were indeed managed permissions for DELETE. But ultimately, I wasn't aware of much of the implementation detail ...

OJFord 7y ago

Hm, that is 'good thought fodder'. My feeling on it though is that it just shifts all the faith to your test data being true to not only does but conceivably could happen in prod. All very well testing with a bunch of constraints, but if it turns out one actually isn't exercised and then happens in production? It also means you can't use them for validation or whatever. Which, I don't know, maybe you shouldn't anyway, but if you turn them off in prod you can't.

munk-a 7y ago

This approach is quite scary to me and I would have argued very vocally against their rejections of FKs. There are ways to rely on replication for read serving, periodic disabling of FKs during batch inserts, FK integrity checks on replications, ... these can all address the performance issues inherent in FKs, it's also (generally) quite possible to attempt to architecturally disentangle too large networks of interdependent data in an effort to reduce how large any single data store you are relying on will get. But disabling FKs in production is... eh. I'd much rather see the opposite, declare the FKs on a testing environment NOT VALID, run application code, VALIDATE CONSTRAINT those you'd marked and see if anything is in violation - ensuring that application code won't (in the normal course of operation) generally hit FK constraints can greatly reduce your performance hit from having them... then in prod you can turn them on and be confident in your comparably lighter performance loss to data integrity.

barrkel 7y ago
FKs add read locks to referenced rows. It limits concurrency and it is observable. FKs constrain your ability to incrementally widen 32-bit FKs once you go over 2 billion rows, if you start out with 32-bit PKs. Two concrete reasons to avoid FKs in production, or at least disable them for longer running transactions. I think this perspective is something you only get once you've run bigger databases in production.
- spookthesunset 7y ago
  This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it is a permeant truth that applies to all database servers.
  - barrkel 7y ago
    FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.
    - jeltz 7y ago
      Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row. PostgreSQL has implemented this minimum level of necessary locking for quite many years now.
      - barrkel 7y ago
        I never said anything about locking out reads. Did I? FWIW, I just tested in Postgres. Locks like I said it does: A: create table parent(id int, value int, unique(id)); A: create table child(id int, parent_id int references parent(id)); A: insert into parent values (1, 10); A: begin; A: insert into child values (1, 1); B: begin; B: select 1 from parent where id = 1 for update; B: (blocks) The situation in MySQL is worse because it'll block updates on any field, not just row locks. I have specific experience of this due to use of database locks at the application level to avoid deadlocks (different lock orders) and inconsistent updates (updates based on reads across multiple tables that may have separate racing updates) by locking rows up front. For understandable schema reasons, what is logically a parent entity is the natural thing to lock, but for understandable performance reasons, FKs to the parent entity are distributed through some fairly large tables.

asdkhadsj 7y ago

A neat idea for sure. However I'd be concerned that suddenly you have two different applications. Example, if you delete a row with a cascading deletion elsewhere, that will work on dev, but on prod it would leave dangling data. Though, I imagine you could simply not use cascading behaviors.. Neat idea though. Scary, but neat hah.

jagged-chisel 7y ago
No DELETEs - it's banking! But seriously, there are a host of issues one must overcome for this plan to work. And there were indeed managed permissions for DELETE. But ultimately, I wasn't aware of much of the implementation detail ...