Database growth statistics method and program product

Abstract

Embodiments of the present application provide a database expansion statistics method and program product, relating to the technical field of database storage management. In view of the problems of insufficient statistical accuracy, large input / output and calculation overhead, and inconsistent statistical caliber of different relationship objects in existing expansion diagnosis based on directory statistics, page sampling or full-scan, a database relationship expansion statistics scheme based on page-level statistical path is proposed. The scheme first classifies the relationship pages in combination with visibility mapping, infers the page occupancy using the free space information for pages in the full-visible state, performs page-by-page scanning for non-full-visible pages to identify tuple visibility and extract statistical data, and then performs aggregation calculation on the statistical results of various types of pages to obtain the full-table expansion statistics of the relationship. Using the scheme can improve the accuracy of the expansion state description, reduce the invalid scanning range, reduce resource consumption, improve the statistical efficiency, and enhance the consistency of the diagnosis results of multiple types of relationship objects.

Description

Technical Field

[0001] This application relates to the field of database storage management technology, and in particular to a database expansion statistics method and program product. Background Technology

[0002] In enterprise-level database management, the storage expansion of tables and indexes is usually diagnosed using system statistics, sampling analysis tools, or full scans.

[0003] However, some existing solutions rely on directory statistics or low-ratio sampling, which makes it difficult to accurately reflect the proportion of dead tuples, page free space, and the true expansion status. Other solutions use full scans, which are time-consuming and resource-intensive when dealing with large-scale tables and indexes.

[0004] Therefore, how to balance high statistical accuracy and low resource consumption during the database expansion statistics process has become a technical problem that needs to be solved. Summary of the Invention

[0005] This application provides a database bloat statistics method and program product to solve the aforementioned technical problems. This method addresses the need for diagnosing database relationship bloat. It differentiates and processes pages within the database relationship based on the differences in information available under different page states, and then uniformly generates statistical results for the entire table. This reduces unnecessary scanning overhead while improving the accuracy of bloat statistics.

[0006] In a first aspect, embodiments of this application provide a database bloat statistics method, including:

[0007] Retrieve the set of pages with database relationships, and divide the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on visibility mapping;

[0008] For the pages in the first page set, obtain the page free space according to the free space mapping and determine the tuple occupancy. For the pages in the second page set, perform a full page scan and determine the tuple visibility to obtain page statistics.

[0009] Based on page statistics, a preset statistical algorithm is used to determine the full table statistics of database relationships.

[0010] In one possible implementation, before the steps of obtaining page free space and determining tuple occupancy for pages in the first page set according to free space mapping, performing a full page scan and determining tuple visibility for pages in the second page set, and obtaining page statistics, the method further includes:

[0011] Parse the relation name or relation identifier of a database relation to obtain the relation type of the database relation;

[0012] Bind the corresponding page statistics function according to the relationship type. The page statistics function is used to perform expansion statistics on the pages in the page collection.

[0013] In one possible implementation, the corresponding page statistics function is bound according to the relationship type, specifically including at least one of the following:

[0014] When the relation type is a B-Tree index, bind the B-Tree page statistics function;

[0015] When the relation type is a hash index, bind the hash page statistics function;

[0016] When the relation type is GiST index, bind the GiST page statistics function;

[0017] When the relation type is GIN index, load the GIN index processing module and bind the GIN page statistics function;

[0018] When the relation type is BRIN index, load the BRIN index processing module and bind the BRIN page statistics function;

[0019] When the relation type is a Bloom index, load the Bloom index processing module and bind the BRIN page statistics function.

[0020] In one possible implementation, the step of performing a full page scan on the pages in the second page set and determining tuple visibility to obtain page statistics data specifically includes:

[0021] Scan the pages in the second page set sequentially;

[0022] When the current tuple is encountered, determine the current block in which the current tuple belongs;

[0023] Locate the uncounted blocks preceding the current block based on the block pointer, and accumulate the page free space of the uncounted blocks;

[0024] The visibility of the current tuple is determined based on the oldest global transaction identifier;

[0025] Write the visibility classification result of the current tuple and the page free space corresponding to the current block into the page statistics, and update the block pointer to the next block after the current block.

[0026] In one possible implementation, prior to the step of determining the full table statistics of database relationships using a preset statistical algorithm based on page statistics, the method further includes:

[0027] When the database relationship is a B-Tree index, read the index metadata page and retrieve version data and tree depth data from it;

[0028] Traverse each index page of the B-Tree index;

[0029] When the current index page is a leaf page, count the number of live tuples, the number of dead tuples, and the free space of the page, and determine the leaf page fill rate based on the free space of the page and the maximum available space; or, when the current index page is an internal page, record the internal page count; or, when the current index page is an empty page, record the empty page count; or, when the current index page is a deleted page, record the deleted page count.

[0030] Compare the next page pointer of the current leaf page with the current block number to determine the chained fragment data and obtain the fragmentation rate data;

[0031] Write version data, tree depth data, live tuple count, dead tuple count, leaf page fill rate, internal page count, empty page count, deleted page count, and fragmentation rate data into the page statistics.

[0032] In one possible implementation, prior to the step of obtaining the set of pages representing database relationships, the method further includes:

[0033] Apply a shared access lock to the database relationship;

[0034] Open each page in the database relationship's page set using a shared access lock.

[0035] In one possible implementation, the step of obtaining a set of pages representing database relationships and dividing the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on a visibility mapping specifically includes:

[0036] Read the visibility-mapped page into the first buffer;

[0037] The next visibility map page adjacent to the visibility map page is preloaded into the second buffer;

[0038] The full visibility flag of the current data page is determined based on the visibility mapping pages in the first buffer;

[0039] When processing subsequent data pages, the page reading order corresponding to the first buffer and the second buffer is swapped, and the next visibility mapping page is preloaded.

[0040] In one possible implementation, the preset statistical algorithm includes a weighted regression statistical model; the step of determining the full table statistics of database relationships based on page statistics using the preset statistical algorithm specifically includes:

[0041] Extract the free space data and tuple distribution data corresponding to the scanned pages;

[0042] The extrapolation weights of each scanned page are determined based on free space data and tuple distribution data.

[0043] Based on extrapolation weights, a weighted regression statistical model is used to perform weighted regression processing on the page statistics data to obtain the full table statistics.

[0044] In one possible implementation, after the step of determining the full table statistics of database relationships using a preset statistical algorithm based on page statistics, the method further includes:

[0045] Determine the actual number of pages scanned and the total number of pages;

[0046] The percentage of actual scanned pages is calculated based on the actual number of scanned pages and the total number of pages.

[0047] Output the percentage of actual scanned pages.

[0048] Secondly, embodiments of this application provide a database bloat statistics device, the device comprising:

[0049] The partitioning module is used to obtain the set of pages with database relationships and partition the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on the visibility mapping.

[0050] The processing module is used to obtain the free space of the pages in the first page set according to the free space mapping and determine the tuple occupancy, perform a full page scan on the pages in the second page set and determine the tuple visibility, and obtain page statistics data.

[0051] The determination module is used to determine the full table statistics of database relationships based on page statistics data using a preset statistical algorithm.

[0052] In one possible implementation, the processing module is further configured to:

[0053] Parse the relation name or relation identifier of a database relation to obtain the relation type of the database relation;

[0054] Bind the corresponding page statistics function according to the relationship type. The page statistics function is used to perform expansion statistics on the pages in the page collection.

[0055] In one possible implementation, the processing module is specifically used for:

[0056] When the relation type is a B-Tree index, bind the B-Tree page statistics function;

[0057] When the relation type is a hash index, bind the hash page statistics function;

[0058] When the relation type is GiST index, bind the GiST page statistics function;

[0059] When the relation type is GIN index, load the GIN index processing module and bind the GIN page statistics function;

[0060] When the relation type is BRIN index, load the BRIN index processing module and bind the BRIN page statistics function;

[0061] When the relation type is a Bloom index, load the Bloom index processing module and bind the BRIN page statistics function.

[0062] In one possible implementation, the processing module is specifically used for:

[0063] Scan the pages in the second page set sequentially;

[0064] When the current tuple is encountered, determine the current block in which the current tuple belongs;

[0065] Locate the uncounted blocks preceding the current block based on the block pointer, and accumulate the page free space of the uncounted blocks;

[0066] The visibility of the current tuple is determined based on the oldest global transaction identifier;

[0067] Write the visibility classification result of the current tuple and the page free space corresponding to the current block into the page statistics, and update the block pointer to the next block after the current block.

[0068] In one possible implementation, the determining module is further configured to:

[0069] When the database relationship is a B-Tree index, read the index metadata page and retrieve version data and tree depth data from it;

[0070] Traverse each index page of the B-Tree index;

[0071] When the current index page is a leaf page, count the number of live tuples, the number of dead tuples, and the free space of the page, and determine the leaf page fill rate based on the free space of the page and the maximum available space; or, when the current index page is an internal page, record the internal page count; or, when the current index page is an empty page, record the empty page count; or, when the current index page is a deleted page, record the deleted page count.

[0072] Compare the next page pointer of the current leaf page with the current block number to determine the chained fragment data and obtain the fragmentation rate data;

[0073] Write version data, tree depth data, live tuple count, dead tuple count, leaf page fill rate, internal page count, empty page count, deleted page count, and fragmentation rate data into the page statistics.

[0074] In one possible implementation, the partitioning module is also used for:

[0075] Apply a shared access lock to the database relationship;

[0076] Open each page in the database relationship's page set using a shared access lock.

[0077] In one possible implementation, the partitioning module is specifically used for:

[0078] Read the visibility-mapped page into the first buffer;

[0079] The next visibility map page adjacent to the visibility map page is preloaded into the second buffer;

[0080] The full visibility flag of the current data page is determined based on the visibility mapping pages in the first buffer;

