Incremental materialized view implementation method and device based on logical decoding of a PostgreSQL database

Abstract

This invention discloses a method and device for implementing incremental materialized views based on PostgreSQL database logical decoding, relating to the field of electronic digital data processing technology. The method includes: generating a semantic structure for maintaining the incremental operation characteristics of the materialized view; monitoring WAL log records generated by the base table associated with the materialized view through a logical decoding output plugin, and decoding the WAL log records into corresponding logical change messages; acquiring all logical change messages generated before the current refresh deadline, parsing the logical change messages according to the semantic structure to generate incremental operation instructions for updating the materialized view; batch executing the incremental operation instructions in a database transaction, and updating the corresponding refresh point information according to the current refresh deadline when committing the database transaction, thereby achieving a single incremental refresh of the materialized view.

Description

Technical Field

[0001] This invention relates to the field of electronic digital data processing technology, specifically to an incremental materialized view implementation method and device based on PostgreSQL database logical decoding. Background Technology

[0002] In database applications, materialized views significantly improve the response speed of complex queries by pre-compiling and storing query results. However, the freshness of materialized views is highly dependent on their maintenance mechanism. Currently, in data analysis or reporting systems built on PostgreSQL databases, the built-in refresh command `REFRESHMATERIALIZED VIEW` is typically used. This refresh method employs a full refresh strategy, discarding all data in the materialized view on each execution and re-executing its definition query to populate it with new data. When the base table contains a large amount of data with only a small number of changes, this operation consumes significant CPU, I / O, and network resources, resulting in an extremely long refresh process that fails to meet near real-time analysis requirements. Furthermore, it ignores the actual changes in data, repeatedly calculating and writing the vast majority of unchanged data, leading to severe waste of computational and storage resources. Moreover, during a full refresh, materialized views are often unavailable to queries or return empty results, causing prolonged service interruptions or data inconsistencies in upper-layer applications that depend on them, severely impacting user experience and business continuity.

[0003] To reduce refresh overhead, some solutions attempt to create triggers on the base table to capture changes in real time and synchronously update materialized views, such as the PostgreSQL plugin pg_ivm. However, triggers intrude into the base table's Data Manipulation Language (DML) execution path, leading to significant performance bottlenecks in high-concurrency write scenarios. Furthermore, they struggle to handle complex materialized views involving multi-table joins and aggregations, resulting in extremely high maintenance costs. Other solutions utilize external Change Data Capture (CDC) toolchains such as Debezium and Kafka to capture and propagate changes. This not only increases the complexity of the system architecture, requiring the deployment, operation, and monitoring of multiple independent components, but also makes it difficult to guarantee strong consistency at the transaction level because change capture and view updates belong to different systems, leading to complex fault recovery logic. Summary of the Invention

[0004] To address the aforementioned problems, this invention proposes an incremental materialized view implementation method based on PostgreSQL database logical decoding, comprising: In response to the creation command of the materialized view, the definition query statement of the materialized view is semantically parsed to generate a semantic structure for maintaining the incremental operation features of the materialized view; Load the built-in logical decoding output plugin of the PostgreSQL database, listen to the WAL log records generated by the base table associated with the materialized view through the logical decoding output plugin, and decode the WAL log records into the corresponding logical change messages; In response to an incremental refresh command for the materialized view, all logical change messages generated before the current refresh deadline are obtained. Based on the semantic structure, the logical change messages are parsed to generate an incremental operation instruction for updating the materialized view. The incremental operation instructions are executed in batches within a database transaction, and when the database transaction is committed, the corresponding refresh point information is updated according to the current refresh cutoff point, thereby achieving a single incremental refresh of the materialized view.

[0005] In one implementation of the present invention, semantic parsing is performed on the definition query statement of the materialized view to generate a semantic structure for maintaining the incremental operation features of the materialized view, specifically including: Based on a preset system directory table, register the metadata corresponding to the materialized view; wherein, the metadata includes name, definition query statement and dependent base table information; Based on the metadata, semantic parsing is performed on the definition query statement of the materialized view to extract the core meta-information in the definition query statement; Based on the core meta-information, a semantic structure is generated to maintain the incremental computational features of the materialized view.

