Loading

PostgreSQL

1、PostgreSQL入门&安装

2、PostgreSQL基本操作

2.1、单引号与双引号区别

写SQL语句时，单引号用来标识实际的值。
双引号用来标识一个关键字，比如表名，字段名。

select 1.414, '卡塔尔',"葡萄牙"
# 会报错，""双引号里只能是表名或字段名

2.2、数据类型转换

方式1：

-- 当前格式是字符串哦
select '010101010101001'
-- 将字符串转换为位图类型
select bit '010101010101001' 
-- 以下是日期类型的转换
select date '2011-11-11' 将字符串转换为日期类型

方式2：

-- 这里查询结果为0,可以指定长度::bit(20)，前面是之前的结果，后面补0
select '010101010101001'::bit

方式3：完整写法

-- 将varchar转为int
select CAST(varchar '100' as int)
-- 简写形式
select CAST('100' as int)

2.3、官网-数据类型

https://www.postgresql.org/docs/18/datatype.html

2.4、Boolean类型

  • 存储特性：可以存储三个值，分别是true、false和null（代表未知值）
  • 存储空间：仅占用1个字节（8个比特位）的存储空间
  • 值表示：可以用数字0和1表示false和true，同时null作为特殊值存在
-- 以下三种写法都可以
select true,false;
select True,False;
select TRUE,FALSE;
select 'yes'::boolean, boolean 'no';
select boolean 'n',boolean 'y';
select NULL::boolean

-- 特殊的
false AND null → false（只要有一个false结果即为false）
null AND null → null；null OR null → null
false OR null → null（没有true值时结果为null）

2.5、数值类型

  • 整形
    • smallint/int2：2字节存储空间
    • integer/int/int4：4字节存储空间（最常用）
    • bigint/int8：8字节存储空间
  • 浮点型
    • decimal(n,m)：底层均为numeric，转成此类型，还是显示numeric
    • numeric(n,m)
  • 序列
    • MySQL中的主键自增，是基于auto_increment去实现。MySQL里没有序列的对象。
    • PGSQL和Oracle十分相似，支持序列：sequence。
    • PGSQL可没有auto_increment。
    • 序列的正常构建方式：create sequence laozheng. table_id_seq;
    • 序列的下一个：select nextval('laozheng.table_id_seq')
    • 查看当前的序列：select currval('laozheng.table_id_seq')
      默认情况下，seqeunce的起始值是0，每次nextval递增1，最大值9223372036854775807
      告诉缓存，插入的数据比较多，可以指定告诉缓存，一次性计算出20个后续的值，nextval时，就不可以不去计算，直接去高速缓存拿值，效率会有一内内的提升。

表自增效果-传统方式：

create table laozheng.xxx(
	id int8 default nextval(laozheng.table_id_seq),
	name varchar (16)
);

简化形式：
bigserial相当于给bigint类型设置了序列实现自增。

  • smallserial
  • serial
  • bigserial
create table laozheng.yyy (
	id bigserial,
	name varchar (16)
);

在drop表之后，序列不会被删除，但是序列会变为不可用的状态。
因为序列在使用serial去构建时，会绑定到指定表的指定列上。
如果是单独构建序列，再构建表，使用传统方式实现，序列和表就是相对独立的。

2.6、数值的常见操作

操作符 描述 示例 结果
^ 幂 2 ^ 3 8
|/ 平方根 |/36 6
@ 绝对值 @ -5 5
& 与 31 & 16 16
| 或 31 | 32 63
<< 左移 1 << 1 2
>> 右移 16 >> 1 8

2.7、字符串类型

字符串类型用的是最多的一种，在PGSQL里，主要支持三种：

  • character varying （varchar），可变长度的字符串。（最大可以存储1G）
  • character（就是MySQL的char类型），定长字符串。（最大可以存储1G）
  • text（跟MySQL异常）长度特别长的字符串。

字符串拼接：在PostgreSQL中必须使用双竖线"||"进行字符串拼接，与MySQL的CONCAT函数或加号不同。例如：'abc' || '123'会得到abc123

2.8、日期类型

  • 核心类型：PostgreSQL中核心的时间类型有三个：
    • timestamp（时间戳，覆盖 年月日时分秒）
    • date（年月日）
    • time（时分秒）
  • 当前系统时间获取：
      • now函数：
        • 特点：没有时区信息
        • 精度：精确到秒后6位
    • current_timestamp函数：
      • 特点：默认带东八区时区(+08:00)
      • 推荐使用
        常用sql
-- 加减整数：默认单位为天，如下：得到2011-11-12
select date '2011-11-11' + 1
-- → 得到完整时间戳
select date '2011-11-11' + time '12:12:12' 

-- +interval '1day'
-- +interval '1minute' 
-- +interval '1month'支持单位：
select timestamp '2025-10-20 11:11:11' + interval '1day'

2.9、JSON、JSONB类型

JSON在MySQL8.x中也做了支持，但是MySQL支持的不好，因为JSON类型做查询时，基本无法给JSON字段做索引

JSON和JSONB的使用基本没区别。撇去JSON类型，本质上JSON格式就是一个字符串，比如MySQL5.7不支持JSON的情况的下，使用varchar也可以，但是字符串类型无法校验JSON的格式，其次单独的字符串没有办法只获取JSON中某个key对应的value。

