What period of time is best for Sentinel database partitions?

Sentinel 6 allows the administrator to define time-based partitions as often as once per hour. On the other hand partitions could be utilized spanning several days.

Determining the perfect partition size requires involving several environment-specific factors. For example the default setup is to have one partition defined every eight hours which means every partition will hold eight hours' data. This value is probably valid for most customers but keep in mind that the eight-hour period will cover peak hours when there may be millions of events as well as off-hours when there may be just a few dozen events. As partitions are used for archiving data and eventually removing old data from the database the simplicity of those operations depends on the size of the dataset. A partition with megabytes of data will be faster and simpler to archive and delete than one with gigabytes or terabytes of data. Also partitions defined for smaller units of time can be archived more often freeing space sooner. For the reasons listed above having partitions defined on the eight-hour scale or less is attractive to many database administrators.

Looking at the issue from a different perspective there are things to consider when defining more partitions. First there is the consideration of the sheer number of files created as partitions get smaller. In a single week 168 partitions will be created, archived, and eventually deleted with one partition defined per hour. While these will be smaller and easier to work with individually than the same data in partitions spanning longer period of time the number of them may be frustrating to use on the server where they are archived. Also as each partition is defined it takes up an extent of its own from the database which, by default, is ten megabytes in size. If one month of partitions are online at any given time then there will be seven or more gigabytes of space used at the minimum just by having these partitions defined. The extent used as each partition is allocated is also used for data storage but this minimum may not be expected and may look like database space that is not otherwise available once defined and could cause concern. If longer periods of time are to be online at a given time then additional database space requirements will exist. Typically, considering the fixed overhead for a partition, hourly partitions are only recommended in environments with very high event rates where the volume of actual data dwarfs the overhead. The logic of having more data coming into the database than is required for the partitions applies regardless of the time span of the partition.

With this information measure the size of data captured per period of time and find a partition size that best meets your online and offline data requirements. Keep in mind that partition time spans can be configured after Sentinel is running but typically the Sentinel database jobs create these into the future so modifications may not come into affect for several days depending on the settings implemented. Consult with your database administrator for other tuning issues involved in the database.