PostgreSQL 表

PostgreSQL 表（Table）体系是数据库设计的核心单元，也是开发者与数据打交道的最直接对象。“PostgreSQL 表” 这个概念从创建、存储、约束、索引、分区、统计信息、安全、维护、性能等角度串成一条可落地的知识链，既覆盖日常高频操作，也穿插容易被忽略的细节与最佳实践。

──────────────────
1. 表的物理与逻辑视角
• 逻辑上：表是行列二维结构，列有名称、数据类型、默认值、约束；行是数据实体。
• 物理上：表对应一个或多个文件（1 GB 分段），位于 base/<db_oid>/<relfilenode>；TOAST 超长列另存 toast 表；分区表则对应一张“父表”+若干“子表”。
• 系统目录：pg_class 记录 relname、relkind（r=ordinary table），pg_attribute 记录列定义，pg_constraint 记录约束，这三张“元数据表”是理解表结构的起点。

──────────────────
2. 创建与数据类型
CREATE TABLE country (
id serial PRIMARY KEY,
name text NOT NULL,
continent varchar(50) CHECK (continent <> ''),
population bigint CHECK (population >= 0),
gdp numeric(14,2),
updated_at timestamptz DEFAULT now()
);
• serial 本质是 int + sequence，适合自增主键；也可用 identity column（SQL 标准）。
• text/varchar/char 区别：text 最灵活，varchar(n) 限制长度，char(n) 定长补空，几乎无性能差异，按业务语义选择。
• numeric 适合货币；浮点用 real/double；日期 timestamptz 默认带时区，可减少“夏令时 Bug”。
• 生成列：PostgreSQL 12+ 支持 STORED 生成列（持久化）或 VIRTUAL（规划中）。

──────────────────
3. 约束体系
NOT NULL / CHECK / UNIQUE / PRIMARY KEY / FOREIGN KEY / EXCLUSION（排他约束）。
• 建议总是显式命名约束：CONSTRAINT pk_country PRIMARY KEY (id)。
• 外键必须引用唯一键；ON DELETE CASCADE/SET NULL 决定级联行为。
• DEFERRABLE 约束可把检查推迟到事务提交时，适合“循环引用”场景。
• 排他约束（gist(exclusion)）可用于“时间段不重叠”需求，例如会议室预订系统。

──────────────────
4. 索引：让查询飞
• B-tree：默认、支持 =, <, >, LIKE 'abc%'。
• GIN：倒排，适合全文检索、jsonb 数组包含。
• GiST：几何、范围、排他约束。
• SP-GiST：空间分区树，适合 IP 段、四叉树。
• BRIN：块级索引，超大体量顺序数据，几 MB 索引可覆盖 TB 表。
• 表达式索引：CREATE INDEX ON country(lower(name))，让不区分大小写查找走索引。
• 覆盖索引（INCLUDE）：PostgreSQL 11+ 可把非键列放入索引，避免回表。
• 部分索引：WHERE population IS NOT NULL，可减少索引体积。
• REINDEX CONCURRENTLY 可在线重建索引。

──────────────────
5. 分区与分片
分区（partitioning）是单机水平切表；分片（sharding）需 Citus/FDW。
• 声明式分区（PostgreSQL 10+）：
CREATE TABLE logs_2025 PARTITION OF logs
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
• 支持 range/list/hash。
• 分区裁剪（pruning）：WHERE log_date >= '2025-08-01' 只扫 8 月分区。
• 分区表上的主键必须包含分区键。
• 使用 pg_partman 可自动生成/维护分区。
• 注意：触发器式旧分区（inheritance + trigger）已过时，不建议新项目使用。

──────────────────
6. 统计信息与自动清理
• ANALYZE 收集列分布、MCV（最常见值）、直方图、相关性，指导优化器。
• autovacuum 不仅回收死元组，也自动 ANALYZE；调大 autovacuum_vacuum_cost_limit 可加速大表清理。
• 扩展统计信息：CREATE STATISTICS s1 (dependencies) ON continent, country_name; 解决列相关性问题。

──────────────────
7. 并发控制与行级锁
• MVCC：读不阻塞写，写不阻塞读。
• 行级锁：SELECT … FOR UPDATE/SHARE；SKIP LOCKED 可做任务队列。
• 事务隔离：读已提交（默认）、可重复读、串行化（SSI）。
• advisory lock：pg_advisory_xact_lock() 实现跨会话自定义锁。

──────────────────
8. 安全与权限
• GRANT SELECT, INSERT ON country TO app_user;
• 列级权限：GRANT UPDATE (gdp) TO finance_role;
• RLS（Row Level Security）：
ALTER TABLE country ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_continent ON country USING (continent = current_setting('app.continent')::text);
• 敏感列加密：pgcrypto 提供 pgp_sym_encrypt/pgp_sym_decrypt；或透明加密 TDE（pg_tde 扩展）。

──────────────────
9. 维护与监控
• VACUUM FULL：重写全表，锁表；日常用普通 VACUUM 即可。
• CLUSTER：按 B-tree 索引物理重排，提升 I/O 顺序性。
• pg_repack 扩展可在线 CLUSTER。
• 监控：
– pg_stat_user_tables：seq_scan、n_tup_upd、n_dead_tup。
– pg_statio_user_tables：heap_blks_read、idx_blks_hit。
– pg_stat_statements：定位慢 SQL。
• 定期：
– 检查膨胀：SELECT schemaname,tablename,pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) FROM pg_tables ORDER BY 3 DESC;
– 备份：pg_dump -Fc + pgBackRest 增量。

──────────────────
10. 实用技巧与最佳实践
• 使用 bigint 而不是 int 做主键，避免 21 亿上限。
• 避免“大事务更新/删除”一次性生成大量死元组，可分批 + 游标。
• 合理设置 fillfactor：高更新表设为 70，减少页分裂。
• 用 EXPLAIN (ANALYZE, BUFFERS) 读计划：关注 loops、actual rows、I/O buffers。
• 用 generated column 或视图封装计算列，避免应用层重复逻辑。
• 在 DDL 中写 COMMENT ON TABLE / COLUMN，元数据即文档。
• 用 psql \d+ tablename 一键查看“列、约束、索引、分区”全套信息。
• 升级大版本后，务必跑 vacuumdb --analyze-in-stages，避免统计信息真空。

──────────────────
结语
PostgreSQL 的表不仅仅是“放数据的地方”，它融合了类型系统、约束、索引、存储、统计、安全、并发、扩展等全链路能力。理解表，就等于理解了 PostgreSQL 大半的架构哲学：把复杂性封装在数据库内部，让应用层保持简洁可靠。希望这篇 1500 字的速览能成为你继续深挖 PostgreSQL 的路线图。