Postgres FM - Don't do this

Episode Date: April 5, 2024

Nikolay and Michael discuss several "Don't do this" lists about Postgres — picking out their favourite items, as well as some contentious ones that could be clearer, or not included. Here are some links to things they mentioned:Don’t do this (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_ThisHow to get into trouble using some Postgres features (how to by Nikolay) https://github.com/postgres-ai/postgres-howtos/blob/main/0016_how_to_get_into_trouble_using_some_postgres_features.mdDon’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-thisDon’t use NOT IN (jOOQ) https://www.jooq.org/doc/3.19/manual/reference/dont-do-this/dont-do-this-sql-not-in/ Our episode about NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Our episode on timestamps https://postgres.fm/episodes/timestamps~~~What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!~~~Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Hello and welcome to Postgres FM, a weekly show about all things PostgresQL. I am Michael, founder of PG Mustard. This is my co-host Nikolai, founder of Postgres AI. Hey Nikolai, what are we talking about today? Let's talk about something we shouldn't do, right? All the things that we shouldn't do with respect to Postgres, right? Of course, we are not talking about alcohol or sugar, right? Neither of which you've ever done on the podcast yeah well i yeah well i i was thinking a lot of red bull on podcast and it

has a lot of sugar so but at least it's not alcohol true so there's a fairly famous wiki page which i was actually surprised is only about six 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 um 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 duke who also have some additional don't do this advice so we're going to pick some of the our favorites from those and and help explain why not and what you can do instead 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 it's like including both uh both sides of how of, both boundaries, right? I don't see anything wrong if you use it with integer, for example. But that's what they say, isn't it? I think that's what the wiki is really good at doing. 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 so rich language, we have many things to exclude then as well. For example, various things with join. 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 one, two, three or group by one, 2, 3. Actually, which is good advice in general, but most of experienced guys use it

anyway because it's just short, right? And this list, I actually don't like. These kinds of items I don't really like. In my opinion, they are quite shallow. 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 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 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 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 subtlety you were just talking about.

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. So you could say like 0 to 9 and 10 to 19 or, you know. Yeah. at all so you could say like 0 to 9 and 10 to 19 or you know yeah yeah yeah with timestamps is tricky because sometimes if people 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

or 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. 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 zero rows on the edge because you always have like milliseconds. Or it's even more precise than that, right? Yeah. Yeah, you need to think about edges and how where to include them and of course if you use between it's hidden a little bit i agree with this yeah but in general like don't use between especially with timestamps i cannot agree with this okay well yeah i guess it's a wiki for a reason and edits are allowed. Right.

But should we focus on some of the ones we do agree more with? Yeah, sure. Choose anything. Well, I like the not in. I think 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 a few sections in it gets to sql constructs and the first of those is don't use not in yeah i would recommend reading juke blog post lucas haddon right about this like it's very well explained why it's bad

and if you remember i sometimes one day I mentioned the very nasty problem I had with one 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 NALS.

This exactly was related to this. It was in the episode about NALS, one of our very first episodes. And yeah, nulls are in general very tricky and this is one of the traps where it can catch you. And you need to study 3-variable 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 you just need to practice avoiding this trap. So basically, if you have not in and you have one of the values inside it now, it will always be unknown. So it will not return what you expect.

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. 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 one of these faces. Yeah. Well, I'll link to the episode we did in the show notes for anybody that wasn't a listener back then or missed that one. That also reminds me in the Duke list, there was some schema advice. So some general don't do this with your design.

And one of the things was around constraints on columns in the first place. It's the default to let, in Postgres at least, I think in all databases I've used or at least 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. Can accept nulls. Can accept nulls, yeah. And it's an interesting point that he makes. 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. Yeah, yeah. Maybe. we're designing schemas it could make sense to do it that way. Yeah, yeah, maybe. At least when you design something it's a good habit to consider everything as a not null and if needed next step think about 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. It does have performance implications, that one as well,

because if the database can know that there are no nulls in that column, that can allow certain optimizations as well. Well, yes and no. Well, it depends. Nulls are stored differently. If you put some default value, it will be stored as is, right? And unless you're adding column for existing table. In this case, since Postgres 11, we have like virtual default and stored virtually, not physically.

But for all new rows, default real value will be really stored. And nulls are just stored as bitmap, right? But I agree in general with indexes and so on. Nulls sometimes represent some challenges in terms of performance. Well, I think also it can rewrite certain queries if it knows for sure that a column cannot contain nulls. If it'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.

Yeah, good point. But yeah, this is not important in the grand scheme of things. 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.

What do you think of their next one on the wiki, which says don't use uppercase table or column names? 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 uppercase in general, not only for identifiers. I don't like it for keywords like select. I prefer lowercase.

But, of course, like 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 it's basically if you don't have double quotes, names are like cancer-sensitive search for them. They're lowercase, right? They're internally lowercase, but you can use mixed case, and it will be the same as lowercase, right? 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 start using it. And then you're in trouble. So better to lowercase everything.

But sometimes we have migrated system from other database systems, and in this case, we do see table names like in camel style, for example. In this case, it's just legacy we need

to deal with. I personally like to lowercase everything because it's like economical reasons, like less sections when you type right that's it yeah and underscores between between words in object names of school yeah well maybe sometimes yeah yeah well if yeah so like in Python and shell snake style snake case yeah snake case cool so we're into the meat of the and we've got

we've 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 in general i wanted to mention it in the beginning but i forgot uh this episode is like kind of basic we like, 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. 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 that are 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 should we 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 a better advice is there anything in 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 you know it no beginner is going to be able to dive into the postgres code base and argue on the mailing list for making a default different, I don't think. Right. Yeah, many defaults are outdated. You know my opinion about this. But this is a bit different. We're often talking about GEC settings when we're talking about those defaults,

but this is more kind of design defaults. Yeah. Here as well. talking about those defaults, but this is more kind of design defaults. Yeah. Well, again, I don't see how nullable column can not null can become a global standard, the global default. But I can see how

in a particular project it can become code style or schema design style standard easily. Yeah, it's an interesting point, obviously obviously because we have to backwards like supporting backwards compatibility it's impossible now i think um but at the beginning you could also i guess there are maybe some standard things here but anyway like i don't know maybe there is standard regarding defaults. Maybe not. Let's proceed to some more bigger problems, maybe. What about... Let's finish about small problems.

Don't use money. Do use money. The data type. I know you find this funny every time. Well, Wiki says don't use money. They don't say don't use data type money. They say 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 bad data type. Why it's still there? Maybe a standard as well?

I don't think so. I don't know. So implementation is terrible. You will start losing kind of sense, right, if you use it. So rounding is an issue, partial. Like 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. Right. So fractions of a cent. If you have many transactions, you will we're paying for. Right. So fractions of a... If you have many transactions, you will start losing some money, so don't do it. Yeah. Use numeric, but check the performance, obviously. Yeah, I've also seen people use integers with, like...

Well, okay, but what about fractions of cents for example again 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 well okay if you define precision in advance and then follow this rule okay but why not why not numeric? Numeric will hold everything. I guess for performance reasons, I didn't ask. Right, right. Okay, good, simple, right? Don't use money.

What's next? Let's choose some bigger topic. What do you think? Well, I think the primary key, yeah, let's jump to your list, your how-to guide that I will link up as well. Let's do it. So, yeah, primary key is quite simple thing. Just don't use integer four primary keys.

Consider them in the same manner as integer two primary keys. Do it only when you fully understand consequences and reasons. I mean, like integer 4 has capacity, 2.1 billion rows and 2.1 billion values. Maximum is 2.1 billion, roughly. And sometimes we reach this. Yeah, positive, negative. Well, it's interesting that we can use negative values, but usually people for surrogate kids, 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. I think it's definitely possible, but the amount of work is not trivial.

Again, it's better to convert to integer 8, which is not trivial if you have billion rows already or 2 billion. 2 billion rows, I would say, probably is a case of emergency already. Yeah. But, yeah. But our checkup tool suggests, I think it starts raising big flags, yellow or even red, after 50% of capacity used. Yeah. By the way, it's an interesting problem how in one query to find all tables, checking pgclass and all other system tables which have primary key surrogate

primary key integer 4 or it can be maybe multi-colon primary key but one of the parts is integer 4 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 PLPGSQL and have a snippet. It's an anonymous do block or maybe function. Anyway. And then in this case, it can scan your schema and then check values in sequences and report the capacity for each integer 4 primary key case. And again, like my approach is just very simple. Just if you use integer 4 primary key, it's similar to integer 2 primary key because data volumes we deal with usually already so big.

So it makes sense if you do know what is column tetris and paddock alignment, right? If you know how the storage is organized. You always, yeah. You jumped to this before. 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 big in over

integer so even when you have considered uh column tetris still the cost is so small compared to the long-term advantages four bytes for each row yeah but this is why people usually choose literature for they say okay we will save four and we won't in this table we won't reach two billion and uh we will be saving four bytes for each row it's good for us and they choose it what you think i'm talking about conscious choice i don't yeah i think most choice is unconscious i think most people are just thinking it's default it's Yeah, maybe RRM, maybe like a tutorial they're following. Maybe like I want an integer primary key. Why not choose the type that's called integer?

So I think most of the choices of this is not conscious. 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 is because I saw it many times. Like 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 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 create a TED. Four bytes, eight bytes. So you even don't have any benefits from integer four. It's so emotionally, like, big. Yeah. emotionally like big understanding that you have four zero bytes for all rows.

So it's like, why did we do this? Why did you do this? Right? I just this this is an emotion. That's why I'm talking about it so much. I just I served many teams. I'm just sharing experience here. 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 save four 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. Right. Well, in the end of the day, four bytes for each row, it's not a huge saving. Four billion rows, it's just four billion bytes. Not a lot, right? Noticable probably, but not a lot.

Yeah, and even if you add up the fact that that's... It's probably duplicated in some indexes. You probably have several indexes that involve the primary key, like maybe some multicolumn indexes. Maybe you've worried about memory. 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 big in and instead of also you've used

integer for all of them and only one of those tables has to be converted to big in later that project that you go through to do that conversion you're going to realize we'd have been better off going with big in 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 interesting that wikileaks the wikileaks doesn't have this at all and it doesn't talk about primary keys at all. 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 big serial is also a bad thing in the opinion of people who wrote it. So, interesting, right?

I mean, it's quite common. We could add it. Next, choose anything. what do you think 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 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-partition table, in large table.

So it's a big task, actually, if you need to clean up. And maybe I told you, like, one day I went to 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 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 like when I was young, it was difficult to me to understand how to find proper batch size. If you take batch size one, probably too much transaction overhead.

And it will affect the throughput as well. Like you will be deleting less rows per minute or hour your background chops, right? Yeah. Well, I tend to see like low thousands. Is that how... I guess it depends. But no, what do you tend to do?

I have a simple approach. Remember our very first episode. I actually already described this, and

you see reminding things is also useful sometimes. So I have a very simple approach. It's based

on what our final goals. Our final goals with OTP systems is systems should be

working fast in general. For humans, fast means below 100 or 200 milliseconds, right? Nice, yeah. One second is quite slow. Ten seconds is very slow. Deletes can block some people, some other transactions. Plus, they can lead to IO effects and so on. So I prefer batches to last not more than one, two, three seconds.

In this case, we know negative effects won't last one or a few seconds. And even in the worst case, we will be blocking, for example, someone for just one second. It's quite slow, but not terribly slow. If it's already 10 seconds, it's quite too slow for people. And some of them, we might start losing traffic if we talk about web project or people start complaining and leaving us. If it's 100 milliseconds, it's good, but probably too small. So batches like half a second, one 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 deleting for example according to some if you're batching and deleting according to some order by for example timestamp deleting very old data first then new you need to control vacuum because probably your delete trying to find

the next batch will be scanning too many debt doubles I mean index will have a lot of links to, pointers to dead tuples, and performance of 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 additional thing, like sometimes I found myself, maybe we need an episode about delete actually, or massive operations if we hadn't have 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 degraded, like, and said, 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. Actually not similar. In this case,

you don't depend on the vacuum behavior and can go faster and so on. The other time I've seen this cause real issues is when people don't see how many deletes, for example. Let's say it's like a SAS account and they're deleting an account and then that account has users and that 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, it'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 on Cascade. Yeah, that's interesting. And the systems I built fully, which were built fully under my control, I always try 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, like I think it depends. You need to think about it in advance, of course, right? And maybe just rely on it. There might be 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, before, like, last delete, deleting main object, we clean up asynchronously before it, and only then we delete.

In batches. It depends. Right, right, right. It depends on particular system. But 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 one second is good, half a second is good, right? Yeah, I like that. I like that a lot. Yeah, but we actually, since we spoke about, like this is kind of episode about basics, of course, we forgot an elephant in the room right if you delete without any conditions without the work close yeah from a table it will be slow if table is big but also it will be interesting to see that sometimes table is not

like the space is not immediately available or like it won't be your disk space immediately you need because delete consists of two processes first is your delete synchronous and then vacuum which really deletes physically the data tuples that isn't where i thought you were going with that i used to work on a tool for a 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 um did you like 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 actually yeah yeah there is an extension to prohibit this and actually it was funny I think the very first thing we developed with Andre was this exactly

patch for Postgres, but it was rejected by hackers. Yeah, yeah. But we just wanted a warning to be produced or maybe the ability to forbid wireless deletes.

So, yeah. Let's maybe continue. But IDEs don't have to worry about the core Postgres. It can be implemented in a case-by-case basis there at least. This is a big mistake if you forgot where and execute it, and sometimes we don't need a semicolon in the end. For example, in P-SQL, 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. Oh, I have one more beginner-friendly one from the Duke 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? Yeah, but do you remember what should be used instead? Because I don't like 50 columns to be listed also. And then you understand that it's all of them. Well, but I think there's a few reasons for this. Firstly, do you really need 50 columns? What are you doing that requires 50 columns?

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 underlying table, I will definitely use star. Then later, if I need to view which should consist of all columns of 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, like it's 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, asterisk instead. So in application code, I think it can break in unexpected ways.

So if you then add some columns to that, can your code handle more columns coming back in the future than currently comes back? Depends. If I put the star, of course, I think about future changes of schema. I do think about it. Cool. Well, okay. Interesting.

You know, in Polgis, you can say select table one from table one. Or just table one. No, no, no, no, no. It's different. I mean, table one is just selecting everything, right? So you can do like... Select table name from table name. It will give you just one column, but of record type, like kind of 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 the first normal form is broken, actually, because you basically wrap everything to a single column.

Okay. Whole table. Which is great. And then you can... If you're inside, for example, PLP or SQL context, it's perfectly to do this. You just define a record variable and you can insert whole row

with all these columns to this row. Later, if there is evolution of schema and more columns, this code will work still. But depending on your language and driver, I don't know what will happen if you select table name from table name limit one, for example, with some where clause. I don't know.

I don't know. It's worth checking, but it's interesting. This is when you said if we have more columns than needed. 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 two. Yeah, yeah, yeah. This is a 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 have some constant 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 the 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 whole row for example right it's just like observations trying to yeah there is minimalism in star as well this is what I'm trying to say sure different kind of minimalism so star as well. This is what I'm trying to say. Sure. Different kind of minimalism. So there is a trade-off here. So definitely not an always don't do this, but I'd say don't do this without thinking. It sounds like. Right, right, right.

You need to consider the code and the future evolution. Like, yeah, and so on. Good. Nice. Do you want to end on any that you actually do think are universal? I don't know. It's hard, actually. It always depends, right?

Maybe about transactional DDL. Postgres is cool. It has transactional DDL until you cannot use it. And most really 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 that. You mentioned that it has two transactions. And it can, like, if it's interrupted, it's not fully rolled back, so it's not a transaction already, right? You will have invalid index left, leftovers, right 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 constraint, check constraint to define, not now, you will always need, and you have already Bigtable loaded. Yeah, that's the big.

You need to think about multiple steps, definitely, and be ready to roll back one of it and go retry. So transactional DDL is not easy. I mean, this topic is not easy. It's not like, oh, we have transactional DDL is not easy. I mean, this topic is not easy. It's not like, oh, we have transactional DDL, all problems solved. No. In 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. I'm trying to think if drop index concurrently is transactional. It needs to acquire an exclusive lock. It's similar to drop in a column, but Postgres doesn't offer any tools for zero downtime column drop. Drop column cannot be

zero downtime unless you cook it properly with low log 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.

For example, in terms of leftovers, no leftovers. But it might fail. That's the good thing. Just to acquire a lock gracefully, not to block SELECTs or other queries which came

after you started. 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. 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? Because of the heavy locks. Because normally it's going to be way more important. Yeah, yeah, yeah. Because of locking issues, basically. Exactly. Cool.

Right. Nice one. Well, I'll link all of these up in basically. Exactly. Cool. Nice one. I'll link all of these up in the show notes. Maybe the wiki one 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.

If we say don't do it, don't use transactional DDL. I mean, use it, but only partially like the pieces. You cannot use it in 100% of everything, like of 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. Yeah, I guess create index is the one that will catch

people out if they don't know about the lock. You cannot drop column without proper lock timeout and retries. And this is already beyond one single transaction. Well, and in the past, even adding columns

like... You cannot add column with nullable, without default. You cannot add it. It's the same as a drop 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. Nice. I think you find it hard to word that for the wiki, but I think it would be a good addition. Don't use

transactional DTL. Okay. You'd have a lot of caveats. Right. Well, it's very similar to many items we discussed today. True. True. This advice. Okay. Yeah. Thanks so much, Nikol much nicolai thanks everyone for listening

catch you next week bye