JSON和SONB的区别：

  • JSON类型无法构建索引，JSONB类型可以创建索引。
  • JSON类型的数据中多余的空格会被存储下来。JSONB会自动取消多余的空格。
  • JSON类型甚至可以存储重复的ky,以最后一个为准。JSONB不会保留多余的重复key(保留最后1个)。
  • JSON会保留存储时key的顺序，JSONB不会保留原有顺序。

操作SQL

select '9'::JSON, 'null'::JSON, '"laozheng"'::JSON, 'true'::JSON;
select '9'::JSONB, 'null'::JSONB, '"laozheng"'::JSONB, 'true'::JSONB;

2.10、复合类型

复合类型类似于Java中的对象，可以将多个属性封装成一个类型。例如Java中的User类包含Info对象，而Info对象又包含name和age属性。

public class User{
	private String id;
	private Info info;
}
public class Info{
	private String name;
	private String city;...
}

-- 创建复合类型
create type info_type as (name varchar(32),age int);
--构建表，映射user
create table tb_user(
	id serial,
	info info_type
);
-- 添加数据
insert into tb_user(info) values(('张三',23));
insert into tb_user(info) values(('李四',25));
-- 查询数据
select * from tb_user;

2.11、数组类型

构建表指定数组长度后，并不是说数组内容只有2的长度，可以插入更多数据
甚至在你插入数据，如果将二维数组结构的数组扔到一维数组上，也可以存储。

-- 建表：
create table test(id serial,col1 int[],col2 int[2],col3 int[][]);
-- 数组编写方式
select '{{how,are},{are,you}}'::varchar[];
select array[[1,2],[3,4]];
insert into test(col1,col2,col3) values('{1,2,3}','{4,5,6}','{7,8,9}');
insert into test(col1,col2,col3) values('{1,2,3}','{4,5,6}','{{1,2},{3,4}}');

select * from test;

特殊的字符，保存为数组，要注意事项：

create table test(id serial,col1 varchar[]);
-- 单引号处理：使用两个单引号表示一个单引号字符
select '{''how''}'::varchar[];

-- 大括号处理，无需特殊转义，再加1个即可
select '{{how}}'::varchar[]
-- 逗号处理：用双引号包裹包含逗号的字符串
select ('{"how,are"}'::varchar[])[1]
-- 双引号处理：使用反斜杠进行转义
select ('\"how\"'::varchar[])[1]

数组包含与被包含

-- 包含：前面数组[1,2]包括[1]
select array[1,2] @> array[1];
-- 被包含，前面数组[1,2]被包含在数组[1]中，很明显不是
select array[1,2] <@array[1]; -- 返回false
-- 是否有相同元素 &&
select array[1,2] && array[1] --返回true

2.12、IP类型

-- IP类型：cidr
select '192.168.11.11'::cidr

-- 错误示例：256肯定不对，会报错
select '192.168.11.256'::cidr

create table test{id serial,ip cidr};
insert into test(ip) values('192.168.11.11');
insert into test(ip) values('192.168.11.12');
insert into test(ip) values('192.168.11.13');
select * from test where ip between '192.168.11.11' and '192.168.11.12'

2.13、枚举类型

语法格式：使用CREATE TYPE name AS ENUM语句创建枚举类型

-- 声明一个日期的枚举，表示只能是周一、周二、周日
create type week_type as enum ('Mon','Tues','Sun');

创建表时，指定枚举类型

create table test{
	id serial,
	weekend week_type
};

insert into test(weekend) values('Mon');

3、PostgreSQL的对象操作

3.1、约束

3.1.1、主键约束

create table test{
	id serial primary key,
	name varchar(32)
};

3.1.2、非空约束

create table test{
	id serial,
	name varchar(32) not null
};

3.1.3、唯一约束

create table test{
	id serial,
	id_card varchar(32) unique
};

3.1.4、检查约束

-- price价格，discount_price打折后价格大于0且价格>=优惠后价格
create table test{
	id serial,
	price numeric check(price>0),
	discount_price numeric check(discount_price >0),
	check(discount_price<=price)
}

3.1.5、默认值

create table test{
	id serial,
	created_time timestamp default current_timestamp
}

3.2、触发器

1. 什么是触发器？
触发器是由数据库中的事件（如 INSERT、UPDATE、DELETE、TRUNCATE）自动触发的一段存储过程代码。它在表发生指定操作时自动执行，常用于实现数据一致性、日志记录或级联操作。
2. 触发器能监听哪些事件？
PostgreSQL 的触发器支持以下事件：

  • INSERT（插入）
  • UPDATE（更新）
  • DELETE（删除）
  • TRUNCATE（清空整表）
    可以在事件 之前（BEFORE） 或 之后（AFTER） 触发。

3. 行级触发器 vs 语句级触发器

  • FOR EACH ROW：每影响一行就触发一次（适合处理单行逻辑，如级联删除）。
  • FOR EACH STATEMENT：整个 SQL 语句只触发一次（不管影响多少行）。
    通常用 FOR EACH ROW 更常见。
    4. 触发器函数怎么写？
  • 必须用 CREATE FUNCTION ... RETURNS trigger 声明。
  • 函数体内可使用特殊变量：
    • OLD：删除或更新前的行数据。
    • NEW：插入或更新后的行数据。
  • 函数最后通常 RETURN OLD 或 RETURN NEW（即使不使用也建议返回）。
    示例（删除学生时同步删分数）：
