PgDog prepared-statements memory balloon reproduction (unbounded cache, undercounted by SHOW MEMORY, not released)

README.md

PgDog memory balloon: unbounded prepared-statement cache (in-use statements are never capped)

A client that opens many distinct server-side prepared statements over the extended protocol and keeps them open makes PgDog's prepared-statement cache grow to gigabytes.

Key points:

• prepared_statements_limit does not bound this. It only controls eviction of unused statements (per-server-connection LRU, which protects the backend Postgres). In-use statements (still referenced by a connected client, used > 0) are never evicted, so the pgdog-side cache grows without bound.
• SHOW MEMORY massively undercounts it (a few MB reported while RSS is multiple GB) — it counts entry strings, not the cache's HashMap capacity / Statement (parse + RowDescription) sizes.
• After the client disconnects, memory is only partially released (~1 GB stays).
• The backend Postgres stays flat — the growth is entirely PgDog-side.
• Independent of query_parser (on and off both balloon) and of query_parser_engine (protobuf/raw) — the growth is in the extended-protocol prepared-statement handling (PreparedStatements::insert → GlobalCache), not the query parser.

Root cause (source): frontend/prepared_statements/global_cache.rs. GlobalCache holds statements: HashMap<CacheKey, CachedStmt> and names: HashMap<String, Statement>. Each distinct prepared statement adds an entry with a reference count used. close_unused(limit) only evicts entries with used == 0. While a client keeps statements open, used >= 1, so nothing is evicted and both HashMaps grow unbounded (plus the per-client local: HashMap<String,String>).

Tested on ghcr.io/pgdogdev/pgdog:0.1.48.

Run

docker compose up -d
pip install "psycopg[binary]"
python3 repro.py 500000

Watch PgDog's RSS climb into the GBs while repro.py runs (docker stats), and compare with what PgDog reports:

PGPASSWORD=adminpass psql -h 127.0.0.1 -p 6432 -U admin -d admin \
  -c "SHOW CLIENT MEMORY" -c "SHOW SERVER MEMORY" \
  -c "SELECT count(*) FROM (SHOW PREPARED STATEMENTS) t"

Observed (isolated stand, pgdog 0.1.48, prepared_statements_limit=100)

config	PgDog RSS	SHOW PREPARED STATEMENTS	notes
baseline	27 MB	—
query_parser=on, 625k prepared open	2850 MB	625646	SHOW MEMORY totals 49 MB (~50x undercount); Postgres flat 78 MB
query_parser=off, 500k prepared open	2389 MB	500000	same behaviour — not parser-related
after client disconnect	~1039 MB		per-client map freed (~1.8 GB), but ~1 GB not released

prepared_statements_limit = 100 in all cases — it does not cap in-use statements.

docker-compose.yml

# PgDog prepared-statements memory balloon reproduction.
# See README.md.
services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_USER: lt
      POSTGRES_PASSWORD: ltpass
      POSTGRES_DB: lt
    command: ["-c", "max_connections=500", "-c", "shared_buffers=1GB"]

  pgdog:
    image: ghcr.io/pgdogdev/pgdog:0.1.48
    command: ["/usr/local/bin/pgdog", "--config", "/etc/pgdog/pgdog.toml", "--users", "/etc/pgdog/users.toml"]
    environment:
      # so freed jemalloc memory is returned to the OS; the balloon persists even with this on.
      _RJEM_MALLOC_CONF: "background_thread:true"
    volumes:
      - ./pgdog.toml:/etc/pgdog/pgdog.toml:ro
      - ./users.toml:/etc/pgdog/users.toml:ro
    ports:
      - "6432:6432"
    depends_on: [postgres]
    mem_limit: 12g

pgdog.toml

[general]
host = "0.0.0.0"
port = 6432
default_pool_size = 10
query_parser = "on"
prepared_statements_limit = 100
passthrough_auth = "disabled"

[admin]
user = "admin"
password = "adminpass"

[[databases]]
name = "lt"
host = "postgres"
port = 5432
database_name = "lt"

repro.py

#!/usr/bin/env python3
"""
Prepare many DISTINCT server-side prepared statements over the extended protocol
and keep them open. PgDog's prepared-statement cache grows unbounded and RSS
balloons, even though `prepared_statements_limit = 100` is set and `SHOW MEMORY`
reports only a few MB. Postgres itself stays flat -- the growth is PgDog-side.

    pip install "psycopg[binary]"
    python3 repro.py 1000000

Watch PgDog RSS grow (e.g. `docker stats`), and compare with what PgDog accounts for:
    PGPASSWORD=adminpass psql -h 127.0.0.1 -p 6432 -U admin -d admin \
        -c "SHOW CLIENT MEMORY" -c "SHOW SERVER MEMORY" -c "SHOW PREPARED STATEMENTS"
"""
import sys, time, psycopg

N = int(sys.argv[1]) if len(sys.argv) > 1 else 1_000_000
CI = "host=127.0.0.1 port=6432 user=lt password=ltpass dbname=lt"

conn = psycopg.connect(CI, prepare_threshold=0, autocommit=True)
conn.prepared_max = 50_000_000   # keep every prepared statement open (no client-side eviction)

def big_query(i: int) -> str:
    # 40 output columns -> large RowDescription, similar to real reporting queries.
    cols = ", ".join(f"{i}+{k} AS col_{i}_{k}" for k in range(40))
    return f"SELECT {cols} WHERE {i} >= 0"

print(f"preparing {N} distinct prepared statements (kept open)...", flush=True)
t0 = time.time()
with conn.pipeline() as p:
    for i in range(N):
        conn.execute(big_query(i))       # unique text -> a new server-side prepared statement
        if (i + 1) % 25_000 == 0:
            p.sync()
            print(f"  {i+1}/{N}  ({(i+1)/(time.time()-t0):.0f}/s)", flush=True)

print("DONE preparing; holding the connection open for 120s (measure PgDog RSS now)", flush=True)
time.sleep(120)
print("closing connection", flush=True)
conn.close()
time.sleep(20)
print("done", flush=True)

users.toml

[[users]]
name = "lt"
database = "lt"
password = "ltpass"