Axon 3 adds a new autoincrement primary key to the DomainEventEntry table and removes a column from the unique index that is the primary key under Axon 2. That presents a bit of a migration challenge for nontrivial data sets on applications that have to minimize downtime.
Anyone have any migration tools/scripts to share? Here’s the SQL script (with a tiny bit of pseudocode) I came up with. I’d do all this while the application is still on Axon 2, which will just ignore the new column, so that it’s out of the critical path for deploying the Axon 3 version of the code. The goal here is to do the migration while the Axon 2 app is live, even if the migration takes longer than it might take if we took the app offline. This could be done arbitrarily far in advance of rolling out Axon 3. Critiques/comments appreciated!
-Steve
– Add the column in nullable form first.
ALTER TABLE domainevententry ADD globalindex BIGINT;
– Update the existing rows.
WITH time_ordered AS (
��� SELECT eventidentifier, ROW_NUMBER() OVER (ORDER BY timestamp, sequencenumber) AS seq
��� FROM domainevententry
)
UPDATE domainevententry
SET globalindex = time_ordered.seq
FROM time_ordered
WHERE domainevententry.eventidentifier = time_ordered.eventidentifier;
– Index the column. Newly-inserted rows are null at this point, but that’s okay.
– We’ll be renaming this index later.
CREATE UNIQUE INDEX CONCURRENTLY tmp_idx ON domainevententry (globalindex);
– The above two steps probably took a long time; update newly-inserted rows.
WITH time_ordered AS (
��� SELECT eventidentifier, ROW_NUMBER() OVER (ORDER BY timestamp, sequencenumber) AS seq
��� FROM domainevententry
)
UPDATE domainevententry
SET globalindex = time_ordered.seq
FROM time_ordered
WHERE domainevententry.eventidentifier = time_ordered.eventidentifier
AND globalindex IS NULL;
– Create the sequence, leaving a small gap above the current maximum.
SELECT MAX(globalindex) FROM domainevententry;
– The value 50 here is kind of arbitrary; it just needs to be greater than
– the number of events that could possibly be inserted between the SELECT
– above and the ALTER COLUMN below, but smaller than the largest gap the tracking
– event processor will skip over.
CREATE SEQUENCE domainevententry_globalindex_seq
��� START + 50
��� OWNED BY domainevententry.globalindex;
– Start assigning numbers to newly-inserted rows.
ALTER TABLE domainevententry
� � ALTER COLUMN globalindex SET DEFAULT NEXTVAL(‘domainevententry_globalindex_seq’);
– Fill in the numbers for any rows that were inserted while creating the sequence.
WITH…
– Now there should be no null values, so we can make the column NOT NULL.
ALTER TABLE domainevententry
��� ALTER COLUMN globalindex SET NOT NULL;
– At this point the primary key is still on (aggregateidentifier, sequencenumber, type)
– whereas on Axon 3 the PK is on globalindex and there is a unique constraint on
– (aggregateidentifier, sequencenumber). First create the Axon-3 style unique index.
CREATE UNIQUE INDEX CONCURRENTLY domainevententry_aggregateidentifier_sequencenumber_key
��� ON domainevententry (aggregateidentifier, sequencenumber);
– Make it a constraint to keep the table metadata tidy.
ALTER TABLE domainevententry
� � ADD UNIQUE USING INDEX domainevententry_aggregateidentifier_sequencenumber_key;
– Drop the primary key and its associated index. The unique constraint we just added
– will keep Axon 2 sufficiently fast.
ALTER TABLE domainevententry
��� DROP CONSTRAINT domainevententry_pkey CASCADE;
– Rename the new index so it follows the PostgreSQL index naming convention.
ALTER INDEX tmp_idx RENAME TO domainevententry_pkey;
– Promote the globalindex index to primary key.
ALTER TABLE domainevententry
��� ADD PRIMARY KEY USING INDEX domainevententry_pkey;