[0081] When processing subsequent data pages, the page reading order corresponding to the first buffer and the second buffer is swapped, and the next visibility mapping page is preloaded.

[0082] In one possible implementation, the preset statistical algorithm includes a weighted regression statistical model; the determining module is specifically used for:

[0083] Extract the free space data and tuple distribution data corresponding to the scanned pages;

[0084] The extrapolation weights of each scanned page are determined based on free space data and tuple distribution data.

[0085] Based on extrapolation weights, a weighted regression statistical model is used to perform weighted regression processing on the page statistics data to obtain the full table statistics.

[0086] In one possible implementation, the determining module is further configured to:

[0087] Determine the actual number of pages scanned and the total number of pages;

[0088] The percentage of actual scanned pages is calculated based on the actual number of scanned pages and the total number of pages.

[0089] Output the percentage of actual scanned pages.

[0090] Thirdly, embodiments of this application provide an electronic device, including: a memory and a processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory, causing the processor to perform the first aspect and / or various possible implementations of the first aspect as described above.

[0091] Fourthly, embodiments of this application provide a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the first aspect and / or various possible implementations of the first aspect.

[0092] Fifthly, embodiments of this application provide a computer program product, including a computer program that, when executed by a processor, implements the first aspect and / or various possible implementations of the first aspect.

[0093] The database bloat statistics method and program product provided in this application obtains a set of pages in a database relationship and divides the set into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on visibility mapping. The method obtains the free space of pages in the first set according to the free space mapping and determines the tuple occupancy, and performs a full page scan on pages in the second set to determine tuple visibility and obtain page statistics data. This allows for the use of statistical methods adapted to the information characteristics of different page states. While avoiding a full scan of all pages, it retains the ability to perform detailed statistics on key pages. Furthermore, by using a preset statistical algorithm based on the page statistics data to determine the full table statistics of the database relationship, it can balance improved accuracy of database bloat statistics with lower resource overhead. Attached Figure Description

[0094] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0095] Figure 1 A flowchart illustrating the database bloat statistics method provided in this application embodiment. Figure 1 ;

[0096] Figure 2 A flowchart illustrating the database bloat statistics method provided in this application embodiment. Figure 2 ;

[0097] Figure 3 A schematic diagram of the statistical system architecture provided in the embodiments of this application;

[0098] Figure 4 This is a schematic diagram of the database expansion statistics device provided in the embodiments of this application;

[0099] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application.

[0100] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0101] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application.

[0102] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of the relevant data all comply with the relevant laws, regulations, and standards of the relevant countries and regions, have taken necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation access points for users to choose to authorize or refuse.

[0103] Furthermore, the technical solution involved in this application, which involves big data analysis of user information (including but not limited to personal biometrics, identity data, consumption data, asset data, electronic terminal operation data, etc.) and the use of artificial intelligence technology for automated decision-making, and makes decisions that have a significant impact on personal rights based on the results of automated decision-making, provides users with corresponding operation entry points for users to choose to agree to or reject the results of automated decision-making; if the user chooses to reject, the process will proceed to the expert decision-making process.

[0104] In this application, the terms "exemplary" or "for example" are used to indicate that something is an example, illustration, or description. Any embodiment or design described as "exemplary" or "for example" in this application should not be construed as being more preferred or advantageous than other embodiments or designs. Specifically, the use of terms such as "exemplary" or "for example" is intended to present the relevant concepts in a specific manner.

[0105] In the embodiments of this application, the use of terms such as "first" and "second" is to distinguish between identical or similar items that have essentially the same function and effect. For example, "first electronic device" and "second electronic device" are merely used to distinguish different electronic devices and do not limit their order of execution. Those skilled in the art will understand that the terms "first" and "second" do not limit the quantity or execution order, and that "first" and "second" do not necessarily imply that they are different.

[0106] In this application embodiment, "at least one" refers to one or more, and "more than one" refers to two or more. "And / or" describes the relationship between associated objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, or B existing alone, where A and B can be singular or plural. The character " / " generally indicates that the preceding and following associated objects have an "or" relationship.

[0107] The following is an explanation of some terms used in the embodiments of this application:

[0108] Heap table: A default table storage structure in a database.

[0109] Balanced-Tree (B-Tree) index: A commonly used index type that supports equality, range, sorting, and prefix queries.

[0110] Hash index: A commonly used index type suitable for highly selective equality queries.

[0111] Generalized Search Tree (GiST) index: an index type within a scalable indexing framework.

[0112] Generalized Inverted Index (GIN): A commonly used index type that indexes the elements within a composite field.

[0113] Block Range Index (BRIN): A type of index that stores only a statistical summary of each block.

[0114] Bloom Filter Index (Bloom): An index type that is an extended index based on Bloom filters.

[0115] Garbage collection (VACUUM): The operation of reclaiming dead tuples to free up space.

[0116] Rebuild Index (REINDEX): An operation to eliminate index bloat.

[0117] Visibility Map (VM): A marker structure that records whether all tuples on a page are visible.

[0118] Free Space Map (FSM): A structure that records a summary of the free space on a page.

[0119] Transaction snapshot: A transaction view used for determining tuple visibility.

[0120] Global oldest transaction identifier (OldestXmin): The benchmark used for determining the visibility of approximate patterns.

[0121] Multi-Version Concurrency Control (MVCC): A mechanism for databases to generate multiple versions of tuples concurrently.

[0122] For example, during long-term database operation, dead tuples generated by update / delete operations cannot be immediately reclaimed, and page fragmentation can lead to storage space bloat, resulting in wasted storage space, decreased scan performance, and reduced cache hit rate.

[0123] Database storage management technology is widely used in enterprise-level database management systems to diagnose problems such as space bloat, increased free pages, and fragmentation accumulation in tables and indexes during long-term operation. These applications are typically deployed within the database kernel or accompanying operation and maintenance components, targeting high-concurrency scenarios such as finance, telecommunications, IoT, and cloud databases, providing database administrators with data on storage status analysis.

[0124] Existing database bloat diagnosis typically employs system directory statistics, page sampling analysis tools, or full page scans. Directory statistics primarily rely on estimates of live and dead tuples accumulated during historical maintenance. Sampling analysis infers the overall state by extracting a subset of pages, while a full scan checks the tuple status and free space of each page to determine the bloat of tables or indexes.

[0125] For example, the statistics maintained by various database system catalogs are all sampled estimates and cannot reflect the true number of dead tuples and page-level free space. One database's tool can scan accurately, but its coverage is limited; another database's tool only provides bucket distribution; and yet another database's default mode only scans about 2% of the pages.

[0126] One database's tool only supports Heap tables and B-Tree indexes, not other index types such as Hash / GiST / GIN. Another database's index statistics rely on system catalog estimates and do not perform page-by-page scans. Database administrators (DBAs) cannot obtain comprehensive bloat diagnostics through a unified interface when dealing with tables with multiple index types.

[0127] Existing solutions are polarized between accurate statistics and performance overhead: they either rely on low-precision estimations (catalog values, sampling) or perform full scans, lacking intermediate means to obtain sufficient accuracy at an acceptable cost, such as approximate statistical patterns that use visibility information to intelligently skip parts of the page.

[0128] When all tuples on a page are visible to all current transactions, there are no dead tuples on that page, and theoretically, its free space can be determined without scanning tuple by tuple. However, the tools of a certain database do not utilize visibility mapping for page-level intelligent skipping, and instead use the same scanning strategy for all pages, resulting in a large amount of unnecessary input / output (I / O) overhead.

[0129] Existing tools mostly return absolute values ​​(number of bytes, number of tuples), requiring DBAs to manually calculate the expansion rate to determine whether compression is necessary. Furthermore, some databases' sampling modes do not output the actual percentage of sampled pages, leaving DBAs unable to ascertain the confidence level of the statistical results.

[0130] The existing methods described above have significant limitations. Relying on directory values ​​or low-percentage sampling can easily lead to statistical results that deviate from the actual storage state, making it difficult to accurately reflect the proportion of dead tuples, page free space, and fragmentation levels. Using a full scan results in high I / O and computational overhead for large-scale relational objects, and excessively long statistical cycles can impact online services. Furthermore, different tools are often required for different types of indexes, leading to inconsistent results and making it difficult for operations personnel to conduct timely horizontal comparisons and maintenance decisions.

[0131] When high-frequency update and deletion operations are running continuously, the above-mentioned defects will cause the expansion diagnosis to lag, and the timing of compression, cleanup or index reconstruction will be difficult to accurately grasp, which will lead to storage waste, decreased cache utilization and query performance fluctuations.

[0132] To address the aforementioned technical issues, this application provides a database bloat statistics method. This method analyzes database relationships using page-level statistical paths and introduces visibility mapping as a basis for page partitioning. Pages marked with full visibility maintain a stable visibility state, allowing free space information to directly reflect page occupancy; pages without full visibility retain complete scan paths, used to identify the true visibility distribution of tuples within the page. By differentiating statistical methods according to page state, invalid scan ranges can be compressed, maintaining an effective characterization of the bloat state.

[0133] Based on this approach, the function call layer, relation type distribution layer, statistics engine layer, and result output layer within the database form a unified processing chain. Under the same diagnostic framework, it receives relation objects, divides page sets, aggregates page statistics, and calculates full table statistics, thus achieving a balance between statistical accuracy, resource overhead, and consistency in diagnosis of multiple object types.

[0134] The technical solutions of this application will be described in detail below with reference to specific embodiments. The specific embodiments described below can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of this application will be described below with reference to the accompanying drawings.

[0135] Figure 1 A flowchart illustrating the database bloat statistics method provided in this application embodiment. Figure 1 The execution subject of this method can be an electronic device with corresponding data storage and computing capabilities, such as a computer, server, or server cluster. Figure 1 As shown, the method includes steps S101, S102 and S103.

