December 6, 2022 at 12:25 pm
Hi all, i have to refactor an existing database multi-tenant.
All tables should be filtered by tenant and sometimes by year.
I have this idea ti manage multi-tenancy:
- Pk not clustered in id (bigint)
- Index clustered on id (plus year in some tables)
For each customer i will assign a tenantid for example 10,20,30 ecc and i will "riserve" 1.000.000 rows to each one.
At every new inserti i will calcolate max id for that range (BETWEEN tenant * 1.000.000 AND tenant+1 * 1.000.000)
The same range will be filtered in all queries.
Do you think Is a good solution?
Some considerations:
-With that clustered index i all all columns data always available without lookup
-I don't need extra index per tenant
-I will not have so many teanants/rows ti exceed bigint max value
But...i have not seen this solution and i was wondering if is good or no.
Bye
December 6, 2022 at 2:22 pm
Use a sequence. Don't waste resources querying max(id).
Is there really a need to partition the detail ids by tenant? If so, then use a separate sequence for each tenant w/ a different min/max.
1 million seems arbitrary. Do you have sufficient data to be highly confident that 1 million rows/tenant is sufficient even for the largest/most-active tenants for as long as the system lives? What happens if you exceed that?
Is the number of tenants fixed, or will you have to keep modifying to accommodate new tenants?
If you always filter by tenantid, then that should be the first column in your clustered index.
If you usually filter by date as well, that is a good candidate to be a second column in your clustered index.
December 6, 2022 at 3:02 pm
This was removed by the editor as SPAM
December 6, 2022 at 3:39 pm
Why not simply add a new field (TenantID) to each table. and leave your ID as is. Now, ensure that all indexes include the TenantID, and add TenentID to all necessary joins/where clauses. You can also have TenantID as a FK to the Tenant table.
December 8, 2022 at 10:05 am
Hi @desnorton, the other option is to have PK (TenantId,Id) in each table, but in many table i have always filter by year, so i believe it could be better have in the clustered index. My idea was that using the Id to identify the tenant, i will have less columns in the index.
Other question: what is the difference to have PK clustered on (Tenantid, id) or PK notClustered on Id and then another clustered index on (TenantId, Id) ?
Thanks in advance
Bye
December 8, 2022 at 10:19 am
Hi @ratbak thanks for response.
Yes about tipology of application i will never never never exceed 1 milion rows, also because i have a retention of 4-5 years, than i can clean up some stuff.
In alternative infact, as you suggest, i was doing some test with:
clustered: TenantId,Year,Id
pk not clustered: Id (sounds good?)
My common scenario is:
Table A
Table B has a FK on TableA(Id)
I join this two tables with a query like this:
select * from TableA a
inner join TableB b on a.tenant_id = b.tenant_id and a.id=b.id
where b.tenant_id = 100 and b.year=2020
This index confguration should be ok right?
Last thing: if i have a table that is accessed only by join, it's ok have only pk clustered on Id without the Tenant?
For example, TableC will never be queries alone, but always in join with TableA and TableB, i don't need Tenant right?
Thanks for your time.
Bye
December 8, 2022 at 12:58 pm
Quick question, how many tenants are you hosting and will that number change in the foreseeable future?
😎
December 8, 2022 at 8:42 pm
Hi @desnorton, the other option is to have PK (TenantId,Id) in each table, but in many table i have always filter by year, so i believe it could be better have in the clustered index. My idea was that using the Id to identify the tenant, i will have less columns in the index.
Other question: what is the difference to have PK clustered on (Tenantid, id) or PK notClustered on Id and then another clustered index on (TenantId, Id) ?
Thanks in advance
Bye
There is no hard-n-fast answer. A PK is just a unique key. The clustered index (PK or other) is normally the one that satisfies the most queries. Depending on your data distribution and the way that you query the data (single row or batches of rows), sometimes (Tenantid, id) will be a good index, and other times (id, Tenantid) will be a good index.
You need to TEST TEST TEST
December 9, 2022 at 1:31 pm
Actually around 5.000 tenants. Based on kind of application number of tenants in the worst scenario can reach 10.000.
Scope of application is related only to a very specific kind of customer, so the number will be for sure always < 10.000
December 9, 2022 at 1:56 pm
Actually around 5.000 tenants. Based on kind of application number of tenants in the worst scenario can reach 10.000.
Scope of application is related only to a very specific kind of customer, so the number will be for sure always < 10.000
A word of advice, save yourself a lot of trouble and add a TenantI_D to all tables!
😎
I've had to implement this kind of change too many times when multi-tenant designs have gone wrong.
April 10, 2023 at 3:30 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply