Nikolay: Hello, hello, Postgres.FM.

My name is Nik, Postgres.AI, and
as usual my co-host is

Michael, pgMustard.

Hi, Michael.

Michael: Hi, Nik.

How's it going?

Nikolay: I'm very good.

How are you?

Michael: Yeah, very good also.

Nikolay: It's again this time of
the year, right?

When it's obviously the right moment
to discuss something.

We make a mystery of it, but people
already saw the title.

And I'm pretty sure you won't hide
it in the title, right?

This elephant in the room.

Michael: I was going to call the
episode Christmas for Postgres

users.

Nikolay: Big box of gifts.

Michael: Exactly.

Nikolay: Cool.

Okay, let's talk about it.

Michael: Well, I think for anybody
brand new to Postgres or like

relatively new to the show, we've
done these episodes each year

when the major releases come out
and Postgres has got a really

good track record for many many
years now of doing a single major

release each year around this time
of year and that's the only

release in the year that will include
like new functionality,

breaking changes, you know very
semantic version style major

version.

Nikolay: No it's not same there
at all And we have many years

we have a dispute inside my team.

Same there or not same there.

I noticed by the way, Tiger Data,
Timescale, they appreciate

Postgres approach, only 2 numbers,
not 3, because original SemVar

is 3 numbers.

And 3 numbers, I always have, like
I understand the logic behind

3 number versioning, But I like
Postgres approach much more.

It's much more simplified and so
on.

Michael: Cool.

Well, breaking changes will only
ever go into a major version,

but you're right, even the major
version, we would still cut

a new major version even if it
was only new features, even if

it didn't have breaking changes.

So that's a good point.

So yeah, you're right.

Nikolay: Yeah, yeah, yeah.

It's only 2 numbers, but yeah,
major version, minor version.

Simplicity.

Michael: Yes.

Nikolay: And major version changes
every year.

Michael: Major version every year,
minor versions at least every

quarter, sometimes more often if
they need to do quick security

fixes.

So yeah, this is the time of year
where we get new features and

the only time of the year that
we get new features in core Postgres.

So as such there tend to be a lot
of them, so we've got a lot

of things we could be choosing,
no chance we're going to talk

about them all today, but the idea
is to talk about some of our

favorites, some of the kind of
categories of things that are

coming, things that we've been
kind of involved with or emotionally

attached to or wanting for many
years, all sorts.

Where would you like to start?

Nikolay: Well, first of all, maybe
we should just mention the

features which we already discussed
in depth.

And we had the whole episode sometimes
for some features which

are going to be out.

Right, so for example, skip scans
in B-tree, right?

Michael: Yeah, that's a huge one.

And we had a great chat with Peter
Geoghegan, didn't we?

All about them.

Nikolay: Right, right.

And the TLDR version of it, it's
sometimes index on two columns,

for example.

It will be able to support searches,
for example, only on the

second column.

So this rule, we got used to it
over time that we must put the

second column on the first place
if we need searches or create

additional index only on that column,
second column.

Now it's sometimes not true and
things become more complicated

when we make decisions on indexing,
right?

This is my perception, oh, like
it's even more complicated now.

Michael: Yeah, I would add a little
bit that it's about the efficiency

of scanning that index as well.

So we could previously scan a full
index for things that didn't

have filters on the on the leading
columns it just wasn't it

had no chance of being efficient
or it's very unlikely to be

efficient depending on the exact
case now we can efficiently

do it or Postgres can efficiently
scan skipping any equality

like across the first column if
the first column doesn't have

high cardinality.

Let's say I think you gave the
example a while ago of a Boolean.

So if we had only true and false,
we could jump to the true and

check that for the thing we want
to filter on, and then skip

the rest of the true values and
then jump to the false where

we, and pick up again there.

So it's a much, much more efficient
way of being able to scan

indexes that aren't perfect for
our query.

Nikolay: For performance it's excellent
news.

Excellent.

I'm just thinking about the decision-making
process when we have

a lot of tables and complex workload
and we need to choose proper

index set.

And I'm already, as you know, I
shifted my mind totally to full

automation because of full self-driving
idea, full self-driving

Postgres.

And I see several good attempts
to create index advisors mechanisms.

1 more is from Konstantin Knizhnik,
there is blog post and Neon

published this week.

So and there is also a pganalyze
Index Advisor.

I was just thinking how this new
feature will affect those tools.

Michael: Interesting to see.

The good news for users though,
this is 1 of those features that

if you upgrade you might just see
certain queries get faster

without having to add new indexes.

If you haven't thoroughly gone
through all of your queries, optimizing

them with perfect indexes already,
there's a good chance that

some of your existing queries will
better serve queries that

you haven't optimized.

Nikolay: So if in the past we could
consider something like this

as a mistake.

Now, like, Postgres is more foggy
in this area.

Michael: Much more.

Yeah.

Nikolay: Good, good.

Michael: So yeah, this is 1 of
those cool features that I think

loads of people are going to benefit
without even necessarily

knowing that they're going to,
or...

Nikolay: As usual, my advice remains
to verify everything with

real testing, with proper datasets
and settings of Postgres Planner

and work_mem, which is not Postgres
Planner setting.

And just verify everything and
so on.

Cool.

Michael: Testing and

Nikolay: so on.

And maybe, maybe we will be able
to drop some indexes additionally

because of this feature, who knows?

