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,