Relational Database Tuning

When using Axon Framework with a relational database, there are several considerations to take into account when tuning the database for optimal performance:

  1. Using the correct indices for the different types of queries used.

  2. Configuring the right auto-increment configuration when using JPA.

Important indices

If you have generated the tables automatically using your JPA implementation (for example, Hibernate), you probably do not have all the right indexes set on your tables. Different usages of the event store require different indexes to be set for optimal performance. This list suggests the indexes that should be added for the different types of queries used by the default EventStorageEngine implementation:

Normal operational use (storing and loading events)

  • Table DomainEventEntry, columns aggregateIdentifier and sequenceNumber (unique index)

  • Table DomainEventEntry, eventIdentifier (unique index)

Snapshotting

  • Table SnapshotEventEntry, aggregateIdentifier column.

  • Table SnapshotEventEntry, eventIdentifier (unique index)

Sagas

  • Table AssociationValueEntry, columns sagaType, associationKey and associationValue,

  • Table AssociationValueEntry, columns sagaId and sagaType,

The default column lengths generated by, for example, Hibernate may work, but won’t be optimal. A UUID, for example, will always have the same length. Instead of a variable length column of 255 characters, you could use a fixed length column of 36 characters for the aggregate identifier.

The timestamp column in the DomainEventEntry table only stores ISO 8601 timestamps. If all times are stored in the UTC timezone, they need a column length of 24 characters. If you use another timezone, this may be up to 28. Using variable length columns is generally not necessary, since time stamps always have the same length.

It is highly recommended to store all timestamps in UTC format. In countries with daylight saving time, storing timestamps in local time may result in sorting errors for events generated around and during the timezone switch. This does not occur when UTC is used. Some servers are configured to always use UTC. Alternatively, you should configure the event store to convert timestamps to UTC before storing them.

The type column in the DomainEventEntry stores the type identifiers of aggregates. Generally, these are the 'simple name' of the aggregate. Even the infamous AbstractDependencyInjectionSpringContextTests in Spring only counts 45 characters. Here, again, a shorter (but variable) length field should suffice.

Auto-increment and sequences

When using a relational database as an event store, Axon Framework relies on an auto-increment value to allow streaming event processors to read all events roughly in the order they were inserted. We say "roughly", because "insert-order" and "commit-order" are different things.

While auto-increment values are (generally) generated at insert-time, these values only become visible at commit-time. This means another process may observe these sequence numbers arriving in a different order. While Axon Framework has mechanisms to ensure that eventually all events are handled, even when they become visible in a different order, there are limitations and performance aspects to consider.

When a streaming event processor reads events, it uses the "global sequence" to track its progress. When events become available in a different order than they were inserted, Axon Framework will encounter a "gap." Axon Framework will remember these "gaps" to verify that data has become available since the last read. These gaps may be the result of events becoming visible in a different order, but also because a transaction was rolled back. It is highly recommended to ensure that no gaps exist because of over eagerly increasing the sequence number. The mechanism for checking gaps is convenient, but comes with a performance impact.

When using a JpaEventStorageEngine, Axon Framework relies on the JPA implementation to create the table structure. While this will work, it is unlikely to provide the configuration that has the best performance for the database engine in use. That is because Axon Framework uses default settings for the @GeneratedValue annotation.

When using the @GeneratedValue annotation as-is in Hibernate 6, the default increment value jumped to 50. This change, in combination with, for example, the multitenancy extension, may lead to uniqueness issues. Hence, it is strongly recommended to define a custom sequence generator, as described below.

To override these settings, create a file called /META-INF/orm.xml on the classpath, which looks as follows:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="1.0" xmlns="http://java.sun.com/xml/ns/persistence/orm">
    <mapped-superclass access="FIELD" metadata-complete="false" class="org.axonframework.eventhandling.AbstractSequencedDomainEventEntry">
        <attributes>
            <id name="globalIndex">
                <generated-value strategy="SEQUENCE" generator="myGenerator"/>
                <sequence-generator name="myGenerator" sequence-name="mySequence"/>
            </id>
        </attributes>
    </mapped-superclass>
</entity-mappings>

It is important to specify metadata-complete="false". This indicates this file should be used to override existing annotations, instead of replacing them. For the best results, ensure that the DomainEventEntry table uses its own sequence. This can be ensured by specifying a different sequence generator for that entity only.

PostgreSQL, @LOB-annotated columns, and default Hibernate mapping

Specific to PostgreSQL is its TOAST and Large Object Storage functionality. TOAST stands for "the oversized attribute storage technique," ensuring columns that are (by default) larger than 8KB are:

  1. Compressed if possible. If this is insufficient,

  2. the wide columns is broken into chunks and moved to another table.

Any field that is moved out of line to this other "TOAST-table" will transparently be replaced by out-of-line identifiers. Furthermore, TOASTing is only supported for PostgreSQL data types like BYTEA or TEXT.