It's yet to be discovered, because
we don't like a lot of indexes

due to many reasons, and now that
1, like 1 of them will be discussed

later, I'm pretty sure.

What else we already discussed?

Michael: So the 1 we've discussed,
I think we've had at least

2, maybe 3 episodes on, was where
the BUFFERS should be on.

Nikolay: Ah, okay, I didn't get,
now I get.

Yes, this is our favorite, and
this is big news for this podcast

actually.

Right.

This is why in January I said this
is I think it was committed

very long ago in January or it
was already obvious that it's

going to be committed.

So we spent 2 years, actually,
this is my position, my point

of view, we spent 2 years advertising
for using BUFFERS inside

EXPLAIN when you do EXPLAIN ANALYZE,
and finally, I feel like,

I feel slightly being sad because
we don't need this anymore.

I actually had t-shirt with this,
EXPLAIN (ANALYZE, BUFFERS) with BUFFERS

written in bold.

So yeah, BUFFERS after Postgres
18 hitting your Postgres servers.

Yeah, it's not needed anymore.

You just say EXPLAIN ANALYZE and
you get BUFFERS.

Yeah, I had some slight shift.

I know pgMustard website has a recommendation
how to properly

collect plans, execution plans.

And I shifted my mind already,
focus shifted to SETTINGS, WAL,

and VERBOSE.

Great.

Yeah.

So now, for older Postgres versions,
we write EXPLAIN ANALYZE,

BUFFERS, WAL, SETTINGS, VERBOSE.

Then it will be minus 1 word.

But it feels still like there is
a room for improvement here.

And we had this discussion.

So let's just refer to it and move
on.

I think what I've got confused,
because we also had a couple

of episodes about UUID.

Maybe 1 of them was without you
actually.

Michael: It was.

Nikolay: Yeah, this work uuidv7
started a couple of years

ago when Kirk Wolak joined Postgres
hacking sessions where Andrey

Borodin and I tried to cook something
online on YouTube channel,

Postgres TV YouTube channel.

And he joined with idea, let's
implement ULID originally.

Then it shifted to uuidv7,
but then we needed to wait.

I mean, Postgres project needed
to wait for quite some time because

RFC was not polished and Peter
Eisentraut's position was like,

we should release only when it's
polished because who knows,

maybe something is changed, is
going to change.

So I remember like, yeah, very
conservative.

Everyone already has it.

Various NPM modules, Python, Google
projects, they all already

have uuidv7 support, even
though RFC is not finalized.

But Postgres, I think, took very
conservative position.

Maybe it's good for Database because
it should be conservative,

right?

Michael: Well, and we need to support
it for 5 years, right?

Like if whatever we implement,
we're saying we're supporting

this version for 5 years, even
if you automatically, even if

you manage to deprecate it the
next version, which is also nearly

impossible.

Normally you need to deprecate,
give warnings that it's being

deprecated, then actually remove
it in a later version.

So I really understand that and
I think also Postgres did support,

like you've said this multiple
times, Postgres does support uuidv7

in older versions of Postgres.

You just have to generate them
either outside of the database

or with your own function.

You've done it even within Postgres.

Nikolay: Yeah, this is what we
do already for a couple of years.

And we have how to do it in PL/pgSQL
or just with pure SQL.

It was part of my how-tos.

Yeah, it's already aged.

And you can combine it with partitioning,
TimescaleDB or whatever.

But this time it's just, actually
it's like basically synthetic

sugar, honestly.

Michael: It's a nice function,
right?

Nikolay: Since it's official we
can expect it will be more widely

used.

Yes, and it was discussed a couple
of times on Hacker News and

people say there is like concerns
about security ability to guess.

I don't get those concerns because
it's really hard to guess

anything from those values.

You can extract timestamp and unlike
recommendations from original

RFC authors, Postgres decided to
present a function uuid_extract_timestamp

from value, from uuidv7
value, which I find useful, for

example, for partitioning implementation.

So anyway, this is a great feature.

We discussed it in depth.

Let's not lose time.

But it's not like we have a data
type, we just have a couple

of functions to generate and to
extract timestamp, that's it.

But data type remains just UUID,
it's version agnostic.

Michael: Yeah, we already, we do
have a data type, we already

have the perfect data type, which
is we've had it for many years,

now we have nice functions around
it too.

Nikolay: Now it's just in Query
and everything in Query is great.

Just use it, use it.

It's better than version 4 for
performance reasons and also convenient.

I still find it helpful to keep
timestamp like created at separately,

although you lose additional bytes,
but it's a matter of like,

you need to compare what's better
for you.

Just rely on ID value, if it's
uuidv7, or have it separate.

I think it's worth a separate blog
post to compare pros and cons

for this, actually.

It's an interesting topic for developers,
I think.

What else?

We also discussed 1 feature a few
times, which I'm listed as

author, right?

But I'm like, I wipe coded it originally.

And it was --no-policies support
for pg_dump and pg_restore.

I know there is an issue which
some other folks were fixing because

it didn't eliminate comments.

Policies can have comments, you
know.

So if you specify --no-policies,
I saw there was an additional

work following up this patch.

And yeah, it's a pity that I missed it.

But in my defense, I must admit that other know something in

pg_dump and pg_restore also forgot about comments.

And this work was done in Claude 3.7 in January, I think.

Was committed later by Tom Lane with some improvements.

But when I say VibeCoded, it was originally VibeCoded, but eventually

