摘要：本文详细对比了 PostgreSQL 和 MySQL 的主要区别，包括功能、性能、使用场景等。同时介绍了 PostgreSQL 中的 schema 概念及其作用，并解释了 pgAdmin 工具中表概况界面各列的含义，以及如何查看表结构和获取 CREATE TABLE 语句。
PostgreSQL 与 MySQL 主要区别
PostgreSQL 和 MySQL 是两种非常流行的开源关系型数据库管理系统（RDBMS），它们在很多方面都有不同的特点和适用场景。
基本定位与设计哲学
| 特性 |
PostgreSQL |
MySQL |
| 定位 |
强调功能丰富、可扩展性和标准兼容性 |
强调高性能、易用性和广泛适用性 |
| 设计哲学 |
面向对象的关系型数据库，支持复杂查询和高级特性 |
更传统的关系型数据库，注重速度和实用性 |
功能对比
PostgreSQL 的优势
- 支持更复杂的 SQL 查询（如窗口函数、CTE、递归查询等）
- 支持 JSONB 数据类型，性能优于 MySQL 的 JSON
- 支持自定义数据类型、操作符、函数、索引方法等
- 支持全文搜索、地理空间数据（通过 PostGIS 插件）
- 强大的事务处理能力，ACID 支持更严格
- 支持并发版本控制（MVCC），读写不阻塞
- 复杂的连接（JOIN）优化更好
MySQL 的优势
- 性能更高（尤其是读操作），适合高并发、低延迟场景
- 架构简单，部署和管理更容易
- 社区庞大，文档丰富，学习曲线相对平缓
- 支持多种存储引擎（如 InnoDB、MyISAM、Memory 等）
- 主从复制、集群架构成熟，适合 Web 应用
- 对 SQL 标准的支持较为宽松，兼容性好但不如 PG 严格
使用场景对比
| 使用场景 |
推荐数据库 |
| 复杂查询、分析型应用（OLAP） |
PostgreSQL |
| Web 应用、高并发读多写少 |
MySQL |
| 地理信息系统（GIS） |
PostgreSQL（配合 PostGIS） |
| 实时数据分析 |
PostgreSQL |
| 电商、社交类系统 |
MySQL |
| 需要高度可定制和扩展 |
PostgreSQL |
| 轻量级应用、快速开发 |
MySQL |
安全性
| 特性 |
PostgreSQL |
MySQL |
| 行级安全策略 |
✅ 原生支持 |
❌ 不原生支持 |
| 角色权限管理 |
更细粒度，支持基于角色的访问控制 |
权限模型较简单 |
| SSL/TLS 支持 |
默认支持较好 |
支持，但配置稍复杂 |
扩展性与插件生态
| 特性 |
PostgreSQL |
MySQL |
| 自定义函数/触发器 |
✅ 支持多种语言（PL/pgSQL、Python、Perl 等） |
✅ 支持，但灵活性略差 |
| 插件系统 |
强大，支持大量官方和第三方插件 |
插件较少，依赖存储引擎 |
| 分布式支持 |
有分布式解决方案（如 Citus） |
有分库分表中间件（如 MyCat、ShardingSphere） |
性能比较
| 场景 |
PostgreSQL |
MySQL |
| 写入密集型 |
较慢（但稳定） |
更快（尤其在批量插入） |
| 读取密集型 |
可优化到接近 MySQL 水平 |
更快 |
| 复杂查询 |
更强 |
相对较弱 |
| 并发处理 |
MVCC 机制更强 |
表锁问题已改善（InnoDB） |
数据类型支持
| 类型 |
PostgreSQL |
MySQL |
| JSON 支持 |
✅ JSONB（高效二进制格式） |
✅ JSON（文本解析） |
| 数组 |
✅ 支持 |
❌ 不支持 |
| 范围类型 |
✅ 支持（如日期范围、数字范围） |
❌ 不支持 |
| 自定义类型 |
✅ 支持 |
❌ 不支持 |
| 时间戳精度 |
更高（微秒级） |
秒或毫秒级 |
事务与一致性
| 特性 |
PostgreSQL |
MySQL |
| ACID 支持 |
✅ 所有操作默认在事务中 |
✅ InnoDB 支持 |
| 多版本并发控制（MVCC） |
✅ 原生支持 |
❌ InnoDB 有类似机制但非标准 MVCC |
总结建议
| 如果你关注： |
推荐选择： |
| 功能强大、标准兼容、复杂查询 |
PostgreSQL |
| 快速部署、易用性、Web 应用 |
MySQL |
| 高并发、读多写少 |
MySQL |
| GIS、JSON、大数据分析 |
PostgreSQL |
| 高可用架构、主从复制 |
MySQL 成熟方案更多 |
| 扩展性强、可定制 |
PostgreSQL |
PostgreSQL 中的 Schema 概念
public 架构是什么？
在 PostgreSQL 中，Schema 是一个命名空间，用来组织数据库对象（如表、视图、函数等）。`public` 是 PostgreSQL 的默认架构，当你创建表时没有指定 schema，就会自动放到 `public` 下。
为什么表要在架构下面？
PostgreSQL 的设计要求所有表必须属于某个 schema，这是为了实现更好的逻辑隔离、权限控制和模块化管理。
示例：创建表并指定 schema
CREATE TABLE public.users (
id SERIAL PRIMARY KEY,
name TEXT
);
-- 或者创建新的 schema 并使用它
CREATE SCHEMA sales;
CREATE TABLE sales.orders (
order_id SERIAL PRIMARY KEY,
amount NUMERIC
);
查看架构和表
\dn -- 查看所有 schema
\dt public.* -- 查看 public 架构下的所有表
为什么要使用多个 schema？
- 逻辑隔离：比如开发、测试、生产环境可以用不同的 schema。
- 权限控制：不同用户或角色可以访问不同的 schema。
- 多租户支持：每个客户的数据可以放在独立的 schema 中。
- 模块化设计：大型项目中，按功能划分 schema，便于维护。
pgAdmin 表概况界面各列含义
| 列名 |
含义 |
| 表名 |
表示当前统计信息所对应的表名称。 |
| 总量 |
表示该表占用的总存储空间大小。 |
| 元组已插入 |
表示已经成功插入到表中的记录（元组）数量。 |
| 元组已更新 |
表示已经通过 UPDATE 操作修改过的记录（元组）数量。 |
| 元组已删除 |
表示已经通过 DELETE 操作标记为删除的记录（元组）数量。 |
| 元组已热更新 |
表示最近一段时间内被频繁更新的活跃记录（元组）数量。 |
| 存活元组 |
表示当前表中仍然有效的记录（元组）数量。 |
| 死亡元组 |
表示已经被标记为删除但尚未被清理的记录（元组）数量。 |
如何查看表结构和获取 CREATE TABLE 语句
方法一：使用图形界面查看表结构
在 pgAdmin 中展开表，右侧标签页显示字段、约束、索引等信息。
方法二：通过“脚本”功能导出 CREATE TABLE 语句
右键点击表 → Script → CREATE Script，会生成完整的 CREATE TABLE 语句。
方法三：使用 SQL 查询获取 CREATE TABLE 语句
SELECT pg_get_tabledef('schema_name.table_name');
方法四：使用备份功能生成建表语句
右键点击表 → Backup → Backup...，设置为 Plain Text，勾选 Only schema。
方法五：SQL 查询表结构
SELECT
column_name,
data_type,
is_nullable,
column_default
FROM
information_schema.columns
WHERE
table_schema = 'public'
AND table_name = 'your_table_name';
总结
| 方法 |
是否推荐 |
用途 |
| 图形界面查看结构（Columns/Constraints） |
✅ 推荐 |
快速查看字段、约束等信息 |
| 右键 → Script → CREATE Script |
✅✅ 强烈推荐 |
获取完整建表语句 |
| 使用 pg_get_tabledef() 函数 |
✅ 推荐 |
在查询窗口中快速获取建表语句 |
| 使用备份功能导出结构 |
⭕ 可选 |
适合批量导出多个表或整个 schema 的结构 |
| SQL 查询表结构 |
✅ 推荐 |
编写脚本或自动化时很有用 |