Just Use Postgres! with Denis Magda

Welcome to the episode of Flying Highway Flutter. I'm your host, Alan Wyman. You can see my

lighting is a little bit not that good because I was rushing to come to the show. We have

a great guest on here. We have Dennis, Magda, as you can see. I didn't even prepare

your slide for you, so let me go ahead and fix that wire going on there. Maybe while I'm fixing

that, do you mind to kind of give a quick introduction about yourself? Yeah, so the reason

why I hear your folks going to hear me on this podcast, we will be discussing a book called

Just Use Potsgres, which was published, I think, when a couple of months ago. I'm the author

of this book and during my day-to-day job and activity, you can think about me as a software

engineer, who is focused on the system engineering platform engineering, so I work a lot with databases,

I work with the back-hands as well as front-ends. So my goal is to understand the entire stack,

which will let us build reliable and fast applications. And when you need to build reliable and fast

applications, you need to know pretty much all of the components from the top to the bottom.

So that's just my quick intro. So I actually had the most amount of time I actually prepared for

your episode and I did a lot of research and you've been doing quite a few podcasts about this

book and I thought it was pretty interesting to actually listen to your background. So your background

is that you were, I guess I could faithfully say you were pretty much a very heavy Java person.

So I'm happy that you were in the internals of the JVM and also the JDK, is that right?

Yeah, my first five-plus years, if not more, probably within 10 years,

at the very beginning of my career, I worked with Java a lot and I worked for sound microsystems,

for Oracle, not just using Java, but also building Java virtual machine, Java development,

JDK, JDK. That was not for their standard edition, which you usually run on servers and desktops,

but that was that was the Java for the mobile phones and for the embedded devices.

So first I was building JVM for, like there was the JVM called Java EME mobile edition.

And it was quite popular. Some of the folks out there can remember it. It was widespread

until Google released Android, which is also a Java based JVM, but it was developed by Google.

And after that, my focus switched to the embedded devices, I was on the team, I was leading the team

that whose goal was to take JVM and JDK and to make sure that it works on different microcontrollers,

different low-level systems. And that was a fun exercise because you were kind of enabling

JVM and JDK for some circuit board, but most of the time you have to code and see,

or C++. So this is how it looked like from inside. But after that, after learning Java and

after working with Java a lot, I stick connected to the Java community,

but I decided to broaden my understanding of the technology and I switched to the world of

databases. And they, and I spent probably the rest five or seven or eight years, I don't even count

working with different databases. And Postgres is one of them, and Postgres is my default database.

And eventually I decided to share my experience and wrote the book that we are going

to discuss today a little bit. The book is something that's quite interesting. When I first heard

a title, I was thinking like, okay, this is going to be a very heavy, strong, like, okay, drop

whatever database you're using, use Postgres. But that's actually not the style that you wanted to go

for. You want to go for more of like, if you're already using Postgres, or you are open to change,

or you don't know what to use, Postgres is definitely something that you should take a look at,

not just take a look at, as a relational database, but as like a, just all around, like,

kind of like, I guess I can call it a Swiss army knife of tools, right? Because it has so much

stuff in it, like, it's insane all the stuff it has in it. I was just looking in this morning,

how I can better use the, for many of currencies, because I want to be able to, from the database,

format a currency. But it, sadly, it doesn't work the way I wanted to. I want to be able to pass

an currency symbol and get it out because my application, I have like Hong Kong dollar,

US dollar, Chinese RMB euro. I have all these currencies I have to deal with. But yeah, that's kind

of besides the point, but like, something that you touched on in, I think you're going to touch on

the book. I haven't gotten that far yet, but I think you definitely went over in a podcast. Another

podcast is PubSub. I was actually invited to be on another podcast because I actually used

Postgres PubSub on a database trigger. And like, all these people, like, I presented it again,

like, several years later at a local dev meetup. And all these, like, developers, like, we're like,

so excited. They're like, what? You can do that. I didn't know that Postgres has a PubSub. I always

reach for Redis or some other kind of tool. Is there something in particular about Postgres that

you think is like so surprising, like a feature like this, or you just kind of like, this is,

of course, it's very normal because you've been using Postgres for, I mean, I don't know how long

I'm afraid to ask how many years now, because it's been, wow, no.

Yes. Postgres, if you talk about the title, why this title first? And then we can talk about

PubSub and other capabilities. The title, the title is not my invention, just your Postgres,

