PostgreSQL介紹

PostgreSQL 是一個功能強大的開源關系型數據庫系統，它使用并擴展了 SQL 語言，并結合了許多功能，可以安全地存儲和擴展復雜的數據工作

PostgreSQL 因其經過驗證的架構、可靠性、數據完整性、強大的功能集、可擴展性以及軟件背后的開源社區始終如一地提供高性能和創新解決方案的奉獻精神而贏得了良好的聲譽。PostgreSQL 可在所有主要操作系統上運行，自 2001 年以來一直符合ACID，并且具有強大的附加組件，例如流行的PostGIS地理空間數據庫擴展器。

為什么使用 PostgreSQL？

PostgreSQL 附帶了許多功能，旨在幫助開發人員構建應用程序、管理員保護數據完整性和構建容錯環境，并幫助您管理數據，無論數據集有多大或多小。除了免費和開源之外，PostgreSQL 還具有高度可擴展性。例如，可以定義自己的數據類型、構建自定義函數，甚至可以用不同的編程語言編寫代碼，而無需重新編譯數據庫！

PostgreSQL社區是純社區，沒有被商業公司控制，讓很多用戶云廠商愿意貢獻核心代碼，讓PostgreSQL得到了快速的版本迭代，豐富的應用插件。

為什么要學習PostgreSQL？

中國目前在全面實行國產化替代項目，從軍工、政府、金融、醫療、教育到企業逐步替換掉非國產化的東西，比如說計算機服務器，軍工，軟件等，軟件中數據庫是重要的一環。國內很多數據軟件，以下列舉幾個常用的國產重構的關系型集中式架構數據庫。

國外數據庫使用情況 來源：https://db-engines.com/en/ranking/relational+dbms

國內數據庫使用情況 來源：https://www.modb.pro/dbRank

可以看到排行前幾的底層技術基本使用PostgreSQL

為什么要國產數據庫大多數底層都基于PostgreSQL而非MYSQL？

主要是PostgreSQL與MySQL的版權區別

PostgreSQL許可

PostgreSQL許可是一種自由的開源許可，BSD開源協議是一個給于使用者很大自由的協議。可以自由的使用，修改源代碼，也可以將修改后的代碼作為開源或者專有軟件再發布，被譽為開源許可的“活雷鋒”。

BSD 代碼鼓勵代碼共享，但需要尊重代碼作者的著作權。BSD由于允許使用者修改和重新發布代碼，也允許使用或在BSD代碼上開發商業軟件發布和銷售，因此是對商業集成很友好的協議。所以很多公司企業在選開源產品的時都首選BSD協議，因為可以完全控制這些第三方的代碼，在必要的時候可以修改或者二次開發。

PostgreSQL許可描述：https://www.postgresql.org/about/licence/

MySQL許可

眾所周知，MySQL被Oracle所控制，MySQL同時使用了GPL和一種雙重許可

GPL(General Public license)是公共許可，遵循了GPL的軟件是公共的。如果某軟件使用了GPL軟件，那么該軟件也需要開源，如果不開源，就不能使用GPL軟件，這和是否把該軟件商用與否是沒關系的。具體約束：

• 不允許對在MySQL上作出的修改申請專利；
• MySQL上的修改需要公開，且所有權歸Oracle所有；
• 出于純學術目的、練習目的源碼修改也是符合GPL的；
• Oracle的MySQL企業版或高級功能會涉及費用，并且Oracle公司不允許其它基于MySQL的閉源產品。

可以理解為：商業許可是控制MySQL的公司留給自己的特權。

與mysql區別

PostgreSQL的優勢

1. 在SQL的標準實現上要比MySQL完善，而且功能實現比較嚴謹。
2. 對表連接支持較完整，優化器的功能較完整，支持的索引類型很多，復雜查詢能力較強。
3. PostgreSQL主表采用堆表存放，MySQL采用索引組織表，能夠支持比MySQL更大的數據量。
4. PostgreSQL的主從復制屬于物理復制，相對于MySQL基于binlog的邏輯復制，數據的一致性更加可靠，復制性能更高，對主機性能影響更小
5. PostgreSQL支持JSON，XML和其他NoSQL功能，還支持索引JSON數據以加快訪問速度
6. PostgreSQL完全免費，而且是BSD協議，如果你把PostgreSQL改一改，然后再拿去賣錢，也沒有人管你，相反MySQL現在是被Oracle公司控制

