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