but obviously it draws attention. Even if you don't use Postgres, you will not, you know,

just pass by this book if it's title, just your Postgres, because it sounds provocative.

And many, many, like, when you read such a title, your instincts will tell you that, all right,

probably this book is how Postgres, you know, became a Swiss Army knife. But once you start using

Postgres, once you kind of become a member of that Postgres user community or co-postgres community,

you will realize that just use Postgres doesn't imply that Postgres can replace every other

database. It's not the case, it's not possible. But it's just a hint from the community,

just a good piece of advice saying that Postgres is very capable database. Most of the people

know Postgres as a relational database. And when you think about relational database,

kind of thing that a relational, an RDB mass is only good for OLTP workloads, transactional

workloads. And that's it. And then if I need cash, and then if I need, you know, vector search,

and then if I need full text search, I need to switch to other databases. But the just use

Postgres implies that, come on, before you make the decision, and that can be a good decision to

use another database. Take a look if Postgres already does this for you. Like with these

Pub-Sub capabilities. Yeah, Postgres has special extensions. One of them is PGMQ,

Postgres MessageQ, which is just an API, abstraction layer on top of the Postgres table structure,

partitions, and some of their special functions and appulators. But with that extension,

if you just need a simple Pub-Sub system, MessageQ, like, or JobQ, when some job task is

port, or message is port, and then consumers read, and executors execute the task,

then Postgres is good enough for that. You're not just Postgres. Pretty much you can build comparable

comparable systems using other relational databases, such as MySQL or Oracle. But why many

folks turn to other specialized solutions, or databases, such as Redis, when they think about

Pub-Sub, is because those databases made a great job on introducing special APIs, because we

develop, or if I want to use a Pub-Sub system, I probably just want to see some APIs that look

like, I will be exchanging messages or sending some tasks for the execution. Extensions, like

PGMQ and Postgres, they introduce that abstraction. And you just start your database, and then you

use familiar APIs that will create Q, and then you will be putting messages in the Q, you will

be reading the messages in the Q, but under the hood, everything is stored inside the same

relational tables. Everything, you also will be internally, it will be using transactions,

making sure that you do not read, you know, the same message twice, so that everything is up,

which is updated, it is updated consistently, and so on, so forth. This is one of the examples.

But at the same time, what I'm warning the readers about in the book is, don't be fanatic, because

if, let's say, you were successful, right, you use Postgres, and it's okay to say just use

Postgres, because explore Postgres capabilities before you switch to something else specialized.

But if, let's say, Postgres doesn't really work for that streaming use case, when we talk about

streaming, you know, something that is usually provided by Kafka, you use stream data from a bunch

of readers, then you have a bunch of consumers who need to read the same messages, do something,

more kind of specialized, more advanced architecture than probably instead of trying to build a

bicycle on top of Postgres use Kafka instead. Just this is my rule of thumb. So just use Postgres

to summarize here is just a good piece of advice to you, because if you already know Postgres,

if you're learning Postgres, then consider it for other use cases before you use specialized

databases. Why? Because the fewer components you have in your architecture, the better.

Once you go in production, you need to support not just Postgres, but other specialized system.

But if during that POC, during that exploration phase, you see that, all right, now Postgres,

Postgres kind of doesn't work that well for this full tech search use case, and I need

definitely elastic and go for elastic. But also there will be scenarios and you will surprise

to see those scenarios when Postgres can handle your full tech search use case exceptionally well,

and you don't need to run a standalone elastic cluster. Basically, this is how I see the book,

and this is the main objective, and this is how I usually explain the just use Postgres

title. Does it make sense to you? No, I heard it, because I listen to you on

at least a different podcast today, and I think you've said it many times, basically the same thing,

and so I understand what you're trying to say. The title is a little bit cocky and arrogant,

but I mean, I think it's fine. You want something kind of shocky, even though we already understand,

when you read the book, when you listen to what you want to say, it makes sense, but it's definitely

something that shocks you. It's like, why should I just use Postgres? It's a bit more than that,

I remember, that's something that surprised me a lot when I started learning more about Postgres,

all the functions it has, and that's what made me start to realize, before I reach for another

tool, I definitely need to actually take a look to see, can I actually do something? Can I accomplish

the same task without Postgres? The Pub sub thing, I specifically had some of the microservices

architecture quite a few years ago. I had to communicate from one system to another in

different programming languages, different platforms, and you figure, okay, HTTP is one way to do it,