CREATE FUNCTION delete_student_score() 
RETURNS trigger AS $$
BEGIN
  DELETE FROM score WHERE student_id = OLD.id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

5. 如何创建触发器？
语法结构：

CREATE TRIGGER 触发器名
AFTER DELETE ON student
FOR EACH ROW
EXECUTE FUNCTION delete_student_score();
  • 指定事件（如 AFTER DELETE）
  • 指定作用表（ON student）
  • 指定行级/语句级（FOR EACH ROW）
  • 指定要执行的函数（EXECUTE FUNCTION）
    6. 为什么用 $$ ... $$ 而不是单引号？
    因为函数体中可能包含单引号（如字符串），用 $$ 作为界定符可避免引号冲突，相当于“安全的字符串包裹”。

7. 语言必须指定吗？
是的！必须在函数末尾写明语言，如 LANGUAGE plpgsql。PostgreSQL 支持多种语言（plpgsql、C、SQL 等），不指定会报错。
8. 外键 vs 触发器实现级联？

  • 外键可自动实现级联删除，但耦合强、维护难。
  • 触发器更灵活，可自定义逻辑（如记录日志、调用其他服务），但需手动编写和测试。
    9. 学触发器的重点是什么？
    不是背语法，而是：
  • 看到能看懂；
  • 知道它能做什么；
  • 需要用时能查文档快速写出。
    尤其对 Java 开发者，了解即可，重点仍在应用层逻辑。

3.3、表空间

1. PostgreSQL 中的表空间（tablespace）是什么？
表空间在 PostgreSQL 中仅用于指定数据在磁盘上的物理存储路径。它不参与逻辑结构管理，只决定表、索引等对象的数据文件存放在哪个目录。

2. 默认存储位置在哪？
如果不创建自定义表空间，所有数据都会存放在 PostgreSQL 的默认数据目录（通常叫 pg_data）下的 base/数据库OID/ 子目录中。

3. 如何查看某张表的实际存储路径？
使用函数：

SELECT pg_relation_filepath('表名');

返回结果如 base/24577/41000 或 pg_tblspc/16385/PG_12_.../24577/41000，表示该表的数据文件位置。

4. 如何创建自己的表空间？
语法：

CREATE TABLESPACE 表空间名 LOCATION '/绝对路径';

注意两点：

  • 必须由超级用户（如 postgres）执行；
  • 指定的目录必须提前手动创建好，且 PostgreSQL 有读写权限。

5. 如何让数据库或表使用自定义表空间？

  • 创建数据库时指定：
    CREATE DATABASE db_test TABLESPACE tp_test;
    此后该库中新建的表和索引默认存到该表空间。
  • 创建表时也可单独指定：
    CREATE TABLE test (id int) TABLESPACE tp_test;

6. 为什么数据没直接出现在我指定的目录里？
PostgreSQL 不会把数据文件直接放在你 LOCATION 指定的路径下，而是：

  • 在 $PGDATA/pg_tblspc/ 下创建一个以表空间 OID 命名的符号链接；
  • 实际数据存放在你指定的目录中，但路径结构为：
    /你指定的路径/PG_主版本_编译ID/数据库OID/表文件
    这是正常行为，并非错误，也不是存了两份。

7. 表空间的作用是什么？

  • 将不同业务的数据分散到不同磁盘，便于 I/O 优化或备份管理；
  • 控制大表的存储位置，避免默认目录空间不足。

3.4、解决表空间问题

1. 设置表空间后，数据是不是存了两份？
不是。PostgreSQL 不会在默认目录和自定义表空间目录中各存一份完整数据。
2. 为什么看起来像有两份？

  • 在 $PGDATA/pg_tblspc/ 目录下会有一个以表空间 OID 命名的符号链接（symlink），指向你指定的实际存储路径。
  • pg_relation_filepath() 返回的路径可能包含这个符号链接路径（如 pg_tblspc/16385/.../41020），而你手动查看的是你指定的真实路径（如 /pg_data/tp_test/.../41020）。
  • 两个路径指向同一个物理文件，并非重复存储。

3. 实际存储位置在哪？
数据只存一份，就存在你创建表空间时用 LOCATION 指定的那个目录里。$PGDATA 目录下只有元数据和符号链接，没有冗余数据文件。

4. 为什么会有误解？
因为：

  • 文件名（如 41020）在多个路径中出现；
  • 没注意到 pg_tblspc 下是符号链接而非真实副本；
  • 误以为不同路径 = 不同文件。

5. 正确认知：
使用表空间只是改变数据文件的物理存放位置，系统通过符号链接机制让 PostgreSQL 能正确找到它。数据始终只有一份，不会双写或冗余存储。

3.5、表空间重复存储

1. 表空间设置后，数据是不是存了两份？
不是。看起来像存了两份，其实是一份真实数据 + 一个链接文件。

