RE: alter table xxx set unlogged take long time

["James Pang (chaolpan)" <chaolpan@xxxxxxxxx>]

How to make it fast ?  These are our steps about copy large data from Oracle to Postgres

1. Create table in Postgres  2. Extract data from Oracle to CSV  3. Alter table set xxx unlogged,   4. Run copy command into Postgres db  5. Alter table set xxx logged  6. Create index …

Step 5 took long time ,especially for large tables.

 

Thank,

 

James

 

From: Jim Mlodgenski <jimmy76@xxxxxxxxx>
Sent: Tuesday, July 26, 2022 8:21 PM
To: James Pang (chaolpan) <chaolpan@xxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
Subject: Re: alter table xxx set unlogged take long time

 

 

 

On Tue, Jul 26, 2022 at 4:53 AM James Pang (chaolpan) <chaolpan@xxxxxxxxx> wrote:

Hi ,

We have PG v13.6 in RHEL8.4, we try to set table unlogged before load data.  There are a lot of existing data in this table, when ‘alter table xxx  set unlogged’, we found it take long time and spend time on IO datafileread.  Is it expected?

 

Yes, the whole table needs to be written to WAL so this could take a long time for a large table