PgDog AST query-cache memory balloon — reproduction (query_cache_limit caps count not bytes; heavy queries balloon RSS, held until LRU-evicted; TEC-2911)

README.md

PgDog AST query-cache memory balloon

Reproduces a memory balloon in PgDog caused by the AST query cache (query_cache_limit, default 1000), not by prepared statements and not by allocator retention.

The mechanism

PgDog keeps one global LRU cache of parsed query ASTs per process (static CACHE, shared across all clients and all databases the instance fronts), keyed by the query text. The limit bounds the number of entries (1000), not the bytes. A parsed tree for a wide/complex query is large (a few MB here, 5-6 MB for real reporting queries), so:

• 1000 heavy entries -> multi-GB RSS. The limit can't prevent this — it caps count, not memory.
• Entries leave the cache only by LRU eviction (1000 newer distinct queries push them out), by RESET QUERY_CACHE, or by restart. There is no TTL / idle expiry. So a burst of heavy queries (e.g. a nightly report) fills the 1000 slots and the RSS stays ballooned until enough new distinct queries arrive to evict them — which, on a low-diversity daytime workload, may not happen before the pod is restarted.

The memory is live (the cached entries), not freed-but-retained: background_thread:true (or even dirty_decay_ms:0) does not release it, because the pages hold live allocations.

Run

docker compose up -d
pip install 'psycopg[binary]'
python3 repro.py        # run from this directory

repro.py drives three phases and prints pgdog RSS + query_cache_size.

Expected result

phase	what it does	RSS	cache_size
baseline	—	~30 MB	0
P1	2000 distinct heavy queries	~3 GB	1000 (heavy)
P2	120 s light traffic, 100 fixed (hits, no turnover)	stays ~2.5 GB	1000
P3	2000 new distinct light queries (evict heavy)	-> ~150 MB	1000 (light)

Takeaways:

1. Heavy queries fill the cache -> balloon. ~3 MB/entry here; real report queries are heavier (hence 5-6 GB in production).
2. While the cache doesn't turn over, memory is held. P2: light hits keep the heavy entries in the LRU, RSS stays at ~2.5 GB. Matches production during a low-diversity daytime after a nightly report.
3. Eviction frees it without a restart. P3: new distinct light queries push the heavy entries out of the LRU; RSS drops back to baseline as background_thread returns the pages. Same effect as RESET QUERY_CACHE.

Note cache_size is pinned at 1000 the whole time — the LRU count is bounded; the memory is not, because entry size varies by orders of magnitude.

Mitigations

• Lower query_cache_limit (bounds count -> bounds worst-case memory; costs cache hit-ratio, so tune per workload).
• A byte-based cache limit (cap total memory, not entry count) and/or a time-to-idle (evict entries not used within N seconds) would fix this without the count/hit-ratio trade-off. Both validated locally; not upstream yet.

docker-compose.yml

# PgDog AST query-cache memory balloon reproduction.
# One global LRU AST cache per pgdog process (query_cache_limit, default 1000),
# keyed by query text. Heavy queries (big parse trees) fill it and hold RSS
# until they are evicted by newer queries — not on a timer, not on idle.
# See README.md.
services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_USER: lt
      POSTGRES_PASSWORD: ltpass
      POSTGRES_DB: lt
    command: ["-c", "max_connections=200"]

  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:
      # Return freed jemalloc pages to the OS. The balloon persists even with this on:
      # the memory is LIVE (cached AST entries), not retained-freed.
      _RJEM_MALLOC_CONF: "background_thread:true"
    volumes:
      - ./pgdog.toml:/etc/pgdog/pgdog.toml:ro
      - ./users.toml:/etc/pgdog/users.toml:ro
    ports:
      - "6432:6432"   # postgres protocol
      - "9090:9090"   # openmetrics (query_cache_size)
    depends_on: [postgres]
    mem_limit: 8g

pgdog.toml

