PostgreSQL 擴充模組生態：從 pg_stat_statements 到 PostGIS | PostgreSQL

2026/07/11
postgresql extensions pg_stat_statements pg_trgm
PostgreSQL 擴充模組生態：從 pg_stat_statements 到 PostGIS | PostgreSQL

PostgreSQL 最強大的設計哲學之一就是 可擴展性（Extensibility）。透過 Extension 機制，你可以在不修改核心原始碼的前提下，為資料庫新增資料型別、運算子、索引方法，甚至完整的功能套件。從效能調優必備的 pg_stat_statements，到地理空間處理的 PostGIS、向量搜尋的 pgvector，Extension 生態讓 PostgreSQL 成為了「不只是關聯式資料庫」的強大平台。

Extension 的組成結構

一個完整的 Extension 由三個核心部分構成：

1. Control File（.control）——元資料描述檔：

# pg_trgm.control（範例）
comment = 'text similarity measurement and index searching based on trigrams'
default_version = '1.6'
module_pathname = '$libdir/pg_trgm'
relocatable = true
欄位說明
comment簡短功能描述
default_version預設安裝版本
module_pathname共享函式庫路徑（若有 C 程式碼）
relocatable是否可安裝至任意 Schema
requires依賴的其他 Extension 清單
trusted是否為 Trusted Extension（PG13+）

2. SQL Script File——定義 Extension 的所有資料庫物件（函式、型別、運算子、索引存取方法等）。

3. Shared Library（.so）——當 Extension 包含 C 語言函式時需要。純 SQL/PL/pgSQL 的 Extension 不需要。

Extension 生命週期

-- 安裝
CREATE EXTENSION pg_trgm;

-- 指定 Schema 安裝
CREATE EXTENSION pg_trgm WITH SCHEMA extensions;

-- 防止重複安裝報錯
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- CASCADE 自動安裝所有依賴
CREATE EXTENSION postgis CASCADE;

-- 查看已安裝的 Extension
SELECT extname, extversion FROM pg_extension;

-- 升級版本
ALTER EXTENSION pg_trgm UPDATE TO '1.6';

-- 移除（會一併刪除所有相關物件）
DROP EXTENSION pg_trgm CASCADE;

contrib 模組 vs 第三方 Extension

分類說明範例
contrib 模組隨 PostgreSQL 原始碼發布，品質有核心團隊背書pg_stat_statements、pg_trgm、hstore
第三方 Extension社群/公司開發，透過 PGXN 或 GitHub 發布PostGIS、TimescaleDB、pgvector、Citus
-- 查詢所有可用的 Extension
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

-- 查詢可升級的 Extension
SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version != default_version;

pg_stat_statements：效能調優必備

pg_stat_statements 是 效能調優最重要的 Extension——它追蹤所有 SQL 語句的執行統計資訊，是找出慢查詢、分析資源消耗的第一工具。

啟用設定（需重啟）

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000          # 追蹤的最大查詢數
pg_stat_statements.track = all          # all | top | none
pg_stat_statements.track_utility = on   # 是否追蹤 DDL 等 utility 指令
pg_stat_statements.save = on            # 重啟後是否保留統計資料

核心查詢

CREATE EXTENSION pg_stat_statements;

-- 找出最耗時的查詢（總執行時間）
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    calls,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 找出平均執行時間最長的查詢
SELECT
    round(mean_exec_time::numeric, 2) AS mean_ms,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    query
