Hibernate.orgCommunity Documentation

Chapter 8. Audit table partitioning

8.1. Benefits of audit table partitioning
8.2. Suitable columns for audit table partitioning
8.3. Audit table partitioning example
8.3.1. Determining a suitable partitioning column
8.3.2. Determining a suitable partitioning scheme

Because audit tables tend to grow indefinitely they can quickly become really large. When the audit tables have grown to a certain limit (varying per RDBMS and/or operating system) it makes sense to start using table partitioning. SQL table partitioning offers a lot of advantages including, but certainly not limited to:

Generally SQL tables must be partitioned on a column that exists within the table. As a rule it makes sense to use either the end revision or the end revision timestamp column for partioning of audit tables.

The reason why the end revision information should be used for audit table partioning is based on the assumption that audit tables should be partionioned on an 'increasing level of interestingness', like so:

  1. A couple of partitions with audit data that is not very (or no longer) interesting. This can be stored on slow media, and perhaps even be purged eventually.

  2. Some partitions for audit data that is potentially interesting.

  3. One partition for audit data that is most likely to be interesting. This should be stored on the fastest media, both for reading and writing.

In order to determine a suitable column for the 'increasing level of interestingness', consider a simplified example of a salary registration for an unnamed agency.

Currently, the salary table contains the following rows for a certain person X:

The salary for the current fiscal year (2010) is unknown. The agency requires that all changes in registered salaries for a fiscal year are recorded (i.e. an audit trail). The rationale behind this is that decisions made at a certain date are based on the registered salary at that time. And at any time it must be possible reproduce the reason why a certain decision was made at a certain date.

The following audit information is available, sorted on in order of occurrence: