Questions tagged [multi-tenant]

A database architecture where multiple tenants share a single database or server. Also known as multi-client.

Filter by
Sorted by
Tagged with
1 vote
0 answers
23 views

On demand snapshot and CDC from Postgresql multitenant DB

I have a Postgresql multitenant DB where each table contains tenant level metadata. e.g. Table "connector" Column | Type |...
deGee's user avatar
  • 111
0 votes
0 answers
9 views

Incremental CDC from Postgresql for select rows

I have a postgresql DB which has tables containing multi-tenant data. Example: Table Customer CustId Cust Name First CustA Second CustB Table CustomerShoppingDet Item Name CustId. Buying Date ...
deGee's user avatar
  • 111
0 votes
1 answer
90 views

How do I launch sql script on every pdb using catcon with a SPECIFIC USER?

I need to launch some pl/sql on all pdbs in a cdb, loggin in as a specific user which doesn't exist on the cdb$root. I thought I could use the catcon perl script, but I tried in different ways and I ...
trustno1's user avatar
0 votes
1 answer
34 views

Postgresql - Create role to connect to database and revoke all other permissions

I have an application which is configured with the multitenancy strategy of "single database multi schema". Now I want to create a role which can have only specific permission that is to : ...
Subhajit's user avatar
  • 101
0 votes
0 answers
54 views

Set role for database connection in java (spring boot with multitenancy)

For every http request there is tenant identifier based on which the schema is switched (datasource switching). connection.setSchema(tenantIdentifier); Now to secure the data access I am introducing ...
Subhajit's user avatar
  • 101
1 vote
2 answers
89 views

Scaling from Multiple Database to Single Database Architecture in SQL Server

My application is centered around self-contianed "workspaces". For many really good reasons (everything from management to security), we have always had a one-database-per-workspace ...
Peter Moore's user avatar
0 votes
0 answers
28 views

How to fast alter table when a system isolates tenants' data by database?

I am developing a multi-tenany system. My senior staff suggest to isolate tenants' data by database, not by schema nor discriminator. My question is, if I have 1000 customers, therefore I have 1000 ...
wureka's user avatar
  • 159
0 votes
0 answers
14 views

Scaling MySQL Server for Handling High Volume Requests in a Multitenant Application

I am currently working on developing a multi-tenant application where I have implemented a backend that identifies the tenant from the header and connects to the appropriate database. However, I ...
user avatar
0 votes
0 answers
16 views

Implementing multi-tenancy

I have designed my DB and created all the necessary table, such as company, user, department, etc for a user management system. Now I need to make changes so that entities/tenants can register in ...
Helder Batalha's user avatar
6 votes
5 answers
3k views

SQL Server with multiple databases (one per client) - what is the best security practice in terms of logins/users/permissions?

We have multiple SQL Servers with each holding dozens of databases - one per client (a client in this case means a customer organization). These databases are accessed via an application, but the ...
SQL_Guy's user avatar
  • 655
0 votes
1 answer
182 views

How do I provide access to certain rows that are common for all users when implementing PostgreSQL Row Level Security?

We have a multi-tenant database and this is a table in which we have implemented row-level security based on tenant_id: tenant_id product_id 0 A 0 B 1 1A 1 1B 2 2A 2 2B Our requirement is that ...
user5706's user avatar
0 votes
0 answers
996 views

Handling multiple users/ databases in postgres

Whats the best way to organize seperate companies/users in postgres? I've developed a multi user platform, where users have access to only their companies data. I'm no DB engineer and initially the ...
Lewis Morris's user avatar
2 votes
1 answer
451 views

Aggregate data with multi-schema tenant architecture

We have an application that uses the tenant schema architecture, one database, has multiple schema per "tenant", each tenant is a replica with same tables etc. Sometimes we don't know the ...
adaba's user avatar
  • 121
0 votes
1 answer
533 views

ODA X8-2M crossrestore with TDE wallet fails with: DCS-10001:Internal error encountered: failed to open the tde password based wallet

We are trying to crossrestore a TDE encrypted Oracle Database 19.12 on a new ODA X8-2M. TDE wallet has been successfully backedup (odacli create-backup -in CDBET015 -c TDEWallet) and transferred to ...
r0tt's user avatar
  • 998
0 votes
0 answers
1k views

When cloning a PDB over dblink in Oracle 19c, is it possible to use file_name_convert without specifying entire paths?

Context Oracle version: 19.13 On server 1 I have a production database, with a CDB named (for the purpose of this question) CDBPROD and a PDB named PDBPROD. The datafiles are distributed in multiple ...
Anne-Laure's user avatar
-1 votes
1 answer
253 views

Deadlock and locks on multitenant database

