Batch write method and device for a PostgreSQL database

Abstract

The embodiment of the specification discloses a batch writing method and device of a PostgreSQL database, relates to the technical field of cross-database migration, and is used for solving the problem of poor compatibility and low reliability of the existing mode. The method comprises the following steps: performing cross-database dialect semantic mapping on a heterogeneous data source based on a cross-database semantic mapping engine, obtaining a global concurrent import control parameter set of a target database, and performing extraction on the heterogeneous data source based on the analysis and configuration of the global concurrent import control parameter set to obtain a standard record object; performing flow control on a task channel of the target database based on an adaptive back pressure strategy, assembling the standard record object into a data batch, and distributing the data batch to a work coroutine of the target database; calling a batch writing protocol through the work coroutine, initiating a batch direct writing request to the target database for batch writing, and processing a writing exception based on a preset fault-tolerant mechanism to obtain a writing result, which effectively reduces the data writing risk while ensuring compatibility and improves the reliability of cross-database batch writing.

Description

Technical Field

[0001] This manual relates to the field of cross-database migration technology, and in particular to a batch write method and device for a PostgreSQL database. Background Technology

[0002] PostgreSQL is a relational database with a copy-write protocol interface at its underlying storage. It can bypass the conventional SQL syntax parse tree and write data directly to the data page through binary streams or text streams, which is the foundation for achieving high-performance data import.

[0003] However, in scenarios involving high-concurrency import of massive amounts of heterogeneous data into PostgreSQL databases, such as migrating from enterprise-level databases like Oracle to PostgreSQL, existing PostgreSQL import technologies have several issues. Specifically, current hard-coded parsing methods struggle to recognize the unique dialects of enterprise-level databases like Oracle, and lack dynamic inference capabilities when key parameters such as missing delimiters in control files are missing, leading to parsing interruptions and high costs for manual intervention. Furthermore, existing data pumps use full buffering or stateless push methods, making it difficult for upstream databases to detect data transmission congestion in downstream databases, resulting in data backlogs that can cause memory overflows or deadlocks. On the other hand, write methods based on copy-based write protocols require entire batch rollbacks when individual abnormal data exists; this coarse-grained fault tolerance can lead to transaction pollution.

[0004] Therefore, a high-performance and reliable method for writing batch data from heterogeneous data sources to a PostgreSQL database is needed. Summary of the Invention

[0005] This specification provides one or more embodiments of a batch write method and device for a PostgreSQL database, which solves the following technical problem: There is a need for a high-performance and reliable batch data write method from heterogeneous data sources to a PostgreSQL database.

[0006] One or more embodiments of this specification employ the following technical solutions: This specification provides one or more embodiments of a batch write method for a PostgreSQL database, the method comprising: Based on the cross-database semantic mapping engine, cross-database dialect semantic mapping is performed on heterogeneous data sources to obtain concurrent import parameters of PostgreSQL database and form a global concurrent import control parameter set. Based on the parsing configuration of the global concurrent import control parameter set, the streaming data continuously read from the heterogeneous data source is extracted using the multimodal parsing engine to obtain standard record objects; The task channel of the PostgreSQL database is flow-controlled based on an adaptive backpressure strategy to assemble the standard record objects into data batches and distribute them to the worker coroutines of the PostgreSQL database. The worker coroutine calls the batch write protocol of the PostgreSQL database to initiate a batch write request to the PostgreSQL database and perform batch writes. During the batch writing process, write anomalies are handled based on a pre-set three-level fault tolerance mechanism to obtain the write result.

[0007] Optionally, in one or more embodiments of this specification, cross-database dialect semantic mapping is performed on heterogeneous data sources based on a cross-database semantic mapping engine to obtain concurrent import parameters for the PostgreSQL database, forming a global concurrent import control parameter set, specifically including: The control files of the heterogeneous data source are identified based on a specific format suffix, and feature instructions and missing key parameters of the control files are extracted based on a preset key dictionary; wherein, the feature instructions include: global options, input sources, fault tolerance definitions and table mapping relationships; If dialect feature import is detected based on the feature instruction, the cross-database semantic mapping engine is triggered to perform cross-database dialect semantic mapping on the dialect features of the heterogeneous data source and convert them into concurrent import parameters of the PostgreSQL database. If the missing key parameter is a non-empty set, then the first data probe is used to sniff out multiple lines of content in the data file of the heterogeneous data source, and the frequency and number of occurrences of the preset candidate delimiter in each line of content are counted. Based on the frequency of occurrence and the number of occurrences, the missing features of the missing key parameters are obtained, and default compensation is performed. The concurrent import parameters are assembled with the compensated missing key parameters to generate the global concurrent import control parameter set, and then serialized into memory.

[0008] Optionally, in one or more embodiments of this specification, after constructing a global concurrent import control parameter set, the method further includes: The database connection pool is initialized by using the connection configuration parameters and concurrency parameters of the global concurrency import control parameter set to establish a concurrent connection channel for the PostgreSQL database. Based on the index management strategy in the global concurrency import control parameter set, query the system metadata of the PostgreSQL database, obtain the non-unique index of the PostgreSQL database, and generate the index creation script of the non-unique index for persistent storage. Execute the silent uninstall command to remove the non-unique indexes of the PostgreSQL database, so that the PostgreSQL database enters an index-free write state.

