Re: alter table xxx set unlogged take long time

On 7/26/22 08:59, James Pang (chaolpan) wrote:
>     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 xxxx; create index on tables….”  
> With wal_level=minimal, is it ok to make copy and create index  without 
> logging ?

Not sure if it would work for you, but perhaps a usable strategy would 
be to partition the existing large table on something (e.g. a new column 
like batch number?).

Then (completely untested) I *think* you could create the "partition" 
initially as a free standing unlogged table, load it, index it, switch 
to logged, and then attach it to the partitioned table.

Perhaps you could also have a background job that periodically 
aggregates the batch partitions into larger buckets to minimize the 
overall number of partitions.


-- 
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com