[0136] S101: Obtain the set of pages in the database relationship and divide the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on the visibility mapping.

[0137] In this application, the database relationship, as the object of statistics, can be the storage relationship corresponding to a heap table or materialized view, or a relationship that stores data in a page-based organization. The executing entity can be a statistical function in the database kernel, a background maintenance process, or an operation and maintenance component that interacts with the database kernel. The page set can be the set of all data pages corresponding to the database relationship within the current physical storage range. It can be obtained sequentially through the block number range of the relationship file, or it can be formed by traversing page by page according to the currently valid page number after the relationship is opened.

[0138] Visibility mapping can be used to record whether a page has a full visibility flag. This flag indicates whether all tuples on the page are in a stable visible state under the current transaction visibility rules, thus providing a basis for page classification. The first set of pages can be a set of pages with full visibility flags, carrying pages that can be accessed through the fast statistical path without tuple-by-tuple checks. The second set of pages can be a set of pages without full visibility flags, carrying pages that require a full page check.

[0139] For example, in a specific implementation, the executing entity obtains the database relationships to be analyzed, reads the physical storage information of the relationships, determines the total number of pages in the database relationship, and constructs a page set based on the page numbers. In one possible embodiment, the page set is directly composed of a continuous sequence of page numbers from block number 0 to the last block number. In another possible implementation, if the database relationship has extended segment files, the executing entity expands the block numbers according to the segment file order and merges them to form a unified page set.

[0140] Subsequently, the executing entity accesses the visibility mapping structure corresponding to the database relationship and completes the classification based on the read results. For pages whose status bits indicate that they have a full visibility flag, the corresponding page number is written into the first page set; for pages without a full visibility flag, the corresponding page number is written into the second page set. The page set can be organized as a memory array, bitmap index, linked list structure, or segmented interval table, as long as it can support iterative processing by set in subsequent steps.

[0141] For example, to ensure that page partitioning is consistent with the current storage state during this process, the executing entity maintains the same statistical snapshot or the same management transaction context when reading the total number of pages and the visibility mapping, so that page numbers, relationship lengths, and visibility states have a consistent time base. When some page entries do not exist in the visibility mapping, these pages can be directly assigned to the second page set.

[0142] Based on the above analysis, this step, by dividing the set of pages in the database relationship into two categories according to visibility mapping, allows stable and unstable visible pages to use different statistical paths subsequently. This effectively reduces the range of pages that need to be checked tuple by tuple, laying the foundation for controlling I / O scan volume while maintaining statistical accuracy. It should be understood that the above example is merely illustrative and not limiting.

[0143] S102: Obtain the free space of the pages in the first page set according to the free space mapping and determine the tuple occupancy. Perform a full page scan on the pages in the second page set and determine the tuple visibility to obtain page statistics.

[0144] In this application, free space mapping is used to store page-level remaining available space information. Page free space represents the amount of space in a page not occupied by tuples. Tuple occupancy represents the actual amount of space occupied by tuples and their related storage entities within a page. Full page scan is a complete process of checking each page, slot, and tuple within a page. Tuple visibility is used to distinguish between visible and invisible tuples under the current statistical semantics. Page statistics serve as intermediate results, including at least the page number, page free space, tuple occupancy, and visibility statistics obtained for the pages to be scanned.

[0145] For each page in the first page set, the executing entity accesses the corresponding entry in the free space mapping according to the page number and directly reads the page's free space value. The page's free space can be represented in bytes or as an encoded discrete space level value. When reading an encoded value, the executing entity first converts it to the number of free bytes according to the decoding rules of the database's free space mapping.

[0146] Subsequently, the tuple occupancy is determined by considering the fixed page size of the database relationship. The fixed page size can be taken as the available space after subtracting the page header, row pointer area, and necessary management overhead from the database block size, or it can be taken as the uniform page size of the database and then deducted uniformly from the fixed management overhead in the statistical algorithm.

[0147] For example, in one possible implementation, if the total page space is denoted as P and the free page space is denoted as F, then the tuple occupancy is denoted as U. The execution entity calculates the page space already occupied according to U=PF and records this value in the page statistics.

[0148] In another possible implementation, the fixed overhead H of the page header can be deducted separately, and the effective tuple occupancy can be determined according to U=PHF, making the subsequent aggregation results closer to the actual tuple carrying space. Since the pages in the first page set already have full visibility markers, this step no longer performs visibility determination on each tuple, but directly establishes page utilization statistics based on the page's free space.

[0149] For each page in the second page set, the execution entity reads the page content sequentially and performs a full page scan. The full page scan can access each tuple item in the order of the row pointers within the page, or it can continuously read the record area in the order of physical offsets and then restore the tuple boundaries using the slot directory. During the scan, the page header is first parsed to obtain basic information such as the page's free start position, the number of row pointers, and deleted flags. Then, the tuple status information is checked one by one, and the visibility of the tuple is determined based on the current statistical semantics.

[0150] For tuples that meet the visibility criteria, the number of visible tuples and the space occupied by visible tuples are accumulated; for tuples that do not meet the visibility criteria, the number of invisible tuples, the space occupied by invisible tuples, or the space occupied by dead tuples are accumulated. In addition, the executing entity can also record unallocated space, reclaimed but unmerged space, fragmented space, or reusable space within the page, and form page statistics records corresponding to that page.

[0151] For example, page statistics may include page number, page category identifier, page free space, tuple usage, number of visible tuples, number of invisible tuples, number of bytes occupied by visible tuples, and number of bytes occupied by invisible tuples.

[0152] In terms of data organization, the page statistics generated by the first and second page sets are written to a unified statistics buffer, or written to the fast statistics result area and the scan statistics result area respectively before being merged. After processing each page, the executing entity generates a page-level record with the set identifier to which the page belongs, so as to distinguish the statistical source during subsequent aggregation.

[0153] Based on the above analysis, the first page set directly obtains the page's free space and calculates the tuple occupancy through free space mapping. The second page set obtains more detailed visibility distribution and space occupancy information through a full page scan. The two statistical paths share the same page statistical data structure, achieving a balance between statistical accuracy and processing overhead within the same path. For stable visible pages, omitting tuple-by-tuple checks reduces invalid scans. For unstable visible pages, retaining complete visibility determination ensures the accuracy of dead tuple and free space identification. It should be understood that the above example is merely illustrative and not limiting.

[0154] S103: Determine the full table statistics of database relationships based on page statistics using a preset statistical algorithm.

[0155] In this application, a pre-defined statistical algorithm is used to aggregate page-level results into relation-level results. Full-table statistics reflect the overall expansion status of the database relations and may include the total number of pages, total page space, total free space, total tuple occupancy, estimated expansion rate, and other statistical results related to the database relational storage status. Page statistics, as input to the pre-defined statistical algorithm, are validated according to a uniform field format before entering the aggregation process to ensure that each page record contains at least a page number, page free space, and tuple occupancy. For records from the second page set, visibility determination results are also included.

[0156] For example, in practical implementation, the executing entity sequentially reads page statistics data from the statistical buffer and sums up the page records corresponding to the first and second page sets. If all pages of the database relation have been included in the page set and the statistics have been completed, the preset statistical algorithm directly sums up the records of each page to obtain the total table statistics. For example, the total free space of the relation is obtained by summing up the free space of all pages. The total space occupied by the relation is obtained by summing up the tuple occupancy of all pages. The space occupied by visible tuples and invisible tuples accumulated in the second page set are summed up to form the visible and invisible occupancy at the relation level, and then the corresponding proportions are calculated by combining them with the total page space.

[0157] For example, if the total number of relation pages is denoted as N, the single-page statistical space is denoted as P, the total free space of all pages is denoted as ΣF, and the total tuple occupancy of all pages is denoted as ΣU, then the free space ratio can be expressed as ΣF / (N×P), and the occupancy ratio can be expressed as ΣU / (N×P). When the total space occupied by invisible tuples in the second page set is denoted as ΣD, the inflation correlation ratio can be calculated as ΣD / (N×P) or ΣD / ΣU. The former represents the proportion of invisible occupied parts in the entire relation space, and the latter represents the proportion of invisible parts in the occupied space. If the algorithm introduces fixed overhead for the page header, then N×H is uniformly deducted from the denominator of the total space, or it is directly summarized after being deducted at the page level.

[0158] In one possible implementation, since the first page set obtains direct page-level space information through free space mapping, and the second page set obtains a combination of page-level space information and tuple visibility information, the executing entity can contribute the first page set to the total free space and total occupied space statistics. Furthermore, the second page set is additionally contributed to the dead tuple occupied space, live tuple occupied space, and visibility distribution statistics. If the statistical framework employs segmented reading or abnormal skipping mechanisms for some pages, extrapolation calculations can be performed based on the ratio of the counted pages to the total number of pages. For example, the corresponding value for pages not directly counted can be estimated using the average space occupancy or average dead tuple ratio of the counted pages, and then incorporated into the final result.

[0159] In this application, the page set is read as a complete page set within the current storage scope; therefore, the total table statistics are determined by directly summarizing the statistical data of all pages. Finally, the executing entity encapsulates the total table statistics into relational-level statistical results and returns them to the function call layer or result output layer. The output can be an in-memory structure, system view records, text-based diagnostic reports, or a result set for use by operations and maintenance components.

[0160] The database expansion statistics method provided in this application uses visibility mapping as the basis for page traffic distribution, incorporating stable visible pages into the free space direct read path and unstable visible pages into the full page scan path. It aggregates page statistics generated by both types of pages under a unified statistical algorithm, effectively establishing a complete processing chain from page-level statistics to relation-level expansion results.

[0161] Based on this, the first page set reduces unnecessary tuple-by-tuple scanning, lowering computational overhead; the second page set retains dead tuple identification and true visibility determination, thus the full table statistics simultaneously cover page free space, tuple occupancy, and tuple visibility distribution, enabling the expansion state of database relations to be output under a unified standard. This achieves a balance between high statistical accuracy and low resource consumption during database expansion statistics. It should be understood that the above example is merely illustrative and not limiting.

