Nik and Michael are joined by Fabrízio Mello to discuss his new extension pg_stat_log. Here are some links to things they mentioned: Fabrízio Mello https://postgres.fm/people/fabrizio-de-royes-mello Timbira https://www.timbira.com.br PlanetScale https://planetscale.com pg_stat_log https://github.com/fabriziomello/pg_stat_log Custom Cumulative Statistics (docs) https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-ADDIN-CUSTOM-CUMULATIVE-STATISTICS Custom Cumulative Statistics (wiki page) https://wiki.postgresql.org/wiki/CustomCumulativeStats logerrors https://github.com/munakoiso/logerrors pgFouine https://www.postgresql.org/ftp/projects/pgFoundry/pgfouine/pgFouine pgBadger https://github.com/darold/pgbadger pg_stat_checkpointer https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-CHECKPOINTER-VIEW pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling RFC: pg_stat_logmsg (patch proposal by Joe Conway) https://www.postgresql.org/message-id/flat/89742024-d51a-c66b-90b9-67f837072cd2%40joeconway.com ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
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.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...