MySQL的優勢

1. innodb的基于回滾段實現的MVCC機制，相對PG新老數據一起存放的基于XID的MVCC機制，是占優的。新老數據一起存放，需要定時觸發VACUUM,會帶來多余的IO和數據庫對象加鎖開銷，引起數據庫整體的并發能力下降。而且VACUUM清理不及時，還可能會引發數據膨脹。
2. MySQL采用索引組織表，這種存儲方式非常適合基于主鍵匹配的查詢、刪改操作，但是對表結構設計存在約束。
3. MySQL的優化器較簡單，系統表、運算符、數據類型的實現都很精簡，非常適合簡單的查詢操作。
4. MySQL相對于PostgreSQL在國內的流行度更高，PostgreSQL在國內顯得就有些落寞了。
5. MySQL的存儲引擎插件化機制，使得它的應用場景更加廣泛，比如除了innodb適合事務處理場景外，myisam適合靜態數據的查詢場景。

從應用場景來說，PG更加適合嚴格的企業應用場景( 比如金融、電信、ERP、CRM )，但不僅僅限制于此，PostgreSQL的json,jsonb,hstore等數據格式，特別適用于一些大數據格式的分析；

而MySQL更加適合業務邏輯相對簡單、數據可靠性要求較低的互聯網場景(比如google、facebook、alibaba)，當然現在MySQL的在innodb引擎的大力發展，功能表現良好。

對比維度	MySQL	PostgreSQL
基礎架構	多線程架構（共享進程資源），適合高并發簡單查詢	多進程架構（獨立進程處理連接），資源隔離性更好
SQL標準支持	支持SQL:2003核心規范，對窗口函數等高級特性支持較晚（5.8+版本）	嚴格遵循SQL:2016標準，支持窗口函數、CTE、遞歸查詢等復雜語法
事務與并發控制	InnoDB支持MVCC，默認隔離級別為可重復讀（REPEATABLE READ）	全功能MVCC，支持可序列化（SERIALIZABLE）隔離級別，無鎖讀設計更優
存儲引擎	多引擎可選（InnoDB/MyISAM/Memory），MyISAM適合讀密集型場景	單一存儲引擎但支持自定義索引（GIN/GiST/BRIN等）和擴展（如PostGIS）
數據類型	基礎類型（INT/VARCHAR/TEXT），5.7+支持JSON	50+種內置類型（數組/JSONB/范圍/幾何類型），支持自定義類型
索引能力	B+樹為主，支持全文索引	支持B-Tree/GiST/GIN/SP-GiST/BRIN/Bloom等多種索引類型
擴展性	插件式存儲引擎擴展，用戶自定義功能有限	支持自定義函數/運算符/聚合函數，可集成TimescaleDB、Citus等擴展
高可用方案	InnoDB Cluster + MySQL Router，主從復制延遲較低	流復制 + Patroni集群，支持同步/異步/級聯復制
性能特性	簡單查詢響應快（主鍵查詢0.0001秒級），高并發寫入吞吐量高（TPC-C 1000并發達96k tpmC）	復雜查詢優化強（窗口函數+并行查詢），高并發下延遲更低（TPC-C 1000并發延遲89ms vs MySQL 132ms）
GIS支持	需依賴第三方工具（如MySQL Spatial）	內置PostGIS擴展，支持空間數據索引和GIS函數
全文搜索	基于N-gram分詞，支持簡單全文檢索	支持多語言分詞（TSVector/TSQuery），加權搜索和短語匹配更優
開發友好度	語法寬松（如允許錯誤日期插入），適合快速迭代	語法嚴格（強制日期合法性檢查），適合企業級嚴謹開發
許可證	GPL協議，商業用途需購買授權	BSD協議，允許閉源和商業二次開發
典型適用場景	電商秒殺、社交應用、中小型CMS	金融系統、地理信息、數據分析、ERP/CRM

PostgreSQL下載安裝

官網地址：https://www.postgresql.org/download/

直接選擇對應的版本就行 https://ftp.postgresql.org/pub/source/v16.6/

數據庫基礎操作

1. 創建數據庫和用戶

