Transaction-controlled robustness for replication - Mailing list pgsql-hackers

One of the cool features of 8.3 was the ability to control at the
transaction level whether we would use synchronous or asynchronous
commit.

We're planning to add integrated replication features to 8.4, and I
think it will be possible to extend the concept of asynchronous commit
to a more general form of transaction-level robustness. Note that the
proof that its possible to mix asynchronous and synchronous transactions
on the same system has already been established, so this is just a
straight extension of that concept.

Asynchronous commit controls whether we go to disk at time of commit, or
whether we defer this slightly. We have the same options with
replication: do we replicate at time of commit, or do we defer this
slightly for performance reasons. DRBD and other replication systems
show us that there is actually another difference when talking about
synchronous replication: do we go to disk on the standby before
acknowledging the primary?

We can generalise this as three closed questions, answered either Yes
(Synchronous) or No (Asynchronous)

* Does WAL get forced to disk on primary at commit time?
* Does WAL get forced across link to standby at commit time?
* Does WAL get forced to disk on standby at commit time?

In code, these are simple if tests: Do we wait, or not?

We could represent this with 3 parameters:
synchronous_commit = on | off
synchronous_standby_transfer = on | off
synchronous_standby_wal_fsync = on | off

If we are able to define these robustness characteristics for each
transaction *separately* then it will represent an industry first: no
other database has that capability implemented or planned on published
roadmaps, nor has it been discussed in research to my knowledge.

Changing the parameter setting at transaction-level would be expensive
if we had to set three parameters.

Or we could use just two parameters:
synchronous_commit = on | off
synchronous_replication = 'AA', 'SA' or 'SS' with A = Asynchronous, S = Synchronous  which corresponds with DRBD's
algorithmslike thisDRBD A = AADRBD B = SADRBD C = SS
 

Or we could use just a single parameter
synchronous_commit = 'AAA', 'SAA', 'SSA', 'SSS' or on |off when no
log-based replication is defined

Having the ability to set these at the transaction-level would be very
cool. Having it set via a *single* parameter would make it much more
viable to switch between AAA for bulk, low importance data and SSS for
very low volume, critical data, or somewhere in between on the same
server, at the same time.

So proposal in summary is
* allow various modes of synchronous replication for perf/robustness
* allow modes to be specified per-transaction
* allow modes to be specified as a single parameter

I think Itagaki may have described similar concepts at PGCon2008, but
this thread has been started to make sure that meme definitely has been
released into the wild, and to discuss how we might specify it?

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support