[0162] In one possible implementation, before obtaining the page free space and determining the tuple occupancy of the pages in the first page set according to the free space mapping, performing a full page scan and determining the tuple visibility of the pages in the second page set, and obtaining the page statistics data, the method further includes: parsing the relation name or relation identifier of the database relation to obtain the relation type of the database relation; binding the corresponding page statistics function according to the relation type, and the page statistics function is used to perform expansion statistics on the pages in the page set.

[0163] For example, upon receiving the relation name or relation identifier of a database relation, it is parsed and the relation metadata is queried to determine the relation type to which the database relation belongs. This relation type can correspond to different index objects or relation objects. Based on this, a mapping relationship is established between the relation type and the page statistics function, and the binding is completed after the relation is opened or a diagnostic flag is received. When the bound page statistics function processes the page collection subsequently, it can select either the free space mapping statistics path or the full page scan statistics path based on the page status, and aggregate the statistics results into page statistics data.

[0164] When a database relation belongs to a certain type, the page statistics function can directly call the entry function matching that type to obtain the parameters required for the expansion statistics of each page in the page collection, and write the page free space, tuple occupancy, and tuple visibility into the corresponding statistical structure. For different relation types, the bound function entry and statistical scope maintain a consistent interface, while the internal implementation can be adapted according to the relation type, thus enabling page statistics in the page collection to be completed within a unified framework.

[0165] In this embodiment, by parsing the relation type and binding the corresponding page statistics function before page statistics are performed, subsequent expansion statistics can be made consistent with the database relation type. Based on this, page free space mapping, full page scanning, and statistical data aggregation can all be handled by matching functions, resulting in page statistics data that better reflect the storage state of the corresponding relation objects.

[0166] In one possible implementation, the corresponding page statistics function is bound according to the relation type, specifically including at least one of the following: when the relation type is a B-Tree index, a B-Tree page statistics function is bound; when the relation type is a Hash index, a Hash page statistics function is bound; when the relation type is a GiST index, a GiST page statistics function is bound; when the relation type is a GIN index, a GIN index processing module is loaded and a GIN page statistics function is bound; when the relation type is a BRIN index, a BRIN index processing module is loaded and a BRIN page statistics function is bound; when the relation type is a Bloom index, a Bloom index processing module is loaded and a BRIN page statistics function is bound.

[0167] For example, after parsing the relation type of the database relationship, the database system first retrieves the corresponding page statistics function from the function mapping table based on the relation type, and writes the function into the statistics context of the current relation object.

[0168] When the relation type is a B-Tree index, Hash index, or GiST index, function binding is completed directly, enabling subsequent page statistics calls to perform page occupancy, page free space, or tuple visibility statistics according to the corresponding index structure. For GIN, BRIN, or Bloom indexes, the database system can first load the corresponding processing module, and then the processing module registers GIN page statistics functions, BRIN page statistics functions, or Bloom page statistics functions with the statistics context to supplement the statistical logic required by the index-specific data organization, page directory organization, and tuple location methods. The binding relationship can be implemented through function pointers, callback handles, or runtime distribution tables. The bound function outputs page statistics data that matches the index type when facing a set of pages.

[0169] For example, once a database relationship is identified as a specific index type, the database system establishes a correspondence between that type and a preset page statistics entry point. This ensures that subsequent statistical calls to the page collection no longer rely on a unified, general processing method, but instead switch to a dedicated processing path adapted to the index structure. For GIN, BRIN, or Bloom indexes that require additional capabilities, the processing module is loaded first, and then the function is bound. This allows the statistics engine to obtain dedicated parsing and statistical support for the corresponding index and output page statistics results that can be used for full table statistical estimation.

[0170] In this embodiment, different index types can correspond to a consistent and clear statistical entry point, maintaining a unified statistical caliber and enhancing the adaptability between page statistical logic and index structure. This makes subsequent expansion statistics results more stable and facilitates the analysis of multiple types of relational objects within the same diagnostic framework.

[0171] Figure 2 A flowchart illustrating the database bloat statistics method provided in this application embodiment. Figure 2 ,like Figure 2 As shown, the steps for performing a full page scan and determining tuple visibility to obtain page statistics for the pages in the second page set include: S201, sequentially scanning the pages in the second page set; S202, when the current tuple is scanned, determining the current block in which the current tuple is located; S203, locating the uncounted blocks before the current block according to the block pointer, and accumulating the page free space of the uncounted blocks; S204, determining the visibility of the current tuple based on the global oldest transaction identifier; S205, writing the visibility classification result of the current tuple and the page free space corresponding to the current block into the page statistics, and updating the block pointer to the next block after the current block.

[0172] For example, the current tuple represents the tuple being processed during the sequential scan. The current block represents the data block containing this tuple. The block pointer is used to mark the boundary position of the accumulated free space on the page. Uncounted blocks represent blocks that have not yet been included in the statistics. The global oldest transaction ID (OldestXmin ID) is used to determine the visibility of tuples.

[0173] For example, in the actual implementation, the second set of pages is first scanned sequentially according to the page arrangement order in the database relation. When a tuple is accessed, the page to which the tuple belongs and its block location are parsed to determine the current block. Subsequently, the statistical boundary indicated by the block pointer is read to locate the range of blocks that have not yet been counted before the current block, and the free space of these uncounted blocks is accumulated into the current statistical result.

[0174] For the current tuple, its visibility is determined by combining its transaction information with the identifier of the oldest transaction globally. If the transaction corresponding to the tuple is within the current visibility range, it is recorded as a visible tuple; otherwise, it is recorded as an invisible tuple, and a corresponding visibility classification result is formed. After the determination is completed, the classification result and the page free space corresponding to the current block are written into the page statistics, and the block pointer is updated to the next block after the current block so that subsequent scans can continue to advance in the sequential direction.

[0175] This processing method simultaneously performs tuple visibility classification and page free space aggregation during the scanning process. Block pointers control the boundaries of the statistical range, enabling page statistics to accumulate continuously by block and remain consistent with sequential scanning. Since unstatistical blocks before the current block are only accumulated upon their first arrival, subsequent scans will not repeatedly calculate the page free space of the same block. Therefore, statistical results corresponding to the page order can be obtained, providing stable input for subsequent full table statistics calculations.

[0176] In this embodiment, the full page scan of the second page set can achieve block-level aggregation of page free space while maintaining the accuracy of tuple visibility determination. In this way, page statistics can accurately reflect the distribution of visible tuples and the occupancy of free space, thereby providing consistent and directly aggregated basic data for the expansion statistics of database relationships.

[0177] In one possible implementation, before the step of determining the full table statistics of database relationships using a preset statistical algorithm based on page statistics, the following is also included:

[0178] When the database relationship is a B-Tree index, read the index metadata page and obtain version data and tree depth data from it; traverse each index page of the B-Tree index; when the current index page is a leaf page, count the live tuple count, dead tuple count, and page free space, and determine the leaf page fill rate based on the page free space and the maximum available space; or, when the current index page is an internal page, record the internal page count; or, when the current index page is an empty page, record the empty page count; or, when the current index page is a deleted page, record the deleted page count; compare the next page pointer of the current leaf page with the current block number to determine the chained fragmentation data and obtain the fragmentation rate data; write the version data, tree depth data, live tuple count, dead tuple count, leaf page fill rate, internal page count, empty page count, deleted page count, and fragmentation rate data into the page statistics data.

[0179] For example, index metadata pages can be used to store structural metadata of a B-Tree index. Version data can be used to identify the index version. Tree depth data can be used to characterize the depth of the index hierarchy. An index page is the basic traversable page unit in a B-Tree index; leaf pages carry the actual index entries, internal pages are used to organize the index hierarchy, and empty pages and deleted pages represent unused pages and invalid pages, respectively.

[0180] Live tuple counts characterize the number of valid index entries in a leaf page. Dead tuple counts characterize the number of invalid index entries in a leaf page. Maximum available space serves as a benchmark for leaf page occupancy. Leaf page fill rate reflects leaf page space utilization. The next page pointer indicates the successor page of the current leaf page in the linked list. The current block number identifies the physical block location of the current page. Chained fragmentation data and fragmentation rate data reflect the degree of link breakage in B-Tree index pages.

[0181] For example, in a practical implementation, after the database kernel or statistics module receives the database relationship to be analyzed, it first determines whether the relationship type is a B-Tree index and loads the corresponding index statistics logic accordingly. When reading the index metadata page, the database system can parse the version field and tree depth field from the page header or fixed metadata area and write them as index structure information into the statistics cache.

[0182] Subsequently, the index pages are traversed in page number order. For leaf pages, visible and invisible tuples are identified from the page record area, and their counts are counted. Simultaneously, the page free space field is read, and the leaf page fill rate is calculated based on the maximum available space defined by the page format. For internal pages, empty pages, and deleted pages, the database system accumulates counts according to page type. When comparing the next page pointer with the current block number, if they are inconsistent, it is recorded as chained fragmentation, converted into fragmentation rate data according to the statistical range, and finally, all statistical values ​​are uniformly written into the page statistics data for subsequent statistical algorithms to use.

[0183] In this embodiment, the processing method directly aggregates the structure, occupancy, and fragmentation information of the B-Tree index at the page level, enabling subsequent full-table statistics to simultaneously reflect the index level, page utilization, and physical fragmentation level. Since the statistical results are output according to uniform page statistics, these indicators can be directly reused when using preset statistical algorithms, thereby improving the consistency and accuracy of B-Tree index bloat diagnosis.

[0184] In one possible implementation, before the step of obtaining the set of pages of the database relationship, the method further includes: applying a shared access lock to the database relationship; and opening each page in the set of pages of the database relationship based on the shared access lock.

