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

all things PostgreSQL.

I am Michael, founder of pgMustard, and this is Nik, founder

of Postgres.AI.

Hey Nik, how's it going?

Nikolay: Going great.

I'm very glad to see you.

How are you?

Michael: Likewise.

I'm good, thank you.

And you chose the topic this week.

What are we talking about?

Nikolay: Yeah, I think it's very interesting to discuss the level

of automation we have in terms of all, you know, my position

against managed Postgres, and in this case, it probably will

be opposite, like saying that it's not enough what we have in

terms of managed Postgres and also in terms of Kubernetes operators

and other automation projects Postgres ecosystem has right now.

So why I was thinking about it, imagine 2011, Heroku was started,

Heroku Postgres was started.

2013, RDS Postgres was released in November, I think, and at

the end, I guess, right?

So then this was a foundation of growth of interest, I think.

Like some people say it's because of JSON or something.

I agree with those arguments, but I think the central reason

of why Postgres started to grow in 2014, 15 and by now is that

before that, backend engineers, developers, they were always

complaining how difficult it is to set up Postgres and configure

it and backups and replication that just didn't want to deal

with it and RDS and Heroku before that, they brought automation

for basic things, right?

And this, I think, simplified lives of a lot of engineers.

That's great.

In 2020, Supabase was released.

And I think a new wave of audience was brought to Postgres ecosystem

of front-end people actually.

Because now it's not only Postgres automated, it's very well

automated.

Other components are very well automated, like REST API, real-time

component, right?

These things, authentication component.

So you immediately start working on front-end, forgetting about

backend.

So I admire Supabase for bringing a lot of frontend guys to

Postgres.

That's great.

But at some point they need to learn SQL, I'm pretty sure.

So this is fine.

And now we have AI builders, and this is another wave of users

and basically not humans already sometimes.

So we hear from Supabase and Neon
that a lot of clusters which

are created these days are created
by request from like Cursor

or something, Vibe coding, right?

And so many, many clusters, many
of them are small and maybe

it won't go anywhere because it's
just experiments, prototyping

and so on.

But some clusters grow and they
lack attention.

So with RDS, there was a big shift.

We talked about startup teams who
don't have DBA and they are

fine with it until some point and
this is where Postgres.AI professional

services catch them, right, quite
often.

But now we talk about even lack
of backend engineering team completely,

for example, with Supabase or
even without like lack of engineering

team completely somehow.

Right.

And only guys who understand product
and try to wipe code this,

sometimes with security breaches,
like recently some app was

like storing data in, in Firebase,
right.

Google Firebase.

And that was not secure at all.

5 million users registered.

That was big scandal.

So yeah, anyway, but this is part
of the topic security.

So what I feel is a demand of much
higher automation than just

RDS or Supabase.

Some new level of automation should
be present.

And if you look at enterprise sector,
there is Oracle with this

idea of autonomous database, self-driving
database for many years,

right?

On 1 hand.

On another hand, there are academic
papers like 1 from Carnegie

Mellon University by Andy Pavlo
from 2017, which discusses what

self-driving database management
systems are.

And there's a question.

If you think about zillions of
Postgres clusters, which should

be highly automated And when experts
look at them, everything

should be already transparent and
obvious how to fix and move

on.

What is this?

Like what is self-driving Postgres?

I was thinking.

And to answer that I performed
several waves of research, of

course, with deep research from
cloud and open the ChatGPT,

right?

Latest models, I paid to everyone
a lot of bucks already.

So I was thinking, what is, what
could it be for Postgres?

To answer that, I performed a search
looking at Oracle, first

of all.

And I just asked to, you know,
deep research is when they perform

Google searches or Bing searches
and analyze hundreds of sources

and then write some kind of report
like some student would write.

It might have issues, of course,
with report, but it gives you

a lot of links at least and some
summaries.

So my question was, what people,
after all those years of building

autonomous Oracle, what do people
really like and what they like

less, right?

What's the- What did you find?

Michael: What did they say?

Nikolay: Yeah, and 1 more comment.

In 2013 or 14, I think I was attending
autonomous Oracle webinar

and I was completely shocked.

They promised autonomous Oracle,
but they only talked about clustering

logs, organizing like better log
analysis from hundreds or thousands

of sources and not only database.

And I was like, where is autonomous
Oracle here?

And 10 years since then, almost
more than 10 years, I was thinking

it's stupid.

Now I changed my mind and I hope
you and our audience will understand

why.

So what I found is that people
appreciate a lot self-patching.

It's like many minor releases,
like if new release comes out

with security patches, it's not
a headache at all.

And this kind of automated RDS,
you can just define maintenance

window, yeah, with some caveats.

Michael: Minor versions only, I
think?

Or have they done major versions
now?

Nikolay: Major versions yeah automation
of major version upgrades

and here we definitely have something
to discuss I mean we discussed

it already and I mean my team like
we we had very good recent

cases where our customers had the
0 downtime upgrades.

It's like, we're very happy.

I hope some blog posts are coming.

Michael: 0 downtime is very different
to fully autonomous though.

Like very, very different.

Nikolay: From fully autonomous

Michael: or self-driving a major
upgrade is very different.

Nikolay: Let's do 1 more step back.

What is self-driving car?

Michael: Yeah, great.

Nikolay: There are 6 levels defined
by SAE, like kind of standard

or something.

So there are 6 levels from 0 to
5.

0 means not autonomous at all,
manual, regular car.

And then 5 is fully autonomous.

And looking at first few levels,
I realized interesting thing.

