pgpulse

A terminal-based live monitoring tool for PostgreSQL (similar to top / htop). It periodically reads pg_stat_activity, pg_stat_statements, locks, and table/index summaries with low overhead; for pg_stat_statements it diffs against the previous snapshot and keeps a 5-slot ring buffer (default 2s interval) for the last 10-second activity window.

Example (ASCII)

The TUI is a fixed grid: each box is fed by a specific catalog / view (or a join built on them). Roughly:

Screen region	PostgreSQL source	What you are looking at
Live queries (top-left)	pg_stat_activity	One row per backend on the current DB: pid, user, age, state, truncated query, wait info.
Top queries (top-right)	pg_stat_statements (extension)	Aggregated statement stats: calls, total/mean time, rows, buffer hits — sortable; d toggles cumulative vs last 10s delta window.
Locks / blocking (middle)	pg_locks + pg_stat_activity	Blocked vs blocking pid, wait time, relation, snippet of the blocked query.
Tables / indexes (strip)	pg_stat_user_tables + pg_stat_user_indexes	High seq_scan tables; unused index candidates (idx_scan = 0 among top candidates).
Footer	—	Global keybindings (q, r, s, /, …).

Typical layout (colors omitted; narrow terminals may wrap lines):

  [ top-left: live sessions ]          [ top-right: statement stats ]
┌─ live queries (pg_stat_activity) ────────┐┌─ top queries [cum sort:total_exec_time] ─┐
│ 1842  app     1.2s    active   SELECT …  ││ calls 12400 total 890.1ms mean 0.07ms | …  │
│ 1845  app     12.4s   active   UPDATE …   ││ calls 2100  total 120.5ms mean 0.06ms | …  │
│ 1901  report  0.3s    idle     COMMIT …   ││ calls 980   total 45.2ms  mean 0.05ms | …  │
│ ...                                       ││ ...                                          │
└──────────────────────────────────────────┘└──────────────────────────────────────────────┘
  [ middle: who blocks whom ]
┌─ locks / blocking ────────────────────────────────────────────────────────────────────────┐
│ 1842 ← 1903  wait  3.2s  public.orders  │  SELECT * FROM orders WHERE …                 │
│ (no blocking pairs)                                                                      │
└──────────────────────────────────────────────────────────────────────────────────────────┘
  [ bottom strip: table + index health snapshot ]
┌─ tables / indexes ────────────────────────────────────────────────────────────────────────┐
│ tables  public.orders:seq=12000  public.users:seq=800 ...                                  │
│ unused idx  public.idx_foo_created(idx_scan=0)  ...                                      │
└──────────────────────────────────────────────────────────────────────────────────────────┘
 q quit  r resetΔ  s sort(tot)  d delta(cum)  / filter  l locked  i idle-tx  tab focus …

Requirements

• Node.js 18+ (LTS recommended)
• PostgreSQL 10+ recommended (earlier versions are untested)

PostgreSQL version compatibility

On connect, pgpulse reads current_setting('server_version_num') and picks SQL and feature flags accordingly (see src/utils/version.js, src/utils/sql.js, src/collectors/pgServerStats.js).

Area	Behavior
pg_stat_activity.backend_type	Selected only on PG 10+. On older servers the query omits this column (compatible with pre-10 catalogs).
pg_stat_statements (Top queries panel)	PG 13+: total_exec_time / mean_exec_time. PG 12 and older: total_time / mean_time, exposed in the UI under the same labels via aliases. Requires the extension (not built-in).
Footer: checkpoint / writer stats	PG 16 and older: pg_stat_bgwriter. PG 17+: pg_stat_checkpointer (checkpoint-related stats moved; pg_stat_bgwriter no longer used for this hint).

Install the extension on the database if you want the Top queries panel:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Installation

Install globally so the pgpulse command is available on your PATH:

npm i -g pgpulse

Then run:

pgpulse

If you install better-sqlite3 for optional local snapshot history, you can use --sqlite (requires a native build; typically when developing from a clone with optional deps installed).

Documentation (web)

A static single-page overview lives at docs/index.html (open locally or publish with GitHub Pages).

Development

Clone the repository, install dependencies, and run locally:

git clone <repo-url> && cd pgpulse
npm install
npm start

Unit tests use Vitest. Test files live next to sources: src/**/*.test.js.

npm test          # run once
npm run test:watch   # watch mode

Configuration: vitest.config.js (environment: node, include: src/**/*.test.js). Pure helpers (utils, engine/delta, SQL builders) are covered; TUI and live DB access are not.

Connection

Precedence order:

1. DATABASE_URL
2. --connection-string
3. PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE and optional flags: --host, --port, --user, --password, --database

Examples:

DATABASE_URL=postgres://user:pass@localhost:5432/mydb pgpulse

pgpulse --host localhost --port 5432 --user postgres --database app

Options

Flag	Description
--interval <ms>	Refresh interval (default 2000)
--long-threshold <s>	Long-query threshold; yellow/red highlighting (default 5)
--export <file>	Default path for export when using e
--sqlite <file>	Optional SQLite snapshot history
-h, --help	Help

Keyboard

Key	Action
q	Quit
Tab	Focus panels (activity / statements / locks)
r	Reset delta baseline and 10s window
s	Sort: total_exec_time → mean_exec_time → calls
d	Toggle cumulative vs last 10s delta window
/	Text filter (Enter to apply, Esc to cancel)
l	Show only backends involved in locks
i	Show only idle in transaction
Enter	Full query text for selection (activity / statements)
t	Table analysis: pick a user table (high seq_scan sample), then per-table index list + heap/cache stats
x	EXPLAIN on selected statement (confirm)
k	pg_terminate_backend (confirm)
e	Export JSON (--export or timestamped file)

Architecture (summary)

• src/collectors/*: Each runs focused/optimized SQL and returns normalized JSON.
• src/engine/snapshot.js: Merges all collector output.
• src/engine/delta.js: pg_stat_statements deltas keyed by userid:dbid:queryid plus a 10s ring.
• src/ui/layout.js: neo-blessed grid, modals, global shortcuts.
• t table analysis: list of hot tables (pg_stat_user_tables + pg_statio_user_tables + size); Enter loads that table’s indexes from pg_stat_user_indexes on demand.
• Footer (2nd line): PostgreSQL server snapshot (via SQL): buffer cache hit % (pg_stat_database blks_hit/read), sessions vs max_connections, shared_buffers / work_mem (from pg_settings), deadlocks, temp_files, checkpoint pressure hint (pg_stat_bgwriter on PG ≤16, pg_stat_checkpointer on PG 17+). This is not the OS process CPU% or total server RAM — those require OS/agent metrics outside PostgreSQL.

Security notes

• EXPLAIN and pg_terminate_backend require sufficient privileges; use carefully in production.
• Long query text is truncated in the UI; the modal shows the full text.

License

MIT