[00:00:00] Michael: Hello and welcome to Postgres fm, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard. This is my cohost Nikolay, founder of Postgres ai. Hey, what are we gonna be talking about today?

[00:00:11] Nikolay: Hi Michael. Let's talk about extensions.

[00:00:14] Michael: Awesome. Yeah, we've had a couple of requests for this, since we started nearly a year ago. so it is cool to be getting to it. I know we've covered a couple of extensions specifically. but yeah, this was my choice and I thought it was about time. We actually got to extensions are, how they can be useful, some things to watch out for that kind of thing.

[00:00:36] Nikolay: Yeah. First of all, we started, 49 weeks ago because this is episode number 49, and I, it amuses me that we didn't miss any, any single week and soon it'll be one year, like in one month. It'll be anniversary, right, of our podcast, which is great. And in Indeed, extensions is a big topic in pocus and, uh, it's one of the key aspects of bogs where we can say it's very, very extensible. Uh, extensibility of this system is very, Great. not only, you can create obviously tables and and so on and data types.

For example, you can create data types. you can create indexes for your data types and so on. You can create, languages, but you also, you can create extensions and actually Lang customer languages is, is a one of kind of extensions. Right? You one of the like idea of abstract extension. I have some special data type and I want.

This data tap to be, performing very well. So I create special type of index, probably based on Generalized search three, just or generalized inverted index gin. These are two, like abstract, systems to create new indexes.

In this case, your, my, data type is backed by some index. I can give it a name and package as extension. And this happens all the time. And recently, on the, wave of LLM exploding, popularity extension, PPG Vector started to grow very well. It was created before, T GT version four was released.

But of course, this fueled the growth of, of popularity of PPG Vector and, even RDS already supports it. Which, like, I don't remember that requests to add some extension were handled so quickly by rds. It means that like something interesting, like, and this extension also provides a special type of indexes for Canan search of, uh, you find, uh, key nearest neighbors for your vectors.

It helps you to work with, uh, embeds for J P T or other, other, LLMs and so on. So it, it's very helpful to build your own systems using large language models.

And this is just an example of, how it can happen.

[00:03:04] Michael: Yeah. Nice example. I didn't realize this, but I think PG Vector was, is, for the Ruby folks out there, something written by, or at least, maintained, mostly written by Andrew Cain, who does, who's really famous Yeah. In the Ruby world for the number of gems. So I don't think he's a listener, but, yeah, I'm sure a lot of our listeners are very aware of who that is.

Very cool.

[00:03:26] Nikolay: right. So with, and just an example, it's very popular example, but of course extension can do different things. Can can implement not only data type of indexing, uh, can implement something else. For example, it can be just a set of SQL functions which are useful for administration. It can be, of course a full fledged language.

And recently also, also, like I think rds also edited, uh, PL rust. Which is, uh, was very like, has some high demand

[00:03:57] Michael: yeah, I think that was a cool thing. I think maybe the team behind ZOBO DB are the ones leading the charge on PR rust. but I'm, I could be wrong on that, but yeah, another extension. so there's. Yeah, I've seen a couple of good definitions that I like.

the simplest was an extension is a package of code and sequel objects to extend Postgres functionality. And so by that definition it's extremely broad and Postgres was extensible before the concept of these, what, what, what we're talking about here specifically, was added in 2011. I've got down here Postgres 9.1.

The ability to, to package these up, in a. Fully supportive manner, but if you could extend Postgres long before then I, that was, in my opinion, that was one of, I think we covered this actually in our, uh, episode on why was Postgres so popular? But I think it's been a really key, reason that Postgres has been so successful over the years.

[00:04:51] Nikolay: I agree. Yeah. They, this, property, to be very extendable. It's very important. and, it's not like marketplace, but, to be able to allow people to add what they want to add. This is super important for an open source project because, and sometimes they're, they, we have competing implementation of the same, like to achieve the same goal.

And this is normal for open source because, it doesn't have a single direction. And, uh, like roadmap? Well, it's, by the way, it's, it's also a different topic. May maybe kind of roadmap should exist, but in general, if you take some company, usually they have some direction, they have goals, they have strategy where to move.

