英文:
How to delete some million rows in PostgreSQL?
问题
在我的系统中，一个表中积累了大量的数据。现在我不知道如何删除它们。后来我意识到我需要使用分区，但是我无法回到过去。
今天我有1000万行数据，需要删除800万行。
对于创建的表，已经创建了索引。主键是一个带有uuidv4的列。
我编写了一个 PostgreSQL 存储过程来循环删除行。每次循环迭代，我删除1000行并提交。但是这个存储过程会使数据库的负载增加几倍于正常负载。最终我在1小时内删除了2000行。
然后我尝试使用一个简单的 SQL 脚本来删除100行，使用了 begin-commit，但是我得到了很高的负载。在15分钟内，我只删除了那些微不足道的100行。
有什么想法或者对这个问题有经验的吗？
英文:
In my system, a lot of data has accumulated in a table. Now I don't understand how to delete them. Then I realized that I need to use partitions, but I can't go back in time.
Today i have 10 million rows, need to delete 8 million rows.
For table created indexes. PK is column with uuidv4.
I wrote PostgreSQL Procudere to delete rows in loop. Every loop iteration i delete 1000 rows and set commit. But this procedure increases the load on the database by several times the normal load. In the end I deleted 2000 rows in 1 hour.
I then try to delete 100 rows using a simple SQL script with begin-commit, but I get a high load.
In 15 minutes I deleted those measly 100 rows.
Any ideas or exprience with this problem?)
答案1
得分: 1
一种选择是删除应用于表的所有约束，然后尝试删除数据。删除后，您可以恢复所有已删除的约束。
另一种选择是创建一个与原表具有相同结构（不包括约束）的临时表，将要保留的数据复制到该表中，删除旧表，将新表重命名为旧表的名称，并恢复约束。
英文:
One option could be to delete all constraints applied to the table, and then try to delete the data. After deletion, you can return all all constraints that have been deleted.
Another option is to create a temporary table with the same structure as the table has (do not include constraints), copy the data you want to keep into this table, delete the old one, rename the new one with the name of the old one, and restore constraints.
答案2
得分: 0
应该很快。很难相信删除2k条记录需要一个小时。
以下是一些可能的情况：
- 删除触发了自动清理(auto-vacuum)并且自动清理过程需要资源。
- 您的应用程序正在使用数据并获取锁，这会延迟删除操作。
我建议创建一个带有分区的新表，并将所需的数据复制到新表中，然后运行以下命令来修改表格。
ALTER TABLE example_table RENAME TO example_table_old;
ALTER TABLE new_table RENAME TO example_table;
在成功迁移后删除 example_table_old。
英文:
It should be quick. Hard to believe that it's taking an hour to delete 2k records.
Here are some possible cases:
- Delete triggers auto-vacuum and auto-vacuum process is resource incentive.
- Your application using data and acquiring locks which adds delay in deletion.
I would suggest create new table with partition and copy the require data to new table and run following command to alter the table.
ALTER TABLE example_table RENAME TO example_table_old;
ALTER TABLE new_table RENAME TO example_table;
Drop the example_table_old after successful migration.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论