Nik and Michael are joined by David Ventimiglia to discuss pg_flight_recorder, a new tool he created for monitoring a Postgres database from within. Here are some links to things they mentioned: David Ventimiglia https://postgres.fm/people/david-ventimiglia pg_flight_recorder https://github.com/dventimisupabase/pg_flight_recorder Supabase https://supabase.com pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling pg_ash https://github.com/NikolayS/pg_ash pg_cron https://github.com/citusdata/pg_cron pg_tle https://github.com/aws/pg_tle ~~~ 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'm Michael, founder of pgMustard and I'm joined as always by Nik, founder of PostgresAI. Hey Nik. Nikolay: Hi Michael. Michael: And we have a special guest, David Ventimiglia, Solutions Architect at Supabase and the creator of pg_flight_recorder, which we're talking about today. Welcome, David. David: Thank you so much. Good morning, and I guess good afternoon and good evening. It's nice to meet you. Michael: Yeah, I think we've got all 3 bases covered. Where would you like to start, perhaps, with why another tool
in this area? What's the origin story? What's the motivation? David: The motivation, so as a solutions architect at Supabase, my job evidently is to help our customers and often they come to us and they say, I've got this problem. My database is slow, or this query is behaving weirdly. Please help us. And then we try to bring whatever tools we have to bear on the subject. And to be honest, often we're starting out with no idea what
the problem is. And our beautiful customers cannot be relied upon to, you know, relay all the information perfectly. And I just needed more. I was telling Nik this at 1 point, you know, some people say less is more. I say more is more. I needed more data and I just wasn't getting it. And I did the usual thing that people would do these days. Version 0 of this, maybe 4 or 5 months back, it was a rush job with our good friend Claude Code. And I just cobbled something together to get the data that I
needed for a particular customer. And we got the data that we needed and we were able to get through that particular instance rather swimmingly. And I was pleased by the outcome and I thought, you know, let's try to turn this something into something a little better and a little something more real. As a sidebar, I think we're all, most of us are familiar with the excellent pg_wait_sampling, which is a excellent extension, but sadly is not available on all managed Postgres services.
Even Supabase... Nikolay: Cloud SQL David: is the only 1. Cloud SQL, but even Supabase, on whose staff is Alexander Korotkov, who wrote it, but we don't have that extension on the platform. And there's, you know, there's some resistance within managed platforms to get new extensions added. I mean, there's a rich and vibrant ecosystem for extensions and that's 1 of the strengths of PostgreSQL, but a weakness is getting those into all of the places where you needed it.
So I needed to, I needed something that was a poor person's substitute for pg_wait_sampling. That's really how it started out was just a worse version of pg_wait_sampling written in SQL in PL/pgSQL. And then you know how these things go. It just took off from there. Nikolay: Yeah. There's a lot of unpack here actually. Yeah. Yeah. And, and I agree with you starting from the end of your intro. I keep saying extensions are not extending us anymore, because
in reality of managed Postgres, we are limited by only the set of extensions which are present. There is pg_tle, and we should discuss that separately, which is, I think, a great idea. Actually, you told me. It's a lot of happening also. Let me tell my story. I think Every experienced Postgres DBA at least once wrote this snapshot tool for pg_stat_statements and other pg_stat views because it's only cumulative statistics.
Some numbers are growing, that's it. We need persistent storage. Usually for bigger clusters we have monitoring. But I remember I was working with a really big company called Chewy. And I remember they had great monitoring already. Some clusters were on RDS already, so they also have some performance insights. Still I wrote my snapshot tooling because I didn't fully trust
monitoring things. I also wanted to verify, and some details were missing because they didn't capture all metrics. I needed specific metrics and so on. So meanwhile, there are some projects which implement this idea, but they are extensions, so they are Not available. pg_profile, I think, there is such a thing, right? pg_profile. So that's why many, many DBAs, maybe not all, but many DBAs at least once wrote some snapshotting tool. And I remember our checkup tool was also snapshotting.
First versions of checkup tool, it was a shell script. So we snapshot it. You have 2 snapshots, now you need diff. And since we were in Bash, we didn't want to do diff, so we sent these snapshots back to do diff on the observed Postgres. So I saw a lot of solutions which try to make this data persistent and have snapshots and then show like everything without the need to set up full-fledged monitoring.
And even if it exists still sometimes we need additional lightweight solution. This is 1 thing to warm up us why it's needed. Another thing is that I guess Supabase has a lot of clusters. Some of them are, David: we have a few. Nikolay: Yeah. Yeah. Like millions, right? It's quite unique, interesting story. And many of them are small and you cannot justify full-fledged monitoring, right? David: And getting smaller, yeah. Nikolay: Getting smaller, yeah, because people just experiment
so much, right? And they need so many databases. David: Yeah, we are now, I mean, we have a skewed distribution, of course. We have a few giant customers, but we have lots of medium-sized customers and many more small ones and millions of tiny ones. And now with the AI builders, we are shoveling millions of nano instances into the AI builder furnaces. Like where are these databases going? What's the long-term prognosis for these tiny databases? Who knows, who cares? That's a completely different animal.
But yes, it's a very, it's a vibrant ecological niche that we've developed here. Michael: Yeah. On that note, who's this tool for? Like of that distribution, are there some where it's not appropriate for them and some where it's ideal or like where does it fit? David: Yeah, that's a great question Michael. Again, and as Nik indicated, this is, you know, all these things what time is a flat circle, all these things will happen before and will happen again. You know, versions of this have been written before and versions
of this will be written in the future. There's nothing really that profound about this, but this is the tool that I needed right now at this time for the reasons that Nik just described. Among which is, you know, we have, I, who, who this tool would be for, I think would be startups, SMBs, builders, sort of the canonical Supabase customer. Those who are starting out, you know, building a business, building a backend, building a project, they need PostgreSQL and off they
go. You know, we at Supabase, we, we don't, I don't think it's any big surprise. We don't really have that many migrations onto Supabase. I mean, we would love to have more than anybody who's willing to bring giant workloads over to Supabase. Come on over. But we know that databases are infamously sticky tools anyway. People don't really migrate that often. And they're probably less likely to migrate giant workloads over from Oracle or Microsoft SQL Server to Supabase, although we
are entertaining that option. But if we did do those things, those folks would probably come over with DBAs, database experience, database expertise. So our sort of customer portfolio doesn't really reflect that. What we have are, even our largest customers, I would say, tend to be, I mean, they may have 3 or 4 or 5 years of experience with PostgreSQL now, by dint of hard effort, but they all started
out small. Every 1 of our large customers was a little acorn that grew into a giant oak. And we try to make Supabase easy, and we do. It's certainly easy to get into. I've used this analogy too many times, but it's like the car dealership. You can drive it off the lot in 5 minutes, but actually Operating it, especially at scale is something different. And we, Nik knows this, Michael, you know this, we would all benefit from more and better automation and it's coming.
It's coming from within the community and it's coming from Supabase. We will be able to help these customers more seamlessly and operate their databases in the future. But right now what we need is tooling to help customers as they grow and as they scale. So in a nutshell, like who's this for? People who are not DBAs, not database experts. They just want to run a business. They want to grow that business and they want some tools to help them. That's it. Nikolay: Some of them start with some small, very small database
instance paying 25 or some very low number of bucks, right? And it's hard to justify paying right away 150 or $400 or $500 for monitoring a full-fledged solution. And then you need to spend time there and so on. It cannot be justified easily. And also I wanted to mention, it's quite elastic. So if you just inject this tool inside your Postgres database, it starts collecting inside, like self-observed.
And you pay a little bit for those megabytes per day, I don't know. Since I helped with storage to rewrite it, it's quite efficient. I again used this approach for PgQue, rotation of partitions and truncates, So it's very efficient and so on. And I'm just saying it's like a little bit, you pay a little bit and it's self observed, right? And when I was thinking what are pros and cons, self-observed versus externally observed, ideally we need to have both actually,
Because you cannot install agent right to RDS or super-based machine. So if you observe it outside with external monitoring tool, if something bad happens, maybe you don't have connectivity, right? While this thing sitting inside, it still keeps observing, right? David: That's right. Nikolay: At the same time, if everything is down, you don't see, you cannot reach the data, right? So like external tools also have benefits. They have both pros and cons if you think about it. It's interesting.
So in my realization, even bigger clusters should have maybe a small black box or flight recorder, right? While we have a full-fledged solution outside, they're both remote telemetry and something internal, right? David: Yeah, that's right. And I think I landed on the name pg_flight_recorder. And then at some point, I think it had some reservations because I thought, sure, but in the event of a crash, then maybe the data aren't available and it's not really that useful. But then, I mean, if an actual airplane crashes, then that airplane
also is not really useful either. That airplane is dead. No 1 Nikolay: will be using it. Just a side note, I just learned David has a PhD in astrophysics, so this name is not a random thing, I guess, right? David: And a master's in aerospace engineering. But at every turn, I was trying to do something else. And I was trying to get away from computers. And it just, I just kept getting sucked back in. But I grew up in the 70s when It seemed like airplanes were crashing all the time when they weren't being hijacked.
Mercifully, that doesn't really seem to happen all that often. But I think, I'm not a pilot, but it's my understanding that actual, you know, flight recorders are useful for far beyond crash investigation. They're useful for optimization, for troubleshooting, like, in-flight incidences. And so I think the nature of this is hopefully a little bit more like that. Nik, you would know better than I would, but I have the feeling that in reality, databases don't really actually crash all that often. What they do is they exhibit behavior, and we want to be able
to investigate that behavior. And that's what this helps us do. Briefly about the tool itself, again, all it really does is it takes snapshots of wait events. That's how it started. It's like pg_ash. Nikolay: We developed it in parallel, actually. Yeah. So when I told David that there should be something small which self-observes, David just sent me the link. It's already done. It was interesting that we had parallel courses of development of pg_ash and pg_flight_recorder.
They're very similar in this case. David: Yeah, very similar and like that it captures active session history, wait events, nature of course of Vacuum and Idle Hands of the Devil's Workshop. You know, with the tools available, I couldn't resist the urge to just keep pouring more into it. So it records lock activity and checkpointer activity and background activity and IO stats and statement stats and... Nikolay: Config changes, right? David: Config changes as well. And hopefully the conjecture is that there's some value in, if
not capturing everything, having an opinionated and curated set of many things that are captured simultaneously in a correlated fashion so that maybe you experience a checkpoint storm and then you notice that there has been a config change recently and you're able to bring these things together. So that's the idea. As Nik indicated, it was when we talked about this, version 0 was done. Again, it's a pretty simple tool. I had a few guiding principles, 1 of which was sort of the Hippocratic
oath, try to do no harm. I put a lot of effort into making this safe to run. Nikolay: Statement timeouts and so on, right? David: Yeah, statement timeouts, circuit breakers, graceful degradation of some of the components, dozens, too many configuration settings, but then configuration profiles that that capture those to make it easy to use. I think I got 3 quarters of the way there or maybe 50% of the way there, but there were still some improvements to be made, among which the storage engine, which we can thank Nik for rewriting
using PgQ or essentially the engine that is part of PgQ. Correct, Nik? Nikolay: Yeah, it's like partitions, rotation, I think daily partitions. There is also a roll up for all data to have it less precise, not raw but aggregated. Everything is already implemented. And I remember I was brainstorming with Claude Code, like what kind of storage we should choose because I think originally you used a lot of JSON, right? It's quite bloated in my opinion sometimes. David: Well, It wasn't JSON, but I had originally, I was using
skip locked and unlogged tables in a vain attempt to mitigate dead tools and bloat. But If you're not diligent, then they're still there. And so that's why you had to rewrite the engine. Nikolay: Also, like, data format is interesting. And I had multiple ideas, and I have some, like, brainstorm document where, like, thinking what to choose. And some ideas were compressing data quite a lot, but it was hard to deal with because it was basically encoded so much that
it's inconvenient, so I did a trade-off choice that it should be human-readable even in raw form, although I did apply some tricks from pg_ash as well, like timestamps are relative to, I think, I don't know, 2020 or something. Like Unix timestamp by shifted, so we have capacity until the end of century. And you have as few bytes wasted as possible, like very compact way. And plus this PgQ style rotation. And also, worth mentioning, there is a soft requirement, pg_cron.
It's not a requirement, but it's very recommended, because this is how it's ticking as well, right? David: That's correct. So again, it's a simple tool. It's 2 sort of packages, 2 simple install scripts, 2 schemas, 1 of which is required, 1 of which is optional. The part that's required, it's the data model, the tables and the views and the functions to record those data. And then the other optional piece is a set of functions for analyzing those data. But again, they could be analyzed in raw form in whatever way
you like. But as Nik indicated, somebody has got to generate the ticks. Somebody has got to force the samples and that could be pg_cron, it could be something like an outside scheduler, somebody's got to do it. The sort of default way is with pg_cron. Nikolay: And pg_cron is available everywhere, right? David: Yeah, pg_cron sort of snuck in before the sort of iron curtain started to drop on extensions, maybe. So it's in a lot of places at least, you know. Michael: I, I, yeah, maybe that's true.
I got the impression it solved such a useful problem and was from such a reputable author that I think people trusted it and also thought it's simple enough that we can maintain. I understand why managed service providers don't just offer any extension but if you think about how much work it would be to maintain pg_cron if the author ditched it or you know actually it's not huge and it's Nikolay: so useful. And it should be in core. David: There's all of it. That is true. Nikolay: I wish pg_cron was in core.
And we would have, for example, automated new partition creation out of the box without any extensions. Magic. So simple thing, actually, right? Michael: 1 thing about pg_cron from a database perspective is it is, I think, once per second is the lowest you can schedule. So what do you use, David, when you're using this with people? Do you use pg_cron with a one-second tick, or do you suggest something else?
David: So far, I have used it just with pg_cron. The resolution that has been sufficient so far, you know, because with the customers that I've worked with, the resolution before has been, I guess, infinite as in they didn't have this at all. So it's just worth having the data. And a finer resolution, I haven't encountered a demand for that or a need for that yet, although certainly plausible. But again, yeah, that has worked well so far. And Again, this is just a simple tool.
The idea, the objective anyway, is sort of a set it and forget it. Install the tool, then forget that you've installed the tool. But because it's safe to run, by virtue of Nik's hard effort, it is safe to run. So just forget that it's there. And then you have an incident, and then you think, oh wait, I have pg_flight_recorder. Let's find out what happened. Nikolay: And just point your eye to the data, maybe a dump of that data or something, and that's it.
And the second package you mentioned, it has interesting functions, like what happened at or something, right? It's based on function names. I see already your thought, oh, AI should guess, right? You designed it for, So it's self-explanatory, right? So it's great. But I also wanted to... About pg_cron a little bit. Version 1.5 is, I remember, the lowest resolution, once per second, and I use it for pg_ash, but I guess you use it by default at much less frequency, especially for Azure data, right?
Maybe once per 30 seconds or 60, but it's tunable, right? David: Everything is tunable. You know, default sample collection is, I think I have it set to once per second, but There are aggregates that are taken at a coarser resolution. There are roll-ups that are taken at a coarser resolution. Data are archived at a coarser resolution. Then there's retention for the core tables, as I think that my default is 7 days for the aggregates of the 7 days.
And then for, for snapshots, I think it's by default 30 days, but all of these are configurable. So there, there are a few different cadences that are happening. And there is, again, as you indicated on the analyze side, there is a wall of functions, appropriately named, meant to be understandable by humans and by AI alike, so that they can use these functions to analyze the data. But then, of course, it's always available to be analyzed in raw form as well. Nikolay: Yeah, I can share some interesting story from PgQ about
function names. When I was developing recently client libraries for PgQue, multiple times Claude Code made a mistake because there is a function force tick, but it's not ticking, it's just shifting this pointer. And then you need to run ticker in a separate transaction. And Claude couldn't get it because it's confusing name actually. And it made mistake multiple times developing this. And this, I had huge flashback to 15 years, 15 plus years ago when I made the same mistake manually without AI, because it
was so confusing to me 15 years ago. So I just renamed that function to force next tick. So you immediately understand this is about next tick. You're not doing, you're just preparing this job. And looking at your functions, what happened at, incident timeline, I'm just thinking, this is self-explanatory. Maybe long, but everyone will understand what it is for. So it's worth making a lot of. David: Yeah. Yeah. That's right. And if there are too many functions, then, again, we can use AI to paw our way through and figure out which ones to use.
Just a final thought. It's meant for a few things, not just incident response, but also capacity planning, blast radius, evaluation. You know, again, where I intend to go with this is just getting back to Supabase briefly. I have lots of customers that I have to, I should say I'm blessed with helping, but so many of them I want to get to early. It's, you know, again we talked about this, but all of these databases are small.
They start out small anyway. I would say from a certain point of view, from the point of view of scale, many of them are sort of doing things wrong, but that's okay because they're small. You can do, with a small database, you can do everything wrong and it's fine, no problems. But it's when you start to scale that you need to think about this. So it was like exercise. It's something you have to get into the habit of doing it early and often, even though you don't want to, and maybe you would benefit from a personal trainer and some encouragement to get
you on the path early so that it pays dividends. When you're old, like I am, and when you're a big database, like some of these eventually will become. Nikolay: I wanted also to mention, by default, it's consuming up to a couple of gigabytes for those 7 days, right? But again, it's tunable if you, or less. David: Yeah, it's tunable. It's a few gigabytes. Nik, I think you and I, we benchmarked this. I think we, with the new storage engine, I think we estimate maybe for like under, on the happy path, maybe I think around
like 20 gigabytes for the month. Nikolay: It depends. David: There's some, 1 of my goals is to sort of draw more attention to this so that I could get feedback and improve it. And there is some low-hanging fruit to be plucked insofar as data retention for path-wise. Nikolay: I think it depends on how many queries you have in Pagesaw statements by default, up to 5, 000. And Also, you collect data about indexes and tables, so how many
tables and indices you have. I think you have limits there, but still it depends a lot on the cardinality of these things. About use cases, I used it recently for benchmarking PgQue. For me it's so natural. I had multiple already projects like this and I just, okay. I injected both pg_ash and pg_flight_recorder because pg_ash has more frequency and more details about ash data. pg_flight_recorder brings a lot of stuff, right? So I just injected it into some synthetic database as provisioned
with pg_cron configured. And I just asked AI, of course, to do it, so just inject it. And then don't forget to dump after each run. And then visualize it. That's it, Only 3 sentences. David: Yeah, exactly. Nikolay: And this is how I created a beautiful looking. I actually asked to animate benchmarks because it's great to look how lines go. And this is what brought PgQue good attention because this data is like easy to understand Right. So for example, how much WAL was generated, right?
Lot of stuff. What was the behavior of check pointer or autovacuum and so on? Michael: That was gonna mean my next question actually on the wall front. You mentioned a few minutes ago about how you originally went with unlogged tables. Does that mean these are now logged and there is Nikolay: WAL generated? Yeah, it was my decision to say that, first of all, important limitation of all those tools which are ticking on pg_cron and write something and it's only PL/pgSQL, it's primary only.
But we live in this strange situation for me, old DBA, when a lot of clusters are single node. I have even cases clients are coming like 10 plus like 15 terabytes on single node and they are fine. Cloud like resources became quite relevant. For some actually I think it's okay Because backups matter more than HA because they are fine to be done, but not to pay for additional couple of nodes and so on. So anyway, this is primary only because we cannot write to- Can Michael: I add something? Yeah. Can I add something to that?
Because you say single node, but I think that's slightly simplistic because a lot of ones I see, they're HA, but the replicas are not read replicas, they're like failover replicas. Nikolay: Shadow standby node. Michael: I wouldn't call that a one-node cluster, but it's still you only need to Nikolay: monitor the primary. Yeah, you're right actually, you're right. But in this case you are not interested because you don't have any workload on that standby, hidden standby, right? Michael: Exactly, exactly. Nikolay: So you are interested on the primary and we see so many projects reaching dozens of terabytes already, which single node.
You inject it, we need to understand, okay, it's self-recording, so it's going to produce some writes. If it's unlogged table, if it crashed, it's gone. That's the key idea. We cannot afford... David: At least, where the data lands initially, those data would be gone, right? When they were unlogged tables. Nikolay: You need to snapshot, you need external means. So to understand the incident after crash, we should use regular tables. And when we've redesigned storage, it's not so super expensive.
Of course, there is some WAL to be written and some data storage to be paid. And of course, a little bit of shared buffers occupied by our data. It goes to, if you have replicas, it goes to replicas. Maybe it's not a bad thing because if it's the health of primary, we can pay this price. Michael: What are we roughly talking about? You mentioned a few gigabytes up to maybe 20 gigabyte, like that kind of amount for storage. What are we talking about in terms of WAL generation by default, just to give people a rough idea? Nikolay: I don't remember.
I thought about baby clusters like 1 gigabyte once, like free tier is up to 1 gigabyte, right? So I thought they should afford this maybe with a little bit tuned to less frequency or something retention wise But it's Michael: a storage now or WAL 3 Nikolay: both both. Michael: Okay, Nikolay: They are connected actually. If you need to write- Michael: You mean on super base? Nikolay: Anywhere, any Postgres. If you need to write 100 megabytes to storage, you will produce like very roughly, you will produce kind of close to 100 megabytes
to WAL because this is the same data. Yes, in different form, but it's the same data, right? If you need to write 100 times more, expect 100 times more of wall. David: Yeah, order of magnitude, it would be about the same. Nikolay: Yeah, very roughly. Of course, like full page writes, all the compression, but it's very different. Michael: I also think of them very differently because with WAL I think of it as like megabytes per second, always. It's always like a, with a time component, if that makes sense. So it's like a, it's a constant amount that we're generating,
of course over a month or whatever it is, that's a few gigabytes. But I guess that doesn't actually add up to very much per second in terms of, yeah. Nikolay: I think we should expect something like 100 to a few hundreds megabytes per day with a lot of queries and indexes and so on. Yeah. David: I mean, if you like ballpark math, if it were 30 gigabytes of data per month, that would be roughly, I guess, by the power of arithmetic, maybe a gigabyte per day. Nikolay: It's very stable.
David: And by 3600, you could figure out megabytes per second of WAL generation. Nikolay: Kilobytes maybe already, right? And it's very stable because it depends only on this cardinality. And if you have some spikes of workload, it doesn't affect the amount of data, these snapshots, right to WAL and data directory, right? David: It's a baseline. So just, you know, you're paying maybe $25 to Supabase, maybe pay $26 and just pay for a little bit more storage.
Nikolay: Not hundreds more as you would pay if you install full-fledged monitoring. David: And if it's only, if it's to first order for the primary node. Again, all of these small projects are starting out with only 1 node anyway. I mean, life is complicated and these people are just trying to get like a business off the ground and a job done. They're not thinking about multiple nodes, especially early on,
but they, you, I mean, we 3 know that they will need data to guide them on their journey. So this is just part of that. I mean, Michael, you work in the observability space. We sort of dilate this out to a wider view. This is just another entry in the observability space, like maybe a new generation 0.5 of observability tools for PostgreSQL. But I mean, and there's pg_ash and there's... Nikolay: So it's not only about observability. I see, actually, the word new kind of breed you used, I think,
in our discussions. So I have pg_ash, you have pg_flight_recorder. I also trying to revive PgQ in this very format, pg_cron and PL/pgSQL only. That's it. So it can be installed anywhere and just tick. I also have Lindex, which is not yet released, which is rebuilding indexes on pg_cron. That's it. You can inject PL/pgSQL and tick on pg_cron. That's it. It's super simple. I already think about tool for automated partition creation without heavy tools.
I don't know, it should be easy to use. But then you mentioned pg_tle. David: Yes. Nikolay: So can you maybe elaborate a little bit why pg_tle? Why not just single SQL file or PL/pgSQL file? David: It's both. So, for those who don't know, TLE is trusted language extensions, which I have my own view on that. I regard it as just a little bit of extra housekeeping that's associated with just a simple SQL install file, but they are,
they, you know, TLE sort of dress up SQL and PL/pgSQL code as if they were a sort of kind of managed extension, but they can be installed without super user privileges. pg_flight_recorder comprises both just simple install scripts. You can use psql to install it, but it also is available as a trusted language extension that can be installed through I think dbdev Because that's how some people want to be able to install.
Nikolay: Just to track like metadata. David: Yeah, just so you can do, it makes housekeeping a little bit easier. You can slide pg_flight_recorder in with an install and if you don't like it, you can uninstall it in a very managed fashion. So that's all that's meant there. But yeah, it's very TLE, it's a very lightweight way to have managed extensions and Flight Recorder offers that as well. Nikolay: Yeah, I actually wish PjCrone and TLE, they're both inside Postgres itself. And we would say something like create package or something.
I don't know. And it's just a bunch of SQL and Pell, just go code or maybe Pell Python if you want like anything and it just can be installed anywhere with versioning and so on with like tracking of CVS. I don't know, like if there are any and so on, who knows. Yeah. But just like extensions don't feel like a part of extensibility of Postgres to me anymore. This is my honest like feeling lately. David: Well, it also, what worries me is who is testing, I mean, with, with major version upgrades, let alone minor versions,
who's testing all of these extensions? Nikolay: But this question is also applicable to any regular backend code. You use some libraries, You just import them to your code, somehow include, and that's it. And versions also matter there, and it's on your shoulders. It should be on your shoulders. This idea that we're not providing some extensions because we will need to maintain, give it to shoulders of people. This is a different part of the thing. David: This is intention with products which telegraph or advertise
that were easy to use and you don't need to worry, we will handle it for you. Nikolay: Yeah. But I think It's great to have flexibility and if people can use various, like, choose and... But they need to be responsible for upgrades and part of maintaining as they are already for libraries and Go language, any language, right? TypeScript and so on. So there's something here. And I think it's AWS guys who created pg_tle, right? So definitely this project was created with realization that
something is limiting people here and let's bring something here. That's a great idea. And I wish- I mean, David: Michael, do you experience that at all with your customers? Their challenges? I mean I know you work in a slightly different space but you certainly you must encounter this as well like tensions with extensions, with managed database providers. Michael: Yeah so I get the impression so I don't speak to people all the time about this kind of thing, but I get the impression that people are looking for a little bit of advice almost from
their managed service provider on which extensions they should trust, which are like the best at what they do. You know, often there's a choice of 2 or 3 and they kind of want their managed service providers to pick 1 and say, you know, this is the 1 we suggest or this is the 1 we support and I feel like there's a little bit of that going on as well so it isn't just yeah I think it is a little bit of I don't know if it's like king making or something but like people saying this is the 1 everyone using when people come to Postgres for example for the first time they're like which backup tool should I use
which monitoring tool what's everyone else using And there's no kind of like official, there's no, there's barely any kind of extension management systems. There's been about 3 or 4 kind of created and I think there is still, is it PGXN? That's probably the most used. But it doesn't have like reviews or it doesn't have, Like it doesn't have a lot of things people are looking for in terms of which ones are actually used which ones people do that people actually like which ones have got a good track record when it comes to major versions or low Maybe no CVEs or very few CVEs,
you know that kind of thing So I think trust is a big part of it, and also people want a shortcut as to which ones of these should I be using. Nikolay: I actually agree. Supabase's, this database.dev, it's another attempt to have this register of extensions, right? David: It is yet another attempt, which seems to be somewhat honored in the breach but it's yet another attempt but Michael I take your point definitely that there is a need and I would say a growing need for if not king making at least someone to offer guidance and to bless these You would know as well as I
would, the sort of persona for database operators definitely does seem to be changing. I mean, there once was a time when databases were an arena for people to sort of develop and then project expertise, which is certainly true. But more and more, I encounter people, customers, super-based users, who are very candid. They will say to me or to us, I don't know what I'm doing. I'm not a DBA.
Some of them will say, I'm not even a tech. I'm not even technical. I'm a founder. And I just vibe coded my way into this. And, you know, there's less of an urge now than there was in the past to sort of burnish your credentials as a database expert. People are very happily, they're very candid, and they will say, I am not a database expert at all, so please, can you help us? Can you offer guidance? If you tell me what extension to install, I'll install it. So there's growing need for those kinds of tools and for greater
and better automation. That can be a topic for another session. That's where my mind is. Nikolay: And I agree actually with this authority, what's good, what's reliable. Sometimes I have cases where we have huge Postgres, self-managed Postgres clusters, and when I'm saying we should add some extension, I'm saying it's available on this managed platform, so it's reliable, you know, let's add it. It helps me to speed things up so yeah
Michael: I had a couple of last things I wanted to make sure like or to get it'd be great to get your thoughts on 1 is is there any like Is there anything that we haven't talked about that you have 1 of your favorite features of the talk? I've seen there's quite a few in there and then also is there anything missing that you really want to add? David: There are In reverse order things that I want to add I again I really want I would like to fortify this against observer effect, against deleterious consequences.
I already know that there are some important fixes to be made and I'm committed to doing that. I'm hoping, my tender hope is that people will use this to some degree again, so that I can get feedback. If there are, if it needs to be strengthened, I will strengthen it. I will pour effort into it to make sure that happens. So I wouldn't say there are features that I want to add. It's probably already bloated in terms of features anyway. So maybe I'll just stop in terms of adding features. In terms of favorite features, I mean, Capacity planning, Nik,
you know, is another worthwhile endeavor besides incident management. And it's something that is sorely needed for super-based customers. There are functions within Flight Recorder to help you project your capacity needs. Like everything else, those functions will be strengthened and improved, but I really would like to exercise those. I'd rather this tool is used to forestall problems rather than to investigate ones. Let's just not have problems at all.
Nikolay: You touched several things. I wish we had a separate episode on each. And actually, I think how we would approach incident response, RCA, with this tool, particularly step-by-step, it's possible. And also capacity planning, I agree. And thing related to observer effect, 1 thing everyone who is using this new breed of tools ticking on pg_cron should remember that pg_cron records logs, right? And you need to clean them up. And I think we need to team up and bring some pull requests to
make this configurable and make it Unix way, Linux way, when everything is cool, don't say anything and don't log anything because it works, right? Like some levels, right? Like warning error level for each job in pg_cron. And another thing is this verbosity. pg_cron also depends on this frequency and it can produce a lot of bloated logs right in Postgres as well. Especially if we go to sub-second frequency, which is just implemented
for PgQue, just running a single stored procedure, ticking 10 times within 1 second. Yeah, I don't know if it's needed for pg_flight_recorder. Maybe not at this point. It's too much precision, right? Yeah, anyway. David: Anyway, yes. So there is a soft dependency on pg_cron. pg_cron is also maybe a little overly chatty. My kitchen wall clock just silently ticks away. It doesn't generate a daily journal of the fact that it ticked.
That's what I would like. This is what we have to live with. So yeah. Nikolay: Let's create pull requests. I can create or you can create and just support each other. Maybe pg_cron maintainers will agree that it's... Actually, I opened the issue. I didn't see feedback from them, but it's definitely an issue. It was an issue before we started creating these tools. I have another places where pg_cron was chatty in logs. Yeah, anyway. And final thing for me, a good place to start is benchmarking. If you just do benchmarks, just inject these tools, pg_flight_recorder,
and ask your AI to visualize the result of that data, like before destroying instance or something, destroying database, just dump that data and visualize it. It's so easy these days. David: It is. Yeah. The nature of these tools is changing and this makes it super easy. That's what I learned as well. But yeah, That's it. No, it's not very profound. It's not very complicated, but we need more and better automation in the community. This is just 1 small contribution. Many more to come, I think from you 2 and hopefully me and others
in the community. So looking forward to that. Michael: Nice 1, David. And just to check, what's the license here? David: It's as generous as I could make it. Michael: Nice. So, open and permissive. David: Open, yeah, exactly. Michael: Supabase style. David: Supabase style, it is. I work at Supabase, but this belongs to, if anybody, to the community. Michael: Wonderful. Lovely to meet you. You as well. David: Very kind. I appreciate it. Thank Nikolay: you.
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...