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). On connect, the client reads current_setting('server_version_num') and picks SQL accordingly:
  • pg_stat_activity: backend_type is selected only on PG10+; older servers use NULL for that column.
  • pg_stat_statements: PG13+ uses total_exec_time / mean_exec_time; PG12 and older use total_time / mean_time (still exposed in the UI as total_exec_time / mean_exec_time via aliases).
• Optional: pg_stat_statements extension (for the Top Queries panel)

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Installation

npm install

If you install better-sqlite3 for optional local snapshot history, you can use --sqlite (requires a native build).

Development

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.

Running

npm start

or

node src/index.js

After a global install: pgpulse (see package.json bin).

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 npm start

node src/index.js --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). 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