2. 默认目录下看到的“数据文件”是什么？
在 PostgreSQL 默认数据目录（如 $PGDATA/pg_tblspc/）下看到的同名文件，实际上是一个软链接（symbolic link），不是真实数据文件。

3. 软链接的作用是什么？

  • 它像 Windows 的“快捷方式”，指向你通过 CREATE TABLESPACE ... LOCATION 指定的真实存储路径。
  • PostgreSQL 启动或访问表时，通过这个链接自动找到实际数据位置。

4. 为什么要有这个链接？
PostgreSQL 内部依赖统一的目录结构管理所有对象。即使数据存到外部路径，也需要在默认目录下保留一个“入口”（即软链接），确保系统能正常定位和加载数据。

5. 结论：
使用表空间不会导致数据冗余。真实数据只存在于你指定的物理路径中，默认目录下只是保留一个轻量级的链接文件，用于系统内部寻址。

3.6、视图

1. 视图是什么？
视图是一个虚拟表，本质是一条封装好的 SELECT 语句。对用户来说像一张真实表，可以查询；对开发者来说，就是一条预定义的 SQL。

2. 视图的主要作用？

  • 简化复杂查询（如多表连接），避免重复写长 SQL；
  • 隐藏敏感字段（如不暴露密码、身份证等）；
  • 提供统一的数据访问接口。

3. 如何创建视图？
语法：

CREATE VIEW 视图名 AS
SELECT 字段 FROM 表 WHERE ...;

4. 视图能修改数据吗？

  • 单表视图（只查一张表、无聚合、无 DISTINCT 等）：PostgreSQL 默认允许 UPDATE、INSERT、DELETE，操作会直接作用于底层表。
  • 多表视图（涉及 JOIN、子查询、聚合等）：默认不允许修改，会报错。

5. 能对视图进行写操作吗？推荐吗？
虽然简单视图支持写操作，但强烈不推荐。
原因：

  • 容易误改底层数据；
  • 逻辑不清晰，维护困难；
  • 视图的设计初衷是查询，不是更新。

6. 修改视图数据的本质是什么？
对视图的写操作，最终都会直接修改其依赖的原始表数据，视图本身不存储数据。

3.7、索引应用

1. 索引是什么？
索引是数据库中用于加快查询速度的数据结构。它能快速定位数据，但会带来额外的存储开销和写入性能损耗（因为要维护索引）。

2. 索引的优缺点？

  • 优点：大幅提升查询效率；可实现唯一性等约束（如唯一索引）。
  • 缺点：
    • 占用额外磁盘空间；
    • 插入、更新、删除时需同步更新索引，变慢；
    • 某些索引类型限制查询方式（如哈希索引不支持范围查询）。

3. PostgreSQL 常见索引类型？

  • B-tree（默认）：支持等值、范围、排序查询，最常用。
  • Hash：只支持等值查询（=），不支持范围或排序。
  • GIN（Generalized Inverted Index）：适合数组、JSON、全文搜索等复合类型，支持“包含”类操作（如 @>）。

4. 如何创建索引？
基本语法：

CREATE INDEX 索引名 ON 表名 (字段名);

指定类型（如 GIN）：

CREATE INDEX 索引名 ON 表名 USING GIN (数组字段);

5. 索引生效的条件？

  • 表数据量足够大（小表可能直接全表扫描更快）；
  • 查询条件匹配索引字段；
  • 查询方式符合索引特性（如 Hash 索引不能用于 > <）。

6. 如何验证索引是否生效？
使用 EXPLAIN 查看执行计划：

  • Seq Scan：全表扫描，未用索引；
  • Index Scan 或 Bitmap Index Scan：使用了索引。

7. 测试效果对比（以 300 万数据为例）

  • 无索引：WHERE name = 'xxx' 耗时约 0.3 秒，执行计划为 Seq Scan；
  • 有 B-tree 索引：同样查询耗时降至 0.01 秒内，执行计划为 Index Scan；
  • 数组字段无索引：WHERE phone @> ARRAY['...'] 耗时约 0.5 秒；
  • 有 GIN 索引：同样查询降至 0.01 秒内，执行计划为 Bitmap Index Scan。

8. 关于物化视图？
PostgreSQL 支持物化视图（Materialized View），但直播中未深入讲解。它与普通视图不同：会实际存储查询结果，需手动刷新，适合复杂聚合查询的缓存场景。

9. 后续待探索问题？

  • 表空间是否真会“双写”？（实际不会，只是软链接）
  • 执行计划（EXPLAIN）的详细解读；
  • MVCC 和事务机制；
  • 主从复制配置；
  • 数据库插件扩展等。

3.8、物化视图-基本操作

1. 普通视图 vs 物化视图的区别？

  • 普通视图：只是一个 SQL 查询的封装，没有物理存储，每次查询都重新执行原始 SQL。
  • 物化视图：会把查询结果实际存到磁盘上，像一张真实表，查询时直接读取存储数据，不重新执行原 SQL。
    2. 物化视图的优点？
  • 查询速度快（因为数据已预计算并存储）；
  • 可以在物化视图上创建索引、做表连接等操作；
  • 适合复杂、耗时的查询场景（如多表聚合、统计报表）。