but I think I couldn't do it because they were in containers and totally separated for some reason,

I'd spend many years, oh, I'm sorry, I know what it was, I believe it wasn't using Kubernetes,

obviously I could use services and things like that, but I decided, okay, why don't I just

push the message over Pub sub and then let it handle it? I think it was some kind of AI crunching

data thing, so it just made sense to pump it over Postgres and it would take the message and

then do what to do and kick it back. It's definitely something that's that's working me out.

So was there some sort of a job queue? Not just the message queue, right? Someone would post a

two task takes and then some other component would need to pick it up. Yeah, so for this one,

it was something like a job queue, but it was just only Pub sub, it was just send the message

to crunch some data or something like that. It's been so long, I can't remember, it's been over

five years in many projects since then. Postgres as a message queue, I thought that was interesting

because I never thought about that, but I do use this package called Open, which is available,

it's written in a lecture, but it's available for like Python and a lecture applications,

where it basically is, it's a messaging queue, like it's a job queue. You stick a job in there

and runs it, you can also give it a crown like interface where it can run a job every

X amount of times it's got, it sticks all the jobs right into Postgres and it, and they use a lot

of Postgres features, I haven't dug into what they're exactly using. You may have even heard of it,

I know it's quite, it's somewhere, no, no, no, does it, does it run, does it run as a Postgres extension,

does it run inside of Postgres or is it just not okay? It's outside Postgres, it's just heavily

used as Postgres. I mean, so I got introduced to Postgres because of Rails, like a long time ago,

and then I switched Rails over to Elixir and I've been using that a lot for a lot of applications,

they just naturally went straight to Postgres because the guy came from, the creator of Elixir came

over from Ruby and, you know, and also like for relational databases, I mean, it's, yeah, we both

really like it compared to others. I remember my first issue I had was like with MySQL, we're actually

I was saving, I don't know if you know or not, but I'm based in Hong Kong. And before I used to live

in mainland China, and something I noticed right away was that MySQL is at that time, you can give

a data and it would just store it as opposed to I believe Postgres actually has a protection so that

it doesn't just store it like garbage because we end up storing some bad characters somehow.

And so like when I wanted to read the data, it would just like crash because the data was basically

corrupted but still got saved at MySQL at the time. Yeah, I don't know about that one, but Postgres,

yeah, Postgres is a relational database whenever you update a new record or you insert a new record,

let's say that when you insert a new record and you record gets created and stored in the

database, if you update an existing record like your bank account, it doesn't overwrite the record

in place. Instead, it does a full copy of your existing record and introduces, you know, the

acquired changes. But the previous version of the record still exists and it still exists in

the same table. When you do this, you know, all over again, you keep updating the same record

by bunch of records. The previous versions of the records we will be existing in

database, it will be consuming some storage space. And how does the database deal with those dead

records or dead tuples like basically garbage? It has garbage collection. So same with Java, same with

JavaScript or gather other programming languages. Postgres and many other databases, they use the

garbage collection because this is this. Postgres does. Postgres creates those versions for the sake of

transactional consistency for their temicity. It wants to see. He's kind of, regardless of all of

the modern general purpose capabilities Postgres has, there is one use case that Postgres,

which Postgres has to accomplish, you know, not just good enough, but exceptionally well,

and this is transactions, transactional workloads. That's why this is how the system is designed.

Speaking about pops up, my story is why this chapter about Postgres as a message cure. So in the book,

we have, yeah, for those who are listening to us. And in the book, in the very end of the book,

we have the last closing chapter called Postgres as a message cure. Initially, I did not want,

I didn't plan to add it to the book because I was also skeptical, probably like same as you,

Ellen. You didn't know about this use case, but many people who know about this case, they

might be skeptical. And eventually, but why, why I changed my mind and why the chapter appeared

in the structure. When you write a book, who is many, you have several phases. And one of the

phases is called MIP. It stands for Manning Early Access Program. And what happens, let's say,

that you as an author, you crafted the first four or five chapters. And once the publication

teams decides that, okay, those chapters are good enough. They will release your book draft

as an early access version with everyone. And you will be able to read it electronically.

So this is what exactly what we did with the just use Postgres book. Once the first chapters

were completed, we released it in this early access program. And I basically shared the news with

everyone on social. And I went to read it. On the read it, we have a very vibrant Postgres

community, Postgres, Postgres SQL sub-ready, where I just posted guys, like, look, this is the book

