[TOC] Postgres SELECT INSERT 流程 ？ news/2023/11/17 15:01:50 SELECT 当执行SELECT查询时，PostgreSQL数据库会按照以下流程进行处理：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

更新索引：在执行 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 功能需要进行以下步骤：

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

在 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 日志提示功能。

创建 Undo 日志文件存储目录和表空间。 CREATE TABLESPACE undo LOCATION ‘/path/to/undo’; CREATE DIRECTORY ‘/path/to/undo’; 其中，/path/to/undo 是你指定的 Undo 日志文件存储目录。

在 PostgreSQL 中创建 Undo 表空间。 CREATE UNDO TABLESPACE undo_tablespace LOCATION ‘/path/to/undo’; 其中，/path/to/undo 是你指定的 Undo 日志文件存储目录。

在需要使用 Undo 功能的数据库中创建 Undo 表。 CREATE UNDO TABLESPACE undo_tablespace; 开启事务，进行数据修改操作，然后回滚事务，查看 Undo 表中的数据是否被恢复到修改前的状态。 需要注意的是，Undo 日志文件可能会占用大量的磁盘空间，因此需要根据实际情况进行配置和管理。

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

在需要进行修改操作的表中添加一个历史表，用于记录每次修改前的数据。 CREATE TABLE my_table_history AS SELECT FROM my_table WHERE 1 = 0; 在修改前，将原表中的数据插入到历史表中。 INSERT INTO my_table_history SELECT FROM my_table; 进行数据修改操作。 UPDATE my_table SET column1 = ‘new_value’ WHERE id = 1; 如果需要回滚操作，可以将历史表中的数据重新插入到原表中。 INSERT INTO my_table SELECT * FROM my_table_history WHERE id = 1; 需要注意的是，这种方法需要手动编写 SQL 语句进行回滚操作，比较麻烦。而且，如果有大量的修改操作，历史表可能会占用大量的磁盘空间，需要进行定期清理。因此，建议在 PostgreSQL 14 或更高版本中使用官方的 Undo 功能。