LumaDB

上篇：PostgreSQL 标准大页 1. 为什么要大页：页表会吃掉内存 Linux 默认内存页 4 KB。进程把 共享内存（PostgreSQL 的 shared_buffers 等）映射进自己的虚拟地址空间时，内核要为这些映射维护 页表（Page Table）。 粗算（64 位、4 KB 页，教材常用 每页约 8 字节 页表开销）： 单进程映射 24 GB shared_buffers： (24G / 4K) × 8B ≈ 48 MB 页表/进程 500 个 backend（一连接一进程）： 500 × 48 MB ≈ 24 GB 页表（量级上可与 shared_buffers 相当） 页表不是连接瞬间一次性占满，而是访问共享页时 逐步分配；跑久了仍可能涨到很大，表现为 MemAvailable 莫名下降、甚至 OOM。 标准大页（显式 Huge Pages）：在 OS 预留 2 MB 或 1 GB 一页，同样 24GB 映射所需页表项数量大约按页大小成比例下降（相对 4KB 常差 两个数量级以上）。 2. 标准大页是什么（Hugetlbfs） 项 说明 机制 管理员通过 vm.nr_hugepages 等在启动前 预留 一块「大页池」 大小 常见 2 MB（Hugepagesize: 2048 kB）；部分机器还有 1 GB 分配特点 启动前预留、大小固定；不够则 PG huge_pages=on 会 启动失败 PostgreSQL 共享段尽量从 Hugetlb 分配；参数 huge_pages 与「普通 4KB 页 + 内核自动合并」的 透明大页（THP） 不是同一套机制（见下篇）。 ...

执行层（排序、Join、TempTable、缓冲池）：PG 与 MySQL 内存对照。 一、InnoDB 内存结构（17.5） 章节 结构名 作用 17.5.1 Buffer Pool Buffer Pool 表/索引页；LRU young/old 17.5.2 Change Buffer Change Buffer 二级索引页不在 BP 时缓存 DML；内存占 BP 份额 17.5.3 Adaptive Hash Index AHI B-tree 索引 自适应哈希；≠ SQL Hash Join Buffer Pool 在启动时按 innodb_buffer_pool_size 通过 malloc() 分配整池（官方 17.5.1）。 二、mysqld 完整内存结构图 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 mysqld 进程 │ ├─── InnoDB 存储引擎 ─────────────────────────────────────────── │ │ │ ├── Buffer Pool [17.5.1] │ │ ├── 数据页 / 索引页（LRU: young + old sublist） │ │ ├── Undo 页（undo tablespace 读入后作为普通 frame） │ │ └── Change Buffer 相关页（IBUF_INDEX / IBUF_BITMAP） │ │ │ ├── Change Buffer（逻辑结构，内存占 BP 份额） [17.5.2] │ │ │ ├── Adaptive Hash Index (AHI) [17.5.3] │ │ │ ├── Log Buffer → #innodb_redo [17.6.5] │ │ │ ├── Doublewrite Buffer（内存）+ .dblwr 文件 [17.6.4] │ │ │ └── Dictionary Cache 等 │ ├─── SQL Server 层 ───────────────────────────────────────────── │ ├── 每连接 THD：sort buffer、Join Buffer、Hash Join 表、 │ │ TempTable、Binlog Cache、mem_root … │ └── 全局：Table Open/Definition Cache、PFS、权限缓存 … │ └─── 磁盘：表空间、#innodb_redo、undo、doublewrite、binlog、tmpdir 三、redo / undo / 双写 / binlog 落在哪 组件 MySQL 内存结构名 磁盘 PostgreSQL 对照 Redo Log Buffer #innodb_redo WAL Buffers → pg_wal Undo Buffer Pool 中的 undo 页 undo tablespace undo 文件 + 缓冲池中页 Doublewrite Doublewrite Buffer + 文件 .dblwr 等 实现机制不同 Binlog Binlog Cache binlog 文件 逻辑解码 / WAL 热点数据页 Buffer Pool LRU young .ibd Shared Buffers 四、PostgreSQL 平行结构图 1 2 3 4 5 postgres 实例 ├── 共享内存：Shared Buffers、WAL Buffers、锁 … ├── 每 backend：MemoryContext（Tuplesort、HashJoinTable…）、 │ local buffers（临时表）、pgsql_tmp └── 磁盘：base/、pg_wal、表空间 五、执行层结构速查（与总图关系） 场景 MySQL PostgreSQL 排序 sort buffer Tuplesortstate Hash Join Hash Join 哈希表 HashJoinTable BNL Join Buffer — GROUP BY / 物化 TempTable Materialize / HashAggregate 表页 Buffer Pool Shared Buffers 六、手册索引 10.12.3.1 How MySQL Uses Memory 17.5 InnoDB In-Memory Structures 17.5.1 Buffer Pool 17.5.2 Change Buffer 17.5.3 Adaptive Hash Index 17.6.4 Doublewrite Buffer 17.6.5 Redo Log 10.4.4 Internal Temporary Tables

