It is amazing what Vitess / PS will help you accomplish but they don't talk a lot about tradeoffs you make to get there (which are similar to what you face with sharding MySQL without additional tooling).
For example, a multi-tenant application could have a tenants table with PK id, and an orders table with PK (tenant_id, id), and a FK from orders(tenant_id) to tenants(id). Then CockroachDB would keep the orders with tenant_id = 123 in the same machine where the tenant with id = 123 was.
I have the impression they deprecated interleaved tables, but I can't find more info about it.
https://github.com/cockroachdb/cockroach/issues/52009
Not only deprecated, but completely removed a few years ago as seen in:
I stumbled upon your DB service several times, and considered it several times suggesting it as the DB of choice for my customers (uxwizz.com), but the pricing is a bit confusing/hard to estimate in my case, which makes it hard for me to recommend it. Any suggestions?
Now that Vitess has native schema change tools it's more reasonable to revisit user-friendly, out-of-the-box foreign key support.
I'm also going to disagree with you a little, again on the issue of reading, if you have foreign keys then you can do optimisations like cutting out chunks of joins. Adding constraints means you know more which you can feed to the optimiser which means often you can get better performance (read performance, that is).
As to why you can't really have foreign keys, basically, sharding schemes like this sit at an intermediate layer between the application and the various DB clusters that are the shards. You CAN have strong data consistency (and useful foreign keys) within a shard because it's all inside a single database; however across shards, you CAN NOT. The sharding layer doesn't perform checks for you, so if you have two logical tables that are partitioned differently across the shards, you can't have a foreign key that will be enforced correctly as the foreign key of a given row in one table may live on a different shard in the other table. The local database within the shard would reject the insert. Transactions across shards can also be tricky to impossible.
You're conflating the concept of a normalized database with insanely slow DB-enforced referential integrity/foreign keys.
Toy example? Sure. Take a well-formed 3NF schema and disable foreign key constraints.
> Take a well-formed 3NF schema and disable foreign key constraints.
I'm familiar with 3NF, but can you expand on how 3NF enables you to remove foreign keys? Or feel free to point me to an article/blog, I don't want to waste your time if it's too much to explain. I did some googling but wasn't sure where to proceed from your post.
Something like SQL Server can enforce foreign key constraints if you explicitly tell it your relationships between tables. The downside is that having this referential integrity costs you performance as the database has to check your relations when inserting/updating/deleting rows. E.g. checking that a foreign key is pointing at a valid primary key, checking that you aren't leaving invalid foreign keys when deleting a primary key, etc. This is to prevent you inserting bad data into the database.
You can delete these constraints and still have the exact same behavior so long as your code is correct. It just means that the database isn't going to stop you writing bad data.
My sense is that many typical CRUD apps aren't writing gargantuan volumes of data or making very complex edits, and if they do, it's ok if it takes a second longer. Usually read speed is more of a bottleneck for user-facing applications, but I'm sure there are probably some examples where this tradeoff is worth it.
This is a pretty tough definition of correct, though. Without foreign key constraints you'll have a really tough time dealing with concurrency artifacts without raising your isolation levels, which generally brings larger performance concerns.
My experience is that if you have a moderate amount of foreign keys, a lot of DBMS (not Postgres) will refuse the `ON DELETE CASCADE` (in the diamond case), and you have to do it "manually" anyway (from your query builder).
I’m not talking about using cascade - this applies perfectly well to use of on delete restrict. FKs are more or less the only standard way to reliably keep relationships between tables correct without raising up the isolation level (at least, in most dbs) or doing explicit locking schemes that would be slower than the implicit locking that foreign keys perform.
user = {user_id, email}
order = {order_id, user_id}
Enabling RDBMS-enforced foreign key constraints is the issue. It slows everything down dramatically.
How slower is it really? MySQL automically creates indexes for foreign keys so I don't think it slows down "dramatically", just an additional indexed retrieval?
Do you know of any benchmarks which show the difference?
Table Resources: resourceid, userid, etc
If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.
select id from users where id = ? for update;
if row_count() < 1 then raise 'no user' end if;
insert into sub_resource (owner, thing) values (?, ?);
commit;
??
If we take postgres as an example, performing the select takes exactly zero row level locks, and makes no guarantees at all about selected data remaining the same after you’ve read it.
edit: my mistake - I missed that the select is for update. Yes, this will take explicit locks and thus protect you from the deletion, but is slower/worse than just using foreign keys, so it won't fundamentally help you.
further edit: let's take an example even in a higher isolation level (repeatable read):
-- setup
postgres=# create table user_table(user_id int);
CREATE TABLE
postgres=# create table resources_table(resource_id int, user_id int);
CREATE TABLE
postgres=# insert into user_table values(1);
INSERT 0 1
Tran 1:
postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# select * from user_table where user_id = 1;
user_id
---------
1
(1 row)
Tran 2:
postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# select * from resources_table where user_id = 1;
resource_id | user_id
-------------+---------
(0 rows)
postgres=# delete from user_table where user_id = 1;
DELETE 1
postgres=# commit;
COMMIT
Tran 1:
postgres=# insert into resources_table values (1,1);
INSERT 0 1
postgres=# commit;
COMMIT
Data at the end:
postgres=# select * from resources_table;
resource_id | user_id
-------------+---------
1 | 1
(1 row)
postgres=# select * from user_table;
user_id
---------
(0 rows)
This stuff is harder than people think, and correctly indexed foreign keys really aren't a performance issue for the vast majority of applications. I strongly recommend just using them until you have a good reason not to.
On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure…
Intriguing. Can you tell me more?
Sure, I meant an index in which the key is primary. But, on second thought, that's probably me misreading the GP's message.
> On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
That can definitely be a problem (just like destructor deallocation stampedes in C++ or Rust). Still less risky than cascading manually and asynchronously, I suspect.
I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your code, the DB won't allow it, and vice versa.
Plus, relational databases don't just sit under a single application. There's usually multiple applications/services talking to them. Worse, humans connect to them an do all sorts of things they shouldn't do. That's the whole point of managing referential integrity in the DBMS, since you can only control "just write good code" across so many application domains.
Of course whether the performance tradeoff is worth it is a complicated decision for many of the reasons people have mentioned. But in 20 years of working with relational databases at big companies, I've seen few examples where the performance win exceeded the business risk.
And as you point out, there are exceptions, like financial data. But not marketing funnels where you might throw everything away.
You should probably have different types of engineers working on such different projects as well.
I’ve seen several teams regret making this assumption. Unless you enforce draconian access control over a database, you’re going to discover that customer support and accounting and biz dev have been quietly relying on it.