Works in psql, Flaky in Prod: PgBouncer's Silent Murder of LISTEN/NOTIFY

LISTEN/NOTIFY in transaction pooling taught me humility. “The notifications just stop arriving. No error, no warning, they just… stop.” We had built a real-time dashboard using PostgreSQL’s LISTEN/NOTIFY—a simple, elegant pub/sub mechanism built into the database. It worked flawlessly in development. Local tests passed every time. But in production, after some random interval, notifications would silently stop arriving. Restarting the listener service temporarily fixed it, until it happened again.

The debugging was maddening because there was no error to chase. The LISTEN command succeeded. PostgreSQL logs showed NOTIFYs being sent. The listener was connected and healthy. Yet somehow the notifications were vanishing into thin air. I spent two days adding logging, checking network issues, and reviewing our code before a colleague asked: “Are you connecting through PgBouncer?”

That question changed everything. PgBouncer in transaction pooling mode doesn’t maintain session state between transactions. When your LISTEN transaction ends, the connection goes back to the pool and might be given to a completely different client. Your LISTEN registration exists on a connection that’s now serving someone else’s queries. The NOTIFYs are being delivered—just to the wrong client.

This is one of PostgreSQL’s most common gotchas when using connection poolers. LISTEN/NOTIFY, prepared statements, advisory locks, and session-level settings are all “session state” that assumes you keep the same database connection throughout. Transaction pooling breaks that assumption silently.

Environment: PostgreSQL 16, PgBouncer 1.21 in transaction pooling mode, application using LISTEN/NOTIFY for real-time updates

Understanding the Problem

How LISTEN/NOTIFY Works (Without Pooler)

Direct PostgreSQL connection:

Client Process                PostgreSQL Backend
     |                              |
     |  LISTEN events;              |
     |----------------------------->|
     |  OK (listener registered)    |
     |<-----------------------------|
     |                              |
     |  ... time passes ...         |
     |                              |
     |                         NOTIFY events, 'payload'
     |                         (from another session)
     |                              |
     |  Async notification          |
     |<-----------------------------|
     |  payload: 'payload'          |
     |                              |

The client KEEPS the same backend connection.
LISTEN registration persists until UNLISTEN or disconnect.

How It Breaks (With Transaction Pooling)

PgBouncer in transaction pooling mode:

Client Process         PgBouncer              PostgreSQL Backends
     |                    |                         |
     |  BEGIN; LISTEN;    |  → Backend A            |
     |  COMMIT;           |                         |
     |------------------->|---------+               |
     |                    |         |  LISTEN registered on A
     |                    |         v               |
     |  (transaction      |  Backend A released     |
     |   complete)        |  back to pool!          |
     |                    |                         |
     |  ... time ...      |                         |
     |                    |                         |
     |  Other client      |  → Backend A            |
     |  (SELECT * FROM)   |  (now used by others!)  |
     |                    |                         |
     |                    |                    NOTIFY events
     |                    |                         |
     |                    |  Notification goes to   |
     |                    |  Backend A... which is  |
     |                    |  now serving OTHER      |
     |                    |  client!                |
     |                    |                         |
     ❌ Original listener NEVER receives notification!

Session State That Breaks With Transaction Pooling

PostgreSQL session-level features that DON'T survive transaction pooling:

