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