before sending patch, I always review line by line everything

and polish myself and we're using multiple LLMs.

So I think it's not strictly speaking vibe-coding.

We call it vibe-hacking, but it's just good for prototyping to

use some help of LLM if you don't write C code every day.

And I think it unlocks the power for people who are not C coders.

They can implement things and present patches.

But obviously, if you do it without looking into code and send

some vibe-coded, purely vibe-coded patch, it's a terrible idea.

And inside my company, everyone is using LLM AI to code, but

it's like, it's prohibited to right now to start coding on your

own, not thinking about LLM, but it's also prohibited, not, not

taking responsibility for final result.

If it's some BS code, it's your, on your shoulders, not like

you cannot blame AI.

Right.

And if you, if you send something, some proposal, open pull request,

merge request, or sending patches, obviously you are responsible

for what you are sending right

Michael: yeah well I think it's quite rude as well.

Isn't it because you're asking when you send a proposal PR merge

request Anything you're asking other people to review it and

I think the minimum you owe them is that you've reviewed it yourself

yeah, when you when you ask other people to review something.

And that didn't used to be an issue because by writing something,

you are reviewing it in a way.

But now that people have options that involve them not even writing

it, I think it is a step worth being insistent on.

Nikolay: If you like not writing C code all the time, but you

want to implement something, go take AI to help you, but then

take another AI to review it and explain every line, explain

every line to you so you fully understand what you, like the

final result and you are happy with it and you have tests and

documentation.

By the way, small tip, start with writing documentation first.

And like usually people like, it's boring to think about documentation

and tests with AI.

It's slightly more like productive and Postgres test system is

it's Perl.

So it's really like not easy to deal with if you've like never

wrote Perl or already forgot it after 20 years, for example,

or something.

Anyway, I think AI is great.

It's like it's unlocking creativity here, but the final result

is on your shoulders fully.

And I also, before this episode, I checked a couple of articles

about Postgres 18 and I found terrible articles.

Terrible articles.

I can, like, I already, I'm building the set list of companies

who produce BS blog posts, obviously LLM generated.

And this is like not okay, in my opinion.

Do you mind if I name the companies?

Michael: Not at all.

I find it quite frustrating.

And I think again, it's rude, right?

You're taking a shortcut and publishing something that you hope

will be read by many, many people, taking many, many more hours

to read it than you took to write it.

I just think it's...

Nikolay: Yeah.

In the past, I saw a lot of BS articles about Postgres on the

website called MinervaDB.xyz, something like

this.

This time stormatics.tech blog post has a lot of wrong information.

Michael: Well yeah I guess this goes back to your your age-old

thing of always verify right but sadly now we need to make sure

we're also verifying things from what we considered maybe in

the past to be reputable companies.

Nikolay: Actually let me correct myself this stormatics.tech

article was not I'm in the context of you know like building

some police bot for blog posts about Postgres.

This blog post was about optimization of moving pg_wal to different

disk and it had a lot of mistakes.

For example, mentioning undo in WAL and a few others.

But this time about Postgres 18, we have blog post published

on dev.to, devtips, and it has a lot of, like it's LLM generated

because it says logical application gets full DDL support.

Well, no, it's not.

This is 1 of the pains we still have.

Obviously, LLM generated.

So I think before we consider problems with patches, wipe coded,

purely wipe coded, not wipe hacked, as I said, but wipe coded.

We have a bigger problem of misinformation being spread on in

blog posts So yeah, and what do you think it would it would help

to have some tool to quickly verify if I to do some fact-checking

So we I checking AI and so on.

Michael: The challenge is false positives and false negatives

right?

Like if an AI is capable of coming up with this stuff, well who's

to say an AI wouldn't also rubber stamp it?

So that's the kind of, there's the, there's the not catching

the ones that are AI written.

And well, and then there's the issue of catching or flagging

blog posts that were human written,
but it thinks AI and accusing

those of being AI written.

I think this is an unsolved problem,
isn't it?

Don't education have this exact
same issue with coursework and

things?

I think

Nikolay: you are right.

Humans also hallucinate.

I hallucinated on this very podcast
sometimes.

I think we should say, not AI checking
AI, we should say, I have

this information.

It can be a blog post or some report.

We actually, internally, we have
such tools already because we

do a lot of consulting We write
some reports and we need to verify

we do we need to do fact-checking
because sometimes you think

this is this is it this is how
Postgres works, but it worked

like that 10 years ago and it already
changed.

So we need to dig deeper and do
some fact-checking all the time.

Because for example, I was wrong
recently when I said changing

your primary key from int4
to int8 is going to produce

a lot of bloat.

I was completely wrong.

And in that case, I was wrong very
deeply.

So it turned out there is a table
rewrite mechanism similar to

VACUUM FULL.

Nice.

And table comes out from it, like
alter table, alter column.

Table in the end is actually fresh
and all indexes are freshly

rebuilt.

And sometimes it's acceptable to
have this downtime maintenance

window.

Michael: Yeah of course completely
offline operation but no bloat.

Nikolay: So yeah I sometimes don't
trust myself and this tool

like checking, fact checking we
could use it yeah we use it internally

again Like I am thinking to present
it as an external tool so

we could check blog posts we suspect
are not accurate.

Michael: I think that well accuracy
is slightly different to

LLM generated and I think it depends
what you want to like.

