执行此查询时，是否将所有记录都加载到物理内存中？

Question

我有一张表，里面有数百万条记录。该表的总大小仅为6-7 GigaByte。此表是我的应用程序日志表。这个表的增长速度非常快，这是有道理的。现在，我想将记录从日志表移动到备份表中。这是场景，这是我的问题。

Table Log_A
Insert into Log_b select * from Log_A;
Delete from Log_A;

我正在使用postgres数据库。问题是

执行此查询时，是否将来自Log_A的所有记录加载到物理内存中？注意:上面的两个查询都在一个存储过程中运行。如果不是，那么它将如何工作？

我希望这个问题适用于所有的数据库。

我希望有人能在这方面给我一些建议。

Answer 1

在PostgreSQL中，这可能会执行顺序扫描，将一些记录加载到shared_buffers中，插入它们，写出脏缓冲区，然后继续执行。

所有的记录都将通过主内存，但它们不必同时都在内存中。因为它们都是使用正常缓冲读取(pread)从磁盘读取的，所以它将影响操作系统磁盘缓存，从而有可能将其他数据推出缓存。

其他数据库可能会有所不同。有些人可以在处理SELECT之前执行整个INSERT (不过，如果真的有人这样做，我会很惊讶)。有些使用O_DIRECT读取或原始磁盘I/O来避免操作系统缓存的影响，因此缓冲区缓存的影响可能会有所不同。不过，如果有任何数据库依赖于将整个SELECT加载到内存中，我会感到惊讶。

当您想要查看PostgreSQL正在做什么以及如何做时，EXPLAIN和EXPLAIN (BUFFERS, ANALYZE)命令非常有用。参见the manual。

您可能会发现writable common table expressions对此很感兴趣；它允许您在一条语句中完成所有这些工作。在这种简单的情况下，可能没有什么好处，但在更复杂的数据迁移中，它可能是一个很大的胜利。

顺便说一句，请确保运行包装在BEGIN和COMMIT中的那对查询。

Answer 2

可能不会。

每条记录都是单独处理的；这个特定的查询不需要知道任何其他记录就能成功执行。因此，在任何给定时刻，唯一需要存储在内存中的记录就是当前正在处理的记录。

但这实际上取决于数据库是否认为它可以通过加载整个表来更快地完成此操作。检查查询的执行计划。

Answer 3

如果您的设置允许，只需重命名旧的表并创建一个新的空表。显然，速度要快得多，因为根本不需要复制。

ALTER TABLE log_a RENAME TO log_b;
CREATE TABLE log_a (LIKE log_b INCLUDING ALL);

LIKE子句复制旧表(现已重命名)的结构。INCLUDING ALL包括默认值、约束、索引...

依赖于表或其他不太常见的依赖项(而不是plpgsql函数中的查询)的外键约束或视图可能是此路线的障碍。您必须重新创建这些表，以使它们指向新表。但是像您所描述的日志记录表可能没有这样的依赖关系。

这将获得表上的独占锁。我假设，在您的情况下，典型的写访问将仅为INSERT？处理并发访问的一种方法是在不同的模式中创建新表，并为您的应用程序用户更改search_path。然后，应用程序开始写入新表，而不会出现并发问题。当然，您不需要在INSERT语句中对表名进行模式限定才能使其生效。

CREATE SCHEMA log20121018;
CREATE TABLE log20121018.log_a (LIKE log20121011.log_a INCLUDING ALL);
ALTER ROLE myrole SET search_path = app, log20121018, public;

或者在任何对您有效的级别上更改search_path设置:全局、每个数据库、每个角色、每个会话、每个函数……