Axon Framework and PostgreSQL without TOAST

Introduction

When using Axon Framework together with PostgreSQL, you will find out that PostgreSQL uses something that is called TOAST (The Oversized Attribute Storage Technique).

It means that PostgreSQL uses a fixed page size of 8 KB and does not allow for tuples to use multiple pages; Postgres will compress the data. You can find more info about TOAST in the PostgreSQL wiki.  

For each “toasted“ value, PostgreSQL generates an OID (Object Identifier Type). With this OID, PostgreSQL keeps track of the actual values of that database column.

When using AxonFramework, you encounter this when you:

• are using PostgreSQL as the event store (the payload of an event)
• are using Event Processors that store a token as a pointer in PostgreSQL
• are using Sagas that are serialized and stored in PostgreSQL (the serialized saga)

In these cases, the value of the column is not human-readable with a simple select statement:

SELECT token FROM token_entry

| token |
| 17127 |

Another disadvantage (also mentioned here) is you can only store 4 billion entries, and PostgreSQL does not automatically `garbage collect` them. The TokenEntry table is updated a lot and you can quickly hit the max OID limit. You get the following exception when you’ve hit this limit:

org.hibernate.exception.GenericJDBCException: could not update:[org.axonframework.eventhandling.tokenstore.jpa.TokenEntry

If you don’t want to clean the OID entries and make the data type in your database human-readable, you can change the type to BYTEA. Then you can store large objects and still keep them readable.

To configure this, you need to do the following:

1. Adjust the Hibernate dialect
2. Override the Hibernate mapping behavior for the database columns that need to be adjusted
3. Migrate existing columns from type OID to BYTEA

Adjust the Hibernate dialect

Implement a new class that overrides the existing Dialect. All fields annotated with @Lob or @Blob should be configured as BYTEA. It depends on whether you are using Spring Boot 2 with Hibernate 5 or Spring Boot 3, which moved to use Hibernate 6 how to do this. With Hibernate 5:

public class NoToastPostgresSQLDialect extends PostgreSQL94Dialect {

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

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

}

For Hibernate 6, use this instead:

public class NoToastPostgresSQLDialect extends PostgreSQLDialect {
    public NoToastPostgresSQLDialect(){
        super(DatabaseVersion.make(9, 5));
    }

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

    @Override
    protected String castType(int sqlTypeCode) {
        if (sqlTypeCode == SqlTypes.BLOB){
            return "bytea";
        }
        return 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);
    }
}

You can tell Hibernate to use this Dialect. In Spring, you can add the following property to your application properties:

jpa.database-platform: fully.qualified.classname.NoToastPostgresSQLDialect

Override the Hibernate mapping behavior

You can use the Hibernate metadata override mechanism to tell which columns need to be of the BYTEA type instead of OID. Add a file named orm.xml (ORM stands for object-relational mapping) under src/main/java/resources/META-INF. Below is an example of overriding the serialized saga column of the SagaEntry and the token column of the TokenEntry:

<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>

Migrate existing columns from type OID to BYTEA

If you have already created the OID columns, you need to migrate them to BYTEA. A SQL script like this can do the job:

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.

For further reference, you can take a look at the code samples here.