[0006] In one implementation of the present invention, the logical change message is parsed according to the semantic structure to generate an incremental operation instruction for updating the materialized view, specifically including: Based on the semantic structure, the view type corresponding to the materialized view is determined; wherein, the view type includes simple projection type and aggregation type; If the view type is the simple projection type, determine whether the operation record corresponding to the logical change message meets the filtering conditions defined in the semantic structure, so that if the filtering conditions are met, an incremental operation instruction for updating the materialized view is generated according to the logical change message. If the view type is an aggregation type, incremental aggregation calculation is performed on the logical change message based on the grouping aggregation information in the semantic structure to generate incremental operation instructions for updating the aggregation value and hiding auxiliary columns in the materialized view.

[0007] In one implementation of the present invention, incremental aggregation calculation is performed on the logical change message based on the grouping aggregation information in the semantic structure to generate incremental operation instructions for updating the aggregated values ​​and hidden auxiliary columns in the materialized view, specifically including: If the operation type corresponding to the logical change message is an insertion operation or a deletion operation, the group row corresponding to the logical change message in the materialized view is located according to the group aggregation information in the semantic structure. Based on the hidden auxiliary column in the materialized view, incremental aggregation calculations are performed on the logical change message to update the hidden auxiliary column; Based on the updated hidden auxiliary column, determine the aggregate value corresponding to the grouping row, and generate an incremental operation instruction for updating the aggregate value and the hidden auxiliary column corresponding to the grouping row. When the operation type of the logical change message is an update operation, depending on whether the logical change message updates the group aggregation information, an incremental operation instruction is generated to perform a corresponding reorganization update operation or a direct update operation on the group rows in the materialized view.

[0008] In one implementation of the present invention, after locating the grouping row corresponding to the logical change message in the materialized view, the method further includes: If the grouping aggregation information includes an extremum function, and the operation type corresponding to the logical change message is a deletion operation, and the deleted column value is equal to the maximum or minimum value in the current group row, the base table is looked up through the composite index in the materialized view to locate the maximum or minimum value of the group row at the current refresh cutoff point; wherein, the composite index is an index created based on the grouping columns and aggregation information in the base table on which the materialized view depends; Update the minimum or maximum value to the aggregate value corresponding to the grouping row.

[0009] In one implementation of the present invention, depending on whether the logical change message updates the grouping aggregation information, an incremental operation instruction is generated for performing a corresponding reorganization update operation or a direct update operation on the grouping rows in the materialized view, specifically including: Determine whether the logical change message updates the group aggregation information; If not, an incremental operation instruction is generated to perform a direct update operation on the grouped rows in the materialized view; If so, generate an incremental operation instruction to perform a reorganization update operation, thereby deleting the grouped rows in the materialized view before the base table change and inserting the grouped rows after the base table change.

[0010] In one implementation of the present invention, before determining whether the logical change message updates the group aggregation information, the method further includes: Based on whether the base table satisfies the filtering conditions in the semantic structure before and after the change, the incremental maintenance operation type triggered by the materialized view is determined, and an incremental operation instruction for performing a direct update operation is generated according to the incremental maintenance operation type.

[0011] In one implementation of the invention, prior to an incremental refresh command for the materialized view, the method further includes: Receive incremental refresh commands manually triggered by the user; Alternatively, monitor the difference between the latest log sequence number in the WAL log record of the base table and the log sequence number recorded at the last refresh, and generate an incremental refresh command for the materialized view when the sequence number difference reaches a preset sequence number threshold. Alternatively, determine the query frequency of the materialized view within a preset time window; if the query frequency is greater than a preset frequency threshold, generate an incremental refresh command for the materialized view.

[0012] This invention provides an incremental materialized view implementation device based on PostgreSQL database logical decoding, the device comprising: At least one processor; And, a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which are executed by the at least one processor to enable the at least one processor to execute an incremental materialized view implementation method based on PostgreSQL database logical decoding as described in any of the preceding claims.

[0013] This invention provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows: An incremental materialized view implementation method based on PostgreSQL database logical decoding, as described in any of the preceding items.

[0014] The incremental materialized view implementation method based on PostgreSQL database logical decoding proposed in this invention can bring the following beneficial effects: The logic decoding output plugin converts row-level changes related to materialized views in the WAL log into logical change messages in real time, providing raw input for subsequent incremental aggregation calculations. This process runs entirely within the PostgreSQL database kernel, without relying on any external tools or middleware. Change capture occurs asynchronously in the WAL log generation path, incurring no additional performance overhead for normal data operations on the base table. Compared to the traditional method of creating triggers on the base table, this embodiment utilizes a logic decoding framework to monitor the WAL log, fundamentally avoiding trigger intrusion into the base table's write path. This ensures that the base table's throughput remains unaffected under high-concurrency write scenarios. Compared to solutions that introduce external CDC tools, there is no need to deploy and maintain independent components. All logic is cohesive within the database kernel, guaranteeing transactional atomicity and consistency between change capture and subsequent refresh operations. Attached Figure Description

