Building PostgreSQL for the Future with Heikki Linnakangas

0:00PostgreSQL is an open source database known for its robustness, extensibility, and compliance with SQL standards. Its ability to handle complex queries and maintain high data integrity has made it a top choice for both startups and large enterprises. Heike Linakangas is a leading developer for the PostgreSQL project, and he's a co-founder at Neon, which provides a serverless platform for spinning up PostgreSQL databases. In this episode, he joins Kevin Ball to talk about why PostgreSQL has become so popular, why he founded Neon, PostgreSQL Core vs. Extensions, the PG Vector Similarity Search for AI Applications, and much more.

0:42Kevin Ball, or KBall, is the Vice President of Engineering at Mento and an independent coach for engineers and engineering leaders. He co-founded and served as CTO for two companies, founded the San Diego JavaScript Meetup, and organizes the AI in Action discussion group through Latent Space. Check out the show notes to follow KBall on Twitter or LinkedIn, or visit his website, kball.llc.

1:17hey key welcome to the show thank you kevin i'm excited to get to talk to you so let's start a little bit with about you so can you introduce yourself and your background and what got you to the point where we're talking today so hi my name is heiklin nagangas i've been working on Postgres for the last 20 years for different companies. Currently, I'm a co-founder of Neon, which is a cloud provider for Postgres. Before that, I worked for Dreamplum, like an open source analytics platform built on Postgres and many other things. But currently, I'm working on Neon on serverless Postgres and cloud.

1:53Yeah, I have been very heavily on the Postgres train since probably the mid-2010s. I sort of grew up on MySQL, but then at some point, they seemed to really fall behind and Postgres kept going. And honestly, these days, I feel like there's all these new specialized VectorDBs and specialized DBs. And I'm like, why not just Postgres? It's a high bar. Yeah, I think that's a bit of a meme nowadays, like just use Postgres. It's kind of funny because I started around 2003, I want to say. And back then, it was not a given that people would use Postgres. Like MySQL was made more popular. And you kind of had to explain to people, first of all, what is Postgres?

2:35And then next you had to explain why would you use Postgres rather than MySQL or something else. Like proprietary databases were much bigger back then as well. But over the years, something changed. And you don't have to explain that anymore. It's become the default, which is funny. It feels strange to me because it used to not be that way. But nowadays, people just take it for granted that people are using Postgres. What do you think gave Postgres such both longevity and why has it become the default? That's a great question. I've thought about that myself. To be honest, I don't know, but I can speculate.

3:07I think Postgres has done really well. It has a reputation of being very stable and it has a good feature set, but it's been around for a long time and it's stable and predictable. And I think a lot of the credit for that goes to the packagers and the whole release management teams that we've been doing regular yearly releases, annual releases for a long, long time now. And it's like very predictable, the schedule and what's new, all the upgrade process, all of that. It keeps improving, but it's a very stable and predictable process. At the same time, proprietary databases, well, proprietary software in general has become less popular.

3:42People are moving to open source increasingly. and in the open source ecosystem, MySQL have had their own problems with the versions and there's been confusing in that world and then other databases never really became that popular for some reason. But then there's, of course, technical reasons like Postgres has a big ecosystem of all kinds of tools and a lot of things that kind of come with the fact that it has become popular and the default. Like a lot of people are, like there's a big ecosystem and if you have a problem with Postgres, you can Google that easily and you will find 10 blog posts explaining the same error message that you are seeing and how do you fix that?

4:16So it has reached kind of the point where there's a snowball effect going on. But because it is popular, it has advantages simply because it is popular. Yeah, that momentum effect is there. One of the things that stands out to me actually thinking about it is also extensibility. I feel like Postgres has been able to not just grow in the core, and it has grown in the core. When document stuff started to get big, we said, okay, we've got JSONB, like, let's go. but the custom types and i'm kind of curious like actually for folks who have never played around with that maybe we can talk a little bit about that extensibility like what does the extensibility story for postgres look like that has actually been like if you go all the way back to the university project 30 years ago when postgres was born that was at the core of postgres already back then that the idea of extensibility and especially with the data types postgres has a very flexible type system, you can create your own data type with its own functions.