Do you want accurate posts or do
you want human written posts?

And that those are subtly different.

Nikolay: I don't care about who
wrote it, I want accurate posts.

Michael: Yes, so I wasn't even
talking about inaccuracies.

I was talking about inaccuracies
in the checking.

So like, even if you assume the
article is completely accurate

and human-written an LLM checker
could still say we think this

was this has some hallucinations
in it you know hallucinating

the hallucination so I don't have
much hope in this, other than

trust-based.

You know, if you as a company start
publishing posts that you

haven't checked, whether with humans
or if it has inaccuracies

in it, I'll forgive a few.

But if you're doing it consistently and there's loads of made

up stuff, I'm gonna stop reading your stuff and I'm gonna stop

sharing it and you know there's...

Nikolay: So LLM can be considered like amplifier of your like

state.

If you are not accurate, you produce a lot of bad pieces of advice,

wrong things.

It just amplifies it, right?

So if you don't check information.

To connect bits a little bit and finalize this AI discussion,

I find it Also interesting that sometimes hallucinations are

really helpful.

I had several hallucinations which led to the idea, actually

this missing feature would be great to have.

Well, this is great hallucination.

Let's have DDL in Postgres 18.

Already too late, right?

There is a work in progress, unfortunately.

And by the way, I recently checked it, DDL and logical, support

of DDL and logical replication.

It's not like we discussed it, right?

I have low expectations that it will be implemented very soon,

unfortunately.

And it's

Michael: a lot of work, isn't it?

And there's probably lower hanging fruit like sequences and things.

Nikolay: Yes, this is also a work in progress.

And I think, is it already going to post this 19 next year?

I'm not sure.

But sometimes I like just working, writing some ideas, I'm getting

some great ideas like we had the case with corruption and we

needed to fix it with all backups and so on.

So we needed to apply a pg_resetwal.

The idea was we need to recover from some backups and they were

broken, so LLM suggested to use pg_resetwal with a system identifier

option which doesn't exist.

So I ended up implementing that option and proposing a patch.

Unfortunately, it was not finished before Postgres 18, but maybe

it will be finished before Postgres 19 and we will have this

option.

Yeah, so there is discussion about this.

Michael: That's funny.

Nikolay: Yeah, and a few things like this.

So sometimes, so this is like, interesting thing to have LLMs,

but you must fact check and sometimes you think, oh, this is

a good idea and go implement it.

So why not?

Michael: Yeah.

All right.

Well, while we're on topic of corruption,
another thing we've

discussed a few times is checksums
and encouraging people to

enable them.

And we're getting them, they're
going to be on by default now

from 18 onwards.

Nikolay: I think it's long overdue.

Michael: Yeah, it was but great.

Nikolay: Yeah, it's a great great
thing Minus 1 recommendation

we will have in the future for
our clients and RDS had it for

ages, right?

Checksums are enabled there by
default.

Google Cloud also yeah yeah just
great thing

Michael: yeah I think quite a few
of the hosting providers do

but each of them have to it's another
1 that any new hosting

provider that comes along they
have to then know to do that or

their customers don't get it by
default.

Now they don't even have to know
and they'll get it on by default

which I think is great.

Yeah.

Another 1 we've discussed a few
times is pg_upgrade getting or

preserving the statistics, the
optimized statistics across a

version upgrade?

Nikolay: This is a huge pain, and
especially because, unlike

previous topics, this is not solved
in managed Postgres providers.

I didn't see any of them, I mean,
major providers like RDS, Cloud

SQL, others.

They always put it on shoulders
on users to run, Analyze after

major upgrade.

And we had outages because of that.

So now I think in the future, not
now, in the future.

You cannot use it before your old
cluster is already on 18.

So it's only like first time we'll
be able to benefit from it

in 1 or 2 years only, when you
upgrade from 18 to newer version.

But anyway, this is great news,
so I feel really happy that this

is solved finally.

And this is solved in a very elegant
way because now you can

dump statistics, So it's not only
for upgrades, you can use it

for other, in other areas because
you know, like, pg_restore also

doesn't care about it.

You restore and you need to run,
Analyze manually, also vacuum

actually, you need to run vacuum.

Now, recommendation will be after
pg_restore, after restoring

from a logical backups, aka dumps,
like logical backups is fine

to name dumps, like, but not just
backups.

So when you restore from it, we
always said you need to run a

vacuum Analyze.

Now it's just a vacuum to have
visibility maps created sooner.

Michael: Your index only scans,
yeah.

Nikolay: Well if you forget vacuum,
it's not a big deal.

Maybe you will like, and of course,
autovacuum will take care

of it, but lacking statistics,
it's a big danger.

Now you can dump statistics, restore
statistics.

This is great I think from this
we can benefit sooner than from

having it in upgrades because once
you're already running Postgres

18, you can start using it

Michael: By the way, I think I
think we might owe at least 1

cloud provider an apology.

Do you know who implemented this
feature?

3 guys, all from AWS.

So I think at least 1 cloud provider
does deserve credit for

implementing this feature, and
they've actually implemented it

in a way that everybody benefits
which is cool.

Nikolay: Right, but why do we need
to apologize?

Michael: Well because you said
all of the cloud providers have

just shoved this onto users and
didn't do anything about it.

Nikolay: I think yes, we can apologize
easily, not a problem.

And this is great.

I knew this feature is from RDS
team.

This is great.

