Michael: Hello and welcome to PostgresFM,
a weekly show about

all things PostgreSQL.

I am Michael, founder of pgMustard,
and today I am joined by

Alicja Kucharczyk, Program Manager
for Azure Database for PostgreSQL

at Microsoft and organizer of the
Warsaw PostgreSQL user group.

Thank you so much for joining me
today Alicja, it's an honor

to have you here.

Alicja: Thank you for having me
Michael.

Michael: Wonderful, well I proposed
a topic this week but mainly

because you're one of the biggest
fans or at least advocates of

this tool, I think in the world.

But it's probably an underused
tool, in my opinion anyway.

I definitely don't use it at times
when I could or should, and

I know most people that could be
using it have never even heard

of it, at least newer users.

So what are we going to be talking
about today?

Alicja: Yeah about my favorite
tool which is pgBadger which is

totally open source which is really
great it's like allows you

like to do really comprehensive
troubleshooting and I believe

like it's yeah top-notch of the
class.

Michael: Yeah absolutely do you
want to give us a little bit

of background on the tool or how
people use it?

Alicja: Yes absolutely so maybe
I will start with my own story

because I've learned that the tool
exists over, I believe, 10

years ago when I was working for
a Polish Bank.

And the process in there was like
each time something was happening,

they started to generate a pgBadger
report.

And that was just always the
first step that they've been

doing.

So that was the place where I learned
that this tool exists.

And over the years, actually, because
I was moving from company

to company, and I started to be
a Postgres consultant.

And back then, that started to
be my main tool for doing the

troubleshooting.

Because as a consultant, you don't
really know the system.

Like you're going to the customer,
you know something has happened

and the customer is saying, for
instance, CPU is very high or

latency of the query suddenly has
grown, but you still don't

really have any knowledge about
the system.

And pgBadger is really one of the
best tools to quickly

get knowledge about the workload
that is happening within your

database.

And I really like pgBadger also
that it's showing you like

really comprehensive picture because
you might know other tools,

like similar tools, like pg_stat_statements.

And it's not that I want to say
that this is a bad tool or anything.

I do use pg_stat_statements.

I know that many people do, but
pg_stat_statements is actually just

one tab in the pgBadger report.

And next to that, you've got also
like report about the temporary

files.

You've got really nicely aggregated
errors.

So, everything that you've got
in the logs that are errors, fatals,

warnings, it's really nicely aggregated
on the events tab.

You would also have the logs and
logs with CK, right?

Not logs, but logs.

I know it's easy to mix these two.

So you also have these logs, you've
got the number of connections,

number of sessions.

So you really have a comprehensive
view.

For instance, if you've got
pg_stat_statements, you see, okay,

this query was executed 1,000 times,
and the total time it was

this and like the average time
was this, right?

You would have like the same thing
in PgBadger, but the difference

is in PgBadger, you can quickly
go to another tab, for instance,

temporary files tab and see that,
okay, this execution time was

caused actually because this query
was generating temporary files

or maybe it was waiting for a lock.

So that's why I do prefer PgBadger
over pg_stat_statements and

other tools, because it's giving
me this comprehensive view.

Michael: Yeah, I like it.

Shots fired right up top.

I'm a big fan of pg_stat_statements
as well, so I'm hoping to

be somewhat converted here.

I definitely think there's pros
of both.

So with PG Badger, it's a log analysis.

So it's a standalone tool as well,
right?

So open source, as you said, we
can install it wherever and analyze

our log files for anything that
we can spit out to the log.

So as you mentioned, locks, yeah,
so wait events, errors as well,

that's a big missing part of pg_stat_statements.

So sometimes we have, it's quite
common advice to have a timeout,

right?

If you have queries that are taking
more than, I don't know,

some, on some systems might be
30 seconds, time them out instead

of measuring, instead of letting
them complete.

In pg_stat_statements, we don't
see those at all, but in PgBadger,

we can see those because

Alicja: of the errors, right?

Absolutely.

Yes, yes, yes, yes, yes.

And the errors actually, you know,
it's helping with other problems.

Like recently I was troubleshooting
logical replication errors

and if you are just, have the plaintext
book and you've got just

