Postgres Monitoring for SQL Server DBAs: Statistics and Logs 101

As a SQL Server developer and DBA learning Postgres, it’s easy to expect that the information you need for meaningful query and performance tuning will be readily available. For years (decades, maybe) you’ve learned the DMVs, set up Extended Events sessions, relied on Query Store, and regularly run Ola Hallengren’s maintenance scripts and Brent Ozar’s First Responder Kit. Nearly everything you do to find and tune poorly performing queries happens through SQL or through a GUI in SSMS.

Rarely, if ever, do you think about combing through logs to find query performance issues. The error log is where you go when something broke: a failed startup, a corruption message, a login from an IP that shouldn’t exist, a backup that didn’t. It’s an incident destination, not a daily instrument.

Most SQL Server DBAs I talk to have also never had to think hard about log configuration, because there was never a decision to make. Logging is built into the Windows server ecosystem. It just exists, and you get it for free.

It’s no wonder, then, that SQL Server DBAs who are new to Postgres have real confusion about where to find the information they need when there’s a problem. And it’s no wonder so many are shocked when they discover the information isn’t there at all, because Postgres was never configured to record it.

This doesn’t mean Postgres monitoring is worse. In some cases it’s markedly better. Postgres actually gives you significantly more configuration options around what gets tracked and logged. They’re just set conservatively out of the box. Postgres also puts different information in places you wouldn’t expect coming from SQL Server. Unlike SQL Server, the Postgres log isn’t only where you go when something broke. It’s the primary record of what happened, and for several classes of question it’s the only record.

There is no Query Store. There are far fewer DMV equivalents, and the coverage differs in ways that matter. Most notably, Postgres has nothing like sys.dm_exec_query_plan, so there’s no way to go ask the server what plan it used for a query that already finished. Extended Events don’t exist either.

Which means the interesting problem isn’t just getting access to Postgres monitoring data and reading it. It’s that the data gets recorded according to decisions you make in advance.

Consider the following table of day-to-day tasks in managing your databases, and where the information lives in each system.

The question	SQL Server	Postgres
What are my worst queries overall?	Query Store, dm_exec_query_stats	pg_stat_statements
What’s running right now?	dm_exec_requests	pg_stat_activity
How much time have I spent waiting, and on what?	dm_os_wait_stats (cumulative)	sampled pg_stat_activity
Why was this query slow at 3:07am, with what parameters?	Query Store	log
What plan did it actually use in production?	Query Store plan capture	log (auto_explain)
What waited on a lock for 8 seconds?	Blocked Process Report	log (log_lock_waits)
What deadlocked?	system_health XEvents (on by default)	log
Is autovacuum keeping up on this table?	n/a, different model	log (log_autovacuum_min_duration)
Are checkpoints thrashing?	perf counters	log (log_checkpoints)
Which queries spilled to disk?	tempdb DMVs	log (log_temp_files)

Look at the right-hand column. Two rows are queryable views. One requires you to build a sampler. Seven are the log.

In SQL Server, most of your diagnostics are query-time decisions. The engine is already recording; you decide what to ask, and when you need it. Query Store captured that execution whether or not you were thinking about it, and you tune retention after the fact.

In Postgres, whether to record or not is a decision to be made. The log line either got emitted at the moment the event happened, or it didn’t. And a log line that was never emitted is not recoverable by any amount of clever querying afterward.

Everything needed to answer these questions ships with Postgres. But you have to turn it on, and you have to make specific decisions about what to record. Most managed providers help with part of this and not the rest. Amazon RDS, for instance, loads pg_stat_statements by default on PostgreSQL 11, and Aurora does the same going back to PostgreSQL 10. But those same providers leave nearly all of the log_* settings at their conservative defaults, and each one hands you a completely different mechanism for getting at the log files themselves.

So at 3:07am when the support alert goes off, if you haven’t actively enabled log events and query statistics tracking, you’ll have almost no query-level data. You’ll have cumulative counters that tell you totals since the last restart, a handful of errors and deadlocks that Postgres logs regardless, and CPU and disk utilization from your cloud provider or local operations dashboard. None of that tells you when the problem started or which query brought the server to its knees.

