{"id":15107,"date":"2017-10-09T09:05:58","date_gmt":"2017-10-09T07:05:58","guid":{"rendered":"https:\/\/blog.trifork.com\/?p=15107"},"modified":"2017-10-09T09:05:58","modified_gmt":"2017-10-09T07:05:58","slug":"axon-postgresql-without-toast","status":"publish","type":"post","link":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/","title":{"rendered":"Using Axon with PostgreSQL without TOAST"},"content":{"rendered":"

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

From the PostgreSQL documentation:<\/p>\n

“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”<\/em><\/p><\/blockquote>\n

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

For PostgreSQL this will result in events that are not easily readable:<\/p>\n

SELECT payload FROM domainevententry;\n\n| payload |\n| 24153   |\n<\/pre>\n
The data type of the payload column of the domainevententry table is OID.<\/p>\n
The PostgreSQL JDBC driver obviously knows how to deal with this. The real content is deTOASTed<\/em> 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.<\/p>\n
So we wanted to change the data type in our database to something more human readable. BYTEA<\/code> 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.<\/p>\n
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.
\n<\/p>\n
Hibernate Dialect<\/h2>\n
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<\/code>, which is a subclass of the PostgreSQL93Dialect<\/code>, which is a subclass of the PostgreSQL92Dialect<\/code>, which is … Well, you guessed it.<\/p>\n
First, through the use of a Dialect<\/code> implementation, we tell Hibernate to map fields that are annotated with @Lob<\/code> (or @Blob) to store as BYTEA<\/code>.<\/p>\n
package nl.trifork.blog;\n\nimport java.sql.Types;\n\nimport org.hibernate.dialect.PostgreSQL94Dialect;\nimport org.hibernate.type.descriptor.sql.BinaryTypeDescriptor;\nimport org.hibernate.type.descriptor.sql.SqlTypeDescriptor;\n\npublic class AxonPostgreSQLDialect extends PostgreSQL94Dialect {\n\n  public AxonPostgreSQLDialect() {\n    super();\n    this.registerColumnType(Types.BLOB, \"BYTEA\");\n  }\n\n  @Override\n  public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {\n    if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {\n      return BinaryTypeDescriptor.INSTANCE;\n    }\n    return super.remapSqlTypeDescriptor(sqlTypeDescriptor);\n  }\n\n}\n<\/pre>\n
A little disclaimer: It looks that calling registerColumnType<\/code> in the constructor and remapping the SqlTypeDescriptor<\/code> 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.<\/p>\n
Hibernate needs to be configured to use this dialect. See the documentation of Hibernate on this subject<\/a>. It really depends on your project how you do this.<\/p>\n
Overriding metadata<\/h2>\n
Next, using Hibernate’s metadata override mechanisms<\/a>, we can override Hibernate’s mapping behavior.<\/p>\n
<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n<entity-mappings\n  xmlns=\"http:\/\/java.sun.com\/xml\/ns\/persistence\/orm\"\n  xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\"\n  xsi:schemaLocation=\"http:\/\/java.sun.com\/xml\/ns\/persistence\/orm orm_2_0.xsd\"\n  version=\"2.0\">\n\n  <entity class=\"org.axonframework.eventsourcing.eventstore.jpa.DomainEventEntry\">\n    <attribute-override name=\"payload\">\n      <column name=\"payload\" column-definition=\"BYTEA\"\/>\n    <\/attribute-override>\n    <attribute-override name=\"metaData\">\n      <column name=\"metadata\" column-definition=\"BYTEA\"\/>\n    <\/attribute-override>\n  <\/entity>\n\n  <entity class=\"org.axonframework.eventsourcing.eventstore.jpa.SnapshotEventEntry\">\n    <attribute-override name=\"payload\">\n      <column name=\"payload\" column-definition=\"BYTEA\"\/>\n    <\/attribute-override>\n    <attribute-override name=\"metaData\">\n      <column name=\"metadata\" column-definition=\"BYTEA\"\/>\n    <\/attribute-override>\n  <\/entity>\n\n  <entity class=\"org.axonframework.eventhandling.saga.repository.jpa.SagaEntry\">\n    <attribute-override name=\"serializedSaga\">\n      <column name=\"serializedsaga\" column-definition=\"BYTEA\"\/>\n    <\/attribute-override>\n  <\/entity>\n\n  <entity class=\"org.axonframework.eventhandling.tokenstore.jpa.TokenEntry\">\n    <attribute-override name=\"token\">\n      <column name=\"token\" column-definition=\"BYTEA\"\/>\n    <\/attribute-override>\n  <\/entity>\n\n<\/entity-mappings>\n<\/pre>\n
Somewhere in the Spring Axon configuration there’s a EntityManagerFactoryBean<\/code> defined. It must know about the metadata override file (orm.xml<\/code> in this case) by setting the mapping resources.<\/p>\n
@Bean(name = \"axonEntityManagerFactory\")\n@Primary\npublic LocalContainerEntityManagerFactoryBean axonEntityManagerFactory(\n    DataSource dataSource,\n    @Qualifier(\"jpaProperties\") Properties jpaProperties) {\n  final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();\n  em.setDataSource(dataSource);\n  em.setPackagesToScan(\n    \"org.axonframework.eventsourcing.eventstore.jpa\",\n    \"org.axonframework.eventhandling.saga.repository.jpa\",\n    \"org.axonframework.eventhandling.tokenstore.jpa\");\n  em.setJpaVendorAdapter(jpaVendorAdapter());\n  em.setJpaProperties(jpaProperties);\n  em.setMappingResources(\"\/orm.xml\");\n  return em;\n}\n<\/pre>\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":89,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":""},"categories":[113,81,31,98],"tags":[80,438,107,439,440],"class_list":["post-15107","post","type-post","status-publish","format-standard","hentry","category-axon","category-axon-framework","category-java","category-microservices","tag-axon-framework","tag-hibernate","tag-microservices","tag-postgresql","tag-toast"],"acf":[],"yoast_head":"\nUsing Axon with PostgreSQL without TOAST - Trifork Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using Axon with PostgreSQL without TOAST - Trifork Blog\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/\" \/>\n<meta property=\"og:site_name\" content=\"Trifork Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-10-09T07:05:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\" \/>\n<meta name=\"author\" content=\"Patrick Kik\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Patrick Kik\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/\",\"url\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/\",\"name\":\"Using Axon with PostgreSQL without TOAST - Trifork Blog\",\"isPartOf\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\",\"datePublished\":\"2017-10-09T07:05:58+00:00\",\"author\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/804a91e87bb08ce5fa231668f9a4f347\"},\"breadcrumb\":{\"@id\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#primaryimage\",\"url\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\",\"contentUrl\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/trifork.nl\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using Axon with PostgreSQL without TOAST\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/trifork.nl\/blog\/#website\",\"url\":\"https:\/\/trifork.nl\/blog\/\",\"name\":\"Trifork Blog\",\"description\":\"Keep updated on the technical solutions Trifork is working on!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/trifork.nl\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/804a91e87bb08ce5fa231668f9a4f347\",\"name\":\"Patrick Kik\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a09a7d756b544d2b48846fe320107ebc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a09a7d756b544d2b48846fe320107ebc?s=96&d=mm&r=g\",\"caption\":\"Patrick Kik\"},\"url\":\"https:\/\/trifork.nl\/blog\/author\/patrickk\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using Axon with PostgreSQL without TOAST - Trifork Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/","og_locale":"en_US","og_type":"article","og_title":"Using Axon with PostgreSQL without TOAST - Trifork Blog","og_description":"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 […]","og_url":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/","og_site_name":"Trifork Blog","article_published_time":"2017-10-09T07:05:58+00:00","og_image":[{"url":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png","type":"","width":"","height":""}],"author":"Patrick Kik","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Patrick Kik","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/","url":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/","name":"Using Axon with PostgreSQL without TOAST - Trifork Blog","isPartOf":{"@id":"https:\/\/trifork.nl\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#primaryimage"},"image":{"@id":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#primaryimage"},"thumbnailUrl":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png","datePublished":"2017-10-09T07:05:58+00:00","author":{"@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/804a91e87bb08ce5fa231668f9a4f347"},"breadcrumb":{"@id":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#primaryimage","url":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png","contentUrl":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png"},{"@type":"BreadcrumbList","@id":"https:\/\/trifork.nl\/blog\/axon-postgresql-without-toast\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/trifork.nl\/blog\/"},{"@type":"ListItem","position":2,"name":"Using Axon with PostgreSQL without TOAST"}]},{"@type":"WebSite","@id":"https:\/\/trifork.nl\/blog\/#website","url":"https:\/\/trifork.nl\/blog\/","name":"Trifork Blog","description":"Keep updated on the technical solutions Trifork is working on!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/trifork.nl\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/804a91e87bb08ce5fa231668f9a4f347","name":"Patrick Kik","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a09a7d756b544d2b48846fe320107ebc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a09a7d756b544d2b48846fe320107ebc?s=96&d=mm&r=g","caption":"Patrick Kik"},"url":"https:\/\/trifork.nl\/blog\/author\/patrickk\/"}]}},"_links":{"self":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/users\/89"}],"replies":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/comments?post=15107"}],"version-history":[{"count":0,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15107\/revisions"}],"wp:attachment":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/media?parent=15107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/categories?post=15107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/tags?post=15107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}