Spaces:
Sleeping
Sleeping
| 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: | |
| ```bash | |
| 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](https://github.com/YOUR_USERNAME/pg_plan_cache) | |
| - **PostgreSQL**: 18+ | |
| - **Dependencies**: hiredis, Redis | |