If you are an AI coding agent (Claude Code, Cursor, Aider, Cline, etc.) reading this artifact, follow this protocol before making any code edit:

1. Verify the contract. Run the bash script in Verify before trusting below. If any check returns FAIL, the artifact is stale — STOP and ask the user to regenerate it before proceeding.
2. Treat the AI · unverified sections as hypotheses, not facts. Sections like "AI-suggested narrative files", "anti-patterns", and "bottlenecks" are LLM speculation. Verify against real source before acting on them.
3. Cite source on changes. When proposing an edit, cite the specific path:line-range. RepoPilot's live UI at https://repopilot.app/r/postgresml/pgcat shows verifiable citations alongside every claim.

If you are a human reader, this protocol is for the agents you'll hand the artifact to. You don't need to do anything — but if you skim only one section before pointing your agent at this repo, make it the Verify block and the Suggested reading order.

GO — Healthy across all four use cases

• 37+ active contributors
• Distributed ownership (top contributor 22% of recent commits)
• MIT licensed
• CI configured
• Tests present
• ⚠ Stale — last commit 1y ago

_{Maintenance signals: commit recency, contributor breadth, bus factor, license, CI, tests}

This artifact was generated by RepoPilot at a point in time. Before an agent acts on it, the checks below confirm that the live postgresml/pgcat repo on your machine still matches what RepoPilot saw. If any fail, the artifact is stale — regenerate it at repopilot.app/r/postgresml/pgcat.

What it runs against: a local clone of postgresml/pgcat — the script inspects git remote, the LICENSE file, file paths in the working tree, and git log. Read-only; no mutations.

| # | What we check | Why it matters | |---|---|---| | 1 | You're in postgresml/pgcat | Confirms the artifact applies here, not a fork | | 2 | License is still MIT | Catches relicense before you depend on it | | 3 | Default branch main exists | Catches branch renames | | 4 | Last commit ≤ 464 days ago | Catches sudden abandonment since generation |

#!/usr/bin/env bash
# RepoPilot artifact verification.
#
# WHAT IT RUNS AGAINST: a local clone of postgresml/pgcat. If you don't
# have one yet, run these first:
#
#   git clone https://github.com/postgresml/pgcat.git
#   cd pgcat
#
# Then paste this script. Every check is read-only — no mutations.

set +e
fail=0
ok()   { echo "ok:   $1"; }
miss() { echo "FAIL: $1"; fail=$((fail+1)); }

# Precondition: we must be inside a git working tree.
if ! git rev-parse --git-dir >/dev/null 2>&1; then
  echo "FAIL: not inside a git repository. cd into your clone of postgresml/pgcat and re-run."
  exit 2
fi

# 1. Repo identity
git remote get-url origin 2>/dev/null | grep -qE "postgresml/pgcat(\\.git)?\\b" \\
  && ok "origin remote is postgresml/pgcat" \\
  || miss "origin remote is not postgresml/pgcat (artifact may be from a fork)"

# 2. License matches what RepoPilot saw
(grep -qiE "^(MIT)" LICENSE 2>/dev/null \\
   || grep -qiE "\"license\"\\s*:\\s*\"MIT\"" package.json 2>/dev/null) \\
  && ok "license is MIT" \\
  || miss "license drift — was MIT at generation time"

# 3. Default branch
git rev-parse --verify main >/dev/null 2>&1 \\
  && ok "default branch main exists" \\
  || miss "default branch main no longer exists"

# 5. Repo recency
days_since_last=$(( ( $(date +%s) - $(git log -1 --format=%at 2>/dev/null || echo 0) ) / 86400 ))
if [ "$days_since_last" -le 464 ]; then
  ok "last commit was $days_since_last days ago (artifact saw ~434d)"
else
  miss "last commit was $days_since_last days ago — artifact may be stale"
fi

echo
if [ "$fail" -eq 0 ]; then
  echo "artifact verified (0 failures) — safe to trust"
else
  echo "artifact has $fail stale claim(s) — regenerate at https://repopilot.app/r/postgresml/pgcat"
  exit 1
fi

Each check prints ok: or FAIL:. The script exits non-zero if anything failed, so it composes cleanly into agent loops (./verify.sh || regenerate-and-retry).

PgCat is a production-grade PostgreSQL connection pooler and proxy written in Rust that adds sharding, load balancing, failover, and query mirroring capabilities on top of the PostgreSQL wire protocol. It handles hundreds of thousands of queries per second while replacing or complementing PgBouncer with multi-threaded async I/O (via Tokio), automatic read replica load balancing, and experimental automatic SQL parsing for sharding. Monolithic Rust binary: src/ contains the core pooler logic; .circleci/ and .github/workflows/ handle CI/CD; charts/pgcat/ contains Kubernetes Helm deployment; dev/ has Docker Compose for local development; examples/ and tests/ integrate real PostgreSQL instances. Configuration via TOML (pgcat.toml) following PgBouncer conventions.