As you make the transition from SQL Server to Postgres, especially if you’re planning to self-host, it’s imperative that you understand these settings, the bare minimum to enable, and the kinds of permissions your monitoring tooling will need in order to help you when things go sideways.

Where to find Postgres monitoring data

When it comes to query tuning in Postgres, we saw from the table above that you’ll spend most of your time in one of four places.

• pg_stat_activity: a real-time view of the processes running right now, what queries or background tasks they’re executing, and what each one is currently waiting on. Because it’s instantaneous, it’s always changing, and it’s most helpful for identifying long-running processes and understanding what kind of waits are occurring.
• pg_stat_database, pg_stat_user_tables, and pg_stat_user_indexes: cumulative statistics about the corresponding monitored object, counted since the last reset.
• pg_stat_statements: aggregated, cumulative runtime execution metrics for queries across all databases and users on the server. This tells you, essentially, the total amount of work (number of calls, execution time, planning time, blocks read) since the server restarted or the statistics were reset.
• The logs: long-running statements, queries that spilled to disk, query plans, lock waits, errors, and more. Having easy, searchable access to log data is essential for long-term query optimization.

Even more importantly, the monitoring tools you’ll want to use need access to all of this to help you tune and optimize. So let’s talk about the configuration each one requires.

What the built-in pg_stat views tell you

pg_stat_activity and most of the other pg_stat_* views are installed automatically and readable by any user who can connect to the database. You don’t need to grant SELECT on them. What you do need to think about is visibility: without superuser, pg_monitor, or pg_read_all_stats, a user sees full detail only for their own sessions. Other users’ query text comes back as null.

For the cumulative views, remember that the numbers are counters that go up, and only become meaningful when you record two values at different points in time, and look at the change in value in that time frame. A single reading of the seq_scan counter in pg_stat_user_tables tells you very little. The delta between two readings an hour apart tells you how many sequential scans occurred on that table in that hour.

pg_stat_activity is different, and even though Postgres lists it as part of its “cumulative statistics” it does not actually accumulate anything. This can be one of the bigger surprises coming from SQL Server. pg_stat_activity is a snapshot: it shows you what each backend is doing at the instant you run the query, including the state it’s in and the wait event it’s currently blocked on:

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 state column reports one of a small set of values: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, or disabled. The wait_event_type column groups waits into categories that will feel roughly familiar: Lock for heavyweight lock contention (the one that usually means another session is blocking you), LWLock for internal shared-memory latches, IO for reads and writes, Client for waiting on the network, plus Activity, BufferPin, IPC, Extension, and Timeout.

However, if you look at the result of this query you might wonder: what’s the equivalent of sys.dm_os_wait_stats? Unfortunately, Postgres does not accumulate wait time anywhere in core. SQL Server keeps a running total of time spent in each wait type and hands it to you on request; Postgres tells you what a backend is waiting on in this specific moment, and nothing more. The only way to answer “how much time did we spend waiting on locks yesterday” is to have been sampling pg_stat_activity frequently the entire time and to keep the results.

That’s what monitoring tools do, and why they poll on a tight interval. If you’d rather keep that bookkeeping inside Postgres, the pg_wait_sampling extension samples wait events for you and accumulates them over time.

That said, the nice thing about these views is that they’re available with no installation and no configuration. The data they provide is just a query away.

How to enable pg_stat_statements

When it comes to looking at aggregated query statistics, the view you’ll reach for most often is pg_stat_statements. As mentioned earlier, it’s the closest Postgres analog to Query Store. However, the comparison does break down fairly quickly.

In its current form as of Postgres 18, pg_stat_statements is excellent at what it does within the boundaries it was designed for. (If you want the nitty-gritty details, we have a whole video series digging into its internals.) It tracks dozens of execution metrics for each normalized query, including total time, call count, rows, buffer activity, and planning time. But every one of those metrics is cumulative, so you either have to reset the statistics frequently, or use an external tool that performs a diff of the counter values on a schedule.

With the right tooling to extract data over time, statistics from pg_stat_statements are invaluable for finding your problematic queries and watching them change across deploys.

One thing to be aware of: pg_stat_statements isn’t enabled by default. It’s a contrib module (what Postgres calls an extension) that has to be loaded into shared memory at server start. That means adding it to shared_preload_libraries and performing a full restart, not a config reload. This is the step people miss, and the symptom is confusing, because CREATE EXTENSION will succeed and then every query against the view returns an error.

