[ORDBMS/对象建模] PostgreSQL 概述

0 序

近两天捣鼓群晖 NAS，发现其内置数据库是 PostgreSQL 数据库（11.11 版本）。
为此，在把玩了一下该数据库后，在此简单总结一下该数据库。

1 概述：PostgreSQL

产品定位

PostgreSQL

https://www.postgresql.org/

PostgreSQL 是开源对象关系型数据库（ORDBMS），被业内叫"开源界的 Oracle"。

既能扛 OLTP 业务系统（事务、订单、用户中心等）

又能写复杂 OLAP 查询（窗口函数、CTE、递归）

还可通过扩展变成：文档库（JSONB）、空间库（PostGIS）、向量库（pgvector）

许可：类 BSD 的 PostgreSQL License，商用/改源码/闭源分发都自由。

对大数据开发者的定位："【业务系统】到【数据湖】之间的【可信数据底座】 + 【轻量数仓】 + 【AI 向量附属存储】"。

优劣点

优势

SQL 标准兼容度极高（SQL:2023 核心特性覆盖 ~170/177），窗口函数/CTE/MERGE 原生支持
真 ACID + MVCC（2001 起），高并发读写不脏读
扩展机制无敌：pgvector（AI）、PostGIS（地图）、TimescaleDB（时序）、FDW（跨源外表）
类型丰富：JSONB、数组、UUID、枚举、范围类型
云与托管成熟： RDS/Aurora PG、Cloud SQL、AlloyDB、Azure PG、Neon、Supabase

劣势

默认行存，大宽表 OLAP 性能不如 ClickHouse/Doris/StarRocks
连接数 = 进程数，高并发短连接需配 PgBouncer 连接池
调优项多（autovacuum、shared_buffers、work_mem），新手易"跑得慢怪 PG"
社区版缺原生 TDE/审计脱敏，企业级合规要靠 EDB/云厂商补

诞生背景与研发团队

起源：1986 年 UC Berkeley 由 Michael Stonebraker（图灵奖得主）主导的 POSTGRES 项目，受 DARPA/NSF 资助，初衷是超越早期 Ingres，支持抽象数据类型与复杂对象。
1994 年 Andrew Yu & Jolly Chen 加入 SQL 解释器 → Postgres95
1996 年更名 PostgreSQL 6.0，转向 SQL 标准 + 社区驱动
现维护方：PostgreSQL Global Development Group（PGDG），全球志愿者核心组 + 各云厂/EDB/2ndQuadrant 等商业公司共建，每年一个大版本，约 5 年支持周期

版本发展沿革

1989 POSTGRES 4.2 外发 → 1996 PG 6.0（定名）
8.0（2005）：原生 Win + 完善 MVCC/ACID
9.0（2010）：流复制；9.6（2016）：并行查询
10（2017）：逻辑复制 + 原生分区；
12（2019）：分区/索引优化
14~16：高并发 vacuum、并行 DML、逻辑复制增强
17（2024）/ 18（2025-09 大版本，2026-02 出 18.3）：新 wire 协议、逻辑复制与优化器再提速

特别注意：生产环境尽量 ≥ PG 14，云上直接用托管最新大版本。

竞品对比（Oracle / MySQL / PG / Doris）

维度	Oracle	MySQL	PostgreSQL	Apache Doris
定位	商业企业级 OLTP	互联网轻量 OLTP	开源全能 ORDBMS	MPP 实时数仓
协议	商业收费	双协议(社区开源)	BSD 类自由开源	Apache 2.0
SQL 标准	高但有私有语法	中等(~70%)	极高(~90%+)	MySQL 语法兼容+分析扩展
复杂查询	强	一般	强(窗口/CTE/递归)	强(列存/向量化)
扩展生态	封闭	中等	极强(pgvector等)	中等(向量/湖仓加速)
典型场景	银行核心/ERP	Web 业务/CMS	业务系统+轻数仓+AI底座	报表/日志/广告/OLAP
大数据领域的扮演角色	源系统	源系统	贴源层/维表/向量库	数仓查询引擎

DB-Engines 2025-2026 综合热度：

Oracle #1(≈1132)、MySQL #2(≈846)、SQL Server #3、PostgreSQL #4(≈650-688，分数持续上涨)、Doris 在关系型总榜外单列（MPP 细分）。

Roadmap 衍化方向（2025+）

AI 原生化：pgvector 持续增强（HNSW 索引、StreamingDiskANN、量化），PG 内核考虑向量类型一等公民
云原生存算分离：Neon 类分支、AlloyDB Omni 本地 K8s 部署
Lakehouse 外表：通过 FDW/Iceberg 外表直查数据湖（EDB Analytics Accelerator 等）
运维自动化：逻辑复制双向、增量备份更轻、AI 调优建议（如 AlloyDB AI 自然语言转 SQL）

市占率与趋势

DB-Engines 流行度：稳居全球第 4、开源关系型第 2（仅次于 MySQL），但分数增速第一梯队
Stack Overflow 2025 开发者调查：使用率 55.6% 排所有数据库第一，超 MySQL 40.5%
大数据行业：在"湖仓一体+BI 贴源层+特征表+RAG 知识库"场景渗透率快速超 MySQL

AI 与大数据领域的定位 *

PG 不是"替代 Spark/Doris"，而是扮演"带事务的轻量智能数据层"：

典型厂商与场景

AWS：RDS/Aurora PG + pgvector → 电商推荐、RAG 客服
Google Cloud AlloyDB：pgvector + Gemini + 语义重排 → 专利检索、商品推荐、自然语言转 SQL
Azure PG：azure_ai 扩展直连 Azure OpenAI/ML → 情感分析、PII 脱敏、RAG
EDB Postgres AI：Iceberg/Delta 外表 + 向量 + AI Agent → 企业知识库、湖仓查询加速
Neon / Supabase：Serverless PG 给 LLM 应用存会话/用户/向量/定时任务
国内：腾讯云/阿里云 RDS PG 跑标签维表、特征快照、Doris/Spark 的结果回写层

大数据流水线里的位置

业务库(MySQL/Oracle) ─CDC(Flink/Debezium)→ PG(贴源/维表/质量校验)
                                              │
                                              ├─ 推 Doris/ClickHouse(明细数仓)
                                              ├─ 推 Spark(离线宽表)
                                              └─ pgvector 存 Embedding → RAG/向量检索

2 原理架构篇

核心概念

PostgreSQL 作为一款功能强大的开源对象-关系型数据库（ORDBMS），其核心概念可以从逻辑结构、存储机制、并发控制、扩展性几个层面来理解。下面按“由表及里”的方式梳理最关键的概念。

一、逻辑结构：从实例到行

1. 实例（Instance / Cluster）

一个 PostgreSQL 实例 = 一个数据目录（$PGDATA）
一个实例可以管理多个数据库
同一实例内，所有数据库共享：
- 后台进程（postmaster、checkpointer、walwriter 等）
- 内存结构（shared buffers、WAL buffers）
- 配置文件（postgresql.conf、pg_hba.conf）

注意：PostgreSQL 的 “cluster” ≠ 分布式集群，而是指一个数据库实例。

2. 数据库（Database）

一个实例下的逻辑隔离单元
不同数据库之间：
- 不能直接跨库查询（除非用 dblink / postgres_fdw）
- 各自拥有独立的系统表、对象命名空间
常见用途：按业务/租户建库

3. Schema（模式）

数据库内部的二级命名空间
一个数据库中可以有多个 schema
用于逻辑分组、权限隔离、避免命名冲突

