Michael: Hello and welcome to Postgres FM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard.
And this is my co-host Nikolay, founder of Postgres.AI.
Hey Nikolay, what are we talking about today?
Nikolay: Let's talk about something we shouldn't do.
Michael: All the things that we shouldn't do with respect to
Postgres, right?
Nikolay: Of course, we are not talking about alcohol or sugar,
right?
Michael: Neither of which you've ever done on the podcast, yeah.
Nikolay: Well, I was drinking a lot of Red Bull on the podcast and
it has a lot of sugar, so.
But at least it's not alcohol.
Michael: True.
So there's a fairly famous wiki page, which I was actually surprised
is only about 6 years old.
It's probably the wiki page I link to the most in the PostgreSQL
wiki.
And it's called, it's titled, don't do this.
And it's a list of a bunch of things that are largely inadvisable,
but there's also a couple of other, you've written a how-to that
includes a lot of don't do this that aren't on the wiki.
And there was a really good page that you sent me that I've looked
at as well by Yandex who also have some additional don't do this
advice.
So we're going to pick some of our favorites from those and help
explain why not and what you can do instead.
Nikolay: Right, right, right.
So the wiki list is quite large.
It's not huge, but quite large.
And I like almost everything.
Not everything, not everything.
For example, between, I use sometimes between, you just need
to understand how it works.
So it's like including both sides of the range, both boundaries,
right?
I don't see anything wrong if you use it with an integer, for
example.
Michael: But that's what they say, isn't it?
I think that's what the wiki is really good at doing.
Nikolay: Well, if this list is supposed to be simple to memorize,
and if something is included, like, don't use between, and you
think, okay, I should avoid it.
Maybe actually you should avoid it.
But if we take between, SQL is such a rich language, we have
many things to exclude then as well.
For example, I don't know, like various things with join.
Like, this can bring us to the area, for example, always use
as, don't use aliases without as, right?
And so on, like these things, these kinds of things.
Because it can lead to confusion.
Don't use order by 123, or group by 123.
Actually which is good advice in general, but most of experienced
guys use it anyway because it's just short, right?
And like this list, I actually don't like this.
These kinds of items I don't really like, in my opinion, they
are quite shallow.
Michael: So I'm going to push back.
I think this isn't a list to be memorized and not looked at.
I think one of the benefits of having it written down with some
extra points, like for example, in the title, it says don't use
between in a parenthesis, especially with timestamps.
And then it has why not explains that it includes the boundaries,
whereas you probably don't want that if you're dealing with continuous
ranges like timestamps.
And then it says, when should you?
So it says, between is safe for discrete quantities like integers
or dates, as long as you remember that both ends of the range
are included in the result.
And then they say, but it's a bad habit to get into.
And I actually think that contains all of the subject you were
just talking about.
Nikolay: Well, let's maybe edit wiki and change the title to
don't use between for timestamps to make it clear.
Because for integers I don't see the problem at all.
Michael: So you could say like 0 to 9 and 10 to 19 or you know,
yeah.
Nikolay: With timestamps it's tricky because sometimes if people
don't spend time understanding, like thinking about what exactly
they include, what they don't, and they can make quite bad problems.
They can have troubles even without between, using just simple
comparison.
Also grouping of timestamp, truncated timestamps, like with that
date trunk, can be tricky as well.
You just need to understand that, like, depending on your data,
the edge can be tricky.
Sometimes it includes a lot of rows.
Midnight can be a lot of rows and you need to decide where should
it go to the previous day or next day, right?
Or sometimes it can be 0 rows on the edge because you always
have milliseconds.
Michael: Or it's even more precise than that, right?
Nikolay: Yeah, you need to think about edges and where to include
them.
And of course, if you use between, it's hidden a little bit.
I agree with this.
But in general, don't use between, especially with timestamps.
I cannot agree with this.
Michael: Okay, well, yeah.
I guess it's a wiki for a reason and edits are allowed.
Right.
But should we, should we focus on some of the ones we do agree
more with or like the,
Nikolay: Yeah, sure.
Choose anything.
Michael: Well, I like the not in, like, I think it starts with
the wiki.
The wiki starts with a bunch that I don't see as being that useful,
don't seem to trip that many people up.
But it gets to a few sections in, it gets to SQL constructs and
the first of those is don't use not in.
Nikolay: Yeah I would recommend reading Juk blog post Lucas said
about this like it's very well explained why it's bad and if
you remember I sometimes 1 day I mentioned the very nasty problem
I had with 1 of my startups in the past when we couldn't launch
properly for several months, losing traction and then I found
an issue related to nulls.
This exactly was related to this.
It was in the episode about nulls, 1 of our very first episodes.
And yeah, nulls are in general very tricky, and this is 1 of
the traps where it can catch you.
And you need to study 3-value logic, but for some persons, for
example myself, it will be always a trap.
I mean, I know I can, even with 20 years of experience, I can
be caught there.
That's why, like, you just need to practise avoiding these traps.
So basically if you have not in and you have 1 of the values
inside it now, it will always be unknown, so it will not return
what you expect.
Michael: Yeah, and I think the reason I think this trips
people up the most is that in and exists can be used say interchangeably,
but that's not true for not exists and not in.
And that's confusing like That's
Nikolay: confusing.
Yeah.
Now it's confusing in general.
Three-valued logic is confusing in general.
So now is a big trap itself.
It has just many faces and not in is 1 of these faces.
Michael: Yeah, well, I'll link to the I'll link to the episode
we did on nulls in the show notes for anybody that wasn't wasn't
a listener back then or missed that 1.
That also reminds me on the in the Duk list.
There was there was some schema advice.
So some general don't do this with your design.
And 1 of the things was around, like, constraints on columns
in the first place.
Like, it's the default to let, in Postgres at least, I think
in all databases I've used, or at least the ones I remember,
the default is columns can contain nulls.
Nikolay: Can accept
Michael: nulls.
And it's an interesting point that you make, that maybe that
should be flipped and not null should be the default.
Or at least when we're designing schemas, it could make sense
to do it that way.
Nikolay: Yeah, yeah, maybe.
At least when you design something, it's a good habit to consider
everything as not null.
If needed, the next step is to think about the default value
and only then accept nulls.
Yeah, just reduce the number of cases where nulls can appear.
And that's a great, I would say, style.
This is about style.
Michael: It does have performance implications, that 1 as well,
because if the database can know that there are no nulls in that
column, that can allow certain optimizations as well.
Nikolay: Well, yes and no.
Well, it depends.
Nulls are stored differently.
If you put some default value, it will be stored as is, right?
Unless you're adding a column for an existing table.
In this case, since Postgres 11, we have like a virtual default
and it's stored virtually, not physically.
But for all new rows, a default real value will be really stored.
And nulls are just stored as like a bitmap, right?
And, But I agree in general with indexes and so on, nulls sometimes
present some challenges in terms of performance.
Michael: I think also it can rewrite certain queries if it knows
for sure that a column cannot contain nulls.
If there's like a constraint on it.
If you've written, I guess this is a contrived example, but if
you've written where X is not null, It can just ignore that completely.
It can just ignore certain filters or certain operations.
Nikolay: Good point.
Michael: But yeah, this is not important in the grand scheme
of things.
Nikolay: Just not in is dangerous.
That's it.
Not in is dangerous.
Here we can put it to, I think the wiki page has it as well,
right?
And don't use not in.
Agreed.
With this statement, I agree.
Michael: What do you think of the next one on the wiki which
says don't use uppercase table or column names?
Nikolay: Again, it's a matter of style.
Right here.
It's a matter of style.
So I don't like uppercase in general, not only for identifiers.
I don't like it for keywords like select.
I prefer lowercase.
But of course, if you don't want to be required to use double
quotes, then don't.
Or just if you do it, you can avoid using double quotes, but
in this case you need to always use...
So, basically, if you don't have double quotes, names are like
case-sensitive search.
Michael: They're lowercased, right?
Nikolay: They're internally lowercase, but you can use mixed
case and it will be the same as lowercase, right?
So, no big problem if you just type all table names uppercase
until you start using quotes, double quotes or some of your software
starts using it and then you're in trouble.
So better to lowercase everything.
But sometimes we have migrated systems from other database systems
and in this case we do see table names like in camel style for
example, right?
In this case it's just legacy we need to deal with.
I personally like to lowercase everything because it's like,
like, economical reasons, like less, less actions when you type,
right?
That's it.
Michael: Yeah.
And underscores between words in object names?
Nikolay: Underscore, yeah.
Well, maybe sometimes, yeah.
So like in Python and Shell, snake style, right?
Michael: Snake case, yeah.
Nikolay: Snake case.
Michael: Cool.
So we're into the meat of the, and we've got, we've discussed
the timestamp stuff, and we've got a whole episode on times and
timestamp things so we can link that as well.
Nikolay: In general, I wanted to mention it in the beginning
but I forgot.
This episode is like kind of basic.
We like basic episodes, right?
Because we know many people are not database experts who listen
to us and it's good to not to dive into some very narrow problems
and deep but sometimes just to remind about some basics and it's
good if no new in this episode for you.
In this case, it's also good, but I hope for some folks it will
be useful.
Michael: I do think it's also useful sometimes as people that
have got a lot more experience to remember what it was like being
a beginner.
Chances are you've got people on your team or people you have
to help those new to this stuff and having a few more resources
that you can point people at as to like, why this is a bad idea,
instead of having to explain everything again from first principles,
I find that somewhat helpful.
Hence why I link people to the wiki so often.
But yeah, also in considering should some defaults change?
Can we make the wiki clearer?
Like, if you do know all this stuff already, it'd be cool if
you could help us make these resources clearer or give better
advice.
Is there anything that you advise people never to do in Postgres
that isn't on the wiki yet?
Could you update it?
So I'd love even more advanced people to think about how they
can make things easier for new folks coming along.
Should we make defaults different?
No beginner is going to be able to dive into the Postgres codebase
and argue on the mailing list for making a default different,
I don't think.
Nikolay: Right.
Yeah, many defaults are outdated, you know my opinion about this.
Michael: But this is a bit different, right?
We're often talking about GC settings, when we're talking about
those defaults.
This is more a design goal.
Nikolay: Yeah, here as well.
Well, again, I don't see how a nullable column can, like...
Not null can become a global standard, the global default.
But I can see how in a particular project it can become a code
style or schema design style standard easily.
Michael: Yeah, it's an interesting point.
Obviously, because we have to support backward compatibility,
it's impossible now, I think.
But at the beginning, you could.
Nikolay: Also, I guess, there may be some standard things here.
Anyway, I don't know.
Maybe there is a standard regarding defaults, maybe not.
Let's proceed to some more like bigger problems maybe.
What about...
Let's finish about small problems.
Don't use money.
Michael: The data type.
I know you find this funny every time.
Nikolay: Well, the wiki says don't use money.
They don't say don't use data type money.
They say don't use money.
Some communists here, I think.
So, yeah, money is a bad data type.
Why is it still there?
Maybe it's standard as well?
I don't think so.
I don't know.
So implementation is terrible.
You will start losing sense if you use it.
Michael: Surrounding's an issue, Partial.
There's quite a lot of pricing these days.
If we're paying for compute and paying for API, like tokens or
access to things, often we're paying fractions of a cent per
image, per minute, per whatever we're paying for.
So fractions of
Nikolay: a cent...
If you format transactions, you will start losing some money,
so don't do it.
Yeah.
Use numeric, but check the performance, obviously.
Michael: Yeah, I've also seen people use integers with like well
Nikolay: okay but what about fractions of cents for example again
Michael: well That's the idea like you can just move the decimal
place like if you store it as an integer and just with like
Nikolay: Well, okay, if you define precision in advance and then
follow this rule, okay, but why not?
Michael: I guess performance reasons.
I don't know, I didn't ask.
Nikolay: Right, right. Okay, good simple right. Don't use money.
What's next?
Let's choose some bigger topic.
What do you think?
Michael: Well, I think the primary key...
Yeah, let's go to...
Let's jump to your list, your how-to guide that we'll link up
as well.
Nikolay: Let's do it.
So yeah, primary key is quite a simple thing, just don't use integer
4 primary keys.
Consider them in the same manner as integer 2 primary keys.
Do it only when you fully understand the consequences and reasons.
I mean, like, integer 4 has a capacity of 2.1 billion rows and 2.1
billion values.
The maximum is roughly 2.1 billion.
And sometimes we reach this.
Yeah, positive, negative.
Well, it's interesting that we can use negative values, but usually
people for surrogate keys, they don't use negative values.
There is an idea, well, like to...
I saw it many times, people say, okay, we are approaching 2.1
billion soon, let's just start using negative values.
Okay, good luck with that in URLs and so on.
It's like, I think it's definitely possible, but the amount of
work is not trivial.
Again, it's better to convert to int8, which is not trivial
if you have a billion rows already or 4000000000 rows, I would
say probably it's a case of emergency already.
But our checkup tool suggests, I think it starts raising big
flags, yellow or even red, after 50% of capacity used.
By the way, it's an interesting problem how in one query to find
all tables checking pg_class and all other system catalogs which
have primary key, surrogate primary key, int4, or it can
be maybe a multi-column primary key, but one of the parts is int4
and then to check the current value in the sequence.
It's not trivial.
Unfortunately, I remember I was trying to solve it with pure
SQL, not possible, I downgraded to PL/pgSQL and have a snippet.
It's an anonymous do block or maybe a function, anyway.
And then in this case, it can scan your schema and then check
values in sequences and report the capacity for each int4
primary key case.
And again, my approach is very simple.
If you use int4 primary key, it's similar to int2 primary
key.
Because the data volumes we deal with usually are already so
big.
So, it makes sense if you do know what column tetris and padding
alignment are.
If you know how the storage is organized.
You always,
Michael: Yeah, you jumped to this before, like, I think you're
right to mention it.
But I think the larger argument is there's almost no cost to,
like, even when there is a cost to using bigint over int,
so even when you have considered column tetris, still the cost
is so small compared to the long-term advantages.
Nikolay: 4 bytes for each row.
Michael: Yeah, it's trivial.
Nikolay: But this is why people usually choose int4.
They say, okay, we will save 4 bytes.
We won't, in this table, we won't reach 2000000000 and we will
be saving 4 bytes for each row.
It's good for us and they choose it.
Michael: What you think is
Nikolay: like, I think most...
I'm talking about a conscious choice.
Michael: I don't, yeah, I think most choice is unconscious.
I think most people are just thinking it's default.
It's in there, like, yeah, maybe an ORM, maybe like a tutorial they're
following.
Maybe like, I want an integer primary key.
Why not
Nikolay: choose the type
Michael: that's called integer?
So I think most of the choices of this is not conscious.
Nikolay: I agree with you.
Yeah, behavior of humans is that they just choose defaults and
go because they don't have time to understand.
But why I'm talking about this so precisely?
Because I saw it many times, people are already approaching 2
billion, they have problems, it's hard to migrate without downtime,
it's possible.
And then we tell them, you know guys, not only do you have a
big problem and you'll be down soon, I mean partially down, not
accepting inserts anymore for this table, but also look at this,
ID and created at.
4 bytes, 8 bytes, so you even don't have any benefits from int4.
4.
It's so emotionally big, understanding that you have four zero bytes
for all rows.
So it's like, why did we do this?
Why did we do this?
I just, this is an emotion.
That's why I'm talking about it so much.
I just, I saw it in many teams.
I'm just sharing experience here.
Michael: Yeah, and you're totally right to mention it, that a
lot of the time due to alignment padding we don't even get the
benefit of the saved 4 bytes.
But what I meant was more, like, so totally right to mention
it.
And I think that will shock people.
But even without the fact that there's sometimes zero benefit,
I think it's still worth it.
Nikolay: Right.
Well, in the end of the day, four bytes for each row, it's not,
it's not a huge saving for a billion rows.
It's just 4 billion.
But it's not, not, not a lot, right.
Noticeable probably, but not a lot.
Michael: Yeah, and even if you add up the fact that that's, it's
probably duplicated in some indexes, like you probably have several
indexes that involve the primary key, like maybe some
multi-column indexes.
Maybe you've worried about memory, like, but yeah, it's
not big.
And the time you will realize that it wasn't worth it is the
one project.
Let's say you have a hundred tables and you've put them all as BigInt
and instead of, or you've used int4 for all of them.
And only one of those tables has to be converted to int8 later.
That project you go through to do that conversion, you're
going to realize we'd have been better off going with int8
for all of them.
You only have to go through one of these projects once to realize
it's just not worth it.
The time investment alone.
Never mind the stress, like, under pressure.
Nikolay: Interesting that the wiki list doesn't have this at
all and it doesn't talk about primary keys at all and it says
don't use serial. But it's not about the number of bytes, it's
about in general serial is not a good thing.
So I mean, I guess bigserial is also a bad thing in the opinion
of people who wrote it.
So interesting, right?
I mean, it's quite common.
We could edit, yeah.
Next, choose anything.
What do you think?
Michael: From your list, we've talked about NULLs a bit already.
I think Transactional DDL and DELETE.
DELETE is a great one.
DELETE a lot of rows with one command.
Nikolay: Massive DELETE is massive.
Yeah, I had incidents.
It was on weaker storage, but it was very painful incidents when
just deleting 10 million rows led to more than 10 minutes outage
and a lot of money loss.
So yeah, it's not easy to delete rows in non-partitioned tables,
in large tables.
So it's a big task actually if you need to clean up.
And maybe I told you like one day I went to the VLDB conference which
is probably like the oldest, maybe in the area of databases, the
big conference, mostly with academic people.
It was in Los Angeles and it was one of the keynotes saying data volumes
grow so massively that we need to study how to delete data and
not how to store it, or how to find things which we need to delete.
But with Postgres, knowing how MVCC is organized in Postgres
and how DELETE works, you need to understand, without downtime,
without big stress, DELETEs should be batched.
And the batch size should be...
When I was young, it was difficult for me to understand how to
find the proper batch size.
If you take batch size one, probably too much transaction overhead.
And it will affect this throughput as well.
Like you will be deleting fewer rows per minute or hour, your
background jobs, right?
Michael: Yeah.
Well, I tend to see like low thousands.
Is that how, I guess it depends, but no, what do you tend
Nikolay: to do?
I have a simple approach.
Remember our very first episode.
I actually already described this and you see, like, reminding
things it's also useful sometimes.
So I have a very simple approach.
It's based on what our final goal is.
Our final goal with OLTP systems is that systems should be working
fast in general.
For humans, fast means below 100 or 200 milliseconds, right?
Michael: Nice,
Nikolay: yeah.
1 second is quite slow, 10 seconds is very slow.
Deletes can block some people, some other transactions.
Plus, like, they can lead to I/O.
Effects, like, and so on.
So I prefer batches to last not more than 1, 2, 3 seconds.
In this case, we know negative effects won't last 1 or a few
seconds.
And even in the worst case, we will be blocking, for example,
someone for just 1 second, it's quite slow, but not terribly
slow.
Yeah.
If it's already 10 seconds, it's quite too slow for people and
some of them, like, we might start losing traffic if we talk
about web projects or people start complaining and leaving us,
right?
If it's 100 milliseconds, it's good, but probably too small.
So batches like half a second, 1 second, in my opinion, are perfect.
But sometimes it's hard to understand the duration in advance
for all batches.
You try a few batches, they are fast, but over time they can
degrade.
So you need to keep in mind that if you're batching and deleting
according to some order by, for example, timestamp deleting very
old data first, then new, new, new.
You need to control vacuum because probably your delete trying
to find the next batch will be scanning too many dead tuples.
I mean, index will have a lot of links to, pointers to dead tuples,
and performance of a single batch delete will degrade over time.
Not to allow it, you need to control vacuum behavior and maybe
to do vacuum yourself from time to time, cleaning up dead tuples
and let index be fresh.
Or an additional thing, like sometimes I found myself, maybe we
need an episode about delete actually, or massive operations
if we haven't had it.
But sometimes I just decide, okay, I like stateless queries which
delete batch after batch and we don't remember the state.
But sometimes, like instead of dealing with vacuum myself, I
just delegate it, like I say, okay, I will memorize, I will make
my script or program memorize the state and just know the latest
ID or timestamp deleted and start from there.
It's similar to key set pagination basically.
Michael: Yeah.
It's
Nikolay: not similar, it's it.
In this case, you don't depend on the vacuum behavior and can
go faster and so on.
Michael: The other time I've seen this cause real issues is when
people don't see how many deletes, for example if they, let's say
it's like a SaaS account and they're deleting an account and
then that account has users and those users have events
and these like the amount of deletes a single delete can cascade
to can be very different depending on like if you're deleting
a large account that's a very different amount of deletes than
if you're deleting a small account so I'm guessing this is where
you avoid delete cascade on cascade.
Nikolay: Yeah that's interesting. And in the systems I built fully,
which were built fully under my control, I always tried to avoid
cascade deletes.
But surprisingly, I saw quite big systems which use it.
Yeah.
With many dependent objects deleted and so on.
And I don't know, I think it depends.
You need to think about it in advance, of course, and maybe just
rely on it.
There might be a hybrid approach developed.
So if we know this object is not huge, we delete it relying on
delete cascade.
But if we know the object is huge, maybe we perform like the last
delete, deleting the main object, we clean up asynchronously before
it and only then we delete.
Michael: In patches.
It
Nikolay: depends.
Right, right, right.
It depends on a particular system, but the surprise to me was
to see that there are good systems relying on cascaded delete
with many, many dependent objects, like thousands, tens of thousands,
and kind of okay.
Performance, I would say, as always, it's worth thinking in advance
and testing.
Just testing your system, your environment hardware, performance,
imagine the worst case, test it, see how it works, and then follow
this rule, like 1 second is good, half a second is good, right?
Michael: Yeah, I like that.
I like that a lot.
Nikolay: Yeah, but we actually, since we spoke about, like, this
is kind of an episode about basics, of course, we forgot an elephant
in the room, right?
If you delete without any conditions, without a WHERE clause.
From a table, it will be slow if the table is big, but also it
will be interesting to see that sometimes the table is not like,
the space is not immediately available or like it won't be your
disk space immediately.
Because delete consists of 2 processes.
First is your delete, synchronous, and then vacuum, which really
deletes physically the data tuples.
Michael: That isn't where I thought you were going with that.
I used to work on a tool for SQL Server, Microsoft SQL Server,
it was like a plug into the IDE that they have, that Microsoft
has.
And one of our favorite features, one of the features people loved
the most was a warning to say, did you mean to...
It was for delete and update.
Did you...
If you try and run delete without a where clause, it would warn
you before running it.
If anybody's out there who's writing Postgres IDEs, please add
that feature.
It's so helpful.
Nikolay: Actually, yeah, there is an extension to prohibit this.
And actually, it was funny, I think the very first thing we developed
with Andrei was this exact patch for Postgres, but it was rejected
by
Michael: Oh, for Postgres?
Nikolay: By hackers, yeah, yeah.
But, like, we just wanted a warning to be produced or maybe the
ability to forbid wireless deletes.
So Yeah, and yeah, let's maybe continue.
Michael: But IDEs don't have to worry about the core Postgres.
It can be implemented on a case-by-case basis there, at least.
Nikolay: This is a big mistake if you forget where and execute
it, and sometimes we don't need a semicolon in the end.
For example, in PSQL, if it's hyphen C and you write something,
it will execute it.
But of course, you need quotes.
Sometimes we don't need semicolon if it's a single query execution.
In this case, it can be terribly bad.
So you deleted everything.
Right, right, right.
Okay, but in general, yeah, in general, delete is tricky.
So.
Michael: Oh, I have one more beginner- friendly one.
From the quick list.
Don't use select star, or at least in application code.
I think it's useful for ad hoc queries and exploration.
How do you feel about this one?
Nikolay: Yeah, but do you remember what should be used instead?
Because I don't like like 50 columns to be listed also.
And then you understand that that's all of them.
Right?
Michael: Well, but...
So I think there's a few reasons for this.
Firstly, do you really need 50 columns?
Like, what are you doing that requires 50 columns?
Nikolay: There are so many cases.
This advice cannot be generic.
Because, for example, if I define a view which should consist
of all columns of the underlying table, I will definitely use star,
then later if I need to add a column I redefine view, and I don't
need to rewrite this query, right?
And I know everything is included.
There are many different situations.
Of course, it's a minimalistic approach.
Don't take too much, right?
In general.
But listing all columns, if I need all columns, I would say I
would use star, an asterisk instead.
Michael: So in application code, I think it can break in unexpected
ways.
So like when you if you then add some columns to that, can your
code handle more columns coming back in the future than the current
depends.
Nikolay: If I put the star, of course, I think about future changes
of schema.
I do think about it.
Michael: Cool.
Well, okay.
Interesting.
Nikolay: You know, in Postgres, you can say select table1 from
table1.
Michael: Or just table1.
Nikolay: No, no, no, no, it's different.
I mean, table1 is just selecting everything, right?
Michael: You can do like.
Nikolay: Select table name from table name.
It will give you just 1 column, but of a record type.
Like, kind of a virtual data type.
And everything will be collapsed, and you can unwrap it later
in your queries if it's a subquery.
But this is powerful; you don't need to list all columns.
And it's better than star because sometimes you just need...
This is the magic of Postgres, where maybe First Normal Form
is broken, actually, because you basically wrap everything into
a single column.
Okay.
Michael: The
Nikolay: whole table.
Which is great.
And then you can, if you're inside, for example, PL/pgSQL
context, it's perfect to do this.
You just define a record variable, and you can insert a whole
row with all these columns into this row.
Later, if there is evolution of schema and more columns, this
code will still work.
But depending on your language and driver, I don't know what
will happen if you select table name from table name limit 1,
for example, with some WHERE clause.
I don't know.
It's worth checking, but it's interesting.
This is when you said if we have more columns when they're needed
all right.
Michael: yeah well the time I see this used the most where
I like the reason I think this advice is good is I so often see
I think mostly as a result of ORMs people selecting every
column when they only need 2.
Nikolay: Yeah, yeah, yeah.
This minimalism approach, I get this.
But I have a student right now who writes Python code.
And sharing experience, I just see the code which is written
and I always say like, oh, you define this function but you use
it just once.
Why do you need the function here?
You define the variable which you assign to a different variable
and then you just use it once.
Why do you do this?
You had some constant you defined and used it once.
Just like, why do you need these things?
And here I see a similar pattern.
If we select column names, what if we rename these column names,
right?
We need to go there and rewrite it as well, something like this.
Well, it depends on the situation, of course, but I don't like
the idea that we will list all column names many times if we
know we need the whole row, for example.
It's just like observations trying to...
There is minimalism in star as well.
This is what I'm trying to say.
A different kind of minimalism.
So there is a trade-off here.
Michael: So definitely not always don't do this, but I'd say
don't do this without thinking.
It sounds like.
Nikolay: Right, right.
You need to consider the code and the future evolution, and so
on.
Michael: Nice.
Do you want to end on any that you actually do think are universal?
Nikolay: I don't know.
It's hard, actually.
Michael: It always depends, right?
Nikolay: Yeah, maybe about transactional DDL.
Postgres is cool.
It has transactional DDL until you cannot use it.
And most really heavily loaded systems understand that transactional
DDL is some kind of myth.
I mean, you need it, but to really apply schema changes without
downtime, you need to break it.
Create index concurrently is non-transactional, right?
We discussed it, you mentioned that it has 2 transactions.
If it's interrupted, it's not fully rolled back, so it's not
a transaction already.
You will have an invalid index left, leftovers of your actions.
If you want to change schema in any aspect, If you want to do
it without downtime, you usually need multiple transactions.
For example, if you want some foreign key to define or some check
constraint to define, not now, you will always need, and you
have already a big table loaded.
You will need to think about multiple steps, definitely, and
be ready to roll back 1 of it and go retry.
So, transactional DDL is not easy.
This topic is not easy.
It's not like, oh, we have transactional DDL, all problems solved.
No.
In a highly concurrent environment, it will actually put you to
downtime if you just blindly use transactional DDL.
And create index concurrently is a perfect example.
It's not transactional.
If you use create index, which is transactional, you have downtime.
Partial downtime.
Michael: I'm trying to think if drop index concurrently is transactional.
Nikolay: It needs to acquire an exclusive lock.
It's similar to dropping a column, but Postgres doesn't offer
any tools for 0 downtime drop column.
Drop column cannot be 0 downtime unless you cook it properly
with a low lock timeout and retries.
Drop index concurrently, it's shipped with Postgres; this tool
exists, great, but it's not transactional as well because
it might fail as well.
Right, for example, like, and well, in terms of leftovers, no
leftovers.
Michael: No, no, no.
But it might fail, that's the good thing.
Nikolay: It's just to acquire a lock gracefully, right?
Not to block selects or other queries which came after you started.
Michael: You've worded this quite well in your how-to, but the
idea here is don't assume that Postgres having transactional
DDL will make all your problems go away.
You're still going to have some complex...
Nikolay: I mean, it has it, but in many cases you cannot use
it and in heavily loaded systems you absolutely cannot use it
as is.
You need to cook it properly and have a lot of things around.
A lot of dances need to be learned right
Michael: because of the heavy locks because you normally
Nikolay: Yeah, yeah because of locking issues basically exactly
Cool, right
Michael: Nice one.
Well, I think I'll link all of these up in the show notes.
Maybe the wiki will have changed by the time this episode goes
out.
It'd be cool to hear if anybody thinks there should be additional
ones that should be in there.
Nikolay: If we say don't do it, don't use transactional DDL.
I mean, use it, but only partially, like as pieces.
You cannot use it in 100% of everything, like all schema changes.
You cannot use create index, which is transactional.
Don't use transactional DDL.
I hope people understand when I say this.
Don't use transactional DDL.
Michael: Yeah, I guess create index is the one that will catch people
out if they don't know about
Nikolay: locks.
You cannot drop a column without proper lock timeout and retries.
And this is already beyond a single transaction.
Michael: Well, and in the past, even adding columns...
Nikolay: You cannot add a column without default.
You cannot add it.
It's the same as dropping a column.
You need an exclusive lock and if there is an ongoing long transaction,
you're blocked and you have troubles.
It means you need some additional orchestration.
Michael: Nice, I think you'll find it hard to word that for the
wiki, but I think it would be a good addition.
Nikolay: Don't use transactional DDL.
Okay.
Michael: You'd have a lot of caveats.
Nikolay: Right, well it's very similar to many items we discussed
today.
Michael: True, true.
Nikolay: Right, this advice.
Okay?
Michael: Yeah, thanks so much, Nikolay.
Thanks everyone for listening.
Catch you next week.