Efficient way to move large number of rows from one table to another new table using postgres

Question

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.

So I want to know what is the best possible approach to perform this in postgresql database?

Postgresql version: 9.4.2.
Approx number of rows: 8000000
I want to move rows: 2000000

Answer 1

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;

But think carefully whether you actually need it. Like a_horse_with_no_name said in the comment, tuning your queries might be enough.

Answer 2

This is a sample code for copying data between two table of same. Here i used different DB, one is my production DB and other is my testing DB

INSERT INTO "Table2"
select * from dblink('dbname=DB1 dbname=DB2 user=postgres password=root', 
'select "col1","Col2" from "Table1"') 
as t1(a character varying,b character varying);