• 🚀 1.分區(qū)表

• 🌈 1.1 什么是表分區(qū)

• 🌈 1.2 為什么需要表分區(qū)

• 🌈 1.3 分區(qū)表的優(yōu)缺點(diǎn)

• 🚀 2.分區(qū)表的類型

• 🌈 2.1 RANGE分區(qū)

• 🌈 2.2 LIST分區(qū)

• 🌈 2.3 HASH 分區(qū)

• 🚀 3.分區(qū)表常用命令

• 🚀 4.總結(jié)

PG表分區(qū)，是指根據(jù)一定規(guī)則，將數(shù)據(jù)庫中的一張表分解成多個(gè)更小的，容易管理的部分，本文做了詳細(xì)的說明。

下面由邦德老師，給大家詳細(xì)的介紹下PostgreSQL的分區(qū)吧~

🚀 1.分區(qū)表

🌈 1.1 什么是表分區(qū)

分區(qū)表就是根據(jù)分區(qū)策略，將數(shù)據(jù)數(shù)據(jù)分散到不同的子表中，
并通過父表建立關(guān)聯(lián)關(guān)系，從而實(shí)現(xiàn)數(shù)據(jù)物理上的分區(qū)

PostgreSQL 10.x 之前的版本提供了一種“手動(dòng)”方式使用分區(qū)表的方式，
需要使用繼承 + 觸發(fā)器的來實(shí)現(xiàn)分區(qū)表，
步驟較為繁瑣，需要定義附表、子表、
子表的約束、創(chuàng)建子表索引，創(chuàng)建分區(qū)刪除、修改，觸發(fā)器等。

PostgreSQL 10.x 開始提供了內(nèi)置分區(qū)表（內(nèi)置是相對(duì)于 10.x 之前的手動(dòng)方式）。
內(nèi)置分區(qū)簡(jiǎn)化了操作，將部分操作內(nèi)置，
最終簡(jiǎn)單三步就能夠創(chuàng)建分區(qū)表。
但是只支持范圍分區(qū)（RANGE）和列表分區(qū)（LIST），
11.x 版本添加了對(duì) HASH 分區(qū)。

🌈 1.2 為什么需要表分區(qū)

1.可以讓單表存儲(chǔ)更多的數(shù)據(jù)。
2.分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過清除整個(gè)分區(qū)批量刪除大量數(shù)據(jù)，
也可以增加新的分區(qū)來支持新插入的數(shù)據(jù)。
另外，還可以對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作。
3.部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上，查詢速度會(huì)很快。
4.分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而高效利用多個(gè)硬件設(shè)備。
5.可以使用分區(qū)表來避免某些特殊瓶頸，
可以備份和恢復(fù)單個(gè)分區(qū)。

🌈 1.3 分區(qū)表的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：
（1）**數(shù)據(jù)維護(hù)成本降低。某一部分?jǐn)?shù)據(jù)需要失效，
不需要執(zhí)行命令來更新數(shù)據(jù)，可以直接接觸綁定關(guān)系，
接觸綁定的數(shù)據(jù)和分區(qū)表都依然保留，需要時(shí)可以隨時(shí)恢復(fù)。
（2）一個(gè)表只能放在一個(gè)物理空間上，使用分區(qū)表之后可以將不同的表放置在不同的物理空間上，
從而達(dá)到冷數(shù)據(jù)放在廉價(jià)的物理機(jī)器上，熱點(diǎn)數(shù)據(jù)放置在性能強(qiáng)勁的機(jī)器上。
（3）直接從分區(qū)表查詢數(shù)據(jù)比從一個(gè)大而全的全量數(shù)據(jù)表中讀取數(shù)據(jù)效率更高。


缺點(diǎn)：
（1）性能上的兩面性。性能上通過分區(qū)表的父表查數(shù)據(jù)相對(duì)于普通的數(shù)據(jù)全量表查詢效率要低。
直接分區(qū)表中查詢數(shù)據(jù)比在全量表中查詢數(shù)據(jù)效率要高。
（2）10.x 版本不支持跨分區(qū)更新，11.x 版本中才支持快分區(qū)的更新。
（3）主鍵有可能重復(fù)。由于分區(qū)表的的主鍵約束都是分別建立的，
因此可能存在主鍵重復(fù)。只能通過一些策略來規(guī)避主鍵相同的問題。

🚀 2.分區(qū)表的類型

🌈 2.1 RANGE分區(qū)

對(duì)于范圍分區(qū)，數(shù)據(jù)根據(jù)所選范圍劃分為段。當(dāng)您需要訪問時(shí)間序列數(shù)據(jù)時(shí)，提供日期（例如年份和月份）很有用

