PostgreSQL Monitoring and Maintenance for DBAs
On this page
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:
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 = onMost 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:
-- 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:
| Question | View |
|---|---|
| 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:
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_waldirectory. 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 plan | PostgreSQL |
|---|---|
| Update statistics | Autovacuum's analyze, plus ANALYZE after big loads |
| Rebuild or reorganize indexes | Rarely needed. REINDEX CONCURRENTLY for bloated indexes. |
| Shrink files | VACUUM 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 cleanup | pgBackRest or WAL-G retention settings |
| Agent job history | pg_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.