On Wed, Sep 11, 2019 at 12:57 PM Rick Otten <rottenwindfish@gmail.com> wrote:
>
> On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <dinesh@opsveda.com> wrote:
>>
>> I think Merlin has outlined pretty much all the options and very neatly. (As an asides Merlin could you possibly
elaborateon the "C Hack" how that might be accomplished.)
>>
>> To OP, I am curious if the performance changes were the query rewritten such that all timestamp columns were listed
firstin the selection. I understand it might not be feasible to make this change in your real application without
breakingthe contract.
>>
>> Regards
>> Dinesh
>
>
> It looks like AWS has a pgbouncer query re-writer service that might be a starting point:
>
https://aws.amazon.com/blogs/big-data/query-routing-and-rewrite-introducing-pgbouncer-rr-for-amazon-redshift-and-postgresql/
>
> I've never used it.
Yeah, I haven't either. Side note: this system also provides the
ability to load balance queries across distributed system; that's a
huge benefit. Say you have master server and five replica, it seems
that you can round robin the read only queries using this system or
other neat little tricks. I would be cautious about pgbouncer-rr
becoming the bottleneck itself for certain workloads though.
Anyways, a 'hack' strategy on linux might be to:
*) Check and verify that libpq is dynamically linked (which is almost
alwasys the case). ldd /your/application should give the dynamic
library dependency to libpq.
*) Grab postgres sources for same version as production
*) configure
*) switch to interfaces/libpq
*) figure out which interface routine(s) being called into. The
approach will be slightly different if the query is
prepared/paramterized or not. Assuming it isn't, you'd have to modify
the PQsendQuery routine to check for the signature (say, with
strcmp), create a new string, and have that be put instead of the
incoming const char* query. The parameterized versions
(PQsendQueryParams) would be easier since you'd be able to use a
static string rather than parsing it out.
*) Build the library, do some testing with hand written C program
*) inject the modified libpq with LD_LIBRARY_PATH
It must be stated that some people might read this and be compelled to
barf :-) -- it's pretty gross. Having said that, sometimes you have to
find a solution. I would definitely try the pgbouncer-rr approach
first however; this has a *lot* of potential benefit.
merlin
