Method and device for automatically sequencing table fields of PostgreSQL database and storage medium

Abstract

The invention discloses a method and a device for automatically sequencing table fields of a PostgreSQL database and a storage medium, the method provided by the embodiment of the invention is set in the PostgreSQL database, and the method can be used as a background task for regular operation or can be started when a specific event is triggered (such as after a large amount of data is inserted). When an algorithm is started, firstly, all fields of a target data table are traversed, and field attribute information is collected; and then, according to the collected information, performing field sorting by applying a sorting rule. Practice proves that required data can be positioned more quickly during query through reasonable field sorting, so that disk I / O (input / output) and memory consumption are reduced. In addition, the waste of storage space is reduced to a certain extent through good field sorting, so that the storage efficiency of the database is improved. Meanwhile, manual intervention is greatly reduced, the cost and complexity of database management are reduced, and the maintainability of the database is improved to a certain degree.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a method, device and storage medium for automatically sorting table fields of a PostgreSQL database. Background Art

[0002] When creating PostgreSQL, technicians do not pay attention to the order of fields in the table. However, the disk space occupied by different field types and the alignment on the disk will directly affect the space occupied by table data on the disk. Therefore, if the field type cannot be properly processed and set, it will inevitably occupy more storage space, thereby affecting the performance of the PostgreSQL database. Summary of the invention

[0003] The present invention provides a method, device and storage medium for automatically sorting table fields of a PostgreSQL database, so as to solve the problem that resources of the PostgreSQL database cannot be reasonably processed, thereby wasting resource space.

[0004] In a first aspect, the present invention provides a method for automatically sorting table fields of a PostgreSQL database, the method comprising: performing attribute analysis on each field of a data table in the PostgreSQL database to obtain field attributes of each field in the data table, the field attributes comprising data type, field length, data frequency, and cardinality;

[0005] Formulate a sorting rule according to the field attributes obtained by the analysis, and sort the fields in the data table according to the formulated sorting rule to reduce the space occupied by the data table in the PostgreSQL database;

[0006] The sorting rules include: arranging the fields in descending order according to the data type size, arranging the fields in descending order according to the data frequency, or arranging the fields in descending order according to the cardinality size.

[0007] Optionally, formulating a sorting rule according to the field attributes obtained by analysis includes: analyzing the occupied byte widths of different fields according to the field attributes obtained by analysis, and sorting the fields based on the minimum occupied byte width as a sorting principle.

[0008] Optionally, the method further comprises: saving the formulated sorting rules;

[0009] The load information and field attributes of the PostgreSQL database are monitored in real time, and when a preset trigger condition is met, the field sorting of the data table in the PostgreSQL database is automatically triggered to adapt to the dynamic changes of the PostgreSQL database.

[0010] Optionally, the trigger condition includes one or more of the following: the load rate of the PostgreSQL database is lower than a preset load rate threshold, the data frequency change of the PostgreSQL database is greater than a preset data frequency change threshold, the data cardinality of the PostgreSQL database is greater than a preset cardinality threshold, and the structure of the data table in the PostgreSQL database changes.

[0011] Optionally, the change in the structure of the data table includes one or more of the following: creating a table, deleting a field in a table, adding a field in a table, and modifying a field type in a table.

[0012] Optionally, before performing attribute analysis on each field of the data table in the PostgreSQL database, the method further comprises: performing analysis according to field types supported by the PostgreSQL database itself and user-defined data types built by the user to complete preliminary sorting of the fields.

[0013] Optionally, the sorting of fields in the data table includes:

[0014] According to the analyzed field attributes, data distribution and historical query records, and combined with the preliminary sorting of the field attributes, the fields are sorted secondary so that the sorting results are more user-friendly to the PostgreSQL database.

[0015] Optionally, before sorting the field attributes, the method further includes: calculating a comparison report of disk space usage before and after sorting based on the number of data items and field types in the data table, and determining whether to sort the field based on the comparison report.

[0016] In a second aspect, the present invention provides a device for automatically sorting table fields of a PostgreSQL database, the device comprising:

[0017] A data analysis module is used to perform attribute analysis on each field in a data table in a PostgreSQL database to obtain field attributes of each field in the data table, wherein the field attributes include data type, field length, data frequency, and cardinality;

[0018] A data sorting module is used to formulate sorting rules according to the field attributes obtained by analysis, and sort the fields in the data table according to the formulated sorting rules to reduce the space occupied by the data table in the PostgreSQL database; wherein the sorting rules include: arranging the fields in descending order according to the data type size, arranging the fields in descending order according to the data frequency, or arranging the fields in descending order according to the cardinality size.

[0019] In a third aspect, the present invention provides a computer-readable storage medium having a computer program stored thereon, wherein the program, when executed by a processor, implements any of the above-mentioned methods for generating a data element based on a digital spectrum.