[0015] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this invention, illustrate exemplary embodiments of the invention and are used to explain the invention, but do not constitute an undue limitation of the invention. In the drawings: Figure 1 A flowchart illustrating an incremental materialized view implementation method based on PostgreSQL database logical decoding, provided in an embodiment of the present invention; Figure 2 This is a schematic diagram of the structure of an incremental materialized view implementation device based on PostgreSQL database logical decoding, provided as an embodiment of the present invention. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this invention, and not all of them. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0017] The technical solutions provided by the various embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0018] like Figure 1 As shown in the embodiment of the present invention, the incremental materialized view implementation method based on PostgreSQL database logical decoding includes: S101: In response to the creation command of the materialized view, perform semantic parsing on the definition query statement of the materialized view to generate a semantic structure for maintaining the incremental operation characteristics of the materialized view.

[0019] This invention provides a built-in logical decoding output plugin for PostgreSQL databases. As an extension module of the database kernel, this plugin can monitor and capture changes to relevant base tables. Its internal intelligent engine is responsible for translating the original changes into specific operations on materialized views. Driven by refresh commands, the plugin performs incremental refreshes of materialized views within database transactions, ensuring the consistency of materialized view updates. A materialized view refers to a pre-calculated and physically stored query result set in the database. Unlike ordinary views, materialized views actually save query results as physical tables, significantly accelerating the response speed of complex queries, especially suitable for data analysis and report generation scenarios. However, when base table data changes, materialized views need to be updated synchronously to maintain data freshness. To ensure the efficiency and consistency of materialized view updates, when a user sends a creation command for a materialized view supporting incremental refresh via the data definition language CREATE INCREMENTAL MATERIALIZED VIEW, the system first responds to the creation command by calling the logical decoding output plugin to perform semantic parsing of the materialized view's definition query statement and generate a semantic structure representing the incremental operation characteristics of the materialized view.

[0020] A semantic structure is a structured data object that includes query type, base table information, grouping columns, aggregation information, and filtering conditions (i.e., the WHERE clause). The query type distinguishes between simple projection queries and aggregation queries. Grouping columns refer to the columns specified in the GROUP BY clause. Aggregation information is in array form, with each element describing aggregate functions such as COUNT / SUM / AVG / MIN / MAX, their source column names, and aliases in the materialized view. By pre-extracting the semantic structure, the original SQL statement can be re-parsed during subsequent incremental refreshes of the materialized view. Instead, the mapping is directly modified based on the pre-stored rules in the semantic structure, fundamentally avoiding runtime parsing overhead. This also allows the system to uniformly handle materialized views of varying complexity, including simple projections, aggregations, and even future multi-table joins, significantly improving refresh efficiency.

[0021] In one embodiment, during the generation of the semantic structure, firstly, the metadata of the materialized view is registered in a preset system directory table, such as pg_incremental_matview. The metadata includes its name, definition query statement, and dependent base table information. After registration, based on the registered metadata, the definition query statement of the materialized view is semantically parsed to extract the corresponding core metadata. The core metadata consists of several elements contained in the aforementioned semantic structure. For example, for the definition query statement "SELECT dept_id,COUNT(*) AS cnt,FROM employee GROUP BYdept_id", it can be identified that the view type is an aggregation query, the grouping column is dept_id, the aggregation information includes COUNT and its respective source columns, and the filter condition is null. Based on the extracted core metadata, a semantic structure for maintaining the incremental operation characteristics of the materialized view is constructed. This semantic structure is serialized into JSONB format and stored in a dedicated field in the aforementioned system directory table so that it can be read and used at any time during subsequent incremental refresh processes.

[0022] S102: Loads the built-in logical decoding output plugin of the PostgreSQL database. Through the logical decoding output plugin, it listens for the WAL log records generated by the base table associated with the materialized view and decodes the WAL log records into the corresponding logical change messages.

