- Hello YouTube, hello Michael.
- Hello Michael.
- It's great to have you here.
We're gonna talk databases, it's gonna be so fun.
- We are, thank you for having me,
I'm looking forward to it.
- Absolutely.
All right, folks out there in the audience,
feel free to ask questions if you're watching live.
Thanks for watching the replay
if you're watching afterwards.
And with that, let's kick it off.
Michael, welcome to Talk Python to Me.
- Thank you, thank you for having me.
It's a pleasure.
- Yeah, it's a pleasure to have you.
I'm honestly pretty psyched to talk about database stuff.
I feel like databases are these magical centerpieces
of so many applications,
and yet, yet they are so unoptimized in many situations
that if you could just sprinkle a few of these ideas
that we're gonna talk about on them,
they'll make the entire app and the entire experience
for everyone involved better, right?
Easier for the developers to have code that goes fast
if their queries are fast,
customers will be happy that it's not like, wait, did I click the button? Oh, yeah, it's still
spinning. That's right. You know, like all of these experiences, just like every time I have them,
I'm just like, remain calm. Somebody didn't put an index somewhere. There should have been one. And
if this thing will probably work eventually, I don't know if you have those feelings as you go
around the web. Yes, 100%. I might have taken it a bit far in trying to fix them. But yes,
I'm going to send a support request and here's the explain document that I've come up with
for you.
Fantastic.
All right.
Well, we're going to dive into specifically Postgres, but we were chatting a bit before
we hit record.
And this much of this guidance expands beyond Postgres, right?
Even beyond relational potentially.
Yeah, absolutely.
And going back to something you said just briefly then, the beauty of databases is they've
worried so much about performance and you can get up and running, you can get a lot
of performance out of them without doing even the basics right sometimes.
But at a certain scale that starts to fall down.
So yeah, once you're past the first few stages, quite often the performance problems you're
hitting tend to be database related.
So yeah, that's what I'm excited about.
But it's not, yes, most of my recent experiences with Postgres, but the performance is mostly
about trying to work out how to do less work or how to not do the work in the first place.
And that's the same whether you're, whichever database you're working with, or yeah, data
in general, basically.
Yeah, databases seem magical.
They take all this data and they come up with answers incredibly quickly, but they're not
magical.
and then they know how to sort of estimate
and plan those together and take guesses.
And our conversation today will be how to understand
those different things that databases can do,
how we can interrogate them
to tell us what they're trying to do
and then what changes we can make to make them faster
and some tools along the way.
But before we do that, how'd you get into databases
and being associated with databases?
- Yeah, so I'm gonna start with the database.
being associated with PG Mustard and all the stuff you're doing?
Yes.
So I, it goes back in university, I studied mathematics.
So that involved a tiny bit of programming, but really not much, just a little bit of
statistics that I quickly dropped.
Tiny bit of mechanics that I dropped as well.
Very pure.
There must be something about math in the air.
The last three episodes I've done have all been like people with PhDs in math and doing
all sorts of interesting stuff. So and I have a math background as well. But yeah.
Amazing.
Yeah, it is. So it started in math. Yeah.
Yeah, absolutely. No PhD here. But I then went to a... Well, I thought I wanted to be a management
consultant for my sins back in the day. And then the financial crisis happened. So luckily,
that got scuppered. And I ended up finding a job at a company called Red Gate, which
I understand you're actually familiar with.
So they did database tools, and were predominantly for SQL Server, but also developer tools for
.NET as you've seen, and then expanded out from there.
So I started at a time where their most popular product was called SQL Compare for comparing
databases.
And just a few months into when I was there, they launched a product called SQL Source
Control.
So it got into kind of the change management side of things.
And luckily got so many opportunities there to do various roles and landed mostly in product
management.
Luckily, got a chance to take on their Oracle tools team.
So it's a tiny team compared to the rest of the company.
And it was the first foray into their tools for Oracle.
We also span off a couple of tools for MySQL as well.
And in working in that small team, we started to see, I wouldn't say the rise of Postgres
because it's been going for so long, but the kind of the resurgence and the slow and steady
improvements from that. So yeah, that's that was my kind of first five years of getting
to know databases. And then what a cool place to do it, right? I mean, there are some very
hot database companies, you know, Postgres, the MongoDB folks, obviously, Oracle, Microsoft,
where they make them, but then there's only a few places
where they really say, and what was missing?
Let's just fix all of that, right?
Let's make the developer experience
really right on databases.
And I feel like Redgate was very much like that,
like SQL Compare and the source control stuff.
That was all very neat.
- Yeah, absolutely. - Probably still is.
I haven't touched it for a while,
but I don't mean to speak of it totally in the past.
That's my experiences that was in the past.
- Yeah, and they're doing some cool things now.
They're doing some of their products across database now.
They're doing some Postgres things.
They acquired FlywayDB, which a lot of people use.
But yes, the thing that first attracted me to them
was their slogan, believe it or not.
It was ingeniously simple software,
which is something about,
I guess it's the math background as well,
about getting to a simple solution.
And we've talked about databases sometimes feeling magic.
But even if you realize they're not magic,
they're still complicated or there's certain complexity
to dealing with them.
And if you can make that easier for folks,
or at least help them with better defaults,
like people designing databases are exceptionally smart
and they pick the defaults for a reason.
But in the real world,
sometimes you can afford to be slightly less conservative
or choose a certain perspective on something
and make a tool that's only for a certain subset
of the users. So you can be a bit more opinionated as a tools vendor versus a platform provider.
And I like that kind of space where you can differentiate a little bit more on usability
and performance, maybe a little bit less on the absolute fundamentals.
Sure. Well, there's so many different situations in which databases are used, right? Are you
Google? Are you talk Python? Or are you a single little app that one person is using?
Like these are all very unrecognizable to each other in significant ways.
Yeah absolutely and the mad thing is they can all run off the same type of database.
So you could run.
Yeah that is mad.
Yeah.
Yeah that's a really good point that you can run off.
Basically everyone installs the same database, does some sort of schema creation thing and
goes from there.
Absolutely. Or even Mongo, right? Run well. I think that's the key thing. Run well, probably
most I mean, Facebook still runs off, albeit an admittedly highly modified, very impressive
MySQL setup. And I think just today we saw a couple, well, maybe in the last two days,
we've seen a couple of really impressive companies launching SQLite on the edge, you know, so there's
Really, pretty much any database that's made it to this point can probably support you
at any scale if you know enough about it or if you want it well.
Yeah, for sure.
How interesting.
All right, now before we get into optimizing these, first let's get the order right.
Understanding the performance and then optimizing, right?
Not take a shot at optimizing and then we'll figure out if we got the right spot.
like profiling and performance optimization of code.
Let's just talk for a minute about
of we were no matter what,
GUI tools for managing databases.
Because I saw that you were using
in some presentations you're doing,
you're using DBeaver, which is pretty interesting.
And I feel like a lot of people
don't end up using these tools.
So maybe you could highlight a couple
that I think sort of stand out for just interesting database stuff.
I mean, coming from a tool vendor, of course.
Yeah, no, go ahead.
- I do apologize.
I got a tiny bit of lag there.
But yes, so if you can hear me clearly, then I do like dbeaver for doing demos.
It's very easy to run multiple queries in a row that you don't have to type out or copy
paste around.
It's also exceptional for post GIS, which is for geographic data.
I know a few people are really keen on its visualizations for that.
I tend to not use it day to day anymore, but only because there's so many tools out there
that you can use.
I think you've listed a few, but a lot of people are very keen on the default one that
that ships with Postgres called PSQL,
which is a command line interface.
Very, very powerful.
Can do a lot of things that other tools can't,
but has quite a steep learning curve in my opinion.
There's some people doing really good tutorials on,
I think Leticia is doing some really good conference talks
at the moment on this and has done them in the past
and has a website around PSQL tips.
So yeah, there's loads of good gurus out there.
My, at the moment I'm using a really modern one
called ArchType, only recently launched,
but I tend to try out tools probably earlier than most would.
- But wait, this looks nice.
- Right?
(laughing)
- It's real nice.
ArchType, A-R-C-T-Y-P-E.com.
- You got it.
- Yeah, right on.
This is cool.
Okay, and this works on the different databases
that you might.
So they stripped back down to a couple, maybe a year or two ago.
So it was Postgres and MySQL, I believe, but I think they're adding some back in.
Not 100% sure though, sorry.
I'm Postgres through and through.
Yeah, it solves the problem.
What else do we need to worry about, right?
Absolutely.
And if we're talking specifics, actually, because I'm on Postgres on a Mac, I think
probably the best long-lived option and one that's been around for years, I would say
is Postico, which is a Mac-only app. But, yes, it's lightning fast, which I -- so Postico
with an O at the end.
>> Cool. Okay. Oh, Mac-only. Okay. Yeah, that's pretty nice. But this other one, this
archetype looks really nice.
that looks on everything, right?
- No, no, of course, you're absolutely right, they're not,
but they need to work well.
So the two others that I was gonna throw out there
that are kind of nice, this one clearly is not for Postgres
because it's DB browser for SQLite.
But at the same time, I feel like a lot of people
are interested in databases, generally not just Postgres,
right, you can come across SQLite all over the place.
So I think this one is nice, this one is free.
And then have you done anything with Bkeeper?
No, I have seen it around.
- Yeah, Beekeeper's pretty nice.
It gives you like auto-complete for your queries
and ways to explore your schemas.
But what it seems to not have really
is any sort of exploration of the schema visually, right?
There's not like a UML looking diagram type thing, so.
- Yeah, one last one before we,
I think a company that are doing great things
in ID space in general, JetBrains,
and their DataGrip product is very good as well.
And very, very cross-platform in terms of different support
for different databases.
- Yeah, absolutely.
I think, yeah, DataGrip is really great, right?
Like if you use PyCharm, for example,
and when you're doing the database features of PyCharm,
it really is just DataGrip, right?
If you just want the standalone edition.
So super cool.
Yeah, these are all really nice.
and I discovered a few myself as well from this.
So, awesome.
I just think it's kind of important
for people to have these tools
and maybe you end up just on CLI tools like PSQL,
but when you're starting,
I think that that can be the difference of,
I can do this or I can't do this.
You know what I mean?
Rather than efficiency.
People might just look at it and go,
that's too much for me, I'm out.
- Yeah, well, it's also about discovery.
I think sometimes you don't know what's there
on what's possible and in a command line tool,
it's really on you to figure that out.
Whereas in a GUI, they can leave you hints,
they can show you the tree of things.
You can start to realize what is and isn't possible.
And they've got a lot more freedom to educate you
as a new user, whereas in a command line tool,
there's very little they can do on that front.
So yeah, not all of the GUIs take advantage of that,
but the good ones. - The possibility is there.
Yeah, the good ones.
All right, I was talking about schema
and I'd like to hear your thoughts on this.
I'm sure you're more well versed in it than I am.
When I think about schemas, I think about the tables,
the maybe the columns, the types of columns that are there.
So this one is a Varchar 16,
this one is a float or whatever.
Would you consider indexes part of the schema?
- I would, yes.
- Yeah, okay.
- I think, yeah, I guess it depends on,
definitions are hard.
Two mathematicians discussing definitions,
how long have you got?
But I guess when I'm thinking about schema changes
and deployment scripts and things like that,
I think it's just as important
for the functioning of your app.
An index existing or not
can be the difference between you effectively being up
or being down.
And if it's that important, then I'd probably say it does.
It is integral.
>> For sure.
Quick audience question from William.
What's the most powerful utility you personally
got out of PSQL?
>> Good question.
>> What's the most powerful thing
you find yourself doing with PSQL,
maybe is a good way to phrase it.
So, yeah, I actually don't, it feels like I'm probably not the best person to ask.
I tend to use it for really quick things.
So it's more of like, it's by far the easiest and quickest to throw a quick query into and
get a really quick result back out of.
There's zero load time or anything like that.
Right.
And you get text output straight back.
You don't have to worry about any formatting or anything.
Right. Open your terminal, type psql, some query, you see the answers right there.
Yeah. But that's not like, there's probably people ripping their hair out and screaming at the screen saying,
"Why didn't you mention this amazing feature of psql?"
But the truth is I'm not a heavy database user in the grand scheme of things.
You know, people doing DBA work.
I make and help design and run a tools company, not a, like we don't have a massive database.
I don't do tons of analytical queries day to day.
So those, yeah, it's the DBAs of this world and the people that have got a lot of hands-on
database experience probably do a lot more of that.
But I would, in terms of recommendations, I think Leticia Avereux does a, has a website
called psql tips and I think if you go to it, it just loads a random tip of about 100
and something that she keeps adding to and I think that's a great, like that's a fun
way to get some discovery or learn some features in a tool.
Yeah, absolutely.
All right, you spoke about the being in a tools place.
You want to just give a quick shout out to PG Mustard and we'll come back and talk about
it more later?
Yeah, sure.
That's sort of the second question I was gonna ask you,
or not making a speedy progress, which is great.
But, so you were at Redgate,
and now you've gone on to start your own company,
co-found your own company around database tools.
- Yeah, absolutely.
So I worked at a couple of companies in between,
one of which was a big fan of Postgres.
So there was a company called GoCardless,
and the team there were exceptional,
loved my time there,
and they ran quite a big payments company
by the time I left on a single Postgres database.
And it was, yeah, the team were big fans
and completely converted me.
I was already keen on Postgres or the idea of it
from Redgate time and hearing what customers
were saying about it.
So I was pretty sold on the idea.
I loved the platform, I loved how extensive it was,
I loved that you could see the source code,
you could even contribute back to it.
So I was a big fan of the platform.
But my first ideas for a business were pretty lukewarm, let's say.
So there's a regular product I love called P-SQL.
So you mentioned autocomplete in, I think it was Beekeeper.
They had an autocomplete tool in SSMS, which is SQL Server Management Studio,
which people loved and it's an everyday use tool and you could easily see yourself
making it better and better for years.
So that was my kind of ideal tool.
But building it would have entailed either integrating
into the 25 IDs that we've looked at so far,
or picking one and really maybe not being that useful.
So ended up trying to build something
a little bit more standalone.
And there's a few really good open source query plan
visualization tools, and they do a lot of work
to help you see where the time's going in a query
and give you some basic kind of guidance
and maybe not basic, maybe super DBA friendly guidance.
So it's often built by DBAs for DBAs type tools.
- Advanced but maybe basic in his presentation.
Something like that, right?
It's just like, here's a wall of text.
It speaks your language, go figure it out.
- Yeah, exactly.
And I quite often saw in different places,
people would post links to them and say,
I've put it into this tool,
but what can I do to speed it up?
So there was clearly still a missing step
of what can we do about it?
Okay, here's where the time's going,
but what can I actually do about that?
So tried to build something that would help solve that,
solve that problem, and this is what we came up with.
- Yeah, it's really cool.
And the way it works is you give it explain,
you have Postgres explain your query plan to it, to you,
and then you give that query plan to bgmustard
and it puts it into a graphical form
and into a more digestible form.
And it's interesting.
But what's really interesting is it gives you advice.
It says, you know what?
This sort is happening without an index
and that's where you're spending most of your time.
Here's your big opportunity for a win.
We'll come back to this, like I said,
after we talk about finding these problems.
But I just wanna point out,
I had, we'd covered this on Talk Python.
One of our guests mentioned it.
I think it was Henik.
And then we also talked about it on Python Bytes.
My other show is just one of the topics.
And I don't remember which place we talked about it.
But somebody made the joke of,
oh, this is my new consulting job.
What I'm gonna do is I'm gonna buy a license to PG Mustard
and I'm gonna go around and optimize people's databases
by dropping the query plans in here
and just taking its advice.
which I thought was hilarious, but also plausible.
- We have some consultants that use us.
- I can imagine.
I mean, it's really good visualizations
and interesting, often useful advice that it gives.
So, all right, that is all super cool,
but let's go back more broadly to Postgres,
but then maybe even, like I said, more general.
I feel like a lot of this applies to
MySQL, Microsoft SQL Server, and even Mongo.
So let's just start out about
maybe you're the unfortunate person who is running one of these websites
that makes me think about the optimizations that were missed
as I wait for the spinning thing to load.
And it's your job to figure out what is wrong.
So maybe we could talk about some of the techniques for finding
problems with your database.
Let's just start there.
My database is slow. Help.
Perfect. So
a lot of databases
will have some version
of a slow query log
or a built-in
monitoring view
or you'll have a
monitoring tool set up
yourself to
log and aggregate this kind of thing.
And
Normally the starting point, so I guess there's a few starting points.
Either customer reports, customer or team member or somebody reports something's slow
or your database is on fire.
It's you know maxed out on some something.
- CPU memory disk, some combination thereof.
- Yeah exactly or in the cloud maybe you've run out of your provision.
Yeah anyway so there's that.
But yeah, let's take the I've got one slow query.
So in that case, you probably know or can get
from the query log, the actual query that was being run.
- Sure.
Let me just, yeah, let me just make a little side comment
here is like, if you're using an ORM,
it can be a little bit harder to see what that is, right?
You might have to attach some kind of real time
logging tool to the database or get it to log it to a file
and go track it down.
But also, a lot of the ORMs have features
that you can make it spit out the queries
like in super verbose mode.
So for example, SQLAlchemy, when you create the engine,
you can say echo equals true,
but it's not just SQLAlchemy,
you can do it with many of them,
and it'll start printing the queries,
and maybe that's a place to start, right?
- Yeah, absolutely, and some of them have,
there's some tools now increasingly
that let you even do the explain.
So yeah, the first step is get the query,
but in some of these frameworks and ORMs,
you can even ask for the explain plan directly as well.
Increasingly even with,
and this is where we start to get a little bit more technical
and a bit more database specific,
but I think it's the same across many databases,
but you want explain will give you the plan
of what the database has chosen to be,
probably the optimal route to do that query or to get you that data or to
insert that data and
But it if you if you want to get the performance information and get really start to dig into exactly why it's slow
you need to run something called explain analyze which will also run the query so
Be careful around that if you're running kind of big delete or something and probably not run it on production, please please please
Why is this delete slow?
Oh, wait, why is there no data?
It's fast now.
That's great.
Wait, why is it fast?
There's no data.
Or you go to run it a second time
and don't understand why it's not--
Yeah, it's different somehow.
Yeah, so explain, analyze, we'll run the query behind the scenes.
But instead of getting the data-- or instead of, let's say,
getting the data back or getting a normal return,
you get a query plan back with performance data in it
with quite a lot of statistics.
So yes, you just append that to the beginning of your query,
run it as normal, and then it gives you the query plan.
So that's step one is get that information
from your database.
- Right, and the way that that looks is
instead of writing SELECT * FROM table where,
you write EXPLAIN SELECT * FROM that,
or EXPLAIN ANALYZE, depending on
whether you want that timing information, right?
- Exactly, so if you have, and this is probably,
hopefully not many of you are in this situation ever,
but if you have a query that never returns,
let's say it's gonna take six hours maybe,
you probably wanna get the explain plan,
just to get a query plan, because explain analyze
is gonna take as long as your query,
because it is executing behind the scenes.
- It runs it and figures out how much IO it's doing
and stuff like that, right?
- Yeah, exactly, oh, good question.
So to add IO information, at least in Postgres,
you need to include an additional parameter.
So it would be explain analyze buffers.
And this is part of my advice to people is,
especially when, well, probably everybody,
but especially when you're new,
and you might need to ask for help from somebody else,
if you add as many parameters you can to that explain,
if you're in an IDE, just select them all.
Once you're getting timing,
there's not that much additional overhead
to get in the other information as well.
But in Postgres, we recommend buffers for both settings.
And there's even a new one
that gives you right ahead log information as well.
So if you ask for all of it,
then you only have to get it once.
And even if you have to go to ask,
maybe you're pasting that into a tool,
but maybe you're asking a colleague for help,
they might spot something in that extra information
that helps them help you.
- Right, right, right, okay.
Michael in the audience asked a question which makes me
think of my next one here.
Says Google Cloud SQL just added some sweet query
monitoring and planning visualization.
So that's pretty nice.
- Yeah, Google Cloud SQL doing some really cool things.
They also just recently announced
that you can do major version upgrades
much more easily now, which they didn't have before.
So yeah, the cloud providers are doing
some really interesting things.
The query plan visualization stuff is obviously very interesting to us, but there is a natural
trade-off with performance as always.
There's a trade-off in general between monitoring constantly for these things versus getting
them when you need them.
There's a small overhead, well I say small, but normally small overhead to constantly
profiling constantly monitoring for these things and getting timings does have some overhead.
So there is some advice out there to not do this in general for most applications.
But we have customers that get the query plan for, they don't log it for every query, but they get it
for every query so that if it's slow, they put it into the logs and it can be investigated later.
Interesting. Yeah, so my question is, let's suppose I'm going to get some cloud database type thing, right? Managed Postgres, let's say, and I also happen to have Postgres running locally. Or maybe I'm fully insane. And I have SQL lite local, but then Postgres and yeah, that's gonna make make you worry. I'm sure but
I run one of these explain analyze plans against them.
Are they comparable?
Do I need to make sure that I pointed at my production system when I'm asking these questions?
Yeah, let's take, let's start with a Postgres, Postgres example.
That's going to be easier.
And yeah, my main, the main thing that's tricky there is, well, there's a few things to get
right.
Naturally, the schema being as similar as possible is pretty important.
The config of the database as well, as similar as possible.
So there's a bunch of config changes that you might have made on production that's pretty
important to have locally as well, so that the planner's making similar decisions.
And then the big one that we see people do, sadly, far too often, is having a really tiny
data set locally. So, you know, generating 100 rows or even worse, inserting three rows
or not having any data in a table. And databases, as we've discussed, magical or let's say clever.
And they know that if there's the amount of data is really important to them in terms
of their choices. So, you mentioned different, I think, the join algorithms. Postgres, for
Yeah, has multiple join algorithms that can use and if there's very little data, it will
choose one that has a very low startup cost.
Even if it gets would get expensive at lots of data because it knows there's or it thinks
there's not much.
Whereas in a world it's saying if locally you've got not much data but then on production
you've got a lot of data, it might choose a different join algorithm.
So the choices it's making are very different depending on the amount of data.
So that tends to be the one that trips people up most.
But it's not just algorithms, it's also whether it would pick an index or not.
That's the one that really confuses people.
So you're like, I know there's an index in here, why isn't it using it?
Exactly.
It's a super common question.
And it turns out you've only got 10 rows there.
They all fit on a single block.
And it's much faster for Postgres just to return that single block to you than it is
to look up each entry in an index.
So yeah, exactly.
- Yeah, okay.
So I did wanna give a shout out to a couple of tools
for having fake data.
Maybe you've got some recommendations as well,
but there's really cool tools like this one at makaroo.com.
Are you familiar with Makaroo?
- I'm not, no.
- So what's cool about this,
like you can go and generate fake data, right?
So of all sorts of things,
but one of the problems is like,
well, the fake data is too fake.
It doesn't look like the right structure
or it doesn't validate or, you know,
it's also good just for developing things.
But like, for example, you can have
all sorts of stuff in here and it gives you,
it has the thing that's cool is there's a library
of types of things that I can get.
So I could get like all sorts of stuff to do with cars,
that has real car makes and models and years
or VIN numbers or credit cards or genders
or those types of things.
And then you can go and you can export the schema
as CSV or JSON or a create table SQL insert script,
which is pretty cool.
So if you need more real data,
places like this for generating them or Faker,
which is a package for Python and these other ones
that you can just make enough real fake data, right?
- Yeah, exactly, and this is super powerful
in terms of getting as similar data as possible
to your production system without having to worry
about personal sensitive information.
- Yeah, that's a big concern, right?
It's like, if you take production data,
let's suppose it's not that massive,
and you give it to all the developers,
somebody's gonna lose it.
Somebody's gonna lose their laptop
without the drive being encrypted.
They're gonna put it on a USB stick to transfer it.
that's just like fat 32 with no security
or something terrible, right?
- Yeah, absolutely.
And then you might not even want people
to even have select access to some of that stuff
in certain environments.
So yeah, but it's, you know,
probably the more important thing in my world is,
or the first stumbling block is the sheer amount of data.
- Yeah, the number of rows, right?
- Yeah, so even if, you know,
some of these fields aren't perfect,
maybe you've got numbers in as names and things like that.
It's probably not gonna make a huge difference
to performance unless you're really far out
in terms of the size of that data.
So if you're inserting kind of multi-kilobyte blobs
instead of gender,
then you're gonna maybe see some differences,
but not as much as if there were a million rows
versus if there were two.
So step one is make sure you've got enough.
And then step two,
maybe if you need to make it a lot more realistic.
- Yeah, size and data type,
but not necessarily super precise, yeah.
- Great point on data type as well, yes,
especially when it comes to indexing.
- Right, exactly, 'cause indexing a string case
and sensitive is different than a number.
- Yes, yes, great point on case and sensitivity.
The other thing I wanted to call out
on the Postgres side of this is it's got
a really powerful generate series function
that can be used to mock up data quite quickly and easily.
Not as powerful as some of these,
but simpler and built into Postgres already.
So that's quite cool.
And Ryan Booze from Timescale has been doing
some quite fancy demos of that,
of using that to do quite interesting time series,
you know, huge amounts of data being populated via it.
So it is surprisingly powerful as well.
- All right, speaking of time series,
Michael asks, "Any tips for storing machine learning
"embeddings in Postgres?"
Best thing he's seen is storing them as binary blobs.
I have no advice here.
Sorry, I don't have any experience in it.
How about you?
- I don't think I fully understood
what would be different about it.
In terms of binary blobs--
I think generally what happens with the machine learning stuff is people will train up these
models and they just save them as just binary blobs.
So it's more of a tips for storing binary data perhaps in general.
So this is second hand.
This is not something I have direct experience of, but I've read a lot about performance,
believe it or not, over the years.
It seems to me that the advice is in the early days, keep it simple.
Maybe you want to store blobs in the database, but you could also -- there's very cheap blob
storage out there, and you can store IDs to those in the database.
But if you want to, if you want to only have one tool to start with, you can store them.
The database allows you to.
There's specific types in Postgres for that.
Jason Beef support in Postgres is amazing.
And I think probably led to,
well, it was partly responsible
for why it's popular today, I think.
- I think so too.
I think people see it as a significant,
let's say a really good choice
that's like some middle ground
between a purely relational database
and document databases like Mongo.
They're like, but we can kind of have both, right?
- Yeah, and I don't even know
if you need to say the word kind of,
because I think maybe there's, I don't know many,
but maybe there's some use cases
where Mongo is gonna really outperform Postgres
using JSON-B, but I've not seen them
and I don't know what they are.
So it's super powerful and I guess the key difference
between that and pure blob storage is you can index it.
So you can, yeah.
- You can index it and query it.
You can index into, go to this object in JSON
and then here and then here.
and if they have that value, I want it back as an index.
That's the magic.
- Right, but it's not as performant
as splitting those out into columns in a schema
and indexing those, but it's a trade-off again.
Back to trade-offs.
- Yeah, yeah, yeah.
Do you have a billion of those things or a hundred of them?
'Cause if you got a hundred, you're probably fine.
I mean, you probably weigh more than a hundred,
but billions is there, you might be pushing your luck.
Yeah, okay, pretty interesting there.
Other real quick shout out just to like another thing that these guys,
disclosure, they were sponsors of Talk Python
but I found them before they were sponsors.
I thought they were cool and started talking to them.
It's this place called Tonic that you connect it to your real live data
and it will generate like PPI cleaned up or stuff that looks like your real data,
but it's not actually your real data.
So you sort of base it off of your production data.
Anyway, if people are looking for fake data,
there's I guess my whole reason of going down that
is there's not a lot of choices,
not a lot of reasons to not have enough data.
There's a lot of options for getting enough data
at various versions of realism.
- Yeah, good point.
- Yeah, all right, so we were talking about,
can I run this explain, analyze against my local machine
and against, say, compare the results
to how it's gonna be in production?
So one of your big pieces of advice
is make sure you have enough data
and data types that are representative and probably relations, right?
If you're doing joins, like you want to have like similar amount of related data and whatnot.
Yeah, ideally, but again, in step one, and that website you talked about that has a spinning
loader, they're going to be fine, you know, they're going to spot their problems without
very accurate.
Yeah.
So if a certain, you know, if a foreign key isn't indexed and they're doing a lookup on
that, they'll notice it even if the distribution is slightly off.
So yeah, the nice thing about performance is you don't, often you're talking about 10x
difference, 100x difference, you know, thousands sometimes.
All the blog posts talk about, you know, how we made Postgres 20,000 times faster.
But it's just-
All the stuff was added in an index.
Well, there you go.
Yeah.
And there's other things, right?
There are, performance isn't just indexing,
but when you're talking about the absolute basics,
it's, yeah, it's normally,
you're probably gonna get pretty close
as soon as you've got even,
like as soon as you've not just got 10 rows
in your database.
- Sure, sure, sure.
Okay, so because there were interesting things
that you'll find, maybe you'll only find this
if you do the buffers also,
in addition to just analyze is if you're doing a sort
and you have a ton of data,
it might say there's too much data to sort in memory.
So it might do a, what was it called?
A merged sort on disk.
That's crazy.
It writes data to the disk and sorts it as the database.
You're like, wait a minute, what is it doing?
It's taken out of memory.
Are you insane?
What is this?
- Well, yeah, it's, I mean, it's super clever, as you said,
but the alternative is risking an out of memory exception
the whole thing should be.
- Do you want it fast and wrong or write slow and correct and working, right?
Like, take your pick, right?
At some point, it can't do it otherwise.
But that goes back to the real data.
If you don't have enough data, you'll never see the problem as it's going to disk because
why would three records go to disk?
That would be crazy.
- Yeah, exactly.
So, well, this is a really good example because Postgres determines what it would do in memory
versus on disk by a couple of parameters now, but the main one being work mem or working
memory and that's set very low for most production workloads. So that's four megabytes by default
and I've seen a really good one of the top Postgres consultants giving a talk at a conference
even a few years ago. So this might even be out of date saying they just automatically
change that to 16 megabytes wherever just always as a starting point and sometimes bump
it you know for analytical workloads it could be a lot lot higher than that but they have
there's loads of formulas you can see out there but that's an example of a default that
probably is a little bit low in Postgres.
But if it's running on the cheapest server you can get it somewhere like Linode and it's
got you know 200 megs total well then you you want it to still be right and slow not
wrong and crashing or fast and crashing.
- Yeah, exactly.
- Yeah, but you do need to know these defaults.
I think part of being successful is like,
okay, this is my context.
So these are the defaults that make sense.
And these are the ones that I should be tweaking.
So in another presentation I've seen you give,
you talked about the process that you go through
for finding these,
you sort of determine there's a problem,
figuring out what it is and then solving it.
You want to talk through your process there?
- Yeah, absolutely.
And this is a process I recommend
and I'm guilty of not always following,
but this helped me as well.
So yeah.
- Learn the rules first,
so then you can break them later.
There you go.
- Right, exactly.
But I tend to regret breaking these actually.
So yeah, the first couple,
I think we've already covered briefly,
were run on a realistic data set.
and then second one being make use of the parameters that explain has in your database.
So for Postgres, use as many as your version supports is my, it tends to be my recommendation.
So get a lot of information is the short version of that.
With Postgres query plans, I think, especially if people are used to reading text format
they'd be very easily forgiven for starting at the English-speaking world, start at the
top, read left to right, makes loads of sense. But the plans are indented, so they're a list
of operations that Postgres is doing, and the first one you see is the last one it does
to give you the data back.
Think of it like an onion, right? It's the outer shell of the thing that you're being
given.
Yeah, exactly. It'd be difficult to start in the inside of an onion, wouldn't it?
So, yes, my advice tends to be, so firstly, you need to know that the plan is executed
from the most indented inwards, just to be able to understand what's going on.
But secondly, it reports some really important statistics right at the bottom.
So the total execution time being an obvious example, but also the amount of time it spends
on planning, on triggers, and on just-in-time compilation.
So these are... don't worry if you don't know what all of those mean, I didn't, but those can all,
in rare cases, be the dominant issue. So if planning time is 300 milliseconds and your
execution time is one millisecond, but you've got 20 operations that the query's done in that one
millisecond, you could spend easily an hour trying to understand that entire query plan,
maybe more, maybe you've been interrupted a few times, and then you get to the bottom
and you see that planning time was the problem the whole time.
You're like, I have five seconds, five milliseconds I could possibly save.
Yeah. Yeah, exactly.
Do things like cache the query plans or stuff like that?
Yeah, yes, so that's a whole topic in itself.
But a lot of a lot of our arms do that kind of thing for you.
Yeah. But. The the big advice there is not so much on the planning time front, it's more
check out that bottom.
If you're on Postgres, especially check out that bottom section first and look for dominant
issues before you spend ages reviewing the rest of the query plan.
So that's my, that's the third of the five.
And the fourth is then the bit that I sometimes mess up
and forget to do, which is work out which,
so if you then have to look through that tree
of operations, don't look at any of the statistics
until you've worked out where the time is going
or where the majority of the work is happening.
So Postgres especially is difficult in this area because it will report, I think MySQL does the same, I'm not sure about Mongo and others,
it will report all of the operations including the statistics of their children.
So you have to do a bunch of subtraction and some slightly more complicated stuff than that, sadly,
to work out where this time is going and it's very easy to get distracted by a pattern that you've seen before.
Like, you know, the obvious, the one that gets me and lots of other people is spot a
sequential scan on a relatively big table and you start to, you just jump to that and
think that's probably where the problem is.
- Missed index, we're gonna go fix that, yeah.
- Exactly, but in a big enough query, a scan of even a few hundred thousand rows might
be the, especially if it's returning most of them, might be optimal.
Or it definitely might not be the bottleneck.
There might be bigger problems.
So yeah, my advice is work out where the time's going or the work's being done first.
And then the last step is once you've found like, is there a big bottleneck?
Where's the time going?
Then narrow down into looking at just that operational, that cluster, that subtree.
Like and it could be a programming pattern.
It could be, well, this query is returning 200,000 rows.
when it gets back to the app,
the app is iterating over the first 10
and deciding it's had enough, right?
But meanwhile.
- Yeah, and yeah, exactly.
So yeah, I mean, it could be that.
- Yeah, and the fix would be a simple limit 10
and skip a certain amount.
Like you just put paging in the app as part of the query
and you're good to go.
- Yeah, exactly.
Pagination's huge,
especially once you've got good indexing.
can be so, so powerful.
But yeah, I mean, to be fair, we're probably,
this process is mostly for a query that you've
worked out is slow and you want to make faster, and that would be a great example.
But then there are also other application side things, so it might not be that any of
these queries are slow necessarily, but you're firing off
the typical N+1 example, you're firing off a lot when you
you don't need to, you could, instead of going, doing a lot of round trips, or...
Yeah, instead of doing like an eager join, eager load on an ORM relationship, you're
doing, I got a 50 of these back, and then I'm doing the relationship 50 times. Yeah.
Yeah, exactly.
Yeah, and that echoing out the database calls of your ORM, you'll just see stuff ripping
by like, why is there so much database stuff here? This seems insane. And they're like,
well, n plus one. That's the problem. Really quick, I want to just follow up on that. There
There was a question out in the audience saying,
what would you like to use?
Would you wanna use maybe Postgres and SQLAlchemy
or PsychoPG2, maybe the async version these days
would be really nice to use.
I said, well, you might also consider a SQL model,
but with either of those, SQL model,
both on top of SQLAlchemy or just SQLAlchemy,
the question was, can it handle getting millions of rows
back in a short amount of time?
Yes and no.
I like to hear what your experience is with this,
Michael, if you have any.
What I've seen is a lot of times the database
will really quickly give those results back
and actually even ship them over the network to the app.
But the deserialization of like, here is a record
and I'm gonna make a class out of it,
and here's a record and you're making a million classes
and then setting probably 10 properties on each one of them.
You know, 10 million assignments, like that's,
it doesn't matter how fast your database is gonna be,
just don't return 10 million rows into an RM almost ever.
Very, very rarely, let's say.
- Yeah, exactly.
The general advice I think is
if you're gonna be doing aggregation work,
try to do it database side.
They're designed to do that stuff.
They have incredibly smart algorithms built into them.
I'm sure there's exceptions,
but it seems to be the wise way of doing things.
I mean, of course, analytical stuff,
maybe you are presenting a report
that needs to be hundreds of thousands of lines long,
But there aren't many apps that there is a good argument
for that, I don't think.
- Yeah, and so many of these ORMs have a way
to limit the returned fields, right?
Say I only need the title and the ID,
or I need the title, the price, and the ID,
'cause I'm gonna do some local data sciencey stuff
with price or whatever, right?
But you don't need the full description
and all the user reviews or whatever, right?
So that can save a lot.
- That's such a good point.
And yes, I think I might even see that one more often.
So requesting, you know,
it's effectively the select staff from equivalent.
- Yeah, it is, yeah.
- You'd be surprised how many different things
that can affect the number of operations down that tree
that can be affected by the number of columns
you've requested is huge.
And it affects things like, oh, by the way,
the same goes for how many columns you ask
it to be sorted or ordered by. So it can make the difference between being able to do, for
example, an index only scan versus an index scan. And that can be a huge performance difference.
Just that one alone. In terms of aggregation, it can make a big difference. Yeah, there's
so many things you'd be kind of the knock on effects of passing that many data. We talked
about sorts. If you're having to keep that many rows in memory and each one's bigger,
they're going to use that work memory more quickly.
Right. It might fall back to one of those write to disk and sort it rather than sort
it in memory.
Exactly.
Interesting. Okay. I hadn't thought about the knock on effects as much. Let's talk a
little bit about fixing this. We've set up the problem, things you shouldn't do, but
how do you know whether you're doing them? So you've got a couple of free tools that
people can use that you might give a shout out to and then let's talk about
PG Mustard and maybe talk through some examples there as well.
Sure, so yeah there's I mean there's so many query plan visualization tools a
lot of the ideas we discussed also have them inbuilt as well and amazingly all
of them a lot of them seem to choose to visualize it quite differently so it's
quite be quite interesting looking through some of them but this the one
One you've got up on screen at the moment is a free tool written by a French team, a
Postgres consultancy called, I don't know how to pronounce it, it's either Dalibo or
Dalibo.
And they, this tool is inspired, I think it's even a fork of a product that was written
many years ago by a great guy called Alex Tatyants.
So a lot of people will recognize the Tatyants name from the URL, that was a tool called
Pev.
So back in the day when I started the two tools were Depez and Tatyants.
So Depez being the one you just flicked to briefly there.
This is like the original, this is the first one.
Let's start with the OG first then.
This guy is a bit of a legend in the Postgres community.
He's so helpful on so many of the chat forums.
And he's a Postgres DBA and has been for many, many years.
And he built this tool because the text format of Explain, I think he described it as unreadable.
So he's written a whole great blog series on maybe six parts now on reading Explain
plans and this tool he's using to highlight in red here kind of big problems or where
a lot of the time is going.
So that's that step four that we talked about, work out where time's going first.
So if you've seen the explain text, it's kind of indented and talks about the problem and
it has things about timing.
But here's an HTML table that has columns that shows you the timing and then the number
of rows is very important.
And then it has, again, still the actual text.
And it sounds to me like you think this is pretty important to still have the text that
people expect.
I think that text is super important for experts and people that have been doing Postgres performance
work for years.
So they can fall back to that text.
If the left-hand side's not helping them,
or in the case we're looking at right now,
there's a lot of red,
it might be tricky to know where to start.
You might fall back to looking
at the right-hand side for something.
Interestingly, that rows column
is a row count estimate multiplication.
So it's how many rows did Postgres expect to get
versus how many actually came back.
And that's super important for the cases we discussed
briefly around which join algorithms are gonna choose
or multiple other, join order is really important as well.
It'll try and filter out as many as possible
in the first few joins so that there's less data
to worry about in the future ones.
So if it's really far out in an estimate somewhere,
that's really important.
So yeah, this tool's doing some of those things
and doing it really neatly.
but that text format I think is super expert friendly.
People that are very used to the Postgres text format,
this is probably the tool they're gonna like best.
- Yeah, yeah, it's really nice.
Okay, and then there's the Dalibo,
other one that's similar, right?
- Yes, so this one's the one that's based on PEV.
So they call it PEV2, I think,
and it's open source GitHub repos PEV2.
And this is their hosted tool.
Again, free to use, open source.
And like the other one, this supports both text format
explains and JSON format explains.
So I think that, yeah, that's what they're saying
up at the top there.
This is much more visual as a tool.
So this displays the query patterns.
- Sadly, they don't have an example I can just pull up.
I'd have to like put it all together
and drop it in there.
Or do they?
I didn't see one when I clicked on that.
- No, it's at the bottom, they might have sample plans.
There you go.
So the gray button on the right.
- Oh yeah, yeah, okay, you're right.
Okay, let's just go with that one.
Cool, and then I'll submit it.
Yeah, how did it, oh, well, it's gray.
That's why I missed it.
Okay, yeah, this looks fantastic.
- Yeah, so this is a visualization,
very similar color scheme to the last one.
So you'll see things in red
that they're saying are really bad,
orange if they're medium bad,
and then yellow if there may be a problem.
Yeah, exactly.
Don't worry about it for now, get back to it later.
And then, so this is good for visual stuff if you're that way inclined.
The other thing, some people that are still extremely competent at this love the left
hand side especially.
So it looks unassuming but those little bars are kind of like a flame chart on its side
And they'll show you, in this case,
we're looking at the amount of time spent by each operation,
but you might be able to take buffers there
is a really popular way of looking at it.
That's a feature that's not in a lot of the other tools.
So that's a common reason that people really like this tool.
- Fantastic.
Okay, so these are both totally free, web hosted,
sounds like some of them are open source.
You can probably download them as well.
They look nice.
Let's talk PG Mustard a little bit.
So like I said, when I came across this,
I thought, "Wow, this is really a lot nicer
than the other tools that I've seen for doing this."
And maybe the best way to understand it would be to talk about two things real quick.
You can tell me how you want to talk about it.
But I think maybe talk about the performance advice.
What do you think about that? We could maybe go through that real quick.
Some of the things that are in mind for you.
Well yes, I know there's a lot listed there, but the short version is that we're also trying
to, we've got some strong opinions on the visual side of things, you know, the red gate
trying to keep things as simple as possible has not left.
So my co-founder of this is a guy called Dave, and we worked together for a couple of years
almost full time building this.
So we've also spent a lot more time than some of the others on building this.
There's a different level of polish and attention to detail if something is your job and the
maker or breaker of your success versus something you work on in your spare time because it'll
help a little, right?
Like there just is.
Yeah, well, absolutely.
And hopefully that shows but also the others have done an amazing job and there's surprisingly
few differences if you consider how much time difference there is.
But yeah, so the big differences are on the visualization front, we try and keep things
that bit simpler.
We're also a lot more opinionated.
We assume you are trying to speed up your query.
And that might sound stupid, but it's not the only reason you would look at a query
plan.
So there are reasons why that we might not be a good fit for people.
Let's say you're working on the Postgres source code and you're trying to implement a new
feature and you just want to look at some explain plans.
You probably want the text format and you probably really want the costs.
So the things that the plan is using to choose between plans, you probably want those highlighted
and we don't highlight those.
We hide those really far away from you because most of the time when you're trying to speed
up a query, that's not what you're going to be wanting to see.
So yeah, so it's super.
Yeah.
So which ones of these really do you feel like are both highly valuable, highly recommended
Or maybe some that are like, wow, I can't believe it would find that I would have never found that
Good question. Sadly, the most valuable are the index related ones
So as you mentioned that we call it we call it high index potential when there's a lot of rows being filtered
Versus the amount being scanned
um
So that's the big thing to look out for in the text format is when there's high numbers of rows being built not just on
sequential scans, but also if you've got an index scan that's still having to filter out a lot of rows
that we call that an inefficient index. But it's only inefficient for that query. It's
there's nothing wrong with the index. It's more that your query doesn't suit that. It's just
Postgres. That's the best one Postgres could use. Right, right. So yeah, the indexing one's super
valuable. Operations on disk we've already discussed a few times that comes up a lot and
and poor row estimates.
These are the most common.
All of the others are either rarer
or less valuable when you hit them.
But sometimes when they're rare,
they can be extremely helpful.
- Sure, well, once you pass the advice
of look for table scans and then add an index
and don't select star if you got a big result set
so it doesn't go to disk.
Those are pretty, not super easy,
but those are somewhat easy.
If it's not fast enough, you're like, well, now what?
I added an index.
It's not fast enough.
Now what?
Yeah.
One more that I'd love to discuss actually is something we talked about really early
on is the idea of trying to do less work.
So either not doing the work.
So what I think something I'm quite proud of is that we'll try and not highlight anything
if it's pretty optimal already.
Now that's if your query is pretty optimal already and doing a lot of work and still
not fast enough for what you want, you kind of need to rethink. You either need to think,
should we estimate this number? Do we need to materialize it and run it once every few
minutes and have every user look at kind of a cached version of it? So that's quite powerful.
And then the other side of it, if it's not efficient, it might be a sign of something
else going wrong. So in Postgres, one of the things that it gets beaten with is effectively
its equivalent of garbage collection. So the way it does multi-version concurrency control,
it ends up bloating the tables and the indexes a little bit, sometimes a lot, which is a
problem. It has automatic systems that people sometimes disable around that. But it also,
old versions especially, indexes on heavily updated tables for example can quite quickly
and easily bloat.
So pointing out, so if you include the buffers flag we talked about, you can spot when Postgres
is doing a lot of reads versus the amount of data it's returning and that's sometimes
a sign that for example an index has got really badly bloated and rebuilding that index, ideally
concurrently if you're on production, but yeah rebuilding that index.
This is how you fix it, Michael.
What you do is you put that little banner up there and you say we're going to be down
for four hours on Sunday.
So if you just don't mind, don't use our website on Sundays because that's when we rebuild
the index.
Those are often on the same site that have the really bad queries when they're not offline.
Anyway, yeah.
So we're trying to help them and trying to educate and trying to teach them how to avoid
those things.
But yeah, I know exactly what you mean.
So yeah, those are the kinds.
Yeah, that's good.
Yeah, and that I think the more I talk to people that really know this topic and they've spent 20-30 years on it
they focus mostly on work done and
Really trying to minimize and reduce that as much as possible
Less is more and all of that. So yeah. Yeah. Okay
So let's maybe highlight this through a couple of examples
I also have one maybe final tool to suggest you have a couple of examples that show how you can
Check this out over at app.pgmuster.com.
It will give you the visualization that you can dive into
and then the recommendations, right?
- Yeah, so this is just an example we've published.
Maybe not the best example ever, but it's one that I like.
- Sure.
All right, well, maybe let's just talk
through the three examples here.
Maybe really quickly, the UI.
So you've got these nodes that are connected,
sort of graph theory-like,
And you can expand and collapse the sections to get details about very much the indented way of the explain text, right?
Yes, so we're trying to mimic the text format with that.
So people that are used to text format, this is the exact same structure but with less data.
We're doing some of the calculations there, so those 200,000 that you see there, that's the number of rows being returned at each stage.
And one thing you saw was that those two of those operations were hidden by default at the beginning when you first loaded it.
That's one of the opinionated things we're doing. By default we'll hide subtrees that are really fast already.
So you can, in this example, it's not great, you know, there's only four nodes.
But you can get queries that have hundreds of nodes, and even ones that return quickly.
So hiding far sub trees is a very opinionated thing to do,
but I find very valuable.
- Sure, I like it.
And it's about find, you know,
bring your attention to the part
where you can make improvements.
- Yeah, exactly. - Where the time is being spent.
Yeah.
- So yeah, so that's what we're doing.
At the top there, we have a timing bar,
which is a bit like a flame graph, but viewed from the top.
So kind of like a bird's eye view on that.
Maybe this one's got a little better breakdown.
- Definitely.
So you can see the width of those
is the amount of time spent
and they're ordered by slowest to fastest.
So the idea is to quickly show you
if there's one dominant issue
versus maybe quite a few
that are taking a similar amount of time.
- Sure, so for people listening,
there's this tree of nodes that you can explore
to see what's happening,
like a gather merge or sort and so on.
and there's the timing and the likelihood
of good recommendations.
They all have numbers and then across the top,
you can see, well, number one was the worst offender
of performance and then number zero and then number two.
- Yeah, I've just realized how un-podcast friendly
this might be.
So apologies to people listening.
- Well, you know, I'm gonna link to these examples
and people can go check them out.
- Yeah, well, yeah, thank you.
And then, so the last thing that we're doing,
so the other tools do that quite well, in my opinion,
in terms of showing you where the time's going.
I prefer a very visual way of seeing that,
and that's similar to the Dalibor one we looked at.
You can see quite quickly where those time bars are going.
The thing we do additionally is these scored tips.
So we're trying to look for every issue we know about,
and we're trying to look at them
on every single operation we see,
and then we're calculating roughly how much time
we think you could save if you fix the problem
that we think might be there,
and then scoring that so that we only show you
the top three, maximum three, in this case, just one.
So even in this case, you can see that most of the time is being spent doing 200,000 loops
of an index only scan.
So something that's probably extremely fast on its own, but the fact we're doing 200,000
loops of it adds up quite quickly.
Yeah, exactly.
And the real problem is a bad row estimate.
So in fact, it's the exact example we were discussing earlier.
picked a nested loop because it's not expecting to have to do that many loops.
But in reality there were nearly 200 times more rows than it was expecting and that was
probably a bad choice of join algorithm.
Right, okay.
Yeah, so if I click on, if you just open it up, right on the left it says top tips 4.6
out of five likelihood of improvement,
operation zero, row estimate out by a factor of 191.8.
Then I can click on the details here.
If I click on the node that it pulls up,
it says that same thing, you can expand it out
and it gives you a nice human English language description
of that and then the operations,
the nested loop it describes like what that is,
gives you some stats on the buffers
and then the operational details
like it was an inner join and how long it took and so on.
- Yeah, absolutely.
And actually, it's a really good point
on the operation descriptions.
I also spent ages, probably a couple of months solid,
making documentation page or a glossary of terms
that you can come across in WordPress.
- Writing a manual, but in like two paragraphs at a time
to be delivered.
- Yeah, so we put a lot of them in the product,
but we've also put them online for people
so they can freely access them as well.
sort of yeah those are all available on glossary on our website as well.
Yeah okay so in this example what do I do like it tells me these things what is what
should I go do to make this better is there anything I can't?
Well exactly I mean here's what we're looking at.
Vow estimates are tricky but Postgres has a thing called analyze where it will gather
statistics on each table, it will take a sample of them, it will look for most common values,
so it can kind of estimate joins accurately, that kind of thing. And it might be that analyze
hasn't run in a long time for whatever reason.
I see the data has dramatically changed since it last took stats. So it's using out of date
stats to make its decisions.
Exactly. A really common cause of that is if you do a major version upgrade in Postgres,
doesn't bring along your statistics so you do need to run and analyze as part of your
upgrade so that's really important.
But then if you, there's a learn more link here as well with, it will go into quite a
few more things you can do so if it's not analyzed there's a bunch of other ways you
can teach Postgres or ask Postgres to gather more statistics so if it's a heavily skewed
column. Yeah. Yeah. Anyway, so we've gone into quite a lot of detail in that blog post.
But yeah, there's a bunch. I love it, though. Because if I if somebody told me this, I'm
like, well, I don't really know what to do. To be clear, I'm basically a beginner on Postgres,
but not on relational databases, especially not on databases in general. Right. So I might
look at this go up. Great. But does Postgres even have a way to fix this? Right. And then
you're right here next to it. You're like, OK, you just run this command and you'll probably
And I think that's super valuable,
because it's one thing to say, "Here's your problem,"
or, "This is where the problem resides."
It's another, like, "Now what?"
- Yeah, and these days, engineers and developers
are expected to know, well, there's so many people
that started back-end, and then they kind of went full stack,
but then they have to worry about the database as well.
And it feels like the surface area is expanding
in a lot of cases.
So we meet a lot of extremely smart developers
developers that have been doing back-end work for decades that only have to deal
with Postgres every few months or maybe even every couple of years.
Those people, we kind of want to help them because they know what
they're doing, it's just this information is not top of mind right now.
So that tends to be who we can help most easily and we're trying to
make it a bit more beginner-friendly as well.
Yeah, that's fantastic.
Okay, so that's example one, which I'll link to.
Example two, we've got a more significant challenge here, more stuff happening.
This one we're throwing away too many rows.
Yeah, so well this is a great example actually of a tip I didn't mention, but it's probably index related.
So we're throwing away rows at the end here, going down from 45,000 in the second to last operation, down to 100 in the last one.
last one, if we could filter down to ideally a much smaller number very early on, we could
avoid, there's a couple of large sequential scans there, a couple of hash joins, all on
tens of thousands of rows of data.
In this case, there's no big filter until the end, so we're not giving an index potential
tip, but we're saying if you can, yeah, so in fact, we'll actually see on the sequential
scans we're saying index potential is zero.
Because no rows are being filtered there.
But the late filter at the end, the rows discarded, is depending on exactly how it's discarding
those and what it's doing, I think I remember from this example, we're actually sorting
by columns in two different tables. So we're ordering by a column from one and then ordering
by a column from the other and neither of those columns is indexed. So if we could get
the data from each of those in an ordered manner, this would probably be a lot faster.
But it's a tricky investigation because we don't know from what Postgres is telling us
here exactly how those are distributed and how many of them are going to be appropriate
at each stage.
Yeah.
Another thing I find value is not necessarily the advice, but the non-advice.
Like you know what, there's zero index potential here.
This is fine.
So don't look down that path.
Go look somewhere else.
Right?
Row estimates, perfect.
Operation happened in memory.
Don't worry about those things.
Go find the problem somewhere else.
Yeah, exactly.
That was actually user feedback quite early on.
People wanted to see the things that were not an issue.
So it's been really helpful working with people and trying to understand what they do and
don't find.
It wasn't something I thought of, but through feedback, it was really helpful to know that
is exactly what people want to see.
Yeah.
I mean, I think this is super valuable.
So people want to check out PG Mustard.
It's worth pointing out that it does cost money, right?
It is a product.
- Yeah, sorry, for our sins, it's commercial.
But if anybody's, if there are students listening,
for non-commercial use, we give it away to people.
All you need to do is just contact us.
And yeah, there's a couple of other reasons
we give it away for free.
So yeah, feel free to reach out.
But we hope it's not too expensive as well,
especially if you're doing this for work.
Yeah, it starts at 95 euros per year for a single person.
So hopefully affordable.
- Yeah, and I think it's,
I don't mean to make that derogatory, not at all.
I just wanted people to know,
like the other ones we talked about were free
and open source and this one, it is a product,
you do pay for it.
But boy, if you could save a database upgrade
to a new machine, or if you could save,
you know, spending $100 a month on a larger server
in the cloud every month because this thing help you find
that, oh, you actually don't need it.
You're just doing it wrong, right?
Well, $100 once seems like a pretty good bargain, you know?
- Yeah, absolutely.
And I mean, not even talking about PG Mustard,
but tools in general,
I think developers probably underestimate
how valuable their time is,
not just from a salary per hour perspective,
but also from a, if your management team
or your boss or whoever could choose
whether you would ship the next year's worth of features today,
and they could pay, let's say, 10 times your salary,
a lot of them would actually take that trade.
I think every company in the world is hiring developers.
Most people have a huge backlog that they want to be building.
So this is an awful example in terms of spending money to solve that,
but even if it comes to more expensive tools,
I think you'd be surprised how much,
if they can make you more efficient,
companies will probably think of it as a better trade than you do.
Yeah, I agree. I do think that we often muddle along using
something that we should be better off paying a decent
amount. I'm talking about like $2,000 a month subscriptions of
some insane tool that may or may not be helpful. But also, just
from a joy, you're like, do you want to go to work and work on
stuff? Or are you like, God, I'm in the log files again. I just,
I can't do it, I can't do another day of this, right?
Versus, oh, like I really have a good understanding
I'm building something that I'm proud to share with my friends
look how fast and zippy this site is
versus, yeah, we're working on it, you know?
Like there is a pride and a joy aspect of being better
and if tools get you there, that's great.
All right, speaking of tools,
I think we're about out of time for this
but final two questions before we get out of here.
I think I want to focus on database GUI editors
rather than text editors for this episode.
And we talked about a bunch.
So maybe we could just quick reference back to that.
But favorite database GUI tool these days?
- Oh, I'm going to give it a shot.
- Do you want to go with PSQL?
- No, I'm going to risk the wrath of the Postgres crowd
and say Archetype.
- Archetype.
All right, yeah, Archetype looks pretty neat.
And then, normally I ask about Python extensions,
how about Postgres, or Python packages,
how about Postgres extensions for a notable one?
- Yeah, so I think the Postgres extension
that everybody should install really,
if you're using Postgres of course, is pgstatstatements.
If you're using a cloud provider,
it's probably on by default already.
So in that case, I'd give a shout out
to my favorite, autoexplain.
- Cool, awesome.
All right, Michael, well, thanks for giving us insight
to all these different things we can do
to make our databases work better, especially Postgres.
And thanks for making PG Mustard.
I'm sure it'll help a lot of people.
Final call to action,
people wanna get better with their databases.
Maybe pick up some of these tools or these ideas.
What do you tell them?
- Ooh, don't be scared to learn.
It probably looks more intimidating than it is.
The Postgres documentation's awesome,
so a lot of the other databases out there.
I know it's a bit of a meme and a joke these days,
but do read the documentation.
It's written, it's been written and revised over years
by some really smart people,
but it's very approachable still.
- Fantastic, all right.
Well, thanks a bunch.