FROM pg_stat_statements
WHERE calls > 10  -- 至少執行 10 次才有統計意義
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 找出 I/O 最密集的查詢
SELECT
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(100.0 * shared_blks_hit /
        nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

-- 重置統計資料
SELECT pg_stat_statements_reset();

auto_explain：自動記錄慢查詢計畫

auto_explain 在查詢執行時間超過門檻值時，自動將執行計畫記錄至日誌，無需手動執行 EXPLAIN。

# postgresql.conf（需重啟）
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000   # 超過 1000ms 才記錄
auto_explain.log_analyze = on          # 記錄實際執行統計
auto_explain.log_buffers = on          # 記錄 Buffer 使用
auto_explain.log_timing = on           # 記錄各節點執行時間
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 1.0         # 抽樣比例

-- 在當前 Session 中臨時啟用（不需重啟，用於除錯）
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;   -- 記錄所有查詢
SET auto_explain.log_analyze = true;

-- 執行查詢後，到 PostgreSQL 日誌中查看執行計畫
SELECT * FROM orders WHERE customer_id = 123;

pg_trgm：三元組模糊搜尋

pg_trgm 實作三元組（trigram）文字相似度演算法，能顯著加速 LIKE 和 ILIKE 查詢，並支援模糊搜尋。

CREATE EXTENSION pg_trgm;

-- 基本相似度計算（0-1，越高越相似）
SELECT similarity('hello world', 'hello postgresql');
-- 結果：0.36363636

-- 使用 % 運算子進行相似度比較
SELECT * FROM products
WHERE name % 'postgre';

-- 建立 GIN 索引加速 LIKE/ILIKE 查詢
CREATE INDEX idx_products_name_trgm
ON products USING gin(name gin_trgm_ops);

-- 現在 ILIKE 查詢可以走索引！
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE name ILIKE '%postgre%';

-- 按相似度排序（nearest-neighbor，需 GiST 索引）
CREATE INDEX idx_products_name_gist_trgm
ON products USING gist(name gist_trgm_ops);

SELECT name, similarity(name, 'postgres') AS sim
FROM products
ORDER BY name <-> 'postgres'   -- <-> 是距離運算子
LIMIT 10;

-- 顯示字串的 trigram 分解
SELECT show_trgm('hello');
-- 結果：{"  h"," he","ell","hel","llo","lo "}

btree_gist / btree_gin：EXCLUDE 約束的基石

btree_gist 和 btree_gin 讓 GiST 和 GIN 索引支援原本只有 B-tree 才能處理的資料型別，主要用於實作 EXCLUDE 約束（排除約束）。

CREATE EXTENSION btree_gist;

-- 會議室預約：防止同一會議室的時間重疊
CREATE TABLE room_bookings (
    id          serial PRIMARY KEY,
    room_id     integer NOT NULL,
    booked_at   tstzrange NOT NULL,
    title       text,
    -- 確保同一會議室的預約時間不重疊
    EXCLUDE USING gist (
        room_id WITH =,         -- 需要 btree_gist
        booked_at WITH &&       -- && 表示時間範圍重疊
    )
);

-- 正常插入
INSERT INTO room_bookings (room_id, booked_at, title)
VALUES (1, '[2026-07-11 09:00, 2026-07-11 10:00)', '晨會');

-- 以下插入會因時間重疊而失敗
INSERT INTO room_bookings (room_id, booked_at, title)
VALUES (1, '[2026-07-11 09:30, 2026-07-11 11:00)', '衝突會議');
-- ERROR: conflicting key value violates exclusion constraint

這是資料庫層級保證時間不重疊的最優雅方案——不需要應用層檢查邏輯。

hstore：鍵值儲存

hstore 在單一欄位中儲存鍵值對。雖然多數場景已被 JSONB 取代，但在特定情況（如儲存差異、簡單標籤）仍有用途。

CREATE EXTENSION hstore;

CREATE TABLE product_attributes (
    id    serial PRIMARY KEY,
    name  text,
    attrs hstore
);

INSERT INTO product_attributes (name, attrs) VALUES
    ('筆電', 'cpu => "Intel i7", ram => "16GB", storage => "512GB SSD"');

-- 查詢特定鍵的值
SELECT name, attrs -> 'cpu' AS cpu
FROM product_attributes
WHERE attrs ? 'cpu';   -- ? 運算子：鍵是否存在

-- 查詢包含特定鍵值對的記錄
SELECT name FROM product_attributes
WHERE attrs @> 'cpu => "Intel i7"';

-- 更新/新增鍵
UPDATE product_attributes
SET attrs = attrs || 'warranty => "2 years"'
WHERE name = '筆電';

-- 建立 GIN 索引加速查詢
CREATE INDEX idx_product_attrs ON product_attributes USING gin(attrs);

UUID 生成：uuid-ossp vs gen_random_uuid()

-- 方式一：PG13+ 內建函式（推薦，不需 Extension）
SELECT gen_random_uuid();

-- 方式二：uuid-ossp Extension（提供多種 UUID 版本）
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();       -- UUID v4（隨機）
SELECT uuid_generate_v1mc();     -- UUID v1（時間序，MAC 隨機化）

-- 實際建表應用
CREATE TABLE users (
    id    uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text UNIQUE NOT NULL
);

選擇建議：PG13+ 優先使用 gen_random_uuid()，無需安裝 Extension。需要 UUID v1/v3/v5 或舊版 PostgreSQL 才用 uuid-ossp。

citext：大小寫不敏感文字

CREATE EXTENSION citext;

CREATE TABLE users (
    id       serial PRIMARY KEY,
    username citext UNIQUE NOT NULL,
    email    citext UNIQUE NOT NULL
);

INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');

-- 以下插入因 citext 唯一約束失敗（alice = Alice = ALICE）
INSERT INTO users (username, email) VALUES ('alice', 'other@example.com');
-- ERROR: duplicate key value violates unique constraint

-- 查詢時自動不區分大小寫
SELECT * FROM users WHERE username = 'ALICE';   -- 找到 'Alice'
SELECT * FROM users WHERE email = 'Alice@Example.COM';   -- 找到

相比每次查詢都寫 lower(username)，citext 更簡潔且索引效率更高。

pgcrypto：加密與雜湊

CREATE EXTENSION pgcrypto;

-- bcrypt 密碼雜湊（推薦用於密碼儲存）
SELECT crypt('mypassword', gen_salt('bf', 12));

-- 驗證密碼
SELECT crypt('mypassword', stored_hash) = stored_hash AS is_valid
FROM users WHERE username = 'alice';

-- 對稱加密（AES）
SELECT pgp_sym_encrypt('sensitive data', 'encryption_key');
SELECT pgp_sym_decrypt(encrypted_data, 'encryption_key')
FROM sensitive_table;

-- 加密強度的隨機 bytes
SELECT encode(gen_random_bytes(16), 'hex');

-- SHA-256 雜湊
SELECT encode(digest('hello world', 'sha256'), 'hex');

shared_preload_libraries：預載設定

某些 Extension 需要在 PostgreSQL 啟動時預先載入，才能正常運作。修改此設定需要 重啟。

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements, auto_explain, pg_cron'
需要預載不需要預載
pg_stat_statements（查詢掛鉤）pg_trgm（按需載入）
auto_explain（查詢掛鉤）hstore（按需載入）
pg_cron（背景 Worker）uuid-ossp（按需載入）
pgaudit（稽核掛鉤）citext（SQL 定義）
timescaledb（計畫修改）pgcrypto（按需載入）
-- 確認設定
SHOW shared_preload_libraries;

-- 確認 Extension 已正確安裝
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

自訂 Extension 開發

目錄結構

myextension/
├── myextension.control        # Control file
├── myextension--1.0.sql       # SQL 定義腳本
├── myextension--1.0--1.1.sql  # 升級腳本（1.0 → 1.1）
├── myextension.c              # C 程式碼（可選）
└── Makefile                   # 使用 PGXS 建置

純 SQL Extension 範例

# myextension.control
comment = '自訂工具函式集'
default_version = '1.0'
relocatable = true

-- myextension--1.0.sql
CREATE OR REPLACE FUNCTION my_format_bytes(bigint)
RETURNS text
LANGUAGE sql
IMMUTABLE STRICT PARALLEL SAFE
AS $$
    SELECT CASE
        WHEN $1 >= 1073741824 THEN round($1/1073741824.0, 2) || ' GB'
        WHEN $1 >= 1048576    THEN round($1/1048576.0, 2) || ' MB'
        WHEN $1 >= 1024       THEN round($1/1024.0, 2) || ' KB'
        ELSE $1 || ' bytes'
    END;
$$;

CREATE VIEW my_table_sizes AS
SELECT
    relname AS table_name,
    my_format_bytes(pg_total_relation_size(oid)) AS total_size,
    my_format_bytes(pg_relation_size(oid)) AS table_size,
    my_format_bytes(pg_indexes_size(oid)) AS indexes_size
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC;

建置與安裝

# Makefile
EXTENSION = myextension
DATA = myextension--1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

make
sudo make install
psql -c "CREATE EXTENSION myextension;"

版本升級

-- myextension--1.0--1.1.sql（升級差異腳本）
CREATE OR REPLACE FUNCTION my_new_feature(text)
RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $$
    SELECT upper($1);
$$;

-- 執行升級
ALTER EXTENSION myextension UPDATE TO '1.1';
SELECT extname, extversion FROM pg_extension WHERE extname = 'myextension';

PGXN 套件管理

PGXN（PostgreSQL Extension Network）是 Extension 的集中倉庫，類似 Python 的 PyPI。

# 安裝 PGXN 客戶端
pip install pgxnclient

# 搜尋 Extension
pgxn search trigram

# 安裝 Extension
pgxn install pg_partman

# 安裝特定版本
pgxn install pg_partman=4.7.3

重要的第三方 Extension：

Extension用途
PostGIS地理空間資料處理
TimescaleDB時序資料與自動分區
pgvector向量相似度搜尋（AI/ML）
pg_partman自動化分區管理
pg_cron資料庫內的排程任務
pgaudit稽核日誌記錄
Citus水平分片（Sharding）

Trusted Extension（PG13+）

Trusted Extension 允許非 superuser 的資料庫擁有者安裝特定 Extension，無需依賴 superuser 介入。

-- 非 superuser 但具有 CREATE 權限的使用者
GRANT CREATE ON DATABASE mydb TO regular_user;

-- regular_user 可以安裝 Trusted Extension
\c mydb regular_user
CREATE EXTENSION citext;      -- Trusted ✓
CREATE EXTENSION pgcrypto;    -- Trusted ✓
CREATE EXTENSION pg_cron;     -- 非 Trusted ✗（需 superuser）

常見 Trusted Extension：btree_gist、btree_gin、citext、hstore、pgcrypto、pg_trgm、uuid-ossp、ltree、intarray、tablefunc

常見陷阱

陷阱症狀解法
忘記 shared_preload_librariesExtension 可 CREATE 但資料為空加入設定並重啟
Schema 隔離Extension 函式找不到調整 search_path
DROP CASCADE 連鎖刪除依賴物件一併被刪執行前用 pg_depend 確認影響
版本不相容升級後函式簽章改變先查 pg_available_extension_versions

版本演進

版本Extension 相關變更
PG9.1引入 Extension 機制
PG13Trusted Extension；gen_random_uuid() 內建化
PG14pg_stat_statements 加入 toplevel 欄位
PG15pg_walinspect、pg_buffercache 增強
PG16更多 Extension 支援邏輯複製
PG17Extension 安全性改善

總結

PostgreSQL Extension 機制讓資料庫從「關聯式資料庫」進化為「資料平台」。核心要點：

  1. 必裝：pg_stat_statements（效能調優）、pg_trgm（模糊搜尋加速）
  2. 按需：auto_explain（慢查詢診斷）、btree_gist（排除約束）、citext（大小寫不敏感）
  3. 生態：PostGIS、pgvector、TimescaleDB 等第三方擴充讓 PostgreSQL 能勝任幾乎任何資料處理場景
  4. 開發：自訂 Extension 只需三個檔案（control + SQL + Makefile），PGXS 提供標準化建置流程

下一篇，我們將深入探討 PostgreSQL 常用擴充模組精選——涵蓋 pg_cron、ltree、tablefunc、pg_partman 等實用擴充的詳細用法與最佳實踐。

BenZ Software Developer

熱愛技術的軟體開發者，在這裡分享程式開發經驗與學習筆記。