A method, system and device for automatic reclamation of PostgreSQL database table spaces

Abstract

This application provides a method, system, and device for automatic tablespace reclamation in a PostgreSQL database, belonging to the field of database technology. The method includes: obtaining business attribute tags of a target data table and determining a first reclamation trigger threshold based on the business attribute tags; real-time collection of dynamic indicators of the target data table; wherein the dynamic indicators include at least the frequency of insert operations and the frequency of update operations per unit time; determining the space reuse probability value of dead tuples in the target data table based on the dynamic indicators; dynamically adjusting the first reclamation trigger threshold based on a preset piecewise linear adjustment function and the space reuse probability value to determine a corresponding second reclamation trigger threshold; when the space fragmentation rate of the target data table is greater than the second reclamation trigger threshold, matching the corresponding reclamation operation according to the relationship between the space reuse probability value and a preset diversion threshold; wherein the preset diversion threshold is used to distinguish different reclamation operations.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, system and device for automatic reclamation of PostgreSQL database tablespaces. Background Technology

[0002] PostgreSQL, as an open-source relational database management system, uses Multi-Version Concurrency Control (MVCC) to achieve transaction isolation. Under MVCC, when performing update (UPDATE) or delete (DELETE) operations, the original data tuples are not immediately physically deleted, but are marked as dead tuples. The storage space they occupy becomes logically available, but is still physically retained in the table data file.

[0003] To reclaim space occupied by dead tuples, the industry commonly uses a combination of scheduled tasks and fixed thresholds to trigger space reclamation operations. For example, a scheduled task can be configured using the pg_cron plugin to execute a VACUUM FULL operation on all tables that meet the condition when the space fragmentation rate of a certain table reaches, for example, 50%. Existing technical solutions only trigger reclamation based on static indicators such as space fragmentation rate, lacking the ability to predict space reusability. This may lead to unnecessary table locking for highly reusable tables. Furthermore, the use of a uniform fixed threshold does not consider the differences in operational characteristics of different business tables, making it impossible to balance the timeliness of reclamation with the overhead of table locking. Summary of the Invention

[0004] To address the aforementioned issues, this application provides a method, system, and device for automatic reclamation of PostgreSQL database tablespaces. This mechanism predicts the reuse probability of dead tuple spaces and adjusts the reclamation trigger threshold and reclamation operation type accordingly, thereby releasing the physical storage space of low-reuse tables in a timely manner while avoiding ineffective reclamation.

[0005] In a first aspect, embodiments of this application provide a method for automatic reclamation of PostgreSQL database tablespaces, the method comprising: Obtain the business attribute tags of the target data table, and determine the first recycling trigger threshold of the target data table based on the business attribute tags; The dynamic indicators of the target data table are collected in real time; wherein, the dynamic indicators include at least the frequency of insertion operations and the frequency of update operations per unit time. Based on the dynamic indicators, determine the spatial reuse probability value of dead tuples in the target data table; Based on a preset piecewise linear adjustment function and the space reuse probability value, the first recycling trigger threshold is dynamically adjusted to determine the corresponding second recycling trigger threshold. When the space fragmentation rate of the target data table is greater than the second recycling trigger threshold, a corresponding recycling operation is matched according to the relationship between the space reuse probability value and the preset diversion threshold; wherein, the preset diversion threshold is used to distinguish different recycling operations.

[0006] In one implementation of this application, obtaining the business attribute tags of the target data table and determining the first recycling trigger threshold of the target data table based on the business attribute tags specifically includes: Access the PostgreSQL system directory table and read the metadata comment information field of the target data table; A preset business identifier is extracted from the metadata annotation information field using a regular expression matching algorithm, and used as the business attribute tag; the business attribute tag includes at least a high-frequency business tag and a low-frequency business tag. If the preset business identifier is not extracted, the business attribute tag is determined based on the table name rules of the target data table and / or the historical access frequency. Based on the mapping relationship between the business attribute tags and the preset threshold configuration template, the first recycling trigger threshold of the target data table is determined.

[0007] In one implementation of this application, the real-time collection of dynamic indicators of the target data table specifically includes: Based on a preset collection period, statistical snapshots of the target data table in the PostgreSQL built-in statistics interface are periodically read; wherein, the statistical snapshots include at least the current cumulative number of insert operations and the current cumulative number of update operations; Based on the current cumulative number of insertion operations in the current snapshot and the historical cumulative number of insertion operations in the previous snapshot, the difference in the number of insertion operations is calculated, and the first ratio of the difference in the number of insertion operations to the duration of the preset acquisition period is used as the insertion operation frequency per unit time. The difference in update operation count is calculated based on the current cumulative update operation count in the current snapshot and the historical cumulative update operation count in the previous snapshot. The second ratio of the update operation count difference to the collection period duration is used as the update operation frequency per unit time.

[0008] In one implementation of this application, the method further includes: Calculate the current percentage of dead tuples based on the number of dead tuples and the number of live tuples in the target data table at the current moment. The rate of change of the proportion of dead bytes is determined based on the difference between the current proportion of dead bytes and the historical proportion of dead bytes in the previous preset collection period, and the preset collection period. The spatial reuse probability change amplitude is calculated based on the spatial reuse probability value determined at the current moment based on the insertion operation frequency and the update operation frequency, and the historical spatial reuse probability value of the previous preset acquisition period. The absolute value of the rate of change of the proportion of dead units is matched with the first preset threshold interval, and the magnitude of the change of the spatial reuse probability is matched with the second preset threshold interval. If the absolute value of the rate of change of the proportion of dead units is less than the lower limit of the first preset threshold interval, and the magnitude of the change of the spatial reuse probability is less than the lower limit of the second preset threshold interval, the preset collection period is extended to the first target duration. If the absolute value of the rate of change of the proportion of dead units is greater than the upper limit of the first preset threshold interval, or if the magnitude of the change of the spatial reuse probability is greater than the upper limit of the second preset threshold interval, the preset acquisition period is shortened to the second target duration.