使用createdb命令可以創建新的數據庫，而createuser命令則用于創建新的數據庫用戶。在創建數據庫和用戶之前，確保你已經安裝了PostgreSQL并正確配置了系統環境。

# 創建數據庫
createdb mydatabase# 創建用戶
createuser myuser --pwprompt    # pwprompt選項設置密碼

完整

CREATE DATABASE name[ WITH ] [ OWNER [=] user_name ][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ STRATEGY [=] strategy ] ][ LOCALE [=] locale ][ LC_COLLATE [=] lc_collate ][ LC_CTYPE [=] lc_ctype ][ ICU_LOCALE [=] icu_locale ][ ICU_RULES [=] icu_rules ][ LOCALE_PROVIDER [=] locale_provider ][ COLLATION_VERSION = collation_version ][ TABLESPACE [=] tablespace_name ][ ALLOW_CONNECTIONS [=] allowconn ][ CONNECTION LIMIT [=] connlimit ][ IS_TEMPLATE [=] istemplate ][ OID [=] oid ]

2. 連接數據庫

使用psql命令行工具可以連接到PostgreSQL數據庫。需要提供數據庫名稱、用戶名和密碼作為連接參數。

# 連接到數據庫
psql -U myuser -d mydatabase

3. 數據類型與表操作

PostgreSQL支持豐富的數據類型，包括數值、字符、日期/時間、二進制數據等。你可以使用CREATE TABLE語句來創建表，并指定列的數據類型。同時，還可以使用INSERT、UPDATE和DELETE語句來操作表中的數據。

-- 創建表
CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,age INTEGER,hire_date DATE
);-- 插入數據
INSERT INTO employees (name, age, hire_date) VALUES ('Alice', 30, '2025-01-01');-- 更新數據
UPDATE employees SET age = 31 WHERE name = 'Alice';-- 刪除數據
DELETE FROM employees WHERE id = 1;

4. 查詢數據

使用SELECT語句可以從表中檢索數據。可以使用各種條件、排序和聚合函數來定制查詢結果。

-- 查詢所有數據
SELECT * FROM employees;-- 查詢特定列的數據
SELECT name, age FROM employees;-- 帶條件的查詢
SELECT * FROM employees WHERE age > 30;-- 排序查詢結果
SELECT * FROM employees ORDER BY age DESC;-- 使用聚合函數
SELECT COUNT(*) FROM employees;

這些查詢語句將分別返回employees表中的所有數據、特定列的數據、滿足年齡大于30歲的員工數據、按年齡降序排列的員工數據，以及員工的總數。

5. 索引與性能優化

為了提高查詢性能，可以為表的某些列創建索引。加速數據的檢索速度，但也會占用額外的磁盤空間并可能增加插入、更新和刪除操作的開銷。

-- 創建索引
CREATE INDEX idx_employees_name ON employees(name);-- 刪除索引
DROP INDEX idx_employees_name;

6. 備份與恢復

為了數據安全，定期備份數據庫是非常重要的。PostgreSQL提供了多種備份和恢復工具，如pg_dump和pg_restore。

# 備份數據庫
pg_dump -U myuser -d mydatabase > backup.sql# 恢復數據庫
pg_restore -U myuser -d mydatabase < backup.sql

7.擴展操作

在PGSQL中，寫SQL語句時，單引號用來標識實際的值。雙引號用來標識一個關鍵字，比如表名，字段名。

-- 單引號寫具體的值，雙引號類似MySQL的``標記，用來填充關鍵字
-- 下面的葡萄牙會報錯，因為葡萄牙不是關鍵字
select 1.414,'卡塔爾',"葡萄牙";

數據類型轉換

-- 第一種方式：只需要在值的前面，添加上具體的數據類型即可
-- 將字符串轉成位圖類型
select bit '010101010101001';-- 第二種方式：也可以在具體值的后面，添加上 ::類型 ，來指定
-- 數據類型
select '2011-11-11'::date;
select '101010101001'::bit(20);
select '13'::int;-- 第三種方式：使用CAST函數
-- 類型轉換的完整寫法
select CAST(varchar '100' as int);

九大類型

布爾類型

布爾類型簡單的丫批，可以存儲三個值，true，false，null

-- 布爾類型的約束沒有那么強，true，false大小寫隨意，他會給你轉，同時yes，no這種他也認識，但是需要轉換
select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;

