Обсуждение: PostgreSQL high availability solutions for high rates.
Hi to the PostgreSql Admin community,
we are evaluating PostgreSQL for our productions, for a system with 1000 updates per second,1000 inserts per sec, ~1000 deletes per second1,
250,000 transactions per day, writing ~200+GB day, 200+gb wall per day, and deleting ~80-100GB per day.
we are looking for “active-active (r/w) (r/w)” , “active(r/w) -active(read) “
and “active(r/w) stand-by” high availability solution,
what High availability solutions ( PG internal or 3rd party) are used successfully, keeping up with the similar rates? double of those rates?
do we need 3rd party software solution or can use postgreSQL replication solution alone? ( or combinations?)
do we have benchmark papers comparing alternatives?
thanks in advance, Aliza.
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service
The HA & SPOF dependency can be removed completely with help of PostgreSQL & Pgpool-II design and replace no use of any licensed software components.
We have implemented a very complex HA architecture at one of our customer using Open Source tools (PostgreSQL/PgpoolII).
Hi to the PostgreSql Admin community,
we are evaluating PostgreSQL for our productions, for a system with 1000 updates per second,1000 inserts per sec, ~1000 deletes per second1,
250,000 transactions per day, writing ~200+GB day, 200+gb wall per day, and deleting ~80-100GB per day.
we are looking for “active-active (r/w) (r/w)” , “active(r/w) -active(read) “
and “active(r/w) stand-by” high availability solution,
what High availability solutions ( PG internal or 3rd party) are used successfully, keeping up with the similar rates? double of those rates?
do we need 3rd party software solution or can use postgreSQL replication solution alone? ( or combinations?)
do we have benchmark papers comparing alternatives?
thanks in advance, Aliza.
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service
> we are evaluating PostgreSQL for our productions, > for a system with 1000 updates per second, > 1000 inserts per sec, > ~1000 deletes per second1 That adds up to 3000 transactions per second, but this: > 250,000 transactions per day only results in about 3 transactions per second. But even 3000 transactions per second can be sustained with good hardware (especially fast SSDs) without problems as long as there are at least some "quiet" times where autovacuum can catch up with the updates and deletes. > we are looking for “active-active (r/w) (r/w)” , “active(r/w) -active(read) “ > and “active(r/w) stand-by” high availability solution, Postgres only supports read-only replicas where you can distribute the load of SELECT queries. It does not offer a solution to distribute write load over multiple nodes. > what High availability solutions ( PG internal or 3rd party) are used successfully, keeping up with the similar rates?double of those rates? A master/slave system (including multiples slaves) can be set up without the need of 3rd party tools. If you want to distribute read requests across slaves you will need a 3rd part tool, e.g. pgPool or pgBouncer. To sustain a high write throughput on a single node you need a fast I/O system. Typically this is done with several really fast SSDs or NVMe disks nowadays. The more CPUs you have, the better.
> > we are evaluating PostgreSQL for our productions,
> > for a system with 1000 updates per second,
> > 1000 inserts per sec,
> > ~1000 deletes per second1
> That adds up to 3000 transactions per second, but this:
>
> > 250,000 transactions per day
>
> only results in about 3 transactions per second.
>
> But even 3000 transactions per second can be sustained with good hardware (especially fast SSDs) without problems
> as long as there are at least some "quiet" times where autovacuum can catch up with the updates and deletes.
>
> > we are looking for “active-active (r/w) (r/w)” , “active(r/w) -active(read) “
> > and “active(r/w) stand-by” high availability solution,
>
> Postgres only supports read-only replicas where you can distribute the load of SELECT queries.
> It does not offer a solution to distribute write load over multiple nodes.
i can recommend BDR extension which comes at a prices if you need a proper Master-Master cluster setup. But you could also setup a couple of standalone postgresql
instances which replicate the written data via logical replication to a central database. this way you can scale IO load and have many master nodes for redundancy.
if this approach works for you depends on your use cases.
> > what High availability solutions ( PG internal or 3rd party) are used successfully, keeping up with the similar rates? double of those rates?
>
> A master/slave system (including multiples slaves) can be set up without the need of 3rd party tools.
>
> If you want to distribute read requests across slaves you will need a 3rd part tool, e.g. pgPool or pgBouncer.
>
> To sustain a high write throughput on a single node you need a fast I/O system.
> Typically this is done with several really fast SSDs or NVMe disks nowadays.
>
> The more CPUs you have, the better.
Cheers, stephan