5:13And not just functions, but also operators on how you integrate into the different index types. And you get to define your own definition of ordering for your data type. And what does it mean to sort it? Or what does it mean to, you know, you can create your hash functions. Many other databases have a few basic data types, strings and integers, for example, and everything is either a string or an integer with some sugar on top. But Postgres goes a lot deeper in that. Even all of the built-in data types are created using the same primitives of operators, operator families, operator classes, and how do other operators work together.

5:46And you get to define all of that yourself. Remember, I wrote for a presentation once a data type for indexing colors, for example, or to work with different colors. It was a toy, but it's really interesting to see that you can create a data type for colors, for example, and then you get to define your own operators. What does it mean for yellow to be greater than blue, for example, or whatever operators make sense. And then you get to build your own indexing system for that. You can build, similar to geographic indexes, you can build indexes on which colors are closer to each other. And you get to define the distance function and all of that.

6:19So yeah, the type system is really very flexible. A lot of the credit goes all the way back to the university at the times. That was the whole idea of Postgres, even before any of the other stuff was like while logging out all that was even created yet but then we've had these extensions post gis has been really important for that like that's been around for a very very long time and postgres has been kind of the default for geographic applications even longer than it has been for other data for like other applications because of post gis and the fact that post gis was built as an extension and it has a different license it's gpl licensed so it was never going to become part of the core because of the license issue, if nothing else.

6:58But that has kind of forced a nice dynamic between the communities too. It's like we're all friends and we talk to each other. So whenever there has been a need for a new kind of an indexing or some kind of support for indexing geographical types, we've kind of designed the core features for what does it mean to have defined these things. And then on the post GIS side, they've implemented the implementations of the data for the geographic types. But that does play really well. Like another very popular extension nowadays, recently, thanks to all the AI stuff, is PG vector. So that's become really popular in the last couple of years suddenly.

7:31But the fact that, why is it possible to create a PG vector extension? It all plugs into the same extension points that we have had for PostGIS and other data types. And it just plugged in really nicely there. That makes sense. And it is interesting how having that external body be such a big part, actually, of Postgres's early growth forced you into that. Yeah, I think that's really, really helpful. And the fact that it was a different license, there was never really any question of, should it be part of the core? It was not meant to be, but that has kind of driven the way we think about extensions.

8:05And in general, like it's a good thing to have extensions and we don't want to have all the different data types in core. Like they have a more happy life outside of the core as an extension. How do you think about what should live in core and what should not? That's a great question. People have different opinions. my opinion is that anything that if there's a reason it needs to be part of core then it can be part of core but for most new things it's better if you can have something else than extensions have a lot of advantages like you can have your own release schedule you get to decide your own releases you get to decide your own other external dependencies something like post for example depends on a bunch of other libraries that we would if we would rather not have a dependency on core postcards on those libraries anyway.

8:48But similarly, if you're building a new data type nowadays, you get to decide which other libraries you use, for example. You don't need to ask for permission for anyone, you just do it. And I think that's really powerful. That allows you to move much faster. Absolutely. There are reasons. People do suggest or want to include prior stuff in core, and we have to have discussions like, why should this be in core or why not? I think that dynamic is shifting a little bit. One of the reasons why people want to have extensions in core or move these things into core is that they want to have the same level of support and the same kind of they want it to be in the core part of postgres for whatever reasons but i'm as a developer i'm always pushing back on that because that basically sounds like someone is dumping a lot of work to me for me to to maintain all that stuff and i don't want that i want to have less things to maintain yeah for sure so i mean i think this balance is always tricky right and you've got as you say if you're outside of core you can move much faster you can explore things much quicker.

9:45You can use a lot more third-party dependencies. What types of innovations are still in progress for Core? Where do you think the Core of Postgres is developing? Well, there's no central roadmap. We know what's coming in version 18, which is released in autumn, in September, probably. There's going to be asynchronous IO. That's the big feature I've been keeping an eye on. I haven't done much work on it except some reviewing, but That's the Andres Freund and others from Microsoft have been leading that effort. But that's going to bring big improvements to the I.O. characteristics of Postgres. One thing that I've been working on a little bit is multitreading.