But open source, usually it's like, you know, cathedral do bazaar. And for open source, it's essential to maintain this bazaar, philosophy where many, many voices, are heard from everywhere, right? And people just experiment. And the most successful, interesting ideas, uh, resonate in other people, other minds, right?

And, uh, this is how some extension can grow. But of course, we have examples of. Cathedral style, extensions and we know them very well. One of them is cyto acquired by Microsoft. Some people call it super extension. These, these kinds of extension. And we have another, another one, timescale. it's just extension, right? This is great. You don't, not, not a fork, right? You can install it. Traditional list, you can install it to regular post.

This is super cool. And they, they have a lot of stuff happening. It's not just data that happen and index it, it's, it's a lot of stuff. They have their own notes and execution plan and so on, like so many things

[00:06:44] Michael: I think super extension's a really good word for it, and I think so. I think there are some that do so much that you can almost consider them a. shifting Postgres enough that it becomes a different kind of database. Like it. Some things that some people, let's say Postgres experts that have never worked with Citus or never worked with timescale before, might struggle to diagnose and help you with certain issues because so many things have changed, but they are implemented as extensions, which is really cool.

So I think there's a new one as. New-ish one, I guess it's been going a while now, or your DB that I, that I put in that category and I've seen some people put post g i s in that category as well, even though,

even though it's

[00:07:22] Nikolay: Hold, hold on, hold on. Audio cannot be put into this category yet. It's a fork. Uh, and it'll be difficult. I, I hope they will succeed, but it'll be difficult to transform to extension. Actually, as I remember, timescale was, was a fork as well. Originally. They, they, Or, or cys, like some, some people pass, pass this, uh, route.

Like first it's a fork, but then slowly moving to be able to like maintain fork is nightmare. Usually we can look at, uh, green plant for example. It's not, it's a lot of fork. You always legging behind community

[00:07:59] Michael: r d s. Yeah.

[00:08:00] Nikolay: Right, right, right. Well, I'm not sure how are

[00:08:04] Michael: They lag a bit though, right? They, it takes them a while. Aurora especially.

[00:08:08] Nikolay: Aurora is a different database. but, uh, then you become, extension, it's great because it's much easier to start working with new major version. Of course, some work is still required, but you have interfaces, even sometimes hooks and so on. But it's possible. And, audiology B has a lot of innovative stuff.

A lot, like, a lot of stuff changed. And as I understand their plan of probably should talk to them one day firsthand and understand, understand problems firsthand. But the, the ideas is like things should be moved to POGS core and in the future, uh, once these things are moved to the core, to Postgres core, they become extension.

This is the plan as I understand, but now it's not. So just to clarify, right.

[00:08:55] Michael: Yeah, I think, I think you're right. and that the things that they need to. Put in court would be things to make it more extensible for others as well. So it's, it's increasing the extensibility is, is their goal, which is, it's cool. And, and there have been previous projects around this before, right around plugable storage and things.

So, Postgres, there seem to be people in the ecosystem who want it to be even more extensible, even though it's the most extensible database I've ever seen People want it to be even more extensible, which is quite cool. But there are, as well as these super extensions, which I think are worth mentioning, most extensions are much smaller, are much more kind of like small utilities to allow you to do something small. and there's a, there's just. Hu so many hundreds of them. In fact, I saw kind of a throwaway joke tweet. I think he's, said it a few times and probably it's a bit of a, an in joke between people.

But I've seen, people using the, the Apple quote, you know, that there's an app for that. But, for Postgres, there's an extension for that. So if you, if you can think of something, uh, want Postgres to be able to do something, there's a, a chance that you'll be able to find an extension to do it now.

Might not be available on your cloud, uh, if you, if you're

[00:10:02] Nikolay: This is the key. This is the key. So we live, uh, in time when a lot of POGS users are not actually bogus users, but some managed service users. And some people say, for example, pogs, RRGs is not pocus, which like I thought many times in various places where people get communal support, they, some people come and they say, we have this problem and then.

