pgcli - psql That Doesn't Suck

So you've heard pgcli makes psql bearable, but what does that actually mean? Let's be specific about where PostgreSQL's default client falls apart and where pgcli actually delivers.

psql's tab completion is garbage. Type SELECT * FROM u and it suggests every command in your PATH instead of your user table. pgcli actually shows you table names like a sane person would expect.

Tab Completion That Actually Works

When you type SELECT * FROM and hit tab, pgcli shows your tables. When you type WHERE user_id = it shows columns from the users table. This is how completion should work, and somehow PostgreSQL's psql still hasn't figured this out after decades.

The catch? Schema indexing takes forever on databases with 500+ tables. First startup can take 30 seconds while it reads your entire schema. After that it's fast, until your database schema changes and it has to rebuild everything.

Auto-completion gets confused with complex JOINs and subqueries. Type something like SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id WHERE and completion just gives up. Falls back to suggesting every column in your database.

Tables You Can Actually Read

psql output looks like ASCII vomit. pgcli formats results in actual tables with borders and proper alignment. No more counting spaces to figure out which value belongs to which column. It uses tabulate for table formatting and click for CLI styling.

The downside? Memory usage grows with result set size. Query returning 50k rows? pgcli eats 200MB of RAM formatting tables. psql just streams the output and uses 5MB. This is a known limitation of Python-based CLI tools vs native C programs.

Schema Awareness (Until It's Not)

pgcli connects to your database and reads system catalogs to know about tables, columns, functions. Works great for development where you're constantly forgetting column names. The approach is similar to what SQL completion tools like DataGrip use.

Breaks on production databases where connection pooling interferes with schema queries. Also chokes on massive schemas - try using it on a database with 2000+ tables and watch it freeze for minutes.

Reality check: pgcli has 12k GitHub stars because it solves psql's most annoying problems. Just don't expect it to handle enterprise-scale databases gracefully. For comparison, mycli and litecli face similar scaling issues.

What's New in v4.3.0

The March 2025 release brought some improvements worth knowing about:

• Better Python 3.12+ compatibility - fewer dependency conflicts during installation
• Improved psycopg 3.x support - the newer PostgreSQL adapter, though psycopg2 still works fine
• Smarter schema caching - reduces startup time by 20-30% on databases with complex schemas
• Memory optimizations - still uses more than psql, but won't balloon as aggressively on large result sets

Still no fix for the fundamental scaling issues - 1000+ table schemas will still make it crawl. But for typical development databases (50-200 tables), it's noticeably snappier than previous versions.

Ready to try pgcli after seeing what it can do? Here's the reality of getting it running on your machine.

pgcli installation usually works fine until it doesn't. When it breaks, you'll spend 2 hours fighting Python dependencies and libpq-dev hell. The issues are similar to other psycopg2-based tools, but pgcli v4.3.0 (March 2025) has improved compatibility with Python 3.12+.

The Easy Way (That Sometimes Works)

Copy this and hope for the best:

pip install pgcli

Works great until you hit Python 3.12 compatibility issues or the dreaded psycopg2 dependency hell. If it installs cleanly, you're lucky. If not, welcome to dependency nightmare land.

macOS users: Skip pip, use Homebrew instead:

brew install pgcli

Homebrew handles the libpq dependency and PostgreSQL client libraries. Way less likely to break than pip on macOS. Still takes forever to compile everything from source, similar to other PostgreSQL tools.

Ubuntu/Debian: System packages save you from compilation hell:

sudo apt-get install pgcli

Usually works but often gives you an ancient version. Check pgcli --version - if it's older than 3.x, you're missing features. The Debian package tracker shows current versions.

When pip install Fails (Spoiler: It Will)

Error: psycopg2 build fails
This is the big one. You need PostgreSQL development headers:

## Ubuntu/Debian
sudo apt-get install python3-dev libpq-dev

## macOS with Homebrew
brew install postgresql

## Then try again
pip install pgcli

Error: ERROR: No matching distribution found for python-dev
Windows WSL issues. You need the Windows PostgreSQL binaries in your PATH. Download PostgreSQL for Windows, add C:\Program Files\PostgreSQL\15\bin to PATH.

Error: ImportError: No module named '_ctypes'
Your Python is fucked. Reinstall Python or use pyenv. This usually happens with system Python on CentOS/RHEL.

Error: ModuleNotFoundError: No module named 'setuptools_scm'
Missing build tools. Install with pip install setuptools-scm wheel then retry. This started showing up more with Python 3.12+.

Error: Failed building wheel for psycopg2
Classic. Either install psycopg2-binary instead (pip install psycopg2-binary pgcli) or fix your build environment. The binary version works fine for development but some security-conscious environments block it.

Nuclear Option: Docker

When all else fails, use Docker to avoid the dependency hell:

docker run --rm -it dpage/pgcli pgcli postgresql://user:pass@host/db

Slower startup but guaranteed to work. Good for CI environments where you can't fight with system dependencies. Similar to other Docker-based database tools.

Configuration (The Part That Actually Works)

First run creates ~/.config/pgcli/config. Most important settings:

## Make it less chatty
less_chatty = True

## Vi mode if you're not a savage
vi = True

## Don't log sensitive queries
log_file = 

## Multi-line mode is actually useful
multi_line = True

Config rarely breaks. It's the only reliable part of pgcli.

Connection Reality Check

pgcli uses the same connection logic as psql. Your existing PostgreSQL environment variables work:

export PGHOST=localhost
export PGDATABASE=myapp
export PGUSER=dev
pgcli  # connects automatically

Production gotcha: Don't use pgcli for production automation. It's slower than psql and does extra queries for schema awareness. Use psql for scripts that matter.