[0009] In one implementation of this application, determining the spatial reuse probability value of dead tuples in the target data table based on the dynamic index specifically includes: Map the insertion operation frequency to a first normalization interval to determine the first normalization value; The second normalized value is determined by the second normalized interval of the update operation frequency mapping value; The first normalized value, the second normalized value, and the preset weight coefficient group are weighted and calculated to determine the spatial reuse probability value; wherein, the preset weight coefficient group includes a first weight coefficient and a second weight coefficient corresponding to the first normalized value and the second normalized value, respectively; the sum of the first weight coefficient and the second weight coefficient is 1.

[0010] In one implementation of this application, the step of dynamically adjusting the first recycling trigger threshold based on a preset piecewise linear adjustment function and the space reuse probability value to determine a corresponding second recycling trigger threshold specifically includes: If the space reuse probability value is less than or equal to the first probability threshold, the product of the first recycling trigger threshold and the first adjustment coefficient is used as the second recycling trigger threshold; wherein, the first adjustment coefficient is determined based on the difference between the first probability threshold and the space reuse probability value. If the space reuse probability value is greater than the first probability threshold and less than the second probability threshold, the first recycling trigger threshold shall be used as the second recycling trigger threshold. When the space reuse probability value is greater than or equal to the second probability threshold, the product of the first recycling trigger threshold and the second adjustment coefficient is used as the second recycling trigger threshold; wherein, the second adjustment coefficient is determined based on the difference between the space reuse probability value and the second probability threshold, and the second adjustment coefficient is less than the first adjustment coefficient; the first probability threshold and the second probability threshold are symmetrically distributed about the central symmetric point of the preset piecewise linear adjustment function.

[0011] In one implementation of this application, the step of matching a corresponding recycling operation based on the relationship between the space reuse probability value and a preset diversion threshold specifically includes: When the space reuse probability value is greater than the preset diversion threshold, the first recycling operation is matched; the first recycling operation is the VACUUM operation, which is used to clean up dead tuples and update the free space mapping, mark the dead tuple space as reusable in the table, and does not shrink the table physical file; When the space reuse probability value is less than or equal to the preset diversion threshold, a second recycling operation is matched; the second recycling operation is a VACUUM FULL operation, which is used to rewrite the table data file and shrink the table physical file.

[0012] In one implementation of this application, the method further includes: After performing the matching recycling operation, the recycling effect data of the target data table is recorded; wherein, the recycling effect data includes at least the space fragmentation rate before recycling, the space release amount after recycling, the recycling execution time, and the space growth rate within a preset time after recycling; When the spatial growth rate exceeds the first preset growth threshold for a first preset number of consecutive recycling cycles, the first recycling trigger threshold is increased according to preset rules. When the space growth rate is less than or equal to the first preset growth threshold in the first preset number of recycling cycles, and the space fragmentation rate growth rate is greater than the second preset growth threshold, the first recycling trigger threshold is reduced according to the preset rule.

[0013] Secondly, this application discloses an automatic tablespace reclamation system for a PostgreSQL database, characterized in that the system comprises: The acquisition and determination module is used to acquire the business attribute tags of the target data table and determine the first recycling trigger threshold of the target data table based on the business attribute tags; The acquisition module is used to acquire dynamic indicators of the target data table in real time; wherein, the dynamic indicators include at least the frequency of insertion operations and the frequency of update operations per unit time. The determination module is used to determine the spatial reuse probability value of dead tuples in the target data table based on the dynamic indicators. The dynamic adjustment module is used to dynamically adjust the first recycling trigger threshold based on a preset piecewise linear adjustment function and the space reuse probability value, and determine the corresponding second recycling trigger threshold. The matching module is used to match a corresponding recycling operation based on the relationship between the space reuse probability value and the preset diversion threshold when the space fragmentation rate of the target data table is greater than the second recycling trigger threshold; wherein, the preset diversion threshold is used to distinguish different recycling operations.

[0014] Thirdly, embodiments of this application also provide an automatic tablespace reclamation device for PostgreSQL databases, the device comprising: At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform the above-described PostgreSQL database tablespace automatic reclamation method.

[0015] Compared with the prior art, the significant advantages of this application are as follows: This application, through the aforementioned technical solution, utilizes business attribute tags to configure a basic reclamation trigger threshold and dynamically adjusts it in conjunction with a space reuse probability value. This enables differentiated reclamation of data tables with different business attributes. Simultaneously, it uses the space reuse probability value to predict the reuse probability of dead tuple space, allowing for the execution of different reclamation operations and avoiding unnecessary physical shrinkage overhead. This achieves a mechanism that predicts the reuse probability of dead tuple space and adjusts the reclamation trigger threshold and reclamation operation type accordingly, effectively releasing physical storage space of low-reuse tables while avoiding ineffective reclamation. Attached Figure Description

