Granted Patent B2

US 12,737,346 · App. 19/039,010 · Granted Sep 15, 2026

Inventor: Haifeng Wu (Shanghai, CN)

Assignee: PayPal, Inc.

G06F16/2365G06F16/2282

Abstract

Claims (57)

1 . A method, comprising:

maintaining, by a database system, a point in time (PIT) database, wherein the maintaining includes:

updating a snapshot table included in the PIT database according to a snapshot table time-to-live (TTL) value of 3K/2, wherein the updated snapshot table is configured to provide accurate data for queries up to K years prior to PITs at which the queries are executed, and wherein K is a number of years greater than or equal to one;

updating a binlog table included in the PIT database according to a binlog table TTL value of K;

receiving, by the database system from a computer system, a request to access PIT data stored in the PIT database;

accessing, by the database system based on a timestamp specified in the request, the PIT database, including generating a set of PIT data by accessing the snapshot table and the binlog table based on a point in time prior to the timestamp; and

transmitting, by the database system to the computer system, the set of PIT data, wherein the set of PIT data includes accurate data for K years prior to the timestamp specified in the request.

2 . The method of claim 1 , wherein accessing the PIT database includes:

querying the snapshot table included in the PIT database to retrieve a latest snapshot that corresponds to a point in time that is prior to the timestamp;

querying the binlog table included in the PIT database to retrieve one or more binlogs corresponding to points-in-time between the latest snapshot and the timestamp; and

replaying the one or more binlogs to the latest snapshot to generate the set of PIT data that corresponds to the timestamp specified in the request.

3 . The method of claim 1 , wherein respective binlog records stored in the binlog table indicate one or more changes made to a given database record stored in a production database maintained by the database system, and wherein a given snapshot indicates a state of a database record at a given point in time.

4 . The method of claim 1 , wherein the maintaining further includes:

executing, according to a snapshot dump TTL of K/2, a dump of production snapshots from a production database maintained by the database system into the snapshot table of the PIT database, wherein the production snapshots correspond to database changes specified in one or more database operations and executed by a production ingestion service of the database system on the production database.

5 . The method of claim 1 , wherein updating the snapshot table included in the PIT database further includes calculating the TTL value for the snapshot table according to a K value specified by a system administrator of the database system.

6 . The method of claim 1 , wherein the database system maintains records in a production database with timestamps that are a plurality of years before timestamps of records maintained by the database system in the PIT database, and wherein the PIT database stores records indicating changes made to production records during a previous K years.

7 . The method of claim 1 , further comprising:

querying, by the database system based on a date range within the last K years, the PIT database; and

training, by the database system using a plurality of records retrieved from the PIT database via the querying, a machine learning model, wherein the plurality of records retrieved from the PIT database include key-value pairs having keys that include timestamps that fall within the date range specified by the querying.

8 . A non-transitory computer-readable medium having program instructions stored therein that are executable by a database system to perform operations comprising:

maintaining a point in time (PIT) database, wherein the maintaining includes:

updating a snapshot table included in the PIT database according to a snapshot table time-to-live (TTL) value of 3K/2, wherein the updated snapshot table provides accurate data for queries up to K years prior to PITs at which the queries are executed, and wherein K is a number of years greater than or equal one;

updating a binlog table included in the PIT database according to a binlog table TTL value of K;

receiving, from a computer system, a request to access PIT data stored in the PIT database;

accessing, based on a timestamp specified in the request, the PIT database, including accessing one or both of the snapshot table and the binlog table based on a point in time prior to the timestamp;

generating, based on accessing one or both of the snapshot table and the binlog table, a set of PIT data corresponding to the timestamp specified in the request; and

transmitting, to the computer system, the set of PIT data, wherein the set of PIT data includes accurate data for K years prior to the timestamp specified in the request.

9 . The non-transitory computer-readable medium of claim 8 , wherein the maintaining further includes:

executing, according to a snapshot dump TTL of K/2, a dump of production snapshots from a production database into the snapshot table of the PIT database.

