What's going on? Everybody. Welcome to another episode of Adventures and dev Ops. I'm your host Will Button and joining me today. This is going to be exciting. I have the founder of pg Analyze and a member of the founding team at product ND. I've got Lucas Fiddle. Welcome, Lucas, thank you, happy to be here right on. I'm excited to have you. And this follows on nicely to an episode we had two episodes ago talking about the role of DBAs in our environment, you know, with our with
the movement that's happened over the last decade. You know, whenever I started, DBA was like the premier role to have in technology. You know, being a database administry was like the ultimate role because you could just you could just say no and shut everyone down, and you know, the company was almost powerless to do anything about it. Not that that was your goal,
but like that was the prestige that the DBA had. And these days we just don't see as many DBAs around, but those tasks that the DBAs do still exists, and so I'm interested to hear your perspective on that. But before we jump into that, give us a little bit about your background. How you got to this point. Sure, yeah, and you know, I'll try to relate this to also's Postcrest, which is the database I care a lot about. So over the years, you know, I eventually started
out in those six dozen and seven putting servers in racks. But back when
we still had racks. You know, still do or have racks. It's just you don't think about them anymore geological concept more than the physical figure think about And so you know pretty much you know, early like right after that, you know, kind of the first job I had, I you know, kind of started a startup with some friends of mine and Postcress was a database of choice, right, and so you know back then, you know, we kind of just had everything stort in our postgress and as you know,
my personal career went on, I kept, you know, seeing Postcress over the years, right, and postcus is an old database, like it's not as old as I am, but it's close to it. Right, So the you know, like they turned I think twenty seven years last year post was it not life? So uh, the really the story here, right is that I think what I've seen over the years in my career, right, is like so like product hunt for example, use posts my database
right like product the community. The database was you know, reasonable, not the biggest database, but it just worked right like you just had the system of record and that was Postgress. And then after that I joined a company called side of Data. Inside of Data is essentially a way to scale out Postgress, and that ended up being a quiet Microsoft. So you know, I had some fun in the corporate world for a little bit. And then you know, really what this led me to is where wearing math currently,
which is a company called pg Analyze. And so pg Analyze is a small startup, bootstrapped very intentionally, so no VC funding. And really the idea is that we want to make post press work better. Right posts performance in particular work better for the application engineers, the BBAs the data platform engineers help you optimize slow careers better. I've actually run this as a side project before, you know, kind of I went full time. So I actually started
technically like almost eleven years ago. Wow, but you know, only I would say the last four or five years has been really a serious project. Right before then, it was more of a this was, you know, solving a problem. Some people are paying for it, but really, you
know, as a business. It's really been you know, the last couple of years where it has taken off and we've really seen great success right on, So you kind of it's it's one of those sounds like it's one of those stories where you had a specific problem personally and you built this tool to solve it and then realized, hey, other people kind of have the same problem, that's right. Yeah, And I actually so I had a friend who he was involved early on with the project but is no longer. But
I had a friend who was essentially my go to book posters person. Right, So if you asked me ten years ago, I probably wouldn't have been that smart about poscasts asiety these days. Definitely not the smartest. There's definitely smarter people out there. But I learned or two. But you know, back then, essentially he was a person I would ask, you know, how to optimize this query? How do I know set this thing up so
it works well? How do I direct the replication? And so really, in part of scratching my own interest inially was this is a person that's great, but as there's some tasks that just don't seem necessary for a person to do manually, right, for sure. Yeah, and I think that's one of the things I've encountered as well as like I have in my own network, like, oh, this dude is my go to guy for that.
But you know, it's a personal relationship, you know, and I'm always concerned with, you know, making sure that that relationship goes two way and that I'm just not beating these people up with questions, you know. So yeah, I see the need there to build that. So what what were some of the like big things that you were trying to accomplish with postgress or doing cogrit postgress or how did you even know that these were things that you
needed to be worried about? I mean, the simplest thing is just a slow query, right, which I would say basically everybody who runs a postcres data base we went to slow query at some point. And this is not
you know, just postgress, right, it's just any database. Really, Queries are a thing, right, Like that's your basic unit of work essentially, And so I think the most fundamental thing is the database doesn't behave as I expected to write, Like, it's not as fast as I expected to it's returning less data than I expected to, like, whatever it's happening. And so really I think that was the most fundamental thing is understanding which careers
are slow. Right. Sometimes you have visibility problem because sometimes on the application side, you're actually calling a function like in your orem, you're not really thinking of the database as queries, right, You're not actually running SEQL. Sometimes it's just a visibility problem. But then once you once you know which query is slow, there's a question of you know, why is it slow?
Right? How do I how do I go from knowing it slow that it's kind of this matching box of activity to you know, actually the database is doing just index scan, it's doing a sequential scan, it's doing this joint and maybe there's a better way to join things, right, So there's there's different choices of how the database implements what you tell it to do.
And so this is I think really the most fundamental thing that I've seen myself need to know about databases is you know why things are slow and can I do something to improve them? And then you know, the more you have an expert at hand, Really, what the expert I think can do is help you understand which action to take right, and the action might be something like rewrite the query kind of get database to a different querer plan. There's
like different ways to influence what's going on. But understanding is you have a slow queer problem in the first place. I think that's really the most fundamental thing to start with for sure. Yeah, and I'm glad you brought up o rms because they are, like I think anymore, that's like the default and interaction for a lot of engineers with the databases just through the o RM, and you don't see the queries that they create a lot of times.
And I've seen quite a few instances where when the database performance slows, the first response is always to increase the size of the database server. And then and then you get that that bill, you know, you get the AWS or the GCP bill, And that's whenever the finance team comes down and says,
hey, maybe we should take a different approach to this. And so that's where some you know, that's whenever you've got to break open the hood on the o RM and really start understanding what queries it's running and is there are a way to optimize those? That's right, Yeah, And since you talk about costs, right, I think like these days everybody's like optimizing for costs, right because like things are expensive, and companies are you know,
laying people off and all the bad stuff. And database so interesting right because they're quite expensive, but they're also hard to change, right, And so I think that's that's you know something else I see. Often it's really like as you mentioned, right, like this like people reach kind of a performance bottlem like but then they don't know how to solve it, and so they
just upscale database. It's worse is usually in the cloud, your databases go up and you know slizes of two essentially multiple power of two type things, right, So like I have sixteen gigabytes to RAM, next you got to have three two gigabytes TOD then sixty four, right, and so you just
keep essentially like doubling your costs, which is horrible, right. And it's really hard to not do that because you can't just like have multiple database service like that requires architecture, like you actually have to think about you know, can I use read replicas and like if you like split up the read workload
and so stuff like that becomes a challenging problem. Right. So this is where if you can figure out a very performance issue without scaling up the data reserve, right, because adding A to the index, for example, then that you know, connectually safety a lot of money, which is why people care about it. Oh for sure. Yeah. And I'm not a DBA by any stretch of the imagination. But the power of indexes, whenever I discovered that early on in my career, was just mind blowing at at how
much of a difference that would make in the database performance. Yeah for sure. Yeah, and it's I mean, the good news is, I think people have gotten better over the years of understanding the basics of indexes. I think where it's hard, like what's hard to understand if indexes sometimes is that there's multiple different index types. Right, So Postcress, like the default is a bea tree, which most people roughly know exceptually what a B tree is.
But Postcress has all these different index types like just gin hash, Britain, all these like specialized things if you're talking AI and m L, there's you know kind of future vector has like IBF flat and hsu inex types and so really the challenge there is understanding when you use these specialized types, right, But like that's that's essentially what's even verse than the regular indexing problem. It's just you know, oh, I forgot to add it, and next
which index to that? Great? Yeah, just information overload that. Yeah. So everyone's talking about AI these days, and I think one of the one of the stories that we're trying to sell really hard is that AI is not the tool to replace your job. AI is a tool that helps you be more proficient at your job because as an engineer today, like you've got to be versed in so many different things from infrastructure to databases, to the
actual application code that you're writing, to the business knowledge. So what kind of what kind of assistance can we get from AI and helping understand things like that like which where we need an index and what type of index? Yeah? And I think it's it's a complex topic, right, So I think the answer is I think there are things we should be we should be thinking about. Is it important that a human is doing activity or can we be
machine assisted at the very least? Right, I don't necessarily know if we want to be machine driven, right, I don't know if we want to have the AI kind of take control of our database tuning, for example. I think you know very much the same way that if you think of the depth hoops world, right, do we just want to have you know, AI orchestrate our servers automatically and just provision things? Probably not? Right, Like things like terror form and stuff are a good thing, Like you want
to have that level control. And so I think with I mean there's a couple of things going on that field, right, I think at the like just looking backward the last couple of years, looking outside of what we ourselves is done, and they we just start with what other people have done. So just in terms of research, there used to be a project called auto
Tune. There's their company now they essentially do hazing details personally, but they essentially do like basin statists six based optimization, right, so they do not use generative AI, right, Like it's not like you're asking Chatchy D how to optimstic database. What they're essentially doing is saying, for this particular parameter value, can we run you know, a model that essentially comes up with the best possible parameter? Right, And it's pretty beff feel like it's it's
a cool idea's a cool system. There's other like there's another startup like that, Dbtune, which also tries to do with something very similar more recent some similar idea, right, which is how can we get the best parameter values? Essentially, so I think that's great. You should take a look at
these if you're interested in tuning parameters. What we have done is pach Analyze is we've optimized focused on essentially the things that you know, they're a little bit more fuzzy in terms of who owns them, right, so in you just want index earlier, and so what we've actually come up with a system called pach Analys's Index Advisor, which is essentially a recommendation system for which indexes to create, right, and so different than parameters, right, the parameter
is sometimes a choice, like you kind of want to find tune it, but you don't necessarily need to do that all the time, or your application engineers don't need to know how you tune your share buffer parameter in post cress Like that stuff isn't that important essentially to the application engineer. But indexes are interesting because they they have like this fuzzy ownership, right, Like is it the application engineer owning them? Is it the kind of DBA or the platform
engineer owning them? And so what recentially built this system that you know, imagine it's kind of like your safety harness, right, Like, it doesn't necessarily say you you've got to drive your decisions, but if you forget to add an index, it will tell you. And so it's not you know what I call it AI. I don't know, right, AI is a
complex term. It's not chene AI, right, Like, it's not chanitif I the same way as auto tune or DBTN or not chanitif I. But it is a way to essentially have a recommendation, uh, kind of of which things to create based on your career, work club, right, and so similar spirit, what we've also done is what we call vacuum Advisor.
And so vacuum and postcress is very particular concept. You may not be familiar with it, but essentially it's it's essentially that the dead row cleanup and postcress rights like when you have like you've been update or delete postgress you know, will create essentially a record that kind of just marks the deleted data essentially, and then vacuum comes and cleans that up. And so sometimes you need to find tune the schedule of that, right, you need to understand how often
is it running? Is running too often or not often enough? Is it? You know, kind of like running it wrong time a day, right, like I have my business hours and suddenly the database is busy a vacuum. And so we've essentially done something where we looked at all the time serious data that we have in our system and we said, you know, can we make your recommendations for which confex stennings to change. This kind of goes
a little bit into that parameter tuning area. What we then do is we do is on a per table basis, and so this is then again where if you as a human did that, it just becomes very complicated, right because you've got to think of like imagine our own database. For example, we have a thousand tables. If you have a thousand tables, looking at each and every one of them and then looking at you know, a graph over time that describes to you how you know the auto vacuum works. It's
just very tedious. And so being able to have that automatically kind of looked at an analyzed is really what I've found quite useful over the years. And what we done, oh for sure, that's to me, that's like pure gold right there, because you know, like I know that you have to vacuum postpress databases, and when I first encountered that, I was like, what the hell I have to vacuum this thing? Like can I hire a housekeeper? Or how does this work? And then you want to do it?
Yeah? Yeah, and then so then yeah, that was just led to more questions for me, like, well, should I trust this built in housekeeper? When's it going to do it? How do I know if
it's doing it at the right time? And so to have a tool that looks at what's actually going on in my data database and then makes recommendations based on that activity, I think it is just like an amazing resource to have because it answers so many questions for me that that I didn't even know we're supposed to be questions, right exactly, Yeah, And I think especially if you, if you like are not a pubicles expert, right, like,
this is what I've seen order hears is like people migrate from Oracle to postcress or from civile server to postcress, and these databases they do this differently, right, so they don't have vacuum as a concept, And so people when they first go to postcress and then they have this bike production system. Suddenly these vacuum problems start happening, and they're like, what's going on? Like what should I do? Right, Like I don't have anybody in house who
knows about this. Yeah, yeah, and you can always post on stack overflow and hope for the best. There there are some good people on stack overflow. Let me talk like, it's really you know, I've I've learned
this thing or two or ten from a stack overflow posts for sure. Yeah, it's it's funny because like there's the learning curve of stack overflow, like it's it's this tremendous resource, but you have to learn how to interact with you know, how to here's the minimum criteria I have to have to open the question, you know, and here's what I've got to include if I'm going to get a legitimate value response, right, And I think what's interesting
on the topic of psago flow that that actually makes me think in terms of AI, that makes me think of gen AI, right, Like the whole you know, kind of give me a response that resempless stechgorical answer is I think what generator A I and LMS are good at and so somebody in the postgars community and Nicolai from postgrass Ai. He's a recent project where he's essentially connected to cheat GPT with kind of a knowledge base of different postgress articles and
it's actually a really interesting experiment. But he's essentially saying, what if we had a chatbot, you know, that was power by chat, ChiPT or GPT for I think, and then you would essentially ask a question just like, you know, how do I set this parameter in postcars and then will just spit out something that essentially looks like a Stachofal answer, which I think like that has has this place as well, right because people, I think
what people do alternatively to asking a chat about that, they'll go on Google and they'll search how to tune this parameter. And then, unfortunately, with you know, the advance of AI, what has happened is a lot of more articles are just not that useful because it's beau, I'm pretty cheap to generate, you know, kind of that constant sense. And so what he's done is really, you know, kind of I think, built a better
solution to you know, how do I get good postters knowledge? Is by essentially building off with you before and having kind of this this more trusted I think data like database of good articles, which I found interesting. Yeah. I don't think it's going to be truly successful though until chat GPT actually sends out insulting answers at random just to insult your intelligence, until you to come
back when you actually know how to ask an intelligent question. Yeah. Yeah, And it has some weird issues like earlier this week, I just I'm not an active chutch up to user personally, but you know that there was like this issue where it just like started screwing RD and garbage. It's certainly you know sometimes I mean it's it's interesting in a sense, right, Like it feels like I'm reading a science fiction novel and I not to get sure
where it's going. Right. Cool, So let's go back to to someone who's who's working with postgress, you know, has multiple other roles, and we talked about, you know, the need for indexes and query optimization. But that's like pretty pretty specific, like you're you're narrowing in on the actual problem there. What are the indicators before that that tell them that this is
the area what you want to focus on, Like you're running postgress. What kind of things do you see that tell you, hey, this is a something that postgress might help with, versus this is something that looks like it's application code or infrastructure related. Yeah. No, it's a good question. I think it's it's actually not an easy problem to solve, right, because
you you're kind of looking at two different worlds. Right. So I think that what people most commonly would have is they would have like an APM tool of sorts or tracing toolrights that might be using you relic data dog like long list of apmtals, right, Yeah. And so what you were seeing these tools is you would see a trace for example, right, these as you would call a trace, and you would say, here's a slow request, and you would see different spans essentially in the trace that would say you know
which part of request is slow? And so usually with most of these instrumentations, you from the application side, you will know that the query is the slow part. Right, So you would I would expect, let's say, with a request that takes ten seconds, we would see that you know, out of these ten seconds, nine seconds are spent in the database. Great, right, So this is this is actually just something that you will get
most of the time. Not a hard part is understanding is this something I could do something about, right, because it's essentially will say, here's a sequel query and here is you know, this nine seconds span, but it doesn't tell you anything more about it. And so what I've found quite interesting we've uh, we've kind of launched this feature also last year, is we essentially use the fact that open telemetry allows you to connect different essentially different services.
Right. So the idea in a microservice architecture is you can have different systems sent into the same trace essentially. And so imagine that if your database could tell you, hey, you know these nine seconds of time, Actually I spent five seconds scanning this index, four seconds joining this data, right, Suddenly your trace becomes much more usable, right because you're not looking at
this you know, nine second time span. You're actually looking at individual operations that you maybe understand better, right because you're doing a big quer on this table. And so what we've actually done essentially is piggyback on this idea of different services sending into the you know, same system, the same observability system.
And so we've built an integration where we pull the kind of a slow query log or auto explained log in postgrass, which essentially says, here is you know a slow queer execution, and here's the plan for that execution, and so we pulled that as part of our kind of agent, and then we send the essentially a reference of that information into the tracing system. Right. And so what the solves is what you would do otherwise. Right.
So let's imagine if you don't have this type of solution, is you look at your trace and you have that nine second span, and then you have to correlate that with the database activity. Right, So you would essentially either look at your database logs or like in postcristers, tools like ggstat statements which
tracks the query performance over time. And so what you would do is you essentially say, well, I know roughly the query shape, right, formatting sometimes differs slightly, but maybe you know, I'll like copy and paste a part of the query. I'll go over to the database and I'll kind of put it in and I'll try to find the thing that's matching that looks like
the right query. But it's really hard to do that precisely. And so this is I think where open telemetry really has an interesting way of solving this, right, because just expand that briefly, what essentially it does is in tracing, you have these IDs right as you're trying to propagate across systems. And so the idea is when you run a CQL career, you add a comment and in a comment that says what's the trace ID and the parent span
ide of that query is. And so when the query arrives on the database side, the database can output a log for example, that says, hey, you know, this career was slow. Here's the career plant. And also, by the way, here's the little comment at the start that tells you that tells the tracing system essentially how to kind of stitch that back together into one unified view of the world, right, Yeah, And that's the Open Telemetry has just made some huge strides in tying all of that together.
We had Andy Grabner from Dinah Trace on the show yesterday and we ended up talking about open telemetry there as well, and just the like the whole, you know, the whole community effort from all of these different players to agree like, hey, we can all use this standard and create this system that allows you to tie those kinds of things together. It's pretty cool and something that I don't think we we could have seen in our industry up until the
last few years. Yeah, for sure. And I think open Elementary is interesting because it has that like cross company collaboration. Right, So like Dina Trace is involved in a standard, it's Microsoft that are like big players involved.
Like it just seems like I'm like, I'm positively surprised essentially that they were all able to come to the table, and like some of them are vendors, some of them are you know, big customers of observability data, and even if some of them are selling their own solutions, right, they are essentially still at the table discussing, hey, how can make the standard
work? Yeah, which is an end user is actually great, right because you kind of go out of this vendor specific instrumentation in your code and you're moving more towards the standardized instrumentation and you can choose different vendors. So I think that's that's how things should be. Yeah. Yeah, And then the skeptical side of me sees that and it's like, hmm, okay, what's
the catch. I mean, I think the catches it's it's sometimes sometimes the story is a little bit oversold, right, So I've spent a lot of time and some of you know, some of the maybe more niche use cases, right, and the whole thing we just like I've just talked about in terms of commenting and such like. For example, there's a project called SQL Commentary which is supposed to add these query tags. In the beginning, that's
the project at Google actually donated to the Open Climate project. And it's great they've donated it, but also it's kind of stall since then, so it's not like they've actually pushed forward and really said, hey, you know, how do we make this, you know, a proper standard, how do
we document this as part of the official project? And so sometimes I think the risk essentially of these stanardization projects is that sometimes you know, there is like this you know, Alpha spec to do something, or like this thing that was contributed by one of the players, but there's not enough momentum around actually developing the standard further. And so I think, for example, if
I was doing tracing right, definitely do the open plamage tracing stuff. But if you're doing logs even right, like logs are like they're pretty stable in open plemagry understanding but they're still less, you know, kind of commonly, kind of fully supported across all different languages and whatnot. Right, Yeah, so you've got a ton of experience with Postgress. There are there's quite a few database choices available. I mean really in my mind, there's like there's
Postgrass, my sequel, and Mango. I consider those to be like the big players. And there's different variants of that, you know, and you know when you get off into the no SQL stuff, you know, there's different use cases there. But I really see it as being like those three as being the most prevalent. What are the things? And I've used Postgress a ton because it just seems to fit any use case you throw at it. But what are you what's your your take on why Postgress versus some of
the other database offerings. Yeah, and I would add, like, just just to add the fourth to the list, I would say these days out of a ClickHouse to the list as well. In terms of source databases, it's not relational like like or it's a column store essentially. But I've definitely seen a lot of companies use posts and ClickHouse for different workload, different parts
of workloads. But yeah, I would say you know the it's so so I think that like there's there's different reasons why you use postosts, why use from ones. One of the things that I usually talk about open clemmmetry and different companies coming together, right, and it's from a community perspective. The one thing like what keeps me the postcrist community, right, which is not exactly answering your question, but I think it is an interesting aspect of this
is postcurs is not a project by Bond company, right. Post Chris is a community project. Like it has you know, people from ab guests working in it, people from Microsoft working from ed B working it, from Google working in it, people from small companies plant working it. And it is a true community project, kind of like Linux currently is. And I think this this is what fascinates me about it and what makes me, you know,
just the longevity of it is like so much. Essentially I trust it to be to have that longevity even in you know, ten years, twenty fifty years from now, just because it's it's it's been able to kind of advance over the years, but it's also been able to do that as a community project, not as a commercial player trying to you know, kind of get both benefits essentially, right, because if you look at mangoay to be, like mamay B has you know, their ATHLETs service, And I don't
know how it differs between you know what mama to be open source gets you versus you know, the Atlas base Mango to be. But certainly, you know, I would imagine they have that conflict, right They're constantly thinking what should I put there? What should it? But there are people just going to deploy their own, right, and so just that that is really I think you know why postcrists is a great building block, right, because it
doesn't have that that fundamental conflict that these companies usually have. I think when you're trying to make a decision which one to use, I think if you're using my sequel, there's you know, there's some reasons to migrate to postgress, but they're they're usually not that strong, right, So I think when I see people migrating between the databases, it's really more to old school like Oracle or SQL server to post press, right, And for example there postcrists
is a very good target for these migrations because it has like a lot of similarities in some sense and career language and such before Orecle for example, and stuff like that. There's easy it's easier to migrate essentially to post Christ into my sequel. Also, if you're using Oracle, why would you go to my sqel because that's owned by Oracle. Again, kind of doesn't really make
sense. And I think Mango to be I don't, you know, I don't have much experience personally, but I think it's like really it's it's it's the kind of thing where you make a choice. Sometimes companies, you know, run multiples of those, like oftentimes I see companies just as in Postcriss, I think you can scale out all of these right like Postcress as Citas. My sequel has the tests and planet scale. I'm I'm going to be
kind of as it's built in way of scaling out. So it's it's you don't you don't really run into a bottom like these days anymore with not being able to scale beyond a certain data poet. Yeah, for sure. Yeah.
My introduction to Mango deb was gosh, it's been I think it's been over ten years ago now, but where we used it was with mobile applications, uh, and user attribution because you have this application that ties into all these different services, you know, like ties into Facebook and ties into Amazon and different things like that, and you're trying to attribute your marketing campaigns to
those platforms, but with each one and you get different data. And so Mango for us turned out to be a really good way to just say, Okay, here's the attribution data we got for this user, and we're just gonna put it in this Mango field. And then in some cases we know that there's one key value pair that's consistent across all of those platforms, and then we're gonna keep the rest of the stuff around because we might end up needing it later too, And so that was a really strong use case for
Mango. Now Postgress also though, supports Jason data types, so you could just as easily have done it with Postgress. I don't know if Postgress actually had it back when we did this, but I know what that it is
there now, yeah, and what it probably had. So there's you know, I would say, there's three things worth thinking about and knowing about it, right, So, Like the simplest thing is you could always just store texts like Jason has texting your database, right, right, But what you're kind of losing there is like any sense of validation, right, Like if you're missing a parenthesies, you're not going to database if going to tell you,
right, And so the most simplest form, postcress has adjacent data type and Jason in postcus is really just a validation step right where it says, okay, well let me conform to this actual correct Jason, and then you know it can do some operations on top of it. But really, where I think postcress has become more of a competitive Mango to B is the Jason B data type. B stands for binary, and the idea behind data is that it lets you, amongst other things, index a Jason B kind of
field, similar to how you could indexes with Mongo. Right. So, like the idea is that if you know, I have this like schema less data of sorts, like I don't know exactly the shape of it or what comes what's gets thrown into it, but I usually want to query for some of the keys, right, and I want to search for things and that taste that essentially, then the way that you can do this nowadays with post presses, you have a Jason B, you have a gin index or in
some cases just in necess' on the on Jason B column and you could just do queries on it. But you don't have to declare upfront what you can to query, right, You don't have to say, I'm always going to query for you know, this campaign, idea field or just you know, other kind of whatever the fields are. But you could actually have a kind
of more generic index. And so that's really I think what you know these days if you're storing Jason and the postcars definitely used Jason because there's no reason not to, or almost no reason not to. And so that I think, you know, gives you gets you ninety five percent of the use cases
that I'm going to be usually would have. Yeah, and you're still in a database platform that supports relational data as well, and that's I think to me, that's huge because every application, every business does have relational data, and so now you're able to accomplish that in a single database platform versus juggling multiple database connections and trying to remember which one has which data that's right.
Yeah, And on that note, more recently, also so talking about AI, we like in the postal community, somebody called Andrew Caine, I create a product called pg vector. Excuse me pg vector and pegbackctor essentially does is it stores vector embeddings inside Postgress so that you don't have to use a dedicated
vector database instead. It's essentially the same idea, right, Like you have essentially a special like column like data type that has special indexes, and then you can store you know, like if you're trying to you know, build all these AI applications, instead of now using specialized dabases, you can just use your Postgress And really the big benefit that people are seeing these days, right is that they can then also keep the rest of the data in Postgress.
Right, So you're building your cool, fancy AI startup, you can now you know, use Postgress for everything up to a certain limit. Right. So there are still benefits to using a specialized database, but especially when you're starting out, it's just so much simpler to stay within one system and then later on, you know, you kind of scale down. And I do want to mention at that point, right. Part the reason we talk
different database technologies. One of the reasons why Postgress is very good at, you know, kind of supporting these newer things like the embeddings is because it has a very good extension system. So compared to for example, my SQL, it's much easier in postgress to create an extension that changes some of the core functionality and postcress that hooks into different parts. There's more of a community
around that also that kind of releases these extensions. And so the fact that you know, somebody like three years ago Andrew Kaine was like, yes, I gotta you know, built this library called pg vector and I'll just publish
this. You know, it's an extension of postgress. And then you know, all the big ciut companies like a ws GCP Azure, they're all like, yes, AI, you know, it's the best thing, and by the way, we support AI. And then you look at it and it's just pgo vector kind of you know, sitting under in the database actually, like they just went and used the extension. I mean, it's cool,
they did it right, like they they made it accessible and usable. But it's it's fascinating how you know that extension system allows this adaptability of postprice essentially, yeah, for sure. And I think that you know, that sets up the stage so that you can empower your engineers so that they can focus
on application performance, right, Yeah, that's right. And I think also not learning about the completely new system, right because they will have to be installing the driver probably like on the application site, or I probably wouldn't support it, right, Like there's all this extra steps that they would have to take if to use them completely different. So yeah, and you know, going back again to talking about the number of technologies and fields we have to
have expertise in. Really that's like the overall objective for all of those is to create a performance app that moves our business forward. Because most of us are not in the business of running postcress databases or building AWS infrastructure. That's just the means to an end for whatever our company is actually trying to do. Agree, where do you see? Where do you see postgress and pg analyze going from here? Because we're still in the early stages of AI,
still trying to figure out what it means. And I think you might agree with me that AI is not a job killer but a job enabler. I always say it's complicated, right, I think for some people it's definitely job killer. Right. So if you are in the creative industries, and let's say you used to like, let's see you're an artist, but you used
to get money from you know, working advertising campaigns. All turns outs, you know, Open the Eye just released Sora, which you know makes your whole video production pipeline much easier to you know, just automate with AI. And so maybe you're out of a job, right because like suddenly your creative industry's job is just no longer paid, like you can still keeping an artist, right, there's just no money to be made. So I would disagree saying, you know, it's uh, it's definitely taking jobs, right,
Like that is very clear. I think it's that's in a sense how when change is happening, that happens, but it's also shitty, right, like it causes all kinds of problems. So yeah, I think you have to accept that too. But I think when I think about, you know, more personally, in terms of what I see right like happening in my like niches of the world, right, which is like engineering and data is optimization.
I think as mentioned data is optimization. I'm not sure how much this is a generative problem, right, So there to me, the natural language aspect of it is more a maybe I you know, there's there's a semi automated system that can solve some of these problems for me. But instead of me having to interact with the user interface where I click around or like maybe I should write some code, instead of that, I can just talk to my quasi you know, admin of sorts and it just happens to be,
you know, to be a large language model type interface. But behind the scenes, what's actually doing is something much more deterministic essentially, right, Like
it's driving another system that really makes makes the smartness essentially work. I think, you know, in the context of engineering obviously, you know, get a copilot for example, I think does have some interesting aspects to it, right, and so I have not seen it fully you know, solved, Like I would wish it could write my test for me, right, Like when I write code, I hate writing tests, Like it's just like I'm
lazy, and so what if I could just have copilot right it? But then the problem is intent, right because like it doesn't replace the intent, like you have to still tell it do this, do that, create that, But it does kind of take a little bit of the activation energy away, right. Sometimes when you you have a tedosnask, which there's a lot
of things to do. It's just hard to get started. And so what I've heard from at least other people, and I have some seem to live with myself, is that the that's today already a value that you know, something like copilot can deliver. And so I think if we you know, project forward, right, I don't think the I don't think the thinking goes away, right, I don't think like none of the large language models are
thinking, right, They're not reasoning, they're just like generating. And so I think really it's a question of how do we, like it's a question of like how do we drive these systems? Right, Like, how do we as operators as engineers drive a system in a way that makes predictable outcomes but automates the things that are you know, either hard to like it's just like take a lot of time. They you know, we take a lot of expertise, right, So I think there's a lot of opportunities there.
But I don't think the human goes away that drives essentially this is how the system should work for sure. Gotcha. So there's it's a way to provide the technical expertise, but you still have to apply like the the is this a good idea, yeah, or like the bounds of it, right, Like maybe it gives you suggestions that Like it's kind of like we do with findext thing, right, So like today if I look at our solution for the next thing, it's okay, like it could definitely better, right,
Like, it gives you a reasonable good recommendation. But I think over the years, but I'll see you know us do right, It's like at PG analyzed, our index recommendation will actually get to the point where ninety percent of the time you they are just good, right, Like there's something that an expert would give you as well, and you know you can just apply them.
But still I think the decision of when to apply them, how often to apply them, you know, which level of testing to do, that is something that a human operator should essentially take into accounts and decide because I think otherwise, you know, otherwise, you know, you don't really have control over the system, I guess, right, like you'll have you know, things be unnecessarily expensive and it could be cheaper you maybe have you know,
things be slow because the system oftimised for the wrong thing. And so I think that there's this level of control that I anticipate us needing ever so you know, ever more so in the next couple of years. Yeah, it makes me think of the Jurassic Park meme with I think it's Jeff Goldbloom, where it's like you were so focused on whether or not you could you never stop to think whether or not you should. Yes, Yes, that's definitely something I think of when I see some of these things Aye does.
Right, So what are the what are the big problems that beyond index and query tuning. What are some of the big problems you see with postgrass that fall into the I wish I'd known that sooner category? Yeah, good question. I think it's I mean, there are some things like most the other about this. So if you have a lot of data, like terabytes of data, you can definitely sort it in post ris, right, So like
postcris doesn't really have a limit per se. I think the one thing that I've I have definitely seen is if you anticipate, you know, scaling hugely to the point that you know you'll have one hundred terabytes of data, it really does help to think a little bit about how you may be able to split up your data potentially or at least how it should be structured in databased. So I'll give you two different examples of represential data modeling of some sort
is what I'm going for. So one example is back when I was cite of data. Right, societies is a sharting system, so you have different database servers essentially, and you scale your workload b adding servers, which generally is a very good pattern. Not a problem is if you're looking for, you know, a record, right, So let's say we have users.
Well, let's see, yeah, let's say if use we have just we have billions of users, right, we have all these servers, right, and like a certain subset of our users are in each server, and so if you're looking for a particular let's say email, what we'd have to do, right is we essentially have to run a query across all these servers, right, which, like, the more service you have, the more complicated
that becomes. And you can paralyze a love of that, right, But like essentially many a problem with you know, too many connections, and so like generally speaking, it's not good if you have a lot of queries that go across all the service. And so when you have this type of scaling out anticipation. One thing that I always do these days is I make sure that I think about how could my data potentially be sharded in the future,
right, split up like that? And oftentimes let's say you you build a sales tool, right, and a sales tool has like B two B customers, right, so you have all these customer IDs, right, And so in the most native implementation, you have, you know, maybe a customer's table, and then you have a CRM records table and that has a customer D. But then maybe each record has a comment and sort of comment just you know, includes the record ID, but not the actual customer D because
you're like, that's that seems duplicated, right, would I copy this you know, same value all over the place. And so the thing I would actually do if I anticipate the scale is I would actually include my customer D
in this case, in all my tables. Because that's the one thing that makes it a lot easier to shart out your data is if it's very easy for you to say, if I have, you know, my one tear by table, which subset of the table belongs to each customer, and then when I'm going to move you know, like ten percent of my customers to this at a server, I can just you know, select you know, essentially by customer I D versus doing a very complicated joint that actually becomes quite
expensive once you set its large. And so that's the one thing I would do to to anticipate scaling is to to really, you know, I think think of that, you know, what, what's your unit of subdivision essentially in your data? Is there a way to like potentially just add that ahead of time so that you have a better chance scaling in the future essentially with outbumach effort. And then the other thing again data related that I've found recently
is just there are some tips and tricks. This is really kind of postcers specific. It probably applies to my SEQL to which is sometimes it makes sense to store data in a what seems like a less than optimal format, but it's actually more efficient. So the example is we store a lot of time
serious data, and we don't use specialized time seriou data base. We use postcress for for storting postcress time serio data of course, right, And so one of the things we've recently done is we've started using a rays to store some of our data. And so imagine you have like a data point and you have you know, let's say you know, there's five different values,
they're all in the same timestamp. And so the most simplest implantation is you just have you know, timestam column data point one, data two, they've went three hundred and four all the same time stam. So what we've done recently and this has like yielded a tremendous performance like well disk based efficiency, but also performance benefits, is we've essentially put more data points for the same
customer in one row. Right, so the row essentially becomes like an array of time stems, array of data point one, a ray of data point two, a rady of data point three and such. And what that does is postgress is various mechanisms. How it you know, kind of will reduce overhead in this case, like it will move some things to a second or storage. In some cases it will and if you avoid the what's called tuople header, which is like the each row has like a little header that takes
extra space. And so if for some reason you have a problem that looks like ours, right, then you may want to think about a rays as as a way to kind of build a not column storage of sorts, but like it's it's the kind of thing that the column storage is good at. But if you are in a row based stores, like postgress, arrays can be an interesting hack there to optimize things. And then you can just add
time series database to the list of things that postgress can do. Exactly, Yeah, I guess I should you know, in completeness the stake, I should probably say that partitioning is what you should do first. So forget about the array stuff. You should partition your tables if you haven't, right, So, like that's the other thing, if you haven't append on the work
up which time zer state it usually is. Just make sure you use partitions, because the big type pattern and progress is if you're like you're doing inserts and you're doing the leads and so what'sentually happens is that you create all lot of these dead rows versus if you have partitions, right, Like, let's imagine you want to keep thirty days with data, and so you insert each day's data into each partition and then on the thirtieth day you drop that partition.
And so dropping a partition like a table petition is much cheaper than doing as elite statement across you know, millions of records in a table, gotcha, And then that not only is that more efficient to do, but it also saves you overhead when it comes time to vacuum the database. Is that correct? That's right exactly, because the vacuum doesn't have to do the work right because like, yeah, they're not dead ros, they just are.
You just dropped the table, so right on. Excellent. Cool. Well, we could continue digging in on this, but I know you've got a meeting coming up here, so it feels like we're a good stopping point and then we'll get you off to your next meeting on time. But thanks for thanks for coming and talking about this. This has been cool, and I it's been insightful. I learned a lot of things about put Express that I
didn't know despite having used it for a long time. Perfect. Yeah, And if anybody who's listening is interested to learn even more about Postgress, I host a weekly video series and YouTube called five Minutes of Postgress. So if you want to get you know, a little like snippet of what's new with Postgress each week, feel free to subscribe. To that, and I try to make that as useful as I can to the community. Right on.
What's the name of that YouTube channel. It's just an a PG analysed channel, but okay, the name of the series is five minutes of Postgress Awesome. Right on? And then anywhere else did you hang out online that people can interact with you? Yeah, I mean I'm on macedon, I'm still on Twitter, akax and LinkedIn, so feel free to you know, find me online. I'll send you a few links you can include the show notes perfect, But generally, you know, if you want to hear more about
PG analyes, just go through a website pglies dot com. We host webinars every now and then where we talk about, you know, things how how to running your postcress database and of course how preach olice can help. But we try to, you know, make that general useful. And then you know, also, I'm in a bunch of the postal community spaces and postals conferences, so if you're at a post conference later this year, maybe basically there awesome, right ern, Well, thank you so much for joining me
on the show. Perfect, Thank you so much for having me all right, see you see everyone else Next week, by every one,