But still, if you run upgrade on
RDS, it has 16 points, 16 steps.

Michael: Yeah, yeah, yeah.

Nikolay: How to upgrade using pg_upgrade
official documentation

has 19 steps.

RDS has 16 steps.

And 1 of them, last 1, 1 of couple
of last ones, is you don't

need to run analyze.

And people tend to forget or just
say, okay, maybe it's not super

important.

And it happens.

People, it's manual.

And so I stand my ground.

They don't automate it, they put
it on shoulders on users.

But of course, now they need to
wait until 18 and then 19, and

then it will be fully automated,
which is great.

Yeah, I heard reasons why they
don't automate it, because it

can take time, prolonging downtime
window.

And there is an official approach,
analyzing stages, which I

think is a mistake, honestly.

Because in OLTP cases, we better
to get final statistics sooner

and do it inside maintenance window.

Right?

Michael: Yeah.

I just think if you care about
the downtime, you should probably

be working out how to do a 0 downtime
upgrade in some way, shape,

or form, and then you don't have
to worry about this problem

anyway.

So it's more, I think if you can
take the downtime, why not just

do the full analyze?

Nikolay: Exactly.

And also in this area, pg_upgrade,
I think, did you see that it

got parallelization --drops option for...

Michael: Yes, I did, yeah.

Nikolay: This is also great news
for those who care about upgrades.

Everyone should care about upgrades,
right?

Michael: Yeah, especially when
we're talking about a new major

version.

Nikolay: But anyway, like, am I
right that statistics can be

dumped and restored, even not in
the context of upgrades?

Michael: If it's if If they can,
then actually people can already

benefit from this because you can
use...

Nikolay: Yeah, this is a dump option
statistics.

So once we are...

Because this is discussed in the
context of upgrades only, but

again, it's not about only upgrades.

Once your server is running Postgres
18, you can already start

benefiting and adjust your dump
scripts and all these dump statistics.

And then you can just restore it
and that's it.

I also noticed that vacuumdb has
now, in Postgres 18, received

the option missing stats only,
to compute only missing optimizer

statistics.

This is interesting, because vacuumdb
has also --jobs,

so to move faster if you
have many many cores?

Right, you can say...

Michael: I think, yeah, I think
we need the missing statistics

because currently extended statistics
aren't dumped and aren't

preserved.

So If you create, you know, we
talked about correlations between

2 columns.

If you create those, those are
not preserved.

So I suspect that got added to
analyze so that we don't have

to run full analyze still.

Otherwise there's, Well, if we've
used create statistics, if

we've used the extended statistics.

Nikolay: Yeah, that's interesting.

But also, as like, to be careful
with vacuumdb --drops,

I love it.

And because if, if you have increased
default statistics target,

or like a lot of statistics to
a lot of tables and so on, it's

great to have parallelized processing
here, right?

But unfortunately, until version
19, so next year only, partition

tables will still be in trouble
because vacuumdb doesn't take

proper care of them, it only cares
about partitions themselves

but root table is not processed
and if you process it with analyze

it will process partitions once
again anyway so there is a problem

here.

Michael: We have a new feature
for that too in 18.

Nikolay: No it should be in 19.

Michael: We have an analyze only
for the parent partition.

Nikolay: No no no Analyze only
for parent partition is in work.

I think it's not like it's, it's,
maybe it's committed already,

but it's not in Postgres 18.

Laurenz Albe did it.

I noticed because I also wanted
to do it but it was only recently

committed I think.

There is allow vacuum analyze to
process partition tables without

processing their children.

This is great but this is not vacuumdb
stuff.

Michael: Yeah okay but we can do
analyze only and then the table,

the parent name, you're right it's
a separate part.

Nikolay: Analyze only we had always,
I mean for ages.

vacuumdb --analyze-only, this is
what we do after upgrade, I know

it because This was our recipe
until we hit this very problem

when partition tables lacked, root
table lacked statistics and

some queries suffered from it.

Michael: No, but if you did it
on the parent partition, it would

also gather statistics on all of
the child notations as well.

Nikolay: There are many things
here.

There is a single threaded SQL
command, right?

Analyze, or vacuum analyze, doesn't
matter.

They are single

Michael: thread,

Nikolay: right?

And there is also vacuumdb, which
has a -j, aka

--drops option, which helps
you parallelize.

The problem is when you run vacuumdb -j
like 60, I'll analyze

only.

Root tables in partition tables
will lack statistics after it.

And this is solved after Postgres
18 beta 1 was out.

So we need to wait until 19.

Michael: So that would be in 19,

Nikolay: sure.

We cannot move fast and take care
of partitions, unfortunately,

partition tables properly.

Yeah.

Michael: We need to set, then you
need to do a separate task.

Nikolay: Right.

So in Postgres 18, first of all,
statistics can be dumped, which

is great.

pg_upgrade is not in trouble anymore.

Okay.

But sometimes we still need to
rebuild statistics.

In Postgres 18, also, vacuumdb
received missing stats only.

And vacuum, single-threaded SQL
command, received ability to

take care of partition tables properly.

This is what happened.

Michael: Not just vacuum, but also
analyze.

Nikolay: Yeah.

Vacuum, well, yeah, vacuum analyze
and analyze.

Michael: So vacuum's a bit pointless.

I don't think there's any point
vacuuming a parent partition

because it's not going to have
any data in it.

