Hey Jeff, yes, we now relaxed the idle in transaction setting to 15 mins.
i was hesitant to increase the settings as it blocked auto vaccum. We use hot_standby_feedback = true also as we split reads/writes and allow long running queries on read replicas, this too affects auto vaccum. so overall, all the options i set to ensure auto vaccum gets triggered get impacted by increased idle in tx and hot_standby_feedback = true, both of which seem to be necessary for the setup now. we have been trying to work with sharding using (mutli coordinator FDW) on our own (and have been successful although have hiccups), using directory based sharding in pg10. (if we cannot handle growth, all goes to mongo for its automatic sharding and failover) I have to admit we can do better here though. we need to rebalance the data in the shards when we come close to 90% disk. those are long delete/upsert queries. We have very aggressive autovaccum to ensure we do not have a lot of stale stats. I have plans to rearchitect the whole setup with pg11 where we plan to introduce time based sharding and then table partitioning in each shard further by time and also use Materialized views, for day old data with pre aggregated fields on each shard so that explain does not have to work too hard :) and then create foreign tables and attach them as partitions. similar to https://github.com/MasahikoSawada/pgconf-asia-demo/tree/c47e25bf589c7d401c9d342329b400ec26eb61db i guess, i am diverting the query, but just saying :) Thanks for suggestions and help Jeff. Appreciate it. Regards, Vijay On Mon, Feb 18, 2019 at 12:39 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > Jeff Janes <jeff.ja...@gmail.com> writes: > > A question for the PostgreSQL hackers would be, Is it necessary and > > desirable that the EXPLAIN be issued in the same transaction as the > > eventual DECLARE and FETCHes? I don't think it is. > > It seems like a good idea to me. I certainly don't think "I've got > an idle-in-transaction timeout on the remote that's shorter than my > local transaction runtime" is a plausible argument for changing that. > You could trip over that with a slow query regardless of whether we > separated the EXPLAIN step, just because there's no guarantee how > often we'll ask the FDW to fetch some rows. > > > I guess if the foreign > > side table definition got changed between EXPLAIN and DECLARE it would > > cause problems, but changing the foreign side definition out of sync with > > the local side can cause problems anyway, so is that important to > preserve? > > I believe that the EXPLAIN will leave the remote transaction holding > AccessShareLock on the query's tables, meaning that doing it in one > transaction provides some positive protection against such problems, > which we'd lose if we changed this. > > regards, tom lane >
