2

I've been thinking about how to map user claims to permissions in a way that scales from a read and write perspective, probably using a stand-alone service. What we have today doesn't scale for reasons I'll mention in a moment. At the core, we have this:

  • User data
  • Object data (it's metadata about indexed content, which is irrelevant here)
  • Normalized data that maps users to the objects with read/edit/delete permissions
  • There are cascades on both sides, with users who inherit permissions from groups, and objects that inherit permissions from other objects

From a read perspective, this works beyond fine. I don't even know what the ceiling is for doing lookups across the user and object entities, but with tens of millions of rows it's a non-issue. The cracks happen when you need to update either side of the equation, but it's all kinds of bad when you delete a user or object, which can cascade across tens of thousands of records. If it matters, it's data in SQL Server, but as a future stand-alone service that maps these, I'm not married to any specific technology.

So the question is: What technology would allow me to quickly update these relationships? I would imagine that denormalizing the SQL could get me part of the way there, but eventually you need to clean up the 10,000 records associated with a user you deleted. I'm open to graph databases and such, though there's added cost for that because of new skills required. Being able to run the database in containers would be nice too, but that's kind of expected at this point.

  • I'm having a little trouble understanding what the problem is exactly on update and delete. Is it that the foreign keys that are slowing things down? Can you add a data model diagram? Even an informal one would help. – JimmyJames Aug 14 at 21:09
  • Yes, deleting or updating when you're using relational data creates huge cascades. Think of this: You have a group with many users, and many groups have access to many "folders" that contain many "folders" or objects. So let's do that math... 10 groups 10 sub groups 100 users in each group 10 folders 10 subfolders 10 objects... To be read optimized, you'll have 10 million rows associated with all of those combinations. – Jeff Putz Aug 15 at 1:11
  • OK, I think I get the design. You say that things are fine for reads and your challenge is updates/deletes. De-normalizing would only seem to make things worse on the update and delete side. Why is that under consideration. Am I missing something? – JimmyJames Aug 15 at 15:22
  • Denormalizing isn't the right word... more ditching foreign key constraints. At least in that sense you could have eventual concurrency, but again, this is all assuming that permissions are tightly coupled to the users and objects they correspond to. – Jeff Putz Aug 15 at 16:06
0

As you mention in the comments, one solution is to remove the foreign keys. You would need to make sure that all your queries join back to the root user table to ensure the permissions are no longer found from the other tables.

IMO this is a little ugly. Foreign keys can help with avoiding data problems. There's also a chance that eliminating the foreign keys could degrade query performance but I'm hesitant to make a strong claim either way on that. There are so many factors to consider and potential mitigation strategies.

A similar option that let's you avoid this is to flag the users as removed. You could do this by adding a column to the user table or with another table. You could even do things like changing the id to make it impossible to log in. Again, you need to make sure all the queries join back to this table and that you exclude these soft deletes in the query. A simple view could be useful here.

Again, as you have noted, you will need to clean up at some point. If you have reliable periods of lower usage e.g. middle of the night, you can create a batch job that will remove these records. Even if you don't have that, you can minimize the impact of the hard delete by working backwards up the dependency tree, deleting rows from the child tables first. You can switch the foreign key constrain to not allow cascading deletes to be sure you are doing it right. That way you aren't putting as much pressure on the DB by deleting such a large number of records in one transaction.

As a side note, you might also want to coordinate this with other DB maintenance activities. I've run into situations where a re-org is needed to truly release the space associated with data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.