[0009] Optionally, in one or more embodiments of this specification, based on the parsing configuration of the global concurrent import control parameter set, the streaming data continuously read from the heterogeneous data source is extracted using a multimodal parsing engine to obtain standard record objects, specifically including: Based on the second data probe, basic information of the data files from the heterogeneous data source is sniffed out; wherein, the basic information includes: file signature and file extension; Based on the basic information, dynamically route to the corresponding parsing pipeline; If the parsing pipeline is a text format parsing pipeline, then the buffered stream input of the text format data source is parsed based on a finite state machine, so as to encapsulate the complete record of each semantic boundary recognition during the parsing process into a standard record object with a unified data structure. If the parsing pipeline is a spreadsheet format parsing pipeline, then the buffered stream input of the spreadsheet format data source is parsed based on the event-driven mechanism, and the reading event is triggered row by row by the streaming sliding window to encapsulate each row of data into a standard record object with a unified data structure.

[0010] Optionally, in one or more embodiments of this specification, flow control is performed on the task channel of the PostgreSQL database based on an adaptive backpressure strategy to assemble the standard record objects into data batches and distribute them to the worker coroutines of the PostgreSQL database, specifically including: The main control coroutine monitors the load status of the task channel based on a preset high and low water level monitor; wherein, the main control coroutine is used to consume the standard record objects output by the upstream multimodal parsing engine; If, based on the load status, it is determined that the amount of buffered data in the task channel has reached the high water level threshold, a back pressure suspension signal is sent to the upstream multimodal parsing engine to suspend the data reading operation. If, based on the load status, it is determined that the amount of buffered data in the task channel has reached the low water level threshold, a back pressure recovery signal is sent to the upstream multimodal parsing engine to resume the reading operation; During the data reading process, the standard record object is encapsulated based on a preset batch capacity threshold to obtain a data batch, which is then distributed to the working coroutine of the PostgreSQL database.

[0011] Optionally, in one or more embodiments of this specification, during the data reading process, the standard record object is encapsulated based on a preset batch capacity threshold to obtain a data batch, and then distributed to the working coroutine of the PostgreSQL database, specifically including: In response to the read start event of the heterogeneous data source, standard record objects are read sequentially based on the task channel and stored in the memory cache. When the number of records in the memory cache reaches the batch capacity threshold, the corresponding standard record objects are encapsulated into data batches and sent concurrently to the working coroutines of the PostgreSQL database through the task channel. In response to the read completion event of the heterogeneous data source, the remaining standard record objects in the memory cache are obtained, and the remaining standard record objects are encapsulated into a final batch and a close signal is sent.

[0012] Optionally, in one or more embodiments of this specification, the batch write request to the PostgreSQL database is initiated by calling the batch write protocol of the PostgreSQL database through the worker coroutine to perform batch write, specifically including: The task channel is monitored by the worker coroutine to obtain the distributed data batches, and an idle connection of the concurrent connection channel is obtained based on the database connection pool. Based on the session-level interceptor, the performance optimization parameters of the global concurrency import control parameter set are injected into the current database connection session; In response to the performance optimization parameter taking effect event of the current database connection session, based on the batch write protocol, a batch write request is initiated to the PostgreSQL database to perform batch writing.

[0013] Optionally, in one or more embodiments of this specification, during the batch writing process, write anomalies are handled based on a preset three-level fault tolerance mechanism to obtain the write result, specifically including: During the batch writing process, the data batches are directly written in batches, and the write execution status returned by the PostgreSQL database is obtained in real time. If a write error is detected, the write error is intercepted, and it is determined whether the data batch corresponding to the write error is greater than the preset single-item processing threshold. If the value exceeds the preset single-data-processing threshold, the data batch corresponding to the write exception is split into two parts, and the split sub-data batches are recursively retried for batch direct write. Based on the batch writing results of the sub-data batch, if there are failures and the preset single-row processing threshold is not reached, then the failed sub-batch is recursively split into binary and batch retried; otherwise, the batch writing is downgraded to single-row insertion. The global error count for the batch write is accumulated to trigger a circuit breaker based on the fault tolerance threshold, thereby stopping the write and persisting the breakpoint state.

[0014] Optionally, in one or more embodiments of this specification, after handling write anomalies based on a preset three-level fault tolerance mechanism and obtaining the write result during the batch write process, the method further includes: Based on the writing results, the task status of the batch writing is obtained; wherein, the task status includes: aborted status and completed status; If the task status is completed, then read the index creation script and rebuild the non-unique index of the PostgreSQL database; If the task status is aborted, then obtain the recovery script of the index creation script so that maintenance personnel can maintain it.

[0015] This specification provides one or more embodiments of a batch write device for a PostgreSQL database, the device comprising: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform any of the methods described above.

