PostgreSQL 資料庫開發規範

来源:https://www.cnblogs.com/88223100/archive/2022/10/18/PostgreSQL-Database-Development-Specification.html
-Advertisement-
Play Games

PostgreSQL的功能非常強大，但是要把PostgreSQL用好，開發人員是非常關鍵的。 下麵將針對PostgreSQL資料庫原理與特性，輸出一份開發規範，希望可以減少大家在使用PostgreSQL資料庫過程中遇到的困惑。 目標是將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);
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Podman與docker相容性問題
    使用Podman最好的地方就是支持rootless，也就是說用戶不需要為root許可權即可進行容器的管理操作。因此現在在CentOS 8及以後的版本中，預設使用Podman替代Docker，如果使用docker命令，會重定向到podman。 rootless很好，但是也帶來了一些問題： 多餘的提示 運 ...
  • 高可用linux 伺服器搭建
    最原始的服務部署，為單點部署，即直接把服務部署在一個伺服器上。如果伺服器出現故障，或者服務因為某個異常而掛掉，則服務就會發生中斷。單點部署出現故障的概率最高。 後來，出現了網關，比如 nginx kong 等。如下圖所示： 這樣，所有客戶請求都會經過網關，再由網關轉發到各個服務。如果由服務出現故障， ...
  • linux 安裝VMware tools
    安裝VMware Tools選項顯示灰色的解決辦法 Linux 1. 解決VMware灰色狀態 ①點擊虛擬機； ②在虛擬機設置分別設置CD/DVD和軟盤中選擇連接【使用物理驅動器】（如有CD/DVD2一樣操作）； ③重啟虛擬機，灰色字即點亮。 image-20221018195444397 2.VM ...
  • Microsoft Teams團隊頻道里共用文件彈出登錄提示解決辦法
    Linux中的文件訪問控制 在Unix系統家族裡，文件或目錄許可權的控制分別以讀取、寫入、執行3種一般許可權來區分，另有3種特殊許可權可供運用【SUID,SGID,SBIT】，再搭配擁有者與所屬群組管理許可權範圍。 SUID： 1、只對二進位可執行程式有效，不能為普通文件; 2、對程式文件必須擁有執行許可權; ...
  • vsftpd服務
    vsftpd服務 ftp是互聯網中進行文件傳輸的一種協議，基於C/S模式，FTP預設有兩個工作埠（20數據傳輸，21FTP服務端就收客戶端發來的指令和） 安裝FTP服務 [root@haha-main-130 ~]# yum -y install vsftpd 配置文件位置及內容 [root@ha ...
  • Windows開啟關閉測試模式的方法（含開啟測試模式失敗的解決辦法）
    前言： 內含：Windows開啟關閉測試模式的方法、開啟測試模式失敗的解決辦法、win10進入bios的方式、BitLocker恢復方式。 對於互聯網從業者來說，尤其是開發人員、測試人員、產品經理，在內部測試或驗收時，軟體未獲得微軟簽名時，需要以系統以測試模式運行，安裝軟體進行測試。 win7系統開 ...
  • nginx反向代理與負載均衡
    nginx反向代理與負載均衡 nginx通常被用作後端伺服器的反向代理，這樣就可以很方便的實現動靜分離以及負載均衡，從而大大提高伺服器的處理能力。 nginx實現動靜分離，其實就是在反向代理的時候，如果是靜態資源，就直接從nginx發佈的路徑去讀取，而不需要從後臺伺服器獲取了。 nginx通過ups ...
  • 親測-ubuntu20.04 安裝 MySQL5.7
    背景介紹 ubuntu 20.04 版本系統自帶的 MySQL 版本是 8.0，普通方法很難安裝 5.7 版本的。由於 8.0 版本較 5.7 版本做了不少改動，筆者比較習慣使用 5.7 版本。 網上搜做了一圈，跟著各種教程試了很多遍，最後終於找到了成功的方法。過程記錄分享出來，供大家參考。 安裝過 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類