database
 └── schema
      ├── table
      ├── view
      ├── function
      └── sequence

示例：

CREATE SCHEMA finance;
CREATE TABLE finance.orders (...);

4. 表（Table）、行、列、数据类型

PostgreSQL 是行存关系型数据库
表由行（tuple）和列组成
支持丰富的数据类型：
- 基础类型：int, text, boolean, timestamp
- 集合类型：ARRAY, JSONB, HSTORE
- 复合类型：
  - point (x, y)
  - line 直线
  - lseg 线段
  - box 矩形
  - path 闭合/开放路径
  - polygon 多边形
  - circle 圆
- 自定义类型：CREATE TYPE

二、物理存储：数据是如何落盘的

5. Relation & Page（堆表与页）

表、索引在内部统称为 relation
数据文件按 8KB page 组织
- PostgreSQL 把磁盘上的数据文件，切成固定 8KB 大小的“块”（Page），所有表、索引的读写，都以 Page 为单位进行。
  - Page 是“最小读写单元” | 即： 磁盘 I/O ←→ Page ←→ Buffer Pool
    - 不是按“行”读磁盘
    - 不是按“字节”读磁盘
    - 而是一次读 / 写一个完整的 8KB Page
  - 为什么是 8KB？
    - 接近操作系统页大小（通常 4KB）
    - 减少随机 I/O
    - 平衡 CPU Cache / 磁盘吞吐
    - 编译期可改（--with-blocksize），但极少人动
  - Page 和表的关系：一个表 = N 个 Page；一个 Page = 多个 Tuple（行）；行不能跨 Page 存储
    - 一行数据不能跨 Page，但1行数据超 8KB 时走 TOAST 或行外存储（Out-of-Line Storage）
      - TOAST = The Oversized-Attribute Storage Technique
  - 一个 8KB Page 内部包含：
    - Header（元数据）
    - Tuple（行数据）
    - Free Space（空闲空间）
    - Special（索引专用）

┌───────────────┐
│ Page Header   │
├───────────────┤
│ Tuple 1       │
│ Tuple 2       │
│ ...           │
├───────────────┤
│ Free Space    │
├───────────────┤
│ Special Space │
└───────────────┘

每个 page 中存放多个 tuple（行）

简化结构：

datafile
 └── page (8KB)
      ├── header
      ├── tuple1
      ├── tuple2
      └── free space

如果一行数据超过了8KB，会怎么存储呢？
- 一行数据不能跨 Page，超 8KB 时走 TOAST 或行外存储（Out-of-Line Storage）。
- 1️⃣ 普通列（短数据）
  - 整行必须塞进 一个 8KB Page
  - 行头 + 所有列 ≤ Page 可用空间（约 8KB - header - 对齐）
- 2️⃣ 大字段（text / bytea / jsonb / array 等） : 当某列“太大”时，触发 TOAST（The Oversized-Attribute Storage Technique）：
  - 主表行里：只存一个指针（十几字节）
  - 真实数据：
    - 压缩后放 TOAST 表（独立文件）
    - 或进一步切片存到多个 TOAST Page
    - 或直接使用“行外存储”（不压缩）

6. OID & Filenode

PostgreSQL 内部大量使用 OID（Object ID）
表、索引、函数等都有 OID
表对应的物理文件名通常是其 relfilenode

SELECT oid, relname, relfilenode
FROM pg_class
WHERE relname = 'my_table';

大表会被拆分成多个 1GB 的文件（如 12345, 12345.1, 12345.2）

7. TOAST（超长字段存储）

单行不能超过约 2KB（受 page 限制）
超过阈值的字段（如 text, bytea）会进入 TOAST 表
自动压缩 + 外存，对用户透明

三、事务与并发控制（非常核心）

8. 事务（Transaction）

遵循 ACID
使用 BEGIN / COMMIT / ROLLBACK
支持：
- 保存点：SAVEPOINT
- 子事务
- 两阶段提交（XA）

9. MVCC（多版本并发控制）

这是 PostgreSQL 最重要的特性之一。

写不阻塞读，读不阻塞写
每次 UPDATE / DELETE 实际是：
- 标记旧行为“已删除”
- 插入新版本行
通过 xmin / xmax 判断行的可见性

关键优势：

几乎不需要读锁
避免大量锁竞争

代价：

产生“死元组”（dead tuples）
需要 VACUUM 清理

10. VACUUM & Autovacuum

[英译]

vacuum n.真空、真空吸尘器、空间、空虚、空白

VACUUM：回收死元组、更新统计信息
- 死元组（Dead Tuple）= 被 MVCC 标记为“已删除 / 过期”，但还没被清理掉的旧版本行。
VACUUM ANALYZE：同时更新优化器统计信息
autovacuum：后台自动进程，生产环境必须开启

四、索引机制

11. 索引类型（PostgreSQL 一大亮点）

B-Tree：默认，适合等值、范围查询
Hash：等值查询（较局限）
GiST / SP-GiST：通用搜索树，地理、全文检索
GIN：倒排索引，适合 JSONB, ARRAY, 全文检索
BRIN：块范围索引，适合时序/日志数据

示例：

CREATE INDEX idx_tags ON articles USING GIN (tags);

五、SQL 与对象模型

12. SQL 标准 + 扩展

完整支持 SQL:2016 核心特性
支持：
- CTE（WITH）
- 窗口函数（OVER / PARTITION BY）
- 递归查询
- UPSERT（INSERT ... ON CONFLICT）

13. 对象-关系特性

支持继承

CREATE TABLE parent (id int);
CREATE TABLE child () INHERITS (parent);

支持 自定义类型、操作符、聚合函数
接近面向对象建模能力

六、可靠性与高可用

14. WAL（Write-Ahead Logging）

所有修改先写 WAL，再改内存
崩溃恢复依赖 WAL
支持：
- 时间点恢复（PITR）
- 物理复制（流复制）

PG数据库的真实存储模型：
- 堆表（Heap）：行存，8KB Page，无序插入，MVCC 产生死元组
- 索引：B-Tree / GIN / GiST 等，各自独立文件
- WAL：只是堆表和索引页修改的“旁路日志”，先写 WAL 再改内存页，后台 checkpointer 再把脏页刷回堆文件

即：PG 是 “Heap + B-Tree + WAL”，不是 “LSM + SSTable + WAL”。

LSM 树里通常也用 WAL 保护 Memtable，但 LSM 本身替代的是 PG 的堆+B树，不是 WAL。

特别注意
- PostgreSQL 的 WAL ≠ LSM 树，两者是不同层面的东西。
  - WAL（Write-Ahead Log）：是一种日志协议/恢复机制，核心是“改数据前先顺序追加写日志”，用于崩溃恢复、复制、PITR。它本身只是一个 append-only 的日志记录流（16MB 段文件，按 LSN 顺序），不是一种索引/存储数据结构。
  - LSM 树（Log-Structured Merge Tree）：是一种存储引擎/数据组织方式，用于把随机写转顺序写（RocksDB、Cassandra、TiDB 等）
    - 典型结构是 内存 Memtable → 刷盘 SSTable → 多层 Compaction 合并

15. 物理复制 & 逻辑复制

物理复制：基于 WAL，主备完全一致
逻辑复制：基于逻辑解码，可按表/行级同步
常见架构：
- 一主多备
- 级联复制
- 读写分离

七、权限与安全

16. 角色体系（Role）

PostgreSQL 没有用户/角色之分
LOGIN 权限的角色 ≈ 用户

