跳至主要內容
日沐 SunBathe

Appearance

常用擴充套件 ​

PostgreSQL 的擴充機制讓它能處理各種專業領域的需求。本篇介紹常用的擴充套件。

一、 擴充管理基礎 ​

sql
-- 查看可用擴充
SELECT * FROM pg_available_extensions;

-- 安裝擴充
CREATE EXTENSION IF NOT EXISTS extension_name;

-- 查看已安裝擴充
SELECT * FROM pg_extension;

-- 更新擴充
ALTER EXTENSION extension_name UPDATE;

-- 移除擴充
DROP EXTENSION extension_name;

二、 pg_trgm：模糊搜尋 ​

Trigram 相似度搜尋，支援模糊匹配和 LIKE 索引。

sql
CREATE EXTENSION pg_trgm;

-- 建立 GIN 索引
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

相似度搜尋 ​

sql
-- 計算相似度
SELECT similarity('PostgreSQL', 'Postgres');  -- 0.625

-- 相似度搜尋
SELECT name, similarity(name, 'PostgreSQL') AS sim
FROM products
WHERE name % 'PostgreSQL'  -- 相似度 > 0.3
ORDER BY sim DESC;

-- 調整相似度閾值
SET pg_trgm.similarity_threshold = 0.5;

LIKE 索引加速 ​

sql
-- 現在 LIKE '%keyword%' 也能用索引！
SELECT * FROM products WHERE name LIKE '%database%';
SELECT * FROM products WHERE name ILIKE '%Database%';

三、 PostGIS：地理空間 ​

處理地理位置資料的必備擴充。

sql
CREATE EXTENSION postgis;

-- 建立表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)  -- WGS84 座標系
);

-- 插入資料（經度, 緯度）
INSERT INTO locations (name, geom) VALUES
    ('台北101', ST_SetSRID(ST_MakePoint(121.5645, 25.0339), 4326)),
    ('高雄85大樓', ST_SetSRID(ST_MakePoint(120.2922, 22.6096), 4326));

-- 建立空間索引
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

距離查詢 ​

sql
-- 找出 1 公里內的地點
SELECT name, ST_Distance(
    geom::geography,
    ST_MakePoint(121.5, 25.0)::geography
) AS distance_m
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_MakePoint(121.5, 25.0)::geography,
    1000  -- 1000 公尺
);

區域查詢 ​

sql
-- 找出多邊形範圍內的地點
SELECT name FROM locations
WHERE ST_Within(geom, ST_GeomFromText(
    'POLYGON((121 25, 122 25, 122 26, 121 26, 121 25))',
    4326
));

四、 pg_stat_statements：效能監控 ​

追蹤查詢執行統計。

sql
-- 需要設定 postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

-- 查看最耗時的查詢
SELECT
    calls,
    total_exec_time::INTEGER AS total_ms,
    mean_exec_time::INTEGER AS mean_ms,
    LEFT(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 重設統計
SELECT pg_stat_statements_reset();

五、 uuid-ossp：UUID 生成 ​

sql
CREATE EXTENSION "uuid-ossp";

-- 生成 UUID v4（隨機）
SELECT uuid_generate_v4();
-- d7c4e7b0-3a1f-4c5e-9d2f-1b3c4d5e6f7a

-- 用於主鍵
CREATE TABLE users (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name TEXT
);

替代方案：gen_random_uuid() ​

PostgreSQL 13+ 內建 gen_random_uuid()，不需要擴充：

sql
SELECT gen_random_uuid();

CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT
);

六、 pgcrypto：加密 ​

sql
CREATE EXTENSION pgcrypto;

-- 雜湊
SELECT crypt('password123', gen_salt('bf'));
-- $2a$06$...

-- 驗證密碼
SELECT crypt('password123', stored_hash) = stored_hash;

-- 加密
SELECT pgp_sym_encrypt('secret data', 'encryption_key');

