Is there a limit on number of schemas in an Exasol database?

[bakka]

Hi Exasol

We are building a SaaS solution, where we plan to put each customers data in separate schema. Over time this will hopefulle mount to a lot of schemas.

In order to cover the bases I would love your take on the implications of this approach

• Is there a limit - fixed or practical - on the number of schemas an instance can handle (10.000, 100.000, 1.000.000)?
• Is there an overhead per schema, that affect the resources needed in an instance?
• Is there a performance penalty due to the system tables growing large from a high amount of schemas, tables, columns, etc.?
• Other things to take into account?

I would expect that this approach, where the amount of date per customer is small, have implications on the data distribution rules once we go into production and establish a cluster setup, and I expect to get back to you once we get there.

[Joachim]

At BlueYonder we have schema numbers of the order of upto 1k per database and a factor of 2 of corresponding roles, and 20k tables.

In that region we never faced any significant issues that could be traced to the pure overhead of schema/object management (compared to user behavior and his data), but that's obviously quite on the low end compared to the numbers you were suggesting.

In general our experiences align with Matze's statements.

[exa-Matze · Accepted solution]

Hi @bakka 

There is no limit in regards to the amount of objects in a database since Version 6.2 (I hope the version is correct, might also be 6.1 😉). Objects are schemas, tables, views, functions, scripts. That said: there is no limit in amount of schemas.

But of course there is some overhead in regards to system table / metadata queries with an increasing amount of objects. I am not aware that this ever had a bigger impact on query performance.

To minimize the overhead on system table / metadata queries it is highly recommended to use selective filters for those. Some examples:

1. Schema name filter for exa_schemas
2. schema and table name filter for exa_dba_tables 
3. schema and view filter for exa_dba_views
4. ...

Our engine will be able to apply such filters deep in our engine to get optimal performance

[bakka]

Thanks, then we will proceed down this path with easy at mind.

[Charlie]

not true for all cases 😉

/* snapshot execution */
SELECT
	edu.USER_NAME, edrp.GRANTED_ROLE , EDRp.ADMIN_OPTION 
FROM
	sys.EXA_DBA_USERS edu
INNER JOIN sys.EXA_DBA_ROLE_PRIVS edrp
ON 
	edu.USER_NAME = edrp.GRANTEE
INNER JOIN SYS.EXA_DBA_OBJ_PRIVS edop 
ON edop.GRANTEE = edrp.GRANTED_ROLE 
WHERE
	edu.user_name = ?;

Duration: 278 seconds.

So stay way from too many roles

[exa-Chris]

Let me nudge @MichaelKoenigBY , if they could add their Ideas to this...

[Charlie]

There was also a user group just recently regarding multi tenancy and  how Blue Yonder  implemented this on Azure

Blue Yonder - Multi-Tenancy Azure Environement 

[Charlie]

Hi,

we did not yet run into problems however we have not that much different schemas

• ~ 250 Schemas
• ~ 8000 Tables
• ~ 181k Columns
• ~ 2000 Users

This is not the a problem in any way however we automatically generate roles for every object and schema to be able to restrict user access to a minimum.

The roles do have an impact on performance

Table	Cardinality	Time for count
EXA_DBA_ROLES	100359	91 seconds
EXA_DBA_USERS	1916	58 seconds
EXA_DBA_ROLE_PRIVS	38647	47 seconds
EXA_DBA_OBJ_PRIVS	195595	121 seconds

This can really be a pain in the ass when doing automation.

This can also have an impact on the time it takes for users to log on for example in Power BI.

 PS: this is a 18 node on-prem cluster with 24 TB of memory