放棄 MySQL，擁抱 PG！

Java精選面試題（微信小程序）：5000+道面試題和選擇題，包含Java基礎、并發、JVM、線程、MQ系列、Redis、Spring系列、Elasticsearch、Docker、K8s、Flink、Spark、架構設計、大廠真題等，在線隨時刷題！

一、前言

最近在最新的數據庫受歡迎程度排行榜中，排在第一位的竟然不是我們熟悉的MYSQL數據庫而是PostgreSQL，并且MySQL數據庫的受歡迎程度比PostgreSQL竟然落后了15個百分點。

為何pg數據庫這幾年突飛猛進，為何mysql數據庫逐漸落寞？本文旨在總結和挖掘Pg數據庫的幾個亮點功能，分析為何pg數據庫能成為當下最受歡迎的數據庫

二、安裝

這里簡要說明下安裝過程，筆者是使用docker安裝的pg數據庫 過程中使用到的幾個關鍵命令如下：

docker run --namepostgres-db-ePOSTGRES_PASSWORD=initialpass-p5432:5432-dpostgres:latest

docker exec-itpostgres-dbpsql-Upostgres

-- 創建用戶root并設置密碼

CREATE USER root WITH SUPERUSER PASSWORD'root';

-- 授予所有權限

GRANT ALL PRIVILEGES ON DATABASE postgres TO root;

-- 使用root用戶登錄 postgres 數據庫

psql-Uroot-dpostgres

三、Pg數據庫功能亮點

3.1 強大的面向對象的支持能力

Pg數據庫是關系型數據庫但是也是面向對象的數據庫，這就使得這個數據庫更符合服務端面向對象編程的思維模式。

先定義一個雇員類型：

createTYPEemployee AS (

name VARCHAR,

age INT,

skill TEXT[]

);

上面的代碼中 我們創建了一個自定義類型 employee包含三個字段：

• name：字符串類型（員工姓名）

• age：整數類型（員工年齡）

• skill：文本數組（員工技能列表）

然后我們創建一個數據表

CREATE TABLE"tb_employees"(

id SERIAL PRIMARY KEY,

"employee_info""employee",

"department_id"varchar(20)",

"created_at"timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

"updated_at"timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT"tb_employees_pkey"PRIMARY KEY ("id")

)

;

INSERT INTO"tb_employees"(employee_info) VALUES (ROW('張三', 30, ARRAY['Java','Python'])::employee);

-- 注意 employee 類型中的每個字段值都必須寫上，缺少任何一個字段都會報錯

INSERT INTO"tb_employees"(employee_info, department_id) VALUES (ROW('Maria', 20, ARRAY[]::TEXT[])::employee,'Guard');

表和表之間還能夠繼承，我們創建了一個子公司員工表 tb_sub_employees ，它繼承了 tb_employees 數據表，同時我們還定義了一個額外字段 sub_company_name

create table tb_sub_employees(

sub_company_name VARCHAR(64)

) INHERITS (tb_employees)

INSERT INTO tb_sub_employees (

employee_info,

sub_company_name,

department_id

) VALUES

(

ROW('王一', 30, ARRAY['java']::TEXT[])::employee, -- employee 類型完整字段（name, age, skill）

'北京分公司',

'技術部'

),

(

ROW('王二', 35, ARRAY['項目管理']::TEXT[])::employee,

'廣州分公司',

'行政部'

);

然后我們打開這個表看下結構 發現這個表繼承了父表的所有字段

表的繼承還有下面兩個重要規律

• 父表后續新增 / 刪除字段時，子表不會自動同步（需手動調整子表結構）；但父表修改字段類型（如 VARCHAR(32) 改 VARCHAR(64)），子表會自動同步。

• 不會自動繼承父表的觸發器、索引、主鍵約束、外鍵約束等 “對象級配置”，僅繼承字段結構

此外當我們執行SELECT * from tb_employees，會同時查找出來子表和父表的內容.

如果只需要查找出子表的內容需要將查詢修改為下面這樣，添加一個ONLY 關鍵字

SELECT* from ONLY tb_employees

利用這個機制其實還可以做數據隔離，比如我們現在的 tb_employees 有一個狀態字段 status，標識員工是否在職，然后我們創建一個 tb_employees_his 表記錄已經離職的人員，然后給tb_employees 綁定一個觸發器在status變為離職狀態的時候，往tb_employees_his插入一條數據，然后刪除掉tb_employees 中的數據，這樣上層應用就查不到這個員工了，但是如果去掉ONLY關鍵字就還能查到。

3.2 豐富的內置字段類型