CREATE ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE app TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

17. 认证与加密

认证方式：pg_hba.conf
- trust, password, md5, scram-sha-256
SSL 连接
行级安全（RLS）

八、扩展生态（PostgreSQL 的灵魂）

18. Extension 机制

插件式扩展，热加载
著名扩展：
- PostGIS：地理空间
- pg_stat_statements：SQL 统计
- pgcrypto：加密
- TimescaleDB：时序数据
- Citus：分布式

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

九、核心概念速查

层级	核心概念
实例	Cluster / Instance
物理	实例 --> TableSpace （均以文件目录做物理隔离）
逻辑	Database（逻辑隔离） → Schema（逻辑隔离） → Table
存储	Page / Tuple / TOAST
并发	MVCC / XID / VACUUM
索引	B-Tree / GIN / GiST / BRIN
事务	ACID / SAVEPOINT
高可用	WAL / 物理复制 / 逻辑复制
安全	Role / GRANT / RLS
扩展	Extension

graph TD A[ PostgreSQL 实例（Cluster） ] --> B[数据库（Database） ] A --> C[全局系统表（pg_global） ] B --> D[Schema] D --> E[Table] D --> F[Index] E --> G[TOAST 表] F --> H[索引文件] A --> I[表空间（Tablespace）] I --> J[pg_default] I --> K[pg_global] I --> L[自定义表空间] J --> B K --> C E --> M[8KB Page] M --> N[Tuple （行）] O[WAL 日志] --> A P[MVCC] --> A Q[VACUUM] --> A style A fill:#ffe4c4,stroke:#333 style B fill:#add8e6,stroke:#333 style D fill:#90ee90,stroke:#333 style E fill:#ffb6c1,stroke:#333 style F fill:#ffb6c1,stroke:#333 style I fill:#dda0dd,stroke:#333

架构设计与运行原理

Client-Server + 多进程：每连接一个 backend 进程，共享内存放 buffer/shared catalog
存储：表 → Heap 文件；行存；MVCC 靠行头 xmin/xmax 标记版本，旧版本由 autovacuum 回收
WAL（Write Ahead Log）：先写日志再改页，保证崩溃恢复；流复制读 WAL 同步备库
查询链路：SQL → 解析 → 重写 → 优化器（基于成本） → 执行器（支持并行 scan/join/agg）
扩展挂载点：自定义类型/函数/索引访问方法/FDW 外表/后台 worker，pgvector 就是挂 GIN/HNSW 索引实现的

3 使用指南

常用操作(PSQL模式下)

本节以基于PSQL客户端这种访问方式为例，这种方式不需要改任何配置，适合临时查询、维护。

ash-4.4# sudo su - postgres
postgres@Xxx:~$ psql

帮助手册

ash-4.4# psql --help
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "root")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "root")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

Report bugs to <pgsql-bugs@postgresql.org>.

以指定用户登录

//方式1
psql -U postgres -d notestation -h localhost -p 5432

查看数据库版本

synofoto=# select version();
                                              version                                              
---------------------------------------------------------------------------------------------------
 PostgreSQL 11.11 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 12.2.0, 64-bit
(1 row)

列出所有数据库

ash-4.4# sudo su - postgres
postgres@Xxx:~$ psql
postgres-# \l
                                           List of databases
    Name     |           Owner            | Encoding  |  Collate   |   Ctype    |   Access privileges   
-------------+----------------------------+-----------+------------+------------+-----------------------
 autoupdate  | postgres                   | SQL_ASCII | C          | C          | 
 download    | DownloadStation            | SQL_ASCII | C          | C          | 
 mediaserver | MediaIndex                 | UTF8      | en_US.utf8 | en_US.utf8 | 
 notestation | NoteStation                | SQL_ASCII | C          | C          | 
 ong         | SynologyApplicationService | SQL_ASCII | C          | C          | 
 postgres    | postgres                   | SQL_ASCII | C          | C          | 
 synodrive   | postgres                   | SQL_ASCII | C          | C          | =Tc/postgres         +
             |                            |           |            |            | postgres=CTc/postgres+
             |                            |           |            |            | office=CTc/postgres
 synoffice   | office                     | SQL_ASCII | C          | C          | 
 synofoto    | SynologyPhotos             | UTF8      | C          | C          | 
 synoindex   | MediaIndex                 | SQL_ASCII | C          | C          | 
 template0   | postgres                   | SQL_ASCII | C          | C          | =c/postgres          +
             |                            |           |            |            | postgres=CTc/postgres
 template1   | postgres                   | SQL_ASCII | C          | C          | =c/postgres          +
             |                            |           |            |            | postgres=CTc/postgres
(12 rows)

连接指定的库、列出库中的表

方式0 postgresql 的 psql 特有方式

//连接某个库（比如系统索引库 mediaserver）
postgres-# \c mediaserver
You are now connected to database "mediaserver" as user "postgres".

//查表
mediaserver-# \dt
                List of relations
 Schema |        Name        | Type  |   Owner    
--------+--------------------+-------+------------
 public | album_track        | table | MediaIndex
 public | artist_track       | table | MediaIndex
 public | composer_track     | table | MediaIndex
 public | config             | table | MediaIndex
 public | directory          | table | MediaIndex
 public | genre_track        | table | MediaIndex
 public | music              | table | MediaIndex
 public | personal_directory | table | MediaIndex
 public | personal_playlist  | table | MediaIndex
 public | photo              | table | MediaIndex
 public | pin                | table | MediaIndex
 public | playcount_track    | table | MediaIndex
 public | playlist           | table | MediaIndex
 public | playlist_sharing   | table | MediaIndex
 public | rating_track       | table | MediaIndex
 public | replaygain_track   | table | MediaIndex
 public | track              | table | MediaIndex
 public | video              | table | MediaIndex
 public | video_convert      | table | MediaIndex
 public | virtual_info_track | table | MediaIndex
 public | virtual_music      | table | MediaIndex
 public | voice_search       | table | MediaIndex
(22 rows)

方式1 SQL by pg_tables（PG 原生，最常用）

SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE 1=1
  -- pg_catalog 是 PostgreSQL 自带的核心系统模式（system catalog schema），可以理解成 PostgreSQL 的“元数据字典”仓库
  -- pg_catalog 里存的是 PostgreSQL 自己用的系统表和系统视图，用来描述整个数据库的结构（表、列、索引、函数、权限等）
  and schemaname NOT IN ('pg_catalog', 'information_schema')
  and schemaname not LIKE 'pg_%' 
  -- and schemaname = 'public'
ORDER BY schemaname, tablename;

方式2 SQL by information_schema.tables（SQL 标准，可移植性好）

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND table_type = 'BASE TABLE'   -- 只列普通表，排除视图
ORDER BY table_schema, table_name;

列出索引

方式1: 用 pg_indexes（最简单，带建索引的 SQL 定义）

-- 所有索引
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
ORDER BY schemaname, tablename, indexname;

-- 只看某张表（如表名区分大小写，用引号）
SELECT indexname, indexdef
FROM pg_indexes
WHERE 1=1
  and schemaname = 'public' 
  AND tablename = 'your_table';

方式2: 用 pg_class + pg_index（能看到是否主键/唯一）

SELECT
  n.nspname        AS schema_name,
  c.relname        AS table_name,
  i.relname        AS index_name,
  x.indisprimary   AS is_primary,
  x.indisunique    AS is_unique,
  pg_get_indexdef(x.indexrelid) AS index_def