Nikolay: But it needs statistics.

Vacuum, analyze, vacuum.

Michael: Exactly, it does need
statistics separately from its

children.

But in the past, in 17 for example,
in version 17, 16, 15, if

I, to get statistics on the parent
partition, I'd have to run

analyze on the parent table, which
would also gather, regather

statistics for all of the child
tables, which is a lot, like

that, There's so much more work.

Nikolay: So what happened, we say,
vacuumdb -j is great because

we have 96 cores, let's go.

Hyphen-j 96, let's go.

But then, oh, actually, root tables
and partition case, they

lack statistics.

We need to run analyze on them.

But when you run analyze on root
table, it recalculates statistics

for all partitions once again.

Now in 18, we have ability to skip
that and say, okay, vacuumdb

-j, move really fast.

And then additionally run analyze
only on partition tables, only

for root partitions, skipping partitions
themselves, because

vacuumdb already did it.

So now we have full recipe for
Postgres 18, how to recalculate

statistics in all database and
move really fast.

When you're like inside maintenance
window, for example, you

need vacuumdb -j, and then separately
you need to take care of

root tables for partition tables,
skipping partitions themselves.

Right?

This will be the fastest.

In Postgres 19, vacuumdb will take
care of this and we will need

to get rid of this second step.

Michael: Yeah, I didn't check the
commit, but it's possible.

I said missing stats only might
be for create statistics, it

might be for extended statistics,
but it might also be for parent

partitions.

That could be another place you're
missing stats.

Yeah.

I haven't checked.

Nikolay: Anyway, 19 will be excellent
and we will have all the

pieces of the puzzle ready and
we can move fast.

I like, I honestly think like Vacuum
and Analyze could have some

option, I mean SQL commands, they
could have some option to tell

like I want additional workers.

Give me like, you know, like vacuumdb
is good, But if you in

managed Postgres case like RDS,
you cannot easily run vacuumdb

because you need this to instance
running in the same region

or something.

So you like it's terrible idea
to run it from laptop, right?

Because connection issues and so
on, you will lose it.

So you need an EC2 instance next
to it, like some maintenance

host, and from there you can...

It's not convenient, right?

It would be great to have autovacuum
and say, I want like 16 drops

or 96 drops and let's go full speed,
we are inside maintenance

window.

Makes sense, right?

Michael: I think so, yeah.

Yeah I'm thinking also you might
always want to have more, if

you've got, you know, regularly
scheduled jobs, you might always

be able to give it more.

You might even want a configuration
parameters like always give

my vacuums at least 3, you know
up to 3 workers or something.

Anyway, getting into autovacuum
territory here.

Nikolay: Yeah, yeah, yeah.

Let's maybe jump in between some
other pieces.

There are actually so many great
things.

I like the release overall, a lot
of stuff.

And for example, I like the idea
that now regular check, not

check, not null constraints can
be defined as not valid.

As we discussed a few times, it's
terrible naming not valid because

they will be checked for new writes,
but they are not checked

for old writes.

I think it's like all the pieces
we had before already, because

not null, I think in Postgres 12,
since Postgres 12, it can rely

on check constraints, check not
null constraints.

There is not null and there is
check constraint.

For primary keys, we needed only
regular not nulls, not null

constraints, right?

But implicitly we could have already
used like regular check

constraints with not null and then
do some dance around it.

Now it's like becoming easier to
redefine primary keys without

thinking about all these nuances.

I like this.

Things are polished now in many
areas.

Like we just discussed it with
statistics and this, not now.

I like this.

Maturity of many pieces, many functionalities.

Michael: It feels like a release
chock full of things that have

come up for real.

For real users have hit these issues
and people have come up

with fixes for them.

It's not like, I know that's the
almost a definition of a maturing

product but it doesn't feel like
there's that bigger divide between

hackers working on things that
hackers want to work on versus

users hitting problems and actually
wanting to solve problems

you know that it feels very very
tightly done which is nice.

Nikolay: Yeah yeah another thing
I wanted to mention is ability

is infamous number of fast path
slots for Lock Manager, right?

We had 16 only.

Yeah, we had issues in multiple
customer databases.

Several companies suffered.

And I remember Jeremy Schneider,
who did it?

Like this was year of Lock Manager.

Michael: Yes, that was his post.

Nikolay: Yeah, it was maybe a couple
of years ago, right?

Or maybe, yeah, 2023, I think.

And now, yeah, I raised this last
year in hackers that we need

to make this probably adjustable,
like expose it as a setting.

And Postgres 18 finally has this
solved, but in a different way.

I think Tomas Vondra, who worked
on it mainly, right?

And instead of adding 1 more GUC
setting, we already have almost

300, right?

Too many.

Instead of that, now it takes into
account max locks per transaction

setting, right?

I think.

And automatically adjusts based
on that.

So if you have a lot of cores,
you likely, if you tune your Postgres,

you raise that setting already.

So number of these slots for fast
path checks in the Lock Manager

mechanism, it will automatically
raise.

And we checked in 1 case, we had
16 hitting replicas in that

case.

Sometimes it's hitting your primary
workloads, sometimes replicas

workloads.

In that case, we had 16 by default,
right, before 18, but now

we will have, due to that setting,
it's already adjusted, we

will have 64.

And honestly, in half an hour,
there's a scheduled benchmark

I need to do, I need to conduct.

But unfortunately, I remember early
implementations of the idea