[general]
host = "0.0.0.0"
port = 6432
openmetrics_port = 9090
default_pool_size = 10
query_parser = "on"          # ensures every query is parsed -> AST cache is populated
# query_cache_limit = 1000   # default; the LRU AST cache holds up to this many entries
passthrough_auth = "disabled"

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

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

repro.py

#!/usr/bin/env python3
"""
PgDog AST query-cache memory balloon — reproduction.

Three phases against a stock pgdog (query_cache_limit=1000 default):

  P1  fill the cache with 2000 distinct HEAVY queries (~300-column SELECTs,
      big parse trees). The LRU AST cache tops out at 1000 heavy entries and
      RSS balloons to a few GB (~3 MB/entry here; real report queries are
      heavier, hence the 5-6 GB seen in production).

  P2  120 s of LIGHT traffic that does NOT turn the cache over (100 fixed
      queries, all cache hits). RSS stays high: the heavy entries are still
      in the LRU. This mirrors a production daytime with few new distinct
      queries — the heavy nightly set just sits there.

  P3  2000 NEW distinct LIGHT queries. These evict the heavy entries from the
      LRU. RSS drops back to baseline WITHOUT a restart — background_thread
      returns the freed pages to the OS.

Run:  docker compose up -d  &&  pip install psycopg[binary]  &&  python3 repro.py
(must be run from the compose directory; it reads pgdog RSS via `docker compose exec`.)
"""
import subprocess, time, urllib.request
import psycopg

DSN = "host=localhost port=6432 user=lt password=ltpass dbname=lt"

def rss_mb():
    r = subprocess.run(["docker", "compose", "exec", "-T", "pgdog",
                        "awk", "/VmRSS/{print int($2/1024)}", "/proc/1/status"],
                       capture_output=True, text=True)
    return r.stdout.strip() or "?"

def cache_size():
    try:
        body = urllib.request.urlopen("http://localhost:9090/metrics", timeout=3).read().decode()
        for line in body.splitlines():
            if line.startswith("query_cache_size"):
                return line.split()[1]
    except Exception:
        pass
    return "?"

def report(label):
    print(f"  {label:38s} RSS={rss_mb():>6} MB   cache_size={cache_size()}", flush=True)

# Heavy = ~300-column SELECT -> large parse tree (~MBs cached).
def heavy(i):
    cols = ", ".join(f"({i}+{k})*{k+1} AS h_{i}_{k}" for k in range(300))
    return f"SELECT {cols} WHERE {i} >= 0"

# Light = trivial SELECT -> tiny parse tree.
def light(i):
    return f"SELECT {i} AS x WHERE {i} >= 0"

def burst(n, gen):
    with conn.pipeline() as p:
        for i in range(n):
            conn.execute(gen(i))
            if (i + 1) % 500 == 0:
                p.sync()

conn = psycopg.connect(DSN, prepare_threshold=0, autocommit=True)
conn.prepared_max = 100  # short-lived-like: only 100 prepared alive client-side at a time

report("baseline")

print("P1: 2000 distinct HEAVY queries (fill cache) ...", flush=True)
burst(2000, heavy)
report("after P1 (heavy fill)")

print("P2: 120 s light HOLD (100 fixed, cache hits, no turnover) ...", flush=True)
t0 = time.time()
while time.time() - t0 < 120:
    with conn.pipeline() as p:
        for j in range(100):
            conn.execute(light(100000 + j))
        p.sync()
    time.sleep(2)
report("after P2 (light hold)")

print("P3: 2000 NEW distinct LIGHT queries (evict heavy) ...", flush=True)
burst(2000, lambda i: light(200000 + i))
time.sleep(15)  # let background_thread return freed pages
report("after P3 (light evict)")

print("\nExpected: baseline ~30 MB -> P1 ~3 GB -> P2 stays ~2.5 GB -> P3 back to ~150 MB.")
print("cache_size stays pinned at 1000 throughout (LRU is bounded; the MEMORY is not).")
conn.close()

users.toml

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