Having worked on a large-scale, multi-tenant system (federated approach with customers spread across 18+ servers, each server having identical schema, just different customers, and thousands of transactions per second per each server), I can say:
There are some folks (a few, at least) who will agree on your choice of GUID as the IDs for both "TenantID" and ...
The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer.
When you’re building an application for lots of clients, there’s two common ways to design the database(s):
Option A: Put all clients in the same database
The only issue is that there isn't really a convenient way to enforce these relationships. You can use a boatload of triggers, or store the master table in each instance database and simply expose all of them as metadata to the master, using a view perhaps.
The biggest downside, I think, has to do with keeping the data accurate and synchronized, in at least ...
If I don't partition, is (tenantID, id) or (id, tenantid) a better
choice. Assume reads are all by tenantid, writes are random by
tenantid, and a there is enough downtime nightly to run a maintenance
(tenantID,id) is the better ordering, as it creates locality for each tenant's data.
What are the pros and cons of creating a separate partition ...
Because all PDBs share the same redo log files, it's not possible to enable archive log mode only for a specific PDB. All PDBs share the archive log mode of the CDB.
Remember, the PDBs themselves don't have any instance (processes and memory areas) or control file and redo logs
Oracle Database 12c Feature: Multitenant Database
First I need to reiterate what RDFozz said in his answer about including an explicit ORDER BY. If SQL Server does an allocation order scan you may get the wrong results. Including ORDER BY in the query doesn't cause a performance hit. Why not do it?
From a query performance perspective, the index that you want depends on how many rows you return at a time ...
A few things to keep in mind:
What is efficient at large scale is not always efficient at small scale.
What you think you'll need at large scale is not often what you actually need when you get to that scale.
Best performance is application specific, not generic. What performs best for your app may not be what performs best for my app.
All schema-less means ...
One further consideration I haven't yet seen in other answers.
Having a design that allows for many tenants in a single database will give flexibility later. Should load/ scale out/ security/ geo location demands later suggest a tenant should have a separate database it can be created by restoring the currect DB on the new instance. The other tenants' data ...
Obviously, you are dealing with the same tables in each user schema. Have you considered inheritance for this? It can give you the best of both worlds for some use cases. There are also some limitations. You can have a separate schema for each user and still search all user tables at once very conveniently.
Select rows from table where each row ...
Are you really asking about a performance solution w/o ever telling us what the performance bottleneck actually is? Did you measure? The server is 'heavely' loaded at night... why? What resources are stretched? Is it memory? IO? CPU? Contention? Is it poorly tuned maintenance? Bad data model? Misaligned schedule? Hardware bottleneck?
I recommend you use a ...
One practice that makes multi-tenant models much easier, even though it breaks normalization*, is to include a column on every table for the tenant. You could call it TenantID. That way every query run against the database can filter on TenantID on every table, and you can use database partitioning to isolate the data for each tenant and speed up queries by ...
Your options are:
Use roles, and SET search_path on each role so it looks in the appropriate organization's schema. While in many ways the cleanest approach from a database perspective, this is a pain when giving orgs the right to manage their own users.
Require users to specify their organisation at login time, probably with user@domain style logins, or ...
Given that you haven't started coding the app or DB yet, but do have a sense of the scope of the data (even if that will change somewhat as time goes on): no, you are not over-thinking this. I wouldn't spend months pondering this one aspect of the system design, but to not think of it at all is a bit foolish since refactoring app code is far easier than ...
I do not believe that what you are trying to accomplish is possible, at least not without an extra, over-complicated layer of Dynamic SQL.
Since you have already gone through the trouble of creating unique Logins and Schemas, you might want to consider one of the following approaches:
Accept the separate Schema concept and create the Stored Procedures (and ...
You could probably use dynamic SQL,
CREATE PROCEDURE [StoredProcedures].[Insert_Table1]
DECLARE @sql nvarchar(max)=N'
INSERT INTO table1 VALUES (NEWID());
INSERT INTO table1 SELECT col1 FROM StaticTable;
EXECUTE sys.sp_executesql @sql;
However, you should be aware that this is problematic for several reasons. The most obvious ones are:
If you're ...
It sounds like you're looking at something similar to, if not identical to, a multi-tenant architecture.
In a multi-tenant architecture, all the tenants (clients, customers) share something. They might share only the server and the dbms, with each tenant getting a private database. Or they might share the database, with each tenant getting a private schema....
My understandng of MySQL is that what they call a "database" is a schema on other db's. So the answer to your question is "yes" or "no" depending on the specifics you are asking. In fact "create schema" just maps to "create database" on MySQL.
So yes, you can do one schema per tenant but that is not any different on MySQL than one database per tenant. So ...
Possibly, but there's a lot of queries to one table of bookings with
many people trying to get bookings at the same time. It's more complex
than that, with queries needing to join other tables, with data being
loaded in to these tables by other tenants at the same time, etc
So, it's a database! Great, let's answer some questions.
Not as a counter-point to anything stated in @sp_BlitzErik's answer, but to more fully address one specific question out of the several posed by the O.P.:
Changing clustered indexes from the auto-incrementing int PK to being tenantID + id. (note all tables have tenantID column and already a non-clustered index on tenantID)
Simply stated: Yes, yes, yes, ...
But as a general practice, what is recommended in this scenario?
Database-per-tenant is the best-practice here. There are scenarios where it is impractical, but should be your strong preference in designing any multi-tenant system on SQL Server.
Database-per-tenant gives you:
Excellent security and data isolation that's verifiable and easy to
Postgres has no feature to ferret out semantically a subset of the rows in your tables. Nor does any other database I’ve heard of.
You will need to write your own code to retrieve just the one tenant’s rows from each of the relevant tables. This code might be in your app, or might be done on the server within Postgres.
Also, you’ll need to decide if any ...
I'll quote a few things from Postgres wiki, Priorities:
Prioritizing users, queries, or databases
PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system ...
it all depends.
from my personal experience, if you have one database for each organisation, all the data is neat and clean and it is all in one place for each different organisation.
In case there are changes specific to a particular organisation it will affect only that particular organisation database - as the changes may or may not be welcomed for the ...
Regardless of how you solve the problem, you have to go through some sort of "router". And that may be the least secure part.
Separate computer for each customer -- you need to tell user which machine to go to.
Separate process or API or VM on same machine -- user must log into the appropriate path.
Separate databases (one per customer) on single MySQL ...
Strictly in terms of MySQL, a Database and a Schema are one and the same.
From the wording of your question
What you call a single database is really the mysqld server process
What you call a schema in just an OS subfolder managed by the mysqld process
The way mysqld creates a database is straightforward: It simply makes a subfolder under the current ...
For a data warehousing/analytics type application (DW/OLAP), I would go with PostgreSQL. It has set operators, windowing functions (also known as analytic functions) and common table expressions. You will most likely have to implement some or all of these in your own code with MySQL with the attendant possibility of bugs. MySQL is more suited to read-heavy ...
Store the tenant_id first. When you do this you can enable index key compression.
See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts.
In your case, you can do it like this:
create unique index mytable_idx on mytable(...
The notes about this in Oracle 126.96.36.199 state that it is not required to move/copy the files from the primary during the plugin. The note also states that in this release the copy of the standby files of the source can be postponed using the standbys=none clause. This makes it possible to plugin a large primary database in a primary container database and ...
Yes there is contained databases as of SQL Server 2012
A contained database is a database that is isolated from other
databases and from the instance of SQL Server that hosts the database.
SQL Server 2016 helps user to isolate their database from the instance
in 4 ways.
Much of the metadata that describes a database is maintained in the database. (...