After the restart, create the extension in each database you want to query it from, then verify:

-- Run once per database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

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

Once it’s tracking, the first question most people ask is which statements consume 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;

Finally, give your monitoring user the visibility it needs without handing out superuser:

CREATE ROLE monitoring LOGIN PASSWORD '<use something better than this>';
GRANT pg_monitor TO monitoring;

pg_monitor is the grant you want here rather than assembling permissions by hand. It’s a predefined role that bundles pg_read_all_stats, pg_read_all_settings, and pg_stat_scan_tables, which together let a non-superuser see full query text in pg_stat_activity and pg_stat_statements, read superuser-only configuration values, and examine table statistics. It’s the closest thing Postgres has to SQL Server’s VIEW SERVER STATE permission.

How to configure Postgres logging

Most of the remaining information you need for real query tuning, and for system-level troubleshooting generally, comes through the logs. As we discussed earlier, if you don’t configure logging well ahead of time, there’s nothing to reap the benefits from later.

In essence, there are three things to consider when configuring Postgres logs.

1. Where do Postgres logs go?

Yes, you have to tell Postgres where you want the logs to actually go. And there are several output formats to choose from. Note that you may only be able to define a subset of these settings, depending on your hosting provider. And I do highly recommend setting this up when you don’t need the logs yet, vs when you realize you should have configured logging to understand an incident that just occurred.

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

By default, log_destination is set to stderr, and this is more literal than it sounds. It means Postgres writes to the postmaster process’s standard error stream, and where that ends up depends entirely on how the server was started. With logging_collector = on, a dedicated background process captures that stream and writes it to files in log_directory. With the collector off, your log output goes wherever your service manager sends stderr, which on most modern Linux distributions means journald. On a hosted platform, stderr typically gets captured by the host and forwarded into something provider-specific: CloudWatch on AWS, Azure Monitor, Cloud Logging on GCP.

There are other destinations besides stderr. You can use csvlog for fixed columns you can load into a table, jsonlog for structured output with real keys (Postgres 15 and later), or syslog to hand everything to the system logger. You can list several at once, like log_destination = 'stderr,jsonlog', if you want text for humans and JSON for a log shipper. Two things to know: csvlog and jsonlog both require logging_collector = on, since they need a file to write to, and tool support for jsonlog is still noticeably thinner than for plain stderr text, so check what your monitoring stack actually parses before you commit to it.

Once you’ve settled on a format, and when using the logging collector, setting log_directory, log_filename, and log_file_mode control where and how the files get created.

The biggest issue with most self-hosted installs is putting logs in a directory the tooling can actually reach. The default log_directory in a source build is log, which is a relative path, meaning it resolves to a subdirectory inside $PGDATA. (Debian and Ubuntu packages helpfully override this to /var/log/postgresql. RHEL-family packages generally don’t.)

Here’s why that default causes trouble. Postgres requires the data directory to be mode 0700, or 0750 if you initialized the cluster with group access enabled, and it refuses to start otherwise. A monitoring agent running as a non-postgres user (which is typical, and correct) needs the execute bit on every directory in the path to reach a file inside. It doesn’t have that on $PGDATA, and you can’t grant it, because loosening those permissions is exactly what makes Postgres refuse to start. The log file itself may have perfectly reasonable permissions. The agent still can’t traverse the directory to get to it.

This is the failure mode that confuses people most, because you can add your agent’s user to the postgres group, confirm the group looks right, and still get permission denied.

The fix is to move the log directory outside $PGDATA and make sure the agent’s user is in the group that owns it:

sudo mkdir -p /var/log/postgresql
sudo chown postgres:postgres /var/log/postgresql
sudo chmod 750 /var/log/postgresql

# Whatever user your monitoring agent runs as
sudo usermod -a -G postgres pganalyze

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

log_file_mode = 0640 gives the owning group read access on newly created files, and group membership is what lets the agent use it. Both halves are required. Also worth knowing: if your agent runs as a service, adding it to a new group usually means restarting that service before the membership takes effect.

