Postgresql fdw tracing

From: Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Postgresql fdw tracing
Date: 2021-05-12 20:44:22
Message-ID: CAM+6J967g+OvLFpdDbKi9Cc8f=1d=by=ys7Dq7+MjMTGuSGUaw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.
https://image.slidesharecdn.com/fdw-basedsharding-170321103514/95/fdwbased-sharding-update-and-future-12-638.jpg?cb=1490092596

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 :)

Browse pgsql-general by date

  From Date Subject
Next Message Swathi P 2021-05-13 10:46:19 Query on postgres_fdw extension
Previous Message Tom Lane 2021-05-12 13:39:16 Re: ERROR: no known snapshots