So they talk about not each feature
particularly, but of combination

of features.

For example, level number 1, it
could be either either adaptive

cruise control, like so keeping
my maintaining speed, but safely,

right?

Or maintaining lane, but not both.

If it's both it's already the level
number 2 right and there

are several levels and we and for
example this paper Carnegie

Mellon Andy Pavlo's paper from
from 2017 and discusses how

to map it to database management
systems right

Michael: so a little bit not not
much in my opinion but a little

bit yeah for sure and this this
paper I looked at this in advance

you shared these car things I'll
link them up in the show notes

as well.

It struck me that level 4 to level
5, so the last step, is a

huge jump.

It's like here's loads of features
in the car that will help

the driver and then level 5 is
suddenly and now the driver does

nothing.

So it's like that feels to me like
a whole like a maybe a potentially

huge chasm that maybe there's like
a hundred more levels in between

4 and 5 that we're going to need
to break down at some point.

But like I felt like it was very,
a very hand wavy way of saying

we might be really close to this
because we already have level

4 features, we're very close to
having level 5.

And it feels to me like there might
be like a, like I was unclear,

for example, in a level 5 car,
whether a human could still take

control if needed or was there
absolutely no way of doing that

like that that feels to me like
a level that wasn't defined and

maybe there's like other I believe
there'll be

Nikolay: yeah yeah let me explain
how I see it.

And I think if you ask several
people, they will answer differently.

And I also heard Kubernetes ecosystem
tries to map it as well.

And some operators, they claim
they have a very high automation,

but many people say they are not,
they're not, they don't have,

they don't have, and so on.

So in terms of cars, let me like
propagate it.

So number 2, like both these options,
for example, and this is

what Tesla autopilot, for example,
does.

I use it a lot.

Like you just turn it on, but you
must sit and keep basically

officially you must keep hands
on wheel and be ready to take

control any second basically.

Right.

But it's still, it's, it's great.

It maintains lane and speed.

Like you just relaxes and, and,
spend much less effort.

And I think we can think about
this in databases as well.

Next level is level number 3.

And level number 3, it's everything
is automated, but you still

need to be to be ready to take
control.

And this is what for example, Tesla
full self driving is, well,

this is this is like, you not everything
automated.

It's like under supervision of
you.

Michael: You have just put I'm
just putting it up.

No, no, no, it's it's not quite
that it's like level 3.

It says here, for example, is a
traffic jam chauffeur.

So it can handle like, basic traffic
conditions like a traffic

jam.

But for example, it doesn't account
for like all weather conditions

or like there's a bunch of other
like potential things.

So it's, it's not like it's

Nikolay: the limitations.

Michael: Exactly.

And

Nikolay: you still need to take
control if needed.

Basically, you need to be ready
to take control.

This is level 3, I agree.

There are limits, but it can bring
you fully automatically from

point to point.

This is what Tesla full self-driving
does.

You sit in the driver's seat, and
from point to point you can...

Basically, you can enjoy full automation
of some whole ride,

but if some bad condition occurs,
then you need to take control

and fix things.

For example, if we map to full
major upgrade, full major upgrade,

downtime and so on.

By the way, when we think about
autonomacy, we also bring some

additional features like 0 downtime.

It's like it could be in place
and without downtime, but somehow

our mind wants some good features
additionally to autonomous.

So yeah, this is like natural desire
to have good stuff.

But we can imagine, for example,
we have a whole thing automated

And under many circumstances, like
in many cases it will work,

but in some edge cases it won't
and you will need to take control

and make some decisions before
proceeding on or even postponing

the whole procedure.

This is very similar to Tesla full
self driving and I experienced

it.

It really can drive you from point
to point.

But for example, if you go inside,
for example, my property,

I have some roads inside it, it
won't be able to drive there

at all because it's like, oh, this
is already not a road, not

proper road, you know.

So there is another level 4, it's
also conditional, but there

you like what my perception again
might be wrong, there you can

go to backseat and sit there.

You are allowed to relax completely,
fully, but again it will

work only under some conditions.

For example, there is Waymo.

I tried it multiple times in San
Francisco.

It's amazing.

You go to its Jaguar, you go to
backseat And everything is fine.

But if like some, we saw this YouTube
videos or something, multiple

Waymo machines, the cars, they
just create a traffic jam themselves

and like basically deadlock, right?

Michael: So like if they sense
that they can't drive or they

spot something they can't deal
with, they'll stop as a safety

precaution.

And then what do you do?

You have to get out?

Nikolay: Yeah.

In this case, yes.

In the case of Waymo, you're a
passenger, yes.

So you can imagine, For example,
if we map it to major upgrades,

for example, imagine there is a
procedure for develop, there

is a vendor who can intervene and
can take control sometimes

if allowed.

But the passenger in this case,
those who asked to perform full

major upgrades, They are passengers,
they cannot make decisions,

but this is good for them because
their mind is spent for product

development, for example, right?

But thanks to like million miles
already of experiments and real-life

experience for these cars, If something
goes wrong, safety first,

it will just abandon the trip,
I mean postpone it and cancel

and another car will come later,
right?

So this is approach.

But It's like whole thing encapsulated,
like black box for you.

You don't go down and don't make
decisions according to some

diagram of decisions.

But it has also limitations.

And I think Waymo is perfect example
for level 4, because it

works in San Francisco, in some
areas, but you cannot drive to

San Jose, which is like slightly
more than 1 hour usually drive,

because it's outside of coverage.

And this is like, this can happen
here as well, major upgrades,

