1 00:00:00,060 --> 00:00:04,400 Nikolay: Hello, hello, this is PostgresFM, a podcast about Postgres. 2 00:00:04,660 --> 00:00:06,000 Hi, Michael. 3 00:00:06,260 --> 00:00:08,040 Sorry for Mike last time. 4 00:00:08,040 --> 00:00:08,760 Michael: That's all right, Nik. 5 00:00:08,760 --> 00:00:10,260 I don't even notice normally. 6 00:00:10,600 --> 00:00:11,100 Nikolay: Right. 7 00:00:11,120 --> 00:00:11,840 Nik is fine. 8 00:00:11,840 --> 00:00:12,740 Nik is fine. 9 00:00:12,940 --> 00:00:15,520 So, episode number almost 90. 10 00:00:15,520 --> 00:00:16,740 I don't remember exactly. 11 00:00:16,880 --> 00:00:17,640 Like, Do you remember? 12 00:00:17,640 --> 00:00:18,620 You always remember. 13 00:00:19,140 --> 00:00:21,660 Michael: I remember, but you told me not to remind you anymore 14 00:00:21,660 --> 00:00:23,160 until we get to Milestone. 15 00:00:23,440 --> 00:00:26,080 And you're the only one that ever brings up the episode number. 16 00:00:26,840 --> 00:00:27,340 Nikolay: Really? 17 00:00:27,980 --> 00:00:29,340 Michael: I never mention it. 18 00:00:30,520 --> 00:00:31,020 Nikolay: Okay. 19 00:00:31,280 --> 00:00:31,780 Interesting. 20 00:00:32,900 --> 00:00:35,640 So we are going to talk about what? 21 00:00:36,220 --> 00:00:39,620 Michael: Oh yes, so this week was your suggestion, and it's a 22 00:00:39,620 --> 00:00:40,340 cool 1. 23 00:00:40,900 --> 00:00:44,180 It's going to be a new feature in Postgres 17, hopefully. 24 00:00:44,340 --> 00:00:46,860 It's committed, but you never know for sure. 25 00:00:46,920 --> 00:00:50,500 And it's transaction timeout, a new parameter that we'll have 26 00:00:50,500 --> 00:00:52,080 hopefully in the next version. 27 00:00:53,240 --> 00:00:55,520 Nikolay: I can show off, it was my idea. 28 00:00:55,760 --> 00:01:01,990 But most of the work, Andrey did, Andrey Borodin did. 29 00:01:01,990 --> 00:01:05,700 So it started during our PostgresTV hacking Postgres session. 30 00:01:06,880 --> 00:01:08,300 Michael: Which are back now, right? 31 00:01:08,400 --> 00:01:09,380 After a long pause. 32 00:01:09,380 --> 00:01:10,680 Nikolay: Well, yes, we'll try. 33 00:01:10,680 --> 00:01:11,540 Nobody knows. 34 00:01:11,960 --> 00:01:14,820 It's hard to arrange usually because to do proper hacking, you 35 00:01:14,820 --> 00:01:15,800 need a lot of time. 36 00:01:16,500 --> 00:01:20,580 Last time we did restart it, we talked about this transaction 37 00:01:20,640 --> 00:01:26,880 timeout and Andrei tried to write tests because former tests 38 00:01:27,700 --> 00:01:28,500 needed to be removed. 39 00:01:28,500 --> 00:01:33,560 By the way, I also wanted to say thanks to Alexander Korotkov 40 00:01:33,640 --> 00:01:35,040 who committed this patch. 41 00:01:35,740 --> 00:01:37,440 Honestly, I even didn't ask. 42 00:01:37,560 --> 00:01:40,140 So it was good to see that it's committed. 43 00:01:40,440 --> 00:01:46,560 And so session finished after 90 minutes by timeout because we 44 00:01:46,560 --> 00:01:47,380 needed to go. 45 00:01:47,720 --> 00:01:49,400 Session about timeout finished. 46 00:01:49,400 --> 00:01:50,880 Okay, now you're smiling. 47 00:01:51,500 --> 00:01:52,840 So, transaction timeout. 48 00:01:53,480 --> 00:01:58,440 It took a few years for me to understand something is wrong. 49 00:01:58,780 --> 00:01:59,940 We don't have something. 50 00:02:01,300 --> 00:02:06,220 Then, last year, like a year ago, I started thinking, it should 51 00:02:06,220 --> 00:02:06,720 be. 52 00:02:08,140 --> 00:02:12,160 My idea was maybe I'm very wrong or I'm very right. 53 00:02:12,160 --> 00:02:17,700 So it's so strange in 2023 to think about this basic setting. 54 00:02:18,580 --> 00:02:23,140 But honestly, the last few years, I already suggested to everyone 55 00:02:23,140 --> 00:02:26,080 to consider the transaction timeout to be applied on application level. 56 00:02:26,920 --> 00:02:32,520 In 100% of cases, I saw a very strange look, if it was a video 57 00:02:32,520 --> 00:02:33,020 meeting. 58 00:02:33,140 --> 00:02:36,340 It was a very strange look, like Postgres cannot do it? 59 00:02:36,340 --> 00:02:40,320 Yes, Postgres cannot do transaction timeout, but you do need 60 00:02:40,320 --> 00:02:42,020 it and we can discuss why. 61 00:02:42,720 --> 00:02:46,620 So I always say, do it always on the application side if you 62 00:02:46,620 --> 00:02:52,260 have LTP, because, you know, like anyway, HTTP has timeout. 63 00:02:52,360 --> 00:02:56,340 A server, for example, NGINX, like 30 seconds, it's normal, right? 64 00:02:56,880 --> 00:03:00,560 Nobody will wait many minutes, unless it's a specific request. 65 00:03:01,340 --> 00:03:05,880 Sometimes we can wait a few minutes, but usually we have a limit. 66 00:03:05,920 --> 00:03:07,840 And Postgres doesn't have this transaction timeout. 67 00:03:07,840 --> 00:03:11,340 It has only statement timeout and idle transaction session timeout. 68 00:03:11,980 --> 00:03:13,520 But no transaction timeout. 69 00:03:14,540 --> 00:03:18,580 Michael: And also, so I looked into when we got each of these, 70 00:03:18,580 --> 00:03:23,600 and statement timeout was ages ago, like 7.3, would you believe? 71 00:03:24,020 --> 00:03:28,040 And then idle in transaction session timeout was actually more 72 00:03:28,040 --> 00:03:29,060 recent than I realized--- 73 00:03:29,060 --> 00:03:32,860 It's 9.6, which is probably only about 7 or 8 years ago. 74 00:03:33,220 --> 00:03:38,440 And then we also got more recently idle session timeout. 75 00:03:39,100 --> 00:03:40,020 Nikolay: Which is different. 76 00:03:40,920 --> 00:03:42,740 It's outside of normal work. 77 00:03:42,880 --> 00:03:46,060 It's for like, let's drop the connection if it doesn't do anything. 78 00:03:46,680 --> 00:03:50,500 Michael: Yeah, but that was a recent, like, version 14 edition. 79 00:03:50,500 --> 00:03:52,440 So still only a couple of years ago. 80 00:03:52,440 --> 00:03:55,580 Nikolay: Ah, you want to say that it's also a very, very basic 81 00:03:55,580 --> 00:03:57,540 thing and only added recently? 82 00:03:57,540 --> 00:03:58,680 Yeah, I agree with that. 83 00:03:58,680 --> 00:04:03,400 Usually people implemented it outside Postgres as well, to drop 84 00:04:03,400 --> 00:04:03,900 connections. 85 00:04:05,020 --> 00:04:08,760 Michael: I've seen less need for that one than I have seen for 86 00:04:08,760 --> 00:04:12,080 this one, but like you, I didn't, it's one of those things that I 87 00:04:12,080 --> 00:04:15,440 kind of didn't realize I needed because statement timeout can 88 00:04:15,440 --> 00:04:19,640 cover a lot of the base, like a lot of the use cases can be just 89 00:04:19,640 --> 00:04:23,820 about coped with via statement timeout, but one or two really can't. 90 00:04:23,840 --> 00:04:28,260 And I think I saw your post and I'll link it up the mailing list 91 00:04:28,260 --> 00:04:32,740 thread that I think Andre started, but your first reply to that 92 00:04:32,740 --> 00:04:36,540 included some really good examples of when you do need this in 93 00:04:36,540 --> 00:04:40,220 addition to statement timeout or like instead of statement timeout. 94 00:04:40,460 --> 00:04:42,620 So is it worth like covering a couple of those? 95 00:04:42,620 --> 00:04:43,120 Yeah. 96 00:04:43,260 --> 00:04:47,860 Nikolay: Yeah, let's discuss why we use statement timeout in 97 00:04:47,860 --> 00:04:51,760 OLTP like in regular the most common cases like web and mobile 98 00:04:51,760 --> 00:04:52,260 apps. 99 00:04:52,380 --> 00:04:55,460 We had an episode about that and I think it was the very first 100 00:04:55,460 --> 00:04:56,400 episode maybe. 101 00:04:57,980 --> 00:04:59,640 Speaking of counting. 102 00:05:00,300 --> 00:05:02,840 Michael: Yeah, we've done a few other episodes related to this 103 00:05:02,840 --> 00:05:03,160 as well. 104 00:05:03,160 --> 00:05:05,840 We did one on connections, one on zero downtime migrations. 105 00:05:05,840 --> 00:05:08,040 Nikolay: No, I'm talking about the very first episode. 106 00:05:08,040 --> 00:05:08,940 What was it? 107 00:05:09,380 --> 00:05:10,460 Michael: Yeah, slow queries. 108 00:05:10,960 --> 00:05:12,160 Nikolay: Slow queries, yes. 109 00:05:12,160 --> 00:05:14,700 And this is about statement amount, basically. 110 00:05:15,300 --> 00:05:18,540 But people don't do SQL queries. 111 00:05:18,540 --> 00:05:24,060 I mean, end users, web and mobile app users, they don't do SQL 112 00:05:24,060 --> 00:05:24,560 queries. 113 00:05:26,000 --> 00:05:28,760 So they work at a higher level, HTTP requests. 114 00:05:29,760 --> 00:05:33,340 So why do we need statement timeout at all? 115 00:05:33,580 --> 00:05:34,080 Why? 116 00:05:34,740 --> 00:05:37,620 What is the problem we are solving? 117 00:05:39,720 --> 00:05:43,720 I have this honest question because in my opinion, when we think 118 00:05:44,340 --> 00:05:47,480 about what we try to solve with statement timeout, it's not the 119 00:05:47,480 --> 00:05:48,900 right tool usually. 120 00:05:49,700 --> 00:05:56,540 For example, again, users work at a high level and we know some 121 00:05:56,540 --> 00:06:00,580 intermediate software settings, middleware, which is usually 122 00:06:00,580 --> 00:06:03,580 written in some Python, Java, Ruby, anything. 123 00:06:04,060 --> 00:06:07,480 These application servers and also an HTTP server, like maybe front 124 00:06:07,480 --> 00:06:11,540 end server, like an NGINX or something, and VoIP, right? 125 00:06:11,680 --> 00:06:17,220 They usually have some limits to drop connections which last 126 00:06:17,220 --> 00:06:21,960 too long, if a server is taking too long to respond, or communication 127 00:06:22,200 --> 00:06:24,980 taking too long, usually like 30 or 60 seconds. 128 00:06:25,920 --> 00:06:30,360 This is what users will get if something which should take 100 129 00:06:30,360 --> 00:06:34,720 milliseconds or less, takes 30 seconds. 130 00:06:36,040 --> 00:06:39,360 It's time to admit there is a problem and tell the user that, 131 00:06:40,080 --> 00:06:45,520 like, for 502 gateway timeout, it's usually what NGINX was returning. 132 00:06:45,740 --> 00:06:48,940 And then we, back to database, we think, OK, we need to limit 133 00:06:48,940 --> 00:06:49,620 as well. 134 00:06:49,900 --> 00:06:51,760 But what exactly do we need to limit? 135 00:06:52,000 --> 00:06:53,500 This is the question. 136 00:06:54,140 --> 00:06:56,340 Is the statement timeout the right tool for that? 137 00:06:56,980 --> 00:07:00,140 Michael: I do think there's a like, a lot of cases where it's 138 00:07:00,140 --> 00:07:00,640 enough. 139 00:07:01,000 --> 00:07:05,760 So if, for example, we're trying to, in that case, let's say, 140 00:07:05,760 --> 00:07:09,480 for example, the users or like the app has already given up, 141 00:07:09,480 --> 00:07:11,720 but the database carries on working. 142 00:07:11,720 --> 00:07:15,060 Yeah, that's doing pointless kind of, that's pointless work. 143 00:07:15,060 --> 00:07:17,500 So avoiding pointless work would be great. 144 00:07:17,520 --> 00:07:20,500 But there are other cases too, where even if the app didn't have 145 00:07:20,500 --> 00:07:25,240 a timeout, is it okay for a few users of a system to be using 146 00:07:25,240 --> 00:07:30,040 all the resources when, you know, like hogging those resources 147 00:07:30,480 --> 00:07:33,740 by running some really, really slow or really, really heavy queries. 148 00:07:34,120 --> 00:07:37,080 Is that an acceptable trade-off or do you want to limit that? 149 00:07:37,080 --> 00:07:40,080 So I think there are other slightly different cases. 150 00:07:40,080 --> 00:07:40,880 Right, but 151 00:07:41,780 --> 00:07:42,880 Nikolay: Work is not... 152 00:07:43,280 --> 00:07:44,880 Statements are too small. 153 00:07:45,140 --> 00:07:47,200 It's too atomic a piece, right? 154 00:07:47,780 --> 00:07:49,120 Michael: Well, I think there's... 155 00:07:49,180 --> 00:07:53,100 Before we move on to why transaction timeout, there's also one 156 00:07:53,100 --> 00:07:57,940 other case which I think or maybe maybe this makes the point 157 00:07:57,940 --> 00:08:02,360 perfectly. These long-running transactions, even if they're a single 158 00:08:02,360 --> 00:08:07,740 statement transaction, they can block internal processes like 159 00:08:07,740 --> 00:08:11,720 autovacuum and cause issues that we've, I mean, we've spent multiple 160 00:08:11,720 --> 00:08:14,860 episodes talking about the kind of issues that long-running statements 161 00:08:14,860 --> 00:08:18,800 could cause, never mind long-running multi-statement transactions. 162 00:08:18,800 --> 00:08:20,940 Nikolay: No, no, no, no, not statements block it. 163 00:08:21,140 --> 00:08:21,640 Transactions. 164 00:08:22,660 --> 00:08:25,680 Michael: But as you've said multiple times, you can't have a 165 00:08:25,680 --> 00:08:27,020 statement without a transaction. 166 00:08:27,120 --> 00:08:31,360 So like a long-running statement is enough, but you're right 167 00:08:31,360 --> 00:08:31,860 that... 168 00:08:31,980 --> 00:08:35,020 Nikolay: You can have a statement without transactions for example 169 00:08:35,020 --> 00:08:39,960 creating an index concurrently. Three transactions I mean, you're okay 170 00:08:39,960 --> 00:08:41,120 without a transaction completely. 171 00:08:41,120 --> 00:08:42,180 No, no dirty reads. 172 00:08:42,180 --> 00:08:43,760 Michael: Exactly right, 173 00:08:43,820 --> 00:08:44,780 Nikolay: Right, right. 174 00:08:45,020 --> 00:08:51,860 So yeah, but like we want to apply the right tool. 175 00:08:51,860 --> 00:08:57,680 It means that it should suit for all edge and corner cases. 176 00:08:58,840 --> 00:09:03,760 And I'm saying statement timeout is for a different hole to close. 177 00:09:05,140 --> 00:09:08,160 Michael: You gave a really great example that completely sold 178 00:09:08,160 --> 00:09:10,080 me in that email thread I mentioned. 179 00:09:10,080 --> 00:09:10,580 Nikolay: Email? 180 00:09:10,940 --> 00:09:11,440 Michael: Yes. 181 00:09:11,820 --> 00:09:15,280 And that was a case I've seen actually quite recently, which 182 00:09:15,280 --> 00:09:15,780 was... 183 00:09:16,020 --> 00:09:16,520 ...taratatata. 184 00:09:16,960 --> 00:09:18,060 Nikolay: Yeah, I call it... 185 00:09:18,480 --> 00:09:20,520 It's like from a machine gun. 186 00:09:20,900 --> 00:09:22,920 Yeah, but - statements coming from a machine gun. 187 00:09:22,920 --> 00:09:24,660 Brief statements with brief pause. 188 00:09:25,980 --> 00:09:28,780 Michael: And somebody setting a transaction at the beginning, 189 00:09:29,440 --> 00:09:33,640 so in the case I saw, it was deliberately taking out, like doing 190 00:09:33,640 --> 00:09:38,640 begin, then doing multiple updates or like upsets in this case, 191 00:09:38,900 --> 00:09:41,180 and then only at the end committing it. 192 00:09:41,380 --> 00:09:45,220 So any one of those statements was really short, but because they 193 00:09:45,220 --> 00:09:47,560 were doing tens of thousands or even hundreds of thousands, I 194 00:09:47,560 --> 00:09:51,900 think, in this case, the transaction as a whole was longer than 195 00:09:51,900 --> 00:09:54,640 you'd probably want on a OLTP system. 196 00:09:54,820 --> 00:09:57,800 So in that case, statement timeout wouldn't have helped, or at 197 00:09:57,800 --> 00:10:01,600 least any sane statement timeout wouldn't have cancelled that. 198 00:10:01,780 --> 00:10:03,240 But a transaction timeout... 199 00:10:03,800 --> 00:10:07,620 Nikolay: An idle transaction session timeout wouldn't cancel 200 00:10:07,740 --> 00:10:10,580 this transaction because the breaks are also short. 201 00:10:11,280 --> 00:10:13,720 Michael: Yeah, it's not idle, exactly. 202 00:10:14,340 --> 00:10:15,780 Nikolay: It's like from a machine gun. 203 00:10:15,780 --> 00:10:19,160 It can be select plus 1, by the way, if it's wrapped in a transaction 204 00:10:19,200 --> 00:10:19,540 block. 205 00:10:19,540 --> 00:10:23,320 Select plus 1, this anti-pattern, which... 206 00:10:24,860 --> 00:10:26,100 Michael: Like n plus 1. 207 00:10:27,340 --> 00:10:28,000 Nikolay: 0, sorry. 208 00:10:28,080 --> 00:10:28,940 Yes, yes. 209 00:10:28,940 --> 00:10:29,640 Yeah, yeah. 210 00:10:30,060 --> 00:10:35,640 I mean, it's actually a terrible name, but the idea is that it's 211 00:10:36,040 --> 00:10:38,600 n selects in a loop. 212 00:10:39,060 --> 00:10:41,760 So, we didn't notice that they have a loop. 213 00:10:42,500 --> 00:10:47,800 It can be short updates with the idea that we want to make them 214 00:10:47,800 --> 00:10:50,140 short because it's better, you know, like to... 215 00:10:50,240 --> 00:10:55,120 But if it's wrapped into a transaction, you cannot release any 216 00:10:55,120 --> 00:10:56,880 locks until the very end of the transaction. 217 00:10:56,880 --> 00:10:57,780 This is the rule. 218 00:10:58,320 --> 00:11:02,060 All locks are released only if it's a commit or rollback. 219 00:11:02,780 --> 00:11:03,480 That's it. 220 00:11:05,280 --> 00:11:11,980 And so it's a bad idea to split into batches, updates or deletes. 221 00:11:12,880 --> 00:11:16,040 Insert is almost never, I think, an issue. 222 00:11:16,120 --> 00:11:18,260 It makes sense to split. 223 00:11:18,260 --> 00:11:21,760 An insert should be a single massive insert, usually. 224 00:11:22,360 --> 00:11:23,620 Like copy or insert. 225 00:11:24,180 --> 00:11:27,920 But if it's updates and deletes, people know there's a rule to 226 00:11:27,920 --> 00:11:32,540 split, then they put it in a single transaction and what's happening 227 00:11:32,540 --> 00:11:33,040 here. 228 00:11:33,700 --> 00:11:35,040 What do we do? 229 00:11:35,280 --> 00:11:38,580 We're accumulating a lot of logs and not releasing them. 230 00:11:40,960 --> 00:11:44,200 So there are only 2 reasons, the big reasons I see. 231 00:11:44,340 --> 00:11:47,180 Well, the source of the transition is 1 reason, but there are 2 problems 232 00:11:47,260 --> 00:11:48,580 which are very bright. 233 00:11:50,860 --> 00:11:53,040 They scream let's have transaction timeout. 234 00:11:53,100 --> 00:11:56,320 They did it for many years and I always say, okay, this is a 235 00:11:56,320 --> 00:11:58,520 problem we must solve on the application side. 236 00:11:58,520 --> 00:12:01,320 We cannot do it in PostgreSQL at all. 237 00:12:01,720 --> 00:12:02,220 Surprise. 238 00:12:02,500 --> 00:12:06,760 Well, we can use pg-cron and put maybe some, I call it terminator 239 00:12:07,080 --> 00:12:11,280 snippet, working with pg_stat_activity, checking the exact start, like 240 00:12:11,280 --> 00:12:14,680 transaction start timestamp, and then terminating. 241 00:12:15,480 --> 00:12:16,760 So terminator script. 242 00:12:16,780 --> 00:12:18,360 This name is derived from pg_terminate_backend. 243 00:12:20,380 --> 00:12:23,940 So was it renamed or no? 244 00:12:23,940 --> 00:12:24,820 I don't remember. 245 00:12:24,960 --> 00:12:26,920 pg_start_backup was renamed. 246 00:12:27,660 --> 00:12:29,840 It's about naming and I support it. 247 00:12:30,040 --> 00:12:33,980 Well, I'm having 3 in my speech, sorry. 248 00:12:35,200 --> 00:12:38,300 So, pg_start_backup was renamed to pg_basebackup start. 249 00:12:38,300 --> 00:12:41,340 So the same should happen with pg_terminate_backend, I think. 250 00:12:42,740 --> 00:12:44,780 Now, pg_terminate_backend should be pg_backend_terminate. 251 00:12:45,020 --> 00:12:47,860 So, this terminate script is also like a workaround. 252 00:12:47,900 --> 00:12:50,820 It should be some simple solution. 253 00:12:51,180 --> 00:12:55,400 Let's not allow too long-running transactions. 254 00:12:56,280 --> 00:12:59,200 Because we keep locks, this is problem number 1 we just discussed. 255 00:12:59,200 --> 00:13:04,540 And second problem you mentioned, keeping xmin horizon frozen 256 00:13:04,620 --> 00:13:05,980 basically, not shifting. 257 00:13:06,380 --> 00:13:12,740 Which means that all, like xmin horizon is the hidden xmin value 258 00:13:12,740 --> 00:13:18,220 of a tuple which is the oldest in the system and we usually cannot... 259 00:13:19,940 --> 00:13:23,180 Autovacuum usually comes and deletes it at that time, we delete 260 00:13:23,180 --> 00:13:26,980 it but we cannot delete it if there is some transaction which 261 00:13:26,980 --> 00:13:33,480 still can read from this because xmin is like current time not 262 00:13:33,480 --> 00:13:38,240 past, not the past for this transaction which means that if we 263 00:13:38,240 --> 00:13:43,380 keep our transaction we block this xmin frozen and that means 264 00:13:43,380 --> 00:13:47,740 that Autovacuum cannot delete dead tuples, more and more of them, 265 00:13:47,780 --> 00:13:49,940 freshly dead tuples, I call them. 266 00:13:50,500 --> 00:13:52,940 Or maybe it can be improved this name. 267 00:13:53,320 --> 00:13:57,780 So these 2 problems, locks and xmin horizon frozen. 268 00:13:58,360 --> 00:14:05,860 Locks are not released and they both can hit and a lot of people 269 00:14:05,860 --> 00:14:09,880 can notice and you can even be down down server can be down in 270 00:14:09,880 --> 00:14:16,160 some cases and you cannot limit it inclusion in Postgres 16 I 271 00:14:16,160 --> 00:14:18,940 was so happy to realize I'm not mad. 272 00:14:21,820 --> 00:14:26,260 It's obvious and I think it was discussed maybe, so shouldn't 273 00:14:26,260 --> 00:14:30,380 discuss many times, but we just went ahead, many things to Andrej, 274 00:14:30,380 --> 00:14:32,180 just went ahead and coded it. 275 00:14:32,640 --> 00:14:36,560 Then many strange cases started to appear. 276 00:14:36,600 --> 00:14:41,660 For example, sub-transactions or conflict with statement_timeout, 277 00:14:42,100 --> 00:14:44,620 which should fire first, for example. 278 00:14:46,320 --> 00:14:47,560 Michael: That's a good point. 279 00:14:47,620 --> 00:14:51,880 Do you want to talk a tour about in this future, like once Postgres 280 00:14:51,900 --> 00:14:57,700 17 is out, if you're designing a new OLTP system, are you even 281 00:14:57,700 --> 00:15:00,920 using, like, do you normally use global statement timeouts? 282 00:15:00,920 --> 00:15:04,480 And if you did in the past would you now replace that or would 283 00:15:04,480 --> 00:15:07,040 you use both with different settings how would you 284 00:15:07,040 --> 00:15:07,700 Nikolay: do it? 285 00:15:07,900 --> 00:15:09,220 Good question, good question. 286 00:15:09,520 --> 00:15:12,980 So first of all in documentation we can read that setting statement 287 00:15:12,980 --> 00:15:17,300 timeout globally is not a good idea and I think it's a very bad 288 00:15:17,300 --> 00:15:22,280 idea to write this in documentation because we discussed, even 289 00:15:22,280 --> 00:15:25,120 in HTTP server, application server, they have their own timeouts 290 00:15:25,440 --> 00:15:29,020 database is usually the most like... 291 00:15:29,480 --> 00:15:33,080 Data intensive work is usually in database unless we talk about 292 00:15:33,080 --> 00:15:36,900 some other stuff happening now outside of web and mobile apps, 293 00:15:36,900 --> 00:15:37,400 right? 294 00:15:37,540 --> 00:15:40,960 I mean AI stuff like in GPU and so on. 295 00:15:40,960 --> 00:15:46,960 So we want to limit in all OLTP systems, we definitely want to limit 296 00:15:46,960 --> 00:15:49,800 our work to 30 seconds maybe. 297 00:15:50,500 --> 00:15:51,560 It's a good limit. 298 00:15:52,420 --> 00:15:56,980 This article and our very first episode is about basics, which 299 00:15:57,980 --> 00:16:02,080 are so like 30 years, like 40 years since the beginning of the 300 00:16:02,080 --> 00:16:02,580 Internet. 301 00:16:03,100 --> 00:16:07,580 People don't like to wait more than a second, in a normal case. 302 00:16:08,220 --> 00:16:12,680 It means that we don't want to limit globally and be protected. 303 00:16:12,780 --> 00:16:18,400 Only specific users which need it, always can change it, unless 304 00:16:18,420 --> 00:16:19,240 it's restricted. 305 00:16:19,740 --> 00:16:23,680 So you just set statement timeout or anything and go. 306 00:16:23,680 --> 00:16:27,180 Of course, if DDL is needed to be executed, for example, create 307 00:16:27,180 --> 00:16:30,840 index concurrently, of course, you also should remove this limit. 308 00:16:30,840 --> 00:16:35,580 But global setting is protection, it should be there for OLTP 309 00:16:35,600 --> 00:16:35,900 case. 310 00:16:35,900 --> 00:16:39,140 And Postgres is like, OLTP is the main case. 311 00:16:39,140 --> 00:16:43,180 We don't usually think about analytical queries. 312 00:16:44,160 --> 00:16:48,280 So it means that I always recommend setting this and documentation 313 00:16:48,740 --> 00:16:52,220 is having bad advice in this case. 314 00:16:53,540 --> 00:16:55,060 So 30 seconds, my recommendation. 315 00:16:55,440 --> 00:16:57,740 Sometimes 15, it's better. 316 00:16:58,140 --> 00:16:58,880 Michael: Oh, wow. 317 00:16:59,100 --> 00:17:01,360 Nikolay: Yeah, well, even 10 maybe. 318 00:17:01,520 --> 00:17:02,140 It depends. 319 00:17:03,940 --> 00:17:07,800 It raises the bar requirement for queries that this is about 320 00:17:07,800 --> 00:17:08,740 quality actually. 321 00:17:08,740 --> 00:17:13,460 So if your query is poorly designed or you forgot to create a proper 322 00:17:13,460 --> 00:17:17,420 index something like a bad plan, be killed. 323 00:17:17,700 --> 00:17:20,880 But now with transaction timeout, I would say statement timeout 324 00:17:20,880 --> 00:17:22,320 might be not needed at all. 325 00:17:23,260 --> 00:17:23,760 Michael: Interesting. 326 00:17:23,760 --> 00:17:25,680 I wondered if you were going to say that. 327 00:17:26,140 --> 00:17:29,400 Nikolay: So again, my logic is applied at a higher level. 328 00:17:30,940 --> 00:17:32,320 This is about each feature. 329 00:17:32,320 --> 00:17:35,860 If we go down, in both cases, database specific, Postgres specific 330 00:17:35,860 --> 00:17:38,940 cases I mentioned, they also apply to a transaction level at 331 00:17:38,940 --> 00:17:39,660 higher levels. 332 00:17:39,960 --> 00:17:43,580 We don't care about statement and breaks between statements. 333 00:17:44,340 --> 00:17:46,760 Michael: And do you think like same order of magnitude? 334 00:17:46,880 --> 00:17:50,140 Like you mentioned 30, I've seen 60 seconds quite often mentioned 335 00:17:50,140 --> 00:17:53,080 as a sensible default, so 30 seconds not too different. 336 00:17:53,540 --> 00:17:57,440 Would you go similar, like for any reason to double it or like 337 00:17:57,440 --> 00:18:01,460 increase it, like add a little bit of buffer there or just similar 338 00:18:01,460 --> 00:18:01,620 number? 339 00:18:01,620 --> 00:18:03,040 Nikolay: No, no, no, Why buffer? 340 00:18:03,180 --> 00:18:07,340 Set it to 30, solve all problems and then consider going even 341 00:18:07,340 --> 00:18:07,840 down. 342 00:18:08,040 --> 00:18:10,440 And if we talk about statement timeout, why at all? 343 00:18:10,440 --> 00:18:13,940 Like we need it, like, okay, we put it, but what if we have transaction 344 00:18:13,940 --> 00:18:17,280 timeout 30 seconds, for example, which statement timeout would 345 00:18:17,280 --> 00:18:17,700 I use? 346 00:18:17,700 --> 00:18:19,260 30 seconds maybe as well. 347 00:18:19,360 --> 00:18:21,040 But it's already solved. 348 00:18:21,340 --> 00:18:23,260 Michael: I don't think there's any, that wouldn't make sense 349 00:18:23,260 --> 00:18:23,600 to me. 350 00:18:23,600 --> 00:18:26,380 But I was wondering if you might say you know statement timeout 351 00:18:26,380 --> 00:18:29,700 30 seconds transaction timeout, 60 seconds and 352 00:18:29,960 --> 00:18:32,860 Nikolay: Transaction? I don't... transaction session timeout, 60 seconds. 353 00:18:32,860 --> 00:18:36,240 But when transaction timeout? What? 354 00:18:36,260 --> 00:18:37,700 Session timeout, you mean? 355 00:18:38,300 --> 00:18:41,840 Michael: No, no, no, transaction. So, transactions can consist 356 00:18:41,840 --> 00:18:43,420 of multiple statements, right? 357 00:18:43,620 --> 00:18:50,240 Right. So, there's no need to have a statement timeout that is 358 00:18:50,600 --> 00:18:52,660 bigger or equal to the transaction timeout. 359 00:18:52,660 --> 00:18:53,740 Nikolay: It doesn't make sense. 360 00:18:54,120 --> 00:18:57,340 Michael: So the only thing that could make sense would be a statement 361 00:18:57,340 --> 00:19:00,040 timeout that's less than the transaction timeout. 362 00:19:00,820 --> 00:19:01,320 Nikolay: Right. 363 00:19:01,320 --> 00:19:02,520 Michael: But it seems pretty neat. 364 00:19:02,520 --> 00:19:03,020 Yeah. 365 00:19:03,820 --> 00:19:07,260 Nikolay: But again, like if you take transaction timeout, which 366 00:19:07,260 --> 00:19:12,340 is already quite good, restrictive, 30 or 60 seconds, then I 367 00:19:12,340 --> 00:19:13,400 think statement amount... 368 00:19:13,620 --> 00:19:16,060 And I don't see logical to go down. 369 00:19:17,300 --> 00:19:19,080 Okay, it's about resources. 370 00:19:19,120 --> 00:19:24,020 If you go down, maybe you save some resources by killing some statement 371 00:19:24,060 --> 00:19:25,940 earlier than the transaction. 372 00:19:27,740 --> 00:19:30,540 But it's not the main reason to have transaction command for 373 00:19:30,540 --> 00:19:31,040 me. 374 00:19:31,340 --> 00:19:37,100 Well, of course. Maybe it's actually super important. 375 00:19:38,100 --> 00:19:41,140 Maybe I just forgot that we use it. 376 00:19:41,260 --> 00:19:42,580 Okay, I don't know. 377 00:19:43,660 --> 00:19:48,920 I saw so many incidents where even statement timeout was not achieved, 378 00:19:49,020 --> 00:19:51,160 but resources were spent fully. 379 00:19:51,620 --> 00:19:52,640 So I don't know. 380 00:19:52,640 --> 00:19:55,640 I mean, we have very restrictive statement timeout, very, very 381 00:19:55,640 --> 00:19:56,140 low. 382 00:19:56,720 --> 00:20:01,160 And still CPU 100% and everything is down because a lot of work 383 00:20:01,160 --> 00:20:01,860 is happening. 384 00:20:01,920 --> 00:20:06,460 So statement timeout doesn't protect you, even 15 seconds. 385 00:20:06,460 --> 00:20:09,860 I mean, to start protecting, maybe I would like to have it 1 386 00:20:09,860 --> 00:20:10,360 second. 387 00:20:10,580 --> 00:20:13,220 In this case, I think it will start protecting. 388 00:20:15,520 --> 00:20:16,020 Michael: Interesting. 389 00:20:16,560 --> 00:20:18,360 Nikolay: But 30 seconds is too high. 390 00:20:19,860 --> 00:20:22,040 Michael: Yeah, it's a tricky one, right? 391 00:20:22,040 --> 00:20:26,380 Because yeah, I think there's a growing, and I know you mentioned 392 00:20:26,380 --> 00:20:30,800 analytical isn't our primary case, but there's a growing trend 393 00:20:30,800 --> 00:20:32,700 that I'm seeing of kind of hybrid. 394 00:20:33,040 --> 00:20:37,360 I've had it called HTAP databases where people don't want to 395 00:20:37,360 --> 00:20:41,980 spin up a second analytical, like a warehouse or something, and they're 396 00:20:41,980 --> 00:20:47,060 trying to run some small analytical queries on their like OLTP 397 00:20:47,260 --> 00:20:51,380 database, which means you do get these kind of the odd several 398 00:20:51,380 --> 00:20:55,340 second query or at least hundreds of milliseconds running 399 00:20:55,340 --> 00:20:56,520 on the same database. 400 00:20:56,600 --> 00:20:59,760 So I can see how it could get tricky for some folks. 401 00:20:59,760 --> 00:21:03,380 But I think that the 30 seconds and the higher feels like a sane 402 00:21:03,740 --> 00:21:05,280 starting point, as you mentioned. 403 00:21:05,280 --> 00:21:08,600 And if you start to see some errors from those, look into them. 404 00:21:08,600 --> 00:21:12,040 And if you don't, look into what your slowest queries are and 405 00:21:12,040 --> 00:21:13,380 whether you can reduce it. 406 00:21:13,520 --> 00:21:16,900 Nikolay: In case of HTAP, I will just have another database user 407 00:21:17,220 --> 00:21:21,480 specifically for long-running queries. I will adjust this setting 408 00:21:21,820 --> 00:21:25,280 maybe setting it to a couple or maybe 5 minutes and allowing to 409 00:21:25,280 --> 00:21:26,640 last longer, that's it. 410 00:21:27,040 --> 00:21:29,340 Michael: Yeah, that's a good point that I don't know if we mentioned 411 00:21:29,340 --> 00:21:33,240 yet that these can be set at the role level, at a session level, 412 00:21:33,240 --> 00:21:34,020 and globally. 413 00:21:34,080 --> 00:21:36,760 Nikolay: Well, I never tried, but statement timeout definitely 414 00:21:36,760 --> 00:21:38,160 can be set at user level. 415 00:21:38,160 --> 00:21:41,100 I think lock_timeout should work as well. 416 00:21:41,280 --> 00:21:42,680 Worth checking by the way. 417 00:21:43,040 --> 00:21:45,660 Michael: I thought the doc said that, but I was checking the... 418 00:21:45,660 --> 00:21:47,620 Because this is the development doc. 419 00:21:47,860 --> 00:21:48,920 Nikolay: Maybe even it's... 420 00:21:49,200 --> 00:21:49,740 It should be 421 00:21:49,740 --> 00:21:50,260 Michael: in the 422 00:21:50,260 --> 00:21:51,680 Nikolay: testing tests maybe. 423 00:21:51,680 --> 00:21:52,440 Maybe it's... 424 00:21:52,540 --> 00:21:54,740 So I'm quite sure it should work, yeah. 425 00:21:55,080 --> 00:21:55,820 So you... 426 00:21:56,040 --> 00:21:58,740 Anyway, we talk about statement timeout right now. 427 00:21:58,740 --> 00:22:01,020 We don't have lock_timeout until 17. 428 00:22:01,020 --> 00:22:04,200 So statement timeout can be set for different users differently. 429 00:22:04,860 --> 00:22:11,200 And I also had a case when people had very low global statement 430 00:22:11,200 --> 00:22:17,000 timeout, but then adjusted for people, for human connections, 431 00:22:17,160 --> 00:22:19,440 they adjusted it to set to 0. 432 00:22:20,020 --> 00:22:21,360 Michael: How do you feel about that? 433 00:22:21,600 --> 00:22:22,900 Nikolay: I feel this is dangerous. 434 00:22:23,740 --> 00:22:27,900 It's like someone can just run something then drink some coffee 435 00:22:28,040 --> 00:22:30,360 and then go for some call. 436 00:22:31,160 --> 00:22:32,460 It's kind of dangerous. 437 00:22:34,620 --> 00:22:34,740 By the 438 00:22:34,740 --> 00:22:36,520 Michael: way, I've got bad news for you. 439 00:22:37,660 --> 00:22:41,660 The warning seems to have been copied over to transaction timeout 440 00:22:41,660 --> 00:22:42,840 documentation as well. 441 00:22:42,840 --> 00:22:46,120 And it says in the docs, in the new docs, setting transaction 442 00:22:46,120 --> 00:22:48,940 timeout in postgresql.conf is not recommended because it would 443 00:22:48,940 --> 00:22:50,140 affect all sessions. 444 00:22:50,820 --> 00:22:51,960 Nikolay: Yeah, actually it's a good point. 445 00:22:51,960 --> 00:22:53,260 I need to raise this again. 446 00:22:53,260 --> 00:22:54,620 I don't agree with this. 447 00:22:54,680 --> 00:22:56,860 It's a bad, very bad statement. 448 00:22:57,280 --> 00:23:00,180 Michael: There's also 1 other note in here that I had forgotten. 449 00:23:00,180 --> 00:23:03,040 I guess it's only a minor note, but prepared transactions are 450 00:23:03,040 --> 00:23:04,580 not subject to this timeout. 451 00:23:04,760 --> 00:23:05,780 Thought worth mentioning. 452 00:23:06,680 --> 00:23:08,040 Nikolay: Yeah, it's so. 453 00:23:09,380 --> 00:23:10,440 Michael: So, yeah. 454 00:23:10,920 --> 00:23:12,040 Is there anything else? 455 00:23:12,700 --> 00:23:15,560 I guess the story for how this came about is pretty interesting. 456 00:23:15,640 --> 00:23:18,840 The thread seems quite long, to me at least. 457 00:23:18,840 --> 00:23:22,700 Nikolay: As usual, a lot of unpredictable problems and then people 458 00:23:23,040 --> 00:23:25,260 seem to agree and then I think okay, not 4.17. 459 00:23:25,520 --> 00:23:27,520 But it was a good surprise last week, right? 460 00:23:27,520 --> 00:23:28,880 Or a couple of weeks ago. 461 00:23:30,300 --> 00:23:36,400 Yeah, I just wanted to say, if our world survives, right? 462 00:23:37,400 --> 00:23:43,680 For folks who are listening to this in 2025 or later, just start 463 00:23:43,680 --> 00:23:47,960 thinking about limiting at transaction level first and then go 464 00:23:47,960 --> 00:23:48,460 down. 465 00:23:49,020 --> 00:23:52,980 This is the main advice, but for others, limit the application 466 00:23:53,240 --> 00:23:56,280 side and limit statement amount and idle transaction session 467 00:23:56,280 --> 00:24:00,040 timeout, of course, both are good to have limited to below a minute 468 00:24:00,040 --> 00:24:01,800 maybe, at least 1 minute. 469 00:24:03,180 --> 00:24:08,680 I also saw people are afraid of an idle transaction session 470 00:24:08,680 --> 00:24:10,240 timeout being low. 471 00:24:10,240 --> 00:24:11,680 I don't understand this. 472 00:24:12,340 --> 00:24:13,940 Put it very low, very low. 473 00:24:13,940 --> 00:24:16,860 Like if someone is not doing anything, bye bye. 474 00:24:17,000 --> 00:24:21,900 OK, maybe for humans, you can limit it to 1 or 2 minutes but 475 00:24:21,900 --> 00:24:26,400 still you're probably holding some very important exclusive locks 476 00:24:26,400 --> 00:24:31,560 and blocking someone, it's not a good idea so either commit or 477 00:24:31,560 --> 00:24:33,460 do something already, right or 478 00:24:33,460 --> 00:24:37,560 Michael: yeah the default is no limit like there's absolutely 479 00:24:37,640 --> 00:24:38,720 no limit at all. 480 00:24:39,140 --> 00:24:39,740 So, any... 481 00:24:40,760 --> 00:24:42,880 Yeah, even if you're really... 482 00:24:43,180 --> 00:24:46,880 Even if you are concerned, what would an hour limit look like? 483 00:24:46,880 --> 00:24:48,480 Several hour limit look like? 484 00:24:49,080 --> 00:24:52,320 Even that's going to be better than none at all. 485 00:24:52,800 --> 00:24:56,640 So yeah, I like that advice for people to be checking. 486 00:24:56,640 --> 00:24:58,280 Do you even have any of these limits set? 487 00:24:58,280 --> 00:24:59,940 Because the default is not to. 488 00:25:00,060 --> 00:25:02,860 And even on a lot of cloud providers, they don't set 489 00:25:02,900 --> 00:25:03,360 Nikolay: defaults for you. 490 00:25:03,360 --> 00:25:08,000 IGOR MINAROVICH-SHMAROVICH Yeah, Nginx, if you work on web or 491 00:25:08,000 --> 00:25:13,640 mobile apps, Nginx, for example, has a default proxy timeout limit 492 00:25:14,060 --> 00:25:15,340 of 60 seconds. 493 00:25:15,940 --> 00:25:18,540 This is a good starting point to think about it. 494 00:25:19,540 --> 00:25:25,420 If 99% of your database work is to serve HTTP requests, at least 495 00:25:25,600 --> 00:25:28,320 60 seconds should be there. 496 00:25:28,860 --> 00:25:30,520 And go down to details. 497 00:25:30,860 --> 00:25:32,520 Also, one more thing. 498 00:25:32,700 --> 00:25:34,700 Maybe some folks will be inspired. 499 00:25:35,320 --> 00:25:41,920 I'm not a good drawer of some pictures, but imagine if we had 500 00:25:41,920 --> 00:25:47,720 a schema in documentation showing the relationships between statement 501 00:25:48,660 --> 00:25:52,920 and breaks between statement and some big transaction and timeouts 502 00:25:52,920 --> 00:25:54,120 will be visualized. 503 00:25:55,080 --> 00:25:58,740 So for better understanding, this is transaction timeout, between 504 00:25:58,740 --> 00:26:01,480 them there are multiple statements, they are also limited and 505 00:26:01,480 --> 00:26:02,420 breaks are limited. 506 00:26:02,640 --> 00:26:05,140 And also outside we have a session timeout. 507 00:26:05,500 --> 00:26:08,040 So idle session, how is it called? 508 00:26:08,040 --> 00:26:08,940 Michael: Idle session timeout. 509 00:26:08,940 --> 00:26:09,840 We have both. 510 00:26:11,260 --> 00:26:13,040 Nikolay: No, session timeout, what is it? 511 00:26:13,040 --> 00:26:13,320 I mean- 512 00:26:13,320 --> 00:26:15,520 Michael: We have idle in transaction session timeout and we have 513 00:26:15,520 --> 00:26:16,620 idle session timeout. 514 00:26:16,720 --> 00:26:17,560 Nikolay: Idle session timeout. 515 00:26:17,560 --> 00:26:18,120 This is what... 516 00:26:18,120 --> 00:26:20,420 Like, it's outside of a transaction also have limited. 517 00:26:21,600 --> 00:26:25,880 So all periods of workflow are covered now. 518 00:26:25,920 --> 00:26:28,220 And to visualize this would be a great idea. 519 00:26:28,820 --> 00:26:31,500 Documentation already has a couple of pictures. 520 00:26:31,500 --> 00:26:32,660 It would be good picture to have. 521 00:26:32,660 --> 00:26:32,780 I 522 00:26:32,780 --> 00:26:35,160 Michael: think you'd be in the first 10 still though if you got 523 00:26:35,160 --> 00:26:36,040 this one in. 524 00:26:36,280 --> 00:26:39,720 Nikolay: Right exactly, so maybe someone will be inspired. 525 00:26:40,680 --> 00:26:41,120 Good. 526 00:26:41,120 --> 00:26:41,620 Michael: Nice. 527 00:26:42,040 --> 00:26:42,540 Yeah. 528 00:26:43,700 --> 00:26:44,780 Thanks so much Nikolay. 529 00:26:44,960 --> 00:26:45,500 Nikolay: Catch you next week. 530 00:26:45,500 --> 00:26:45,540 Bye. 531 00:26:45,540 --> 00:26:45,620 Catch you 532 00:26:45,620 --> 00:26:46,160 Michael: next week. 533 00:26:46,400 --> 00:26:46,650 Nikolay: Bye.