of raising this threshold were
not super successful in terms

of helping with this performance
cliff.

So I'm not sure.

This should be very carefully tested
in each particular case,

because there are several variants
of this cliff.

Michael: Yeah, well if I trust
anybody around performance cliff,

I do trust Tomas to have looked
into it.

Nikolay: So I'm optimistic.

As we learned from our podcast
episode, we have different definitions

of performance cliff.

Michael: True.

There is 1 huge feature in Postgres
18 we haven't talked about

yet, which is asynchronous I/O.

Nikolay: Yeah, well, this is elephant
in the room, honestly.

And I must admit, I lack experience.

So I cannot tell a lot about this.

I know this is a big work led by
Andres Freund, right?

Yeah.

So yeah, I remember following this
work.

It's a lot.

I hope we will find good results
in some benchmarks in like actual

systems we help manage But I don't
have such experience yet.

Looking forward to it, honestly,
so

Michael: Yeah, it'll be good to
benchmark it because I think

a lot of the normal benchmark type
workloads, you know, pgbench,

sysbench, that kind of thing, won't
see benefits from this work.

There are like lots and lots of
small queries and this is much

better at large scans, right?

Like if you're benefiting from
gathering a lot at once, you're

gonna see bigger differences when
you're getting more blocks

than 1 block, for example.

So yeah, I think it'll be interesting
in real world workloads

to see how big a difference.

I think it's another 1 of those
things that you could see significant

improvements to how fast vacuums
run on some of your largest

tables or how fast, I don't actually
don't know exactly which

things have made it in, but I think
bitmap heap scans definitely

have and vacuum definitely has
and there's a few others but as

we see more and more of those get
used in future versions of

Postgres I think we're going to
see the benefits of this without

having to change anything again
it's another 1 of those features

that has managed to be committed
on by default and with quite

high, like not, maybe not high
is not necessarily the right word,

but it hasn't been shipped with
really conservative defaults,

I don't think.

That's how I'd phrase it.

Nikolay: Yeah.

I would also pay attention to additional
CLI tools this version

is bringing.

Because in Postgres 17 somehow
I've overlooked and we in our

discussion didn't touch it, but
later I noticed, not I noticed,

somebody pointed out to this big
new tool appeared, a pg_create_subscription,

converting physical
standby to logical replica,

which is great, like absolutely,
like this is automation of,

like we can throw out a lot of
automation, which is great.

I always love to do it.

It's official and it's working.

I'm not sure about managed Postgres
setups yet because I think

they must expose it via their APIs
and CLIs because it needs

to be run on the server and we
don't have access to it.

But in self-managed setups, it's
already available since last

year.

I mean, since your production is
running Postgres 17.

Yeah.

But also, this release Postgres
18 gets extension pg_logicalinspect.

Well, it's not CLI, but some extension.

I'm not sure what's inside in detail,
but I would like to explore

it, because logical replication
is quite complex, and inspecting

logical snapshots and sounds is a really good idea.

And also there is extension pg_overexplain.

I'm jumping between CLI tools and some extensions, I know.

But this is a good thing, I think, to understand.

I remember how it was born.

Robert Haas did it, right?

Yeah.

And I think maybe you can tell more about this because it's closer

to your fields, dealing with EXPLAIN plans.

Michael: Yeah, I think, firstly, kudos for an awesome name.

How good a name is pg_overexplain

Nikolay: but over tune

Michael: Yeah But yeah, so I think it does 2 things that are

quite important 1 is it's the first customer of the ability to

extend EXPLAIN in an extension, So I think it's a good example

to people if they want to add more features to EXPLAIN in the

future which has been, you know, over the last 4 or 5 versions

we've had quite a few new parameters added to EXPLAIN and I suspect

there's some amount of belief that maybe some of those could

have been extensions for a while first and seen how popular they

were before adding them for everybody.

So that first and foremost Robert made it EXPLAIN extensible

and then overexplain is an example of that but also is more

information than a user would normally want from EXPLAIN that

would generally be useful for hackers like people working on

the Postgres source code or optimizer trying to understand why

it's making certain decisions while they're working on a new

feature.

So for example the behavior around disabled nodes.

So you know when you can you know when you're testing why isn't

Postgres using my index and you want to do enable_seqscan = off

so that you completely disable, well you try and discourage sequential

scans so that if it could possibly use your index it will.

In the past that used to work by adding a massive cost constant

to sequential scans so that they'd be discouraged but not impossible

That's changed in I know Robert actually was happy to be the

person working on that 1 as well and it's now done on a on a

base of counting the number of disabled nodes as the first tiebreak

and then cost as the second tiebreak which is a really neat implementation

I've actually blogged about that but the overexplain instead

of telling you which nodes are disabled within the EXPLAIN plan

it will give you a counter of the number of disabled nodes so

far so that's useful for hackers but it's really confusing as

a user because if you're using it you can see disabled nodes

counter being 1 all the way up and you have to kind of look back

to see what the first 1 was and luckily I think it was David

Rowley who kind of backed that out and made it more user-friendly

but the the point is EXPLAIN is designed for users.

The primary user of EXPLAIN and EXPLAIN ANALYZE are people trying

to speed up slow queries.

Nikolay: But- Do you mean human
users or do you

Michael: mean- I

Nikolay: mean human users.

Michael: I mean human users and
what I mean is not Postgres hackers,

