← 返回首页 ❓ 面试题库
📑 本篇章节导航 (共9章,点击直达)
一、PostgreSQL基础与MySQL对比二、PostgreSQL高级查询三、PostgreSQL索引与优化四、PostgreSQL MVCC与事务五、PostgreSQL分区与运维六、pgvector与AI应用七、NewSQL数据库八、面试高频综合题面试速记口诀

Q117 PostgreSQL与NewSQL深度实战

20K Java+AI 岗位高频考点 — PostgreSQL是增长最快的开源数据库,pgvector更让它在AI领域大放异彩。覆盖PG核心原理、高级查询、MVCC、pgvector向量扩展、NewSQL(TiDB/CockroachDB)。

一、PostgreSQL基础与MySQL对比

1. PostgreSQL vs MySQL核心差异?

维度PostgreSQLMySQL
定位对象-关系型关系型
扩展性极强(扩展/自定义类型)中等
JSON支持JSONB(二进制,可索引)JSON(文本)
并发MVCC(无锁读)MVCC+锁
全文搜索内置tsvector需外置ES
窗口函数完整支持8.0+支持
分区声明式分区(10+)8.0+支持
GISPostGIS(最强)空间索引
适用复杂查询/分析/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 UncommittedPG不允许可能可能
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?

维度MySQLMongoDBTiDB
SQL完整不支持完整
扩展垂直水平水平
事务ACID4.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?

维度pgvectorMilvus
类型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(金融级)