PostgreSQL JSONB查询优化

网上关于这个的资料不少，但很多都只讲原理不给代码。这里补一个完整的可运行示例，亲测有效。

先搞清楚原理

先说基本原理。PostgreSQL的核心思路是事务隔离级别。简单来说就是：把输入经过一系列变换，最终得到想要的结果。这个过程可以用下面的代码来理解：

-- 事务与并发控制
BEGIN;

SELECT * FROM products WHERE id = 3 FOR UPDATE;

UPDATE products
SET updated_at = NOW()
WHERE id = 3;

-- 乐观锁
UPDATE products
SET version = version + 1, value = 'new_value'
WHERE id = 3 AND version = 5;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

COMMIT;

-- 死锁检测
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
WHERE blocked_locks.granted IS FALSE;

实际怎么用

实际用的时候有几个注意点。首先是配置问题，不同环境参数可能不一样。其次是性能，数据量大的时候要注意查询优化。看这段实际运行的代码：

-- 备份与恢复策略
-- 全量备份
pg_dump -h localhost -U postgres -Fc mydb > backup_9_$(date +%Y%m%d).dump

-- 增量备份（WAL归档）
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f';

-- 时间点恢复
RESTORE DATABASE mydb FROM backup_9
  UNTIL TIME '2024-06-15 14:30:00';

-- 表空间管理
CREATE TABLESPACE ts_logs LOCATION '/data/pg_tablespaces/logs';
ALTER TABLE logs SET TABLESPACE ts_logs;

进阶一点的用法

有些场景下PostgreSQL的常规写法不够用，需要做查询优化。这个改动涉及的地方不多，但要注意顺序：

容易踩的坑

总结几个实际项目中的教训：PostgreSQL在查询优化上容易出问题。建议写单元测试覆盖这些边界情况，免得线上出故障才后悔。

写在最后

PostgreSQL的要点就这些。核心是查询优化，其他都是围绕这个展开的。代码已经贴在前面了，照着跑一遍基本就能上手。后续有新的发现再更新。