I work for a saas company that uses multi tenancy by a tenant_id, however we perform nightly jobs that dump mass amount of data from the last day of each tenant, sometimes we face deadlocks or simply ...
beto's user avatar
  • 1
0 votes
0 answers
146 views

Add extra column to anyelement type - querying across multiple schemas with extra information

I'm trying to create a generic function which can be used to query across all of my schemas. I'm using schemas to separate my tenants, so they all have the structure. I found Staab's post useful: ...
Rory Anderson's user avatar
1 vote
1 answer
2k views

How can I Run stored procedures in public schema to provision and create tables in a new schema

I'm building a multi tenant application with shared database and different schemas(each customer has its isolated data in a schema) I have run a stored procedure that creates all tables and ...
Suleiman Bashir Shamsudeen's user avatar
0 votes
1 answer
375 views

Unable to create pluggable database on 19c using response file

I am trying to create a pluggable database using a response file, but it is giving me the error below, indicating that one of the mandatory parameters is missing. Although I have checked it multiple ...
mohd atif's user avatar
  • 109
3 votes
2 answers
870 views

SQL Server - Automatically full backup for new databases which are auto-created with EntityFramework-Migrations

I have an ASP.NET MVC Application with a Multi-Tenant architecture (using SQL-Server). I'm using EntityFramework DB-Migrations which automatically generates a database per customer. In SQL-Server I ...
Phoniex's user avatar
  • 33
1 vote
1 answer
1k views

PostgreSQL: Switching schema name in a loop

I am trying to switch the schema name dynamically to do some data manipulation. I am running the following. DO $$ DECLARE schema_name TEXT; BEGIN FOR schema_name in select "name" ...
Dashwall's user avatar
0 votes
0 answers
537 views

Multi-Tenant Database Design - Tenant-specific Fields

I am working on an application modernization project. We run an application which keeps track of requests for medication for patients of multiple companies. One common design issue we run into keeping ...
Kirk Saunders's user avatar
0 votes
1 answer
242 views

Multi-tenant reduce number of MongoDB connections

Our multi-tenant app has about 4000 databases per tenant and we are having a problem with performance because of too many connections. Creating MongoDB connections on each database is a heavy ...
Orgil's user avatar
  • 101
2 votes
2 answers
910 views

Should I in general partition tables by tenant IDs in a multi-tenant system?

