Method and system for ensuring data consistency across partitions in tables with cross-partition updates
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.
1 . A method for cross-partition database table management, comprising:
storing a plurality of data records in a database table, each data record including a primary key, an effective end time, and a record update time;
partitioning the plurality of data records in the database table into a plurality of partitions based on effective end times of the plurality of data records;
detecting an update to a first data record in the database table that inserts a second data record to the database table, the second data record having:
a primary key identifier same as the first data record,
an effective end time set to a time when the update becomes effective, and
a record update time indicating the time when the update occurred;
determining whether the first data record and the second data record are assigned to different partitions based on their respective effective end times;
in response to determining that the first data record and the second data record are assigned to different partitions,
generating a synthetic data record, the synthetic data record including:
a primary key identifier same as the first data record,
an effective end time same as the first data record, and
a record update time later than the first data record; and
inserting the synthetic data record into the database table, wherein the synthetic data record and the first data record are assigned to a same partition based on their same effective end times and same primary key identifier, and the record update time of the synthetic data record being later than the first data record causes a background merge operation to identify and remove the first data record during partition-local merge operations.
2 . The method of claim 1 , wherein the update further inserts a third data record to the database table, the third data record having:
a primary key identifier same as the first data record,
an effective start time set to a time when the update becomes effective,
an effective end time set to a far-future time indicating current validity, and
a record update time set to the time when the update occurred.
3 . The method of claim 2 , wherein each data record further includes a latest value indicator indicating whether a corresponding data record is a latest data record,
wherein the latest value indicator of the second data record is set to false, the latest value indicator of the synthetic data record is set to false, and the latest value indicator of the third data record is set to true.
4 . The method of claim 1 , further comprising:
performing a background merge operation based on record update times of data records, comprising, for a plurality of data records associated with a same primary key identifier:
identifying a data record with a latest record update time; and
deleting a remaining data record that has an earlier record update time.
5 . The method of claim 1 , wherein each data record further includes a partition identifier indicating an assigned partition of a corresponding data record.
6 . The method of claim 5 , wherein for each data record, a value of a corresponding partition identifier is derived based on a corresponding effective end time, and data records with effective end times within a predetermined time window are assigned to a same partition identifier.
7 . The method of claim 1 , wherein detecting the update comprises monitoring data insertion operations through a first pre-configured materialized view.
8 . The method of claim 7 , wherein in response to detecting the update via the first pre-configured materialized view, a second pre-configured materialized view generates the synthetic data record.
9 . The method of claim 1 , wherein the record update time of the synthetic data record is set to a predetermined special marker timestamp.
10 . The method of claim 9 , the method further comprising:
receiving a query against the database table;
modifying the query against the database table by including a filter condition to exclude synthetic data records based on the predetermined special marker timestamp in the record update time.
11 . A non-transitory computer readable storage medium having instructions encoded thereon that, when executed by one or more processors, cause the one or more processors to perform steps comprising:
storing a plurality of data records in a database table, each data record including a primary key, an effective end time, and a record update time;
partitioning the plurality of data records in the database table into a plurality of partitions based on effective end times of the plurality of data records;
detecting an update to a first data record in the database table that inserts a second data record to the database table, the second data record having:
a primary key identifier same as the first data record,
an effective end time set to a time when the update becomes effective, and
a record update time indicating the time when the update occurred;
determining whether the first data record and the second data record are assigned to different partitions based on their respective effective end times;
in response to determining that the first data record and the second data record are assigned to different partitions,
generating a synthetic data record, the synthetic data record including:
a primary key identifier same as the first data record,
an effective end time same as the first data record, and
a record update time later than the first data record; and
inserting the synthetic data record into the database table, wherein the synthetic data record and the first data record are assigned to a same partition based on their same effective end times and same primary key identifier, and the record update time of the synthetic data record being later than the first data record causes a background merge operation to identify and remove the first data record during partition-local merge operations.
12 . The non-transitory computer readable storage medium of claim 11 , wherein the update further inserts a third data record to the database table, the third data record having:
a primary key identifier same as the first data record,
an effective start time set to a time when the update becomes effective,
an effective end time set to a far-future time indicating current validity, and
a record update time set to the time when the update occurred.
13 . The non-transitory computer readable storage medium of claim 12 , wherein each data record further includes a latest value indicator indicating whether a corresponding data record is a latest data record,
wherein the latest value indicator of the second data record is set to false, the latest value indicator of the synthetic data record is set to false, and the latest value indicator of the third data record is set to true.
14 . The non-transitory computer readable storage medium of claim 11 , the steps further comprising:
performing a background merge operation based on record update times of data records, comprising: for a plurality of data records associated with a same primary key identifier,
identifying a data record with a latest record update time; and
deleting a remaining data record that has an earlier record update time.
15 . The non-transitory computer readable storage medium of claim 11 , wherein each data record further includes a partition identifier indicating an assigned partition of a corresponding data record.
16 . The non-transitory computer readable storage medium of claim 15 , wherein for each data record, a value of a corresponding partition identifier is derived based on a corresponding effective end time, and data records with effective end times within a predetermined time window are assigned to a same partition identifier.
17 . The non-transitory computer readable storage medium of claim 11 , wherein detecting the update comprises monitoring data insertion operations through a first pre-configured materialized view.
18 . The non-transitory computer readable storage medium of claim 17 , wherein in response to detecting the update via the first pre-configured materialized view, a second pre-configured materialized view generates the synthetic data record.
19 . The non-transitory computer readable storage medium of claim 11 , wherein the record update time of the synthetic data record is set to a predetermined special marker timestamp, and the steps further comprises:
receiving a query against the database table;
modifying the query against the database table by including a filter condition to exclude synthetic data records based on the predetermined special marker timestamp in the record update time.
20 . A system, comprising:
one or more processors; and
a non-transitory computer readable storage medium having instructions encoded thereon that, when executed by the one or more processors, cause the one or more processors to perform steps comprising:
storing a plurality of data records in a database table, each data record including a primary key, an effective end time, and a record update time;
partitioning the plurality of data records in the database table into a plurality of partitions based on effective end times of the plurality of data records;
detecting an update to a first data record in the database table that inserts a second data record to the database table, the second data record having:
a primary key identifier same as the first data record,
an effective end time set to a time when the update becomes effective, and
a record update time indicating the time when the update occurred;
determining whether the first data record and the second data record are assigned to different partitions based on their respective effective end times;
in response to determining that the first data record and the second data record are assigned to different partitions,
generating a synthetic data record, the synthetic data record including:
a primary key identifier same as the first data record,
an effective end time same as the first data record, and
a record update time later than the first data record; and
inserting the synthetic data record into the database table, wherein the synthetic data record and the first data record are assigned to a same partition based on their same effective end times and same primary key identifier, and the record update time of the synthetic data record being later than the first data record causes a background merge operation to identify and remove the first data record during partition-local merge operations.