0

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.

10
  • Not sure I follow your question...does it effectively boil down to "should one table be used vs two tables when needing temporal capabilities?"? FWIW, with modern hardware, it won't make too much of a difference either way.
    – J.D.
    Commented Jun 27, 2023 at 3:34
  • 1
    UPDATE and DELETE are quite fine on modern hardware. They even were back on mechanical hardware days, depending on the amount of data changes (both in size and frequency) that occurred in a given time period. But especially so nowadays on modern hardware. You'd have to be running those kind of DML statements 100,000s of times (maybe more) a second, with no ability to reduce via batching, before it'd be worth considering an INSERT only implementation.
    – J.D.
    Commented Jun 27, 2023 at 12:27
  • 1
    Please note that's 100,000 statements per second, not rows per second (big difference). I've worked with pretty big data (10s of billions of rows tables that were multi-terabyte). We still were only inserting rows at a rate of around a few thousand per minute, maybe at peek a few hundred every few seconds. These batch DML changes would result in only a couple statements per minute. Updates and delete changes were even less frequent. Unless you're creating the next Google or Facebook, I doubt you'll be doing 100,000 update and delete statements per second.
    – J.D.
    Commented Jun 27, 2023 at 16:30
  • 1
    Not to mention you'd have other much more important optimization focal points, if you were ever at that scale. Also, size of the DML changes matter too. 100,000 single row updates (if each statement was for only one row) will potentially have less locking contention (assuming concurrency matters) than if each update was against the entire table or a majority of it.
    – J.D.
    Commented Jun 27, 2023 at 17:04
  • 1
    I would say that 100 DMLs/second is where you should begin worrying. 100K is hard to achieve except in very limited applications.
    – Rick James
    Commented Jun 27, 2023 at 22:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.