I've been working on. This is the current table of contents. And the first two chapters are

available. And this is what I'm planning to walk on next. Just looking forward to your feedback.

And I genuinely wanted to hear the people feedback. And then what happened is there were a couple of

folks who posted some feedback saying that, all right, yeah, we like the book. We like, you know,

the where where it's going. But then it's like, what's happening? There is one, you know,

topic which is not covered. It's missing like Postgres is a message queue. And they would explain

why this topic would need to be in the book. And under those, you know, two or three comments,

which were in the discussion, I saw, let's say, like, like dozens and dozens of thumbs up.

And after that, I decided, all right, look, probably the guys who are asking to include

public Postgres as a message queue in the book are very knowledgeable. And they convinced that

Postgres can't not just tackle this use case, but it can support it exceptionally well.

And I changed my mind. I went to the publisher and said, like, Jonathan, Jonathan is my acquisition

editors. I said, like, Jonathan, like, we got to, we got to add this chapter back to the book to

the book. We added this chapter. And then that was my last chapter. I studied much more than I

did for the other chapters in the book, but I enjoyed it. And after finishing this chapter now,

I'm a big believer that, yeah, you can use Postgres for as a message queue for pop-up system,

but also be mindful that it doesn't replace Kafka, doesn't replace any other specialized streaming

streaming technologies. But if you're a Postgres practitioner, if you've already experienced

Postgres and all you need is just to add some, like, as you Ellen said, some communication between

different components of your system, that communication might be possible to implement by using

database as a queue. And Postgres is a friend here, which always makes me kind of nervous,

because I mean, no database can be like great at everything. Like I, I went to a database launch

party about, I don't know, five years ago or so. I was a bit skeptical because I wasn't quite

sure what kind of database this was. Nobody was actually able to answer the question. I'm talking

about like, is this relational? Is this document? Is this, you know, whatever? And I never really

got an answer. And I asked the founders, I'm like, like, what is this database good for? And they

replied everything. No way. One database can like be great at every type of database kind of thing.

Oh, that Postgres does come pretty close. I mean, it's, you can store JSON B documents like,

like, what do you call that? Like, like, like a document. Yeah, Jason, yeah, yeah, Jason, Jason,

Jason is a data type. Postgres, yeah, speaking about Postgres, I usually explain Postgres as a

general-purpose database. In general, purpose implies that, yeah, you can use it for various

use cases. And use case can be relational date, like a transactional workload. It can be a

generative AI application, like the vector search. It can be full text search, etc. But that doesn't

mean that Postgres is specialized in those workloads. Let's say, let's take, let's take full

text search as an example. Postgres has built-in data type and has built-in functions and

appulators that will help you to take your text, kind of convert the text to a list of like

SAMs, like units, units of language, and then perform the full text search over the data.

The default post in Postgres has some of the specialized indexes, built-in specialized

indexes for the full text search data type. Can use the gene index and I think the gist

index as well, if my memory doesn't fail me. However, when you compare this to a

elastic search, there is a specialized ranking algorithm which native Postgres misses.

It's called the BM2 ranking. So this BM2 ranking algorithm is basically what powers

elastic search and other specialized full text search systems. And it's quite, you know,

and Postgres did not support it. And this is why, you know, sometimes people would try to use

Postgres for full text search. Sometimes it would work, sometimes it would not, depending on the

data which you store. And they would switch to elastic because the elastic, you know,

did it better because of that BM2, BM25 ranking algorithm. And how like this, but then

Postgres can always catch up. Right now we have a couple of extensions. One of them is

PG text search and they implemented this BM2 25 ranking algorithm for Postgres so you can install

it. So this is basically what you have with Postgres. Yeah, it was designed as a relational

database, but it was designed as a relational database which can be extended. It's very

extensible or extendable. And the Michael Stonebreaker, he is like the original creator of Postgres.

It was because Postgres was born in Berkeley University, California. It was the research project

Postgres, basically stands for post post ingress. So there is the ingress database. What Michael

Stonebreaker noticed, there is a wonderful kind of interview with him where he explains everything.

What he noticed is that ingress was very powerful relational database, but eventually the

times were changing. New use cases would emerge. New types of applications would need to be built.

And those new use cases and applications require different data types, require different

index types, et cetera. And it was hard to catch up with all of those demands by, you know,

enhancing and building the ingress database engine. And then he envisioned that like the next

database engine, which became Postgres, it needs to be not just a relational transactional database,

but it also should be easy to expand it, extend it if it's necessary. And it can be extended not

just by the core database team, but those, but also by the people who want to introduce their

own custom data types or custom functions and custom indexes. So some of those capabilities,

they eventually get back into the core database engine. JSON is an example. Postgres supports

JSON and JSON be data types. And this is part of the core database engine. You can store

JSON documents. There are also specialized indexes and functions, which will let you query them.

But it doesn't at the same time. And this is, this is basically shows how that

architecture, the concept, which Michael store breaker put in place, you know, works out today.

This is why Postgres is so ubiquitous this, this is why it's so widespread because

Postgres core database engine is, is very, I would say conservative. They don't, they do,

it, it, it, it's, it's, there is a lot of innovation in the core database engine,

but they do not do this rapidly. For instance, if you want to improve something on the

replication side, it can take a couple of years because they just, we release Postgres every,

Postgres version, every year, major version. And if let's say your capability doesn't make

into this release because like some of the folks didn't manage to review it, didn't have time

to review it or because there are some of the gaps which needs to be addressed. All right,

it will get into the train the next year. So this is how the community builds Postgres.

But if you want to introduce something, something much more rapidly, you have extensions.

Take a look at the PGVector. PGVector is an extension for, I would say, for the

Generative AI applications. It allows you to run the vector search, the vector similarity

search, where you embedding, and it also introduces a special data type called vector.

It was created actually before, it was created before, there are, before, before,

before everything became so popular in ubiquitous, it was used for some of the, yeah,

search over the vectors and those vectors were not embeddings, which are generated by

LLMs. But once, the chat GPT was released, once people started building,

specialized vector databases, folks who use Postgres, they say like, well,

Postgres already has this and it's not in the core database engine, but we have PGVector

extension and the PGVector extension maintainers that continued improving this extension.

So it has some of the contemporary index types that it's been improved, it's been developed.

So this is an example, but if let's say you have, because if you have Postgres right now,

and let's say Postgres is released only like once a year, if Postgres did not have that

extensibility, then the development community would need to wait while all of those,

let's say specialized indexes and vector types would be added into the core database engine,

which could have taken years, especially if to consider how Postgres is being developed.

But you have extensions and everything in Postgres can be extended, you know,

really quick, you don't need to wait for anything added to the core database engine.

Just go ahead and develop it and release it as an extension.

So this basically explains why Postgres is so popular and why we have these people,

like me saying just use Postgres. When I'm saying just use Postgres, my hint is just take a look

if Postgres, you know, will serve you well for your use case. If not, then just go and use

something else. Yeah, you touched on a lot of really interesting topics in there. There's

no one I really want to bring up, but I forgot it now, but there's actually another one that's

even more important for people who are kind of new to Postgres, who want to give it a try,

and they want to store JSON data. Maybe you should have had a chapter of the book called

Just Use JSON B, because I remember Cracky, JSON is like a very quick iteration of holding JSON

data. What I remember is like people kind of described it as we had to get some JSON in the system,

and now that we thought about it some more, JSON B is the better one. Just try not to use

JSON anymore. That's what I kind of have gotten the gist of. Is that still the case?

Because that's what I hear. Yeah, we do discuss, we do discuss JSON versus JSON B in the book,

but yeah, we do have JSON being Postgres, and this data type should be used by default,

but it doesn't mean that we do not need JSON, the JSON type. So what's the difference?

The JSON B, whenever when you will, let's say you have your record, you insert your record,

and one of the columns is of the JSON B type, it stores you some document. Let's say,

it can be some metadata about the applications on your screen, placement of the applications

on the screen. If this column is of the JSON B type, then Postgres will kind of read it,

and it will optimize it for the storage. Why it does it? Because if then you're going to

read the records and filter the records by the content of the JSON column, let's say that in that

JSON column, you have the field called display under the display, you have a number,

right? And that can be, let's say, one, two, three displays. And then let's say you run the

query like select everything from this table where display number equals to three.

And when Postgres uses JSON B, all of those records are already kind of analyzed

when you insert the record. And then it will be much faster for Postgres to retrieve this data

from the database. It doesn't need to read the entire JSON document and diserialize it.

It will be able to get to this data quickly, especially if you use specialized indexes.

And those indexes can index the column of particular fields like display or

of the entire path, such as display number. However, the JSON data type, what happens with the

JSON B, whenever you insert your JSON B column and you then want to read it back as is Postgres doesn't