數值類型

整型

整型比較簡單，主要就是三個：

• smallint、int2：2字節
• integer、int、int4：4字節
• bigint、int8：8字節

正常沒啥事就integer，如果要存主鍵，比如雪花算法，那就bigint。空間要節約，根據情況smallint

浮點型

浮點類型就關注2個（其實是一個）

• decimal(n,m)：本質就是numeric，PGSQL會幫你轉換
• numeric(n,m)：PGSQL本質的浮點類型

針對浮點類型的數據，就使用 numeric

序列

序列大多數的應用，是用作表的主鍵自增效果。默認情況下，seqeunce的起始值是0，每次nextval遞增1，最大值9223372036854775807

MySQL中的主鍵自增，是基于auto_increment去實現。MySQL里沒有序列的對象。

PGSQL和Oracle十分相似，支持序列：sequence。

PGSQL可沒有auto_increment。序列的正常構建方式：

create sequence laozheng.table_id_seq;
-- 查詢下一個值
select nextval('laozheng.table_id_seq');
-- 查詢當前值
select currval('laozheng.table_id_seq');

數值的常見操作

針對數值咱們可以實現加減乘除取余這5個操作，另外還有

字符串類型

字符串類型用的是最多的一種，在PGSQL里，主要支持三種：

• character（就是MySQL的char類型），定長字符串。（最大可以存儲1G）
• character varying（varchar），可變長度的字符串。（最大可以存儲1G）
• text（跟MySQL異常）長度特別長的字符串。

操作沒什么說的，但是字符串常見的函數特別多。

字符串的拼接一要要使用||來拼接。其他的函數，可以查看 http://www.postgres.cn/docs/12/functions-string.html

日期類型

在PGSQL中，核心的時間類型，就三個。

• timestamp（時間戳，覆蓋 年月日時分秒）

• date（年月日）

• time（時分秒）

在PGSQL中，聲明時間的方式：只需要使用字符串正常的編寫 yyyy-MM-dd HH:mm:ss就可以轉換為時間類型。直接在字符串位置使用之前講到的數據類型轉換就可以了。

當前系統時間 ：

• 可以使用now作為當前系統時間（沒有時區的概念）

  -- 直接查詢now，沒有時區的概念
  select timestamp 'now';
  -- 也可以使用current_timestamp的方式獲取（推薦，默認東八區）
  select time with time zone 'now' at time zone '08:00:00'
  

日期類型的運算

• 正常對date類型做+，-操作，默認單位就是天~

-- date + time = timestamp~~~
select date '2011-11-11' + time '12:12:12' ;-- 可以針對timestamp使用interval的方式進行 +，-操作，在查詢以時間范圍為條件的內容時，可以使用
select timestamp '2011-11-11 12:12:12' + interval '1day' + interval '1minute' + interval '1month';

枚舉類型

枚舉類型MySQL也支持，只是沒怎么用，PGSQL同樣支持這種數據類型，可以聲明枚舉類型作為表中的字段類型，這樣可以無形的給表字段追加詭異的規范。

-- 聲明一個星期的枚舉，值自然只有周一~周日。
create type week as enum ('Mon','Tues','Sun');
-- 聲明一張表，表中的某個字段的類型是上面聲明的枚舉。
drop table test;
create table test(id bigserial ,weekday week
);
insert into test (weekday) values ('Mon');
insert into test (weekday) values ('Fri');

IP類型

PGSQL支持IP類型的存儲，支持IPv4，IPv6這種，甚至Mac內種詭異類型也支持，這種IP類型，可以在存儲IP時，幫助做校驗，其次也可以針對IP做范圍查找。

JSON&JSONB類型

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因為JSON類型做查詢時，基本無法給JSON字段做索引。

PGSQL支持JSON類型以及JSONB類型。JSON和JSONB的使用基本沒區別。

本質上JSON格式就是一個字符串，比如MySQL5.7不支持JSON的情況的下，使用text也可以，但是字符串類型無法校驗JSON的格式，其次單獨的字符串沒有辦法只獲取JSON中某個key對應的value。

JSON和JSONB的區別：