[0020] The beneficial effects of the present invention are as follows:

[0021] By setting the method described in the embodiment of the present invention in a PostgreSQL database, the method can be run regularly as a background task, or started when a specific event is triggered (such as after a large amount of data is inserted). When the algorithm starts, all fields of the target data table are first traversed to collect field attribute information. Then, according to the collected information, the sorting rules are applied to sort the fields. Practice has proved that the present invention can locate the required data faster during query through reasonable field sorting, thereby reducing disk I / O and memory consumption. In addition, the present invention also reduces the waste of storage space to a certain extent through good field sorting, thereby improving the storage efficiency of the database. At the same time, the present invention greatly reduces manual intervention, reduces the cost and complexity of database management, and improves the maintainability of the database to a certain extent.

[0022] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Various other advantages and benefits will become apparent to those of ordinary skill in the art by reading the detailed description of the preferred embodiments below. The accompanying drawings are only for the purpose of illustrating the preferred embodiments and are not to be considered as limiting the present invention. Moreover, the same reference symbols are used throughout the accompanying drawings to represent the same components. In the accompanying drawings:

[0024] Figure 1 It is a flowchart of a method for automatically sorting table fields of a PostgreSQL database provided by an embodiment of the present invention;

[0025] Figure 2It is a schematic diagram of the structure of a data table of a PostgreSQL database provided by an embodiment of the present invention;

[0026] Figure 3 It is a flowchart diagram of another method for automatically sorting table fields of a PostgreSQL database provided by an embodiment of the present invention;

[0027] Figure 4 It is a flowchart of another method for automatically sorting table fields of a PostgreSQL database provided by an embodiment of the present invention;

[0028] Figure 5 The present invention is a schematic diagram of a structure of a device for automatically sorting table fields of a PostgreSQL database provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0029] The present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and do not limit the present invention.

[0030] The existing sorting of fields requires classifying and calculating the type of each field, and manually sorting the fields in the table creation stage, or manually sorting the fields in the later stage. However, this method will waste a lot of time and energy when there are many fields in the table, and it is impossible to determine the effect of the field sorting. In the case of unreasonable sorting of table fields, it will waste the storage space of the PostgreSQL database. In order to solve this problem, the embodiment of the present invention provides a method for automatically sorting table fields of the PostgreSQL database, see Figure 1 , the method comprising:

[0031] S101, performing attribute analysis on each field in a data table in a PostgreSQL database to obtain field attributes of each field in the data table, wherein the field attributes include data type, field length, data frequency, and cardinality;

[0032] Specifically, the typalign attribute of each field type in postgresql describes the alignment of different fields when storing data. According to this attribute, the field data is automatically pre-filled to ensure that the field data starts from the specified disk boundary.

[0033] In the embodiment of the present invention, analyzing field attributes is to perform attribute analysis on each field in the PostgreSQL data table, including data type, field length, data frequency (for example, the frequency of a field being queried or updated) and cardinality (the number of different values).

[0034] See also Figure 2, we can analyze that smallint is 2 bytes, alignment padding is 2 bytes, and integer is 4 bytes.

[0035] In the specific implementation, c=char alignment can be set, that is, no alignment is required, s=short alignment (2 bytes on most machines), i=int alignment (4 bytes on most machines), d=double alignment (8 bytes on most machines). Of course, this is only an example of the present invention, and those skilled in the art can set it according to actual needs.

[0036] In general, the embodiment of the present invention analyzes the fields of a data table in a PostgreSQL database and formulates sorting rules based on the fields.

[0037] S102, formulating a sorting rule according to the field attributes obtained by the analysis, and sorting the fields in the data table according to the formulated sorting rule to reduce the space occupied by the data table in the PostgreSQL database;

[0038] That is, the embodiment of the present invention analyzes the occupied byte widths of different fields according to the analyzed field attributes, and sorts the fields based on the minimum occupied byte width as the sorting principle.

[0039] Furthermore, the embodiment of the present invention can also save the formulated sorting rules, and then monitor the load information and field attributes of the PostgreSQL database in real time. When the preset trigger conditions are met, the field sorting of the data table in the PostgreSQL database is automatically triggered to adapt to the dynamic changes of the PostgreSQL database.

[0040] That is to say, the method of the embodiment of the present invention can automatically trigger sorting when the triggering conditions are met, so as to free up manpower to the greatest extent and improve the usage experience.

[0041] In simple terms, as the database is used, the present invention continuously monitors changes in field attributes. When certain conditions are met (such as significant changes in data frequency or cardinality), the algorithm is automatically triggered to reorder the fields to adapt to dynamic changes in the database.