We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this: create table Things( accountId varchar(64) not null, internalId varchar(64) ...
Andrey Agibalov's user avatar
2 votes
0 answers
268 views

Schema level encryption in Postgres

A multi-tenancy approach for many webapps is to use a separate PostgreSQL schema for each tenant. In addition to permissions and access controls, is it possible to encrypt a tenant's whole schema ...
Turgs's user avatar
  • 213
0 votes
1 answer
1k views

Most efficient table structure for multi-tenant database

I have a conventional relational database (mySQL) with several entities stored in their own table. Three examples are images, events and organisations. We are making a change to the way our site works ...
Leon's user avatar
  • 103
2 votes
2 answers
3k views

Designing a multi-tenant database for a scenario with multiple user types

I'm developing a SaaS application for recruiting which has multiple user types such as Client, Recruiter, Panellist and Owner etc. There is also a chance that the user types may increase. The Owner ...
Shahbaj Sayyad's user avatar
0 votes
1 answer
69 views

With Postgresql v10 and WAL2JSON... Is it possible to include the database name / datname in the json?

We have a set of databases that were not set up in a multi-tenant fashion. Looking to build a hub that will hold the data in a multi-tenant fashion using wal2json to pipe data from source to the hub. ...
Andy Norris's user avatar
1 vote
1 answer
486 views

Circular reference and multi-tenancy

I'm have an issue with circular reference in my DB design. As see the ER diagram below have 2 circular reference that I highlighted with Red and Orange arrow. Anyone know how to resolve this issue?
Jerry's user avatar
  • 11
0 votes
1 answer
85 views

Query that marries data between two databases

Database 1 is the master database where data on all domains in our client's industry are stored Database 2 (or Database N) are the application specific databases of our client. (e.g. Application ABC ...
lecarpetron dookmarion's user avatar
1 vote
0 answers
46 views

Tool to help breaking multitenant into a single tenant [closed]

Oracle 11.2.0.4 I've been tasked with breaking out a single customer from our multitenant system into a separate system. And the data will have to be continuously updated. This is a large system ...
rfusca's user avatar
  • 1,569
0 votes
0 answers
303 views

Single-tenant to multi-tenant: restore old database to schema in new database

I have a Django app which uses Postgres as a database w/ the django-tenants package providing multitenant functionality. This is a migration from what was originally a single tenant database with just ...
Emile Paffard-Wray's user avatar
0 votes
1 answer
261 views

Postgres Search Path between connections

I'm a noob to Postgres, and am setting up a multi-tenant database using one schema per tenant. To target each tenant I need to set search_path accordingly. I'm worried about accidentally executing ...
Paul Guz's user avatar
1 vote
0 answers
30 views

Are there any downfalls to not using foreign key relationships in a multi-tenary scheme?

We have a proposed restructure of our CRM containing almost 500 databases containing ~25 tables each into a single multidimensional database with one giant fact table linking everything together. I ...
amaster's user avatar
  • 180
1 vote
1 answer
1k views

GDPR compliant multitenant MYSQL DB

I wonder what are the GDPR constraint on a MySQL DB for a multitenant SaaS application. While a solution with a single DB per each multitenant customer and restricted access will probably be fine, ...
Glasnhost's user avatar
  • 478
1 vote
1 answer
2k views

PostgreSQL: How to restrict resources per DB, Schema or User

Is there a way to configure PostgreSQL server instance to limit resources, e.g. work_mem or max_parallel_workers, by databases or schema? (or even limit a specific user) To my knowledge, parameters ...
Mahatma_Fatal_Error's user avatar
1 vote
2 answers
117 views

In Oracle multi-tenant architecture, is a PDB a container?

Below is an excerpt from Oracle document. I just want to confirm, is it correct to consider a user-created PDB or a seed PDB as a container? It's a little weird to me. If this is true. Then ...
Just a learner's user avatar
1 vote
1 answer
128 views

Understanding of Oracle multi-tenant PDBs

I'm a new user to Oracle. The document states The CDB root, also called simply the root, is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong. and A PDB is ...
Just a learner's user avatar
0 votes
1 answer
53 views

100+ DBs in one instance or divide and conquer : MariaDb Ubuntu 64bits

I have a desktop app (100+ customers and more to come) running at customer's small local server, all of them have same structure in tables (different info). I'm migrating that app to web ( almost ...
R. Islas's user avatar
2 votes
3 answers
4k views

Multiple instance in one server

Could you please help to explain why one might need multiple instances of SQL Server on the same server? What is a good point to use separate instances? Why wouldn't one use two databases in one ...
Rada Tann's user avatar
2 votes
2 answers
240 views

What is better - having 10 databases for 10 organisations or 1 table for 10 organisation with org_id? [duplicate]

I am making a web service (using Laravel in case that is relevant) that will have many separate organisations generated by the service. Considerations I can manage all organisation data in one ...
Shashank's user avatar
  • 123
3 votes
1 answer
2k views

How to Prevent heavy queries on one database, affecting the performance of other databases on same server?

I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server. One of the databases has some computationally intensive ...
Devdatta Tengshe's user avatar
0 votes
1 answer
165 views

Reference a schema from public table column

I have a multi-tenant Postgresql database with each schema belonging one of our customers (tenants). Each tenant has a table called Messages. So CustomerX.Messages contains all of the messages that ...
wheresmycookie's user avatar
0 votes
1 answer
537 views

Isolating access to tenant data in a multi-tenant database

We have a single SQL Server database for all tenants of a web-application. How can we ensure when a support engineer queries the DB directly via SSMS, they only touch data belonging to one tenant? ...
Marcus's user avatar
  • 205
1 vote
0 answers
105 views

Multi tenant .NET Core application

We are designing a new multi tenant .NET Core 2.1 application progra, where we are using one database per tenant. We can give each tenant a different subdomain so that we can base the tenant lookup ...
timeshift's user avatar
  • 111
4 votes
1 answer
2k views

How to do per-tenant backups for multi-tenant databases?

Are there any best practices, experiences or guidelines when it comes to do per-tenant backups/restores in a microservice multi-tenant application? In my case I have multiple Postgres servers which ...
xcq1's user avatar
  • 43
1 vote
1 answer
262 views

Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application?

I am developing a multi tenant SaaS that uses Azure SQL for data storage. I make use of sp_set_session_context to make sure that any query only touches records relevant to the current tenant. I ...
Gabriel Smoljar's user avatar
3 votes
1 answer
2k views

What are the downsides of using PARTITION for multi-tenant access?

We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores. Let's say we have 1MB per store and 10k stores so we'...
Yehosef's user avatar
  • 131
1 vote
1 answer
217 views

MongoDB as a log storage. Choosing shard key

I'm designing a log storage system based on MongoDB. I want to shard a log collection to increase ingestion and capacity (distribute writes to several machines) while allow fast search. I should be ...
Philipp Bocharov's user avatar
1 vote
1 answer
161 views

How to build the database for subdomain multisite? [closed]

I'm building a kind of "multi site application" based on wildcard subdomain. And every site will of course have the same files but also the same database structure. But it's not necessary for each and ...
Peter Westerlund's user avatar