FROM pg_class c
JOIN pg_index x  ON c.oid = x.indrelid
JOIN pg_class i  ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'   -- 普通表
ORDER BY n.nspname, c.relname, i.relname;

indisprimary = true 就是主键索引，indisunique = true 是唯一索引

小提醒

表名大小写：PG 默认把未加引号的标识符转小写。如果你的表是用 "UserInfo" 这种带引号建的，查询时也要写 tablename = 'UserInfo'，否则查不到。
当前数据库/模式：SELECT current_database(); 看当前库；SHOW search_path; 看默认模式。DBeaver 左侧导航树其实也能直接展开 schema → 表 → 索引看，但导出清单、做巡检还是 SQL 更方便。

查询指定表的数据

synofoto=# select * from synofoto.public.item limit 5;
 id | id_user | type 
----+---------+------
  1 |       1 |    0
  2 |       1 |    0
  3 |       1 |    0
  4 |       1 |    0
  5 |       1 |    0
(5 rows)

mediaserver=# select * from mediaserver.public.photo limit 5;
 id | path | title | filesize | album | resolutionx | resolutiony | camera_make | camera_model | exposure | aperture | iso | date | timetaken | mdate | fs_uuid | fs_online 
----+------+-------+----------+-------+-------------+-------------+-------------+--------------+----------+----------+-----+------+-----------+-------+---------+-----------
(0 rows)

mediaserver=# select * from photo;
 id | path | title | filesize | album | resolutionx | resolutiony | camera_make | camera_model | exposure | aperture | iso | date | timetaken | mdate | fs_uuid | fs_online 
----+------+-------+----------+-------+-------------+-------------+-------------+--------------+----------+----------+-----+------+-----------+-------+---------+-----------
(0 rows)

查看有哪些 schema

synofoto=# \dn
     List of schemas
  Name  |     Owner      
--------+----------------
 public | postgres
 test   | SynologyPhotos
(2 rows)

查看当前库下所有表的权限(ACL)

synoffice=# \dp
                                          Access privileges
 Schema |             Name              |   Type   | Access privileges | Column privileges | Policies 
--------+-------------------------------+----------+-------------------+-------------------+----------
 public | db_cleaner_version            | table    |                   |                   | 
 public | db_template_version           | table    |                   |                   | 
 public | db_user_statistic_version     | table    |                   |                   | 
 public | db_version                    | table    |                   |                   | 
 public | history_prune                 | table    |                   |                   | 
 public | link                          | table    |                   |                   | 
 public | mru_fc                        | table    |                   |                   | 
 public | mru_fc_id_seq                 | sequence |                   |                   | 
 public | node                          | table    |                   |                   | 
 public | node_delete                   | table    |                   |                   | 
 public | notification                  | table    |                   |                   | 
 public | notification_id_seq           | sequence |                   |                   | 
 public | template                      | table    |                   |                   | 
 public | template_link                 | table    |                   |                   | 
 public | template_perm_app             | table    |                   |                   | 
 public | template_perm_group           | table    |                   |                   | 
 public | template_perm_user            | table    |                   |                   | 
 public | template_recent               | table    |                   |                   | 
 public | udc_template_privileged_count | view     |                   |                   | 
 public | user_event_log                | table    |                   |                   | 
 public | user_event_log_id_seq         | sequence |                   |                   | 
(21 rows)

切换用户

//切换用户（推荐）
\c - 新用户名

//切换用户 + 数据库
\c <dbname> <username>

//完全重连
\q → psql -U user -d db

查看当前用户

synoffice=# SELECT current_user, session_user;
 current_user | session_user 
--------------+--------------
 postgres     | postgres
(1 row)

session_user：原始登录用户
current_user：当前生效角色（SET ROLE 后会变化）

查看所有用户/角色

postgres=# \du
                                            List of roles
         Role name          |                         Attributes                         | Member of 
----------------------------+------------------------------------------------------------+-----------
 DownloadStation            |                                                            | {}
 MediaIndex                 |                                                            | {}
 NoteStation                |                                                            | {}
 SynologyApplicationService | Create DB                                                  | {}
 SynologyPhotos             | Superuser, Create DB                                       | {}
 office                     |                                                            | {}
 postgres                   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}



//查看更详细的情况
postgres=# \du+
                                                   List of roles
         Role name          |                         Attributes                         | Member of | Description 
----------------------------+------------------------------------------------------------+-----------+-------------
 DownloadStation            |                                                            | {}        | 
 MediaIndex                 |                                                            | {}        | 
 NoteStation                |                                                            | {}        | 
 SynologyApplicationService | Create DB                                                  | {}        | 
 SynologyPhotos             | Superuser, Create DB                                       | {}        | 
 office                     |                                                            | {}        | 
 postgres                   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

PostgreSQL 中用户 = 可登录的角色，所以 \du 看到的就是“数据库用户”。

查验指定用户能连接哪些数据库 (connect 权限)

synoffice=# SELECT datname FROM pg_database WHERE has_database_privilege('xxx_user', datname, 'CONNECT');
   datname   
-------------
 postgres
 template1
 template0
 autoupdate
 synoindex
 mediaserver
 ong
 synofoto
 synoffice
 notestation
 download
 synodrive
(12 rows)

如果这里 目标数据库没列出来 → 说明 CONNECT 权限都没给到。

查验指定用户能访问的有哪些schema

synoffice=# SELECT nspname FROM pg_namespace WHERE has_schema_privilege('xxx_user', nspname, 'USAGE');
      nspname       
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 information_schema
 public
(6 rows)

查验指定用户是否设置了密码认证

以 postgres 用户为例

ash-4.4# sudo -u postgres psql -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname='postgres';"
 rolname  | rolpassword 
----------+-------------
 postgres | 
(1 row)

如果 rolpassword 是 NULL → 没设过密码

如果是 SCRAM-SHA-256$... 或 md5... → 设过，但无法反推明文（单向哈希）

新建用户

-- 创建用户（在 PostgreSQL 中，用户和角色是通用的，这里用 CREATE USER 默认隐含 LOGIN 权限）
CREATE USER xxx_user WITH PASSWORD '你的强密码';

-- 授予三大权限
ALTER USER xxx_user WITH SUPERUSER CREATEROLE CREATEDB;

或一句话创建: CREATE USER xxx_user WITH PASSWORD '你的强密码' SUPERUSER CREATEROLE CREATEDB;

退出(psql)

mediaserver-# \q
postgres@Xxx:~$

特别注意

DBeaver 连接 PostgreSQL

局域网下的 dbeaver 连接 postgresql 数据库时，必须确定明确要连接、使用的数据库名，否则连接后大概率目标库表的数据无法正常查询。

此时，即可在 DBeaver 中查询目标表的数据了

select * from synofoto.public.folder limit 5;
 id | id_user |         name          | parent | name_for_sort | permission | mtime | passphrase_share | shared | sort_by | sort_direction | permission_parent | name_for_sear
ch 
----+---------+-----------------------+--------+---------------+------------+-------+------------------+--------+---------+----------------+-------------------+--------------
---
  1 |       0 | /                     |      1 | /             |            |     0 |                  | f      |       0 |              0 |                 1 | 
  2 |       1 | /                     |      2 | /             |            |     0 |                  | f      |       0 |              0 |                 2 | 
  3 |       1 | /PhotoLibrary         |      2 | PHOTOLIBRARY  |            |     0 |                  | f      |       0 |              0 |                 3 | PHOTOLIBRARY
  4 |       1 | /PhotoLibrary/2024    |      3 | 0000002024    |            |     0 |                  | f      |       0 |              0 |                 4 | 2024
  5 |       1 | /PhotoLibrary/2024/06 |      4 | 0000000006    |            |     0 |                  | f      |       0 |              0 |                 4 | 06
