Back in the day when databases were on physical disks and seek times are slower, I was told that one performance tweak that can be done is to avoid doing UPDATE or DELETE in a table and instead rely on INSERT. This was also to avoid locking the rows.
I've seen the supersededBy pattern in use with a large enterprise application as well.
So if I were to implement the Temporal Property pattern I was wondering if those ideas still hold. This is what I have without the supersededBy
create table my_temporal_property (
id BINARY(16) not null,
parent_id BINARY(16) not null,
name varchar(64) not null,
effective_on DATE not null,
attribute_value MEDIUMTEXT,
primary key (id),
constraint my_temporal_attribute_uc unique (parent_id, name, effective_on));
create table my_temporal_entity (
version_no integer not null,
id BINARY(16) not null,
my_lookup_key varchar(255),
primary key (id),
constraint my_temporal_entity_uc unique (my_lookup_key))
And with the supersededBy
create table my_temporal_property (
id BINARY(16) not null,
parent_id BINARY(16) not null,
name varchar(64) not null,
effective_on DATE not null,
attribute_value MEDIUMTEXT,
superseded_by BINARY(16),
primary key (id),
constraint my_temporal_attribute_uc unique (parent_id, name, effective_on, superseded_by));
The supersededBy pattern also appears to be used in Kafka where they have periodic log compaction to remove the replaced records given a key. This pattern does help in preventing the need for row locks caused by updates or worse table lock escalations. I'm using MYSQL as well.
