search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
496 stars 156 forks source link

Slow Transfer Speed #690

Closed KoalaGeo closed 4 months ago

KoalaGeo commented 4 months ago

Hi @laurenz,

Hoped you might be able to offer some advice. I'm running Oracle & Postgres and am trying to populate postgres with ~40 geospatial tables each with 1 - 2M rows of data.

  1. I'm using oracle_fdw to IMPORT FOREIGN SCHEMA "PUBLISHED"
  2. Then CREATE MATERIALIZED VIEW for the tables I'm interested in WITH NO DATA
  3. Then creating a function REFRESH MATERIALIZED VIEW

This is done via flyway & GitLab CI, with the refresh function called at the end (and on schedules) to populate the views.

This refresh job is currently taking nearly 3 hours hours to run. Do you know if there's as way I could speed this up?

Many thanks

laurenz commented 4 months ago

If you have SDO_GEOMETRY columns in the table, I would expect bad performance. Reading such tables requires a round trip between PostgreSQL and Oracle for each table row. This limitation has been lifted for LOB columns in version 2.6.0, but it still applies to geometry data.

The only way to improve the performance is to reduce the network latency between Oracle and PostgreSQL.

KoalaGeo commented 4 months ago

Thank you for that clarification 👍

I'll have a go making the same tables without SDO_GEOMETRY but including a lat/long, then making the geometry column in PostGres.

laurenz commented 4 months ago

Right; a view might be a good workaround.