Michael: Hello and welcome to Postgres.FM,
a weekly show about

all things PostgreSQL.

I am Michael founder of pgMustard.

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.

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.

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.

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.