README.md

metadata

title: pg_plan_cache Agent
emoji: 🐘
colorFrom: blue
colorTo: indigo
sdk: gradio
sdk_version: 5.23.0
app_file: app.py
pinned: false
license: postgresql
tags:
  - postgresql
  - redis
  - query-cache
  - database
  - plan-cache
  - extension
short_description: Manage and monitor the pg_plan_cache PostgreSQL extension

pg_plan_cache Agent

An interactive agent for managing, monitoring, and diagnosing the pg_plan_cache PostgreSQL extension — a query execution plan cache backed by Redis.

Features

Tab	Requires Backend?	Description
Normalizer	No	Normalize SQL queries and compute SHA-256 cache keys
Architecture	No	Visual documentation of how pg_plan_cache works
Cache Stats	Yes	Live hit/miss/error statistics
Analyze	Yes	Efficiency analysis with tuning recommendations
Diagnose	Yes	Full diagnostic across PostgreSQL, Redis, stats, config
Redis Health	Yes	Redis memory, clients, keyspace info
Cached Plans	Yes	List/inspect cached plan entries
Dependencies	Yes	Query-table dependency mappings
Configuration	Yes	View/manage GUC parameters
Invalidate	Yes	Flush plans globally or per-table
SQL Query	Yes	Execute read-only SELECT queries

Demo Mode

The Normalizer and Architecture tabs work without any backend. They demonstrate the core query normalization and hashing logic that pg_plan_cache uses internally.

Live Mode

To enable all tabs, set these as Space Secrets (Settings > Variables and secrets):

Secret	Description	Example
PG_HOST	PostgreSQL hostname	my-db.example.com
PG_PORT	PostgreSQL port	5432
PG_DATABASE	Database name	postgres
PG_USER	Database user	postgres
PG_PASSWORD	Database password	secret
REDIS_HOST	Redis hostname	my-redis.example.com
REDIS_PORT	Redis port	6379
REDIS_PASSWORD	Redis password (optional)	secret

CLI Agent

A command-line version is also included (agent.py) with the same functionality:

pip install psycopg2-binary redis python-dotenv
cp .env.example .env  # fill in credentials
python agent.py

About pg_plan_cache

pg_plan_cache is a PostgreSQL extension that caches query execution plans in Redis. It hooks into the PostgreSQL planner, normalizes queries (replacing literals with $N placeholders), computes a SHA-256 hash, and stores/retrieves serialized plans with configurable TTL and automatic schema-change invalidation.

• Source: pg_plan_cache
• PostgreSQL: 18+
• Dependencies: hiredis, Redis