• JSON類型無法構建索引，JSONB類型可以創建索引。
• JSON類型的數據中多余的空格會被存儲下來。JSONB會自動取消多余的空格。
• JSON類型甚至可以存儲重復的key，以最后一個為準。JSONB不會保留多余的重復key（保留最后一個）。
• JSON會保留存儲時key的順序，JSONB不會保留原有順序。

JSON中key對應的value的數據類型

[{"name": "張三"},{"name": {"info": "xxx"}}
]

操作JSON

select '9'::JSON,'null'::JSON,'"laozheng"'::JSON,'true'::json;
select '9'::JSONB,'null'::JSONB,'"laozheng"'::JSONB,'true'::JSONB;-- JSON數組
select '[9,true,null,"我是字符串"]'::JSON;-- JSON對象
select '{"name": "張三","age": 23,"birthday": "2011-11-11","gender": null}'::json;
select '{"name": "張三","age": 23,"birthday": "2011-11-11","gender": null}'::jsonb;-- 構建表存儲JSON
create table test(id bigserial,info json,infob jsonb
);
insert into test(info,infob) values ('{"name":"張三","age": 23,"birthday": "2011-11-11","gender": null}','{"name":"張三","age": 23,"birthday": "2011-11-11","gender": null}')
select * from test;create index json_index on test(info);
create index jsonb_index on test(infob);

JSON還支持很多函數。可以直接查看 http://www.postgres.cn/docs/12/functions-json.html 函數太多了，不分析了。

復合類型

復合類型就像Java中的一個對象，Java中有一個User，User和表做了一個映射，User中有個人信息對象。可以基于符合類型對映射上個人信息

public class User{private Integer id;private Info info;
}class Info{private String name;private Integer age;
}

按照上面的情況，將Info構建成一個復合類型

-- 構建復合類型，映射上Info
create type info_type as (name varchar(32),age int);
-- 構建表，映射User
create table tb_user(id serial,info info_type
);
-- 添加數據
insert into tb_user (info) values (('張三',23));
insert into tb_user (info) values (('露絲',233));
insert into tb_user (info) values (('jack',33));
insert into tb_user (info) values (('李四',24));
select * from tb_user;

數組類型

數組還是要依賴其他類型，比如在設置住址，住址可能有多個住址，可以采用數組類型去修飾字符串。

PGSQL中，指定數組的方式就是[]，可以指定一維數組，也支持二維甚至更多維數組。

構建數組的方式：

drop table test;
create table test(id serial,col1 int[],col2 int[2],col3 int[][]
);
-- 構建表指定數組長度后，并不是說數組內容只有2的長度，可以插入更多數據
-- 甚至在你插入數據，如果將二維數組結構的數組扔到一維數組上，也可以存儲。
-- 數組編寫方式
select '{{how,are},{are,you}}'::varchar[];
select array[[1,2],[3,4]];
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{7,8,9}');
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}',array[[1,2],[3,4]]);
insert into test (col1,col2,col3) values ('{1,2,3}','{4,5,6}','{{1,2},{3,4}}');
select * from test;

如果現在要存儲字符串數組，如果存儲的數組中有雙引號怎么辦，有大括號怎么辦。

-- 如果存儲的數組中的值，有單引號怎么辦？
-- 使用兩個單引號，作為一個單引號使用
select '{''how''}'::varchar[];
-- 如果存儲的數組中的值，有逗號怎么辦？(PGSQL中的數組索引從1開始算，寫0也是從1開始算。)
-- 用雙引號將數組的數據包起來~
select ('{"how,are"}'::varchar[])[2];
-- 如果存儲的數組中的值，有雙引號怎么辦？
-- 如果要添加雙引號，記得轉義。
select ('{"\"how\",are"}'::varchar[])[1];

數組的比較方式

-- 包含
select array[1,2] @> array[1];
-- 被包含
select array[1,2] <@ array[1,2,4];
-- 是否有相同元素
select array[2,4,4,45,1] && array[1];

【總結】

1. 數字類型

   • smallint: ±32767，小整數，int2。適用于范圍有限的整數場景。
   • integer: ±2147483647，整數，int4。適用于大多數整數場景。
   • bigint: ±9223372036854775807，大整數，int8。適用于需要更大范圍的整數場景。

