Hello Gentlemen,
I am trying to setup a shard array of pg clusters behind fdw which is fronted by haproxy/envoyproxy to load balance.
Something like this, but including pgbouncer.
Everything works perfectly well, but monitoring is becoming difficult for remote queries.
If there are 100s of queries all querying multiple shards behind, it is very difficult to trace server from where the query originated from the backend shards.
If you have understood till here,
Has anyone worked with passing a unique request id (like in http requests via header ) that lives across the queries via fdw etc so that we can identify and trace the query.
Even if it is via a sidecar.
If we remove the fdw context,
This works on local queries as pg_stat_activity shows parent pid if parallel workers spawned.
If any one has used pgbouncer, even pgbouncer maps the front-end to backend queries to trace the client ip to the backend request.
I hope i am clear, but I can elaborate more if required.
I am trying to simulate a poor man's citus sharding :)
