Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard.
This is Nikolay, founder of Postgres.AI.
Hello Nikolay.
Nikolay: Hi Michael, how are you?
Michael: I'm good, how are you?
Nikolay: Very good.
Let's talk about some crazy stuff.
Michael: I chose the topic this week and it was a listener suggestion,
it's Postgres gotchas and we've had a short discussion before
this call about what we should even count as gotchas.
So how do you define it, what do you think of when you hear Postgres
gotcha?
Nikolay: Well if you for example run, I don't know, like SELECT
5/2, what will you get?
Michael: Is this like data type casting?
Nikolay: Right right well it's not Postgres related I think it's
SQL related but still what do you expect to get?
Normal person would expect 2.5, right?
But you will get, and I just double checked because I always...
I'm always like thinking, am I right?
So you're getting 2, so 5.
Michael: 0 wow, I actually thought it was going to be 3.
Nikolay: We probably have 2 things to discuss here.
First is of course integer and integer, right?
Results should be integer, so there's no decimal, there's no
point here, there's no dot.
And, but 2 or 3?
Rounding or flooring, right?
Michael: Yeah, because like rounding conventions at least where
I grew up we generally round up from 0.5 but not clearly not
here anyway I like this almost this definition which is something
where you may expect 1 outcome but a different outcome happens.
That's
Nikolay: unexpected behavior basically in other words.
Michael: Yeah, especially unexpected if you haven't read the
documentation.
Nikolay: Sometimes documentation also wrong or misleading or
sometimes it's just confusing.
Michael: Yeah fair enough.
I just wanted an excuse to list a few things that are made very
clear in the documentation, but I know people get confused by
it because often they don't read that.
Nikolay: You know, I'm curious, I actually found a good example,
right?
It's like probably a gotcha SQL, not Postgres, because
I think it's maybe defined by standard, but, and this is super
basic, but I'm wondering why indeed it's 2 not 3 because if you
for example I what I did I double check of course I checked floor
and round functions and of course as expected floor I converted
5 to numeric or just just to
Michael: last with the colon colon
Nikolay: you're right right so the result is also numeric and
then we have of course 2.500 like so but then I checked floor
gives me 2 round gives me 3 is expected because 2.5 means like
we need if you're rounding according to their names for these
rules, I don't remember, right?
But usually 5 you go up, 4 you go down, right?
And it gives 3, Round gives 3.
But I was thinking, okay, why 5 slash 2 integer and integer gives
2 and not 3?
Maybe because it uses 4.
We could check the source code, of course, but the next thing
I checked is the final thing I checked.
5 converted to numeric slash 2 gives you 2.5.
And then you convert back to int, right?
Again, double colon int.
It gives 3.
Amazing.
So how come we have 2 if we don't use type conversion, explicit
type conversion at all?
Any ideas?
It was good gotcha to find.
I always knew that it's 2 because I demonstrated.
I just didn't think like why.
Now I think why.
I have questions.
Maybe there is some good explanation, simple, but I can see easily
that it can be gotcha.
It can be considered gotcha.
And let's agree on 1 more thing.
We don't talk about nulls today.
Michael: I actually did have them on my list, but not to go into
detail.
Nikolay: We had a whole episode, and this whole episode is telling
like, nulls are surrounded by gotchas both like standard defined
standard produce and like global SQL gotchas and local Postgres
related gotchas as well for example when you go to a race right
Let's just exclude it because it's a whole massive area.
Michael: I think that's very sensible.
And if you haven't listened to that episode, I'll link it up
in the show notes.
Nikolay: Consider it to be fully included here.
Yeah.
Right.
Okay, let's maybe switch to your ideas.
I know you have a list, pull something from it.
Michael: Yeah, I've got a few that I wanted to make sure we mentioned.
I'm gonna start with some ones I consider more beginner level
gotchas and then maybe we can work our way up a little bit.
The first 1 I had is 1 I see all the time in kind of forums every
now and again on Reddit, quite a few times in like the Postgres
Slack, a few up on a few mailing lists.
People getting confused or annoyed when they name objects in
Postgres and they use mixed case, so maybe camel case or some
way of naming a table like for example like a two-worded name
and using capital letters for the 2 names and no space in between
them that kind of thing and then realizing the table is not called
that behind the scenes and it's all lowercase and if they've
used an ORM or something to name these at the ORM often puts
quotes around those case names so then they become cased in the
database.
So I think this whole area is like a 1 big gotcha for beginners
and the people who set up the schema in the first place, if they
do something like this, if they don't just use lowercase object
names, can really make life a bit painful for everybody that
comes after them having to double quote everything.
Nikolay: Yeah, especially Java people like it.
Michael: Oh, really?
Nikolay: Yeah, I noticed.
Usually, if we have a new customer and we start discussing their
database, we see some camel-stack-cased table names.
And I think, I'm already thinking, are you Java guys?
Yeah, there are.
Usually, not always, but usually so.
And my previous startup was using it camel-style.
And we just decided to keep it as is because it was inherited
from some...
I think it was in MySQL originally.
Like, we started very lean, so we borrowed some schema from some
open source project.
And it had it.
And I just, knowing this very well, I still decided to keep it.
But the problem with double quotes is not like...
It's not a big deal, but when...
You know what comes with quotes usually, right?
Backslashes.
Michael: Oh, having...
Yeah, having to escape them.
Nikolay: Yeah, and you cannot escape from this.
You need to use it to escape.
Right?
I mean, yeah.
And backslashes, if you have automation in many languages, like
sometimes it's nightmare.
Right now we have an ongoing problem with backslashes and escaping
of quotes and double quotes in our AI system.
It's a combination of Python and Bash actually.
And yeah, it drives us crazy sometimes.
We solved some problems, but not all of them still.
And when Yi needs to talk about your table names and you give
the schema with double quotes, and then you want to experiment
in DBLab to find some plans or something, it sometimes is broken
just because of problems with backslashes and passing these pieces
between different components.
I would prefer keeping everything lowercase, that's it.
In post-process.
Right?
Snake style, right?
Michael: Yeah.
Nikolay: And that's it.
And just forget about it.
Michael: Yeah, plays.
Pay for plays.
Nikolay: I wanted to mention the list of gotchas I saw actually
this is like as we discussed right before this recording I didn't
know this the word gotcha until 2006 or 2007 and this is exactly
when this list was published.
And I remember we had 2 lists, 1 for Postgres and 1 for MySQL.
For MySQL, it's outside of scope, right?
Again, even more than nulls.
But this list, I'm looking at it right now and I'm very first
of all it's survived how many years like it's insane 18 17 years.
Wow.
And I'm looking at this, it's quite short list at SQLinfo.de,
right?
And almost everything is crossed out.
Good.
Michael: Yeah, this I've never seen this page before.
This is really cool.
And yeah, maybe more than half of them have been cost
down.
Nikolay: Yeah, account start is very slow.
Let's just laugh at it.
Michael: Oh, and we had an episode on that, right?
Nikolay: I think we should.
Unicode means UTF-8 left as well.
Well, yeah.
Yeah, constraint checking is interesting, but maybe let's not
dive into it.
And finally, I wanted to highlight what else is left.
Only 4 items.
5, Okay, 5.
But 1 of them is upgrades, upgrades, related stuff.
And I was discussing on Twitter, on X, I was discussing this
topic over the last few days.
I just checked documentation of 3 major managed Postgres providers.
So RDS, Cloud SQL, and Azure Postgres.
I saw that all of them say, okay, upgrade is done, major upgrade.
Now it's time, don't forget, now it's time to run ANALYZE.
And they say ANALYZE like they give you a snippet, ANALYZE semicolon,
which means it's single-threaded as well.
First of all, they don't include it.
None of them include it into automation.
But we don't talk about managed Postgres providers today, we
talk about Postgres.
My question is why pg_upgrade does not include...
It prints only some text in there, but who reads the text?
Many people will say, for those who don't read, it's on their
shoulders, it's their problem.
But I cannot agree with it.
Michael: I could even make a case for the gotcha here is that
statistics are not preserved on major upgrade.
I think if you asked somebody that didn't know whether they would
expect the statistics to be wiped or to be preserved, somebody
maybe doesn't understand how difficult it might be or like the
catalog, like the reasons why they're not preserved, you'd be
forgiven to think like in a in the most advanced open source
database that that might be that might happen even though it's
complicated and I think there has been some work.
It should
Nikolay: happen and Everyone agrees on it, just not solved.
Michael: Yeah, so I think that's the main gotcha.
And then, obviously, the solution at the moment is we need to
want to analyze.
Nikolay: Yeah, let's unwrap a little bit.
So we talk about statistics, which is required for a planner
to make right decisions.
If the database is not trivial, like containing like 100 rows
in each table, that's it.
If it's not trivial, we need statistics.
Without statistics, database is slow.
And what happens, I think in a zillion cases, it happens.
Like It's hard to estimate, but when I posted this tweet, I received
several replies immediately that, oh, we got beaten by this badly.
Some person wrote, we learned this hard way, obviously.
And this is like what happens all the time.
And recently, during summer, we also like failed to help a customer
with it.
Like we discussed major upgrades, but we...
From now on, I'm pointing out, like, don't forget, don't forget,
don't forget.
But we discussed upgrades, we discussed some, like, complicated
stuff, and then they went to upgrade, and next Monday they say,
oh, today they are in Europe.
So in the morning, it was already by the evening in Europe.
And they say, we had nasty database incidents today because guess
what?
We forgot to run ANALYZE.
And this is huge gotcha not only by managed services, they could
do it but I don't care too much about them.
This is a gotcha of pg_upgrade.
I think it's very well understood, there is work in progress
for export and import of statistics, like quite a long thread
in hackers already and commitfest entry.
It's still not done, but there is hope.
There is 1 more thing here.
So I think pg_upgrade definitely should have it.
And also pg_restore, but let's return to it in a few minutes.
So when someone says you need to recalculate statistics yourself
after upgrade, pg_upgrade.
I definitely would expect pg_upgrade to do this for sure.
But okay, it just prints do it.
pg_upgrade prints vacuumdb, I think, no?
Or in stages, or like vacuumdb in stages.
It has this vacuumdb analyze only in stages.
2 options.
Analyze only means no vacuum and just Analyze.
In stages means first it gets only 1 bucket for each table, for
each column actually, right?
And then some midway until your...
And then default_statistics_target or individual column based
setting for each column.
default_statistics_target by default is 100, 100 buckets.
This is what defines how much Postgres planner will know about
distribution in each column, right?
And it can be adjusted globally or for individual columns.
But...
Michael: Almost like a sample size, like the sample size increases
of what it samples.
Nikolay: Yeah.
And I quickly realized this is not what you want to do in OLTP,
because in OLTP, you either include Analyze inside maintenance
window.
In this case, there is no sense to run it 3 times, or you just...
Or that's it, there is only option actually.
Like, I don't trust in opening gates with weak statistics.
It will be like unpredictable again incident.
Michael: Yeah.
Yeah, risky.
But this feels like a topic that might be worth a deeper dive.
Nikolay: Right.
Right.
I just wanted to point out that when people say Analyze, it's
just 1 threaded, right?
And you expect Postgres has a lot of parallelism implemented.
But when you run Analyze, it's always single threaded.
This is unexpected as well, right?
Michael: Good 1.
Yeah, I like that.
Nikolay: It's kind of gotcha inside gotcha we have matryoshka
style gotcha here right Russian doll style
Michael: what do you do you recommend doing like just kicking
off a bunch of queries analyzed tape like per table
Nikolay: I recommend partition I recommend vacuumdb
Michael: nice okay
Nikolay: And speed up and go full speed inside maintenance window.
Again, it's a different story.
But this vacuumdb, it has --jobs
or -j.
You can define.
And usually, we take as many courses we have on the server and
go full speed inside maintenance window to achieve.
Analyze should be quite fast if you don't do vacuum.
There is also gotcha a little bit in vacuumdb options because
vacuumdb's, --analyze will also do vacuum.
So there's
Michael: also analyze only or
Nikolay: there is analyze only.
Yeah, but it's easy to overlook.
There's 1 more gotcha here inside all this.
When people go with manual single-threaded analyze or maybe multi-threaded
but just explicit analyze.
I've like quickly, it was interesting, I was thinking someone
said analyze can be super expensive.
I agree, but even if it's like, if the default_statistics_target
is 1000, it's not that expensive.
But back to this, can we throttle it?
For example, you want it to go very slow, because autovacuum
has autovacuum, doesn't have actually
Michael: a vacuum_cost_delay also?
Nikolay: Yes, there is vacuum_cost_delay and vacuum_cost_limit.
By default, it's off because I think the cost limit is 0, meaning
that it's not checked at all.
Not cost limit, cost delay is 0, like no check.
But autovacuum has like mirrored pair of these settings.
It's throttled.
Long story short, autovacuum is throttled usually.
And we actually usually fight with our customers a little bit
to increase this, make it even more aggressive.
And managed providers already did part of that job, which is
good.
But if we run it manually, it's not throttled.
It will go full speed.
I doubt you will need to throttle it, but some people want, okay,
let's throttle it.
Question, how?
We have a vacuum limit, vacuum_cost_limit, vacuum_cost_delay.
We can set vacuum_cost_delay from default 0 to some point.
But is it about Analyze, or it's only about vacuum?
Based on just the naming, it's not about Analyze.
It's about vacuum only.
Michael: Right?
Yeah, that would be my guess.
But by the line of your questioning, I'm guessing it does affect
your analysis.
Nikolay: Right.
I go to documentation and I see vacuum_cost_limit, vacuum_cost_delay,
I think.
And I see description.
They talk only about vacuum, they don't talk about analysis.
So I make conclusion, it's only about vacuum.
But some part of my very long-term memory tells me, is it really
so?
And then Sadek Dusty, who helped me a lot with, and still helping
sometimes when I write at how-to's and reviews How-To's, I'm
very thankful for that.
I have already 94 how-tos.
So I wrote how-to, how-to Analyze.
And right now I need to add this.
vacuum_cost_limit, vacuum_cost_delay, they affect Analyze.
If you scroll up to the beginning of section, you will see that
it talks about both vacuum and Analyze.
And we also, using AI, I checked source code and quickly found
that there is a function called vacuumExec, which works with
both vacuum and Analyze.
And this confusion comes from source code, propagates to the
documentation, and then to our heads, right?
This is gotcha as well.
So you can use vacuum_cost_limit, vacuum_cost_delay, to throttle
Analyze, although I usually don't do it because I think we need
to go full speed.
Michael: And in the 1 example we're talking about here, we want
to go faster, not slower.
Nikolay: Right, right.
But it's interesting, right?
So there is some small confusion.
I think there are many such confusions.
But they are slowly fixed.
They are slowly being fixed.
Michael: Great example of 1 where the documentation
wasn't helpful in avoiding that 1.
So yeah, nice.
I've got a few more beginner-friendly ones that maybe we could
rattle through.
I'm not even sure you'd consider them all gotchas, but 1 relates
of places.
I think that not only catches...
Well, you don't think it's a gotcha?
Nikolay: I think we should exclude this on the same, like, we're
using the same approach as now because it's a huge bag of, yeah.
Michael: Cool.
And then another 1 that I see catch out, well, a couple that
I see catch out beginners, again, maybe this is another huge
topic, but access exclusive locks on like just basically DDL
blocking selects, like blocking every, blocking inserts and like
you know all sorts of people getting, I think people don't realize
because if they're working on small databases at first, they
start to grow in confidence of how to do things.
And then it only catches them out once the startup's got really
successful and the table's much bigger, that kind of thing.
Nikolay: Yeah, I think there is some memes with Gauss distribution,
or how it's called, right?
Gaussian distribution.
And first, I think schema changes are painful.
In the, like, schema changes are super painful.
They require expertise, like, beginner, right?
Then in the middle, you can imagine, oh, Postgres has transactional
DDL, everything is fine, we just need to wrap everything into
1 transaction and, and rely on Postgres great capabilities of
transactional DDL.
Michael: Yeah, or just concurrently and think
Nikolay: yeah Yeah, MVCC works.
Well, it doesn't block sell Excel X are always working you can
you can have a lot of Like exciting stuff in the middle and then
on the on the right you say post this doesn't look was this has
come Transactional detail, but you cannot use it
Michael: Well the in on the right has to be the same as the left,
doesn't it?
Schema changes are painful.
Nikolay: Yes, they are painful, transactional DDL cannot be used
in many, many cases, and you can shoot your feet very well many
times.
So this is it, I think.
And a lot of gotchas there.
Michael: Another 1 that's not necessarily, I'm not sure if this
counts as a gotcha, I'd be interested in your opinion but it
does have seen it catch people out and I guess it's a little
bit of a foot gun and that's you know you see the memes about
this as well actually DELETE or UPDATE without a WHERE clause
and then just suddenly seeing you know instead of 5 updated.
But if you think like it's not Postgres, why do you say it's
not Postgres out of interest?
Nikolay: Because the SQL standard does it like defines this and
any relational database which follows SQL standard to some extent
has it.
Michael: Yeah.
But I've seen clients catch these, But the ones that ship, well,
the only, the psql doesn't.
So I don't think it's unreasonable that Postgres does it this
way, but I've definitely seen it catch people out.
Nikolay: I agree.
Blame SQL standard.
And there is extension to forbid it.
And also it was maybe 1 of the very first hacking sessions with
Andrei.
We implemented some setting, GUC setting in Postgres, which would
allow administrators to, I don't think error, warning, like completely
forbid or just warn, I don't remember.
I think error should be here.
But it was not accepted, of course, because I think it was discussed
many times.
I mean, it's possible.
And right now, as I see, I think PostgREST does it, right?
PostgREST...
Michael: Oh, does it?
Nikolay: So usually right now it's sold, if there is some middleware,
it's sold there.
So if you develop API using PostgREST, for example, I think, Maybe
I'm mistaken, but I think there is a way.
If there is no way, it's a good idea to implement, actually.
Michael: Yeah, it would be, yeah.
Nikolay: Yeah, so if in URL of your HTTP request of RESTful API,
there is a command to run delete without any filters, I think
it will not do it it will not go to progress with it so but inside
database I don't I don't think it's going to change right and
we tried actually so
Michael: yes with the well and we tried what do you mean each
right again
Nikolay: we had a session yeah we had a patch and they submitted
it, but it was a small discussion and chances are close to 0
that it will be successful.
Michael: Patch in psql or somewhere else?
Nikolay: No, not in psql.
We wanted to have a Postgres setting, GUC setting, to just
allow administrators to forbid unspecified, I don't remember
how we called it, maybe unspecified delete and update, when you
don't have the WHERE clause at all.
Just like,
Michael: cool,
Nikolay: don't do it.
There's a truncate for if you want to hit right an update of
whole table I don't know yeah but again like right now it's in
middleware usually sold in middle where You can have triggers
or something to protect yourself if you want.
Or this extension if you're on self-managed Postgres.
There is some extension for it.
I wanted to mention 1 thing, step back to DDL.
My favorite example is, do you think just adding a Column, you
cannot put your traffic down or your Database down?
You can, if it cannot acquire Lock.
It starts blocking all Queries to this Table, even SELECTs.
Like, I cannot acquire Lock, everyone else can wait.
Even SELECTs who come with access Share Lock, don't touch this.
That's it.
And that's not good.
And this is super unexpected and a super big surprise.
Michael: Yeah, especially because you could even test that.
You could even make a fork of production or clone it run that
on its own in isolation where there is no Lock and it runs super
fast so you think this is safe but even though it was super fast
it does require that heavy Lock and that blocks everything else
definitely a gotcha that's a great 1
Nikolay: yeah yeah and there are many others in the area of DDL
but this I find like affecting every single project which had
like at least some growth like I don't like to 1000 TPS and it's
already noticeable it's not noticeable in tiny projects
Michael: yeah yeah which I think is part is kind of part of the
problem right or like at least makes it worse because anybody
that gains experience on smaller projects as they grow Get some
confidence that these things work or they're fine and then it
change changes at some point
Nikolay: exactly
Michael: so you've got a great article in this actually I think
that I can share about DDL it's all about DDL changes I think
Nikolay: yeah it's yeah yeah it's this this 1 is definitely included
I think this 1 like has a different blog post about like I have
few about Details okay what else
Michael: well in that in that area of making big changes partly
by mistake you know that the DELETE or UPDATE without WHERE Do
you think people would expect to be able to undo something?
I'm not thinking just from people with experience of relational
databases, but the fact that if you do something like that by
mistake, that you have to go to your backups to get that data
back.
If I was in my early 20s or just out, you know, if I was in my
teens, all other software I use has the ability to undo something
if I do it by mistake.
So it does feel slightly unexpected, potentially if I've grown
up in that world, that I can make 1 mistake and not be able to
undo it.
Nikolay: And what Database you can?
I mean, of course, there are like...
Michael: Yeah, I don't know.
Nikolay: Time travel or...
If branching is supported, you can...
But it's still like not super simple, right?
Well, yeah, I don't know.
I mean, there are several projects which try to solve this problem
and I mean the data specific databases supporting branching for
example or even other I think Have you heard about this new company
where Michael Stonebraker is participating?
Michael: I've
heard of it, I don't know much about it.
Nikolay: DBOps?
Or how is it called?
I don't remember.
Not DBOps, DBOS.
Michael: Yes.
Nikolay: So I wonder if this feature will be inside it.
I don't know.
For me, it's not expected.
I cannot name this gotcha because it's a hard problem, and we
didn't get used to it, to having it, because it's rare.
But if we go to young people's minds, there are many gotchas
for them, right?
Oh, this is working not as I expected.
So naming is hard just because it should avoid gotchas.
If you chose some name, you should think about other people and
think to be very predictable.
This is the art of naming.
And we just discussed this vacuum_cost_limit affecting analyze.
So yeah, and in young minds, gotchas, because gotchas world is
much bigger because they don't understand the logic in many places,
how it's like, like common practice in many places, right?
And that's why many more unexpected situations.
Michael: Yeah, fair.
Nikolay: Yeah, by the way, if you run pg_dump on a very large
database, like 1 table after another, And then we deal with snapshot,
right?
With snapshot, it's a repeatable read and snapshot.
And even if you use multiple workers for pg_dump, they are synchronized
reading from the same snapshot.
But what if some table is dropped?
Will it be present in this table?
I'm not sure, actually.
I just think there is a God check
Michael: here.
Nikolay: If during dump there are some schema changes, can we
have some inconsistency in backups?
Not in backups, in dumps.
By the way, this confusion was resolved recently, right?
We didn't mention it in the previous discussions of confusions,
but the pg_dump page is not saying it's for dumps anymore.
So dumps are not backups.
Michael: That's coming.
Nikolay: Yeah, in 2018.
Okay, okay, okay.
We will discuss this additionally.
But this is a big confusion when the computation says pg_dump
is a backup tool.
It's still so, even in 16.
And now we have all rights to say it's like all people who said
pg_dump is not a backup tool, we are right, because finally in
18 it's already changed.
I think it won't be reverted, I hope so, this patch.
But back to our topic, pg_dump can be expected to be a tool for
backups just because of documentation.
Then you have quite a painful process of realizing it's very
bad for backups.
So it's a gotcha, which is already fixed in the future in Postgres
18.
And if we, in this area, pg_restore doesn't run analyze, which
hurts every second attempt to restore a big database, even with
experienced people like I am.
Because, again, it's like with pg_upgrade, we discussed it.
pg_restore, You restore a huge dump, you start working with
it, but it's slow, and in plans you see where you expect a simple
index scan, you see bitmap scans, bitmap index, bitmap heap scan,
because there is no vacuum, there is no analysis.
It also should run on a vacuum to collect visibility maps.
Or
Michael: it might not be unreasonable to think that maybe backups
would include the statistics and then restore would restore the
statistics.
Nikolay: Physical backups, I agree.
Logical backups, I disagree.
You don't include indexes, which is derivative from data itself.
Statistics is also derivative of data.
So you include description, like create index.
There should be, maybe should be some word like analyze in there.
I wanted to say create statistics, but it was analyzed already.
There's such word.
Why restore?
And restore could run it.
If we use pg_restore -j 8, for example, 8 parallel drops.
In this case, why are we not recalculating statistics?
Right now, everyone who deals with some automation, for example,
for DBLab Engine, we need it when provisioning is logical.
So it's dump restore and we need to move as fast as possible
multiple jobs, multiple workers for dump, multiple workers for
restore.
And then of course, vacuumdb, in this case, --analyze
to have vacuum as well and also -j, but People forget about
this and forget forgetting means like you expect it will be fine,
but if you don't run analyze yourself ideally in multiple threads
and multiple workers using multiple workers in this case performance
will be very bad and people start thinking oh Postgres is slow
have you heard about this Like I restore from dump and it's slow.
Michael: I have to admit I haven't heard that complaint, no.
Nikolay: Okay, I'm hearing it and I'm doing it myself almost
20 years.
I know about this.
Michael: No, I'm not saying it doesn't exist, I just haven't,
you know, maybe it's just not the category of problem maybe what
happens when people experience that is they don't noticed online
or I didn't know it's or they don't post they just go try something
else
Nikolay: yeah it's it's not a big deal if you restore, and it's
working, and then you want to deal with it 1 day later.
Because probably, likely, autovacuum will do it.
But If you expect to run fast queries immediately after restoration
from a dump, I'm talking about something which is 100% very well
known to everyone, who is at least a little bit experienced.
But it doesn't make it non-gotcha, it's still gotcha.
That pg_restore doesn't calculate statistics and doesn't run Vacuum.
Michael: I mean it's quite remarkable, I know we could go on
with this list for quite a while and there's like varying levels
of gotchas but I like that I like the list you sent me how how
short and brief it is and I I really think for many other databases
it would be a much much longer list I listened to well you're
listening to your experience of you know a couple of days of
trying to use MySQL at the beginning, and recently I was listening
to an interview with Tom Lane, and he mentioned, like, checking
out MySQL at the beginning and not liking a few things about
it.
And that feels like a really common experience.
And having worked with commercial databases like SQL Server and
Oracle, that often move much faster in terms of adding features
that enterprise clients really want.
There's just so many more gotchas that result from processes
like that.
So I imagine I could be wrong.
Nikolay: I think Postgres has lots of gotchas, lots of them.
Michael: But compared to other relational databases?
Nikolay: Yeah, it's a complex system, all complex systems have
a lot of gotchas.
Some of them are just very niche, very narrow and deep and so
on.
Some of them are like nobody knows about them.
For example, SELECT INTO.
This is what you can write inside PL/pgSQL, right?
So you write a FUNCTION or TRIGGER, and you write SELECT INTO
some variable, blah, blah, blah.
And then you think, OK, this SELECT is like maybe it's complex
SELECT, actually.
Subselect or, I don't know, joins and so on.
But in the end, you have a scalar or something, and you select
into a variable inside PL/pgSQL.
And then you think, I want to understand the plan, or I want
to just run it and try to troubleshoot, and you put it to psql,
what will happen?
If you put SELECT INTO blah blah blah, and then some regular
select from where order by you know
Michael: just an error no what happens
Nikolay: it will create a table
Michael: oh okay yeah
Nikolay: go select into is 1 of your lights it's great table
This I think just should be removed, like completely removed,
deprecated.
Select into creating a table, it's a DDL.
This is definitely a gotcha for everyone.
Michael: Yes, imagine all those parsers that just...
I know this is flawed for multiple reasons, but look at any query
that starts with SELECT, and like, oh, it's a read query.
Nikolay: Well, yeah, usually we say SELECT for update is acquiring
a heavy lock, blocking all writes.
Yes, blocking all writes on these rows, particular rows, not
the whole table.
But Select can create a table.
And we have create table as select already.
And this is what all people use, I think, who need it.
So select into probably just some very old stuff that could be
potentially removed, I think.
Michael: If it's not in the SQL standard
Nikolay: oh it's a good point if it is no chances right
Michael: yeah well probably not no cool anything else you want
to make sure we covered
Nikolay: maybe it's enough I think we covered less than 1% of
what we could.
This is my feeling.
What do you think?
Michael: Well, it depends what you count as gotchas and how niche
you're willing to go.
It turns out the more you know, the more gotchas you know about,
I guess.
Nikolay: So I could not resist and I checked the documentation.
SQL standard uses SELECT INTO to represent selecting values into
a scalar variable of a host program rather than creating a new
table.
It's definitely Postgresism and I think it should be removed.
Michael: Okay, cool, good 1.
Nikolay: Good, okay, good chat, maybe some kind of entertaining
more than useful, but I enjoyed it.
I enjoyed it.
Michael: Yeah, absolutely.
And feel free to send us any more that you've got or that you
Nikolay: Maybe we should think about specific areas like we did
for for now and maybe we should explore some areas from this
perspective what can be unexpected
Michael: Yeah, for sure. Thanks so much.
Nikolay.
Catch you next week.
Nikolay: Thank you.
You too.