[0016] The above-described at least one technical solution adopted in the embodiments of this specification can achieve the following beneficial effects: By performing cross-database dialect semantic mapping on heterogeneous data sources, concurrent import parameters for the PostgreSQL database are obtained, achieving seamless compatibility with heterogeneous data source dialects. Based on the concurrent import parameters, a global concurrent import control parameter set is constructed, serving as the sole global state benchmark for subsequent engines, ensuring end-to-end consistency. A multimodal parsing engine extracts streaming data continuously read from heterogeneous data sources to obtain standard record objects, resolving the issue of inconsistent data structures in parsed results of different formats, which necessitates writing different processing code for each format in downstream processing logic, leading to high system coupling and poor scalability. Adaptive backpressure strategy-based flow control resolves memory overflow and deadlock issues. Assembling standard record objects into data batches effectively balances network transmission efficiency and transaction granularity. A pre-built three-tiered fault tolerance mechanism handles write exceptions, ensuring data integrity while isolating errors. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart illustrating a batch write method for a PostgreSQL database provided in the embodiments of this specification; Figure 2 This specification provides a system architecture diagram for batch writing of a PostgreSQL database, as illustrated in the embodiments of this specification. Figure 3 The main flowchart of a batch write process for a PostgreSQL database provided in the embodiments of this specification; Figure 4 This specification provides a schematic diagram of a control file parsing process as illustrated in an embodiment. Figure 5 A class diagram of a format parsing engine provided in the embodiments of this specification; Figure 6 This is a schematic diagram illustrating the acquisition of a standard record object provided in this embodiment. Figure 7 A schematic diagram of a concurrent scheduling architecture provided for an embodiment of this specification; Figure 8 A schematic diagram of an architecture for a pre-configured three-tiered fault tolerance mechanism provided in the embodiments of this specification; Figure 9 A schematic diagram illustrating the workflow of index management provided in the embodiments of this specification; Figure 10 This is a schematic diagram of a parameter configuration process provided in an embodiment of this specification; Figure 11 This is a schematic diagram of the structure of a batch write device for a PostgreSQL database provided in an embodiment of this specification. Detailed Implementation

[0018] This specification provides a method and device for batch writing to a PostgreSQL database.

[0019] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this specification, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this specification.

[0020] like Figure 1 The embodiments shown in this specification provide a batch write method for a PostgreSQL database, the method comprising: S101: Based on the cross-database semantic mapping engine, cross-database dialect semantic mapping is performed on heterogeneous data sources to obtain concurrent import parameters of PostgreSQL database and form a global concurrent import control parameter set.

[0021] Based on the aforementioned background technology, it is known that in scenarios involving high-concurrency import of massive heterogeneous data into PostgreSQL databases, such as the migration from enterprise-level databases like Oracle to PostgreSQL, existing PostgreSQL import methods generally employ static hard-coded parsing when handling control files from heterogeneous data sources. For example, when dealing with Oracle SQL Loader's .ctl control files, existing tools cannot recognize Oracle's unique import dialect, necessitating extensive manual cleaning and script reconstruction during cross-database migrations. To address this issue, such as... Figure 2 The heterogeneous data source shown is as follows: Figure 2 The external data source shown needs to be parsed and semantically mapped. For example... Figure 3 As shown, the parsing and semantic mapping process involves triggering the cross-database semantic mapping engine to perform cross-database dialect semantic mapping on heterogeneous data sources to obtain concurrent import parameters for the PostgreSQL database, thus forming a global concurrent import control parameter set.

[0022] Furthermore, in existing write methods, when key parameters such as field delimiters and character sets are missing from the source control file, there is a lack of self-healing and dynamic inference capabilities, resulting in direct exceptions and parsing interruptions, leading to high costs for manual troubleshooting. This semantic gap problem also makes the migration process from Oracle to PostgreSQL cumbersome and inefficient. Therefore, specifically as follows... Figure 4 As shown in one or more embodiments of this specification, cross-database dialect semantic mapping is performed on heterogeneous data sources based on a cross-database semantic mapping engine to obtain concurrent import parameters for the PostgreSQL database, forming a global concurrent import control parameter set. Specifically, the process includes the following: The system identifies control files from heterogeneous data sources based on specific file extensions, such as reading externally input .ctl format control files. Then, it extracts characteristic instructions and missing key parameters from the control files using pre-defined key dictionaries such as OPTIONS and PARALLEL. These characteristic instructions include global options, input sources, fault tolerance definitions, and table mapping relationships.

[0023] If dialect features are detected for import based on feature instructions, the cross-database semantic mapping engine is triggered. Based on compatibility rules, a language tree is constructed to perform cross-database dialect semantic mapping on the dialect features from heterogeneous data sources, converting them into concurrent import parameters for the PostgreSQL database. Specifically, this involves constructing a mapping language tree from Oracle syntax to PostgreSQL syntax according to preset compatibility rules to obtain the concurrent import parameter ratio. For example, if the Oracle ERRORS parameter is mapped to the global circuit breaker threshold of the three-level fault tolerance engine, then the global circuit breaker threshold of the three-level fault tolerance engine becomes the concurrent import parameter.

[0024] However, if the missing key parameter is a non-empty set, meaning it's for scenarios where the control file might be missing some key parameters such as field separators or character sets, then triggering a mechanism like... Figure 4 The data sniffing engine shown uses a first data probe to sniff multiple lines of data files in a heterogeneous data source and counts the frequency and number of occurrences of preset candidate delimiters in each line. Based on the frequency and number of occurrences, it obtains the missing characteristics of the missing key parameters and performs default compensation. Specifically, it reads the first N lines of the data file from the heterogeneous data source, counts the frequency and number of occurrences of preset candidate delimiters in each line, determines the candidate delimiter with the most stable frequency across lines and consistent occurrence counts across all lines as the default delimiter, and compensates it into the parsing context. Then, it assembles the concurrent import parameters and the compensated missing key parameters to generate a global concurrent import control parameter set, and serializes it into memory. That is, as shown... Figure 4As shown, after the mapping, compensation, and validity verification described above, the final parameters are assembled into a structured global concurrent import control set. This control set is serialized into memory, serving as the sole global state benchmark for subsequent concurrent scheduling, error handling, and index management engines. During this process, a cross-database semantic mapping engine performs cross-database dialect semantic mapping on heterogeneous data sources, automatically converting control files from these data sources into execution strategies recognizable by PostgreSQL. This eliminates the need for manual script reconstruction, reducing the costs wasted on manual intervention. For missing delimiters, character sets, and other critical parameters, data probes automatically infer and compensate, effectively preventing parsing interruptions. The generation of a structured global concurrent import control parameter set also provides a consistent configuration source for subsequent processing engines.

[0025] Furthermore, in one or more embodiments of this specification, such as Figure 2 as well as Figure 9 As shown, after constructing the global concurrent import control parameter set, the method also includes: The system initializes the database connection pool by using the connection configuration parameters and concurrency parameters in the global concurrency import control parameter set to establish a concurrent connection channel with the PostgreSQL database. Specifically, the global concurrency import control parameter set includes database authentication credentials and concurrency parameters. The system initializes the corresponding number of database connections according to the specified concurrency parameters, establishing a concurrent connection channel with the PostgreSQL database. For example, if the concurrency parameter is set to 8, the system initializes 8 database connections, forming a connection pool of size 8, providing a concurrent write channel for multiple subsequent worker coroutines.

[0026] Based on the index management strategy in the global concurrent import control parameter set, the system metadata of the PostgreSQL database is queried to obtain the non-unique indexes of the PostgreSQL database, and an index creation script for the non-unique indexes is generated and persistently stored. Specifically, the index management switch, i.e., the `manage_indexes` flag, is checked. If enabled, the target database system metadata is actively queried, and the creation statements for these indexes are dynamically generated, serialized, and backed up and saved to memory or persistent storage. A silent unloading command (DROP INDEX) is then executed to remove the non-unique indexes of the PostgreSQL database, putting the PostgreSQL database into an index-free write state. At this point, subsequent data import operations do not require index maintenance, thus eliminating the frequent B-tree splitting problem caused by real-time index maintenance, i.e., eliminating the "write amplification" effect, and significantly improving data write performance. After the above silent index unloading, the target table is in an index-free write state. The system then schedules a multi-coroutine concurrent import engine to begin large-scale data writing. In this state, when worker coroutines perform batch direct writes, no index maintenance is required, and write performance is significantly improved. After the full data import is complete, the system will rebuild the unloaded non-unique indexes concurrently using the backed-up index creation scripts across multiple cores.

[0027] S102: Based on the parsing configuration of the global concurrent import control parameter set, extract the streaming data continuously read from the heterogeneous data source using the multimodal parsing engine to obtain standard record objects.

[0028] Existing import tools typically support only a single format, such as CSV or TXT. When users need to import XLSX files, they must first convert them to CSV using other tools, increasing the number of steps and the risk of errors. Furthermore, traditional XLSX parsers use a DOM tree approach, loading the entire XML file into memory to build the document tree. For GB-level XLSX files, this consumes a large amount of memory and is prone to memory overflow issues. In addition, the data structures of parsed results are inconsistent across different formats, requiring downstream processing logic to write different processing code for each format, resulting in high system coupling and poor scalability. Therefore, to solve this problem, this application... Figure 2 The multi-format streaming read engine shown extracts standard record objects from the streaming data continuously read from heterogeneous data sources based on the parsing configuration of the global concurrent import control parameter set and the multi-modal parsing engine.

[0029] Specifically, such as Figure 5 and Figure 6As shown, in one or more embodiments of this specification, based on the parsing configuration of the global concurrent import control parameter set, the streaming data continuously read from heterogeneous data sources is extracted using a multimodal parsing engine to obtain standard record objects, specifically including: When initiating a data reading task, a second data probe is used to sniff out basic information about data files from heterogeneous data sources. This basic information includes file signature and file extension. Because the system has a built-in multimodal adaptation bus, it dynamically routes data to the corresponding parsing pipeline based on the sniffing results, thus shielding the downstream concurrent scheduling engine from the differences in underlying data formats. Specifically, the second data probe first reads several bytes from the file header to identify the file signature. For example, for CSV text files, which typically do not have a specific magic number, the system relies on the file extension, such as ".csv", for identification; for XLSX files, the magic number in the file header is "PK", which is combined with the file extension ".xlsx" for comprehensive judgment. Based on the sniffing results, the system dynamically routes the data stream to the corresponding parsing pipeline. If the data is identified as a text format such as CSV, it is routed to the text format parsing pipeline, i.e., the state machine text extraction engine; if it is identified as a compressed table format such as XLSX, it is routed to the event-driven XML stripping engine, i.e., the spreadsheet format parsing pipeline. If the parsing pipeline is a text format parsing pipeline, it uses a finite state machine to parse the buffered input stream of the text format data source. This encapsulates the complete record identified at each semantic boundary during parsing into a standard record object with a unified data structure. Specifically, it enables a buffered input stream with a custom chunk size and creates a buffered Reader. If a skipped line number is specified in the control file, it locates the starting line of data and skips header or comment lines. Then, it reads the data stream in chunks using a sliding window, thus reading the data into the buffer. Internally, the system implements a finite state machine for precise handling of nested quotes, escape characters, and heterogeneous newline characters. The state machine defines the following states: field start state, normal field scan state, quote field start state, quote field scan state, quote closing state, and record end state. The state machine scans the buffer character by character, determining the state transition based on the current state and the input character. Each time the state machine successfully matches and closes the semantic boundary of a complete record, it encapsulates it into a unified standard record object and pushes it to the concurrent task channel. The standard record object contains information such as field arrays, row numbers, and metadata, and has a unified data structure for downstream master control coroutines to perform batch assembly.