[0023] During the creation of a materialized view, the system automatically creates a corresponding logical replication slot and designates the logical decoding output plugin as the output plugin for the logical replication slot. Simultaneously, to ensure complete row-level change information is obtained, the system sets the REPLICA IDENTITY property of the base table on which the materialized view depends to FULL, so that each WAL log record contains the complete content of the changed row. Once the logical decoding output plugin is loaded into memory, it can monitor the WAL log records generated by the base tables associated with the materialized view. Whenever a new record is generated in the WAL log, the plugin captures the latest WAL log, then determines whether this WAL log record belongs to the monitored base table. If not, it is ignored; if so, the WAL log record is decoded into a structured logical change message and output in plain text format. The message format is as follows: I:schema.table col1=val1 col2='val2' ... (INSERT) D:schema.table col1=val1 col2='val2' ... (DELETE) U:schema.table O:col1=old1 N:col1=new1 ... (UPDATE) T:schema.table (TRUNCATE) Value encoding: NULL values ​​are encoded as \N; numeric types are not enclosed in quotes; other types are enclosed in single quotes, with the inner single quotes escaped as ''.

[0024] In logical change messages, 'I' indicates INSERT, followed by the schema name, table name, and the values ​​of each column. For delete operations, the message begins with 'D'; for update operations, the message begins with 'U', with 'O' and 'N' marking the old and new values ​​respectively; for clearing the table, the message begins with 'T'. NULL values ​​are represented by '\N' in the message. Numeric types are not enclosed in quotes, while string types are enclosed in single quotes with the inner single quotes escaped as double single quotes. In addition to change processing callbacks, the logical decoding output plugin also executes a transaction commit callback upon transaction commit, outputting a 'C' marker as a transaction commit flag.

[0025] The logic decoding output plugin converts row-level changes related to materialized views in the WAL log into logical change messages in real time, providing raw input for subsequent incremental aggregation calculations. This process runs entirely within the PostgreSQL database kernel, without relying on any external tools or middleware. Change capture occurs asynchronously in the WAL log generation path, incurring no additional performance overhead for normal DML operations on the base table. Compared to the traditional method of creating triggers on the base table, this embodiment utilizes a logic decoding framework to monitor the WAL log, fundamentally avoiding trigger intrusion into the base table's write path. This ensures that the base table's throughput remains unaffected under high-concurrency write scenarios. Compared to solutions that introduce external CDC tools, there is no need to deploy and maintain independent components. All logic is cohesive within the database kernel, guaranteeing transactional atomicity and consistency between change capture and subsequent refresh operations.

[0026] S103: In response to an incremental refresh command for the materialized view, obtain all logical change messages generated before the current refresh deadline, parse the logical change messages according to the semantic structure, and generate an incremental operation instruction for updating the materialized view.

[0027] Updates to materialized views require an incremental refresh command. This command can be initiated manually by the user using the `REFRESH INCREMENTAL MATERIALIZED VIEW mv_name` command, or automatically generated by a background process based on a preset refresh strategy. For example, the background process continuously monitors the difference between the latest log sequence number of the base table and the log sequence number recorded during the last successful refresh. When this difference reaches a preset sequence number threshold, an incremental refresh operation is automatically initiated, generating the corresponding command to prevent materialized view data from becoming severely outdated during frequent updates to the base table. Furthermore, the background process monitors the query frequency of the materialized view within a preset time window using statistics recorded in the PostgreSQL system view `pg_stat_user_tables`. When the query frequency exceeds a preset frequency threshold, a refresh is automatically triggered, generating an incremental refresh command to ensure that frequently accessed materialized views maintain high data freshness. Regardless of the triggering method used, once the system receives an incremental refresh command for a materialized view, the refresh engine is activated. After activation, the refresh engine first determines the refresh cutoff point from the transaction snapshot. The refresh cutoff point refers to a specific Log Sequence Number (LSN), which indicates that this incremental refresh needs to process all logical change messages corresponding to the WAL logs up to that sequence number.

[0028] The refresh engine responds to incremental refresh commands by reading all logical change messages generated from the point recorded in the last refresh to the end point of the current refresh from the logical copy slot corresponding to the materialized view. Then, it parses the logical change messages according to the semantic structure, thereby converting the logical change messages into incremental operation instructions that can directly update the materialized view.