10 . The non-transitory computer-readable medium of claim 8 , wherein accessing the PIT database includes:

querying the snapshot table included in the PIT database to retrieve a latest snapshot that corresponds to a point in time that is prior to the timestamp;

querying the binlog table included in the PIT database to retrieve one or more binlogs corresponding to points-in-time between the latest snapshot and the timestamp; and

replaying the one or more binlogs to the latest snapshot to generate the set of PIT data that corresponds to the timestamp specified in the request.

11 . The non-transitory computer-readable medium of claim 8 , further comprising:

executing, based on a plurality of requests received from one or more computer systems, a plurality of database operations on a production database; and

updating, based on the executing, the binlog table, wherein the updating includes appending, via a PIT ingestion service of the database system, a plurality of binlogs onto the binlog table based on a binlog dump of successful changes made to the production database according to the plurality of database operations.

12 . The non-transitory computer-readable medium of claim 8 , wherein updating the snapshot table included in the PIT database further includes calculating the TTL value for the snapshot table according to a K value specified by a system administrator of the database system.

13 . The non-transitory computer-readable medium of claim 8 , wherein the database system maintains records in a production database with timestamps that are a plurality of years before timestamps of records maintained by the database system in the PIT database, and wherein the PIT database stores records indicating changes made to production records during a previous K years.

14 . The non-transitory computer-readable medium of claim 8 , wherein updating the snapshot table includes removing one or more snapshots stored in the snapshot table that include keys with point in time values that are older than a point in time specified by the snapshot table TTL value.

15 . A system comprising:

a production database;

a point in time (PIT) database that includes a PIT snapshot table and a PIT binlog table;

a processor; and

a non-transitory computer-readable medium having stored thereon instructions that are executable by the processor to cause the system to perform operations comprising:

executing, according to a snapshot dump time-to-live (TTL) calculated using a first algorithm, a dump of production snapshots stored in the production database into the PIT snapshot table of the PIT database, wherein a given snapshot stores a plurality of binlogs;

updating, according to a PIT snapshot TTL calculated using to a second algorithm, the PIT snapshot table;

updating, according to a PIT binlog TTL calculated using to a third algorithm, the PIT binlog table, wherein the second algorithm is based on the PIT binlog TTL; and

during the executing, the updating the PIT snapshot table, and the updating the PIT binlog table, servicing a plurality of queries for data stored in the PIT database, wherein servicing a given query of the plurality of queries includes:

accessing the PIT database at least to retrieve a latest snapshot and one or more binlogs from the PIT binlog table, wherein the latest snapshot corresponds to a point in time prior to a timestamp specified in the given query and the one or more binlogs correspond to points-in-time between the latest snapshot and the timestamp.

16 . The system of claim 15 , wherein executing the dump includes converting the production snapshots into PIT snapshot format according to a schema of the PIT snapshot table, and wherein the converting includes altering private keys of the production snapshots to a format corresponding to the schema of the PIT snapshot table.

17 . The system of claim 15 , wherein the instructions are further executable to cause the system to perform operations comprising:

calculating the snapshot dump TTL using the first algorithm including determining to perform the snapshot dump TTL every K/2 years, wherein K is a real number with a value greater than or equal to one.

18 . The system of claim 15 , wherein the instructions are further executable to cause the system to perform operations comprising:

calculating the PIT snapshot TTL using the second algorithm including determining that the PIT snapshot TTL for the PIT snapshot table is 3K/2 years, wherein K is a real number with a value greater than or equal to one.

19 . The system of claim 15 , wherein the instructions are further executable to cause the system to perform operations comprising:

calculating the PIT binlog TTL using the third algorithm including determining that the PIT binlog TTL for the PIT binlog table is K years, wherein K is a real number with a value greater than or equal to one.

20 . The system of claim 15 , wherein the updating the PIT snapshot table includes removing one or more snapshots stored in the PIT snapshot table that include keys with PIT values that are older than the PIT specified by the PIT snapshot TTL.

Assignments (1)

Priority Claims (1)

Continuity (1)

References Cited (18)