but if there are some extensions,
for example, it's outside coverage,

we don't support this kind of upgrade
because this extension,

like, I don't know, Citus or TimescaleDB,
it requires an additional

approach and we don't have it covered
here, right?

So this is what I think here, like
we can map it and why not?

But my main insight was looking
at deep research of feedback

from Oracle users, DBAs and engineers.

They say, upgrades are great, both
minor and major.

Security is great, like security
control, automatic procedures

to level up security.

These kinds of maintenance things
are great, but when we talk

about smart, like index advising
and so on, it's hit and miss

situation.

And this, I had an aha moment because
I was thinking, actually,

if you look at what all people
try to do, they try to invent

configuration tuning, automated,
with machine learning and AI.

This is what Andy Pavlo was doing
with OtterTune.

And by the end of, OtterTune was
closed, but by the end of Auto-Tune

live, I already noticed the shift
which happened to Postgres

AI earlier.

Attention to query tuning and optimization,
like creation of

indexes.

And I see pganalyze is doing a
great job, not resisting to LLMs,

which is also great.

And also some teams go inside Postgres
and try to make planners

smart.

And I had, like, with configuration,
it's pretty straightforward

for me, it's Pareto principle.

Take pgtune.leopard.in.ua, very
simple configuration, 80% of

job done in 1 person, like really,
really fast.

It's just heuristic-based, rule-based
approach for OLTP or like

anything.

And it's good enough for many,
for many, like we don't need any

machine learning and so on.

Michael: And even when you say
for many as well, I think it's

also about time.

So it's for a while, it will then
be good.

And so for long, it will be good
enough.

So it's like...

Nikolay: Yes, I agree.

Yes, I agree.

And then you need, but then you
need to tune.

My way of tuning is to conduct
experiments.

You know this very well, like how
to make experiments faster,

cheaper, reproducible and so on.

Like these kind of things.

Michael: Is it worth, cause I think
self-driving, like probably

level 4, many people would count
as self-driving, but there's

still the kind of enough caveats
that a lot of the benefits of

self-driving cars.

Let's go back to cars briefly.

I'm really excited and optimistic
about self-driving cars.

I love the idea of being able to
get on with something else while

something drives me.

I don't mind that it might not
be like the fastest way or like

it might not drive like completely
optimally.

Nikolay: Not the safest.

What about safest?

Michael: Probably the safest but
not the fastest sorry.

Probably safer than me but maybe
it would be a bit more gentle

you know maybe it wouldn't take
the yellow light when a human

driver would, you know, that kind
of thing.

But I love that you can just watch
a movie or chat to a friend

easily or play a board game in
the back, you know, you do whatever

you want, you get so much time
back, especially in America where

a lot of the time is spent driving.

It makes so much sense to me that
self-driving cars are like

a huge unlock for a lot of people.

But largely only at stages 4 and
5, like at that highest level.

Like cruise control is great, but
I still have to concentrate.

I still have to be watching the
road.

Like I don't actually gain that
much.

And if we go back to Postgres,
I feel like a lot of the automation

features are great.

Nikolay: Yes.

Michael: But we still have to concentrate.

We still need the person.

We still need the DBA.

And as long as we still need the

Nikolay: driver, and as long

Michael: as we still need the driver,
and as long as we still

need fail-safes down to humans,
all I see is kind of this like

gradual need for maybe maybe this
is where the driving analogy

breaks down a little bit but maybe
fewer humans per Server to

like maybe the DBA team for a company
will be smaller on average

compared to how it was in the past
you know that and I think

we've already seen that over time
but I'm struggling with like

the that last step until we get
to those which which feels to

me like a long way off especially
given like the experience you're

saying about like Oracle and the
experience we saw with very

smart people trying to automate
a lot of this stuff from, you

know, with a lot of AI, but kind
of not even LLM stuff, right?

Like, a lot of the research in
this area has been machine learning

and other longer researched AI
methodologies that have lots of

real-world use cases and even there
we've seen mixed results

and it really I feel like the model
struggling to employ In the

experience I've had talking to
customers that use Auto-Tune for

example, I feel like the Constraints
for example were not as

clear as driving or the slightly
different use cases or the performance

trade-offs that different people
have in different cases are

subtle enough that you can't set
the exact same guardrails for

everybody.

And at that point, it breaks down
enough that well, and 1 more

addition, performance cliffs are
so real that if you change 1

thing, and it looks like it's going
to be great as soon as you

hit a cliff it's then a disaster
and then recovering from those

disasters is actually a real problem.

Nikolay: So I feel like...

Troubleshooting disaster is also
a problem.

Root cause analysis is a problem.

Michael: And arguably they get
harder when you involve automation

because the more that it's automated
the less people actually

know what was changed when and
why.

So I think it can get tricky.

Nikolay: I disagree here with you
because you must be an expert.

But if you have automation, you
move much faster with high level

of automation.

Take Cursor, give it a lot of pieces
together, and explain how

you approach methodology of analysis.

This is like expert needs to bring, and then you move much faster.

But this is like, I was trying to explain how I moved to this

area completely.

Right.

Michael: Yeah.

Okay.

Nikolay: So people say in Oracle, this works that 1 doesn't.

What works?

Quite simple things.

I, I said like upgrades, maintenance, security stuff.

Well, not simple, but they are boring, you know.

Of course, replication and backups.

Like for me, HA and DR, It's like auto steering and maintaining

speed and maintaining lane.

Basics.

Cars must do this, so database must have good HA and DR.

