Re: postgres_fdw insert extremely slow

On Thu, Nov 26, 2020 at 12:37 AM Mats Julian Olsen wrote: > > We have just set up postgres_fdw between two postgres databases, x and y, > with the plan to periodically insert data from x into y. > > We've successfully set up the connection with a few options: > `use_remote_estimate 'true'` and `f

Re: postgres_fdw insert extremely slow

On Fri, Nov 27, 2020 at 2:00 PM pabloa98 wrote: > I would like to suggest for postgres_fdw: If the foreign database is > PostgreSQL, > Just to be clear, the "postgres" part of the name means the remote database must be a PostgreSQL database, there is no "if". Likewise, for the extension mysql_f

Re: postgres_fdw insert extremely slow

I would like to suggest for postgres_fdw: If the foreign database is PostgreSQL, the link should just pass through all the CRUD SQL commands to the other database. If the other database is of a version so different that cannot make sense of the CRUD SQL command, it will generate an error and that'

Re: postgres_fdw insert extremely slow

On 27.11.2020 10:11, pabloa98 wrote: On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe > wrote: On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote: > So even if Mats where to break this query: > > INSERT INTO foreign.labels (address, labels)

Re: postgres_fdw insert extremely slow

On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe wrote: > On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote: > > So even if Mats where to break this query: > > > > INSERT INTO foreign.labels (address, labels) > > SELECT address_id, ARRAY_AGG(name) AS labels > > FROM labels > > GROUP BY 1 > > LIM

Re: postgres_fdw insert extremely slow

On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote: > So even if Mats where to break this query: > > INSERT INTO foreign.labels (address, labels) > SELECT address_id, ARRAY_AGG(name) AS labels > FROM labels > GROUP BY 1 > LIMIT 100; > > down into something like this: > > INSERT INTO foreign.

Re: postgres_fdw insert extremely slow

On 11/25/20 11:18 AM, Tom Lane wrote: Mats Julian Olsen writes: I've got some more numbers here: ... To me this does indicate some sort of networking issue, but I'm wondering if INSERTs are treated differently than SELECTs in postgres_fdw? The only feasibly explanation I have is that postgres_f

Re: postgres_fdw insert extremely slow

Mats Julian Olsen writes: > I've got some more numbers here: > ... > To me this does indicate some sort of networking issue, but I'm > wondering if INSERTs are treated differently than SELECTs in > postgres_fdw? The only feasibly explanation I have is that postgres_fdw > does many more network

Re: postgres_fdw insert extremely slow

On 25.11.2020 18:12, Tom Lane wrote: Mats Julian Olsen writes: On 25.11.2020 17:58, Tom Lane wrote: ... Have you tried looking into pg_locks on the remote server while this query is running? Thanks Tom, I'll try to spin up a regular Postgres instance on both rds and ec2 and see if that help

Re: postgres_fdw insert extremely slow

Mats Julian Olsen writes: > On 25.11.2020 17:58, Tom Lane wrote: >> ... Have you tried looking into pg_locks on the >> remote server while this query is running? > Thanks Tom, I'll try to spin up a regular Postgres instance on both rds > and ec2 and see if that helps. As for the locks, I can not

Re: postgres_fdw insert extremely slow

On 25.11.2020 17:58, Tom Lane wrote: Mats Julian Olsen writes: Postgres version(s)? x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit (RDS) y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1

Re: postgres_fdw insert extremely slow

On 25.11.2020 17:57, Adrian Klaver wrote: On 11/25/20 8:48 AM, Mats Julian Olsen wrote: Apologies for the sloppiness! Postgres version(s)? x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit (RDS) y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64

Re: postgres_fdw insert extremely slow

Mats Julian Olsen writes: >> Postgres version(s)? > x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) > 4.9.3, 64-bit (RDS) > y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, > compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit (GCP) Hmm,

Re: postgres_fdw insert extremely slow

On 11/25/20 8:48 AM, Mats Julian Olsen wrote: Apologies for the sloppiness! Postgres version(s)? x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit (RDS) y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-

Re: postgres_fdw insert extremely slow

Apologies for the sloppiness! Postgres version(s)? x: Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit (RDS) y: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg19.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.2.1-9ubuntu2) 9.2.1 20191008, 64-bit (GCP) With

Re: postgres_fdw insert extremely slow

On 11/25/20 8:37 AM, Mats Julian Olsen wrote: Helle pgsql-general, We have just set up postgres_fdw between two postgres databases, x and y, with the plan to periodically insert data from x into y. We've successfully set up the connection with a few options: `use_remote_estimate 'true'` and

postgres_fdw insert extremely slow

Helle pgsql-general, We have just set up postgres_fdw between two postgres databases, x and y, with the plan to periodically insert data from x into y. We've successfully set up the connection with a few options: `use_remote_estimate 'true'` and `fetch_size '5'` (the latter we've played aroun