Turns out they use rds, for example, or Cloud Secret or something. And then some community members in this chat or anywhere, like, can be Telegram, for example. They say, oh, it's not, it's not, pogs go to rds. I find it's not, it's not right. And I think, uh, RRGs, played huge role in other managed postals.

They still play a huge role, in growing. Pogs popularity, it's important, but at the same time, they have very strict list of extensions. And for example, uh, I remember there is such extension called, uh, there, there is June index, generalized inverted index. and, uh, it's powerful for large data sets.

It's important for full tech search, for, search of overlapping arrays and so on. we, there is a problem with Gene. want to find everything by sort, by according to timestamp or according to some id.

So we want the freshest items go first. It's, uh, unsolvable problem for gin, so it works not good and people will live with it. For example, three gram surge, also backed by gin, and you have this problem. So pointers should choose a sitter to use, uh, battery three, and then follow. filtering

[00:11:52] Michael: Mm-hmm.

[00:11:53] Nikolay: on the fly or to extract everything and order by in memory, top and ordering and so on.

There is also extensions called ram, and you cannot install it on RRGs Ram. Solve this problem extending June, with this. Timestamps or IDs or numbers, and then it's a single index scan you have ordered, uh, result and ordered by limit. N works well, but uh, on rds it's not not available. I requested that many, many years ago.

I still like Ram is a, it's questionable that, uh, that it can be used in each case because, uh, the size of index execution is in my cases. But still, I want to be able to test it on RRGs if, uh, if my customer is RRGs, customer also, right? But I cannot, and I ask them they didn't do it. And this limits, our, um, so.

In this, like when people say, go to RRGs, we won't help you in community. I think it's not fair because it perhaps like pogs, it helps to grow pogs popularity. We should like accept this fact, right? And we should help these people as well. It's interesting, like we have many animals, kinds of animals.

Some of them really look like pocus. Why not? like accepting them, right? But at the same time we cannot add extensions. we won't. Right? And the list is limited and we need to wait forever. Sometimes. Well, if it's PG vector, okay, everyone accepts it's very popular right now and they edit it. But if it's some exceptional like ram, it's still not there.

And sometimes if you go to Hico, they even don't support PPG pack. For example, I

have fresh case, fresh customer on Hico,

[00:13:37] Michael: yeah.

[00:13:38] Nikolay: and we like, they, they experience blowout issues. What to do. They say, oh, It's not good. Like I, I, I say and then I suddenly realize Heroku doesn't have P Park.

[00:13:51] Michael: maybe they added it yesterday and we haven't noticed yet. Remember the auto explain example from last, uh, from a few, weeks ago.

[00:13:59] Nikolay: maybe in the end of the day, RRGs and others are not true, true open source. S indeed. And, uh, it somehow, somehow, like something should be changed. So either they find a way to support all extensions, or we need to find a way, to have, all the benefits they propose. But somehow, so we have all extensions and uh, we have extensible, full extensible positives again.

[00:14:28] Michael: Let's come back to that cuz there is a, there is something new in that area isn't there? But quickly to defend the people that I, I do have some sympathy for the people that have animosity towards, RDS support because they, they have a commercial product and people come to support for support for it from a free community.

And for years, Amazon, famously didn't contribute that much back to postcard, aside from the marketing, which I think shouldn't be overlooked. but you know, development wise, there was much less, involvement than there is now. So I think some of it's the hangover from that. But coming back to the, yeah, so Heroku I think are a bad example because I don't think they've implemented even some extremely popular extensions.

But I think the Amazon list of extensions is a, is relatively like a relatively reasonable list of what the most popular Postgres extensions are. There are some exceptions. I think they support some things that I've never seen

anybody

[00:15:22] Nikolay: it's, it's it's actually a good point. I, I used it, in, in the cases when customers have self manage positive, I sometimes use this approach. I say, you know, we should add this extension. Even RDS has it.

[00:15:34] Michael: Yeah, it's like proof that it's somewhat popular

