alter table xxx set unlogged take long time

[James Pang (chaolpan)]

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?

Thanks,

James

Re: alter table xxx set unlogged take long time

[Jim Mlodgenski]

On Tue, Jul 26, 2022 at 4:53 AM James Pang (chaolpan) 
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

RE: alter table xxx set unlogged take long time

[James Pang (chaolpan)]

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 
Sent: Tuesday, July 26, 2022 8:21 PM
To: James Pang (chaolpan) 
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time



On Tue, Jul 26, 2022 at 4:53 AM James Pang (chaolpan) 
mailto:chaol...@cisco.com>> 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

Re: alter table xxx set unlogged take long time

[Tom Lane]

"James Pang (chaolpan)"  writes:
> 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 …

The easy answer is to skip steps 3 and 5.

regards, tom lane

RE: alter table xxx set unlogged take long time

[James Pang (chaolpan)]

Without step 3 ,  copy data take long time.   Use wal_level=minimal can help 
make COPY load data without logging ?

Thanks,

James

-Original Message-
From: Tom Lane  
Sent: Tuesday, July 26, 2022 8:43 PM
To: James Pang (chaolpan) 
Cc: Jim Mlodgenski ; pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time

"James Pang (chaolpan)"  writes:
> 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 …

The easy answer is to skip steps 3 and 5.

regards, tom lane

Re: alter table xxx set unlogged take long time

[Jim Mlodgenski]

On Tue, Jul 26, 2022 at 8:45 AM James Pang (chaolpan) 
wrote:

> Without step 3 ,  copy data take long time.   Use wal_level=minimal can
> help make COPY load data without logging ?
>
>
I assume that you're most concerned with the total time of moving the data
from the source database into the final table so you might get a big win by
not moving the data twice and directly load the table through a Foregin
Data Wrapper and avoid the csv export/import. Something like the oracle_fdw
might help here:
https://github.com/laurenz/oracle_fdw

-Original Message-
> From: Tom Lane 
> Sent: Tuesday, July 26, 2022 8:43 PM
> To: James Pang (chaolpan) 
> Cc: Jim Mlodgenski ;
> pgsql-performance@lists.postgresql.org
> Subject: Re: alter table xxx set unlogged take long time
>
> "James Pang (chaolpan)"  writes:
> > 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 …
>
> The easy answer is to skip steps 3 and 5.
>
> regards, tom lane
>

RE: alter table xxx set unlogged take long time

[James Pang (chaolpan)]

   We use JDBC to export data into csv ,then copy that to Postgres. Multiple 
sessions working on multiple tables.  If not set unlogged , how to make COPY 
run fast ?   possible to start a transaction include all of these  “truncate 
table xxx; copy table ; create index on tables….”  With wal_level=minimal, 
is it ok to make copy and create index  without logging ?

James

From: Jim Mlodgenski 
Sent: Tuesday, July 26, 2022 8:53 PM
To: James Pang (chaolpan) 
Cc: Tom Lane ; pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time



On Tue, Jul 26, 2022 at 8:45 AM James Pang (chaolpan) 
mailto:chaol...@cisco.com>> wrote:
Without step 3 ,  copy data take long time.   Use wal_level=minimal can help 
make COPY load data without logging ?

I assume that you're most concerned with the total time of moving the data from 
the source database into the final table so you might get a big win by not 
moving the data twice and directly load the table through a Foregin Data 
Wrapper and avoid the csv export/import. Something like the oracle_fdw might 
help here:
https://github.com/laurenz/oracle_fdw

-Original Message-
From: Tom Lane mailto:t...@sss.pgh.pa.us>>
Sent: Tuesday, July 26, 2022 8:43 PM
To: James Pang (chaolpan) mailto:chaol...@cisco.com>>
Cc: Jim Mlodgenski mailto:jimm...@gmail.com>>; 
pgsql-performance@lists.postgresql.org
Subject: Re: alter table xxx set unlogged take long time

"James Pang (chaolpan)" mailto:chaol...@cisco.com>> writes:
> 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 …

The easy answer is to skip steps 3 and 5.

regards, tom lane

Re: alter table xxx set unlogged take long time

[David G. Johnston]

On Tue, Jul 26, 2022 at 5:45 AM James Pang (chaolpan) 
wrote:

> Without step 3 ,  copy data take long time.   Use wal_level=minimal can
> help make COPY load data without logging ?
>
>
I believe you are referring to:

https://www.postgresql.org/docs/current/populate.html#POPULATE-COPY-FROM

Since the final state of your table will be "logged" relying on the above
optimization is the correct path, if you enable "logged" at the end, even
with wal_level=minimal, you do not benefit from the optimization and thus
your data ends up being written to WAL.

Otherwise, it is overall time that matters, it's no use boasting the COPY
is fast if you end up spending hours waiting for ALTER TABLE at the end.

David J.