Shulou(Shulou.com)05/31 Report--

PostgreSQL table partition example analysis of different implementations, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The pg_pathman update provides support for changing queries and deleting queries. Due to the use of PostgreSQL query plan hooks, update and delete plans when querying a single partition, the speed will be improved otherwise, query planning is still slow,UPDATE,Delete operations involving only one partition seems to be the most common, most in need of optimization.

Also, share some benchmarks. This benchmark is a year of bookkeeping, partitioned by day, totaling about 1 million (1M) pieces of data. Of course, this is just a test column, because in practice, the data volume is so small that no one would divide it into so many partitions.

But it's still nice to see zoning overhead. Compare the performance of:

Select a single row (using timestamp) SELECT one;

Select all data for a day (query all data for a single partition) SELECT one_partition

Random Insert Single Row (with Random Timestamp) INSERT

Random Update Single Row (Filter with Random Timestamp) UPDATE

The following zoning methods were compared:

Single table without partition pg_partman extension pg_pathman extension

On a server with 2xIntel Xeon CPU X5675@3.07GHz and 24 GB RAM, the database parameter configuration fsync=off using 10 threads yields the following results.

I can conclude that

pg_pathman is significantly better than pg_partman, mainly because pg_pathman uses query plan hooks, while pg_partman uses built-in inheritance mechanisms.

pg_pathman is almost as fast as regular tables when selecting queries or updating individual rows, the difference between inserting individual rows is slightly larger because of the trigger used

The difference between pg_partman and pg_pathman when selecting whole partitions is not that many times (when querying single rows) when selecting whole partitions for queries. This is because query planning time is only a fraction of the total statement execution time.

PG_pathman still executes much faster than PG_partman at random INSERT, because the other two use triggers on parent relationships, but PG_pathman uses fast C functions to implement it.

Selecting the entire partition table partitioned by pg_pathman is slightly higher than selecting the same row from the plain table. This is because sequential scans are used when scanning the entire table partition, whereas index scans are used to select portions of the table. This discrepancy is expected to be greater when the data volume is large and the cache is insufficient.

SQL scripts used for benchmarking, see this rationale.

create_*.sql Create journal tables using various methods of partitioning. Scripts for pg_bench: select_one.sql, select_day.sql, insert.sql, and update.sql .

After reading the above, do you have the method of sample analysis of different implementations of PostgreSQL table partitioning? If you still want to learn more skills or want to know more related content, welcome to pay attention to 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.