[0029] Specifically, the view type corresponding to the materialized view is determined based on the query type in the semantic structure. View types include simple projection and aggregation types. Different transformation logic is used for different view types to generate incremental refresh commands. For a materialized view of the simple projection type, its definition query only contains the projection column and optional WHERE filter conditions, without a GROUP BY clause or aggregate functions. In this case, for an insert message, it is first determined whether the operation record corresponding to the logical change message meets the filter conditions defined in the semantic structure. These filter conditions are extracted from the WHERE clause of the original definition query and stored in the semantic structure, such as salary > 10000. Specifically, for an insert operation, it is determined whether the new row meets the filter conditions. If it does, an INSERT operation command is generated for the materialized view to insert the new row into the materialized view; otherwise, the message is ignored. For a delete operation, it is determined whether the old row meets the filter conditions. If it does, a DELETE operation command is generated to delete the corresponding row from the materialized view; otherwise, it is ignored. For update operations, both the old and new rows need to be checked to see if they meet the filtering conditions, resulting in four possible outcomes. If both the old and new rows meet the conditions, an UPDATE operation is generated to directly update the corresponding row in the materialized view. If the old row meets the conditions but the new row does not, a DELETE operation is generated to delete the old row from the materialized view. If the old row does not meet the conditions but the new row does, an INSERT operation is generated to insert the new row into the materialized view. If neither condition is met, no operation is performed. In this way, simple projection type materialized views can be precisely incrementally maintained, and updates are only performed on the materialized view when changes to the base table affect the projection or filtering conditions defined by the materialized view.

[0030] If the view type is aggregate, incremental aggregation calculations are needed for logical change messages based on the grouping aggregation information in the semantic structure to generate incremental operation instructions for updating aggregate values ​​and hiding auxiliary columns in the materialized view. Aggregate values ​​refer to the column values ​​corresponding to the grouping columns. Grouping aggregation information includes the grouping columns and aggregation information. The grouping row referred to here is the record row in the materialized view corresponding to a specific grouping key value. For example, in a materialized view grouped by department, each department number corresponds to a unique row. Since each row in an aggregate materialized view represents a group, a change message in the base table typically only affects the row of its group. Therefore, for each change message, the refresh engine first parses the operation type and row data, determines the group to which the row belongs based on the value of the grouping column, and searches for the corresponding group row in the materialized view. If it does not exist, it means that the group has never appeared before, and this insertion operation is the first record belonging to that group. In this case, the refresh engine creates a new group row in the materialized view and fills in the value of the grouping column.

[0031] After locating or creating grouped rows, the refresh engine needs to execute different processing logic based on the operation type of the logical change message, incrementally updating the hidden helper columns. Hidden helper columns are special columns automatically added to the physical storage table by the system when creating an aggregated materialized view; they are not visible to ordinary queries. These mainly include count helper columns and summation helper columns. The count helper column records the actual number of rows contained in the base table within the current group, while the summation helper column records the cumulative sum of values ​​in a specified column within the current group.

[0032] When the logical change message is an insert operation, the value of the count auxiliary column in the grouped row is incremented by one, and the value of the corresponding column in the newly inserted row is added to the sum auxiliary column. The count auxiliary column is incremented by 1, and the sum auxiliary column is added to the corresponding column. When the logical change message is a delete operation, the value of the count auxiliary column is decremented by one, and the value of the corresponding column in the deleted row is subtracted from the sum auxiliary column. After updating the hidden auxiliary columns, the aggregate values ​​corresponding to the grouped rows can be determined based on the updated hidden auxiliary columns, and aggregate values ​​such as COUNT (count), SUM (sum), and AVG (mean) can be recalculated. AVG is obtained by dividing the sum auxiliary column by the count auxiliary column, while COUNT and SUM are directly taken from their respective auxiliary columns.

[0033] If the aggregation information in the semantic structure contains an extremum function (i.e., MIN or MAX), then the deletion operation requires special handling. When the operation type corresponding to the logical change message is a deletion operation, the refresh engine extracts the column value corresponding to the extremum function from the deleted row and compares this column value with the minimum or maximum value stored in the current group row. If the deleted column value is not equal to the minimum or maximum value in the current group row, it means that the deleted record is not an extreme value record within the group. Its deletion operation does not affect the extremum aggregation result of the current group row, so no additional processing is required. Only the counting auxiliary column and the summation auxiliary column need to be updated according to the normal logic.

[0034] However, if the deleted column value happens to be equal to the maximum (for the MAX aggregate function) or minimum (for the MIN aggregate function) value in the current grouped row, the maximum / minimum aggregation result cannot be updated simply by addition / subtraction or by retaining the original value, because the second largest or second smallest value in the current group is unknown and cannot be directly calculated incrementally. Therefore, it is necessary to use the pre-created composite index in the materialized view to look up the base table and recalculate the maximum or minimum value of that grouped row at the current refresh cutoff point.

