Postgres operations

Install

emisar pack install fetches this pack, re-validates it, and verifies its content hash against the --hash below — the exact bytes this page was rendered against, so a tampered copy is rejected — before copying it into the runner's packs dir. The command reloads a running daemon itself; no manual restart.

sudo emisar pack install postgres --hash sha256:ec6f761fd7d750536bab793c92ec91bcf75a24c78c7ee00c4aeca5a057692f2c

• postgres.activity_detail exec low

  pg_stat_activity (per-backend)

  Show per-backend detail: pid, user, app, state, wait_event, query age, query text.

  View source on GitHub
• postgres.activity_states exec low

  Backend counts by state

  Show a quick "how many idle / active / idle-in-transaction" health snapshot.

  View source on GitHub
• postgres.analyze_table exec medium

  ANALYZE <schema>.<table>

  Refreshes planner statistics for one table.

  View source on GitHub
• postgres.backend_holding_xmin exec low

  Backend holding the oldest xmin

  Show the backend whose snapshot is preventing vacuum from cleaning dead tuples cluster-wide.

  View source on GitHub
• postgres.bgwriter_stats exec low

  pg_stat_bgwriter

  Show background writer + checkpoint stats since stats reset.

  View source on GitHub
• postgres.cancel_query exec high

  Cancel a running query

  Sends SIGINT to one backend via `pg_cancel_backend(pid)`. The query aborts but the connection survives. Use to clear a stuck SELECT or a blocker surfaced by `postgres.locks`. If cancel doesn't take effect within seconds the backend is likely stuck in a kernel call — escalate to `kill_idle` (terminate) only as a last resort.

  View source on GitHub
• postgres.connections exec low

  Postgres connection summary

  Counts pg_stat_activity rows grouped by state and application_name. Surfaces idle-in-transaction backends, connection storms, and per-app traffic skew. Read-only. Pair with `postgres.kill_idle` if the long-idle-in-transaction count is non-zero.

  View source on GitHub
• postgres.database_stats exec low

  pg_stat_database

  Show per-DB: commits/rollbacks, blks_read/hit, deadlocks, conflicts, temp file usage.

  View source on GitHub
• postgres.db_sizes exec low

  All databases by size

  List every database with pg_database_size.

  View source on GitHub
• postgres.dead_tuples_top exec low

  Top tables by dead-tuple ratio

  List tables where n_dead_tup / (n_live_tup + n_dead_tup) is high. Bloat suspects.

  View source on GitHub
• postgres.duplicate_indexes exec low

  Duplicate indexes (same column set)

  List indexes covering identical columns. One per group is redundant; drop after verifying.

  View source on GitHub
• postgres.explain_analyze exec high

  EXPLAIN ANALYZE (FORMAT JSON) <query>

  Runs EXPLAIN ANALYZE — the query IS executed (with timing). Forced into a read-only transaction (default_transaction_read_only=on) so data-modifying CTEs and volatile writes are rejected by the server, but the read still runs and can be expensive. High-risk because it executes an arbitrary operator-supplied query on the live database; use explain_query for a plan without execution.

  View source on GitHub
• postgres.explain_query exec low

  EXPLAIN (FORMAT JSON) <query>

  Show the plan-only EXPLAIN. The query is NOT executed.

  View source on GitHub
• postgres.extensions_installed exec low

  Installed extensions

  List currently-loaded extensions with versions + schema.

  View source on GitHub
• postgres.hot_update_ratio exec low

  HOT update ratio per table

  Show HOT update ratio per table. HOT updates avoid index work and bloat. Low ratio on a hot table = missing fillfactor tuning or wrong index.

  View source on GitHub
• postgres.idle_in_transaction exec low

  Idle-in-transaction backends

  List backends sitting in 'idle in transaction' state — they hold locks + bloat vacuum's xmin horizon.

  View source on GitHub
• postgres.index_sizes exec low

  Top 50 indexes by size

  List the largest indexes — candidates for bloat investigation.

  View source on GitHub
• postgres.invalid_indexes exec low

  Invalid indexes (indisvalid = false)

  List indexes from failed CREATE INDEX CONCURRENTLY — present but not used by the planner.

  View source on GitHub
• postgres.is_in_recovery exec low

  pg_is_in_recovery()

  Show whether this instance is a replica (boolean — true if in recovery).

  View source on GitHub
• postgres.kill_idle exec high

  Terminate idle-in-transaction backends

  Calls `pg_terminate_backend(pid)` on every backend that has been in `idle in transaction` state longer than `older_than_seconds`. Frees up the locks they're holding. Application code on the killed connections will see "server closed the connection unexpectedly" and reconnect — any in-flight transaction rolls back. Always check the count via `postgres.connections` first. Do not run during normal traffic.

  View source on GitHub
• postgres.largest_tables_full exec low

  Largest tables with vacuum/dead-tuple info

  List top 50 tables by size with live + dead tuple counts and last vacuum timestamps.

  View source on GitHub
• postgres.last_vacuum_per_table exec low

  Last vacuum/analyze per table

  List tables ordered by oldest last-vacuum — find ones autovacuum hasn't touched.

  View source on GitHub
• postgres.lock_blocking_chains exec low

  Blocker → blocked chains

  List each blocked backend with its blocker. Use to find the head of a stuck lock chain.

  View source on GitHub
• postgres.locks exec low

  Blocking lock graph

  Show who's blocking whom. Joins pg_locks with pg_stat_activity to show blocker_pid → blocked_pid pairs plus the truncated SQL of each side. Read-only. Use before a `cancel_query`; you want to cancel the blocker, not the victim.

  View source on GitHub
