PostgreSQL 資料庫開發規范-有解無憂

背景

PostgreSQL的功能非常強大，但是要把PostgreSQL用好，開發人員是非常關鍵的，

下面將針對PostgreSQL資料庫原理與特性，輸出一份開發規范，希望可以減少大家在使用PostgreSQL資料庫程序中遇到的困惑，

目標是將PostgreSQL的功能、性能發揮好，她好我也好，

PostgreSQL 使用規范

命名規范

【強制】庫名、表名限制命名長度，建議表名及欄位名字符總長度小于等于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時的編碼一致，否則可能亂碼，

設計規范

【強制】多表中的相同列，以及有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

【強制】使用外鍵時，一定要設定fk的action，例如cascade，set null，set default，
例如

postgres=# create table tbl2(id int references tbl(id) on delete cascade on update cascade, info text);  
CREATE TABLE  
postgres=# create index idx_tbl2_id on tbl2(id);  
CREATE INDEX  
postgres=# insert into tbl values (1,'test');  
INSERT 0 1  
postgres=# insert into tbl2 values (1,'test');  
INSERT 0 1  
postgres=# update tbl set id=2;  
UPDATE 1  
postgres=# select * from tbl2;  
 id | info   
----+------  
  2 | test  
(1 row)

【強制】對于頻繁更新的表，建議建表時指定表的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可以用來清理未被參考的大物件資料，

【推薦】對于固定條件的查詢，可以使用部分索引，減少索引的大小，同時提升查詢效率，
例如

select * from tbl where id=1 and col=?; -- 其中id=1為固定的條件  
create index idx on tbl (col) where id=1;

【推薦】對于經常使用運算式作為查詢條件的陳述句，可以使用運算式或函式索引加速查詢，
例如

select * from tbl where exp(xxx);  
create index idx on tbl ( exp );

【推薦】如果需要除錯較為復雜的邏輯時，不建議寫成函式進行除錯，可以使用plpgsql的online code.
例如

do language plpgsql  
$$  
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資料型別存盤用戶資料，

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 ，用具體的欄位串列代替*，不要回傳用不到的任何欄位，另外表結構發生變化也容易出現問題，

管理規范

【強制】資料訂正時，洗掉和修改記錄時，要先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重組表，建議操作前檢查膨脹率，

穩定性與性能規范

【強制】在代碼中寫分頁查詢邏輯時，若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/39682
例如

CREATE 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;  
ALTER FUNCTION  
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 & 阿里巴巴') ;  
  phonenum   |            info             | c1  |  c2   |              c3              |             c4               
-------------+-----------------------------+-----+-------+------------------------------+----------------------------  
 13888888888 | i am digoal, a postgresqler | 123 | china | 中華人民共和國，阿里巴巴，阿 | 2016-04-19 11:15:55.208658  
(1 row)  
  
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遞回

with recursive t_result as (  
  select * from tbl_test where id=5  
    union all  
  select t2.* from t_result t1 join tbl_test t2 on t1.pid=t2.id  
)  
select * from t_result;  
  
 id | name | pid   
----+------+-----  
  5 | 121  |   2  
  2 | 11   |   1  
  1 | 10   |   0  
(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);  
CREATE TABLE

生成一批測驗資料

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  
(10 rows)  
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  
(2 rows)  
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
1 | c1      | p1
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);
   id   | firstname | lastname |   corp   | age 
--------+-----------+----------+----------+-----
 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;
 ?column? 
----------
 t
(1 row)

另外還有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

例如

begin;
set local join_collapse_limit=1;
set local geqo=off;
postgres=# create table t1(id int, info text);
CREATE TABLE
postgres=# create table t2(id int, info text);
CREATE TABLE
postgres=# create table t3(id int, info text);
CREATE TABLE
postgres=# create table t4(id int, info text);
CREATE TABLE
postgres=# create table t5(id int, info text);
CREATE TABLE
postgres=# create table t6(id int, info text);
CREATE TABLE
postgres=# create table t7(id int, info text);
CREATE TABLE
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  (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)
(38 rows)
end;

或者設定會話級別的join_collapse_limit=1;

set join_collapse_limit=1;
set geqo=off;
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  (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)
(38 rows)

如何通過優化器獲得最好的JOIN順序？
通常可以將join_collapse_limit設定為一個很大的值，然后查看執行計劃，根據JOIN順序修改SQL陳述句，
例如

postgres=# set join_collapse_limit=100;
SET
postgres=# set geqo=off;
SET
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..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  (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)
   ->  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 Join  (cost=176.34..303.67 rows=8064 width=72)
                     Merge Cond: (t4.id = t7.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)
                     ->  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=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  (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)
                           ->  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)
(36 rows)

修改SQL，固定為最佳JOIN順序，

postgres=# set join_collapse_limit=1;
SET
postgres=# set geqo=off;
SET

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);
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Merge Join  (cost=617.21..1255482.81 rows=83245594 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  (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)
   ->  Materialize  (cost=352.69..6317.45 rows=325140 width=144)
         ->  Merge Join  (cost=352.69..5504.60 rows=325140 width=144)
               Merge Cond: (t4.id = t6.id)
               ->  Merge Join  (cost=176.34..303.67 rows=8064 width=72)
                     Merge Cond: (t4.id = t7.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)
                     ->  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=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  (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)
                           ->  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)
(36 rows)

【推薦】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來設定
SET
postgres=# set geqo=off;
SET
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  (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=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  (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=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)
                           ->  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=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)
                                       ->  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=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  (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)
(32 rows)

如何通過優化器判斷FROM子句是否需要提升以得到好的執行計劃？
通過優化器的指導，調整SQL即可
例如

postgres=# set join_collapse_limit=100;
SET
postgres=# set from_collapse_limit=100;
SET
postgres=# set geqo=off;
SET
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..199114.66 rows=13111182 width=196)
   Merge Cond: (t1.id = t6.id)
   ->  Merge Join  (cost=264.52..1166.28 rows=51206 width=108)
         Merge Cond: (t4.id = t1.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=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  (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)
   ->  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  (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: (t6.id = t5.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)
                           ->  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)
(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 join_collapse_limit=1;
SET
postgres=# set from_collapse_limit=1;
SET
postgres=# set geqo=off;
SET
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);
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Merge Join  (cost=529.03..199114.66 rows=13110272 width=196)
   Merge Cond: (t1.id = t6.id)
   ->  Merge Join  (cost=264.52..1166.28 rows=51206 width=108)
         Merge Cond: (t4.id = t1.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=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  (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)
   ->  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  (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: (t6.id = t5.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)
                           ->  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)
(31 rows)

【推薦】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);
CREATE TABLE
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)
(2 rows)

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)

作者丨digoal

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/PostgreSQL-Database-Development-Specification.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/517696.html

標籤：PostgreSQL

上一篇：使用Python進行XML轉換

下一篇：京東云TiDB SQL優化的最佳實踐