1 error, it's really not easy like
to go up like thousands of

lines up and see what was the root
cause error, right?

Because sometimes you've got 1
error and this is causing the

whole other errors, right?

Like in this case.

And in PgBadger, you're seeing
like that.

That's great because of the aggregation
that pgBadger is doing.

Michael: Nice.

So, because we're reliant on the
logs here, I would like to ask,

a lot of people won't have, a lot
of the default settings are

to not log a lot of important settings.

So a lot of people's logs, if they're
new to Postgres or if they're

using a managed provider and haven't
set any settings, they won't

have many of the things.

So if they ran pgBadger now, I'm
guessing a lot of them wouldn't

get some of the reports you're
talking about because they wouldn't

have the right settings on.

Is that fair?

Alicja: Yes, yes, absolutely.

Like with default settings, you
would probably have like a very

poor pgBadger report because as
you said, like pgBadger is just

Perl script that is just taking
the text files, the text logs

as the input and then generating
HTML report as the output, right?

So whatever is in logs, it will
be regenerated, like it will

be parsed and it will be, you know,
aggregated in this really

nice pgBadger format.

But if something is not in the
logs, then obviously you will

not see that, right?

So for instance, if you want to
see the locks this time, like

you need to have log_lock_waits
on.

On.

There is 1 setting in Postgres
that you need to have locks

logged to be able to generate that.

Michael: Yeah, log_lock_waits,
I think it is.

Alicja: Log_lock_waits, yes.

I believe this is the one.

And this is responsible for the
locks tab in the pgBadger.

So if you've got this off, you
will not see anything.

Also like the log_line_prefix is
super important.

So, you know, the default prefix,
it's pretty much null.

And I'm using the one that is actually
recommended by pgBadger,

and it contains a lot of stuff
like database name, user name,

application name, of course, like
the timestamp, and it also

has like a host.

So the IP of the server that the connection
is coming from.

And this allows you to have really
like, you know rich pgBadger

because pgBadger is able also to
split the queries into different

tabs per database per user.

So it's able like to show you for
instance how many select queries

were like on a particular database
or were issued by a particular

user.

But you need log_line_prefix for
that.

Otherwise, if you've got really
a short default, you will not see

a lot of useful information.

But I believe in here, the pgBadger
documentation is pretty

good.

You've got the entire list of the
settings that you need to set.

And most of that, like most of
that is safe.

Like I would say most, because
you've got, of course, 1 setting,

log_min_duration_statement, which
is super, might be super

dangerous, right?

Of course, maybe just to explain
log_min_duration_statement, it's

the setting that is regulating
how many queries we want to log

into the text logs.

And by default, this is minus 1.

It means no.

And 0 means log everything.

So everything means even select
1, like something that is under

milliseconds, right?

With 0, you will see like a huge,
huge impact on your database.

So, you know, you always need to
kind of adjust this to your

system, to the workload you've
got, how intensive, you know,

your database is processing the
queries.

If you've got an analytical database,
right, like super huge data

amounts that you've got, yeah,
5 queries per hour, probably not

many of you, but yeah, let's say
log_min_duration_statement

0 is okay then.

But with millions, thousands of
queries per second, this is,

yes, this is a killer.

Like I saw even like 40% overhead
of the overall performance

with log_min_duration_statement
0.

So this is dangerous.

I usually start with something
around between 1 minute to 10

minutes, depending on the system.

Oh, wow.

Yeah.

So, you know, depending on the
particular intensivity, like,

let's say, of the system, depends
what customer is telling me,

okay, it's super intense.

If it's not, right?

So let's say we are starting from
1 minute and then we see the

overhead.

If it's huge, we are going up.

If it's not huge, then we try and
go a bit down with the setting.

Michael: When you say 1 minute,
you mean only log the duration

of statements that take longer
than 60 seconds?

Alicja: Yes, yes, yes, yes.

Michael: I thought you're going
to go much lower, but that makes

a lot of sense, just in case there's
a huge overhead.

Alicja: Exactly, exactly.

This is like to be on the safe
side, right?

Because otherwise if you've got
a production system, then you

go too low and then you've got
an outage.