-- 解密
SELECT pgp_sym_decrypt(encrypted_data, 'encryption_key');

七、 hstore：鍵值對 ​

簡單的 key-value 儲存。

sql
CREATE EXTENSION hstore;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes HSTORE
);

INSERT INTO products (name, attributes) VALUES
    ('Laptop', 'brand => HP, ram => 16GB, ssd => 512GB');

-- 查詢
SELECT name, attributes->'brand' AS brand FROM products;

-- 條件查詢
SELECT * FROM products WHERE attributes @> 'brand => HP';

-- 轉換為 JSONB
SELECT hstore_to_jsonb(attributes) FROM products;

TIP

現代開發建議使用 JSONB 取代 hstore，功能更豐富。

八、 TimescaleDB：時序資料 ​

專門處理時間序列資料。

bash
# 安裝（需要另外安裝）
apt install timescaledb-postgresql-16
sql
CREATE EXTENSION timescaledb;

-- 建立普通表
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    temperature FLOAT,
    humidity FLOAT
);

-- 轉換為 Hypertable
SELECT create_hypertable('metrics', 'time');

-- 插入資料
INSERT INTO metrics VALUES
    (NOW(), 1, 25.5, 60.2),
    (NOW() - INTERVAL '1 hour', 1, 24.8, 58.5);

-- 時間聚合查詢
SELECT
    time_bucket('1 hour', time) AS hour,
    device_id,
    AVG(temperature) AS avg_temp
FROM metrics
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY hour, device_id;

自動壓縮 ​

sql
-- 設定壓縮
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id'
);

-- 壓縮舊資料
SELECT add_compression_policy('metrics', INTERVAL '7 days');

九、 pg_cron：定時任務 ​

sql
CREATE EXTENSION pg_cron;

-- 每小時執行
SELECT cron.schedule('refresh_stats', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_stats');

-- 每天凌晨 3 點
SELECT cron.schedule('cleanup', '0 3 * * *',
    'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days''');

-- 查看任務
SELECT * FROM cron.job;

-- 取消任務
SELECT cron.unschedule('refresh_stats');

十、 其他實用擴充 ​

pg_partman：分區管理 ​

sql
CREATE EXTENSION pg_partman;

SELECT create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_interval => 'daily'
);

pgvector：向量搜尋 ​

用於 AI/ML 嵌入向量相似度搜尋：

sql
CREATE EXTENSION vector;

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    embedding vector(384)  -- 384 維向量
);

-- 餘弦相似度搜尋
SELECT * FROM items
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 10;

pg_hint_plan：查詢提示 ​

sql
CREATE EXTENSION pg_hint_plan;

-- 強制使用特定索引
SELECT /*+ IndexScan(users idx_users_email) */ *
FROM users WHERE email = 'alice@example.com';

十一、 擴充安全性 ​

sql
-- 只允許 superuser 安裝擴充
-- 在 pg_hba.conf 限制 superuser 連線

-- 信任的擴充清單
# postgresql.conf
trusted_extensions = 'pg_trgm,uuid-ossp'

總結 ​

擴充用途
pg_trgm模糊搜尋、LIKE 索引
PostGIS地理空間資料
pg_stat_statements查詢效能分析
uuid-osspUUID 生成
pgcrypto加密與雜湊
TimescaleDB時序資料
pg_cron定時任務
pgvector向量搜尋 (AI/ML)

PostgreSQL 的擴充生態讓它幾乎可以處理任何類型的資料需求。

進階挑戰 ​

  1. 安裝 pg_trgm 擴充，為產品名稱實作模糊搜尋功能
  2. 使用 pgvector 擴充建立一個簡單的語義搜尋系統
  3. 設定 pg_cron 每天凌晨 3 點自動刷新物化檢視

延伸閱讀與資源 ​

← 上一章：安全性與權限 | 返回專題首頁 | 下一章：與應用程式整合 →