Re: Fwd: increase insert into local table from remote oracle table preformance

Hi,
I'll try to answer all your question so that you will have more information
about the situation :

I have one main table that is called main_table_hist. The "main_table _hist"
is partitioned by range (date column) and includes data that is considered
as "history data" . I'm trying to copy the data from the oracle table to my
local postgresql table (about 5T). For every day in the year I have in the
oracle table partition and therefore I will create for every day in year
(365 in total) a partition in postgresql. Every partition of day consist of
4 different partitions by list (text values). So In total my tables
hierarchy should look like that :
main_table_hist
14/08/2018_main
14/08/2018_value1
14/08/2018_value2
14/08/2018_value3
14/08/2018_value1

Moreover, I have another table that is called "present_data" that consist
of 7 partitions (the data of the last 7 days - 300G) that I'm loading from
csv files (daily). Every night I need to deattach the last day partition
and attach it to the history table.

This hierarchy works well in oracle and I'm trying to build it on
postgresql. Right now I'm trying to copy the history data from the remote
database but as I suggested it takes 10 hours for 200G.

Some details :
-Seting the wals to minimum is possible but I cant do that as a daily work
around because that means restarting the database.
I must have wals generated in order to restore the "present_data" in case
of disaster.
-The network
-My network bandwidth is 1GB.
-The column in the table are from types : character varying,big
int,timestamp,numeric. In other words no blobs.
-I have many check constraints on the table.
- Laurenz - "You could try a bigger value for the "prefetch" option."- Do
you have an example how to do it ?
-Inserting directly into the right parittion might increase the preformance
?

Thanks , Mariel.

2018-08-14 0:03 GMT+03:00 legrand legrand <legrand_legrand(at)hotmail(dot)com>:

> Did you try
> - runing multiple inserts in parallel,
> - Stop wal archiving,
> - Tune fetch sise ?
>
> Regards
> PAscal
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>