The (almost) perfect architecture: a PostgreSQL approach for performance and HA

The PostgreSQL engine is proved to be the best choice complex and efficiency oriented architectures. Still, the system architects are often confronted with the performance vs. resilience dilemma, especially when business demands both of them. This presentation intends to give precious insights and useful tips to strengthen the database layer for modern IT architectures, designed for microservices, orchestration and containerization technologies. Although the PostgreSQL configurations trends aim for cloud-native solutions and containerized databases, this topic will expose the proof that PostgreSQL works best with dedicated virtual machines. The current implementation follows the general guidelines for a modern application stack: Web servers, Applications and proxies level (Kubernetes, Docker, HAProxy) and Databases (HA cluster), with the fine touch of PostgreSQL compatible features: configuration tuning, load balancing, automatic failover and a bidirectional replication approach. The idea of implementing a free solution for a Primary-to-Primary configuration seemed impossible for open-source software, but the implementation is quite intuitive, even with no application’s code alteration. Together with the new PostgreSQL 16+ features for logical replication, the PostgreSQL dedicated load balancer PgPool, useful extensions, a little additional SQL development for the DDL schema build and guidelines for maintenance programming, the presented architecture is the baseline for resilient and complex informatic systems, particularly for public institutions in countries like Romania which are making a great fund economy this way. The final purpose of a real-life implementation is to expose the advantages of implementing a low-cost, fault-tolerant and high efficiency architecture, with the contribution of human DevOps and DBA specialists as the most important actors in the nowadays IT infrastructures.