December 8, 2025 • 21 mins
Adi Polak talks to Gwen Shapira (Nile) about her career in databases and data infrastructure. Gwen’s first job: a side hustle fixing computers. Her challenge: figuring out why a production report at HP slowed down dramatically after daylight saving time.
SEASON 2
Hosted by Tim Berglund, Adi Polak and Viktor Gamov
Produced and Edited by Noelle Gallagher, Peter Furia and Nurie Mohamed
Music by Coastal Kites
Artwork by Phil Vo
- 🎧 Subscribe to Confluent Developer wherever you listen to podcasts.
- ▶️ Subscribe on YouTube, and hit the 🔔 to catch new episodes.
- 👍 If you enjoyed this, please leave us a rating.
- 🎧 Confluent also has a podcast for tech leaders: "Life Is But A Stream" hosted by our friend, Joseph Morais.
Listen
Watch
Mark as Played
Transcript
Episode Transcript
Available transcripts are automatically generated. Complete accuracy is not guaranteed.
SPEAKER_02 (00:00):
The clock changed and an entire production system
fell apart.
No code changes, no hardwarefailures, just time itself
breaking the database.
This is confluent developer.
SPEAKER_01 (00:14):
Our CTO is expecting it every day at 8 a.m.
He's getting pretty upset thatit's not ready until much later.
I think it takes some bravery todo it, but I can see how if your
data changes significantly overtime, it's a really good idea.
So we turned on something thatyou basically never turn on in
the database, and a lot ofdatabases don't even have that.
SPEAKER_02 (00:36):
I'm Adi Polak and welcome to Confluent Developer,
where we explore the journeys ofengineers who turn impossible
programs into elegant solutions.
My guest today is Gwen Shapira,database pioneer, author of
Kafka, the Definitive Guide, andfounder of Neil, a modern
(00:57):
reimagined of Postgres for theAI era.
From our teenage side hustle,fixing computers to debugging
daylight saving time, Gwen'sstory proves that curiosity can
turn even the strangest bug intoa breakthrough.
Let's dive in.
(01:21):
Hi Gwen.
Hey, so good to see you.
Yes, so good to see you.
It's been a while.
It's been ages.
Ages, I know.
I'm curious, what are you up torecently?
SPEAKER_01 (01:34):
Oh no, nothing special, just building a
multi-tenant database and tryingto market it, onboarding first
customers to my product.
Nothing super exciting.
No kidding, it's super exciting,and especially just watching a
product that you built grow isexciting.
(01:56):
And obviously, I've loveddatabases for a very long time,
and there is something specialabout having your own.
SPEAKER_02 (02:04):
Yeah, it's amazing to see your vision comes to
life.
I remember we talked about itsome years back, and you know,
now all the pieces are comingtogether.
So it's uh definitely amazing.
Um for the people who didn't geta chance to know you before,
maybe you want to say a coupleof words about you uh so they
can you know know you better.
SPEAKER_01 (02:22):
Yeah, how far back should I go?
Do I talk about?
I left Confluent I think almostfour years ago now, so it has
been a while.
I used to be the Kafka personand wrote a Kafka book and did a
lot of Kafka talking.
Before that, I was a Hadoopperson.
And now I'm kind of back to whatI did before I was into the
(02:45):
whole Hadoop thing, which isrelational databases.
And if back then I was mostlyhelping other people build
products with relationaldatabases, uh Nile, which I've
been building for the last fouryears, is basically taking
Postgres and updating it formodern workloads, things with
(03:08):
AI, multi-tenant applications,all these kinds of things.
That's fascinating.
SPEAKER_02 (03:13):
I just want to call out one thing your book, uh The
Definitive Guide for Kafka, isstill a bestseller, and every
time someone wants to learnabout Kafka, this is the most
recommended book.
So just FYI.
SPEAKER_01 (03:25):
We didn't know that because it's actually pretty old
at this point.
Someone should probably go andwrite a third edition.
Uh, a lot of change, right?
And especially with Confluenthaving Quora and like cloud
native Kafka and all of that.
It's uh there's a lot of newthings to answer.
SPEAKER_02 (03:45):
Yes, and cues for Kafka, it changed the whole
architecture.
But hey, this is for completelydifferent conversations.
Let's jump into it.
So, in our podcast, we're gonnatalk about roughly about some
challenges that we solved uh asan engineers uh throughout our
career.
But the first thing that we haveto know about uh Gwen is what
(04:06):
was your very first job?
SPEAKER_01 (04:09):
So it kind of depends how you count.
My very first earning money wasactually working for myself as a
teenager.
Everyone always asked me to forhelp with their computer, you
know, fix my printer, my Windowshas viruses, all this kind of
(04:30):
stuff.
And at some point, my dad toldme, Look, you're helping all of
my friends, you're spendinghours helping all of my friends.
Why don't you start charging themoney?
I'm like, I can charge peoplemoney for for helping them.
I thought I'm doing it just bybeing a nice person.
And I was like, Yeah, you know,you should probably save some
(04:50):
money, you can buy nice clothes,this kind of thing.
I was like, okay, let's do that.
And it was so embarrassing toask for money for what I do, and
like it was stressful.
What if I suddenly fail to fixtheir computer and now I'm
charging them money and I'mfailing?
(05:11):
While previously I was mostlysuccessful, but my fellas, it's
like my fellows don't count if Idon't charge them money.
So I think this may count as afirst job, even though I was
kind of not working for anyoneelse.
My other way of making moneythat did involve having an
(05:32):
actual boss and working hoursand all of that.
I was working at the HebrewUniversity in Jerusalem as they
were doing medical research, andit involved you know, small
animals, mice and rabbits andall of those.
Someone has to actually maintainthese animals, you know, have to
(05:54):
feed them, clean up the cages,pet them, and you know, make
sure they feel uh taken care ofkind of thing.
And um yeah, I really it was anobviously didn't use any of the
skills that I had except justliking small furry creatures,
(06:14):
uh, but it was a nice way to uhspend time and make some money
when I was uh yeah, I was inuniversity anyway because I was
getting my degree, so it wasjust a nice way to do it.
SPEAKER_02 (06:26):
Yeah, skills that last till today.
Um watching all your Twitterposts with cats.
SPEAKER_01 (06:34):
Yeah, cats are way better than um those guinea pigs
and mice, to be honest.
SPEAKER_02 (06:41):
Right.
I just want to highlight onething that you said that um you
know really hit home.
It's the fact that uh your firstjob, it's when you're helping,
you're actually delivering valueto to someone, and it's okay to
charge for it.
Um you know, I I believe manypeople go through that phase of
understanding that um deliveringvalue equals uh you can monetize
(07:05):
that.
Very, very interestinglearnings.
SPEAKER_01 (07:10):
Yeah, it's funny because you grow up, you're
asked to help people so much,and at some point it's like,
okay, now it's okay to get tocharge for it.
SPEAKER_02 (07:18):
Right, right.
It's um you're exchangingsomething for something, and uh
that's always very interesting.
Cool, very cool uh first twojobs.
Uh one is very techie, one isuh, you know, uh love for furry
uh pets.
And uh that was veryinteresting.
So I'm curious uh now that wegonna jump forward into your
(07:40):
career and some of the thingsyou build in tech, and you build
a lot of things uh in tech.
Um so what are some time whereyou hit like a tricky software
challenge or technologychallenge, and how did you
figure out your way through it?
SPEAKER_00 (07:53):
Now a quick word from our sponsor.
Confluent developer the podcastis brought to you by Confluent
Developer the website, which haseverything you need as a
developer of data streamingsystems.
And it's completely free.
We've got curriculum, hands-onexercises, executable tutorials,
the online data streamingengineer certification, also
free, a way to find a meetupnear you, those are free.
(08:16):
Everything is there.
I really want you to besuccessful in your journey as a
data streaming engineer, andthis is the site that has what
you need.
Check it out atdeveloper.confluent.io.
That's developer.confluent.io.
Now back to the show.
SPEAKER_01 (08:32):
Yeah, so I picked the specific story not
necessarily because it was thehardest, but because it was, you
know, how there is some thingsthat are literally never so much
never the problems that we'realmost used as a joke about it
cannot possibly be the problem.
Like in Dr.
House, it's never a lopus, anduh you know it's never a
(08:54):
compiler bug.
And if you have a bug, you canblame the face of the moon, but
what are the chances that yourbug is actually related to the
face of the moon?
So this is um actually a problemfrom many years ago, probably
well over a decade now, and Iwas responsible for a large,
(09:16):
very large set of databases atHP, and someone came over and
said uh my query is runningreally slowly.
We have a report that is it wasover 10 times slower than
expected, and it startedhappening when the time zone
(09:38):
changed, like daylight savingtime started, so we came over.
Think it like was like end ofmerch, is like time we have
daylight saving time since March15, and now for the last two
weeks, our report, which issupposed to be ready every day
at 8 a.m., is actually not readyuntil much later, and our CTO is
(10:03):
expecting it every day at 8 a.m.
He's getting pretty upset thatit's not ready until much later.
And yeah, it's like how canpossibly a report become slower
because of daylight saving time?
I would understand, okay, it'shaving the wrong numbers, maybe
someone did the calculationsthat didn't take daylight saving
(10:24):
time into account.
This is super common.
Every person who ever didreporting with data knows that
time zones and daylight savingtime make every report about 10
times harder.
Yeah, but why would it beslower?
Uh so the first thing you checkis is it still cal getting
similar results?
(10:44):
And yes, does it still hit moreor less the same amount of data?
Did the amount of data in thedatabase change?
No, it didn't.
What could possibly so and thenyou're like, okay, it cannot
possibly be Del at 7 time, butyou know, something may have
happened two weeks ago.
What did we do to the system twoweeks ago?
It's the same storage, we didn'treally change anything.
(11:05):
There were some operating systempatches, but they don't really
seem performance related, andthe only thing it's really
impacting is this report, andthose are busy databases.
What could it possibly be thatonly affects a single report and
in and changed two weeks ago?
(11:27):
So obviously, one of the if youhave a large report, one of the
biggest things that could changein a database is the query
plans.
And so I we had kind of samplelogging for our uh query plans,
and but this was a long andlarge enough report that we had
(11:49):
really good historical samplesfor it.
So that was good.
That's one important tip.
If you're responsible forperformance, it's really good to
have snapshots taken at regularintervals.
Because if you have if someonecomes to you and says, Hey, it's
slow now, and it was fastbefore, and you don't have any
information of what happenedbefore, it's really hard to
(12:09):
figure out why is it slower nowbecause all you have is the bad
state, you don't have any goodstate to compare it to.
So we went back and looked atthe what happened before, and
the plan completely changed.
The moment we saw it, it waslike, Yes, it's clear and why
it's slow now.
It used to have a good plan, nowit has a bad plan.
SPEAKER_02 (12:26):
So the query plan, the the the actual query plan
changed, like the physical queryplan or okay.
SPEAKER_01 (12:33):
Well, depends on the layers, there are different
layers, but it's like thephysical query plan of how the
data essentially how we're sowhen you do in relational
databases, when you run a query,you write SQL text, and then
there is an planner andoptimizer that says, okay, this
join is going to be nested loopjoin because one side is very
(12:53):
small and one side is large, orboth sides are large, you're
going to do a hash join.
So all and usually in uh in alarger port, there is thousands
of those decisions that theoptimizer has to make in order
to figure out how best toproduce this data.
And got it.
Without the data changing, theplan became completely
(13:15):
different.
Now there could be reasons for aplan to change.
Uh one of them is that thestatistics have changed for
whatever reason.
Uh the plan is created based ondata statistics.
The data didn't change, and Ichecked the statistics, they
also looked completely normal,which was very suspicious.
The statistics didn't change,and yet the plan was totally
(13:42):
different, which is absolutelyinsane.
And so we turned on somethingthat you basically never turn on
in the database, and a lot ofdatabases don't even have that.
We're a bit lucky that back thenOracle had this.
You can turn on a flag that itwill not just log the plan, it
will log every decision that theplanner did and why it made a
(14:03):
decision it made.
So I'm choosing this because ofthose statistics.
And the moment I looked at that,I could see that the statistics
the plan we were using were notthe statistics that I was
looking at.
It was it basically believedthat the entire report was
running on empty tables.
(14:26):
Like, why would it think that myentire report is running on
empty tables and plan for that?
Well, turned out that we had anightly job to refresh
statistics because you know youload data, you delete data, you
make updates, things change.
So every night at I thinkmidnight, uh a job ran that
deleted all the statistics andcollected new ones.
(14:50):
The report was supposed to startat 1 am.
I don't know if you see theproblem.
The late 70s started.
The statistics for some reasonstayed at midnight because there
were this was one chrome jobthat basically midnight was
stable there.
The report generation moved toan hour earlier at to start at
(15:15):
midnight.
So it started at exactly the onepoint in the day which lasted no
more than 10 minutes, in whichwe had no statistics.
So it did all the planning basedon the 10-minute interval in
which it could believe that allthe tables were actually empty.
SPEAKER_02 (15:33):
Wow.
SPEAKER_01 (15:34):
So yeah, the night saving time can cause the report
to be a lot slower if the reportruns at the wrong time.
Which by the way is somethingthat excited me, I think fairly
recently, but like two yearsago, Databricks published a
paper and how they allow if theymonitor the query plan during
(15:58):
the execution, and if itdiscovers that the amount of
data it sees while executingdon't match the amount of data
it believed it had while makingthe execution plan, it actually
goes back and replans andmodifies the execution in real
time.
It's called adaptive querysomething.
(16:19):
And what when I read the paper,I was like, oh my god, I could
have used that years ago.
SPEAKER_02 (16:28):
Yeah.
It's fascinating.
So essentially, let me see if Iif I got it correctly.
Essentially, there was a youknow some bug in the uh in the
query, the query was super slow,um, something happened there,
and you start investigating andlooking into the data and what
is going on, and then you turnon uh the query planner output
so you can see exactly you knowwhat's the the actual the
(16:49):
physical plan that got out andwhat are the statistics that
these plans was based off.
Uh and then you realized, andI'm guessing you have to sift
through like many, many days,right?
Like to compare uh the differenttimes where everything worked
well versus when things start togo uh downhill.
Um and then comparing like themetadata there and and seeing
(17:12):
like what what's the plan,what's the metadata, what's the
statistics, if that makes sense,only to realize that the query,
the the job was planned to runat 1 a.m.
And when the clock changed, youknow, something that happens
twice a year.
Um, but it was a specific case,was moved back to uh uh to
(17:32):
midnight when just before themetadata query that updates the
statistics started to run,right?
SPEAKER_01 (17:40):
Yeah.
I mean it's uh it managed tomove to exactly the point after
the statistics job managed todrop all the old statistics and
before it managed to collect thenew statistics.
It was incredibly bad luck.
SPEAKER_02 (17:54):
Wow, that's uh yes, yeah.
How did you fix it?
What was what was the fix forthat?
SPEAKER_01 (17:59):
I mean the fix was easy, right?
You just uh change the timing ofthe reports.
I mean it's amazing when you thereport starts 15 minutes later,
it actually finishes four hoursearlier.
It's almost like you know, thetime you leave home or work if
you have to time it with rushhour traffic, and sometimes like
(18:21):
15 minutes change has a hugeimpact on when you actually
arrive to work.
SPEAKER_02 (18:26):
Yeah, especially in the Bay Area.
It's like this 10 minutes in themorning really really counts.
Every minute counts, absolutely.
Every minute counts.
And yeah, that's super cool.
And the um the adapt adaptive uhquery planner is also a very
interesting solution.
I also looked at it some yearsago, actually, not recently.
Um, and I was very surprised touh to see that.
(18:49):
But I do I am curious becausewith every kind of like magical
thing that happens automaticallyuh under the hood, um what's the
the rate of you know errors andmistakes?
But I guess we'll never knowbecause uh I agree.
SPEAKER_01 (19:04):
I don't know if adaptive is good or bad.
There is something nice aboutknowing that you have the same
query plan for the same reportday after day.
Um but on the other hand, if youknow statistics change, data
changes, uh then you actuallydon't no longer want the good
old one day after day.
So it's uh definitely aninteresting judgment call.
(19:28):
Uh Postgres now has an extensionfor uh they call it a switch
join, which is kind of the sameidea, you know, joins have a lot
of impact, and there is severalnormal join methods and you
choose between them based onstatistics.
And the switch join extensionbasically means that if it
(19:48):
chooses one of them, but ifduring executing the join it
looks like it's getting way moredata than expected or way less
data than expected, it willswitch over to the alternative
plan midway.
I think it takes some bravery todo it, but I can see how if your
(20:09):
data changes significantly overtime, it's a really good idea.
SPEAKER_02 (20:14):
Yeah, it's fascinating.
All the all the optimizationsaround joins like broadcast
joints, hash joins, and so onare you know a whole universe
that I think um not manysoftware engineers get to dive
into.
So it's definitely an excitingpart that you do.
Um, I hope you know you'll writemore about it.
Uh I I would be the first one toread.
(20:36):
I'm making a note.
Here we go.
Challenge accepted.
All right.
Gwen, thank you so much uh forjoining us today and sharing
your your knowledge andexpertise.
It was you know super excitingto to hear first about what you
did early on and then later onsome challenges you're solving.
And of course, I'm very excitedfor Neil and everything that
(20:58):
you're building.
Um, I, you know, optimistically,you know, carefully optimistic,
but I think this is the future.
Um, so very excited for yourvision to come to life.
SPEAKER_01 (21:10):
Thank you so much.
It's been a pleasure as always.
The clock changed and an entire production system
fell apart.
No code changes, no hardwarefailures, just time itself
breaking the database.
This is confluent developer.
SPEAKER_01 (00:14):
Our CTO is expecting it every day at 8 a.m.
He's getting pretty upset thatit's not ready until much later.
I think it takes some bravery todo it, but I can see how if your
data changes significantly overtime, it's a really good idea.
So we turned on something thatyou basically never turn on in
the database, and a lot ofdatabases don't even have that.
SPEAKER_02 (00:36):
I'm Adi Polak and welcome to Confluent Developer,
where we explore the journeys ofengineers who turn impossible
programs into elegant solutions.
My guest today is Gwen Shapira,database pioneer, author of
Kafka, the Definitive Guide, andfounder of Neil, a modern
(00:57):
reimagined of Postgres for theAI era.
From our teenage side hustle,fixing computers to debugging
daylight saving time, Gwen'sstory proves that curiosity can
turn even the strangest bug intoa breakthrough.
Let's dive in.
(01:21):
Hi Gwen.
Hey, so good to see you.
Yes, so good to see you.
It's been a while.
It's been ages.
Ages, I know.
I'm curious, what are you up torecently?
SPEAKER_01 (01:34):
Oh no, nothing special, just building a
multi-tenant database and tryingto market it, onboarding first
customers to my product.
Nothing super exciting.
No kidding, it's super exciting,and especially just watching a
product that you built grow isexciting.
(01:56):
And obviously, I've loveddatabases for a very long time,
and there is something specialabout having your own.
SPEAKER_02 (02:04):
Yeah, it's amazing to see your vision comes to
life.
I remember we talked about itsome years back, and you know,
now all the pieces are comingtogether.
So it's uh definitely amazing.
Um for the people who didn't geta chance to know you before,
maybe you want to say a coupleof words about you uh so they
can you know know you better.
SPEAKER_01 (02:22):
Yeah, how far back should I go?
Do I talk about?
I left Confluent I think almostfour years ago now, so it has
been a while.
I used to be the Kafka personand wrote a Kafka book and did a
lot of Kafka talking.
Before that, I was a Hadoopperson.
And now I'm kind of back to whatI did before I was into the
(02:45):
whole Hadoop thing, which isrelational databases.
And if back then I was mostlyhelping other people build
products with relationaldatabases, uh Nile, which I've
been building for the last fouryears, is basically taking
Postgres and updating it formodern workloads, things with
(03:08):
AI, multi-tenant applications,all these kinds of things.
That's fascinating.
SPEAKER_02 (03:13):
I just want to call out one thing your book, uh The
Definitive Guide for Kafka, isstill a bestseller, and every
time someone wants to learnabout Kafka, this is the most
recommended book.
So just FYI.
SPEAKER_01 (03:25):
We didn't know that because it's actually pretty old
at this point.
Someone should probably go andwrite a third edition.
Uh, a lot of change, right?
And especially with Confluenthaving Quora and like cloud
native Kafka and all of that.
It's uh there's a lot of newthings to answer.
SPEAKER_02 (03:45):
Yes, and cues for Kafka, it changed the whole
architecture.
But hey, this is for completelydifferent conversations.
Let's jump into it.
So, in our podcast, we're gonnatalk about roughly about some
challenges that we solved uh asan engineers uh throughout our
career.
But the first thing that we haveto know about uh Gwen is what
(04:06):
was your very first job?
SPEAKER_01 (04:09):
So it kind of depends how you count.
My very first earning money wasactually working for myself as a
teenager.
Everyone always asked me to forhelp with their computer, you
know, fix my printer, my Windowshas viruses, all this kind of
(04:30):
stuff.
And at some point, my dad toldme, Look, you're helping all of
my friends, you're spendinghours helping all of my friends.
Why don't you start charging themoney?
I'm like, I can charge peoplemoney for for helping them.
I thought I'm doing it just bybeing a nice person.
And I was like, Yeah, you know,you should probably save some
(04:50):
money, you can buy nice clothes,this kind of thing.
I was like, okay, let's do that.
And it was so embarrassing toask for money for what I do, and
like it was stressful.
What if I suddenly fail to fixtheir computer and now I'm
charging them money and I'mfailing?
(05:11):
While previously I was mostlysuccessful, but my fellas, it's
like my fellows don't count if Idon't charge them money.
So I think this may count as afirst job, even though I was
kind of not working for anyoneelse.
My other way of making moneythat did involve having an
(05:32):
actual boss and working hoursand all of that.
I was working at the HebrewUniversity in Jerusalem as they
were doing medical research, andit involved you know, small
animals, mice and rabbits andall of those.
Someone has to actually maintainthese animals, you know, have to
(05:54):
feed them, clean up the cages,pet them, and you know, make
sure they feel uh taken care ofkind of thing.
And um yeah, I really it was anobviously didn't use any of the
skills that I had except justliking small furry creatures,
(06:14):
uh, but it was a nice way to uhspend time and make some money
when I was uh yeah, I was inuniversity anyway because I was
getting my degree, so it wasjust a nice way to do it.
SPEAKER_02 (06:26):
Yeah, skills that last till today.
Um watching all your Twitterposts with cats.
SPEAKER_01 (06:34):
Yeah, cats are way better than um those guinea pigs
and mice, to be honest.
SPEAKER_02 (06:41):
Right.
I just want to highlight onething that you said that um you
know really hit home.
It's the fact that uh your firstjob, it's when you're helping,
you're actually delivering valueto to someone, and it's okay to
charge for it.
Um you know, I I believe manypeople go through that phase of
understanding that um deliveringvalue equals uh you can monetize
(07:05):
that.
Very, very interestinglearnings.
SPEAKER_01 (07:10):
Yeah, it's funny because you grow up, you're
asked to help people so much,and at some point it's like,
okay, now it's okay to get tocharge for it.
SPEAKER_02 (07:18):
Right, right.
It's um you're exchangingsomething for something, and uh
that's always very interesting.
Cool, very cool uh first twojobs.
Uh one is very techie, one isuh, you know, uh love for furry
uh pets.
And uh that was veryinteresting.
So I'm curious uh now that wegonna jump forward into your
(07:40):
career and some of the thingsyou build in tech, and you build
a lot of things uh in tech.
Um so what are some time whereyou hit like a tricky software
challenge or technologychallenge, and how did you
figure out your way through it?
SPEAKER_00 (07:53):
Now a quick word from our sponsor.
Confluent developer the podcastis brought to you by Confluent
Developer the website, which haseverything you need as a
developer of data streamingsystems.
And it's completely free.
We've got curriculum, hands-onexercises, executable tutorials,
the online data streamingengineer certification, also
free, a way to find a meetupnear you, those are free.
(08:16):
Everything is there.
I really want you to besuccessful in your journey as a
data streaming engineer, andthis is the site that has what
you need.
Check it out atdeveloper.confluent.io.
That's developer.confluent.io.
Now back to the show.
SPEAKER_01 (08:32):
Yeah, so I picked the specific story not
necessarily because it was thehardest, but because it was, you
know, how there is some thingsthat are literally never so much
never the problems that we'realmost used as a joke about it
cannot possibly be the problem.
Like in Dr.
House, it's never a lopus, anduh you know it's never a
(08:54):
compiler bug.
And if you have a bug, you canblame the face of the moon, but
what are the chances that yourbug is actually related to the
face of the moon?
So this is um actually a problemfrom many years ago, probably
well over a decade now, and Iwas responsible for a large,
(09:16):
very large set of databases atHP, and someone came over and
said uh my query is runningreally slowly.
We have a report that is it wasover 10 times slower than
expected, and it startedhappening when the time zone
(09:38):
changed, like daylight savingtime started, so we came over.
Think it like was like end ofmerch, is like time we have
daylight saving time since March15, and now for the last two
weeks, our report, which issupposed to be ready every day
at 8 a.m., is actually not readyuntil much later, and our CTO is
(10:03):
expecting it every day at 8 a.m.
He's getting pretty upset thatit's not ready until much later.
And yeah, it's like how canpossibly a report become slower
because of daylight saving time?
I would understand, okay, it'shaving the wrong numbers, maybe
someone did the calculationsthat didn't take daylight saving
(10:24):
time into account.
This is super common.
Every person who ever didreporting with data knows that
time zones and daylight savingtime make every report about 10
times harder.
Yeah, but why would it beslower?
Uh so the first thing you checkis is it still cal getting
similar results?
(10:44):
And yes, does it still hit moreor less the same amount of data?
Did the amount of data in thedatabase change?
No, it didn't.
What could possibly so and thenyou're like, okay, it cannot
possibly be Del at 7 time, butyou know, something may have
happened two weeks ago.
What did we do to the system twoweeks ago?
It's the same storage, we didn'treally change anything.
(11:05):
There were some operating systempatches, but they don't really
seem performance related, andthe only thing it's really
impacting is this report, andthose are busy databases.
What could it possibly be thatonly affects a single report and
in and changed two weeks ago?
(11:27):
So obviously, one of the if youhave a large report, one of the
biggest things that could changein a database is the query
plans.
And so I we had kind of samplelogging for our uh query plans,
and but this was a long andlarge enough report that we had
(11:49):
really good historical samplesfor it.
So that was good.
That's one important tip.
If you're responsible forperformance, it's really good to
have snapshots taken at regularintervals.
Because if you have if someonecomes to you and says, Hey, it's
slow now, and it was fastbefore, and you don't have any
information of what happenedbefore, it's really hard to
(12:09):
figure out why is it slower nowbecause all you have is the bad
state, you don't have any goodstate to compare it to.
So we went back and looked atthe what happened before, and
the plan completely changed.
The moment we saw it, it waslike, Yes, it's clear and why
it's slow now.
It used to have a good plan, nowit has a bad plan.
SPEAKER_02 (12:26):
So the query plan, the the the actual query plan
changed, like the physical queryplan or okay.
SPEAKER_01 (12:33):
Well, depends on the layers, there are different
layers, but it's like thephysical query plan of how the
data essentially how we're sowhen you do in relational
databases, when you run a query,you write SQL text, and then
there is an planner andoptimizer that says, okay, this
join is going to be nested loopjoin because one side is very
(12:53):
small and one side is large, orboth sides are large, you're
going to do a hash join.
So all and usually in uh in alarger port, there is thousands
of those decisions that theoptimizer has to make in order
to figure out how best toproduce this data.
And got it.
Without the data changing, theplan became completely
(13:15):
different.
Now there could be reasons for aplan to change.
Uh one of them is that thestatistics have changed for
whatever reason.
Uh the plan is created based ondata statistics.
The data didn't change, and Ichecked the statistics, they
also looked completely normal,which was very suspicious.
The statistics didn't change,and yet the plan was totally
(13:42):
different, which is absolutelyinsane.
And so we turned on somethingthat you basically never turn on
in the database, and a lot ofdatabases don't even have that.
We're a bit lucky that back thenOracle had this.
You can turn on a flag that itwill not just log the plan, it
will log every decision that theplanner did and why it made a
(14:03):
decision it made.
So I'm choosing this because ofthose statistics.
And the moment I looked at that,I could see that the statistics
the plan we were using were notthe statistics that I was
looking at.
It was it basically believedthat the entire report was
running on empty tables.
(14:26):
Like, why would it think that myentire report is running on
empty tables and plan for that?
Well, turned out that we had anightly job to refresh
statistics because you know youload data, you delete data, you
make updates, things change.
So every night at I thinkmidnight, uh a job ran that
deleted all the statistics andcollected new ones.
(14:50):
The report was supposed to startat 1 am.
I don't know if you see theproblem.
The late 70s started.
The statistics for some reasonstayed at midnight because there
were this was one chrome jobthat basically midnight was
stable there.
The report generation moved toan hour earlier at to start at
(15:15):
midnight.
So it started at exactly the onepoint in the day which lasted no
more than 10 minutes, in whichwe had no statistics.
So it did all the planning basedon the 10-minute interval in
which it could believe that allthe tables were actually empty.
SPEAKER_02 (15:33):
Wow.
SPEAKER_01 (15:34):
So yeah, the night saving time can cause the report
to be a lot slower if the reportruns at the wrong time.
Which by the way is somethingthat excited me, I think fairly
recently, but like two yearsago, Databricks published a
paper and how they allow if theymonitor the query plan during
(15:58):
the execution, and if itdiscovers that the amount of
data it sees while executingdon't match the amount of data
it believed it had while makingthe execution plan, it actually
goes back and replans andmodifies the execution in real
time.
It's called adaptive querysomething.
(16:19):
And what when I read the paper,I was like, oh my god, I could
have used that years ago.
SPEAKER_02 (16:28):
Yeah.
It's fascinating.
So essentially, let me see if Iif I got it correctly.
Essentially, there was a youknow some bug in the uh in the
query, the query was super slow,um, something happened there,
and you start investigating andlooking into the data and what
is going on, and then you turnon uh the query planner output
so you can see exactly you knowwhat's the the actual the
(16:49):
physical plan that got out andwhat are the statistics that
these plans was based off.
Uh and then you realized, andI'm guessing you have to sift
through like many, many days,right?
Like to compare uh the differenttimes where everything worked
well versus when things start togo uh downhill.
Um and then comparing like themetadata there and and seeing
(17:12):
like what what's the plan,what's the metadata, what's the
statistics, if that makes sense,only to realize that the query,
the the job was planned to runat 1 a.m.
And when the clock changed, youknow, something that happens
twice a year.
Um, but it was a specific case,was moved back to uh uh to
(17:32):
midnight when just before themetadata query that updates the
statistics started to run,right?
SPEAKER_01 (17:40):
Yeah.
I mean it's uh it managed tomove to exactly the point after
the statistics job managed todrop all the old statistics and
before it managed to collect thenew statistics.
It was incredibly bad luck.
SPEAKER_02 (17:54):
Wow, that's uh yes, yeah.
How did you fix it?
What was what was the fix forthat?
SPEAKER_01 (17:59):
I mean the fix was easy, right?
You just uh change the timing ofthe reports.
I mean it's amazing when you thereport starts 15 minutes later,
it actually finishes four hoursearlier.
It's almost like you know, thetime you leave home or work if
you have to time it with rushhour traffic, and sometimes like
(18:21):
15 minutes change has a hugeimpact on when you actually
arrive to work.
SPEAKER_02 (18:26):
Yeah, especially in the Bay Area.
It's like this 10 minutes in themorning really really counts.
Every minute counts, absolutely.
Every minute counts.
And yeah, that's super cool.
And the um the adapt adaptive uhquery planner is also a very
interesting solution.
I also looked at it some yearsago, actually, not recently.
Um, and I was very surprised touh to see that.
(18:49):
But I do I am curious becausewith every kind of like magical
thing that happens automaticallyuh under the hood, um what's the
the rate of you know errors andmistakes?
But I guess we'll never knowbecause uh I agree.
SPEAKER_01 (19:04):
I don't know if adaptive is good or bad.
There is something nice aboutknowing that you have the same
query plan for the same reportday after day.
Um but on the other hand, if youknow statistics change, data
changes, uh then you actuallydon't no longer want the good
old one day after day.
So it's uh definitely aninteresting judgment call.
(19:28):
Uh Postgres now has an extensionfor uh they call it a switch
join, which is kind of the sameidea, you know, joins have a lot
of impact, and there is severalnormal join methods and you
choose between them based onstatistics.
And the switch join extensionbasically means that if it
(19:48):
chooses one of them, but ifduring executing the join it
looks like it's getting way moredata than expected or way less
data than expected, it willswitch over to the alternative
plan midway.
I think it takes some bravery todo it, but I can see how if your
(20:09):
data changes significantly overtime, it's a really good idea.
SPEAKER_02 (20:14):
Yeah, it's fascinating.
All the all the optimizationsaround joins like broadcast
joints, hash joins, and so onare you know a whole universe
that I think um not manysoftware engineers get to dive
into.
So it's definitely an excitingpart that you do.
Um, I hope you know you'll writemore about it.
Uh I I would be the first one toread.
(20:36):
I'm making a note.
Here we go.
Challenge accepted.
All right.
Gwen, thank you so much uh forjoining us today and sharing
your your knowledge andexpertise.
It was you know super excitingto to hear first about what you
did early on and then later onsome challenges you're solving.
And of course, I'm very excitedfor Neil and everything that
(20:58):
you're building.
Um, I, you know, optimistically,you know, carefully optimistic,
but I think this is the future.
Um, so very excited for yourvision to come to life.
SPEAKER_01 (21:10):
Thank you so much.
It's been a pleasure as always.
Popular Podcasts
Hey Jonas!
Hey Jonas! The official Jonas Brothers podcast. Hosted by Kevin, Joe, and Nick Jonas. It’s the Jonas Brothers you know... musicians, actors, and well, yes, brothers. Now, they’re sharing another side of themselves in the playful, intimate, and irreverent way only they can. Spend time with the Jonas Brothers here and stay a little bit longer for deep conversations like never before.
Stuff You Should Know
If you've ever wanted to know about champagne, satanism, the Stonewall Uprising, chaos theory, LSD, El Nino, true crime and Rosa Parks, then look no further. Josh and Chuck have you covered.
Dateline NBC
Current and classic episodes, featuring compelling true-crime mysteries, powerful documentaries and in-depth investigations. Follow now to get the latest episodes of Dateline NBC completely free, or subscribe to Dateline Premium for ad-free listening and exclusive bonus content: DatelinePremium.com