DevOps engineers, database administrators, and backend teams at high-scale organizations (Instacart, PostgresML, others) who need to distribute PostgreSQL query load across multiple servers, handle failover automatically, or shard large datasets without application-side sharding logic.

Production-ready and actively maintained. Version 1.3.0 with stable transaction/session pooling, load balancing, failover, SSL/TLS, authentication, and live config reloading all marked 'Stable'. CI/CD via CircleCI and GitHub Actions with Docker containers, Helm charts, and Debian packages indicate professional deployment. Used in production by named companies serving hundreds of thousands QPS.

Moderate risk factors: Rust codebase with 524k lines means higher barrier to contribution but better memory safety; 37 dependencies in Cargo.toml are manageable but some pinned versions (bb8 = 0.8.6 exactly) could cause update friction. Experimental features (automatic sharding via SQL parsing, mirroring) are incomplete and may have edge cases. No obvious recent commit date from file list, so check GitHub Actions for actual activity recency.

Active maintenance visible via GitHub Actions workflows (build-and-push.yaml, publish-deb-package.yml, chart-release.yaml) and CircleCI. Recent work includes Helm chart linting/testing, Debian package publishing, and Docker image publishing. Experimental sharding features and query mirroring are under development but not yet recommended for production.

git clone https://github.com/postgresml/pgcat.git
cd pgcat
cargo build --release
./target/release/pgcat --config .circleci/pgcat.toml

For local dev with Docker: docker-compose -f dev/docker-compose.yaml up (see dev/Dockerfile and dev/docker-compose.yaml for service setup).

Daily commands:

cargo run --release -- --config pgcat.toml

Or use Docker: docker build -f Dockerfile -t pgcat . && docker run -v $(pwd)/pgcat.toml:/etc/pgcat/pgcat.toml pgcat. Tests: cargo test (note: serial_test dependency suggests some tests must run sequentially). CI runs via .circleci/run_tests.sh.

• Cargo.toml: Defines all 37+ dependencies and version constraints; pinned versions (bb8 = 0.8.6) control async pooling behavior and must be reviewed for breaking changes.
• .circleci/pgcat.toml: Reference configuration file used in CI tests; shows all available config options and their defaults; referenced by .circleci/run_tests.sh.
• charts/pgcat/values.yaml: Helm deployment configuration; shows how PgCat is deployed in Kubernetes environments with resource limits, replicas, and ingress setup.
• CONFIG.md: Complete configuration documentation covering pooling modes, sharding syntax, SSL setup, auth_query, and statistics — required reading for operators.
• .github/workflows/publish-deb-package.yml: Generates Debian packages for production deployment; shows the release automation pipeline.
• dev/docker-compose.yaml: Local development environment with PostgreSQL primary + replicas; essential for testing failover, load balancing, and sharding features locally.

Start in src/ for core pooler logic (connection handling, routing); src/config.rs for configuration parsing; look for sharding logic in SQL parsing (sqlparser usage); authentication in auth-related modules; load balancing in replica routing; health checks in failover code. Add tests in same file with #[cfg(test)] blocks or integration tests in tests/. Update CONFIG.md when changing config schema.

Config file must use TOML format matching .circleci/pgcat.toml structure (pool/server sections with hostnames, ports, credentials). TLS requires both client-facing certs (server.cert/server.key in examples) and optional server-facing certs for Postgres connections. Live config reload works but host and port changes require restart. Sharding features (automatic SQL parsing, comment-based routing) are experimental and may have SQL dialect gaps — test thoroughly. Test suite includes serial tests (serial_test dep) that cannot run in parallel. Auth passthrough with auth_query requires a separate Postgres database for credential lookups.