If we look closer, actually, there are issues with both the HA

and DR, which will prevent us from reaching very high level of

automation.

But we can dive into this later.

But anyway, boring stuff lacks automation.

Interesting, like, Remember I mentioned level 1 and 2, they talk

about combinational features.

So if we start analyzing each feature particularly, we cannot

apply the same classification.

You know, because classification talks about combinational features.

Michael: Yeah, sure.

Coming back actually, just to make sure I understand, what have

Oracle done in terms of security?

Can you give some example automation features?

Nikolay: Well, I know a little.

I would say what we should do in Postgres and This is like least

topic I would like, I'm ready to discuss now.

Because this is in the roadmap, but right now we're focusing

on different areas.

I can just speculate on this, like identify potential threats,

like checking permissions, roles.

For example, I know organizations which use the same super user

for all human DB engineers, and this is quite easy to identify

or any organization.

I had a couple of them, on consulting contracts, which passed

through IPO process.

Before that you have audit, right?

And during audit, they ask specific questions.

Some of them are quite like silly, I would say, but some of them

are quite good enough.

And you just inspect your pg_hba.conf, you inspect a user model,

you inspect how multi-tenancy is organized.

We had an episode about it, right?

So these kinds of things can be automatically analyzed and so

on.

I don't know.

I don't know details about Oracle.

I just saw feedback that these
stuff engineers really appreciate

and they appreciate less automated
configuration, automated creation

of indexes.

And this was appreciated

Michael: less or appreciate less
or it gets it wrong.

When it gets it wrong, it's more
painful.

Like what's, do you see what

Nikolay: I mean?

Mixed results, mixed results.

I don't know, like there's lack
of trust in some minds.

Like, I don't know.

Michael: I, for example, I catch
up with customers from time

to time just to hear what they're
doing, what they like about

products, what they don't.

And I was speaking to a customer
of mine that did try and use

OtterTune for a while.

And it'd be interesting, I'd love,
maybe we should invite somebody

from that team on to discuss what
happened there.

Yeah.

Why did it shut down?

Nikolay: I can tell you why.

People don't need configuration
tuning that much.

Michael: Okay.

Well, I also think there might
be other issues.

So there's definitely like not
needing

Nikolay: I tell you the big need
in configuration tuning exists

only if you have, say, 10, 000
plus clusters.

Then you can say, okay, we are
going to save like 5 to 10% of

money just with tuning or workload
will be reduced.

We know, you know very well, a
really bad plan can screw all

efforts of configuration tuning.

Michael: Well, yes, but I think
it's worse than that.

I think also in addition to it
not being needed that often and

therefore kind of subscription
based models not working that

well, also when it, well this customer
was telling me, they moved

from a mental state of, when something
goes wrong, let's dive

into what happened, straight to,
when something goes wrong now,

what did OtterTune change?

And that was like a real shift.

It became

Nikolay: like a...

Trust.

Michael: Trust, but also that must
be based on the fact it made

changes in the past that made things
worse.

So it's not trust for no reason,
it's not kind of like distrust

for no reason.

It's every now and again if you
change something, you hit a performance

cliff.

And it's unexpected.

Or maybe it's not even, like, always
a performance cliff.

Maybe it has another unintended
consequence that you care about

more.

Probably not in a lot of these
cases.

But I think they talked about,
for example, making the mistake

of letting it configure some parameters
that even affected, like,

durability and things like that.

So if you're changing depending
on what you allow it to change,

there might be unintended side
effects.

And I think that's, like, putting
parameters around, like, what

you do, you will and won't let
it do, is actually harder than

it sounds, I think.

Like, it's very hard.

Nikolay: To perform, like, enterprise
approach for making a change,

it's extremely complex.

I'm very grateful that 7 years
ago, I was working with Chewy.

They were preparing for IPO, and
I remember CTO was ex-Oracle,

and discussions we had and resistance
they had in any change

I proposed.

It taught me this enterprise approach.

I'm very grateful.

It was great experience for me.

And I realized, oh, actually, if
you want to be serious about

changes, any small change should
be very thoroughly tested, like

experiments, experiments, all risks
must be analyzed, and then

there should be a plan to mitigate
if a risk, like a non-risk

occurs, right?

And AI doesn't help here almost,
you know, like this is framework

you need to build without AI first.

But this

Michael: is, yeah.

I don't necessarily agree.

I think AI could really help with
these things.

When I say AI, I'm including machine
learning and not just the

latest LLM stuff.

I just think we need to define
constraints really clearly and

define what we care about really
clearly and make it really clear

we care more about reliability
and durability than we do about

performance.

So, like,

Nikolay: yes, yes.

Michael: And that's almost always
true.

And I think that might be more
core to the reason that these

performance tuning tools aren't
haven't yet succeeded because

we haven't yet nailed the reliability,
durability stuff.

So that would be my theory as to
like why they didn't like necessarily

succeed.

Because if we help, even if they
did help performance almost

all the time, if they ever hurt
reliability, that's not a tradeoff

most organizations are willing
to make.

And that's a difficult thing to
tell a tool that's trying to

optimize for better performance.

Nikolay: Yeah.

Yeah, I agree.

And durability has issues.

There is a good article from Sugu
just published on Sugu's blog

about synchronous replication issues
and we also know the very

good talk by Alexander Kukushkin
about issues with synchronous

replication.

So durability is a must thing to
have and targets, I agree with

you, targets, durability, availability,
must be reliability,

must be number 1 before performance.

By the way, I also remember from
that research, people appreciate

automated analysis and control
of costs.

And this can be initially quite
simple.

I remember actually talking to
1 huge organization, their database

director told me, you know what,
it's cool stuff, what you're

showing in terms of experiments
and performance tuning, like

query tuning experiments with Database Lab
and so on.

But the number 1 problem we have
is abandoned instances.

And how to stop doing that and
lose a lot of money.

And big organizations, it's a big
problem.

And yeah, so cloud providers still
don't offer good tools.

It still takes a lot of effort
to understand the cost is how

the structure of spending right?

Practically actively like usually
it's a little late.

They are not interested.

Yeah, right. So anyway, what my
aha moment back to it was

Michael: yeah, sure

Nikolay: people like from academia
from really great people, right?

Great minds.

They try to build really cool stuff.

Like let's have automated parameter
tuning, automated indexing,

or even let's go inside Planner
and create an adaptive query

optimizer.

Michael: I saw even more extreme.

In the paper, they were talking
about choosing whether tables

should be row oriented or column
oriented based on the workload

they're observing.

So it's like trying to attack really
cool

Nikolay: areas.

This is great.

And this has been always so.

Academia guys, they attack like
really detached from reality,

things.

Meanwhile, I realized we implemented
with multiple teams already

automated indexing, and this is
what people really need.

And lately I realized in consulting
we almost always say you

need automated re-indexing, but
we didn't have a polished solution.

We have multiple ways to do it.

I always said you take this tool,
like someone from our team

developed as a side project and
then polish it.

And it's only about bit reindexes
and this and that.

And also estimates, bloat estimates
might be off.

We know very well if there are
estimates, not just startuple

numbers or real fucking like reindex
numbers from a clone, right?

And I realized, actually, this
problem is not solved.

And this is a boring problem.

And we can solve it.

That's why, number 1 thing right
now we are going to release.

We already, like, it's about to
be released.

We call it pg_index_pilot.

And there is a good roadmap inside
of this whole project.

And it's going to be real simple.

And A guy who part-time works with
me right now, Maxim Boguk,

1 of the most experienced DBAs,
Postgres DBAs I know, much more

experienced than I.

He created, basically I consider
prototype, I said we fork it

and then we started iterating on
it.

The idea is simple, I call it a
Boguk number.

You take index size, divide by
the number of life tuples in this

table.

Let's forget for a while for partial
indexes.

And we have some ratio, right?

When we just created the index,
let's consider this ratio as

perfect.

And consider tables which exceed,
like, say, a million rows.

Over time, you will see...

This number is nothing, like, it
costs nothing to check it.

You can put it to monitoring for
all indexes.

It's super fast to get a number
of...

Michael: Because these aggregates
are stored.

They're stored, right?

Nikolay: Yeah, yeah, yeah.

It's from system catalogs.

System views.

Immediately you get it.

And then over time you see degradation
of this number.

Why?

Because some pages inside the index,
they are not fully...

They are not full.

They're half empty and so on, like
they're sparse.

Yeah.

So it means at some point you say,
oh, it's time to re-index.

And the best, there are pros and
cons of this approach compared

to like say traditional based on
bloat estimates everyone is

using.

The big con, like a couple of big
cons of this approach, it required

us some effort to get away from
super user.

We did it.

And oh, 1 more thing.

On purpose, we said this component
is going to be inside, like

self-driving inside database.

We don't need external means like
something installed on EC2

instance or lambdas.

We don't need anything.

It will be inside.

This means it's running inside
PL/pgSQL code.

We know since Postgres 12 or earlier,
stored procedures have

transaction control, right?

So we can go, but we need to index
concurrently, right?

So with index concurrently, we
cannot wrap it inside a transaction

block.

So unfortunately we need something
like dblink, right?

And it's a challenge not to do
it properly on RDS, for example,

because dblink, you need to expose
password, and you don't want

to store it in plain text.

So I remembered very old trick
I used a lot of years ago, dblink

over postgres_fdw.

And this is how we do it right
now.

And there is another kind of limitation.

To start this thing to work, you
need baseline.

So baseline means you need to index
everything first or to bring

this data from clone which we are
this is an idea where I'm going

to implement very soon so to avoid
full reindex because sometimes

our customers have like 10 plus
terabytes databases and it's

not cool to ask them to re-index,
it will take forever and so

on, there's also a big impact when
you re-index quite fast.

So, But a good benefit from this
approach, it's not only about

B-tree.

You can take care of GiST, SP-GiST,
and even HNSW and others if

they degrade.

And like basically, yeah, basically
we measure kind of storage

efficiency for index.

It's super trivial.

And I think I believe into this
simple approach.

I think we are going to have it.

And I think also like I talked
about this last couple of weeks

ago with Andrey and Kirk on Postgres
hacking on Postgres TV.

And we started doing something
mind-blowing.

Andrey just said, let's just implement
merge.

Because you know, B-tree and B-tree implementation
in Postgres, it has

only split.

It cannot merge pages.

And since Andrey's PhD and his
work is in the area of indexes,

it was great to have ability to
start this work, and I'm looking

forward to, like, every 1.

Michael: Wait.

So, like, if, for example, an area
of the index starts to get

sparse because we've deleted a
bunch of let's say we've deleted

some historic data and we're not
going to insert historic data

back in that part of the index,
it could proactively, like, it's

kind of self healing.

Wow, cool.

Nikolay: So, our project would
be archived at some point, I hope.

So, I'm not an expert in Oracle
since I have never been an expert

of Oracle, but also not a user.

