Postgresql big data efficient maintenance-free automatic partition method

An automatic partitioning and maintenance-free technology, applied in database distribution/replication, electronic digital data processing, structured data retrieval, etc., can solve problems such as data overflow, low DML efficiency, and increased code maintenance difficulty, so as to save storage costs, Concise logic and the effect of solving code efficiency problems

Active Publication Date: 2017-12-12
深圳翰飞网络科技有限公司
View PDF4 Cites 2 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0007] The present invention is to solve the problem that after the definition of the partition table of the PostgreSQL database is fixed, the later partition table is not easy to split, and the data is easy to overflow from the range partition table. The more partition tables, the longer the partition logic code, resulting in low efficiency and increased DML. Insufficient code maintenance difficulty, provide a PostgreSQL that fixes the partition logic in a cycle, creates a fixed partition logic according to the fixed cycle, strips the active partition from the partition logic, automatically transfers data to the active partition, and automatically splits the partition according to the amount of data Efficient and maintenance-free automatic partitioning method for big data

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Postgresql big data efficient maintenance-free automatic partition method
  • Postgresql big data efficient maintenance-free automatic partition method

Examples

Experimental program
Comparison scheme
Effect test

example 1

[0034] Example 1: Efficient and maintenance-free automatic partitioning method for PostgreSQL big data, see figure 1 , figure 2 shown, including the following steps:

[0035] (1) Create an entry table, and the application program of the business side parallel data DML only interacts with the entry table.

[0036] (2) According to the characteristics of the partition value, abstractly convert the value into a cyclic value over time, such as a time field, which can be evolved into a cyclic value over time: cycle by month (1-12), cycle by week (1 -53), cycle by day (1-366), cycle by hour (1-24), cycle by minute (1-60), cycle by second (1-60), cycle by half hour (0100-2400), etc. , which provides a basis for solving partition logic code maintenance.

[0037] (3) Create a fixed partition logic according to the canned cycle value, and the partition logic code of the partition logic is distributed to the corresponding inheritance table according to the field value. For example, ...

example 2

[0047] Example 2: PostgreSQL big data efficient and maintenance-free automatic partition method, see figure 1 , figure 2 shown, including the following steps:

[0048] 1. Entrance table and partition logic implementation for business interaction.

[0049] 1-1. Create an entry table tbl, and specify a partition field such as ctime. It is required to distinguish which inheritance table the row is inserted into from the content of the field, for example, the type is timestamp. The application only interacts with the entry table.

[0050] 1-2. Create the inheritance table of the entry table, for example, create 24 inheritance tables by hour, tbl_01 to tbl_24, add necessary constraints to the ctime of each inheritance table, for example, tbl_01 constraint checkto_char(ctime,'hh24')=01 .

[0051] 1-3. Create an insert trigger function, and insert the corresponding inheritance table according to the value of the ctime function field. For example, 2014-01-0112:01:01 is inserted ...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

PUM

No PUM Login to view more

Abstract

The invention discloses an efficient maintenance-free automatic partition method for big data of a PostgreSQL, belonging to the field of processing technologies for the big data of a database. The method comprises the following steps of creating an entry table and converting a partition value to a fixed cycle value over time in an abstract manner; creating fixed partitioned logic according to the fixed cycle value; creating a partition inheritance table, which is distributed according to a partitioned logic code and a field value of the entry table; stripping an active partition from the partitioned logic and automatically creating the active partition over time; utilizing inheritance properties to transfer data of a fixed partition to the active partition regularly and deleting the data in the fixed partition after transfer is completed; carrying out split design according to the data size, predefining correspondence rule of layering and the data size, and automatically carrying out split on the next layer when the table data size of the active partition exceeds an upper threshold; and creating an asynchronous inheritance table, which is transferred according to asynchronous active movement data of the partition inheritance table.

Description

technical field [0001] The invention relates to the technical field of database big data processing, in particular to a high-efficiency maintenance-free automatic partitioning method for PostgreSQL big data. Background technique [0002] The existing PostgreSQL database has a limit on the number of records in a single table, and also has a limit on the storage space. Even if these are not limited, the operation efficiency of MDL (Data Manipulation Language Command) will be affected by the growth of the table size. influences. Existing PostgreSQL databases generally use data partitioning or data tiering to process big data. For example, the PostgreSQL database not only supports several data partitioning methods such as range, list and hash, but also supports any combination of data layering methods, but the data layering method needs to be implemented through triggers. [0003] Currently, there are the following issues when using traditional data partitioning for PostgreSQL...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

Application Information

Patent Timeline
no application Login to view more
Patent Type & Authority Patents(China)
IPC IPC(8): G06F17/30
CPCG06F16/27
Inventor 周正中
Owner 深圳翰飞网络科技有限公司
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Try Eureka
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap