For a long time, when I heard "database performance problem," I imagined slow queries first. I thought about indexes, joins, query plans, and large tables.
During my internship, I encountered a different kind of pressure. A PostgreSQL server was shared by many internal applications and databases. Some databases contained millions of rows, and dozens of applications depended on the same infrastructure. At one point, a Django application using two databases contributed to connection pressure on that PostgreSQL environment.
The situation forced me to look beyond SQL performance. A database can struggle even when an individual query is reasonable if too many clients are trying to keep connections open. Application configuration, concurrency, connection lifetime, and the shape of the infrastructure matter too.
That investigation changed how I think about databases in production. Knowing how to write SQL is not the same as understanding what happens when many applications continuously talk to the same server.
The first signal was not a bad query
The problem was connection pressure. That distinction mattered because it changed what I needed to investigate.
A PostgreSQL connection is not just an abstract socket that costs nothing. The server maintains state and resources for connected clients. If many applications each open multiple connections, the total can grow long before any single application looks extreme in isolation.
A shared PostgreSQL server makes this more interesting. One application may behave acceptably on its own but become part of a larger capacity problem when combined with many other systems. The relevant question is no longer, "How many connections does this application use?" It becomes, "How does this application's behavior interact with everyone else using the same server?"
That is an architectural question, not only a query question.
Why I did not want to start with more hardware
Adding resources can be valid, but I did not want it to be the first explanation for the problem.
If the pressure comes from connection behavior, increasing capacity without understanding that behavior can postpone the issue rather than clarify it. It can also hide configuration choices that will keep scaling in the same direction.
Before thinking about larger infrastructure, I wanted to understand the path from the Django application to PostgreSQL. When does Django create a connection? How long is it kept? What happens when requests run concurrently? What changes when one application uses two databases? Are there long-running operations? Which parts of the code assume session state on a particular PostgreSQL connection?
Those questions are more useful than assuming that the database server is simply too small.
Django participates in the connection story
An ORM can make database access feel like an implementation detail. I can write a QuerySet and think mainly about the data I want back. In production, the connection beneath that QuerySet still has a lifecycle.
Django's database configuration influences how connections are opened and reused. Process count and concurrency influence how many database clients may exist at the same time. An application configured with multiple databases adds another dimension because a single application process can interact with more than one database connection pool.
The important lesson for me was that application architecture and database capacity cannot be reasoned about separately.
Even if PostgreSQL has a configured connection limit, that number is not a target to consume. It is a boundary. Once several applications compete near that boundary, a local configuration change in one service can affect unrelated systems.
Why PgBouncer became interesting
This led me to investigate PgBouncer.
PgBouncer is a lightweight connection pooler that sits between application clients and PostgreSQL. The basic idea is to avoid requiring every client-side connection to map permanently to a dedicated PostgreSQL backend connection. Depending on the pooling mode, PgBouncer can reuse a smaller set of server connections across more client activity.
The attractive part was clear: connection pooling could reduce pressure on PostgreSQL.
The dangerous part was also clear: inserting a pooler changes connection semantics. I could not treat it as a transparent switch without understanding how the applications behave.
Session, transaction, and statement pooling are different contracts
The three modes I focused on were session pooling, transaction pooling, and statement pooling.
With session pooling, a client is assigned a PostgreSQL server connection for the lifetime of that client session. The server connection is not freely reused by another client until the client disconnects. This preserves the strongest connection affinity and is therefore the closest to connecting directly to PostgreSQL, although it offers less aggressive reuse than shorter pooling modes.
With transaction pooling, a server connection is assigned for the duration of a transaction and can return to the pool after that transaction finishes. This can reuse server connections much more efficiently, but the application cannot assume it will receive the same PostgreSQL backend connection for its next transaction.
With statement pooling, reuse is even more aggressive: a server connection can be released after individual statements. That imposes much stricter limitations and is not a mode I would introduce casually into an application that was not designed around those semantics.
The key point is that these are not merely performance presets. They are different contracts between the application and the database layer.
Why session pooling felt like the conservative first step
Because the existing applications were not designed around a connection pooler from the beginning, session pooling was interesting as a conservative option.
It does not produce the maximum possible connection reuse, but it preserves behavior that depends on connection affinity more closely than transaction pooling. For existing systems, that compatibility can be more important than theoretical efficiency.
This is where I had to resist thinking of optimization as a single dimension. "Fewer server connections" is valuable, but so is "does not subtly break existing behavior."
A production change should improve the system without changing assumptions that the application depends on.
`QuerySet.iterator()` made the trade-off concrete
One detail that made me think carefully was Django code using QuerySet.iterator() and behavior related to database sessions.
On PostgreSQL, streaming large query results can involve server-side cursor behavior. That kind of behavior can depend on the relationship between a transaction and the backend connection serving it. More aggressive pooling changes when a client is guaranteed to stay on the same PostgreSQL connection.
The important part of the lesson was not a single Django setting. It was the method: before changing infrastructure semantics, audit application behavior that may depend on those semantics.
It is easy to read a PgBouncer configuration example and see only the operational benefit. It is harder, and more important, to ask what parts of the codebase become invalid under that configuration.
Shared infrastructure multiplies the cost of assumptions
A PostgreSQL server supporting many applications creates a special kind of risk. A change intended to help one application can influence others if applied globally. Conversely, one application's connection behavior can consume capacity needed by systems with completely different workloads.
That means a connection-pooling decision should include an inventory of who is connecting, how they connect, and which behaviors are sensitive to connection reuse.
I learned to think in terms of blast radius. The more applications share a resource, the more valuable it is to understand the effects of a change before making it.
This is one reason production infrastructure can feel slower to change than application code. The correct unit of analysis is larger.
Debugging production is an exercise in consequences
The investigation also changed what I mean when I say I "know" a technology.
I can know that PgBouncer exists. I can explain its configuration file. I can describe transaction pooling. That is useful knowledge, but it is not yet engineering judgment.
Engineering judgment begins when I ask what will happen to the applications already running. Which queries are long-lived? Which code relies on session-level behavior? How are failures observed? What is the rollback plan? Does the expected reduction in connections justify the new operational component?
The same applies to PostgreSQL itself. Knowing indexes, transactions, and SQL syntax is different from understanding how connection limits, application concurrency, and shared infrastructure interact under real load.
What I took from the incident
I did not come away believing that PgBouncer is a universal fix for PostgreSQL scalability. Connection pooling addresses a particular class of problems. It cannot repair inefficient queries, poor schema design, inappropriate locking, slow storage, or every other database bottleneck.
What I gained instead was a better debugging model.
Start with the observed constraint. Trace the behavior across layers. Separate symptoms from causes. Understand the semantics of any infrastructure component before adding it. Audit the code that may depend on the behavior you are about to change. Prefer a reversible, conservative improvement when the system is shared and the blast radius is large.
The database became "the problem" only in the sense that it exposed a wider system problem. PostgreSQL was not isolated from Django, process concurrency, application configuration, or the architecture around it.
That was the real lesson: production systems fail across boundaries, so debugging them requires understanding those boundaries too.