[0030] If the parsing pipeline is a spreadsheet format parsing pipeline, then it uses an event-driven mechanism to parse the buffered input of the spreadsheet format data source. A streaming sliding window triggers read events row by row, encapsulating each row of data into a standard record object with a unified data structure. Specifically, when routing to a compressed spreadsheet format, the parsing pipeline becomes a spreadsheet format parsing pipeline. In this case, the parsing engine bypasses the traditional DOM tree memory loading mode and adopts a low-level event-driven mechanism. The system directly parses the decompressed sheet.xml data stream using a streaming sliding window, triggering read events row by row, achieving a constant and extremely low memory usage independent of the total file size. S103: Flow control is performed on the task channel of the PostgreSQL database based on an adaptive backpressure strategy to assemble the standard record objects into data batches and distribute them to the worker coroutines of the PostgreSQL database.

[0031] Existing data pumps employ aggressive full-memory buffering or stateless unidirectional push methods. When downstream database I / O becomes congested, the upstream parser lacks awareness, leading to a large backlog of standard record objects in the task channel, causing system-level memory overflows and concurrent deadlocks. Therefore, to address this issue, in the embodiments of this specification, such as... Figure 3 The diagram illustrates flow control of the PostgreSQL database task channel based on an adaptive backpressure strategy. This strategy assembles standard record objects into data batches and distributes them to PostgreSQL database worker coroutines. By masking the physical differences of heterogeneous data sources and dynamically suspending or waking up underlying file transfers through the adaptive backpressure strategy, the system maintains constant memory usage at maximum throughput, preventing memory overflow issues. Furthermore, the worker coroutines that assemble standard record objects into data batches and distribute them to the PostgreSQL database achieve complete physical separation and computational isolation of data parsing, assembly, and writing, enabling the utilization of the parallel transmission capabilities of multi-core CPUs.

[0032] Specifically, in one or more embodiments of this specification, flow control is performed on the task channel of the PostgreSQL database based on an adaptive backpressure strategy to assemble the standard record objects into data batches and distribute them to the worker coroutines of the PostgreSQL database, specifically including: Combination Figure 7As shown, after obtaining the standard record objects, the main control coroutine monitors the load status of the task channel based on a preset high and low water level monitor. Specifically, the main control coroutine consumes the standard record objects output by the upstream multimodal parsing engine and evaluates the load status of the task channel in real time using the high and low water level monitor. The standard record object stream from the upstream multimodal parsing engine continuously flows into the main control coroutine, which monitors the buffered data volume of the task channel in real time using the preset high and low water level monitor, and then decides whether to send a backpressure control signal to the upstream multimodal parsing engine based on the load status.

[0033] If, based on the load status, it is determined that the buffered data volume in the task channel has reached the high-water mark threshold, a backpressure suspension signal is sent to the upstream multimodal parsing engine to pause data reading operations. Understandably, when the buffered data volume in the task channel reaches the high-water mark threshold, it indicates that the downstream worker coroutine's data writing speed cannot keep up with the upstream multimodal parsing engine's production speed, resulting in a large backlog of unprocessed standard record objects in the channel. At this point, the main control coroutine immediately sends a backpressure suspension signal to the upstream multimodal parsing engine. Upon receiving this signal, the upstream multimodal parsing engine triggers an adaptive backpressure mechanism, suspending the current file reading operation and halting the reading of new data from the data file. Furthermore, the upstream multimodal parsing engine enters a waiting state and no longer pushes new standard record objects to the task channel, thus avoiding memory overflow issues.

[0034] If, based on the load status, it is determined that the buffered data volume of the task channel has reached the low-water mark threshold, it indicates that the downstream worker goroutines have consumed enough data, and the channel has sufficient capacity to accommodate new data. At this point, the master goroutine sends a backpressure recovery signal to the upstream multimodal parsing engine. Upon receiving this signal, the upstream multimodal parsing engine wakes up previously suspended file read operations, resumes the read operation, continues producing standard record objects, and pushes them to the task channel. During the data read process, standard record objects are encapsulated based on a preset batch capacity threshold to obtain data batches, which are then distributed to the worker goroutines in the PostgreSQL database.

[0035] Furthermore, in one or more embodiments of this specification, the process of encapsulating the standard record object based on a preset batch capacity threshold to obtain a data batch and distributing it to the working coroutine of the PostgreSQL database during the data reading process specifically includes the following steps: In response to a read start event from a heterogeneous data source, the main control coroutine sequentially reads standard record objects based on the task channel and stores them in a memory buffer. When the number of records in the memory buffer reaches the batch capacity threshold, the corresponding standard record objects are encapsulated into a complete data batch. After encapsulation, the encapsulated data batch is distributed to the PostgreSQL database worker coroutines via the task channel. In response to a read completion event from a heterogeneous data source, after reading all data files, the remaining standard record objects in the memory buffer are retrieved. This involves checking if there are any remaining records in the memory buffer that have not yet reached the batch capacity threshold. The retrieved remaining standard record objects are then encapsulated into the final batch and sent to the task channel. After sending, a close signal is sent to the task channel. Furthermore, multiple worker coroutines concurrently listen to the task channel. After obtaining the distributed data batch, they call the database's underlying COPY protocol interface to initiate a batch write request. The worker coroutines capture the write execution status returned by the database in real time. If the write is successful, they wait to obtain the next data batch. If a write exception is detected, an error degradation handling mechanism is triggered, processing the currently failed data batch according to a pre-defined three-level tiered fault tolerance mechanism.

