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.

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 regular expressions for values (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 (for example CatalogEntities ), the entity ID that the operation targets is matched against the list of regular expression values. For operations against a Commerce list, for example, IFindEntitiesInListPipeline, the match is based on the list name.

The following provides an example of the default database sharding policies for Catalog entities and Catalog lists:

  {
        "$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"
      },
      {
        "$type": "Sitecore.Commerce.Plugin.SQL.ListShardingPolicy, Sitecore.Commerce.Plugin.SQL",
        "Expressions": {
          "$type": "System.Collections.Generic.List`1[[System.String, mscorlib]], mscorlib",
          "$values": [
            "^List-CatalogItems.*?$",
            "^List-Catalogs.*?$",
            "^List-Categories.*?$",
            "^List-SellableItems.*?$",
            "^List-Categories.*?$",
            "^List-SellableItems.*?$",
            "^List-PurgeCatalogs.*?$",
            "^List-PurgeCategories.*?$",
            "Entity-Catalog.*?$",
            "Entity-Category.*?$",
            "Entity-SellableItem.*?$"
          ]
        },
        "TableName": "CatalogLists"
  }

Sharding and relationship membership

The sharding policy contains a list of regular expressions that are used to map relationship membership information into the RelationshipLists table.

Note

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.*?$"
                ]
  },
   "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 existing regular expressions (excluding the List- prefix). For example, names such as RelationshipUpSell, or FeaturedCategoryToCategory are valid relationship names, because they are based on the regular expressions "^List-Relationship.*?$" and "^List-.*Category.*?$", respectively.

If a business user creates a custom relationship definition that does not match an existing regular 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 value in the "TableName": "RelationshipLists" list.