[0016] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 This is a flowchart illustrating an automatic tablespace reclamation method for a PostgreSQL database in an embodiment of this application. Figure 2 This is a schematic diagram of the structure of an automatic tablespace reclamation system for a PostgreSQL database in an embodiment of this application; Figure 3 This is a schematic diagram of the structure of an automatic tablespace reclamation device for a PostgreSQL database in an embodiment of this application. Detailed Implementation

[0017] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application 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 application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0018] PostgreSQL provides two reclamation operations: VACUUM (the first reclamation operation) cleans up dead tuples and updates the free space mapping, marking the space as reusable within the table, without shrinking the table physical file, and without locking the table during execution; VACUUMFULL (the second reclamation operation) rewrites the table file and shrinks the physical size, releasing the space to the operating system, and requires acquiring a table-level exclusive lock during execution, blocking business read and write operations.

[0019] To reclaim space occupied by dead tuples, the industry commonly uses a combination of scheduled tasks and fixed thresholds to trigger space reclamation operations. For example, a scheduled task can be configured using the pg_cron plugin to execute a VACUUM FULL operation on all tables that meet the condition when the space fragmentation rate of a certain table reaches, for example, 50%. Existing technical solutions only trigger reclamation based on static indicators such as space fragmentation rate, lacking the ability to predict space reusability. This may lead to unnecessary table locking for highly reusable tables. Furthermore, the use of a uniform fixed threshold does not consider the differences in operational characteristics of different business tables, making it impossible to balance the timeliness of reclamation with the overhead of table locking.

[0020] Based on this, this application provides a method, system, and device for automatic reclamation of PostgreSQL database tablespaces to solve the technical problem of the lack of an automatic tablespace reclamation scheme that can predict space reusability and determine reclamation based on the differences in the characteristics of comprehensive business table operations. It predicts the space reuse probability of dead tuples and adjusts the reclamation trigger threshold and reclamation operation type accordingly, thereby releasing the physical storage space of low-reusability tables in a timely manner while avoiding invalid reclamation.

[0021] The various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0022] This application provides a method for automatic tablespace reclamation in a PostgreSQL database, such as... Figure 1 As shown, the method may include steps S101-S105: S101, obtain the business attribute tags of the target data table, and determine the first recycling trigger threshold of the target data table based on the business attribute tags.

[0023] It should be noted that the server can serve as the execution subject of the PostgreSQL database tablespace automatic reclamation method, but the server is only an example, and the execution subject is not limited to the server. This application does not make any specific limitations on this.

[0024] The business attribute tags in this application are used to characterize the frequency level of business operations on the target data table, including at least high-frequency business tags and low-frequency business tags. High-frequency business tags correspond to data tables with frequent business write operations, such as order tables and transaction log tables, while low-frequency business tags correspond to data tables with sparse business write operations, such as historical archive tables and log backup tables.

[0025] In this embodiment of the application, the above-mentioned acquisition of the business attribute tags of the target data table and determination of the first recycling trigger threshold of the target data table based on the business attribute tags specifically includes: Access the PostgreSQL system directory table and read the metadata comment information field of the target data table. Extract a pre-defined business identifier from the metadata comment information field using a regular expression matching algorithm, which serves as the business attribute tag. The business attribute tags must include at least high-frequency and low-frequency business tags. If the pre-defined business identifier is not extracted, determine the business attribute tags based on the target data table's table name rules and / or historical access frequency. Based on the mapping relationship between the business attribute tags and a pre-defined threshold configuration template, determine the first recycling trigger threshold for the target data table.

[0026] In other words, the business attribute tags obtained in this application can be determined through two methods: manual annotation and automatic recognition. In the manual annotation method, the database administrator can logically annotate the table using the database comment function, writing preset business identifiers such as: Business tag: High-frequency table. For example, when creating an order table, the following is executed: COMMENT ON TABLE orders IS 'Business Tag: High Frequency Table'; COMMENT ON TABLE archive_logs IS 'Business Tag: Low Frequency Table'; At this point, the corresponding data table carries the manually labeled preset business identifier in its metadata annotation information field.

[0027] When automatically identifying business attribute tags, the server can access PostgreSQL system directory tables, such as pg_class and pg_description, to read the metadata annotation information fields of the target data table to be automatically recycled. Using a preset regular expression matching algorithm, the server extracts the business identifier from the metadata annotation information fields. For example, the preset regular expression matching algorithm is configured as: business tag\s*[::]\s*(high frequency|medium frequency|low frequency), extracting keywords such as high frequency and low frequency from the metadata annotation information fields. If no business identifier is detected, the business attribute tag can be automatically inferred based on table name rules or historical access frequency. Specifically, if there are preset first and second inference rules, the first inference rule sets the correspondence between table name keywords such as _log and _archive and business attribute tags, and the second inference rule can include historical access frequency ranges, with different historical access frequency ranges corresponding to different business attribute tags. This application does not specifically limit the specific settings of the first and second inference rules.

[0028] Unlike manual configuration, it automatically constructs business frequency tags by parsing the comment fields and table naming rules of the database system tables, providing an initial basis for differentiated thresholds.

[0029] Furthermore, the initial manually labeled business attribute tags in this application will gradually be overwritten by the automatically corrected business attribute tags from the system. For example, if a table marked as low frequency continuously triggers high-growth feedback, the system will automatically adjust its internal weight to medium frequency, realizing the transformation from "static configuration" to "dynamic evolution".

