Batch processing method of an ai database based on postgresql
By building a sliding window in the PostgreSQL database and using a thread pool for batch processing, the problem of batch prediction of models in the PostgreSQL AI database is solved, achieving fast data processing and efficient output results.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- COMPUTER INNOVATION TECH RES INST OF ZHEJIANG UNIV
- Filing Date
- 2024-01-31
- Publication Date
- 2026-07-28
AI Technical Summary
Existing AI databases based on PostgreSQL struggle to achieve batch predictions without modifying the kernel, resulting in limited output inference speed.
By employing sliding window and thread pool techniques, a sliding window is constructed within the PostgreSQL database for batch processing, including data loading, preprocessing, model inference calculation, and result type conversion. Window functions and thread pools are used for parallel processing to achieve batch output.
Without modifying the database kernel, data processing time was significantly shortened, hardware resource utilization was improved, and batch prediction and rapid output of the model were achieved.
Smart Images
Figure CN118069720B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the fields of databases, deep learning, and neural networks, and in particular to a batch processing method for AI databases based on PostgreSQL. Background Technology
[0002] PostgreSQL is a powerful open-source database widely used in industry. Many AI database products based on the PostgreSQL kernel have encountered difficulties in upgrading the kernel, limiting the introduction of new community features. On the other hand, PostgreSQL-based AI databases, without modifying the kernel, are limited by the Volcano model, making it difficult to achieve batch predictions and significantly restricting the speed of outputting inference results. The key to resolving the contradiction between upgrading the PostgreSQL kernel and achieving batch prediction lies in finding a batch processing method that does not modify the kernel. Summary of the Invention
[0003] To address the problems in the background technology, this invention proposes a batch processing method for AI databases based on PostgreSQL. Without modifying the database kernel, it achieves batch processing of data loading, data preprocessing, AI model inference calculations, and result type conversion, significantly reducing the time users spend waiting for data to be processed by the PostgreSQL-based AI database.
[0004] The technical solution adopted in this invention is:
[0005] 1) Based on multiple data records in the PostgreSQL database, the data records are input into the AI model for inference calculation. The inference calculation process includes loading data, data preprocessing, AI model inference calculation, and result type conversion in sequence.
[0006] 2) Use the built-in window functions of the PostgreSQL database to build a sliding window. Use the sliding window to batch process the inference calculation process, and then output the data after inference calculation one by one by sliding the window backward.
[0007] In step 2), the batch processing of the inference calculation using a sliding window includes the following steps:
[0008] 2.1) Loading data: Accumulates multiple consecutive data entries, starting from the first data entry, into a sliding window of a preset size;
[0009] 2.2) Data Preprocessing: The thread pool is used to simultaneously convert the data types of multiple data items within the sliding window, transforming the data types of the data within the sliding window into the data types required by the AI model. Each converted data item within the sliding window consists of multiple sets of n-dimensional data. The multiple sets of n-dimensional data within the sliding window are then subjected to dimensionality upscaling to obtain a batch of AI model data to be input. This batch of AI model data consists of multiple sets of n+1-dimensional data, where n is a positive integer greater than or equal to 1.
[0010] 2.3) AI Model Inference and Calculation: After a batch of data to be input into the AI model is input into the AI model, the AI model performs inference and calculation to obtain a batch of output data;
[0011] 2.4) Result type conversion: Split a batch of output data into multiple output data, with the number of multiple output data being the same as the number of data in the sliding window. Use a thread pool to simultaneously convert the data types of multiple output data to the data types of the PostgreSQL database.
[0012] In step 2), the output of the inference-calculated data using a backward sliding window method is specifically as follows:
[0013] When the data in the sliding window accumulates to a preset number of consecutive data entries, the batch processing steps 2.2), 2.3), and 2.4) are performed sequentially.
[0014] After each batch processing is completed, the sliding window slides forward one data point, meaning the sliding window's step size is one data point. The first batch-processed output data in the current sliding window is output, and a new unprocessed data point is added to the sliding window as the last data point of the current sliding window. This process continues until all data in the sliding window is unprocessed, triggering the next batch processing. Ultimately, multiple batch-processed output data points from the PostgreSQL database are output sequentially.
[0015] Each time the sliding window slides backward, the data within the sliding window is checked by the built-in window detection module of the PostgreSQL database to determine whether all the data within the sliding window is data that has not been batch processed.
[0016] When all the data in the sliding window is data that has not been batch processed, perform the batch processing steps 2.2), 2.3), and 2.4) in sequence;
[0017] When the data in the sliding window includes batch-processed output data, the sliding window slides backward and outputs the first batch-processed output data in the current sliding window, while adding a new, unprocessed data item to the sliding window as the last data item.
[0018] As the sliding window slides backward, when the number of batch-processed output data reaches the preset number, the built-in cache cleanup module of the PostgreSQL database cleans up and releases all batch-processed output data, i.e., batch cache cleanup processing.
[0019] The thread pool is a multi-threaded processing tool, and the AI model is a type of deep learning model.
[0020] This invention enables batch processing of data loading, data preprocessing, AI model inference calculation, and result type conversion without modifying the database kernel, greatly reducing the time users spend waiting for AI databases based on PostgreSQL to process data.
[0021] The beneficial effects of this invention are:
[0022] It resolves the contradiction between kernel upgrades and batch processing faced by PostgreSQL-based AI databases. It enables batch prediction of models while achieving a smooth upgrade of the PostgreSQL kernel. It facilitates the incorporation of the latest community achievements, improves hardware resource utilization, shortens the latency of output prediction results, and provides more possibilities for further reducing the output prediction result time. Attached Figure Description
[0023] Figure 1 This is a flowchart illustrating the steps of batch processing inference calculations using a sliding window, as described in this invention. Detailed Implementation
[0024] The present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments.
[0025] like Figure 1 As shown, the batch processing method for AI databases based on PostgreSQL includes the following steps:
[0026] 1) Based on multiple data records in the PostgreSQL database, the data records are input into the AI model for inference calculation. The inference calculation process includes loading data, data preprocessing, AI model inference calculation, and result type conversion in sequence.
[0027] 2) Use the built-in window functions of the PostgreSQL database to build a sliding window. Use the sliding window to batch process the inference calculation process, and then output the data after inference calculation one by one by sliding the window backward.
[0028] The process of batch processing inference calculations using a sliding window includes the following steps:
[0029] 2.1) Loading data: Accumulates multiple consecutive data entries, starting from the first data entry, into a sliding window of a preset size;
[0030] 2.2) Data Preprocessing: The thread pool is used to simultaneously convert the data types of multiple data items within the sliding window, transforming the data types of the data within the sliding window into the data types required by the AI model. Each converted data item within the sliding window consists of multiple sets of n-dimensional data. The multiple sets of n-dimensional data within the sliding window are then subjected to dimensionality upscaling to obtain a batch of AI model data to be input. This batch of AI model data consists of multiple sets of n+1-dimensional data, where n is a positive integer greater than or equal to 1.
[0031] 2.3) AI Model Inference and Calculation: After a batch of data to be input into the AI model is input into the AI model, the AI model performs inference and calculation to obtain a batch of output data;
[0032] 2.4) Result type conversion: Split a batch of output data into multiple output data, with the number of multiple output data being the same as the number of data in the sliding window. Use a thread pool to simultaneously convert the data types of multiple output data to the data types of the PostgreSQL database.
[0033] In step 2), the data output after inference calculation is specifically presented using a backward sliding window method as follows:
[0034] When the data in the sliding window accumulates to a preset number of consecutive data entries, the batch processing steps 2.2), 2.3), and 2.4) are performed sequentially.
[0035] After each batch processing is completed, the sliding window slides forward one data point, meaning the sliding window's step size is one data point. The first batch-processed output data in the current sliding window is output, and a new unprocessed data point is added to the sliding window as the last data point of the current sliding window. When all the data in the sliding window is unprocessed, the next batch processing is triggered. Finally, multiple batch-processed output data points from the PostgreSQL database are output sequentially.
[0036] Each time the sliding window slides backward, the data within the sliding window is checked by the built-in window detection module of the PostgreSQL database to determine whether all the data within the sliding window is data that has not been batch processed.
[0037] When all the data in the sliding window is data that has not been batch processed, perform batch processing steps 2.2), 2.3), and 2.4) in sequence;
[0038] When the data in the sliding window includes batch-processed output data, the sliding window slides backward and outputs the first batch-processed output data in the current sliding window, while adding a new, unprocessed data item to the sliding window as the last data item.
[0039] As the sliding window slides backward, when the number of batch-processed output data reaches the preset number, the built-in cache cleanup module of the PostgreSQL database cleans up and releases all batch-processed output data, i.e., batch cache cleanup processing.
[0040] Thread pools are multi-threaded processing tools, and AI models are a type of deep learning model.
[0041] Example 1
[0042] In this embodiment, the hardware is a computer device with a 16-core CPU, and the selected AI model is an emotion classification model in deep learning models. First, one hundred Chinese sentences are stored in the database, with each Chinese sentence being an input data. The AI model is called through an SQL statement, and after processing by the AI model, the output is whether the emotion of the sentence is positive or negative.
[0043] The SQL statements for creating a table and inserting data are as follows:
[0044] “create table test_emotion(id int, words varchar)”, “insert into test_emotionvalues(2,“There are many unknown secrets in the mountains, do not go to the mountains easily”)”;
[0045] The SQL statement for batch processing is as follows:
[0046] "select predict_batch(words)over(current row and following 8rows)fromtest_emotion"
[0047] The specific process of batch processing is as follows:
[0048] 1) Data loading: Use the built-in window functions of the PostgreSQL database to build a sliding window and copy the eight Chinese statements into the state variable of the sliding window;
[0049] 2) Data preprocessing: Start an eight-thread thread pool to convert each statement into a one-dimensional token vector in the encoded state in parallel, and then merge the eight one-dimensional token vectors into a one-dimensional token vector;
[0050] 3) AI model inference calculation: The two-dimensional token vector is input into the emotion classification model. After processing by the emotion classification model, a one-dimensional vector and a dictionary are obtained. The one-dimensional vector contains eight elements, each of which is 0 or 1. The dictionary contains two elements. The first element has a key of 0 and a value of "positive". The second element has a key of 1 and a value of "negative".
[0051] 4) Result type conversion: Start the thread pool, and according to the mapping relationship between key and value in the dictionary, use the thread pool to process each element of the one-dimensional vector in parallel to obtain string data, resulting in eight string data. Finally, copy the eight string data into the state variable of the sliding window.
[0052] When the data in the sliding window accumulates to a preset number of consecutive data entries, steps 2), 3), and 4) are performed in sequence for batch processing. After each batch processing, the sliding window slides one data entry forward, meaning the sliding window's step size is one data entry. The first batch-processed output data entry in the current sliding window is output, i.e., a string type data entry. At the same time, a new unprocessed data entry is added to the sliding window as the last data entry in the current sliding window. When all the data in the sliding window is unprocessed, the next batch processing is triggered. Finally, the emotional attributes corresponding to the one hundred batch-processed Chinese sentences are output sequentially.
[0053] Comparative Example 1
[0054] Comparative Example 1 uses the same conditions as Example 1, that is, the selected AI model, data and hardware are the same as in Example 1. However, batch processing is not involved in the data loading, data preprocessing, AI model inference calculation and result type conversion. By comparing the time difference between supporting parallel processing and not supporting parallel processing, the time required to output all results in the case of not supporting parallel processing is 5 times that of supporting parallel processing.
[0055] This invention enables batch processing of data loading, data preprocessing, AI model inference calculation, and result type conversion without modifying the database kernel, greatly reducing the time users spend waiting for AI databases based on PostgreSQL to process data.
Claims
1. A batch processing method for AI databases based on PostgreSQL, characterized in that: The method includes the following steps: 1) Based on multiple data records in the PostgreSQL database, the data records are input into the AI model for inference calculation. The inference calculation process includes loading data, data preprocessing, AI model inference calculation, and result type conversion in sequence. 2) Use the built-in window functions of the PostgreSQL database to build a sliding window, use the sliding window to batch process the inference calculation process, and then output the inference calculation data one by one in the backward sliding window manner. In step 2), the batch processing of the inference calculation using a sliding window includes the following steps: 2.1) Loading data: This function sequentially accumulates multiple consecutive data entries, starting from a single entry, into a sliding window of a preset size. 2.2) Data Preprocessing: The thread pool is used to simultaneously convert the data types of multiple data items within the sliding window, transforming the data types of the data within the sliding window into the data types required by the AI model. Each converted data item within the sliding window consists of multiple sets of n-dimensional data. The multiple sets of n-dimensional data within the sliding window are then subjected to dimensionality upscaling to obtain a batch of AI model data to be input. This batch of AI model data consists of multiple sets of n+1-dimensional data, where n is a positive integer greater than or equal to 1. 2.3) AI Model Inference and Calculation: After a batch of data to be input into the AI model is input into the AI model, the AI model performs inference and calculation to obtain a batch of output data; 2.4) Result type conversion: Split a batch of output data into multiple output data, with the number of multiple output data being the same as the number of data in the sliding window. Use a thread pool to simultaneously convert the data types of multiple output data to the data types of the PostgreSQL database.
2. The batch processing method for AI databases based on PostgreSQL according to claim 1, characterized in that: In step 2), the output of the inference-calculated data using a backward sliding window method is specifically as follows: When the data in the sliding window accumulates to a preset number of consecutive data entries, steps 2.2), 2.3), and 2.4) are performed sequentially. After each batch processing is completed, the sliding window slides backward, outputting the first batch-processed data in the current sliding window, and adding a new unprocessed data to the sliding window. This continues until all data in the sliding window is unprocessed, triggering the next batch processing. Finally, multiple batch-processed data from the PostgreSQL database are output sequentially.
3. The batch processing method for AI databases based on PostgreSQL according to claim 2, characterized in that: Each time the sliding window slides backward, the data within the sliding window is checked by the built-in window detection module of the PostgreSQL database to determine whether all the data within the sliding window is data that has not been batch processed. When all the data in the sliding window is data that has not been batch processed, perform the batch processing steps 2.2), 2.3), and 2.4) in sequence; When the data in the sliding window includes batch-processed output data, the sliding window slides backward and outputs the first batch-processed output data in the current sliding window, while adding a new unprocessed data item to the sliding window.
4. The batch processing method for AI databases based on PostgreSQL according to claim 2, characterized in that: As the sliding window scrolls backward, when the number of batch-processed output data reaches the preset number, the built-in cache cleanup module of the PostgreSQL database cleans up and releases all batch-processed output data.
5. The batch processing method for AI databases based on PostgreSQL according to claim 1, characterized in that: The thread pool is a multi-threaded processing tool, and the AI model is a type of deep learning model.