Q117 PostgreSQL与NewSQL深度实战
20K Java+AI 岗位高频考点 — PostgreSQL是增长最快的开源数据库,pgvector更让它在AI领域大放异彩。覆盖PG核心原理、高级查询、MVCC、pgvector向量扩展、NewSQL(TiDB/CockroachDB)。
一、PostgreSQL基础与MySQL对比
1. PostgreSQL vs MySQL核心差异?
| 维度 | PostgreSQL | MySQL |
| 定位 | 对象-关系型 | 关系型 |
| 扩展性 | 极强(扩展/自定义类型) | 中等 |
| JSON支持 | JSONB(二进制,可索引) | JSON(文本) |
| 并发 | MVCC(无锁读) | MVCC+锁 |
| 全文搜索 | 内置tsvector | 需外置ES |
| 窗口函数 | 完整支持 | 8.0+支持 |
| 分区 | 声明式分区(10+) | 8.0+支持 |
| GIS | PostGIS(最强) | 空间索引 |
| 适用 | 复杂查询/分析/AI | 事务型/互联网 |
2. PostgreSQL的数据类型?
基本类型:integer, bigint, numeric, real, serial
文本类型:varchar, text, char
日期类型:date, time, timestamp, timestamptz, interval
JSON类型:json(文本), jsonb(二进制,推荐)
数组类型:integer[], text[]
网络类型:inet, cidr, macaddr
几何类型:point, line, circle, polygon
UUID类型:uuid
范围类型:int4range, timestamptzrange
自定义类型:composite, enum, domain
3. JSONB高级操作?
-- 存储
INSERT INTO products (id, attrs) VALUES (1, '{"cpu": "i7", "ram": 16, "tags": ["gaming", "premium"]}');
-- 查询JSON字段
SELECT attrs->>'cpu' AS cpu FROM products; -- 文本结果
SELECT attrs->'ram' AS ram FROM products; -- JSON结果
-- 条件查询
SELECT * FROM products WHERE attrs->>'cpu' = 'i7';
SELECT * FROM products WHERE (attrs->>'ram')::int > 8;
-- 包含查询
SELECT * FROM products WHERE attrs @> '{"cpu": "i7"}';
SELECT * FROM products WHERE attrs ? 'tags';
-- 数组包含
SELECT * FROM products WHERE attrs->'tags' ? 'gaming';
-- JSONB索引
CREATE INDEX idx_attrs_gin ON products USING GIN (attrs);
CREATE INDEX idx_cpu ON products ((attrs->>'cpu'));
4. Java操作PostgreSQL JSONB?
// Spring Data JDBC + PG JSONB
@Table("products")
public class Product {
@Id private Long id;
private String name;
@Column("attrs")
private Map<String, Object> attrs; // JSONB → Map
}
// 自定义Type
public class JsonbType implements UserType {
@Override
public int[] sqlTypes() { return new int[]{Types.OTHER}; }
@Override
public Class<?> returnedClass() { return Map.class; }
// nullSafeGet/set implementation...
}
5. PostgreSQL数组类型?
-- 数组操作
CREATE TABLE posts (id serial, title text, tags text[]);
INSERT INTO posts (title, tags) VALUES ('Java指南', ARRAY['Java', '教程']);
-- 包含查询
SELECT * FROM posts WHERE tags @> ARRAY['Java'];
SELECT * FROM posts WHERE 'Java' = ANY(tags);
-- 数组函数
SELECT array_length(tags, 1) FROM posts;
SELECT unnest(tags) FROM posts; -- 展开为行
SELECT array_agg(tag) FROM unnest(ARRAY['a','b','c']) AS tag;
二、PostgreSQL高级查询
6. 窗口函数?
-- 排名(相同值同排名,跳号)
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students;
-- 密集排名(不跳号)
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank;
-- 分组排名
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;
-- 累计/移动聚合
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total,
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM daily_sales;
-- LAG/LEAD
SELECT date, amount,
amount - LAG(amount) OVER (ORDER BY date) AS daily_change,
LEAD(amount, 7) OVER (ORDER BY date) AS next_week
FROM daily_sales;
7. CTE递归查询?
-- 组织架构树
WITH RECURSIVE org_tree AS (
-- 基础查询:根节点
SELECT id, name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
-- 递归查询:子节点
SELECT e.id, e.name, e.manager_id, t.level + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
)
SELECT id, name, level FROM org_tree ORDER BY level;
-- 部门树形汇总
WITH RECURSIVE dept_tree AS (
SELECT id, name, parent_id, name AS path
FROM departments WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id, t.path || ' > ' || d.name
FROM departments d JOIN dept_tree t ON d.parent_id = t.id
)
SELECT * FROM dept_tree;
8. UPSERT(INSERT ON CONFLICT)?
-- 有则更新,无则插入
INSERT INTO user_scores (user_id, score, updated_at)
VALUES (1, 100, NOW())
ON CONFLICT (user_id)
DO UPDATE SET score = user_scores.score + EXCLUDED.score, updated_at = NOW();
-- 有则忽略
INSERT INTO users (email, name) VALUES ('a@b.com', '张三')
ON CONFLICT (email) DO NOTHING;
-- RETURNING
INSERT INTO orders (user_id, amount)
VALUES (1, 99.9)
ON CONFLICT (user_id) DO UPDATE SET amount = EXCLUDED.amount
RETURNING id, amount;
9. LATERAL JOIN?
-- 每个用户最新的3条订单
SELECT u.name, o.amount
FROM users u,
LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) o;
-- 每个分类的Top-N商品
SELECT c.name, p.product_name, p.price
FROM categories c,
LATERAL (SELECT product_name, price FROM products WHERE category_id = c.id ORDER BY price DESC LIMIT 5) p;
10. 其他高级SQL?
-- FOR UPDATE悲观锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- SKIP LOCKED(跳过已锁行,适合任务队列)
SELECT * FROM task_queue WHERE status = 'pending'
ORDER BY created_at LIMIT 10 FOR UPDATE SKIP LOCKED;
-- FILTER聚合
SELECT count(*) AS total,
count(*) FILTER (WHERE status = 'active') AS active,
count(*) FILTER (WHERE status = 'inactive') AS inactive
FROM users;
-- DISTINCT ON(每组取一条)
SELECT DISTINCT ON (user_id) user_id, created_at, action
FROM user_actions ORDER BY user_id, created_at DESC;
三、PostgreSQL索引与优化
11. PostgreSQL索引类型?
| 类型 | 说明 | 适用场景 |
| B-tree | 默认,有序 | 等值、范围、排序 |
| GIN | 倒排索引 | JSONB、数组、全文搜索 |
| GiST | 通用搜索树 | GIS、范围、全文 |
| BRIN | 块范围索引 | 大表有序列 |
| Hash | 哈希索引 | 等值查询 |
| SP-GiST | 空间分区 | 电话、路由 |
12. 部分索引和表达式索引?
-- 部分索引(只索引满足条件的行)
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';
-- 表达式索引
CREATE INDEX idx_lower_email ON users (LOWER(email));
-- 复合索引
CREATE INDEX idx_user_date ON orders (user_id, created_at DESC);
-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users (LOWER(email));
-- 并发创建(不锁表)
CREATE INDEX CONCURRENTLY idx_name ON orders (user_id);
13. EXPLAIN ANALYZE?
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;
-- 关注指标:
-- Seq Scan(全表扫描)→ 需要加索引
-- Index Scan(索引扫描)→ 好
-- Bitmap Heap Scan → 大量行时好
-- cost=0.00..100.00 → 预估成本
-- rows=100 → 预估行数
-- actual time=0.01..0.05 → 实际时间
-- Buffers: shared hit=10 read=5 → 缓存命中/磁盘读取
14. pg_stat_statements性能监控?
-- 开启扩展
CREATE EXTENSION pg_stat_statements;
-- 查看Top慢查询
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
-- 查看全表扫描
SELECT relname, seq_scan, idx_scan, seq_scan::float / GREATEST(idx_scan, 1) AS ratio
FROM pg_stat_user_tables
ORDER BY ratio DESC;
四、PostgreSQL MVCC与事务
15. MVCC原理?
每行数据有4个隐藏字段:
- xmin:插入时的事务ID
- xmax:删除/更新时的事务ID(0表示未删除)
- xmin:对创建该行版本的事务可见
- xmax:对该事务之后的某个事务可见
可见性判断:
SELECT:找xmin <= 当前txid AND (xmax == 0 OR xmax > 当前txid) 的行
INSERT:设置xmin = 当前txid, xmax = 0
DELETE:设置xmax = 当前txid
UPDATE:DELETE旧行 + INSERT新行
优势:读不阻塞写,写不阻塞读
劣势:需要VACUUM清理死元组
16. 事务隔离级别?
| 级别 | 脏读 | 不可重复读 | 幻读 |
| Read Uncommitted | PG不允许 | 可能 | 可能 |
| Read Committed | 不会 | 可能 | 可能 |
| Repeatable Read | 不会 | 不会 | PG不会(特殊) |
| Serializable | 不会 | 不会 | 不会 |
-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 快照读取
-- 其他事务修改了balance,这里读到的还是旧值
COMMIT;
17. WAL(Write-Ahead Log)?
WAL原理:
1. 数据修改前,先将修改记录写入WAL
2. WAL刷盘后,才修改内存中的数据页
3. 定期checkpoint,将脏页写入磁盘
4. 崩溃恢复:重放WAL中未checkpoint的记录
WAL配置:
wal_level = replica # WAL级别
max_wal_size = 2GB # WAL最大大小
min_wal_size = 800MB # WAL最小大小
checkpoint_completion_target = 0.9
synchronous_commit = on # 同步提交
流复制:主库通过WAL流复制到从库
18. 咨询锁(Advisory Lock)?
// 应用级分布式锁
@Autowired JdbcTemplate jdbcTemplate;
public boolean tryLock(long lockId) {
Integer result = jdbcTemplate.queryForObject(
"SELECT pg_try_advisory_lock(?)", Integer.class, lockId);
return result != null && result == 1;
}
public void unlock(long lockId) {
jdbcTemplate.queryForObject(
"SELECT pg_advisory_unlock(?)", Integer.class, lockId);
}
五、PostgreSQL分区与运维
19. 声明式分区?
-- 按范围分区(按月)
CREATE TABLE orders (
id bigserial, user_id bigint, amount decimal, created_at timestamp
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- 按哈希分区
CREATE TABLE users (id bigint, name text) PARTITION BY HASH (id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- 自动创建分区(pg_partman扩展)
20. VACUUM与自动清理?
VACUUM:标记死元组空间为可重用(不回收磁盘空间)
VACUUM FULL:回收磁盘空间,重建表(锁表,生产慎用)
AUTOVACUUM:自动执行,默认开启
配置:
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min # 检查间隔
autovacuum_vacuum_scale_factor = 0.2 # 20%数据变更触发
autovacuum_analyze_scale_factor = 0.1 # 10%数据变更触发分析
监控:
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
21. 连接池pgBouncer?
; pgBouncer配置
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction ; 事务级池化(推荐)
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
22. 逻辑复制?
-- 发布者
CREATE PUBLICATION order_pub FOR TABLE orders, order_items;
-- 订阅者
CREATE SUBSCRIPTION order_sub
CONNECTION 'host=publisher port=5432 dbname=mydb'
PUBLICATION order_pub;
-- 适用场景:部分表同步、版本升级、读写分离
六、pgvector与AI应用
23. pgvector安装与使用?
-- 安装扩展
CREATE EXTENSION vector;
-- 创建向量列(1536维 = OpenAI text-embedding-ada-002)
CREATE TABLE documents (
id bigserial PRIMARY KEY,
content text,
embedding vector(1536)
);
-- 插入向量
INSERT INTO documents (content, embedding)
VALUES ('Java是面向对象语言', '[0.1, 0.2, 0.3, ...]');
-- 向量搜索(余弦距离)
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...'
LIMIT 5;
24. 向量索引?
-- HNSW索引(推荐,速度快)
CREATE INDEX idx_doc_embedding ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- IVFFlat索引
CREATE INDEX idx_doc_ivf ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- 查询时设置搜索参数
SET hnsw.ef_search = 100; -- 搜索精度(越大越准但越慢)
-- 距离函数
-- <=> : 余弦距离
-- <-> : L2距离
-- <#> : 内积(负)
25. Spring AI + pgvector?
// 自动配置
@Configuration
public class PgVectorConfig {
@Bean
public VectorStore vectorStore(JdbcTemplate jdbcTemplate, EmbeddingModel embeddingModel) {
return new PgVectorStoreBuilder(jdbcTemplate, embeddingModel)
.dimensions(1536)
.distanceType(DistanceType.COSINE)
.removeExistingVectorStoreTable(false)
.initializeSchema(true)
.build();
}
}
// 使用
@Autowired VectorStore vectorStore;
// 添加文档
vectorStore.add(List.of(new Document("Java是面向对象语言", Map.of("source", "wiki"))));
// 搜索
List<Document> results = vectorStore.similaritySearch(
SearchRequest.builder().query("什么是Java").topK(5).build());
26. pgvector性能优化?
1. 预热:查询前 SELECT COUNT(*) FROM documents;
2. 索引参数:HNSW m=16-48, ef_construction=64-200
3. 搜索参数:ef_search = 40-200
4. 批量插入:INSERT比COPY慢,大批量用\copy
5. 分区:大表按时间分区,减少搜索范围
6. 预过滤:先WHERE过滤再ORDER BY向量距离
7. 并行查询:max_parallel_workers_per_gather = 4
百万级向量:HNSW索引,搜索 < 10ms
千万级向量:考虑分片或专用向量库(Milvus)
七、NewSQL数据库
27. 什么是NewSQL?
NewSQL = 关系型SQL接口 + NoSQL水平扩展能力
代表:
- TiDB(PingCAP,中国)
- CockroachDB(美国)
- OceanBase(蚂蚁集团)
- YugabyteDB(印度裔团队)
核心特性:
1. SQL兼容:MySQL/PostgreSQL协议兼容
2. 水平扩展:在线扩缩容,自动均衡
3. 分布式事务:ACID跨节点事务
4. HTAP:同时支持OLTP和OLAP
28. TiDB架构原理?
组件:
- TiDB Server:SQL解析、优化、执行(无状态)
- PD(Placement Driver):元数据管理、调度
- TiKV:行存储引擎(RocksDB + Raft)
- TiFlash:列存储引擎(OLAP加速)
特性:
- MySQL协议兼容,迁移成本低
- 自动分片(Region),在线扩缩容
- Raft协议保证数据一致性
- TiDB + TiFlash = HTAP
Java连接:
jdbc:mysql://tidb-host:4000/mydb
29. CockroachDB原理?
架构:对称节点,无Master
存储:Range(64MB),每个Range 3副本Raft
事务:分布式事务,2PC优化(Parallel Commits)
一致性:Linearizable(串行化)
SQL:PostgreSQL协议兼容
特点:
- 强一致性(比TiDB更强)
- 地理分布友好(多区域部署)
- 自动复制和再平衡
30. NewSQL vs RDBMS vs NoSQL?
| 维度 | MySQL | MongoDB | TiDB |
| SQL | 完整 | 不支持 | 完整 |
| 扩展 | 垂直 | 水平 | 水平 |
| 事务 | ACID | 4.0+ | 分布式ACID |
| 分析 | 弱 | 聚合管道 | HTAP |
| 运维 | 简单 | 中等 | 复杂 |
| 适用 | 核心业务 | 灵活Schema | 海量+分析 |
31. OceanBase架构?
蚂蚁集团出品,阿里/支付宝核心数据库
架构:
- Zone(可用区):3个Zone,每个Zone有Observer
- Paxos协议:保证数据一致性
- 多租户:资源隔离(CPU/内存)
- 兼容MySQL和Oracle
特性:
- 416万tpmC(TPC-C世界纪录)
- 支持双十一峰值
- OBServer一体化引擎(行存+列存)
八、面试高频综合题
32. MySQL迁移到PostgreSQL的步骤?
1. Schema迁移:
- 数据类型映射(tinyint→smallint, datetime→timestamptz)
- 自增ID(AUTO_INCREMENT→SERIAL/IDENTITY)
- 反引号→双引号
2. 数据迁移:
- pgloader工具(推荐)
- 或:MySQL导出CSV → COPY导入PG
3. SQL适配:
- LIMIT OFFSET → LIMIT OFFSET(相同)
- GROUP_CONCAT → STRING_AGG
- IFNULL → COALESCE
- DATE_FORMAT → TO_CHAR
- INSERT IGNORE → INSERT ON CONFLICT DO NOTHING
4. 应用改造:
- JDBC驱动替换
- 方言适配
- 连接池配置
5. 验证:数据校验 + 功能测试 + 性能测试
33. PostgreSQL高可用方案?
方案一:流复制 + 自动故障转移
主库 → 从库(同步/异步) + Patroni自动切换
方案二:Patroni + etcd + HAProxy
- Patroni管理PG实例
- etcd存储集群状态
- HAProxy做连接路由
方案三:PgPool-II
- 连接池 + 负载均衡 + 自动故障转移
- 适合读多写少场景
推荐:Patroni方案(云原生,K8s友好)
34. PostgreSQL读写分离?
// Spring Boot多数据源读写分离
@Configuration
public class PgDataSourceConfig {
@Bean
@Primary
public DataSource routingDataSource(
@Qualifier("masterDataSource") DataSource master,
@Qualifier("replicaDataSource") DataSource replica) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("master", master);
targetDataSources.put("replica", replica);
AbstractRoutingDataSource routing = new AbstractRoutingDataSource() {
@Override
protected Object determineCurrentLookupKey() {
return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
? "replica" : "master";
}
};
routing.setTargetDataSources(targetDataSources);
routing.setDefaultTargetDataSource(master);
return routing;
}
}
35. 如何选择PostgreSQL索引?
等值查询 → B-tree(默认)
范围查询 → B-tree
JSONB查询 → GIN
数组包含 → GIN
全文搜索 → GIN(tsvector_ops)
地理空间 → GiST(PostGIS)
大表有序 → BRIN(节省空间)
电话号码 → SP-GiST
36. PostgreSQL性能调优?
# postgresql.conf 关键参数
shared_buffers = '4GB' # 共享缓冲区(物理内存25%)
effective_cache_size = '12GB' # 查询规划器缓存估计(物理内存75%)
work_mem = '256MB' # 排序/哈希内存
maintenance_work_mem = '1GB' # VACUUM/CREATE INDEX内存
max_connections = 200 # 最大连接数
random_page_cost = 1.1 # SSD设1.1,HDD设4
effective_io_concurrency = 200 # SSD设200
max_parallel_workers_per_gather = 4 # 并行查询
37. PostgreSQL在AI时代的优势?
1. pgvector:向量搜索,RAG应用
2. JSONB:灵活存储AI响应
3. 全文搜索:tsvector + tsquery
4. 扩展生态:PostGIS(GIS)、TimescaleDB(时序)、Citus(分布式)
5. 窗口函数:数据分析
6. CTE递归:层级数据处理
7. LISTEN/NOTIFY:实时通知
AI应用典型架构:
Spring AI → pgvector(向量检索) → PostgreSQL(文档存储) → OpenAI(生成)
38. pgvector vs Milvus?
| 维度 | pgvector | Milvus |
| 类型 | PG扩展 | 独立向量库 |
| 规模 | 百万级 | 十亿级 |
| SQL | 支持SQL过滤 | 自定义API |
| 运维 | PG运维即可 | 独立运维 |
| 事务 | 支持ACID | 不支持 |
| 适用 | 中小规模+SQL需求 | 大规模向量检索 |
39. 分布式SQL优化技巧?
-- 避免跨节点事务
-- 将关联数据放在同一分片(相同分片键)
-- 使用分区裁剪
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
-- 只扫描 orders_2025_01 分区
-- 批量操作代替循环
INSERT INTO orders SELECT * FROM staging_orders;
-- 使用CTE优化复杂查询
WITH user_orders AS (
SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT * FROM users u JOIN user_orders o ON u.id = o.user_id WHERE o.cnt > 10;
40. PostgreSQL监控体系?
核心监控:
1. pg_stat_activity:活跃连接、锁等待
2. pg_stat_statements:慢查询Top
3. pg_stat_user_tables:表统计
4. pg_stat_bgwriter:后台写统计
5. pg_locks:锁信息
6. pg_stat_replication:复制延迟
告警项:
- 连接数 > 80%
- 复制延迟 > 10秒
- 死元组 > 20%
- 慢查询 > 1秒
- 磁盘使用 > 80%
- 缓存命中率 < 95%
面试速记口诀
PG优势三件:JSONB灵活、窗口函数强大、pgvector AI加持
>
索引选择:等值B-tree、JSONB用GIN、大表BRIN、向量HNSW
>
MVCC核心:xmin/xmax无锁读写,VACUUM清理死元组
>
NewSQL三选:TiDB(MySQL兼容)、CockroachDB(强一致)、OceanBase(金融级)