I'm trying to improve the performance of a postgresql databases that I migrated from oracle. I`m bringing alot of data from oracle tables with oracle_fdw extension into local postgresql tables and after that I'll create indexes on them.

In total, I'm bringing 56 GB of data from 10 tables. When I say "bringing" I mean that I run insert into local_postgresql_table select * from remote_oracle_table. After that I'm building many multiple indexes on the local postgresql tables.

Lets look on specific table ps_rf. The size of the table is 23 GB and it took 1.5 hours only to bring the data and 14 min. for creation of every index (5 indexes for this table).

Another example is a table with a size of 3.3 GB, it took me 10 min. to bring its data.

The parameters that I assigned :

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5 
max_worker_processes = 8 
effective_cache_size = 2500MB
work_mem = 16MB
maintenance_work_mem = 1500MB
shared_buffers = 1500MB
RAM : 5G

I would like to improve both operations - bringing data from oracle and creation on indexes. Please advice how can I continue.

  • If the Oracles tables are not local to your Postgresql server, are you querying over the WAN, or is it all internal network? What are the read speeds of the Oracle database like (is a full table scan usually fast at the Oracle side?) – bma Aug 11 '17 at 17:26
  • Hi, it is an internal network. The read speed of the oracle database is quite well. What do you think about the parameters I mentioned above ? – JeyJ Aug 13 '17 at 6:05
  • THe parameters don't look unreasonable, assuming you have sufficient CPU cores. Have you tried EXPLAIN (ANALYZE,BUFFERS) INSERT ... SELECT * FROM oracle_table to see if there is anything out of the ordinary in the INSERT? Have you measured the read/write speeds of your local Postgres server? Also, if you export a 23GB table to the Oracle server then rsync/scp it to your Postgres server, does it take significantly less than 1.5 hours? Are you watching the output of vmstat and iostat during these operations? It is entirely possible you have maxed out your cores or disk IOPS – bma Aug 13 '17 at 14:10
  • I saw that the creation of the indexes is taking alot of time because it do alot of reads from disk. During the operation I dont use all my ram and my load isnt so high. If I preform scp/rsync it takes less than 1.5 hours.. – JeyJ Aug 13 '17 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.