[00:15:36] Nikolay: Yeah. Yeah. So, so it's, it's very fire, you know? It's, it's good. It's, I, I agree. This is good. Like, this helps me to sell some extension to some customer. Right. To, I mean, to, to idea, to, to have it, to install it,

[00:15:47] Michael: while we're on that topic, I think, there is PG x n as well, the extension network. There are a couple of these, uh, not. Fully official, but pretty official.

[00:15:58] Nikolay: I think it's that. I mean, I mean it's, it's, it's, it looks like a live, but it's dead. This, this idea, it's, it didn't become like, the marketplace software or some like registry. I, I, when I see ppg, I usually under, like, for me, it means I will have some troubles installing it because, uh, I need to install this additional steps.

Like, I don't like it at all. Like, it, it does work because, In my opinion, the feather was because, it was down by community, like, and so on. And if, uh, website was, was polished, if it was very social oriented, like, ratings, comments and so on, probably it would have chance to be like some single.

Registry of extensions and I, but we don't have this, like we don't posts doesn't have good to register of extensions right now and it's like are many lists and PJ X N is just a ability to have some standard way to install it and standard way to like to count all of them to have doesn't work for i, I think it doesn't work.

There are new attempts. I remember some like Cordy B launched recent attempts

[00:17:03] Michael: Yeah, they called it PG Trunk or Trunk, um, and also super base, right? DB

[00:17:07] Nikolay: Super. Yeah, yeah, yeah. Interesting to observe what will happen. I have low expectations, but maybe something will work. Uh, it is good to see such attempt. I also had ideas to, to do this and realized like you need to be very good in market marketing here. And, uh, yeah, so, so for me it's the most important would be the reading comments and people opinions, uh, feedback, real experience.

But P Jackson doesn't provide this. For, for installing. It's a headache for me to, to use it for installation. I don't know. Like value is not there. It's like source force. It, it became dead eventually. Source Force also was good until it wasn't, right. So yeah, sorry for direct and, uh, maybe like for some people offensive opinion, but, uh, this is how I feel and I think, uh, I'm not alone here, but,

[00:18:02] Michael: Yeah, it def, It

definitely feels like, extension providers currently still linked to people generally link to, let's say, their GitHub to show like, and I, it would be, this is one of the advantages sometimes a cathedral would have over a bazaar, right?

Like they can tell you if it's a closed source system with extensions or an app store, you can point at the official one and everybody points at that, right? You can see your reviews there, you can see, um, all sorts. Yeah, exactly. but we don't have that. so yeah, it's, uh, it's interesting to see the efforts and a lot of effort has gone into them, but I,

[00:18:35] Nikolay: There is demand, there is some demand in such like a registry of huge list of all extensions. Maybe not only extensions, but also post related projects like idols and cell idols and everything. backup tools, a lot of stuff like of course it would be great if. Someone would maintain some, uh, registry with, comments, reviews, and so on.

Maybe some, someone would, uh, like I, I see some efforts from different areas. Maybe some of them will succeed, but let me know. Just like, just think about it. Extensions currently probably one of the key areas where managed POGO providers compete.

[00:19:17] Michael: Yeah.

[00:19:17] Nikolay: They They compete in like supporting more and more extensions.

So this is good, but still, I think, uh, it, it still feels like not open source because I want full freedom. I want to be able to install my extensions. I developed yesterday, right now, why not? And I cannot do that.

[00:19:38] Michael: Well, I think the reason security, right? Like I think that's the,

[00:19:42] Nikolay: I, I can name you five reasons understandable, right? So like, if, if security and uh, if we support something, we are responsible. Yes. Like for example, what if extension developer stop developing it and we find a bug who will apply it? This, this logic is clear. Absolutely. But open sources is not like that open source.

It's, uh, freedom. Right. We. We should be able to go where, wherever, We want, And, uh, I think it's problem unsolvable. If you close these doors like can try to maintain a lot, a lot, a lot of extensions be responsible for them, but, At at at different levels.

For example, stagger maybe has the huge biggest list of extensions supported more than a hundred. But what happens if some extension, like not maintained by original author who will fix bug? It's a question, but still staggers I think has the biggest list, probably like in, in terms of extension supported.