Michael: And I don't think this
requires a restart, right?

So you can reload the config and
it's not a huge overhead to

change that 1 but yeah that's
a really good tip.

Without that we get almost nothing
like we don't get any reporting

on the slow statements but I guess
the trade-off is if our system's

biggest bottleneck is CPU,
let's say the customer mentioned

they have high CPU, and that's
mostly being caused by millions

of executions of very fast queries,
then we don't spot them.

So it's that trade-off between
slow queries being the issue versus

lots of fast queries being the
issue.

Alicja: It is, but you know in
this kind of, this is a very good

point because you are right like
I saw outages when we had like

yes a lot of super small queries,
right, **that were executed often

like thousand times a second and
you know we've got 2 ways actually

to do that.

One way would be like to have some
kind of cron job that would

change log_min_duration_statement
to 0.

And after some short period of
time, like switch back, right?

That would be one.

And yeah, and the second one would
be, there is like the new method

that I haven't tried yet on production,
but there is the sampling.

So, you know, in Postgres, like,
I haven't tried it yet, but

it might be something to consider.

So, for now, I was just doing cron jobs,
but to be very honest

with you, it's a problem when you
are analyzing the report, right?

Because, yeah, you see really weird,
like, charts.

So, you always need to keep that
in mind that you've got this

switch in the cron job.

Michael: Yeah, or for the fast
queries, rely on something like

pg_stat_statements if you have
that on already, for example.

But back to pgBadger, I looked
at the docs and some of the other

ones that people might want to
switch on that have low overhead.

Stop me if I mention one that is
dangerous.

But we have log_checkpoints, log_connections, log_disconnections,

log_lock_waits you mentioned already,
log_temp_files.

Do you do any...

So the recommendation in the pgBadger
Docs is to set that to

0?

Alicja: No I just set it to 0 you
know.

Michael: Okay great so that's good
that that's you don't see

that as being anywhere near as
dangerous **I have seen some recommendations

to start with a higher setting
of that and go downwards as well

which I guess people can do if
they want to be extra cautious

but if you've tuned work_mem at
all already then I guess you should

be pretty safe to put that to 0.

Alicja: I've never seen any problem
when I tweaked with temp

files or checkpoints to be very
honest.

Like maybe there are some narrow
use cases, but for, you know,

over the years, I've never seen
any problems with other settings.

Like the same with autovacuum,
like people also were a bit afraid

about the autovacuum, but **it's
not, it's really just a couple

of lines like per minute and yeah,
it will not hit your system

that much.

Michael: Nice.

And yeah, you've got one that I forgot
there, which is log_autovacuum_min_duration.

vacuum min duration.

Set that to 0 as well so that we
get the important vacuum stuff.

Great.

So that lets people get this set
up.

And I've checked some hosting.

So seeing as you work on a managed
service provider, Do you have

some of these set by default or
is this something that people

need to go in and change?

Alicja: Yes, for instance, yeah,
because I work at Microsoft

it's the easiest for me to talk
about how we've got the setup

done.

I don't know that much about other cloud
providers but we've got log

connections, log disconnections
to on, and users cannot change

it.

It's read only.

And it's because we need it for
the internal telemetry of ours.

So this is just on.

Also, you know, pgBadger needs
to have logs in English.

So also this is by default on Azure.

So a lot of them are already enabled,
already on, like on Azure.

So this is good news, but still
you need to enable logs, you

need to enable autovacuum, you
need to enable logging like

for other stuff and log_line_prefix.

I know that not all cloud providers
you are able to change

log_line_prefix, as far as I know
at least.

Yes, we do allow that.

So this is good news, but I've
heard that not on all, like probably

AWS doesn't allow that, as I remember.

At least it wasn't a couple of
years ago, you know.

I remember writing a script,
like to parse the logs, to

be able to generate like pgBadger,
because we were not able to

change log_line_prefix.

And yes, they also have this, they
had really weird format.

So we needed a script that would
parse that.

But good news is that pgBadger
introduced support for this log

format.

So you've got a separate format,
which is RDS.

So you don't need to have custom
scripts anymore.

So this is good news.

Michael: Yeah, I was so impressed
reading through the pgBadger

