A method and system for automatic database reclamation due to database bloat
By coordinating internal database expansion detection with external execution, the problem of database expansion space reclamation interruption was solved, achieving automated and reliable storage space reclamation and ensuring the normal operation of the database.
Patent Information
- Application Number
- CN202611628882.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-10-21
- Publication Date
- 2026-11-17
AI Technical Summary
Existing technologies are prone to interruption or failure when performing database expansion space reclamation, and cannot reliably achieve automated reclamation, especially when scheduled tasks trigger kernel errors or external script execution is interrupted.
Inflation detection is performed using a read-only detection function deployed inside the database, maintenance tasks are generated and added to the task queue, and instructions for setting session-level lock timeout parameters and maintenance actions are executed on the same connection using an execution terminal outside the database in an auto-commit manner, ensuring independent transaction execution.
It enables automated and reliable reclamation of storage space occupied by database expansion without interrupting the reclamation process, avoiding kernel errors and long-term lock resource occupation, and ensuring normal business operations of the database.
Smart Images

Figure CN123115123A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database technology, and in particular to a method and system for automatic database expansion and recycling. Background Technology
[0002] Relational databases typically employ Multi-Version Concurrency Control (MVCC). When performing update or delete operations, existing data tuples are marked as dead tuples, but the storage space they occupy is still physically retained in the data file. The database kernel's built-in background cleanup process only marks the corresponding page space as reusable, without reducing the physical size of the data file. After a large number of write operations, the table and index files become increasingly bloated, leading to excessive disk space consumption.
[0003] To reclaim the aforementioned bloat, existing technologies typically rely on scheduled tasks to trigger space reclamation operations. One approach involves creating a custom function within the database, which is then called by a scheduled plugin to perform space reclamation operations on an object-by-object basis. Another approach involves deploying scripts outside the database to perform space reclamation or index rebuilding operations on a table-by-table basis after querying system views.
[0004] However, both of the above methods have shortcomings in automating space reclamation operations: the first method may trigger database kernel errors during the timed execution process, causing the automated reclamation process to be interrupted and difficult to continue; while the other method may also experience execution interruption or failure when performing space reclamation operations, and cannot guarantee that the reclamation operation is executed reliably.
[0005] Therefore, how to achieve reliable and automated recycling of database expansion without interrupting the recycling process has become a pressing technical problem to be solved in this field. Summary of the Invention
[0006] To address the aforementioned issues, embodiments of this application provide a method and system for automatic database bloat and reclamation.
[0007] In a first aspect, embodiments of this application provide a method for automatic database reclamation due to database bloat, the method comprising: An expansion detection function deployed within the database is used to perform an expansion detection on objects in the database to determine the objects to be maintained and the corresponding maintenance actions; wherein, the read-only detection function only performs query operations during runtime; A maintenance task is generated based on the object to be maintained and the maintenance action, and the maintenance task is added to the task queue of the database; The maintenance task is retrieved from the task queue by an execution terminal deployed outside the database; Through the execution terminal, the first instruction and the second instruction are executed sequentially in an auto-commit manner on the same connection between the execution terminal and the database to perform the maintenance action on the object to be maintained and reclaim the storage space occupied by the object to be maintained; wherein, the first instruction is used to set the session-level lock timeout parameter, and the second instruction is the maintenance instruction corresponding to the maintenance action; the first instruction and the second instruction are executed as independent transactions, so that the second instruction is not executed within an explicit transaction block.
[0008] In one implementation of this application, the step of performing bloat detection on objects in the database using a read-only detection function deployed within the database to determine the object to be maintained and the corresponding maintenance action specifically includes: When the object is a table, the statistical view of the database is read through the read-only detection function to obtain the first dead tuple ratio of the object, and the objects whose first dead tuple ratio is greater than or equal to the pre-screening threshold are identified as pre-screened objects. Physical sampling statistics are performed on the pre-screened objects to determine the proportion of the second dead group of the pre-screened objects; Based on the second dead tuple ratio, determine the object to be maintained and the corresponding maintenance action.
[0009] In one implementation of this application, the step of reading the statistical view of the database, obtaining the proportion of the first dead tuples of the objects, and determining objects whose proportion of the first dead tuples is greater than or equal to the pre-screening threshold as pre-screened passing objects specifically includes: Objects whose mode name does not belong to the system mode, whose physical volume is greater than or equal to the preset minimum object volume threshold, and whose first dead tuple ratio is greater than or equal to the pre-screening threshold are identified as the pre-screened objects; wherein, the first dead tuple ratio is equal to the quotient obtained by dividing the number of dead tuples by the sum of the number of dead tuples and the number of live tuples.
[0010] In one implementation of this application, the step of performing physical sampling statistics on the pre-screened objects to determine the proportion of the second dead tuples of the pre-screened objects specifically includes: Sampling statistics are performed on the storage pages of the pre-screened objects to determine the percentage of space occupied by dead tuples of the pre-screened objects. The percentage of space occupied by the dead tuples is determined as the second dead tuple ratio of the pre-screened objects, and the first dead tuple ratio of the pre-screened objects is updated to the second dead tuple ratio.
[0011] In one implementation of this application, determining the object to be maintained and the corresponding maintenance action based on the second dead tuple ratio specifically includes: If the proportion of the second dead tuples of the pre-screened objects is greater than or equal to the first preset threshold, the pre-screened objects are identified as objects to be maintained. Based on the proportion of the second dead tuples of the object to be maintained and the physical volume of the object to be maintained, perform the following operations: If the proportion of the second dead tuples of the object to be maintained is less than the second preset threshold, the maintenance action for the object to be maintained is determined to be online cleanup; If the proportion of the second dead tuples of the object to be maintained is greater than or equal to the second preset threshold, and the physical volume of the object to be maintained is less than or equal to the preset volume safety threshold, the maintenance action of the object to be maintained is determined to be offline rewrite cleanup. If the proportion of the second dead tuple of the object to be maintained is greater than or equal to the second preset threshold, and the physical volume of the object to be maintained is greater than the preset volume safety threshold, the maintenance action of the object to be maintained is determined to be manual intervention. The maintenance tasks corresponding to the online cleanup can be executed by the execution terminal at any time; the maintenance tasks corresponding to the offline rewrite cleanup can be executed by the execution terminal within a preset maintenance time window; the manual intervention instruction prohibits the corresponding maintenance tasks from being added to the task queue and generates alarm information.
[0012] In one implementation of this application, the step of performing bloat detection on objects in the database using a read-only detection function deployed within the database to determine the object to be maintained and the corresponding maintenance action further includes: When the object is an index, the index type of the object is obtained through the read-only detection function, and the following operations are performed: When the index type is B-Tree, the physical volume of the object and the average density of the leaf pages are obtained through the read-only detection function. When the physical volume of the object is greater than or equal to the preset minimum object volume threshold and the average density of the leaf pages is less than the preset density threshold, the object is identified as an object to be maintained, and the maintenance action of the object to be maintained is determined to be online index rebuilding. When the index type is GIN, the number of pages to be processed for the object is obtained through the read-only detection function. When the number of pages to be processed is greater than or equal to the first page number threshold and less than the second page number threshold, the table to which the object belongs is identified as an object to be maintained, and the maintenance action for the object to be maintained is determined to be online cleanup. When the number of pages to be processed is greater than or equal to the second page number threshold, the object is identified as an object to be maintained, and the maintenance action for the object to be maintained is determined to be online index rebuilding. If the index type is GiST or Hash, the object will not be identified as an object to be maintained.
[0013] In one implementation of this application, the step of generating a maintenance task based on the object to be maintained and the maintenance action, and adding the maintenance task to the task queue of the database, specifically includes: Check whether the object to be maintained and the maintenance action corresponding to it, which are in a pending state, already exist in the task queue; If the maintenance task does not exist in the task queue, the maintenance task is generated and added to the task queue.
[0014] In one implementation of this application, the step of retrieving the maintenance task from the task queue via an execution terminal deployed outside the database specifically includes: The row-level locking query statement is used to retrieve maintenance tasks that are in a pending state from the task queue; wherein, the row-level locking query statement is used to add a row-level lock to the data rows that are hit by the query and skip the data rows that have been locked by other execution ends; The status of the maintenance task in the task queue is updated from pending to being processed.
[0015] In one implementation of this application, before performing the maintenance action on the object to be maintained, the method further includes: The execution terminal determines whether the current time is within the preset maintenance time window corresponding to the maintenance action; The execution terminal determines whether the number of active sessions in the database is greater than or equal to the active session number threshold. If so, the execution terminal pauses the acquisition of the maintenance task. When the execution terminal acquires the maintenance task from the task queue, the number of the maintenance tasks acquired is less than or equal to a preset single-round task number threshold. The method further includes: If the lock is not acquired within the time corresponding to the session-level lock timeout parameter during the execution of the second instruction, the execution terminal terminates the maintenance task. The execution terminal retryes a terminated or failed maintenance task a preset number of times, and marks the maintenance task as failed after the number of retries exceeds the preset number; The execution terminal writes the execution result of the maintenance task into the task queue.
[0016] Secondly, embodiments of this application also provide an automatic database bloat recovery system, the system comprising: The expansion detection module is used to perform expansion detection on objects in the database through a read-only detection function deployed inside the database, to determine the objects to be maintained and the corresponding maintenance actions; wherein, the read-only detection function only performs query operations during runtime; The maintenance task generation module is used to generate maintenance tasks based on the object to be maintained and the maintenance action, and add the maintenance tasks to the task queue of the database; The maintenance task acquisition module is used to acquire the maintenance task from the task queue through an execution terminal deployed outside the database; The recycling module is used to execute a first instruction and a second instruction sequentially on the same connection between the execution terminal and the database in an auto-commit manner to perform the maintenance action on the object to be maintained and reclaim the storage space occupied by the object to be maintained; wherein, the first instruction is used to set the session-level lock timeout parameter, and the second instruction is the maintenance instruction corresponding to the maintenance action; the first instruction and the second instruction are executed as independent transactions, so that the second instruction is not executed within an explicit transaction block.
[0017] Compared with the prior art, the significant advantages of this application are as follows: By separating bloat detection from space reclamation execution, bloat detection is performed by a read-only detection function deployed inside the database that does not perform any actions other than query operations during runtime, while space reclamation is performed by an execution terminal deployed outside the database according to a task queue. This ensures that space reclamation instructions are no longer initiated by functions inside the database, thus avoiding falling into the execution context restricted by the database kernel. This overcomes the problem of interruption caused by kernel errors when automating space reclamation operations in existing methods.
[0018] Furthermore, during space reclamation, the execution end independently executes the first instruction for setting session-level lock timeout parameters and the second instruction as a maintenance instruction sequentially on the same connection using an auto-commit mechanism. This prevents the second instruction from being committed as part of an explicit transaction block, thus avoiding the space reclamation instruction failing due to being implicitly wrapped into a transaction block. This ensures the reliable execution of the reclamation operation. Additionally, it terminates promptly after lock acquisition timeout, preventing prolonged lock resource occupation and blocking of other business operations in the database. Therefore, automatic reclamation of storage space occupied by database expansion is achieved without interrupting the reclamation process. Attached Figure Description
[0019] 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 1This is a schematic flowchart of a database expansion automatic reclamation method according to an embodiment of this application; Figure 2 This is a schematic diagram of a table-based hierarchical processing method in an embodiment of this application; Figure 3 This is a schematic diagram of the architecture of a database kernel and an external execution terminal in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of an automatic database expansion and recycling system according to an embodiment of this application. Detailed Implementation
[0020] 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.
[0021] To reclaim the aforementioned bloat, existing technologies typically rely on scheduled tasks to trigger space reclamation operations. One approach involves creating a custom function within the database, which is then called by a scheduled plugin to perform space reclamation operations on an object-by-object basis. Another approach involves deploying scripts outside the database to perform space reclamation or index rebuilding operations on a table-by-table basis after querying system views.
[0022] However, both of the above methods have shortcomings in automating space reclamation operations: the first method may trigger database kernel errors during the timed execution process, causing the automated reclamation process to be interrupted and difficult to continue; while the other method may also experience execution interruption or failure when performing space reclamation operations, and cannot guarantee that the reclamation operation is executed reliably.
[0023] Based on this, the embodiments of this application provide a method and system for automatic database expansion and reclamation. The expansion detection is performed by a read-only detection function deployed inside the database, and the space reclamation is performed by an execution terminal deployed outside the database according to the task queue. The instructions for setting session-level lock timeout parameters and the reclamation instructions are executed independently and sequentially on the same connection in an automatic submission manner, so as to reclaim the storage space occupied by database expansion without interrupting the reclamation process.
[0024] The various embodiments of this application are described in detail below with reference to the accompanying drawings.
[0025] This application provides a method for automatic database reclamation due to bloat, such as... Figure 1 As shown, the method may include steps S101-S104: S101, the server performs an expansion detection on objects in the database through a read-only detection function deployed inside the database, and determines the objects to be maintained and the corresponding maintenance actions.
[0026] The read-only detection function performs only a query operation during runtime.
[0027] In this embodiment, the read-only detection function can be the database function `maintenance.detect_bloat()`, written in PL / pgSQL. Its function body contains only a query (SELECT) statement and does not include any update, delete, insert, or other write operations, nor any space reclamation instructions. Therefore, this read-only detection function only performs query operations at runtime, holds only a lightweight shared read lock, and will not impose write locks to block online services.
[0028] This application allows for static review of the creation and definition code of the read-only detection function (such as install.sql) to confirm that its internal logic is solely for data retrieval. It also allows monitoring of the locks held during the function's execution via the pg_locks view.
[0029] In one embodiment of this application, the read-only detection function can be triggered by a scheduled task. Specifically, the scheduled task can be configured through a database scheduling plugin (such as pg_cron), with a standard Crontab configuration of 30 0. This means that `maintenance.detect_bloat()` is automatically called at 0:30 AM every day, with a default cycle of once a day, fixed during the off-peak hours at night. This read-only detection function operates on the currently connected single-target database; in multi-database scenarios, it can be distributed and called via a scheduled task.
[0030] In addition, the read-only detection function can also accept the following parameters: `p_check_tables` (Boolean type, default value is true) indicates whether table bloat detection is enabled; `p_check_btree` (Boolean type, default value is true) indicates whether B-Tree index density detection is enabled; `p_check_gin` (Boolean type, default value is true) indicates whether GIN index pending page detection is enabled; and `p_check_gist_hash` (Boolean type, default value is false) indicates whether GiST / Hash index detection is enabled. Notably, the thresholds required by the read-only detection function at runtime are not passed in as parameters, but are instead stored in the configuration table `maintenance.bloat_config`, and the function reads these thresholds directly from this table at runtime.
[0031] During the expansion detection process, this application can also exclude objects in system mode, i.e., filter system tables and operation and maintenance tool tables using the condition `nspname NOT IN ('pg_catalog','information_schema','maintenance')`; exclude small-sized objects, i.e., filter objects whose physical size is less than the minimum object size threshold (1GB by default) using the condition `pg_relation_size(c.oid)>= cfg.min_object_size`, and handle these small objects by the kernel's built-in background cleanup process; for partition tables, match the physical table by the object type identifier `relkind='r'`, ignore the partition parent table without physical data, and only perform expansion detection on the partition sub-tables that have reached the minimum object size threshold.
[0032] It should be noted that the server, as the executor of the database expansion automatic recycling method, is only an example, and the executor is not limited to the server. This application does not make any specific limitation on this.
[0033] In this embodiment of the application, a read-only detection function deployed inside the database is used to perform bloat detection on objects in the database to determine the objects to be maintained and the corresponding maintenance actions, specifically including: When the object is a table, the bloat detection can include two levels of cascaded detection. Specifically, a read-only detection function reads a statistical view of the database to obtain the proportion of the first dead tuples of the objects, and objects whose proportion of the first dead tuple is greater than or equal to the pre-screening threshold are identified as pre-screened passing objects. Physical sampling statistics are performed on the pre-screened passing objects to determine the proportion of the second dead tuples of the pre-screened passing objects. Based on the proportion of the second dead tuples, the objects to be maintained and their corresponding maintenance actions are determined.
[0034] In one specific embodiment, objects with a first dead tuple ratio greater than or equal to a pre-screening threshold are determined as pre-screened passing objects. Specifically, this includes determining objects whose mode name does not belong to the system mode, whose physical volume is greater than or equal to a preset minimum object volume threshold, and whose first dead tuple ratio is greater than or equal to the pre-screening threshold, as pre-screened passing objects. The first dead tuple ratio is equal to the quotient obtained by dividing the number of dead tuples by the sum of the number of dead tuples and the number of live tuples.
[0035] The first level of detection is a low-cost pre-screening based on statistical views: It reads the system views pg_class, pg_namespace, and pg_stat_user_tables, retrieves the number of dead tuples n_dead_tup and the number of live tuples n_live_tup from pg_stat_user_tables, and calculates the first dead tuple ratio dead_pct, which is calculated as: dead_pct = n_dead_tup / (n_live_tup + n_dead_tup) × 100%. The corresponding query is s.n_dead_tup::numeric / NULLIF(s.n_live_tup + s.n_dead_tup, 0). 100. As mentioned above, objects whose pattern name does not belong to the system pattern, whose physical volume is greater than or equal to the minimum object volume threshold, and whose first dead tuple ratio is greater than or equal to the pre-screening threshold (default 7.0%) are identified as pre-screened objects and enter the second-level detection. Since the first-level detection only reads statistical information and does not read physical pages, its overhead is extremely low.
[0036] The second-level detection is based on physical sampling statistics for confirmation: only for objects that have passed the pre-screening, the physical sampling statistics function is called. In a specific embodiment, this statistics function can be pgstattuple_approx(r.oid). The storage pages of the objects that have passed the pre-screening are sampled and statistically analyzed to obtain the percentage of space occupied by dead tuples, dead_tuple_percent. This percentage of space occupied by dead tuples is determined as the second dead tuple ratio dead_pct of the objects that have passed the pre-screening. The corresponding query statement is SELECT (pgstattuple_approx(r.oid)).dead_tuple_percent INTO dead_pct.
[0037] This application performs physical sampling statistics based on the object's storage pages, eliminating the need for a full table scan. This achieves accurate expansion data while avoiding the input / output overhead of a full physical scan. Subsequently, the proportion of the first dead tuple of the pre-screened object is updated to the proportion of the second dead tuple. That is, the proportion of the second dead tuple obtained from the second-level detection is used as the dead tuple proportion for that object, which is then used to determine subsequent maintenance actions.
[0038] In one embodiment of this application, the object to be maintained and the corresponding maintenance action are determined based on the proportion of the second dead tuples, specifically including: If the proportion of second dead tuples in pre-screened objects is greater than or equal to a first preset threshold, the pre-screened objects are identified as objects to be maintained. Based on the proportion of second dead tuples in the objects to be maintained and their physical volume, the following operations are performed: If the proportion of the second dead tuple of the object to be maintained is less than the second preset threshold, the maintenance action for the object to be maintained is determined to be online cleanup; If the proportion of the second dead tuple of the object to be maintained is greater than or equal to the second preset threshold, and the physical volume of the object to be maintained is less than or equal to the preset volume safety threshold, the maintenance action of the object to be maintained is determined to be offline rewrite cleanup. If the proportion of the second dead tuple of the object to be maintained is greater than or equal to the second preset threshold, and the physical volume of the object to be maintained is greater than the preset volume safety threshold, the maintenance action of the object to be maintained is determined to be manual intervention.
[0039] Among them, the maintenance tasks corresponding to the online cleanup mentioned above can be executed by the execution terminal at any time; the maintenance tasks corresponding to the offline rewrite cleanup can be executed by the execution terminal within a preset maintenance time window; manual intervention indicates that the corresponding maintenance tasks should not be added to the task queue and generates alarm information.
[0040] Specifically, refer to Figure 2 This application performs hierarchical processing on the table to obtain the proportion of the second dead tuple in the data table (i.e., Figure 2 dead_pct) and physical volume (i.e. Figure 2 Starting from the size (in the middle), three layers of judgment are performed sequentially. The first layer determines whether the proportion of the second dead tuple is greater than or equal to a first preset threshold, which is... Figure 2 The `table_threshold` parameter defaults to 15%. If not specified, this maintenance action is determined to be online cleanup. Figure 2 The first step involves a lightweight VACUUM operation, which has no exclusive locks and can be executed at any time. If the condition is met, the second-level decision is initiated. The second level checks whether the proportion of the second dead tuple is greater than or equal to a second preset threshold. The second preset threshold is... Figure 2 In the `table_repack` function, the default size is 40%. The third layer checks if the physical volume exceeds a preset volume safety threshold. This preset volume safety threshold is... Figure 2 `max_full_size`, default 50GB: If the physical volume exceeds the preset safe volume threshold, then the maintenance action is determined to be manual intervention. Figure 2 If the system triggers an out-of-limit termination, it will prevent the maintenance task for that object from being added to the task queue and generate an alarm message; if the physical volume is less than or equal to a preset volume safety threshold, the maintenance action will be determined as an offline rewrite cleanup, i.e. Figure 2 The VACUUM FULL task is generated within a window, holding an exclusive lock, and is executed only within a preset maintenance time window. Specifically, the maintenance task corresponding to offline rewrite and cleanup is only executed by the client within the preset maintenance time window; maintenance tasks requiring manual intervention are prohibited from being added to the task queue and generating alarm information.
[0041] In another embodiment of this application, an expansion detection function is deployed within the database to perform bloat detection on objects in the database, thereby determining the objects to be maintained and the corresponding maintenance actions. Specifically, this also includes: When the object is indexed, the inflation detection is performed differently based on the index type. Specifically, the read-only detection function obtains the object's index type and performs the following operations: When the index type is B-Tree, a read-only detection function reads pg_class, pg_namespace, and pg_am, and calls pgstatindex(oid) to obtain the average leaf page density avg_leaf_density. This retrieves the object's physical volume and the average leaf page density. If the object's physical volume is greater than or equal to a preset minimum object volume threshold, and the average leaf page density is less than a preset density threshold, the B-Tree index is identified as an object to be maintained, and the maintenance action for this object is determined to be online index rebuilding. When the index type is Generalized Inverted Index (GIN), a read-only detection function reads pg_class, pg_namespace, and pg_am, and calls get_raw_page(...) combined with gin_metapage_info(...) to read the GIN index's metadata pages. This retrieves the number of pages to be processed for the object. If the number of pages to be processed is greater than or equal to the first page number threshold and less than the second page number threshold, the table to which the GIN index belongs is identified as an object to be maintained, and the maintenance action for this object is determined to be online cleanup. When the number of pages to be processed is greater than or equal to the second page number threshold, the object is identified as an object to be maintained, and the maintenance action for the object to be maintained is online index rebuilding. If the index type is Generalized Search Trees (GiST) or Hash, the object is not identified as an object to be maintained, in order to avoid blind rebuilding without reliable metrics by explicitly disabling the `enable_gist_hash` switch by default.
[0042] In this application, no specific limit is made on the specific value of each threshold. For thresholds that have a size relationship, this application only limits the relative size relationship and does not make a specific limit on their specific value.
[0043] S102, the server generates maintenance tasks based on the objects to be maintained and the maintenance actions, and adds the maintenance tasks to the task queue of the database.
[0044] Specifically, the objects to be maintained and the maintenance actions are used to generate maintenance tasks and add them to the task queue. In one specific embodiment, the insertion operation is directly performed internally by the read-only detection function, writing the maintenance task into the task queue list `maintenance.cleanup_queue`; simultaneously, the read-only detection function declares a return value type `RETURNS TABLE(enqueued_type text, enqueued_name text, enqueued_action text)`, and returns the enqueued result as a result set to the external caller while writing to the queue. The maintenance tasks in the task queue record the following fields: `object_type`, indicating the object type (table or index); `schema_name`, indicating the schema name; `object_name`, indicating the table name or index name; `action`, indicating the action (online cleanup, offline rewrite cleanup, online index rebuilding, or manual intervention); `reason`, indicating the description of the reason for enqueuing (e.g., dead=46.0%, btree density=70.12); `object_size`, indicating the physical size of the object in bytes. For manual intervention, it instructs that the corresponding maintenance task should not be added to the task queue and generates an alarm message.
[0045] In this embodiment of the application, a maintenance task is generated based on the object to be maintained and the maintenance action, and the maintenance task is added to the task queue of the database, specifically including: Check if there are any maintenance tasks in the task queue that are pending and have corresponding maintenance actions. If there are no maintenance tasks in the task queue, create a maintenance task and add it to the task queue.
[0046] In other words, before adding a maintenance task, this application checks whether there is already a maintenance task in the task queue that is pending and corresponds to the object to be maintained and the maintenance action. If the maintenance task does not exist in the task queue, the maintenance task is generated and added to the task queue, thereby avoiding the repeated generation of maintenance tasks for the same object due to repeated detection.
[0047] Specifically, this application can restrict the repeated addition of the same maintenance action for the same object to the pending state by establishing a unique index on the task queue list. The status of maintenance tasks in the task queue includes pending state and processing state.
[0048] S103, the server retrieves maintenance tasks from the task queue through an execution terminal deployed outside the database.
[0049] Maintenance tasks are acquired by an execution client deployed outside the database. This execution client is a shell script, pg_bloat_worker.sh, which resides outside the database and connects to the database via the database's native command-line client, psql (e.g., psql -h ... -c "..."), using independent connections and sessions. This execution client includes: an environment and load verification module to verify the maintenance time window and the number of active database sessions; a task acquisition module to retrieve maintenance tasks from the task queue; and a dual-channel execution module to independently issue the first and second commands sequentially and write back the queue status.
[0050] The execution end can be a resident process running on the server, independent of the database kernel process, which cyclically retrieves and executes maintenance tasks from the task queue; there can be one or more execution ends, each of which interacts with the database through an independent connection and independently retrieves maintenance tasks from the task queue.
[0051] In this embodiment of the application, maintenance tasks are obtained from the task queue through an execution terminal deployed outside the database, specifically including: The execution end retrieves maintenance tasks in a pending state from the task queue using a row-level locking query. This query applies a row-level lock to the rows of data matched by the query, skipping rows already locked by other execution ends. For example, the query could be `SELECT ... FOR UPDATE SKIP LOCKED`. The execution end applies a row-level lock to the matched maintenance task records to exclusively acquire them, skipping records already locked by other execution ends. This ensures that multiple execution ends retrieve unique maintenance tasks without waiting for each other, avoiding duplicate execution and queuing. After retrieving a maintenance task, its status in the task queue is updated from pending to processing.
[0052] S104, the server executes the first and second instructions sequentially via the execution terminal on the same connection between the execution terminal and the database in an auto-commit manner, performs maintenance actions on the object to be maintained, and reclaims the storage space occupied by the object to be maintained.
[0053] The first instruction sets the session-level lock timeout parameter, and the second instruction is the maintenance instruction corresponding to the maintenance action. The first and second instructions are executed as independent transactions, so that the second instruction is not executed within an explicit transaction block.
[0054] After receiving the maintenance task, the execution end performs space reclamation. On the same connection (same session) established by the same psql, the execution end executes the first and second instructions sequentially in an auto-commit manner. The first instruction sets the session-level lock timeout parameter, which in one specific embodiment is SET lock_timeout='5s', and this parameter applies to the current session. The second instruction is the maintenance instruction corresponding to the maintenance action. When the maintenance action is online cleanup, the second instruction is VACUUM; when the maintenance action is offline rewrite cleanup, the second instruction is VACUUM FULL; when the maintenance action is online index rebuilding, the second instruction is REINDEX CONCURRENTLY.
[0055] In this embodiment, the execution end executes the first instruction and the second instruction independently and sequentially on the same connection using an auto-commit method. Because of the auto-commit method, each individually executed statement constitutes an implicit transaction, allowing the first and second instructions to be executed as independent transactions. The second instruction is not executed within an explicit transaction block. Furthermore, since the first and second instructions reside on the same connection (i.e., the same session), the session-level lock timeout parameter set by the first instruction applies to the second instruction, ensuring that the second instruction terminates promptly if it fails to acquire the lock within a preset time, thus preventing prolonged lock resource occupation and blocking of other business operations in the database.
[0056] In one embodiment of this application, before performing maintenance actions on the object to be maintained, the method further includes: The execution end determines whether the current time is within the preset maintenance time window corresponding to the maintenance action. For example, offline rewrite cleanup and online index rebuild are only allowed to be executed within the preset maintenance time window (such as 1:00 to 6:00). Outside the window, the maintenance task remains in a pending state and is not executed.
[0057] The execution end determines whether the number of active database sessions is greater than or equal to the active session threshold. If so, the execution end pauses the retrieval of maintenance tasks, meaning that no maintenance tasks are retrieved in this round when the database load is too high. Specifically, when retrieving maintenance tasks from the task queue, the execution end retrieves fewer than or equal to a preset single-round task count threshold to avoid long-tail waiting caused by retrieving too many tasks in a single round.
[0058] Furthermore, if the lock is not acquired within the time limit corresponding to the session-level lock timeout parameter during the execution of the second instruction, the execution end terminates the maintenance task. The execution end retryes the terminated or failed maintenance task a preset number of times, and marks the maintenance task as failed after the preset number of retries is exceeded. The execution end writes the execution result of the maintenance task to the task queue, and the status of the maintenance task in the task queue flows between pending, processing, success, and failure.
[0059] Figure 3 This application provides an embodiment of an automatic database bloat recovery method, which includes an overall architecture diagram of the database kernel and external execution terminal. The architecture includes a read-only detection function located within the database kernel. Figure 3 (The example in the text uses detect_bloat()) and a unified task queue ( Figure 3 The diagram illustrates the `cleanup_queue` (as shown in the image) and the execution client located outside the database kernel (illustrated as `pg_bloat_worker.sh`). The read-only detection function performs only query operations during runtime and can be triggered by a scheduled task. After completing the bloat detection, the read-only detection function writes the objects to be maintained and the maintenance actions into a unified task queue as maintenance tasks. Figure 3 The read-only detection function points to the write task in the task queue; the execution end retrieves the maintenance task from the unified task queue using an independent connection, i.e., the retrieval operation from the task queue to the execution end in the diagram, corresponding to a row-level locking query statement; after retrieving the maintenance task, the execution end executes the first instruction and the second instruction in sequence. Figure 3 The text illustrates the first instruction to set lock_timeout and the second instruction to execute VACUUM / REINDEX, respectively, to actually reclaim space for objects to be maintained in the database kernel.
[0060] Figure 4 This is a schematic diagram of the structure of an automatic database bloat recovery system provided in an embodiment of this application, as shown below. Figure 4 As shown, the database bloat automatic recycling system 400 includes: The expansion detection module 401 performs expansion detection on objects in the database using a read-only detection function deployed within the database to determine the objects to be maintained and their corresponding maintenance actions. The read-only detection function only performs query operations during runtime. The maintenance task generation module 402 generates maintenance tasks based on the objects to be maintained and the maintenance actions, and adds these tasks to the database's task queue. The maintenance task retrieval module 403 retrieves maintenance tasks from the task queue using an execution terminal deployed outside the database. The recycling module 404 executes a first instruction and a second instruction sequentially on the same connection between the execution terminal and the database using an auto-commit method to perform maintenance actions on the objects to be maintained and reclaim the storage space occupied by these objects. The first instruction sets session-level lock timeout parameters, and the second instruction is the maintenance instruction corresponding to the maintenance action. The first and second instructions are executed as independent transactions, ensuring that the second instruction is not executed within an explicit transaction block.
[0061] 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 embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.
[0062] The systems and methods provided in this application are one-to-one correspondences. Therefore, the system also has similar beneficial technical effects as its corresponding method. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the system will not be repeated here.
[0063] 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.
[0064] 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 method for automatic database reclamation due to database bloat, characterized in that, The method includes: An expansion detection function deployed within the database is used to perform an expansion detection on objects in the database to determine the objects to be maintained and the corresponding maintenance actions; wherein, the read-only detection function only performs query operations during runtime; A maintenance task is generated based on the object to be maintained and the maintenance action, and the maintenance task is added to the task queue of the database; The maintenance task is retrieved from the task queue by an execution terminal deployed outside the database; Through the execution terminal, the first instruction and the second instruction are executed sequentially in an auto-commit manner on the same connection between the execution terminal and the database to perform the maintenance action on the object to be maintained and reclaim the storage space occupied by the object to be maintained; wherein, the first instruction is used to set the session-level lock timeout parameter, and the second instruction is the maintenance instruction corresponding to the maintenance action; the first instruction and the second instruction are executed as independent transactions, so that the second instruction is not executed within an explicit transaction block.
2. The method for automatic database reclamation according to claim 1, characterized in that, The step involves using a read-only detection function deployed within the database to perform bloat detection on objects in the database, determining the objects to be maintained and the corresponding maintenance actions, specifically including: When the object is a table, the statistical view of the database is read through the read-only detection function to obtain the first dead tuple ratio of the object, and the objects whose first dead tuple ratio is greater than or equal to the pre-screening threshold are identified as pre-screened objects. Physical sampling statistics are performed on the pre-screened objects to determine the proportion of the second dead group of the pre-screened objects; Based on the second dead tuple ratio, determine the object to be maintained and the corresponding maintenance action.
3. The method for automatic database reclamation according to claim 2, characterized in that, The step of reading the statistical view of the database, obtaining the proportion of the first dead tuples of the objects, and determining objects whose proportion of the first dead tuples is greater than or equal to the pre-screening threshold as pre-screening passed objects specifically includes: Objects whose mode name does not belong to the system mode, whose physical volume is greater than or equal to the preset minimum object volume threshold, and whose first dead tuple ratio is greater than or equal to the pre-screening threshold are identified as the pre-screened objects; wherein, the first dead tuple ratio is equal to the quotient obtained by dividing the number of dead tuples by the sum of the number of dead tuples and the number of live tuples.
4. The method for automatic database reclamation according to claim 2, characterized in that, The step of performing physical sampling statistics on the pre-screened objects to determine the proportion of the second dead tuples of the pre-screened objects specifically includes: Sampling statistics are performed on the storage pages of the pre-screened objects to determine the percentage of space occupied by dead tuples of the pre-screened objects. The percentage of space occupied by the dead tuples is determined as the second dead tuple ratio of the pre-screened objects, and the first dead tuple ratio of the pre-screened objects is updated to the second dead tuple ratio.
5. The method for automatic database reclamation according to claim 2, characterized in that, The step of determining the object to be maintained and the corresponding maintenance action based on the second dead tuple ratio specifically includes: If the proportion of the second dead tuples of the pre-screened objects is greater than or equal to the first preset threshold, the pre-screened objects are identified as objects to be maintained. Based on the proportion of the second dead tuples of the object to be maintained and the physical volume of the object to be maintained, perform the following operations: If the proportion of the second dead tuples of the object to be maintained is less than the second preset threshold, the maintenance action for the object to be maintained is determined to be online cleanup; If the proportion of the second dead tuples of the object to be maintained is greater than or equal to the second preset threshold, and the physical volume of the object to be maintained is less than or equal to the preset volume safety threshold, the maintenance action of the object to be maintained is determined to be offline rewrite cleanup. If the proportion of the second dead tuple of the object to be maintained is greater than or equal to the second preset threshold, and the physical volume of the object to be maintained is greater than the preset volume safety threshold, the maintenance action of the object to be maintained is determined to be manual intervention. The maintenance tasks corresponding to the online cleanup can be executed by the execution terminal at any time; the maintenance tasks corresponding to the offline rewrite cleanup can be executed by the execution terminal within a preset maintenance time window; the manual intervention instruction prohibits the corresponding maintenance tasks from being added to the task queue and generates alarm information.
6. The method for automatic database reclamation according to claim 1, characterized in that, The step of performing bloat detection on objects in the database using a read-only detection function deployed within the database to determine the objects to be maintained and the corresponding maintenance actions for those objects further includes: When the object is an index, the index type of the object is obtained through the read-only detection function, and the following operations are performed: When the index type is B-Tree, the physical volume of the object and the average density of the leaf pages are obtained through the read-only detection function. When the physical volume of the object is greater than or equal to the preset minimum object volume threshold and the average density of the leaf pages is less than the preset density threshold, the object is identified as an object to be maintained, and the maintenance action of the object to be maintained is determined to be online index rebuilding. When the index type is GIN, the number of pages to be processed for the object is obtained through the read-only detection function. When the number of pages to be processed is greater than or equal to the first page number threshold and less than the second page number threshold, the table to which the object belongs is identified as an object to be maintained, and the maintenance action for the object to be maintained is determined to be online cleanup. When the number of pages to be processed is greater than or equal to the second page number threshold, the object is identified as an object to be maintained, and the maintenance action for the object to be maintained is determined to be online index rebuilding. If the index type is GiST or Hash, the object will not be identified as an object to be maintained.
7. The method for automatic database reclamation according to claim 1, characterized in that, The step of generating a maintenance task based on the object to be maintained and the maintenance action, and adding the maintenance task to the task queue of the database, specifically includes: Check whether the object to be maintained and the maintenance action corresponding to it, which are in a pending state, already exist in the task queue; If the maintenance task does not exist in the task queue, the maintenance task is generated and added to the task queue.
8. The method for automatic database reclamation according to claim 1, characterized in that, The step of retrieving the maintenance task from the task queue through an execution terminal deployed outside the database specifically includes: The row-level locking query statement is used to retrieve maintenance tasks that are in a pending state from the task queue; wherein, the row-level locking query statement is used to add a row-level lock to the data rows that are hit by the query and skip the data rows that have been locked by other execution ends; The status of the maintenance task in the task queue is updated from pending to being processed.
9. The method for automatic database reclamation according to claim 1, characterized in that, Before performing the maintenance action on the object to be maintained, the method further includes: The execution terminal determines whether the current time is within the preset maintenance time window corresponding to the maintenance action; The execution terminal determines whether the number of active sessions in the database is greater than or equal to the active session number threshold. If so, the execution terminal pauses the acquisition of the maintenance task. When the execution terminal acquires the maintenance task from the task queue, the number of the maintenance tasks acquired is less than or equal to a preset single-round task number threshold. The method further includes: If the lock is not acquired within the time corresponding to the session-level lock timeout parameter during the execution of the second instruction, the execution terminal terminates the maintenance task. The execution terminal retryes a terminated or failed maintenance task a preset number of times, and marks the maintenance task as failed after the number of retries exceeds the preset number; The execution terminal writes the execution result of the maintenance task into the task queue.
10. A database expansion automatic reclamation system, characterized in that, The system includes: The expansion detection module is used to perform expansion detection on objects in the database through a read-only detection function deployed inside the database, to determine the objects to be maintained and the corresponding maintenance actions; wherein, the read-only detection function only performs query operations during runtime; The maintenance task generation module is used to generate maintenance tasks based on the object to be maintained and the maintenance action, and add the maintenance tasks to the task queue of the database; The maintenance task acquisition module is used to acquire the maintenance task from the task queue through an execution terminal deployed outside the database; The recycling module is used to execute a first instruction and a second instruction sequentially on the same connection between the execution terminal and the database in an auto-commit manner to perform the maintenance action on the object to be maintained and reclaim the storage space occupied by the object to be maintained; wherein, the first instruction is used to set the session-level lock timeout parameter, and the second instruction is the maintenance instruction corresponding to the maintenance action; the first instruction and the second instruction are executed as independent transactions, so that the second instruction is not executed within an explicit transaction block.