And it's interesting, but. can I install my own extension there? Probably, no. I need to go to them and say, add it to your distribution. But it's, it'll, it already feels better as like, more like open source than managed cloud providers. It's easier to add, so it's a difficult topic or who, who responsibility is a difficult topic.

[00:21:11] Michael: I, I think you're completely right. I think it, we probably have to mention here, the project Amazon, uh, announced last year, uh, I think it was last year, of, uh, t l e, which is itself an extension, which is quite confusing. Um, or at least it,

[00:21:25] Nikolay: It's only for languages.

[00:21:27] Michael: a while. But yeah, it's, it's for Tru, it's trusted language.

Extensions is what TLE stands for, I And I was, I was originally quite excited by this. I thought this was Amazon trying to really let you bring your own extension to,

[00:21:41] Nikolay: Everyone, everyone, uh, was in this trap. I also did like, I, I saw extension, so like there is some need here. I want to install anything, but they cannot solve it because they like, They cannot solve it because with this responsibility problem, and when they list this theory, many people, including both of us, started to think they solve it somehow.

But no, no, no, no, no. This is only for languages. It's very different, right?

It's not for extensions, It's for like, languages are extensions as well, but specialized extensions, so.

[00:22:15] Michael: yeah. I think they also, I might have misunderstood or misremembered this, but I think they also support some very basic level things that you would consider extensions. For example, you've just got a few small P L P G C, cool objects, for example. I think those could be packaged up like this, but they're, again, they're very.

They'd be very simple and very small, and I think it's more about cloud, it's more of a cloud provider thing to let people bring some of their own code and maybe let a DBA allow their team to install or not install certain packages that they've dev developed in-house, um, on their database or not. So it's, I think it's very, it's a very small subset of what current extensions can do, is.

Uh, this allows, but maybe that's like, maybe that's just step one and they've got step two to 10 planned already, and this is like, maybe it could be a cross cloud provider collaboration effort on how they can let people bring extensions to their clouds. But yeah, I'm, I understand it is difficult.

[00:23:17] Nikolay: Yeah. Yeah. Well, for, for me, if, if I, again, like, let's, like, uh, I do some bottom line, what I have in my head and we will probably discuss something else, maybe more, um, useful for users because we, I, I, I think, I think we discussed some like philosophical questions, which are very important, but maybe not that helpful for users.

Actually, they're helpful because they need to make decision. Go to rgs, go to Timescale Cloud, go to something else, go to. install, uh, from Kubernetes separator or maintain everything themselves. So, currently we have not good situation. extensions are like, very limited. Okay? List is drawing, but limited and full power, full power.

All of open sources, full freedom. And, managed posts cannot provide it. And this is not right if, I mean, if I want, I, I pay you right? And this is the resources you provide and you provide, and we've had some additional automation, but I paid you a lot. And I, if I want some extension, I would like to have it even if, like, uh, accepting some responsibility on my shoulder.

So I, I say, okay, I understand that something can be broken if I install this extension. But this rat should belong to me. I'm like paying, this is open source. Something is to totally wrong, uh, in industry right now, here. Totally wrong. Like open source money. I pay you. And they, you tell me I cannot use the power of open source.

I cannot sole extensions. I think something should be changed in LA next, like five years or so, because right now it feels for me like Oracle,

[00:24:52] Michael: I

I

think, I think you're onto something, but I would, I might even use the example that we're currently living in a world where most cloud providers act like Apple. And they have an app store and they have, they very much have to only allow a, a small number of apps onto it. And maybe what we're missing is a cloud provider that acts a little bit more like Google or Android, that allows a lot more, um,

[00:25:14] Nikolay: Perfect example. Exactly, which is based also on, on Java. A lot of things are open sourced. It's also Linux, right? I mean, uh, their operational system. And it can be installed on various devices, not only one single vendor. This is great example by the way. So, yeah, I think, uh, something like that is missing in our postal ecosystem.

And uh, we observed those dozens of. Companies who compete in the area of managed s and timescale who switched to the idea cloud only, cloud first, not only cloud first, but something else can be done here and something else should appear soon. I, I, I think, and this, thing which will appear not, will not limit us in the area of extensibility of extension.

