Secure, Scalable, Multi-Tenant SaaS and ORMs
A couple of years ago I had an idea for a simple, multi-tenant SaaS application. It would track building occupancy and have a simple data structure and UI. At a high level it would consist of a few levels deep of relational objects and have a simple API to allow users to send in the number of people entering and leaving via an entrance and track the number of people in the building to make sure the building was following local regulations.
Thankfully, I was fully employed and kept busy as what I was doing was not impacted as greatly by world events as others were, so I just wrote down some high-level architecture notes and left it at that. Nowe, however, I have some time, so I decided to use that SaaS idea to bring my skill set up to date with the latest technology, something I was sadly unable to do on my last job as part of my work.
I sketched out some more details, dug into Blazor, EF Core and had proof of concept up and running quickly. Then I started working on turning that concept into something that would properly scale and be secure, which is when I was reminded of all the things that make ORMs less helpful than they could be for larger applications.
There are several rules I have learned the hard way over the years that impact scalability with databases. Two of those are related to something ORMs love, Foreign Keys; specifically, enforced foreign keys. Related to enforced foreign keys, cascading deletes.
Enforced Foreign Keys and Scalability
Foreign keys are fantastic and make relational databases work. As a concept, enforceable foreign keys are great, as a tool within a large, scalable multi-tenant database, however, they tend to have performance issues. While I always define the data as a column in the table, as a rule I do not define that column as a foreign key within the schema. If I do, I turn off enforceability. That does mean that the middle ware needs to do a bit more work, and there will need to be back-end processes that validate the data on a regular basis.
What you gain, however, is a much more scalable data store. Look at this simple table that does nothing more than store the data generated whenever a user logs the number of people entering or leaving a location via a defined entrance:
Technically, the EntranceCounterId is the only key that needs to be on this log table. The LocationId, OrganizationId and EntranceId could be determined from the relationships of Entrances to Locations to Organizations to Entrance. So why are they there? Having all four Ids in this table means data is more easily accessible for online reports without having to handle joins. The downside to this is that, when writing the data to the table with enforceable foreign keys, SQL Server needs to validate that all four values exist in their respective tables, which means touching the Entrances, Locations, Organizations and EntranceCounters tables every time the OccupancyLogs table is written to, and there will be a lot written here.
As I know that the middleware has already verified that the ids are valid, I can turn off the validation and save the SQL Server from having to check on every write, turning this into a simple one table insert, which is very performant. Which leads neatly to the second topic of this post:
Deleting Data is Bad
Data does need to be deleted, for a lot of reasons. Having said that, when you are running a Scalable, Secure Multi-Tenant SaaS application, you need to be firmly in control of when that data is deleted as well as how much data is deleted. Cascading deletes is a prime set up for performance issues.
Blocking
Just as an example, let’s say the occupancy tracker has a large grocery store chain as a client, with 3,500 large stores. Each store has 3 entrances, each entrance has at least one counter associated with it, resulting in over 24,000 entries, not including any OccupancyLog data. Now, let’s say that an admin decides to delete that grocery store chain. It could be an accident, or it could be that the store is no longer using the application. If you are using an ORM, the default configuration is to use foreign keys with cascading delete turned on, which means the server is going to attempt to delete that organization and all associated data. Even with a server that has a lot of horsepower, that operation is going to lock up affected tables, and that locking will prevent the application from functioning.
Or let’s look at a slightly less all-encompassing issue and a client’s busiest store that has four entrances and a lot of traffic is deleted. The cascading delete could impact the Entrances, EntranceCounters and OccupancyLogs tables which would render the application inoperable again.
For this reason, marking data so it is no longer available is preferrable. With a properly architected system, once an object is marked as not being active, all children should respect that. Which means that one row in one table is updated, and the thousands of rows of data below do not have to be modified. Yes, it is more work in designing and implementing, but it pays dividends in performance.
Human Error
The second reason that deleting data is bad has less to do with performance and scalability and more with maintainability and human interaction. When you delete data, it’s gone. Well, it might be possible to reconstruct the data from logs or similar metadata, but it will not be easy. And the number of times that users have deleted data that it turns out they didn’t mean to is higher than you might think. Even if the UI has warnings and multiple dialogs asking if the user is really, truly, certain this is what they want to do, they will mistakenly delete the wrong thing. Having an effortless way to recover that data by setting a value in a column in the database makes life far easier.
Next Steps
Having fully explored EF Core and what it can and cannot do for me on this project, I’m going to start once more. I have all the data storage, APIs and general screen design taken care of, so I should have the functioning code deployed within the next week. Then I’ll be adding more here about design choices, best practices and more.
If you have an interest in Secure, Scalable, Mulit-Tenant SaaS Architecture and Best Practices, subscribe.