[0185] For example, after receiving the database relationship identifier, the statistics module first requests a shared access lock for that relationship from the storage engine. The lock request can be registered as shared mode through the kernel lock manager, and the relationship object identifier, lock-holding session, and lock state are recorded in the lock table. After the lock request is successful, the storage engine locates the corresponding set of pages based on the relationship's meta pages, segment pages, or page table information, and establishes a buffer page mapping page by page under the protection of the shared access lock, reads the page header information, and puts the page into a statistically accessible state.

[0186] By adopting a shared access lock, the page opening process remains stable within the same session, providing a unified reading foundation for subsequent page set partitioning based on visibility mapping. In practical applications, this lock type can also be replaced by other concurrency control modes provided by the database kernel, and this application does not limit this.

[0187] Once the page collection is fully accessed, the statistics engine can continue performing page-level analysis under lock protection, passing the read page metadata, page free space information, and visibility-related flags to subsequent processing modules. Because the database relationships are always under shared access lock constraints during the page collection reading process, the content of each page will not become inconsistent due to concurrent modifications, and the statistical results can maintain a correspondence with the current relationship state.

[0188] In this embodiment, the page content of the database relationships within the statistics window is consistent, and the opening and subsequent reading processes of the page set remain stable. This allows the expansion statistics based on page state to more closely approximate the actual storage state of the relationships, and makes the statistical process repeatable in a concurrent environment.

[0189] In one possible implementation, the steps of obtaining the set of pages representing database relationships and dividing the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on visibility mappings specifically include: reading visibility-mapped pages into a first buffer; preloading the next visibility-mapped page adjacent to the visibility-mapped page into a second buffer; determining the full visibility marker of the current data page based on the visibility-mapped pages in the first buffer; and when processing subsequent data pages, swapping the page reading order corresponding to the first buffer and the second buffer, and continuing to preload the next visibility-mapped page.

[0190] For example, a visibility mapping page can be used to carry visibility mapping information and provide a basis for determining the full visibility flag of the current data page. A first buffer can be used to temporarily store the current visibility mapping page to support the determination of whether the current data page has a full visibility flag. A second buffer can be used to temporarily store the next visibility mapping page adjacent to the current visibility mapping page to facilitate the continuous processing of subsequent data pages. The current data page can represent the data page currently undergoing full visibility flag determination.

[0191] For example, in a concrete implementation, after receiving a relational object, the database kernel can first locate the corresponding visibility mapping page based on the page index and read that page into the first buffer. Following the sequential addresses of the visibility mapping pages, the next adjacent visibility mapping page is pre-loaded into the second buffer. When the current data page arrives, the kernel directly reads the visibility mapping bits or corresponding flag fields from the first buffer. Furthermore, combining this with the data page numbers covered by the current page, it determines whether the current data page has a full visibility flag and assigns it to either the first page set or the second page set accordingly.

[0192] In this embodiment, as the processing object continues to advance, the reading order of the first buffer and the second buffer is swapped, making the content in the original second buffer the current judgment source, and the new next visibility mapping page continues to be preloaded, thereby maintaining a continuous page judgment process. This implementation can reduce the waiting time of visibility mapping pages without changing the page partitioning result, and maintain a stable sequential access characteristic in the page set partitioning process.

[0193] In one possible implementation, the preset statistical algorithm includes a weighted regression statistical model; the steps of determining the full table statistics of database relationships based on page statistics using the preset statistical algorithm specifically include: extracting free space data and tuple distribution data corresponding to the scanned pages; determining the extrapolation weight of each scanned page based on the free space data and tuple distribution data; and performing weighted regression processing on the page statistics using the weighted regression statistical model based on the extrapolation weights to obtain the full table statistics.

[0194] For example, a scanned page can be a page already included in the statistics, and its corresponding free space data is used to characterize the amount of space within the page that has not yet been occupied by tuples. Tuple distribution data can be used to characterize the distribution density, clustering degree, and occupied position of tuples within the page. Extrapolation weights are used to represent the contribution of each scanned page to the statistical extrapolation of unscanned pages, and the weights can vary with the proportion of free space on the page, the dispersion of tuple distribution, and the completeness of page statistics.

[0195] For example, in a concrete implementation, after the database system reads the page statistics, it separates and extracts the free space field and tuple distribution field for each scanned page, forming a feature vector that can be used for regression modeling. The free space data can be calculated from the available space offset recorded in the page header, the length of the free list, or the number of free bytes. The tuple distribution data can be jointly expressed by the tuple start offset within the page, the tuple spacing distribution, the number of live tuples, and the number of dead tuples.

[0196] The database system can calculate extrapolation weights according to a preset mapping relationship, giving higher weights to pages with more stable changes in free space and whose tuple distribution better represents the overall state of the page, while giving lower weights to pages that are abnormally discrete or have incomplete information. After obtaining the extrapolation weights, the database system can input page statistics data into a weighted regression statistical model to fit the relationship between variables related to free space, variables related to tuple distribution, and the target statistic, and then adjust the error of each sample according to the extrapolation weights.

[0197] For example, a weighted regression statistical model can be solved using either least squares regression or weighted regularized regression to output a full table statistic that matches the overall state of the current relation. This full table statistic can then be used to characterize the total free space, total tuple occupancy, or inflation diagnosis results of the relation.

[0198] In this embodiment, by jointly modeling page-level free space data and tuple distribution data, and controlling the influence of each scanned page in the regression using extrapolation weights, local page statistical results can be stably mapped to relation-level statistical results. The resulting full-table statistics show high consistency with the actual page distribution and can reasonably extrapolate to unscanned pages based solely on scanned pages, thereby improving the representativeness and computational stability of the statistical results.

[0199] In one possible implementation, after determining the full table statistics of database relationships based on page statistics using a preset statistical algorithm, the method further includes: determining the actual number of pages scanned and the total number of pages; calculating the percentage of pages actually scanned based on the actual number of pages scanned and the total number of pages; and outputting the percentage of pages actually scanned.

[0200] For example, the actual number of pages scanned can represent the number of pages actually included in the statistics, used to measure the coverage of the page set in this statistical analysis. The total number of pages can represent the total number of pages contained in the database relation, serving as the baseline denominator when calculating the percentage.

[0201] In this implementation, after the database relation completes the full table statistics calculation, the statistics module continues to read the page counts involved in this statistics and combines them with the total number of pages recorded in the relation metadata to obtain the page coverage base. The statistics module records the number of pages actually processed as the actual scanned pages and the total number of pages corresponding to the relation as the total number of pages, and calculates the ratio between the two to obtain the percentage of actual scanned pages. This percentage can be directly written into the result structure by the statistics engine, or it can be returned to the calling end along with the full table statistics, so that the calling end can simultaneously know the coverage of this statistics when receiving the expansion statistics results.

[0202] For example, in a practical implementation, the actual number of pages scanned can be obtained by summing the number of pages in the page statistics that have completed page free space resolution, tuple visibility determination, or page feature recording. Furthermore, the total number of pages can be obtained by parsing the page directory information, metadata page information, or relation size information of the relation. After completing weighted regression processing or other preset statistical algorithms, the statistics module divides the actual number of scanned pages by the total number of pages, retaining a preset precision decimal place for the result, forming the percentage of actual scanned pages. This percentage can be stored as a result field along with the full table statistics in the diagnostic record, or returned to the upper-level operation and maintenance components via an interface.

[0203] In this embodiment, after the percentage is output, the user can directly know the range of pages covered by the current page statistics and determine the applicability of the current diagnostic result by combining the full table statistics. Since the actual scanned page percentage is output synchronously with the full table statistics, the result includes both an estimate of the degree of expansion and a measure of the degree of coverage, thus providing the diagnostic output with more complete contextual information, which facilitates subsequent maintenance modules to make decisions on cleaning, compression, or reconstruction.

[0204] The database expansion statistics method provided in this application will be further illustrated below through an embodiment.

[0205] In one possible implementation, a shared access lock is first applied to the database relationship. Under this shared access lock, each page in the page set is opened, and the relationship name or relationship identifier is resolved to obtain the relationship type of the database relationship. Based on the relationship type, the corresponding page statistics function is bound: when the relationship type is a B-Tree index, the B-Tree page statistics function is bound; when the relationship type is a Hash index, the Hash page statistics function is bound; when the relationship type is a GiST index, the GiST page statistics function is bound; when the relationship type is a GIN index, the GIN index processing module is loaded and the GIN page statistics function is bound; when the relationship type is a BRIN index, the BRIN index processing module is loaded and the BRIN page statistics function is bound.

[0206] Subsequently, the set of pages representing database relationships is retrieved, and visibility-mapped pages are read into the first buffer. The next visibility-mapped page adjacent to the current page is preloaded into the second buffer. Based on the visibility-mapped pages in the first buffer, it is determined whether the current data page has a full visibility flag. The page set is divided into a first set of pages with full visibility flags and a second set of pages without full visibility flags. When processing subsequent data pages, the page reading order corresponding to the first and second buffers is swapped, and the preloading of the next visibility-mapped page continues.

[0207] After page partitioning, the free space of pages in the first page set is obtained according to the free space mapping, and the tuple occupancy is determined by combining the page capacity. When performing a full page scan on pages in the second page set, the pages in the second page set are scanned in page order. When the current tuple is scanned, the current block containing the current tuple is determined. The uncounted blocks before the current block are located according to the block pointer, and the page free space of the uncounted blocks is added to the page statistics.

[0208] Next, the visibility of the current tuple can be determined based on the oldest global transaction identifier. The visibility classification result of the current tuple and the page free space corresponding to the current block are then written into the page statistics. Furthermore, the block pointer can be updated to the next block after the current block. For different relation types, the bound page statistics function performs corresponding inflation statistics processing on the pages in the page set during the same statistical process. Page free space, tuple occupancy, visibility classification results, and other data are then aggregated to form the page statistics.

