For SQL Server DBAs, monitoring Postgres means configuring before the incident

An article published by Ryan Booz, an engineer at pganalyze, on Planet PostgreSQL, puts a finger on a common sore spot for teams migrating workloads from SQL Server to Postgres: the assumption that the new database will give you, for free, the same kind of visibility that Query Store, Extended Events, and the First Responder Kit provided in the Microsoft ecosystem. It won't. And the reason isn't a lack of features in Postgres, it's a difference in philosophy that changes the day-to-day work of anyone writing and optimizing queries.

The difference nobody warns you about

In SQL Server, the engine logs by default. Query Store captures plan and execution regardless of whether you thought about it beforehand; you decide later what to ask and how long to retain it. In Postgres it's the opposite: a log line only exists if it was emitted at the moment the event happened, and a line that was never emitted can't be recovered by any clever query after the fact.

This means that if the production alert goes off at 3 a.m. and nobody configured pg_stat_statements and the right log_* settings ahead of time, what's left are cumulative counters since the last restart, some errors and deadlocks that Postgres logs regardless, and CPU/disk metrics from the cloud provider. None of that tells you which specific query brought the server down.

Booz's article builds a table comparing common DBA tasks and where the information lives in each database. It's worth reproducing the essentials, because it's the mental map anyone migrating needs to internalize:

• Worst queries overall: Query Store / dm_exec_query_stats in SQL Server becomes pg_stat_statements in Postgres.
• What's running right now: dm_exec_requests becomes pg_stat_activity.
• Why was this query slow at 3:07 a.m., with which parameters: Query Store becomes the log.
• Which plan was actually used in production: Query Store becomes the log, via the auto_explain extension.
• What got stuck on a lock for 8 seconds: Blocked Process Report becomes the log, via log_lock_waits.
• Is autovacuum keeping up on this table: there's no direct equivalent; it becomes the log, via log_autovacuum_min_duration.
• Are checkpoints thrashing: perf counters become the log, via log_checkpoints.
• Which queries spilled to disk: tempdb DMVs become the log, via log_temp_files.

Of the ten questions, two are answered with queryable views, one requires building a manual sampler, and seven depend on the log. For anyone coming from a world where almost everything is a query-time decision, this inversion is the most costly blind spot of the migration.

What you already get for free: the pg_stat views

pg_stat_activity and the pg_stat_* family are installed automatically and readable by any connected user, with no extra GRANT needed. The catch is visibility: without pg_monitor (or superuser), a user only sees the full text of their own session; the query from other sessions comes back as null.

Another conceptual trap: the counters in pg_stat_user_tables and similar views are cumulative since the last reset. A single reading of seq_scan says little; the delta between two readings an hour apart is what tells the story.

pg_stat_activity, despite being classified as a cumulative statistic, doesn't accumulate anything: it's an instant snapshot of what each process is doing right now, including what kind of wait (wait_event_type) it's stuck on, something like Lock (heavy contention), LWLock (internal latch), IO, or Client. A typical query:

SELECT pid, state, wait_event_type, wait_event, backend_type,
 now() - query_start AS duration, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
 AND state <> 'idle'
ORDER BY duration DESC;

The thing that hurts most for anyone coming from sys.dm_os_wait_stats is that Postgres doesn't accumulate wait time anywhere in core. The only way to answer "how long did we wait on locks yesterday" is to have sampled pg_stat_activity frequently the whole time and stored the results, or to use the pg_wait_sampling extension, which does this for you.

pg_stat_statements: the closest thing to Query Store, but not quite

pg_stat_statements is the view that comes closest to Query Store, and it does a good job within the limits it was designed for: total time, number of calls, rows, buffer activity, and planning time per normalized query. The problem is that every metric there is cumulative, so either you reset frequently or you rely on an external tool that calculates the diff between two collections.

The detail that catches people off guard: pg_stat_statements doesn't come enabled by default. It's a contrib module that needs to be loaded into shared memory at server startup, which requires adding it to shared_preload_libraries and doing a full restart, not a configuration reload. The classic symptom of someone who skips this step: CREATE EXTENSION runs without error, and every query against the view still returns an error anyway.

-- Once per database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Confirm the lib actually loaded
SELECT setting FROM pg_settings WHERE name = 'shared_preload_libraries';