(5 rows)



或： select * from folder limit 5;

关键使用方法

以上手最小集为出发点

-- 1) 建库表（JSONB + 数组）
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  payload JSONB,
  tags TEXT[],
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 2) 复杂查询：窗口函数算用户累计金额
SELECT user_id,
       sum(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS cum_amt
FROM orders;

-- 3) CTE + 递归（组织树/血缘）
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM dept WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.parent_id, d.name FROM dept d JOIN tree t ON d.parent_id=t.id
) SELECT * FROM tree;

-- 4) pgvector 向量检索（RAG 场景）
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (id SERIAL, emb vector(1536));
INSERT INTO docs (emb) VALUES ('[0.1,0.3,...]');
SELECT id FROM docs ORDER BY emb <-> '[0.2,0.1,...]' LIMIT 5;  -- 余弦/L2 距离

日常命令

进终端：psql -h localhost -U user -d dbname
看表：\dt；看结构：\d orders；导数据：COPY tbl TO '/tmp/a.csv' CSV HEADER;
连大数据生态：JDBC/psycopg2（Python）/Spark JDBC 源/Flinks CDC 目标

初级避坑

别用 SELECT * 跑大表，记得加条件 + 索引
JSONB 查询建 GIN 索引：CREATE INDEX ON t USING gin(payload jsonb_path_ops);
高并发配 PgBouncer，别让前端直连
定期观察 pg_stat_activity 长事务，防 vacuum 阻塞

Z FAQ for PostgreSQL

Q: 对象-关系型数据库/ORDBMS，与关系型数据库的异同？并以PostgreSQL、MYSQL类比举例说明?

从概念、核心差异、典型特性、以及 PostgreSQL / MySQL 的实际表现四个层面，系统讲清 ORDBMS（对象-关系型数据库） 与 RDBMS（关系型数据库） 的异同。

一、概念层面的异同

1. 共同点（为什么都叫“关系型”）

都以表（Table）、行（Row）、列（Column）为核心模型
都支持 SQL
都强调 ACID 事务
都依赖 关系代数（选择、投影、连接等）

👉 所以，ORDBMS 本质上仍然是关系型数据库，只是在其基础上扩展了面向对象能力。

2. 核心区别

RDBMS：一切都是二维表 + 标量值

ORDBMS：表 + 对象类型 + 继承 + 复杂结构

维度	RDBMS	ORDBMS
数据类型	标量（int、varchar…）	标量 + 复合类型 + 自定义类型
数据建模	扁平、范式化	支持嵌套、聚合、继承
行为封装	存储过程	方法绑定到类型
SQL 标准	SQL92 / SQL99	SQL:1999 对象扩展
典型代表	MySQL（传统）	PostgreSQL

二、ORDBMS 的核心特性（对比 RDBMS）

1. 自定义复合类型（Composite Types）

RDBMS（MYSQL）：

-- 地址拆成多个字段
CREATE TABLE users (
  id int,
  city varchar(50),
  street varchar(100)
);

ORDBMS（PostgreSQL）：

CREATE TYPE address AS (
  city varchar(50),
  street varchar(100)
);

CREATE TABLE users (
  id int,
  addr address
);

✅ 优势：

更符合现实世界建模
减少字段爆炸
语义更清晰

2. 表继承（Inheritance）

这是 ORDBMS 最具代表性的特征之一。

PostgreSQL 示例：

CREATE TABLE animals (
  id serial,
  name text
);

CREATE TABLE dogs (
  bark_volume int
) INHERITS (animals);

dogs 自动拥有 id、name
查询父表可看到所有子类数据

👉 MySQL 完全不支持表继承。

3. 数组与集合类型

RDBMS：

-- 标签通常拆表
tags: tag_id, user_id, tag_name

ORDBMS（PostgreSQL）：

CREATE TABLE users (
  id int,
  tags text[]
);

✅ 适合半结构化、弱关联数据

4. 方法与操作符重载 *

ORDBMS 允许将“行为”绑定到类型上。

-- 建表测试用（可选）
CREATE TABLE t_geo (
  -- type = circle（复合类型）,作为 PostgreSQL 内置几何类型之一; PG 原生支持这些几何类型： point, line, lseg, box, path, polygon, circle
  -- circle 的属性字段: center :: point (圆心) , radius :: float8 (半径)
  -- circle 的常用方法: 算面积 area(circle) :: float8 , 求直径 diameter(circle) ::float8 , 算半径 radius(circle) :: float8 , 求圆心 center(circle) :: point
  -- SELECT (c).center, (c).radius , center(c), area(c), diameter(c) FROM ( SELECT '((0,0),5)'::circle ) t(c);
  -- SELECT '(0,0)'::point , '((0,0),5)'::circle , radius(circle '((0,0),5)') , area(circle '((0,0),5)');   -- radius = PG 的内置函数; area = PG 其实也自带 area(circle)
  c circle
);

INSERT INTO t_geo(c) VALUES ( circle '((0,0),5)' );

-- 创建面积函数
CREATE OR REPLACE FUNCTION circle_area(circle)
RETURNS float8
LANGUAGE SQL
AS $$
    SELECT pi() * ($1).radius * ($1).radius; -- $1 是 SQL 函数的位置参数引用，表示函数的第一个输入参数 : 即 circle
$$;

-- 查询验证
SELECT circle_area(c) FROM t_geo;

-- 直接调用
SELECT circle_area(circle '((0,0),5)');

👉 更接近面向对象语言（Java / C++）的设计方式。

5. 面向对象的“多态”查询

结合继承 + 类型判断，可实现类似 OOP 的多态：

SELECT 
  *, tableoid::regclass
FROM animals;

三、PostgreSQL vs MySQL：经典对照

特性	PostgreSQL（典型 ORDBMS）	MySQL（典型 RDBMS）
自定义类型	✅ 支持	❌ 不支持
表继承	✅ 支持	❌ 不支持
数组类型	✅ 原生支持	❌ 不支持
JSON	✅ JSONB（索引、操作符）	✅ JSON（功能较弱）
多态查询	✅ 支持	❌ 不支持
面向对象建模	✅ 强	❌ 无
生态定位	OLTP + 分析 + 扩展	轻量 OLTP

👉 PostgreSQL = “最像 ORDBMS 的开源数据库”

👉 MySQL = “纯粹、简洁的关系型数据库”

四、什么时候该用 ORDBMS？

✅ 适合 ORDBMS（PostgreSQL）的场景

领域模型复杂（GIS、金融、医疗）
需要嵌套结构、数组、枚举
希望数据库层贴近业务对象
规则引擎、配置系统、元数据管理

✅ 适合传统 RDBMS（MySQL）的场景

CRUD 为主
简单表结构
高并发 Web 业务
团队熟悉度 & 运维成本优先

五、举例：用 Java / Python ORM（如 Hibernate / SQLAlchemy）对比 ORDBMS 建模

本案例旨在说明：

ORM 在“假装面向对象”，而 ORDBMS 在“真正面向对象”。

下面用 同一业务模型，分别用 Hibernate（Java） 和 SQLAlchemy（Python），对比它们在 传统 RDBMS（MySQL） 与 ORDBMS（PostgreSQL） 下的建模差异。

1、统一业务场景：员工–岗位模型

业务规则

