PostgreSQL high availability solutions for high rates.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL high availability solutions for high rates.

Aliza Abulafia

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

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL high availability solutions for high rates.

Shreeyansh dba
Hi Aliza,

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).

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


On Wed, Mar 20, 2019 at 12:45 PM Aliza Abulafia <[hidden email]> wrote:

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

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL high availability solutions for high rates.

Thomas Kellerer
In reply to this post by Aliza Abulafia
> 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.





Reply | Threaded
Open this post in threaded view
|

AW: PostgreSQL high availability solutions for high rates.

schmidt

 

 

 

> > 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