not people working on the optimizer.

So overexplain gives them a bit
more information about exactly

what the optimizer is doing at
each stage.

And I just don't think it's going
to be useful for users.

Nikolay: Yeah.

Let's also mention moving to macro
level.

We mentioned that pg_stat_statements
have a couple of improvements.

We always suggest that especially
for Java applications or some

applications which tend to set
application_name to some like

session ID, blah, blah, blah.

And set comment could pollute pg_stat_statements.

We always said set pg_stat_statements.track_utility
to off, so you

don't track set commands.

Now in Postgres 18 it's not a problem
anymore.

Parameter is just like, I think
$1, right?

Should be.

Yeah.

So yeah, you basically it's all,
everything is just 1 entry set

application_name to something.

So normalization is implemented
for set commands.

And also something is improved
in the area of tracking parallel

activity, right?

Michael: Yes, there's like some
extra columns.

Nikolay: Not enough columns in
pg_stat_statements.

Michael: I have actually seen a
conversation since then discouraging

more columns.

Interesting that that 1 got in.

I wouldn't have thought that would
be the 1 that snuck in last.

Talking of new columns, there's
also some new ones on

pg_stat_all_tables and similar for vacuum
tracking, vacuum and analyze

time spent which looks really useful.

Nikolay: Yeah yeah I like this
and I think it's worth working

on moving information from logs
to SQL yeah It's easier to monitor

it than dealing with logs.

I can't wait until errors will
be tracked properly in Postgres.

There is work in progress in this
area.

I hope it will end up being added
to 19.

Not yet, I just like, maybe it
won't work, but I think errors

should be tracked as well, properly.

Counters at least, like something.

Yeah, because now we need to go
to logs all the time.

So yeah, although I actually, I
learn all the time.

Last 20 years I learned Postgres
and sometimes I feel like I'm

lagging.

Development is faster than my learning
curve.

Yeah.

So I just learned that there is a pg_stat_database_conflicts or something,

view, which exposes some types of errors.

And for example, it can tell you that query was canceled because

tablespace was moved.

It also can tell you that it was canceled because of certain

timeouts, but not statement timeout and not transaction timeout,

not idle transaction timeout.

Yeah, due to lock timeouts, for example.

Lock timeouts, yes, but not statement timeout, not transaction,

not, I don't know.

And a few more like replication conflicts.

So it tracks some counters, but choice is really strange.

And query cancellation due to very common reasons like statement

timeout.

Unfortunately, you need to parse logs right now or use an extension

called logerrors.

Not super popular, but very convenient.

Unfortunately, available nowhere, almost.

So only in self-managed case, I guess.

But I hope this idea of logerrors will be finally implemented

in the future in Postgres itself and yeah.

Okay, good.

We touched some future a little bit as well.

Michael: I had 1 more favorite I wanted to give a shout out to.

Obviously it's EXPLAIN related being me.

Nikolay: Right.

Michael: I think there's a really big improvement that has gone

a bit under the radar for many, which is actual row counts are

going to be reported as decimals now so this is most important

when you have loops and lots of loops and it will report you've

got 100,000 loops.

Pardon me?

Nikolay: 1.5 rows, right?

Michael: 1.5 per loop is way more useful than 1 or 2 rounded

to, but it's even more important when you are around the 0.5

rows per loop or 0 like anything 0 to 1 is particularly interesting

below 0.5 is very very important because it will round to 0 and

no matter what you multiply 0 by you're gonna think that that's

returning 0 rows and that's really Really not true when you've

got a hundred thousand loops.

Nikolay: Yeah Yeah, the mistake accumulates and multiplies.

Michael: Yeah, exactly Okay, the next like once you get past

the looping operation, you will see the actual number of rows,

but you don't know what's happened in between.

Now you don't have to worry about that.

It will report it as a decimal and you can multiply that.

Nikolay: Let me add 1 more because this is great actually because

I feel connection to so many features and 1 more yeah this is

where Andrey Borodin worked like and others many others actually.

He worked for quite some years and finally amcheck is getting

support for GIN indexes.

Yeah.

This is big because we do need it.

We do need it because we need to use amcheck often.

Michael: I actually missed that in the release notes as well.

Nikolay: It always worked for B-tree to check indexes for corruption

when you, for example, perform OS upgrade or other stuff.

You need to check for corruption from time to time.

But GIN indexes, like, we couldn't do it.

Well, we usually, in self-managed cases, yeah, only in self-managed

cases, I think we used patched version.

So we, we tested that patch.

We, it, it took several years for, for the patch to mature and

get rid of false positives, false negatives, I think.

Especially false negatives when it's missing.

I always get confused.

But it was false positives as well.

Yeah, but now it's great.

It's there.

So it's time to use it.

Extend your amcheck actions to a GIN.

Yeah.

And if you don't have amcheck actions, you must.

I mean, you should, not must, you should.

Because amcheck is a great tool.

I think maybe it should be renamed because we use it mostly to

check indexes for corruption.

But yeah, anyway, maybe it's a good name actually because it's

unique.

But I think for large Postgres databases, everyone should check

for corruption from time to time for various types of corruption

and amcheck is a great tool for it.

Michael: Is it access method?

Nikolay: Access method check, yeah.

Michael: Yeah, makes sense.

All right, yeah, good one.

Thanks so much, Nikolai.

Catch you next week.

Nikolay: Thank you, enjoy it.