我正在使用 PostgreSQL 数据库进行实时项目。其中,我有一个8列的表格。 该表包含数百万行,因此,为了使从表的搜索更快,我想删除该表中的旧条目并将其存储到新的另一个表中。
I am using PostgreSQL database for live project. In which, I have one table with 8 columns. This table contains millions of rows, so to make search faster from table, I want to delete and store old entries from this table to new another table.
为此,我知道一种方法:
To do so, I know one approach:
- 首先选择一些行
- 创建新表
- 将此行存储在该表中
- 比从主表中删除。
- first select some rows
- create new table
- store this rows in that table
- than delete from main table.
但是它花费了太多时间,而且效率不高。
But it takes too much time and it is not efficient.
所以我想知道在postgresql数据库中执行此操作的最佳方法是什么?
So I want to know what is the best possible approach to perform this in postgresql database?
Postgresql版本: 9.4.2。 大约行数:8000000 我要移动行:2000000
Postgresql version: 9.4.2. Approx number of rows: 8000000 I want to move rows: 2000000
推荐答案您可以使用CTE(公用表表达式)在单个SQL语句中移动行(文档中的更多内容):
You can use CTE (common table expressions) to move rows in a single SQL statement (more in the documentation):
with delta as ( delete from one_table where ... returning * ) insert into another_table select * from delta;但是请仔细考虑是否确实需要它。就像评论中所说的a_horse_with_no_name一样,调整查询可能就足够了。
But think carefully whether you actually need it. Like a_horse_with_no_name said in the comment, tuning your queries might be enough.
更多推荐
使用postgres将大量行从一个表移动到另一个新表的有效方法
发布评论