Table Rotation - Extension vs Shard

[ceskie]

Ahoy,

let me just jump straight into the topic, there is this sys_table_rotation in ServiceNow, with Type field, which offers you the following:

- Rotation (which rotates operational data into N tables, and upon last rotation, oldest data are truncated)
- Extension (which splits operational table into N+1 tables, creating more tables and keeping them forever)

- Shard (which... does what exactly?)

We have a table with large data set and we want to preserve it for ~10 years, meaning there can be around ~50M records. At the same time, we want to have fast running queries and list/form loads at least for operational/new data, so we would like to "shard" this table into multiple tables, exactly as Extension does, but not based on sys_created_on, but based on custom field (shard_by). However, when you pick Type=Shard, it OOB creates 16 Shard tables and sets shard_by field to sys_id (which does not help).

Does any of you guys ever worked with this, can provide any insight into Shard, provide docs, examples, anything?

Thank you!

[ceskie]

That sounds reasonable, this theory is also supported by the OOB sys_activity sharding as I described:

So rephrasing like this

---

@Kristen Ankeny wrote:

You could have very high levels of data in a certain time frame, very low in another 

This fits our case very well, so we will give the Type=Shard a try, I will eventually come back later and post my findings (whether its acting as expected from performance pespective).

Thank you!

(Although, it'd be nice if someone from SN stopped by and eventually put some more light into this sharding by sys_id .-)