员工分为：普通员工、经理
员工有地址（城市 + 街道）
员工有多个标签
经理有额外属性：bonus_rate（奖金比例）

2、在 RDBMS（MySQL）中的“妥协式”建模

1️⃣ Java + Hibernate（JPA）

实体类

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Employee {
    @Id
    private Long id;
    private String name;

    @Embedded
    private Address address;

    @ElementCollection
    private List<String> tags;
}

@Entity
public class Manager extends Employee {
    private Double bonusRate;
}

实际生成的表（MySQL）

employee
---------
id
name
address_city
address_street

manager
---------
id
bonus_rate

✅ ORM 帮你“拼回对象”

❌ 数据库里仍是扁平表 + 外键

2️⃣ Python + SQLAlchemy

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    type = Column(String)  # polymorphic_identity
    city = Column(String)
    street = Column(String)

    tags = relationship("Tag")

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    bonus_rate = Column(Float)

👉 本质仍是：

JOIN
映射表
应用层组装对象

🔑 RDBMS + ORM 的本质

数据库不懂“对象”，ORM 只是翻译官

3、在 ORDBMS（PostgreSQL）中的“原生对象建模”

1️⃣ PostgreSQL 原生对象定义

复合类型

CREATE TYPE address AS (
  city text,
  street text
);

表继承

CREATE TABLE employees (
  id serial PRIMARY KEY,
  name text,
  addr address,
  tags text[]
);

CREATE TABLE managers (
  bonus_rate numeric
) INHERITS (employees);

✅ 数据库本身就理解：

继承
复合结构
集合属性

2️⃣ Java + Hibernate（PostgreSQL）

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class Employee {
    @Id
    private Long id;
    private String name;

    @Type(type = "com.vladmihalcea.hibernate.type.array.StringArrayType")
    @Column(columnDefinition = "text[]")
    private String[] tags;

    @Type(type = "com.vladmihalcea.hibernate.type.basic.PostgreSQLHStoreType")
    private Address addr; // 映射为 PG 复合类型
}

👉 Hibernate 不再“模拟”对象，而是直接映射数据库原生能力

3️⃣ Python + SQLAlchemy（PostgreSQL）

from sqlalchemy.dialects.postgresql import ARRAY, CompositeType

Address = CompositeType(
    'address',
    [
        Column('city', String),
        Column('street', String)
    ]
)

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    addr = Column(Address)
    tags = Column(ARRAY(String))

class Manager(Employee):
    __tablename__ = 'managers'
    id = Column(Integer, ForeignKey('employees.id'), primary_key=True)
    bonus_rate = Column(Float)

✅ SQLAlchemy 对 PostgreSQL 的支持非常“对象友好”

4、关键差异对比（ORM 视角）

维度	MySQL + ORM	PostgreSQL + ORM
继承实现	JOIN / SINGLE_TABLE	表继承（DB 原生）
复杂结构	拆表 / Embeddable	复合类型
集合属性	关联表	数组 / 多值列
ORM 复杂度	高（大量映射逻辑）	低（接近领域模型）
查询语义	多表 JOIN	单表 + 多态扫描
性能	JOIN 成本高	更紧凑、更少 JOIN

5、一个非常有代表性的查询对比

需求：查询所有员工（含经理）

MySQL + ORM（隐式）

SELECT *
FROM employee e
LEFT JOIN manager m ON e.id = m.id;

PostgreSQL（原生）

SELECT * FROM employees;

✅ 自动包含 managers 的数据

✅ 数据库理解“is-a”关系

6、ORM 在两种数据库中的角色变化

在 MySQL 中

ORM = 对象模拟器

负责继承
负责组合
负责集合
负责多态

在 PostgreSQL 中

ORM = 对象映射器

数据库已经懂对象
ORM 只做“桥接”
更接近 领域驱动设计（DDD）

7、总结

MySQL + ORM：把对象“压扁”进表

PostgreSQL + ORM：让数据库“长成”对象

8、延伸思考（很重要）

问题	结论
ORM 能替代 ORDBMS 吗？	❌ 不能，只是掩盖差异
为什么很多项目不用 PG？	运维成本 + 团队认知
微服务时代还重要吗？	✅ 领域模型越复杂，价值越大
适合 DDD 吗？	✅ PostgreSQL 是天然土壤

六、举例：用真实业务案例（如电商商品模型）对比 PG vs MySQL

业务背景

商品有多种类型（普通商品、图书、数码），且属性差异巨大。

MySQL：典型的“妥协式”设计

┌──────────────┐
│   products   │  ← 宽表 / EAV
├──────────────┤
│ id           │
│ title        │
│ price        │
│ author       │  ← NULL（如果不是书）
│ isbn         │
│ brand        │  ← NULL（如果不是数码）
│ warranty     │
│ attr_key     │  ← EAV 模式才有
│ attr_value   │
└──────────────┘
        ▲
        │ 1:N
┌──────────────┐
│ product_attrs│  ← 可选（EAV）
└──────────────┘

特点——MySQL：典型的“泛化妥协”模型
- 只有一张（或两张）物理表
- 靠 NULL 或关联表表达差异
- DB 不理解“什么是图书”

方案 1：宽表（冗余严重）

CREATE TABLE products (
  id BIGINT,
  title VARCHAR(255),
  price DECIMAL(10,2),
  -- 图书专用
  author VARCHAR(100),
  isbn VARCHAR(20),
  -- 数码专用
  brand VARCHAR(50),
  warranty_months INT
);

❌ 大量 NULL 字段，无法约束“图书必须有 ISBN”。

方案 2：EAV 模型（性能灾难）

CREATE TABLE product_attrs (
  product_id BIGINT,
  attr_key VARCHAR(50),
  attr_value TEXT
);

❌ 无法做类型约束，查询必 JOIN，索引失效。

ORM 层（Java / Python）

必须用 Single Table / Joined 继承策略
复杂查询需手写 SQL
业务规则被迫写在应用层

PostgreSQL：原生“对象化”设计

┌──────────────┐
│  products    │  ← 抽象父类
├──────────────┤
│ id           │
│ title        │
│ price        │
│ specs(JSONB) │
└──────┬───────┘
       │ INHERITS
┌──────┴────────────────┐
▼                       ▼
┌───────────┐         ┌──────────────┐
│  books    │         │ electronics  │
├───────────┤         ├──────────────┤
│ isbn      │         │ brand        │
│ author    │         │ warranty     │
└───────────┘         └──────────────┘

特点——PostgreSQL：真正的“泛化–特化”模型
- 表之间有 IS-A 关系
- 子类字段强约束
- DB 原生理解“图书是一种商品”

1. 基础类型 + 继承

-- 公共属性
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  price NUMERIC(10,2)
);

-- 图书（继承商品）
CREATE TABLE books (
  isbn CHAR(13) NOT NULL,
  author VARCHAR(100)
) INHERITS (products);

-- 数码（继承商品）
CREATE TABLE electronics (
  brand VARCHAR(50),
  warranty_months INT
) INHERITS (products);

2. 复杂属性用 JSONB

ALTER TABLE products ADD COLUMN specs JSONB;

-- 支持索引
CREATE INDEX idx_specs ON products USING GIN (specs);

3. 查询示例

-- 查所有商品（自动包含子类）
SELECT * FROM products;

-- 查图书特有的字段
SELECT title, author FROM books;

核心差异对照表

