Re: The plan for FDW-based sharding

On 27 February 2016 at 22:38, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:

> That could be part of a solution. What I sketched out with the
> "apparent order of execution" ordering of the transactions
> (basically, commit order except when one SERIALIZABLE transaction
> needs to be dragged in front of another due to a read-write
> dependency) is possibly the simplest approach, but batching may
> well give better performance.
>
> > Collecting a list of transactions that must be applied before the current
> > one could be accumulated during SSI processing and added to the commit
> > record. But reordering the transaction apply is something we'd need to
> get
> > some real clear theory on before we considered it.
>
> Oh, there is a lot of very clear theory on it. I even considered
> whether it might work at the physical level, but that seems fraught
> with potential land-mines due to the subtle ways in which we manage
> race conditions at the detail level. It's one of those things that
> seems theoretically possible, but probably a really bad idea in
> practice. For logical replication, though, there is a clear way to
> determine a reasonable order of applying changes that will never
> yield a serialization anomaly -- if we do that, we dodge the choice
> between using a "stale" safe snapshot or waiting an indeterminate
> length of time for a "fresh" safe snapshot -- at the cost of
> delaying logical replication itself at various points.
>

I think we're going to have practical difficulties with these concepts.

If an xid commits with inConflicts, those refer to transactions that may
not yet have assigned xids. They may be assigned xids for hours or days
even so its hard to know whether they will eventually become write
transactions or not, making it a challenge to even know whether we should
delay. And if even if we did know, delaying apply of commits for hours to
allow us to reorder transactions isn't practical in all cases, clearly,
more so if the impact is caused by one minor table that nobody much cares
about.

What I see as more practical is reducing the scope of "safe transactions"
down to "safe scopes", where particular tables or sets of tables are known
safe at particular times, so we know more about which things we can look at
safely.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services