Potential SQL for Axon required JPA entities (for Postgres)

Imported from Google Groups
#1

I have the following (auto-generated) script for creating the JPA entities that Axon requires:

`
– Script for creating the Axon required tables.

CREATE TABLE IF NOT EXISTS public.association_value_entry
(
id bigint NOT NULL,
association_key character varying(255) NOT NULL,
association_value character varying(255),
saga_id character varying(255) NOT NULL,
saga_type character varying(255),
CONSTRAINT association_value_entry_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.saga_entry
(
saga_id character varying(255) NOT NULL,
revision character varying(255),
saga_type character varying(255),
serialized_saga oid,
CONSTRAINT saga_entry_pkey PRIMARY KEY (saga_id)
);

CREATE TABLE IF NOT EXISTS public.token_entry
(
processor_name character varying(255) NOT NULL,
segment integer NOT NULL,
owner character varying(255),
“timestamp” character varying(255) NOT NULL,
token oid,
token_type character varying(255),
CONSTRAINT token_entry_pkey PRIMARY KEY (processor_name, segment)
);

`

My question is that the type character varying(255)is used a lot. Would this be better in Postgres as a JSONB column?
I.E. are fields such as association_key and ``association_value always expected to be serialised to JSON in Axon?

Regards,

#2

Hi,

"character varying(255)"is fine if the content varies but never exceeds 255 characters.
It won’t make sense to have e.g. a processor_name that is longer than 255 characters.

The columns, that contain serialized data - however - can get much longer.
These could be defined as JSONB in PostgreSql, if you want to use PostgreSql-specific database columns functions later on.

Here are the DDLs that i did for a showcase using JSONB instead of “old”:
Command-Side:
PostgreSqlJsonbTypeForBinaryData.sql

AxonOnMicroprofileTryoutEventsourcing.sql

Query-Side
PostgreSqlJsonbTypeForBinaryData.sql

AxonOnMicroprofileTryoutTrackingToken.sql

Axon needs adapters to work with JSONB (PostgreSql-specific):
showcase-quarkus-eventsourcing/src/main/java/io/github/joht/showcase/quarkuseventsourcing/messaging/infrastructure/axon/database/postgresql

You don’t need to use JSON for serialized data, if you don’t want do. But it’s the most common way to do it.
Here is my custom serializer, that i use to get axon to run on MicroProfile / Java EE (with JSON-Binding):
showcase-quarkus-eventsourcing/src/main/java/io/github/joht/showcase/quarkuseventsourcing/messaging/infrastructure/axon/serializer

I hope, i got your question right. :slight_smile:

Best regards. Johnny.

#3

Thanks very much for the link to your showcase Johnny - it was very interesting to examine, and definitely helped.

I have a couple of questions for you:

  • In your showcase the schema used for the axon tables is not ‘public’ but rather “axon_on_microprofile_tryout”. How do you get Axon to use that schema for its entities rather than the public one? I am using the JPA entities if that makes any difference.

  • Out of curiosity: I can see that your showcase is AOT compiled. How long does it take for the Axon program to start. Mine routinely takes anywhere from 20-40 seconds and I was curious how long an AOT compiled one took. I’ve seen in the latest spring boot videos that spring boot apps are able to be launched in 0.5seconds - is this also the case for quarkus?
    Regards,
    vab2048

#4

Hi vab2048,

  • In your showcase the schema used for the axon tables is not ‘public’ but rather “axon_on_microprofile_tryout”. How do you get Axon to use that schema for its entities rather than the public one? I am using the JPA entities if that makes any difference.

I should had mentioned that i tried JPA as well as JDBC, and my showcase is based on JDBC.
The schema, custom table names, … can be configured using JPA like this:
https://github.com/JohT/snippets/tree/master/axon-customized-jpa

Axon is very flexible when it comes to configuration.
When using JDBC, you can have a look at method “eventStorageEngine” or “jdbcTokenStore”:
https://github.com/JohT/showcase-quarkus-eventsourcing/blob/master/showcase-quarkus-eventsourcing/src/main/java/io/github/joht/showcase/quarkuseventsourcing/messaging/infrastructure/axon/AxonConfiguration.java

Be aware, that this is made using Java EE and not spring. There might be more “wiring” in my code in contrast to spring.
There is some more code around it since i implemented it for H2 and PostgreSql databases.

I did custom schemas to explore, if axon could be used in multi tenant systems, where the schema is used to separate tenants (and it works).
I was also curios to try out, if query- and command-side could be split apart that way to simulate “another” database (does also work fine).

  • Out of curiosity: I can see that your showcase is AOT compiled. How long does it take for the Axon program to start. Mine routinely takes anywhere from 20-40 seconds and I was curious how long an AOT compiled one took. I’ve seen in the latest spring boot videos that spring boot apps are able to be launched in 0.5seconds - is this also the case for quarkus?

It only needs a couple of seconds for everything (database setup, server, axon, application) and starts really fast.
Since this is only a very small showcase, i can imagine, that a real work project might need longer to start.
On the other hand, i use flyway to setup database schema including some “updates” (as if the schema evolved over time),
which is running at startup and leads to longer start up times.

Best regard,
Johnny.

#5

I tried the ahead of time compiled version: 159ms startup time without database setup, about 1 sec. with schema setup.

#6

1 second startup is pretty amazing!

I think it would make running integration tests a breeze!

How long does it take to compile the binary? From what I’ve seen GraalVM has notoriously slow compile times (could be 1hour+).

Regards,
vab2048

#7

As discussed in Issue 1427 this is a rather complex topic.
Yes, it takes - in my case - about 30 min…
With newer versions up to 1h.

Improvement is possible, but not trivial.

The GraalVM „substrate“ is a good alternative,
since it builds fast and includes optimizations.

Nonetheless this all is fascinating.