[0030] When determining the first recycling trigger threshold, this application loads data based on the mapping relationship between business attribute tags and preset threshold configuration templates. For example, the first recycling trigger threshold corresponding to a high-frequency business tag is 50%, and the first recycling trigger threshold corresponding to a low-frequency business tag is 30%. This mapping relationship is merely illustrative and is not specifically limited in this application. High-frequency tables corresponding to high-frequency business tags have higher fragmentation rate thresholds (i.e., recycling trigger thresholds), meaning the automatic recycling system has a greater tolerance for space waste in high-frequency tables, reducing recycling frequency to lessen the impact of table locking on services. Conversely, low-frequency tables corresponding to low-frequency business tags have lower fragmentation rate thresholds, meaning the system is more sensitive to the space occupancy of low-frequency tables, releasing physical storage resources promptly.

[0031] S102 collects dynamic indicators of the target data table in real time.

[0032] Among them, dynamic indicators include at least the frequency of insertion operations and the frequency of update operations per unit time.

[0033] In this embodiment of the application, the real-time collection of dynamic indicators of the target data table specifically includes: Based on a preset collection period, statistical snapshots of the target data table are periodically read from the PostgreSQL built-in statistics interface. Each statistical snapshot includes at least the current cumulative number of insert operations and the current cumulative number of update operations. The difference in the number of insert operations is calculated by comparing the current cumulative number of insert operations in the current statistical snapshot with the historical cumulative number of insert operations in the previous statistical snapshot. This difference is then used as the first ratio of the insertion operation difference to the collection period duration of the preset collection period, and is taken as the insertion operation frequency per unit time. Similarly, the difference in the number of update operations is calculated by comparing the current cumulative number of update operations in the current statistical snapshot with the historical cumulative number of update operations in the previous statistical snapshot. This difference is then used as the update operation frequency per unit time, and is taken as the update operation frequency per unit time.

[0034] Specifically, the dynamic metric collection process periodically reads statistical snapshots from the PostgreSQL built-in statistics interface based on a preset collection period. The initial preset collection period can be 60 seconds long, but this duration can be set by the user based on expert experience and is not specifically limited here. The PostgreSQL built-in statistics interface is, for example, `pg_stat_user_tables`. The statistical snapshot includes at least the current cumulative number of insert operations (`n_tup_ins` field) and the current cumulative number of update operations (`n_tup_upd` field). The current time is... The previous moment was , The duration of the data collection cycle is as follows: The difference in the number of insertion operations = n_tup_ins(t) n_tup_ins( ); Insertion frequency per unit time = Difference in the number of insertion operations ; The difference in the number of update operations = n_tup_upd(t) n_tup_upd( ); Update operation frequency per unit time = Difference in the number of update operations .

[0035] In one embodiment of this application, if the preset collection period is always a fixed collection period duration, then monitoring accuracy and resource consumption cannot be balanced according to the actual data table attributes. Therefore, this application provides the following embodiments: Calculate the current percentage of dead tuples based on the number of dead tuples and live tuples in the target data table at the current moment. Determine the rate of change of the dead tuple percentage based on the difference between the current percentage and the historical percentage in the previous preset acquisition period, and the preset acquisition period. Calculate the amplitude of change in the spatial reuse probability based on the spatial reuse probability value determined at the current moment based on the insertion and update operation frequencies, and the historical spatial reuse probability value in the previous preset acquisition period. Match the absolute value of the rate of change of the dead tuple percentage with a first preset threshold interval, and match the amplitude of change in the spatial reuse probability with a second preset threshold interval. If the absolute value of the rate of change of the dead tuple percentage is less than the lower limit of the first preset threshold interval, and the amplitude of change in the spatial reuse probability is less than the lower limit of the second preset threshold interval, extend the preset acquisition period to the first target duration. If the absolute value of the rate of change of the dead tuple percentage is greater than the upper limit of the first preset threshold interval, or the amplitude of change in the spatial reuse probability is greater than the upper limit of the second preset threshold interval, shorten the preset acquisition period to the second target duration.

[0036] In other words, the server can extract the number of dead tuples and the number of live tuples from the n_dead_tup and n_live_tup fields of pg_stat_user_tables, and then use the number of dead tuples as a metric. The current percentage of dead tuples is calculated as (number of surviving tuples + number of dead tuples). Then, the percentage of dead tuples is calculated as: (current percentage of dead tuples - historical percentage of dead tuples). The sampling period duration is used to calculate the rate of change of the proportion of dead tuples. The change amplitude of the spatial reuse probability is calculated as |current spatial reuse probability value - historical spatial reuse probability value|. Subsequently, the server matches the absolute value of the rate of change of the proportion of dead tuples with a first preset threshold interval, such as a lower limit of 0.01 and an upper limit of 0.05, both in % / second. The change amplitude of the spatial reuse probability is then matched with a second preset threshold interval, such as a lower limit of 0.05 and an upper limit of 0.2. The upper and lower limits of these two preset threshold intervals are merely illustrative examples, and this application does not impose specific limitations on them.

[0037] When the absolute value of the rate of change of the proportion of dead tuples is less than the lower limit of the first preset threshold interval, and the amplitude of the change of the spatial reuse probability is less than the lower limit of the second preset threshold interval, it indicates that the recent state of the target data table is stable and the risk of spatial expansion is low. The system will extend the preset collection period to the first target duration, for example, from 60 seconds to 600 seconds, to reduce the collection overhead of periodic readings. The specific value of the first target duration can be set according to the actual use scenario and is not specifically limited here.

