Nikolay and Michael discuss self-driving Postgres - what it could mean, using self-driving cars as a reference, and ideas for things to build and optimize for in this area. Here are some links to things they mentioned: Nikolay’s blog post on Self-driving Postgres https://postgres.ai/blog/20250725-self-driving-postgres SAE J3016 levels of driving automation https://www.sae.org/news/2019/01/sae-updates-j3016-automated-driving-graphic Oracle Autonomous Database https://www.oracle.com/uk/autonomous-database/ Self-Driving Database Management Systems (2017 paper) https://db.cs.cmu.edu/papers/2017/p42-pavlo-cidr17.pdf PGTune https://pgtune.leopard.in.ua/ pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot/ [Vibe] Hacking Postgres with Andrey, Kirk, Nik - index bloat, btree page merge https://www.youtube.com/watch?v=D1PEdDcvZTw ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
Michael: Hello and welcome to Postgres.FM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard, 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.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...