If you genuinely can’t move the directory (a vendor image, a compliance rule, a platform you don’t control), there are other routes. You can read log files over the SQL connection using pg_read_file() wrapped in a SECURITY DEFINER helper function, forward everything through syslog, ship logs to an OpenTelemetry endpoint, or read a container’s stdout. Each has its own trade-offs and its own ways to go wrong, and that’s a separate article. At pganalyze we’ve spent a lot of time supporting each of these mechanisms, and you can see how we handle them in our Log Insights docs. For now: get the logs out of the data directory if you possibly can, and always verify access as the agent’s user rather than as root, because that’s the test that actually catches this.

2. What should log_line_prefix include?

Beyond deciding where and how the events get logged, there’s one more adjustment that determines whether those events are usable.

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

The log_line_prefix is the highest-leverage single setting in Postgres logging, because in the text format the prefix is the only place per-line identity can live. There’s no schema. It’s a printf-style string, and whatever you don’t put in it simply doesn’t exist.

By default, the prefix prepends each log line with only the timestamp and the process ID that wrote it. You won’t know which database, user, or application produced the line. Without that, neither you nor your monitoring tool can group anything or spot a trend.

How often do we see that deadlock on database X? Why are there so many more queries over log_min_duration_statement on Monday evenings in database Y? And why does it only happen with user Z?

If you don’t update log_line_prefix, you can’t answer any of those. Here’s what I’d recommend as a starting point:

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

%m is the timestamp with milliseconds, %p is the process ID (the Postgres documentation lists every escape), and then there’s %q, which is the useful trick most people don’t know about. %q emits nothing on its own. It tells non-session processes (the checkpointer, the autovacuum launcher, the startup process) to stop rendering the prefix at that point, while session backends carry on and render the rest. So a client statement gets a useful [user=app,db=orders,app=web-api], and a checkpoint line doesn’t get a useless [user=,db=,app=] tacked onto every entry.

The trailing space isn’t strictly required by Postgres, but it’s strongly recommended, and in practice most log parsers depend on it to separate the prefix from the message. If your monitoring tool suddenly stopped picking up log lines and you recently modified the prefix, check whether that trailing space survived. In my experience, it’s the single most common thing lost when a config gets copy-pasted from documentation.

One word of warning before we move on. There are roughly twenty escape sequences available, and I’ve seen users try something like this:

# Please don't do this
log_line_prefix = '%t %m %n %p %P %l %c %s %v %x %e %i %u %d %a %h %r %b '

While that might feel like insurance against ever needing a field you don’t have, it creates two problems.

First, it adds roughly 150 characters of prefix before the message even starts. At 5,000 log lines per second, which is routine for a chatty application with log_connections enabled, that’s about 65 GB per day of prefix alone, before any message text at all. You pay for that in disk, in shipping bandwidth, and in per-GB ingest fees. And %h or %r combined with log_hostname = on means a reverse DNS lookup in the connection path; the Postgres documentation warns that this “might impose a non-negligible performance penalty.”

Second, and this one is specific to Postgres with no SQL Server equivalent: your prefix has to be a shape your tooling understands. Some tools like pganalyze support dynamic handling of prefixes, but other tools have a fixed set of prefixes that are supported. Confirm your monitoring stack parses your prefix before you accumulate weeks of history trying to work out why your tool can’t show you anything.

3. Which log settings should you turn on?

And finally, what do you actually want logged?

The settings below are worth investigating and setting deliberately so the information you need exists when it matters. It’s fine if these get adjusted over time. Your initial values will often produce too little or too much. Workloads and applications change with them. This is rarely a set-it-and-forget-it situation.

My suggested starting values are provided for situations where you don’t have specific runtime data pushing you in a different direction.

# Statement duration and sampling
log_min_duration_statement = 1000     # ms; log the text of statements at least this slow
log_min_duration_sample = 100         # sample the 100ms-1s band...
log_statement_sample_rate = 0.05      # ...at 5%

# Contention and resource pressure
log_lock_waits = on                   # fires after deadlock_timeout, 1s by default
log_temp_files = 0                    # log every spill to disk

# Background activity
log_checkpoints = on                  # already the default since PG 15
log_autovacuum_min_duration = '60s'   # default is 10min since PG 15

