Michael: Hello and welcome to Postgres FM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard, and this is my co-host Nikolay,
founder of Postgres.AI.
Hey Nikolay, what are we talking about today?
Nikolay: Hi Michael, about upgrades again, but it will be some
small talk.
Just minor upgrades, easy, right?
Michael: Yeah, minor episode about minor upgrades.
Nikolay: Yeah, replace binaries, restart, bye-bye.
That's it.
Episode done.
This is what the documentation says, right?
Replace binaries, restart.
Easy peasy.
Michael: Yeah, so, well, this was my choice, and I wanted to
pick it for a few reasons.
1 is that we just had new minor releases announced a few days
ago as of recording, about a week ago as of the time this will
go out.
Nikolay: Planned one, right?
Every 2 months.
Yes, yes, yes.
Not like urgent.
Michael: Yes, exactly.
So I thought it was a good time to bring it up.
I thought there were a couple of interesting things in it.
But also it was a good reminder to me that we hadn't talked about
in a while.
I did look back and the last time we spoke about upgrades, it
was also my idea.
And it was the time where I was suggesting boring topic after
boring topic.
So thank you for not, Thank you for stopping having a go at me
when I bring up boring topics.
Partly because I see a lot of people, especially when they use
managed service providers, on really quite old versions.
I don't mind so much if it's an old major version.
I can see there's reasons.
I encourage them to upgrade, but I do understand that there's
reasons.
But when they're on a supported major version and lagging really
far behind on minor versions, it's difficult to justify.
Nikolay: The guys on version 14.3, for example, which was a bad
one.
Do you remember?
Michael: Yeah, yeah.
Nikolay: It was corruption.
Reindex concurrently corruption.
Michael: Last time we mentioned a couple of things about the
schedule, but I didn't have the details and I've looked it up
this time.
And I think that's a good reminder that there's normally a schedule
for these minor releases.
I keep calling them minor versions, but the docs don't call them
that.
They're called minor releases in the documentation, which I found
interesting.
Major versions and minor releases.
Nikolay: And I think it's just some inconsistency, small one.
Michael: Maybe.
It felt deliberate.
Like it felt very like, anyway, as you said, this recent one last
week was a planned release and they, in general, it says, unless
otherwise stated on the second Thursday of February May August
and November
Nikolay: yeah I was wrong every 3 months not 2 there is a wiki
page where it's the table with schedule when when my journey
this has become end of life.
And also this policy for planned releases, but sometimes unplanned
releases happen as well.
I also saw somewhere statistics.
Ah, it was on the WhyUpgrade, WhyUpgradeDepth.com, we should mention
it a few times in this episode because it's a super convenient
tool to see differences in better form.
You can find everything in the release notes or just selecting
some things in the system catalogs, but it's much easier just
to go to whyupgrade.depth.com and then just see the differences
choosing between a couple of minor versions or also major versions,
it's also supported.
So yeah, long list and security related stuff is highlighted.
So there I saw that how many changes happened in each major version
and how many minor releases happened.
And can you imagine, in 7.4, more than 30 minor releases happened.
It was 7.4.30.
Oh wow.
Yeah, it was popular, popular major version, I guess.
Michael: So what would expected be about 20, 5 years with 4 updates
per year, maybe 19 or 20?
Nikolay: 94, 96, I think they were like 25, maybe 24 releases,
but now it's shorter, like 20 maybe.
I don't remember details, like right now.
Yeah, yeah.
So there are many minor versions.
And sometimes we saw it happened, like minor version planned
released and then in a week or 2, new version released because
some problem just identified.
So minor version can bring not only fixes but problems, because
it's a regular thing in software development.
Despite all the measures to control the quality and regression
tests and performance testing, everything, It still happens.
So downgrades or fix it forward, like, let's wait until the next
minor release.
It happens still.
Michael: I was going to say, though, I think it's been happening
a lot less recently so I think yeah only the only 1 I remember
this is why I brought up the schedule is because you mentioned
14.3 and it was 14.4 was the last unscheduled I actually don't
know if they've got a name for it.
I guess it is just a minor release.
But it was not on the schedule we mentioned.
It came out in June.
So a month after, about a month after the...
Nikolay: I think there are more.
I don't remember, actually.
Michael: More recent ones?
Nikolay: Maybe.
I don't remember. I don't remember.
But let's discuss the process.
And from there, probably we will return to difficulties, right?
So, as I said, the documentation says the process is super simple.
Replace binaries and restart.
But in cloud, probably you just should provision new replica
and perform switchover, right?
Michael: Well, what do you mean in cloud?
Do you mean using a managed provider or do
Nikolay: you mean...
I mean when it's easy to take another VM.
Like in general, right?
It can be your own cloud or something.
If it's easier for you to just to bring a new VM than to deal
with dependencies and conflicts of binaries, I don't know, like
some packages, you know you can provision a new node, and when
you install from apt or rpm, apt install, apt-get install, or
yum install, yum install, how to pronounce, then you get always
the latest minor version.
You cannot specify a minor version, we will discuss it later.
Interesting. Right, right.
It's a big problem actually, but we will touch it later.
So in this case, in a new node, you will have already updated
Postgres.
It's a replica, a lag is close to 0, a synchronous replica for
example, then you just perform controlled switchover.
Controlled failover also known as switchover, right?
In this case, all good, right?
Michael: Well, I do think that process would work most releases,
but I do think there are other parts that you shouldn't forget,
which are like, read the release notes.
There might be other additional steps you need to take in order
to... No, No,
Nikolay: no, no, no, no, no.
I'm not forgetting anything here.
I'm just talking about the technical process of upgrade.
Of course, not only you need to read all the notes, you need
to properly test and almost nobody does it properly because it's
a lot of work.
I mean, I'm sure clouds do this.
That's why they lag many months usually.
They do a lot of testing.
That's why usually they're behind official minor versions, sometimes
skipping some of them actually.
But I'm just talking about 2 big approaches to upgrade.
Replace binary and restart as documentation says.
By the way, does the documentation already mention how to restart
faster?
Michael: It doesn't mention checkpoints.
Nikolay: Exactly.
It should.
It should mention it.
Michael: You did a great whole episode on how to do it faster.
So I'll link that up in the show notes for anybody who wasn't
a listener back then.
Nikolay: Yeah.
Super easy.
If you want to restart faster, you need to remember about shutdown
checkpoint.
Shutdown checkpoint will take long if you have max WAL size
and checkpoint amount tuned.
You should have it tuned.
We had another episode about it.
And the problem is that during shutdown checkpoint, Postgres
doesn't accept new queries to execute at all.
And that's a big problem.
Actually, it could be improved in Postgres itself because it
could do 2 checkpoints.
Why not?
Like pre-shutdown checkpoint and actual shutdown checkpoint,
which is super fast.
So our current recipe for all Postgres versions to restart faster,
you perform an explicit shutdown checkpoint, which is not blocking
anyone.
Queries are executed.
And then immediately after it's done, you restart or you shut
down.
Restart consists of shutdown and start, right?
In this case, shutdown checkpoint has almost nothing to do, and
it's fast.
This saves a lot of time.
Michael: Yeah.
Last time you mentioned there was some discussion in your team
as to whether even explicitly doing 2 checkpoints might make
sense because if the first 1, if you've done, I don't know if
that discussion went anywhere if you did some further testing
there.
Nikolay: So in total it becomes already 3 checkpoints.
Michael: 3, yeah.
Nikolay: 2 explicit and 1 sort of.
Yeah, anyway, you can speed this restart a lot in a heavily loaded
system if you know this simple trick.
And this simple trick, we code it everywhere when we automate
some things, major upgrades, minor upgrades, various things.
But the good thing, back to my point that we have 2 approaches,
2 recipes.
1 recipe is this, replace binary and restart.
And another recipe is what maybe in other database worlds, not
Postgres, but maybe Oracle, for example, or SQL Server, what
they call a rolling upgrade, right?
When you upgrade 1 replica, another replica, and so on, and then
you perform switch over maybe multiple times, I don't know.
The good thing is that current Postgres versions and current
Patroni, I think since 2012, right?
Restart is not needed when you promote.
Because before that, to reconfigure...
Remember recovery.conf was in a separate file and not in PostgreSQL.conf.
And to reconfigure primary coninfo or restore command, you needed
to perform restart of your replicas.
So if primary changes, it means all replicas needed to be restarted.
But right now, no more.
Everything is good.
Promotion can be done.
And promotion and reconfiguration of your primary on all replicas
can be done without restart and Patroni does it.
So it means that it's faster and since restart is not needed,
even no tricks with checkpoints, right?
Michael: Yeah, I think this is how some of the cloud providers
are doing it behind the scenes.
Yeah, yeah.
The managed services, yeah.
Nikolay: But good point.
Yeah, let's discuss some topics here.
So you mentioned you need to check, for sure you need to check
release notes, because it might say, release notes might say,
you must re-index some types of indexes, for example.
I don't know.
Michael: Well, even the let's look at the latest 16.3, I looked
at the release notes and shout out to Lucas Vittel, who did an
episode of his 5 minutes of Postgres on this as well.
So, we'll link that up from last week.
But he reported a security issue in the appropriate way, very
well done.
And it got fixed in 16.3.
And you can only fix it, like, so, there's detailed instructions
in the release notes on how to go about fixing it.
Just applying the minor release in the usual way, in either way
that you described actually, I think, would not fix the...
Actually would the replica...
It depends how you did the replication thing, I think.
If you spun up a new replica, I think that might actually be
okay.
But if it was a replica you already had on a minor version, did
the minor release upgrade there, and then failed over to it,
you wouldn't get the fix for that security issue.
Nikolay: Right.
So people in many cases don't do it and it's bad.
Michael: Well, I think so, especially on managed service.
Like imagine your managed services doing it.
You've got, maybe you've scheduled for some days.
Nikolay: Somebody already did it.
Michael: But maybe they are running the SQL script.
Like the fix in this case is there's SQL scripts.
Nikolay: After a situation with major upgrades, with 1 of our
customers who was on some managed service which was running on
Zalando operator, where major upgrade is fully automated, and
they used it, After we saw corruption related to JLibc version,
and we know Zalando, it was implemented there, automation, but
it's not enabled by default.
It means that you need to just specify a special parameter to
enable automation to mitigate GLIBC version change when you perform
upgrade of your Spillo.
Spillo, this is part of Zalando operator.
It's like an image ready to run Postgres and Patroni in clouds,
in AWS, first of all.
So if you don't specify a parameter, you get corruption of indexes.
And the fact that it was not enabled by this managed Postgres
provider means that even they didn't read release notes because
that was specified.
I knew about that automation.
I was curious why it didn't work in this case.
And I immediately saw in the release notes that there is such
parameter, and it's not enabled by default.
So even managed Postgres providers sometimes skip reading release
notes.
It's a big problem, but it's a regular problem.
We buy things and we don't read manuals, right?
We just try to use them right away.
Same things here.
It's like psychology.
So I don't know, of course, let's be like these kind of guys,
like, always read manual, right?
Always read release notes.
Why upgrade?
Depeche.com is a very convenient tool to read differences.
Michael: Well, and we have this gift that the release notes are
good.
They include full details of, but like when I say full details,
it's kind of an abridged version of every issue that was fixed.
It's like 1 or 2 paragraphs.
It's very, very simple, very easy to understand.
Even if you don't use the feature, it's not that long.
It doesn't.
Yeah, I did.
I read the 16.3 ones quite quickly.
Maybe it took me 5, 10 minutes just to scan, like to look through
them before the episode, just because I wanted to see what had
changed.
In fact, there's a couple in there that I think you'll like,
which is nice.
And yeah, it doesn't take ages.
They're easy to find.
And I think we've just been a little bit, well, I get frustrated
when I use iOS, I use an Apple phone, and every single app update
I get, if I look at the release notes, it just says bug fixes
and performance improvements.
That's all I ever get.
And it's so annoying.
And we get so much more than that in Postgres.
Anyway, it feels like such a gift that they're giving us.
Nikolay: You know what I lack in release notes?
Links to commits.
I always have many minutes spent trying to link proper release
notes.
It's mostly related to major releases, though, but minor ones
as well sometimes.
I try to find the exact commit, commit fast entry and git commit,
just to understand what has changed.
And it would be great to have links right in the release notes.
Some software has it in the release notes.
They link commits and pull requests and so on.
So the bottom line about release notes is you should read them
even if you're on managed service, managed Postgres.
You should read them because who knows what your provider has
missed.
Maybe they were good a couple of years ago, but then some good
guys were fired.
It happens today.
And this year, probably they are not so good already.
So it happens.
And unfortunately, here I also like, if managed service providers
could provide more transparency, what they tested, how they tested,
blah, blah, blah.
Like, sometimes, like, maybe I would read that instead, instead
of official release notes, like results of testing, for example,
published or something.
It would be great.
But I know it takes sometimes a few months for them to incorporate
release.
ALEX ENDOVSKY
Michael: I would love that.
Imagine getting an email from your provider saying your scheduled
maintenance is, like, your database is due to be upgraded at
your scheduled time of this.
Here's what we're doing.
We've already done the release.
Nikolay: Here are the results of tests.
Details.
Michael: Yeah, that would be amazing.
And then all you have to do is read the email, and you trust
them to get on with it.
Nikolay: I would read a few times, but then I know the detail.
Like, I would be impressed, for example, with the level of detail.
And next time, probably, I already would rely on that.
But this process would be great.
But not only do you need to read and perform actions, some versions
require you to do some actions, like rebuild indexes, to mitigate
possible corruption you have.
Or something like security-wise.
Not only this, it happens in minor versions as well.
But also you must test it.
2 more things, you must test it and you should not forget about
extensions.
Michael: Great, so how do you recommend testing?
Nikolay: Well, regular testing.
Testing means you should do the same types of actions.
If you do this official recipe with restart, replacing binaries,
restart, you should do it on some lower environment, a few weeks
before production upgrade, maybe a week, at least 1 week.
Michael: In a lot of the non-production systems I've seen, they're
not under heavy load.
So, and they wouldn't be like long running queries and things
that could
Nikolay: actually screw you up.
Honestly, I don't think we do need load testing for minor releases.
It's super expensive to conduct, usually.
You need separate environments, separate machines, and a lot
of actions.
Ideally, I would skip it, maybe.
Unless you're a provider.
In this case, you have a lot of databases, it's better to do
it.
But if I'm an organization with just a single cluster or a few
clusters, dozens of clusters, then I just would think about testing
it only if I see potential changes can affect performance.
Maybe if you have good workflow of testing, very good automation,
it's good to have.
But what to test?
Simple synthetic benchmarks, it's already tested in these load
farms, performance farms, right?
It's already happening on various types of operational systems
and so on.
So maybe it's not interesting.
You need to do it like a lot of testing is a complex topic, but
at least to try to check that packaging works as expected.
For example, preparing to this episode, I just quickly asked
our bot to extract experience from discussed in mailing lists
issues with minor upgrades.
And they both found some case for Postgres 9.6.1 upgrading to 9.6.2
when Postgres could not start.
It was related to some problems with some like corrupted pg_hba.conf.
Packaging put some placeholders there.
Michael: Wait, which versions?
Nikolay: 9.6.1 to 9.6.2.
That's 6 years ago.
So not 6 even.
Michael: Probably 7 or 8, but it's still not that long ago.
Nikolay: Yeah, it does matter, but packaging is another layer
that can introduce some problems.
And you upgrade and something wrong happens.
Postgres itself was well tested, but apt-package, for example,
was not well tested.
And if you don't test it on your environment and don't encounter
with problems of upgrading, it's bad.
So you should do it.
And then you should run Postgres for some time just to see that
it's working and all your code is working and so on.
Michael: Well, and I think I probably should have asked you about
extensions first because it feels like testing is also about
testing your extensions and the way extensions work, the core
aren't testing them, right?
And your combination of extensions might be, even if you're mostly
using common extensions, you might not even have any private
ones, or you might be using relatively few, your combination
might not have been tested by anybody yet.
The fact that you've got these 4 or 5 extensions.
So I do think...
Nikolay: Everyone is talking about these problems with combination,
but I personally didn't see problems with combination.
I know this is a quite popular topic, but maybe I'm using too
few extensions usually, or seeing them being used less than 10
or less than 20, for example.
I don't know.
What I know is, first of all, a minor version of extension is
not changing unless you do it explicitly with alter extension,
right?
Other session update to version or something like that, right?
Or just update it to the latest available version.
And I usually, what I do usually, I check in our checkup tool,
checks PgStat available extensions.
It has currently installed version and available version.
And if we see mismatch, it means that update didn't happen.
And it happens all the time.
People don't upgrade them.
They skip it all the time.
And packages don't upgrade them.
But maybe it's good.
I don't know, actually.
The problem
Michael: is- Would you then schedule them at the same time as
minor updates for Postgres?
Nikolay: Good question.
Maybe I would...
Yeah, we know this dilemma, right?
Like
Michael: If we
Nikolay: change 1 thing at a time, overhead is huge.
It's like doing things in separate transactions, transaction
overhead.
So you need to plan it, coordinate it.
If you have some bureaucracy, and you should have some bureaucracy,
like approvals, And then actual planning and approvals, description
of what to do, how to roll back, and so on.
And how to downgrade.
We will touch this very soon, I promise.
But If you combine everything in 1 shot, something goes wrong
and you don't know what it is.
I don't know, maybe I would plan...
Usually, we plan it separately.
But maybe it's not perfect, Honestly.
Extensions usually lack love, I would say.
DBAs don't go there usually.
Not usually, but DBAs like, oh, we upgraded this, upgraded that,
good, done.
Extensions, oh, it's like back-end engineers need them, right?
So unless it's like DBA kind of extension like Page Inspector,
pg_buffercache or something, which usually like, do we
need to upgrade them?
Even pg_stat_statements is lagging sometimes.
And I tell you the story, I remember, pg_stat_kcache was installed
on a very heavily loaded system and it got upgraded silently.
It was RPM, it was CentOS I think, and RPM was upgraded automatically
with various stuff, even non-DBA, but some SREs, upgraded operational
system packages.
And we had exclusions for all Postgres packages, but not PgStack.k
cache. It was not in the block list.
And it got upgraded.
And then every server started crashing with segfault and so on.
It was a bug introduced in PgStatK cache.
Not well tested.
Not noticed in lower environments because nobody tested properly
upgrades of operational system components there.
It's like testing should be done properly, but it requires a
lot of effort, coordination, and so on.
Right?
So minor extensions might introduce problems, even if you don't
explicitly use them, but they are loaded in SharePilot libraries,
it still can be dangerous.
It can crash your server if there is some bug.
I don't know, maybe it should be in 1 shot, minor version plus
all related extensions, but contrib modules, they have same cycle,
but third-party extensions like PgStack and KCache, they have
their own cycle.
And Michael: you need
Nikolay: to follow all of them and check release notes of all
of them.
Honestly, I like the extensibility.
But I also like when everything is monolithic and comes well-tested.
Michael: We can't have it both ways, though, can we?
Especially if we see the progress pgvector's been making so
rapidly because they can just release multiple new features per
year.
It's hard to argue against that.
Nikolay: But cloud providers lag a lot with upgrading.
It's a super hot topic, but look at AWS.
They just upgraded the pgvector to 0.7.0 only a few days ago.
But it was released...
I don't remember.
I saw some huge lags, months again.
Everyone needs it right now.
Things are moving so fast.
But I guess it's a lot of testing and maybe adjustments and so
on.
And if you check Cloud SQL, They lag even more, I think.
Michael: I think you might be thinking of the wrong provider,
because I've always thought AWS has been really hot on pgvector
updates, especially.
And also, I was going to give them a shout out, because as a
big company, I'm a bit surprised they're so able to ship minor
version.
I think they ship the these latest minor version releases within
a day of the announcement.
Nikolay: 0.7.0 with small like?
Michael: Not pgVector, sorry, I meant the Postgres QR.
Nikolay: Oh, this manner.
Michael: Like 16.3.
Yeah.
And well, there were only 2 providers I saw that shipped it,
and I haven't seen any since so quickly, and that was AWS and
Crunch Data.
Nikolay: That's cool.
Michael: Yeah, got the minor releases out really quickly, which
sadly, as you say, isn't that common.
And I've been growing
Nikolay: more and more aware of
Michael: how much people lag.
Yeah, Cloud SQL are lagging, unfortunately.
Nikolay: It's last time I checked and I checked a few times.
They don't lag a lot.
But what you see in documentation, it's just a problem of documentation.
Michael: Got it.
Nikolay: So if you provision machine, you see it's quite up to
date.
Michael: Yeah, I didn't provision new ones on Cloud SQL, I did
just check their docs.
Nikolay: Yeah, there are some SLAs in terms of version lag, and
they usually define and try to follow.
Michael: I don't think Cloud SQL even support version 16, though,
in PostgreSQL, do they?
Nikolay: Yeah, good point.
Probably not yet.
But major version is a different story.
It can lag like half a year.
Michael: I know, I know.
Nikolay: Easily.
But it's already more than half a year, right?
Michael: Quick question.
How do you feel about...
I had never thought about it this way, but I think if I was picking
a new provider today, this is 1 of the things I would look at,
how much do they lag on minor version updates.
Nikolay: Because it feels dangerous If I need some bug to be
fixed soon and they tell me it will take even not months, weeks,
it's frustrating.
Michael: So did you want to talk about downgrades?
Nikolay: Downgrades is the topic which you must have if you're
a serious organization, but in Postgres it's not enterprise-ready
at all.
I mean, if you follow this approach and official packages, the
official approach again is to replace binary and restart.
But both apt and rpm packages support only the latest minor version.
So how am I supposed to downgrade?
The answer is, it's not supported.
Michael: Or do you have to like store them somewhere?
Like do you have to store them on your side?
Nikolay: You can download packages probably and deal with all
dependencies.
I always end up screwing myself up completely and starting from
scratch.
And this is exactly where I like having ability to provision
new VM.
If I can provision a new VM, we have a different approach.
I upgrade a replica, I test it, probably switch over, probably
keep 1 replica on the previous version.
Michael: Just in case you need to go back.
Nikolay: Yeah, and probably I can do cloning and so on.
In managed services, I guess it's easy to downgrade, right?
You can just choose the version which you want.
Or no.
I'm not a big user of RDS and Cloud SQL.
I usually have...
Like, minor upgrades, I never was involved, honestly.
Because...
Michael: I think it's important to be biological, but not through...
Nikolay: No, no, no.
It should be possible to downgrade, no?
If we don't support downgrades, Maybe it's not a problem, maybe
nobody needs it.
Postgres never requires you to downgrade.
It's so well tested, right?
I've
Michael: never seen anyone downgrade in my profession, though.
It's a good point.
Nikolay: Yeah, I don't know.
Let's provision some notes and check.
I will have an answer in follow-up comment probably.
So, yeah, but in my vision, downgrades are needed.
Otherwise, you don't know what to put in your plan.
In plan, usually, management requires what if things go south?
And you should have the point.
This is our rollback plan or downgrade or something.
Reverse plan, right?
And if downgrades are not supported, but again, if you have multiple
machines, you can play with it and include into the plan the
idea that you can move back to the previous version just performing
switchover backwards, right?
Michael: Yeah, there is a nice kind of quote from the docs that
I pulled out before the episode.
It says, I think in the versioning policy, the community considers
performing minor upgrades to be less risky than continuing to
run an old minor version.
We recommend that users always run the current minor release
associated with their major version.
Nikolay: Right.
Michael: So, you know, it might even answer the question of whether
they consider downgrading.
It's a very interesting point.
Nikolay: Yeah, well, this is a good point.
We didn't discuss it.
But in general, you should upgrade quite fast.
The lag of versions should be very small, otherwise you skip
a lot of optimizations and bug fixes.
Sometimes good optimizations happen in minor versions as
Michael: well.
Really?
Nikolay: Yes.
Michael: I don't remember performance optimizations.
Nikolay: Yeah, well, if something was completely wrong, it's
considered a kind of bug, right?
In this case, it can be faster.
Sometimes things work not as expected, like it was planned to
be fast, but it was not planned fast because of some problem
in code.
This problem is fixed, it becomes fast as expected.
This happens.
Michael: Oh, interesting.
Fair enough.
Nikolay: Cool.
This is it.
All right.
What else about minor upgrades?
Michael: I've only got one more thing, which is kind of a fun one.
I found reading the release notes like quite fun, like it was
quite it was one in particular that made me smile, made me think
of you actually, I thought you'd quite like it.
And that was a fix by David Rowley.
Let me not sure how to pronounce Rowley Rowley.
Nikolay: You ask me?
Michael: Yeah, yeah.
One of the fixes he did in 16.3, let me find it quickly, was, I
think I remember it actually, it was a partitioning bug around
nulls.
So remember how much we talked about feeling like nulls catch
us out still?
Well, it catches Postgres developers out, too.
If you partitioned on a boolean column, so like true, false or
null, and you have three partitions, one that goes in when it's true,
one when it's false and one when it's null.
And then you've queried where X is not false.
It would the partition pruning would the planner would prune
out the null partition, even though it should be in there.
It's not false, but it was pruned out so you'd get incorrect
results. Nikolay: Because it's unknown.
Yeah, it's a three-value logic, my favorite topic in SQL.
Michael: Yeah.
So I thought it would make you feel less bad because it catches
even Postgres development itself.
Nikolay: Three-value logic should be a central topic in all educational
courses when people study SQL.
My daughter, she studied SQL multiple times at high school, currently
at university, And right now she has yet another SQL course and
they don't cover 3-layer logic at UCSD.
This is ridiculous.
Absolutely ridiculous.
Because this is the source of so many troubles.
Yet another 1, my favorite, right?
We discussed it also, we had an episode about nulls.
Michael: Yeah, I'll link that up as well.
Nikolay: If you touch null, you should expect unknown.
Unknown is not true at all, right?
And not false as well.
Right.
Michael: Last thing, We have episode 100 coming up.
I put a post on Reddit asking for ideas on what we should do.
Nikolay: Yeah.
Michael: I will link it up in case anybody wants to send us any
more ideas.
Anything you wanted to ask people, Nikolai?
Nikolay: Yeah, if someone is running Postgres 100 plus terabytes
under good load, at least dozens of transactions per second.
Reach out to us, let's discuss the complexities.
Maybe we will have you as a guest.
Yeah, by the way, I just checked the Cloud SQL in RDS.
I didn't find, maybe it's there, but I didn't find how to choose
minor version in Cloud SQL.
But in RDS, it's easy and they already have all these minor versions
released last Thursday.
So
Michael: yeah, that's the day.
Nikolay: As you said, yeah, 16.3, 15.7.
And but I can choose 16.1, for example.
Interesting that I cannot choose 14.3.
Michael: Good.
Oh, there's like a weird quirk.
I think AWS fixed the issue and called it 14.3.
But that's like an old...
I remember something weird around that.
But yeah, I'm not sure if that's relevant.
Nikolay: They also had have revisions, I guess, because it's
hyphen r1, r2 means like, I think, revision 1, revision 2 for
the same minor version.
So internal versioning additionally.
Interesting.
Because it's not Postgres, right?
It's modified Postgres.
Michael: Yeah, yeah, true.
Things that they've added or changed.
Nikolay: Yeah.
Right, right.
Well, good.
So in general, it means that downgrades are possible.
And if managed postgres are like this, like RDS, you can have...
You already upgraded the whole cluster, but then you understand
if anything goes wrong, you can provision the replica on lower
version. On a
Michael: different version.
Nikolay: Previous version, for example, and switch over to it.
But if you cannot do it, you need to keep all the old nodes,
un-upgraded nodes, and then be ready to go back to them.
Also possible, but maybe requires more resources to be spent.
You need to run them for some time.
So easy topic, right?
Simple.
Michael: Well, I'm glad we covered it in a bit more detail.
Nikolay: Maybe this packaging minor version problem, I know people
tried to solve it.
Someone from Percona, I remember, tried to solve it.
So yeah, at least for containers, Images should be possible to
specify.
Is it possible for so-called official images to specify minor
version?
I'm going to check it.
I'm curious.
Can we specify 16.3 right now?
Docker run Postgres colon 16.3.
Docker run Postgres 16.2.
Let's try 16.2 first.
Let's see.
Unable to find image.
Yeah, it's pulling, so it's possible.
This is good.
We have different minor versions there, so we can test at least
something on containers.
What about 16.3?
Michael: And downgrade.
Nikolay: Yeah, yeah, yeah.
16.3 also there.
16.4 is not available because it doesn't exist yet.
So good.
Containers are good.
I already forgot.
I've used it multiple times.
I just forgot.
Michael: Oh, cool.
Nikolay: Good.
Michael: Okay.
Thanks so much, Nikolai.
Nikolay: Thank you, Michael.
See you next time.
Catch you Michael: next week.
Nikolay: Bye.