1 0:0:0,06 --> 0:0:2,6599998 Michael: Hello and welcome to PostgresFM, a weekly show about 2 0:0:2,6599998 --> 0:0:3,54 all things PostgreSQL. 3 0:0:3,74 --> 0:0:6,58 I am Michael, founder of pgMustard, and I'm joined as usual 4 0:0:6,58 --> 0:0:8,36 by Nik, founder of PostgresAI. 5 0:0:8,36 --> 0:0:9,62 Hey Nik, how's it going? 6 0:0:9,92 --> 0:0:11,86 Nikolay: Hi Michael, everything's alright. 7 0:0:12,179999 --> 0:0:14,82 How is your business and life? 8 0:0:15,719999 --> 0:0:16,6 Michael: Yeah, good. 9 0:0:16,72 --> 0:0:20,8 We're in spring in the UK and it's just getting a bit warmer 10 0:0:20,8 --> 0:0:24,4 now and yeah business is good ticking along I've got some upcoming 11 0:0:24,4 --> 0:0:26,92 news soon actually that I'll publish in the newsletter. 12 0:0:27,18 --> 0:0:28,779999 Nikolay: That's great yeah looking forward. 13 0:0:29,24 --> 0:0:30,14 Michael: How about you? 14 0:0:30,8 --> 0:0:33,36 Nikolay: Yeah obviously I'm a guest today right? 15 0:0:33,92 --> 0:0:35,78 Michael: Yeah What are we talking about? 16 0:0:36,1 --> 0:0:38,04 Nikolay: We talk about the queues again. 17 0:0:38,32 --> 0:0:39,18 Queues and Postgres. 18 0:0:39,52 --> 0:0:43,44 My favorite, remember I told you so many times I like to observe 19 0:0:43,62 --> 0:0:48,0 how many of them are created and how many of them have issues. 20 0:0:48,62 --> 0:0:52,239998 Actually, almost all of them have issues. 21 0:0:53,16 --> 0:0:56,86 Now I dig into the topic deeper, and actually I had surprises. 22 0:0:57,18 --> 0:1:2,8 My understanding in the past was not fully correct, And I'm going 23 0:1:2,8 --> 0:1:5,26 to confess today like where I was not. 24 0:1:6,04 --> 0:1:8,1 Like where there were gaps. 25 0:1:8,8 --> 0:1:12,38 Michael: Yeah, and just to clarify, when you say that they all 26 0:1:12,38 --> 0:1:17,86 have issues, do you mean queue implementations inside Postgres or 27 0:1:17,86 --> 0:1:20,58 inside relational database or OLTP database? 28 0:1:20,58 --> 0:1:24,24 Nikolay: Yeah, so when we have a new client, for example, at 29 0:1:24,24 --> 0:1:29,12 PostgresAI, we, our most popular type of client is a startup 30 0:1:29,18 --> 0:1:35,14 without DBA who are on RDS or Cloud SQL or Supabase, whatever. 31 0:1:35,94 --> 0:1:38,26 And they have issues because they have growth. 32 0:1:38,4 --> 0:1:40,22 This is my favorite type of client. 33 0:1:40,44 --> 0:1:41,96 And they bump into some problems. 34 0:1:42,34 --> 0:1:47,48 We check, we have various tooling for health checking and almost 35 0:1:47,5 --> 0:1:53,34 always we recognize 1 of a few patterns like log-like, append-only, 36 0:1:54,86 --> 0:1:59,76 unpartitioned huge table, or a table usually also unpartitioned, 37 0:1:59,96 --> 0:2:3,04 which receives some events to process. 38 0:2:4,88 --> 0:2:9,6 They got updates, deletes, And if this startup is more mature, 39 0:2:9,64 --> 0:2:12,1 we see some like pgmq, for example. 40 0:2:12,52 --> 0:2:15,3 Or if it's smaller, usually nothing. 41 0:2:15,56 --> 0:2:20,64 And everything is wrong there, starting from heavyweight log 42 0:2:20,64 --> 0:2:22,66 contention, right? 43 0:2:22,94 --> 0:2:26,26 But also bloat and a lot of complaints. 44 0:2:26,82 --> 0:2:29,58 Michael: Are we talking about like a self-rolled queue inside 45 0:2:29,58 --> 0:2:30,04 the database? 46 0:2:30,04 --> 0:2:33,08 Nikolay: Yes, so like naive implementation of queue in Postgres. 47 0:2:33,18 --> 0:2:37,88 You can see it naturally just looking at pg_stat_all_tables, noticing 48 0:2:38,1 --> 0:2:41,26 patterns of workload, a lot of updates and deletes, but also 49 0:2:41,26 --> 0:2:43,48 a lot of tuples and autovacuum. 50 0:2:43,74 --> 0:2:46,4 If it's untuned especially, it cannot keep up. 51 0:2:46,42 --> 0:2:49,64 But also if they have long running transactions or other reasons 52 0:2:49,64 --> 0:2:50,56 to block xmin horizon. 53 0:2:50,8 --> 0:2:53,66 We need to discuss it slightly deeper today. 54 0:2:54,14 --> 0:2:57,84 They will have a lot of bloat accumulated and all latencies suffer 55 0:2:57,84 --> 0:3:2,72 and all this like piece of workload and this table, usually just 56 0:3:2,72 --> 0:3:5,14 1 table, it becomes a hotspot. 57 0:3:5,98 --> 0:3:9,44 This is a huge reason for them to complain about how Postgres 58 0:3:9,44 --> 0:3:10,12 is bad. 59 0:3:10,12 --> 0:3:15,56 And I don't fully disagree, like only who didn't complain about 60 0:3:16,22 --> 0:3:22,84 Postgres MVCC and vacuum, all being headache all the time, everyone 61 0:3:22,84 --> 0:3:23,34 did. 62 0:3:26,64 --> 0:3:29,84 I usually said, all you need is 2 things. 63 0:3:30,18 --> 0:3:35,34 Actually not only said, I came to some implementations of queues 64 0:3:35,34 --> 0:3:37,78 less naive and even helped them. 65 0:3:37,78 --> 0:3:41,32 For example, long ago, there was a project called Delayed Jobs 66 0:3:41,32 --> 0:3:42,04 in Ruby. 67 0:3:43,08 --> 0:3:49,76 And I added a couple of things like index, which was easy, like 68 0:3:49,76 --> 0:3:54,4 just missing index, but also I said, let's use SKIP LOCKED for 69 0:3:54,4 --> 0:3:55,62 updates, SKIP LOCKED. 70 0:3:56,12 --> 0:4:0,5 So you just don't need this heavyweight lock contention when 71 0:4:0,5 --> 0:4:4,78 multiple sessions compete to update or delete the same row. 72 0:4:5,8 --> 0:4:6,88 It's quite straightforward. 73 0:4:7,9 --> 0:4:11,18 And to some others I said always like you just need 2 things, 74 0:4:11,46 --> 0:4:13,28 SKIP LOCKED and partitioning. 75 0:4:14,72 --> 0:4:17,94 And As I understand, this is where everyone went. 76 0:4:18,56 --> 0:4:23,94 So SKIP LOCKED created roughly 10, 11 years ago, actually 11, 2015. 77 0:4:24,72 --> 0:4:28,78 I think it was 9.5, right? 78 0:4:28,78 --> 0:4:29,84 Because it's 2015. 79 0:4:31,86 --> 0:4:36,44 That was a great feature to get rid of heavyweight lock contention. 80 0:4:37,7 --> 0:4:40,46 But it's not enough. 81 0:4:41,32 --> 0:4:42,88 It doesn't solve the bloat problem. 82 0:4:42,88 --> 0:4:46,36 Actually, somehow I noticed in this my recent work over the last 83 0:4:46,36 --> 0:4:50,74 few weeks on Hacker News discussions and some other places, I 84 0:4:50,74 --> 0:4:54,0 noticed people think that SKIP LOCKED will solve their bloat 85 0:4:54,0 --> 0:4:55,58 issues, vacuum issues. 86 0:4:55,58 --> 0:4:56,56 It's not so. 87 0:4:57,34 --> 0:5:2,0 So, partitioning and SKIP LOCKED is quite good enough, and this 88 0:5:2,0 --> 0:5:3,4 is where everyone went. 89 0:5:3,4 --> 0:5:9,34 I think pgmq, actually all modern queue systems and Postgres, they 90 0:5:9,52 --> 0:5:10,76 love SKIP LOCKED. 91 0:5:10,76 --> 0:5:12,54 They are like all about SKIP LOCKED. 92 0:5:12,7 --> 0:5:16,82 At some extent my AI bots started, We did a lot of research, 93 0:5:16,82 --> 0:5:18,76 did a lot of experimenting, benchmarks. 94 0:5:19,02 --> 0:5:23,38 So at some point I noticed they started to name all these guys 95 0:5:23,64 --> 0:5:26,02 SKIP LOCKED architecture somehow. 96 0:5:26,2 --> 0:5:30,12 Sometimes, I don't like it, I like more update-delete architecture. 97 0:5:30,24 --> 0:5:33,46 So update-delete-queue systems, not SKIP LOCKED systems. 98 0:5:33,46 --> 0:5:36,46 Because SKIP LOCKED, it's shifting too much attention to itself. 99 0:5:36,46 --> 0:5:40,88 But SKIP LOCKED is a simple thing, just let's get rid of heavyweight 100 0:5:40,88 --> 0:5:42,42 lock contention, that's it. 101 0:5:42,62 --> 0:5:45,86 Other problems which are bigger actually and harder to solve 102 0:5:45,86 --> 0:5:46,86 are not eliminated. 103 0:5:47,48 --> 0:5:51,14 They can be only mitigated with partitioning and rotation. 104 0:5:52,48 --> 0:5:57,22 And I saw, for example, pgmq, it's quite popular. 105 0:5:57,66 --> 0:6:0,42 I think this is a good legacy from Tembo. 106 0:6:1,86 --> 0:6:5,14 It's supported, I think it's supported on Supabase, quite popular 107 0:6:5,16 --> 0:6:5,66 there. 108 0:6:6,6 --> 0:6:11,36 And they actually also went to get rid of the need of create 109 0:6:11,36 --> 0:6:11,86 extension. 110 0:6:12,18 --> 0:6:15,46 So they re-implemented it fully in PL/pgSQL. 111 0:6:16,42 --> 0:6:20,78 And then in form like a trusted language, you know, pg_tle, trusted 112 0:6:20,78 --> 0:6:21,64 language extension. 113 0:6:21,74 --> 0:6:27,28 Since it's purely PL/pgSQL, you don't need to ask provider to support 114 0:6:27,28 --> 0:6:27,52 it. 115 0:6:27,52 --> 0:6:32,02 If provider supports pg_tle, You can have it and not only load 116 0:6:32,02 --> 0:6:36,36 it as only SQL file, but you can have it as tracked Extension 117 0:6:36,5 --> 0:6:39,3 without provider support because it's just a pure PL/pgSQL. 118 0:6:39,72 --> 0:6:44,72 So They also focus on SKIP LOCKED and they have support of partitioning 119 0:6:44,8 --> 0:6:49,14 but it requires pg_partman and additional effort Right, 120 0:6:49,22 --> 0:6:51,0 Michael: And just a question on their partitioning. 121 0:6:51,5 --> 0:6:57,32 Is it like time-based partitions and you detach and drop them 122 0:6:57,32 --> 0:6:57,88 over time? 123 0:6:57,88 --> 0:6:59,8 Or is it a rotational thing 124 0:6:59,8 --> 0:7:0,09332 Nikolay: like you talked about? 125 0:7:0,09332 --> 0:7:2,06 I don't remember but it doesn't matter actually. 126 0:7:2,16 --> 0:7:5,7 So what matters here, you cannot rotate partitions every minute. 127 0:7:5,74 --> 0:7:6,66 It's not practical. 128 0:7:6,98 --> 0:7:7,64 Sure, sure, sure. 129 0:7:7,64 --> 0:7:10,22 And it will lead actually to some other issues. 130 0:7:11,66 --> 0:7:15,6 So partition rotation should happen less often. 131 0:7:15,66 --> 0:7:20,04 And by the way, those who use partitioning to mitigate bloat, 132 0:7:20,04 --> 0:7:23,54 it's a great idea, but instead of detaching, attaching partitions, 133 0:7:23,56 --> 0:7:29,54 which I see in some queue-systems, better to have several static 134 0:7:29,54 --> 0:7:30,04 partitions. 135 0:7:30,1 --> 0:7:32,46 I mean, stop creating them. 136 0:7:32,5 --> 0:7:35,04 It will lead to catalog bloat eventually, right? 137 0:7:35,14 --> 0:7:40,74 And also detaching has its own issues under heavy loads. 138 0:7:41,4 --> 0:7:44,24 It's better just to truncate and have rotation. 139 0:7:44,92 --> 0:7:48,8 Like round robin of partitions and you just truncate them and 140 0:7:48,8 --> 0:7:49,54 that's it. 141 0:7:49,74 --> 0:7:52,48 So it's much better in many senses. 142 0:7:52,72 --> 0:7:54,44 And this is what PgQ does. 143 0:7:54,62 --> 0:7:58,32 So I always said like these 2 things are enough but I realized 144 0:7:58,32 --> 0:7:59,06 that they are not. 145 0:7:59,06 --> 0:8:0,22 That's the like... 146 0:8:1,3 --> 0:8:2,18 Michael: Can I just? 147 0:8:3,14 --> 0:8:8,3 PgQ, when you say PgQ, are we talking about your new tool, PgQue, 148 0:8:9,52 --> 0:8:13,8 or PgQ the Skype based origin, because just the letters PgQ? 149 0:8:14,04 --> 0:8:18,34 Nikolay: Let me, yeah, Let me explain how it all started. 150 0:8:18,76 --> 0:8:22,84 So on our podcast I was saying that's it, like just SKIP LOCKED 151 0:8:22,84 --> 0:8:25,74 and partitions, rotation or something. 152 0:8:25,92 --> 0:8:29,34 SKIP LOCKED felt natural because this is how we solve heavyweight 153 0:8:29,34 --> 0:8:33,42 lock contention when you have multiple backends competing to 154 0:8:33,42 --> 0:8:35,04 update or delete the same row. 155 0:8:35,14 --> 0:8:35,58 Michael: Yes, yes. 156 0:8:35,58 --> 0:8:36,52 Nikolay: Inserts cannot. 157 0:8:36,74 --> 0:8:38,0 The inserts don't need it. 158 0:8:38,0 --> 0:8:39,34 They are independent, right? 159 0:8:39,34 --> 0:8:41,62 But updates and deletes, they can compete. 160 0:8:42,5 --> 0:8:47,84 And when I said partitioning, I always said, look at PgQ Skype 161 0:8:47,84 --> 0:8:49,22 created 20 years ago. 162 0:8:50,66 --> 0:8:51,34 And that's it. 163 0:8:51,34 --> 0:8:52,46 I thought it's enough. 164 0:8:53,04 --> 0:8:55,34 I thought Skype didn't have it. 165 0:8:55,44 --> 0:8:58,82 I mean Skype didn't have SKIP LOCKED that time. 166 0:8:59,86 --> 0:9:3,4 So I was thinking they did it differently because SKIP LOCKED didn't 167 0:9:3,4 --> 0:9:5,9 exist in 2006 and 7. 168 0:9:6,42 --> 0:9:11,02 PgQ was created exactly 20 years ago, 2006, and it was open sourced 169 0:9:11,4 --> 0:9:12,16 in 2007. 170 0:9:12,44 --> 0:9:14,54 Now I'm talking about SkyTools PgQ. 171 0:9:15,48 --> 0:9:15,98 Right. 172 0:9:16,6 --> 0:9:17,1 Michael: Yes. 173 0:9:17,86 --> 0:9:21,5 But, okay, but it didn't do skip lock because it but it was also 174 0:9:21,5 --> 0:9:25,32 not doing updates or deletes right like it 175 0:9:25,32 --> 0:9:27,26 Nikolay: was Exactly, we will come 176 0:9:27,26 --> 0:9:28,0 Michael: to that. 177 0:9:29,34 --> 0:9:32,78 Nikolay: But I was thinking I had a false impression that we 178 0:9:32,78 --> 0:9:36,82 should use keyplog because it's a modern path, but we also should 179 0:9:36,82 --> 0:9:40,76 mitigate bloat issues, vacuum problems, and so on, just using 180 0:9:40,76 --> 0:9:42,22 partitioning and rotation. 181 0:9:42,54 --> 0:9:48,94 And coincidentally, this is how all current guys are doing it. 182 0:9:49,54 --> 0:9:52,54 River, pgmq, Que, others. 183 0:9:55,68 --> 0:9:56,82 There are many now. 184 0:9:57,04 --> 0:10:0,08 And also some of them are quite agnostic to languages. 185 0:10:0,18 --> 0:10:4,76 Some like River is Go oriented, so they're focused on Go frameworks. 186 0:10:4,76 --> 0:10:6,82 And that's it. 187 0:10:6,82 --> 0:10:8,62 And then what happened actually? 188 0:10:8,62 --> 0:10:11,54 So this is my understanding 3 weeks ago. 189 0:10:11,82 --> 0:10:12,74 Then what happened? 190 0:10:13,18 --> 0:10:17,3 I was in Zion Canyon On campground and they have good connection. 191 0:10:17,44 --> 0:10:17,94 Actually. 192 0:10:18,34 --> 0:10:23,04 I was in my tent And I saw that it was Friday evening. 193 0:10:23,04 --> 0:10:29,44 I think and I saw plane scale blog post Yeah Right, and I think 194 0:10:29,44 --> 0:10:33,12 I was I started to read not a blog post itself because I quickly 195 0:10:33,12 --> 0:10:34,34 realized what it's about. 196 0:10:35,14 --> 0:10:38,74 I started reading discussions of it on Twitter on X. 197 0:10:40,44 --> 0:10:45,88 So that post was dancing around Brandur Leach, who is actually 198 0:10:45,88 --> 0:10:47,78 1 of creators of River Queue. 199 0:10:49,54 --> 0:10:55,44 So Brandur had a post in 2015 about how, like, basically how 200 0:10:56,48 --> 0:11:3,48 challenging it is to have queue in Postgres because of MVCC and if 201 0:11:3,48 --> 0:11:6,92 you have long-running transaction with XID assigned or repeatable... 202 0:11:7,54 --> 0:11:11,82 I think Brandur used repeatable read transaction lasting 1 hour 203 0:11:11,82 --> 0:11:13,34 or half an hour, I don't remember. 204 0:11:13,68 --> 0:11:20,24 And I think in his post it was like below 1, 000 events per second 205 0:11:20,76 --> 0:11:22,86 inserted, like maybe 800. 206 0:11:23,86 --> 0:11:30,56 And quickly, like something like 60, 000 events were accumulated 207 0:11:30,66 --> 0:11:34,06 unprocessed by consumers because everything started to lag and 208 0:11:34,06 --> 0:11:34,54 so on. 209 0:11:34,54 --> 0:11:35,28 It was 2015. 210 0:11:36,58 --> 0:11:40,84 I think this is actually was a year when SKIP LOCKED was added 211 0:11:40,84 --> 0:11:41,54 to Postgres. 212 0:11:42,34 --> 0:11:43,06 Interesting right? 213 0:11:43,66 --> 0:11:43,76 2015. 214 0:11:43,76 --> 0:11:44,64 Yeah, good timing. 215 0:11:45,18 --> 0:11:45,68 Yeah. 216 0:11:46,64 --> 0:11:51,74 So PlanetScale discussed how bad it is. 217 0:11:51,74 --> 0:11:56,8 Not like queue and Postgres are bad, but it's bad to have long-running 218 0:11:56,86 --> 0:12:0,26 transactions or something which is blocking xmin horizon, right? 219 0:12:0,86 --> 0:12:4,7 And they promoted their new feature, how to mitigate it, but 220 0:12:4,7 --> 0:12:5,64 mitigate how? 221 0:12:6,04 --> 0:12:8,56 Just cancel that, right? 222 0:12:8,56 --> 0:12:11,36 So they have some smart approach, like which traffic is more 223 0:12:11,36 --> 0:12:12,86 important, which is less important. 224 0:12:12,86 --> 0:12:16,3 In my opinion, what we created with Andrei, Transaction timeout 225 0:12:16,3 --> 0:12:20,36 is good enough for everyone as default solution against long-running 226 0:12:20,4 --> 0:12:24,5 transactions Although there might be other problems like unused 227 0:12:24,52 --> 0:12:28,44 or lagging logical replication slot, right? 228 0:12:28,62 --> 0:12:33,18 Yeah, it can be or Maybe someone is using 2PC and prepared transactions 229 0:12:33,34 --> 0:12:34,7 also can be a problem. 230 0:12:35,2 --> 0:12:38,48 Michael: So anything that holds xmin horizon and prevents the 231 0:12:38,48 --> 0:12:41,82 cleanup of dead or like old versions. 232 0:12:42,18 --> 0:12:44,96 Nikolay: By the way, we just released our monitoring which is 233 0:12:44,96 --> 0:12:45,96 like front. 234 0:12:46,84 --> 0:12:51,66 When we say front, we mean monitoring with Grafana and Victoria 235 0:12:51,66 --> 0:12:53,5 Metrics and Postgres inside everything. 236 0:12:53,92 --> 0:12:57,44 We just released with our new dashboard for Xmin Horizon analysis. 237 0:12:58,38 --> 0:13:0,96 And there are 5 possible reasons. 238 0:13:1,02 --> 0:13:5,68 And also Laurenz Albe, very timely posted, blog post about 239 0:13:5,74 --> 0:13:7,84 how he monitors autovacuum. 240 0:13:9,12 --> 0:13:12,1 I stole a couple of thoughts there, I just implemented it in 241 0:13:12,1 --> 0:13:15,22 dashboard and it's already released and like it's free for use 242 0:13:15,22 --> 0:13:20,16 Apache license, but it works much better if you go and become 243 0:13:20,16 --> 0:13:23,24 our customer, because we have great new health metrics. 244 0:13:23,24 --> 0:13:25,02 I will blog post about it separately. 245 0:13:25,32 --> 0:13:29,34 Anyway, this is connected because xmin horizon, like I, we also 246 0:13:29,34 --> 0:13:30,04 talked about it. 247 0:13:30,04 --> 0:13:32,76 Everyone monitors long running transactions, but it's off. 248 0:13:32,76 --> 0:13:34,54 It's wrong thing to monitor in this context. 249 0:13:34,54 --> 0:13:38,3 You need to understand xmin horizon being blocked, and by whom, 250 0:13:38,52 --> 0:13:42,38 to unblock it promptly, because this is how you can put your 251 0:13:42,38 --> 0:13:45,4 River or Prisma Queue or something down, actually. 252 0:13:45,4 --> 0:13:50,2 Not down, but basically lagging and having very poor performance. 253 0:13:50,98 --> 0:13:54,64 Michael: Accumulating a lot of bloat that is not ever recovered, 254 0:13:54,72 --> 0:13:56,4 if it's not using like a... 255 0:13:56,4 --> 0:13:59,38 I think that's the other thing that people don't realize is there's 256 0:13:59,38 --> 0:14:4,44 no recovering from that because once that's bloated unless you're 257 0:14:4,44 --> 0:14:6,1 using like the partition rotation. 258 0:14:6,22 --> 0:14:8,3 Nikolay: There are several things here several things. 259 0:14:8,3 --> 0:14:11,06 First it's a lot of dead tuples are accumulated. 260 0:14:11,72 --> 0:14:12,22 Yeah. 261 0:14:12,74 --> 0:14:17,22 Because xmin horizon is blocked so dead tuples are created every 262 0:14:17,22 --> 0:14:21,94 time you produce delete, successful, delete, successful, update, 263 0:14:22,1 --> 0:14:23,64 or unsuccessful insert. 264 0:14:24,02 --> 0:14:26,92 So it means, by the way, that we also can produce dead tuples 265 0:14:26,92 --> 0:14:28,4 if some inserts are failing. 266 0:14:28,86 --> 0:14:30,36 But this is very subtle. 267 0:14:31,32 --> 0:14:35,04 It's nuance, like we can omit it, right? 268 0:14:35,14 --> 0:14:37,82 So regular approach, we always produce dead tuples. 269 0:14:37,82 --> 0:14:39,36 Tuple is a row version. 270 0:14:39,52 --> 0:14:43,82 We agreed on our first episode that I say tuple, you say tuple, 271 0:14:43,82 --> 0:14:44,6 or vice versa. 272 0:14:44,6 --> 0:14:45,42 I don't remember. 273 0:14:45,9 --> 0:14:46,3 Yeah. 274 0:14:46,3 --> 0:14:47,56 Anyway, tuple is a row version. 275 0:14:47,56 --> 0:14:54,06 So old version becomes dead, but it's still hanging out everywhere 276 0:14:54,14 --> 0:14:56,44 actually, including shared buffers everywhere. 277 0:14:57,04 --> 0:14:58,2 It's polluting everything. 278 0:14:58,26 --> 0:15:2,8 So garbage collection called vacuum is needed to delete it. 279 0:15:3,06 --> 0:15:4,4 So it's a two-phase process. 280 0:15:4,74 --> 0:15:6,92 First it's only marked that and then it's deleted. 281 0:15:7,84 --> 0:15:10,22 And the first problem, a lot of dead tuples are accumulated, 282 0:15:10,24 --> 0:15:13,48 they cannot be deleted by garbage collection called autovacuum. 283 0:15:15,1 --> 0:15:20,04 And this first bad effect is latencies of consumption degrade 284 0:15:21,42 --> 0:15:22,56 very fast, actually. 285 0:15:23,1 --> 0:15:27,9 Because to find the next thing, you need to skim through all 286 0:15:27,9 --> 0:15:33,58 the tuples with your index scan and it becomes less and less 287 0:15:33,74 --> 0:15:34,24 performant. 288 0:15:34,9 --> 0:15:42,02 Next bad thing is that we accumulate a big set of unprocessed 289 0:15:42,54 --> 0:15:43,04 events. 290 0:15:43,04 --> 0:15:44,06 Sometimes, not always. 291 0:15:44,06 --> 0:15:45,86 Sometimes we have degradation. 292 0:15:46,08 --> 0:15:49,04 And when I say degradation, it means like degradation was, it 293 0:15:49,04 --> 0:15:53,68 was like 1 millisecond to fetch next event, for example, or a 294 0:15:53,68 --> 0:15:54,58 bunch of events. 295 0:15:54,84 --> 0:15:57,64 And then it degrades to second or a few seconds. 296 0:15:57,72 --> 0:16:1,02 During 1 hour, I saw, I think, 5 seconds for some queues. 297 0:16:1,48 --> 0:16:4,1 Just to fetch 1 event, 5 seconds, can you imagine? 298 0:16:4,12 --> 0:16:8,1 It already, at some point in some systems, if you keep inserting 299 0:16:8,1 --> 0:16:11,18 a lot of events and consuming them, at some point it might start 300 0:16:11,18 --> 0:16:12,72 timing out on statement timeout. 301 0:16:12,72 --> 0:16:16,8 If you have strict statement timeout, as you should for all OLTP 302 0:16:16,8 --> 0:16:17,3 systems. 303 0:16:17,44 --> 0:16:20,34 We always recommend to have strict timeouts. 304 0:16:22,5 --> 0:16:26,08 So a lot of that apples degradation of consumer performance, 305 0:16:27,04 --> 0:16:27,94 consumer query. 306 0:16:28,52 --> 0:16:31,72 Second effect is a lot of them accumulated just because consumer 307 0:16:31,72 --> 0:16:33,5 capacity throughput is not enough. 308 0:16:34,6 --> 0:16:37,48 You have, for example, 10 consumers working in parallel, skip 309 0:16:37,48 --> 0:16:40,84 lock to help them not to fight with each other. 310 0:16:41,4 --> 0:16:44,76 And you have capacity, for example, to consume 2000 events per 311 0:16:44,76 --> 0:16:45,26 second. 312 0:16:45,3 --> 0:16:49,26 But now suddenly latency became from 1 millisecond to 1 second 313 0:16:49,64 --> 0:16:51,36 thousand times worse. 314 0:16:51,6 --> 0:16:54,18 It can happen during 10 minutes or so. 315 0:16:54,52 --> 0:16:57,54 And 10 minutes just like for example you have multi-terabyte 316 0:16:57,7 --> 0:16:58,2 database. 317 0:16:58,2 --> 0:17:2,72 If you decided to dump it, right, Or create logical replica in 318 0:17:2,72 --> 0:17:3,9 a traditional way. 319 0:17:4,82 --> 0:17:5,58 This is it. 320 0:17:5,58 --> 0:17:10,98 This is how you can reach second level of consumer query performance. 321 0:17:11,82 --> 0:17:15,04 And this leads to accumulation of unprocessed events. 322 0:17:16,02 --> 0:17:20,4 And for some queues I noticed even when we stop a long-running 323 0:17:20,42 --> 0:17:22,26 transaction, we unblock xmin horizon. 324 0:17:23,4 --> 0:17:27,18 First of all, autovacuum comes, cleans up dead tuples, immediately 325 0:17:27,44 --> 0:17:29,64 latency for consumer query drops. 326 0:17:30,48 --> 0:17:33,56 But for some it didn't recover to previous level. 327 0:17:33,74 --> 0:17:36,14 It didn't recover to 1 millisecond level. 328 0:17:36,34 --> 0:17:38,98 It stayed like 50 milliseconds or so. 329 0:17:39,08 --> 0:17:41,26 I think it was QUE, which is like Q-U-E. 330 0:17:42,26 --> 0:17:43,4 Very bad naming. 331 0:17:43,86 --> 0:17:44,56 It's mine. 332 0:17:45,78 --> 0:17:47,0 Michael: I call it care. 333 0:17:47,12 --> 0:17:47,9 I call it care 334 0:17:47,9 --> 0:17:48,48 Nikolay: in Spanish. 335 0:17:48,48 --> 0:17:52,54 If you check, I'm not speaking Spanish, but who is speaking Spanish, 336 0:17:52,64 --> 0:17:55,42 how my tool is pronounced, it's crazy, right? 337 0:17:55,44 --> 0:17:57,74 Something like PepeK or something, I don't know. 338 0:17:57,74 --> 0:18:0,6 I have an issue, and actually I have already pulled a request 339 0:18:0,6 --> 0:18:4,28 to rename, but I didn't like all brainstormed names so far. 340 0:18:4,28 --> 0:18:8,42 pg_belt, this was the best I had, I didn't like it. 341 0:18:8,42 --> 0:18:10,76 And we will come to that, why pg_belt, right? 342 0:18:10,76 --> 0:18:13,28 And why queue actually is misleading, as I learned. 343 0:18:13,58 --> 0:18:17,8 I learned 2 big things during this journey, last 3 weeks or 2 344 0:18:17,8 --> 0:18:18,3 weeks. 345 0:18:18,5 --> 0:18:21,24 Michael: So just quickly, before we move on from side effects, 346 0:18:21,32 --> 0:18:26,32 I want, so eventually even in a non-partition system, the heap 347 0:18:26,32 --> 0:18:30,18 bloat would get re-once the tuples are marked dead and vacuum 348 0:18:30,18 --> 0:18:35,98 comes along, marks it's like reusable, new jobs or new events 349 0:18:35,98 --> 0:18:38,54 can go into that space in the table. 350 0:18:39,76 --> 0:18:42,34 But people don't, and I know we've talked about this like thousands 351 0:18:42,34 --> 0:18:45,92 of times, but it's the indexes that can end up bloated in a way 352 0:18:45,92 --> 0:18:48,34 that isn't recoverable without a reindex. 353 0:18:48,4 --> 0:18:52,62 So I wonder if that could be the source of the not ever recovering. 354 0:18:53,3 --> 0:18:54,14 Nikolay: Maybe, yes. 355 0:18:54,4 --> 0:18:55,24 Yes, you're right. 356 0:18:55,24 --> 0:18:59,94 And bloat might still be there, and this is what can be solved 357 0:19:0,52 --> 0:19:1,62 post-factum, actually. 358 0:19:1,62 --> 0:19:5,1 It can be solved with partitions and truncate and in rotation 359 0:19:5,34 --> 0:19:8,76 because truncate means like you will have fresh empty index and 360 0:19:8,76 --> 0:19:12,18 it will start growing from scratch it's great but again I was 361 0:19:12,18 --> 0:19:15,44 thinking first actually it's an interesting nuance as well I 362 0:19:15,44 --> 0:19:18,62 was thinking oh partitioning won't help in the middle of long 363 0:19:18,62 --> 0:19:21,36 running transactions, in the middle of when the xmin horizon 364 0:19:21,36 --> 0:19:21,6 will... 365 0:19:21,6 --> 0:19:22,62 Actually it will. 366 0:19:22,76 --> 0:19:23,6 It will help. 367 0:19:24,06 --> 0:19:24,72 It will help. 368 0:19:24,72 --> 0:19:28,04 You will switch to a new partition, old partition will keep old 369 0:19:28,04 --> 0:19:31,66 stuff with dead tuples, degraded indexes and that's it. 370 0:19:31,84 --> 0:19:35,04 And this is actually, it will lead us to interesting optimization 371 0:19:35,9 --> 0:19:36,8 in my tool. 372 0:19:37,42 --> 0:19:41,76 But what it won't be possible to do, to have, I think it's possible, 373 0:19:41,76 --> 0:19:45,56 but I wouldn't recommend it, to have aggressive, like every minute 374 0:19:45,78 --> 0:19:47,38 partition rotation and truncation. 375 0:19:48,58 --> 0:19:52,82 First of all, you need a lot of partitions, maybe. 376 0:19:52,82 --> 0:19:53,68 Maybe not, actually. 377 0:19:53,68 --> 0:19:57,8 Maybe you can find a way to just jump between them. 378 0:19:57,8 --> 0:20:1,62 But it's not practical, again, because of catalog bloat and stress. 379 0:20:1,96 --> 0:20:4,54 When you switch to new partition and some stress and you need 380 0:20:4,54 --> 0:20:7,32 to clean up and all everything should be processed. 381 0:20:7,42 --> 0:20:12,2 And if you dropped, if your dropped latency, like degraded latency 382 0:20:12,24 --> 0:20:15,74 leads to event, unprocessed event accumulation, you won't be 383 0:20:15,74 --> 0:20:19,12 able to recycle, right? 384 0:20:19,44 --> 0:20:22,26 Old partition because it still has useful data. 385 0:20:23,04 --> 0:20:26,24 You can move it to new, like it's becoming nightmare actually. 386 0:20:26,5 --> 0:20:32,5 So anyway, it feels architectural wrong for me to have very often 387 0:20:32,5 --> 0:20:33,58 partitioning switch. 388 0:20:34,24 --> 0:20:35,16 Right, this is... 389 0:20:35,16 --> 0:20:37,94 And back to your question, time-based or size-based. 390 0:20:38,16 --> 0:20:39,52 Well, time-based is fine. 391 0:20:39,52 --> 0:20:41,82 I actually don't remember what I have in settings. 392 0:20:41,82 --> 0:20:42,78 I need to check. 393 0:20:43,22 --> 0:20:48,16 But here your other question, are we talking about PgQue new or 394 0:20:48,16 --> 0:20:49,84 PgQ old SkyTools? 395 0:20:50,28 --> 0:20:54,16 So what I did I took it as is, right? 396 0:20:54,16 --> 0:20:59,44 First thing I did, I took it as is and I just started to build 397 0:20:59,44 --> 0:21:0,18 around it. 398 0:21:0,18 --> 0:21:3,72 So I'm not touching core engine, That's the key. 399 0:21:3,72 --> 0:21:8,86 Michael: So the original SkyTools PgQ core engine remains. 400 0:21:9,44 --> 0:21:9,84 Nikolay: Great. 401 0:21:9,84 --> 0:21:10,24 I 402 0:21:10,24 --> 0:21:13,26 Michael: mean I knew that but not actually by reading your... 403 0:21:13,26 --> 0:21:13,78 I saw... 404 0:21:13,78 --> 0:21:14,88 Did you see a really... 405 0:21:15,06 --> 0:21:18,28 I thought it was a good blog post by Christophe Pettus covering 406 0:21:18,48 --> 0:21:20,54 the 0.1 announcement. 407 0:21:21,66 --> 0:21:24,72 Yeah, he's been on a bit of a blogging spree lately, but he's 408 0:21:24,72 --> 0:21:29,44 done a blog post about PgQ, or PgQue, your tool, already. 409 0:21:29,68 --> 0:21:30,42 Nikolay: I missed it. 410 0:21:30,42 --> 0:21:30,9 It's cool. 411 0:21:30,9 --> 0:21:31,12 Nice. 412 0:21:31,12 --> 0:21:36,26 Well, what I wanted, I wanted to say, guys, like there is alternative 413 0:21:37,08 --> 0:21:41,32 forgotten Kung Fu, I say, because I know how trustworthy it is. 414 0:21:41,32 --> 0:21:42,24 20 years. 415 0:21:42,72 --> 0:21:47,64 I use it in 2 of my 3 social network startups myself before we, 416 0:21:47,64 --> 0:21:51,18 I think it's a mistake, switched to RabbitMQ, I regret it now. 417 0:21:51,28 --> 0:21:52,32 We use it heavily. 418 0:21:52,38 --> 0:21:54,96 We use it even originally as Skype. 419 0:21:54,96 --> 0:21:58,58 Skype build it also not only like for event processing, but also 420 0:21:58,58 --> 0:22:0,04 to have logical replication. 421 0:22:0,8 --> 0:22:4,9 Instead of Slony, they built Londiste and it was working on 422 0:22:4,9 --> 0:22:6,3 top of PgQ. 423 0:22:7,36 --> 0:22:10,06 A native logical replication didn't exist at the time. 424 0:22:10,58 --> 0:22:13,48 So it was serving many purposes. 425 0:22:14,2 --> 0:22:17,3 And When they built it, Kafka didn't exist. 426 0:22:17,3 --> 0:22:20,04 Kafka created in 2011, I looked at. 427 0:22:20,34 --> 0:22:25,74 So it's called queue, but by nature, this is second big thing I learned. 428 0:22:26,12 --> 0:22:27,54 It's not a queue system. 429 0:22:27,6 --> 0:22:32,72 It's a immutable log similar to Kafka, not distributed like Kafka, 430 0:22:32,9 --> 0:22:35,78 but or Redpanda, also modern thing, right? 431 0:22:35,98 --> 0:22:36,72 As I learned. 432 0:22:37,28 --> 0:22:41,3 And it just guarantees that something is inserted, there is order, 433 0:22:41,52 --> 0:22:43,74 and consumer is just a pointer shifting. 434 0:22:45,06 --> 0:22:49,16 But it can be used for queue-like workloads, maybe not all of them, 435 0:22:49,16 --> 0:22:51,16 and we can discuss it in a bit. 436 0:22:51,16 --> 0:22:54,66 But what I wanted, I saw this blog post from PlanetScale, and again, 437 0:22:54,66 --> 0:22:57,18 these discussions, like just use SKIP LOCKED. 438 0:22:57,54 --> 0:23:1,48 And first thing I posted, and I found big feedback, like people, 439 0:23:1,48 --> 0:23:2,74 like, yeah, that's it. 440 0:23:2,8 --> 0:23:5,94 I said, first of all, SKIP LOCKED doesn't solve vacuum problems. 441 0:23:6,26 --> 0:23:7,74 It's just very wrong. 442 0:23:8,08 --> 0:23:11,18 SKIP LOCKED solves heavyweight lock contention. 443 0:23:12,26 --> 0:23:12,76 Right. 444 0:23:12,84 --> 0:23:16,56 And also SKIP LOCKED, there is another post from Laurenz, SELECT 445 0:23:16,56 --> 0:23:19,14 for update considered harmful, right? 446 0:23:19,6 --> 0:23:22,9 So there are issues with SKIP LOCKED, SKIP LOCKED is a part of select 447 0:23:22,9 --> 0:23:23,32 update. 448 0:23:23,32 --> 0:23:24,88 You cannot use it without SELECT FOR UPDATE. 449 0:23:24,88 --> 0:23:27,14 And there are issues with that approach as well. 450 0:23:27,62 --> 0:23:28,78 Additional danger is there. 451 0:23:29,2 --> 0:23:34,44 But I wanted to show like there is PgQ from SkyTools and we 452 0:23:34,44 --> 0:23:37,28 all know another tool called pgBouncer. 453 0:23:37,9 --> 0:23:41,74 And I'm wondering, and I know PgQ is still used in very large 454 0:23:41,74 --> 0:23:46,28 companies as a important building block, very reliable, very 455 0:23:46,28 --> 0:23:46,78 performant. 456 0:23:47,1 --> 0:23:50,54 Skype originally built architecture for, as I learned from their 457 0:23:50,54 --> 0:23:52,76 talks, for 1 billion users. 458 0:23:54,22 --> 0:23:55,9 They achieved hundreds of millions. 459 0:23:55,9 --> 0:23:59,2 I don't know after acquisition by Microsoft, by the way, Skype 460 0:23:59,2 --> 0:24:1,24 was closed last year. 461 0:24:1,5 --> 0:24:2,28 Also case. 462 0:24:2,98 --> 0:24:5,24 So that's an interesting legacy here. 463 0:24:5,38 --> 0:24:9,74 And I'm thinking, why PgBouncer became quite popular, but PgQ 464 0:24:10,32 --> 0:24:12,28 didn't became quite popular? 465 0:24:12,56 --> 0:24:18,24 And my hypothesis is it's because of its extension, which requires 466 0:24:18,24 --> 0:24:19,26 additional daemon. 467 0:24:21,3 --> 0:24:24,12 And maybe I'm wrong, but maybe providers just didn't want to 468 0:24:24,12 --> 0:24:27,72 bring any additional daemon which is not a regular background 469 0:24:27,72 --> 0:24:30,9 worker, but something that needs to be managed separately. 470 0:24:32,0 --> 0:24:32,5599 And since you like... 471 0:24:32,5599 --> 0:24:35,46 Michael: Whereas PgBouncer is completely independent and... 472 0:24:36,18 --> 0:24:37,72 Nikolay: Well yeah, yeah, yeah, yeah. 473 0:24:37,72 --> 0:24:39,78 But yeah, that's a good point. 474 0:24:39,96 --> 0:24:41,18 It needs to be managed. 475 0:24:41,28 --> 0:24:44,08 There are some providers who provide it as managed service, but 476 0:24:44,08 --> 0:24:48,96 pgBouncer is like old tool solving that problem. 477 0:24:49,2 --> 0:24:52,6 Here it's inside Postgres and we need additional daemon. 478 0:24:52,6 --> 0:24:56,32 I don't know, like I didn't participate in any of those decisions, 479 0:24:56,32 --> 0:24:59,94 but I just don't see any of providers supporting PgQ. 480 0:25:0,48 --> 0:25:3,96 Michael: Maybe on CloudSQL actually, is it? 481 0:25:3,96 --> 0:25:7,76 Nikolay: They support PL... This is maybe I told you this and I 482 0:25:7,76 --> 0:25:8,2 was wrong. 483 0:25:8,2 --> 0:25:11,68 I mixed it with another tool from SkyTools called PL proxy. 484 0:25:11,74 --> 0:25:13,5 CloudSQL supports PL proxy. 485 0:25:13,84 --> 0:25:14,34 Yeah. 486 0:25:14,76 --> 0:25:19,64 And yeah, hello, Hannu Krosing, who actually liked every single 487 0:25:19,64 --> 0:25:23,54 my post I think on LinkedIn about PgQue because this is... He was 488 0:25:23,54 --> 0:25:24,6 from Skype as well. 489 0:25:24,6 --> 0:25:25,54 So it's great. 490 0:25:25,76 --> 0:25:26,32 It's great. 491 0:25:26,32 --> 0:25:31,62 And yeah, so Then interesting thing like I'm thinking okay, you 492 0:25:31,62 --> 0:25:36,0 know, you know my work pg_ash anti-extension concept right? 493 0:25:36,58 --> 0:25:39,16 Michael: Yes yeah we talked about it. 494 0:25:39,38 --> 0:25:42,4 Nikolay: Yeah then there are others like I have a couple of more 495 0:25:42,4 --> 0:25:47,64 coming but then I think okay I just need to repackage it right 496 0:25:48,14 --> 0:25:53,8 and being in my tent I have new tool to create thorough specification, 497 0:25:54,52 --> 0:25:58,64 so spec creation tool, which I use for many things now. 498 0:25:59,34 --> 0:26:0,04 It's CLI. 499 0:26:2,38 --> 0:26:5,54 You start from idea and then explore questions, research, and 500 0:26:5,54 --> 0:26:8,7 then build a comprehensive tool and then iterate with multiple 501 0:26:9,4 --> 0:26:12,54 LLMs, most powerful you can reach. 502 0:26:12,82 --> 0:26:15,56 And then you have already like version 7, for example, which 503 0:26:15,56 --> 0:26:16,9 is ready for implementation. 504 0:26:17,28 --> 0:26:23,14 So I wrote spec, how to repackage PgQ from Skype in this anti-extension 505 0:26:23,4 --> 0:26:23,8 manner. 506 0:26:23,8 --> 0:26:25,68 So no create extension is needed. 507 0:26:25,76 --> 0:26:31,0 And since like we discussed this, since we have pg_cron, Like 508 0:26:31,0 --> 0:26:32,28 with pg_ash, same thing. 509 0:26:32,28 --> 0:26:33,28 Michael: Almost everywhere. 510 0:26:33,74 --> 0:26:34,24 Nikolay: Yeah. 511 0:26:34,54 --> 0:26:35,04 Yeah. 512 0:26:35,32 --> 0:26:35,5667 You need a ticker. 513 0:26:35,5667 --> 0:26:36,6 So that's why that daemon was needed. 514 0:26:36,6 --> 0:26:37,5 You need ticker. 515 0:26:37,72 --> 0:26:39,34 So PgQ, it's a log. 516 0:26:39,34 --> 0:26:44,48 There is an insert, can be single insert or batch, never updates, 517 0:26:44,54 --> 0:26:45,04 never deletes. 518 0:26:45,04 --> 0:26:48,3 And there is basically their own horizon. 519 0:26:48,84 --> 0:26:51,02 It's based on snapshots of data, right? 520 0:26:51,02 --> 0:26:53,5 So every consumer knows position. 521 0:26:54,06 --> 0:26:56,98 And to shift position, you need to tick, you need to announce, 522 0:26:56,98 --> 0:27:0,14 okay, we shifted because something new arrived. 523 0:27:0,28 --> 0:27:3,92 And by default, PgQ from SkyTools, it ticks every second. 524 0:27:4,84 --> 0:27:9,38 So it shifts and the consumer sees new data and fetches a whole 525 0:27:9,38 --> 0:27:11,3 batch of data, events. 526 0:27:12,18 --> 0:27:17,06 So batch is by default, like the batch processing is by default. 527 0:27:17,66 --> 0:27:20,34 Michael: The thing I didn't understand is that you have a second 528 0:27:20,38 --> 0:27:22,86 table for keeping track of those. 529 0:27:23,22 --> 0:27:27,38 Nikolay: There are meta tables, yeah, for ticking and for subscriptions, 530 0:27:28,26 --> 0:27:32,9 but queue itself, it's 3 partitions, old school inheritance partition, 531 0:27:32,9 --> 0:27:36,52 because it was created before native partitioning was created. 532 0:27:36,58 --> 0:27:41,24 So 3 partitions, 1 is in work, 1 is like in the past, 1 is in 533 0:27:41,24 --> 0:27:41,74 future. 534 0:27:42,34 --> 0:27:44,64 And there is rotation using truncate. 535 0:27:45,3 --> 0:27:49,44 There is also a delayed table separately for those events which 536 0:27:49,44 --> 0:27:52,94 cannot be processed now Can they need retry? 537 0:27:53,92 --> 0:27:54,98 We put it there 538 0:27:56,0 --> 0:27:59,42 Michael: Yeah, I don't end up truncating jobs that haven't been 539 0:27:59,42 --> 0:27:59,64 done. 540 0:27:59,64 --> 0:28:1,2 Nikolay: Yeah, It was a single table. 541 0:28:1,2 --> 0:28:5,2 I think I also implemented the same pattern there because sometimes 542 0:28:5,2 --> 0:28:9,96 we have, we might have a lot of jobs to be retried, events to 543 0:28:9,96 --> 0:28:11,3 be retried for processing. 544 0:28:11,32 --> 0:28:13,04 So I created also 3 partitions. 545 0:28:13,18 --> 0:28:17,98 You also create a dead letter queue for concept of maximum retries, 546 0:28:17,98 --> 0:28:20,78 and then we put it to dead letter not to retry forever. 547 0:28:21,22 --> 0:28:25,84 So, I just suggested this is how many modern systems work. 548 0:28:25,84 --> 0:28:26,88 I said, oh, good idea. 549 0:28:26,88 --> 0:28:29,98 Let's adopt it here on top of what already exists. 550 0:28:30,52 --> 0:28:32,4 So I created a spec. 551 0:28:33,06 --> 0:28:37,24 So I was thinking, guys, I understand the plain scale position. 552 0:28:37,54 --> 0:28:43,04 Let's just have a shotgun and fire all those long running transactions. 553 0:28:43,04 --> 0:28:43,54 Great. 554 0:28:43,58 --> 0:28:45,52 Transaction timeout also that's shotgun. 555 0:28:45,66 --> 0:28:49,7 Maybe less smart, but still working and reliable. 556 0:28:50,54 --> 0:28:52,66 But let's just compare performance. 557 0:28:53,04 --> 0:28:54,86 I said, OK, let's compare performance. 558 0:28:54,86 --> 0:28:58,1 And I said it in the same session of Cloud Code, where we just 559 0:28:58,1 --> 0:28:59,02 created the spec. 560 0:28:59,18 --> 0:29:3,12 And then we started benchmarking, provisioned, I think, 7 VMs 561 0:29:3,4 --> 0:29:6,8 in cloud for alternatives and PgQ. 562 0:29:7,2 --> 0:29:10,3 And I noticed that provisioned 2 machines for PgQ. 563 0:29:10,92 --> 0:29:16,06 1 was original PgQ and 1 it called like 564 0:29:17,02 --> 0:29:17,76 PL mode. 565 0:29:17,78 --> 0:29:19,78 I was thinking, okay, what is PL mode? 566 0:29:19,86 --> 0:29:22,76 We're supposed to create it according to spec, but we haven't 567 0:29:22,76 --> 0:29:23,9 implemented it yet. 568 0:29:24,32 --> 0:29:27,48 It said, you created it in 2019. 569 0:29:27,98 --> 0:29:31,26 I said, okay, first of all, it was not me, it was Marko Kreen, 570 0:29:32,84 --> 0:29:34,06 author of PgQ. 571 0:29:34,44 --> 0:29:36,24 But second, how come it's created? 572 0:29:38,18 --> 0:29:41,08 Apparently also interesting part of the story, Alexander Kukushkin, 573 0:29:41,12 --> 0:29:46,98 maintainer of Patroni, in January, I think in PGD Prague, I might 574 0:29:46,98 --> 0:29:53,58 be mistaken, presented a talk about PgQ because PgQ is an excellent 575 0:29:53,62 --> 0:29:54,12 thing. 576 0:29:54,24 --> 0:29:55,62 Let's revive it, right? 577 0:29:55,84 --> 0:29:57,48 Great, goals align here. 578 0:29:57,52 --> 0:29:59,44 I also want to revive it. 579 0:29:59,86 --> 0:30:4,14 And I was like, you know, I told him, but unfortunately, you 580 0:30:4,14 --> 0:30:6,22 cannot install it on RDS, right? 581 0:30:6,42 --> 0:30:8,04 I was like slightly provocative. 582 0:30:8,56 --> 0:30:11,62 He said, no, my slides have a recipe. 583 0:30:13,46 --> 0:30:14,64 How come you learned it? 584 0:30:14,64 --> 0:30:17,66 He said, it's Everything is written in commit messages. 585 0:30:19,3 --> 0:30:22,4 The problem with PgQ always has been a lack of documentation. 586 0:30:23,04 --> 0:30:24,34 Everyone can tell it. 587 0:30:24,34 --> 0:30:28,04 I hear it for 15 years and can say it myself. 588 0:30:28,5 --> 0:30:32,52 So yes, indeed, there is a commit in 2019 or so to make it work 589 0:30:32,52 --> 0:30:37,7 on RDS and others, let's have this support without create extension, 590 0:30:37,8 --> 0:30:39,18 just a single file. 591 0:30:40,08 --> 0:30:42,08 Or maybe multiple files, doesn't matter. 592 0:30:42,18 --> 0:30:45,06 PL only, PL/pgSQL only mode. 593 0:30:45,06 --> 0:30:48,66 It's called PL mode internally in PgQ. 594 0:30:48,82 --> 0:30:52,24 So apparently it was already ready, but it was not. 595 0:30:52,5 --> 0:30:56,96 I said, okay, but I actually, meanwhile, I talked to my cloud 596 0:30:56,96 --> 0:30:57,26 code. 597 0:30:57,26 --> 0:30:59,56 I say, okay, but I cannot find that file. 598 0:31:0,02 --> 0:31:1,12 How, Like, where did you get it? 599 0:31:1,12 --> 0:31:2,82 I don't see it in the repository. 600 0:31:3,16 --> 0:31:5,46 It says, okay, you just need to run make. 601 0:31:7,94 --> 0:31:8,44 Michael: What? 602 0:31:9,4 --> 0:31:12,8 Nikolay: You need to run make to get the SQL file you can load 603 0:31:12,8 --> 0:31:13,94 to your RDS. 604 0:31:15,3 --> 0:31:17,46 We live in different worlds here a little bit. 605 0:31:18,74 --> 0:31:24,4 I told Kukushkin, I think Gen Z won't understand us with this 606 0:31:24,4 --> 0:31:24,9 make. 607 0:31:26,96 --> 0:31:28,16 I don't know, it's interesting. 608 0:31:28,58 --> 0:31:29,94 For me it's like mind-blowing. 609 0:31:30,38 --> 0:31:36,1 Everything exists, but it's buried under these walls. 610 0:31:36,6 --> 0:31:38,56 For some people, it's not a wall. 611 0:31:38,56 --> 0:31:41,4 You git clone, CD, make. 612 0:31:42,7 --> 0:31:45,82 psql, import file, everything works. 613 0:31:46,5 --> 0:31:49,74 But imagine for some people it's a big barrier, right? 614 0:31:50,38 --> 0:31:53,2 Michael: And it would be weird not seeing it in the repo. 615 0:31:53,6 --> 0:31:54,1 Yeah. 616 0:31:54,34 --> 0:31:56,62 Nikolay: Well, you need to read commit messages. 617 0:31:56,82 --> 0:32:1,94 And that's great that Alexander has a talk promoting that this 618 0:32:1,94 --> 0:32:2,64 is possible. 619 0:32:3,38 --> 0:32:5,08 But it inspired me even more. 620 0:32:5,08 --> 0:32:7,54 Let's do it and add more and more in documentation. 621 0:32:7,8 --> 0:32:13,36 So my tool is just basically, it has even a sub-module, and then 622 0:32:13,36 --> 0:32:15,54 it compiles and presents this as our SQL. 623 0:32:15,54 --> 0:32:18,42 But of course, I started adding more things around. 624 0:32:19,02 --> 0:32:19,74 So this is it. 625 0:32:19,74 --> 0:32:24,06 This is the idea of PgQue, which is universal edition, 626 0:32:24,06 --> 0:32:25,58 so it can be used anywhere. 627 0:32:26,6 --> 0:32:31,48 And I'm releasing this week second version with a lot of stuff, 628 0:32:31,48 --> 0:32:32,86 actually, a lot of stuff. 629 0:32:33,26 --> 0:32:34,96 Somehow, some requests and so on. 630 0:32:34,96 --> 0:32:36,9 First of all, I realized I need libraries. 631 0:32:37,2 --> 0:32:42,74 So we now have TypeScript, Go, and Python libraries. 632 0:32:43,36 --> 0:32:46,42 And 1 person promised to bring Ruby library as well. 633 0:32:46,78 --> 0:32:47,46 Michael: Oh, nice. 634 0:32:47,5 --> 0:32:47,72 Nikolay: Yeah. 635 0:32:47,72 --> 0:32:50,4 And I also already have 2 external contributors. 636 0:32:50,5 --> 0:32:52,24 So like I have some life. 637 0:32:52,66 --> 0:32:56,58 I achieved thousand stars in 4 days or so. 638 0:32:56,58 --> 0:32:57,24 It was good. 639 0:32:57,24 --> 0:33:0,74 I mean, I, it felt great, but I also learned it's not a queue. 640 0:33:0,74 --> 0:33:5,38 It's like a log because it's more like Kafka than RabbitMQ or 641 0:33:5,38 --> 0:33:5,88 ActiveMQ. 642 0:33:6,18 --> 0:33:8,18 And I agree after a thorough understanding. 643 0:33:8,3 --> 0:33:12,24 That's why in version 2, I'm bringing another concept from Sky 644 0:33:12,24 --> 0:33:13,1 Tools originally. 645 0:33:13,38 --> 0:33:17,64 It's called the cooperative consumers or sub-consumers. 646 0:33:18,58 --> 0:33:21,48 So logically it's a single consumer, but there is a group of 647 0:33:21,48 --> 0:33:24,22 consumers which distribute load between them. 648 0:33:24,96 --> 0:33:28,4 This is needed, for example, when you have, imagine you have 649 0:33:28,74 --> 0:33:31,06 a queue of jobs like process on video. 650 0:33:31,88 --> 0:33:34,94 Some videos are super small, Some videos are super large. 651 0:33:34,96 --> 0:33:39,56 In case of PgQs, if you just see your position and read it by 652 0:33:39,56 --> 0:33:44,06 1, if you have some people, by the way, looking at PgQ think, 653 0:33:44,06 --> 0:33:47,86 okay, if I'm going, if I'm adding more consumers, I I'm increasing 654 0:33:47,86 --> 0:33:48,36 capacity. 655 0:33:48,52 --> 0:33:54,02 No throughput won't increase because every consumer in PgQ reads 656 0:33:54,02 --> 0:33:54,52 everything. 657 0:33:57,66 --> 0:33:58,64 Everyone, everything. 658 0:33:58,74 --> 0:34:1,72 It's, you need different queues to distribute load. 659 0:34:1,72 --> 0:34:3,46 It's like topics in Kafka. 660 0:34:4,12 --> 0:34:6,52 Because it's actually not a queue, it's a log. 661 0:34:7,76 --> 0:34:10,08 Michael: Yeah, do you support multiple queues? 662 0:34:10,08 --> 0:34:11,54 Or I guess it just involves 663 0:34:11,6 --> 0:34:12,4 Nikolay: creating multiple tables. 664 0:34:12,4 --> 0:34:15,04 Yeah, you can create as many queues as you want. 665 0:34:15,04 --> 0:34:17,6 Every time they will be partitioned and all the mechanics will 666 0:34:17,6 --> 0:34:18,1 work. 667 0:34:19,28 --> 0:34:24,8 But with concept of sub-consumers, which is not my idea, it's 668 0:34:24,8 --> 0:34:29,34 original idea, but I couldn't import it because it's a separate 669 0:34:29,34 --> 0:34:32,86 repo, PgQ Co-op, and it doesn't have a license. 670 0:34:33,7 --> 0:34:36,88 There are only 2 issues, asking what license is it, because we 671 0:34:36,88 --> 0:34:39,44 want to package it as Debian package or something. 672 0:34:39,44 --> 0:34:43,0 And I couldn't take it, but I stole idea, of course, and re-implemented 673 0:34:43,22 --> 0:34:44,52 it with my own code. 674 0:34:45,06 --> 0:34:47,42 But the idea is the same, the feature is right now experimental, 675 0:34:47,8 --> 0:34:49,3 I need to play with it. 676 0:34:49,34 --> 0:34:52,38 We started already benchmarking it and so on, it looks good. 677 0:34:52,96 --> 0:34:55,52 So this, I think, should be natively supported. 678 0:34:57,72 --> 0:35:1,84 So there is a lot of stuff, but The key idea is that now it's 679 0:35:1,84 --> 0:35:3,84 like a single file, you can load it. 680 0:35:3,84 --> 0:35:7,8 I also made it a pg_tle extension for those who want to properly 681 0:35:7,8 --> 0:35:8,82 track as extension. 682 0:35:10,84 --> 0:35:13,9 And you just inject it, you configure pg_cron. 683 0:35:15,52 --> 0:35:20,42 I actually thought about, and Hannu Krosing, who is now at GCP 684 0:35:21,2 --> 0:35:25,32 and X Skype, we discussed it on LinkedIn and I implemented it. 685 0:35:25,32 --> 0:35:28,28 So pg_cron cannot tick more often than 1 second. 686 0:35:28,78 --> 0:35:30,52 Michael: I was going to ask you about this. 687 0:35:30,9 --> 0:35:31,4 Yeah. 688 0:35:31,4 --> 0:35:34,54 But what reads me says the default is 100 milliseconds. 689 0:35:35,22 --> 0:35:36,96 Nikolay: This is new for version 2, yes. 690 0:35:36,96 --> 0:35:38,24 I just made it yesterday. 691 0:35:39,32 --> 0:35:43,48 So I was thinking, first of all, people think about latencies, 692 0:35:43,62 --> 0:35:45,36 I recognize 3 latencies. 693 0:35:45,36 --> 0:35:50,28 First is producer query latency, how fast it takes to insert 694 0:35:50,28 --> 0:35:53,2 1 event or a batch of events, like 100 or 1, 000. 695 0:35:53,56 --> 0:35:57,74 Second is the consumer query latency, how fast it is to take 696 0:35:57,74 --> 0:35:59,94 next batch, fetch it, right? 697 0:36:0,66 --> 0:36:4,58 And we measure them and we see how badly the second 1 degrades 698 0:36:4,68 --> 0:36:9,44 for all alternative modern, I cannot name myself modern tool, 699 0:36:9,44 --> 0:36:10,46 I'm very old. 700 0:36:10,52 --> 0:36:12,54 This engine is 20 years old. 701 0:36:12,6 --> 0:36:13,64 So they all degrade. 702 0:36:13,7 --> 0:36:15,64 This here, we don't degrade almost. 703 0:36:16,22 --> 0:36:20,84 We slightly degrade from 100 microseconds, so we go slightly 704 0:36:20,86 --> 0:36:25,46 above 1 millisecond, while they from 1 millisecond go to 1 second, 705 0:36:25,86 --> 0:36:27,34 sometimes 5 I saw. 706 0:36:27,8 --> 0:36:29,78 And degradation, we can discuss separately. 707 0:36:30,04 --> 0:36:33,08 Our degradation also solvable, but not solved into version 2 708 0:36:33,08 --> 0:36:33,58 yet. 709 0:36:33,82 --> 0:36:38,04 When I say version 2, it's 0.2 because it's early, but it's super 710 0:36:38,04 --> 0:36:39,44 solid engine, we know. 711 0:36:39,52 --> 0:36:43,52 And there is third latency, end to end event delivery latency. 712 0:36:45,06 --> 0:36:49,22 If you tick, if you shift your vision horizon only every second, 713 0:36:49,22 --> 0:36:51,18 it can be up to second at least. 714 0:36:51,18 --> 0:36:56,6 Also, consumer itself might wake up not immediately like you 715 0:36:56,6 --> 0:36:58,6 need listen notify or something. 716 0:36:58,78 --> 0:37:0,46 It's partially supported right now. 717 0:37:0,8599 --> 0:37:3,62 But you need polling or something, You might lose some milliseconds 718 0:37:3,74 --> 0:37:9,1 there as well And I was thinking this decision to have Once per 719 0:37:9,1 --> 0:37:12,28 second was made 10 to 20 years ago. 720 0:37:12,66 --> 0:37:13,88 We have better hardware. 721 0:37:13,9 --> 0:37:18,26 So Let's have 10 per second by default. 722 0:37:18,68 --> 0:37:19,1802 And how? 723 0:37:19,1802 --> 0:37:21,34 Okay, pg_cron, which we rely on. 724 0:37:21,34 --> 0:37:23,18 By the way, pg_cron is optional. 725 0:37:23,32 --> 0:37:25,76 You can put it to cron or something. 726 0:37:25,76 --> 0:37:28,38 You just need select ticker, PgQ ticker. 727 0:37:28,56 --> 0:37:29,68 That's it. 728 0:37:29,68 --> 0:37:29,936 Ah, okay. 729 0:37:29,936 --> 0:37:30,32 Tick, tick, yeah. 730 0:37:30,32 --> 0:37:34,24 Every second by default was, it was originally from SkyTools. 731 0:37:34,34 --> 0:37:36,1 It was in the first version. 732 0:37:36,1 --> 0:37:38,88 In second version, I made it 10 times per second. 733 0:37:39,8 --> 0:37:40,82 And it's simple. 734 0:37:41,14 --> 0:37:45,86 In pg_cron, there is a storage procedure who has a loop with 735 0:37:45,86 --> 0:37:49,24 commit, because we need the separate transactions actually to 736 0:37:49,24 --> 0:37:50,22 shift the snapshot. 737 0:37:51,9 --> 0:37:53,24 So it's not every... 738 0:37:53,46 --> 0:37:57,32 This is the same misconception as for backslash watching psql. 739 0:37:57,54 --> 0:37:59,36 It's not every hundred milliseconds. 740 0:37:59,5 --> 0:38:3,06 If it's hundred milliseconds wait time, The operation itself 741 0:38:4,02 --> 0:38:5,74 has non-zero duration, right? 742 0:38:5,74 --> 0:38:8,08 So roughly it should be fine. 743 0:38:8,42 --> 0:38:14,08 And it ticks 10 times per second, but not exactly, it's slightly 744 0:38:14,3 --> 0:38:14,8 shifting. 745 0:38:15,08 --> 0:38:17,42 But updating 1 row, it's super fast. 746 0:38:18,14 --> 0:38:18,64 Yeah. 747 0:38:18,9 --> 0:38:22,78 And it will be even better when I implement bloat mitigation 748 0:38:22,9 --> 0:38:28,28 for system tables, because this is why we go from 100 microseconds 749 0:38:28,52 --> 0:38:33,16 to 1 millisecond or so under blocked xmin horizon, because we 750 0:38:33,16 --> 0:38:35,14 accumulate dead tuples in these metatables. 751 0:38:35,74 --> 0:38:36,92 Ticker and subscription. 752 0:38:38,3 --> 0:38:41,64 Michael: Are there any other downsides to increasing the ticker 753 0:38:42,04 --> 0:38:44,0 speed or decreasing the ticker frequency? 754 0:38:45,84 --> 0:38:49,84 Nikolay: So I did preliminary benchmarks yesterday and the important 755 0:38:49,84 --> 0:38:54,02 thing to understand, if when it's ticking, if nothing to do, 756 0:38:54,58 --> 0:38:56,04 nothing to read, to read. 757 0:38:56,04 --> 0:38:57,34 So it doesn't show. 758 0:38:57,88 --> 0:39:2,36 And it means it's great if load is low, it won't produce new 759 0:39:2,36 --> 0:39:2,86 writes. 760 0:39:3,08 --> 0:39:7,76 But if, imagine every hundred milliseconds you have new events. 761 0:39:8,36 --> 0:39:13,6 In this case, every time ticking, it's updating this row, which 762 0:39:13,82 --> 0:39:14,42 has metadata. 763 0:39:15,04 --> 0:39:19,9 And we estimated it for ticking every second, it's 24 megabytes 764 0:39:19,9 --> 0:39:23,6 per second of fall, it's very rough because it doesn't take into 765 0:39:23,6 --> 0:39:25,1 account full page rights. 766 0:39:25,64 --> 0:39:29,44 So it's very rough just from ticking overhead from ticking 767 0:39:29,44 --> 0:39:32,9 Michael: 24 megabytes per second from a single tick per second. 768 0:39:32,9 --> 0:39:33,28 Nikolay: Oh, per 769 0:39:33,28 --> 0:39:34,28 Michael: day, that makes more sense. 770 0:39:34,28 --> 0:39:34,78 Yeah, that makes 771 0:39:34,78 --> 0:39:35,46 Nikolay: more sense. 772 0:39:36,22 --> 0:39:39,26 240 megabytes per day, sorry, per day. 773 0:39:39,64 --> 0:39:44,2 If you have right now default in version 2 to 10 times per second, 774 0:39:45,16 --> 0:39:46,06 Which is acceptable. 775 0:39:46,3 --> 0:39:48,84 I mean, this means you have load already, right? 776 0:39:48,84 --> 0:39:53,24 So doing this database is loaded if you if every hundred milliseconds 777 0:39:53,36 --> 0:39:56,18 there is ticking happens. 778 0:39:56,52 --> 0:39:56,82 Michael: If it 779 0:39:56,82 --> 0:39:58,6 Nikolay: doesn't happen again, no writes. 780 0:39:59,06 --> 0:40:3,04 Michael: So it sounds like it scales fairly linearly then, like 781 0:40:3,04 --> 0:40:3,28 10 782 0:40:3,28 --> 0:40:3,58 Nikolay: times more. 783 0:40:3,58 --> 0:40:9,34 This is overhead from updating this meta table with row where 784 0:40:9,34 --> 0:40:10,12 we are. 785 0:40:10,24 --> 0:40:11,08 That's it. 786 0:40:11,92 --> 0:40:16,24 So of course, if you inject a lot of data, there's mechanics 787 0:40:16,3 --> 0:40:19,12 there, interesting, might happen. 788 0:40:19,12 --> 0:40:21,96 And again, if you have long-running transactions, like xmin horizon 789 0:40:22,12 --> 0:40:25,74 blocked, in this case, dead tuples will accumulate, unfortunately, 790 0:40:25,76 --> 0:40:30,38 in system, in metadata tables, which I'm going to solve also 791 0:40:30,38 --> 0:40:32,32 with partitioning and truncate. 792 0:40:33,42 --> 0:40:36,96 Michael: So something I don't quite understand is what, when 793 0:40:36,96 --> 0:40:38,04 doesn't this make sense? 794 0:40:38,04 --> 0:40:40,9 You mentioned it's not really, it's a queue, it can be used for 795 0:40:40,9 --> 0:40:43,66 queue-like workloads, but maybe sometimes it doesn't make sense. 796 0:40:43,66 --> 0:40:45,16 Should we go into that a little bit? 797 0:40:45,16 --> 0:40:46,42 Nikolay: This is a great question. 798 0:40:46,58 --> 0:40:47,86 I'm deep in database. 799 0:40:48,12 --> 0:40:53,9 So I would like to hear from back-end engineers and people who 800 0:40:53,9 --> 0:40:56,58 build systems, what's lacking here. 801 0:40:56,58 --> 0:41:0,32 1 thing I can understand is lack of, for example, priorities 802 0:41:0,8 --> 0:41:1,56 for events. 803 0:41:1,56 --> 0:41:2,06 Yeah. 804 0:41:2,26 --> 0:41:2,52 Right. 805 0:41:2,52 --> 0:41:4,8 So, because this is very linear. 806 0:41:5,28 --> 0:41:9,66 With cooperative consumers, I think the problem of big task blocks, 807 0:41:9,66 --> 0:41:12,18 small tasks will be basically resolved. 808 0:41:13,26 --> 0:41:16,02 But priority, I don't know, this is definitely not a pattern 809 0:41:16,02 --> 0:41:16,52 here. 810 0:41:16,96 --> 0:41:22,0 Also, if you need the almost immediate delivery, pgmq or River 811 0:41:22,9 --> 0:41:26,42 might be better because they deliver faster, right? 812 0:41:27,16 --> 0:41:32,26 We have end-to-end, I mean, end-to-end latency for job processing. 813 0:41:33,12 --> 0:41:38,38 What we have here is worse end-to-end latency controlled by this 814 0:41:38,38 --> 0:41:39,18 ticking frequency. 815 0:41:39,38 --> 0:41:42,98 Obviously, I actually wrote a document about frequency tuning 816 0:41:43,08 --> 0:41:44,04 with some considerations. 817 0:41:45,34 --> 0:41:49,34 There is a docs folder directory, There is a special document 818 0:41:49,34 --> 0:41:50,04 right now. 819 0:41:50,28 --> 0:41:51,96 So the problem will be... 820 0:41:53,12 --> 0:41:56,98 So if you want almost immediate delivery, like for example, like 821 0:41:56,98 --> 0:42:0,18 it's I don't know like chat or something Maybe you should choose 822 0:42:0,18 --> 0:42:3,9 pgmq or River, but you need to fight xmin horizon blockers very 823 0:42:4,34 --> 0:42:5,76 actively, right? 824 0:42:6,28 --> 0:42:11,04 And install our monitoring and connect it to our platform and 825 0:42:11,04 --> 0:42:14,78 check the health and so on and fight those blockers actively. 826 0:42:15,42 --> 0:42:21,22 What I can say is that update-delete, SKIP LOCKED systems, they 827 0:42:21,22 --> 0:42:26,14 have better end-to-end delivery, but they degrade badly under 828 0:42:26,4 --> 0:42:27,28 xmin horizon-blocked. 829 0:42:27,98 --> 0:42:32,22 We have worse initially, but it's predictable, reliable, right? 830 0:42:32,44 --> 0:42:35,74 And in the case, if you have like background, for example, we 831 0:42:35,74 --> 0:42:39,96 discussed how to convert integer 4 primary key to integer 8 primary 832 0:42:39,96 --> 0:42:40,08 key. 833 0:42:40,08 --> 0:42:43,54 You need, you have, for example, 1000000000 rows, you need to 834 0:42:43,78 --> 0:42:44,62 change them. 835 0:42:45,14 --> 0:42:48,74 And you chose, for example, the approach I call the new column 836 0:42:48,74 --> 0:42:49,22 approach. 837 0:42:49,22 --> 0:42:52,54 You create a new column with integer 8, and then you need to 838 0:42:52,54 --> 0:42:56,22 install Trigger for future rows already, and then you need to 839 0:42:56,24 --> 0:42:59,44 process your big backlog, 1000000000 tables. 840 0:42:59,44 --> 0:43:0,6 You do it in batches. 841 0:43:1,0 --> 0:43:2,64 How to schedule this processing? 842 0:43:2,86 --> 0:43:7,86 This is exactly any background processing where you like 50 millisecond 843 0:43:8,1 --> 0:43:10,7 and that is fine, this is it. 844 0:43:10,76 --> 0:43:12,94 It's good in working batches. 845 0:43:13,78 --> 0:43:20,06 If you need 1 millisecond, okay, choose new tools, but fight 846 0:43:20,06 --> 0:43:21,3 xmin horizon blockers. 847 0:43:22,44 --> 0:43:22,84 Michael: Yeah. 848 0:43:22,84 --> 0:43:26,58 I mean, my understanding of when you use queues is it's for asynchronous 849 0:43:26,78 --> 0:43:27,18 stuff. 850 0:43:27,18 --> 0:43:30,68 So I can't, I'm struggling to imagine something that can't cope 851 0:43:30,68 --> 0:43:33,66 with 50 milliseconds of overhead on something asynchronous. 852 0:43:34,16 --> 0:43:37,36 Even like a password reset, if it comes through a second later, 853 0:43:37,36 --> 0:43:37,86 it's 854 0:43:38,68 --> 0:43:39,0 Nikolay: fine. 855 0:43:39,0 --> 0:43:39,6 I agree. 856 0:43:39,6 --> 0:43:43,78 And in this case, maybe you should consider doing it outside 857 0:43:43,78 --> 0:43:49,68 of Postgres with different systems like Redpanda or something. 858 0:43:49,96 --> 0:43:54,12 I can imagine some systems where you need very responsive behavior, 859 0:43:54,12 --> 0:44:0,18 but you need to learn how MVCC works and what dangers await you 860 0:44:0,34 --> 0:44:2,02 if you build like that. 861 0:44:2,08 --> 0:44:5,46 You will have good latency in the beginning, but suddenly then 862 0:44:6,28 --> 0:44:9,12 some something blocks you and then it degrades quickly. 863 0:44:9,32 --> 0:44:14,22 I wanted to mention that queue in database is great because it's 864 0:44:14,22 --> 0:44:14,72 ACID. 865 0:44:15,58 --> 0:44:17,72 Nothing will be lost, right? 866 0:44:18,42 --> 0:44:21,74 It's like it's replicated, it goes to backups, nothing is lost 867 0:44:21,74 --> 0:44:22,7 and it's isolation. 868 0:44:23,46 --> 0:44:29,12 All 4 properties are very follow followed, right? 869 0:44:29,64 --> 0:44:34,34 If you go and use different system and you need to think about 870 0:44:34,7 --> 0:44:35,88 consistency, right? 871 0:44:35,98 --> 0:44:38,66 So you need to think about if you have something in database 872 0:44:38,86 --> 0:44:43,38 you already wrote here but didn't delete that or it can be inconsistent. 873 0:44:44,38 --> 0:44:51,34 These days GitHub works very poorly And since I posted this project 874 0:44:51,34 --> 0:44:53,98 on GitHub, I worked, I'm usually on GitLab. 875 0:44:54,44 --> 0:44:57,56 And I know GitLab issues as well, because they are our clients 876 0:44:57,56 --> 0:44:59,56 many years, but they are great. 877 0:44:59,68 --> 0:45:2,92 On GitHub lately, I like, wow, it's interesting. 878 0:45:2,98 --> 0:45:7,08 You already merged pull request, but it takes some seconds for 879 0:45:7,08 --> 0:45:8,46 counter to propagate. 880 0:45:8,66 --> 0:45:12,66 It's also for list to, for this pull request to disappear. 881 0:45:12,84 --> 0:45:17,06 So they have big lags, synchronous processing, right? 882 0:45:17,54 --> 0:45:20,66 And, But legs are fine, eventual consistency, right? 883 0:45:20,66 --> 0:45:22,44 But data loss is not fine. 884 0:45:22,44 --> 0:45:27,6 So if you have data, I would say, if you need predictable performance, 885 0:45:27,9 --> 0:45:32,92 reliable approach, good throughput, not suffering from degradation 886 0:45:33,08 --> 0:45:35,78 when xmin horizon is blocked, PgQ is great. 887 0:45:36,18 --> 0:45:39,28 When you need much faster delivery and you want to stay inside 888 0:45:39,28 --> 0:45:43,68 database, ACID and so on, choose different system for Postgres, 889 0:45:43,7 --> 0:45:46,3 but fight xmin horizon blockers. 890 0:45:47,62 --> 0:45:52,68 And if you want better throughput, go with Redpanda, Kafka or 891 0:45:52,68 --> 0:45:57,44 anything if you can afford supporting or paying for managed version. 892 0:45:57,72 --> 0:46:2,18 But in this case, do look at Transactional outbox pattern. 893 0:46:3,54 --> 0:46:3,9 Yeah. 894 0:46:3,9 --> 0:46:7,76 Because this is from microservices theory, so to speak. 895 0:46:7,9 --> 0:46:12,18 There is a pattern to organize data delivery from database to 896 0:46:12,18 --> 0:46:13,88 queue properly and all statuses. 897 0:46:14,24 --> 0:46:18,66 This is how you should do it because otherwise data loss is eventually 898 0:46:18,84 --> 0:46:19,34 inevitable. 899 0:46:21,54 --> 0:46:25,46 Yeah, this is how to navigate solutions, advice from me. 900 0:46:26,06 --> 0:46:26,56 Yeah. 901 0:46:27,74 --> 0:46:30,04 Michael: What, anything else you wanted to make sure we covered 902 0:46:30,04 --> 0:46:30,98 before we wrap up? 903 0:46:31,36 --> 0:46:34,96 Nikolay: Well, I'm just excited that, among others, as you said, 904 0:46:34,96 --> 0:46:39,06 Christophe Pettus and also, as I said, Kukushkin, we, like, I guess 905 0:46:39,06 --> 0:46:43,3 we teamed up a little bit, not like somehow in distributed fashion, 906 0:46:44,34 --> 0:46:47,94 to shed a new light at PgQ, because it's a great piece of software. 907 0:46:48,16 --> 0:46:53,0 It solved problems before people encountered them, but somehow 908 0:46:53,0 --> 0:46:54,36 it got lost with knowledge. 909 0:46:54,44 --> 0:46:59,24 I hope more people at least keep in mind what's possible and 910 0:46:59,24 --> 0:47:0,78 consider it building systems. 911 0:47:1,38 --> 0:47:4,34 And telling their AI to consider because maybe they just, the 912 0:47:4,34 --> 0:47:7,98 AI will look at it, do some benchmarks, research and make decision. 913 0:47:9,06 --> 0:47:9,56 Right? 914 0:47:10,68 --> 0:47:11,46 That's it. 915 0:47:11,46 --> 0:47:12,54 Michael: Maybe, yeah. 916 0:47:12,62 --> 0:47:13,76 Alright, last 1. 917 0:47:13,78 --> 0:47:16,22 Well, thanks so much, Nikolay, and catch you soon. 918 0:47:16,5 --> 0:47:17,46 Nikolay: Thank you for listening. 919 0:47:17,72 --> 0:47:18,78 See you soon, bye.