[0036] This process encapsulates standard record objects based on batch size thresholds to obtain optimal data batches, achieving dynamic assembly of data batches and effectively reducing network round trips. Simultaneously, controlling the number of records encapsulated in a single batch using the batch size threshold avoids memory pressure caused by excessively large batches. Remaining records in the cache that have not reached the threshold are also encapsulated into the final batch, resolving data loss issues and helping to ensure the complete import of all data.

[0037] S104: By calling the batch write protocol of the PostgreSQL database through the worker coroutine, a batch write request is initiated to the PostgreSQL database to perform batch write.

[0038] In the embodiments described in this specification, a worker coroutine calls the PostgreSQL database's batch write protocol to initiate a batch write request to the PostgreSQL database for batch writing. Because this process removes all non-unique indexes on the target table through the aforementioned silent index unloading, the target table enters an index-free write state. Therefore, when the worker coroutine executes the batch write, the database does not need to maintain any non-unique indexes and directly writes data to the data pages, completely eliminating the write amplification effect caused by index maintenance.

[0039] Specifically, based on Figure 10In one or more embodiments of this specification, the batch write request to the PostgreSQL database is initiated by calling the batch write protocol of the PostgreSQL database through the worker coroutine to perform batch write, specifically including: During system initialization, the parameter configuration module reads the external performance tuning configuration file. The module's internal syntax parser parses the key=value format configuration items line by line, verifying their validity and converting them into a key-value pair configuration set in memory. The parameter configuration module parses the above configuration items line by line, verifying the validity of each parameter. If the verification is successful, it converts the parameter into a key-value pair configuration set in memory, which serves as the basis for subsequent session-level parameter injection. Multiple worker coroutines run concurrently in the system, each continuously listening to the concurrent task channel. When the master coroutine sends a packaged data batch to the task channel, the worker coroutines retrieve the data batch from the channel. Simultaneously, the worker coroutines obtain an idle database connection from the database connection pool. Based on a session-level interceptor, the performance optimization parameters of the global concurrency import control parameter set are injected into the current database connection session. That is, after a physical connection is successfully established, the system immediately iterates through the above configuration set and sends an SQL command (SET key TO value) to the independent session to set the configuration parameters. After the command is sent, the system actively captures the execution status code returned by the database to verify whether the parameters have truly taken effect. If the verification passes, the connection is marked as ready and allowed to the business layer. If the verification fails, an alarm is triggered and the database default parameters are used in a fallback mode.

[0040] In response to the performance optimization parameters taking effect event of the current database connection session, a batch write request is initiated to the PostgreSQL database based on the batch write protocol, such as the COPY protocol interface, to perform batch writing. The COPY protocol bypasses the conventional SQL syntax parsing tree and writes data directly to the data pages, achieving the highest write throughput. The worker coroutine captures the write execution status returned by the database in real time. If a successful confirmation is received, the number of processed rows in the progress tracker is updated, and the task channel continues to listen for the next batch of data. If a write exception is captured by the database, the write operation of the current batch is immediately interrupted, the data batch and its exception information are encapsulated as a failed task, and forwarded to the pre-configured three-level fault tolerance mechanism for processing. When the worker coroutine completes the import task and disconnects, the lifecycle of the modified performance optimization parameters also ends, without causing any pollution to the database's global configuration file.

[0041] S105: During the batch writing process, write exceptions are handled based on a preset three-level fault tolerance mechanism to obtain the write result.

[0042] Traditional methods for high-concurrency direct write scenarios use the COPY protocol for batch direct writes. However, this protocol is a coarse-grained transaction model. If even a few abnormal data points exist in the batch, such as format misalignment or primary key conflicts, existing technologies typically only trigger a rollback of the entire batch or even a global task interruption. To solve this problem, in the embodiments of this specification, during the batch write process, such as... Figure 2 The three-level degradation and circuit breaker engine shown above handles write exceptions based on a pre-set three-level fault tolerance mechanism to obtain the write result.

[0043] Specifically, such as Figure 3 as well as Figure 8 As shown, in one or more embodiments of this specification, during the batch writing process, write anomalies are handled based on a preset three-level fault tolerance mechanism to obtain the write result, specifically including: During the batch write process, the data batches are written directly in batches, and the write execution status returned by the PostgreSQL database is obtained in real time. If a write exception is detected, the write exception is intercepted, and it is determined whether the data batch corresponding to the write exception is greater than a preset single-row processing threshold. In other words, if a write exception thrown by the database is captured, the exception is intercepted, the overall write of the current batch is stopped, and the abnormal data batch is transferred to the second-level fault tolerance module.

[0044] After the second-level fault-tolerance module takes over the abnormal data batch thrown by the first level, it initiates a tiered splitting and retry strategy. First, it determines whether the capacity of the current abnormal batch exceeds a preset single-data-processing threshold. If it does, the data batch corresponding to the write exception is split in half, and batch direct write retry is recursively performed on the split sub-data batches. That is, the abnormal batch is divided into two sub-data batches with half the capacity, and batch write requests are initiated for each sub-data batch based on the batch write protocol.