2. 字符串型

   • char(n): 固定長度字符，如國家代碼。注意：存儲過短或過長的字符串可能導致不必要的空間浪費或截斷錯誤。
   • varchar(n): 可變長度文本，如姓名或地址。推薦為多語言或特殊字符需求指定UTF-8字符集和編碼。
   • text: 較長文本，禁止用于存儲附件。注意：在某些查詢中性能可能不如varchar。

3. 日期/時間類型

   • date: 日期。適用于只需要日期不需要時間的場景。
   • datetime: 日期時間。適用于需要日期和時間的場景，精度到秒。
   • time: 時間（精確到1微秒）。
   • time(0): 時間（精確到1秒）。
   • timestamp: 時間戳（精確到1微秒）。適用于需要時間戳的場景。
   • timestamp(0): 時間戳（精確到1秒）。
   • timestamptz: 帶時區的日期和時間。注意：時區處理需要根據實際應用范圍考慮。

4. 貨幣類型

   • decimal(p, s): 精確財務場景，如decimal(15, 6)，精度s。推薦用于需要高度精確的財務計算。
   • money: 固定兩位小數精度貨幣金額，±2233720368547758.07。注意：適用于不需要自定義精度的貨幣存儲。

5. 邏輯類型

   • boolean: 狀態/開關/條件判斷，如true、false、unknown。

6. JSON類型

   • json: 用于存儲JSON對象或數組，文本格式。注意：可能導致存儲膨脹和查詢復雜性增加。
   • jsonb: 用于檢索，支持索引和高效查詢，二進制格式。注意：雖然查詢性能高，但也同樣面臨存儲膨脹的風險。

7. 其他類型

   • inet: IPv4或IPv6地址。
   • cidr: 網絡地址。
   • macaddr: MAC地址。
   • set: 多選字符集集合。
   • xml: 格式化XML數據，最大2G。注意：存儲大量XML數據可能影響性能。
   • table: 結果集。
   • bytea: 用于存儲大型二進制對象，如圖形。注意：應考慮存儲效率和訪問速度。
   • real: 單精度浮點數。
   • double precision: 雙精度浮點數。
   • enum: 枚舉類型，增強安全性及明確語義。注意：變更枚舉類型可能需要修改表結構，謹慎操作。

高級特性

索引進階

-- 創建索引
CREATE INDEX index_name ON table_name (column_name);-- 刪除索引
DROP INDEX index_name;-- 查看索引
SELECT indexname, indexdef FROM pg_indexes WHERE tablename='table_name';-- 重建索引
REINDEX INDEX index_name;

PostgreSQL 支持多種類型的索引，如 B-tree、Hash索引等。默認的 B-tree 索引適用于大多數情況，有些特殊應用場景可能需要其他類型的索引

CREATE INDEX index_name ON table_name USING gin (column_name);-- 復合索引 多個列創建
CREATE INDEX index_name ON table_name (column1, column2);-- 部分索引 基于條件創建索引
CREATE INDEX index_name ON table_name (column_name) WHERE condition;-- 并行索引
CREATE INDEX index_name ON table_name USING method (column_name) WITH (parallel_workers = 4);

模式（Schema）

模式是PostgreSQL中用于組織數據庫對象（如表、視圖、索引等）的邏輯容器。一個數據庫可以包含多個模式，每個模式可以包含多個對象。模式的主要作用包括：

• 命名空間管理：避免對象名稱沖突，例如在不同模式中可以存在同名的表。
• 權限控制：可以為模式設置獨立的權限，控制用戶對模式內對象的訪問。
• 邏輯分組：將相關的對象組織在一起，便于管理和維護。

創建和使用模式的示例：

CREATE SCHEMA my_schema;
CREATE TABLE my_schema.my_table (id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL
);

在查詢時，可以通過模式名限定對象：

SELECT * FROM my_schema.my_table;

表分區

什么是分區表，何時需要分區？

表分區（Partitioning）指的是將大表拆分成多個小的物理分塊，每個小表稱為一個“分區”。減少數據庫對表的查詢時間及物理資源消耗

當表的大小應超過數據庫服務器的物理內存時，就需要考慮將表進行分區。

優點

• 查詢性能高

  • 頻繁查詢的數據將會放在單個分區或者在少量的分區中。
  • 當表分區后，頻繁使用的索引將會存放在內存中。
  • 當查詢或更新單個分區的大量數據時，將會使用分區的順序掃描而不是使用索引，從而提高性能

