Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics

On 11st of February 2023, Andres Freund committed patch:

Add pg_stat_io view, providing more detailed IO statistics
 
Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation.
 
Rows of the view show IO operations for a particular backend type, IO target
object, IO context combination (e.g. a client backend's operations on
permanent relations in shared buffers) and each column in the view is the
total number of IO Operations done (e.g. writes). So a cell in the view would
be, for example, the number of blocks of relation data written from shared
buffers by client backends since the last stats reset.
 
In anticipation of tracking WAL IO and non-block-oriented IO (such as
temporary file IO), the "op_bytes" column specifies the unit of the "reads",
"writes", and "extends" columns for a given row.
 
Rows for combinations of IO operation, backend type, target object and context
that never occur, are ommitted entirely. For example, checkpointer will never
operate on temporary relations.
 
Similarly, if an IO operation never occurs for such a combination, the IO
operation's cell will be null, to distinguish from 0 observed IO
operations. For example, bgwriter should not perform reads.
 
Note that some of the cells in the view are redundant with fields in
pg_stat_bgwriter (e.g. buffers_backend). For now, these have been kept for
backwards compatibility.
 
Bumps catversion.
 
Author: Melanie Plageman <melanieplageman@gmail.com>
Author: Samay Sharma <smilingsamay@gmail.com>
Reviewed-by: Maciek Sakrejda <m.sakrejda@gmail.com>
Reviewed-by: Lukas Fittl <lukas@fittl.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Discussion: https://postgr.es/m/20200124195226.lth52iydq2n2uilq@alap3.anarazel.de

Continue reading Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics

Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

On 17th of January 2021, Magnus Hagander committed patch:

Add pg_stat_database counters for sessions and session time
 
This add counters for number of sessions, the different kind of session
termination types, and timers for how much time is spent in active vs
idle in a database to pg_stat_database.
 
Internally this also renames the parameter "force" to disconnect. This
was the only use-case for the parameter before, so repurposing it to
this mroe narrow usecase makes things cleaner than inventing something
new.
 
Author: Laurenz Albe
Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda
Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at

Continue reading Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

Waiting for 9.6 – Add simple VACUUM progress reporting.

On 15th of March, Robert Haas committed patch:

Add simple VACUUM progress reporting. 
 
There's a lot more that could be done here yet - in particular, this
reports only very coarse-grained information about the index vacuuming
phase - but even as it stands, the new pg_stat_progress_vacuum can
tell you quite a bit about what a long-running vacuum is actually
doing.
 
Amit Langote and Robert Haas, based on earlier work by Vinayak Pokale
and Rahila Syed.

Continue reading Waiting for 9.6 – Add simple VACUUM progress reporting.

Waiting for 9.2 – temporary file stats per database

On 26th of January, Magnus Hagander committed patch:

Add counters for number and size of temporary files used
for spill-to-disk queries for each database to the
pg_stat_database view.
 
Tomas Vondra, review by Magnus Hagander

Continue reading Waiting for 9.2 – temporary file stats per database

Change in anonymization of plans on explain.depesz.com

As you perhaps know, explain.depesz.com has anonymization feature.

Couple of days ago Filip contacted me and sent a patch that stopped anonymization of typecasts.

I thought about the patch, and what it achieves, changed it's internals, but kept the effect. And today, it got released.

Continue reading Change in anonymization of plans on explain.depesz.com

Waiting for 9.1 – stats reset tracking

On 10th of February, Magnus Hagander committed patch:

Track last time for statistics reset on databases and bgwriter
 
Tracks one counter for each database, which is reset whenever
the statistics for any individual object inside the database is
reset, and one counter for the background writer.
 
Tomas Vondra, reviewed by Greg Smith

Continue reading Waiting for 9.1 – stats reset tracking

explain.depesz.com – stats after a year

I just checked, and apparently explain.depesz.com is online for over a year now. First plans was added on 2008-12-04 13:20:43+01.

Since then there have been 3602 plans added, 770 of them were set to be private (not displayed on previous explains page.

Longest plan has 2267194 characters (yes, 2.2 megabytes of explain analyze output!), but it's not public, so I can't tell you the url. Longest public plan is this monstrosity, which (while being only explain output, not explain analyze) has 503585 characters.

During this time, there have been 61 days that nobody added any plan on (perhaps problems with software?).

The most heavily used day was 23rd of June 2009, when 71 new plans have been added (4 private).

Thank you all for using it. I hope you find it useful, and I promise, that one day, I will finally sit down, and add all long-due features (like work with new explain formats, fixing some output glitches, stats of used objects per plan).

If you'd like to take a peek at how it works – source of explain.depesz.com is always available.