The last version I used was in
2001 or 2002.

It was 8i.

But people say Oracle doesn't have
this need in re-indexing.

SQL Server has this need.

Over time, health declines.

We talked about it so much.

I said, this is mantra.

Everyone needs re-indexing at some
point.

And we know Peter Geoghegan's work
and Anastasia Lubenikova in 2013-14,

they implemented deduplication
and other improvements.

That's great.

But still, there is no merge.

So pages cannot be merged

Michael: automatically.

And there was like some work, there
was also some work I think

from Peter Geoghegan and others to
help avoid page splits in more

cases.

Yeah, that's great.

And not just the deduplication
work, but yeah, the bottom up

deletion.

So yeah, kind of avoiding them
getting into this state was 1

thing, helping them heal when they
do is another.

It also strikes me as a lot of
this stuff could live in core,

right?

We already have some automation
features, right?

We already have autovacuum, which
does about 3 or 4 different

jobs.

So we have some groundwork here
already.

We have tools like pg_repack and
pg_squeeze, not in core, but some

ideas of moving more of their features
into core.

So we do have some of this in core,
some of it in popular extensions

that are supported by many clouds.

So that feels like it is the project
is already naturally going

in this direction.

Maybe slowly and maybe like not
all the areas you want it to,

but is that like What does the
end goal look like here?

Is it being in core ideal?

Nikolay: In my opinion, we just
need to solve very complex problems.

I know in some big companies, managed
Postgres services, sometimes

1 experience DBA is responsible
for a thousand clusters.

A thousand.

It's insane.

But we need to be prepared to be
responsible for a million clusters

because AI builders will bring
us a lot of clusters.

Like the time changing really fast.

So Postgres need like not to lose
the game by the way right now,

if you check Hacker News trends,
it was growing and not only

about job postings as I usually
mentioned, but everything, all

discussions on Hacker News, it
was growing until the beginning

of last year, 2024.

And then we have slight decline.

And I think Postgres has right
now huge challenge.

It needs to be much more automated.

If things needs to be in core,
it should be in core.

But not everything should be in
core we know autofailover is

still not in core right Patroni
right but Patroni Patroni I just

asked Kukushkin did has he considered
automation of post resume

and switch over without downtime
this is because this is what

people want and expect for highly
automated system he said no

and I started making joke because
I said Patroni is outside of

Postgres because it's not the job
of Postgres to do HA or to

failover and now I expect you Patroni
maintainer to tell me that

automatic switcher 0 downtime switcher
is no job for Patroni

so I need to implement another
layer on top of it, right?

This is what actually we do already.

For 0 downtime upgrades, we do
this.

And by the way, I can share this
already on our consulting page,

we mentioned GitLab, but also Supabase
and also Gadget.dev.

These companies took tech from
us And I have official quotes

from their managers, so I can share
this news.

I think we developed really great
automation for 0 downtime upgrades,

which are not only 0 downtime,
but also, of course, 0 data loss

and reversible.

And reversible without data loss
as well.

So these are the perfect properties,
but it requires a lot of

orchestration.

Fortunately, since Postgres 17,
things are improving and less

pieces need to be automated.

So let me go back and explain my
humble, finally, and my vision

of what I have right now.

I realize that boring stuff needs
to have much higher level of

automation.

This is 1.

Second, I realized PostgreSQL CI
team, this is exactly what we

are doing because with consulting
people bring us these topics.

And I realized also that in every
area if we think about automation

of feature we can apply simplified
approach for classification.

So if every single step must be
executed manually, like CLI call,

like pg_dump call or something,
pg_upgrade call, or SQL snippets,

if they are to be executed manually
by engineer, this is manual.

If there are bigger pieces, which,
like, say, whole procedure

consists of 2 or 3 big pieces, and they are combined, and engineer

only makes choices to proceed or not between pieces.

For example, our major upgrade consists of 2 huge pieces.

Physical to logical with upgrade, they are bundled due to some

specific reasons.

And switchover, 2 big steps, and inside them there is a high

level of automation.

You just call playbook, it executes, right?

In this case, it's already can be considered level 1, say.

Say level 1, or like 2 maybe, I don't know.

And then if we can fully relax and go to passenger seat and just

say, okay, I approve that, that you need to do everything, but

you will do it yourself.

I mean, Postgres or additional system will do everything yourself.

Like full major upgrade with switchover.

In this case, like say at level 2 and then, and you're in passage,

but there are limitations if it will encounter some problem,

it will stop, revert, postpone.

And you need to approve things.

Highest level, if this feature, like if system decides, oh, it's

time to upgrade, and then it schedules it, like, oh, we have

low activity time on the weekend, and you even, like, you are

notified about it, You probably can block it, but it moves on

itself.

This is the highest level of automation.

And for back to remixing, which I chose this as lowest hanging

fruit.

Everyone needs it.

Nobody in my own community, maybe after our episode, people from

RDS, CloudSQL, I know some of them are listening to us, will

rush into implementing this.

I just see everyone needs it, nobody has it in terms of managed

Postgres, nobody offers it.

So I am highest level automation for pg_index_pilot right away.

So it will decide when to re-index.

It will re-index.

It will control activity level so not to saturate disks, and

so on, and so on.

You can check roadmap.

I explained it in readme of this project.

And this is open source.

Because I actually truly believe at some point it won't be needed,

this project.

If Andrey's idea will work for merge, maybe, I don't know.

This is a dream, right?

So forget about index bloat.

Right?

Yeah.