3. 物化视图的缺点？

  • 数据不是实时同步的：原表更新后，物化视图不会自动更新；
  • 需要手动或通过定时任务/触发器刷新，存在数据延迟；
  • 占用额外存储空间（因为保存了一份完整结果）。

4. 物化视图能修改吗？

  • 不能直接 INSERT / UPDATE / DELETE 物化视图中的数据；
  • PostgreSQL 明确禁止对物化视图的写操作，会报错；
  • 所有数据变更必须通过刷新（REFRESH） 来同步原表结果。

5. 如何创建物化视图？
语法：

CREATE MATERIALIZED VIEW 视图名 AS
SELECT 字段 FROM 表 WHERE ...;

6. 如何更新物化视图？
使用命令手动刷新：

REFRESH MATERIALIZED VIEW 视图名;
  • 刷新会重新执行原查询，覆盖旧数据；
  • 可配合触发器（表变更时自动刷新）或定时任务（如 cron）实现半自动同步。

7. 物化视图和原表的关系？

  • 完全解耦：物化视图是独立的物理存储，修改原表不影响它，反之亦然；
  • 可理解为“快照”——创建时的数据状态，之后不会自动变化。

8. 使用场景？

  • 读多写少、对实时性要求不高的报表或分析查询；
  • 复杂 SQL 结果需要频繁访问，但计算成本高；
  • 需要给结果加索引提升查询性能。

3.9、物化视图-全量更新/增量更新

1. 物化视图如何同步原表数据？
PostgreSQL 提供两种方式：全量更新 和 增量更新。

2. 全量更新是什么？怎么用？

  • 含义：删除物化视图当前所有数据，重新执行创建时的查询语句，生成全新结果。
  • 命令：
    REFRESH MATERIALIZED VIEW 物化视图名;
  • 特点：简单直接，但数据量大时耗时长、锁表（默认会阻塞查询）。

3. 增量更新是什么？怎么用？

  • 含义：只同步自上次刷新以来发生变化的数据（包括新增、修改、删除）。
  • 命令：
    REFRESH MATERIALIZED VIEW CONCURRENTLY 物化视图名;
  • 前提条件：
    • 物化视图上必须存在唯一索引（用于识别每行的唯一性）；
    • 否则会报错：“cannot refresh materialized view concurrently”。

4. 为什么需要唯一索引？
因为 PostgreSQL 需要通过唯一键（如主键 ID）来判断哪一行是“新”的、哪一行被“修改”或“删除”，从而决定如何增量同步。

5. 增量更新能同步修改和删除吗？
可以。

  • 不只是新增数据，UPDATE 和 DELETE 也会被同步；
  • 内部依赖 PostgreSQL 的 MVCC 机制（通过 xmin / xmax 等系统字段判断行版本变化）；
  • 只要原表数据发生变更并提交，下次 CONCURRENTLY 刷新就能反映出来。

6. 如何给物化视图加唯一索引？
语法和普通表一样：

CREATE UNIQUE INDEX 索引名 ON 物化视图名 (字段名);

例如：

CREATE UNIQUE INDEX idx_mv_test_id ON mv_test (id);

7. 增量更新的优势？

  • 刷新时不锁表（允许并发查询）；
  • 只处理变化数据，速度快、资源消耗低；
  • 适合高频刷新场景。

8. 实际使用建议？

  • 若数据量小、刷新不频繁 → 用全量更新；
  • 若数据量大、需实时性、不能阻塞查询 → 用增量更新（记得先建唯一索引）；
  • 可通过触发器或定时任务（如 cron） 自动执行刷新命令。

4、PostgreSQL的事务

4.1、事务基本介绍&特点

1. 什么是事物（Transaction）？

  • 定义：一组相关的数据库操作（增删改查），要么全部成功，要么全部失败。
  • 作用：保证数据的一致性和完整性。

2. ACID特性是什么？

  • A - 原子性（Atomicity）：事务中的所有操作要么全部执行，要么一个也不执行。
  • C - 一致性（Consistency）：事务完成后，数据库应处于一致状态，即预期结果与实际结果一致。
  • I - 隔离性（Isolation）：并发事务之间相互隔离，一个事务的中间状态对其他事务不可见。
  • D - 持久性（Durability）：一旦事务提交，所做的更改将永久保存，即使系统崩溃也不会丢失。

3. PostgreSQL 中的 MVCC 是什么？

  • 多版本并发控制（Multi-Version Concurrency Control, MVCC）：允许读写操作互不干扰，实现高并发性能。
  • 优点：读操作不会被写操作阻塞，反之亦然。但写写冲突仍需通过锁机制解决。

4. PostgreSQL 支持哪些类型的锁？

  • 表锁（Table Locks）：锁定整个表，防止并发修改。
  • 行锁（Row Locks）：锁定特定行，允许多个事务同时操作不同行。
  • 页锁（Page Locks）：锁定数据页，较少使用，主要用于内部维护。

5. PostgreSQL 事务与其他数据库的区别？

  • 支持 DDL 在事务中：不仅支持 DML（如 INSERT、UPDATE、DELETE），还支持 DDL（如 CREATE TABLE、ALTER TABLE）在同一个事务中执行。
  • 集群一致性：在分布式环境中，可以确保多个节点上的操作一致，例如创建表时，所有节点都必须成功才能提交事务。