维度	MySQL	PostgreSQL
建模范式	表驱动（扁平化）	对象驱动（层次化）
扩展性	改表结构 / EAV	新增子表即可
数据约束	弱（NULL 泛滥）	强（NOT NULL 作用于子类）
复杂查询	多表 JOIN	单表扫描 + 多态
JSON 能力	仅存储 / 简单提取	索引 + 路径查询 + 函数
ORM 负担	重（大量映射配置）	轻（接近领域模型）

总结

MySQL：为了适应表结构，牺牲了业务的“对象感”

PostgreSQL：为了适应业务，强化了数据库的“对象感”

实战建议

SKU 结构简单、迭代快：选 MySQL（省心）
商品类目多、属性差异大、搜索复杂：选 PostgreSQL（省钱，省代码）

七、举例： “RDBMS → ORDBMS → NoSQL”演进关系图 *

RDBMS → ORDBMS → NoSQL 的演进关系与分化逻辑（不是单纯时间线，而是「能力扩展」与「取舍」）。

graph LR R[RDBMS 关系型数据库 表/行/列/SQL/ACID] O[ORDBMS 对象-关系型数据库 +复合类型/继承/数组/方法] N[NoSQL 非关系型数据库 文档/KV/列族/图] R -->|扩展建模能力| O R -->|放弃强schema/SQL 追求扩展性与灵活模型| N O -->|仍保留SQL与事务 增强对象语义| PG[(PostgreSQL)] R -->|纯关系模型 简单高效| MY[(MySQL)] N --> DOC[(MongoDB)] N --> KV[(Redis)] N --> COL[(Cassandra)] N --> G[(Neo4j)] style R fill:#e1f5fe,stroke:#0288d1 style O fill:#f3e5f5,stroke:#8e24aa style N fill:#fff3e0,stroke:#f57c00

RDBMS → ORDBMS
- 不改关系本质，向内增强建模能力（面向对象）
RDBMS → NoSQL
- *向外放弃部分关系约束**，换扩展性 / 灵活数据模型
ORDBMS ≠ 中间态 （它和 NoSQL 是两条不同进化树：）
- ORDBMS：关系 + 对象
- NoSQL：反关系 / 弱关系

八、总结

ORDBMS = RDBMS + 面向对象建模能力

PostgreSQL 把“对象”放进数据库，MySQL 把“对象”留在应用层。

Q: pg数据库中，表、索引的存储实现？是以独立的文件存放吗？

是的，表和索引在 PostgreSQL 中本质上就是操作系统文件，但“一个表 ≠ 一个文件”这么简单。

表、索引在物理上以文件形式存储在表空间目录中，但会根据大小拆分为多个【文件】，TOAST 数据另有独立文件。

存储位置在哪？

路径规则：

$PGDATA/
 └── base/           # 默认表空间 pg_default
      └── <db_oid>/
           ├── <relfilenode>
           ├── <relfilenode>.1
           ├── <relfilenode>.2
           └── <relfilenode>_fsm

<db_oid>：数据库的 OID（pg_database.oid）
<relfilenode>：表/索引的文件名（来自 pg_class.relfilenode）

表和索引是不是独立文件？

是的，每个表、每个索引都有自己的文件集合

SELECT 
    relname, relfilenode
FROM pg_class
WHERE relname IN ('orders', 'orders_pkey');

结果类似：

orders        | 16384
orders_pkey   | 16385

👉 表 orders 和索引 orders_pkey 是完全不同的文件。

示例

synofoto=# SELECT relname, relfilenode FROM pg_class WHERE relname IN ('address', 'activity');
 relname  | relfilenode 
----------+-------------
 activity |       18361
 address  |       17396
(2 rows)

文件拆分规则（非常重要）

1️⃣ 单文件最大 1GB

超过 1GB，自动拆分：

16384
16384.1
16384.2

防止文件系统对大文件的限制问题

2️⃣ 辅助文件（自动维护）

后缀	作用
`_fsm`	Free Space Map（空闲空间映射）
`_vm`	Visibility Map（可见性映射，MVCC 优化）
`_init`	未日志表的初始化文件

通常不需要手动管它们。

TOAST：大字段的独立文件

[英译] toast : n.烤面包、土司、干杯

当某列太大（如 text, bytea）：

主表文件中只存一个 TOAST pointer
真实数据存在 TOAST 表中（独立文件）

16384          -- 主表
16385          -- toast table
16386          -- toast index

查询：

SELECT 
  reltoastrelid::regclass
FROM pg_class
WHERE relname = 'orders';

示例

synofoto=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'address';
      reltoastrelid      
-------------------------
 pg_toast.pg_toast_17396
(1 row)

索引的存储

索引 = 独立文件
不同索引类型（B-Tree / GIN / BRIN）内部结构不同
索引文件同样遵循 1GB 拆分规则

逻辑 vs 物理对照表

逻辑对象	物理表现
Database	目录（db_oid）
Table	文件集合（relfilenode）
Index	独立文件集合
TOAST	独立表 + 独立索引
Schema	❌ 无物理文件（仅逻辑命名空间）

总结

PostgreSQL 中，表和索引以文件形式存储，每个对象有独立的 relfilenode 文件，超过 1GB 自动拆分，大字段通过 TOAST 表独立存储，Schema 不参与物理存储。

Q: 创建数据库时指定的【模板数据库】，有什么作用？

在 PostgreSQL 中，模板数据库（Template Database）的本质作用是：

作为“克隆源”，用来快速创建新数据库。

当你执行 CREATE DATABASE xxx; 时，PostgreSQL 并不是从零建库，而是复制一个已有数据库的结构和内容，这个被复制的库，就是模板数据库。

最核心的SQL语句

CREATE DATABASE new_db;

等价于（默认情况下）：

CREATE DATABASE new_db TEMPLATE template1;

👉 template1 是默认模板

PostgreSQL 自带哪几个模板库？

初始化实例后，通常会有两个“特殊”数据库：

1️⃣ `template1`（最重要）

默认模板
所有 CREATE DATABASE 不带 TEMPLATE 时都基于它
你可以改它（加表、加扩展、改参数）

2️⃣ `template0`（系统保留）

最干净的空库
字符集/排序规则固定
不允许连接，也不建议改
用途：
- 创建不同编码的数据库
- 从“完全干净”的状态建库

查看：

synofoto=# SELECT datname, datistemplate, datallowconn FROM pg_database;
   datname   | datistemplate | datallowconn 
-------------+---------------+--------------
 postgres    | f             | t
 template1   | t             | t
 template0   | t             | f
 autoupdate  | f             | t
 synoindex   | f             | t
 mediaserver | f             | t
 ong         | f             | t
 synoffice   | f             | t
 notestation | f             | t
 download    | f             | t
 synofoto    | f             | t
 synodrive   | f             | t
(12 rows)

模板数据库是怎么工作的？

创建新库的真实过程

指定一个模板库（默认 template1）
PostgreSQL 在文件系统层复制模板库的目录
复制系统表、对象、扩展、配置
对新库做少量初始化（如设置 owner）

⚠️ 注意：

不是逻辑导出/导入
是“物理级拷贝”（效率高）
新库和模板库在创建那一刻完全一致

模板数据库能干什么？（实战价值）

✅ 场景 1：统一新建库的基线

你可以在 template1 里提前放好：

常用 schema（public, audit, logs）
基础表（migrations, dict_*）
扩展（pgcrypto, uuid-ossp）
默认权限
搜索路径（search_path）

之后：

CREATE DATABASE order_service;

新库自动带这些东西。

✅ 场景 2：多租户 SaaS 建库

-- 先做好 tenant_template
UPDATE pg_database
SET datistemplate = true
WHERE datname = 'tenant_template';

