Sitecore Commerce - How to Extend SQL Sharding
In previous versions of Sitecore Commerce the SQL Database Schema included 2 tables:
- CommerceEntities: Stored all Commerce Entities (Catalog Items, Orders, Promotions ) as JSON.
- CommerceLists: Maps Entities to Lists (SellableItems To Category for example).
Those tables quickly reached a huge amount of Data and caused significant performance issues in some cases.
To solve the dilemma**, Sitecore Commerce 9** introduced a simple SQL Sharding technique driven by an Environment JSON Policy-Set: PlugIn.SQL.Sharding.PolicySet-1.0.0.json
Sitcore Comemrce Sharding Policy Set:
This policy set includes two Policy Types:
1. Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy:
Maps an Entity ID to a table using a regular expression.
Example:
In this example, all the Commerce Entities with Ids starting like Entity-Catalog% would be persisted/expected in Table CatalogEntities .
2. Sitecore.Commerce.Plugin.SQL.ListShardingPolicy:
Maps a List ID to a table using a regular expression.
Example:
In this example, all the commerce Lists with Ids starting like List-Promotion% would be persisted/expected in Table PromotionLists
As you can see it s a very flexible technique that allows you to easily decide how you want to distribute your data. However, you need to be cautious to not use ambiguous rules. You need to make sure your Entity or List ID fits in one policy/regular expression only.
Out of the box, Sitecore commerce comes with these tables:
Extending the Sharding policy:
The Sharding policy can be extended in 3 easy steps:
1. Manually create the tables(s) you d like to add to the schema.
2. Update : PlugIn.SQL.Sharding.PolicySet-1.0.0.json to add your policy.
3. Bootstrap Commerce Engine.
P.S: You should extend the Sharding policy at the beginning of your project or after a data clean/re-initialize. If you extend over existing data, you might not be able to retrieve old Entities/Lists.
Examples**:**
Below two examples of how you could extend the Sharding policies:
Example 1:
You can decide to store Customers in separate tables. (OOB they re stored in CommerceEntities and CommerceLists tables).
Step 1: Create the two tables using same definition as CommerceEntities and CommerceLists.
Step 2: Update PlugIn.SQL.Sharding.PolicySet-1.0.0.json to add these policies:
Step3: Bootstrap Commerce Engine.
Example 2:
OOB all Catalog items (Catalogs, Categories, Sellable Items) are stored in CatalogEntities table.
Let s say you have a huge catalog and would like to shard your sellable items so they don t all go to this default table.
Let s assume your EntityIDs look like this: Entity-SellableItem-{Some Guid}
We know a Guid starts with a number 0-9 or a letter A-F.
We can decide that:
- Guids starting 0-3 be stored in table ProductEntities_0
- Guids starting 4-7 be stored in table ProductEntities_1
- Guids starting 8-B be stored in table ProductEntities_2
- Guids starting C-F be stored in table ProductEntities_3
The policies in that scenario would look like:
Summary:
Sitecore Commerce 9 Sharding Policy is very flexible and easy to customize.
When you re starting a new Sitecore Commerce project, you should take some time to check if OOB policy meets your needs and customize it accordingly.
You can also use a different policy per environment if needed.