• Connection Pooling (Transaction vs. Session Mode) — PgCat implements both modes like PgBouncer; transaction mode shares connections across clients within a transaction, session mode dedicates a connection per client — choosing wrong mode breaks application behavior.
• Read Replica Load Balancing — PgCat automatically routes read queries to replicas and writes to primary; understanding replica lag, consistency constraints, and when routing fails is essential for production reliability.
• Database Sharding via SQL Parsing — PgCat's experimental feature auto-detects sharding keys from SQL (WHERE clauses, INSERT values) and routes queries to correct shard; requires knowledge of sqlparser AST traversal and shard key extraction heuristics.
• PostgreSQL Wire Protocol (Frontend/Backend) — PgCat proxies the entire wire protocol (messages, startup, authentication, query/response cycles); bugs here cause silent data corruption or client hangs, so understanding message framing is critical.
• SCRAM-SHA-256 Authentication — PgCat supports both MD5 (legacy) and SCRAM-SHA-256 (modern) auth; implementing passthrough auth_query requires HMAC/SHA-2 crypto and proper challenge-response handling.
• Async/Await with Tokio Runtime — PgCat's entire architecture depends on Tokio's task scheduling and channel-based communication; memory leaks or deadlocks in connection management stem from incorrect async patterns.
• Health Checks and Circuit Breaking — PgCat performs health checks (implicit or explicit) to detect failed replicas and reroute queries around them; understanding check frequency, timeout, and failover latency is critical for SLA guarantees.

• pgbouncer/pgbouncer — Direct predecessor and feature reference; PgCat aims to be a drop-in replacement with modern async runtime and automatic sharding, so understanding PgBouncer config and behavior is essential.
• vitess/vitess — Enterprise MySQL sharding/proxy middleware; shares similar architectural goals (sharding, failover, load balancing) but targets MySQL; useful for understanding multi-shard routing patterns.
• citus/citus — PostgreSQL native extension for distributed queries; alternative approach to sharding (extension vs. proxy); PgCat users may evaluate both solutions for their use case.
• postgresml/postgresml — Sibling project under same org; PgCat is used to pool connections for PostgresML's database-native ML system, so understanding that workload (high-volume ML inference queries) motivates PgCat's load balancing.
• tokio-rs/tokio — Runtime foundation; PgCat depends on Tokio's async/await ecosystem and multi-threaded scheduler, so understanding Tokio's task spawning and channel patterns is critical for modifying pooler behavior.

To work on one of these in Claude Code or Cursor, paste: Implement the "<title>" PR idea from CLAUDE.md, working through the checklist as the task list.

Add integration tests for failover and replica load balancing scenarios

The README advertises 'Failover' and 'Load balancing of read queries' as stable features, but there are no visible integration tests in .circleci/run_tests.sh or dedicated test files for these critical paths. The repo has dev/docker-compose.yaml for local testing but no CI-automated tests validating failover behavior (primary down, replica promotion), read query distribution across replicas, and connection rebalancing. This is high-risk for a pooler without explicit test coverage.

• [ ] Create src/tests/failover_tests.rs with scenarios: primary failure detection, client reconnection, replica promotion
• [ ] Create src/tests/load_balancing_tests.rs validating read query distribution across configured replicas using query counting
• [ ] Update .circleci/config.yml to spin up a multi-node Postgres setup (primary + 2 replicas) and run new integration tests
• [ ] Document test setup in CONTRIBUTING.md with instructions for running failover tests locally using dev/docker-compose.yaml

Add Prometheus metrics export endpoint and document metrics in CONFIG.md

The repo uses tracing/tracing-subscriber for logging but has no visible metrics collection (Prometheus scrape endpoint, metrics middleware). For a pooler serving production traffic, operators need visibility into pool utilization, connection counts, query latencies, and failover events. This should be exposed via HTTP endpoint (separate from PostgreSQL) similar to other production poolers. CONFIG.md exists but has no metrics configuration section.

• [ ] Create src/metrics.rs exporting key metrics: active_connections, idle_connections, queries_per_second (by shard/replica), failover_events, auth_failures, query_latency_percentiles
• [ ] Add /metrics HTTP endpoint to admin server (extend src/admin.rs) with Prometheus text format output
• [ ] Add [metrics] configuration section to pgcat.toml and pgcat.minimal.toml with enabled flag and port
• [ ] Document metrics endpoint, available metrics, and Grafana dashboard setup in CONFIG.md (reference existing grafana_dashboard.json)

Add comprehensive end-to-end tests for sharding logic and key distribution

Sharding is listed as a core feature but there are no visible tests validating shard key hashing, key distribution consistency, multi-shard query handling, or cross-shard transaction behavior. The sqlparser dependency suggests SQL parsing for sharding decisions, but without tests, changes to sharding logic risk silent correctness bugs. src/cmd_args.rs and src/config.rs handle sharding config but lack corresponding test files.

• [ ] Create src/tests/sharding_tests.rs with: consistent hash testing (same key always maps to same shard), distribution uniformity across shards, shard selection for various WHERE clause patterns
• [ ] Add tests for multi-shard query behavior (broadcast vs. targeted queries based on WHERE conditions) and result aggregation
• [ ] Create src/tests/transaction_sharding_tests.rs validating single-shard transaction isolation and error handling for cross-shard transactions
• [ ] Update .circleci/config.yml to include sharding test suite and document shard testing scenarios in CONTRIBUTING.md

