I'm joined as always by Nik, founder of PostgresAI. Hey Nik.
Nikolay: Hello Michael.
Michael: And we have a special guest Fabrízio Mello, who is owner
of Timbira, software engineer at PlanetScale and creator of pg_stat_log,
which is a new extension that we're going to be talking about
today.
Welcome Fabrízio.
We're really glad you could join us.
So pg_stat_log, do you want to give us a little bit of what it
is and why you started work on it.
Fabrízio: Well, pg_stat_log, it's supposed to be an extension.
That solves, at least tries to solve, a very tricky problem on
Postgres observability, that is, collect information about your
logs in several different ways.
For example, you need to check your logs for an error.
For example, your application is erroring out for some reason
and you need to check what the error is happening after some
deploy or something.
You need to go search the logs, grab the logs, and depending
on where your Postgres is running, it can be even tricky, right?
Because you have managed service containers and all of different
environments running Postgres.
So you can have a bunch of different ways to go to the logs and
look for some. And pg_stat_log is essentially a view.
It's a pg_stat view inside Postgres that you collect counters,
collect information about what is happening in your log.
It is grouped by backend types, user ID, database ID, SQL error
code, and error level.
And you can configure pg_stat_log to collect different error levels,
depending on how much information you want to process those
counters.
So essentially a brief introduction is this.
So make your life easier.
Instead of you go to jump into weird tools to access log, you
have all the information you need, the least basic information
inside your Postgres, then you can attach some monitor to it
and collect this information.
And for example, see over the time, error rate based on
a specific application, for example.
If you collect information for a given user.
Nikolay: So I agree with you.
This is a big gap in Postgres observability that we cannot understand
error rates beyond a couple of things.
There is a rollback counter, like transaction xact_commit, xact_
rollback, 2 counters in pg_stat_ database.
But what if some other error happened, right?
And also, what kind of error?
Is it just like uniqueness violation or it's corruption, like
codes, there are 3 codes for like most painful codes, XX000,
XX001, XX002.
You mentioned error codes, there is a registry of error codes,
but it's not possible to monitor them other than just going to
logs.
But logs, I agree with you, it's hard to reach them often.
But also they contain sometimes personal data.
This PII, for example, Our stack for PostgresAI, companies come
to us to analyze health of database, and we don't deal with logs
directly, never.
Because we don't want to tell them we deal with PII, because
everything we deal with is just metadata, some counters, And
we want error rates like that, right?
Grouped by, as you mentioned, by multiple things.
For me, the most interesting is error codes.
I want to know exactly, like, in the past month, did we have
any XX codes in this database or not? It's not possible to answer.
It's insane that it's not possible to answer, right?
There's another thing which is not possible to answer.
It's queries per 2nd.
Not possible to answer.
You can extract it from pg_stat_database, but it's maybe not the
whole iceberg, right?
Yeah, but it's a different story.
I'm also thinking, why is it not like, why nobody's attacking
this missing piece?
But that's great.
So you built this extension and 1st question, you mentioned all
these things to group by, why there is no query ID there?
Fabrízio: I think just because I didn't add it yet, maybe for
the next version we can introduce a query ID.
We are jumbling the query and providing the query ID, we can
collect and store that as a new key for this.
Nikolay: Because I want to know which, for example, which queries
had these errors.
Right?
Fabrízio: That makes sense.
Makes sense.
Nikolay: Maybe it's expensive in terms of overhead.
We can discuss overhead slightly later maybe because it's a big
topic.
But another question I had, why did you call it pg_stat_log, not
pg_stat_errors or something?
Fabrízio: Because we can collect normal log messages as well.
So I created this configuration, min error level, that you can
define what is the minimum error level you can, you want to collect.
Nikolay: Not only error, also warnings and notices like...
Fabrízio: Everything.
You can collect everything.
The default is a warning, error level is error, but you can define
any error level.
Even debug.
Michael: I think default is warning.
Fabrízio: Oh, default is warning, sorry.
Michael: Which is the same as, you had a really good caveat section
in the README.
You mentioned about log_min_messages as well, which also defaults
to warning.
So by default, both match, which is nice.
But if you want more than warnings, you also need to change that
because otherwise they don't end up in the logs in the 1st place
and you're using that hook.
I think it's really cool for all the reasons Nik mentioned,
but also potentially for more.
I feel like there's probably quite a few things that are warnings
in the logs that probably a lot of maybe managed service users
or maybe users in general just don't ever notice because they
don't think to look in the logs for warnings.
So I think exposing those via some Monitoring tools or other
tools could be really useful as well.
Just show you you do have warnings going on in your logs, by
the way
Nikolay: Let's name the core of the problem the root of the problem
there are like all SREs know methodologies like USE, right? Brendan
Gregg's USE and four golden signals We cannot apply them to Postgres
right now without digging the logs all the time.
And this is insane that they cannot apply basic methodologies,
because all of them include question, do we have a spike of errors,
right?
We cannot answer.
Let's go grab logs and answer.
It's an insane situation, which means that demand in this tool
is huge.
Everyone needs it.
Fabrízio: Yeah, you can get rid of some complex stack like Logstash,
and
Nikolay: Yeah, exactly.
And all
Fabrízio: this stuff, Kibana, and just have your simple view
and a postgres_exporter or something collecting time to time information
from this view and send out to Prometheus.
Grafana or something.
Nikolay: Exactly.
Like you can analyze it inside database.
I saw weird extensions.
Fabrízio: Yeah, using R2, your pg_ash.
Nikolay: Yeah, like that.
Like just self-collecting samples and understand buckets and
so on.
It's possible.
But also, Because of lacking this observability piece, I saw
long ago, like RDS had it as well, extensions like foreign data
wrappers over logs just to bring this visibility about errors
and something to SQL context, which is like absolutely crazy
thing.
So yeah, that's great and it's great to have it, but everywhere
we discussed it so far, and this podcast won't be an exclusion,
I say this must be, I'm not asking, I'm just saying, this must
be in core.
So.
Fabrízio: Yeah, what I was about to ask you why do you want do
you think this should be in core?
It truly in core not as an extension because my plan right now
is package this as a contrib module and submit it upstream.
I didn't do it yet just for final review.
Michael: Like pg_stat_statements.
Nikolay: Right.
Fabrízio: Yeah.
Like pg_stat_statements.
Nikolay: That's great.
I think this is the absolute minimum that needs to be done.
But I also think pg_stat_statements should be in core to avoid this
friction of creating extensions.
And this should be in core just because, Again, any serious methodology
includes error analysis, and it's not available without digging
logs.
And digging logs is not always possible because it has a PII
and not all people have access to it.
Not all external people or internal people sometimes don't have
access because there is PII, like emails or something, right?
We just don't need to know basic things like error rates.
Without building this stack with Kibana or something, I never
saw it working really well.
I saw many companies build this, But the only thing I saw that
was working quite well was Splunk, which costs millions of dollars.
Splunk costs millions of dollars and requires a whole team to
manage.
It's a great piece of software, super expensive.
I don't see any startup that could afford it actually, in terms
of money and people allocated to manage it.
So it should be simple.
Like error analysis must be simple.
That's why it must be in core, available to everyone.
Fabrízio: And you know that, talking about simplicity, this extension
was something that I wanted to work for a long time.
And the main reason I didn't start it many years ago was because,
oh, I need to build an entire pg_ stat_statements-like extension.
With serializing the information to somewhere, and then
deserializing information and all of those.
It's a lot of work, right?
And then I always have something else to do.
And I never do.
Then the new feature Postgres released on PG 18, Custom Cumulative
Stats.
That is great because open the doors for the extension authors
to create their own statistics like that.
And then the statistics collector of Postgres, you do the job
to collect your statistics.
So it abstracts a lot of complexity of dealing with a lot of
memory, serializing, deserializing stuff.
And Postgres, and on the extension side, we need to deal only
with the business logic of the thing, what you want to collect,
right?
So this is 1 of the reasons that I started.
The 2nd reason is, okay, I need to try more AI tools because
I'm an old guy trying new AI stuff and then I tried it.
And the 3rd reason because Nik liked it very much and pushed
me a bit.
Okay, we need to do this, we need to have this in core.
So let's do this.
Then I bootstrap the 1st version and then Nik, helping me using
AI tools, reviewing, testing, and all of the edge cases.
So now we have a good state of code that is good enough to send
to upstream like a contrib module like pg_stat_statements.
Those are 1 of the reasons that I decided to do this right now.
It means that this extension won't work for example, with PG
17, 16 only on PG 18 and next versions.
Michael: Nice.
I saw you already added 19 support as well.
Was that much additional work or was that relatively straightforward
in the end?
Fabrízio: I think it was only 1
Nikolay: macro,
Fabrízio: only 1 small piece of code.
It's just 1 commit to make it work.
The most part of it.
The issue is the same Because some internal data structures was
renamed or moved around and then I just used a macro or two to
make it compatible either on 18 or 19.
Even on main Postgres branch is compatible right now.
Michael: Nice, perfect.
Fabrízio: For the 20 development cycle.
Nikolay: That's good.
Let's talk a little bit about overhead and observer effect.
What's the price of having all those counters?
Fabrízio: I did some very basic benchmarks.
The overhead I saw, setting the min error level to log everything
and run some pgbench synthetic workload I max around 2-3%
of overhead
Nikolay: What was QPS and how many log messages per 2nd?
Fabrízio: Come on, I don't...
I think I have this information in the issue.
Nikolay: Yeah, I'm very curious.
Maybe I want to dig myself.
I just need to find time.
Michael: Just to be clear though, the default here is warning
and we're, but we're all saying that it would be useful at warning
or even error, it would still be extremely useful.
So presumably at those levels, it's going to be near 0 overhead.
Fabrízio: Yes, exactly.
Yeah.
I tested on the worst case scenario.
1 of the worst case scenario, logging everything and then collecting
everything.
And then now I discovered something that I'm working on because
we need to configure the capacity of how many lines we collect,
how many entries we collect in this pg_stat_log, like pg_stat_statements.
Nikolay: And pg_stat_statements is 5000 by default, pg_stat_statements
dot max.
Fabrízio: Yeah, exactly.
And then a new different entry came.
I'm not doing O(1) now, brute forcing.
And this make expensive.
I have 1 entry that should be discarded, be dropped and not collected
because my list is full.
My list of entries that I'm collecting right now is full.
So this, I think Nik advised me to create this dropped information
on the pg_stat_log_info() to see how many messages were not collected.
And when we reach to this point, then the performance is even
worse.
The overhead is like 27% or 30%.
So I'm here working the main algorithm to don't be so expensive.
Nikolay: Right.
We talk about overhead here on the edge case when it's basically
a stress load, when we have a lot of log messages happening.
Then it's worth noticing that these messages go to logs as well,
right?
If we have so many log messages, we have observer effects there
anyway.
Fabrízio: Yes, you have problem anyway because your log is...
Nikolay: All queries, this is simple.
Let's set log_statement to all, and let's go.
This is how you can put your server down If it's loaded.
So it means that overhead you introduce is like sister or brother
overhead of what we already have in logs.
We're just slightly amplifying it.
When amplifying, not badly, because overhead from Writing to
disk is high.
If you write 1000 lines per 2nd through a logging collector,
you will notice this.
And I know it.
You will notice it.
If you increment some count 1000 times per 2nd, you also probably
will notice it, but less.
So
Fabrízio: we should
Nikolay: log less, this should be a rule, right?
And exploring overhead on edge case makes sense just to understand
how much is...
Maybe we should explore with flame graphs as well in normal conditions,
like how much is going to these functions.
Also, contention risks.
Obviously you have some lightweight lock introduced, right?
Fabrízio: Yes, I have some lightweight lock introduced, but The
good news is that this is processed by a stats collector, because
it's done on the stats collector side.
But anyway, it's using the hot path of the emit_log_hook.
So it creates contention.
But mostly CPU contention.
Memory contention is not too much, but it's more CPU contention.
It's not disk contention because we are not writing anything
on this, on the extension.
It's done on the stats collector when you shut down server and
start the server because this is done by another subsystem, not
in the extension itself.
We have a new abstraction that Custom Cumulative Stats is created
that is great for extension developers.
Michael: Yeah, that's important.
So it means like a safe restart, like not a crash, the stats
will persist.
Yeah.
But on crash it would be lost, which makes sense.
Fabrízio: Yes.
It behaves like PG stats.
Nikolay: I think also granularity matters, right?
Because you have some only some limited number of aggregated
entries, right?
So aggregate by error code, by database, user and severity level,
right?
But if we, for example, have very volatile situation, I don't
know, like maybe a lot of users we have, or maybe a lot of databases.
So we have a lot of entries, we probably don't want them, right?
For example, my 1st thought is maybe I don't want warnings, right?
Because it's too much, too much information.
And I know I will be paying for this overhead.
Maybe I want only errors.
But at the same time, I know some warnings I definitely would
like to see.
For example, a warning about glibc collation mismatch, like suggesting
that maybe we have some corruption, silent corruption and so
on.
These were warnings I definitely would love to catch.
Also, there are some errors I definitely would like to ignore.
For example, uniqueness violation counter is good.
But what if we start that database and it says this very famous
error message, FATAL: the database system is starting up.
It's not
Michael: an error
Nikolay: actually.
It's just like a quintal bit, but it seems like an error and
we can have spike of errors.
Which means that, imagine we have all of that.
To properly tune alerts, I probably should make some decisions around
filtering.
What I want, what I don't want, right?
And maybe it should be done not later but sooner maybe I should
just cut some messages and this could be some like filtering
Fabrízio: yeah some filtering configuration yeah make a lot
of
Nikolay: I just give these warnings shut them down so anything
like this it's more maybe wouldn't let make life easier because
usually what I don't want is some frequent noise for me.
And in this case, if we can discard it, it makes the tool more
lightweight, right?
So overhead lower.
Just brainstorming a little bit.
Fabrízio: You can also create some filtering for a specific user
you want only to track information about a specific user.
Nikolay: But this is only later, because you track all
Fabrízio: users.
Nikolay: I can't say I want only this user.
There is no such filtering.
Anyway, so you mentioned this cumulative statistics mechanism
appeared in Postgres 18, And this is great.
Postgres once again became even more extendable, extensible,
right?
Which is good.
I noticed you registered kind ID, I don't know, 28, right?
The kind of statistics.
Right now the process is very interesting.
You go to wiki page and put it there, right?
27 was pg_stat_plans from Lukas, right?
I think now you 28 also after you, who came, spock, pg_stat_login, right?
I think, yeah.
29 is spock and then number 30 is pg_stat_login.
So we have only 4 lines right now because this is super fresh.
And you were number 2 actually among these 4.
That's great.
I have 2 questions, actually.
I know there were some attempts to create such like this thing,
like cumulative counters for errors.
1 of them was called extension, or was called logerrors, right?
And I'm just curious, what are the benefits from switching?
Because we use logerrors in a few places.
I understand the internal benefit.
You use it in an official way right now.
And I suspect data in your extension will survive restarts, unlike
logerrors.
This is what I see easily.
Because they needed to implement their own piece of shared memory
and all the machinery around it, right?
Maybe it will survive as well if they implemented it.
What else?
If, obviously, you take care of warnings and your granularity
is better, maybe you will introduce query ideas, which will be
nice, but dangerous and expensive, right?
Like what else?
What's the benefit?
Fabrízio: So.
I wasn't aware about this extension that you are mentioning?
I don't remember.
Nikolay: Yeah, logerrors, 1 word without underscore.
Fabrízio: Ah, logerrors.
Okay, I found.
Nikolay: So, it's all I can answer for you.
The main benefit will be if it goes to core or at least as contrib
module, this will become packaging much easier.
Fabrízio: Yes, absolutely.
Nikolay: Yeah.
Yeah.
And since you chose this new approach, but what are the chances?
Obviously Postgres 19 is already past beta 1, beta 2 is coming,
so cannot be included in Postgres 19.
Do you have plans for Postgres 20?
Fabrízio: Yeah, for Postgres 20.
For sure next year.
Next year, yeah.
I will publish it for next year.
Yes.
Nikolay: That's great.
I'm definitely willing to support with any more testing and so
on.
I just want this to be there.
Because so many clusters I observed, which needed.
And I just, honestly, like, when we needed to analyze performance
using usually pgFouine and then pgBadger.
Fabrízio: pgFouine.
Nikolay: I love it was pgFouine, you remember?
And PHP.
Fabrízio: Yes, I remember.
You need to laugh.
Nikolay: Yeah, Perl, pgBadger.
And I remember guys who said, we're going to switch on a logging
or everything for 5 minutes.
It will be hard for everybody, but we need it because we need
to see the whole thing.
And now we have pg_stat_statements, pg_wait_sampling, pg_stat_kcache,
and also another approach, like Active Session History approach.
Great.
But errors are still a problem, right?
And I just don't want to deal with logs like that.
I want them to be a mechanism when we already understood what's
happening at high level and we need examples.
Then I want to go to logs, but the scale of the problem, like
to triage the problem, I don't need logs.
Fabrízio: Yeah.
Excellent.
Thanks for the question.
I think, do you have enough tools now, you don't need pg
Badger?
Excellent.
Nikolay: I haven't touched it maybe 5 years.
I don't need it.
Fabrízio: Yeah, me neither.
It's been more than 5 years.
Nikolay: But in bigger companies I deal with, we usually have
some already flow to process logs either from RDS, CloudWatch,
pull them, put them somewhere.
So they have it, but they usually have it not because of Postgres,
but for everything, right?
So some log collector and analysis, Elastic, Kibana, all this,
right?
But I touch it less and less.
And I don't need it too often.
So yeah, this is, once this is there, at least this contrib module,
life will become much easier when we have incidents, right?
And we need to quickly understand what's happening.
Michael has now.
Michael: I actually think this is more interesting, not necessarily
from an incident point of view, than from an ongoing minor issues
in the background type, things that would go unnoticed otherwise.
I think incidents, normally somebody's on the Zoom who can access
the logs and can check, does have access to those things.
But I'm thinking the warnings that are building up over time
that people aren't noticing, or the errors that are, they're
affecting some users but not enough to complain about.
Like those kinds of things.
What are our 10, what are this application's top 10 errors this
month or this year or whatever?
I think that's super interesting, even outside of incidents.
Maybe it helps with incidents too, but I like the offering.
Nikolay: Inside incidents, usually it's messy and the incident
produces a lot of logs.
It's really hard to deal with them.
You need tooling, you need expertise.
Of course AI helps to process in large amounts, But when you
have a lot of basically bytes to process, to fetch a few numbers,
it's easy to make mistakes.
And LLM makes mistakes when you feed too much information to
it.
If you have very structured counters, It's great.
Also, log can rotate.
You can analyze the wrong log file in the middle of rotation
or something, and so on.
And storing those counters in some time series, like snapshots,
over time is so much easier, so much more manageable.
And in general Postgres moves in this direction.
For example, we had checkpoints and autovacuum, most data only
in logs.
Now it's slowly moving to the pg_stat system.
For example, finally, pg_stat_bgwriter, which always collected
information not about bgwriter, but checkpointer and backends
as well.
Now it's already reorganized, so there's pg_stat_checkpointer,
there's pg_stat_bgwriter, it's more obvious.
And it's easier for LLM actually to analyze what's happening
because naming matters a lot right now.
That's why I asked pg_stat_log.
Is it like, I wish there was specifically pg_stat_errors because
they all have errors, what kind of, right?
But I understand the reasoning, warnings and everything.
I understand that.
So counter-efficient, the process of analysis efficient, that's
important.
And complete.
That's also important.
And fast.
Yeah, you can immediately understand when exactly the spike happened
without grepping.
Fabrízio: Happens often again about overhead.
I think the overhead introduced by this extension is way less
than pg_stat_statements, for example.
Nikolay: That's an interesting statement.
We should maybe somehow with benchmarks think about it.
Yeah, that's interesting.
Why do you think so, by the way?
Fabrízio: Because of what we do in the extension, it's a very
simple hash.
Yeah, but pg_stat_statements need to normalize the query and do a lot
of other stuff.
Oh, yes.
Yeah.
And it takes time.
It's a few cycles.
Nikolay: I proposed the introduction of query ID, right?
You don't need to normalize, right?
Fabrízio: No, I don't need to normalize.
Nikolay: Yeah, because in logs we also compute query ID mechanism,
right?
Michael: On the query ID front, I think 1 of the downsides is
it would balloon the number of entries you would need.
Like you've currently got the max entries to 1024, which feels
not that high, but actually if you think about how many people
actually run multiple databases on the same server and actually
have like thousands of users is probably pretty low.
Probably most people have a few users that are doing a lot of
volume and each of those things you mentioned is probably fairly
low cardinality so that by the time they all multiply it's probably
still below 1000 which is great But if you add query ID into
that as well, that's a much bigger number.
Fabrízio: Yes.
1, 1 thinking about introducing this, the query ID, maybe it
can be optional.
A Boolean configuration that you can turn off.
And then you always have the query ID and we issue don't collect
query ID to be 0 for example.
Michael: So what's the downside of being What's the downside
of increase if I increase max entries by a lot?
I guess that's just more shared memory and slightly more delay
on writing it out on restart.
Are there any other downsides?
Fabrízio: It is a map in memory that you to the time of how many
entries on your CPU.
And yes, but writing to the disk, I don't think unless you have,
you know, not
Nikolay: a lot of bytes.
Fabrízio: Yeah, no, because I'm not writing any text, only integers.
It's written.
Michael: It's very tiny.
Nikolay: Regarding query ID, pg_wait_sampling also has this as
optional.
Fabrízio: This parameter you can enable profiling per query,
so similar approach.
Nikolay: And I think it's even off by default.
I think so, maybe I'm wrong.
And we usually enable it because the information from pg_wait_sampling
is super useful, and we want it to per query.
So, yeah.
Cool.
Michael: Maybe add query ID, but also bump the default way up if
there's very little overhead to having 10, 000 or more I don't
see why that should be limited.
What do you think?
Fabrízio: Thanks for asking this.
This was an arbitrary number
Nikolay: that I got
Fabrízio: from my, probably should increase this and maybe match
with, for example, pg_stat_statements because it don't require
too much memory.
Be honest.
Michael: Yeah.
Nice.
I noticed you labeled it version 0.1.
What else do you need in terms of getting it to a version you
could commit to Postgres or 1.0 or whatever, however you want
to define like production ready.
Fabrízio: The thing that it's blocking it's submit is this issue
that I find in the worst case when we have all the entries allocated
and then new entries entering the dropped message.
I will not collect information because I don't have enough capacity
to collect.
And this is another worst case in the corner.
It's simple.
And then I found that overhead, it's not great.
It increased by 27, 30% of overhead.
And actually it was not great.
I think just this for now, a starting point.
Maybe query IDs, something to discuss with the community, I
don't know.
There are ongoing work in the past, there was ongoing work in
the past, work in progress.
PR, no, maybe it's a patch.
We introduced pg_stat_log_error or something.
It was proposed by Joe Conway, And they collected this
old discussion.
They collected the file name, the source code file name, the
line number, where it was coming from, that I don't think for
real use case.
It's a good idea, I mean, for SREs, DBREs, or people that actually
use it.
I don't know if it's useful to know where is the C file that
the SQLSTATE came.
I don't know.
Maybe I'm wrong, but I thought that was not useful.
And then I started by the base stuff to really observe the log.
A backend type, what's the database ID?
What is the error level and SQL error code?
Because I think this is the basic information we need.
Now that's enough information, right?
Yeah, right.
Yeah.
And be an option.
Nikolay: File is always the same.
It only can change after rotation, right?
Fabrízio: Not the log file, the source code file.
Sorry.
The C file.
Something dot C inside Postgres.
And the line number.
Because we have this information.
Nikolay: So I see.
So we have some error code, for example, my favorite, XX001, for
example, right?
I think it's some corruption or something, right?
Or 002.
And we know that in Postgres source code, it happens and it can
occur in 5 places, for example.
And we want to know exact location among those 5, right?
I think this can be useful.
It reminds me, like when programming, you troubleshoot with printlining,
right?
And you have like, problem happened, problem, test, test, or
like something.
And then you think, oh, they're all this, you need to start distinguishing
them, right?
You say, okay, test 1, test 2, test 3, and then which 1 of them
fired, right?
I can see it may be useful, but again, what overhead?
What's the price, right?
Fabrízio: Yeah, the price is we need to store variable-sized
strings.
The line number is not a problem, but the source code file name,
it's a string with a variable size, then it can increase the
memory.
Depending on how many entries you store.
But yeah, the source code file names are not very big names.
But I don't remember if you have multiple file names, equal file
names with different directories.
I don't think so.
Nikolay: I think it might happen.
Fabrízio: It might happen.
So if we start only the file.c, line number 1000, and then you
have 2 files the same name.
Nikolay: Can be compared somehow I think quite easily so to a
few bytes only but is it really needed?
Maybe no, but it's a nice feature I can imagine like to trace
the exact location.
What I'm concerned about, not about additional bytes, but it
will amplify number of entries significantly.
If it's a common, if this error is popular and we have 100 locations,
so now we have 100 different errors basically, right?
But it's interesting.
It's interesting anyway.
Yeah.
We should have it and this information should be present in logs.
This is very detailed information.
Go to logs and troubleshoot.
It's the level of this extension, we just want counters to observe
holistically everything.
Right?
Then if you identify the spike, go to logs.
Now the problem is we cannot identify spikes at all.
So we're...
Yeah.
So I agree with you, it's too verbose stuff.
Yeah.
Okay, it was some kind of brainstorming session, a little bit.
What I think would help is If somebody starts using it, maybe
not in production right away, but in some benchmarking, like
I'm going to start using it in some benchmarking activities in
lab environment, right?
So 1st, like low risk.
If it crashes, I will let you know.
But eventually I think the production, it should be a production.
Fabrízio: It could be very useful.
Nowadays it's packaged for Debian and RPM on the PGDG repositories.
So it's available there.
I can provide some Docker images, but a Docker image is very
simple.
You can pick a PG 18 and just install a new package there, pgdg
is there.
Nikolay: Cool, but also Postgres 20 development already started,
so we're already inside it.
Let's go there.
Let's
Fabrízio: publish it.
Yes, let's go there.
Nikolay: You know, let's get familiar with it.
I want it to be in core.
This is a great start.
Fabrízio: Yeah, it should be.
Michael: Was there anything you wanted to mention that we haven't
asked about?
Maybe some of the caveats that you wrote about or anything else?
Fabrízio: 1 thing that everybody can potentially ask is, it requires
a Postgres restart because I need to allocate shared memory.
So there's no way nowadays in Postgres to do this without something.
I think this is 1 of the reasons that Nik would love to have
this truly in core, then we don't need to restart Postgres.
But it depends on how it is implemented, right?
Because there are some configurations that we, when we change
it, it will restart anyways.
Nikolay: Yeah, this is definitely why I think pg_stat_statements
should be in core as well.
We still have cases when it's not installed and it's not present
in shared preload libraries, so we need a restart.
Michael: And this requires shared preload as well, doesn't it?
Fabrízio: And there's only another caveat that we didn't talk
about is the parallel query, right, because there are different
backend types, parallel queries.
There are like normal client in backend that is the leader,
and then there will be a parallel worker or something.
So don't count twice in the message maybe.
Yeah.
So I'm not filtering anything.
I just collecting on the log because it go to the log the information.
It passed through the emit_log_hook hook and then I capture it,
the leader and also the parallel workers.
So we filled a monitoring tool and thought that I have parallel
workers, maybe you should filter it out, but double count something.
It's 1 caveat, it is on the README.
Michael: Yeah, I thought it was really interesting.
I thought that was a good 1 to call out.
And I thought the other 1 that was interesting for monitoring
tools was the fact that database name or username could be null.
And that's expected, like some errors happen before there's a
context of having a database?
Like that's something I would have forgotten to do.
So for example, I knew I wanted to monitor 1 database on Postgres.
I would have filtered by that and only looked for errors, but
I need that database name or null.
And then I look at errors across both of those.
Fabrízio: So that
Michael: was a really good call out I thought.
Nice 1.
Fabrízio: I didn't thought too much, but maybe another thing
that's interesting to collect and aggregate is application name.
Nikolay: Oh, it's Pandora box.
Fabrízio: Yeah, I know.
Nikolay: There is a great idea that, well, what about client
address and IP address and so on.
It could go far.
There's a great idea that all such extensions like pg_stat_statements,
pg_stat_kcache, pg_wait_sampling, and let's say pg_stat_log, they all
must have an additional extensibility piece so I could define
my own dimensions.
For example, if my query contains a comment which says, this
query originated from this part of application and anything,
I just can tag my queries.
If those tags could be extracted from comments and allow me to
group by tag value inside pg_stat_ statements or any such
extension.
This would be great.
This is a big lacking piece.
And right now, the way to go is to avoid the cumulative statistics
approach completely and just use, instead of counters, just use
sampling.
You can implement the same in Active Session history approach.
You sample pg_stat_activity, you have these comments, you
extract outside of Postgres, right?
And then you can finally answer, okay, I/O wait event, we mostly
spend 90% of I/O registered in wait event was in this piece
of application, this part, because it was tagged.
In the case of your extension, same thing.
If something is erroring out, group by database, group by user,
Even application level, it's great, but sometimes we need some
custom segmentation.
And now it's like it's difficult right now.
This is maybe future of all those extensions to have custom tagging
mechanism.
Maybe.
These discussions, I know people and myself, we discussed it
over years.
You actually, Fabrízio, is a part of the ground group where we
discussed it, I think, a couple of times.
Great.
Thank you for coming.
I enjoyed it.
I wish all the best to this extension and all your work.
Fabrízio: Thank you very much.
Nikolay: Thank you.
Thank you.
I hope
Michael: absolutely good luck for version 20.