6. 使用事务的好处和代价？

  • 好处：确保数据完整性和一致性，特别是在并发环境下。
  • 代价：为了保证强一致性，可能会增加响应时间，尤其是在分布式系统中等待多个节点同步完成时。

7. 如何理解 CAP 理论？

  • CAP：指一致性（Consistency）、可用性（Availability）、分区容忍性（Partition Tolerance）。
  • 权衡：在分布式系统中，无法同时满足三者，通常需要根据业务需求选择合适的平衡点。
    这些要点帮助你理解和掌握 PostgreSQL 中的事物管理及其核心概念。

4.2、事务的操作

1. PostgreSQL 默认是自动提交事务吗？
是的。和 MySQL 一样，默认每条 SQL 执行后立即自动提交，不需要手动 COMMIT（不像 Oracle 需要手动提交）。

2. 如何关闭自动提交？
在 psql 命令行中使用：

\set AUTOCOMMIT off

关闭后，所有操作都处于一个未提交的事务中，直到你手动执行 COMMIT 或 ROLLBACK。

注：这种方式主要用于学习或调试，实际开发中很少用。

3. 标准事务操作命令有哪些？

  • BEGIN;：开启一个事务；
  • COMMIT;：提交事务，使更改永久生效；
  • ROLLBACK;：回滚事务，撤销所有未提交的更改。

END; 等价于 COMMIT;，但通常直接用 COMMIT 更清晰。

4. 事务中的数据可见性如何？

  • 在事务内执行的 INSERT/UPDATE/DELETE，自己能查到；
  • 其他会话查不到，直到你 COMMIT 提交。

5. 什么是保存点（Savepoint）？

  • 用于大事务中的局部回滚；
  • 可以在事务中设置多个“检查点”，出错时只回滚到某个保存点，而不是整个事务；
  • 语法示例：
    BEGIN;
INSERT ...;
SAVEPOINT sp1;
UPDATE ...;
-- 出错了？
ROLLBACK TO sp1;
COMMIT;

虽然功能存在，但在实际业务中较少使用。

6. PostgreSQL 的默认隔离级别是什么？

  • 默认是 READ COMMITTED（读已提交）；
  • 它也支持 REPEATABLE READ 和 SERIALIZABLE；
  • 注意：PostgreSQL 的 REPEATABLE READ 实际上提供了比 SQL 标准更强的保证，接近 SERIALIZABLE，但不会完全等同。

这些就是事务基本使用的核心要点，简洁明确，便于记忆。

4.3、事务-保存点

1. 什么是保存点（Savepoint）？

  • 是事务中的一个“检查点”或“快照点”；
  • 允许在大事务中局部回滚，而不是整个事务全部回滚。

2. 为什么需要保存点？

  • 大事务包含多个操作，若中间某步失败，全回滚成本高（要从头重做）；
  • 使用保存点后，出错只需回滚到最近的保存点，再重试后续步骤，节省时间和资源。

3. 保存点会破坏事务的原子性吗？

  • 表面上看会：因为事务被拆成多段，部分成功、部分回滚；
  • 实际上可控：只要最终要么全部提交、要么彻底回滚，仍可保证整体原子性；
  • 关键在于开发者如何使用——不能提交“半成品”。

4. 保存点的基本语法？

  • 设置保存点：
    SAVEPOINT 保存点名;
  • 回滚到保存点：
    ROLLBACK TO 保存点名;
  • 提交整个事务：
    COMMIT;
  • 完全回滚（不指定保存点）：
    ROLLBACK;  -- 回滚整个事务

5. 实际项目中常用吗？

  • 很少用。
  • 真实业务（如电商下单）通常采用最终一致性方案（如消息队列 RocketMQ、Seata 等），而非依赖数据库保存点；
  • 保存点更适合单体应用或简单场景下的容错处理。

6. 面试怎么答？

  • 如果被问“大事务如何处理？”，可以说：

    “PostgreSQL 支持保存点（SAVEPOINT），可以在事务中设置回滚点，减少失败时的重试成本。但生产环境更推荐用最终一致性方案，比如通过消息队列解耦，避免长事务带来的锁竞争和性能问题。”

4.4、事务的隔离级别

1. 事务并发会出现哪些问题？

  • 脏读（Dirty Read）：读到其他事务未提交的数据。如果那个事务回滚了，就读到了“不存在”的数据。必须避免。
  • 不可重复读（Non-repeatable Read）：同一事务中，多次读同一行数据，结果不同，因为其他事务已提交修改。
  • 幻读（Phantom Read）：同一事务中，多次执行相同查询，返回的行数不同，因为其他事务插入或删除了符合条件的行。

2. SQL 标准定义的四种隔离级别？

  1. 读未提交（Read Uncommitted）：啥都不防，可能脏读、不可重复读、幻读。PostgreSQL 不支持（仅为兼容保留，实际无法设置）。
  2. 读已提交（Read Committed）：PostgreSQL 默认级别，防止脏读，但允许不可重复读和幻读。
  3. 可重复读（Repeatable Read）：防止脏读、不可重复读；在 PostgreSQL 中还能防止幻读（比标准更强）。
  4. 串行化（Serializable）：完全隔离，并发事务效果如同串行执行，解决所有问题，但性能最低。

