Markhof, Ingolf schrieb am 29.01.2021 um 13:56:
> The set-up basically is a production database and a reporting
> database. As names indicate, the production database is used for
> production, the reporting database is for analysis. On the reporting
> database, the only way to access product data is via foreign tables
> that link to the related production tables.>
>
> However, the same set-up worked fine in Oracle before. Reporting
> wasn't always fast, but it delivered results in acceptable time. A
> query executed on the Oracle reporting server returns data in e.g. 30
> seconds. But running the query translated to PostgreSQL on the
> PostgreSQL DB does not deliver a single row after hours (!) of run
> time.
>
> So, I wonder: Is there a fundamental difference between Oracle
> database links and foreign tables in PostgreSQL that could explain
> the different run times?
My guess is, that your queries use predicates that can't be pushed down
to the foreign server on Postgres, but Oracle can.
What is your Postgres version?
If my assumption is correct, then maybe if you showed one example query,
it might be possible to figure out a way to restructure it.
Is logical replication an option?
How accurate does the data on the reporting server need to be?
Would using materialized views that "cache" the foreign table be an
option? That's obviously only suitable if you can live with some
stale data and are fine with refreshing them maybe twice a day
(depending on how fast the refresh is)