POSTGRESQL: DELETE对比TRUNCATE

数据不仅涉及存储和积累，有时还涉及删除、清理和归档。在SQL中，清空表的方法不止一种。有两种基本方法可用：

•DELETE

•TRUNCATE

DELETE vs. TRUNCATE

这两个命令的作用完全不同，有时人们并不完全理解。

关键的区别在于DELETE基本上是一个行级别的操作。DELETE语句会将与WHERE子句匹配的每一行标记为已删除。在数十亿行的情况下，这需要相对较长的时间。

TRUNCATE与之不同：它基本上是一个表操作。它不会单独触摸每一行，而是简单地清空整个表并启动一个新的数据文件。

DELETE和TRUNCATE的工作原理

让我们创建一些示例数据并查看：

blog=# CREATE TABLE t_sample AS
SELECT * FROM generate_series(1, 1000000) AS id;
SELECT 1000000

您刚刚创建了100万行，这些行可以用来检查流程。

性能: TRUNCATE vs. DELETE

第一个示例显示了一个简单的DELETE，它将删除所有行并终止事务（ROLLBACK）： 

blog=# BEGIN;
BEGIN

blog=*# \timing
Timing is on.

blog=*# DELETE FROM t_sample;
DELETE 1000000
Time: 709.771 ms

blog=*# ROLLBACK;
ROLLBACK
Time: 0.836 ms

PostgreSQL执行此操作大约需要0.7秒。在下一个列表中，您可以看到如果使用TRUNCATE会发生什么：  

blog=# TRUNCATE t_sample;
TRUNCATE TABLE
Time: 2.481 ms

TRUNCATE比DELETE快得多。您需要记住，TRUNCATE只能在您想要清理整个表（或分区）时使用，而DELETE则是为了更有选择性地删除行。因此，结论是，如果您想删除所有行，TRUNCATE是不可战胜的。在这种情况下，请避免使用DELETE。  

TRUNCATE: 底层实现原理

值得注意的是，在PostgreSQL中，TRUNCATE是完全事务性的。这意味着TRUNCATE可以像任何其他命令一样回滚。因此，人们经常会问：它是如何工作的——这怎么可能工作？让我们来看看： 

blog=# SELECT oid, relfilenode, relname
FROM pg_class
WHERE relname = 't_sample';
oid | relfilenode | relname
--------+-------------+----------
309268 | 309271 | t_sample
(1 row)

在PostgreSQL中，表存储在一组由“relfilenode”标识的文件中。发生的情况是，PostgreSQL将在TRUNCATE期间锁定表，并创建一个新的relfilenode文件：

blog=# BEGIN;
BEGIN

blog=*# TRUNCATE t_sample;
TRUNCATE TABLE

blog=*# SELECT oid, relfilenode, relname
FROM pg_class
WHERE relname = 't_sample';
oid | relfilenode | relname
--------+-------------+----------
309268 | 309272 | t_sample
(1 row)

blog=*# COMMIT;
COMMIT

正如你所看到的，一个新文件已经被创建。美妙之处在于：如果发生ROLLBACK操作，新文件可以被丢弃，恢复到初始状态。然而，如果事务成功提交（COMMIT），新文件将被使用。

blog=# SELECT oid, relfilenode, relname
FROM pg_class
WHERE relname = 't_sample';
oid | relfilenode | relname
--------+-------------+----------
309268 | 309272 | t_sample
(1 row)

在这些操作过程中，对象ID（OID，即object ID）将保持不变。