Shulou(Shulou.com)06/01 Report--

How to parse the PostgreSQL partition table, I believe many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The lack of MYSQL on the partitioned table is different, and POSTGRESQL's partitioned table is "hard". PG11 has introduced HASH partitions. What is the specific operation?

First of all, what are the general uses of the partition table?

1 on the basis of being in line with the business, it can improve the query speed for reasonable preset queries.

(2) on the basis of being in line with the business, it can reduce the competition of hot spot blocks and improve the insertion speed.

(3) in some cases, the data in the partition may be scanned sequentially by a reasonable access partition, which can improve the performance of data access better than using the index in the partition.

(4) if the partition is set reasonably, it will be more convenient to deal with the useless data and uniformly process the data in a partition.

Currently, the partitioning methods supported by POSTGRESQL are

1 Range Partitiioning

2 List Partitioning

3 Hash Partitioning

Similar to ORACLE, each partition can have its own subpartition index, default value, or some other definition.

The other thing is that partitioning needs to be designed in advance, rather than doing partitioning tables after the system's database tables have been running for a long time. Therefore, it is important to do a good job of design at the beginning of system design.

Let's do something next.

1 HASH Partitioning is a new feature in PG 11 that accelerates data insertion through HASH partitioning, which is a proud feature of ORACLE DBA.

Let's create a HASH partition table, where BIGINT is the primary key, and the inserted primary key is divided by the remainder of 10 to select the location of the record.

Then the direct insertion of 200000 data was completed in less than 1 second.

Let's take a look at the inserted data, which is basically scattered.

There is no problem with the query, and the index of the primary key is gone.

It is also very simple to clear the data from a partitioned table. Just delete that table.

It's also easy if you want to continue to restore that partition.

Of course, it's easy if you want to keep some of the data in the data partition table, but you don't want him to be a member of the partition table.

At the same time, PostgreSQL also supports the type of range partition. Through the partition of range type, the partition can be divided according to the date already designed, or the row data in other tables for partition table storage.

Don't talk nonsense, set up relevant tables and partition tables

In the partition table of the establishment date type, note that to does not contain the time period specified later. If the next range is set to from ('2008-01-01') to ('2008-12-31') to from ('2019-01-01') to ('2019-12-31'), it will be reported when the data is inserted

No partition of relation found for row

It's a problem.

Another problem is that when creating a partitioned table (Postgresql does not recommend establishing a primary key, this will cause necessary trouble in the future, and unique indexes can be created if necessary)

Create a stored procedure to insert data to see if there is a problem with the partitioned table

Create or replace procedure insert_data ()

Language plpgsql

As $$

Declare

Date_value timestamp

Declare id int

Begin

Date_value: = '2007-01-01 00:00:00'::timestamp +' 1 hour'

Id = 1

While id < 400000 loop

Insert into range_test (password,insert_date) (select id::char (48), date_value::timestamp)

Id = id + 1

Date_value: = date_value::timestamp + '1hour'

End loop

End

$$

There is no problem to validate the data through the two statements in the figure.

We can also set up relevant partition indexes according to the requirements of the query.

Of course, if you query the whole table, you can also use the global index.

After reading the above, have you mastered how to parse the PostgreSQL partition table? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.