Hello. So PGSTATV is, from the name, I think you can understand that it's probably something
to do with Postgres. And it is. It is a new Postgres extension and utility pair. So it
comes with its own tool that you use outside of Postgres. It's minimalist. We'll get into
that in a moment. It only does the thing it's supposed to do and it doesn't touch anything
else in the system that it's not supposed to. And the purpose of PGSTATV is time series
analysis and visualization. That's the vis part of Postgres internal statistics. That's
the stat part. So Postgres internally keeps its own statistics. They are cumulative and
dynamic statistics, right? So you get, like, number of buffers written is a cumulative
statistic that keeps going up. You also have dynamic statistics like PGSTAT activity that
tells you what's happening inside your Postgres server at that moment in time. So if you take
snapshots of these statistics internally from within Postgres and you perform time series
analysis on them, you can gain insights into how your server is behaving. So this utility
that comes with PGSTATV's extension can produce visualizations for selected time ranges on
the stored snapshots that are inside the database. So you can, for example, take snapshots of
your server every 15 minutes during the course of the day and then analyze it over 24 hours
to see what your peak times were and what was happening inside the server at that time.
I wouldn't recommend taking snapshots more frequently than a minute. And it's easy to
see why. You have too many snapshots. It's harder to see the bigger picture, maybe. So
the reason for all of this is you want to track your performance over time and potentially
you can perform troubleshooting on why your server is not behaving the way you expect
it to and additional tuning. So minimalist, this is a tiny package that is based on the
KISS and UNIX philosophies. So keep it simple and sweet, right? And the UNIX philosophy
is that it comes with a tool that you can run as a normal Postgres command line tool
like P SQL with the same parameters and everything else. And it allows you very simply to create
snapshots of the statistics and visualize them. So it's modular. We'll get into the modules
in a minute. It's minimal. It's the least amount of code I could write to make this
thing work. And it's unobtrusive. So you can take snapshots without affecting any other
activity running on your system. And I think that's very important for being able to monitor
and analyze in production. So the components are Postgres extension, as we said, and the
Python utility that retrieves the stored snapshots from the database and creates simple visualizations
with them using matplotlib. The extension is written in plain SQL and PLPG SQL. So there's
nothing to put in shared preload libraries. So this means that you can just type create
extension and you can start using it without even restarting your server. So create extension
PGstatvis is all you need to do. We're working on the packaging now to get it distributed
through the PGDG repos, Postgres global development group repositories. And by extension, it will
find its way into distributions hopefully soon. The way you install the utility is very
simple. You just type pip install PGstatvis. If you tried that this morning, it wouldn't
work, but I just uploaded the file so you can try it out. As I said, this is the last
minute talk. It's very new. The code is pre-production quality. I would call it alpha code, but
you can give it a try for yourself and offer any suggestions or fixes or tell me what I'm
doing wrong. Now, the extension can be used by super users, but you don't have to. The
only thing that the extension needs is PG monitor role privileges in order to be able
to select from the internal Postgres statistics tables. And the usage is dead simple. And
to take a snapshot, you just type from within a client, select PGstatvis.snapshot. Now, why
is there no underscore there? It's because Postgres doesn't like us naming schemas PG
underscore something. That's reserved only for core Postgres. So, extensions are not
allowed to do it. So, what does the command line look like? You just pip install PGstatvis
and you have the utility and the utility when you ask for help is a normal Postgres utility.
You get your database selection, user name, host name part, et cetera, the same way you
would connect with any Postgres client. And you've got modules like buff that shows you
statistics on the background writer and buffers written to disk, cache hit ratio, checkpoint
rate, connections, number of tuples, weights that it found in the server during the snapshot,
wall generation and so on. And you can either run analyze which runs all of the modules
at once and generates visualizations or you can run just one module if you're only interested
in buffers. You can only say run buff. Most importantly, there's a capital D option that
you can use to specify the date range in order to visualize only the time range you're interested
in. So, like the last 24 hours only. And these are specified, of course, in ISO 8601 format.
So, there's no ambiguity in how to type in dates. And it works something like this.
You connect to database fof as user Postgres. You give it a date range and it just generates
the snapshots and writes the visualizations as PNG to disk. And yes, it has a logo. So,
it's complete. The visualizations look something like this. I apologize if the points are a
bit too small for you to see. So, as we said, buffers written to disk is a line that keeps
going up until stats reset. When the stats get reset, it starts from zero again. So,
perhaps this is more useful. This is the buffer write rate in megabytes per second. So, you
can see exactly how many buffers your Postgres server was writing to disk at any moment in
time. And also, you can analyze what was happening because of the background writer.
No? Yes. Thank you. So, you can see here that because this was a test I ran on my laptop
with a script that was just inserting rows into the same table. The checkpoint line,
which is the orange line, didn't do much because it wasn't scheduled activity taken
care of by checkpoints. But you can see that back ends were doing most of the work. And
also, you can see that the background writer, which is the green line, didn't get the chance
to participate in all this buffer writing because from what we can see from the very
low line, its limits were set to low for production. So, you can gain insights into the behavior
of your Postgres server like this. Or you can look at connection versus status count.
So, you can see how many connections you had coming into your server from clients, how
many of them were active, how many of them were idle, how many were idle in transaction,
and so on. But you can also see which users were taking up those connections. And I think
that's really interesting when you have like an environment that's used by multiple applications
so you can know which developers to blame when it all goes south.
Weight events. As I was testing this on my laptop and was overflowing it with IO because
I was inserting millions of rows into a table, I generated an IO data file read, sorry for
the small letters, weight condition. And that was captured by the snapshot that was being
taken every 10 seconds or so for this example. So, thank you for listening. The project is
going to be live at github.com slash virus slash pgstatvis in a few moments. You can find
me on master don. And what the hell? I'll do it right now. So, as we said, this is alpha
quality code. Oh, I forgot to say that it doesn't do any scheduling or any maintenance
or any partitioning of those internal tables where it keeps the snapshots. So you can delete
them by hand. You can schedule the snapshots very easily with any tool you like, like cron
or pgcron. But I didn't want to make this a dependency on the extension. So you can
just configure it yourself. And I can just go to settings and make it public right now.
Cool. Thank you. Any questions? No, okay.
Thanks anyway. Thanks. Thanks for doing me.