PostgreSQL 的 MVCC（多版本并发控制）是实现高并发的核心，无锁读写是最大特点。它不会修改 / 删除原始数据，而是为每行数据生成新版本：插入时标记创建版本，更新时生成新行、旧行标记删除，删除时仅标记删除。每行隐藏xmin（创建事务 ID）、xmax（删除事务 ID）字段，事务根据自身快照判断可见性。读操作取历史版本，写操作生成新版本，读写互不阻塞，仅写写互斥。旧版本数据由 autovacuum 自动清理，既保证了事务隔离，又大幅提升了并发性能，是 PG 区别于其他数据库的关键特性。
PG 支持 4 种事务隔离级别，基于 MVCC 实现，逐级提升安全性、降低并发。读未提交：几乎不用，可读取未提交数据，存在脏读。读已提交（默认）：只能读已提交数据，解决脏读，语句级快照，同一事务多次查询结果可能不同。可重复读：事务级快照，全程数据一致，解决不可重复读。可串行化：最高级别，模拟串行执行，完全避免幻读，并发性能极低。级别越高，锁竞争 / 快照开销越大，生产环境默认读已提交即可满足需求，强一致性场景用可串行化。
PG 索引底层基于有序树结构，通过索引快速定位数据，避免全表扫描。核心类型：B 树索引（默认），支持等值 / 范围 / 排序，适合绝大多数场景，优化时需避免冗余索引、定期重建碎片；GIN 索引，适合多值类型（数组、JSONB）；GiST 索引，适合地理、范围、全文搜索；哈希索引，仅支持等值查询。还有 BRIN 索引（超大表有序数据）、SP-GiST 索引（非平衡数据）。索引会加速查询，但降低增删改效率，需按查询场景创建，高频查询用覆盖索引，避免回表。
PG 支持声明式分区（主流），分为范围、列表、哈希分区，将大表拆分为多个小的分区表，查询仅扫描对应分区。创建父表指定分区规则，子表自动继承结构。动态分区可通过函数 / 触发器自动创建新分区（如按天分区），无需手动维护。分区优势：大幅提升大表查询、删除、 vacuum 效率，降低 IO 开销。使用场景：日志、订单、时序等超大表。优化要点：分区键选查询高频字段，避免跨分区查询，配合索引使用，性能可提升 10 倍以上。
PG 查询优化器是基于成本的优化器（CBO），流程分 4 步：解析 SQL 语法→生成逻辑执行计划→评估所有物理计划成本→选择最优计划执行。优化器会统计表数据分布、索引、行数，选择最佳索引、JOIN 顺序、连接方式（嵌套循环 / 哈希 / 归并）。自动优化子查询、合并过滤条件、消除无用计算。开发者可通过ANALYZE更新统计信息，帮助优化器精准判断。优化器还支持并行查询、索引推荐，配合分区、索引设计，能自动适配不同数据量的查询场景。
PG 触发器是表事件驱动的自动执行程序，按时机分BEFORE/AFTER/INSTEAD OF，按级别分行级、语句级，事件包括 INSERT/UPDATE/DELETE。用途：数据校验、审计日志、自动更新关联表。触发器依赖表，自动触发；存储过程是独立的 SQL 集合，需手动CALL调用，支持事务、复杂逻辑，可被触发器调用。PG 支持 PL/pgSQL 等语言编写触发器函数。注意：过量触发器会降低写入性能，仅用于核心数据约束和日志场景。
WAL（预写日志）是 PG 的数据安全基石，核心原则：先写日志，再写数据。所有修改先写入 WAL 日志文件，再异步刷新到数据文件。若数据库崩溃，重启后通过 WAL 重放未完成的事务，保证数据不丢失、事务原子性。WAL 还支持主从复制、时间点恢复（PITR），是高可用架构的基础。WAL 分为多个段文件，可归档备份，配合复制槽实现数据同步。它大幅降低了磁盘随机 IO，提升了写入性能，同时保障了数据可靠性。
PG 复制分物理复制和逻辑复制。物理复制是块级复制，完整拷贝数据文件，适合同版本 PG，只能整库复制，延迟低、性能高，用于主从备份、高可用。逻辑复制是行级复制，基于 WAL 解析逻辑变化，复制表 / 行级别数据，支持跨版本、跨架构，可筛选表、过滤数据，用于数据同步、分库分表、多活架构。物理复制是整机备份，逻辑复制是灵活的数据同步，两者可结合使用，满足不同高可用、数据分发需求。
GiST 和 GIN 是 PG高级索引，核心区别：GIN 是倒排索引，查询速度极快，适合静态多值数据（数组、JSONB、全文搜索关键词），但写入速度慢、占用空间大。GiST 是通用搜索树，平衡读写性能，支持复杂数据类型（地理坐标、范围类型、几何图形、全文搜索），空间占用小、写入快，适合频繁更新的数据。简单说：查询多、写入少用 GIN（如日志标签）；读写均衡、复杂类型用 GiST（如地理信息、活动时间范围）。
TOAST 是 PG超大字段存储优化技术，自动处理超长文本、二进制数据。PG 默认数据页 8KB，超过阈值的字段会被压缩 + 切片，存储到独立的 TOAST 系统表，原字段仅存指针。查询时自动解压、合并数据，对用户完全透明。支持多种压缩算法，平衡压缩比和性能。TOAST 解决了大字段无法存储的问题，减少了主表数据量，提升了查询速度，所有变长类型（text、bytea、jsonb）自动启用，无需手动配置，是 PG 处理大对象的核心机制。
PG 并行查询利用多核 CPU拆分查询任务，多线程并行执行，大幅提升大数据量查询速度。优化器自动判断查询成本，满足阈值时启用并行：将扫描、聚合、JOIN 任务分给多个工作进程，最后合并结果。支持并行全表扫描、并行索引扫描、并行聚合。可配置并行工作进程数、最小成本阈值。适用场景：大表统计、报表查询、全表扫描。注意：小查询、索引高效查询无需并行，过量并行会增加线程开销，适合 CPU 密集型查询。
表空间是 PG数据文件的物理存储位置，将表 / 索引映射到磁盘目录，实现存储隔离。默认有 pg_default、pg_global 两个系统表空间。可创建自定义表空间，将热数据放在 SSD、冷数据放在机械盘，隔离系统表和业务表。管理：创建表空间指定路径，建表时指定表空间；可移动表 / 索引到其他表空间。性能优化：高频访问的表、索引放高速磁盘，大表、日志放低速磁盘；多表空间分散 IO，避免磁盘瓶颈，适合大容量、高性能需求的业务。
PG 批量插入优化核心：减少事务、日志、索引开销。首选COPY命令，比 INSERT 批量快 10 倍以上，直接加载数据，日志开销最小。关闭自动提交，使用单事务插入所有数据；临时关闭索引、外键、触发器，插入完成后重建。调整 WAL 参数，增大 wal_buffers，禁用归档日志（临时）。使用INSERT ... SELECT替代多行 VALUES，减少解析开销。分区表分批插入，避免单事务过大。还可使用 pg_bulkload 工具，绕过缓冲池直接写入，适合百万、千万级数据批量导入。
保存点是事务内的中间标记点，允许事务部分回滚，不影响整个事务。语法：SAVEPOINT 名称; 标记，ROLLBACK TO 保存点; 回滚到标记处，RELEASE 保存点; 释放。使用场景：复杂事务中，部分步骤执行失败，仅回滚错误步骤，保留前面的成功操作；批量处理数据时，分段提交，避免全部回滚；存储过程、函数内的异常处理。保存点基于 MVCC 实现，不额外占用资源，灵活控制事务粒度，提升复杂业务的容错性。
死锁是多个事务互相持有对方需要的锁，循环等待。PG内置死锁检测机制，后台线程实时扫描锁等待关系，发现死锁后，立即回滚代价最小的事务，释放锁让其他事务继续。预防死锁：统一按固定顺序访问表 / 行；事务保持短小，尽快提交；使用低隔离级别；合理设计索引，避免行锁升级为表锁。可配置死锁超时时间，超时自动释放锁。通过pg_locks视图查看锁状态，分析死锁日志，优化业务逻辑和索引，从根源减少死锁。
序列是 PG自增数字生成器，用于生成唯一主键 ID，独立于表，支持共享使用。创建表时用SERIAL/BIGSERIAL自动生成序列，或手动CREATE SEQUENCE。序列基于事务安全，每次调用nextval生成唯一值，即使事务回滚，序列也不会后退，避免 ID 重复。支持设置起始值、步长、最大值、缓存数，缓存可提升性能。序列是全局唯一的，适合做主键、业务编号，比应用层生成 ID 更高效、安全，是 PG 唯一标识的核心工具。
PG 表继承允许子表继承父表的结构和约束，子表可新增字段，父表查询会自动包含所有子表数据。实现：CREATE TABLE 子表 () INHERITS (父表);。场景：多类型业务表（如商品分普通 / 虚拟商品）、日志分表、多租户数据隔离。优点：结构复用、查询统一；缺点：无强制约束，子表可重复数据，索引、 vacuum 效率低，优化器支持有限。现在声明式分区已完全替代继承，继承仅用于老旧项目，新项目优先用分区表。
CTE 是WITH子句定义的临时结果集，可在查询中重复使用，分为普通 CTE 和递归 CTE。语法：WITH 名称 AS (子查询) SELECT * FROM 名称;。优势：简化复杂嵌套查询，代码可读性高；递归 CTE 实现树形、层级数据查询（如部门、菜单）；PG12+ 支持 CTE 内联，性能与子查询一致。CTE 是临时结果，不存储数据，支持增删改查语句，适合复杂报表、层级查询、分步查询，让 SQL 逻辑更清晰、易维护。
PG 内置原生全文搜索，无需依赖第三方工具。核心流程：将文本转为词素（分词、去停用词），存储为tsvector类型；查询条件转为tsquery，匹配词素。配合 GIN/GiST 索引加速查询，支持多语言、权重排序、模糊匹配。实现：to_tsvector处理文本，to_tsquery处理查询，@@匹配。优势：与数据库无缝集成，无需同步数据，支持复杂逻辑查询，性能优于模糊查询。适合文章、商品标题、日志等文本检索场景，轻量化、易维护。
Listen/Notify 是 PG轻量级消息通知机制，实现数据库与应用、应用之间的实时通信。应用LISTEN 通道名订阅消息，数据库NOTIFY 通道名, 内容发布消息，消息实时推送，无轮询。用途：数据变更通知（如表更新后推送应用）、任务调度、跨服务通信、缓存刷新。消息存储在内存，不持久化，延迟毫秒级，支持多客户端订阅。相比消息队列，轻量无依赖，适合实时性要求高、简单的消息通知场景。
窗口函数是 PG高级分析函数，对结果集分区计算，不合并行数，保留每行数据。语法：函数() OVER (PARTITION BY 分区字段 ORDER BY 排序字段)。常用函数：ROW_NUMBER（行号）、RANK（排名）、SUM/AVG（分组聚合）、LAG/LEAD（上下行数据）。场景：分组排名、连续数据统计、同比环比、分页查询。窗口函数比 GROUP BY 更灵活，无需子查询即可实现复杂分析，是报表、数据分析的核心工具，大幅简化统计 SQL。
PG 的 JSON 和 JSONB 都存储 JSON 数据，核心区别：JSON 是文本存储，原样保存，每次查询解析，写入快、查询慢；JSONB 是二进制存储，预解析、去重、索引优化，写入稍慢、查询极快，支持 GIN 索引。JSONB 支持修改、删除字段，JSON 不支持；JSONB 会调整键顺序，JSON 保留原始顺序。生产环境优先用 JSONB，适合频繁查询、索引、修改的 JSON 数据；JSON 仅用于只写入、不查询的日志场景。JSONB 是 PG 处理半结构化数据的核心类型。
表抽样是 PG快速获取表样本数据的功能，无需全表扫描，适合大数据量的统计、测试、分析。支持两种抽样方式：SYSTEM（块级抽样，速度最快）、BERNOULLI（行级抽样，数据更均匀）。语法：SELECT * FROM 表 TABLESAMPLE 方式(百分比)。用途：大数据报表预估、测试数据生成、数据抽样分析、性能测试。抽样速度远快于全表查询，结果近似真实数据，适合无法全表扫描的超大表，平衡准确性和性能。
PG 提供多层数据加密，保障数据安全。文件级：使用透明数据加密（TDE）加密整个数据文件，防止磁盘被盗。字段级：使用pgcrypto扩展，对敏感字段（手机号、身份证）加密存储，支持 AES 对称加密、非对称加密。传输级：配置 SSL/TLS 加密客户端与服务器的通信。列级权限控制：限制用户访问敏感字段。加密方案：静态数据用 TDE，敏感字段用 pgcrypto，传输用 SSL，多层防护满足金融、政务等数据安全合规需求。
PG 用大对象（LO） 存储超大二进制数据（图片、视频、文件，超过 TOAST 阈值），通过 OID 标识，存储在系统表pg_largeobject中。提供 API：lo_create创建、lo_read读取、lo_write写入、lo_unlink删除。支持流式读写，避免内存溢出。大对象独立于表，需手动管理生命周期，配合事务保证安全。适合存储 GB 级文件，相比 bytea（TOAST），更适合超大文件、流式操作。注意：需开启大对象权限，定期清理无用大对象。
外部表是 PG访问外部数据的虚拟表，基于postgres_fdw扩展，像操作本地表一样查询远程数据库、文件、异构数据库（MySQL、Oracle、Hive）。创建服务器→创建用户映射→创建外部表，即可查询 / 写入外部数据。场景：跨数据库联合查询、数据迁移、多数据源统一查询、数据联邦。外部表不存储数据，实时访问源数据，支持 JOIN 本地表，适合数据中台、跨库协作场景，无需数据同步。
PG 内置时间范围类型（tsrange、tstzrange、daterange），存储一段连续时间，替代开始 / 结束时间两个字段。支持内置函数：判断重叠、包含、相邻，自动约束避免时间冲突。场景：会议室预订、员工排班、活动有效期、计费周期。优势：无需复杂 SQL 判断时间重叠，内置函数高效处理；通过约束强制业务规则（如同一资源时间不重叠）；配合 GiST 索引加速范围查询。大幅简化时间业务逻辑，提升开发效率和数据准确性。
PG 允许开发者自定义聚合函数，扩展内置聚合能力（如 sum、count）。实现步骤：编写状态转换函数（处理每行数据）→编写最终函数（可选，处理结果）→CREATE AGGREGATE定义聚合函数。支持并行计算、有序聚合。场景：自定义统计（如中位数、众数、加权平均）、业务专属计算。自定义聚合函数封装复杂逻辑，SQL 调用简洁，性能与内置函数一致，适合数据分析、行业专属统计场景。
EXPLAIN查看 PG 查询执行计划，不实际执行 SQL，显示索引使用、JOIN 方式、扫描行数、成本。EXPLAIN ANALYZE实际执行 SQL，返回真实执行时间、行数、耗时节点，精准定位慢查询原因。核心信息：是否全表扫描、索引是否命中、是否排序 / 临时表、JOIN 顺序。调试方法：查看全表扫描→加索引；查看排序开销→优化索引；查看行数预估偏差→执行ANALYZE更新统计信息。是 PG 慢查询优化的必备工具，零基础也能快速定位问题。
PG 锁控制并发访问，保证数据一致性。按粒度分：表级锁（全表锁定，并发低）、行级锁（单行锁定，并发高）、页级锁。按类型分：共享锁（读锁，多读共享）、排他锁（写锁，独占）。还有意向锁（协调表锁和行锁）、 advisory 锁（用户自定义锁）。行锁基于 MVCC，读写无冲突。锁机制解决了并发下的脏写、数据覆盖问题，合理使用锁 +MVCC，平衡并发性能和数据安全，是数据库事务的核心保障。
PG 高可用核心避免单点故障，主流方案：1. 流复制 +Patroni：主从物理复制，Patroni 自动故障切换、选主，开源稳定。2. Repmgr：简化流复制管理，自动故障转移。3. Pgpool-II：连接池 + 读写分离 + 故障切换。4. Citus：分布式高可用，支持水平扩展。所有方案基于 WAL 流复制，主库故障后，从库秒级切换，数据不丢失，服务不间断。配合负载均衡、备份恢复，构建 7×24 小时高可用数据库架构。
PG 跨库查询用postgres_fdw扩展，创建外部表映射其他 PG 数据库的表，像本地表一样联合查询。步骤：创建扩展→定义外部服务器→用户映射→创建外部表。支持跨库 JOIN、增删改查，权限隔离。也可使用 dblink 插件，临时连接其他数据库查询。跨库查询对应用透明，无需数据迁移，适合微服务、多库架构的数据联合查询，注意跨库查询性能较低，避免大数据量关联。
PG 递归查询基于递归 CTE，分为锚点成员（初始数据）和递归成员（循环查询），自动迭代直到无数据返回。语法：WITH RECURSIVE 名称 AS (锚点 UNION ALL 递归)。场景：树形结构（部门、菜单、分类）、层级路径、无限级分类。递归查询无需存储过程，单 SQL 实现层级遍历，代码简洁。相比应用层循环查询，性能更高，是处理树形、层级数据的最佳方案。
Check 约束是表级数据校验规则，强制字段值满足指定条件，不满足则插入 / 更新失败。语法：CREATE TABLE 表 (字段 类型 CHECK (条件))。场景：年龄≥0、价格 >0、状态在指定范围、手机号格式校验。Check 约束比应用层校验更可靠，保证数据库数据一致性，支持多字段联合校验。可命名约束，方便维护。PG 会自动优化 Check 约束，不影响查询性能，是数据质量保障的基础工具。
PG 多租户实现 3 种方案：1. 独立数据库：每个租户一个库，隔离性最高，维护复杂。2. 独立 Schema：同一库，租户独立 Schema，隔离性、维护性平衡。3. 共享表 + 租户 ID：所有租户一张表，通过租户 ID 区分，成本最低，隔离性差。推荐Schema 级隔离，配合行级安全策略（RLS）强化隔离，还可使用表继承、分区表优化性能。满足 SaaS 系统的租户隔离、数据安全、弹性扩展需求。
PG 通过PostGIS 扩展支持地理空间数据，是业界标准。存储：支持点、线、面、地理坐标（WGS84）等类型。查询：支持距离计算、范围查询、包含判断、路径规划。配合 GiST 索引加速空间查询，性能优异。场景：地图、物流、外卖、智慧城市。PostGIS 完全集成 PG，支持事务、索引、JOIN，无需独立数据库，是地理信息系统（GIS）的首选数据库。
PG 查询优化核心：减少扫描行数、降低 IO、利用索引。1. 建索引：where/join/order by 字段建 B 树，多值类型建 GIN/GiST。2. 优化 SQL：避免 select *，禁用隐式转换，小表驱动大表。3. 大表分区：时序数据按时间分区，仅扫描目标分区。4. 更新统计信息：ANALYZE让优化器精准决策。5. 开启并行查询：大数据量查询多核加速。6. 覆盖索引：避免回表。7. 读写分离、缓存热点数据。配合 Explain 分析执行计划，持续优化。
PG 表空间移动用于调整数据物理存储位置，步骤：1. 关闭数据库（避免数据损坏）。2. 移动表空间对应的操作系统目录。3. 更新表空间的符号链接（Linux）或路径（Windows）。4. 重启数据库，验证表空间路径。也可在线移动表 / 索引到其他表空间：ALTER TABLE 表 SET TABLESPACE 新表空间。移动时避免业务高峰期，会产生 IO 开销，适合磁盘扩容、存储优化、硬件升级场景。
PG 跨表查询（含跨库外部表）通过事务保证一致性：本地跨表查询在同一事务中，基于 MVCC 读取一致快照，避免脏读、不可重复读。跨库外部表查询：本地事务保证一致性，外部数据依赖源数据库的事务隔离。强一致性场景：使用两阶段提交（2PC），协调多个数据库的事务，要么全部提交，要么全部回滚。配合触发器、外键（本地表），保证跨表数据关联完整性，满足金融、订单等强一致业务。
