背景
PostgreSQL的功能非常強大，但是要把PostgreSQL用好，開發人員是非常關鍵的。
下面将針對PostgreSQL資料庫原理與特性，輸出一份開發規範，希望可以減少大家在使用PostgreSQL資料庫過程中遇到的困惑。
目标是将PostgreSQL的功能、性能發揮好，她好我也好。
https://github.com/digoal/blog/blob/master/201609/20160926_01.md#postgresql-%E4%BD%BF%E7%94%A8%E8%A7%84%E8%8C%83 PostgreSQL 使用規範
https://github.com/digoal/blog/blob/master/201609/20160926_01.md#%E5%91%BD%E5%90%8D%E8%A7%84%E8%8C%83 命名規範
【強制】庫名、表名限制命名長度，建議表名及字段名字元總長度小于等于63。
【強制】對象名（表名、列名、函數名、視圖名、序列名、等對象名稱）規範，對象名務必隻使用小寫字母，下劃線，數字。不要以pg開頭，不要以數字開頭，不要使用保留字。
保留字參考
https://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html【強制】query中的别名不要使用 "小寫字母，下劃線，數字" 以外的字元，例如中文。
【推薦】主鍵索引應以 pk_ 開頭， 唯一索引要以 uk_ 開頭，普通索引要以 idx_ 打頭。
【推薦】臨時表以 tmp_ 開頭，子表以規則結尾，例如按年分區的主表如果為tbl, 則子表為tbl_2016，tbl_2017，。。。
【推薦】庫名最好以部門名字開頭 + 功能，如 xxx_yyy，xxx_zzz，便于辨識， 。。。
【推薦】庫名最好與應用名稱一緻，或便于辨識。
【推薦】不建議使用public schema(不同業務共享的對象可以使用public schema)，應該為每個應用配置設定對應的schema，schema_name最好與user name一緻。
【推薦】comment不要使用中文，因為編碼可能不一樣，如果存進去和讀取時的編碼不一緻，導緻可讀性不強。 pg_dump時也必須與comment時的編碼一緻，否則可能亂碼。
https://github.com/digoal/blog/blob/master/201609/20160926_01.md#%E8%AE%BE%E8%AE%A1%E8%A7%84%E8%8C%83 設計規範
【強制】多表中的相同列，以及有JOIN需求的列，必須保證列名一緻，資料類型一緻。
【強制】btree索引字段不建議超過2000位元組，如果有超過2000位元組的字段需要建索引，建議使用函數索引（例如哈希值索引），或者使用分詞索引。
【強制】使用外鍵時，如果你使用的PG版本沒有自動建立fk的索引，則必須要對foreign key手工建立索引，否則可能影響references列的更新或删除性能。
例如
postgres=# create table tbl(id int primary key,info text);
CREATE TABLE
postgres=# create table tbl1(id int references tbl(id), info text);
CREATE TABLE
postgres=# \d tbl
Table "public.tbl"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
info | text |
Indexes:
"tbl_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "tbl1" CONSTRAINT "tbl1_id_fkey" FOREIGN KEY (id) REFERENCES tbl(id)
postgres=# \d tbl1
Table "public.tbl1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
info | text |
Foreign-key constraints:
"tbl1_id_fkey" FOREIGN KEY (id) REFERENCES tbl(id)
postgres=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+----------+-------+----------+-------
public | tbl_pkey | index | postgres | tbl
(1 row)
postgres=# create index idx_tbl1_id on tbl1(id);
CREATE INDEX 【強制】對于頻繁更新的表，建議建表時指定表的fillfactor=85，每頁預留15%的空間給HOT更新使用。
postgres=# create table test123(id int, info text) with(fillfactor=85);
CREATE TABLE 【強制】索引null的位置定義必須與排序定義一緻，否則可能導緻索引不能使用。
《PostgreSQL 資料庫NULL值的預設排序行為與查詢、索引定義規範 - nulls first\last, asc\desc》【強制】表結構中字段定義的資料類型與應用程式中的定義保持一緻，表之間字段校對規則一緻，避免報錯或無法使用索引的情況發生。
說明：
(1).比如A表user_id字段資料類型定義為varchar，但是SQL語句查詢為 where user_id=1234；
【推薦】如何保證分區表的主鍵序列全局唯一。
使用多個序列，每個序列的步調不一樣，或者每個序列的範圍不一樣即可。
postgres=# create sequence seq_tab1 increment by 10000 start with 1;
CREATE SEQUENCE
postgres=# create sequence seq_tab2 increment by 10000 start with 2;
CREATE SEQUENCE
postgres=# create sequence seq_tab3 increment by 10000 start with 3;
CREATE SEQUENCE
postgres=# create table tab1 (id int primary key default nextval('seq_tab1') check(mod(id,10000)=1), info text);
CREATE TABLE
postgres=# create table tab2 (id int primary key default nextval('seq_tab2') check(mod(id,10000)=2), info text);
CREATE TABLE
postgres=# create table tab3 (id int primary key default nextval('seq_tab3') check(mod(id,10000)=3), info text);
CREATE TABLE
postgres=# insert into tab1 (info) select generate_series(1,10);
INSERT 0 10
postgres=# insert into tab2 (info) select generate_series(1,10);
INSERT 0 10
postgres=# insert into tab3 (info) select generate_series(1,10);
INSERT 0 10
postgres=# select * from tab1;
id | info
-------+------
1 | 1
10001 | 2
20001 | 3
30001 | 4
40001 | 5
50001 | 6
60001 | 7
70001 | 8
80001 | 9
90001 | 10
(10 rows)
postgres=# select * from tab2;
id | info
-------+------
2 | 1
10002 | 2
20002 | 3
30002 | 4
40002 | 5
50002 | 6
60002 | 7
70002 | 8
80002 | 9
90002 | 10
(10 rows)
postgres=# select * from tab3;
id | info
-------+------
3 | 1
10003 | 2
20003 | 3
30003 | 4
40003 | 5
50003 | 6
60003 | 7
70003 | 8
80003 | 9
90003 | 10
(10 rows) postgres=# create sequence seq_tb1 increment by 1 minvalue 1 maxvalue 100000000 start with 1 no cycle ;
CREATE SEQUENCE
postgres=# create sequence seq_tb2 increment by 1 minvalue 100000001 maxvalue 200000000 start with 100000001 no cycle ;
CREATE SEQUENCE
postgres=# create sequence seq_tb3 increment by 1 minvalue 200000001 maxvalue 300000000 start with 200000001 no cycle ;
CREATE SEQUENCE
postgres=# create table tb1(id int primary key default nextval('seq_tb1') check(id >=1 and id<=100000000), info text);
CREATE TABLE
postgres=# create table tb2(id int primary key default nextval('seq_tb2') check(id >=100000001 and id<=200000000), info text);
CREATE TABLE
postgres=# create table tb3(id int primary key default nextval('seq_tb3') check(id >=200000001 and id<=300000000), info text);
CREATE TABLE
postgres=# insert into tb1 (info) select * from generate_series(1,10);
INSERT 0 10
postgres=# insert into tb2 (info) select * from generate_series(1,10);
INSERT 0 10
postgres=# insert into tb3 (info) select * from generate_series(1,10);
INSERT 0 10
postgres=# select * from tb1;
id | info
----+------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 rows)
postgres=# select * from tb2;
id | info
-----------+------
100000001 | 1
100000002 | 2
100000003 | 3
100000004 | 4
100000005 | 5
100000006 | 6
100000007 | 7
100000008 | 8
100000009 | 9
100000010 | 10
(10 rows)
postgres=# select * from tb3;
id | info
-----------+------
200000001 | 1
200000002 | 2
200000003 | 3
200000004 | 4
200000005 | 5
200000006 | 6
200000007 | 7
200000008 | 8
200000009 | 9
200000010 | 10
(10 rows) 【推薦】建議有定期曆史資料删除需求的業務，表按時間分區，删除時不要使用DELETE操作，而是DROP或者TRUNCATE對應的表。
【推薦】為了全球化的需求，所有的字元存儲與表示，均以UTF-8編碼，那麼字元計數方法注意：
計算字元長度
postgres=# select length('阿裡巴巴');
length
--------
4
(1 row) postgres=# select octet_length('阿裡巴巴');
octet_length
--------------
12
(1 row) 其他長度相關接口
Schema | Name | Result data type | Argument data types | Type
------------+------------------------+------------------+---------------------+--------
pg_catalog | array_length | integer | anyarray, integer | normal
pg_catalog | bit_length | integer | bit | normal
pg_catalog | bit_length | integer | bytea | normal
pg_catalog | bit_length | integer | text | normal
pg_catalog | char_length | integer | character | normal
pg_catalog | char_length | integer | text | normal
pg_catalog | character_length | integer | character | normal
pg_catalog | character_length | integer | text | normal
pg_catalog | json_array_length | integer | json | normal
pg_catalog | jsonb_array_length | integer | jsonb | normal
pg_catalog | length | integer | bit | normal
pg_catalog | length | integer | bytea | normal
pg_catalog | length | integer | bytea, name | normal
pg_catalog | length | integer | character | normal
pg_catalog | length | double precision | lseg | normal
pg_catalog | length | double precision | path | normal
pg_catalog | length | integer | text | normal
pg_catalog | length | integer | tsvector | normal
pg_catalog | lseg_length | double precision | lseg | normal
pg_catalog | octet_length | integer | bit | normal
pg_catalog | octet_length | integer | bytea | normal
pg_catalog | octet_length | integer | character | normal
pg_catalog | octet_length | integer | text | normal 【推薦】對于值與堆表的存儲順序線性相關的資料，如果通常的查詢為範圍查詢，建議使用BRIN索引。
例如流式資料，時間字段或自增字段，可以使用BRIN索引，減少索引的大小，加快資料插入速度。
create index idx on tbl using brin(id); 【推薦】設計時應盡可能選擇合适的資料類型，能用數字的堅決不用字元串，能用樹類型的，堅決不用字元串。 使用好的資料類型，可以使用資料庫的索引，操作符，函數，提高資料的查詢效率。
PostgreSQL支援的資料類型如下
精确的數字類型
浮點
貨币
字元串
字元
位元組流
日期
時間
布爾
枚舉
幾何
網絡位址
比特流
文本
UUID
XML
JSON
數組
複合類型
範圍類型
對象
行号
大對象
ltree 樹結構類型
cube 多元類型
earth 地球類型
hstore KV類型
pg_trgm 相似類型
PostGIS（點、線段、面、路徑、經緯度、raster、拓撲、。。。。。。）
【推薦】應該盡量避免全表掃描(除了大資料量掃描的資料分析)，PostgreSQL支援幾乎所有資料類型的索引。
索引接口包括
btree
hash
gin
gist
sp-gist
brin
rum (擴充接口)
bloom (擴充接口)
【推薦】對于網絡複雜并且RT要求很高的場景，如果業務邏輯冗長，應該盡量減少資料庫和程式之間的互動次數，盡量使用資料庫存儲過程(如plpgsql)，或内置的函數。
PostgreSQL内置的plpgsql函數語言功能非常強大，可以處理複雜的業務邏輯。
PostgreSQL内置了非常多的函數，包括分析函數，聚合函數，視窗函數，普通類型函數，複雜類型函數，數學函數，幾何函數，。。。等。
【推薦】應用應該盡量避免使用資料庫觸發器，這會使得資料處理邏輯複雜，不便于調試。
【推薦】如果應用經常要通路較大結果集的資料（例如100條），可能造成大量的離散掃描。
建議想辦法将資料聚合成1條，例如經常要按ID通路這個ID的資料，建議可以定期按ID聚合這些資料，查詢時傳回的記錄數越少越快。
如果無法聚合，建議使用IO較好的磁盤。
【推薦】流式的實時統計，為了防止并行事務導緻的統計空洞，建議業務層按分表并行插入，單一分表串行插入。
table1, table2, ...table100;
每個線程負責一張表的插入，統計時可以按時間或者表的自增ID進行統計。
select xxx from table1 where id>=上一次統計的截至ID group by yyy;
【推薦】範圍查詢，應該盡量使用範圍類型，以及GIST索引，提高範圍檢索的查詢性能。
使用範圍類型存儲IP位址段，使用包含的GIST索引檢索，性能比兩個字段的between and提升20多倍。
CREATE TABLE ip_address_pool_3 (
id serial8 primary key ,
start_ip inet NOT NULL ,
end_ip inet NOT NULL ,
province varchar(128) NOT NULL ,
city varchar(128) NOT NULL ,
region_name varchar(128) NOT NULL ,
company_name varchar(128) NOT NULL ,
ip_decimal_segment int8range
) ;
CREATE INDEX ip_address_pool_3_range ON ip_address_pool_3 USING gist (ip_decimal_segment);
select province,ip_decimal_segment from ip_address_pool_3 where ip_decimal_segment @> :ip::int8;
【推薦】未使用的大對象，一定要同時删除資料部分，否則大對象資料會一直存在資料庫中，與記憶體洩露類似。
vacuumlo可以用來清理未被引用的大對象資料。
【推薦】對于固定條件的查詢，可以使用部分索引，減少索引的大小，同時提升查詢效率。
create index idx on tbl (col) where id=1; 【推薦】對于經常使用表達式作為查詢條件的語句，可以使用表達式或函數索引加速查詢。
create index idx on tbl ( exp ); 【推薦】如果需要調試較為複雜的邏輯時，不建議寫成函數進行調試，可以使用plpgsql的online code.
$$ declare begin -- logical code end; $$; 【推薦】當業務有中文分詞的查詢需求時，建議使用PostgreSQL的分詞插件zhparser或jieba，使用者還可以通過接口自定義詞組。
建議在分詞字段使用gin索引，提升分詞比對的性能。
【推薦】當使用者有規則表達式查詢，或者文本近似度查詢的需求時，建議對字段使用trgm的gin索引，提升近似度比對或規則表達式比對的查詢效率，同時覆寫了前後模糊的查詢需求。如果沒有建立trgm gin索引，則不推薦使用前後模糊查詢例如like %xxxx%。
【推薦】gin索引可以支援多值類型、數組、全文檢索等的倒排高效查詢。但是對于PostgreSQL 9.4以及以下版本，建議設定表的fillfactor=70，可以解決高并發寫入時的鎖問題。
《PostgreSQL 10 GIN索引鎖優化》 《PostgreSQL 9種索引的原理和應用場景》【推薦】當使用者有prefix或者 suffix的模糊查詢需求時，可以使用索引，或反轉索引達到提速的需求。
如
select * from tbl where col ~ '^abc'; -- 字首查詢
select * from tbl where reverse(col) ~ '^def'; -- 字尾查詢使用反轉函數索引 【推薦】使用者應該對頻繁通路的大表（通常指超過8GB的表，或者超過1000萬記錄的表）進行分區，進而提升查詢的效率、更新的效率、備份與恢複的效率、建索引的效率等等，（PostgreSQL支援多核建立索引後，可以适當将這個限制放大）。
單表過大，還需要考慮freeze等問題。
《HTAP資料庫 PostgreSQL 場景與性能測試之 45 - (OLTP) 資料量與性能的線性關系(10億+無衰減), 暨單表多大需要分區》【推薦】對于頻繁通路的分區表，建議分區數目不要太多（至少在PostgreSQL 10前，還有此問題），分區數目過多，可能導緻優化器的開銷巨大，影響普通SQL，prepared statement 的BIND過程等。
《PostgreSQL 查詢涉及分區表過多導緻的性能問題 - 性能診斷與優化(大量BIND, spin lock, SLEEP程序)》【推薦】使用者在設計表結構時，建議規劃好，避免經常需要添加字段，或者修改字段類型或長度。 某些操作可能觸發表的重寫，例如加字段并設定預設值，修改字段的類型。
如果使用者确實不好規劃結構，建議使用jsonb資料類型存儲使用者資料。
https://github.com/digoal/blog/blob/master/201609/20160926_01.md#query-%E8%A7%84%E8%8C%83 QUERY 規範
【強制】不要使用count(列名)或count(常量)來替代count(
*
)，count(
*
)就是SQL92定義的标準統計行數的文法，跟資料庫無關，跟NULL和非NULL無關。
說明：count(
*
)會統計NULL值（真實行數），而count(列名)不會統計。
【強制】count(多列列名)時，多列列名必須使用括号，例如count( (col1,col2,col3) )。注意多列的count，即使所有列都為NULL，該行也被計數，是以效果與count(
*
)一緻。
postgres=# create table t123(c1 int,c2 int,c3 int);
CREATE TABLE
postgres=# insert into t123 values (null,null,null),(null,null,null),(1,null,null),(2,null,null),(null,1,null),(null,2,null);
INSERT 0 6
postgres=# select count((c1,c2)) from t123;
count
-------
6
(1 row)
postgres=# select count((c1)) from t123;
count
-------
2
(1 row) 【強制】count(distinct col) 計算該列的非NULL不重複數量，NULL不被計數。
postgres=# select count(distinct (c1)) from t123;
count
-------
2
(1 row) 【強制】count(distinct (col1,col2,...) ) 計算多列的唯一值時，NULL會被計數，同時NULL與NULL會被認為是想同的。
postgres=# select count(distinct (c1,c2)) from t123;
count
-------
5
(1 row)
postgres=# select count(distinct (c1,c2,c3)) from t123;
count
-------
5
(1 row) 【強制】count(col)對 "是NULL的col列" 傳回為0，而sum(col)則為NULL。
postgres=# select count(c1),sum(c1) from t123 where c1 is null;
count | sum
-------+-----
0 |
(1 row) 是以注意sum(col)的NPE問題，如果你的期望是當SUM傳回NULL時要得到0，可以這樣實作
SELECT coalesce( SUM(g)), 0, SUM(g) ) FROM table; 【強制】NULL是UNKNOWN的意思，也就是不知道是什麼。 是以NULL與任意值的邏輯判斷都傳回NULL。
NULL<>NULL 的傳回結果是NULL，不是false。
NULL=NULL的傳回結果也是NULL，不是true。
NULL值與任何值的比較都為NULL，即NULL<>1，傳回的是NULL，而不是true。
【強制】除非是ETL程式，否則應該盡量避免向用戶端傳回大資料量，若資料量過大，應該考慮相應需求是否合理。
【強制】任何地方都不要使用 select
*
from t ，用具體的字段清單代替
*
，不要傳回用不到的任何字段。另外表結構發生變化也容易出現問題。
https://github.com/digoal/blog/blob/master/201609/20160926_01.md#%E7%AE%A1%E7%90%86%E8%A7%84%E8%8C%83 管理規範
【強制】資料訂正時，删除和修改記錄時，要先select，避免出現誤删除，确認無誤才能送出執行。
【強制】DDL操作(以及類似的可能擷取大鎖的操作，譬如vacuum full, create index等)必須設定鎖等待，可以防止堵塞所有其他與該DDL鎖對象相關的QUERY。
begin;
set local lock_timeout = '10s';
-- DDL query;
end; 【強制】使用者可以使用explain analyze檢視實際的執行計劃，但是如果需要檢視的執行計劃設計資料的變更，必須在事務中執行explain analyze，然後復原。
begin;
explain analyze query;
rollback; 【強制】如何并行建立索引，不堵塞表的DML，建立索引時加CONCURRENTLY關鍵字，就可以并行建立，不會堵塞DML操作，否則會堵塞DML操作。
create index CONCURRENTLY idx on tbl(id); 【強制】為資料庫通路賬号設定複雜密碼。
說明：密碼由小寫字母，數字、下劃線組成、字母開頭，字母或數字結尾，禁止123456，hello123等簡單密碼。
【強制】業務系統，開發測試賬号，不要使用資料庫超級使用者。非常危險。
【強制】如果資料庫開啟了archive_mode，一定要設定archive_command，同時監控pg_xlog的空間使用情況，避免因為歸檔失敗，導緻xlog不斷堆積，甚至導緻空間占滿。
【強制】如果資料庫配置了standby，并且使用了slot，必須監控備機的延遲，監控slot的狀态（延遲），否則可能導緻主庫XLOG檔案堆積的問題，甚至導緻空間占滿。
【推薦】多個業務共用一個PG叢集時，建議為每個業務建立一個資料庫。 如果業務之間有資料交集，或者事務相關的處理，強烈建議在程式層處理資料的互動。
不能在程式中處理時，可以将多個業務合并到一個庫，但是使用不同的schema将多個業務的對象分開來。
【推薦】應該為每個業務配置設定不同的資料庫賬号，禁止多個業務共用一個資料庫賬号。
【推薦】在發生主備切換後，新的主庫在開放給應用程式使用前，建議使用pg_prewarm預熱之前的主庫shared buffer裡的熱資料。
【推薦】快速的裝載資料的方法，關閉autovacuum, 删除索引，資料導入後，對表進行analyze同時建立索引。
【推薦】如何加快建立索引的速度，調大maintenance_work_mem，可以提升建立索引的速度，但是需要考慮實際的可用記憶體。
begin;
set local maintenance_work_mem='2GB';
create index idx on tbl(id);
end; 【推薦】如何防止長連接配接，占用過多的relcache, syscache。
當系統中有很多張表時，中繼資料會比較龐大，例如1萬張表可能有上百MB的中繼資料，如果一個長連接配接的會話，通路到了所有的對象，則可能會長期占用這些syscache和relcache。
建議遇到這種情況時，定期釋放長連接配接，重建立立連接配接，例如每個小時釋放一次長連接配接。
PS
阿裡雲的RDS PGSQL版本提供了主動釋放syscache和 relcache的接口，不需要斷開連接配接。
【推薦】大批量資料入庫的優化，如果有大批量的資料入庫，建議使用copy文法，或者 insert into table values (),(),...(); 的方式。 提高寫入速度。
【推薦】大批量資料入庫、大批量資料更新、大批量資料删除後，如果沒有開啟autovacuum程序，或者表級層面關閉了autovacuum，那麼建議人為執行一下vacuum verbose analyze table;
【推薦】大批量删除和更新資料時，不建議一個事務中完成，建議分批次操作，以免一次産生較多垃圾。當然如果一定要大批量操作的話，在操作完後，建議使用pg_repack重組表。 建議操作前檢查膨脹率。
https://github.com/digoal/blog/blob/master/201609/20160926_01.md#%E7%A8%B3%E5%AE%9A%E6%80%A7%E4%B8%8E%E6%80%A7%E8%83%BD%E8%A7%84%E8%8C%83 穩定性與性能規範
【強制】在代碼中寫分頁查詢邏輯時，若count為0應直接傳回，避免執行後面的分頁語句。
【強制】遊标使用後要及時關閉。
【強制】兩階段送出的事務，要及時送出或復原，否則可能導緻資料庫膨脹。
【強制】不要使用delete 全表，性能很差，請使用truncate代替，（truncate是DDL語句，注意加鎖等待逾時）。
【強制】應用程式一定要開啟autocommit，同時避免應用程式自動begin事務，并且不進行任何操作的情況發生，某些架構可能會有這樣的問題。
【強制】高并發的應用場合，務必使用綁定變量(prepared statement)，防止資料庫硬解析消耗過多的CPU資源。
【強制】不要使用hash index，目前hash index不寫REDO，在備庫隻有結構，沒有資料，并且資料庫crash後無法恢複。
同時不建議使用unlogged table ，道理同上，但是如果你的資料不需要持久化，則可以考慮使用unlogged table來提升資料的寫入和修改性能。
注意： pg 10開始hash index也支援寫redo log了， 是以pg 10以後， 随便使用hash index。不受此條限制。
【強制】秒殺場景，一定要使用 advisory_lock先對記錄的唯一ID進行鎖定，拿到AD鎖再去對資料進行更新操作。 拿不到鎖時，可以嘗試重試拿鎖。
CREATE OR REPLACE FUNCTION public.f(i_id integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
a_lock boolean := false;
begin
select pg_try_advisory_xact_lock(i_id) into a_lock;
拿到鎖，更新
if a_lock then
update t1 set count=count-1 where id=i_id;
end if;
exception when others then
return;
end;
$function$;
select f(id) from tbl where id=? and count>0; 可以再根據實際情況設計，原理如上即可。
函數可以如傳回布爾，或者唯一ID，或者數字等。
【強制】在函數中，或程式中，不要使用count(
*
)判斷是否有資料，很慢。 建議的方法是limit 1;
select 1 from tbl where xxx limit 1;
if found -- 存在
else -- 不存在 【強制】對于高并發的應用場景，務必使用程式的連接配接池，否則性能會很低下。
如果程式沒有連接配接池，建議在應用層和資料庫之間架設連接配接池，例如使用pgbouncer或者pgpool-II作為連接配接池。
【強制】程式務必有重連機制，如果沒有重連機制，一個長期空閑的連接配接可能會被網絡層裝置當成無效會話強制中斷掉。即使設定tcp_keepalives_idle,tcp_keepalives_interval,tcp_keepalives_count等較短的TCP心跳，也不一定能覆寫所有場景。
建議有重連機制，建議在使用長時間未被使用的連接配接前使用select 1;探測一下是否連接配接正常，如果不正常，則重連。建議使用select 1;作為連接配接的定期心跳。
【強制】當業務有近鄰查詢的需求時，務必對字段建立GIST或SP-GIST索引，加速近鄰查詢的需求。
create index idx on tbl using gist(col);
select * from tbl order by col <-> '(0,100)';
【強制】避免頻繁建立和删除臨時表，以減少系統表資源的消耗，因為建立臨時表會産生中繼資料，頻繁建立，中繼資料可能會出現碎片。
【強制】必須選擇合适的事務隔離級别，不要使用越級的隔離級别，例如READ COMMITTED可以滿足時，就不要使用repeatable read和serializable隔離級别。
【推薦】高峰期對大表添加包含預設值的字段，會導緻表的rewrite，建議隻添加不包含預設值的字段，業務邏輯層面後期處理預設值。
【推薦】在使用空間查詢時，點面包含、相交等查詢，為了提升效率，盡量使用有效面積大的多邊形，如果做不到，可以先對多邊形進行split，同時使用union all合并結果。
《PostgreSQL 空間切割(st_split)功能擴充 - 空間對象網格化 (多邊形GiST優化)》 《PostgreSQL 空間st_contains，st_within空間包含搜尋優化 - 降IO和降CPU(bound box) (多邊形GiST優化)》 《PostgreSQL 黑科技 - 空間聚集存儲, 内窺GIN, GiST, SP-GiST索引》 《PostgreSQL multipolygon 空間索引查詢過濾精簡優化 - IO，CPU放大優化》【推薦】分頁評估，不需要精确分頁數時，請使用快速評估分頁數的方法。
https://yq.aliyun.com/articles/39682CREATE OR REPLACE FUNCTION countit(text)
RETURNS float4
LANGUAGE plpgsql AS
$$DECLARE
v_plan json;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) '||$1
INTO v_plan;
RETURN v_plan #>> '{0,Plan,"Plan Rows"}';
END;
$$;
postgres=# create table t1234(id int, info text);
CREATE TABLE
postgres=# insert into t1234 select generate_series(1,1000000),'test';
INSERT 0 1000000
postgres=# analyze t1234;
ANALYZE
postgres=# select countit('select * from t1234 where id<1000');
countit
---------
954
(1 row)
postgres=# select countit('select * from t1234 where id between 1 and 1000 or (id between 100000 and 101000)');
countit
---------
1931
(1 row) 【推薦】分頁優化，建議通過遊标傳回分頁結果，避免越後面的頁傳回越慢的情況。
postgres=# declare cur1 cursor for select * from sbtest1 where id between 100 and 1000000 order by id;
DECLARE CURSOR
Time: 0.422 ms 擷取資料
postgres=# fetch 100 from cur1;
。。。 如果要前滾頁，加SCROLL打開遊标
declare cur1 SCROLL cursor for select * from sbtest1 where id between 100 and 1000000 order by id; 【推薦】可以預估SQL執行時間的操作，建議設定語句級别的逾時，可以防止雪崩，也可以防止長時間持鎖。
例如設定事務中執行的每條SQL逾時時間為10秒
begin;
set local statement_timeout = '10s';
-- query;
end; 【推薦】TRUNCATE TABLE 在功能上與不帶WHERE 子句的DELETE 語句相同：二者均删除表中的全部行。但TRUNCATE TABLE 比DELETE 速度快，且使用的系統和事務日志資源少，但是TRUNCATE是DDL，鎖粒度很大，故不建議在開發代碼中使用DDL語句，除非加了lock_timeout鎖逾時的會話參數或事務參數。
【推薦】PostgreSQL支援DDL事務，支援復原DDL，建議将DDL封裝在事務中執行，必要時可以復原，但是需要注意事務的長度，避免長時間堵塞DDL對象的讀操作。
【推薦】如果使用者需要在插入資料和，删除資料前，或者修改資料後馬上拿到插入或被删除或修改後的資料，建議使用insert into .. returning ..; delete .. returning ..或update .. returning ..; 文法。減少資料庫互動次數。
postgres=# create table tbl4(id serial, info text);
CREATE TABLE
postgres=# insert into tbl4 (info) values ('test') returning *;
id | info
----+------
1 | test
(1 row)
INSERT 0 1
postgres=# update tbl4 set info='abc' returning *;
id | info
----+------
1 | abc
(1 row)
UPDATE 1
postgres=# delete from tbl4 returning *;
id | info
----+------
1 | abc
(1 row)
DELETE 1 推薦】自增字段建議使用序列，序列分為2位元組，4位元組，8位元組幾種(serial2,serial4,serial8)。按實際情況選擇。禁止使用觸發器産生序列值。
postgres=# create table tbl4(id serial, info text);
CREATE TABLE
【推薦】如果對全表的很多字段有任意字段比對的查詢需求，建議使用行級别全文索引，或行轉數組的數組級别索引。
select * from t where phonenum='digoal' or info ~ 'digoal' or c1='digoal' or ......;
更正為
postgres=# create or replace function f1(text) returns tsvector as $$
select to_tsvector($1);
$$ language sql immutable strict;
CREATE FUNCTION
postgres=# alter function record_out(record) immutable;
ALTER FUNCTION
postgres=# alter function textin(cstring) immutable;
postgres=# create index idx_t_1 on t using gin (f1('jiebacfg'::regconfig,t::text)) ;
CREATE INDEX
postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & post') ;
phonenum | info | c1 | c2 | c3 | c4
----------+------+----+----+----+----
(0 rows)
postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & china') ;
phonenum | info | c1 | c2 | c3 | c4
-------------+-----------------------------+-----+-------+------------------------------+----------------------------
13888888888 | i am digoal, a postgresqler | 123 | china | 中華人民共和國，阿裡巴巴，阿| 2016-04-19 11:15:55.208658
(1 row)
postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿裡巴巴') ;
postgres=# explain select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿裡巴巴') ;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..1.52 rows=1 width=140)
Filter: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿裡巴巴'::text))
(2 rows)
【推薦】中文分詞的token mapping一定要設定，否則對應的token沒有詞典進行處理。
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z WITH simple;
zhparser分詞插件的其他配置
zhparser.punctuation_ignore = f
zhparser.seg_with_duality = f
zhparser.dict_in_memory = f
zhparser.multi_short = f
zhparser.multi_duality = f
zhparser.multi_zmain = f
zhparser.multi_zall = f
參考
https://yq.aliyun.com/articles/7730 http://www.xunsearch.com/scws/docs.php#libscws【推薦】樹形查詢應該使用遞歸查詢，盡量減少資料庫的互動或JOIN。
CREATE TABLE TBL_TEST
(
ID numeric,
NAME text,
PID numeric DEFAULT 0
);
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
從Root往樹末梢遞歸
with recursive t_result as (
select * from tbl_test where id=1
union all
select t2.* from t_result t1 join tbl_test t2 on t1.id=t2.pid
)
select * from t_result;
id | name | pid
----+------+-----
1 | 10 | 0
2 | 11 | 1
4 | 12 | 1
5 | 121 | 2
(4 rows)
從末梢往樹ROOT遞歸
select * from tbl_test where id=5
select t2.* from t_result t1 join tbl_test t2 on t1.pid=t2.id
(3 rows)
樹形結構的注意事項
1. 一定要能跳出循環，即循環子句查不到結果為止。
2. 樹形結構如果有多個值，則會出現查到的結果比實際的多的情況，這個業務上是需要保證不出現重複的。
【推薦】應盡量避免長事務，長事務可能造成垃圾膨脹。
【推薦】如果業務有多個次元的分析需求，應該盡量使用PostgreSQL的多元分析文法，減少資料的重複掃描。
支援的多元分析文法包括
GROUPING SETS, CUBE, ROLLUP
假設有4個業務字段，一個時間字段。
postgres=# create table tab5(c1 int, c2 int, c3 int, c4 int, crt_time timestamp);
生成一批測試資料
postgres=# insert into tab5 select
trunc(100*random()),
trunc(1000*random()),
trunc(10000*random()),
trunc(100000*random()),
clock_timestamp() + (trunc(10000*random())||' hour')::interval
from generate_series(1,1000000);
INSERT 0 1000000
postgres=# select * from tab5 limit 10;
c1 | c2 | c3 | c4 | crt_time
----+-----+------+-------+----------------------------
72 | 46 | 3479 | 20075 | 2017-02-02 14:56:36.854218
98 | 979 | 4491 | 83012 | 2017-06-13 08:56:36.854416
54 | 758 | 5838 | 45956 | 2016-09-18 02:56:36.854427
3 | 67 | 5148 | 74754 | 2017-01-01 01:56:36.854431
42 | 650 | 7681 | 36495 | 2017-06-20 15:56:36.854435
4 | 472 | 6454 | 19554 | 2016-06-18 19:56:36.854438
82 | 922 | 902 | 17435 | 2016-07-21 14:56:36.854441
68 | 156 | 1028 | 13275 | 2017-07-16 10:56:36.854444
0 | 674 | 7446 | 59386 | 2016-07-26 09:56:36.854447
0 | 629 | 2022 | 52285 | 2016-11-04 13:56:36.85445
(10 rows)
建立一個統計結果表, 其中bitmap表示統計的字段組合, 用位置符0,1表示是否統計了該次元
create table stat_tab5 (c1 int, c2 int, c3 int, c4 int, time1 text, time2 text, time3 text, time4 text, cnt int8, bitmap text);
生成業務字段任意次元組合+4組時間任選一組的組合統計
PS (如果業務字段有空的情況，建議統計時用coalesce轉一下，確定不會統計到空的情況)
insert into stat_tab5
select c1,c2,c3,c4,t1,t2,t3,t4,cnt,
'' ||
case when c1 is null then 0 else 1 end ||
case when c2 is null then 0 else 1 end ||
case when c3 is null then 0 else 1 end ||
case when c4 is null then 0 else 1 end ||
case when t1 is null then 0 else 1 end ||
case when t2 is null then 0 else 1 end ||
case when t3 is null then 0 else 1 end ||
case when t4 is null then 0 else 1 end
from
select c1,c2,c3,c4,
to_char(crt_time, 'yyyy') t1,
to_char(crt_time, 'yyyy-mm') t2,
to_char(crt_time, 'yyyy-mm-dd') t3,
to_char(crt_time, 'yyyy-mm-dd hh24') t4,
count(*) cnt
from tab5
group by
cube(c1,c2,c3,c4),
grouping sets(to_char(crt_time, 'yyyy'), to_char(crt_time, 'yyyy-mm'), to_char(crt_time, 'yyyy-mm-dd'), to_char(crt_time, 'yyyy-mm-dd hh24'))
t;
INSERT 0 49570486
Time: 172373.714 ms
在bitmap上建立索引友善取資料
create index idx_stat_tab5_bitmap on stat_tab5 (bitmap);
使用者勾選幾個次元，取出資料
c1,c3,c4,t3 = bitmap(10110010)
postgres=# select c1,c3,c4,time3,cnt from stat_tab5 where bitmap='10110010' limit 10;
c1 | c3 | c4 | time3 | cnt
----+----+-------+------------+-----
41 | 0 | 30748 | 2016-06-04 | 1
69 | 0 | 87786 | 2016-06-04 | 1
70 | 0 | 38805 | 2016-06-04 | 1
79 | 0 | 65892 | 2016-06-08 | 1
51 | 0 | 13615 | 2016-06-11 | 1
47 | 0 | 42196 | 2016-06-28 | 1
45 | 0 | 54736 | 2016-07-01 | 1
50 | 0 | 21605 | 2016-07-02 | 1
46 | 0 | 40888 | 2016-07-16 | 1
41 | 0 | 90258 | 2016-07-17 | 1
Time: 0.528 ms
postgres=# select * from stat_tab5 where bitmap='00001000' limit 10;
c1 | c2 | c3 | c4 | time1 | time2 | time3 | time4 | cnt | bitmap
----+----+----+----+-------+-------+-------+-------+--------+----------
| | | | 2016 | | | | 514580 | 00001000
| | | | 2017 | | | | 485420 | 00001000
Time: 0.542 ms
【推薦】對于有UV查詢需求的場景（例如count(distinct xx) where time between xx and xx），如果要求非常快的響應速度，但是對精确度要求不高時，建議可以使用PostgreSQL的估值資料類型HLL。
create table access_date (acc_date date unique, userids hll);
insert into access_date select current_date, hll_add_agg(hll_hash_integer(user_id)) from generate_series(1,10000) t(user_id);
select *, total_users-coalesce(lag(total_users,1) over (order by rn),0) AS new_users
FROM
SELECT acc_date, row_number() over date as rn,#hll_union_agg(userids) OVER date as total_users
FROM access_date
WINDOW date AS (ORDER BY acc_date ASC ROWS UNBOUNDED PRECEDING)
) t;
【推薦】PostgreSQL 的insert on conflict文法如下
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
postgres=# insert into tbl values (1,'info') on conflict on constraint tbl_pkey do update set info=excluded.info;
INSERT 0 1
【推薦】如果使用者經常需要通路一張大表的某些資料，為了提升效率可以使用索引，但是如果這個資料還需要被用于更複雜的與其他表的JOIN操作，則可以使用物化視圖來提升性能。
同時物化視圖還可以被用于OLAP場景，例如統計後的資料可以固化到物化視圖中，以便快速的檢索。
CREATE MATERIALIZED VIEW mv_tbl as select xx,xx,xx from tbl where xxx with data;
增量重新整理物化視圖
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_tbl with data;
【推薦】不建議對寬表頻繁的更新，原因是PG目前的引擎是多版本的，更新後會産生新的版本，如果對寬表的某幾個少量的字段頻繁更新，其實是存在寫放大的。
建議将此類寬表的不更新或更新不頻繁的列與頻繁更新的列拆分成兩張表，通過PK進行關聯。
查詢是通過PK關聯查詢出結果即可。
【推薦】使用視窗查詢減少資料庫和應用的互動次數。
有一個這樣的表，記錄如下:
id | company | product
----+---------+---------
1 | c1 | p1
1 | c1 | p2
1 | b1 | p2
1 | c2 | p2
2 | c3 | p3
需要找出某個産品，這個産品隻有一個公司生産。
select distinct product from (select min(company) over(partition by product) m1,max(company) over(partition by product) m2, product from tbl) t where m2<>m1;
又如，根據指定視窗，查詢目前行與以視窗為範圍取其avg,max,min,sum,count,offset,rank,dist等，同時輸出目前行。例如與第一名的差距，與前一名的差距，與全國第一名的差距，與全班第一名的差距，同時還輸出目前記錄的詳情。
【推薦】應該盡量在業務層面避免死鎖的産生，例如一個使用者的資料，盡量在一個線程内處理，而不要跨線程（即跨資料庫會話處理）。
【推薦】OLTP系統不要頻繁的使用聚合操作，聚合操作消耗較大的CPU與IO資源。例如實時的COUNT操作，如果并發很高，可能導緻CPU資源撐爆。
對于實時性要求不高的場景，可以使用定期操作COUNT，并将COUNT資料緩存在緩存系統中的方式。
【推薦】資料去重的方法，當沒有UK或PK時，如果資料出現了重複，有什麼好的方法去重。或者某個列沒有加唯一限制，但是業務層沒有保證唯一，如何去重？
行級别去重
delete from tbl where ctid not in (select min(ctid) from tbl group by tbl::text);
帶PK的列col級别去重
delete from tbl where pk in (select pk from (select pk,row_number() over(partition by col order by pk) rn from tbl) t where t.rn>1);
不帶PK的列級别去重(以業務邏輯為準，可以選擇其他的條件删除)
delete from tbl where ctid not in (select min(ctid) from tbl group by col);
【推薦】快速讀取随機記錄的方法利用索引列進行優化的方法。
方法1.随機取出n條記錄,以下取出5條随機記錄
digoal=> select * from tbl_user
digoal-> where id in
digoal-> (select floor(random() * (max_id - min_id))::int
digoal(> + min_id
digoal(> from generate_series(1,5),
digoal(> (select max(id) as max_id,
digoal(> min(id) as min_id
digoal(> from tbl_user) s1
digoal(> )
digoal-> limit 5;
id | firstname | lastname | corp | age
--------+-----------+----------+----------+-----
965638 | zhou | digoal | sky-mobi | 27
193491 | zhou | digoal | sky-mobi | 27
294286 | zhou | digoal | sky-mobi | 27
726263 | zhou | digoal | sky-mobi | 27
470713 | zhou | digoal | sky-mobi | 27
(5 rows)
Time: 0.670 ms
方法2. 取出N條連續的随機記錄.(此處用到函數)
digoal=> create or replace function f_get_random (i_range int) returns setof record as $BODY$
digoal$> declare
digoal$> v_result record;
digoal$> v_max_id int;
digoal$> v_min_id int;
digoal$> v_random numeric;
digoal$> begin
digoal$> select random() into v_random;
digoal$> select max(id),min(id) into v_max_id,v_min_id from tbl_user;
digoal$> for v_result in select * from tbl_user where id between (v_min_id+(v_random*(v_max_id-v_min_id))::int) and (v_min_id+(v_random*(v_max_id-v_min_id))::int+i_range)
digoal$> loop
digoal$> return next v_result;
digoal$> end loop;
digoal$> return;
digoal$> end
digoal$> $BODY$ language plpgsql;
CREATE FUNCTION
以下舉例取出10條連續的随機記錄
digoal=> select * from f_get_random(9) as (id bigint,firstname varchar(32),lastname varchar(32),corp varchar(32),age smallint);
694686 | zhou | digoal | sky-mobi | 27
694687 | zhou | digoal | sky-mobi | 27
694688 | zhou | digoal | sky-mobi | 27
694689 | zhou | digoal | sky-mobi | 27
694690 | zhou | digoal | sky-mobi | 27
694691 | zhou | digoal | sky-mobi | 27
694692 | zhou | digoal | sky-mobi | 27
694693 | zhou | digoal | sky-mobi | 27
694694 | zhou | digoal | sky-mobi | 27
694695 | zhou | digoal | sky-mobi | 27
(10 rows)
Time: 0.418 ms
【推薦】線上表結構的變更包括添加字段，索引操作在業務低峰期進行。
【推薦】OLTP系統，在高峰期或高并發期間拒絕長SQL，大事務，大批量。
(1). 長SQL占用大量的資料庫時間和資源，占用連接配接，可能影響正常業務運作。
(2). 大事務，或長事務，可能導緻長時間持鎖，與其他事務産生鎖沖突。
(3). 大批量，大批量在并發事務中增加鎖等待的幾率。
【推薦】查詢條件要和索引比對，例如查詢條件是表達式時，索引也要是表達式索引，查詢條件為列時，索引就是列索引。
【推薦】如何判斷兩個值是不是不一樣（并且将NULL視為一樣的值），使用col1 IS DISTINCT FROM col2
postgres=# select null is distinct from null;
?column?
----------
f
(1 row)
postgres=# select null is distinct from 1;
t
另外還有IS NOT DISTINCT FROM的用法。
【推薦】如果在UDF或online code邏輯中有資料的處理需求時，建議使用遊标進行處理。
do language plpgsql $$
declare
cur refcursor;
rec record;
begin
open cur for select * from tbl where id>1;
loop
fetch cur into rec;
if found then
raise notice '%', rec;
update tbl set info='ab' where current of cur;
-- other query
else
close cur;
exit;
end if;
end loop;
end;
$$;
【推薦】應盡量避免在where 子句中使用!=或<>操作符，否則将引擎放棄使用索引而進行全表掃描。
如果業務确實有這種需求的查詢，可以有幾種優化方法
1. partial index
這個是最有效的方法，可以使用到索引掃描，如果有其他條件，也可以在其他條件的索引上建立partial index.
create index idx1 on tbl (id) where cond1 <> xx;
2. 分區表
使用分區表，如果有!=的查詢條件，PostgreSQL會根據分區限制，避免掃描不需要掃描的表。
3. 限制
set constraint_exclusion=on;
exec query;
在查詢列上有限制的情況下，如果!=或<>與限制違背，則可以提前傳回查詢，不會掃描表。
【推薦】對于經常變更，或者新增，删除記錄的表，應該盡量加快這種表的統計資訊采樣頻率，獲得較實時的采樣，輸出較好的執行計劃。
當垃圾達到表的千分之五時，自動觸發垃圾回收。
當資料變化達到表的百分之一時，自動觸發統計資訊的采集。
當執行垃圾回收時，不等待，當IOPS較好時可以這麼設定。
postgres=# create table t21(id int, info text) with (
autovacuum_enabled=on, toast.autovacuum_enabled=on,
autovacuum_vacuum_scale_factor=0.005, toast.autovacuum_vacuum_scale_factor=0.005,
autovacuum_analyze_scale_factor=0.01, autovacuum_vacuum_cost_delay=0,
toast.autovacuum_vacuum_cost_delay=0);
CREATE TABLE
【推薦】PostgreSQL 對or的查詢條件，會使用bitmap or進行索引的過濾，是以不需要改SQL語句，可以直接使用。
以下查詢都可以走索引
select * from tbl where col1 =1 or col1=2 or col2=1 or ...;
select * from tbl where col1 in (1,2);
【推薦】很多時候用exists 代替in 是一個好的選擇：
select num from a where num in (select num from b);
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)
【推薦】盡量使用數組變量來代替臨時表。如果臨時表有非常龐大的資料時，才考慮使用臨時表。
【推薦】對查詢進行優化，應盡量避免全表掃描，首先應考慮在where 及order by 涉及的列上建立索引。
使用explain可以檢視執行計劃，如果發現執行計劃不優，可以通過索引或者調整QUERY的寫法解決。
begin;
explain (verbose,costs,timing,buffers,analyze) query;
rollback;
......
【推薦】PG優化器可以動态調整JOIN的順序，擷取更好的執行計劃，但是如何強制優化器的顯示JOIN順序呢？
首先PG根據join_collapse_limit的設定，當需要關聯的表的個數超過這個設定時，超出的JOIN數部分不會繼續動态調整JOIN順序。
另外需要注意，如果開啟了GEQO，當JOIN的表(含隐式JOIN,以及子查詢) (full outer join 隻算1)數量超過了geqo_threshold設定的值，則會觸發遺傳算法，可能無法得到最佳的JOIN順序。
要讓優化器固定JOIN順序，首先必須使用顯示的JOIN，其次将join_collapse_limit設定為1，顯示的JOIN順序将被固定，固定JOIN順序可以減少優化器的編排時間，降低頻繁執行多表JOIN帶來的優化階段的CPU開銷。
顯示的JOIN例子
t1 join t2 on (xxx)
隐式的JOIN例子
t1, t2 where xxx
set local join_collapse_limit=1;
set local geqo=off;
postgres=# create table t1(id int, info text);
postgres=# create table t2(id int, info text);
postgres=# create table t3(id int, info text);
postgres=# create table t4(id int, info text);
postgres=# create table t5(id int, info text);
postgres=# create table t6(id int, info text);
postgres=# create table t7(id int, info text);
JOIN順序固定為如下
postgres=# explain select * from t2 join t1 using (id) join t3 using (id) join t4 using (id) join t7 using (id) join t6 using (id) join t5 using (id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=617.21..1482900.86 rows=83256006 width=228)
Merge Cond: (t5.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t5.id
-> Seq Scan on t5 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=529.03..266744.20 rows=13111182 width=216)
-> Merge Join (cost=529.03..233966.24 rows=13111182 width=216)
Merge Cond: (t6.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t6.id
-> Seq Scan on t6 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=440.86..42365.87 rows=2064753 width=180)
-> Merge Join (cost=440.86..37203.99 rows=2064753 width=180)
Merge Cond: (t7.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t7.id
-> Seq Scan on t7 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=352.69..6951.07 rows=325158 width=144)
-> Merge Join (cost=352.69..6138.17 rows=325158 width=144)
Merge Cond: (t4.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t4.id
-> Seq Scan on t4 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=264.52..1294.30 rows=51206 width=108)
-> Merge Join (cost=264.52..1166.28 rows=51206 width=108)
Merge Cond: (t3.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t3.id
-> Seq Scan on t3 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=176.34..323.83 rows=8064 width=72)
-> Merge Join (cost=176.34..303.67 rows=8064 width=72)
Merge Cond: (t2.id = t1.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t2.id
-> Seq Scan on t2 (cost=0.00..22.70 rows=1270 width=36)
Sort Key: t1.id
-> Seq Scan on t1 (cost=0.00..22.70 rows=1270 width=36)
(38 rows)
或者設定會話級别的join_collapse_limit=1;
set join_collapse_limit=1;
set geqo=off;
-> Merge Join (cost=529.03..233966.24 rows=13111182 width=216)
-> Materialize (cost=176.34..323.83 rows=8064 width=72)
-> Seq Scan on t2 (cost=0.00..22.70 rows=1270 width=36)
如何通過優化器獲得最好的JOIN順序？
通常可以将join_collapse_limit設定為一個很大的值，然後檢視執行計劃，根據JOIN順序修改SQL語句。
postgres=# set join_collapse_limit=100;
SET
postgres=# set geqo=off;
QUERY PLAN
-------------------------------------------------------------------------------------------
Merge Join (cost=617.21..1255551.94 rows=83256006 width=228)
Merge Cond: (t2.id = t4.id)
-> Merge Join (cost=264.52..1166.28 rows=51206 width=108)
Merge Cond: (t3.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t3.id
-> Seq Scan on t3 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=176.34..323.83 rows=8064 width=72)
-> Merge Join (cost=176.34..303.67 rows=8064 width=72)
Merge Cond: (t2.id = t1.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t2.id
-> Seq Scan on t2 (cost=0.00..22.70 rows=1270 width=36)
Sort Key: t1.id
-> Seq Scan on t1 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=352.69..6317.49 rows=325158 width=144)
-> Merge Join (cost=352.69..5504.60 rows=325158 width=144)
Merge Cond: (t4.id = t6.id)
Merge Cond: (t4.id = t7.id)
Sort Key: t4.id
-> Seq Scan on t4 (cost=0.00..22.70 rows=1270 width=36)
Sort Key: t7.id
-> Seq Scan on t7 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=176.34..323.83 rows=8064 width=72)
-> Merge Join (cost=176.34..303.67 rows=8064 width=72)
Merge Cond: (t6.id = t5.id)
Sort Key: t6.id
-> Seq Scan on t6 (cost=0.00..22.70 rows=1270 width=36)
Sort Key: t5.id
-> Seq Scan on t5 (cost=0.00..22.70 rows=1270 width=36)
(36 rows)
修改SQL，固定為最佳JOIN順序。
postgres=# set join_collapse_limit=1;
explain select * from ((t4 join t7 using (id)) join (t6 join t5 using (id)) using (id)) join (t3 join (t2 join t1 using (id)) using (id)) using (id);
postgres=# explain select * from ((t4 join t7 using (id)) join (t6 join t5 using (id)) using (id)) join (t3 join (t2 join t1 using (id)) using (id)) using (id);
Merge Join (cost=617.21..1255482.81 rows=83245594 width=228)
-> Seq Scan on t3 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=352.69..6317.45 rows=325140 width=144)
-> Merge Join (cost=352.69..5504.60 rows=325140 width=144)
【推薦】PG優化器可以提升子查詢，轉換為JOIN，以獲得更好的執行計劃，但是如何強制優化器使用子查詢呢？
僅當子查詢的數量小于from_collapse_limit時，這些子查詢才會被提升為JOIN子句，超過的部分不會被提升為JOIN子句。
同樣需要考慮GEQO的設定，如果你不想使用遺傳算法，可以設定geqo=off;
要固定FROM子查詢，兩個設定即可from_collapse_limit=1, geqo=off;
postgres=# set from_collapse_limit=1; -- 這一不會提升子查詢了, 但是JOIN順序還是可能變化的，需要通過join_collapse_limit=1來設定
postgres=# explain select * from t1 join t2 using (id) join (select * from t4) t4 using (id) join (select * from t6) t6 using (id) join (select * from t5) t5 using (id) join (select * from t3) t3 using (id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Merge Join (cost=529.03..233966.24 rows=13111182 width=196)
Merge Cond: (t3.id = t1.id)
Sort Key: t3.id
-> Seq Scan on t3 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=440.86..42365.87 rows=2064753 width=180)
-> Merge Join (cost=440.86..37203.99 rows=2064753 width=180)
Merge Cond: (t5.id = t1.id)
Sort Key: t5.id
-> Seq Scan on t5 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=352.69..6951.07 rows=325158 width=144)
-> Merge Join (cost=352.69..6138.17 rows=325158 width=144)
Merge Cond: (t6.id = t1.id)
-> Seq Scan on t6 (cost=0.00..22.70 rows=1270 width=36)
-> Materialize (cost=264.52..1294.30 rows=51206 width=108)
-> Merge Join (cost=264.52..1166.28 rows=51206 width=108)
Merge Cond: (t4.id = t1.id)
-> Materialize (cost=176.34..323.83 rows=8064 width=72)
-> Merge Join (cost=176.34..303.67 rows=8064 width=72)
Merge Cond: (t1.id = t2.id)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
Sort Key: t1.id
-> Seq Scan on t1 (cost=0.00..22.70 rows=1270 width=36)
Sort Key: t2.id
-> Seq Scan on t2 (cost=0.00..22.70 rows=1270 width=36)
(32 rows)
如何通過優化器判斷FROM子句是否需要提升以得到好的執行計劃？
通過優化器的指導，調整SQL即可
postgres=# set from_collapse_limit=100;
Merge Join (cost=529.03..199114.66 rows=13111182 width=196)
Merge Cond: (t1.id = t6.id)
Merge Cond: (t4.id = t1.id)
Sort Key: t4.id
-> Seq Scan on t4 (cost=0.00..22.70 rows=1270 width=36)
Merge Cond: (t1.id = t2.id)
-> Materialize (cost=264.52..1294.30 rows=51206 width=108)
-> Merge Join (cost=264.52..1166.28 rows=51206 width=108)
Merge Cond: (t3.id = t6.id)
Sort Key: t3.id
-> Seq Scan on t3 (cost=0.00..22.70 rows=1270 width=36)
(31 rows)
調整SQL如下
explain select * from ((select * from t4) t4 join (t1 join t2 using (id)) using (id)) join ((select * from t3) t3 join ((select * from t6) t6 join (select * from t5) t5 using (id)) using (id)) using (id);
postgres=# set from_collapse_limit=1;
postgres=# explain select * from ((select * from t4) t4 join (t1 join t2 using (id)) using (id)) join ((select * from t3) t3 join ((select * from t6) t6 join (select * from t5) t5 using (id)) using (id)) using (id);
Merge Join (cost=529.03..199114.66 rows=13110272 width=196)
-> Sort (cost=88.17..91.35 rows=1270 width=36)
【推薦】GIN索引的寫優化，因為GIN的索引列通常是多值列，是以一條記錄可能影響GIN索引的多個頁，為了加快資料插入和更新删除的速度，建議打開fastupdate，同時設定合适的gin_pending_list_limit(機關KB)。
這麼做的原理是，當變更GIN索引時，先記錄在PENDING清單，而不是立即合并GIN索引。進而提升性能。
create index idx_1 on tbl using gin (tsvector) with (fastupdate=on, gin_pending_list_limit=10240)
【推薦】b-tree索引優化，不建議對頻繁通路的資料上使用非常離散的資料，例如UUID作為索引，索引頁會頻繁的分裂，重鎖，重IO和CPU開銷都比較高。
如何降低頻繁更新索引字段的索引頁IO，設定fillfactor為一個合适的值，預設90已經适合大部分場景。
【推薦】BRIN索引優化，根據資料的相關性，以及使用者需求的查詢的範圍，設定合适的pages_per_range=n。
例如使用者經常需要按範圍查詢10萬條記錄，通過評估，發現10萬條記錄通常分布在100個資料頁中，那麼可以設定pages_per_range=100。
評估方法
如何擷取平均每個頁存了多少條記錄。
analyze tbl;
select reltuples/relpages from tbl;
阿裡雲RDS PostgreSQL 使用規範
如果你是阿裡雲RDS PGSQL的使用者，推薦你參考一下規範，阿裡雲RDS PGSQL提供了很多有趣的特性幫助使用者解決社群版本不能解決的問題。
【推薦】冷熱資料分離
當資料庫非常龐大（例如超過2TB）時，建議使用阿裡雲PGSQL的OSS_EXT外部表插件，将冷資料存入OSS。
通過建立OSS外部表，實作對OSS資料的透明通路。
https://help.aliyun.com/document_detail/35457.html【推薦】對RT要求高的業務，請使用SLB鍊路或PROXY透傳模式連接配接資料庫。
【推薦】RDS的地域選擇與應用保持一緻。
說明：比如應用上海環境，資料庫選擇上海region，避免應用和資料庫出現跨區域通路。
【推薦】為RDS報警設定多位接收人，并設定合适的報警閥值。
【推薦】為RDS設定合适的白名單，加強資料通路的安全性。
【推薦】盡量禁止資料庫被公網通路，如果真的要通路，一定要設定白名單。
【推薦】如果資料使用者的查詢中，使用索引的列，資料傾斜較為嚴重，即某些值很多記錄，某些值很少記錄，則查詢某些列時可能不走索引，而查詢另外一些列可能走索引。
特别是這種情況，可能造成綁定變量執行計劃傾斜的問題，如果使用者使用了綁定變量，同時出現了執行計劃的傾斜，建議使用pg_hint_plan綁定執行計劃，避免傾斜。
test=> create extension pg_hint_plan;
CREATE EXTENSION
test=> alter role all set session_preload_libraries='pg_hint_plan';
ALTER ROLE
test=> create table test(id int primary key, info text);
test=> insert into test select generate_series(1,100000);
INSERT 0 100000
test=> explain select * from test where id=1;
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.29..8.31 rows=1 width=36)
Index Cond: (id = 1)
(2 rows)
test=> /*+ seqscan(test) */ explain select * from test where id=1;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test (cost=0.00..1124.11 rows=272 width=36)
Filter: (id = 1)
test=> /*+ bitmapscan(test) */ explain select * from test where id=1;
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.30..8.31 rows=1 width=36)
Recheck Cond: (id = 1)
-> Bitmap Index Scan on test_pkey (cost=0.00..4.30 rows=1 width=0)
Index Cond: (id = 1)
(4 rows)
![解碼器用于語義分割：資料依賴的解碼可以實作靈活的特征聚合[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)