Sharding policies
Database sharding provides data isolation and can facilitate the management of data growth for improved performance and data maintenance.
Sharding for Commerce entity and Commerce list tables
The Commerce Engine defines sharding policies that determine which global database tables to use for read and write operations when interacting with a Commerce entity or a list.
Stored procedures used for sharding do not support custom tables and lists.
The Commerce Engine implements database sharding for Commerce entity and list tables, and provides two types of sharding policies. One type is used for operations against Commerce entities (for example, "TableName": "CatalogEntities" ), and the other type is used for read and write operations against lists (for example, "TableName": "CatalogLists").
The PlugIn.SQL.Sharding.PolicySet-1.0.0.json file, located in the C:\inetpub\wwwroot\<CommerceEngineInstance>/wwwroot\data\Environments\ folder, contains Commerce Engine sharding policies.
The CommerceEntities and CommerceLists policies have an “Expressions” property that takes a list of matching patterns as values. You can define patterns using regular expressions (regex), for example "^Entity-Catalog.*?$" ), or using wildcards, for example "Entity-Catalog*"). The Commerce Engine uses these property values to identify the database table to read from or write to.
For operations against a Commerce entity table (for example CatalogEntities ), the entity ID that the operation targets is matched against the list of pattern values. For operations against a Commerce list, for example, the IFindEntitiesInListPipeline list, the match is based on the list name.
Matching pattern algorithms for list and entity table names
The Commerce Engine supports the following pattern matching algorithms for shard name matching:
-
Wildcard matching
-
Regular expression (regex) matching
For configuration simplicity and optimal performance, we recommend that you use wildcard expressions.
In the PlugIn.SQL.Sharding.PolicySet-1.0.0.json file, each section that contains a "TableName" property can include an optional “UseWildcardExpressions” property that specifies a pattern matching algorithm. In new Sitecore XC 10.2 deployments, wildcard expressions matching is enabled by default ( “UseWildcardExpressions": "true" ).
If the "UseWildcardExpressions" property is missing from the policy set .json file, or if the property value is set to "false", then pattern matching uses regex.
Example configuration using wildcard expressions
The following shows an example of the default sharding policies for Catalog entities using wildcard expressions:
{
"$type": "Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy, Sitecore.Commerce.Plugin.SQL",
"Expressions": {
"$type": "System.Collections.Generic.List`1[[System.String, mscorlib]], mscorlib",
"$values": [
"Entity-Catalog*",
"Catalog-*",
"Entity-Category*",
"Category-*",
"Entity-SellableItem*",
"SellableItem-*"
]
},
"UseWildcardExpressions": true,
"TableName": "CatalogEntities"
},
Example configuration using regex expressions
The following shows an example of a sharding policies that uses a regex expressions for Catalog entities:
Where the "UseWildcardExpressions" property is absent from the configuration, regex matching is implicitly enabled.
{
"$type": "Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy, Sitecore.Commerce.Plugin.SQL",
"Expressions": {
"$type": "System.Collections.Generic.List`1[[System.String, mscorlib]], mscorlib",
"$values": [
"^Entity-Catalog.*?$",
"^Catalog-.*?$",
"^Entity-Category.*?$",
"^Category-.*?$",
"^Entity-SellableItem.*?$",
"^SellableItem-.*?$"
]
},
"TableName": "CatalogEntities"
}Sharding and relationship membership
The sharding policy contains a list of expressions that are used to map relationship membership information into the RelationshipLists table.
All relationships in the catalog system must be mapped into this table, including new relationship definitions.
The default policy is defined as follows:
{
"$type": "Sitecore.Commerce.Plugin.SQL.ListShardingPolicy, Sitecore.Commerce.Plugin.SQL",
"Expressions": {
"$type": "System.Collections.Generic.List`1[[System.String, mscorlib]], mscorlib",
"$values": [
"List-*Catalog*",
"List-*Category*",
"List-Catalog*",
"List-*SellableItem*",
"List-*SellableItem-*",
"List-PriceBookToCatalog*",
"List-PromotionBookToCatalog*",
"List-InventorySetToCatalog*",
"List-Relationship*"
]
},
"UseWildcardExpressions": true,
"TableName": "RelationshipLists"
},Considerations for naming new custom relationships
Relationship definitions in the Business Tools are based on relationships that are pre-defined in the "TableName": "RelationshipLists" table. When a user creates a new relationship definition using the Business Tools, we recommend that they follow a naming convention that matches one of the configured expressions (excluding the List- prefix). For example, names such as RelationshipUpSell, or FeaturedCategoryToCategory are valid relationship names, because they are based on the expressions "List-Relationship*" and "List-*Category*", respectively.
If a business user creates a custom relationship definition that does not match an existing expression defined in the "RelationshipLists" table, you must add an entry into the RelationshipLists list to match the new relationship definition name. The entry must include a "List-" prefix, and a "*" postfix. For example, if a business user creates a new relationship definition named "FeaturedProducts", you must add the entry "List-FeaturedProducts*" as a value in the "TableName": "RelationshipLists" list.