docs, all the different formats
they support is incredibly

flexible tool, almost as if it's
designed by people that are

using it and built by people that
use it all the time.

But definitely tons of options
and lots of auto recognizing formats

and file types and things like
that.

So for a lot of people I would
guess it would just work and for

people on cloud providers you've
written a great post for using

it with Azure but there's also
I found a couple of posts from

people from the RDS team and from
Google CloudSQL teams that

explain how to use it with their
systems as well.

If any other cloud providers are
listening and have guides, please

send them to me and I'll include
them up in the show notes.

But yeah, it feels like this is
a tool people could be using

even with unmanaged Postgres
providers.

Alicja: Exactly, it makes me really
happy to see the blog posts

from AWS and Google Cloud because
exactly as you said it means

that they're using pgBadger,
like the users of the managed

services are using pgBadger,
so it was probably needed.

Michael: Nice, yep and also it
means this thing can live on,

right?

We don't have to completely replace
it with cloud native tools,

it still should work, right?

Still Postgres, the only place
it currently puts some of this

information is in the logs so at
some point we're gonna have

to look at the logs and having
a tool to do so often helps.

Cool, all right so we've mentioned
the min duration being a potential

gotcha are there any others that
we need to be cautious of when

using this?

Alicja: About the parameter settings,
actually this is like the

most dangerous.

So not that much I would say, like
the other thing you might

have problem with, like it's the
size of the log itself.

So I had sometimes like a problem,
you know, when I was like

receiving from the customer just
terabytes of logs and it was

okay, here are my logs, just generate
whatever you want from

that, right?

And Yeah, that also might be a
problem.

And although, you know, pgBadger
has this minus J option, like

most of Postgres tools, right?

It's still like on my own laptop,
it was like days.

So, Yes, so still like if you've
got like really, you know huge

amount of logs you probably still
need powerful VM just to parse

the logs.

Otherwise like yeah, it will not
work

Michael: Yeah, I saw I saw it has
both -j lowercase and -J uppercase So it has like two different

ways of handling.

I think the first one is for parallelizing,
like having multiple

CPUs handling a single large log
file.

I didn't hear of terabytes.

All the examples I saw were in
gigabytes, so that's quite scary.

But I think "-J" was even faster
if you had, let's say, like if

you were given 20 Large log files
and you wanted to give 20 CPUs

and handle them one at a time like
one CPU per file So that's quite

cool that it has multiple ways
of handling like huge workloads,

but terabytes of logs is terrifying.

Did they have to?

So I saw in that, like it has so
many options by the way, I have

a, in fact, a quick trivia question
for you.

It has so many dash single letter
commands that I had noticed

there are only 2 letters in the
English alphabet that it doesn't

have a Dash that letter Can you
guess which letters they are?

Only K and Y. It's not fair only
K, and and there is

even a they even have an option
that's keep comments.

So my recommendation to the pgBadger
team is to make that the

K, and then they only have to find
1 more, and they have the whole

alphabet. Anyway, sorry, that's
a that's not But

Alicja: no, no, this is actually
great point because sometimes

you know, the customers has got
like some non-common questions,

right?

For instance, I had a customer
that had a problem, like performance

problem, and they wanted to share
pgBadger with me, but they

were not able to because of the
data, right?

Like, because they were not able
to show me the queries.

And I didn't even know, but yes,
pgBadger has so many options

that I quickly check and there
is like this anonymized option

like yeah so you can you can find
a lot of stuff like really

a lot of options in pgBadger.

So this is actually a great point.

Michael: I was gonna ask about
2 of them actually, B and E, so

beginning and end.

If we have a huge log file, but
we only, let's say it's from

a month of logs or something, and
we only care about, let's say

a day or 2 that we had problems,
if we set beginning and end,

would that drastically speed things
up as well or are we still

Alicja: going to?

Depends, right, of the size of
the file.

So it's more like an operating system
question, I would say, because,

you know, sometimes if you've got
really huge file, like it,

you need to read your operating
system, you need to read it anyways,

right?

So in this case, it might not help
that much.

So, you know, it's a long story,
but, you know, it's really huge

