Re: [External] Re: FDW, too long to run explain

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeff Janes <jeff(dot)janes(at)gmail(dot)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
>