-- Confirm it's tracking
SELECT count(*) FROM pg_stat_statements;

With data flowing, the most common question is which query consumes the most total time:

SELECT calls,
 round(total_exec_time::numeric, 2) AS total_exec_ms,
 round(mean_exec_time::numeric, 2) AS mean_exec_ms,
 rows,
 left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

To give this visibility to a monitoring tool without handing out superuser, the way to go is the pg_monitor role, which bundles pg_read_all_stats, pg_read_all_settings, and pg_stat_scan_tables: it's the closest equivalent Postgres has to SQL Server's VIEW SERVER STATE.

CREATE ROLE monitoring LOGIN PASSWORD '<decent password>';
GRANT pg_monitor TO monitoring;

Managed offerings on RDS or Aurora usually come with pg_stat_statements already loaded by default since older versions (PostgreSQL 11 and 10, respectively, according to the article), but that doesn't hold for the log settings that follow: practically every provider leaves those settings at the conservative default.

Logs: the primary source, not the incident's destination

Here's the most important shift for anyone coming from SQL Server: in Postgres, the log isn't just where you go when something breaks. It's the primary record of what happened, and for several classes of question it's the only record that exists.

Three decisions need to be made before you need them:

Where the logs end up. By default, log_destination is stderr, and that's literal: Postgres writes to the process's standard error stream, and the destination depends on how the server was started. With logging_collector = on, a dedicated process captures that stream and writes it to files:

logging_collector = on
log_destination = 'stderr'
log_directory = '/var/log/postgresql' # outside the data directory
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0640
log_rotation_age = 1d

The most common mistake in self-hosted installations is leaving log_directory as a relative path inside $PGDATA (the default in source builds; Debian/Ubuntu packages already fix this, RHEL-family ones generally don't). Since Postgres requires the data directory to have 0700 (or 0750) permissions and refuses to start if that's loosened, a monitoring agent running as a non-postgres user can't traverse the directory to reach the log file, even if the file itself has the correct permissions. It's the kind of bug that confuses a lot of people because the group looks right and access is still denied.

The fix, according to the article, is to move the log out of the data directory and make sure the agent's user is in the group that owns the directory:

sudo mkdir -p /var/log/postgresql
sudo chown postgres:postgres /var/log/postgresql
sudo chmod 750 /var/log/postgresql
sudo usermod -a -G postgres pganalyze # agent user

ALTER SYSTEM SET log_directory = '/var/log/postgresql';
ALTER SYSTEM SET log_file_mode = '0640';
SELECT pg_reload_conf();

What goes into log_line_prefix. It's the highest-leverage setting in all of Postgres logging: in text format, the prefix is the only place where each line's identity can exist. There's no schema; whatever isn't in the prefix simply doesn't exist. By default only timestamp and PID come through, which makes it impossible to group by database, user, or application. Booz recommends this as a starting point:

log_line_prefix = '%m [%p] %q[user=%u,db=%d,app=%a] '
log_timezone = 'UTC'

%q is the trick few people know about: it tells non-session processes (checkpointer, autovacuum launcher) to stop rendering the prefix from that point on, while session backends keep getting the rest. The result is a client line with a useful [user=app,db=orders,app=web-api] and a checkpoint line without clutter from empty fields.

Which log flags to turn on. This is where the seven table questions with no view live: log_lock_waits (locks above deadlock_timeout), auto_explain (actual plan for slow queries), log_autovacuum_min_duration, log_checkpoints, and log_temp_files. None of them are on by default in a self-hosted install, and most managed providers also leave them off even when loading pg_stat_statements out of the box.

What this changes for builders

If the team is migrating a service from SQL Server to Postgres (self-hosted, RDS, or Aurora), the minimum checklist before going to production is: pg_stat_statements in shared_preload_libraries with a planned restart, a monitoring role with pg_monitor, logs outside the data directory with group permissions tested as the agent's actual user (not as root), log_line_prefix with %q and app/user/database identification, and the five log flags above turned on with thresholds (log_min_duration_statement, deadlock_timeout) calibrated for real traffic volume, not lab defaults.

None of these decisions is costly to make before the incident. All of them become impossible to fix afterward, because the log line that wasn't emitted at 3:07 a.m. doesn't come back. That's the central lesson of Booz's article, and it's what separates a smooth migration from a production audit done in the dark.