10:22I kind of raised the flag on that, like, let's get multitraded and that has hit social media and stuff. I haven't done much work on actually making that happen, but I think we are in a place where there's agreement that that's where we want to go. And that's kind of the first step in establishing that it's desirable. That's something we want to happen. And I'm hoping to spend a lot more time on that for the next release, actually. But yeah, there's no central roadmap for Postgres. So it always depends on what are the stuff that people submit patches for, what are the things that people and companies who are contributing decide to work on.

10:55Yeah, that makes sense. Well, and let's talk, you mentioned briefly PG vector, right? And I know that whole space of vector databases and storing embeddings and all of these different semantic search layers and things like that is very much the hot topic today. And I think you worked quite a bit on PG vector. Is that correct? Yeah, I have contributed a little bit to that. So what is in PG vector? What does it take to run it? And how does it stack up against, say, for example, a dedicated vector database? I mean, it uses the same algorithm as many other vector databases, HNSW and IVF flat. Like those are the two algorithms that PG vector implements.

11:33And that's pretty much the state of the art. Like there are some other algorithms with slightly different trade-offs. There are other implementations of the algorithms that can be faster or slower, but it's roughly the same algorithm that everyone is using. So I think PG vector is stacking up okay. It could be a little bit faster. It could use less memory. There's always improvements to be made, but it's doing okay. But the thing with all of these algorithms, in my experience, Coming from a traditional database world, I haven't done much with AI, but what I have done, when I started to look at PT vector, I started to look at it from the point of view of, I know indexes, I know how post GIS works, I know all the other index types.

12:09Let's look at this new thing. How different can it be? And it turns out that, yeah, it has some vector data is slightly different. Vectors are large, first of all, and building in these indexes is very slow compared to traditional B3 indexes or other indexes. That was a bit of a shocker, just how expensive and CPU intensive these workloads are. Another thing that is striking is that there are no good disk algorithms for vectors. So all of the algorithms pretty much depend on you have to fit your workload in memory, which puts kind of a cap on upper limit on how much data can you deal with. And the big differences between many of these algorithms and implementations is actually in how much you can compress it, like how much lossy compression can you do.

12:54Some of them, you can compress the vectors all the way down to one bit per dimension. And it works surprisingly well. And there's like all of the new research that is happening is happening on how do you just make the data smaller so that it becomes faster to process and you can fit more of that in a RAM. So that's very different from traditional data types that I'm used to dealing with. Yeah, that makes sense. And then are there any ways that PG vector is different from some other custom type? Or does it essentially end up looking to Postgres the same as PostGIS or something else? It looks exactly the same.

13:30So that's the interesting thing. There are, and this is something we haven't solved, but something that is interesting with the vector algorithms is that they're all approximate. Whenever you're doing a search on a vector index, it's always like the thing it does, like the thing that all of these algorithms do is approximate nearest neighbor search. And whenever you hear something approximate, that's kind of a red flag to a SQL developer. Like the databases are supposed to be very exact. And if you do select star from a table, you don't expect to get approximate results. You expect to get exactly the same data set, what you inserted.

14:04But that's not what these algorithms do. They are always approximate. They lose not that much information, but they do lose some information. And whenever you then do the search and you pick the top 10 results, for example, it's not deterministic which results you get. And that's fine for simple cases, but that actually throws off some of the rest of the system. Like if you're using pgVector together as part of a larger SQL query, for example, you would do a vector search and then you would filter that or whatnot. Then it gets complicated because suddenly the kind of the lossiness of the query propagates all the way to the other stuff.

14:38If you fetch the top 10 results and then you get the different results and then you filter them and then you sort them and you get different results than you would otherwise, for example, it controls the rest of the query and it can even cause errors when the ordering doesn't match what the planner expects, stuff like that. There are rare corner cases, but it can happen. So that's something that we still haven't figured out. How do you represent that kind of approximate query results from these operations at SQL level? I'm not aware of anyone who has a good solution to that. I would be all ears.

