Postgres SELECT & INSERT 流程 ？

SELECT

当执行SELECT查询时，PostgreSQL数据库会按照以下流程进行处理：

1. 首先，查询语句会被发送到服务器。

2. 服务器会接收查询请求，并根据查询条件从表中读取数据。

3. 数据库会将数据存储在磁盘上的数据文件中，然后将其读取到内存中进行处理。

4. 数据库会对数据进行排序、过滤、聚合等操作。

5. 查询结果会被发送回客户端。

6. 客户端会接收到查询结果，并将其显示在屏幕上。

在查询执行过程中，PostgreSQL数据库会使用多种技术来提高查询性能，例如使用索引、分区表、并行查询等。同时，PostgreSQL还支持多种查询语句，例如JOIN、GROUP BY、HAVING等，可以帮助用户更加灵活地查询数据。

缓存

当PostgreSQL执行SELECT查询时，会涉及到三个重要的缓存：Shared Buffers、OS Cache和Disk Cache。下面分别介绍这三个缓存的作用：

1. Shared Buffers：这是PostgreSQL自己实现的内存缓存，用于缓存最常用的数据块。当查询需要读取数据时，PostgreSQL首先会从Shared Buffers中查找数据，如果没有找到，则需要从磁盘上的数据文件中读取数据。

2. OS Cache：这是操作系统提供的内存缓存，用于缓存已经读取过的数据块。当PostgreSQL需要读取数据时，如果数据已经被缓存在OS Cache中，则可以直接从OS Cache中读取数据，而不需要从磁盘上的数据文件中读取。

3. Disk Cache：这是磁盘本身提供的缓存，用于缓存磁盘上的数据块。当数据被写入磁盘时，数据会先被写入Disk Cache中，然后再由操作系统将数据写入磁盘。当需要读取数据时，如果数据已经被缓存在Disk Cache中，则可以直接从Disk Cache中读取数据，而不需要从磁盘上的数据文件中读取。

在查询执行过程中，PostgreSQL会根据需要将数据缓存在这三个缓存中，以提高查询性能。同时，当数据被修改时，PostgreSQL会将修改的数据缓存在Shared Buffers中，然后定期将缓存中的脏数据刷回磁盘。具体的刷脏流程如下：

1. 当数据被修改时，数据会被标记为脏数据。

2. 当Shared Buffers中的脏数据达到一定程度时，PostgreSQL会将脏数据写入磁盘，并将数据标记为干净数据。

3. 如果磁盘上的数据文件已经被写满，则需要将部分干净数据写入磁盘，以腾出空间。

4. 当数据被写入磁盘后，PostgreSQL会将数据从Shared Buffers中移除。

5. 当需要读取数据时，PostgreSQL会首先从Shared Buffers中查找数据，如果没有找到，则需要从磁盘上的数据文件中读取。如果数据已经被缓存在OS Cache或Disk Cache中，则可以直接从OS Cache或Disk Cache中读取数据，而不需要从磁盘上的数据文件中读取。

综上所述，PostgreSQL的查询执行过程涉及到多个缓存和刷脏机制，这些机制可以提高查询性能，并保证数据的一致性和持久性。

INSERT

当执行 INSERT 操作时，PostgreSQL 会将新数据插入到对应的表中。这个过程可以被分为以下几个步骤：

1. 检查是否有足够的权限：在执行 INSERT 操作之前，PostgreSQL 会检查当前用户是否有足够的权限来执行该操作。如果用户没有足够的权限，则操作将被拒绝。

2. 检查约束条件：在执行 INSERT 操作之前，PostgreSQL 会检查表的约束条件是否被满足。如果约束条件不被满足，则操作将被拒绝。

3. 分配事务 ID：在执行 INSERT 操作之前，PostgreSQL 会为该操作分配一个唯一的事务 ID。这个事务 ID 在事务提交或回滚时被使用。

4. 写入 WAL 日志：在执行 INSERT 操作之后，PostgreSQL 会将该操作写入 WAL（Write-Ahead Logging）日志。这个日志记录了数据库的所有修改操作，以便在数据库崩溃时可以恢复数据。

5. 刷脏缓存：在执行 INSERT 操作之后，PostgreSQL 会将新数据写入到共享缓存中。如果该数据已经存在于缓存中，则该数据将被更新。在这个过程中，PostgreSQL 会标记缓存中的数据为“脏数据”，表示该数据已经被修改但还没有被写入到磁盘中。