[0035] Composite indexes are indexes automatically created by the system on the base tables that the materialized view depends on, based on the grouping columns and aggregation information recorded in the semantic structure, when creating an aggregated materialized view. For example, for the query "SELECT dept_id, MIN(salary), MAX(salary) FROM employee GROUP BY dept_id", the system will create a composite index on the base table employee with (dept_id, salary) as the index key. Composite indexes can efficiently support query patterns that filter by grouping key and then sort by aggregation column. This allows backtracking operations to quickly obtain the current minimum or maximum column value within a specified group through index scan only, without scanning the entire base table or all groups, greatly improving the efficiency of recalculating extreme values.

[0036] During the rollback operation, the refresh engine constructs a subquery. Based on the grouping columns and filtering conditions in the semantic structure, this subquery retrieves all records within the group that satisfy the filtering conditions from the base table, under the snapshot corresponding to the current refresh cutoff point. It then uses a composite index to quickly locate the minimum or maximum value. The snapshot is determined at the start of this incremental refresh, ensuring that the data version read by the rollback operation is strictly consistent with the WAL log cutoff point processed by this refresh. This ensures that even if new data changes concurrently occur in the base table during the rollback, it will not affect the correctness of the extreme value results in the materialized view after this refresh. After obtaining the new minimum or maximum value through the rollback, the refresh engine updates the corresponding aggregate value in the grouped row with the latest value obtained from the rollback and generates the corresponding incremental operation instruction.

[0037] For update operations, when an update operation occurs on the base table of a materialized view of aggregate type, the refresh engine first extracts the row data before and after the change, and then determines whether the old and new rows meet the filtering conditions (i.e., the WHERE clause) stored in the semantic structure. Specifically, if the old row does not meet the filtering conditions but the new row does, the change is equivalent to an insert operation for the materialized view, and subsequent processing follows the insert logic; if the old row meets the conditions but the new row does not, it is equivalent to a delete operation, and processing follows the delete logic; if both the old and new rows meet the filtering conditions, it means that the record is within the data range covered by the materialized view before and after the change, and further determination is needed as to whether the grouping key has changed; if neither the old nor the new row meets the filtering conditions, the change does not affect the materialized view and is ignored. This pre-judgment accurately determines the type of incremental maintenance operation triggered by the materialized view. Therefore, even if the grouping key remains unchanged, it generates corresponding direct update instructions based on the incremental maintenance operation type. For example, it executes instructions to update aggregate values ​​and hide auxiliary columns on the corresponding grouped rows in the materialized view, without requiring a reorganization operation. This process ensures that even if the base table update operation changes whether a record meets the WHERE condition, the materialized view accurately reflects the filtering semantics during incremental refresh, avoiding data inconsistencies caused by ignoring condition checks.

[0038] Because update operations may change the values ​​of grouping columns, aggregate columns without changing the grouping columns, and columns involved in filtering conditions, affecting whether a record should exist in the materialized view, the refresh engine also needs to determine whether the current logical change message has updated the grouping and aggregation information, i.e., whether the grouping key has changed. If the grouping key has changed, it means that the record has migrated from the original group to the new group. In this case, incremental operation instructions need to be generated to perform the reorganization update operation, thereby deleting the grouping rows in the materialized view before the base table change and inserting the grouping rows after the base table change. That is, delete operation instructions for the old group and insert operation instructions for the new group are generated separately. If the grouping key has not changed, incremental operation instructions are generated to perform direct update operations on the grouping rows in the materialized view. That is, the hidden auxiliary columns and aggregate values ​​are updated according to the logic of first deleting the old values ​​and then inserting the new values, and the corresponding UPDATE operation instructions are generated.

[0039] Through the above processing methods for different operation types, the refresh engine can accurately convert data changes on any base table into a set of merge operations on the corresponding grouped rows in the materialized view, ensuring that the aggregated view can maintain logical consistency with the base table at any time. The entire process relies entirely on the grouped aggregation information pre-stored in the semantic structure, without the need for manual writing of any maintenance rules, thus realizing automated incremental maintenance of complex aggregation queries.