Pg數據庫比Mysql數據庫支持更多的字段類型，除了上一節說過的自定義字段類型之后還有下面幾種內置字段類型值得關注：

• 1）CIDR類型

CREATE TABLE"net_segments"(

-- serial 是一種特殊的自動增長整數類型，用于簡化自增主鍵（ID 字段）的創建。它本質上是一個語法糖，底層通過結合 integer 類型、序列（sequence）和默認值實現自動增長功能

"id"SERIAL PRIMARY KEY,

"segment"cidr NOT NULL,

"segment_remark"varchar(255)

)

;

COMMENT ON COLUMN"net_segments"."id"IS'id';

COMMENT ON COLUMN"net_segments"."segment"IS'網段';

COMMENT ON COLUMN"net_segments"."segment_remark"IS'網段說明';

INSERT INTO"net_segments"("id","segment","segment_remark") VALUES (1,'192.168.1.0/24','機房1');

-- >> 運算符：這是 PostgreSQL 中專門用于網絡地址類型的包含運算符，表示 “左側網絡包含右側網絡”

select* from net_segmentswheresegment >>'192.168.1.128/28'::CIDR

• 2）幾何學類型

-- 創建包含 circle 類型的表

CREATE TABLE circles (

id SERIAL PRIMARY KEY,

area circle

);

-- 插入圓形數據

INSERT INTO circles (area) VALUES

('((0,0), 10)'), -- 圓心(0,0)，半徑10

('((5,5), 3)'); -- 圓心(5,5)，半徑3

-- 查詢：判斷點 (3,3) 是否在圓內

SELECTid, area

FROM circles

WHEREarea @> point'(3,3)'; -- @> 是包含運算符，判斷圓是否包含點

3.3 強大的JSON字段類型支持

在mysql 8.0 版本之后，數據庫的字段類型也支持了json，但是mysql對json的支持還是比較簡單，接下來我們看看 postgreSQL在JSON字段類型上的強大支持能力

CREATETABLEtb_request_log (

id SERIALPRIMARYKEY,

urlVARCHAR(1024)NOTNULL,

response JSONB,

request_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP

);

-- 說明：這里為什么不給字段直接添加注釋呢？是因為部分版本的pg不支持直接在建表語句里面寫注釋，可以通過COMMENT ON COLUMN 這個語法在創建完數據表之后再添加注釋

隨后向這個表里面添加幾條數據：

那么請你思考下如果我們要在mysql數據庫中選出code是200的記錄，是不是還挺麻煩的，但是在pg這邊這些問題就可以輕松解決，首先為了支持JSON數據的檢索，pg定義了很多新的操作符：

->：返回 JSON 對象（帶引號，如"success"）

->>：返回文本值（不帶引號，如 success）

@>：判斷 JSON 是否包含指定對象（如數組包含某元素）

||：合并兩個 JSON 對象（用于更新）

-：刪除 JSON 中的指定鍵

例如下面給出來的一些查詢實例，這些查詢在MySQL中每個查詢實現起來都比較麻煩，但是在Pg中就比較好實現。

-- 查詢code==200 的訪問log

SELECT*

FROMtb_request_log

WHEREresponse ->>'code'='200';

-- 查詢 response 中包含頂級 data 字段的記錄

SELECT*

FROMtb_request_log

WHEREresponse ?'data';

-- 查詢 response 字段中的requestType 數組中包含 "API" 的記錄

SELECT*

FROMtb_request_log

WHEREresponse ->'requestType'@>'["API"]'::JSONB;

-- 查詢 tb_request_log 表中，response（JSONB 類型）字段包含 {"code":200} 這個 JSON 對象的所有記錄

select*fromtb_request_logwhereresponse @>'{"code":200}'

上面的sql雖然可以查詢到結果，但是如果數據表的記錄數比較多，查詢效率就比較一般了，但是不要慌，pg還支持給需要經常用來作為檢索條件的json字段加索引。

-- 對response中的"code"字段創建索引

CREATEINDEXidx_tb_request_log_response_statusONtb_request_log USING GIN ((response ->'code'));

那么如果在mysql中如果要修改一個json字段的內容，比如添加一個key或者移除一個key，那么基本上就是要覆蓋重寫，但是在pg中就比較簡單了。

-- 更新JSON字段中的值（如修改某條記錄的message）

UPDATEtb_request_log

SETresponse = response ||'{"message": "登錄成功（已更新）"}'::JSONB-- 使用||合并更新

WHEREid = 1;

-- 刪除JSON字段中的某個鍵（如刪除details字段）

UPDATEtb_request_log

SETresponse = response -'details'-- 使用-刪除鍵

