Understanding database multitenancy

Filipe Ximenes
July 5, 2016
<p>Let's imagine we are going to build a system with the following constraints:</p><ol><li>Corporate clients will be using it;</li><li>The data that will be stored in it is very important and confidential. It cannot leak from one customer to another;</li><li>It should be able to scale for hundreds of clients;</li></ol><p>This is the type of system where we want to employ a <strong>multitenant</strong> architecture. Here is the definition in the words of Wikipedia:</p><blockquote>The term "software multitenancy" refers to a software architecture in which a single instance of software runs on a server and serves multiple tenants. A tenant is a group of users who share a common access with specific privileges to the software instance.</blockquote><p>Cool, now that we understand the concept, let's explore a little more how a multitenant system works so we can make good decisions when architecting our next project.</p><p>The first thing it's important to notice is the mention of "single instance of software" in the wikipedia definition. It is important to highlight this because it clears that the option of deploying multiple servers each one with its own copy of the codebase and a database does not characterise a multitenant system. In any case this architecture is not an option to us because it's not compatible with the third constraint we defined.</p><figure class="kg-card kg-image-card kg-card-hascaption"><img src="https://vinta-cms.s3.amazonaws.com/media/filer_public/56/94/5694846e-906a-4d7f-8b4e-fadb49abe1b8/not-multitenant.png" class="kg-image"><figcaption><em>This is not multitenant</em></figcaption></figure><p>Here are some of the terms we will be using across this blog post for quick reference:</p><ul><li><strong>Tenant</strong> - In our context this can be directly translated to "Customer".</li><li><strong>Schema</strong> - A 'blueprint' of how database tables relate to each other.</li></ul><p>That said, there are 3 traditional ways to architect a multitenant system:</p><h2 id="1-multiple-databases">1 - Multiple databases</h2><p>Although this would be considered a valid multitenant approach, deploying multiple databases caries almost all the problems regarding scalability and maintenance as the "one server per tenant" approach. The up side is that you will be maintaining a single codebase and routing each client to it's own private and isolated database.</p><figure class="kg-card kg-image-card kg-card-hascaption"><img src="https://vinta-cms.s3.amazonaws.com/media/filer_public/c1/a1/c1a11043-bc4d-4b7a-8394-4f2ff01f7d40/one-server-multiple-databases.png" class="kg-image"><figcaption><em>A single server connecting to multiple databases</em></figcaption></figure><p>Benefits of this approach include you being able to keep data from each customer 'physically' (not literally) isolated from each client and therefore more secure. Downside: you will need to provision a new database for every new client and make sure you connect to the correct address before making queries.</p><h2 id="2-single-shared-database-schema">2 - Single shared database schema</h2><p>In this approach we will have only one deployed database. In the database there will be a tenant table and all tables will be directly or indirectly linked to this table.</p><figure class="kg-card kg-image-card kg-card-hascaption"><img src="https://vinta-cms.s3.amazonaws.com/media/filer_public/a0/d7/a0d73fdb-7b69-48ef-a26a-36563f3feec6/one-server-one-database.png" class="kg-image"><figcaption><em>One server connecting to one databases</em></figcaption></figure><figure class="kg-card kg-image-card kg-card-hascaption"><img src="https://vinta-cms.s3.amazonaws.com/media/filer_public/39/72/397206f6-c74e-4246-b32d-87b5d9fc92d8/screen_shot_2016-06-29_at_70526_pm.png" class="kg-image"><figcaption><em>Single database schema with a Customer table</em></figcaption></figure><p>Having all your data in a single schema might be a good idea. It's easier to make data aggregations and do system wide queries. The downside is that you will need to be very careful when querying data to display to each customer. Every query will need an additional filter to guarantee it only returns results regarding the desired tenant, otherwise data will leak and we will be in trouble for exposing private information.</p><h2 id="3-single-database-multiple-schemas">3 - Single database, multiple schemas</h2><p>Most of the popular relational databases available support the creation of multiple schemas inside a single database. This is almost like having multiple sub-databases. Each schema is isolated from the others. Since they are not tied to each other they can even evolve differently. To model our initial system, we would place the tenant table in the database shared schema (let's call it public schema). For each entry in the tenant table we will create a new schema and store its name. With the schema name we will be able to make scoped queries to each tenant schema.</p><figure class="kg-card kg-image-card kg-card-hascaption"><img src="https://vinta-cms.s3.amazonaws.com/media/filer_public/02/40/02404660-730e-4b59-bc46-c976db129cfc/sgyn9ho78u9g1yln0jglqdg.png" class="kg-image"><figcaption><em>A database with multiple schemas</em></figcaption></figure><p>This approach has the benefits of having a single shared database (which makes it easier to maintain and scale) and at the same time keep data from each client isolated. Development also becomes a lot easier since you will not need to have additional scope filters in every query.</p><p>This was an introductory walkthrough to the subject. I hope now you have a clear understanding about what is a multitenant architecture and the approaches you can take to achieve it.</p>

Related articles

Browse all articles

Why buying tickets on airlines platforms is always a bad experience?

What our design team learned running remote usability tests

Why Should Every Software Development Team Have a Product Designer?

We use Cookies to promote our services. Settings and Privacy.
Accept
Close Cookie Preference Manager
Cookie Settings
By clicking “Accept all”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage and assist in our marketing efforts. Check our privacy policies.