ServiceNow - Table Rotation vs Extension vs Table Cleaner

[sergiu_panaite]

There seems to be some confusion around when it comes to what Table Rotation, Table Extension and Table Cleaner do in ServiceNow.
I'll try here to explain a bit more in detail the differences between these functionalities.

1. Table Rotation

As the name implies, Table Rotation just implements a way of storing a limited amount of data on a table based on several parameters.
This means oldest data in the table will be deleted at a certain point in time.

How is this really done?

If we take the example of syslog table (System Definition -> Table Rotations -> syslog), you will see the following:

- Duration: 7
- Type: Rotation
- Rotations: 8
- Table Rotations Schedule: shows syslog along with other 8 tables starting with syslog0000 to syslog0007

First each of these tables under the form syslog0000 are called shards (or subtables if you want).
Each of these shards will store 7 days of data and with 8 in total we will have roughly around 56 days of data in total in syslog (the base shard syslog is cleaned automatically on first rotation which means over time it has no data).

The Table Rotations Schedule will show what is the exact start (Valid from) and end (Valid to) of storing period for each shard.

Now, what are some of the advantages of this model:

- older data can be removed without impacting existing data being inserted/queried
- the whole table will grow only to a reasonable size
- when querying based on creation date, we might only look in one shard alone

What are some of the disadvantages of this model:

- queries that don't use a creation date (sys_create_on) will force an expensive UNION ALL query that can be very slow

2. Table Extension

This implements a way of storing data indefinitely (never clean up). The configuration is done basically in same way as for rotations with the difference that now we have to define Type as Extension and Duration is only specifying the period of each shard.
Tables that cannot be cleaned up and are mostly insert-only or do not have a lot of relationships with other tables can be considered candidates for extension.

Note Data is never deleted automatically from tables with Type Extension!

A good example of a table extended is sys_email.

The main disadvantage is the same as for Rotations: queries that don't use a creation date (sys_create_on) will force an expensive UNION ALL query that can be very slow (especially taking into account here we can have lots of shards over time).

3. Table Cleaner

Table Cleaner deletes records from tables based on a matchfield (usually sys_created_on or sys_updated_on with a meaning of data older than a value in seconds). This is very useful for tables that need to be kept at a certain size and not grow to an unmanageable size.
Table Cleaner allows also extra Filter conditions besides the matchfield.

Table Cleaner has a scheduled job that runs every hour and cleans all the tables present in the configuration (can be viewed by simply typing sys_auto_flush_list.do in the Navigator filter).

To summarise:

- use Table Rotations if you need to store data for a certain amount of time and need to query it frequently based on creation date or recent data
- use Table Extensions if you need to store data indefinitely - nowadays it's not very much used (sys_email is the only table I know extended)
- use Table Cleaner if you want to remove data frequently to allow the table not to grow very large

Note Do not add tables in Table Cleaner if they're already in Table Rotations/Extensions. Our platform checks that and won't delete records from them, it will just skip them.