WHEREid = 2;

3.4 豐富的數據庫插件生態

pg數據庫擁有強大的插件生態，這就讓pg的拓展功能非常強大，這其中有5個插件功能讀者可以重點了解一下：

• 全文檢索功能（安裝中文分詞器插件 ，讓pg實現類似 ElasticSearch的全文檢索功能，默認情況下 PostgreSQL支持英文分詞檢索，且不需要安裝任何插件）

• 快速數據讀取（安裝 redis_fdw，讓pg充當redis類似的角色。備注：PostgreSQL可以使用CREATE UNLOGGED TABLE 命令創建臨時表（數據庫重啟之后數據全部丟失）這種臨時表也可以支持快速查詢和快速寫入，也可以可以間接的當緩存使用而不用安裝任何插件）

• 地理位置支持（安裝 postgis ，讓pg支持地理位置,例如經緯度范圍查詢）

• 向量數據庫（安裝 pgvector ，讓pg支持向量檢索 ，用于AI-Agent的 RAG 功能實現）

• 定時任務（安裝 pg_cron 讓pg支持定時任務）

由于篇幅所限不能將所有插件以及特色功能全部介紹一遍，下面以pg_cron和pg_vector為例子介紹下具體的插件安裝和基礎使用

3.4.1 pg_cron 定時任務插件

安裝插件之前需要先獲取下當前pg數據庫的版本，比如我這個docker安裝的postgreSQL的版本就是 17.6 版本，插件的安裝需要考慮和當前pgSQL 的版本匹配

安裝插件也非常簡單，這里以 pg_cron 插件為例子說明下安裝命令

# 更新包索引（容器內操作）

apt-getupdate

# 安裝 pg_cron（版本號與 PostgreSQL 17 匹配）

apt-getinstall-ypostgresql-17-cron

隨后打開pgSQL docker 容器的的配置文件

vim /var/lib/postgresql/data/postgresql.conf

在這個配置文件做下述修改

# 原配置可能為空或包含其他插件

shared_preload_libraries ='pg_cron'# 例如：已有 postgis 則改為 'pg_cron,postgis'

注意pg_cron 插件默認只能且只能在 postgres 數據庫（默認系統數據庫）創建和管理定時任務，如果你自己定義了一個數據庫，需要在這個數據庫中使用插件，你需要修改配置文件，添加下面的選項:

# 在文件末尾添加（替換為你的數據庫名，如 test）

cron.database_name ='test'

創建一個定時任務也非常簡單：

# 在 postgres 數據庫中創建插件和任務（此時不會報錯）

CREATE EXTENSION pg_cron;

# 創建定時任務 每天凌晨 2 點自動刪除 tb_request_log 表中 30 天前的記錄

SELECTcron.schedule(

'clean-test-logs',

'0 2 * * *',

$$DELETEFROM tb_request_logWHERErequest_time < NOW() - INTERVAL'30 days'$$

);

# 查看當前有哪些定時任務

SELECT* FROM cron.job;

# 關閉任務 刪除名為 test-cron-job 的定時任務。

SELECTcron.unschedule('test-cron-job')

啟用定時任務拓展之后就會在當前數據庫下開啟一個新的數據表 cron.job 結構如下：

3.4.2 pgvector 向量數據庫插件

本文使用一個真實案例來介紹 使用pg數據庫來實現RAG功能

RAG功能用咱的大白話來說就是用私域的知識來和用戶的提問一起交給大模型，這樣大模型就知道一些問題的背景了，因為有時候用戶的問題涉及到的相關知識大模型是沒有訓練過的。那么私域的知識可能有很多，不可能每次用戶提問都把所有的知識庫都帶上，帶上哪些相關材料就是RAG要解決的問題。 那么為什么需要向量數據庫呢，這是因為我們的知識庫如果要想實現上面的這套流程，必須先轉為向量存儲之后才行。在用戶在提問的時候，會檢索向量數據庫里面的相似內容，放在提示詞里面一起送給大模型。

我們先按照類似的流程安裝 pgvector 插件 （postgresql-17-pgvector），但是安裝pgvector由于不涉及后臺數據庫調度所以不用改數據庫配置文件也不用重啟容器，具體命令也就下面這一行即可

apt-getinstall-ypostgresql-17-pgvector

隨后我們登錄數據庫筆者這里是使用navicat 直接執行一下下面的命令，當前數據庫也就開啟了vector的拓展

CREATE EXTENSION vector

在RAG中 把 “外部知識” 變成 “可檢索的格式”，具體分為四步：