guarantee you that you will get it in the same shape and form. For instance, if in that JSON

document, you had some, you know, a couple of duplicate fields, like at the top level, let's say,

you had a field called display name and then somewhere down below you had the same name,

the same field with the same name, display name, then Postgres can remove this because it will

tell her, right, like, why do we have this duplicate? Or it can kind of rearrange some of the fields

in your JSON structure, thinking that it's just it's okay to do for the sake of, you know, performance.

But for some of the applications, it can lead to different errors. Some of the applications might

need to have a display name column in the top root level, you know, twice. I don't know why,

but there might be some of such use cases or you might need to, you might need to have this

kind of a specific arrangement of the columns in your JSON structure. And if this is your case,

then you need to use JSON, the JSON type. I use, I use JSON type for the scenario when let's say,

I'm, first, I'm not planning to query this JSON structure in Postgres. Let's say that I have

some metadata object and I just put this metadata object in Postgres. I'm not planning to filter

I'm not planning to query this metadata object. All I need to do is for Postgres to take this

object, store it for me. And then whenever my application needs it, I will just read it back.

So in this case, I use just the JSON data type. Also, I use the JSON data type when I'm dealing with

the right intensive workload. Let's say that again, I have this prerequisite. I am not going to read

the contents of this JSON object on the Postgres site. And I'm inserting a lot of the records

between the JSON structure. When are you insert, let's say those JSON documents in the JSON type,

then Postgres doesn't do any preprocessing. It doesn't try to analyze your JSON structure,

doesn't try to do anything else. It just takes your data and stores it in the storage engine.

So this is the use case for the JSON. And then for other, but for the most of the use cases,

when you want to query the JSON structure on the Postgres site and you want to index it,

then I would use JSON B. So this would be my advice. So both data types exist for reasons.

And as everything in the software engineering, there is some trade-off. That's why Fox

will be reading the book, who will be learning and using Postgres, but if you're into different

resources. Don't dump the JSON type just because everyone says that JSON B is better.

JSON B is better for particular scenarios. Yeah, there might be much more scenarios where

JSON B is suited for, but there is always, you know, there are always scenarios where JSON will

suit you better to study, study what the database offers. That's quite interesting because I

remember hearing that nearly everywhere. It's like just ignore JSON. It was a quick implementation.

And then now that we have JSON B, forget about JSON. So it's good to hear that. I didn't actually

know that there's actually any good use because I'm always thinking like this morning I'll

listen to your podcast. And I was like, why we still have this one? Like there must be a reason,

I mean, maybe backwards compared, like Microsoft, like never get rid of some old API because something

may break from many, many years ago, right? Everybody's kind of relying on, you know, I think

Linux said it himself. It's like once a bug becomes something that people rely on, it's now become

a feature. So you can't really remove it, you know? That's what I thought about the JSON type.

So it's good to hear like now. Actually, I made like you said, you get some really significant,

like actually useful use cases for JSON. So it's good to hear about that. The,

the other thing I thought was really interesting because I was reading like some,

I think you had some tips in the back of the book, which I was just kind of going through.

One that I think is very useful in remind me of a website. I'm sure you've heard it before. It's

just use the index or something like that. Do you know what I'm talking about? There's a website

with this guy has always kind of. I look at it. I look at just use just use the index look,

the Star Wars reference. So like you remind me of that. Yeah, because I recently had a performance

issue. I had to help somebody deal with. And I was like, wait a minute. And then I use, you also

have talked about using explain, which is so helpful. Like I just use explain just to like make

sure. And also I showed him, you see this one, this is a sequential stance scan. That means one by

one for all these thousands of records we have, it's checking one by one. And then I added in the

database. Now we ran the, sorry, I added in the index, we ran the explain. I was like, now you

see how much faster it is. And you see it's now longer doing sequential. It's going straight to

the index, finding everything very quickly. The client was very happy because this is like a long

time problem, where it's taken like 30 seconds, just to just to paginate like 10 records.

The book I have that last appendix, where I list my top five demisations,

tips for application developers. They explain statement in postgres is one of them. So basically

as you explained the explain, what explain does you can, you have this query like select, you know,

star from some table, where or something is something. And if you want to see how postgres

executes or planning to execute this query, you just add the explain statement. It's probably some

parameters before this select. And after that, you will see, for some of the folks in the beginning,

it will look like the magic because you will see all right, this is what the database is planning