[0038] When the absolute value of the rate of change in the proportion of dead tuples exceeds the upper limit of the first preset threshold interval, or the magnitude of the change in the spatial reuse probability exceeds the upper limit of the second preset threshold interval, it indicates that a spatial mutation has occurred in the target data table, such as a sudden surge in a large number of DELETE or INSERT operations. The server shortens the preset collection period to the second target duration, for example, from 60 seconds to 10 seconds, to improve the sensitivity to the risk of spatial expansion and ensure timely response and recovery. The specific value of the second target duration can be set according to the actual use scenario and is not specifically limited here.

[0039] Through the above embodiments, this application dynamically adjusts the monitoring indicator collection cycle according to the stability status of the table, thus solving the problem of resource waste or response lag caused by fixed-frequency collection.

[0040] S103, based on dynamic indicators, determine the spatial reuse probability value of dead tuples in the target data table.

[0041] In this embodiment of the application, the spatial reuse probability value of dead tuples in the target data table is determined based on dynamic indicators, specifically including: The insertion operation frequency is mapped to a first normalization interval to determine the first normalized value. The update operation frequency is mapped to a second normalization interval to determine the second normalized value. The first normalized value, the second normalized value, and a preset weighted coefficient set are weighted and calculated to determine the spatial reuse probability value. The preset weighted coefficient set includes a first weighted coefficient and a second weighted coefficient corresponding to the first normalized value and the second normalized value, respectively. The sum of the first weighted coefficient and the second weighted coefficient is 1.

[0042] The aforementioned space reuse probability value characterizes the probability that the space occupied by dead tuples in the target data table due to the multi-version concurrency control mechanism within the data file will be reused by subsequent write operations. The specific process for determining the space reuse probability value is as follows: First, the insertion operation frequency is mapped to the first normalization interval [0,1], and the first normalization value is determined. The first normalization value = min(1, insertion operation frequency / preset maximum insertion frequency). The preset maximum insertion frequency is a normalization benchmark value set based on specific business scenarios and expert experience, such as 10,000 times / minute, which is not specifically limited here.

[0043] Similarly, the update operation frequency is mapped to the second normalization interval [0,1] to determine the second normalization value. The second normalization value = min(1, update operation frequency / preset maximum update frequency). The preset maximum update frequency is set based on specific business scenarios and is not specifically limited here.

[0044] Then, the first normalized value, the second normalized value, and the preset weight coefficients obtained above are weighted and calculated. The space reuse probability value = first weight coefficient * first normalized value + second weight coefficient * second normalized value. The sum of the first weight coefficient and the second weight coefficient is 1. The two weight coefficients are set based on expert experience. Since the direct impact of insertion operation on space reuse is greater than that of update operation, and update operation produces dead tuples that may not be immediately filled by new data, the first weight coefficient is generally greater than the second weight coefficient. For example, the first weight coefficient is set to 0.6 and the second weight coefficient is set to 0.4.

[0045] This application associates the INSERT frequency with the dead tuple space release strategy. By calculating the space reuse probability, it dynamically selects lightweight cleanup or heavyweight physical shrinkage, thus solving the I / O waste problem caused by the blind recycling of traditional solutions.

[0046] S104, based on the preset piecewise linear adjustment function and the space reuse probability value, dynamically adjust the first recycling trigger threshold to determine the corresponding second recycling trigger threshold.

[0047] In this embodiment, based on a preset piecewise linear adjustment function and a space reuse probability value, the first recycling trigger threshold is dynamically adjusted to determine the corresponding second recycling trigger threshold, specifically including: When the spatial reuse probability value is less than or equal to a first probability threshold, the product of the first recovery trigger threshold and the first adjustment coefficient is used as the second recovery trigger threshold. The first adjustment coefficient is determined based on the difference between the first probability threshold and the spatial reuse probability value. When the spatial reuse probability value is greater than the first probability threshold but less than a second probability threshold, the first recovery trigger threshold is used as the second recovery trigger threshold. When the spatial reuse probability value is greater than or equal to the second probability threshold, the product of the first recovery trigger threshold and the second adjustment coefficient is used as the second recovery trigger threshold. The second adjustment coefficient is determined based on the difference between the spatial reuse probability value and the second probability threshold, and the second adjustment coefficient is less than the first adjustment coefficient. The first and second probability thresholds are symmetrically distributed about the central symmetric point of a preset piecewise linear adjustment function.

[0048] Specifically, a preset piecewise linear adjustment function uses the spatial reuse probability as the independent variable and outputs threshold adjustment coefficients, namely the first adjustment coefficient, the second adjustment coefficient, etc. The central symmetry point of this preset piecewise linear adjustment function is 0.5, and its value is between 0 and 1.

[0049] The first segment of the preset piecewise linear adjustment function: when the space reuse probability value P ≤ the first probability threshold. hour: First adjustment coefficient Second recycling trigger threshold = First recycling trigger threshold × ;at this time >1, the second recycling trigger adjustment threshold is greater than the first recycling trigger threshold, which means that the database system reduces recycling sensitivity and reduces recycling frequency.

[0050] Second paragraph: When the first probability threshold <P< the second probability threshold hour: The second recovery trigger threshold equals the first recovery trigger threshold; this can be understood as the adjustment coefficient being 1, keeping the first recovery trigger threshold unchanged.

