The Pgcli Large-Scale Role Explosion Problem

The query hung for twenty seconds before the terminal came back to life. Pgcli blinked. The role list was longer than the screen could hold. What should have been ten lines had become thousands. This is the large-scale role explosion problem. And if you use Pgcli against a busy Postgres instance, you may already have met it.

Pgcli is a popular command-line client for Postgres. It features auto-completion, syntax highlighting, and smart suggestions that fetch database metadata from system catalogs. That metadata includes user roles and grants. At scale, those catalog lookups can spiral. In some environments, automation creates far too many roles — sometimes hundreds of thousands — due to CI processes, ephemeral environments, or faulty cleanup scripts.

Large-scale role explosion kills Pgcli responsiveness. Each keystroke can trigger background queries. The client waits on enormous joins from pg_roles, pg_auth_members, and ACL tables. The more roles the system has, the more time Pgcli spends fetching and processing them. In extreme cases, the client can hang or crash before you run a single query.

Mitigation starts with understanding where the roles come from. Check SELECT rolname FROM pg_roles; and audit the creation patterns. Limiting role churn through automation fixes or retention policies can prevent the issue from recurring. Where roles must remain, you can tune Pgcli. Disable auto-completion for roles in ~/.config/pgcli/config by adjusting smart_completion settings. Connect with flags that limit metadata lookups. For the worst cases, query the database directly via psql or scripts, skipping Pgcli’s autoload behavior entirely.

Postgres itself can be tuned. Extra indexes on catalog tables rarely help, because the bottleneck is row count rather than search speed. Instead, focus on pruning unneeded roles, consolidating grants, and cleaning orphaned entries. Schedule automated cleanup jobs after test runs. Monitor role counts as part of production health metrics.

The Pgcli large-scale role explosion problem is not a bug in Pgcli — it’s the product of scaling without role hygiene. Once you control role count and adjust client behavior, performance returns to normal.

If you want to see a system free from role explosion and optimized for speed, set it up in minutes at hoop.dev.