to do, have already done. For some of the folks, the output can be discouraging because you still

there is some learning curve, not significant one, but you need to learn how to read those

explain output. But why it's necessary, similar to your use case, we do not need to, all of those

cloud providers, even in the time before, you know, before the cloud, when we had the DBA teams,

which were running databases on our on-prem infrastructures. They want to just developers to focus

just on applications. And that's why for most of the developers, the databases remain

black box. It's like something is deployed in the cloud or someone in my company, the provision

this database for me, this is a connection stream. And I started, you know, doing something.

And then what happened, let's say, you have some high latency, you have performance issues,

then you would go and talk to your DBA and the DBA would point out right now, no, no, like this is

guys, what you did wrong. And then all you would just get some ticket from the cloud provider,

you would create a ticket for cloud provider. And then the cloud provider will tell you the same

guys, you have some issues with some queries. And the, but the thing is, you can minimize the

number of such tickets to the cloud provider or minimize the number of conversations with your DBAs,

if you have a dedicated database team in your company. If you at least strive to understand

how the database, you know, executes your queries and how it can, it will help you to design

much better queries and optimize them. And we explain this is the case, you, when you create your

table, probably in the beginning, you have just a couple of thousands records. And everything

performs fine. But then let's say you have some of those queries and those queries filter data by

column A, B, and C, et cetera. And then suddenly, for some reason, half a year later,

after the product launch, your queries, you know, running slow. Why? And you just scratching

you had, you don't understand why, because everything was fine, you know, a week ago. But

probably because the database right now, because those queries might not have been ever

optimized. It was in your case, Alan, they were doing the full table scan, you know, checking

every record one by one, you know, trying to complete your query and find to find all the records

which match the condition. But then let's say once Postgres courses are, you know, threshold,

you have, let's say, a million record or more records. This full sequential scan will become

eventually expensive and it will hit you badly, because Postgres might not longer have enough

CPU cycles enough memory or something else to execute it efficiently. And then you start

realizing about indexes. And indexes is obviously one of the most widespread optimization techniques.

But if you, if we start creating, let's say, indexes for all of the queries, just let's say,

okay, I have this query, I need this index. I have that query, I need another index. When you

have many indexes, it also can hit you badly, because Postgres, every index consumes storage and

memory space, it doesn't come for free. Every index needs to be maintained, meaning that whenever

you update your records, if any of those fields are indexed by some index structure, those

index structures needs to be updated. So let's say you update in one record in your database,

and then that record is indexed by five indexes. Postgres also needs to update five indexes

transactionally before this update is completed. And also during their execution of the read request,

like select, if you have, let's say, five indexes and also the full tables can, and Postgres can

use any of those five indexes for the execution of your query, Postgres will take spend more time

on the planning phase, trying to realize, okay, which index should I use? But it's always great to

see that people, when people start using the explain statement, because once you start using the

explain statement, you will understand, you know, the difference between like with the power of

indexes, and then when you start using the indexes, also don't, you know, overuse them. There is

the problem called over indexing. When you created too many indexes, and now you have problems,

let's say during the planning phase, now you have problems with rights, because you have

right amplification, indexes needs to be updated as well. Then you have storage space problems,

and then people realize, okay, there are some other ways how you can optimize, you know,

Postgres, and basically they started diving deeper into the database. But generally, all, like,

explain my, my, my belief is that every, every backend engineer or everyone who is, you know,

working with a database directly, and the database is Postgres, they need to know how to see

and analyze execution plans of Postgres. I agree. It's something that everybody kind of like

needs to pick up at some point, because it's very, very useful. So we are starting at the end of the

time, last chance to kind of leave some parting words before we, we had all. I would probably just

repeat myself. The goal of the, the goal of this book, yeah, the book is about Postgres, but what we,

but there is a wonderful afterward in the book, which was authored by, by Vladimir Halsov. What he

pointed out to is the books like this one helps us to build simpler systems, simpler applications,

and what the simplicity means here. The simplicity means that use as a fewer components is possible.

You know, just don't use radius just because you think you need a cache in your case. Probably now,

probably not need radius right now for this use case. You just need to give more RAM to your

database. That's it, because database also caches everything in memory, like Postgres. If you're

same as for the vector search or for the full text search, probably you don't need to introduce

a specialized vector database or full text search database. If Postgres already fully handles your

your specific use case, this make it simple approach will make your life simpler, because once you