[0209] When the database relationship is a B-Tree index, the index metadata pages are read before performing full table statistics calculations. Version data and tree depth data are obtained from these pages, and each index page is traversed. During the traversal, if the current index page is a leaf page, the count of live tuples, the count of dead tuples, and the free space on the page are counted. The leaf page fill rate is determined based on the free space on the page and the maximum available space.

[0210] If the current indexed page is an internal page, record the internal page count. If the current indexed page is an empty page, record the empty page count. If the current indexed page is a deleted page, record the deleted page count. For leaf pages, compare the next page pointer of the current leaf page with the current block number to determine the chained fragmentation data and obtain the fragmentation rate data. Version data, tree depth data, live tuple count, dead tuple count, leaf page fill rate, internal page count, empty page count, deleted page count, and fragmentation rate data are all written into the page statistics data.

[0211] After obtaining the page statistics, the free space data and tuple distribution data corresponding to the scanned pages are extracted. The extrapolation weights of each scanned page are determined based on the free space data and tuple distribution data. A weighted regression statistical model is then used to perform weighted regression processing on the page statistics based on the extrapolation weights to obtain the full table statistics of the database relations. After completing the full table statistics calculation, the actual number of scanned pages and the total number of pages are determined. The percentage of pages actually scanned is calculated based on the actual number of scanned pages and the total number of pages, and this percentage is output.

[0212] The database expansion statistics method provided in this application will be further illustrated below with some exemplary examples.

[0213] Figure 3This is a schematic diagram of the statistical system architecture provided in an embodiment of this application. This statistical system can be used to execute any of the database bloat statistics methods provided in the embodiments of this application described above. For example... Figure 3 As shown, the statistical system includes: a Structured Query Language (SQL) function call layer, a relation type distribution layer, an exact statistical engine, an approximate statistical engine, and a statistical result output layer.

[0214] The SQL function call layer can be used to call functions corresponding to precise table statistics, approximate table statistics, detailed index statistics, and physical page number queries. The relation type distribution layer can route tasks based on relation type and storage format. For example, table-type relation types can be distributed to the heap table statistics engine; index-type relation types can be distributed to different page functions bound to indexes such as B-Tree / Hash / Gist. The precise statistics engine can be used for statistical execution in full scan mode. The approximate statistics engine can be used for statistical execution in intelligent skip mode. The statistical result output layer can output the statistical results corresponding to the full table statistics.

[0215] The database bloat statistics method provided in this application covers a unified and accurate bloat statistics framework for tables and multiple types of indexes. Based on this framework, an intelligent approximate statistical mode based on visibility mapping is designed to achieve three core capabilities. First, based on the accurate mode, a full scan of all data blocks in the second page set is performed, classifying each tuple into live or dead tuples, accurately obtaining byte-level free space values ​​for each block, and outputting multi-dimensional statistical results. Second, based on the approximate mode, visibility mapping is used to quickly skip pages without dead tuples in the first page set, performing a full scan only on pages without visibility markers, and then obtaining a full table estimate through linear extrapolation. Third, a unified framework for multiple index types is implemented. Through a function pointer dispatch mechanism, the corresponding page statistics functions for different types of indexes are called, and all are uniformly converged into the same statistical output structure.

[0216] During the relation type distribution phase, the relation type is determined based on the input relation name (table name, index name) or relation identifier (OID), and the data is routed to the corresponding statistics engine (precise or approximate statistics engine). The specific workflow includes: SQL function call, parsing the relation name or directly using the relation identifier, opening the relation with a shared access lock, permission checks and validity verification, and routing based on the relation type. Specifically, applying a shared access lock during relation opening and verification prevents the table structure from being deleted / truncated during the statistics period, excluding temporary tables from other sessions and inactive indexes.

[0217] When routing based on relation type, the relation types include regular tables (heap table statistics engine), materialized views, the Oversized-StorageSystemTable (TOAST), and sequences; various indexes include B-Tree indexes (index traversal framework + B-Tree page function); Hash indexes (index traversal framework + Hash page function); and GiST indexes (index traversal framework + GiST page function).

[0218] For example, a precise statistics engine can obtain accurate bloat data for tables / indexes through a full scan. The table statistics process includes: full scan → tuple visibility determination → live / dead tuple classification. Idle data is simultaneously calculated during tuple visibility determination, completed in a single traversal. Percentage calculations are performed during live / dead tuple classification to determine the proportion of live / dead / idle data. The index statistics process includes: metadata reading → page type classification → fragmentation detection → fill rate calculation. During page type classification, statistics are performed on internal pages / leaf pages / empty pages / deleted pages for B-Tree indexes; on buckets / overflow / bitmap / unused pages for Hash indexes; and on leaf pages / internal pages for GiST indexes.

[0219] For example, when performing precise statistics on a Heap table, you can first perform initialization, a full scan (to see all tuples) + visibility checker, record the total number of relation blocks, and set the next block pointer for the free space to be counted to 0. The first step is to traverse each tuple, including: locking the current data page; determining visibility; if visible, incrementing the count and size based on live tuples; if invisible, incrementing the count and size based on dead tuples; unlocking the data page; and recording the block number where the tuple is located.

[0220] The second step is to synchronize free space statistics, including: reading all blocks between the next block to be counted for free space and the block containing the current tuple → accurately obtaining free bytes → accumulating them into the free space → moving the block pointer forward. The third step is to process the free space of the remaining uncovered blocks. The fourth step is to calculate the total size and percentages. For example, total size = number of blocks × block size; live tuple percentage = live tuple size / total size × 100%; dead tuple percentage = dead tuple size / total size × 100%; free space percentage = free space / total size × 100%.

[0221] When scanning with the precise statistics engine, free space statistics are performed synchronously within the same traversal of the tuple scan. A block pointer is maintained, pointing to the next block whose free space needs to be counted. Whenever a tuple is encountered, the free space of all uncounted blocks preceding the block containing that tuple is accumulated. The remaining empty blocks are processed only after the scan is complete. Tuple classification and free space statistics are combined into a single traversal.

[0222] For example, when performing precise statistics on a B-Tree index, the metadata page is read, and then the version number and tree depth are obtained. The index then traverses each page sequentially, starting from the first data page after the metadata page. For each page, it is determined whether it is a new page, a page marked as ignored, a leaf page, or an internal node page (internal page).

[0223] If it's a new page, the free space equals the full page size; if it's a page marked as ignored, the empty page count is incremented by 1, and the free space equals the full page size; if it's a leaf page, the leaf page count is incremented by 1, tuples within the page are traversed, classified as live / dead and their sizes are counted, the precise free space and maximum available space are accumulated, and fragmentation is detected (next page pointer < current block number → fragment count + 1); if it's an internal node page, the internal page count is incremented by 1. Furthermore, concurrency scaling protection is implemented, for example, re-acquiring the total number of relation blocks in each loop to prevent concurrent writes from causing missed counts or out-of-bounds errors. Output calculations: Leaf page fill rate = (1 - free space / maximum available space) × 100%; Leaf page fragmentation rate = (number of fragments / number of leaf pages) × 100%.

[0224] For example, when performing precise statistics on hash indexes, the following classifications are made based on page type: For bucket pages → count live / dead entries and precise free space; For overflow pages → count live / dead entries and precise free space; For bitmap pages → only count; For unused pages → include free space in the fixed available space. Fill rate = 1 - Free space / Total available space × 100%.

[0225] For example, when GiST indexes perform precise statistics, they categorize pages by type: for leaf pages, they count live / dead tuples and free space; for internal pages, they only count.

[0226] For example, a smart skip mode scan is performed using an approximate statistics engine. This leverages the smart skip mechanism of visibility mapping and free space mapping to significantly reduce I / O while providing sufficiently accurate bloat estimates. For instance, the execution includes: determining whether each data block has an AllVisible visibility mapping flag. If so, free space is obtained through free mapping, tuple occupancy is estimated, and the full scan is skipped. If not, a full scan is performed, a global oldest transaction identifier is determined, and live / dead tuples are classified. Finally, linear extrapolation is performed to estimate the full table statistics and output the confidence score for the percentage of pages scanned.

[0227] For example, obtain the current global oldest transaction ID (OldestXmin), and use OldestXmin to classify visibility as live / dead. For each data block, determine if there is a visibility mapping marked as All-Visible. If yes (fast path, occupying most pages), directly obtain free space from the free space mapping (no need to read data pages); tuple occupancy = block size - free space; skip the full scan. If no (full path, only processing part of the pages), read the data pages; accurately obtain free space; record the number of pages scanned; for each normal tuple. Then, linearly extrapolate to estimate the full table statistics, extrapolating the number of scanned live tuples to the full table according to the ratio of (total blocks / scanned blocks). Output confidence index: actual scanned page percentage = number of scanned blocks / total blocks × 100%.

[0228] When performing a scan using the approximate statistics engine, dual-path branching, visibility mapping double buffering, determination of the oldest transaction ID, and confidence feedback can be achieved.

[0229] Specifically, dual-path branching can be understood as follows: All-Visible pages take the fast path of free space mapping (only system table queries), while non-All-Visible pages take the full scan path. Visibility mapping double buffering can be understood as using double buffering to read visibility-mapped pages, avoiding triggering a random I / O for each data block. Oldest transaction ID determination can be understood as using the oldest transaction ID at the VACUUM level for visibility determination, rather than transaction snapshots, reducing determination overhead. Confidence feedback can be understood as outputting the percentage of actual scanned pages, allowing the caller to understand the confidence level of the statistical results—the lower the percentage, the more the statistics rely on extrapolation.