Like I, I should be able to install extensions extension I developed yesterday, deploy to my production. Why not?

[00:26:06] Michael: Quick fire. I think we've only, we've not got that one that long left. Um, so I think we should do so. I think you've made a really good point that we haven't done that many practical things. I've got a couple of tips, for people. Um, I imagine you'll be able to add a few more onto this, but one thing to be aware of is major version support.

If you're doing major version upgrades, please consider your extensions and, and check compatibility. That's,

[00:26:29] Nikolay: Everyone forgets about it.

[00:26:31] Michael: Yeah.

everyone forgets, but it's a major issue. And then the, the second thing is before you check, before you're using an extension, do check how it's maintained. I think I quite often see, people having to ping maintainers to add major version, upgrade sup, major version support.

So some are maintained. impeccably and some, it's a little bit slower. So just make sure that they're in line with what you, what you're gonna need and expect, or that you could have the resources in house to, to help them out if you really need to allow on it.

[00:27:00] Nikolay: But each extension is a small, uh, separate product. Sometimes not as, not small if it's a, a cyto sore timescale. But, uh, anyway, it's separate product and it might introduce problems. They have usually smaller release cycles than pocus if they're developed actively. But, uh, if you just install, not testing.

Not without thorough testing. You might have issues. I had issues like some extension was updated and whole Postgres started to have segmentation faults. And we need to like, we need to troubleshoot and there then to find that it's sort like a better upgrade, minor upgrade of some extension cost issues.

So it's did, to uh, an area where we need to invest some efforts to maintain, but we need, we, we should't upgrade, but we should test it as well. Right. And, uh, yeah.

And for this. I would pay. I mean, my ideal thing through PO Pop Source POCUS installed anywhere and supporting any extensions, some of them are maintained by this provider and they pay for it, uh, because they tested, very well, all upgrades and they automate those upgrades and so on.

And none of managed post providers will currently have duties this yet. So this opportunity, I guess,

[00:28:21] Michael: Sounds

[00:28:22] Nikolay: Yeah. And finally, what, what's the most popular extension we have? Everyone

knows it, right?

[00:28:29] Michael: PG stat statements. Do you count

[00:28:31] Nikolay: What? What does it mean?

it means it should be not an extension. I've just returned from, from Ottawa where I had tutorial and you can find on Twitter like a lot of likes.

Uh, slide deck. Unfortunately it was not recorded. Probably we should repeat it. And, uh, I was telling like basic things, tps, qps, and, uh, latency for queries only TPSs, can be provided by sgs itself for Q P s and for latency statements. It should be

in the core.

[00:29:02] Michael: I like that maybe every,

five years the most popular extension. there's some effort to put it into court. That would be amazing.

[00:29:09] Nikolay: Yeah. If something is big, it should be merged. Right.

So

[00:29:13] Michael: that. Well, I think that's probably all we've got time for, so we can, I'm sure we're going to some of those topics in more detail in that further episodes.

But, any parting, things to leave people with?

[00:29:23] Nikolay: Well, also wanted to mention there are some, uh, Third party registry of extensions. Like, uh, there is also Postgres, uh, on, on GitHub. It's not only about extensions, but it's a good list of, manual, curated list of extensions and not all extensions. And it's good to check. Yeah, yeah.

Yeah. It's kind of, yeah, what people consider interesting and, , There are so many extensions out there. Actually, there is also a saying that, uh, for each problem there is an extension. It's already solved. For example, you, you, you showed me today, like, uh, someone, uh, said maybe it's a good idea to have, uh, a timestamp, , with timezone stored.

And, uh, also answer was there's already extension for that.

[00:30:10] Michael: Yeah, not just any old person. That was Robert HARs and Robert Treat, so

[00:30:13] Nikolay: right, right, right, right, right, right, right. Yeah.

[00:30:17] Michael: Well, thanks so much, Nicola. Thanks everybody for listening and catch you next week.

[00:30:21] Nikolay: Thank you. Bye.