Skip to main content

Strategy for Database choice in a multi-tenant application 

Introduction


Which database strategy do I choose from and what are the options on the table?


This post aims to help the developer / architect to choose the database strategy for a multi-tenant application
In a multi-tenant application, there are many areas or concerns that require attention like the cost, data isolation and protection, maintenance, and disaster recovery. 

Multi-tenant solutions can opt any strategy from 
one database per tenant (fully isolated) to 
one row per tenant (shared database) or to 
have separate schema per tenant in the same database 
(Shared server, separate schemas).

Strategy 1: Fully Isolated / database per tenant
  1. This approach is the most expensive option. (Higher the # of tenants, higher the # of servers)
  2. Highest degree of data isolation.
  3. Simplified Disaster recovery as there are separate backups and separate ALB's for the database.
  4. The point of view of maintenance is concerning because of the task of keeping the updates to the database in sync with the rest of the tenants. With the right DevOps plans, these can be tackled, but require effort in the automation, maintenance of the versions, upgrades across all the tenants.
  5. Given that "fully isolated" looks like easily customizable, can raise more maintenance issues. For example, A tenant might have a different pattern of application usage, which might suggest database tuning and this might not be applicable to the rest of the tenant's.
  6. Since we have only one tenant data, there will be lesser number of rows per table there by increasing the performance of the queries.
Strategy 2: Shared database / Shared Schema
  1. This model is having lower expenses because we have a single server.
  2. There might be questions on data isolation, however employing the right strategy can help tackle this problem.
  3. In this approach, each table has a TenantId column that identifies which tenant this row of data belongs to. Caution to be exercised to apply the TenantId filter always 
  4. Disaster recovery in this case is simple because there might be a case where the database is to be restored and upon restoring all the tenant's data will be restored. There is a single universal backup and restoration process
  5. The schema (table structures) can be easy maintained because its a single database and all tenant's use it. There are pain points in upgrades because the upgrades are global and need consent from every tenant and also there is lower chances of having a tenant specific customization.
  6. The volume of the data per table is higher, which might impact the queries performance.
Strategy 3: Separate schema / Tenant

  1. All the Tenants share a single database, but each tenant has it's own named schema. In this case, there are points to consider like the number of schemas per database and the partitioning of the schemas across multiple servers and identification of the servers for the choice of the schemas.
  2. This scheme has better isolation than using a "shared database", we have sql server level permissions the the schemas through GRANT, REVOKE etc
  3. The disaster recovery system is a bit complex depending upon the database server used because we need to restore the database schema alone and not the full database as such.
  4. The aspect of maintenance is similar to that of the "fully isolated" because we might have separate schemas and need some effort from the automation process point of view for deploying the upgrades.
  5. There are chances for performance degrade as the same server will be on load for all the tenant's.
It will be nice to have a table that consolidates all these points, let me build and update that to this post.

Labels:

Comments

Post a Comment

Popular posts from this blog

User Authentication schemes in a Multi-Tenant SaaS Application

User Authentication in Multi-Tenant SaaS Apps Introduction We will cover few scenarios that we can follow to perform the user authentication in a Multi-Tenant SaaS application. Scenario 1 - Global Users Authentication with Tenancy and Tenant forwarding In this scheme, we have the SaaS Provider Authentication gateway that takes care of Authentication of the users by performing the following steps Tenant Identification User Authentication User Authorization Forwarding the user to the tenant application / tenant pages in the SaaS App This demands that the SaaS provider authentication gateway be a scalable microservice that can take care of the load across all tenants. The database partitioning (horizontal or other means) is left upto the SaaS provider Service. Scenario 2 - Global Tenant Identification and User Authentication forwarding   In the above scenario, the tenant identification happens on part of the SaaS provider Tenant Identification gateway. Post which, the
Post a Comment
Read more

SFTP and File Upload in SFTP using C# and Tamir. SShSharp

The right choice of SFTP Server for Windows OS Follow the following steps, 1. Download the server version from here . The application is here 2. Provide the Username, password and root path, i.e. the ftp destination. 3. The screen shot is given below for reference. 4. Now download the CoreFTP client from this link 5. The client settings will be as in this screen shot: 6. Now the code to upload files via SFTP will be as follows. //ip of the local machine and the username and password along with the file to be uploaded via SFTP. FileUploadUsingSftp("172.24.120.87", "ftpserveruser", "123456", @"D:\", @"Web.config"); private static void FileUploadUsingSftp(string FtpAddress, string FtpUserName, string FtpPassword, string FilePath, string FileName) { Sftp sftp = null; try { // Create instance for Sftp to upload given files using given credentials sf
2 comments
Read more

Download CSV file using JavaScript fetch API

Downloading a CSV File from an API Using JavaScript Fetch API: A Step-by-Step Guide Introduction: Downloading files from an API is a common task in web development. This article walks you through the process of downloading a CSV file from an API using the Fetch API in JavaScript. We'll cover the basics of making API requests and handling file downloads, complete with a sample code snippet. Prerequisites: Ensure you have a basic understanding of JavaScript and web APIs. No additional libraries are required for this tutorial. Step 1: Creating the HTML Structure: Start by creating a simple HTML structure that includes a button to initiate the file download. <!DOCTYPE html> < html lang = "en" > < head > < meta charset = "UTF-8" > < meta name = "viewport" content = "width=device-width, initial-scale=1.0" > < title > CSV File Download </ title > </ head > < body >
Post a Comment
Read more

Implementing Row Level Security [RLS] for a Multi-Tenant SaaS Application

Row Level Security The need for row level security stems from the demand for fine-grained security to the data. As the applications are generating vast amounts of data by the day. Application developers are in need of making sure that the data is accessible to the right audience based on the right access level settings. Even today, whenever an application was built, the application development team used to spend a lot of time researching the approach, implementing multiple tables multiple logics 25 queries to add filters to manage the data security for every query that gets transferred from the end user request to the application database. This approach requires a lot of thought process, testing and security review because the queries needs to be intercepted, updated and the data retrieval to be validated to make sure the end-users see only the data that they are entitled to. Implementation With the advent of of row level security feature being rolled out in main d
Post a Comment
Read more

Async implementation in Blazor

Step-by-Step Guide to Achieving Async Flows in Blazor: 1. Understanding Asynchronous Programming: Before delving into Blazor-specific async flows, it's crucial to understand asynchronous programming concepts like async and await . Asynchronous operations help improve the responsiveness of your UI by not blocking the main thread. 2. Blazor Component Lifecycle: Blazor components have their lifecycle methods. The OnInitializedAsync , OnParametersSetAsync , and OnAfterRenderAsync methods allow you to implement asynchronous operations during various stages of a component's lifecycle. 3. Asynchronous API Calls: Performing asynchronous API calls is a common scenario in web applications. You can use HttpClient to make HTTP requests asynchronously. For example, fetching data from a remote server: @page "/fetchdata" @inject HttpClient Http @ if (forecasts == null ) { <p> < em > Loading... </ em > </ p > } else { <table>
Post a Comment
Read more