We have client databases in 3 different region in the world, namely: cr_master, mx_master & mlt_master db. These 3 databases have exactly the same structure, with different subset of client data.
For reporting purpose, we aggregate these 3 db to single collector, using Bucardo replication. Recently, we start experimenting with postgres_fdw, to check for better alternative to Bucardo aggregation. The purpose is to setup a usable fdw-collector, to be used in reporting which aggregates all client data from cr_master, mx_master & mlt_master. Here's our setup: 1) Host machine - running CentOS 2) 4 Openvz instances - running postgres 9.3: a) cr_replica: binary replica for cr_master b) mx_replica: binary replica for mx_master c) mlt_replica: binary replica for mlt_master d) fdw-collector With fdw-collector, remote servers are pointing to those binary replicas, to access the remote tables. However, I run into this performace issue: 1) Query cr_replica directly: select * from transaction.transaction where transaction_time >= '2013-12-01' and transaction_time < '2014-01-01' Total runtime => 10s 2) Query fdw-collector, with remote table to cr_replica: select * from transaction.transaction_cr where transaction_time >= '2013-12-01' and transaction_time < '2014-01-01'; Total runtime => 60s The performance puzzles me. Any idea what makes accessing remote table 6 times slower? In this setup, "remote server" are actually local postgres instances running on same host as fdw-collector. There is no network latency involved. Is this a known performance issue? Or I did some wrong configuration somewhere? Any help is much appreciated, thanks. regards, shuwn yuan
