Method and system for ensuring data consistency across partitions in tables with cross-partition updates

Abstract

A system and method for maintaining data consistency in partitioned database tables that store versioned data records. Each record includes a primary key, an effective end time, and a record update time. Records are partitioned based on their effective end times. When an update inserts a new version of a record with the same primary key but a different effective end time, the updated record may be placed in a different partition than the original. To ensure the outdated version is properly removed during partition-local background merge operations, the system generates and inserts a synthetic data record. The synthetic record has the same primary key and effective end time as the original record, but a later record update time, which triggers merge logic within the original partition to identify and remove the outdated record.