[0051] Third paragraph: When the space reuse probability value P ≥ the second probability threshold hour: Second adjustment coefficient Second recycling trigger threshold = First recycling trigger threshold × ;at this time <1, the second recycling trigger threshold is less than the first recycling trigger threshold, which means that the database system increases recycling sensitivity and is more likely to trigger the lightweight first recycling operation.

[0052] Through the above-mentioned piecewise linear adjustment mechanism, this application can achieve a lower recycling trigger threshold for the data table in high reuse probability scenarios, at which time lightweight recycling will be frequently triggered; while in low reuse probability scenarios, the data table can achieve a higher recycling trigger threshold, at which time cautious heavy recycling (i.e., the second recycling operation) will be performed.

[0053] S105, when the space fragmentation rate of the target data table is greater than the second recycling trigger threshold, the corresponding recycling operation is matched according to the relationship between the space reuse probability value and the preset diversion threshold.

[0054] The preset diversion threshold is used to distinguish different recycling operations.

[0055] The space fragmentation rate of the target data table can be obtained through the PostgreSQL extension pgstattuple. The calculation formula is: Space fragmentation rate = (Number of bytes occupied by dead tuples / Total number of bytes in the table) × 100%. When the space fragmentation rate of the target data table exceeds the second recycling trigger threshold, the server enters the operation diversion decision.

[0056] Specifically, in this embodiment of the application, a corresponding recycling operation is matched based on the relationship between the space reuse probability value and the preset diversion threshold, specifically including: When the space reuse probability value is greater than the preset distribution threshold, the first recycling operation is matched. The first recycling operation is the VACUUM operation, which is used to clean up dead tuples and update the free space mapping, marking dead tuple spaces as reusable within the table, without shrinking the table physical file. When the space reuse probability value is less than or equal to the preset distribution threshold, the second recycling operation is matched. The second recycling operation is the VACUUM FULL operation, which is used to rewrite the table data file and shrink the table physical file.

[0057] In other words, the server can compare the space reuse probability value with a preset traffic splitting threshold, which can be set based on expert experience, for example, 0.3. When the space reuse probability value > 0.3, the first recycling operation is matched. The first recycling operation cleans up dead tuples and updates the free space mapping, marking the dead tuple space as reusable within the table. It does not shrink the table physical file, does not lock the table during execution, and has minimal impact on business operations. When the space reuse probability value ≤ 0.3, the second recycling operation is matched, rewriting the table data file of the target data table, physically removing dead tuples and shrinking the table physical file size, releasing the space to the operating system. During execution, a table-level exclusive lock needs to be acquired, blocking business read and write operations.

[0058] Furthermore, after performing the recycling operation in step S105 above, the method further includes: After performing the matching recycling operation, the recycling effect data of the target data table is recorded. This recycling effect data includes at least the space fragmentation rate before recycling, the space released after recycling, the recycling execution time, and the space growth rate within a preset time period after recycling. When the space growth rate is greater than a first preset growth threshold for a first preset number of recycling cycles, the first recycling trigger threshold is increased according to preset rules. When the space growth rate is less than or equal to the first preset growth threshold for a first preset number of recycling cycles, and the growth rate of the space fragmentation rate is greater than a second preset growth threshold, the first recycling trigger threshold is decreased according to preset rules.

[0059] In other words, the server can record data on the recycling effect, such as the space fragmentation rate before recycling, the amount of space released after recycling (calculated by the difference between pg_database_size and pg_relation_size), the recycling execution time, and the space growth rate within a preset time period (such as 24 hours) after recycling.

[0060] When the space growth rate exceeds the first preset growth threshold (e.g., 50%) for three consecutive recycling cycles, it indicates high business activity in the table, and the space is quickly filled with new data after recycling. The system increases the first recycling trigger threshold (e.g., from 40% to 50%) according to preset rules, reducing the recycling frequency and minimizing the impact of table locking on business. When the space growth rate is less than or equal to the first preset growth threshold (e.g., 50%) for three consecutive recycling cycles, and the space fragmentation rate growth rate exceeds the second preset growth threshold (e.g., 20% / hour), it indicates a large number of invalid updates without new data filling, and dead tuples are rapidly regenerated. The system decreases the first recycling trigger threshold (e.g., from 40% to 35%) according to preset rules and prioritizes matching the first recycling operation (VACUUM), replacing low-frequency heavy shrinkage with high-frequency lightweight maintenance. The content of the above preset rules can be set by the user according to the actual use scenario and is not specifically limited here.

[0061] Furthermore, a feedback mechanism based on historical recycling results (spatial growth rate, recycling time) was established, which can automatically correct the trigger threshold and collection frequency, enabling the system to have the self-learning ability to evolve with changes in business.

[0062] Before performing the matching reclamation operation, the server can also perform security checks such as lock wait detection, load detection, and disk space pre-check. For example, it queries the pg_locks system view to check if there are long transaction locks on the target data table (e.g., locktype='relation' and mode='AccessExclusiveLock' with a non-empty wait queue); if so, it suspends the reclamation operation and generates an alarm message to avoid lock conflicts that could block business operations. It queries pg_stat_activity to count the current number of active connections and the percentage of connections in a waiting state; when the overall database load exceeds a preset load threshold (e.g., active connections > 80% of the maximum number of connections), the reclamation operation is postponed to an off-peak period. Furthermore, before performing the VACUUM FULL operation, it queries the remaining disk space through the operating system interface or pg_stat_activity; when the remaining disk space is less than the space currently occupied by the target data table, it prohibits the VACUUM FULL operation to prevent database crashes due to insufficient temporary space.