3. PostgreSQL 的隔离级别特点？

  • 实际只用两种：Read Committed（默认） 和 Serializable；
  • 虽然语法支持 REPEATABLE READ，但它的行为等同于串行化（内部通过 SSI 技术实现强一致性）；
  • 所以可以简单理解为：PG 只有 读已提交 和 串行化 两种有效隔离级别。

4. 如何验证默认隔离级别是“读已提交”？

  • 开两个会话（窗口），都开启事务；
  • 会话 A 查询某行数据；
  • 会话 B 修改该行但不提交 → A 再查，看不到变化（无脏读）；
  • 会话 B 提交后 → A 再查，能看到新值（说明不是可重复读，而是读已提交）。

5. 实际开发建议？

  • 大多数场景用默认的 Read Committed 就够了；
  • 需要强一致性的场景（如金融对账），才考虑 REPEATABLE READ 或 SERIALIZABLE；
  • 不要过度依赖“可重复读”来避免业务逻辑错误，应结合应用层设计（如乐观锁、最终一致性等）。

4.5、并发MVCC

1. 为什么需要 MVCC（多版本并发控制）？

  • 传统锁机制会导致读写互相阻塞：写的时候不能读，读的时候不能写；
  • MVCC 通过保存数据的多个版本，让读操作可以读旧版本，写操作生成新版本，从而实现读写不冲突。

2. MVCC 能解决什么问题？

  • 避免读写阻塞：读操作不会被写操作阻塞，提升并发性能；
  • 保证事务隔离性：每个事务看到的是它开始时的数据快照；
  • 注意：MVCC 只解决读写冲突，写写冲突仍需靠锁处理。

3. PostgreSQL 中 MVCC 如何实现？

  • 每行数据隐含两个系统字段：
    • xmin：创建该行版本的事务 ID；
    • xmax：删除或更新该行版本的事务 ID（若为 0，表示当前版本仍有效）。
  • 事务开启时会分配一个唯一的事务 ID；
  • 查询时，根据当前事务 ID 和 xmin/xmax 判断该行是否“可见”。

4. MVCC 的工作过程（举例）

  • 事务 A 开启，查询某行 → 看到版本 V1（xmin=100, xmax=0）；
  • 事务 B 开启，也查询该行 → 同样看到 V1；
  • 事务 A 修改该行 → 生成新版本 V2（xmin=101），原版本 V1 的 xmax 被设为 101；
  • 事务 B 再次查询 → 仍看到 V1（因为 A 尚未提交，V2 对 B 不可见）；
  • 事务 A 提交后 → 事务 B 下次查询才能看到 V2，此时 V2 的 xmax=0，成为最新有效版本。

5. 关键规则总结

  • 读操作永远读对自己可见的版本，不会被写操作阻塞；
  • 写操作会生成新版本，不影响其他事务读旧版本；
  • 只有当写事务提交后，新版本才对其他事务可见；
  • xmin 和 xmax 是判断“可见性”的核心依据。

6. 注意事项

  • PostgreSQL 的 MVCC 实现在事务开启时就确定了快照（不是第一次查询时）；
  • 与 MySQL 的 MVCC 实现逻辑不同（MySQL 是在首次 SELECT 时创建快照）；
  • 长时间运行的事务可能导致“膨胀”（旧版本无法清理），需定期 VACUUM。

4.6、行锁、表锁

1. 为什么需要锁？

  • 保证并发环境下数据的一致性和完整性；
  • 防止多个事务同时修改同一数据导致冲突。

2. PostgreSQL 支持哪几种锁？

  • 表级锁（Table-level Locks）：锁定整张表；
  • 行级锁（Row-level Locks）：只锁定特定行，粒度更细、并发更高。

3. 表锁怎么用？

  • 语法：
    BEGIN;
LOCK TABLE 表名 IN 锁模式;
-- 操作
COMMIT;  -- 提交后自动释放锁
  • 常见锁模式：
    • ACCESS EXCLUSIVE：最严格，默认用于 DROP TABLE、ALTER TABLE 等，会阻塞所有其他操作；
    • ACCESS SHARE：最宽松，SELECT 会自动加此锁，不阻塞其他读或写。
  • 注意：表锁必须在事务中使用，提交或回滚后自动释放。

4. 行锁怎么用？

  • 通过 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 实现：
    • FOR UPDATE：加排他行锁，防止其他事务读取（带 FOR UPDATE）或修改该行；
    • FOR SHARE：加共享行锁，允许其他事务读，但不能修改或加 FOR UPDATE。
  • 示例：
    BEGIN;
SELECT * FROM test WHERE id = 3 FOR UPDATE;
-- 此时其他事务无法对 id=3 的行执行 FOR UPDATE 或 UPDATE
COMMIT;

5. PostgreSQL 行锁 vs MySQL 行锁？

  • PostgreSQL 更智能：
    • 即使 WHERE 条件未命中索引（如 LIKE '%xxx%'），也不会升级为表锁，仍只锁符合条件的行；
    • 而 MySQL 在某些情况下（如无索引）可能锁更多行甚至全表。
  • 所以 PG 的行锁更安全、更高效，适合高并发场景。