Large Object Storage takes a slightly different route, as instead, it uses a fixed OID column type. This "object identifier" will allow the main table to point to the actual data in a "large object storage table." The Large Object Storage route will be traversed whenever a column type is a LOB, BLOB, or CLOB.

Although the Large Object Storage is a useful feature from PostgreSQL to help with large objects, it has some impact too, being:

  1. There is some added overhead when reading data, as the original and the large object table should be read.

  2. Removing an object that has an OID column does not automatically remove it from the large object table.

  3. Manually querying the table will result in an OID column instead of the actual data being shown.

Let us look how this behavior from PostgreSQL combines with Axon Framework when combined with JPA and Hibernate.

Axon Framework uses the @Lob in several columns and tables for its JPA-based support, being:

  • The payload and meta_data columns in the domain_event_entry table.

  • The payload and meta_data columns in the snapshot_event_entry table.

  • The token column in the token_entry table.

  • The serialized_saga column in the saga_entry table.

  • The diagnostics, payload, metadata, and token column in the dead_letter_entry table.

Furthermore, Hibernate will by default use the aforementioned OID type whenever an @Lob annotation is found. Thus, the dedicate large object storage solution will be used if you are using PostgreSQL to store events, snapshots, tokens, sagas, and dead letters.

As events and snapshots are frequently read, the overhead predicament discussed earlier will be hit. Arguably more problematic is issue two, especially for the token_entry table.

The "claim" on a token is frequently updated to allow correct collaboration in a distributed Axon setup (please read our Tracking Tokens section for more details). As the large object table is not automatically cleared, it will eventually overflow through all the updates.

Hence, it would be best to avoid the Large Object Storage behavior and instead opt for the transparent TOAST feature. We can achieve this by adjusting Hibernate’s settings, to map the @Lob annotated fields to the BYTEA type. Since Axon Framework stores a byte array in each of the @Lob annotated columns, changing it to the BYTEA type makes sense.

Luckily, changing these settings can be done with three easy steps:

  1. Adjust the Hibernate dialect.

  2. Override the Hibernate mapping.

  3. [Optional] Migrate existing columns from OID to BYTEA.

Hibernate dialect changes

To adjust the dialect to not go for OID, we can enforce the type to BYTEA by providing a custom dialect.

Down below is a PostgreSQLDialect implementation that would get the trick done:

public class ByteaEnforcedPostgresSQLDialect extends PostgreSQLDialect {

    public ByteaEnforcedPostgresSQLDialect(){
        super(DatabaseVersion.make(9, 5));
    }

    @Override
    protected String columnType(int sqlTypeCode) {
        return sqlTypeCode == SqlTypes.BLOB ? "bytea" : super.columnType(sqlTypeCode);
    }

    @Override
    protected String castType(int sqlTypeCode) {
        return sqlTypeCode == SqlTypes.BLOB ? "bytea" : super.castType(sqlTypeCode);
    }

    @Override
    public void contributeTypes(TypeContributions typeContributions,
                                ServiceRegistry serviceRegistry) {
        super.contributeTypes(typeContributions, serviceRegistry);
        JdbcTypeRegistry jdbcTypeRegistry = typeContributions.getTypeConfiguration()
                                                             .getJdbcTypeRegistry();
        jdbcTypeRegistry.addDescriptor(Types.BLOB, BinaryJdbcType.INSTANCE);
    }
}

With the dialect in your application, your next step is to configure it to be used. This can for example be done by setting the jpa.database-platform property when using Spring:

jpa.database-platform=fully.qualified.classname.ByteaEnforcedPostgresSQLDialect

Hibernate mapping override

We use the Hibernate metadata override mechanism to tell which columns need to be of the BYTEA type instead of OID. To that end, add a file named orm.xml (ORM stands for object-relational mapping) under src/main/java/resources/META-INF directory containing the overrides.

Below is an example of overriding the serializedSaga and token columns from the SagaEntry and TokenEntry respectively:

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm" version="2.0">
    <entity class="org.axonframework.modelling.saga.repository.jpa.SagaEntry">
        <attribute-override name="serializedSaga">
            <column name="serializedSaga" column-definition="BYTEA"></column>
        </attribute-override>
    </entity>
    <entity class="org.axonframework.eventhandling.tokenstore.jpa.TokenEntry">
        <attribute-override name="token">
            <column name="token" column-definition="BYTEA"></column>
        </attribute-override>
    </entity>
 </entity-mappings>

OID to BYTEA column migration

If you already have Axon-specific tables using the OID type, you need to migrate them to BYTEA. The following SQL script can get the job done for the token_entry table:

ALTER TABLE token_entry ADD COLUMN token_bytea BYTEA;
UPDATE token_entry SET token_bytea = lo_get(token);
ALTER TABLE token_entry  DROP COLUMN token;
ALTER TABLE token_entry  RENAME COLUMN token_bytea to token;

After making all the changes and running the SQL script, the data-affected columns should now all be readable.