# Connection activity
log_connections = on
log_disconnections = on

# Keep these conservative
log_statement = 'ddl'                 # NOT 'all'
log_error_verbosity = default         # NOT verbose

A note on log_min_duration_statement, since this is the line that does the most work for you: it logs the statement text once the statement finishes and exceeds your threshold. For queries sent through the extended query protocol, which is what most modern drivers and ORMs use, bind parameter values are logged on an accompanying DETAIL line, governed by log_parameter_max_length. On Postgres 13 and later that defaults to logging them in full. It’s worth verifying you’re actually getting parameters, because a slow query without its parameters is much harder to reproduce.

Importantly, resist the instinct to log everything, which is a natural reaction to learning that Postgres records so little by default. Setting log_statement = 'all' or log_duration = on logs every statement regardless of duration. On a busy server that’s gigabytes an hour of noise that drowns out the signal you wanted, and it puts real write pressure on the log volume. Some monitoring tools refuse to work with those settings for exactly that reason. The pganalyze collector, for instance, intentionally skips over log lines produced by log_statement = 'all', log_duration = on, and log_error_verbosity = verbose to allow more relevant lines (e.g. from auto_explain) to be captured without running into limits.

Duration thresholds plus sampling are the better answer. Log everything over a second, then take a small percentage of the band below it. You get visibility into the fast-but-frequent queries without recording all of them.

Being fair to SQL Server

I don’t want to leave the impression that Postgres got this right and SQL Server got it wrong. Having spent a long time in both, there are real things I miss.

Query Store is the big one. It persists query text, compiled plans, and aggregated runtime and wait statistics inside the database, so its persisted history survives restarts and is included in database backups. Enabling it takes a single ALTER DATABASE statement rather than operating-system configuration, although its retention and storage limits still need to be managed. Extended Events provide a structured event stream with versioned schemas and supported readers, rather than requiring consumers to interpret an unstructured text log.

SQL Server’s dynamic management views also expose information that PostgreSQL core does not. You can query the plan cache for the compiled plan of a query that has already run. On SQL Server 2019+, enabling LAST_QUERY_PLAN_STATS can additionally retain the last known actual plan, including runtime operator information, as long as that plan remains cached. PostgreSQL has no built-in equivalent for retrieving such a plan after execution; it must be captured while the query runs, which is why auto_explain and the server log carry that row in the table at the top of this article.

Most of all, a SQL Server DBA can usually answer “why was this slow at 3am” without anyone having thought about instrumentation beforehand. That’s a real design win, and it’s the expectation that Postgres will quietly violate if nobody warns you.

There are places where Postgres comes out ahead, too. Because the log is plain text going somewhere you chose, you can route it to your monitoring vendor, your log platform, and a file on disk simultaneously, without a connector or a license. log_min_duration_statement costs you essentially nothing when nothing is slow. And auto_explain will capture the real plan of a real production execution for a query nobody thought to watch in advance, which in SQL Server would have required a session you set up before the problem happened.

Conclusion

In this article we’ve covered a lot of ground, and I know it can be overwhelming to think through all of this when your main goal is to start using Postgres. At the very least, if you take nothing else from this, start with this small checklist and build from there.

• Enable pg_stat_statements. It needs a shared_preload_libraries entry and a restart, and CREATE EXTENSION in each database. Verify it’s actually tracking before you move on.
• Set log_min_duration_statement. It’s off by default, and it’s the single most valuable line you’ll add to your configuration.
• Fix log_line_prefix. Use '%m [%p] %q[user=%u,db=%d,app=%a] ', trailing space included. The default can’t attribute a log line to a user, database, or application, and no tool can recover what was never written.
• Turn on log_lock_waits, log_temp_files, and autovacuum logging. These have no view or DMV to fall back on. If they aren’t logged, the events are simply gone.
• Get log_directory out of $PGDATA, put your agent’s user in the owning group, and verify access as that user rather than as root.
• Grant pg_monitor to your monitoring role instead of assembling permissions individually or reaching for superuser.

Coming from SQL Server, the surprise isn’t that Postgres records more or records less. It’s that Postgres makes you decide, up front, what you’ll be able to find out later. An afternoon spent on these settings now is the difference between having an answer during your next incident and having a theory.