[0230] In the statistical result assembly and output stage, for example, the statistical result output layer mainly includes the following: 9 columns for the exact mode (total size, percentage of live and dead tuples, percentage of free space, etc.); 10 columns for the approximate mode (adding a confidence field for the percentage of actual scanned pages); 10 columns specifically for B-Tree (version number, tree depth, total index size, root page block number, number of internal pages, number of leaf pages, number of empty pages, number of deleted pages, leaf page fill rate, leaf page fragmentation rate); and 8 columns specifically for Hash (version number, number of bucket pages, number of overflow pages, number of bitmap pages, number of unused pages, number of live entries, number of dead entries, percentage of free space). Table 1 shows an example of the exact mode output, and Table 2 shows an example of the approximate mode output.

[0231] Table 1

[0232]

[0233] Table 2

[0234]

[0235] For example, when querying a database to collect statistics on expansion information using the method described in this application, it can be implemented in the following way:

[0236] For example, a specific implementation of precise statistics for Heap tables is as follows:

[0237] SELECT*FROMkbstattuple('schema.table_name');

[0238] --Output: Total size 100MB, live tuples 42.97%, dead tuples 20.59%, free space 36.48%;

[0239] --Expansion rate = 20.59% + 36.48% = 57.07%, it is recommended to execute VACUUMFULL.

[0240] For example, a specific implementation of approximate statistics for Heap tables (recommended for large tables) is as follows:

[0241] SELECT*FROMkbstattuple_approx('schema.large_table');

[0242] --Output: 35.2% of the pages were actually scanned (the rest were skipped via visibility mapping), and the statistics were faster than in the exact mode.

[0243] For example, the specific implementation of B-Tree index statistics is as follows:

[0244] SELECT*FROMkbstatindex('schema.idx_name');

[0245] --Output: Index depth 3 levels, fill rate 78.5%, fragmentation rate 3.2%, 12 empty pages and 8 deleted pages;

[0246] --REINDEX is recommended.

[0247] For example, a specific implementation of hash index statistics is as follows:

[0248] SELECT*FROMkbstathashindex('schema.hash_idx');

[0249] --Output: Bucket page count / Overflow page count / Bitmap page count / Unused page count / Live / Dead entries / Free percentage

[0250] For example, a specific implementation of batch expansion diagnosis is as follows:

[0251] SELECT table_name, (Dead tuple percentage + Idle tuple percentage) AS inflation_rate; FROM system_statistics_table; ORDER BY inflation_rate DESC;

[0252] --Quickly locate the table with the most severe expansion.

[0253] The method in this application implements a unified multi-relation type expansion statistics distribution framework. A distribution mechanism from relation type to statistics engine is designed. It covers Heap tables, materialized views, TOAST tables, sequences, and B-Tree / Hash / GiST indexes under the same SQL interface. For index types, page-level statistical logic is decoupled through function pointers; each access method's page function independently handles its respective page type classification, ultimately converging into a unified statistical output structure.

[0254] This application proposes a smart page skipping approximate statistics strategy based on visibility mapping and free space mapping. The embodiments of this application propose a dual-path statistics strategy: for All-Visible pages, free space is directly obtained through free space mapping and the tuple occupancy is calculated, skipping the full scan. For non-All-Visible pages, a full scan is performed and the oldest transaction ID is used for tuple classification. Finally, the full table statistics are estimated through linear extrapolation, and the actual scanned page ratio is output as a confidence index. The visibility determination logic of VACUUM is introduced into the inflation statistics scenario, significantly reducing I / O overhead while maintaining sufficient accuracy.

[0255] This algorithm employs a synchronous accumulation mechanism for Heap table free space and tuple statistics. In precise statistics mode, free space statistics are completed synchronously within the same traversal of tuple scanning. The algorithm maintains a block pointer pointing to the next block of free space to be counted. Whenever a tuple is encountered, the free space of all uncounted blocks preceding the block containing that tuple is accumulated. By merging tuple classification and free space statistics into a single traversal, the overhead of two scans is avoided.

[0256] This is based on multi-dimensional quantification of B-Tree index fragmentation rate and fill rate. The statistics of B-Tree index are not limited to the count of live / dead tuples, but also include: leaf page fill rate to reflect space utilization efficiency; chained fragmentation detection by comparing the next page pointer with the current block number to reflect the degree of physical storage fragmentation; and four-category page statistics (internal pages / leaf pages / empty pages / deleted pages) to comprehensively describe the health of the index's internal structure.

[0257] In any of the embodiments described above, the visibility determination mechanism is not limited to the current visibility determiner, and other visibility determination methods (such as MVCC transaction snapshots, timestamp snapshots, etc.) may also be used. The approximate statistical extrapolation algorithm is not limited to linear extrapolation; more complex statistical models such as weighted regression and exponential decay may be used. The extension of visibility mapping skipping can be combined with all-frozen bits to further skip pages that do not require any visibility checks.

[0258] Furthermore, the statistical output format is not limited to SQL composite type returns; it can be output as JavaScript Object Notation (JSON) / Extensible Markup Language (XML) format, written to a statistical table, or generated as a visual report. Concurrency control granularity is not limited to shared access locks; finer-grained row-level locks or optimistic concurrency control can be used. Free space statistics are not limited to precise block-by-block retrieval; they can be combined with free space mapping caching to accelerate statistics.

[0259] Based on the method provided in this application, the degree of expansion can be accurately quantified. Upgrading from directory estimation to byte-level precise statistics, DBAs can accurately determine the expansion rate, avoiding over-compression or omissions caused by estimation errors. Furthermore, it can reduce the overhead of large table statistics. The approximate mode utilizes a visibility mapping skipping mechanism, requiring only a system table query to obtain free space information for All-Visible pages, significantly reducing I / O. In addition, unified diagnosis of multiple index types is implemented. B-Tree / Hash / GiST indexes are statistically analyzed within the same framework, providing a comprehensive overview of the expansion of all indexes at once.

[0260] Based on the method provided in this application, fine-grained index health analysis is possible. The fragmentation rate and fill rate of the B-Tree index quantify its internal health status, providing data support for REINDEX decisions. Furthermore, concurrent and safe statistical operations are possible. The anti-concurrency expansion mechanism ensures that even if concurrent writes cause table expansion during the statistical period, no statistics will be missed or out-of-bounds access will occur. In addition, the results are more intuitive, with built-in percentage calculations, allowing DBAs to determine the degree of bloat without manual calculation.

[0261] Figure 4 This is a schematic diagram of the database expansion statistics device provided in the embodiments of this application, as shown below. Figure 4 As shown in the figure, this application provides a database bloat statistics device, which includes:

[0262] The partitioning module 401 is used to obtain the set of pages in the database relationship and partition the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on the visibility mapping.

[0263] Processing module 402 is used to obtain the free space of the pages in the first page set according to the free space mapping and determine the tuple occupancy, perform a full page scan on the pages in the second page set and determine the tuple visibility, and obtain page statistics data.

[0264] The determination module 403 is used to determine the full table statistics of database relationships based on page statistics data using a preset statistical algorithm.

[0265] In one possible implementation, the processing module 402 is further configured to:

[0266] Parse the relation name or relation identifier of a database relation to obtain the relation type of the database relation;

[0267] Bind the corresponding page statistics function according to the relationship type. The page statistics function is used to perform expansion statistics on the pages in the page collection.

[0268] In one possible implementation, the processing module 402 is specifically used for:

[0269] When the relation type is a B-Tree index, bind the B-Tree page statistics function;

[0270] When the relation type is a hash index, bind the hash page statistics function;

[0271] When the relation type is GiST index, bind the GiST page statistics function;

[0272] When the relation type is GIN index, load the GIN index processing module and bind the GIN page statistics function;

[0273] When the relation type is BRIN index, load the BRIN index processing module and bind the BRIN page statistics function;

[0274] When the relation type is a Bloom index, load the Bloom index processing module and bind the BRIN page statistics function.

[0275] In one possible implementation, the processing module 402 is specifically used for:

[0276] Scan the pages in the second page set sequentially;

[0277] When the current tuple is encountered, determine the current block in which the current tuple belongs;

[0278] Locate the uncounted blocks preceding the current block based on the block pointer, and accumulate the page free space of the uncounted blocks;

[0279] The visibility of the current tuple is determined based on the oldest global transaction identifier;

[0280] Write the visibility classification result of the current tuple and the page free space corresponding to the current block into the page statistics, and update the block pointer to the next block after the current block.

[0281] In one possible implementation, the determining module 403 is further configured to:

[0282] When the database relationship is a B-Tree index, read the index metadata page and retrieve version data and tree depth data from it;

[0283] Traverse each index page of the B-Tree index;

[0284] When the current index page is a leaf page, count the number of live tuples, the number of dead tuples, and the free space of the page, and determine the leaf page fill rate based on the free space of the page and the maximum available space; or, when the current index page is an internal page, record the internal page count; or, when the current index page is an empty page, record the empty page count; or, when the current index page is a deleted page, record the deleted page count.

[0285] Compare the next page pointer of the current leaf page with the current block number to determine the chained fragment data and obtain the fragmentation rate data;

[0286] Write version data, tree depth data, live tuple count, dead tuple count, leaf page fill rate, internal page count, empty page count, deleted page count, and fragmentation rate data into the page statistics.

[0287] In one possible implementation, the partitioning module 401 is further configured to:

[0288] Apply a shared access lock to the database relationship;

[0289] Open each page in the database relationship's page set using a shared access lock.

[0290] In one possible implementation, the partitioning module 401 is specifically used for:

[0291] Read the visibility-mapped page into the first buffer;

[0292] The next visibility map page adjacent to the visibility map page is preloaded into the second buffer;

[0293] The full visibility flag of the current data page is determined based on the visibility mapping pages in the first buffer;

[0294] When processing subsequent data pages, the page reading order corresponding to the first buffer and the second buffer is swapped, and the next visibility mapping page is preloaded.