I guess it will be super hard.

I tried to research why it hasn't been done and I didn't see

why.

Maybe it's scary.

This is basics, like foundation
of whole Postgres, of any cluster,

right?

B-tree.

Michael: Yeah, well, I guess when
would it be done?

Would it be done by a Background
worker like autovacuum?

Or would it be like, at what stage
does it make sense to do?

Nikolay: Well, it should be synchronous.

Oh, I know, this is a good question.

I'm going to ask Andrei about this.

Yeah.

Because split is synchronous.

Michael: Yeah.

I also think the thing you brought
up just at the end there is

super interesting.

Like, controlling the rate to not
saturate disks.

Like, this is an interesting thing
with tradeoffs again.

Like, if you're on Aurora Postgres
and you're paying some amount

per IO, like, for you probably
don't want your indexes rebuilt

constantly all the time.

What you really want is to fix
the root cause.

It's probably an application issue.

Why are you updating the same Row
thousands of times a second.

Nikolay: What's the root cause of

the bloat?

Michael: So, we just had a

Nikolay: case of updating multiple
times a queue like workload.

This can be identified.

Yes, yes.

Michael: Yeah.

So, I'm excited to see what you
build.

I think you're right that more
automation in Postgres would be

good, more automation in extensions
around Postgres would be

good.

But a lot of the issues I see are
kind of application source

issues.

And even if we make Postgres completely
self-driving, there is

still this kind of application
level issue that will be...

Nikolay: Mistake there can put
all your efforts to the ground,

yeah.

Michael: Yeah, exactly.

I agree.

Nikolay: That's why in our, like,
I envision, I identify 25 areas

in my blog post.

Yeah.

Office during our launch week.

Maybe we will adjust those areas,
of course, like it's not final

list.

And we are targeting 3 right now.

Like I hope we will expand this list to 5 to 6 next year.

Once we have quite good building blocks, we'll think about whole

system.

But the central part of all these building blocks is new monitoring

system, which is good both for AI and humans.

And This is what we're already actively building.

We replaced all our observability tooling used in consulting

with new monitoring system, which is called Postgres AI monitoring.

We talked about it separately.

This is going to be the source of insights, why things are wrong.

We cannot self-derive application yet, Although in some cases

might be so, because if for example, Supabase, they control

not only Postgres, but also REST API level, right?

So some things might be down there, but in general case, we cannot.

So in this case, we need just to advise and so on, but eventually

things like serverless, like with cell workloads or so, I see

that can be together with database eventually.

In this case, we can discuss full self-driving something.

Right.

But it's, we are very far from that, I think.

Yeah.

Yeah.

And, and we have limited resources.

I wanted to admit that I'm targeting very narrow topics right

now.

So very boring but really doable and We see demand Like things

like partitioning help with partitioning.

It's nightmare to do it for arbitrary engineer We say Oh declarative

partitioning, but for example partitions are not created automatically

Okay, there's pg_partman, but you need to combine this pieces

something like The level of automation there is terrible.

Michael: Yeah, but these are the kind of things I'd love to like

a couple of things.

I'd love to see more of this go to core.

I think there has been improvements in partitioning core, many

over the last few years and continuing.

And the other thing is, I think there's a lot of focus on fewer

people.

With this automation, a lot of the focus is on we can do more

with less humans.

And I'd love to see more effort into what would happen if we

didn't try and reduce the humans but instead try to increase

the reliability.

You know what I mean?

And I think there's a lot of talk about how do

Nikolay: we cut costs.

We have already I think 5 companies who are very fast growing

AI companies.

Very well known already.

And I see a very different approach to Postgres.

For them it's a natural choice,
like let's start with it, but

then they, oh, we need to partition
terabyte size tables.

Let's estimate work, oh, that much
work?

Okay, maybe we should migrate to
a different database system.

They are moving really fast.

And they are not attached to Postgres,
basically.

So I'm scared about Postgres.

That's why I'm saying automation.

There should be a massive shift
right now.

And resources of my team is not
enough.

So that's why I'm talking about
it.

I'm going to put whole focus on
it.

During consulting, we also choose
only paths that are fully automated,

highly automated, fully automated.

And I'm looking for some use cases,
more use cases, more maybe

partners who think similarly, right?

This is a call for Postgres ecosystem,
like it's not enough right

now, like we're not...

Postgres might lose this game again.

Postgres won multiple challenges,
like object-oriented, NoSQL.

But AI, everyone thinks about AI
only about like pgvector or

storing vectors.

It's not.

Not everyone needs vectors.

People build some app, it needs
database, maybe with vectors,

maybe without vectors, but what
they expect is higher level automation

scale easier.

So I'm super happy to see new innovations
in the area of sharding,

right?

Yeah,

Michael: I think that's actually
more, I do think that's more

important, partly for just the
marketing story.

Nikolay: Then partitioning?

Michael: For those, yeah.

Because I think we talked, like,
if you think about partitioning

if you I think for example when
we spoke to through when we had

our hundredth episode and spoke
to Notion, Figma and Adyen.

Yeah.

Notion skipped partitioning and
went straight to sharding.

They decided that with all of the
partitioning downsides, and

then there are quite a few, quite
a few limitations, for their

multi-tenant architecture it actually
made more sense to shard

instead of partition.

That felt like it wasn't 1 of these
things where they were moving

super quick and made a rash decision.

It felt like a really considered
engineering decision.

And I actually think it was the
right call.

And I wouldn't be surprised if
more of these companies are coming

in and building fairly seamless
sharding that doesn't add any