logs, like huge files.

I've been trying like a lot of
stuff and I've been trying like

to split the files, you know, using
just the split command.

Like, I was trying like a lot of
different ways, but finally

what works the best is just a huge
VM.

Like this is the easiest way and
you just put that on a huge VM

and you've got it solved.

Michael: Nice.

Cool.

Okay.

I saw you hosted 1 of the Postgres
Friday.

We've done a couple of submissions
of podcast submissions to that

event and yours was on pgBadger
but I saw that Gilles Darold submitted

a post and 1 of the things they
mentioned was flexibility so

they they really like to typical
Postgres consultant I go not

typical but extra on the Postgres
side they parse the logs into

a Postgres database in order to
be able to query them, what like

giving them a little bit more flexibility.
So I guess we are somewhat

limited by the reports that pgBadger
gives us, but in the real

world, it's giving us so much useful
information that I guess

those cases are a bit more like
few and far between.

And we get most of the way there
just with the pgBadger report.

Is that fair?

Alicja: You know, I, yeah, I saw
the answer from Leticia and

you know this is actually interesting,
like parsing this to the

database, especially like I had
a lot of ask about that from

ex-Oracle DBAs, right, they always
ask, you know, we're asking

if there is a way to do that, right?

So it might be useful, but when
I was a consultant, because I'm

not that much anymore, like I do
prefer to use something that

I can just ping a link to, like
the tool that is already there

and it works and just pinging the
link, like generate me data,

you know, focusing on writing my
own tools.

So but OK, I don't say it's wrong
or something, but it's just

it's just easier for me.

And also what I like, it's the
visual part, because like the

visual part, it's really something
that allows you super quickly

to go to the root cause.

It's not that easy with the text,
like if you're doing the query

and you've got the result okay
but you need to focus a bit more

and you know it's pgBadger
you've got this chart and you

just you know see everything straight
away so it's the way easier

way faster for me and you've got
like all the answers straight

away so you know it's just a matter
of preferences I would say

my preference is to have graphical
presentation.

Michael: Yeah, that's a huge pro
for a lot of people me included.

You've also mentioned another one
there which I don't think we've

mentioned is how tried and tested
this is this is a I think I

looked it up and it was 2012 pgBadger
v1 came out April or May

time and it's version 12.4 now
with a ton of bug fixes and all

sorts of feature improvements since
then.

So to start writing your own tool,
we've already mentioned some

of the features we get, but there's
honestly dozens of settings

that it has, more than the ones
I mentioned.

Not just every letter covered,
but multiple letters have lowercase

and uppercase and there are quite
a few that don't have shortcuts

like there's another few dozen
that just you know you have to

set separately so you're yes incredibly
flexible and tested like

it's been tested for many many
years which is worth it worth

a lot.

Alicja: Exactly, exactly, you know
like to be honest to write

your own tool that would be that
comprehensive it would have

like so many dashboards so many
information in it like it's years

you know so yeah I do prefer to
have this tool instead of, you

know, writing my own, but I totally
get people that prefer to

write their own tools.

Michael: Nice.

And is there anything that they
could be doing?

I think, is it Gilles Darouine?

I don't know quite how to pronounce
their name, but the maintainer,

is there anything they could be
doing or is there anything you'd

like to see in pgBadger in the
future?

Alicja: I would like to see the
support for Azure.

That would be my main thing.

And you know, probably that also
like the thing about the logs,

and I know this is not easy because
it's not that easy like to

get that from the logs.

But you know, in the logs tab,
you would see only the queries

that were locked, not like the
queries that caused the lock.

And of course, there is a simple
reason because of that, because

it's how Postgres is doing this,
right?

But sometimes if you've got log
min duration statements to 0,

right, and you've got PID in the
prefix, you can just correlate,

right?

Because Postgres would normally
tell you like, okay, this PID

is blocking your query, right?

So then you can search for this
PID and maybe you've got like

the query and try to correlate
that because it's, you know, it's

not that useful.

Like if you just see like the queries
that are locked and not

what is actually locking the queries,
right?

So you need to have an additional
cron job with the lock query to

compare with.

So yeah, that for sure would
be an ask, but I'm not sure if