步驟 1：數據加載（Load） 步驟 2：數據拆分（Split）：大模型處理文本有 “長度限制”（如 GPT-4 上下文窗口是 128k tokens，約 10 萬字），因此需要將長文檔拆成 “短片段”（如每段 200-500 字），確保后續檢索和生成時能完整覆蓋關鍵信息。 步驟 3：文本向量化（Embed）：用嵌入模型（Embedding Model） 將每個文本片段轉換成 “向量”（一串數字，如 768 維 / 1536 維數組）。 向量的核心作用是 “用數字表示語義”：語義越相似的文本，向量在 “向量空間” 中的距離越近 步驟 4：向量存儲（Store）

首先我們先用langchain的相關API構建下述智能體代碼，對langchain 或者 langgraph 不熟悉的同學可以簡單看下我之前寫的相關博客，或者直接去官網了解最新的內容。

importasyncio

from langchain_openaiimportOpenAIEmbeddings

from langchain_community.document_loadersimportWebBaseLoader

from langchain.text_splitterimportRecursiveCharacterTextSplitter

from langchain_community.embeddingsimportDashScopeEmbeddings

from langchain_postgresimportPGEngine, PGVectorStore

# Set embeddings

embd = DashScopeEmbeddings(

dashscope_api_key="xxxxxxxxxxxxxx",

model="text-embedding-v3"

)

# Docs to index

urls = [

"https://java2ai.com/docs/1.0.0-M6.1/tutorials/vectorstore/?spm=4347728f.4dbc009c.0.0.179c6e97dDjP50",

]

# Load

docs = [WebBaseLoader(url).load()forurl in urls]

docs_list = [itemforsublist in docsforitem in sublist]

print(docs_list)

# Split

text_splitter = RecursiveCharacterTextSplitter.from_tiktoken_encoder(

chunk_size=500, chunk_overlap=0

)

doc_splits = text_splitter.split_documents(docs_list)

async def get_store(embedding: OpenAIEmbeddings):

CONNECTION_STRING ="postgresql://root:root@localhost:5432/test?sslmode=disable"

pg_engine = PGEngine.from_connection_string(url=CONNECTION_STRING)

# 創建向量存儲表，指定表名和向量維度

await pg_engine.ainit_vectorstore_table(

table_name="vectorstore",

vector_size=768,

)

store = await PGVectorStore.create(

engine=pg_engine,

table_name="vectorstore",

embedding_service=embedding,

)

returnstore

store = asyncio.run(get_store(embd))

store.aadd_documents(doc_splits)

retriever = store.as_retriever()

user_query="什么是向量存儲"

# 基于向量相似度檢索相關文檔。

docs = retriever.invoke(user_query)

print(docs)

上面的代碼會創建一個支持向量存儲和相似度搜索的專用表 vectorstore，表中包含文本內容、對應的768維向量以及可選的元數據字段，數據表中記錄這文本片段和向量化之后的信息。

在Langgraph中也支持使用pg數據庫來做長期記憶，避免使用內存記憶無法進行持久化的問題。在AI-Agent開發過程中使用Pg數據庫做向量存儲也是一個非常好的選擇

四、為何MYSQL數據庫逐漸黯淡

筆者依然記得在大學時期學習數據庫理論的時候使用的數據庫是一款叫做SQL-Server的數據庫，當時在學習的時候了解到了 觸發器、存儲過程、視圖 這樣的數據庫概念，但是后來畢業后參加工作后這些特性竟然很少用到，這些功能更多的還是利用自己編寫代碼去實現而不是利用數據庫自帶的功能。

筆者分析看來pg數據庫能夠越來越受歡迎的原因有下面幾個

• 1）強大的插件生態

• 2）豐富的內置功能

• 3）更符合標準SQL數據庫規范

也許在未來做數據庫技術選型的時候Pg數據庫更值得考慮，尤其是在一些低預算小項目中，pg的豐富功能可以讓我們去替代一部分的中間件例如es、mongo、定時任務調度中心等。

如果讀者對Pg數據庫感興趣 后期筆者還會出一期 關于MybatisPlus 和Pg數據庫的結合使用的注意事項。

作者：自然吸氣發動機

來源：https://juejin.cn/post/7542045803583209498

公眾號“Java精選”所發表內容注明來源的，版權歸原出處所有（無法查證版權的或者未注明出處的均來自網絡，系轉載，轉載的目的在于傳遞更多信息，版權屬于原作者。如有侵權，請聯系，筆者會第一時間刪除處理！

最近有很多人問，有沒有讀者交流群！加入方式很簡單，公眾號Java精選，回復“加群”，即可入群！

文章有幫助的話，點在看，轉發吧！