Re: History of WAL_LEVEL (archive vs hot_standby)

On Thu, Mar 27, 2014 at 03:06:02PM -0700, David Johnston wrote:
> shamccoy wrote
> > Hello. I've been doing some benchmarks on performance / size differences
> > between actions when wal_level is set to either archive or hot_standby.
> > I'm not seeing a ton of difference. I've read some posts about
> > discussions as to whether this parameter should be simplified and remove
> > or merge these 2 values.
> >
> > I'd like to understand the historic reason between have the extra
> > "hot_standby" value. Was it to introduce replication and not disturb the
> > already working "archive" value?

I think so.

> > If I'm new to Postgres, is there any
> > strategic reason to use "archive" at this point if replication is
> > something I'll be using in the future? I'm not seeing any downside to
> > "hot_standby" unless I'm missing something fundamental.

Probably not. You might manage to construct a benchmark in which the extra
master-side work is measurable, but it wouldn't be easy.

> There is a semantic difference in that "archive" is limited to "wal
> shipping" to a dead-drop area for future PITR. "hot_standby" implies that
> the wal is being sent to another running system that is immediately reading
> in the information to maintain an exact live copy of the master.
>
> As I think both can be used for PITR I don't believe there is much downside,
> technically or with resources, to using hot_standby instead of archive; but
> I do not imagine it having any practical benefit either.

wal_level=archive vs. hot_standby is orthogonal to the mechanism for
transmitting WAL and time of applying WAL. Rather, it dictates whether a
PostgreSQL cluster replaying that WAL can accept read-only connections during
recovery. You can send wal_level=archive log data over streaming replication,
even synchronous streaming replication. However, any standby will be unable
to accept connections until failover ends recovery. On the flip side, if you
use wal_level=hot_standby, a backup undergoing PITR can accept read-only
connections while applying years-old WAL from an archive. That is useful for
verifying, before you end recovery, that you have replayed far enough.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com