Using Axon with PostgreSQL without TOAST

The client I work for at this time is leveraging Axon 3. The events are stored in a PostgreSQL database. PostgreSQL uses a thing called TOAST (The Oversized-Attribute Storage Technique) to store large values.

From the PostgreSQL documentation:

“PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows”

As it happens, in our setup using JPA (Hibernate) to store events, the DomainEventEntry entity has a @Lob annotation on the payload and the metaData fields (via extension of the AbstractEventEntry class):

For PostgreSQL this will result in events that are not easily readable:

SELECT payload FROM domainevententry;

| payload |
| 24153   |

The data type of the payload column of the domainevententry table is OID.

The PostgreSQL JDBC driver obviously knows how to deal with this. The real content is deTOASTed lazily. Using PL/pgSQL it is possible to store a value in a file. But this needs to be done value by value. But when you are debugging your application and want a quick look at the events of your application, this is not a fun route to take.

So we wanted to change the data type in our database to something more human readable. BYTEA for example. Able to store store large values in, yet still readable. As it turned out, a couple changes are needed to get it working.

It took me a while to get all the pieces I needed. Although the solution I present here works for us, perhaps this could not be the most elegant of even the best solution for everyone.

Hibernate Dialect

Hibernate is a database agnostic tool. That means that certain database specific behavior needs to be defined explicitly. One of the extension points of Hibernate is the Dialect interface. It has a ton of implementations. Among others the PostgreSQL94Dialect, which is a subclass of the PostgreSQL93Dialect, which is a subclass of the PostgreSQL92Dialect, which is … Well, you guessed it.

First, through the use of a Dialect implementation, we tell Hibernate to map fields that are annotated with @Lob (or @Blob) to store as BYTEA.

package nl.trifork.blog;

import java.sql.Types;

import org.hibernate.dialect.PostgreSQL94Dialect;
import org.hibernate.type.descriptor.sql.BinaryTypeDescriptor;
import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;

public class AxonPostgreSQLDialect extends PostgreSQL94Dialect {

  public AxonPostgreSQLDialect() {
    super();
    this.registerColumnType(Types.BLOB, "BYTEA");
  }

  @Override
  public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
    if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {
      return BinaryTypeDescriptor.INSTANCE;
    }
    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
  }

}

A little disclaimer: It looks that calling registerColumnType in the constructor and remapping the SqlTypeDescriptor are doing about the same thing, yet I seems to me that both are needed to make it work. I did not bother to figure out why; this took me a lot of time already.

Hibernate needs to be configured to use this dialect. See the documentation of Hibernate on this subject. It really depends on your project how you do this.

Overriding metadata

Next, using Hibernate’s metadata override mechanisms, we can override Hibernate’s mapping behavior.

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings
  xmlns="http://java.sun.com/xml/ns/persistence/orm"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd"
  version="2.0">

  <entity class="org.axonframework.eventsourcing.eventstore.jpa.DomainEventEntry">
    <attribute-override name="payload">
      <column name="payload" column-definition="BYTEA"/>
    </attribute-override>
    <attribute-override name="metaData">
      <column name="metadata" column-definition="BYTEA"/>
    </attribute-override>
  </entity>

  <entity class="org.axonframework.eventsourcing.eventstore.jpa.SnapshotEventEntry">
    <attribute-override name="payload">
      <column name="payload" column-definition="BYTEA"/>
    </attribute-override>
    <attribute-override name="metaData">
      <column name="metadata" column-definition="BYTEA"/>
    </attribute-override>
  </entity>

  <entity class="org.axonframework.eventhandling.saga.repository.jpa.SagaEntry">
    <attribute-override name="serializedSaga">
      <column name="serializedsaga" column-definition="BYTEA"/>
    </attribute-override>
  </entity>

  <entity class="org.axonframework.eventhandling.tokenstore.jpa.TokenEntry">
    <attribute-override name="token">
      <column name="token" column-definition="BYTEA"/>
    </attribute-override>
  </entity>

</entity-mappings>

Somewhere in the Spring Axon configuration there’s a EntityManagerFactoryBean defined. It must know about the metadata override file (orm.xml in this case) by setting the mapping resources.

@Bean(name = "axonEntityManagerFactory")
@Primary
public LocalContainerEntityManagerFactoryBean axonEntityManagerFactory(
    DataSource dataSource,
    @Qualifier("jpaProperties") Properties jpaProperties) {
  final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
  em.setDataSource(dataSource);
  em.setPackagesToScan(
    "org.axonframework.eventsourcing.eventstore.jpa",
    "org.axonframework.eventhandling.saga.repository.jpa",
    "org.axonframework.eventhandling.tokenstore.jpa");
  em.setJpaVendorAdapter(jpaVendorAdapter());
  em.setJpaProperties(jpaProperties);
  em.setMappingResources("/orm.xml");
  return em;
}