上篇：PostgreSQL 1. 排序（Tuplesort / work_mem） 结构 层级 作用 Tuplesortstate backend MemoryContext 单次 Sort 节点工作区 BufFile / logtape pgsql_tmp 超过 work_mem 落盘 Shared Buffers 共享内存 表数据页；不承担排序工作区 多 Sort 节点 → 可能多份 work_mem（× 并发）。 EXPLAIN ANALYZE：quicksort vs external merge（spill）。 参数：work_mem 2. 等值连接（Hash Join） 等值 JOIN：Build 小表 HashJoinTable → Probe 大表。无索引时常用；有索引时多为 Index Nested Loop。 结构 说明 HashJoinTable nodeHashjoin.c，常在 hashCxt BufFile 过大时 batch 落盘 参数：work_mem；PG 13+ 可用 hash_mem_multiplier。 EXPLAIN：Hash Join → Hash → 子扫描。 ...

前言 PostgreSQL 的事务系统对用户基本透明：你执行 BEGIN / COMMIT，很少直接碰到底层的 CommitLog（CLOG） 和 32 位事务 ID（XID）。但一旦遇到 长事务、autovacuum 不及时、XID wraparound 告警，如果不理解这些机制，很容易把问题当成「磁盘满了」或「连接数爆了」去排查，白白浪费时间。 本文基于 PostgreSQL 的 CommitLog 与 XID 原理，系统梳理其工作机制、性能优化、回卷防护与故障案例，并与 MySQL InnoDB 做对照——帮助已经熟悉 MySQL MVCC 的 DBA，快速建立 PostgreSQL 事务运维的心智模型。 一、CommitLog（CLOG）是做什么的 PostgreSQL 把事务的最终状态记录在 CommitLog 中。从 PostgreSQL 10 起，对应文件位于数据目录的 pg_xact 子目录（早期版本叫 pg_clog）。 每条事务有四种状态，用 2 bit 编码： 状态值 宏名 含义 0x00 TRANSACTION_STATUS_IN_PROGRESS 进行中 0x01 TRANSACTION_STATUS_COMMITTED 已提交 0x02 TRANSACTION_STATUS_ABORTED 已回滚 0x03 TRANSACTION_STATUS_SUB_COMMITTED 子事务已提交 CommitLog 本质上是一个位图文件，因此具备以下特点： 32 位 XID 空间下，理论上最多记录约 20 亿 个事务状态； 整个 CLOG 最多占用约 512 MB 空间； CLOG 也会随 VACUUM / FREEZE 被清理，触发阈值与 autovacuum_freeze_max_age（默认 2 亿）相关。 1.1 读每一行都要查 CLOG 吗？ Heap 表每一行都有 xmin（插入事务 ID）和 xmax（删除/更新事务 ID）。如果每次判断可见性都去读 CLOG 文件，性能显然无法接受。 ...

一、为什么表权限还不够 普通权限控制的是： 1 某个用户能不能访问某张表 例如： 1 GRANT SELECT ON sales.orders TO report_user; 这表示 report_user 可以查询 sales.orders。 但很多企业场景需要更细的控制： 1 2 3 4 5 销售只能看自己区域的订单 医生只能看本科室病人的记录 租户只能访问自己的数据 分公司只能看本公司的财务数据 客服只能看被分配给自己的工单 这些不是“能不能访问表”的问题，而是： 1 能访问表里的哪些行 这就需要 PostgreSQL 的 RLS。 二、RLS 是什么 RLS 是 Row Level Security，行级安全。 表级权限控制： 1 能不能 SELECT orders 表 RLS 控制： 1 SELECT orders 时能看到哪些行 例如 orders 表中有字段： 1 2 3 tenant_id department_id owner_user RLS 可以根据当前会话、当前用户或应用设置的上下文，只返回符合条件的行。 ...

一、企业权限管理的目标 企业级权限管理不是简单地“让用户能连上数据库”，而是要回答几个问题： 1 2 3 4 5 6 7 8 谁可以登录？ 谁可以连接哪个数据库？ 谁可以访问哪个 schema？ 谁可以读写哪些表？ 谁可以修改结构？ 谁可以管理权限？ 谁执行过高危操作？ 账号离职或下线后如何安全清理？ 权限治理的目标是： 满足业务访问需要 遵守最小权限原则 降低误操作和泄露风险 方便审计 方便账号生命周期管理 避免权限长期失控 二、最小权限原则 最小权限原则是企业权限管理的核心。 含义是： 1 一个用户或程序只拥有完成工作所必需的最小权限。 不推荐： 1 2 GRANT ALL PRIVILEGES ON DATABASE sales_db TO app_user; ALTER ROLE app_user SUPERUSER; 推荐： 1 2 3 4 GRANT CONNECT ON DATABASE sales_db TO app_user; GRANT USAGE ON SCHEMA sales TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_user; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales TO app_user; 如果应用不需要删数据，就不要给 DELETE。 ...