complexity at the application level,
I could see people tempted

into that, even if it wasn't for
good engineering reasons, just

for the marketing reason of, I
don't have to think about scaling

this, and I don't have to deal
with tailback tables.

Nikolay: You know this normal distribution
meme, right?

Yeah, yeah, yeah.

Usually it's like unexpected on
the right side, where it's expected.

So I don't know where, in which
camp I am.

I was thinking Postgres 1 node
is cool enough, then I was thinking

it's not cool enough because of
performance cliffs.

Now I'm thinking maybe it depends
because in some cases like

yeah it's much safer to avoid all
performance cliffs, not just

allowing like 100 plus thousand
transactions per second on 1

node.

You just, and how it's called?

Resiliency, right?

If 1 node is down, only part of,
like, it's just 1 shard, right?

Maybe yes, but at the same time,
it's so cool to see projects

which require only 1 node.

They are isolated.

They don't need a whole, like,
bunch of shards and clusters,

cluster of clusters, or cluster
term is heavily overloaded, right?

Yeah, and I think, oh, that's really
the power to have just 1

single node, sometimes without
replicas.

I see projects without replicas,
Because cloud times change,

right?

And I think I'm open, you know,
like I know my perception over

years changes, sometimes it's a
pendulum.

So it depends, you know, like regular
answer, normal answer from

consultants, it depends.

Yeah.

But sharding, it's really great
to see that it's coming from

multiple teams, there will be competition.

So yeah, the here, the future looks
bright, but who will be helping

to choose proper schema for sharding?

Michael: Well,

Nikolay: yeah.

And rebalance properly and normally.

Well, yeah, so I think they who
build this think about this as

well, automation, and the problem,
like, to choose time for rebalancing

and fully automated?

Michael: Well, we talked to Sugu,
he said it's inevitable that

you're gonna have to change your
sharding scheme at some point.

It's just designing for that upfront
seems really important.

Vitesse handles it.

So yeah, Interesting times ahead.

I'm a bit worried about the complexity
of these systems.

Personally,

Nikolay: I quite like Let's start

Michael: with simple things.

Yeah.

I quite well, starting simple, but also I like the idea of if

I'm a Postgres user, I can still understand the system, like

roughly what's going on, even if I'm a full stack developer,

even if I do the front end, the back end, like, I like, I know

it's already very complex.

I know there's already a lot of internals that you kind of need

to know about to make a performance system.

But I hope we can hold on to that for a while.

Well,

Nikolay: I truly believe that what my team does is going to help

because we observe many problems.

And every time I'm saying guys like we need to write down some

how to and So show with experiment so users understand what's

happening.

Write some how-to for next time.

And when we're writing how-tos recently, we write it both for

humans and AI.

So next time some RCA is happening, root cause analysis is happening.

If you have our how-tos injected to your Cursor, for example,

it's going to take into account some situations which are written

and how to reproduce them, how to troubleshoot them, right?

So I think something else is coming.

I'm not going to spoil it, but RCA and troubleshooting is 1 thing

we will attack early.

We are preparing pieces for this, you know, and 1 of key indicators

of success here is that non-experts understand what's happening.

You know, because...

Michael: Yeah, well, that's what I've...

That's the area that I specialize in.

You know, I'm actually betting a little bit on humans staying

in the loop for quite a long time and that there will always,

well not always, but for a long time, there'll be categories

of issue that we still need somebody to look into.

And I kind of feel like the level of Postgres knowledge, the

median level of Postgres knowledge for people having to look

into those issues is probably going to go down.

Based on all the trends that you're talking about, It depends.

If we only have a few experts that are shared between lots of

companies, maybe that's not true.

But if we do have a lot of individuals starting with web coding

or starting with like doing a full stack kind of single person

companies running the whole product start to finish.

Those guys, like, they have to know a lot about a lot of things.

They can't know that

Nikolay: much.

Have you seen numbers, Supabase shared, how many clusters

were registered and how fast it is.

Yeah.

Can you imagine how like, yeah, or average knowledge of Postgres

there.

Yeah.

But yeah, but the idea is I, I,
my vision is that we, we collect,

knowledge pieces, we experiment
with, automatic experimentation

and so on.

But then obviously this is what
I see with our customers some

human is needed to explain properly
to other humans to answer

questions properly you know to
build trust and confidence and

so on.

Michael: Yeah, but my question,
I guess the question then is

where do the tools live?

Like, can the end user use a tool
to get help?

Like, or does the Supabase team
use a tool to get help?

Or does the consultant that the
Supabase team employ?

Nikolay: My answer is, who's the
tool for?

Michael: And I know I'm just saying
that, like, it depends who

you count as the user in terms
of how much their Postgres knowledge

is.

I'm talking about that end user.

Nikolay: Yeah, for example, with
DBLab we already went this path.

We moved from a couple of guys
answering all the details for

Backend engineers have in terms
of how plan works and what to

do about it, which index to create.

We now have DBLab Backend engineers
experiment themselves.

And if something is unclear, only
then they call an expert for

help.

Right.

But like 90% of the questions answered
by Backend engineers without

involving expert, Postgres experts,
you know, and expertise in

Backend engineering minds grow,
grows as well.

I I'm just thinking this approach
we had for query optimization

can be applied at ground of schema
for many other areas.

Yeah.

Michael: All right.

Probably enough for today.

Nikolay: Thank you so much.

We went much deeper in specific
areas than I expected and I enjoyed

it a lot.

Thank you so much.

Michael: Nice.