We scaled PgBouncer to 4x throughput
241 points
• 5 days ago
• Article
Link
PgBouncer 是 PostgreSQL 中广泛使用的连接池工具，但其单线程特性带来了明显的性能瓶颈。单个进程无论硬件多强大都只能使用一个 CPU 核心，因此连接池常常在数据库本身到达容量之前就先成为瓶颈。 ClickHouse Managed Postgres 通过运行一组 PgBouncer 进程，而不是依赖单个实例，来解决这一限制。
为管理这组进程，每个进程都配置为使用 so_reuseport 内核特性绑定到相同端口。操作系统会在多个 PgBouncer 进程之间透明地对传入连接做负载均衡，客户端只需连接到单一端点，完全无需感知底层架构。这样可以有效利用所有可用的 CPU 核心，绕开单核限制。
多进程方案带来的一个主要挑战是处理查询取消请求。取消请求通常会通过与正在执行查询的连接不同的连接到达，内核可能会把取消信号路由到并不管理该会话的进程。为此，ClickHouse 引入了进程之间的对等通信机制：如果取消请求落到错误的进程，会被转发到拥有该会话的进程，从而保证取消操作在整个进程组中可靠生效。
在相同的 16-vCPU 硬件上进行的性能测试展示了这种架构的效果。单个 PgBouncer 进程的吞吐通常在每秒约 87,000 次事务时达到瓶颈，之后由于核心竞争性能会下降。而由 16 个进程组成的部署则能随负载线性扩展，吞吐提升到约每秒 336,000 次事务。单进程配置会让机器的大部分资源闲置，而多进程部署则更高效地利用了可用硬件。
总之，多进程方案将连接池从瓶颈变回简单的"管道"。通过在进程组中分摊连接上限，系统既能保持安全运行，又能支持更高的总连接容量。这一配置已成为每台 ClickHouse Managed Postgres 服务器的标准设置，确保在高并发的实际场景中维持良好的性能和可扩展性。
PgBouncer is a widely used tool for connection pooling in PostgreSQL, but its single-threaded nature creates a significant performance ceiling. Because a single process can only utilize one CPU core regardless of how powerful the underlying hardware is, the connection pooler often becomes a bottleneck long before the database itself reaches its capacity. In ClickHouse Managed Postgres, this limitation is addressed by running a fleet of PgBouncer processes rather than relying on a single instance.
To manage this fleet, every process is configured to bind to the same port using the so_reuseport kernel feature. This allows the operating system to load-balance incoming connections across multiple PgBouncer processes transparently, meaning clients connect to a single endpoint without being aware of the underlying architecture. This setup effectively puts every available CPU core to work, bypassing the single-core constraint.
A primary challenge with this multi-process approach is handling query cancellation requests. Since a cancel request arrives on a different connection than the one running the query, the kernel might route the cancel signal to a process that is not managing the original session. To solve this, ClickHouse employs a peering mechanism that allows the processes to communicate. If a cancel request lands on the wrong process, it is forwarded to the specific process owning the session, ensuring that cancellations function reliably across the entire fleet.
Performance testing on identical 16-vCPU hardware demonstrates the effectiveness of this architecture. A single PgBouncer process tends to plateau at around 87,000 transactions per second, after which performance degrades due to core contention. In contrast, a fleet of 16 processes scales linearly with the workload, reaching approximately 336,000 transactions per second. While the single-process configuration leaves the majority of the machine's resources idle, the fleet setup utilizes the available hardware far more efficiently.
Ultimately, this multi-process approach transforms the pooler back into simple plumbing rather than a bottleneck. By splitting connection limits across the fleet, the system can maintain safe operation while supporting a much higher aggregate connection capacity. This configuration is standard for every ClickHouse Managed Postgres server, ensuring that the infrastructure remains performant and scalable under heavy real-world concurrency.
61 comments • Comments Link
• PgBouncer 仍然是经实战检验的 PostgreSQL 连接池行业标准，尤其在近期更新解决了长期限制（如对 prepared statement 的支持）之后。
• Odyssey 可作为 PgBouncer 的可扩展替代方案，pgdog 则提供了另一种专为修补历史缺陷并支持水平分片而设计的解决方案。
• 虽然技术上可以管理 10,000+ 的海量连接，但与维护只有几百个连接的连接池相比，这通常不是最优选择。
• 在 PgBouncer 中实现 "peering" 可以让多个进程协同工作，确保查询取消请求被正确路由到持有活动会话的进程，从而避免多个 bouncer 在共享端口运行时可能出现的匹配错误。
• 在内核层面使用 SO_REUSEPORT 有助于在同一主机上的多个 PgBouncer 实例之间高效分发连接，避免像 HAProxy 这样的额外代理层带来的延迟和架构开销。
• 跨区域网络延迟是分布式 PostgreSQL 架构中关键的性能考量，其影响通常比分段代理带来的轻微开销更显著。
• 虽然 PostgreSQL 已经有显著演进，但其"每个连接一个进程"的模型仍然需要外部池化方案，以避免与频繁 fork 相关的性能开销，尤其在 serverless 或高并发场景中。
• 基础设施软件的起源常引发关于创建者地缘政治背景的争议，把技术实用性与项目国别关联的道德考量相互对立。
讨论强调了对 PgBouncer 等成熟工具的强烈偏好，同时也承认在特定大规模场景下出现了旨在弥补其局限的专业替代方案。大量技术关注点集中在利用 SO_REUSEPORT 和实例 peering 来解决诸如会话感知查询取消等复杂问题，而无需增加不必要的基础设施跳转。尽管性能优化仍是首要任务，但软件来源的社会政治影响也反复出现，反映了开发者社区中更广泛的道德关切。总体来看，社区倾向于选择那些经过验证、高性能且能简化 PostgreSQL 连接扩展管理的解决方案。 • PgBouncer remains the industry-standard, battle-tested choice for PostgreSQL connection pooling, particularly as recent updates have addressed long-standing limitations like prepared statement support.
• Odyssey serves as a scalable alternative to PgBouncer, while pgdog offers another specialized solution built to address historical shortcomings and support horizontal sharding.
• Managing massive connection counts, such as 10,000+, is technically achievable but generally considered suboptimal compared to maintaining a pool of a few hundred connections.
• The implementation of "peering" in PgBouncer allows multiple processes to coordinate. This ensures that query cancellation requests are correctly routed to the process holding the active session, resolving potential mismatches when multiple bouncers operate behind a shared port.
• Utilizing `SO_REUSEPORT` at the kernel level facilitates efficient connection distribution among multiple PgBouncer instances on the same host, avoiding the latency and architectural overhead of an additional proxy layer like HAProxy.
• Cross-zone network latency is a critical performance consideration for distributed PostgreSQL architectures, often having a more significant impact than the minor overhead of a proxy hop.
• PostgreSQL has evolved significantly, yet its process-per-connection model still necessitates external pooling solutions to prevent the performance overhead associated with frequent process forking, especially in serverless or high-concurrency environments.
• The origin of infrastructure software often triggers debate regarding the geopolitical context of its creators, pitting technical utility against ethical considerations regarding a project's national ties.
The discussion highlights a strong preference for established tools like PgBouncer, while acknowledging the emergence of specialized alternatives that address its limitations in specific high-scale scenarios. A significant portion of the technical focus centers on the benefits of `SO_REUSEPORT` and instance peering to solve complex issues like session-aware query cancellation without adding unnecessary infrastructure hops. While performance optimization remains a priority, there is a recurring underlying tension regarding the sociopolitical implications of software origins, reflecting broader ethical concerns within the developer community. Overall, the community favors proven, performant solutions that simplify the architectural burden of managing PostgreSQL connection scaling.