CREATE DATABASE tenant_a TEMPLATE tenant_template;
CREATE DATABASE tenant_b TEMPLATE tenant_template;

每个租户一个库，结构完全一致。

✅ 场景 3：避免编码问题（用 template0）

CREATE DATABASE mydb
  TEMPLATE template0
  ENCODING 'UTF8'
  LC_COLLATE 'C'
  LC_CTYPE 'C';

template1 如果已经被改成某种编码，可能无法创建另一种编码的库。

模板库的特殊属性

一个数据库是不是模板库，由这两个字段决定：

字段	含义
`datistemplate`	是否可作为模板
`datallowconn`	是否允许普通连接

系统判断逻辑：

datistemplate = true 才能被 TEMPLATE= 使用
多数模板库会设为 datallowconn = false（防误连）
把普通库变成模板：

UPDATE pg_database
SET datistemplate = true
WHERE datname = 'my_template';

重要限制（容易踩坑）

❌ 有活跃连接时不能当模板

ERROR: source database "template1" is being accessed by other users

解决：

断开连接
或改用 template0

❌ 不能基于自己克隆自己

❌ 模板库不是“继承关系”

改了 template1，已有库不会变
只影响“以后创建的库”

和“系统表 / 初始库”的区别

概念	作用
`postgres`	默认管理员连接库，不是模板
`template1`	默认建库模板
`template0`	干净模板（编码兼容用）
`pg_catalog`	系统表 schema，不是数据库

总结

模板数据库 = PostgreSQL 创建新库时的“快照源”，默认是 template1，用来统一结构、扩展和基线配置。

Q: 创建数据库时指定的【表空间】起什么作用？内置的 pg_default / pg_global 表空间的区别？

表空间作用

表空间 = 数据文件在操作系统里的物理存储位置。

用来把数据库对象分散到不同磁盘，做 IO 隔离、扩容、性能优化。

CREATE TABLESPACE fast_ssd LOCATION '/ssd/pgdata';
CREATE TABLE t1 TABLESPACE fast_ssd;

层级关系：表空间 --> 数据库 --> Schema --> 表

graph TD TS[(表空间 Tablespace 物理目录: /ssd/pgdata)] TS --> DB1[(数据库 database_a)] TS --> DB2[(数据库 database_b)] DB1 --> SCH1[Schema public] DB1 --> SCH2[Schema finance] SCH1 --> TBL1[(表 table_x INDEX idx_x)] SCH2 --> TBL2[(表 table_y)] DB2 --> SCH3[Schema public] SCH3 --> TBL3[(表 table_z)] PGDEF[(pg_default 表空间)] -.-> DB1 PGDEF -.-> DB2 PGGLOB[(pg_global 表空间)] -.-> GLOB[(全局系统表 pg_database / pg_authid)]

表空间/TableSpace：操作系统目录，可挂多个数据库

数据库/Database：数据库，属于某个实例，逻辑隔离

模式/Schema：库内命名空间，逻辑隔离

表/Table、索引/Index：最终对象，落在某个表空间的某个文件里

物理隔离的最小单位是：表空间（Tablespace）
逻辑隔离的最小单位是：Schema
- 同库不同 Schema 的表，默认都在同一个表空间里
  - 如： CREATE SCHEMA finance; CREATE TABLE finance.orders (...); -- 默认落在 pg_default
- 可以跨 Schema 连表查询；但在同一会话（Connection / Session）中，原生PG数据库下，无法跨 Database 连表查询。
  - 如： SELECT * FROM public.users u JOIN finance.orders o ON u.id = o.user_id;
  - 不能跨库连表查询的原因： Database 是 PostgreSQL 的最高逻辑边界，一个连接只能 attach 到一个 Database。
    - 一个 Connection = 一个 Database
- 采取逻辑隔离的： Database / Schema

层级	隔离类型	说明
Tablespace	物理隔离	对应操作系统目录，可以放在不同磁盘
Database	强逻辑隔离	不同库之间无法直接访问（除非 FDW）
Schema	弱逻辑隔离	只是命名空间前缀（`schema.table`），共用同一个库的资源
Table	无隔离	只是 Schema 下的一个对象

pg_default vs pg_global

表空间	作用	特点
pg_default	普通对象的默认存储	用户表、索引、自己建的库都在这里
pg_global	集群级系统对象存储	存 `pg_database`、`pg_authid` 等跨库共享的系统表

关键区别
- pg_default：每个数据库私有
- pg_global：整个 PostgreSQL 实例（cluster）唯一，所有库共享
- 两者都不能删除
- 只有 pg_global 存的是“全局系统表”

建库时，建议使用 pg_defualt 还是 pg_global 表空间？

结论：永远不要用 pg_global，99% 情况用 pg_default。

对比

表空间	能否在建库时指定	建议	原因
pg_default	✅ 可以（默认）	✅ 强烈推荐	专门存放用户数据库
pg_global	✅ 技术上可行	❌ 严禁使用	只存集群级系统表，污染会导致实例异常

原因

pg_global 是给 PostgreSQL 内核用的，不是给用户库用的。

pg_global 只存：
- pg_database
- pg_authid
- 其他跨库系统表
若把业务库建进去：
- 破坏系统结构
- 备份/恢复风险
- 官方文档明确不推荐

正确姿势

-- 什么都不写，默认就是 pg_default ✅
CREATE DATABASE app_db;

-- 显式写，也推荐 ✅
CREATE DATABASE app_db TABLESPACE pg_default;

只有这两种场景才动表空间：

性能/磁盘规划：新建表空间放到 SSD
冷热分离：历史数据放 HDD

小结

pg_default 管“业务数据”，pg_global 管“集群元数据”。
建库默认用 pg_default，pg_global 碰都别碰。

Q: PG数据库的「生产环境建库最佳实践」?

权限最小化：建库用专用运维账号，业务账号仅授权CONNECT+对应schema权限，禁用superuser跑业务。
参数模板化：预置shared_buffers、work_mem等核心参数模板，按实例规格固化，避免现场随意改。
建库规范：

CREATE DATABASE显式指定OWNER，ENCODING='UTF8'，LC_COLLATE/LC_CTYPE='en_US.UTF-8'（避免中文排序坑）。

业务schema单独创建，禁止业务对象放public。

表空间分离：索引、大表用单独的表空间，数据/日志/WAL分盘挂载，避免IO争抢。
扩展白名单：仅安装必要extension（如pg_stat_statements），禁止随意CREATE EXTENSION。
连接限制：ALTER ROLE xxx CONNECTION LIMIT N，防连接风暴；配合pgbouncer做连接池。
基线配置：开启log_checkpoints/log_connections等审计日志，部署自动 vacuum/analyze，预设WAL归档。
建完校验：\l+、\dt+、pg_tablespace检查，跑一轮基础监控采集验证。

Y 推荐文献

PostgreSQL 官方 18 文档 Tutorial（最权威入门，零基础）

https://www.postgresql.org/docs/current/tutorial.html

Timescale《Understanding PostgreSQL》（架构/优劣/扩展一目了然，英文）

https://www.timescale.com/learn/understanding-postgresql

CSDN《PostgreSQL(PG)全面解析：从核心特性到实操落地，兼与MySQL深度对比》（中文，选型+实操友好）

https://blog.csdn.net/hjj1997/article/details/160914673

X 参考文献

posted @ 2026-07-26 18:06 千千寰宇阅读(10) 评论(0) 收藏举报

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。【好物推荐】