6. 锁的阻塞行为？

  • 如果一个事务已持有某行的 FOR UPDATE 锁：
    • 其他事务的 SELECT ... FOR UPDATE 或 UPDATE 会阻塞等待；
    • 普通 SELECT（不带锁）不会阻塞，仍可读（得益于 MVCC）。
  • 可用 NOWAIT 避免死等：
    SELECT * FROM test WHERE id = 3 FOR UPDATE NOWAIT;
    若无法立即获取锁，直接报错而不是等待。

7. 实际使用建议？

  • 优先使用行锁（FOR UPDATE），避免表锁影响并发；
  • 表锁仅用于 DDL 操作（如改表结构）或极少数全局维护场景；
  • 长时间持有锁会降低性能，应尽快提交事务释放锁。

5、PostgreSQL的备份&恢复

5.1、备份的方式

1. 为什么要备份？

  • 防止数据丢失，是数据安全的第一道防线；
  • 数据可能因硬件损坏（如硬盘爆炸）、人为误删（如实习生级联删除）、程序 Bug 等原因丢失。

2. PostgreSQL 有哪几种备份方式？
共三种：

  1. 逻辑备份（SQL 脚本备份）
  2. 物理备份（文件系统级备份）
  3. 归档备份（基于 WAL 的持续备份）

3. 逻辑备份（如 pg_dump）特点？

  • 优点：简单、可读、跨平台、适合小数据量或开发环境；
  • 缺点：数据量大时导出/恢复极慢，不适合生产环境全量备份；
  • 常用于给同事导出测试数据或上线前的结构备份。

4. 物理备份（直接复制数据文件）特点？

  • 原理：直接复制数据库在磁盘上的数据文件（如 base/ 目录）；
  • 优点：恢复速度极快，启动即用；
  • 缺点：
    • 必须在数据库完全停止时操作，否则可能数据损坏；
    • 要求版本和环境完全一致（如 PG 14 不能还原到 PG 16）；
    • 不适用于正在运行的生产库。
  • 适合冷迁移或离线环境的数据复制。

5. 为什么事务提交后数据不一定写入磁盘？

  • PostgreSQL 使用 BG Writer（后台写进程） 周期性地将内存中的脏页刷到磁盘；
  • 这样做是为了减少频繁 IO，提升性能；
  • 但这也意味着：事务提交 ≠ 数据已落盘。

6. 如果服务器宕机，未落盘的数据会丢吗？

  • 不会丢（前提是配置了 WAL）！
  • 因为 PostgreSQL 有 WAL（Write-Ahead Logging）机制：
    • 所有修改先写入 WAL 日志（持久化到磁盘），再写入数据文件；
    • 即使宕机，重启时可通过 WAL 日志重放（Redo） 未刷盘的事务，保证数据不丢失。

7. 归档备份是什么？

  • 属于物理备份的增强版；
  • 不仅备份数据文件，还持续归档 WAL 日志；
  • 结合基础备份 + WAL 归档，可实现任意时间点恢复（PITR）；
  • 是生产环境最推荐的备份方案。

（注：后续内容会围绕 WAL 和归档展开，这是集群、高可用、容灾的基础。）

5.2、归档备份（下）

1. WAL 是什么？

  • WAL = Write-Ahead Logging（预写日志）；
  • 所有数据修改先写入 WAL 日志，再写入实际数据文件；
  • 即使服务器宕机，只要 WAL 日志还在，就能恢复未落盘的数据。

2. 为什么事务提交后数据不会丢？

  • 因为提交时，WAL 日志已经持久化到磁盘；
  • 数据文件可能还在内存中（由 BG Writer 后台慢慢刷盘），但日志已保底；
  • 重启时 PostgreSQL 会通过 WAL 重放（Redo） 未完成的写操作，确保数据完整。

3. WAL 日志有什么问题？

  • WAL 文件是循环使用的（比如 1024 个段，写满后从头覆盖）；
  • 如果不干预，旧日志会被新日志覆盖，无法用于长时间恢复；
  • 所以不能单独靠 WAL 做备份。

4. 归档（Archiving）的作用是什么？

  • PostgreSQL 提供 归档进程（archiver）；
  • 在 WAL 文件被覆盖前，自动将其复制到安全位置（如其他磁盘或远程服务器）；
  • 这样就能永久保留所有历史 WAL 日志，用于任意时间点恢复。

5. 完整的生产级备份怎么做？

  • 第一步：做一次基础物理备份（全量备份数据文件）；
  • 第二步：开启 WAL 归档，持续保存所有后续 WAL 日志；
  • 恢复时：先还原基础备份，再重放归档的 WAL 日志，可恢复到任意时间点（比如删表前一秒）。

6. 实际应用场景举例？

  • 每天凌晨 1 点做全量备份；
  • 下午 2 点有人误删了整张表；
  • 可立即停止服务，用昨晚的全量备份 + 今天 1 点到 2 点前的 WAL 日志，精准恢复到删表前的状态。

7. 和 MySQL 的对比？

  • PostgreSQL 的 WAL ≈ MySQL 的 Redo Log + Binlog（部分功能）；
  • 两者都通过“日志 + 备份”实现高可靠恢复，理念相似。
posted @ 2025-12-21 20:46  yjiu11  阅读(3)  评论(0)    收藏  举报