---
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