1. LISTEN/NOTIFY
   - LISTEN registers on a connection
   - Transaction ends → connection released
   - Notifications arrive on "your" connection (now someone else's)

2. Prepared Statements (server-side)
   - PREPARE stmt AS SELECT ...
   - Connection released after transaction
   - EXECUTE stmt → "prepared statement does not exist"

3. Session Variables
   - SET search_path = 'myschema'
   - SET statement_timeout = '5s'
   - Next transaction might be on different connection

4. Advisory Locks
   - pg_advisory_lock(key)
   - Lock held on connection, not by your client
   - Connection released → you lose the lock

5. Temporary Tables
   - CREATE TEMP TABLE ...
   - Table exists on connection
   - Connection released → table lost (or conflicts)

Diagnosing the Problem

Verify PgBouncer Mode

# Connect to PgBouncer admin console
psql "host=localhost port=6432 user=admin dbname=pgbouncer"

# Check pool mode
SHOW CONFIG;
# Look for: pool_mode = transaction  ← THIS IS THE PROBLEM

SHOW POOLS;
# Shows active pools and their configuration

SHOW SERVERS;
# Shows backend connections and their state

Watch Connection Reassignment

-- On PgBouncer admin console
SHOW CLIENTS;
-- Note your client PID

SHOW SERVERS;
-- Note which backend your client is using

-- Now on your application:
-- Execute a transaction, then wait

-- Back on admin console:
SHOW SERVERS;
-- Your backend may now be serving a different client!

Test LISTEN/NOTIFY Directly

# Terminal 1: Listener (through PgBouncer)
psql "host=localhost port=6432 user=app dbname=mydb"
mydb=> LISTEN test_channel;
LISTEN
mydb=> -- just wait here

# Terminal 2: Notifier (direct to PostgreSQL)
psql "host=localhost port=5432 user=app dbname=mydb"
mydb=> NOTIFY test_channel, 'hello';
NOTIFY

# Terminal 1: Through PgBouncer in transaction mode
# Nothing appears! The LISTEN was on a connection that's now released.

# Terminal 3: Listener (direct to PostgreSQL)
psql "host=localhost port=5432 user=app dbname=mydb"
mydb=> LISTEN test_channel;
LISTEN
# Now try NOTIFY from Terminal 2 again
# This time it works!

Application-Level Detection

# Python with psycopg2 - detect if notifications stop

import select
import psycopg2

conn = psycopg2.connect("host=pgbouncer port=6432 dbname=mydb")
conn.autocommit = True  # Required for LISTEN
cur = conn.cursor()
cur.execute("LISTEN events")

notification_count = 0
no_notification_cycles = 0

while True:
    if select.select([conn], [], [], 5.0) == ([], [], []):
        no_notification_cycles += 1
        if no_notification_cycles > 12:  # 1 minute without notifications
            print("WARNING: No notifications for 1 minute - possible pooler issue!")
            # Consider reconnecting
    else:
        conn.poll()
        while conn.notifies:
            notify = conn.notifies.pop(0)
            notification_count += 1
            no_notification_cycles = 0
            print(f"Got: {notify.channel}: {notify.payload}")

Reproduction Lab

Docker Compose Setup

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: pass
      POSTGRES_DB: testdb
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

  pgbouncer:
    image: edoburu/pgbouncer:1.21.0
    environment:
      DB_HOST: postgres
      DB_PORT: "5432"
      DB_USER: postgres
      DB_PASSWORD: pass
      POOL_MODE: transaction  # THE PROBLEM!
      ADMIN_USERS: postgres
      AUTH_TYPE: plain
    ports:
      - "6432:6432"
    depends_on:
      postgres:
        condition: service_healthy

networks:
  default:
    driver: bridge

Reproduce the Bug

# Start the stack
docker compose up -d

# Wait for services
sleep 5

# Terminal 1: Listen via PgBouncer (will FAIL)
docker compose exec postgres psql \
  "host=pgbouncer port=6432 user=postgres password=pass dbname=testdb" \
  -c "LISTEN my_channel;" -c "SELECT pg_sleep(60);"

# Terminal 2: Send notification direct to Postgres
docker compose exec postgres psql \
  "host=localhost port=5432 user=postgres password=pass dbname=testdb" \
  -c "NOTIFY my_channel, 'hello from postgres';"

# Terminal 1 should show notification... but it WON'T!
# The LISTEN was lost when the first command completed.

# Now prove it works direct to Postgres:
# Terminal 3: Listen direct to Postgres (will WORK)
docker compose exec postgres psql \
  "host=localhost port=5432 user=postgres password=pass dbname=testdb" \
  -c "LISTEN my_channel; SELECT 1; \\watch 1"
# (Keep this running)

# Terminal 2: Send another notification
docker compose exec postgres psql \
  "host=localhost port=5432 user=postgres password=pass dbname=testdb" \
  -c "NOTIFY my_channel, 'hello direct';"

# Terminal 3 WILL show the notification!

Prepared Statement Failure

-- Through PgBouncer (transaction mode)
-- Terminal 1:
\c "host=pgbouncer port=6432 user=postgres password=pass dbname=testdb"

BEGIN;
PREPARE my_stmt AS SELECT $1::text;
EXECUTE my_stmt('first call');  -- Works!
COMMIT;

-- Transaction ends, connection released

-- Later (possibly different connection):
EXECUTE my_stmt('second call');
-- ERROR: prepared statement "my_stmt" does not exist

The Fix

Option 1: Session Pooling for LISTEN/NOTIFY Clients

# pgbouncer.ini - Multiple pools with different modes

[databases]
# Regular application traffic - transaction pooling (efficient)
mydb = host=postgres dbname=mydb pool_mode=transaction

# LISTEN/NOTIFY clients - session pooling (maintains state)
mydb_session = host=postgres dbname=mydb pool_mode=session

[pgbouncer]
pool_mode = transaction  # default for unlisted databases

# Application code - use the right connection string
# For regular queries:
regular_conn = psycopg2.connect("host=pgbouncer port=6432 dbname=mydb")

# For LISTEN/NOTIFY:
listen_conn = psycopg2.connect("host=pgbouncer port=6432 dbname=mydb_session")
listen_conn.autocommit = True
listen_conn.cursor().execute("LISTEN events")

Option 2: Direct Connection for Listeners

# Bypass PgBouncer entirely for LISTEN connections

import os

# Regular traffic through pooler
POOLER_DSN = "host=pgbouncer port=6432 dbname=mydb"

# LISTEN traffic direct to Postgres
DIRECT_DSN = "host=postgres port=5432 dbname=mydb"

class NotificationListener:
    def __init__(self):
        # Use direct connection, not pooler
        self.conn = psycopg2.connect(DIRECT_DSN)
        self.conn.autocommit = True

    def subscribe(self, channel):
        cur = self.conn.cursor()
        cur.execute(f"LISTEN {channel}")

    def poll(self, timeout=5.0):
        if select.select([self.conn], [], [], timeout)[0]:
            self.conn.poll()
            return list(self.conn.notifies)
        return []

Option 3: Application-Level Pub/Sub

# Replace LISTEN/NOTIFY with polling or external queue
# when using transaction pooling

import redis
import json

class MessageBus:
    """Use Redis pub/sub instead of PostgreSQL LISTEN/NOTIFY"""

    def __init__(self):
        self.redis = redis.Redis(host='redis', port=6379)
        self.pubsub = self.redis.pubsub()

    def publish(self, channel: str, payload: dict):
        self.redis.publish(channel, json.dumps(payload))

    def subscribe(self, channel: str):
        self.pubsub.subscribe(channel)

    def listen(self):
        for message in self.pubsub.listen():
            if message['type'] == 'message':
                yield json.loads(message['data'])

# Or use polling approach with PostgreSQL
class PollingNotifier:
    """Poll a notifications table instead of LISTEN/NOTIFY"""

    def __init__(self, pool):
        self.pool = pool  # Use connection pool normally
        self.last_id = 0

    def poll(self) -> list:
        with self.pool.getconn() as conn:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT id, channel, payload, created_at
                    FROM notifications
                    WHERE id > %s
                    ORDER BY id
                    LIMIT 100
                """, (self.last_id,))
                rows = cur.fetchall()
                if rows:
                    self.last_id = rows[-1][0]
                return rows

Option 4: Disable Server-Side Prepared Statements

// For JDBC - disable server-side prepared statements
// This avoids the "prepared statement does not exist" error

Properties props = new Properties();
props.setProperty("prepareThreshold", "0");  // Disable server-side prepare
// OR
props.setProperty("preferQueryMode", "simple");  // Use simple query protocol

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://pgbouncer:6432/mydb", props);

# For psycopg2 - use client-side parameter binding
# (This is the default, but make sure you're not using server-side prepare)

# Safe - uses client-side binding
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

# Avoid - creates server-side prepared statement
cursor.execute("PREPARE my_stmt AS SELECT * FROM users WHERE id = $1")

Testing Your Fix

Automated LISTEN/NOTIFY Test

#!/usr/bin/env python3
"""Test that LISTEN/NOTIFY works through your connection path."""

import psycopg2
import select
import threading
import time
import sys

LISTEN_DSN = sys.argv[1] if len(sys.argv) > 1 else "host=localhost port=6432 dbname=mydb"
NOTIFY_DSN = sys.argv[2] if len(sys.argv) > 2 else "host=localhost port=5432 dbname=mydb"

received = []

def listener():
    conn = psycopg2.connect(LISTEN_DSN)
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute("LISTEN test_channel")

    for _ in range(20):  # Wait up to 10 seconds
        if select.select([conn], [], [], 0.5)[0]:
            conn.poll()
            while conn.notifies:
                notify = conn.notifies.pop(0)
                received.append(notify.payload)
        if received:
            break
    conn.close()

def notifier():
    time.sleep(1)  # Give listener time to register
    conn = psycopg2.connect(NOTIFY_DSN)
    cur = conn.cursor()
    cur.execute("NOTIFY test_channel, 'test_payload'")
    conn.commit()
    conn.close()

# Run test
t1 = threading.Thread(target=listener)
t2 = threading.Thread(target=notifier)
t1.start()
t2.start()
t1.join()
t2.join()

if received and received[0] == 'test_payload':
    print("SUCCESS: LISTEN/NOTIFY works!")
    sys.exit(0)
else:
    print(f"FAILURE: Expected ['test_payload'], got {received}")
    print("This usually means PgBouncer is in transaction mode")
    print("and you're connecting listeners through it.")
    sys.exit(1)

Monitoring

PgBouncer Statistics

-- On PgBouncer admin console
SHOW STATS;
-- Look for:
-- total_query_count - queries executed
-- total_received - bytes received
-- avg_query_time - average query duration

SHOW POOLS;
-- cl_active: clients with active transactions
-- cl_waiting: clients waiting for connection
-- sv_active: server connections in use
-- sv_idle: server connections idle
-- pool_mode: IMPORTANT - shows actual mode

Alert on Missed Notifications

# Add to your listener application
import prometheus_client

notification_received = prometheus_client.Counter(
    'app_notifications_received_total',
    'Total notifications received',
    ['channel']
)

notification_gaps = prometheus_client.Counter(
    'app_notification_gaps_total',
    'Times we detected missing notifications',
    ['channel']
)

# Use sequence numbers in payloads to detect gaps
last_seq = {}

def handle_notification(channel, payload):
    data = json.loads(payload)
    seq = data.get('seq', 0)

    if channel in last_seq and seq > last_seq[channel] + 1:
        gap = seq - last_seq[channel] - 1
        notification_gaps.labels(channel=channel).inc(gap)
        logger.warning(f"Missed {gap} notifications on {channel}")

    last_seq[channel] = seq
    notification_received.labels(channel=channel).inc()

Checklist

## PgBouncer + LISTEN/NOTIFY Checklist

### Detection
- [ ] Verify PgBouncer pool_mode (SHOW CONFIG on admin console)
- [ ] Test LISTEN/NOTIFY manually (through pooler vs direct)
- [ ] Check for "prepared statement does not exist" errors
- [ ] Monitor notification delivery rate

### Architecture Decision
- [ ] Do I really need LISTEN/NOTIFY? Consider alternatives
- [ ] Can I use session pooling for listener connections?
- [ ] Can listeners bypass PgBouncer entirely?

### If Using LISTEN/NOTIFY
- [ ] Create separate pool with session mode for listeners
- [ ] OR: Connect listeners directly to PostgreSQL
- [ ] Document which connection path to use for what

### If Using Prepared Statements
- [ ] Set prepareThreshold=0 in JDBC
- [ ] Or use session pooling for affected clients
- [ ] Test prepared statement survival across transactions

### Testing
- [ ] Automated test for LISTEN/NOTIFY path
- [ ] Test notification delivery under load
- [ ] Test after PgBouncer restart

Conclusion

The PgBouncer transaction pooling + LISTEN/NOTIFY trap is a perfect example of how two things that work perfectly in isolation can break when combined. PostgreSQL’s LISTEN/NOTIFY is elegant and reliable. PgBouncer’s transaction pooling is an excellent way to scale connection usage. But together, they create a silent failure mode where notifications vanish without any error.

The root cause is a fundamental mismatch: LISTEN/NOTIFY is session-level state (the listener is registered on a specific backend connection), but transaction pooling releases connections between transactions. Your LISTEN registration lives on a connection that PgBouncer happily hands to other clients.

The fix depends on your architecture:

Separate pools: Configure a session-mode pool for LISTEN clients
Direct connections: Bypass PgBouncer for listeners
Alternative pub/sub: Use Redis, Kafka, or polling instead

The key insight is that transaction pooling breaks anything that relies on session state persisting between transactions. LISTEN/NOTIFY, prepared statements, session variables, advisory locks, and temporary tables all fall into this category. If you’re using any of these features, you need either session pooling or direct connections.

Key principles:

Transaction pooling releases connections between transactions—your session state doesn’t survive
LISTEN is session-level, not transaction-level—it requires a persistent connection
No error is raised—the LISTEN succeeds, notifications just go to the wrong place
Session pooling or direct connections are required—for any session-state features
Test your notification path explicitly—don’t assume it works because LISTEN succeeded

Check your PgBouncer mode right now: SHOW CONFIG; on the admin console. If it says pool_mode = transaction and you’re using LISTEN/NOTIFY, you have a bug waiting to happen.

PostgreSQL Prepared Statement Plan Cliff - Another prepared statement gotcha
Connection Pool Sizing with Little’s Law - Pool configuration fundamentals

Works in psql, Flaky in Prod: PgBouncer's Silent Murder of LISTEN/NOTIFY

Understanding the Problem

How LISTEN/NOTIFY Works (Without Pooler)

How It Breaks (With Transaction Pooling)

Session State That Breaks With Transaction Pooling

Diagnosing the Problem

Verify PgBouncer Mode

Watch Connection Reassignment

Test LISTEN/NOTIFY Directly

Application-Level Detection

Reproduction Lab

Docker Compose Setup

Reproduce the Bug

Prepared Statement Failure

The Fix

Option 1: Session Pooling for LISTEN/NOTIFY Clients

Option 2: Direct Connection for Listeners

Option 3: Application-Level Pub/Sub

Option 4: Disable Server-Side Prepared Statements

Testing Your Fix

Automated LISTEN/NOTIFY Test

Monitoring

PgBouncer Statistics

Alert on Missed Notifications

Checklist

Conclusion

Related posts

Cite this article

Understanding the Problem

How LISTEN/NOTIFY Works (Without Pooler)

How It Breaks (With Transaction Pooling)

Session State That Breaks With Transaction Pooling

Diagnosing the Problem

Verify PgBouncer Mode

Watch Connection Reassignment

Test LISTEN/NOTIFY Directly

Application-Level Detection

Reproduction Lab

Docker Compose Setup

Reproduce the Bug

Prepared Statement Failure

The Fix

Option 1: Session Pooling for LISTEN/NOTIFY Clients

Option 2: Direct Connection for Listeners

Option 3: Application-Level Pub/Sub

Option 4: Disable Server-Side Prepared Statements

Testing Your Fix

Automated LISTEN/NOTIFY Test

Monitoring

PgBouncer Statistics

Alert on Missed Notifications

Checklist

Conclusion

Related Articles

Related posts

Cite this article