[0040] Ultimately, the refresh engine generates a series of incremental operation instructions for each change message to update aggregate values ​​and hide auxiliary columns in the materialized view. These instructions are in the form of SQL statements, such as "UPDATE dept_stats SET cnt=3, sum_sal=14000, avg_sal=4666.67,, min_sal=3000,, max_sal=6000 WHERE dept_id=10". Through this transformation, aggregate-type materialized views can also be updated precisely and efficiently in an incremental manner. The entire transformation process strictly adheres to the view type and grouping aggregation information pre-stored in the semantic structure, eliminating the need to re-parse the original SQL at runtime, thus ensuring the versatility and high performance of incremental maintenance.

[0041] S104: In a database transaction, execute incremental operation instructions in batches, and when committing the database transaction, update the corresponding refresh point information according to the current refresh cutoff point to achieve a single incremental refresh of the materialized view.

[0042] After generating all incremental operation instructions for updating the materialized view, the refresh engine starts a separate database transaction and executes the incremental operation instructions in batches within that transaction. By encapsulating all incremental operation instructions within the same database transaction, the atomicity of materialized view updates is ensured—either all changes are successfully applied or none take effect, thus avoiding inconsistencies in materialized view data caused by partial update failures. During the execution of incremental operation instructions, the refresh engine executes the corresponding SQL statements through the Server Programming Interface (SPI) provided by the PostgreSQL kernel, setting all SPI calls to writable mode (read_only=false) to ensure that insert, delete, and update operations on the materialized view table take effect immediately within the current transaction and are visible to subsequent operations.

[0043] Once the database transaction is successfully committed, all changes processed during this incremental refresh are persisted to the materialized view. At this point, the data state of the materialized view is completely consistent with that of the base table at the refresh cutoff point. Based on this, the refresh engine updates the refresh position information recorded in the system directory table according to the log sequence number corresponding to the refresh cutoff point, updating the `last_refresh_lsn` field in the `pg_incremental_matviews` table to the LSN value of the refresh cutoff point. This refresh position is used to identify the WAL log position that the current materialized view has synchronized to, serving as the starting point for the next incremental refresh. This ensures that subsequent refresh operations only need to read logical change messages starting from this position, avoiding duplicate processing of already applied changes and thus guaranteeing the continuity and efficiency of incremental refresh.

[0044] Through the aforementioned transactional batch execution and refresh point update mechanism, this embodiment of the invention achieves a single incremental refresh of the materialized view. The entire refresh process is completed atomically within a single database transaction, ensuring transaction-level consistency between the materialized view and the base table.

[0045] The above are embodiments of the method proposed in this invention. Based on the same idea, some embodiments of this invention also provide devices corresponding to the above methods.

[0046] Figure 2 This is a schematic diagram of the structure of an incremental materialized view implementation device based on PostgreSQL database logical decoding, provided in an embodiment of the present invention. Figure 2 As shown, it includes: At least one processor; and, At least one processor-communication-connected memory; wherein, The memory stores instructions that can be executed by at least one processor, which enables the at least one processor to perform the incremental materialized view implementation method based on PostgreSQL database logical decoding as described in any of the preceding items.

[0047] The various embodiments in this invention 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 device and medium embodiments are relatively simple in description because they are fundamentally similar to the method embodiments; relevant parts can be referred to the descriptions in the method embodiments.

[0048] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the present invention should be included within the scope of the claims of the present invention.

Claims

1. A method for implementing incremental materialized views based on PostgreSQL database logical decoding, characterized in that, The method includes: In response to the creation command of the materialized view, the definition query statement of the materialized view is semantically parsed to generate a semantic structure for maintaining the incremental operation features of the materialized view; Load the built-in logical decoding output plugin of the PostgreSQL database, listen to the WAL log records generated by the base table associated with the materialized view through the logical decoding output plugin, and decode the WAL log records into corresponding logical change messages; In response to an incremental refresh command for the materialized view, all logical change messages generated before the current refresh deadline are obtained. Based on the semantic structure, the logical change messages are parsed to generate an incremental operation instruction for updating the materialized view. The incremental operation instructions are executed in batches within a database transaction, and when the database transaction is committed, the corresponding refresh point information is updated according to the current refresh cutoff point, thereby achieving a single incremental refresh of the materialized view.

2. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 1, characterized in that, The definition query statement of the materialized view is semantically parsed to generate a semantic structure for maintaining the incremental operation features of the materialized view, specifically including: Based on a preset system directory table, register the metadata corresponding to the materialized view; wherein, the metadata includes name, definition query statement and dependent base table information; Based on the metadata, semantic parsing is performed on the definition query statement of the materialized view to extract the core meta-information in the definition query statement; Based on the core meta-information, a semantic structure is generated to maintain the incremental computational features of the materialized view.

3. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 1, characterized in that, Based on the semantic structure, the logical change message is parsed to generate incremental operation instructions for updating the materialized view, specifically including: Based on the semantic structure, the view type corresponding to the materialized view is determined; wherein, the view type includes simple projection type and aggregation type; If the view type is the simple projection type, determine whether the operation record corresponding to the logical change message meets the filtering conditions defined in the semantic structure, so that if the filtering conditions are met, an incremental operation instruction for updating the materialized view is generated according to the logical change message. If the view type is an aggregation type, incremental aggregation calculation is performed on the logical change message based on the grouping aggregation information in the semantic structure to generate incremental operation instructions for updating the aggregation value and hiding auxiliary columns in the materialized view.

4. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 3, characterized in that, Based on the grouping and aggregation information in the semantic structure, incremental aggregation calculations are performed on the logical change message to generate incremental operation instructions for updating the aggregated values ​​and hiding auxiliary columns in the materialized view, specifically including: If the operation type corresponding to the logical change message is an insertion operation or a deletion operation, the group row corresponding to the logical change message in the materialized view is located according to the group aggregation information in the semantic structure. Based on the hidden auxiliary column in the materialized view, incremental aggregation calculations are performed on the logical change message to update the hidden auxiliary column; Based on the updated hidden auxiliary column, determine the aggregate value corresponding to the grouping row, and generate an incremental operation instruction for updating the aggregate value and the hidden auxiliary column corresponding to the grouping row. When the operation type of the logical change message is an update operation, depending on whether the logical change message updates the group aggregation information, an incremental operation instruction is generated to perform a corresponding reorganization update operation or a direct update operation on the group rows in the materialized view.

5. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 4, characterized in that, After locating the grouping row corresponding to the logical change message in the materialized view, the method further includes: If the grouping aggregation information includes an extremum function, and the operation type corresponding to the logical change message is a deletion operation, and the deleted column value is equal to the maximum or minimum value in the current group row, the base table is looked up through the composite index in the materialized view to locate the maximum or minimum value of the group row at the current refresh cutoff point; wherein, the composite index is an index created based on the grouping columns and aggregation information in the base table on which the materialized view depends; Update the minimum or maximum value to the aggregate value corresponding to the grouping row.

6. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 4, characterized in that, Based on whether the logical change message updates the grouping aggregation information, an incremental operation instruction is generated for performing a corresponding reorganization update operation or a direct update operation on the grouping rows in the materialized view, specifically including: Determine whether the logical change message updates the group aggregation information; If not, an incremental operation instruction is generated to perform a direct update operation on the grouped rows in the materialized view; If so, generate an incremental operation instruction to perform a reorganization update operation, thereby deleting the grouped rows in the materialized view before the base table change and inserting the grouped rows after the base table change.

7. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 6, characterized in that, Before determining whether the logical change message updates the group aggregation information, the method further includes: Based on whether the base table satisfies the filtering conditions in the semantic structure before and after the change, the incremental maintenance operation type triggered by the materialized view is determined, and an incremental operation instruction for performing a direct update operation is generated according to the incremental maintenance operation type.

8. The incremental materialized view implementation method based on PostgreSQL database logical decoding according to claim 1, characterized in that, Prior to an incremental refresh command for the materialized view, the method further includes: Receive incremental refresh commands manually triggered by the user; Alternatively, monitor the difference between the latest log sequence number in the WAL log record of the base table and the log sequence number recorded at the last refresh, and generate an incremental refresh command for the materialized view when the sequence number difference reaches a preset sequence number threshold. Alternatively, determine the query frequency of the materialized view within a preset time window; if the query frequency is greater than a preset frequency threshold, generate an incremental refresh command for the materialized view.

9. An incremental materialized view implementation device based on PostgreSQL database logical decoding, characterized in that, The device includes: At least one processor; And, a memory communicatively connected to the at least one processor; The memory stores instructions that can be executed by the at least one processor, which are executed by the at least one processor to enable the at least one processor to perform an incremental materialized view implementation method based on PostgreSQL database logical decoding as described in any one of claims 1-8.

10. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are set as follows: An incremental materialized view implementation method based on PostgreSQL database logical decoding as described in any one of claims 1-8.

Images