[0295] In one possible implementation, the preset statistical algorithm includes a weighted regression statistical model; the determining module 403 is specifically used for:

[0296] Extract the free space data and tuple distribution data corresponding to the scanned pages;

[0297] The extrapolation weights of each scanned page are determined based on free space data and tuple distribution data.

[0298] Based on extrapolation weights, a weighted regression statistical model is used to perform weighted regression processing on the page statistics data to obtain the full table statistics.

[0299] In one possible implementation, the determining module 403 is further configured to:

[0300] Determine the actual number of pages scanned and the total number of pages;

[0301] The percentage of actual scanned pages is calculated based on the actual number of scanned pages and the total number of pages.

[0302] Output the percentage of actual scanned pages.

[0303] The database expansion statistics device provided in this application can be used to execute the technical solution of the database expansion statistics method in any of the above embodiments of this application. Its implementation principle and technical effect are similar, and will not be described again here.

[0304] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application, such as... Figure 5 As shown, the electronic device of this embodiment may include: at least one processor 501; and a memory 502 communicatively connected to the at least one processor; wherein the memory 502 stores instructions executable by the at least one processor 501, the instructions being executed by the at least one processor 501 to cause the electronic device to perform the method as described in any of the above embodiments.

[0305] Optionally, the memory 502 can be either standalone or integrated with the processor 501.

[0306] The implementation principle and technical effects of the electronic device provided in this embodiment can be found in the foregoing embodiments, and will not be repeated here.

[0307] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the method of any of the foregoing embodiments.

[0308] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the method of any of the foregoing embodiments.

[0309] This computer program product can be deployed in the database kernel or operation and maintenance components, enabling the processor to complete relational object reception, page set partitioning, statistical path selection, page data aggregation, and result output when executing the program. The program uses visibility mapping to distinguish between fully visible pages and non-fully visible pages. For the former, it performs fast statistics by combining free space information, while for the latter, it retains page scanning and tuple visibility identification, thereby reducing invalid full scans.

[0310] For tables and different types of indexes, the program can also perform consistent analysis under a unified diagnostic framework, making statistical standards more consistent and results easier to compare horizontally. This reduces I / O and computational overhead while ensuring the accuracy of expansion characterization. Therefore, it can detect space expansion, increased free pages, and fragmentation issues more promptly, providing a more reliable basis for determining when to compress, clean up, or rebuild indexes.

[0311] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For instance, the division of modules is only a logical functional division, and there may be other division methods in actual implementation. For example, multiple modules may be combined or integrated into another system, or some features may be ignored or not executed.

[0312] The integrated modules described above, implemented as software functional modules, can be stored in a computer-readable storage medium. These software functional modules, stored in a storage medium, include several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute some steps of the methods of the various embodiments of this application.

[0313] It should be understood that the aforementioned processor can be a Central Processing Unit (CPU) or other general-purpose processors. The processor can also be a Digital Signal Processor (DSP) or an Application Specific Integrated Circuit (ASIC), etc. A general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in the application can be directly manifested as being executed by a hardware processor, or executed by a combination of hardware and software modules within the processor.

[0314] The memory may include random access memory (RAM) and may also include non-volatile memory (NVM), such as at least one disk storage device, and may also be various media that can store program code, such as USB flash drives, portable hard drives, read-only memory (ROM), disks or optical discs.

[0315] The aforementioned storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof. Examples of storage media include Static Random-Access Memory (SRAM) or Electrically Erasable Programmable Read Only Memory (EEPROM).

[0316] Storage media can be, for example, erasable programmable read-only memory (EPROM) or programmable read-only memory (PROM). Storage media can also be read-only memory (ROM), magnetic storage, flash memory, magnetic disks, or optical disks. Storage media can be any available medium accessible to general-purpose or special-purpose computers.

[0317] An exemplary storage medium is coupled to a processor, enabling the processor to read information from and write information to the storage medium. Alternatively, the storage medium can be an integral part of the processor. The processor and storage medium can reside within an application-specific integrated circuit (ASIC). Alternatively, the processor and storage medium can exist as discrete components within an electronic device or host device.

[0318] It should be noted that, in this document, 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. Unless otherwise specified, 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 that element.

[0319] The sequence numbers of the embodiments in this application are merely for description and do not represent the superiority or inferiority of the embodiments. Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method.

[0320] Based on this understanding, the technical solution of this application, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods of the various embodiments of this application.

[0321] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

[0322] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily essential to this application.

[0323] It should be further noted that although the steps in the flowchart are shown sequentially as indicated by the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise explicitly stated in this document, there is no strict order requirement for the execution of these steps, and they can be executed in other orders.

[0324] Furthermore, at least some steps in the flowchart may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily completed at the same time, but may be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but may be executed in turn or alternately with other steps or at least some of the sub-steps or stages of other steps.

[0325] In the above embodiments, the descriptions of each embodiment have their own emphasis. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments. The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as the combination of these technical features does not contradict each other, it should be considered within the scope of this specification.

[0326] Other embodiments of this application will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are considered illustrative only.

[0327] It should be understood that this application is not limited to the precise structure described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope.

Claims

1. A database bloat statistics method, characterized in that, include: Obtain the set of pages with database relationships, and divide the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on visibility mapping; For the pages in the first page set, obtain the page free space according to the free space mapping and determine the tuple occupancy. For the pages in the second page set, perform a full page scan and determine the tuple visibility to obtain page statistics. Based on the page statistics, a preset statistical algorithm is used to determine the full table statistics of the database relationships.

2. The database expansion statistics method according to claim 1, characterized in that, Before the steps of obtaining page free space and determining tuple occupancy for pages in the first page set according to free space mapping, performing a full page scan and determining tuple visibility for pages in the second page set, and obtaining page statistics, the method further includes: Parse the relation name or relation identifier of the database relation to obtain the relation type of the database relation; The corresponding page statistics function is bound according to the relationship type, and the page statistics function is used to perform expansion statistics on the pages in the page set.

3. The database expansion statistics method according to claim 2, characterized in that, The step of binding the corresponding page statistics function according to the relationship type specifically includes at least one of the following: When the relationship type is a B-Tree index, bind the B-Tree page statistics function; When the relationship type is a Hash index, bind the Hash page statistics function; When the relationship type is GiST index, bind the GiST page statistics function; When the relationship type is GIN index, load the GIN index processing module and bind the GIN page statistics function; When the relationship type is BRIN index, load the BRIN index processing module and bind the BRIN page statistics function; When the relationship type is a Bloom index, the Bloom index processing module is loaded and the BRIN page statistics function is bound.

4. The database expansion statistics method according to any one of claims 1-3, characterized in that, The step of performing a full page scan on the pages in the second page set and determining tuple visibility to obtain page statistics data specifically includes: Sequentially scan the pages in the second page set; When the current tuple is detected, determine the current block in which the current tuple is located; Locate the uncounted blocks preceding the current block based on the block pointer, and accumulate the page free space of the uncounted blocks; The visibility of the current tuple is determined based on the oldest global transaction identifier; The visibility classification result of the current tuple and the page free space corresponding to the current block are written into the page statistics, and the block pointer is updated to the next block after the current block.

5. The database expansion statistics method according to any one of claims 1-3, characterized in that, Before the step of determining the full table statistics of the database relationship based on the page statistics using a preset statistical algorithm, the method further includes: When the database relationship is a B-Tree index, read the index metadata page and obtain version data and tree depth data from it; Traverse each index page of the B-Tree index; When the current index page is a leaf page, count the number of live tuples, the number of dead tuples, and the free space of the page, and determine the leaf page fill rate based on the free space of the page and the maximum available space; or, when the current index page is an internal page, record the internal page count; or, when the current index page is an empty page, record the empty page count; or, when the current index page is a deleted page, record the deleted page count. Compare the next page pointer of the current leaf page with the current block number to determine the chained fragment data and obtain the fragmentation rate data; The version data, tree depth data, live tuple count, dead tuple count, leaf fill rate, internal page count, empty page count, deleted page count, and fragmentation rate data are written into the page statistics data.

6. The database expansion statistics method according to any one of claims 1-3, characterized in that, Before the step of retrieving the collection of pages related to the database relationships, the following steps are also included: Apply a shared access lock to the database relationship; Each page in the set of pages representing the database relationship is opened based on the shared access lock.

7. The database expansion statistics method according to any one of claims 1-3, characterized in that, The steps of obtaining a set of pages representing database relationships and dividing the set of pages into a first set of pages with full visibility markers and a second set of pages without full visibility markers based on visibility mapping specifically include: Read the visibility-mapped page into the first buffer; The next visibility mapping page adjacent to the visibility mapping page is preloaded into the second buffer; The full visibility marker of the current data page is determined based on the visibility mapping pages in the first buffer; When processing subsequent data pages, the page reading order corresponding to the first buffer and the second buffer is swapped, and the next visibility mapping page is preloaded.

8. The database expansion statistics method according to any one of claims 1-3, characterized in that, The preset statistical algorithm includes a weighted regression statistical model; the step of determining the full table statistics of the database relationship based on the page statistical data using the preset statistical algorithm specifically includes: Extract the free space data and tuple distribution data corresponding to the scanned pages; The extrapolation weights of each scanned page are determined based on the free space data and the tuple distribution data. Based on the extrapolation weights, the page statistics data are subjected to weighted regression processing through the weighted regression statistical model to obtain the full table statistics.

9. The database expansion statistics method according to any one of claims 1-3, characterized in that, After the step of determining the full table statistics of the database relationship based on the page statistics using a preset statistical algorithm, the method further includes: Determine the actual number of pages scanned and the total number of pages; The percentage of actual scanned pages is calculated based on the actual number of scanned pages and the total number of pages. Output the percentage of the actual number of pages scanned.

10. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method as described in any one of claims 1-9.

Images