一、为什么要单独设计只读用户和应用用户 生产环境中，不建议所有程序和人员都使用同一个数据库账号。 常见错误做法： 1 应用、报表、开发、DBA 都使用 postgres 或同一个 app_user 这样会带来几个问题： 无法区分是谁执行了操作 报表查询账号可能误删或误改数据 应用账号权限过大，一旦泄露风险很高 权限审计困难 无法做到最小权限 更合理的设计是： 1 2 3 4 5 6 7 readonly_user：只读查询 app_user：应用读写 etl_user：数据导入导出 backup_user：备份 monitor_user：监控 dba_user：日常管理 postgres：超级管理，不用于业务连接 本篇重点整理只读用户和应用读写用户的标准创建方法。 二、准备示例环境 假设数据库名为： 1 sales_db 业务 schema 为： 1 sales 创建 schema： 1 CREATE SCHEMA IF NOT EXISTS sales; 示例表： 1 2 3 4 5 6 7 8 9 CREATE TABLE sales.orders ( id bigserial PRIMARY KEY, order_no text NOT NULL, customer_id bigint NOT NULL, amount numeric(12,2) NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); 注意 bigserial 会创建序列，因此应用插入数据时除了表权限，还可能需要序列权限。 ...

一、GRANT 和 REVOKE 解决什么问题 PostgreSQL 中，角色属性和对象权限是两类不同的权限。 角色属性例如： 1 2 3 4 5 6 LOGIN SUPERUSER CREATEDB CREATEROLE CONNECTION LIMIT VALID UNTIL 这些通常通过 CREATE ROLE 或 ALTER ROLE 管理。 对象权限例如： 1 2 3 4 5 6 7 8 CONNECT USAGE CREATE SELECT INSERT UPDATE DELETE EXECUTE 这些通常通过 GRANT 和 REVOKE 管理。 简单说： 1 2 GRANT：授予权限 REVOKE：回收权限 二、数据库级权限 数据库级常见权限有： 1 2 3 CONNECT：允许连接数据库 CREATE：允许在数据库中创建 schema TEMPORARY / TEMP：允许创建临时表 允许用户连接数据库： ...

一、先看 PostgreSQL 的层级 PostgreSQL 的对象层级可以理解为： 1 2 3 4 5 6 7 8 PostgreSQL 实例 └── database └── schema ├── table ├── view ├── sequence ├── function └── type 也就是说，PostgreSQL 的 database 下面还有一层 schema。表、视图、函数等对象并不是直接属于 database，而是属于 database 里的某个 schema。 例如： 1 2 3 4 5 6 7 8 9 10 11 12 sales_db ├── public │ ├── users │ └── orders ├── sales │ ├── customers │ └── orders ├── finance │ ├── invoices │ └── payments └── audit └── operation_logs 这里 public.orders 和 sales.orders 可以同时存在，因为它们位于不同 schema，完整名称不同。 ...

一、为什么 PostgreSQL 权限容易混乱 很多人刚接触 PostgreSQL 权限时，会下意识拿 MySQL 来类比： 1 2 MySQL：用户 + 数据库 + 表权限 PostgreSQL：角色 + 数据库 + schema + 对象权限 + 默认权限 + RLS PostgreSQL 的权限体系更细，灵活性也更高，但如果没有先建立层级概念，就很容易出现下面这些问题： 用户明明有表的 SELECT 权限，却仍然提示 permission denied for schema 创建了只读用户，但用户还能在 public schema 里建表 给了 GRANT SELECT ON ALL TABLES，新建表却无法查询 不知道 CREATE USER 和 CREATE ROLE 到底有什么区别 不知道 public schema 和 PUBLIC 角色是不是同一个东西 删除用户时提示仍然有对象依赖，无法 DROP ROLE 要理解这些问题，必须先把 PostgreSQL 的权限模型分层看清楚。 二、PostgreSQL 的对象层级 PostgreSQL 常见层级如下： 1 2 3 4 5 6 7 8 PostgreSQL 实例 └── database └── schema ├── table ├── view ├── sequence ├── function └── type 一个 PostgreSQL 实例里可以有多个数据库。每个数据库内部又可以有多个 schema。表、视图、序列、函数等对象通常位于某个 schema 下面。 ...