Hey Jeff,
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.
Vijay
On Mon, Feb 18, 2019 at 12:39 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Jeff Janes <jeff.janes@xxxxxxxxx> 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