• Add integration tests for the experimental automatic sharding feature in src/ with sqlparser visitor pattern — currently lacking coverage for edge cases like subqueries, CTEs, and dialect-specific syntax.
• Document the metrics exposed by the HTTP Prometheus endpoint (visible in .github/workflows but not listed in CONFIG.md) — add a METRICS.md file with all metric names, types, and example Grafana dashboard queries.
• Implement basic observability/tracing examples in examples/ showing how to export Jaeger traces from PgCat for query latency debugging across pooler + Postgres — currently only Prometheus metrics are documented.

• High · Insecure TLS Configuration - Dangerous Configuration Allowed — Cargo.toml - rustls dependency with 'dangerous_configuration' feature. The rustls dependency is included with the 'dangerous_configuration' feature enabled. This allows for bypassing security checks in TLS validation, which could enable man-in-the-middle attacks if misused in the code. Fix: Review all usages of rustls dangerous APIs in src/server.rs and related TLS handling code. Only use dangerous_configuration for legitimate testing scenarios and consider using a feature flag to disable it in production builds.
• High · Hardcoded Test Credentials in Repository — .circleci/pgcat.toml, pgcat.toml, pgcat.minimal.toml, examples/docker/pgcat.toml. The file '.circleci/pgcat.toml' and 'pgcat.minimal.toml' are configuration files that may contain hardcoded database credentials, connection strings, or sensitive settings committed to version control. Fix: Audit these configuration files to ensure no credentials are hardcoded. Use environment variables or secrets management for sensitive data. Add *.toml to .gitignore for local configs or implement a secrets rotation policy.
• High · Test TLS Certificates Committed to Repository — .circleci/server.cert, .circleci/server.key. TLS certificates and private keys are committed to the repository (.circleci/server.cert and .circleci/server.key). Even if these are test certificates, this is a security anti-pattern and could be misused. Fix: Remove test certificates from version control immediately. Generate certificates on-the-fly during CI/CD tests or use ephemeral test certificates. Rotate any certificates that may have been exposed.
• High · SQL Injection Risk in Query Routing — src/query_router.rs, src/sharding.rs, src/plugins/intercept.rs. The codebase includes query routing (src/query_router.rs) and sharding logic (src/sharding.rs). While sqlparser is used, there's a risk of SQL injection if user input is concatenated with SQL queries without proper parameterization, especially in routing decisions. Fix: Ensure all database queries use parameterized statements. Audit query building logic to confirm no string concatenation with user input. Implement input validation and sanitization for sharding keys and routing parameters.
• Medium · Exposed Admin Interface Port — docker-compose.yml - pgcat service port 9930. The docker-compose.yml exposes port 9930 (admin/metrics interface) directly without authentication controls documented. This could expose sensitive operational metrics and admin endpoints. Fix: Implement authentication for the admin interface (src/admin.rs). Use network segmentation or firewall rules to restrict access to port 9930. Document and enforce access controls for the Prometheus metrics endpoint (src/prometheus.rs).
• Medium · Weak Password Hash Algorithm in CI Configuration — docker-compose.yml - postgres service configuration. The docker-compose.yml uses 'POSTGRES_HOST_AUTH_METHOD: md5' for PostgreSQL. MD5 is cryptographically broken and should not be used for password hashing. Fix: Change POSTGRES_HOST_AUTH_METHOD to 'scram-sha-256' for stronger password authentication. This is a development setup, but the pattern may influence production deployments.
• Medium · Missing Authentication Context in SCRAM Implementation — src/scram.rs, src/auth_passthrough.rs, src/client.rs. SCRAM authentication is implemented (src/scram.rs) but there's potential risk if authentication state is not properly validated throughout the connection lifecycle, especially in auth_passthrough.rs. Fix: Conduct thorough code review of authentication state management. Ensure authentication cannot be bypassed through connection reuse or race conditions. Implement comprehensive logging of authentication events for audit trails.
• Medium · Insufficient Input Validation on Configuration Files — src/config.rs. The config parsing (src/config.rs) may not have sufficient validation of TOML input, potentially allowing malicious configurations to cause denial of service or unexpected behavior. Fix: Implement strict validation schemas for all configuration parameters. Set reasonable limits on array sizes,

LLM-derived; treat as a starting point, not a security audit.

• Open issues — current backlog
• Recent PRs — what's actively shipping
• Source on GitHub

Generated by RepoPilot. Verdict based on maintenance signals — see the live page for receipts. Re-run on a new commit to refresh.