[0063] This application, through the aforementioned technical solution, utilizes business attribute tags to configure a basic reclamation trigger threshold and dynamically adjusts it in conjunction with a space reuse probability value. This enables differentiated reclamation of data tables with different business attributes. Simultaneously, it uses the space reuse probability value to predict the reuse probability of dead tuple space, allowing for the execution of different reclamation operations and avoiding unnecessary physical shrinkage overhead. This achieves a mechanism that predicts the reuse probability of dead tuple space and adjusts the reclamation trigger threshold and reclamation operation type accordingly, effectively releasing physical storage space of low-reuse tables while avoiding ineffective reclamation.

[0064] Figure 2 This application provides a schematic diagram of the structure of an automatic tablespace reclamation system for a PostgreSQL database, as shown in the embodiments of this application. Figure 2 As shown, the PostgreSQL database tablespace automatic reclamation system 200 includes: The acquisition and determination module 201 is used to acquire the business attribute tags of the target data table and determine the first recycling trigger threshold of the target data table based on the business attribute tags. The acquisition module 202 is used to collect dynamic indicators of the target data table in real time. These dynamic indicators include at least the insertion operation frequency and update operation frequency per unit time. The determination module 203 is used to determine the space reuse probability value of dead tuples in the target data table based on the dynamic indicators. The dynamic adjustment module 204 is used to dynamically adjust the first recycling trigger threshold based on a preset piecewise linear adjustment function and the space reuse probability value to determine the corresponding second recycling trigger threshold. The matching module 205 is used to match the corresponding recycling operation based on the relationship between the space reuse probability value and a preset diversion threshold when the space fragmentation rate of the target data table is greater than the second recycling trigger threshold. The preset diversion threshold is used to distinguish different recycling operations.

[0065] Figure 3 A schematic diagram of the structure of an automatic tablespace reclamation device for a PostgreSQL database provided in this application embodiment is shown below. Figure 3 As shown, the device includes: At least one processor; and a memory communicatively connected to the at least one processor. The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: Obtain the business attribute tags of the target data table and determine the first recycling trigger threshold based on these tags. Real-time collection of dynamic metrics for the target data table is performed. These dynamic metrics include at least the frequency of insert and update operations per unit time. Based on the dynamic metrics, the space reuse probability value of dead tuples in the target data table is determined. The first recycling trigger threshold is dynamically adjusted based on a preset piecewise linear adjustment function and the space reuse probability value to determine the corresponding second recycling trigger threshold. When the space fragmentation rate of the target data table exceeds the second recycling trigger threshold, the corresponding recycling operation is matched according to the relationship between the space reuse probability value and a preset diversion threshold. The preset diversion threshold is used to distinguish between different recycling operations.

[0066] The various embodiments in this application 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 system and device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0067] The systems, devices, and methods provided in this application are one-to-one correspondences. Therefore, the systems and devices also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the systems and devices will not be repeated here.

[0068] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

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

Claims

1. A PostgreSQL database table space automatic reclamation method, characterized in that, The method includes: Obtain the business attribute tags of the target data table, and determine the first recycling trigger threshold of the target data table based on the business attribute tags; The dynamic indicators of the target data table are collected in real time; wherein, the dynamic indicators include at least the frequency of insertion operations and the frequency of update operations per unit time. Based on the dynamic indicators, determine the spatial reuse probability value of dead tuples in the target data table; Based on a preset piecewise linear adjustment function and the space reuse probability value, the first recycling trigger threshold is dynamically adjusted to determine the corresponding second recycling trigger threshold. When the space fragmentation rate of the target data table is greater than the second recycling trigger threshold, a corresponding recycling operation is matched according to the relationship between the space reuse probability value and the preset diversion threshold; wherein, the preset diversion threshold is used to distinguish different recycling operations.

2. The PostgreSQL database tablespace automatic reclaiming method of claim 1, wherein, The step of obtaining the business attribute tags of the target data table and determining the first recycling trigger threshold of the target data table based on the business attribute tags specifically includes: Access the PostgreSQL system directory table and read the metadata comment information field of the target data table; A preset business identifier is extracted from the metadata annotation information field using a regular expression matching algorithm, and used as the business attribute tag; the business attribute tag includes at least a high-frequency business tag and a low-frequency business tag. If the preset business identifier is not extracted, the business attribute tag is determined based on the table name rules of the target data table and / or the historical access frequency. Based on the mapping relationship between the business attribute tags and the preset threshold configuration template, the first recycling trigger threshold of the target data table is determined.

3. The PostgreSQL database tablespace automatic reclaiming method of claim 1, wherein, The real-time collection of dynamic indicators of the target data table specifically includes: Based on a preset collection period, statistical snapshots of the target data table in the PostgreSQL built-in statistics interface are periodically read; wherein, the statistical snapshots include at least the current cumulative number of insert operations and the current cumulative number of update operations; Based on the current cumulative number of insertion operations in the current snapshot and the historical cumulative number of insertion operations in the previous snapshot, the difference in the number of insertion operations is calculated, and the first ratio of the difference in the number of insertion operations to the duration of the preset acquisition period is used as the insertion operation frequency per unit time. The difference in update operation count is calculated based on the current cumulative update operation count in the current snapshot and the historical cumulative update operation count in the previous snapshot. The second ratio of the update operation count difference to the collection period duration is used as the update operation frequency per unit time.

