Re: Temporary tables under hot standby - Mailing list pgsql-hackers

On Wed, Apr 25, 2012 at 10:04 PM, Noah Misch <noah@leadboat.com> wrote:
> Based on the range of assessments spanning your "almost useless" to Merlin's
> "killer feature", I gather that its utility is exceptionally site-specific.

Well said, sir.

It might be worth taking a couple of steps backward, here.  Regardless
of whether one finds the feature as proposed "almost useless" or a
"killer feature", there's no denying that what you're proposing
amounts to three major development projects the net payoff of which
will be the ability to do an extremely limited amount of writing on
the standby server.  Why should we draw the line between GTTs and
LTTs?  What about unlogged tables?  What about, for that matter,
permanent tables?  What about other kinds of SQL objects, such as
functions?  It could be useful to create any of those things on the
standby, and it's going to be extremely difficult if not outright
impossible to make that work, because we're building it on top of a
replication architecture that is oriented around physical replication,
which means that any change that makes the standby anything other than
a byte-for-byte copy of the master is going to be difficult and, in
some sense, a kludge.  I would put this proposal in that category as
well, even though I find it a rather elegant and well-thought-out
kludge.

It is pretty obvious that we could get around all of these problems
easily if we instead did logical replication.  So why not just install
Slony, Bucardo, Londiste, or, if I may presume to toot my employer's
horn just slightly, xDB replication server?  If you use one of those
products, you can not only create temporary tables on your "standby"
servers, but also unlogged tables, permanent tables, and any other
sort of SQL object you might want.  You can also do partial
replication, replication between different major versions of
PostgreSQL, and replication between PostgreSQL and some other
database.  A given node can be the master for some tables and a slave
for other tables, allowing far more deployment flexibility than you
can get with streaming replication; and you have neither replication
conflicts nor the necessity of replicating bloat (and the exclusive
lock that you must take to remove the bloat) between machines.  You
can even do multi-master replication, with the right product choice.
Despite all that, people still love streaming replication, because
it's fast, administratively simple, and very reliable.

So, I can't help wonder if what we're really missing here is a
high-performance, log-based logical replication solution with good
core support.  I'm sure that there will be opposition to that idea
from a variety of people for a variety of reasons, and that is fine.
But I think we need to confront the fact that as long as we stick with
physical replication, any sort of write capability on slaves is going
to be a lot of work and offer only fairly marginal capabilities.  We
can either live with those limitations, or change our approach.
Either way is fine, but I think that hoping the limitations will go
away without a fundamental change in the architecture is just wishful
thinking.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company