[0042] In specific implementation, the sorting rules described in the embodiment of the present invention include: sorting fields according to data type size, sorting fields according to data frequency, or sorting fields according to cardinality size, etc. Specific technicians in this field can set it according to actual needs, and the present invention does not make specific limitations on this.

[0043] That is to say, the embodiment of the present invention executes the above-mentioned method of the embodiment of the present invention when operating the postgresql database and when using the postgresql database. For example, when creating a table or adding or deleting fields in a table or modifying the type of a table field, by identifying and sorting the table field types, the disk space occupied by the data table and the disk space occupied by the index can be reduced.

[0044] Specifically, the present invention formulates sorting rules based on the analyzed field attributes. For example, the data can be sorted in descending order according to the size of the data type, with the fields of larger data types at the front; the fields with high data frequency are prioritized at the front to improve query efficiency; and the fields with large cardinality are also given a higher sorting priority because they may involve more index requirements.

[0045] Finally, the fields in the data table are automatically sorted according to the determined sorting rules. It should be noted that when the present invention automatically sorts, the integrity and consistency of the data must be ensured.

[0046] In specific implementation, the sorting algorithm in the embodiment of the present invention can read the load information of the system and database, and support automatic sorting of fields of user-configured tables when the database is idle or has a low load. Users can set scheduled tasks and set the table range.

[0047] The following will be combined Figure 3 and Figure 4 The method described in the embodiment of the present invention is explained and illustrated in detail by a specific example:

[0048] See also Figure 3 When the table structure changes, the method for automatically sorting the table fields of the PostgreSQL database according to the embodiment of the present invention specifically includes:

[0049] T001 is the prerequisite for triggering the algorithm. When the structure of the data table changes, the trigger algorithm performs algorithm sorting on the fields of the data table, including:

[0050] First, get the fields and field types in the data table;

[0051] Secondly, the byte widths occupied by different fields are analyzed to obtain the optimal sorting;

[0052] Then, sort the fields in the table according to the above optimal sorting;

[0053] Finally, save the table.

[0054] Before the above-mentioned automatic sorting, the embodiment of the present invention can also perform analysis based on the field types supported by the PostgreSQL database itself and the user-defined data types built by the user to complete the preliminary sorting of the fields, and then further perform secondary sorting of the fields based on the field attributes, data distribution and historical query records obtained by the analysis, combined with the preliminary sorting of the field attributes, so that the sorting result is more friendly to the use of the PostgreSQL database. Specifically, referring to 4, the method includes:

[0055] Preliminary sorting: Analyze the field types supported by the database itself and the user-defined data types to complete the preliminary field sorting;

[0056] Secondary sorting: Multi-dimensional secondary sorting of field usage frequency, data distribution, and historical query records is performed to make the sorting results more friendly to database use;

[0057] When the user manually uses the program to sort, before sorting, the user can view the comparative report of the disk space usage rate before and after sorting calculated by the program based on the model, the number of data items in the table, the field type, etc., and can preview the sorting results of the sorted fields. The user can decide whether to sort the fields at this stage. That is, the embodiment of the present invention can generate a comparative report of the disk space usage rate before and after sorting calculated based on the number of data items in the data table and the field type, and then present the comparative report to the user, and determine whether to sort the fields according to the user's instructions.

[0058] In summary, the method described in the embodiment of the present invention can be run regularly as a background task, or started when a specific event is triggered (such as after a large amount of data is inserted). When the algorithm starts, all fields of the target data table are first traversed to collect field attribute information. Then, according to the collected information, the sorting rules are applied to sort the fields. During the sorting process, the integrity and consistency of the data are ensured, and transaction processing and other methods can be used to prevent data loss or damage. Practice has proved that the present invention can locate the required data faster during query through reasonable field sorting, thereby reducing disk I / O and memory consumption. That is, the present invention reduces the waste of storage space and improves the storage efficiency of the database through good field sorting. In addition, the present invention greatly reduces manual intervention, reduces the cost and complexity of database management, and improves the maintainability of the database to a certain extent.

[0059] Accordingly, the embodiment of the present invention also provides a device for automatically sorting table fields of a PostgreSQL database, see Figure 5 , the device comprises:

[0060] A data analysis module is used to perform attribute analysis on each field in a data table in a PostgreSQL database to obtain field attributes of each field in the data table, wherein the field attributes include data type, field length, data frequency, and cardinality;

[0061] A data sorting module is used to formulate sorting rules according to the field attributes obtained by analysis, and sort the fields in the data table according to the formulated sorting rules to reduce the space occupied by the data table in the PostgreSQL database; wherein the sorting rules include: the sorting rules include: sorting the fields according to the data type size, sorting the fields according to the data frequency, or sorting the fields according to the cardinality size.

