跳到主要内容

PostgreSQL 扩展

问题​

PostgreSQL 有哪些常用扩展？扩展机制的优势是什么？

答案​

一、扩展机制​

PostgreSQL 的 EXTENSION 机制允许以插件形式扩展数据库功能，无需修改核心代码：

-- 安装扩展
CREATE EXTENSION pgvector;

-- 查看已安装扩展
SELECT * FROM pg_extension;

-- 查看可用扩展
SELECT * FROM pg_available_extensions;

二、核心扩展​

1. pg_stat_statements（性能分析必备）​

CREATE EXTENSION pg_stat_statements;

-- 查看最耗时的 SQL
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

2. pgvector（向量搜索）​

AI/RAG 场景中存储和检索 Embedding 向量：

CREATE EXTENSION vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)  -- OpenAI ada-002 的维度
);

-- 创建向量索引
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- 向量相似度搜索
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

3. PostGIS（地理空间）​

CREATE EXTENSION postgis;

-- 查找附近 5km 内的店铺
SELECT name, ST_Distance(
  location::geography, 
  ST_Point(116.4, 39.9)::geography
) AS distance
FROM shops
WHERE ST_DWithin(
  location::geography,
  ST_Point(116.4, 39.9)::geography,
  5000  -- 5km
)
ORDER BY distance;

4. TimescaleDB（时序数据）​

CREATE EXTENSION timescaledb;

-- 将普通表转为超表（hypertable）
SELECT create_hypertable('metrics', 'time');

-- 自动压缩、数据保留策略
SELECT add_compression_policy('metrics', INTERVAL '7 days');
SELECT add_retention_policy('metrics', INTERVAL '90 days');

三、常用扩展汇总​

扩展领域说明
pg_stat_statements监控SQL 性能统计
pgvectorAI/向量向量存储与相似度搜索
PostGISGIS地理空间数据处理
TimescaleDB时序时序数据优化
pg_trgm文本模糊匹配（trigram 相似度）
uuid-ossp工具UUID 生成
hstoreKV键值对数据类型
pgcrypto安全加密函数
pg_partman管理分区管理自动化
Citus分布式水平分片，分布式查询
pgBouncer连接连接池（独立组件）
pg_cron调度定时任务

四、PostgreSQL 的生态优势​

这也是 PostgreSQL 受欢迎的重要原因：一个数据库覆盖多种数据模型，减少架构中的组件数量。

常见面试问题​

Q1: pgvector 支持哪些距离计算？​

答案：

操作符距离类型索引类型
<->欧氏距离（L2）ivfflat / hnsw
<=>余弦距离ivfflat / hnsw
<#>内积（负）ivfflat / hnsw

HNSW 索引（0.5.0+）查询精度更高但构建更慢。

Q2: PostgreSQL 扩展和 MySQL 插件有什么区别？​

答案：

  • PostgreSQL 扩展可以添加 新数据类型、操作符、索引方法、函数，能力极强
  • MySQL 插件主要是存储引擎、认证等，扩展 SQL 能力有限
  • PostgreSQL 的扩展生态远比 MySQL 丰富

相关链接​