put anything in production, you need to monitor this, you need to maintain this, you need to upgrade

this, right? You need to be on those outage calls, and it's going to be good if you manage to

design a system, which has a fewer components, then you should have fewer problems.

And the just use Postgres is one like the book shows you how you can try to achieve this

with Postgres. But if you use another database, if you build in some other systems, then you can

apply the same concept for everything else. Just don't use any other technologies just for the sake

of using technologies, or just because everyone does this. Use only those components, which are

really necessary, because you will have a simpler architecture strive to achieve the simplicity,

not the complexity. Definitely, well said. Well, again, Dennis, thanks for coming on. We do have a

discount code for this book, definitely pick it up, buy it straight from Manning, so that way more

goes into the pocket of Dennis and listen to Jeff Bezos. We don't want to keep propping up his,

I don't know what he's doing these days. He's still going to space. I don't even know. I'm not

sure what he's up to. I think he's enjoying the life here. He has some hobby. He has some hobby

in the space space. Space, right? Space, yeah. Yeah, I don't know. I mean, we're all of us

kind of like technical people. We all have some kind of interesting hobby after we kind of retire.

Like I know one guy, he's into astronomy. Another, not quite a few developers like to be flyers.

I'm not too sure why we want to fly. But in any case, again, it's great to have you on and you know,

maybe we'll come back with just use Postgres version two, right? Or you said, I think another

podcast you're not interested to write another one anytime soon. Not now, no, just I'm done with

writing the books. It was, it's kind of a mixed feeling. It's a joyful process. It's like with

everything, you probably, everyone, everyone had this moment when you're working on something

significant, right? It can be something related. It can be some personal project, probably some,

you know, physical, you're training preparing for something. And then let's say you're running

some long distance, such as half a marathon or no marathon. And it's a painful distance. And

eventually when you cross the finish line, you just feel empty. It's like, okay, it's done. And you

kind of sometimes you don't want to repeat this again. But then what you remember, you remember

the process, the time with you to prepare for that. And the same is with the book, the most joyful

part, part what the process, but it definitely requires a lot of your personal time. Once the book

is published, you know, come on, it's done. And then you start and think like, all right, do you

want to start doing this again? Because it took me like, I think a year, a year and a half just

to write this book from scratch and to get published. I remember you said like 18 months, it was

quick, 18 months. I remember, so 18 months is not quick. This is for me, it's that quick. It's

a long endeavor. It's yeah, it's like 15 months, like 13. Yeah, I started writing it in late July,

early August, 2024. And it was published. I printed copy was published in November, 2025.

Yeah, so basically it's like, yeah, 13, 15 for like 14 months, you've just to be precise.

But it's a February and you're still kind of promoting it, which is quite interesting.

It's okay. I mean, yeah, I slowed down, I slowed down, but yeah, right now the book pretty much

leaves by itself, but it's always great for here feedback from those who read it and find it

useful. At least, yeah, this is this is this is this was my contribution to the Postgres

community because Postgres Postgres is kind of my default database, which means that if I start

a new project, I will explore Postgres first. If Postgres doesn't feed, then I will use another

database. And I spent a lot of the time working with Postgres. That's why I decided to write

how can I contribute back? And the reason why I just wanted to write this particular book,

because even you kind of noted that, okay, you didn't know that Postgres can be used for this,

so that use case. And I had the same, I had the same knowledge gap probably like three, four

years ago. And then I worked for another company called the UGABITE, the bill distributed

version of Postgres. And I was paid to study Postgres. And when I was studying Postgres and

oh, come on, really Postgres can do that. And I was talking to friends of mine, to colleagues,

to different folks and different events. And I heard the same kind of feedback. Oh,

I didn't know that Postgres can do that seriously. Postgres is kind of and I said, all right,

I got to do something. And I decided just to write this book. This is my contribution to

the Postgres community. Hopefully it will help more users to jump on the database and use it

properly. Well, thank you for your contribution. And I just want to say one more time,

yeah, we'll definitely have a discount code for the book. Yeah, thanks again for your contribution,

and hopefully you can keep contributing just a little bit more, maybe not another book, but

maybe in other ways. That's true. That's true. Thanks a lot, Ellen, for your time, and thanks

everyone for listening to us. Yeah, if you have any questions, you can find me on social

LinkedIn or Twitter. We can add my Twitter and LinkedIn handlers to the description of the

Postgres. Yeah, I'll definitely add it over there.