• 可以通過添加或移除分區來完成批量加載和刪除。

• 減少批量 DELETE ，避免 VACUUM。

• 通過使用DROP TABLE或ALTER TABLE DETACH PARTITION刪除單個分區比批量操作要快得多。

• 查詢腳本可保持不變，對業務開發來說是透明的。

• 具有更高的可用性，比如一個分區的的數據頁面損壞，不會影響其它分區。

分區類型

PostgreSQL內置支持以下幾種分區方法：

聲明式分區策略	常用場景
范圍分區	適合時間序列數據等有順序特征的數據。
列表分區	適用于有離散值的數據，如分類或狀態。
哈希分區	適合均勻分布數據。
復合分區	結合兩種或多種分區方式。可以先按范圍分區，再在每個范圍內按列表或哈希分區。

💡注意：選擇分區方式時，要考慮數據是否有明顯的范圍、離散值或均勻性；常用的查詢條件是什么，能否利用分區來提高性能；不同的分區策略在維護和管理上復雜度不同。綜合以上來選擇最適合的分區方式。

下面將演示如何創建與使用范圍分區、列表分區、哈希分區。前提已安裝好 postgresql ，并服務能正常運行。如未安裝 postgresql 數據庫可參考以下文章：

https://www.modb.pro/db/1846154339788881920

范圍分區

💡范圍分區基于某個鍵列或列集合，將表數據劃分為不重疊的范圍。例如，按日期范圍劃分，或按業務對象的ID范圍劃分。范圍分區的邊界是下限包含、上限排除的方式。例如，一個分區的范圍為1至10，下一個分區為10至20，那么值10歸屬于第二個分區。

CREATE TABLE sales (sale_id serial,sale_date date,amount numeric
) PARTITION BY RANGE (sale_date);CREATE TABLE sales_2022 PARTITION OF salesFOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

列表分區

列表分區通過明確指定每個分區中包含的鍵值進行劃分，適合數據量不大且需要區分度高的情況，例如按地域或類別分區。

CREATE TABLE employees (emp_id serial,emp_country text
) PARTITION BY LIST (emp_country);CREATE TABLE employees_usa PARTITION OF employeesFOR VALUES IN ('USA');

哈希分區

哈希分區通過給定模數（modulus）和余數（remainder）劃分表數據。PostgreSQL會根據分區鍵的哈希值對模數取余，將數據分配到相應的分區，適合負載均衡的場景。

CREATE TABLE logs (log_id serial,log_date date
) PARTITION BY HASH (log_id);CREATE TABLE logs_part_1 PARTITION OF logsFOR VALUES WITH (MODULUS 4, REMAINDER 0);

分區表總結

• 選擇合適的分區策略：根據具體的業務場景和條件查詢頻率，選擇合適的分區方式和分區鍵。

• 分區數控制：分區數量并不是越多越好，通過實際業務查詢測試來確定分區的合理數量。

• 索引優化

  • 在分區鍵上創建索引，確保查詢可以快速定位到具體的分區。
  • 創建局部索引。

• 避免跨分區查詢。

• 自動化分區維護：使用觸發器（Trigger）或第三方工具（如 pg_partman）來自動創建新的分區表。

• 定期運行 VACUUM 清理死鎖數據。

• 對特定分區進行 ANALYZE，提高查詢計劃的準確性。

• 使用并行查詢：設置 max_parallel_workers_per_gather 等參數。

分區選擇與設計原則

選擇分區方法時應考慮以下因素：

• 數據訪問模式：例如，如果數據隨時間累積，則范圍分區是一個適合的選擇。
• 查詢性能：如果應用程序主要查詢少數特定值，列表分區可以顯著提高性能。
• 負載均衡需求：若需均衡數據分布，避免單一分區的數據量過大，可以選擇哈希分區。

若內置分區無法滿足需求，可考慮使用繼承（Inheritance）和UNION ALL視圖來手動實現分區。雖然此方法靈活，但會失去部分性能優勢。

總結

PostgreSQL表分區為數據庫優化帶來了極大的靈活性。通過合理選擇分區類型，您可以有效提升查詢性能并簡化數據管理流程。掌握分區的原理和使用場景后，在設計大數據表時能夠充分利用分區的優勢，實現數據庫性能的最大化。