Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Partitioning - Multi-Tenancy

    Hello,

    We are currently designing Multi-Tenancy DB for one of our Cloud Application. We have added Ord_ID column in all the tables as a part of Primary key on which the partitioning (Hash) will be done. We will be using Oracle 11g EE.

    I require some inputs on Partitioning to achieve following and any other usefull info.

    1. When ever a new tenant is added, A new partition needs to be created automatically for the tenant.

    2. What index would be beneficial (LOCAL / GLOBAL);

    3. Info on Rebuilding Indexes and maintainances.

    Regards

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by edwin_fredrick View Post
    1. When ever a new tenant is added, A new partition needs to be created automatically for the tenant.
    Create a trigger for that.

    2. What index would be beneficial (LOCAL / GLOBAL);
    Most probably local indexes

    3. Info on Rebuilding Indexes and maintainances.
    What do you mean with that? Local indexes do not need to be rebuilt. Only global ones if I'm not mistaken in case you drop or create a partition. More details are in the manual
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Oct 2004
    Posts
    60
    We have around 400+ tables, Does that mean that we need to issue 400+ alter statements + for indexes to add a new partition?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by edwin_fredrick View Post
    We have around 400+ tables, Does that mean that we need to issue 400+ alter statements + for indexes to add a new partition?
    Locale indexes are created once per table not per partition. Once the local index is created there is nothing you need to do. If you have a global index, then you need to rebuild it each time you drop or create a partition. Please read the manual. It's all documented there.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Yes, this would mean quite a bit of management. Just a thought, why a partition by tenant? What benefits is partitioning by tenant going to give you?
    I would say you should be using Oracle Virtual Private Database to segment your tenants and then use partitioning over the entire instance for performance.
    Also, partitioning by tenant will limit you to 1 million tenants due to partition limitations (don't know if that will matter or not).

  6. #6
    Join Date
    Oct 2004
    Posts
    60
    Hi Dayneo,

    Thanks for your reply. We are using VPD for filtering based on the tenent id. No queries / dml does operation across multiple tenants. I believe even SalesForce is using this concept (HASH Partitioning by Tenant ID) and may be a POC will help us determine which way to move.

    Instead of adding a partition when a tenant is created, I think we will add increase the number of partitions 2^N times. Also i noticed that local indexes doesnt need to have the Tenant ID column.

    We are using VPD with application context for Row Level Security based on Tenant ID. IMO VPD is Row / column level security and does nothing in terms of performance improvement.
    Last edited by edwin_fredrick; 07-17-12 at 01:19.

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Thumbs up

    Quote Originally Posted by edwin_fredrick View Post
    I believe even SalesForce is using this concept (HASH Partitioning by Tenant ID) and may be a POC will help us determine which way to move.

    Instead of adding a partition when a tenant is created, I think we will add increase the number of partitions 2^N times. Also i noticed that local indexes doesnt need to have the Tenant ID column.
    It is most likely that SalesForce is doing that. It makes sense to partition on tenant because one tenant will never be reading another tenants data. But remember that a hash partition on tenant will not allocate a partition per tenant. It will simply split your tenants into groups (based on the hash) with the goal of having evenly sized partitions.

    There is another partition option called System Partitioning (search down the page in the link) in which the application determines the partition in which the data must be written. That will give you absolute control of where the row is written (i.e. write to the tenants allocated partition). The down side is that this must be specified from the application code. This will most likely turn out to be more of a headache than a benefit (think maintenance and performance tuning).

    IMO VPD is Row / column level security and does nothing in terms of performance improvement.
    Yes that's right.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •