PostgreSQL Tools: Clients, Monitoring, Migration, and Performance

Databases 2026-02-09 · 6 min read postgresql databases sql pgadmin migration

PostgreSQL Tools: Clients, Monitoring, Migration, and Performance

PostgreSQL is the database most developers reach for, but the ecosystem of tools around it is sprawling. There are dozens of GUI clients, multiple migration frameworks, and various monitoring solutions — and choosing wrong means switching later, which is painful.

This guide covers the tools that actually matter for day-to-day PostgreSQL development.

GUI Clients

pgAdmin 4

pgAdmin is the official PostgreSQL management tool. It's free, open-source, and runs as a web application or desktop app.

# Install on macOS
brew install --cask pgadmin4

# Or run as Docker container
docker run -p 5050:80 \
  -e [email protected] \
  -e PGADMIN_DEFAULT_PASSWORD=admin \
  dpage/pgadmin4

What it does well: Full database management — create/alter tables, write and run queries, view query plans, manage roles and permissions, schedule backups. It's comprehensive.

Where it falls short: The UI feels dated. Navigation is clunky — too many clicks to get to common operations. The web-based interface can be slow with large result sets.

Verdict: Use it for administration tasks (roles, permissions, backups). For daily query writing, use something faster.

DBeaver

DBeaver is a multi-database client built on Eclipse. The Community Edition is free; the Pro version ($19/month) adds NoSQL support and advanced features.

brew install --cask dbeaver-community

What it does well: Supports virtually every database (PostgreSQL, MySQL, SQLite, MongoDB, Redis, etc.) through a single interface. The ER diagram generator is useful for understanding unfamiliar schemas. Smart SQL autocompletion is better than pgAdmin's.

Where it falls short: It's a Java application, so startup time is 3-5 seconds and memory usage is 500MB+. The interface has an overwhelming number of panels and options.

Verdict: Good if you work with multiple database types. Overkill for PostgreSQL-only work.

TablePlus

TablePlus is a native GUI client for macOS, Windows, and Linux. It's fast and clean.

brew install --cask tableplus

What it does well: Fast startup (under 1 second), clean interface, inline editing of rows, quick table structure views, and the ability to stage changes before committing them (similar to git staging). Supports PostgreSQL, MySQL, SQLite, Redis, and more.

Where it falls short: The free version limits you to 2 open tabs and 2 database connections. Pro license is $89 one-time. Some advanced features (like query plan visualization) are basic compared to pgAdmin.

Verdict: The best daily-driver GUI client for developers who prefer visual tools. Worth paying for if you use databases regularly.

Beekeeper Studio

Beekeeper Studio is an open-source SQL editor focused on simplicity.

brew install --cask beekeeper-studio

What it does well: Clean, minimal interface. Auto-complete for SQL queries, saved queries, dark mode. The Community Edition is genuinely usable (not crippled).

Where it falls short: Fewer advanced features than DBeaver or pgAdmin. No ER diagrams in the free version.

Verdict: Good for developers who want a simple, free GUI without pgAdmin's complexity.

CLI Tools

psql

psql is PostgreSQL's built-in command-line client. If you use PostgreSQL, you should know psql.

# Connect to a database
psql -h localhost -U postgres -d mydb

# Useful psql meta-commands
\dt          # List tables
\d tablename # Describe table structure
\di          # List indexes
\df          # List functions
\x           # Toggle expanded display (vertical output)
\timing      # Toggle query timing
\e           # Open query in $EDITOR
\watch 2     # Re-run last query every 2 seconds

The .psqlrc file lets you customize the prompt and default behavior:

-- ~/.psqlrc
\set PROMPT1 '%n@%/%R%# '
\set PROMPT2 '%R%# '
\pset null '(null)'
\set HISTSIZE 10000
\set COMP_KEYWORD_CASE upper
\timing

pgcli

pgcli is psql with better autocompletion and syntax highlighting.

pip install pgcli
# or
brew install pgcli

pgcli -h localhost -U postgres -d mydb

It provides multi-line autocompletion that's context-aware — it knows your table names, column names, and even suggests JOIN conditions based on foreign keys. If you spend time in the terminal, pgcli is a significant upgrade over raw psql.

Migration Tools

Drizzle Kit

Drizzle ORM has become the go-to for TypeScript projects. Its migration tool (drizzle-kit) generates SQL migrations from your TypeScript schema.

// drizzle/schema.ts
import { pgTable, serial, text, timestamp, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').unique().notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').references(() => users.id),
});

# Generate migration from schema changes
bunx drizzle-kit generate

# Apply migrations
bunx drizzle-kit migrate

# Open Drizzle Studio (browser-based data viewer)
bunx drizzle-kit studio

Drizzle Kit detects schema changes and generates .sql migration files. You review them, commit them, and apply them in production. The push command applies changes directly without generating migration files — useful for development, dangerous for production.

Prisma Migrate

Prisma uses a declarative schema file and generates migrations.

// prisma/schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

# Generate and apply migration
npx prisma migrate dev --name add_posts_table

# Apply in production
npx prisma migrate deploy

golang-migrate

For non-ORM workflows, golang-migrate (usable from any language via CLI) handles plain SQL migrations:

brew install golang-migrate

# Create a migration
migrate create -ext sql -dir migrations -seq add_users_table

# Apply migrations
migrate -path migrations -database "postgresql://user:pass@localhost/db?sslmode=disable" up

# Rollback last migration
migrate -path migrations -database "..." down 1

-- migrations/000001_add_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- migrations/000001_add_users_table.down.sql
DROP TABLE users;

Which to choose: Drizzle Kit if you're in a TypeScript project using Drizzle ORM. Prisma Migrate if you're using Prisma. golang-migrate or plain SQL files if you want framework-independent migrations you fully control.

Monitoring and Performance

pg_stat_statements

The single most important PostgreSQL extension for performance. It tracks execution statistics for all SQL queries.

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries (by total time)
SELECT
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percent_total,
  left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find queries with the most calls
SELECT calls, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

EXPLAIN ANALYZE

Understanding query plans is the most valuable PostgreSQL skill after basic SQL.

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- With actual execution stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.name
ORDER BY post_count DESC
LIMIT 10;

Key things to look for in query plans:

Seq Scan on large tables: Usually means a missing index
High actual rows vs estimated rows: Statistics are stale; run ANALYZE tablename
Nested Loop with large outer table: Consider a Hash Join (may need more work_mem)
Sort with external merge: work_mem is too low for the sort

pganalyze

pganalyze is a hosted monitoring service that collects query statistics, identifies slow queries, suggests missing indexes, and tracks schema changes. Pricing starts at $249/month per server.

pgHero

pgHero is a free, open-source performance dashboard. It's simpler than pganalyze but covers the essentials.

docker run -e DATABASE_URL="postgresql://user:pass@host:5432/db" \
  -p 8080:8080 ankane/pghero

pgHero shows: slow queries, missing indexes, unused indexes, duplicate indexes, table bloat, connection stats, and replication lag. For small-to-medium deployments, it's often all you need.

Backup and Restore

pg_dump and pg_restore

The built-in tools handle most backup needs:

# Dump a single database (custom format — compressed, supports parallel restore)
pg_dump -Fc -h localhost -U postgres mydb > mydb.dump

# Dump only schema (no data)
pg_dump -Fc --schema-only -h localhost -U postgres mydb > schema.dump

# Dump specific tables
pg_dump -Fc -t users -t posts -h localhost -U postgres mydb > tables.dump

# Restore from custom format dump
pg_restore -d mydb_restored -h localhost -U postgres mydb.dump

# Parallel restore (much faster for large databases)
pg_restore -j 4 -d mydb_restored -h localhost -U postgres mydb.dump

pgBackRest

For production databases, pgBackRest provides incremental backups, parallel backup/restore, backup verification, and cloud storage (S3, GCS, Azure). It's the standard for serious PostgreSQL backup infrastructure.

Recommendations

Daily development: TablePlus (GUI) + pgcli (terminal). Both are fast and don't get in your way.

Migrations: Match your ORM. Drizzle Kit for Drizzle, Prisma Migrate for Prisma, plain SQL with golang-migrate for everything else.

Monitoring: Start with pg_stat_statements (free, built-in). Add pgHero when you want a dashboard. Consider pganalyze for production databases where performance is business-critical.

Administration: pgAdmin for one-off admin tasks. psql for everything scriptable.