• postgres.longest_running_queries exec low

  Top 20 by query age

  List backends in 'active' state, oldest first. Use to spot stuck/runaway work.

  View source on GitHub
• postgres.pg_hba_rules exec low

  pg_hba_file_rules

  List effective pg_hba rules as the server loaded them. Catches syntax errors that didn't make it in.

  View source on GitHub
• postgres.pg_stat_statements_reset exec medium

  pg_stat_statements_reset()

  Clears accumulated pg_stat_statements counters. Use to start a clean measurement window.

  View source on GitHub
• postgres.pg_stat_statements_top exec low

  Top statements by total time

  Show the top 30 normalized queries by total_exec_time. Requires pg_stat_statements extension to be loaded.

  View source on GitHub
• postgres.progress_create_index exec low

  pg_stat_progress_create_index

  Show in-flight CREATE INDEX operations with phase + blocks scanned.

  View source on GitHub
• postgres.progress_vacuum exec low

  pg_stat_progress_vacuum

  Show in-flight VACUUMs with phase + heap_blks_scanned.

  View source on GitHub
• postgres.reindex_concurrent exec high

  REINDEX INDEX CONCURRENTLY <schema>.<index>

  Rebuilds one index without blocking writes. Slower than plain REINDEX but no AccessExclusiveLock.

  View source on GitHub
• postgres.reload_conf exec high

  Reload postgresql.conf

  Calls `pg_reload_conf()`. Re-reads the server config without restarting. Picks up changes to settings whose context is `sighup` (logging, autovacuum, work_mem, etc.); does NOT pick up settings marked `postmaster` (shared_buffers, listen_addresses) — those still require a restart. Safe in steady state but considered high-risk because a malformed config can break logging or reset connection limits.

  View source on GitHub
• postgres.replication_lag exec low

  Replication lag (primary view)

  Show replication slot health from the primary's perspective. Surfaces sent/write/flush/replay LSNs plus the lag in bytes per replica. Run on the primary. Read-only. A lag >10 MB or a stalled flush_lsn is the usual signal that a downstream replica is in trouble.

  View source on GitHub
• postgres.replication_slots exec low

  pg_replication_slots

  List logical + physical replication slots with retained WAL. Inactive slots that retain WAL forever are a disk-full risk.

  View source on GitHub
• postgres.seq_scan_offenders exec low

  Tables with high seq-scan ratio

  List tables where seq_scan / (seq_scan + idx_scan) > 50% AND seq_tup_read > 100k. Candidates for missing indexes.

  View source on GitHub
• postgres.settings_non_default exec low

  pg_settings (non-default)

  List settings the operator has changed from the compiled defaults.

  View source on GitHub
• postgres.slow_queries exec low

  Top slow queries from pg_stat_statements

  List the top N query fingerprints by mean execution time from pg_stat_statements. Requires the extension to be loaded (shared_preload_libraries = 'pg_stat_statements'); errors out cleanly if it isn't. Read-only.

  View source on GitHub
• postgres.ssl_connections exec low

  pg_stat_ssl

  List per-backend TLS state: version, cipher, client_serial.

  View source on GitHub
• postgres.table_io exec low

  pg_statio_user_tables

  Show per-table heap + index buffer reads vs hits. Bad cache hit rate? Find the table.

  View source on GitHub
• postgres.table_sizes exec low

  Top tables by total size

  List the top N tables by total size (heap + indexes + toast) for one schema. Use to find the table that's dominating disk before recommending vacuum, archive, or partitioning. Read-only.

  View source on GitHub
• postgres.terminate_backend exec high

  pg_terminate_backend(pid)

  Hard-disconnects one backend (SIGTERM). Use when pg_cancel_backend isn't enough (e.g., idle in transaction with a long held lock).

  View source on GitHub
• postgres.unused_indexes exec low

  Unused indexes (idx_scan = 0)

  List indexes never used since last stats reset. Drop candidates — but verify they're not for an unrelated path (e.g., uniqueness constraint).

  View source on GitHub
• postgres.uptime exec low

  Postgres uptime and version

  Show server uptime, version, and current connection count. Reads pg_stat_database + pg_postmaster_start_time(). Use as a first-touch sanity check before deeper diagnosis. Read-only.

  View source on GitHub
• postgres.vacuum_status exec low

  Autovacuum + bloat snapshot

  Show last-vacuum/last-autovacuum timestamps and dead-tuple counts for the top N tables in one schema, ordered by dead tuples. Use to decide whether to run VACUUM manually or tune autovacuum. Read-only.

  View source on GitHub
• postgres.vacuum_table exec high

  VACUUM <schema>.<table>

  Reclaims dead-tuple space in one table. Non-blocking (ShareUpdateExclusiveLock). Use VACUUM ANALYZE if planner stats are also stale.

  View source on GitHub
• postgres.wal_archive_status exec low

  pg_stat_archiver

  Show WAL archiver stats: archived/failed counts, last archived WAL, last failure.

  View source on GitHub
• postgres.wal_status exec low

  Current WAL LSN + recovery state

  Show a snapshot of current WAL LSN, last receive/replay LSNs, recovery state.

  View source on GitHub
• postgres.xid_wraparound_proximity exec low

  How close are we to XID wraparound?

  Show per-database age(datfrozenxid). 2^31 (~2.1B) is the wraparound limit. >1B = pay attention; >1.8B = emergency.

  View source on GitHub