Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 4 · Lesson 27 · 4 min read

PostgreSQL Monitoring and Maintenance for DBAs

On this page
  1. A DBA monitoring routine for PostgreSQL
  2. Step 1: Configure server logging
  3. Step 2: pg_stat_activity and other DMV replacements
  4. Step 3: What to alert on
  5. Step 4: Routine maintenance instead of maintenance plans
  6. Check yourself

A DBA monitoring routine for PostgreSQL

Most SQL Server DBAs have a routine: check the error log, look at blocking, glance at job history, check backups, look for growing files. Here's that routine rebuilt for PostgreSQL, with the configuration it needs and the queries to save in DataGrip.

Step 1: Configure server logging

There's no Extended Events or Profiler. The server log, configured in postgresql.conf, captures slow statements, lock waits and connections. A practical starting point:

Config
logging_collector = on
log_destination = 'jsonlog'              # PostgreSQL 15+, easy for log tools to parse
log_min_duration_statement = 500         # log any statement slower than 500 ms
log_lock_waits = on                      # log waits longer than deadlock_timeout (1 s)
log_autovacuum_min_duration = '10s'      # log slow autovacuum runs
log_temp_files = 0                       # log every sort or hash that spills to disk
log_connections = on
log_disconnections = on

Most of these take effect with SELECT pg_reload_conf();. logging_collector needs a restart. log_min_duration_statement is the workhorse. It's like an always-on Extended Events session filtered by duration, with no session to start or stop. On a very busy server, start at a higher threshold and lower it gradually to keep log volume sane.

Step 2: pg_stat_activity and other DMV replacements

These are the DMV replacements. Save each as a query in DataGrip so it's one click away during an incident:

SQL
-- What's running, and for how long (sys.dm_exec_requests)
SELECT pid, usename, datname, state, wait_event_type, wait_event,
       now() - query_start AS running_for, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;

-- Who's blocking whom (blocking chains)
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Sessions left open inside a transaction, which block vacuum
SELECT pid, usename, now() - xact_start AS open_for
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY open_for DESC;

To stop something, SELECT pg_cancel_backend(pid); cancels the current query and keeps the connection. SELECT pg_terminate_backend(pid); ends the session, like KILL.

For the bigger picture:

QuestionView
Which queries cost the most overall?pg_stat_statements (Day 20)
Is autovacuum keeping up?pg_stat_user_tables: n_dead_tup, last_autovacuum
Which indexes are never used?pg_stat_user_indexes: idx_scan = 0
How is a running VACUUM or CREATE INDEX doing?pg_stat_progress_vacuum, pg_stat_progress_create_index
Is WAL archiving working?pg_stat_archiver
Are replicas keeping up?pg_stat_replication

Unused indexes deserve a look after migration. Many SQL Server indexes get copied over by habit, and each one slows writes:

SQL
SELECT schemaname, relname, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Check that the statistics have run long enough to cover monthly jobs before dropping anything. Indexes that back primary keys or unique constraints show zero scans too, but they're still needed.

Step 3: What to alert on

Dashboards are nice. Alerts on these signals prevent outages:

  • Disk space, especially the pg_wal directory. Failed archiving and stuck replication slots both fill it.
  • WAL archiving failures in pg_stat_archiver. A gap breaks point-in-time recovery (Day 23).
  • Inactive replication slots in pg_replication_slots (Day 24).
  • Replication lag in pg_stat_replication.
  • Long transactions and idle-in-transaction sessions, which block vacuum (Day 18).
  • Transaction ID age, age(datfrozenxid). Alert well before a billion.
  • Connections close to max_connections.

The usual tooling reads these same views: Prometheus with postgres_exporter and Grafana, pgwatch, pganalyze, or Datadog. Managed services add their own, such as RDS Performance Insights or Azure's query performance insight. DataGrip isn't a monitoring system. It's where you investigate after an alert.

Step 4: Routine maintenance instead of maintenance plans

What your maintenance plan did on SQL Server, and what replaces each job:

SQL Server maintenance planPostgreSQL
Update statisticsAutovacuum's analyze, plus ANALYZE after big loads
Rebuild or reorganize indexesRarely needed. REINDEX CONCURRENTLY for bloated indexes.
Shrink filesVACUUM FULL or pg_repack, only after large deletes
Integrity checks (DBCC CHECKDB)Data checksums (on by default since PostgreSQL 18), plus the amcheck extension for indexes
Backups and history cleanuppgBackRest or WAL-G retention settings
Agent job historypg_cron job history (cron.job_run_details) or your scheduler

Don't port SQL Server's "rebuild every index every Sunday" job. PostgreSQL B-tree indexes don't need it. Rebuild only indexes you can show are bloated, and use REINDEX CONCURRENTLY so writes continue.

Check yourself

Turn on log_lock_waits and look for lock-wait messages in the log. During a freeze, query pg_stat_activity with pg_blocking_pids() to see who's blocking whom.

log_min_duration_statement = 1000, then reload. It's always on, with no session to start, and each slow statement is logged with its duration.

Don't. PostgreSQL relies on autovacuum, not scheduled rebuilds. Watch for bloated or unused indexes, and rebuild specific ones with REINDEX CONCURRENTLY when the numbers justify it.

pg_stat_activity, which lists every session with its state, current query, start times and wait event. Join it with pg_locks to see who's blocking whom, or use pg_blocking_pids(pid) for a quick answer.

log_min_duration_statement for slow queries, log_lock_waits for lock contention, log_checkpoints, log_autovacuum_min_duration for slow vacuums, and log_temp_files for sorts spilling to disk. Together they explain most performance complaints.