this is actually the ask to Gilles.

I also am not sure how to pronounce
his name, so I'm saying Gilles.

I'm not sure if this is an ask
to Gilles or more to Postgres contributors,

to be honest, just to have that
in the logs.

Michael: Yeah, to log more information.

So yeah, that makes sense that
it might be there.

Cool.

And yeah, it's released under the
PostgreSQL license, which is

one of the most permissive licenses
I've ever seen, which is awesome.

So thank you for doing that.

Thanks for all your hard work on
it.

Is there anything else you'd like
people to know?

Alicja: Nothing's coming to my mind,
to be honest.

Michael: Well actually, because
you started with throwing shade

at pg_stat_statements, I did wonder
if there were any...

So, like, for example, with pg_stat_statements,
we can see things

like buffers.

We can see the amount of work done
by each query.

So reads, writes, temp, shared,
that kind of thing.

I don't think, is there a way to
see that kind of information?

I guess we could look at the auto_explain
logs but I don't think

pgBadger is doing anything at the
moment to aggregate those per

query group.

Alicja: No, I've never seen that.
Just thinking if we would have

like more, if we would change error
verbosity, if that would

be logged and then presented, but
I haven't seen that.

So I don't want to guess, but you
know, like for me, actually,

the pgBadger, it's like I'm going
to the top queries.

And when I see top query, like,
yeah, maybe let's go first through

the process.

That will be easier.

So when I see the top queries,
sometimes it's just, you know,

you just see what's wrong.

Like, it's really pretty often,
like, much more often than, you

know, you would guess, like, just
you see, okay, this is not

in, right, or this is something
similar.

So, you just see that, right?

So, you don't need to even run
explain analyze but if you don't,

then I just take the query
and run explain analyze on

that and yeah, that's the next step
I would do. So but yes, you

are right.

Michael: And then you can ask for
buffers.

Alicja: Exactly, and then like buffers,
yes, absolutely, I would

use that option.

Michael: So just to clarify on
that, I think this is great, so

top queries is a really good point
and it orders those by total

execution time.

So, across all calls, right?

So, you see, it could be a slow
query that's run a few times that's

taking up the most resources or
it could be a fairly fast query

that's being run thousands of times
per second, as you mentioned,

and they're ordered by, I believe
the main report is by total

execution time.

Is that right?

Alicja: You've got like couple
sections on this page.

So you've got the most important
one that I'm using.

It's exactly the one that you mentioned.

So the total execution time, that
this is always like the first

thing I'm looking at, like, you
know, what took the most time

of this, you know, from the system,
which queries that it doesn't

matter for me, just one query, or
it was like multiple executions

of other queries, right?

So this is like probably the third
section from the top, but

you also have other aggregations,
like the slowest individual

queries, other stuff, but I usually
don't look at that, to be

very honest.

I just, you know, I just taking
the, this is absolutely my favorite.

If I need to then, yeah, I go,
but it's absolutely my main point,

like to go there and see.

Michael: Nice.

That's what I like to recommend
people do on pg_stat_statements

as well.

But I guess with this one, with pgBadger,
we get more of an opinionated,

shows people what to look at rather
than pg_stat_statements.

People can query and order by whatever
they want.

So I see a lot more people making
that mistake or just looking

at their slowest queries rather
than the totals.

Amazing.

Alicja, thank you so much for your
time today.

Is there anything you want to plug
or anything you want to share

with people?

Alicja: Actually, if I may plug
my songs, because I just realized

not many people do know my songs.

I produced two songs, the first was
about the vacuum and the second

one actually you were the author
of the lyrics, right?

So you know there are like two Postgres
songs out there and yes

We are co-producers of the second
one.

So yeah, that would be absolutely
my, my plug.

Michael: That's kind of you.

The lyrics are extremely basic
for that second one.

So I apologize to people in advance,
but you did an awesome job

with the vocals and the, that was
not me, the vocals and the

video as well.

It was a nice one of all the Postgres
events so yeah you did an

awesome job I will happily link
those up in the show notes.

Alicja: Thank you.

Michael: Thank you so much Alicja,
it's been a pleasure take care.