{"id":111626,"date":"2026-08-03T12:00:00","date_gmt":"2026-08-03T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111626"},"modified":"2026-07-14T12:32:05","modified_gmt":"2026-07-14T12:32:05","slug":"how-to-build-an-oracle-awr-equivalent-diagnostics-stack-in-postgresql-step-by-step-guide-with-code","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/how-to-build-an-oracle-awr-equivalent-diagnostics-stack-in-postgresql-step-by-step-guide-with-code\/","title":{"rendered":"How to build an Oracle AWR-equivalent diagnostics stack in PostgreSQL (step-by-step guide with code)"},"content":{"rendered":"\n<p><strong>Moving from Oracle to PostgreSQL means losing one of Oracle&#8217;s most-loved diagnostic tools: the Automatic Workload Repository (AWR). The good news? Most of AWR&#8217;s core capabilities &#8211; snapshot history, wait event sampling, Top SQL analysis, and buffer cache inspection &#8211; have direct, open-source equivalents in PostgreSQL. <\/strong><\/p>\n\n\n\n<p><strong>This guide translates Oracle AWR concepts into practical PostgreSQL diagnostics using extensions like pg_profile, pg_wait_sampling, and pg_stat_statements &#8211; complete with runnable SQL you can apply to your own environment today.<\/strong><\/p>\n\n\n\n<p id=\"h-as-a-multi-platform-database-specialist-it-s-safe-to-say-that-if-there-s-one-feature-from-oracle-i-would-like-to-migrate-to-other-databases-it-would-be-the-automatic-workload-repository-awr\">As a multi-platform database specialist it\u2019s safe to say that, if there\u2019s one feature from Oracle I would like to migrate to other databases, it would be the <a href=\"https:\/\/docs.oracle.com\/en-us\/iaas\/performance-hub\/doc\/awr-report-ui.html\" target=\"_blank\" rel=\"noreferrer noopener\">Automatic Workload Repository (AWR)<\/a>.<\/p>\n\n\n\n<p>Introduced in version 10.2.0.3, it\u2019s been welcome with open arms as it includes the highly valued <a href=\"https:\/\/docs.oracle.com\/cd\/E14981-01\/wli\/docs1031\/dbtuning\/statsApdx.html\" target=\"_blank\" rel=\"noreferrer noopener\">STATSPACK<\/a> performance tuning tool, as well as several other enhancements. These include its own background processes, memory allocation, and a configurable data repository.&nbsp;<\/p>\n\n\n\n<p>Also included with the AWR is <a href=\"https:\/\/oracle-base.com\/articles\/10g\/active-session-history\" target=\"_blank\" rel=\"noreferrer noopener\">Active Session History (ASH)<\/a>, the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/tdppt\/automatic-database-performance-monitoring.html\" target=\"_blank\" rel=\"noreferrer noopener\">Automatic Database Diagnostic Monitor (ADDM)<\/a>, and a group of out-of-the-box reports available to the user.<\/p>\n\n\n\n<p>In this guide, based on one of my previous presentations, I\u2019ll demonstrate how to translate some of the concepts from the Oracle AWR into <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/how-to-troubleshoot-and-fix-postgresql-performance-issues\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL performance diagnostics<\/a>. I\u2019ve turned much of it into runnable SQL and configuration steps for the database enthusiast.<\/p>\n\n\n\n<p>Think of this like a cookbook, where every recipe can be applied directly to your PostgreSQL environment to build a persistent performance repository and replicate the most valuable Oracle AWR capabilities.<\/p>\n\n\n\n<p>Before we jump in, just remember that <strong>every database workload is unique!<\/strong> Test all queries for the most optimal implementation &#8211; and <em>never<\/em> simply run in a production environment.<\/p>\n\n\n\n<p><em>This cookbook is<\/em> <em>based on Kellyn Gorman&#8217;s presentation &#8220;Bridging Oracle&#8217;s Diagnostics Power with PostgreSQL&#8217;s Native Performance Views&#8221;<\/em>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-who-is-this-guide-for\">Who is this guide for?<\/h2>\n\n\n\n<p>This guide is for:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>PostgreSQL DBAs building who want to know more about environment performance<br><br><\/li>\n\n\n\n<li>Platform engineers designing observability stacks<br><br><\/li>\n\n\n\n<li>Architects comparing database total cost of ownership<br><br><\/li>\n\n\n\n<li>Anyone who has said &#8220;I miss AWR&#8221; after moving to PostgreSQL<\/li>\n<\/ul>\n<\/div>\n\n\n<div id=\"callout-block_0268f81db37d12742f71c432dcab035b\" class=\"callout alignnone\">\n    <div class=\"child-last:mb-0 child-first:mt-0 bg-gray-50 dark:bg-gray-950 p-4xl my-3xl\">\n\n<p><strong>Did you know?<br><\/strong><code>pg_stat_*<\/code> views cover ~70% of AWR use cases natively, out of the box. The remaining gaps are filled by a small set of open-source extensions, all at zero licensing cost.<\/p>\n\n<\/div>\n<\/div> \n\n\n<h2 class=\"wp-block-heading\" id=\"h-feature-comparison-oracle-awr-vs-postgresql\">Feature comparison: Oracle AWR vs. PostgreSQL <\/h2>\n\n\n\n<p>The table below maps major AWR capabilities to their <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL<\/a> equivalent and flags where custom work is needed. There are considerable advances in PostgreSQL extensions in recent years, all of which provide valuable performance data for the database specialist. <\/p>\n\n\n\n<p><em>Although this table is detailed, it\u2019s not exhaustive. Plus, new extensions become available on a regular basis.<\/em><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Capability<\/strong><\/td><td><strong>Oracle AWR<\/strong><\/td><td><strong>PostgreSQL<\/strong><\/td><td><strong>Delta<\/strong><\/td><\/tr><tr><td>Snapshot-based history<\/td><td>\u2713 Native (AWR)<\/td><td>\u2713 pg_profile ext<\/td><td>Extension required<\/td><\/tr><tr><td>Configurable retention<\/td><td>\u2713 1-50,000 snaps<\/td><td>\u2713 Configurable<\/td><td>Feature parity<\/td><\/tr><tr><td>Named baselines<\/td><td>\u2713 Fixed &amp; moving<\/td><td>\u2717 Manual build<\/td><td>Custom SQL needed<\/td><\/tr><tr><td>Wait event detail (sampled)<\/td><td>\u2713 1,200+ events<\/td><td>\u2713 pg_wait_sampling<\/td><td>Extension + sampling<\/td><\/tr><tr><td>SQL elapsed time ranking<\/td><td>\u2713 AWR Top SQL<\/td><td>\u2713 pg_stat_statements<\/td><td>No plan per exec<\/td><\/tr><tr><td>Per-execution plan capture<\/td><td>\u2713 AWR SQL Plan<\/td><td>\u2713 pg_store_plans<\/td><td>Extension required<\/td><\/tr><tr><td>Segment-level I\/O stats<\/td><td>\u2713 v$segment_statistics<\/td><td>\u2713 pg_statio_*<\/td><td>Good parity<\/td><\/tr><tr><td>Buffer cache inspection<\/td><td>\u2713 v$bh, x$bh<\/td><td>\u2713 pg_buffercache<\/td><td>Extension required<\/td><\/tr><tr><td>Latch \/ mutex contention<\/td><td>\u2713 v$latch detail<\/td><td>\u25b3 Limited visibility<\/td><td>No latch equiv<\/td><\/tr><tr><td>Blocking \/ lock waits<\/td><td>\u2713 v$session history<\/td><td>\u2713 pg_locks + ASH-like<\/td><td>Needs sampling ext<\/td><\/tr><tr><td>Time-model statistics<\/td><td>\u2713 v$sess_time_model<\/td><td>\u2717 Not native<\/td><td>Custom build<\/td><\/tr><tr><td>Compare Periods report<\/td><td>\u2713 AWRDiff<\/td><td>\u2717 Manual SQL<\/td><td>No equivalent<\/td><\/tr><tr><td>OS \/ system metrics<\/td><td>\u2713 Included in AWR<\/td><td>\u25b3 External (Prometheus)<\/td><td>Agent required<\/td><\/tr><tr><td>Zero licensing cost<\/td><td>\u2717 Diagnostics Pack ~$7.8k\/DB<\/td><td>\u2713 Fully open source<\/td><td>PG wins here<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-why-one-is-not-simply-better-than-the-other\">Why one is not simply &#8216;better&#8217; than the other<\/h3>\n\n\n\n<p>It\u2019s essential to not view one as \u2018better\u2019 than the other \u2013 they\u2019re just different. Where Oracle has 40 years of enterprise and expensive features built-in, PostgreSQL is open-source and lightweight. What\u2019s added to building out similar functionality to Oracle\u2019s AWR should be expected &#8211; and may require additional work.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-postgresql-awr-equivalent-extension-stack-what-you-need-to-get-started\">The PostgreSQL AWR-equivalent extension stack (what you need to get started)<\/h2>\n\n\n\n<p>A majority of the work will be covered with <a href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstatstatements.html\" target=\"_blank\" rel=\"noreferrer noopener\">pg_stat_statements<\/a> and <a href=\"https:\/\/pigsty.io\/ext\/e\/pg_profile\/\" target=\"_blank\" rel=\"noreferrer noopener\">pg_profile<\/a>, so you\u2019ll need to install these if you haven\u2019t already. Add any others, incrementally, as needed.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Top SQL workload analysis in PostgreSQL: pg_stat_statements<\/h3>\n\n\n\n<p><strong>Understanding resource consumption by top SQL is a requirement for any database specialist.&nbsp;The top SQL workload analysis is built into PostgreSQL\u2019s core and simply requires us to query the information and present it in an easily readable format.<\/strong> <\/p>\n\n\n\n<p>The query below tracks cumulative execution statistics for every unique query fingerprint.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Step 1: Add to postgresql.conf\nshared_preload_libraries = 'pg_stat_statements'\npg_stat_statements.track = all          -- track all statements including nested\npg_stat_statements.max = 10000          -- max unique queries tracked\n \n-- Step 2: Restart PostgreSQL, then create extension\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;\n \n-- Step 3: Verify\nSELECT count(*) FROM pg_stat_statements;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-how-to-receive-awr-equivalent-snapshots-in-postgresql-pg-profile\">How to receive AWR-equivalent snapshots in PostgreSQL: pg_profile<\/h3>\n\n\n\n<p><strong>The extension <code>pg_profile<\/code> is the closest open-source equivalent to Oracle\u2019s AWR for PostgreSQL. <\/strong><\/p>\n\n\n\n<p>It captures periodic deltas of all <code>pg_stat_*<\/code> views into a set of persistent tables, then generates HTML reports comparable to an AWR report. We can then use these to provide like-for-like results.<\/p>\n\n\n\n<p>It&#8217;s available at <strong>github.com\/zubkov-andrei\/pg_profile<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Install pg_profile (requires pg_stat_statements already enabled)\nCREATE EXTENSION IF NOT EXISTS pg_profile;\n \n-- Verify the snapshot infrastructure was created\nSELECT * FROM profile.servers;    -- should show 'local' server<\/pre><\/div>\n\n\n\n<p><strong>Important note<\/strong>: Always install <code>pg_stat_statements<\/code> before <code>pg_profile<\/code>, since the former needs to be installed first.<\/p>\n\n\n\n<section id=\"my-first-block-block_87587caa704ee821b211e749c5850099\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Get started with PostgreSQL &#8211; free book download<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8216;Introduction to PostgreSQL for the data professional&#8217;, written by Grant Fritchey and Ryan Booz, covers all the basics of how to get started with PostgreSQL.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/hub\/books\/introduction-to-postgresql-for-the-data-professional\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Download your free copy: Get started with PostgreSQL - free book download\">Download your free copy<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-using-pg-wait-sampling-for-ash-wait-event-history-in-postgresql\">Using pg_wait_sampling for ASH\/wait event history in PostgreSQL<\/h3>\n\n\n\n<p><strong><code>pg_wait_sampling<\/code> is the PostgreSQL equivalent of Oracle&#8217;s ASH. <\/strong><\/p>\n\n\n\n<p>Along with the snapshot data retained in the AWR, samples of session information is also retained, this time in Active Session History (ASH).&nbsp;By default, only one out of every ten samples is collected in the long-term AWR history, but this data creates valuable information on average active session (AAS) that a DBA can use to understand how sessions behave over time.<\/p>\n\n\n\n<p>Active Session information from <code>pg_wait_sampling<\/code> provides sample wait events at a configurable interval (default 10ms) and stores them in a ring buffer and persistent history table.<\/p>\n\n\n\n<p>It&#8217;s available at <strong>github.com\/postgrespro\/pg_wait_sampling<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Add to postgresql.conf and restart\nshared_preload_libraries = 'pg_stat_statements, pg_wait_sampling'\npg_wait_sampling.history_size = 10000   -- ring buffer size\npg_wait_sampling.profile_period = 10    -- sample every 10 ms\n \n-- After restart, create extension\nCREATE EXTENSION IF NOT EXISTS pg_wait_sampling;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-pg-buffercache-buffer-pool-segment-inspection-in-postgresql\">pg_buffercache (buffer pool\/segment inspection in PostgreSQL)<\/h3>\n\n\n\n<p><strong>The <code>pg_buffercache<\/code> extension is the PostgreSQL equivalent to Oracle&#8217;s <code>v$bh<\/code> view and buffer cache segment analysis.<\/strong><\/p>\n\n\n\n<p>The <code>pg_buffercache<\/code> extension exposes the contents of the <code>shared_buffers<\/code> pool so you can identify which relations are consuming the most cache.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE EXTENSION IF NOT EXISTS pg_buffercache;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-pg-store-plans-for-execution-plan-history-in-postgresql\">pg_store_plans for execution plan history in PostgreSQL<\/h3>\n\n\n\n<p><strong>The extension for <code>pg_store_plans<\/code> is incredibly valuable to PostgreSQL.<\/strong><\/p>\n\n\n\n<p>It explores a plan per (<code>queryid<\/code>, <code>planid<\/code>) pair, enabling you to detect plan regressions similar to Oracle\u2019s SQL Plan baseline capture (part of the Oracle AWR).<\/p>\n\n\n\n<p>You can download it at <strong>github.com\/ossc-db\/pg_store_plans<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">shared_preload_libraries = 'pg_stat_statements, pg_wait_sampling, pg_store_plans'\n \n-- After restart\nCREATE EXTENSION IF NOT EXISTS pg_store_plans;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-postgresql-slow-query-plan-logging-with-auto-explain\">PostgreSQL slow query plan logging with auto_explain<\/h3>\n\n\n\n<p><strong>You can use <code>auto_explain<\/code> to logs execution plans for any queries exceeding a time threshold.&nbsp; <\/strong><\/p>\n\n\n\n<p>This is the PostgreSQL feature equivalent (and is similar to) enabling Oracle trace event 10053.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">shared_preload_libraries = '..., auto_explain'\nauto_explain.log_min_duration = '1s'     -- log plans for queries &gt; 1 second\nauto_explain.log_analyze = on            -- include actual row counts\nauto_explain.log_buffers = on            -- include buffer usage\nauto_explain.log_nested_statements = on  -- include subquery plans<\/pre><\/div>\n\n\n\n<p>You must enable <code>track_io_timing = on<\/code> in postgresql.conf to get I\/O timing data in <code>pg_stat_statements<\/code> and <code>pg_stat_io<\/code>. Without it, <code>read_time<\/code> and <code>write_time<\/code> columns stay at zero.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-build-the-snapshot-repository-in-postgresql-with-pg-profile\">How to build the snapshot repository in PostgreSQL with pg_profile<\/h2>\n\n\n\n<p><strong>Having performance data history is one of the most valuable features of the AWR. By default, 8 days is retained, but most production databases extend this retention to 30-60 days. With <code>pg_profile<\/code>, we\u2019re able to handle the snapshot capture, delta calculation, retention, and HTML report generation automatically. <\/strong><\/p>\n\n\n\n<p>Our main challenge is to retain the information over an extended period of time or when a reset is issued. Introducing <code>pg_profile<\/code> assists in addressing this challenge &#8211; however, it doesn\u2019t solve it completely.<\/p>\n\n\n\n<p>The recommended setup of <code>pg_profile<\/code> is as follows:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-install-and-configure\">Install and configure<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE EXTENSION IF NOT EXISTS pg_stat_statements;\nCREATE EXTENSION IF NOT EXISTS pg_profile;\n \n-- Review default retention (7 days)\nSELECT * FROM profile.get_connstr('local');\n \n-- Extend retention to 14 days\nSELECT profile.set_server_option('local', 'retention', '14');<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-take-snapshots\">Take snapshots<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Manual snapshot (run any time)\nSELECT profile.take_sample();\n \n-- List available snapshots\nSELECT sample_id, sample_time FROM profile.samples ORDER BY sample_id;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-schedule-snapshots-with-pg-cron\">Schedule snapshots with pg_cron<\/h3>\n\n\n\n<p>Install <code>pg_cron<\/code> from your OS package manager and add it to <code>shared_preload_libraries<\/code> before using the schedule below:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Schedule a snapshot every 30 minutes\nSELECT cron.schedule('pg_profile_snap', '*\/30 * * * *',\n  $$SELECT profile.take_sample()$$);\n \n-- Verify the cron job was created\nSELECT * FROM cron.job WHERE jobname = 'pg_profile_snap';<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-generate-an-awr-style-html-report\">Generate an AWR-style HTML report<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- List available snapshot IDs first\nSELECT sample_id, sample_time FROM profile.samples ORDER BY sample_id DESC LIMIT 20;\n \n-- Generate HTML report between snapshot 1 and snapshot 10\n-- Save the output to a file via psql \\o or application layer\nSELECT profile.get_report(1, 10);\n \n-- Generate a text report instead\nSELECT profile.get_report(1, 10, 'text');<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-navigate-a-pg-profile-report-and-what-the-report-provides\">How to navigate a pg_profile report (and what the report provides)<\/h2>\n\n\n\n<p><strong>A <code>pg_profile<\/code> report contains high-level information about the PostgreSQL server &#8211; including stats on the cluster, database and SQL, then schema objects, functions and settings.<\/strong><\/p>\n\n\n\n<p>Each of these section headers, shown in the image below, are also links to connect to the section in the report they refer to:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"533\" height=\"378\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-28.png\" alt=\"an image showing a pg_profile report in PostgreSQL - in particular, the table of contents of an example report.\" class=\"wp-image-111632\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-28.png 533w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-28-300x213.png 300w\" sizes=\"auto, (max-width: 533px) 100vw, 533px\" \/><figcaption class=\"wp-element-caption\"><em>A <code>pg_profile<\/code> report in PostgreSQL<\/em> <em>&#8211; here, we see the table of contents<\/em>.<\/figcaption><\/figure>\n\n\n\n<p>The <strong>database statistics section<\/strong> highlights important information for all databases in the cluster, including the number of commits, rollbacks, Cache hit%, rate of change, and more.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"975\" height=\"89\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-29.png\" alt=\"an image showing the database statistics section of a pg_profile report in PostgreSQL.\" class=\"wp-image-111633\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-29.png 975w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-29-300x27.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-29-768x70.png 768w\" sizes=\"auto, (max-width: 975px) 100vw, 975px\" \/><figcaption class=\"wp-element-caption\"><em>The database statistics section of a <code>pg_profile<\/code> report<\/em> <em>in PostgreSQL<\/em>.<\/figcaption><\/figure>\n\n\n\n<p>The <strong>&#8216;Top SQL&#8217; section<\/strong> contains information that is easily recognizable to Oracle DBAs. This includes the <code>queryID<\/code>, which database in the cluster the query belongs to, elapsed time, number of rows, calls, and the min and max time.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"975\" height=\"78\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-30.png\" alt=\"image showing the 'top SQL by elapsed time' section of a pg_profile report in PostgreSQL.\" class=\"wp-image-111634\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-30.png 975w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-30-300x24.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2026\/06\/image-30-768x61.png 768w\" sizes=\"auto, (max-width: 975px) 100vw, 975px\" \/><figcaption class=\"wp-element-caption\"><em>The &#8216;top SQL by elapsed time&#8217; section of a <code>pg_profile<\/code> report in PostgreSQL<\/em>.<\/figcaption><\/figure>\n\n\n\n<p>Full SQL text is displayed for each of the query IDs, and then the data is presented in various orders&nbsp;&#8211; e.g by planning time, execution time, I\/O wait time, gets, temp usage, just in time(JIT) activity, and WAL size. This is similar to an Oracle AWR report &#8211; just, in this case, we&#8217;re getting information vital to the management of a PostgreSQL database.<\/p>\n\n\n\n<p>The report concludes with vacuum, index usage, and settings information, along with links to take the reader back to the top of the report. Here, the links navigate to different sections, making the process seamless for users to explore the report once they&#8217;ve identified an issue.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-create-your-own-repository-in-postgresql\">How to create your own repository in PostgreSQL<\/h2>\n\n\n\n<p><strong><code>pg_profile<\/code> should <em>always<\/em> be your first option for performance data in PostgreSQL. <\/strong><\/p>\n\n\n\n<p>However, if for some reason you can&#8217;t install <code>pg_profile<\/code>, don&#8217;t worry &#8211; there are other ways to create a minimal snapshot table. <\/p>\n\n\n\n<p>Here, I&#8217;ll show you how to build one manually that can be used to capture deltas with a scheduled procedure.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-how-to-create-the-snapshot-table\">How to create the snapshot table<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Minimal snapshot table (stores point-in-time copies of pg_stat_statements)\nCREATE TABLE IF NOT EXISTS perf_snapshots (\n  snap_id          BIGSERIAL PRIMARY KEY,\n  snap_time        TIMESTAMPTZ NOT NULL DEFAULT now(),\n  queryid          BIGINT,\n  query            TEXT,\n  calls            BIGINT,\n  total_exec_time  DOUBLE PRECISION,\n  shared_blks_hit  BIGINT,\n  shared_blks_read BIGINT,\n  rows             BIGINT\n);\n \nCREATE INDEX ON perf_snapshots (snap_time);\nCREATE INDEX ON perf_snapshots (queryid, snap_time);<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-the-capture-procedure\">The capture procedure<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE OR REPLACE PROCEDURE capture_perf_snapshot()\nLANGUAGE sql AS $$\n  INSERT INTO perf_snapshots\n    (snap_time, queryid, query, calls, total_exec_time,\n     shared_blks_hit, shared_blks_read, rows)\n  SELECT now(), queryid, query, calls, total_exec_time,\n         shared_blks_hit, shared_blks_read, rows\n  FROM pg_stat_statements;\n$$;\n \n-- Schedule via pg_cron\nSELECT cron.schedule('perf_snap', '*\/30 * * * *',\n  $$CALL capture_perf_snapshot()$$);<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-the-delta-report-between-two-snapshot-times\">The delta report between two snapshot times<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Identify two snapshot times to compare\nSELECT DISTINCT snap_time FROM perf_snapshots ORDER BY snap_time DESC LIMIT 20;\n \n-- Delta report: new load between :start_ts and :end_ts\nWITH s1 AS (\n  SELECT queryid, query, calls, total_exec_time, shared_blks_hit, shared_blks_read\n  FROM   perf_snapshots\n  WHERE  snap_time = :'start_ts'\n),\ns2 AS (\n  SELECT queryid, calls, total_exec_time, shared_blks_hit, shared_blks_read\n  FROM   perf_snapshots\n  WHERE  snap_time = :'end_ts'\n)\nSELECT\n  left(s1.query, 80)                                       AS query_snippet,\n  (s2.calls            - s1.calls)                         AS new_calls,\n  round((s2.total_exec_time - s1.total_exec_time)::numeric \/ 1000, 2)\n                                                            AS added_exec_sec,\n  (s2.shared_blks_read - s1.shared_blks_read)              AS new_disk_reads,\n  (s2.shared_blks_hit  - s1.shared_blks_hit)               AS new_buffer_hits\nFROM   s1\nJOIN   s2 USING (queryid)\nWHERE  s2.calls &gt; s1.calls\nORDER  BY added_exec_sec DESC\nLIMIT  20;<\/pre><\/div>\n\n\n\n<p>This table and scheduled job can now collect snapshot performance data in any PostgreSQL environment. Additionally, querying the table now allows you to inspect performance impacts <em>without<\/em> the <code>pg_profile<\/code> extension.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-sql-ranking-and-workload-analysis-in-postgresql\">SQL ranking and workload analysis in PostgreSQL<\/h2>\n\n\n\n<p>One of the biggest challenges when using performance data is understanding how to query quantities and <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/modeling-time\/\" target=\"_blank\" rel=\"noreferrer noopener\">time<\/a>. The queries in this section mirror Oracle&#8217;s AWR Top SQL report using <code>pg_stat_statements<\/code> and serve as an example of how to calculate it correctly in PostgreSQL. <\/p>\n\n\n\n<p><em>Note the examples for <code>total_exec_time<\/code>, <code>mean_exec_time<\/code>, <code>blk_read_time<\/code>, etc.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-top-sql-by-total-execution-time\">Top SQL by total execution time<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Equivalent to Oracle AWR \"Top SQL by Elapsed Time\"\nSELECT\n  left(query, 80)                                     AS query_snippet,\n  calls,\n  round(total_exec_time::numeric \/ 1000, 2)           AS total_exec_sec,\n  round(mean_exec_time::numeric  \/ 1000, 4)           AS avg_exec_sec,\n  rows,\n  shared_blks_hit,\n  shared_blks_read,\n  round(blk_read_time::numeric  \/ 1000, 3)            AS read_sec,\n  round(blk_write_time::numeric \/ 1000, 3)            AS write_sec\nFROM   pg_stat_statements\nORDER  BY total_exec_time DESC\nLIMIT  10;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-top-sql-by-i-o-disk-reads\">Top SQL by I\/O (disk reads)<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  left(query, 80)                                     AS query_snippet,\n  calls,\n  shared_blks_read                                    AS disk_reads,\n  shared_blks_hit                                     AS buffer_hits,\n  round(100.0 * shared_blks_hit \/\n        NULLIF(shared_blks_hit + shared_blks_read, 0), 1)\n                                                      AS buffer_hit_pct,\n  round(total_exec_time::numeric \/ 1000, 2)           AS total_exec_sec\nFROM   pg_stat_statements\nWHERE  shared_blks_read &gt; 0\nORDER  BY shared_blks_read DESC\nLIMIT  10;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-top-sql-by-average-execution-time-worst-per-call-performance\">Top SQL by average execution time (worst per-call performance)<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  left(query, 80)                                     AS query_snippet,\n  calls,\n  round(mean_exec_time::numeric \/ 1000, 4)            AS avg_exec_sec,\n  round(stddev_exec_time::numeric \/ 1000, 4)          AS stddev_exec_sec,\n  round(min_exec_time::numeric  \/ 1000, 4)            AS min_exec_sec,\n  round(max_exec_time::numeric  \/ 1000, 4)            AS max_exec_sec\nFROM   pg_stat_statements\nWHERE  calls &gt;= 10                    -- ignore single-run outliers\nORDER  BY mean_exec_time DESC\nLIMIT  10;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-top-sql-by-row-throughput\">Top SQL by row throughput<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  left(query, 80)                                     AS query_snippet,\n  calls,\n  rows,\n  round(rows::numeric \/ NULLIF(calls, 0), 1)          AS rows_per_call,\n  round(total_exec_time::numeric \/ 1000, 2)           AS total_exec_sec\nFROM   pg_stat_statements\nORDER  BY rows DESC\nLIMIT  10;<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-wait-event-analysis-in-postgresql\">Wait event analysis in PostgreSQL<\/h2>\n\n\n\n<p><strong>Wait classes serve as high-level categories that help identify pain points in performance, and wait events are more detailed events that belong to each category. <\/strong><\/p>\n\n\n\n<p>These are the cornerstone of method tuning in Oracle and much of it can be duplicated in PostgreSQL from version 16 onwards. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-current-wait-events-live-sessions\">Current wait events (live sessions)<\/h3>\n\n\n\n<p><strong>The point-in-time snapshot is the PostgreSQL equivalent of querying Oracle&#8217;s <code>v$session<\/code> for wait events. It&#8217;ll look familiar to any Oracle DBA and will provide value to even the newest PostgreSQL user.<\/strong> <\/p>\n\n\n\n<p>It shows <strong>event type<\/strong> (like <strong>wait class<\/strong>, which is the category), and then the corresponding breakdown of <strong>wait events<\/strong> (the things causing the majority of time and resource consumption in the database.)<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">consumption in the database.\nSELECT\n  wait_event_type,\n  wait_event,\n  count(*)                                            AS session_count,\n  array_agg(pid)                                     AS pids\nFROM   pg_stat_activity\nWHERE  state != 'idle'\n  AND  wait_event IS NOT NULL\nGROUP  BY 1, 2\nORDER  BY 3 DESC;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-historical-wait-event-profile-pg-wait-sampling\">Historical wait event profile (pg_wait_sampling)<\/h3>\n\n\n\n<p><strong>Sampled wait history is the PostgreSQL equivalent of the Oracle AWR Top 5 Wait Events report. <\/strong><\/p>\n\n\n\n<p>It requires the <code>pg_wait_sampling<\/code> extension and gives us a clear picture of what percentage of what event type (category) is consuming database time (broken down to the exact wait event detail).<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Top wait events by sample count (percentage of total)\nSELECT\n  wait_event_type,\n  wait_event,\n  count(*)::numeric                                   AS samples,\n  round(count(*) * 100.0 \/                           \n        sum(count(*)) OVER (), 1)                    AS pct\nFROM   pg_wait_sampling_history\nGROUP  BY 1, 2\nORDER  BY samples DESC\nLIMIT  10;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-wait-events-over-a-time-window\">Wait events over a time window<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Filter to a specific time window (requires pg_wait_sampling)\nSELECT\n  wait_event_type,\n  wait_event,\n  count(*)                                            AS samples\nFROM   pg_wait_sampling_history\nWHERE  sample_time BETWEEN now() - interval '1 hour' AND now()\nGROUP  BY 1, 2\nORDER  BY 3 DESC\nLIMIT  15;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-a-key-postgresql-gap-versus-oracle\">A key PostgreSQL gap versus Oracle<\/h3>\n\n\n\n<p><strong>Oracle samples waits natively at ~10x\/sec via ASH, but PostgreSQL has no built-in equivalent. <code>pg_wait_sampling<\/code> with a <code>bgworker<\/code> is the closest alternative. Without this, you&#8217;ll only see instantaneous waits from <code>pg_stat_activity<\/code>.<\/strong><\/p>\n\n\n\n<section id=\"my-first-block-block_da57e415611ef2826f91578b15876e54\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Free desktop tool for fast PostgreSQL monitoring and diagnostics<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            Stay in control of PostgreSQL performance with Redgate pgNow \u2013 a free desktop tool for fast, focused diagnostics. No agents, no setup, just actionable insights when you need them.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/products\/redgate-pgnow\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Learn more &amp; download now: Free desktop tool for fast PostgreSQL monitoring and diagnostics\">Learn more &amp; download now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-detect-i-o-bottlenecks-in-postgresql\">How to detect I\/O bottlenecks in PostgreSQL <\/h2>\n\n\n\n<p>There are a few methods for detecting I\/O bottlenecks in PostgreSQL. Here, I&#8217;ll run through a few of them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-system-wide-i-o-accounting-using-pg-stat-io-postgresql-16-onwards\">System-wide I\/O accounting using pg_stat_io (PostgreSQL 16 onwards)<\/h3>\n\n\n\n<p>The <code>pg_stat_io<\/code> extension is the closest PostgreSQL-native equivalent to Oracle&#8217;s <code>v$filestat<\/code>. It breaks down reads, writes, hit rates, and evictions by backend type and object context. As the natural life of a database is growth, understanding how IO is impacting performance is essential to database management.<\/p>\n\n\n\n<p><em><code>pg_stat_io<\/code> is only available in PostgreSQL 16 onwards. For PG 15 (and earlier), use <code>pg_stat_bgwriter<\/code> and <code>pg_stat_io_* <\/code>views instead.<\/em><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Full I\/O breakdown by backend type\nSELECT\n  backend_type,\n  object,\n  context,\n  reads,\n  writes,\n  extends,\n  round(read_time::numeric  \/ 1000, 2)               AS read_sec,\n  round(write_time::numeric \/ 1000, 2)               AS write_sec,\n  hits,\n  evictions,\n  reuses,\n  round(hits::numeric \/ NULLIF(hits + reads, 0) * 100, 1)\n                                                      AS hit_pct\nFROM   pg_stat_io\nORDER  BY reads DESC;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-checkpoint-amp-buffer-manager-stats-in-postgresql-with-pg-stat-bgwriter\">Checkpoint &amp; buffer manager stats in PostgreSQL with pg_stat_bgwriter<\/h3>\n\n\n\n<p>The <code>pg_stat_pgwriter<\/code> extension is currently available in every version of PostgreSQL. It shows checkpoint frequency, buffer writes, and clean\/dirty eviction patterns.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  checkpoints_timed,\n  checkpoints_req,\n  round(checkpoint_write_time \/ 1000, 1)             AS chkpt_write_sec,\n  round(checkpoint_sync_time  \/ 1000, 1)             AS chkpt_sync_sec,\n  buffers_checkpoint,\n  buffers_clean,\n  maxwritten_clean,\n  buffers_backend,\n  buffers_backend_fsync,\n  buffers_alloc,\n  stats_reset\nFROM   pg_stat_bgwriter;<\/pre><\/div>\n\n\n\n<p><em><strong>Tip:<\/strong> If <code>checkpoints_req<\/code> is high (relative to <code>checkpoints_timed<\/code>), your <code>checkpoint_completion_target<\/code> or <code>max_wal_size<\/code> may need tuning. Frequent requested checkpoints increase I\/O spikes.<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-hot-buffer-segment-inspection-in-postgresql-with-pg-buffercache\">Hot buffer\/segment inspection in PostgreSQL with pg_buffercache<\/h3>\n\n\n\n<p>The <code>pg_buffercache<\/code> extension is the PostgreSQL equivalent to Oracle&#8217;s <code>v$bh<\/code> buffer cache segment report, identifying which relations occupy the most <code>shared_buffers<\/code> pages.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  c.relname,\n  count(*)                                           AS buffers_in_cache,\n  round(count(*) * 8192.0 \/ 1024 \/ 1024, 1)        AS size_mb,\n  round(100.0 * count(*) \/\n        (SELECT count(*) FROM pg_buffercache), 1)   AS pct_of_cache,\n  count(*) FILTER (WHERE b.isdirty)                 AS dirty_buffers,\n  round(100.0 * count(*) FILTER (WHERE b.isdirty) \/\n        NULLIF(count(*), 0), 1)                     AS dirty_pct\nFROM   pg_buffercache b\nJOIN   pg_class c ON c.relfilenode = b.relfilenode\nGROUP  BY 1\nORDER  BY buffers_in_cache DESC\nLIMIT  15;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-per-table-i-o-stats-with-pg-statio-user-tables-in-postgresql\">Per-table I\/O stats with pg_statio_user_tables in PostgreSQL<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  schemaname,\n  relname,\n  heap_blks_read,\n  heap_blks_hit,\n  round(100.0 * heap_blks_hit \/                     \n        NULLIF(heap_blks_hit + heap_blks_read, 0), 1)\n                                                     AS heap_hit_pct,\n  idx_blks_read,\n  idx_blks_hit,\n  toast_blks_read,\n  toast_blks_hit\nFROM   pg_statio_user_tables\nORDER  BY heap_blks_read + idx_blks_read DESC\nLIMIT  15;<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-blocking-and-concurrency-detection-in-postgresql\">Blocking and concurrency detection in PostgreSQL<\/h2>\n\n\n\n<p>We already know how important active session information is, but understanding blocking sessions is also valuable.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-active-session-and-blocking-tree\">Active session and blocking tree<\/h3>\n\n\n\n<p><strong>While <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/multi-version-concurrency-control-mvcc-in-postgresql-learning-postgresql-with-grant\/\" target=\"_blank\" rel=\"noreferrer noopener\">multi-version concurrency control (MVCC)<\/a> helps to eliminate some blocking we experience in other database platforms, blocking is still an important area to monitor in PostgreSQL.<\/strong> <\/p>\n\n\n\n<p>Knowing how to show all non-idle sessions, identify which session are blocked, and show the blocking PID chain, is essential. The following query mirrors Oracle&#8217;s <code>v$session<\/code> blocking analysis.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">WITH blocking AS (\n  SELECT\n    pid,\n    usename,\n    datname,\n    wait_event_type,\n    wait_event,\n    state,\n    query_start,\n    pg_blocking_pids(pid)   AS blockers,\n    left(query, 80)         AS query\n  FROM   pg_stat_activity\n  WHERE  state != 'idle'\n)\nSELECT\n  pid,\n  state,\n  wait_event_type,\n  wait_event,\n  array_length(blockers, 1) AS blocked_by_n,\n  blockers,\n  now() - query_start       AS duration,\n  query\nFROM   blocking\nORDER  BY blocked_by_n DESC NULLS LAST, duration DESC;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-long-running-transactions-and-idle-in-transaction-sessions\">Long-running transactions and idle-in-transaction sessions<\/h3>\n\n\n\n<p>The following query displays sessions in the <code>idle-in-transaction<\/code> state, hold row locks, and block <code>VACUUM<\/code>.&nbsp;This query can also be used as a basis to build alerts longer than 5 minutes.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Sessions idle-in-transaction\nSELECT\n  pid,\n  usename,\n  datname,\n  state,\n  now() - xact_start          AS txn_age,\n  now() - state_change         AS idle_since,\n  left(query, 100)             AS last_query\nFROM   pg_stat_activity\nWHERE  state = 'idle in transaction'\n  AND  xact_start &lt; now() - interval '5 minutes'\nORDER  BY txn_age DESC;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-lock-detail-for-a-specific-session\">Lock detail for a specific session<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Replace :target_pid with the PID of interest\nSELECT\n  locktype,\n  relation::regclass          AS relation_name,\n  mode,\n  granted,\n  transactionid,\n  classid,\n  objid\nFROM   pg_locks\nWHERE  pid = :target_pid;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-lock-wait-summary\">Lock wait summary<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- All lock waits currently in progress\nSELECT\n  waiting.pid                  AS waiting_pid,\n  waiting.query                AS waiting_query,\n  blocking.pid                 AS blocking_pid,\n  blocking.query               AS blocking_query,\n  now() - waiting.query_start  AS wait_duration\nFROM   pg_stat_activity AS waiting\nJOIN   pg_stat_activity AS blocking\n         ON  blocking.pid = ANY(pg_blocking_pids(waiting.pid))\nWHERE  waiting.wait_event_type = 'Lock'\nORDER  BY wait_duration DESC;<\/pre><\/div>\n\n\n\n<p><strong>Important note!<\/strong><br><code>idle in transaction<\/code> sessions hold row locks and can prevent <code>VACUUM<\/code> from reclaiming dead <a href=\"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/care-feeding-tuples-c\/\" target=\"_blank\" rel=\"noreferrer noopener\">tuples<\/a>, causing table bloat. Set <code>idle_in_transaction_session_timeout<\/code> in <code>postgresql.conf<\/code> to automatically terminate such sessions.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-named-baselines-and-period-comparison-in-postgresql\">Named baselines and period comparison in PostgreSQL<\/h2>\n\n\n\n<p>Oracle AWR provides fixed and moving-window baselines natively. In PostgreSQL, meanwhile, you&#8217;ll build these manually by tagging snapshot ranges and comparing deltas, as I demonstrate below.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-how-to-create-a-baseline-registry-table\">How to create a baseline registry table<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">CREATE TABLE IF NOT EXISTS perf_baselines (\n  baseline_name   TEXT PRIMARY KEY,\n  description     TEXT,\n  start_snap_time TIMESTAMPTZ NOT NULL,\n  end_snap_time   TIMESTAMPTZ NOT NULL,\n  created_at      TIMESTAMPTZ DEFAULT now()\n);\n \n-- Register a peak-load baseline\nINSERT INTO perf_baselines VALUES (\n  'peak_load_2025_q1',\n  'Monday morning peak observed 2025-01-13 09:00-10:00',\n  '2025-01-13 09:00:00+00',\n  '2025-01-13 10:00:00+00'\n);<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-how-to-compare-the-current-period-against-the-baseline\">How to compare the current period against the baseline<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Compare current 1-hour window to a named baseline\nWITH\nbaseline AS (\n  SELECT queryid,\n         sum(s2.calls            - s1.calls)           AS base_calls,\n         sum(s2.total_exec_time  - s1.total_exec_time) AS base_exec_ms,\n         sum(s2.shared_blks_read - s1.shared_blks_read)AS base_reads,\n         max(s1.query)                                  AS query\n  FROM   perf_snapshots s1\n  JOIN   perf_snapshots s2 USING (queryid)\n  JOIN   perf_baselines b ON b.baseline_name = 'peak_load_2025_q1'\n  WHERE  s1.snap_time = b.start_snap_time\n    AND  s2.snap_time = b.end_snap_time\n  GROUP  BY queryid\n),\ncurrent_window AS (\n  SELECT queryid,\n         sum(s2.calls            - s1.calls)           AS cur_calls,\n         sum(s2.total_exec_time  - s1.total_exec_time) AS cur_exec_ms,\n         sum(s2.shared_blks_read - s1.shared_blks_read)AS cur_reads\n  FROM   perf_snapshots s1\n  JOIN   perf_snapshots s2 USING (queryid)\n  WHERE  s1.snap_time = now() - interval '1 hour'\n    AND  s2.snap_time = (\n           SELECT max(snap_time) FROM perf_snapshots)\n  GROUP  BY queryid\n)\nSELECT\n  left(b.query, 70)                                    AS query_snippet,\n  b.base_calls,     c.cur_calls,\n  round(b.base_exec_ms::numeric \/ 1000, 2)            AS base_sec,\n  round(c.cur_exec_ms::numeric  \/ 1000, 2)            AS cur_sec,\n  round((c.cur_exec_ms - b.base_exec_ms) \/ NULLIF(b.base_exec_ms,0) * 100, 1)\n                                                       AS exec_pct_change\nFROM   baseline b\nJOIN   current_window c USING (queryid)\nWHERE  abs(c.cur_exec_ms - b.base_exec_ms) &gt; 1000     -- &gt; 1 second difference\nORDER  BY exec_pct_change DESC\nLIMIT  20;<\/pre><\/div>\n\n\n\n<p><strong>This process allows you to easily monitor, compare, and manage baseline performance.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-wal-and-replication-monitoring-in-postgresql\">WAL and replication monitoring in PostgreSQL<\/h2>\n\n\n\n<p><strong><a href=\"https:\/\/www.sqlservercentral.com\/editorials\/what-write-ahead-logging-is-and-why-it-matters\" target=\"_blank\" rel=\"noreferrer noopener\">WAL (Write-Ahead Logging)<\/a> is the mechanism PostgreSQL uses to keep your data safe and consistent. <\/strong><\/p>\n\n\n\n<p>The core idea is simple: before PostgreSQL makes any changes to the actual data files on disk, it first records those changes in a sequential log &#8211; the <strong>write-ahead log<\/strong>. Because the change is safely written to this log before it&#8217;s applied, the database can always recover after a crash or power failure by replaying the log and reconstructing any work that hadn&#8217;t yet been fully saved to the data files. <\/p>\n\n\n\n<p>This &#8220;log first, write later&#8221; approach also makes the database faster, since appending to a sequential log is quicker than constantly updating scattered data files. It also underpins important features like <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/postgresql-backups-to-support-point-in-time-recovery-learning-postgresql-with-grant\/\" target=\"_blank\" rel=\"noreferrer noopener\">point-in-time recovery<\/a> and <a href=\"https:\/\/www.postgresql.org\/docs\/current\/protocol-replication.html\" target=\"_blank\" rel=\"noreferrer noopener\">streaming replication<\/a> to standby servers.<\/p>\n\n\n\n<p>It&#8217;s essential for any database specialist to understand the following:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>How much WAL is being generated<br><br><\/li>\n\n\n\n<li>If there\u2019s any latency in WAL generation<br><br><\/li>\n\n\n\n<li>If there&#8217;s any lag in replication of WAL to replicas<br><br><\/li>\n\n\n\n<li>The overall health of the WAL replication<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-wal-generation-rate\">WAL generation rate<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  wal_records,\n  wal_fpi,\n  round(wal_bytes \/ 1024.0 \/ 1024, 1)               AS wal_mb,\n  wal_buffers_full,\n  wal_write,\n  round(wal_write_time \/ 1000, 2)                   AS wal_write_sec,\n  round(wal_sync_time  \/ 1000, 2)                   AS wal_sync_sec,\n  stats_reset\nFROM   pg_stat_wal;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-replication-lag\">Replication lag<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- On the primary: show replica lag\nSELECT\n  client_addr,\n  state,\n  sent_lsn,\n  write_lsn,\n  flush_lsn,\n  replay_lsn,\n  write_lag,\n  flush_lag,\n  replay_lag,\n  sync_state\nFROM   pg_stat_replication\nORDER  BY replay_lag DESC NULLS LAST;<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-table-and-index-health-in-postgresql\">Table and index health in PostgreSQL<\/h2>\n\n\n\n<p>The health of objects in PostgreSQL can refer to numerous things, but one of the areas of concern is around the amount of bloat a database has. Bloat refers to the wasted, unused space that accumulates inside tables and indexes over time, caused by how PostgreSQL handles updates and deletes. Why is this?<\/p>\n\n\n\n<p>Well, rather than overwriting or immediately removing a row, PostgreSQL marks the old version as &#8216;dead&#8217; and writes a new version elsewhere. This is a side effect of its MVCC design, which allows many transactions to read and write at the same time, without blocking each other. &#8216;Dead&#8217; rows can&#8217;t simply disappear on their own &#8211; they linger in the table, taking up disk space and making queries slower because of how PostgreSQL has to scan past them. <\/p>\n\n\n\n<p>Normally, the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/learning-postgresql-with-grant-introducing-vacuum\/#:~:text=the%20autovacuum%20deamon.-,Autovacuum,-Enabled%20by%20default\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL autovacuum process<\/a> cleans up dead rows and makes that space reusable. However, if updates and deletes happen faster than vacuum can keep up, or if vacuum isn&#8217;t tuned well, the dead space piles up as bloat. Left unchecked, bloat increases storage usage, degrades query performance, and can sometimes require maintenance operations like <code>VACUUM FULL<\/code> &#8211; or tools such as <code>pg_repack<\/code> &#8211; to physically reclaim the space.<\/p>\n\n\n\n<section id=\"my-first-block-block_0a428649b61021dc40a407918aa8ece4\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Subscribe to the Simple Talk newsletter<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            Get selected articles, event information, podcasts and other industry content delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            <a href=\"https:\/\/www.red-gate.com\/simple-talk\/subscribe\/\" class=\"btn btn--secondary btn--lg\" aria-label=\"Subscribe: Subscribe to the Simple Talk newsletter\">Subscribe<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-table-statistics-bloat-indicators\">Table statistics: bloat indicators<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  schemaname,\n  relname,\n  n_live_tup,\n  n_dead_tup,\n  round(100.0 * n_dead_tup \/ NULLIF(n_live_tup + n_dead_tup, 0), 1)\n                                                      AS dead_pct,\n  last_vacuum,\n  last_autovacuum,\n  last_analyze,\n  last_autoanalyze,\n  vacuum_count,\n  autovacuum_count\nFROM   pg_stat_user_tables\nORDER  BY n_dead_tup DESC\nLIMIT  20;<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-unused-indexes\">Unused indexes<\/h3>\n\n\n\n<p>The next point of focus is <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/index-cleanup-harder-than-it-looks\/\" target=\"_blank\" rel=\"noreferrer noopener\">unused indexes<\/a>. When index usage is monitored, you know exactly if an index is providing value, or if it\u2019s just consuming space and resources.&nbsp;Unfortunately, PostgreSQL doesn&#8217;t feature index monitoring.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">-- Indexes that have never been used (candidates for removal)\nSELECT\n  schemaname,\n  relname,\n  indexrelname,\n  idx_scan,\n  idx_tup_read,\n  idx_tup_fetch,\n  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size\nFROM   pg_stat_user_indexes\nWHERE  idx_scan = 0\n  AND  schemaname NOT IN ('pg_catalog', 'pg_toast')\nORDER  BY pg_relation_size(indexrelid) DESC;<\/pre><\/div>\n\n\n\n<p>It&#8217;s common for a few objects in a database to become larger than average. Tables that hold transactional information for customer data or inventory can become <em>much<\/em> larger than the majority, and understanding what objects may be creating performance issues is another aspect of monitoring. The indexes on these tables also can become very large, especially if bloat is involved.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-largest-tables-and-indexes\">Largest tables and indexes<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\n  relname,\n  relkind,\n  pg_size_pretty(pg_total_relation_size(oid)) AS total_size,\n  pg_size_pretty(pg_relation_size(oid))       AS table_size,\n  pg_size_pretty(\n    pg_total_relation_size(oid)\n    - pg_relation_size(oid))                  AS index_size\nFROM   pg_class\nWHERE  relkind IN ('r', 'i', 'm')\n  AND  relnamespace NOT IN (\n         SELECT oid FROM pg_namespace\n         WHERE nspname IN ('pg_catalog','information_schema','pg_toast'))\nORDER  BY pg_total_relation_size(oid) DESC\nLIMIT  20;<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-implementation-roadmap-you-should-follow\">The implementation roadmap you should follow<\/h2>\n\n\n\n<p>Now that we understand what to inspect for performance impact, we now need to implement it in the least disruptive way possible, but also in the way that provides the most value.<\/p>\n\n\n\n<p><strong>Consider following this phased rollout to reach enterprise-grade PostgreSQL diagnostics without disrupting your environment.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Day 1: Baseline<\/strong><\/td><td><strong>Week 1: History<\/strong><\/td><td><strong>Month 1: Deep Diag<\/strong><\/td><td><strong>Quarter: Baselines<\/strong><\/td><\/tr><tr><td>Enable <code>pg_stat_statements<\/code><\/td><td>Install <code>pg_profile<\/code><\/td><td>Add <code>pg_wait_sampling<\/code> (wait history)<\/td><td>Register peak-load baselines<\/td><\/tr><tr><td>Set <code>track_io_timing = on<\/code><\/td><td>Schedule snapshot every 30 min (<code>pg_cron<\/code>)<\/td><td>Add <code>pg_buffercache<\/code> (cache inspection)<\/td><td>Build compare-period delta reports<\/td><\/tr><tr><td>Verify <code>pg_stat_io<\/code> (PG 16+)<\/td><td>Set retention to 14+ days<\/td><td>Add <code>pg_store_plans<\/code> (plan history)<\/td><td>Integrate with Prometheus \/ Grafana<\/td><\/tr><tr><td>Baseline with <code>pg_stat_bgwriter<\/code><\/td><td>Review first AWR-style HTML report<\/td><td>Build alerting on blocking sessions<\/td><td>Document tuning runbooks<\/td><\/tr><tr><td>Review current Top SQL<\/td><td>Tune postgresql.conf based on findings<\/td><td>Create <code>idle_in_transaction<\/code> alert<\/td><td>Schedule quarterly baseline reviews<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-reference-and-resources\">Reference and resources<\/h2>\n\n\n\n<p>Below are the numerous GitHub and document references I used to build my presentation and, in turn, this very cookbook you&#8217;re now reading.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-extension-repositories\">Extension repositories<\/h3>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>pg_profile:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; github.com\/zubkov-andrei\/pg_profile<br><br><\/li>\n\n\n\n<li>pg_wait_sampling:&nbsp;&nbsp;&nbsp;&nbsp; github.com\/postgrespro\/pg_wait_sampling<br><br><\/li>\n\n\n\n<li>pg_store_plans:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; github.com\/ossc-db\/pg_store_plans<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-official-postgresql-documentation\">Official PostgreSQL documentation<\/h3>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><code>pg_stat_statements<\/code>:&nbsp;&nbsp; postgresql.org\/docs\/current\/pgstatstatements.html<br><br><\/li>\n\n\n\n<li><code>pg_buffercache<\/code>:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; postgresql.org\/docs\/current\/pgbuffercache.html<br><br><\/li>\n\n\n\n<li>Monitoring Stats Ref: postgresql.org\/docs\/current\/monitoring-stats.html<br><br><\/li>\n\n\n\n<li><code>pg_stat_io<\/code> (PG 16+):&nbsp; postgresql.org\/docs\/current\/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW<\/li>\n<\/ul>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-quick-reference-postgresql-conf-settings\">Quick-reference: postgresql.conf settings<\/h3>\n\n\n\n<p>Finally, here&#8217;s an excellent example of a postgresql.conf file configuration:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \"># \u2500\u2500 required for this cookbook \u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\nshared_preload_libraries = 'pg_stat_statements, pg_wait_sampling, pg_store_plans, auto_explain, pg_cron'\n \n# pg_stat_statements\npg_stat_statements.track = all\npg_stat_statements.max   = 10000\n \n# I\/O timing (critical for meaningful read\/write time columns)\ntrack_io_timing = on\n \n# Autovacuum protection\nidle_in_transaction_session_timeout = 300000  -- 5 minutes in ms\n \n# pg_wait_sampling\npg_wait_sampling.history_size  = 10000\npg_wait_sampling.profile_period = 10   -- ms\n \n# auto_explain\nauto_explain.log_min_duration      = '1s'\nauto_explain.log_analyze           = on\nauto_explain.log_buffers           = on\nauto_explain.log_nested_statements = on<\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion\">Conclusion<\/h2>\n\n\n\n<p>Using these extensions, along with scripts and reporting features, offers the PostgreSQL DBA the opportunity for insight into database performance covering over 70% of what is offered by Oracle\u2019s enterprise Automatic Workload Repository (AWR). <\/p>\n\n\n\n<p>Going forward, I expect significant extension enhancements and improvements that will close the gap between enterprise and open-source tooling to diagnose and identify performance issues in PostgreSQL.<\/p>\n\n\n\n<p><em>This cookbook is<\/em> <em>based on Kellyn Gorman&#8217;s presentation &#8220;Bridging Oracle&#8217;s Diagnostics Power with PostgreSQL&#8217;s Native Performance Views&#8221;<\/em>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Miss Oracle&#8217;s AWR in PostgreSQL? This guide maps every AWR feature &#8211; snapshots, ASH, wait events, Top SQL &#8211; to its PostgreSQL extension equivalent, with ready-to-run SQL.&hellip;<\/p>\n","protected":false},"author":316206,"featured_media":108164,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143533,143534],"tags":[4459,158978],"coauthors":[48576],"class_list":["post-111626","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-oracle-databases","category-postgresql","tag-oracle","tag-postgresql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111626","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316206"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=111626"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111626\/revisions"}],"predecessor-version":[{"id":111680,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111626\/revisions\/111680"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/108164"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=111626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=111626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=111626"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=111626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}