15:11And maybe this is something that needs to go all the way to the SQL standard or something, where you would kind of define the semantics of what does it mean to get approximate results. Yeah, that's fascinating. Because I think, yeah, in a lot of cases, you might have that as a separate data store. And so you don't have to worry about how is that combining then with the SQL place. It's up to the application developer. They decide. Yeah, exactly. You have to figure it out yourself. Yeah. Yeah. So that kind of leads to an interesting question. So you know, this is a type of bug that can occur. How do you deal with that inside of Postgres?

15:43Do you sort of look for it and raise an error? Do you just return incorrect results? Like, what does that look like? Yeah, it depends. It depends on how exactly it happens. It can lead to incorrect query results. Well, they're kind of all incorrect. Like, this is approximate search. Yeah, what does correct mean in this world? Right, what does correct mean? Like there's different levels of correctness and how do you measure that? None of that is well-defined. There are ways you can work around it. Like you can have in the SQL syntax, you can kind of work with it the same way you would with an external vector database and do one query for the approximate part and then do the rest of the query.

16:18Usually the results of that, you can put the barrier with the width clause or something to kind of force the planner to make the plan a certain way. So that's one workaround. But if you don't do that, then yeah, those errors can propagate. And it depends on what the rest of the plan looks like. If it's a merge join and you'd get the different ordering for the results, for example, then there's actually checks like sanity checks in Postgres where if the result said that was supposed to be ordered is not, you will get an error. But in other cases, you might just silently get the incorrect query results again for some definition of incorrect.

16:50Yeah, it is a fascinating world we're in right now. So looking at this and looking at sort of where Postgres is going and how it's being used actually brings me a little bit towards Neon. So can you actually share what was the motivation behind starting Neon? So the way we started, we looked at the architecture of Amazon Aurora, and we decided that we want to build something similar, but make it open source. That was kind of the starting point. Now, along the way, of course, we've come up with new ideas and other stuff, but that's still kind of the core of what we do. And the core is the separation of compute and storage.

17:26So the idea is that there is a separate storage system which keeps all of the history. Like that's the interesting part about Neon and that's different from Aurora. At least they don't expose it. But the way the Neon storage works is that it takes the write-ahead log from regular Postgres and it keeps all of the history and it kind of allows you to do point-in-time recovery and it replaces your regular backups and while archived that you would normally use with the Postgres setup. It kind of integrates and replaces all that with the Neon storage, which keeps all of the history. And that allows you to do stuff like point-in-time query or launch multiple reader replicas against the same storage without having to make multiple copies of your data.

18:05Things like that. And that allows us to scale the storage layer separately, like independently. And then there's the compute side. And the compute for us means basically Postgres. and Postgres connects to the specialized Neon storage instead of the local disk. So this idea of separating the compute and storage, that was at the heart of when we founded Neon and what we started to work on. And it's still at the heart of everything we do. All of the features we have, branching is a popular one that a lot of our paying customers are using Neon because of the branching functionality. Well, that's possible thanks to the storage because the storage allows us to do that branching.

18:41We are serverless. Again, that's the big reason why we can be serverless. It's that we can launch Postgres very quickly. And the reason we can do that is because we have separated the storage. So it all kind of ties back to thanks to the storage engine that makes all of the other stuff we do possible. Yeah, this concept of a serverless database is fascinating because as the world has gone towards, okay, serverless and stateless services and all these things, like the most heavy inertial piece that's hardest to do that for has always been where your data lives. because data is persistent. Data has to be persistent in order to be there.

19:17And if I understand correctly, you're saying, okay, great, but let's sort of cut the tightest barrier possible around that thing that has to be persistent and make everything else serverless. Exactly. So yeah, that's exactly what we do. So we made even Postgres serverless by pushing down the thing that needs to have the state, which is the storage, kind of push that further down the stack and separate that as well from the compute side of Postgres. Yeah, as you said, the data has inertia and it doesn't move easily. It's not serverless. It doesn't just, you can't just suddenly come up with data from thin air.

19:50You have to actually store it somewhere. So how fast can you spin up Postgres if your data is separated off in Neon? What is that response time on a serverless Postgres? So we measure it internally and it's about 700 milliseconds at the moment, I think. There's a little bit more that the user pursues because handshake to the client and so forth. That's a little bit, but we tried to keep it under one second. When we started, another part of how we make this work is that we run the Postgres instances in Kubernetes cluster, and we launch a VM, a separate virtual machine for every Postgres instance.

20:24When we started, the delay of that was about five seconds to launch a new Kubernetes pod and connect your connection to that. And we kept hearing from users like, yeah, Neon is awesome, but man, the cold start time, that's too long. Like, that's killing us. That's too long. So we kept hearing about that and we've started to work on it based on the feedback we got. And we have plans to bring it down further and further. The thing that made a big difference is pre-creating these VMs. So we have a pool of pre-warmed VMs available at all times. And we got it down to about one second and we stopped hearing these complaints.

20:58So that was kind of the tipping point where people stopped complaining. And that was really interesting to see. We still have plans to bring it down even further, but it's not really a problem anymore. Like that seems to be that people are happy with the roughly one second delay when you connect for the first time. Got it. So that's low enough that you can essentially spin this down if you don't have requests coming in, but it'll stay hot so long as you're coming at it. It's not quite the same thing in some ways as a like serverless function that really is lifetime is only the request. Well, a serverless function has the same thing.

21:31The function lives somewhere. There's data, like the code is somewhere. And there is a delay the first time you call a serverless function as well. And then it gets loaded and so forth. It's not as high. Typically, that's even lower. But we also have plans to bring it down even further. But the pain point seems to be somewhere between one and five seconds where people stopped complaining. That makes sense. So I'd love to dive into what you're doing in the storage layer. because I was researching this a little bit ahead of time and found it completely fascinating. So can we maybe just start with high-level architecture, which I think you laid out when you were starting Neon, or at least I saw it in the blog post.

22:10But what does this minimum viable encapsulation of your heavy inertial data look like? And how does it enable these things like point-in-time queries and all of that sort of thing? The kind of abstraction we have is that we store all of the writer head log of what Postgres produces. It's the same writer head log that people use normally with Postgres for point in time recovery, backups, while archive, replication, all of those features. So all of this is based on the same writer head log. And the basic idea is that the storage takes the writer head log stream from Postgres and processes it. And it transforms it into a different, like reshuffles the data into a different format.

22:53Based on the writer head log, it can reconstruct any version of a page so postgres always requests a pay like all of this works at the page level at the block level it works with the eight kilobyte blocks that postgres always uses but whenever postgres needs to read a page instead of reading it locally from disk it sends a request to the storage get page number one two three at this point in time and the kind of this time dimension is what makes all of these other features possible so the storage layer can reconstruct any version of any page. So if the page has been modified a hundred times, it actually keeps all of the hundred versions of the page available and it can return any version of those pages depending on what the request is.

23:38And you can kind of see where, you know, that's the thing that enables us to do things like having a read replica that's lagging a little bit behind the primary. It will just request all of the pages at the slightly delayed point in time or it replaces a point in time recovery because you can just launch a new compute node on your Postgres instance, and you can just tell it to, please show me all the data at this older point in time. And the storage layer can do that. Now, there's a lot of complexity and a lot of smart engineering that goes into the storage to make it possible to do that. Like actually keeping every version of every page is very expensive, obviously.

24:16So there's a lot of smarts in how it stores the data, and it doesn't literally keep all of the versions, but it keeps the writer head log and some images of the pages at specific points in time so that it can quickly reconstruct any page based on the writer head log and the images it has. This sounds to me like essentially an event sourced model, if I'm understanding correctly. Yeah, that's one way to think about it, yes. Where the events are the write-ahead log. It's saying, okay, this changed in this way, this changed in this way. and then it's projecting out here's the state of data at some point in time and you save those images essentially and so any point in time becomes the most recent image before it plus a set of deltas right yeah that's one way to think about it in an event sourcing system that's it's up to you to define what do you do with the events and how do you collapse the events into the final image of whatever whatever you have in this case it's the pages and the page images so postgres never needs to know about any of the stuff that's happening behind the scenes.