創(chuàng)建父表
CREATE TABLE orders (
 id serial,
 user_id int4,
 create_time timestamp(0) 
) PARTITION BY RANGE(create_time);

創(chuàng)建分區(qū)表
CREATE TABLE orders_hisotry PARTITION OF orders FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');
CREATE TABLE orders_202003 PARTITION OF orders FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
CREATE TABLE orders_202004 PARTITION OF orders FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE TABLE orders_202005 PARTITION OF orders FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE TABLE orders_202006 PARTITION OF orders FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');

在分區(qū)上創(chuàng)建索引
CREATE INDEX order_idx_history_create_time ON orders_history USING btree(create_time);
CREATE INDEX order_idx_202003_create_time ON orders_202003 USING btree(create_time);
CREATE INDEX order_idx_202004_create_time ON orders_202004 USING btree(create_time);
CREATE INDEX order_idx_202005_create_time ON orders_202005 USING btree(create_time);
CREATE INDEX order_idx_202006_create_time ON orders_202006 USING btree(create_time);

🌈 2.2 LIST分區(qū)

在列表分區(qū)中，數(shù)據(jù)根據(jù)已指定的離散值進(jìn)行分區(qū)。當(dāng)需要對(duì)地區(qū)、部門等離散數(shù)據(jù)進(jìn)行任意值分組時(shí)，這種方法效果很好

postgres=# CREATE TABLE cities (
    city_id      bigint not null,
    name         text   not null,
    population   bigint
) PARTITION BY LIST (name);

CREATE TABLE cities_1 PARTITION OF cities FOR VALUES IN ('A');
CREATE TABLE cities_2 PARTITION OF cities FOR VALUES IN ('B');
CREATE TABLE cities_3 PARTITION OF cities FOR VALUES IN ('C');
CREATE TABLE cities_4 PARTITION OF cities FOR VALUES IN ('D');

postgres=# \d+
                                        List of relations
 Schema |             Name              |       Type        |  Owner   |    Size    | Description
--------+-------------------------------+-------------------+----------+------------+-------------
 public | cities                        | partitioned table | postgres | 0 bytes    | 
 public | cities_1                      | table             | postgres | 8192 bytes | 
 public | cities_2                      | table             | postgres | 8192 bytes | 
 public | cities_3                      | table             | postgres | 8192 bytes | 
 public | cities_4                      | table             | postgres | 8192 bytes |  
(5 rows)

postgres=# select * from pg_inherits;
 inhrelid | inhparent | inhseqno 
----------+-----------+----------
    16727 |     16724 |        1
    16733 |     16724 |        1
    16739 |     16724 |        1
    16745 |     16724 |        1
(4 rows)
————————————————

🌈 2.3 HASH 分區(qū)

每個(gè)分區(qū)的數(shù)據(jù)通過提供模數(shù)和余數(shù)來分區(qū)。
每個(gè)分區(qū)將包含模數(shù)除以分區(qū)鍵的哈希值得到給定余數(shù)的行。
當(dāng)您希望通過幾乎均勻地分布數(shù)據(jù)來避免對(duì)單個(gè)表的訪問集中時(shí)，這種方法很有效。

CREATE TABLE emp (emp_id int, emp_name text, dep_code int)
PARTITION BY HASH (emp_id);


CREATE TABLE emp_0 PARTITION OF emp FOR VALUES WITH (MODULUS 3,REMAINDER 0);
CREATE TABLE emp_1 PARTITION OF emp FOR VALUES WITH (MODULUS 3,REMAINDER 1);
CREATE TABLE emp_2 PARTITION OF emp FOR VALUES WITH (MODULUS 3,REMAINDER 2);

🚀 3.分區(qū)表常用命令

查看分區(qū)表和每個(gè)分區(qū)的行數(shù)
SELECT relname,reltuples as rows FROM pg_class
WHERE relname IN ('emp','emp_0','emp_1','emp_2')
ORDER BY relname;

從分區(qū)表中分離分區(qū)
ALTER TABLE emp DETACH PARTITION emp_0;

重命名分區(qū)
ALTER TABLE emp_0 RENAME TO emp_0_bkp;

添加新的分區(qū)
ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN ('value');

刪除現(xiàn)有的分區(qū)
ALTER TABLE table_name DROP PARTITION partition_name;

查看所有分區(qū)信息
SELECT * FROM pg_partitions WHERE tablename = 'table_name';

🚀 4.總結(jié)

簡(jiǎn)而言之，分區(qū)是關(guān)系數(shù)據(jù)庫中用于將大表分解為較小分區(qū)的一種方法。這有助于更快地對(duì)大表執(zhí)行查詢。在本文中，您學(xué)習(xí)了以上3種類型的 PostgreSQL 分區(qū)以及如何使用它們。