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.

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

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.