[0045] Based on the batch write results of the sub-data batches, if there are failures and the preset single-row processing threshold is not reached, then the failed sub-batch is recursively split and batch retried; otherwise, the batch write is downgraded to single-row insertion. In other words, when the system performs the first binary split on an abnormal data batch, if both generated sub-data batches immediately return an error during retry, it is determined that there is a large-scale continuous error, and the subsequent recursive splitting action is immediately terminated, directly transferring the entire original abnormal batch of data to the third-level fault tolerance module. If the above is not the case, the progress of the successfully retried sub-data batches is updated. For individual sub-data batches that still capture write errors, binary splitting and batch retrying are continued recursively or cyclically until the capacity of the abnormal sub-data batch drops to the single-row processing threshold.

[0046] If the capacity of the abnormal data batch equals the single-row processing threshold, its processing mode is completely downgraded. The system uses a pre-compiled insert statement mechanism to perform single-row tentative inserts. If the insert is successful, the transaction is committed and the success count is incremented. If an exception is triggered again, the system isolates the erroneous row through a transaction rollback mechanism and extracts the original data and exception feedback information of the specific erroneous row to the third-level fault tolerance module.

[0047] In the third level of fault tolerance, on the one hand, the original data of the erroneous line is appended to a pre-defined error data file without loss of the input source format, so that subsequent business personnel can directly retry after repair. On the other hand, the abnormal feedback information is parsed, and the line number where the error occurred, the error type code, and the description are written to a pre-defined error log file. Then, the global error count for batch writing is accumulated to trigger a circuit breaker based on the fault tolerance threshold, stopping the writing and persisting the breakpoint state. It can be understood that if the error count does not exceed the fault tolerance threshold, the erroneous line is ignored, and subsequent data batches continue to be scheduled. If the fault tolerance threshold is reached or exceeded, the global circuit breaker mechanism is immediately triggered, interrupting the write tasks of all worker coroutines and persisting the current breakpoint state for future resumption.

[0048] Furthermore, in one or more embodiments of this specification, after handling write anomalies based on a pre-set three-level fault tolerance mechanism and obtaining the write result, the method further includes: After the data distribution and import operations are completed, the task status of the batch write is obtained based on the writing results. The task status includes: aborted and completed. That is, if the entire data import is successful and no global circuit breaker is triggered, the task status is completed; if the import process fails due to a global circuit breaker or a serious underlying error, the task status is aborted.

[0049] If the task status is complete, the index creation script is read, and the corresponding number of rebuild coroutines are dynamically pulled according to the number of idle CPU cores on the current server. Utilizing the database's concurrent creation capabilities, rebuild requests are initiated in parallel for multiple unloaded non-unique indexes, thereby rebuilding the PostgreSQL database's non-unique indexes. Through multi-core concurrent rebuilding, the system can fully utilize server CPU resources, significantly shortening index rebuilding time and thus restoring the complete index structure of the target table as quickly as possible.

[0050] If the task status is aborted, the index rebuilding action is skipped, leaving the database in the state where the indexes have been deleted. The recovery script of the index creation script is obtained. This recovery script contains the creation statements of all previously backed-up non-unique indexes. This allows maintenance personnel to manually execute the recovery script to rebuild the indexes after troubleshooting and repairing data anomalies, avoiding secondary problems caused by blindly rebuilding indexes in a data anomaly state.

[0051] like Figure 11 As shown in the diagram, this specification provides a schematic diagram of the structure of a batch write device for a PostgreSQL database. Based on Figure 11 As shown in one or more embodiments of this specification, a batch write device for a PostgreSQL database includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform any of the methods described above.

[0052] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0053] The foregoing has described specific embodiments of this specification. In some cases, the actions or steps described in the specification may be performed in a different order than those shown in the embodiments and the desired results may still be achieved. Furthermore, the processes depicted in the accompanying drawings do not necessarily require a specific or sequential order to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0054] The above description is merely one or more embodiments of this specification and is not intended to limit this specification. Various modifications and variations can be made to the one or more embodiments of this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of one or more embodiments of this specification should be included within the scope of this specification.

Claims

