# Transaction Pooling: The Multi-Tenant Nightmare

> This critique of Transaction Pooling in PgBouncer targets multi-tenant PostgreSQL setups that rely on session-level state (e.g., schema-per-tenant using `search_path`). Because Transaction Pooling discards session state after each transaction, it causes serious cross-tenant data leaks and confusion in these cases. If your app is single-tenant or fully stateless (not relying on session state), Transaction Pooling may work fine. Always evaluate your app’s architecture before enabling Transaction Pooling.
> 
> This blog focuses **specifically on multi-tenancy architectures** — especially schema-per-tenant setups — in PostgreSQL. If you're not building a multi-tenant app, some of these warnings may not apply to you (but stick around anyway — you might still enjoy the tea ☕)

## **Why Using Transaction Pooling in PgBouncer with Multi-Tenancy Is a Disaster Waiting to Happen?**

Let’s not pretend this is fine. You’ve built a beautiful multi-tenant SaaS app. You’re optimizing performance. You install PgBouncer, flip it into **Transaction Pooling mode**, and feel proud — but then, surprise! Tenant A is suddenly seeing Tenant B’s data.

Welcome to the wild world of **connection pooling gone wrong**. If you’re using PgBouncer with **multi-tenant PostgreSQL**, and you think **Transaction** or worse — **Statement Pooling** is okay, sit down. We need to talk. This is your **intervention**.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text"><strong>You’re not scaling. You’re leaking.</strong></div>
</div>

## **PgBouncer Pooling Modes 101**

> (AKA the “how not to shoot your foot” guide)

PgBouncer is amazing — it lets you have thousands of app connections without overwhelming PostgreSQL. It offers 3 pooling modes:

1. **Session Pooling** — Each client gets their own server connection until they disconnect. Like renting a cabin in the woods. Safe. Boring. Effective.
    
2. **Transaction Pooling** — You return the connection after each transaction. Like Airbnb’ing your room out to strangers every night while you're still living in it.
    
3. **Statement Pooling** — Every SQL statement uses a fresh connection. It’s like passing your toothbrush around after every use. Just don’t.
    

‘‘*If you just said “ew,” congrats — you already understand why this blog exists*.’’

## **What Even *Is* Multi-Tenancy?**

Multi-tenancy means one app serves multiple clients (a.k.a tenants) but pretends like each has their own private mansion. In PostgreSQL, the common strategies are:

* **Schema-per-tenant:** Every tenant gets their own schema. Classy, organized, scalable.
    
* **Row-level isolation:** All tenants live in the same tables, separated by tenant\_id. Like co-working space — cheaper, but messier.
    
* **Database-per-tenant:** The gated community of multi-tenancy (not our focus here).
    

We’ll focus on **schema-per-tenant**, where the app dynamically sets the `search_path` on every request to ensure PostgreSQL targets the correct schema for that tenant.

Sounds fancy. Until you accidentally serve Ram’s data to Hari. Then it sounds like a lawsuit.

## **Why Transaction Pooling Is Your App’s Worst Enemy?**

1. **Connection State? What State?:**  
    
    In **Transaction Pooling**, PgBouncer gives your app a fresh connection for each transaction — but it doesn’t clean up session-level settings like `search_path`. So if you don’t manually reset them, **they might leak** into the next request.  
    In **Statement Pooling**, it’s even harsher — PgBouncer throws away everything after every SQL statement. That means session-level config just vanishes.  
    
    *Imagine renting a hotel room and finding the last guest’s toothbrush still wet. That’s your app in Transaction Pooling mode.*  
    *In Statement Pooling? You get a brand-new room and toothbrush every time — but you can’t leave anything behind, not even your towel.*  
    
2. **When Connections Play Musical Chairs (And Your Data Gets Caught in the Game):**  
    Let’s be honest:
    
    1. Thread A sets search\_path = tenant\_a, finishes the transaction
        
    2. Thread B reuses the connection, assumes it’s for tenant\_b, but still sees tenant\_a's context
        
    3. Result: **tenant\_b sees tenant\_a’s data**.
        
    
    You won’t spot this in dev or UAT. It lurks in production, until your CEO gets an angry email:  
      
    **“Why is another company’s invoice in my dashboard?”  
    **Congratulations. You just summoned the final boss: *The Data Protection Authority*.  
    
3. **Session-Level Features? Forget About It**  
    Session-level magic like:
    
    1. SET search\_path
        
    2. Temporary tables
        
    3. Tenant-specific GUCs (like `preferred_language = 'np'`)
        
    4. Advisory locks.
        
    
    ...all break or **leak** under aggressive pooling.  
    You paid for that tea, but some sneaky guy just swiped your cup and disappeared like it was free ***chai*** *☕.*  
    
4. **The Code Becomes a Messy Crime Scene**  
    To avoid disaster with Transaction Pooling, you'd have to:
    
    1. Reset search\_path on **every** request
        
    2. Clean up all lingering session state
        
    3. Pray your ORM doesn’t cache anything nasty.
        

Your clean architecture? Now it’s a patchwork of hacks and prayers your production won’t explode.

> Let’s get fancy with the heading — it deserves it.

## **😇 When Is Transaction Pooling Actually Okay?**

Almost never. But *if*:

* Your app is 100% stateless (no schema switching, no temp tables)
    
* You hate complexity more than you love safety
    
* You enjoy living on the edge (querying prod on a Friday night).
    

Then maybe — *maybe* — it’s okay.

But remember: just because it works on your machine doesn’t mean it won’t destroy your prod.

## **🛡️ Do This Instead: Use Session Pooling**

Session Pooling may not be flashy, but it’s reliable — it keeps your app **safe**, **predictable**, and **easy-going**.

Why it works:

* Keeps tenant-specific search\_path intact
    
* Preserves session-level settings
    
* Avoids accidental data leaks.
    

Yes, it uses more memory. Yes, it’s less efficient. But so is wearing a seatbelt. Don’t be the dev that flies through the windshield.

## **🧠 Want Performance Without the Risk?**

Stop trying to make Transaction Pooling happen. It's not going to happen. Instead:

1. **Increase PostgreSQL’s max\_connections**
    
2. Use **separate PgBouncer pools per tenant** (hardcore but effective)
    
3. Explore **multiplexing(like** `PgCat`**)** or **app-level routing** to manage tenant-based connection pools.
    

You want speed, sure. But not at the cost of serving Communist’s secrets to Raa.Swo.Paa.

## **Quick Recap: Pooling Modes & Multi-Tenancy Safety**

| Feature | **Transaction/Statement Pooling** | **Session Pooling** |
| --- | --- | --- |
| Reuses connections fast | ✅ | ❌ |
| Keeps session state | ❌ | ✅ |
| Safe for multi-tenancy | ❌ | ✅ |
| Data leakage risk | **High** | Low |
| Makes you work on weekends | **Often** | Very Rarely |

## **Real-World Analogy: The Hotel Key Disaster**

Imagine a hotel:

* In **Session Pooling**, each guest gets their own key. They enter their room, stay the night, then leave.
    
* In **Transaction Pooling**, every time a guest goes to the bathroom, they hand the key back to the hotel staff. Next guest uses it — and finds someone else’s underwear on the bed.
    

Don’t build that hotel. Don’t be that dev.

## **Final Thoughts**

If you're building a **multi-tenant app**, **Session Pooling** is the only sane way to use PgBouncer. **Transaction** or **Statement Pooling** may look fast and shiny, but they’re quietly waiting to wreck your tenant isolation and your weekend.

So next time you're tempted to switch modes in pgbouncer.ini, think twice — pick wisely.