6. 刷脏数据到磁盘：在执行 INSERT 操作之后，PostgreSQL 会将脏数据写入到磁盘中。这个过程被称为“刷脏”。在这个过程中，PostgreSQL 会使用 WAL 日志来确保数据的一致性和可恢复性。

7. 更新索引：在执行 INSERT 操作之后，PostgreSQL 会更新表的索引。这个过程可以被分为两个步骤：首先，PostgreSQL 会将新数据插入到索引中；其次，PostgreSQL 会更新已有数据的索引。

在执行 INSERT 操作时，PostgreSQL 还会使用 redo 和 undo 日志来确保数据的一致性和可恢复性。redo 日志记录了所有修改操作，而 undo 日志记录了回滚操作。当数据库发生崩溃时，PostgreSQL 可以使用 redo 和 undo 日志来恢复数据。

PostgreSQL 数据库中有 Redo 和 Undo 的概念。

Redo 是指在数据库崩溃或者重新启动之后，将已经写入到磁盘上的数据重新加载到内存中，确保数据的一致性。在 PostgreSQL 中，Redo 信息被存储在 WAL 日志文件中。WAL 日志文件记录了数据库中所有的变更操作，可以被用来恢复数据库。

Undo 是指在事务回滚时，将已经修改的数据恢复到之前的状态。在 PostgreSQL 中，Undo 信息被存储在 Undo 日志文件中。Undo 日志文件记录了事务执行前的数据状态，可以被用来回滚事务。

需要注意的是，PostgreSQL 中的 Undo 日志文件是在实验阶段的功能，目前还不太成熟。如果需要使用 Undo 功能，需要手动开启。
在 PostgreSQL 中启用 Undo 功能需要进行以下步骤：

1. 确认 PostgreSQL 的版本是否支持 Undo 功能，目前仅支持 PostgreSQL 14 或更高版本。

2. 在 PostgreSQL 配置文件 postgresql.conf 中增加以下配置：

wal_level = logical
max_replication_slots = 1
max_wal_senders = 1
wal_log_hints = on

其中，wal_level 配置项需要设置为 logical，表示开启逻辑复制功能，max_replication_slots 和 max_wal_senders 配置项需要设置为 1，表示开启一个逻辑复制槽和一个 WAL 发送者，wal_log_hints 配置项需要设置为 on，表示开启 WAL 日志提示功能。

3. 创建 Undo 日志文件存储目录和表空间。

CREATE TABLESPACE undo LOCATION '/path/to/undo';
CREATE DIRECTORY '/path/to/undo';

其中，/path/to/undo 是你指定的 Undo 日志文件存储目录。

4. 在 PostgreSQL 中创建 Undo 表空间。

CREATE UNDO TABLESPACE undo_tablespace LOCATION '/path/to/undo';

其中，/path/to/undo 是你指定的 Undo 日志文件存储目录。

5. 在需要使用 Undo 功能的数据库中创建 Undo 表。

CREATE UNDO TABLESPACE undo_tablespace;

6. 开启事务，进行数据修改操作，然后回滚事务，查看 Undo 表中的数据是否被恢复到修改前的状态。

需要注意的是，Undo 日志文件可能会占用大量的磁盘空间，因此需要根据实际情况进行配置和管理。

在 PostgreSQL 14 之前，没有官方的 Undo 功能。 但是，可以通过以下方法实现类似 Undo 的功能：

1. 在需要进行修改操作的表中添加一个历史表，用于记录每次修改前的数据。

CREATE TABLE my_table_history AS SELECT * FROM my_table WHERE 1 = 0;

2. 在修改前，将原表中的数据插入到历史表中。

INSERT INTO my_table_history SELECT * FROM my_table;

3. 进行数据修改操作。

UPDATE my_table SET column1 = 'new_value' WHERE id = 1;

4. 如果需要回滚操作，可以将历史表中的数据重新插入到原表中。

INSERT INTO my_table SELECT * FROM my_table_history WHERE id = 1;

需要注意的是，这种方法需要手动编写 SQL 语句进行回滚操作，比较麻烦。而且，如果有大量的修改操作，历史表可能会占用大量的磁盘空间，需要进行定期清理。因此，建议在 PostgreSQL 14 或更高版本中使用官方的 Undo 功能。