[0062] In specific implementation, the data sorting module in the embodiment of the present invention is also used to analyze the occupied byte widths of different fields according to the analyzed field attributes, and sort the fields based on the minimum occupied byte width as the sorting principle.

[0063] Furthermore, the device described in the embodiment of the present invention also includes a monitoring module, which is used to monitor the load information and field attributes of the PostgreSQL database in real time, and automatically trigger the data sorting module to perform sorting when a preset trigger condition is met.

[0064] Specifically, the data sorting module described in the embodiment of the present invention is also used to perform analysis based on the field types supported by the PostgreSQL database itself and the user-defined data types built by the user, complete the preliminary sorting of the fields, and then perform secondary sorting of the fields based on the field attributes, data distribution and historical query records obtained from the analysis, and combined with the preliminary sorting of the field attributes, so that the sorting result is more friendly to the use of the PostgreSQL database, and before sorting, a comparison report of the disk space usage before and after sorting can be calculated based on the number of data items and field types in the data table, and based on the comparison report, determine whether to sort the fields.

[0065] The relevant contents of the embodiments of the device of the present invention can be understood by referring to the embodiments of the method of the present invention, and will not be discussed in detail here.

[0066] In addition, an embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the method for automatically sorting table fields of a PostgreSQL database described in any one of the method embodiments of the present invention is implemented. For details, please refer to the method embodiments of the present invention for understanding, and no detailed discussion is given here.

[0067] Although the preferred embodiments of the present invention have been disclosed for illustrative purposes, those skilled in the art will appreciate that various modifications, additions and substitutions are possible, and thus, the scope of the present invention should not be limited to the above embodiments.

Claims

1. A method for automatically sorting table fields of a PostgreSQL database, characterized in that: include: Perform attribute analysis on each field in a data table in a PostgreSQL database to obtain field attributes of each field in the data table, wherein the field attributes include data type, field length, data frequency, and cardinality; Formulate a sorting rule according to the field attributes obtained by the analysis, and sort the fields in the data table according to the formulated sorting rule to reduce the space occupied by the data table in the PostgreSQL database; The sorting rules include: sorting the fields according to the data type size, sorting the fields according to the data frequency, or sorting the fields according to the cardinality size.

2. The method according to claim 1, characterized in that The formulating of sorting rules according to the analyzed field attributes includes: analyzing the occupied byte widths of different fields according to the analyzed field attributes, and sorting the fields based on the minimum occupied byte width as a sorting principle.

3. The method according to claim 1, characterized in that The method further comprises: saving the formulated sorting rules; The load information and field attributes of the PostgreSQL database are monitored in real time, and when a preset trigger condition is met, the field sorting of the data table in the PostgreSQL database is automatically triggered to adapt to the dynamic changes of the PostgreSQL database.

4. The method according to claim 3, characterized in that: The trigger condition includes one or more of the following: the load rate of the PostgreSQL database is lower than a preset load rate threshold, the data frequency change of the PostgreSQL database is greater than a preset data frequency change threshold, the data cardinality of the PostgreSQL database is greater than a preset cardinality threshold, and the structure of the data table in the PostgreSQL database changes.

5. The method according to claim 4, characterized in that The change in the structure of the data table includes one or more of the following: creating a table, deleting a field in a table, adding a field in a table, and modifying the field type in a table.

6. The method according to any one of claims 1 to 5, characterized in that: Before performing attribute analysis on each field of the data table in the PostgreSQL database, the method further includes: Analyze the field types supported by the PostgreSQL database itself and the user-defined data types to complete the preliminary sorting of the fields.

7. The method according to claim 6, characterized in that The sorting of the fields in the data table includes: According to the analyzed field attributes, data distribution and historical query records, and combined with the preliminary sorting of the field attributes, the fields are sorted secondary so that the sorting results are more user-friendly to the PostgreSQL database.

8. The method according to claim 6, characterized in that Before sorting the field attributes, the method further includes: A comparison report of disk space usage before and after sorting is calculated based on the number of data items and field types in the data table, and whether to sort the field is determined based on the comparison report.

9. A device for automatically sorting table fields of a PostgreSQL database, characterized in that: The device comprises: A data analysis module is used to perform attribute analysis on each field in a data table in a PostgreSQL database to obtain field attributes of each field in the data table, wherein the field attributes include data type, field length, data frequency, and cardinality; A data sorting module is used to formulate sorting rules according to the field attributes obtained by analysis, and sort the fields in the data table according to the formulated sorting rules to reduce the space occupied by the data table in the PostgreSQL database; wherein the sorting rules include: the sorting rules include: sorting the fields according to the data type size, sorting the fields according to the data frequency, or sorting the fields according to the cardinality size.

10. A computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, the method for automatically sorting table fields of a PostgreSQL database according to any one of claims 1 to 8 is implemented.

Images