1. A batch write method for a PostgreSQL database, characterized in that, The method includes: Based on the cross-database semantic mapping engine, cross-database dialect semantic mapping is performed on heterogeneous data sources to obtain concurrent import parameters of PostgreSQL database and form a global concurrent import control parameter set. Based on the parsing configuration of the global concurrent import control parameter set, the streaming data continuously read from the heterogeneous data source is extracted using the multimodal parsing engine to obtain standard record objects; The task channel of the PostgreSQL database is flow controlled based on an adaptive backpressure strategy to assemble the standard record objects into data batches and distribute them to the worker coroutines of the PostgreSQL database. The worker coroutine calls the batch write protocol of the PostgreSQL database to initiate a batch write request to the PostgreSQL database and perform batch writes. During the batch writing process, write anomalies are handled based on a pre-set three-level fault tolerance mechanism to obtain the write results; Based on a cross-database semantic mapping engine, cross-database dialect semantic mapping is performed on heterogeneous data sources to obtain concurrent import parameters for the PostgreSQL database, forming a global set of concurrent import control parameters, specifically including: The control files of the heterogeneous data source are identified based on a specific format suffix, and feature instructions and missing key parameters of the control files are extracted based on a preset key dictionary; wherein, the feature instructions include: global options, input sources, fault tolerance definitions and table mapping relationships; If dialect feature import is detected based on the feature instruction, the cross-database semantic mapping engine is triggered to perform cross-database dialect semantic mapping on the dialect features of the heterogeneous data source and convert them into concurrent import parameters of the PostgreSQL database. If the missing key parameter is a non-empty set, then the first data probe is used to sniff out multiple lines of content in the data file of the heterogeneous data source, and the frequency and number of occurrences of the preset candidate delimiter in each line of content are counted. Based on the frequency of occurrence and the number of occurrences, the missing features of the missing key parameters are obtained, and default compensation is performed. The concurrent import parameters are assembled with the compensated missing key parameters to generate the global concurrent import control parameter set, and then serialized into memory; After constructing the global concurrent import control parameter set, the method further includes: The database connection pool is initialized by using the connection configuration parameters and concurrency parameters of the global concurrency import control parameter set to establish a concurrent connection channel for the PostgreSQL database. Based on the index management strategy in the global concurrency import control parameter set, query the system metadata of the PostgreSQL database, obtain the non-unique index of the PostgreSQL database, and generate the index creation script of the non-unique index for persistent storage. Execute the silent uninstall command to remove the non-unique indexes of the PostgreSQL database, so that the PostgreSQL database enters an index-free write state; Flow control is applied to the task channel of the PostgreSQL database based on an adaptive backpressure strategy to assemble the standard record objects into data batches and distribute them to the worker coroutines of the PostgreSQL database. Specifically, this includes: The main control coroutine monitors the load status of the task channel based on a preset high and low water level monitor; wherein, the main control coroutine is used to consume the standard record objects output by the upstream multimodal parsing engine; If, based on the load status, it is determined that the amount of buffered data in the task channel has reached the high water level threshold, a back pressure suspension signal is sent to the upstream multimodal parsing engine to suspend the data reading operation. If, based on the load status, it is determined that the amount of buffered data in the task channel has reached a low water level threshold, a back pressure recovery signal is sent to the upstream multimodal parsing engine to resume the reading operation; During the data reading process, the standard record object is encapsulated based on a preset batch capacity threshold to obtain a data batch, which is then distributed to the working coroutine of the PostgreSQL database. The standard record object is encapsulated based on a preset batch capacity threshold to obtain a data batch, which is then distributed to the working coroutine of the PostgreSQL database. Specifically, this includes: In response to the read start event of the heterogeneous data source, standard record objects are read sequentially based on the task channel and stored in the memory cache. When the number of records in the memory cache reaches the batch capacity threshold, the corresponding standard record objects are encapsulated into data batches and sent concurrently to the working coroutines of the PostgreSQL database through the task channel. In response to the read completion event of the heterogeneous data source, the remaining standard record objects in the memory cache are obtained, and the remaining standard record objects are encapsulated into a final batch and a close signal is sent. During the batch write process, write anomalies are handled based on a pre-set three-level fault tolerance mechanism to obtain the write result, specifically including: During the batch writing process, the data batches are directly written in batches, and the write execution status returned by the PostgreSQL database is obtained in real time. If a write error is detected, the write error is intercepted, and it is determined whether the data batch corresponding to the write error is greater than the preset single-item processing threshold. If the value exceeds the preset single-data-processing threshold, the data batch corresponding to the write exception is split into two parts, and the split sub-data batches are recursively retried for batch direct write. Based on the batch writing results of the sub-data batch, if there are failures and the preset single-row processing threshold is not reached, then the failed sub-batch is recursively split into binary and batch retried; otherwise, the batch writing is downgraded to single-row insertion. The global error count for the batch write is accumulated to trigger a circuit breaker based on the fault tolerance threshold, thereby stopping the write and persisting the breakpoint state.

2. The batch write method for a PostgreSQL database according to claim 1, characterized in that, Based on the parsing configuration of the global concurrent import control parameter set, the streaming data continuously read from the heterogeneous data source is extracted using a multimodal parsing engine to obtain standard record objects, specifically including: Based on the second data probe, basic information of the data files from the heterogeneous data source is sniffed out; wherein, the basic information includes: file signature and file extension; Based on the basic information, dynamically route to the corresponding parsing pipeline; If the parsing pipeline is a text format parsing pipeline, then the buffered stream input of the text format data source is parsed based on a finite state machine, so as to encapsulate the complete record of each semantic boundary recognition during the parsing process into a standard record object with a unified data structure. If the parsing pipeline is a spreadsheet format parsing pipeline, then the buffered stream input of the spreadsheet format data source is parsed based on the event-driven mechanism, and the reading event is triggered row by row by the streaming sliding window to encapsulate each row of data into a standard record object with a unified data structure.

3. The batch write method for a PostgreSQL database according to claim 1, characterized in that, The worker coroutine invokes the batch write protocol of the PostgreSQL database to initiate a batch write request to the PostgreSQL database for batch writing, specifically including: The task channel is monitored by the worker coroutine to obtain the distributed data batches, and an idle connection of the concurrent connection channel is obtained based on the database connection pool. Based on the session-level interceptor, the performance optimization parameters of the global concurrency import control parameter set are injected into the current database connection session; In response to the performance optimization parameter taking effect event of the current database connection session, based on the batch write protocol, a batch write request is initiated to the PostgreSQL database to perform batch writing.

4. The batch write method for a PostgreSQL database according to claim 1, characterized in that, During the batch write process, write anomalies are handled based on a pre-set three-level fault tolerance mechanism. After obtaining the write result, the method further includes: Based on the writing results, the task status of the batch writing is obtained; wherein, the task status includes: aborted status and completed status; If the task status is completed, then read the index creation script and rebuild the non-unique index of the PostgreSQL database; If the task status is aborted, then obtain the recovery script of the index creation script so that maintenance personnel can maintain it.

5. A batch write device for a PostgreSQL database, characterized in that, The device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the method described in any one of claims 1-4.

Images