Re: Postgresql Replication Performance

On Thu, Dec 29, 2011 at 3:33 AM, sgupta <saurabh(dot)b85(at)gmail(dot)com> wrote:
> I am doing POC on Posgtresql replication. I am using latest version of
> postgresql i.e. 9.1. There are multiple replication solutions avaliable in
> the market (PGCluster, Pgpool-II, Slony-I). Postgresql also provide in-built
> replication solutions (Streaming replication, Warm Standby and hot standby).
> I am confused which solution is best for the financial application for which
> I am doing POC. The application will write around 160 million records with
> row size of 2.5 KB in database. My questions is for following scenarios
> which replication solution will be suitable:
>
> If I would require replication for backup purpose only
> If I would require to scale the reads
> If I would require High Avaliability and Consistency
> Also It will be very helpful if you can share the perfomance or experience
> with postgresql replication solutions.

The built in HS/SR integrates with the postgres engine (over the WAL
system) at a very low level and is going to be generally faster and
more robust. More importantly, it has a very low administrative
overhead -- the underlying mechanism of log shipping has been tweaked
and refined continually since PITR was released in 8.0. Once you've
done it a few times, it's a five minute procedure to replicate a
database (not counting, heh, the base database copy).

The main disadvantage of HS/SR is inflexibility: you get an exact
replica of a database cluster. Slony (which is a trigger based
system) and pgpool (which is statement replication) can do a lot of
funky things that hs/sr can't do -- so they definitely fill a niche
depending on what your requirements are.

merlin