4. The PostgreSQL database tablespace automatic reclamation method of claim 3, wherein, The method further includes: Calculate the current percentage of dead tuples based on the number of dead tuples and the number of live tuples in the target data table at the current moment. The rate of change of the proportion of dead bytes is determined based on the difference between the current proportion of dead bytes and the historical proportion of dead bytes in the previous preset collection period, and the preset collection period. The spatial reuse probability change amplitude is calculated based on the spatial reuse probability value determined at the current moment based on the insertion operation frequency and the update operation frequency, and the historical spatial reuse probability value of the previous preset acquisition period. The absolute value of the rate of change of the proportion of dead units is matched with the first preset threshold interval, and the magnitude of the change of the spatial reuse probability is matched with the second preset threshold interval. If the absolute value of the rate of change of the proportion of dead units is less than the lower limit of the first preset threshold interval, and the magnitude of the change of the spatial reuse probability is less than the lower limit of the second preset threshold interval, the preset collection period is extended to the first target duration. If the absolute value of the rate of change of the proportion of dead units is greater than the upper limit of the first preset threshold interval, or if the magnitude of the change of the spatial reuse probability is greater than the upper limit of the second preset threshold interval, the preset acquisition period is shortened to the second target duration.

5. The PostgreSQL database tablespace automatic reclamation method of claim 1, wherein, The step of determining the spatial reuse probability value of dead tuples in the target data table based on the dynamic index specifically includes: Map the insertion operation frequency to a first normalization interval to determine the first normalization value; Map the update operation frequency to the second normalization interval to determine the second normalization value; The first normalized value, the second normalized value, and the preset weight coefficient group are weighted and calculated to determine the spatial reuse probability value; wherein, the preset weight coefficient group includes a first weight coefficient and a second weight coefficient corresponding to the first normalized value and the second normalized value, respectively; the sum of the first weight coefficient and the second weight coefficient is 1.

6. The PostgreSQL database tablespace automatic reclamation method of claim 1, wherein, The step of dynamically adjusting the first recycling trigger threshold based on a preset piecewise linear adjustment function and the space reuse probability value to determine a corresponding second recycling trigger threshold specifically includes: If the space reuse probability value is less than or equal to the first probability threshold, the product of the first recycling trigger threshold and the first adjustment coefficient is used as the second recycling trigger threshold; wherein, the first adjustment coefficient is determined based on the difference between the first probability threshold and the space reuse probability value. If the space reuse probability value is greater than the first probability threshold and less than the second probability threshold, the first recycling trigger threshold shall be used as the second recycling trigger threshold. When the space reuse probability value is greater than or equal to the second probability threshold, the product of the first recycling trigger threshold and the second adjustment coefficient is used as the second recycling trigger threshold; wherein, the second adjustment coefficient is determined based on the difference between the space reuse probability value and the second probability threshold, and the second adjustment coefficient is less than the first adjustment coefficient; the first probability threshold and the second probability threshold are symmetrically distributed about the central symmetric point of the preset piecewise linear adjustment function.

7. The PostgreSQL database tablespace automatic reclamation method of claim 1, wherein, The step of matching the corresponding recycling operation based on the relationship between the space reuse probability value and the preset diversion threshold specifically includes: When the space reuse probability value is greater than the preset diversion threshold, the first recycling operation is matched; the first recycling operation is the VACUUM operation, which is used to clean up dead tuples and update the free space mapping, mark the dead tuple space as reusable in the table, and does not shrink the table physical file; When the space reuse probability value is less than or equal to the preset diversion threshold, a second recycling operation is matched; the second recycling operation is a VACUUM FULL operation, which is used to rewrite the table data file and shrink the table physical file.

8. The PostgreSQL database tablespace automatic reclamation method of claim 1, wherein, The method further includes: After performing the matching recycling operation, the recycling effect data of the target data table is recorded; wherein, the recycling effect data includes at least the space fragmentation rate before recycling, the space release amount after recycling, the recycling execution time, and the space growth rate within a preset time after recycling; When the spatial growth rate exceeds the first preset growth threshold for a first preset number of consecutive recycling cycles, the first recycling trigger threshold is increased according to preset rules. When the space growth rate is less than or equal to the first preset growth threshold in the first preset number of recycling cycles, and the space fragmentation rate growth rate is greater than the second preset growth threshold, the first recycling trigger threshold is reduced according to the preset rule.

9. A PostgreSQL database tablespace automatic reclamation system, characterized by, The system includes: The acquisition and determination module is used to acquire the business attribute tags of the target data table and determine the first recycling trigger threshold of the target data table based on the business attribute tags; The acquisition module is used to acquire dynamic indicators of the target data table in real time; wherein, the dynamic indicators include at least the frequency of insertion operations and the frequency of update operations per unit time. The determination module is used to determine the spatial reuse probability value of dead tuples in the target data table based on the dynamic indicators. The dynamic adjustment module is used to dynamically adjust the first recycling trigger threshold based on a preset piecewise linear adjustment function and the space reuse probability value, and determine the corresponding second recycling trigger threshold. The matching module is used to match a corresponding recycling operation based on the relationship between the space reuse probability value and the preset diversion threshold when the space fragmentation rate of the target data table is greater than the second recycling trigger threshold; wherein, the preset diversion threshold is used to distinguish different recycling operations.

10. A PostgreSQL database table space automatic reclamation apparatus, characterized by, 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, which, when executed by the at least one processor, enables the at least one processor to perform the automatic reclamation method for PostgreSQL database tablespaces as described in any one of claims 1-8.

Images