Roiocam
commented
1 year ago proposal
After several months, I finally have the time to complete this merge. I've thoroughly reviewed the migration's changes, and here are my considerations.
Regarding the event_tag table, there are two essential components:
- The
orderingused to sequence events in a specific stream. - The
foreign keyused to load event payloads lazily.
lazy event load means join table and query.
In the previous approach, we used the event_id as the foreign key. However, this incurred some costs since we had to wait for all events to be inserted before retrieving their IDs. Moreover, the 'slick' plugin had performance issues with 'InsertAndReturn', which led to individual inserts instead of batch inserts.
On the other hand, we can employ the primary key of the event_journal table instead of the 'ordering' column for the foreign key. This modification removes the need for InsertAndReturn.
To ensure a rolling updates when shifting to the "new way," we propose a phased rollout with steps controlled by a configuration property:
- Continue with the old approach.
- Modify the table structure to allow for a redundant foreign key column, although only one will be used.
- Write both old and new foreign keys, but only read the old foreign key for event lazy-loading (configurable through the configuration property).
- Once the projection catches up with the newly written foreign key's position, migrate the table to the new primary and foreign keys. Alter the table structure to make the old foreign key column nullable.
- Finally, the migration has completed, switch the projection to read the new foreign key and stop writing in the old manner (controlled by configuration properties).
An real world example using MySQL.
1. add new column before migration
ALTER TABLE event_tag
ADD PERSISTENCE_ID VARCHAR(255),
ADD SEQUENCE_NUMBER BIGINT;2. change to redundant write and read via config
jdbc-journal.tables.event_tag.redundant-write = true
jdbc-read-journal.tables.event_tag.redundant-read = true3. waitting for projection catech, and then
-- drop old fk column
DELETE
FROM event_tag
WHERE PERSISTENCE_ID IS NULL
AND SEQUENCE_NUMBER IS NULL;
-- drop old FK constraint
SELECT CONSTRAINT_NAME
INTO @fk_constraint_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME = 'event_tag';
SET @alter_query = CONCAT('ALTER TABLE event_tag DROP FOREIGN KEY ', @fk_constraint_name);
PREPARE stmt FROM @alter_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- drop old PK constraint
ALTER TABLE event_tag
DROP PRIMARY KEY;
-- create new PK constraint for PK column.
ALTER TABLE event_tag
ADD CONSTRAINT
PRIMARY KEY (PERSISTENCE_ID, SEQUENCE_NUMBER, TAG);
-- create new FK constraint for PK column.
ALTER TABLE event_tag
ADD CONSTRAINT fk_event_journal_on_pk
FOREIGN KEY (PERSISTENCE_ID, SEQUENCE_NUMBER)
REFERENCES event_journal (PERSISTENCE_ID, SEQUENCE_NUMBER)
ON DELETE CASCADE;
-- alter the event_id to nullable, so we can skip the InsertAndReturn.
ALTER TABLE event_tag
MODIFY COLUMN EVENT_ID BIGINT UNSIGNED NULL;4. finally, rollback the redundant config
jdbc-journal.tables.event_tag.redundant-write = false
jdbc-read-journal.tables.event_tag.redundant-read = false
patriknw
References #710