{"id":35706,"date":"2019-10-31T22:05:50","date_gmt":"2019-10-31T19:05:50","guid":{"rendered":"https:\/\/prohoster.info\/blog\/otkazoustojchivyj-klaster-postgresql-patroni-opyt-vnedreniya\/"},"modified":"2026-05-18T20:58:46","modified_gmt":"2026-05-18T18:58:46","slug":"otkazoustojchivyj-klaster-postgresql-patroni-opyt-vnedreniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/otkazoustojchivyj-klaster-postgresql-patroni-opyt-vnedreniya","title":{"rendered":"Fault-tolerant PostgreSQL cluster + Patroni. Implementation experience.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

In this article, I will explain how we approached the issue of PostgreSQL fault tolerance, why it became important to us, and what the outcome was.<\/p>\n

We have a high-traffic service: 2.5 million users worldwide, with over 50K active users every day. Our servers are located in an Amazon region in Ireland: we constantly have more than 100 different servers in operation, of which almost 50 are database servers.<\/p>\n

The entire backend is a large monolithic stateful Java application that maintains a constant websocket connection with the client. When multiple users are working simultaneously on the same board, they all see changes in real time because we log every change in the database. We handle around 10K requests per second to our databases. At peak load in Redis, we write about 80-100K requests per second.
\n\"Fault-tolerant
\n<\/a><\/p>\n

Why we switched from Redis to PostgreSQL<\/h2>\n

Initially, our service operated with Redis, a key-value store that keeps all data in memory. server<\/a>.<\/p>\n

Pros of Redis:<\/p>\n

    \n
  1. High response speed, as everything is stored in memory;<\/li>\n
  2. Convenience of backup and replication.<\/li>\n<\/ol>\n

    Cons of Redis for us:<\/p>\n

      \n
    1. No real transactions. We tried to simulate them at the application level. Unfortunately, this didn't always work well and required writing very complicated code.<\/li>\n
    2. Data volume is limited by the amount of memory. As the amount of data increases, memory will grow, and eventually, we will hit the limitations of the selected instance type, which in AWS requires stopping our service to change instance types.<\/li>\n
    3. We must constantly maintain a low latency level since we have a very high number of requests. The optimal level of latency for us is 17-20 ms. At levels of 30-40 ms, we experience long response times to requests from our application and service degradation. Unfortunately, this occurred in September 2018 when one of the Redis instances inexplicably experienced latency twice as high as usual. To resolve the issue, we halted the service in the middle of the working day for unscheduled maintenance and replaced the problematic Redis instance.<\/li>\n
    4. It's easy to achieve data inconsistency even with minor coding errors and then spend a lot of time writing code to fix that data.<\/li>\n<\/ol>\n

      We took the downsides into account and realized that we needed to move to something more convenient, with normal transactions and less dependence on latency. We conducted research, analyzed numerous options, and chose PostgreSQL.<\/p>\n

      We have been migrating to the new database for 1.5 years and have only transferred a small portion of the data, so we are currently working simultaneously with Redis and PostgreSQL. More about the stages of migration and switching data between databases is detailed in my colleague's article<\/a>.<\/p>\n

      When we first started migrating, our application was interacting directly with the database and making calls to the Redis and PostgreSQL masters. The PostgreSQL cluster consisted of a master and a replica with asynchronous replication. This is what the database interaction scheme looked like:
      \n\"Fault-tolerant<\/p>\n

      Implementing PgBouncer<\/h2>\n

      While we were migrating, the product was also evolving: the number of users and servers working with PostgreSQL increased, and we ran out of connections. PostgreSQL creates a separate process for each connection, consuming resources. The number of connections can be increased only up to a certain point; otherwise, there is a risk of suboptimal database performance. The ideal solution in this situation would be to choose a connection manager that would sit in front of the database.<\/p>\n

      We had two options for the connection manager: Pgpool and PgBouncer. However, the first one does not support transactional mode when working with the database, so we chose PgBouncer.<\/p>\n

      We set up the following interaction scheme: our application connects to one PgBouncer, behind which are the PostgreSQL masters, and each master has one replica with asynchronous replication.
      \n\"Fault-tolerant<\/p>\n

      At the same time, we could not store the entire volume of data in PostgreSQL, and for us, the speed of working with the database was important, so we began sharding PostgreSQL at the application level. The scheme described above is relatively convenient for this: when adding a new shard, it is enough to update the PgBouncer configuration, and the application can immediately work with the new shard.<\/p>\n

      PgBouncer fault tolerance<\/h3>\n

      This scheme worked until the only PgBouncer instance died. We are on AWS, where all instances are running on hardware that sometimes fails. In such cases, the instance simply moves to new hardware and starts working again. This also happened with PgBouncer, but it became unavailable. As a result of this failure, our service was down for 25 minutes. AWS recommends using redundancy on the user side for such situations, which we had not implemented at that time.<\/p>\n

      After that, we seriously considered the fault tolerance of PgBouncer and PostgreSQL clusters because a similar situation could repeat with any instance in our AWS account.<\/p>\n

      We built the fault tolerance scheme for PgBouncer as follows: all application servers connect to a Network Load Balancer, behind which are two PgBouncer instances. Each PgBouncer looks at the same master PostgreSQL of each shard. In the event of a repeat of the AWS instance failure, all traffic is redirected through the other PgBouncer. AWS ensures the fault tolerance of the Network Load Balancer.<\/p>\n

      This scheme allows for easy addition of new PgBouncer servers.
      \n\"Fault-tolerant<\/p>\n

      Creating a fault-tolerant PostgreSQL cluster<\/h2>\n

      In solving this task, we considered different options: custom failover, repmgr, AWS RDS, Patroni.<\/p>\n

      Custom scripts<\/h3>\n

      Can monitor the master\u2019s operation and, in case of its failure, promote the replica to master and update the PgBouncer configuration.<\/p>\n

      The advantages of this approach lie in its maximum simplicity, as you write the scripts yourself and clearly understand how they work.<\/p>\n

      Cons:<\/p>\n