PostgreSQL Connection Pooling: PgBouncer's Three Modes, the Prepared Statement Trap and Multi-Tenant Sizing
How to choose between session, transaction and statement modes, what breaks in transaction mode, why the prepared statement error only appears under load, and how to cap pools in a database-per-tenant setup.
Every client connection to PostgreSQL is a separate operating system process on the server. Opening one means authentication, a fork and cold caches; keeping one open means a few megabytes of memory per connection and extra work for the scheduler. As application servers multiply, 30 replicas each holding a pool of 20 show up at the database as 600 connections. Most of them sit idle, yet the database pays for every one. A connection pooler closes that gap: it hands out plenty of connections to clients, opens few to the database, and pairs them only when needed. This article covers PgBouncer's three pool modes, which workload each fits, how to size pools in a multi-tenant setup, and the trap that bites almost everyone: prepared statements.
Three modes, one decision
PgBouncer's modes differ in how long a server connection stays assigned to a client.
Session mode assigns a server connection when the client connects and keeps it until the client disconnects. Everything on the server side keeps working: session variables, prepared statements, temporary tables, locks. The only gain is caching the cost of opening connections; the number of concurrent connections does not shrink.
Transaction mode assigns the server connection at the start of a transaction and reclaims it at COMMIT or ROLLBACK. Two consecutive transactions from the same client may run on different server connections. A thousand clients of which 40 are actually inside a transaction at any moment get by with 40 server connections. This is where the real win is, and it is the mode you want in production nearly every time.
Statement mode reclaims the connection after every statement; multi-statement transactions are rejected. It has a narrow niche such as autocommit-only read traffic.
The decision is simple: aim for transaction mode and strip everything that depends on session state out of the application. Session mode is a stopgap for the migration period, not a destination.
What breaks in transaction mode
In transaction mode a client cannot assume it will see the same server connection across two transactions. Every feature built on that assumption either fails or, worse, misbehaves silently:
Session settings made with SET (SET search_path, SET timezone) vanish on the next transaction. Worse, the setting stays on the server connection and leaks to whichever client gets it next. Use SET LOCAL inside the transaction; it is discarded at commit.
LISTEN and NOTIFY do not work; the notification arrives on a connection the client no longer holds.
Session-level advisory locks (pg_advisory_lock) stay on the wrong connection. Prefer the transaction-level variants (pg_advisory_xact_lock).
Temporary tables and WITH HOLD cursors become unreachable after the transaction ends.
Named prepared statements deserve their own section below.
Hunting for these in the codebase is the real work of moving to transaction mode. The PgBouncer config is five lines; cleaning up the code can take weeks.
The prepared statement trap
Most drivers use server-side prepared statements for performance: the plan is built once, stored under a name, and later calls send only the name and parameters. That name belongs to the server connection. In transaction mode the client lands on a different server connection for its next transaction and gets "prepared statement does not exist". The nasty part is that it appears randomly and only under load; a single-client test always gets the same connection back and never sees it.
There are two ways out. The first is to disable server-side prepared statements in the driver:
Go pgx: default_query_exec_mode=simple_protocol in the connection string
Python psycopg 3: prepare_threshold=None
JDBC: prepareThreshold=0
Rails: prepared_statements: false
The second is to let PgBouncer track protocol-level prepared statements itself. Since version 1.21, setting max_prepared_statements to a value above zero makes PgBouncer re-prepare a client's statement on whichever server connection it lands on. This applies only to protocol-level statements (the driver's Parse message), not to SQL PREPARE. For a new setup take the second route and keep the driver optimisation. If you are stuck on an older PgBouncer, the first route is the only one.
A baseline configuration
The file below is a starting point that runs in transaction mode and tracks prepared statements:
What the values mean: max_client_conn is the total number of client connections accepted, default_pool_size the number of server connections opened per database and user pair. reserve_pool_size adds extra connections when a client has waited longer than reserve_pool_timeout seconds. query_wait_timeout is how long a client waits in the pool before it gets an error; leave it unbounded and a stalled database turns into a silently piling application tier, so you see a hang instead of a timeout.
The ignore_startup_parameters line swallows startup parameters that some drivers, JDBC among them, send and PgBouncer does not recognise; without it those clients cannot connect at all.
Sizing
Do not derive the number of server connections from the number of clients; derive it from how many concurrent queries the database can execute efficiently. The common starting rule is twice the core count plus the number of disks. On an 8-core server, around 20 active connections deliver more throughput than 200 because context switching and lock contention drop. From there, tune with two columns of SHOW POOLS: if cl_waiting is consistently above zero and maxwait grows, the pool is too small; if sv_idle stays high, it is too large.
PgBouncer keeps one pool per database and user pair. In a multi-tenant system with a database per tenant that product explodes: 200 tenants and a pool of 20 means a potential 4000 connections to the database. Cap it with max_db_connections and max_user_connections, leave min_pool_size at zero so idle tenants hold nothing, and use a wildcard entry instead of listing every tenant in [databases]:
Whatever the numbers, the total must stay below PostgreSQL's max_connections and leave the superuser_reserved_connections share untouched.
Where to put it
Either as a sidecar next to the application or as a central service. A sidecar means a separate pool per application replica; it does not solve the connections-times-replicas problem, it only postpones it. A central deployment genuinely merges connections and should be the default. Its one weakness is that PgBouncer is single-threaded and a single process saturates at very high request rates. You can run several PgBouncer processes sharing the same port, or consider a multi-threaded alternative such as PgCat. PgCat can also split reads from writes; if you do not need that, PgBouncer's maturity and simplicity win.
Do not remove the application-side pool (HikariCP, pgx pool), but shrink it. The application pool manages the cost of establishing connections; PgBouncer manages the total count on the database side. They do different jobs.
When not to use it
If you run a single application replica and the total connection count is well within what the database handles comfortably, PgBouncer only adds a failure point. If your architecture is built on LISTEN/NOTIFY, transaction mode is closed to you and session mode does not deliver the pooler's main benefit, so an application pool is enough. Finally, restarting PgBouncer drops every client connection; do not put it in production before learning the RELOAD, PAUSE and RESUME admin commands for config changes and maintenance.
[OK] 30 records · 9 featured · 1 retainer · 20 standard
/02 services
services.tree
> note
· Prices are in USD. Volume discounts of 7% for 6-12 day projects and 14% for 13+ day projects apply. Programs of 100+ person-days are priced individually. Prices are negotiable based on scope, urgency and long-term collaboration.
> ls ~/products/ --open-source
[OK] 2 entries · 1 live · 1 pre-release
/03 products
products.list
liveopen source · mit/prod/01
filex
the self-hosted file manager that embeds anywhere
single go binary · vue/react/web-component embed · 5 storage drivers · realtime collab · rbac · native multi-tenancy · mcp server for ai agents
We build infrastructure, automate everything, and keep systems running.
BRF Tech is a Bursa-based DevSecOps and software consulting firm. We operate across 30 service areas, from Kubernetes cluster management to serverless platform setup, CI/CD pipeline design to AI agent development.
What we do is simple: we set up your systems, automate them, and make sure they won't wake you up at 3 AM. We codify your infrastructure with Terraform, move your deployments to GitOps with ArgoCD, and monitor everything with Prometheus. If something breaks — we intervene before it does.
We're against vendor lock-in. We work with open-source tools, self-hosted solutions, and industry-standard technologies. We build your own serverless platform on Knative, isolate with Kata Containers, manage your secrets with Vault. Every project is delivered with clear scope, clear timeline, clear pricing.
DevSecOps & CI/CD
Kubernetes & Serverless
Infrastructure as Code
AI & Agent (MCP/ACP)
Security & Zero Trust
Full-Stack Development
Self-Hosted Solutions
> grep -i question ~/faq.md
[OK] 3 entries · click to expand
[?]What services are included in BRF Tech's DevOps solutions?▾
We offer CI/CD pipeline setup, Kubernetes cluster management, container migration, infrastructure automation with Terraform, monitoring & alerting, in-house technology installations and DevOps consulting services.
[?]What are your software development services?▾
We offer MVP development, existing software performance optimization, backend API development (Node.js, Go, Python) and full-stack application development. Every project is delivered with minimal technical debt.
[?]Which DevOps tools do you work with?▾
From Kubernetes and Docker to Talos Linux, from Terraform and OpenTofu to Ansible, and across Jenkins, GitLab CI/CD, GitHub Actions, ArgoCD, Flux, Helm and Rancher, we work with industry-standard tooling. For observability and error tracking we use Prometheus, Grafana, Loki, Tempo, OpenTelemetry and GlitchTip; for networking, Cilium and Envoy; for test automation, Playwright, Cypress, k6, Locust, Testcontainers and Trivy. We determine the most suitable toolset together, based on your project’s needs.
Automated build, test and deployment pipelines with GitHub Actions, GitLab CI/CD.
Automated build, test and deploy pipeline setup for existing or new repositories. GitHub Actions, GitLab CI/CD or preferred tool is used. Separate workflows are defined for staging + production environments.
Container architecture migration, cluster setup and orchestration.
Production-ready Kubernetes cluster setup on bare-metal or cloud. Includes Helm charts, Ingress, TLS certificate management, namespace isolation and RBAC.
Cozystack is an open-source cloud platform built on Kubernetes and the VMware alternative we recommend most. Virtual machines run on KubeVirt, while managed PostgreSQL, MySQL, Redis and Kafka services and dedicated tenant Kubernetes clusters are provisioned from the same dashboard and API. Includes installation on your own bare-metal servers on Talos Linux, replicated storage with LINSTOR/DRBD, network isolation with Kube-OVN, tenant and quota structure, monitoring, backup and a disaster recovery plan. After deployment we also handle upgrades, operations and support; since the platform is open source, there are no license fees and no vendor lock-in.
Packages
Virtual machine support on an existing Kubernetes cluster with KubeVirt
$2,800
4–6 person-day
Pilot setup (3 nodes, virtual machines + managed database)
$3,500
5–8 person-day
Production setup (HA + replicated storage + monitoring + backup)
$6,500
10–16 person-day
Multi-tenant private cloud (tenants, quotas, managed service catalog)
Planned, low-downtime migration of vSphere/ESXi environments to Cozystack, OpenStack or VHI.
A planned move to a VMware alternative for environments whose costs rose after Broadcom moved VMware licensing to subscription bundles, or whose renewal is coming up. We start with an inventory of your vSphere/ESXi estate (virtual machines, disks, networks, dependencies), then compare today's license cost with the total cost of the target platform. In most environments the target is Cozystack; OpenStack fits large-scale IaaS needs and Virtuozzo Hybrid Infrastructure fits storage-heavy service providers. A pilot runs on non-critical virtual machines, disks are converted with tools such as virt-v2v and Forklift, and the migration proceeds in waves within maintenance windows, with a rollback plan for every step.
Packages
Inventory + cost comparison + migration plan
$2,400
3–5 person-day
Pilot migration (up to 10 virtual machines)
$3,500
5–8 person-day
Phased migration (up to 100 virtual machines)
$9,750
15–22 person-day
Full data center migration (network + storage + backup)
OpenStack deployment, Neutron networking, Ceph/Cinder storage, upgrades and operations.
OpenStack deployment and operations in your own data center. A containerized, highly available control plane with Kolla-Ansible; Nova (compute), Keystone (identity), Glance (images) and Horizon (dashboard). On the network side, Neutron with OVN, VLAN/VXLAN, provider and tenant networks, floating IPs and the Octavia load balancer; on the storage side, Cinder block storage on Ceph RBD and optional S3-compatible object storage. For existing deployments: health checks, planned upgrades between releases, monitoring and alerting setup, and a backup strategy.
Packages
Health check + improvement report (existing deployment)
$2,400
3–5 person-day
Lab / PoC setup (single region, 3 nodes)
$3,500
5–8 person-day
Release upgrade (existing deployment)
$3,900
6–10 person-day
Production setup (HA control plane + Ceph + Neutron/OVN)
Hyperconverged infrastructure: multi-tenant cloud plus block and S3-compatible storage for service providers.
Hyperconverged infrastructure with Virtuozzo Hybrid Infrastructure, where compute, storage and networking run on the same servers. Block, S3-compatible object and NFS storage on software-defined storage, plus backup storage (Backup Gateway) for backup products such as Acronis. For service providers: a multi-tenant cloud with domains and projects, quotas, a self-service panel and an OpenStack-compatible API. Includes hardware sizing, network design, cluster deployment and expansion, version upgrades and operations support.
Packages
Pre-deployment assessment + hardware sizing
$2,400
3–4 person-day
Version upgrade / cluster expansion
$2,800
4–6 person-day
Hyperconverged cluster setup (storage + compute)
$3,900
6–10 person-day
Service provider setup (multi-tenant cloud + S3 + backup storage)
Migration of existing applications to container architecture.
Migration of existing applications to container architecture. Docker image design, Compose configuration and conversion to Kubernetes manifests. Kata Containers / gVisor can be included for advanced isolation.
Health check of your current environment, capacity planning, platform selection and a roadmap.
An independent assessment of your current virtualization environment (VMware vSphere, OpenStack, VHI or other KVM-based platforms). The health check covers cluster configuration, resource overcommit, storage latency, forgotten snapshots, backup and patch status. Capacity planning measures CPU, memory and storage growth to work out the hardware you will need next. Platform selection decides which workload should run where: Cozystack for teams running containers and virtual machines side by side, OpenStack for large-scale IaaS, VHI for storage-heavy service providers; staying on the current platform is also an option for some workloads. The deliverable is a written report with a cost comparison and a prioritized roadmap.
In-house tools like GitLab, Nextcloud, VPN, mail server.
Installation of tools like GitLab, Mattermost, Nextcloud, mail server, VPN, internal monitoring on company servers. Deployed on Docker Compose or Kubernetes.
DB + cache + code bottleneck identification and resolution.
Profiling of existing application, identification and resolution of bottlenecks. DB query optimization, caching layer (Redis), service-level improvements.
MCP/ACP agents, RAG pipelines, multi-agent orchestration, model serving.
LLM integration (OpenAI, Claude, Gemini, local models), tool-augmented AI agents with MCP (Model Context Protocol) and ACP (Agent Communication Protocol). RAG pipeline, multi-agent orchestration, model serving (vLLM/Ollama). AI layer for existing business processes.
Event-driven data pipeline with Apache Kafka, RabbitMQ, NATS. ETL/ELT orchestration with Apache Airflow, real-time data streaming, CDC (Change Data Capture). Data lake/warehouse design, schema registry, dead letter queue management.
Packages
Kafka/RabbitMQ setup + basic producer/consumer
$3,500
5–7 person-day
ETL pipeline (Airflow + source → warehouse)
$5,200
8–12 person-day
Full event-driven architecture (CDC + streaming + DLQ)
Hands-on technical training for your teams. Docker & container fundamentals, Kubernetes operations, Terraform IaC, CI/CD best practices, AI/LLM integration workshops. Practical exercises in live lab environments, content customized by skill level.
E2E test automation with Playwright, Cypress, load testing with k6/Gatling. Pre-deploy quality gate integrated into CI/CD pipeline, test coverage reporting, visual regression testing. Reduce test writing time by up to 60% with AI-assisted test generation.
Your Collected Personal Data, Collection Method and Legal Basis
Any information that identifies or makes you identifiable is considered "personal data." When you visit our website or use our services, contact information such as your name, surname, email address, phone number, as well as your IP address and browser cookie data may be collected through automatic or semi-automatic means. This data is processed based on the legal grounds specified in Article 5 of the Personal Data Protection Law No. 6698: "being directly related to the establishment or performance of a contract" and "being mandatory for the legitimate interests of the data controller, provided that it does not harm the fundamental rights and freedoms of the data subject."
Purpose of Processing Your Personal Data
Your collected personal data is processed for the purposes of providing and improving our services, fulfilling customer requests, meeting legal obligations, conducting information security processes and managing communication activities. Your data is processed in a limited and proportionate manner for the stated purposes; when the purpose ceases to exist, data is deleted, destroyed or anonymized.
To Whom and For What Purposes Collected Personal Data May Be Transferred
Your personal data may be transferred to public institutions and organizations as required by legal regulations, to our business partners and technical infrastructure providers for the purpose of delivering services, and to lawyers and consultants in legal disputes. Transfers are carried out in accordance with Articles 8 and 9 of the Law, with necessary technical and administrative measures in place.
Your Rights as a Data Subject
In accordance with Article 11 of Law No. 6698, you have the right to: learn whether your personal data is being processed; request information if it has been processed; learn the purpose of processing and whether it is used in accordance with its purpose; know the third parties to whom it is transferred domestically or abroad; request correction if it has been processed incompletely or incorrectly; request deletion or destruction within the framework of conditions set out in Article 7 of the Law; request that the operations carried out be notified to third parties to whom data has been transferred; object to any adverse result arising from the analysis of data exclusively through automated systems; and claim compensation for damages in case of unlawful processing. You may contact us through the communication channels on our website to exercise these rights.
Terms & Conditions
Terms & Conditions
1. Service Scope and Changes
BRF Tech conducts its software development, DevOps consulting, infrastructure setup and technical support services within the framework of these terms and conditions. The scope of service is determined separately for each project and finalized through mutual agreement. BRF Tech reserves the right to update the scope, pricing and technical details of its services, provided that prior notice is given.
2. Privacy Policy
Personal data collected within the scope of our services is processed in accordance with our Privacy Policy. For detailed information, please review our Privacy Policy page.
3. Service Usage and Responsibilities
Clients agree to use the provided services solely within legal and ethical boundaries. BRF Tech reserves the right to suspend or terminate services in the event of misuse, violation of third-party rights, or use for illegal activities. The client is responsible for the accuracy and currency of the information provided within the project scope.
4. Payment Terms
Payment terms are determined on a project basis and finalized through mutual agreement at the start of the project. Unless otherwise specified, invoices are payable within 15 days of project delivery. A monthly late payment interest of 2% may apply to overdue payments.
5. Cancellation and Refund Policy
Cancellation requests must be submitted in writing at least 7 days before the service start date. For projects already in progress, billing is based on the proportion of completed work. No refunds are issued for software and infrastructure components specifically developed within the project scope.