PostgreSQL OLTP

PostgreSQL OLTP（联机事务处理）常用语法大全，涵盖业务系统中最常出现的增删改查、事务、锁、索引、约束、并发控制等等。内容非常贴近实际开发与运维。

1. 表结构操作（DDL）

创建表

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    TEXT UNIQUE NOT NULL,
    email       TEXT UNIQUE NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

修改表

ALTER TABLE users ADD COLUMN status INT DEFAULT 1;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP COLUMN status;

添加索引

OLTP 场景非常依赖索引性能。

CREATE INDEX idx_users_username ON users (username);
CREATE INDEX idx_users_created_at ON users (created_at DESC);

删除/重建索引

DROP INDEX idx_users_username;
REINDEX TABLE users;

2. 增删改查（CRUD）

INSERT

INSERT INTO users (username, email)
VALUES ('martin', 'martin@example.com')
RETURNING id;

批量插入

INSERT INTO orders (uid, amount) VALUES
(1, 100), (1, 50), (2, 300);

UPDATE

UPDATE users
SET email = 'new@example.com'
WHERE id = 1;

DELETE

DELETE FROM users WHERE id = 1;

SELECT（常用）

SELECT id, username FROM users WHERE status = 1 LIMIT 10 OFFSET 0;

3. 分页（业务系统用最多）

SELECT * FROM orders
WHERE status = 1
ORDER BY id DESC
LIMIT 20 OFFSET 0;

更高性能的 cursor-based pagination：

SELECT * FROM orders
WHERE id < 50000
ORDER BY id DESC
LIMIT 20;

4. JOIN（业务系统三大关键查询核心）

SELECT u.id, u.username, o.amount
FROM users u
JOIN orders o ON u.id = o.uid
WHERE o.amount > 100;

常用：

INNER JOIN：两边都有的数据
LEFT JOIN：保留左边全部
RIGHT JOIN：保留右边全部
FULL JOIN：全部保留
CROSS JOIN：笛卡尔积

示例（左连接）：

SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.uid;

5. 聚合（OLTP 统计小需求）

SELECT uid, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY uid
ORDER BY total DESC;

常见：

COUNT
SUM
AVG
MIN / MAX

6. UPSERT（业务非常常用）

避免重复插入：

INSERT INTO users (username, email)
VALUES ('martin', 'martin@example.com')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email;

7. 事务（OLTP 系统必用）

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

回滚：

ROLLBACK;

8. 并发与锁

行锁（FOR UPDATE）

SELECT * FROM orders WHERE id = 100 FOR UPDATE;

跳过锁

SELECT * FROM orders
WHERE id = 100
FOR UPDATE SKIP LOCKED;

常用于队列消费者。

共享锁

SELECT * FROM inventory FOR SHARE;

9. 约束（保证数据质量）

NOT NULL

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

UNIQUE

ALTER TABLE users ADD CONSTRAINT u_email UNIQUE(email);

CHECK

ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0);

外键

ALTER TABLE orders
ADD CONSTRAINT fk_uid FOREIGN KEY(uid) REFERENCES users(id);

10. JSON 操作（PostgreSQL 现代 OLTP 最大优势）

查询 JSON 字段：

SELECT data->>'name' AS name
FROM user_logs;

更新 JSON：

UPDATE user_logs
SET data = jsonb_set(data, '{ip}', '"127.0.0.1"');

查询 JSON 包含：

SELECT * FROM user_logs
WHERE data @> '{"action": "login"}';

11. 时间序列（OLTP 常用）

按日查询：

SELECT * FROM logs
WHERE created_at::date = CURRENT_DATE;

时间范围：

SELECT * FROM logs
WHERE created_at BETWEEN NOW() - INTERVAL '1 day' AND NOW();

12. CTE（可读性更强）

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day'
)
SELECT * FROM recent_orders WHERE amount > 100;

13. 分区表（高并发 / 大表优化）

CREATE TABLE logs (
    id BIGSERIAL,
    created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

创建分区：

CREATE TABLE logs_2025_01 PARTITION OF logs
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

14. 监控 OLTP 性能的常用语法

查看慢 SQL

SELECT * FROM pg_stat_activity WHERE state != 'idle';

查看表大小

SELECT pg_size_pretty(pg_total_relation_size('orders'));

查看索引使用情况

SELECT * FROM pg_stat_user_indexes;

15. 最佳实践（OLTP 场景）

主键必须用 BIGSERIAL 或 UUID，不要用 SERIAL。
字符类型用 text，不要用 varchar(n)。
所有外键字段必须创建索引。
分页使用 cursor 分页优先于 offset。
大表必须分区（按日期）。
JSONB 比 JSON 更快，优先使用 JSONB。
定期 vacuum + analyze。