1 00:00:00,000 --> 00:00:06,500 [music] 2 00:00:06,500 --> 00:00:13,000 [music] 3 00:00:13,360 --> 00:00:18,840 Nik: Hello, hello. This is Postgres.FM. I'm Nik, as usual, PostgresAI. 4 00:00:18,860 --> 00:00:25,640 And as not usual, today with me is Kirk, KiraSoft. Hi, Kirk. 5 00:00:26,430 --> 00:00:27,430 Kirk: Hey, Nik. 6 00:00:27,650 --> 00:00:31,370 Nik: So Michael couldn't join today, and I asked Kirk to join me, 7 00:00:31,370 --> 00:00:35,450 because the topic is interesting not only for users, but also, 8 00:00:36,610 --> 00:00:40,830 I think there is potential to improve something in Postgres, right? 9 00:00:41,090 --> 00:00:45,790 And as some our listeners know, we have hacking 10 00:00:45,830 --> 00:00:50,510 sessions online with Andrey and Kirk, which lasts, I think, three years, right, 11 00:00:50,950 --> 00:00:53,110 - on YouTube. - Yep. 12 00:00:53,270 --> 00:00:58,250 Nik: So, and, and, and you recently visited us already on, on Postgres.FM, 13 00:00:58,920 --> 00:01:03,020 to discuss Google Summer of Code project, right? 14 00:01:03,600 --> 00:01:09,200 So the topic is let's form it in the form of question. 15 00:01:09,240 --> 00:01:14,520 If you performed dump restore or just restore from a backup, is it enough? 16 00:01:15,440 --> 00:01:17,520 - Yeah. - Is it enough to continue 17 00:01:18,240 --> 00:01:21,940 working with this data which is just loaded to your Postgres? 18 00:01:21,940 --> 00:01:25,600 For me, obvious answer is no, and actually, you know, this podcast, 19 00:01:25,600 --> 00:01:29,600 we touched it multiple times in the past. 20 00:01:29,620 --> 00:01:35,410 But today, I just, like, I have a, a lot of interesting things to discuss because, 21 00:01:35,410 --> 00:01:41,170 with our new AI tooling we built at PostgresAI, it's called DBLab Forge. 22 00:01:41,250 --> 00:01:46,230 We we explored and found so many interesting things, so many nuances, 23 00:01:46,450 --> 00:01:51,370 which I thought, would be interesting for wider audience to discuss today. 24 00:01:51,710 --> 00:01:54,770 How do you find this topic? Like, what do you think about it? 25 00:01:54,770 --> 00:01:59,652 Kirk: Well, Michael sent me a text message and said I have to say it sounds boring. 26 00:01:59,690 --> 00:02:02,110 - No, but it's... [laughs] - Yeah. 27 00:02:02,110 --> 00:02:04,970 - No, this is actually a great topic. - It's a... I agree with you, actually. 28 00:02:04,970 --> 00:02:06,810 It's a boring topic. Yeah, it should be boring. 29 00:02:06,810 --> 00:02:10,470 - No. No, this- - But it's entertaining sometimes when you 30 00:02:10,470 --> 00:02:15,450 just loaded your data and have very big surprises with performance. 31 00:02:15,770 --> 00:02:18,470 There you go. This is what makes it interesting. 32 00:02:18,470 --> 00:02:23,410 And you have the data to back some things up, like out of 28 sites, 33 00:02:23,410 --> 00:02:29,050 companies you've reviewed that give advice of what to do after, only eight of them 34 00:02:29,090 --> 00:02:34,350 cover what, that touched on this out of 28. Three do it really well. 35 00:02:34,350 --> 00:02:39,270 So this is gonna be good stuff for everyone to see. And the question overall, 36 00:02:39,270 --> 00:02:44,780 the restore finished, is your database ready, maybe isn't the right question. 37 00:02:44,780 --> 00:02:48,380 The real question is your database ready to be thrown into production 38 00:02:48,380 --> 00:02:50,410 - mode? And the answer there is no. - No. 39 00:02:50,410 --> 00:02:53,190 All your data's restored. Your indexes are restored. 40 00:02:53,190 --> 00:02:56,810 - Well- - If you throw it into production, crazy. 41 00:02:56,810 --> 00:03:00,110 Nik: Yeah, we start arguing right away. This is why I, I 42 00:03:00,430 --> 00:03:04,200 really, like, looked forward to, to do it with you. 43 00:03:04,200 --> 00:03:09,740 So my question wasn't, is your database ready? I said, "Is it enough?" 44 00:03:10,420 --> 00:03:11,440 - Right. - It's much- 45 00:03:11,440 --> 00:03:13,700 - And it depends on your goal. - Yeah. It depends on your goal. 46 00:03:13,700 --> 00:03:16,390 If, if all you are doing is validating your backup, 47 00:03:16,390 --> 00:03:17,800 - it's probably enough. - No. 48 00:03:17,800 --> 00:03:19,480 If you're making sure you have enough space- 49 00:03:19,480 --> 00:03:21,360 No, this is another topic. This is another topic. 50 00:03:21,360 --> 00:03:22,640 Oh, no, you're right. 51 00:03:22,640 --> 00:03:24,790 - Look at that. - For validation of backup, 52 00:03:24,790 --> 00:03:28,380 it's also not enough, and let's talk about it another time, how- 53 00:03:28,380 --> 00:03:30,700 - Right - properly validate dumps. 54 00:03:31,140 --> 00:03:35,979 And another thing I must admit, dumps are logical backups. 55 00:03:35,979 --> 00:03:41,300 I will always argue with statement, dumps are backups. Dumps are not backups. 56 00:03:41,300 --> 00:03:44,020 They are logical backups. We had a separate episode. 57 00:03:44,020 --> 00:03:47,899 It is just maybe, like, a preference of terminology. It, it... 58 00:03:47,899 --> 00:03:51,599 Of course, some people say, like, have their own opinions. 59 00:03:51,599 --> 00:03:57,459 But if we return to the central topic, like performance, my, my answer obvious, 60 00:03:57,459 --> 00:04:00,830 - like, no, it's not enough, right? - Yeah. 61 00:04:00,830 --> 00:04:04,430 Nik: For, for OLTP, right? Right? 62 00:04:04,430 --> 00:04:08,790 Kirk: Well, actually, it doesn't even matter for what. Let's talk about, 63 00:04:08,790 --> 00:04:13,190 we can talk about why. And also, just for the audience getting used to me, 64 00:04:13,190 --> 00:04:17,230 my motto is simple: be wrong out loud, you'll learn faster. 65 00:04:17,230 --> 00:04:18,689 Nik: [laughs] 66 00:04:18,689 --> 00:04:21,030 - So I love getting corrected, okay? - Right. 67 00:04:21,030 --> 00:04:24,930 Kirk: I... You know, and I just said something, and I got corrected, and boom, 68 00:04:24,930 --> 00:04:29,690 right on. So, so let's turn this topic around a little bit and say, well, 69 00:04:29,690 --> 00:04:34,050 what could be missing? All my data's there, all my indexes are there, 70 00:04:34,090 --> 00:04:38,570 and Postgres recently added that they'll bring over statistics if you tell it 71 00:04:38,570 --> 00:04:39,610 - to. - It's, 72 00:04:39,610 --> 00:04:42,610 - That's fabulous - it happened in Postgres 18, right? Yeah. 73 00:04:42,610 --> 00:04:44,210 Kirk: Right. That's fabulous. 74 00:04:44,210 --> 00:04:46,070 - So that must be everything. - Right. 75 00:04:46,070 --> 00:04:48,850 And then you look under the hood a little bit and you find- 76 00:04:48,850 --> 00:04:52,010 - Yeah - no, you need things like the visibility 77 00:04:52,010 --> 00:04:55,290 map. Oh, but when does that get written? Oh, when you do- 78 00:04:55,290 --> 00:04:57,610 - Yeah - your first query on that table. 79 00:04:57,610 --> 00:04:59,590 - Oh, you mean I can do a read- - Ah 80 00:04:59,590 --> 00:05:02,050 - and- - Hold on. Let, let's untangle this. You, 81 00:05:02,050 --> 00:05:06,370 you started to... Like, okay, you, you are very right except one thing. 82 00:05:06,370 --> 00:05:10,590 Visibility map is not written at first read until Postgres 19. 83 00:05:10,630 --> 00:05:14,290 Let's start untangling this and like, 84 00:05:14,290 --> 00:05:18,870 presenting this maybe in a more structured way because it's really complex topic. 85 00:05:18,870 --> 00:05:23,719 I chose a simple topic for our first podcast together, Like, 86 00:05:23,719 --> 00:05:27,619 when I launched our DBLab Forge for a couple of nights and, and I, 87 00:05:27,619 --> 00:05:32,419 I'm amazed about how many details it brought, you know? 88 00:05:32,559 --> 00:05:38,879 So let's start maybe thinking about what we are missing 89 00:05:39,139 --> 00:05:43,659 after a simple restore. First of all, maybe, like, 90 00:05:43,659 --> 00:05:48,559 what dump already includes by default, forgetting about Postgres 18 edition. 91 00:05:48,579 --> 00:05:52,579 Dump includes some set commands usually, right? 92 00:05:52,659 --> 00:05:57,159 Then DDL to create tables and, and everything, but not indexes. 93 00:05:57,159 --> 00:06:01,759 Indexes, is not... It's not wise to create indexes in the beginning 94 00:06:01,759 --> 00:06:04,139 because they will slow down data load. 95 00:06:04,139 --> 00:06:08,903 And then it includes a bunch of COPY commands to load data to those 96 00:06:08,903 --> 00:06:15,273 tables, and then, indexes and constraints like foreign keys, unique keys and so on. 97 00:06:16,684 --> 00:06:21,384 First thing is quite IO-intensive usually, so it loads disk a lot. 98 00:06:21,444 --> 00:06:25,784 But indexes are both IO but a lot of also CPU intensive, 99 00:06:25,784 --> 00:06:28,484 so you will see CPUs involved a lot. 100 00:06:28,664 --> 00:06:32,824 And like, there is pg_dump, there is pg_restore. 101 00:06:32,924 --> 00:06:39,384 Looks simple, but something is missing. And you started jumping to visibility map. 102 00:06:39,684 --> 00:06:43,024 That is definitely missing. But before that, statistics, right? 103 00:06:43,024 --> 00:06:49,184 Statistics is missing, visibility map is missing, and also hint bits are missing. 104 00:06:49,284 --> 00:06:54,344 Unless you... Like, this, this is nuance, actually. Hint bits are missing 105 00:06:54,464 --> 00:07:00,444 only if you don't create indexes, because indexes will read tuples. 106 00:07:00,764 --> 00:07:03,882 Index creation will read tuples, and it should build, 107 00:07:04,664 --> 00:07:09,204 hint bits. But what are... Like, what kind of statistics? 108 00:07:09,204 --> 00:07:14,484 What are... What is visibility map, and what is a hint bit? What are hint bits, 109 00:07:14,484 --> 00:07:20,184 right? Let's maybe walk through all of them, right? So statistics- 110 00:07:20,444 --> 00:07:22,884 - Okay - statistics is maybe, like, it's, like, 111 00:07:22,884 --> 00:07:28,624 easier to understand, like, because we all know Postgres planner is statistics-based. 112 00:07:29,324 --> 00:07:34,364 Without statistics, it might choose wrong, wrong, plans. 113 00:07:34,724 --> 00:07:39,064 And usually, what I usually see, like, manually doing something, 114 00:07:40,124 --> 00:07:45,044 or with agents actually, right? You just load data. You have indexes, all good. 115 00:07:45,044 --> 00:07:49,384 You think you're good to go, but, but you see the plan, you check the plan, 116 00:07:49,384 --> 00:07:54,713 EXPLAIN ANALYZE BUFFERS, and you see Bitmap index scan, bitmap heap scan, used. 117 00:07:54,713 --> 00:07:59,413 A pair of bitmap scans is used instead of index scan which you expected, 118 00:07:59,413 --> 00:08:05,633 or even worse, you expected index only scan. For example, select count, right? 119 00:08:05,973 --> 00:08:11,472 But you see bitmap scan is used, which is quite slower, 120 00:08:13,064 --> 00:08:17,104 That raises questions why, and obvious answer why, like, 121 00:08:17,104 --> 00:08:20,804 - you don't have statistics. - So let's- 122 00:08:20,844 --> 00:08:23,264 - Yeah - let's define a few things, okay? 123 00:08:23,264 --> 00:08:26,224 So statistics, the easiest one for me, 124 00:08:26,664 --> 00:08:32,284 to explain this to somebody who's new is if you have a primary key index, 125 00:08:32,744 --> 00:08:37,564 the database will automatically keep track of the most minimal value 126 00:08:37,624 --> 00:08:41,244 and the highest value that's currently in the system, 127 00:08:41,244 --> 00:08:46,004 and this allows a very fast way. So if you select something that's where, 128 00:08:46,004 --> 00:08:51,544 where ID is less than the minimum value, it knows there's no rows, okay? 129 00:08:51,544 --> 00:08:55,704 The optimizer can just look at that. Same thing if you try going above that ID, 130 00:08:55,704 --> 00:09:00,484 the maximum ID value. So those are little tricks that you can play. Now, 131 00:09:00,484 --> 00:09:05,764 after that, you get into the more complex things like it's a very dense packing, 132 00:09:05,764 --> 00:09:10,904 which means if you're 10% below the highest number on an ID column, 133 00:09:10,904 --> 00:09:16,344 it can estimate the number of rows it's gonna bump into. Now, it's an estimate, 134 00:09:16,344 --> 00:09:20,264 but it understands this based on collecting some of these statistics. 135 00:09:20,264 --> 00:09:24,904 That versus the visibility map, and the visibility map issue is, like, 136 00:09:24,904 --> 00:09:28,330 funny because after a pg_restore, everything's 137 00:09:28,330 --> 00:09:31,344 - visible. All records are visible. - Right. They don't know about- 138 00:09:31,344 --> 00:09:33,544 Kirk: But the map still has to be written- 139 00:09:33,544 --> 00:09:36,368 - Well- - so for the system to use it. Go ahead. 140 00:09:36,724 --> 00:09:43,304 Nik: Yeah, so... Oh, how to explain this better? So statistics is for planner. 141 00:09:43,304 --> 00:09:47,924 Visibility map is also for planner as well, and for executor as well because 142 00:09:48,364 --> 00:09:54,404 indexes don't have version data. So every time we, we have index scan, 143 00:09:54,624 --> 00:09:59,284 we need to inspect heap. And index only scan, despite the name, 144 00:09:59,284 --> 00:10:05,704 can also inspect heap if it doesn't know anything about this tuple, status. 145 00:10:05,704 --> 00:10:09,175 Is it live or dead? We don't know from index itself, right? 146 00:10:09,224 --> 00:10:15,963 So visibility map, it's a map, which stores two bits for every page. 147 00:10:15,963 --> 00:10:21,204 One is all frozen and other is all visible, which means this page contains 148 00:10:21,264 --> 00:10:26,564 tuples which are all visible for all transactions, right? And you are right. 149 00:10:26,564 --> 00:10:29,744 After restore, we have a lot of new pages. 150 00:10:29,884 --> 00:10:33,704 We probably have some tuples which went to pages 151 00:10:34,004 --> 00:10:37,944 with other tuples, you know, like, not from our, our loading transaction. 152 00:10:37,944 --> 00:10:42,624 It might happen, but most of the pages will be, if it's bulk load a lot of data. 153 00:10:42,624 --> 00:10:47,124 Like, or maybe the table is just created. We talk about fresh dump restore, right? 154 00:10:47,124 --> 00:10:51,006 Table is just created, so all the, those pages are, are, are ours, 155 00:10:51,104 --> 00:10:57,564 and we just finished this work. Everything should be visible to all, but it's like 156 00:10:57,684 --> 00:11:01,644 by default it's not, because this is just how Postgres works. 157 00:11:01,644 --> 00:11:07,964 Those pages are just created and other sessions not know. 158 00:11:08,104 --> 00:11:13,294 They must inspect. The visibility map is not created yet. That's a problem, right? 159 00:11:13,954 --> 00:11:18,574 So, and also there are hint bits, the third the third piece, because 160 00:11:18,974 --> 00:11:22,114 we talked about this multiple times in this, on this podcast, 161 00:11:22,114 --> 00:11:27,954 that you can select, columns xmin, xmax from any table. 162 00:11:28,134 --> 00:11:31,174 It's like date of birth and date of death. 163 00:11:32,534 --> 00:11:35,574 And those columns are always present. 164 00:11:35,574 --> 00:11:40,854 They contain transaction ID of transaction which created this tuple or 165 00:11:40,994 --> 00:11:45,674 attempted to delete this tuple, attempted to create this tuple as well. 166 00:11:45,674 --> 00:11:49,474 But like, I mean, because insert or copy can be canceled as well, right? 167 00:11:49,474 --> 00:11:53,934 And a lot of the tuples will be produced by a failed attempt. 168 00:11:54,214 --> 00:11:57,914 So those columns are like, are hidden columns. 169 00:11:58,074 --> 00:12:02,250 And the interesting piece is that, for example, if we delete some tuple 170 00:12:02,726 --> 00:12:05,886 It just marks xmin, puts it to the row. 171 00:12:06,126 --> 00:12:11,666 This row goes to WAL and to the page itself. And then later, 172 00:12:11,666 --> 00:12:16,266 autovacuum needs to come and actually delete this tuple if it's actually dead. 173 00:12:16,326 --> 00:12:19,186 But this xmin value originally, 174 00:12:21,006 --> 00:12:26,646 when another session tries to read it, originally it doesn't know if this xmin, 175 00:12:27,406 --> 00:12:31,146 belongs to a transaction which succeeded or failed. It doesn't know. 176 00:12:31,146 --> 00:12:36,906 So it must go to, clog, transaction log, to inspect the status of that transaction. 177 00:12:36,906 --> 00:12:41,726 It, it takes a little bit of extra, there is overhead of this operation. 178 00:12:42,386 --> 00:12:44,066 And this is, like, 179 00:12:44,606 --> 00:12:49,946 not super efficient to inspect for every tuple to inspect transaction ID status 180 00:12:50,086 --> 00:12:53,646 for all those transactions which are present in xmin, xmax. 181 00:12:53,646 --> 00:12:56,166 That's why there is a so-called, 182 00:12:56,906 --> 00:13:00,746 there is a mechanism, hint bits, which basically in the tuple header, 183 00:13:00,746 --> 00:13:05,766 it just caches the status of those transactions, xmin and then xmax, right? 184 00:13:05,766 --> 00:13:08,846 So because sometimes you have a live tuple, 185 00:13:08,846 --> 00:13:13,546 you see xmax is filled with some number, but it's just that transaction was roll- 186 00:13:13,546 --> 00:13:19,466 like, got rolled back, right, at the end. Means the tuple is, tuple is still alive. 187 00:13:20,066 --> 00:13:24,486 Of course, some transactions also complicate things additionally, right? 188 00:13:24,486 --> 00:13:31,336 So just to avoid this real time, exploration of transaction IDs, 189 00:13:31,336 --> 00:13:37,966 hint bits cache it right in, in the header of the tuple, and it happens when? 190 00:13:37,966 --> 00:13:43,276 At the first read, actually, after load. I remember this is old, 191 00:13:44,436 --> 00:13:49,456 scary story. It's good to have it, before Halloween, right? Like- 192 00:13:49,456 --> 00:13:53,216 - [laughs] - oh, we see a lot of writes on replicas, 193 00:13:53,556 --> 00:13:58,876 on standbys, and those writes are not coming from temporary file generation. 194 00:13:58,876 --> 00:14:03,076 It's just actually Postgres decided to update some data. What, what's happening? 195 00:14:03,076 --> 00:14:07,656 What's happening, it just updates those hint bits because it's fresh. 196 00:14:07,656 --> 00:14:12,176 Like, we have a lot of fresh tuples. Maybe we had copy, bulk load, or pg, pg_dump, 197 00:14:12,176 --> 00:14:15,736 pg_restore used. And then replicas don't know. 198 00:14:15,736 --> 00:14:20,696 Like, they don't have, those hint bits, and they need to take care 199 00:14:20,716 --> 00:14:25,266 of it by themselves, each replica actually, which is crazy. 200 00:14:25,266 --> 00:14:30,586 But if you enable wal_log_hints or you have data checksums enabled, which 201 00:14:30,586 --> 00:14:35,446 is default if you create cluster recently, you probably have data checksums enabled, 202 00:14:35,446 --> 00:14:39,086 then it's already done on the primary and propagated to all standbys, 203 00:14:39,086 --> 00:14:42,486 so everything should be fine. Hint bits are there. 204 00:14:42,486 --> 00:14:46,006 There are some nuances there still. Some writes might happen, 205 00:14:46,006 --> 00:14:52,027 but not as, as before. So the interesting part about this, like after dump restore, 206 00:14:52,027 --> 00:14:55,427 we don't have statistics, we don't have visibility maps, 207 00:14:55,427 --> 00:15:00,407 and we don't have hint bits, which is, I mean, like- 208 00:15:00,407 --> 00:15:02,995 - Well, - very interesting situation, right? 209 00:15:03,007 --> 00:15:06,187 Kirk: so to be clear, we have all the data we need 210 00:15:06,247 --> 00:15:09,967 for our queries to give us the correct answer. 211 00:15:09,967 --> 00:15:16,087 The problem is it's not optimal, and now we're... this is we're spilling into 212 00:15:16,207 --> 00:15:21,627 you want this to be optimal. And I kind of want to cut to the chase a little bit 213 00:15:21,627 --> 00:15:27,347 and say the best thing you can do right after your restore is a VACUUM ANALYZE. 214 00:15:27,387 --> 00:15:30,087 - If you VACUUM ANALYZE all your tables- - Yeah 215 00:15:30,087 --> 00:15:32,447 - it'll create all of these things. - Yeah. 216 00:15:32,447 --> 00:15:34,667 Kirk: And then everything works out. 217 00:15:34,787 --> 00:15:39,487 And that begs one question: Why doesn't the restore do that automatically? 218 00:15:39,507 --> 00:15:42,627 Nik: Which is a big question. I... here I'm, I, I must 219 00:15:42,627 --> 00:15:46,227 mention that we have a robot here which promised to 220 00:15:47,047 --> 00:15:52,207 follow our discussion and warn us, not to exceed, 45, 50 minutes. 221 00:15:52,207 --> 00:15:55,567 This is our goal for episodes. Because I know Kirk and, and myself, 222 00:15:55,567 --> 00:15:59,387 we can easily spend hours discussing all the nuances, 223 00:15:59,387 --> 00:16:04,847 especially if, as decided now, we will bring a little bit of hacking, 224 00:16:04,856 --> 00:16:11,395 pieces to the discussion because we don't... Like, we have a two-sided goal. 225 00:16:11,676 --> 00:16:16,636 One, like, we want, to, to understand how to operate Postgres these days. 226 00:16:16,636 --> 00:16:23,577 And I agree with you. The... if, if, one thing our listeners should remember 227 00:16:23,617 --> 00:16:27,297 is just never forget VACUUM ANALYZE after loading 228 00:16:27,297 --> 00:16:30,197 bulk load, for example, restoring from dumps. 229 00:16:30,697 --> 00:16:35,377 And we have another goal, slightly touch, possible improvements in Postgres itself, 230 00:16:35,377 --> 00:16:42,377 maybe for our Wednesday sessions, which we irregularly have, right? Irregularly. 231 00:16:42,597 --> 00:16:44,891 - I, I, I cannot pronounce it. - Right. 232 00:16:45,037 --> 00:16:50,076 Nik: So that's interesting. Yeah, let's talk about this. Like, why those tools? 233 00:16:50,076 --> 00:16:55,797 Like, why isn't automated by pg_restore? Why it's not present, for example, 234 00:16:55,797 --> 00:17:00,577 in form of explicit VACUUM ANALYZE lines inside dumps? 235 00:17:01,276 --> 00:17:08,276 Why pg_restore doesn't have hyphen, hyphen, vacuum, hyphen, analyze option? 236 00:17:08,756 --> 00:17:13,156 Well, this is one hacking question, right? Like, why is this not implemented 237 00:17:13,156 --> 00:17:18,256 in Postgres? And another operational, like DBRE question, tooling question 238 00:17:18,556 --> 00:17:24,546 is our system is explore 28 documentation pages. 239 00:17:25,156 --> 00:17:28,536 Most popular platforms managed by Postgres platforms, 28. 240 00:17:28,536 --> 00:17:35,536 Only 3 of them mention vacuum. I think only 5 or 6 of them mention analyze. 241 00:17:36,036 --> 00:17:43,036 Most of them, including RDS, Neon, Cloud SQL, they don't mention neither. 242 00:17:43,096 --> 00:17:49,546 No analyze, no no vacuum. Which means, like I'm very curious how many 243 00:17:49,646 --> 00:17:55,946 folks, humans, and agents bumped into this problem and we are, like- 244 00:17:56,596 --> 00:18:02,056 amazed how performance is better right after restore, right? 245 00:18:02,616 --> 00:18:04,076 - And we hit this- - Let- 246 00:18:04,076 --> 00:18:05,776 - in our production. - Yeah. 247 00:18:05,996 --> 00:18:12,616 Let me mention three more names, because Supabase, Dalibo, and Aiven, only three 248 00:18:12,856 --> 00:18:17,016 documentation pieces, we explored, mentioned VACUUM ANALYZE. 249 00:18:17,016 --> 00:18:23,516 That, that was great. So three winners in this small contest. Right. Well, actually, 250 00:18:23,516 --> 00:18:28,736 before we talk into these two pieces, we need also to raise the question, maybe, 251 00:18:28,736 --> 00:18:32,676 maybe we are wrong. Maybe, you know, there is autovacuum. 252 00:18:32,676 --> 00:18:37,876 It will come and take care of all these pieces, right? Maybe. 253 00:18:37,876 --> 00:18:41,776 And as, as I said, hint bits, if, if you, if your dump, 254 00:18:41,836 --> 00:18:46,456 has index creation for your table, hint bits are solved, so only two topics left, 255 00:18:46,456 --> 00:18:50,916 right? So we we need statistics and we need visibility maps for good performance. 256 00:18:50,916 --> 00:18:54,616 Without visibility maps, all index index only scans will be 257 00:18:54,876 --> 00:18:59,616 having huge heap fetches, which is bad. But what about autovacuum? 258 00:18:59,616 --> 00:19:03,656 It will come soon, like just wait a few seconds, it will catch up, 259 00:19:04,036 --> 00:19:09,836 and you will have both visibility maps, which is job for vacuum, and statistics, 260 00:19:09,836 --> 00:19:15,846 which is a job for analyze, but autovacuum covers both of them. Isn't it so? 261 00:19:16,926 --> 00:19:19,206 Kirk: So that's a great question. Here's... 262 00:19:19,206 --> 00:19:21,126 There's a few challenges, though. 263 00:19:21,126 --> 00:19:25,166 First off, does an autovacuum have a limited window of time it runs? 264 00:19:25,166 --> 00:19:28,286 So if, if one of your tables is exceedingly large, 265 00:19:28,286 --> 00:19:32,746 will autovacuum actually go through all of it to generate the whole visibility map 266 00:19:32,746 --> 00:19:36,926 when it hits that table, all right? That's the first question I have. 267 00:19:36,926 --> 00:19:41,726 The other thing is, is maybe, okay, autovacuum's gotta catch up. 268 00:19:41,726 --> 00:19:46,226 How many tables did you just restore, and how much time is that? So now you get 269 00:19:46,226 --> 00:19:51,070 the question of, I finished my restore, can I use my database? Well, it depends. 270 00:19:51,070 --> 00:19:53,670 Do you have 40 autovacuums back there running trying 271 00:19:53,670 --> 00:19:55,066 - to catch up? - Yeah. 272 00:19:55,066 --> 00:19:57,866 Kirk: Did you let all the autovacuums settle down? 273 00:19:57,866 --> 00:20:01,806 Nik: Most, most databases I observe, even huge ones which come to us, 274 00:20:01,806 --> 00:20:06,366 they usually come with three workers, even if they have, like, 96 cores. 275 00:20:06,366 --> 00:20:12,486 The, people, are afraid of raising number of workers. And you are right, 276 00:20:12,486 --> 00:20:18,186 autovacuum is throttled. So it takes a lot of time for it to process the tables. 277 00:20:18,346 --> 00:20:22,586 Plus also it can be blocked, it can be busy with other stuff, right? 278 00:20:22,646 --> 00:20:27,966 Plus also it can be a problem, with partition tables because autovacuum 279 00:20:27,966 --> 00:20:33,406 doesn't take care. vacuumdb doesn't take care, and I think autovacuum doesn't take 280 00:20:33,406 --> 00:20:36,846 care of root tables in partition, parent tables of partition tables, 281 00:20:36,846 --> 00:20:39,966 which is improved in Postgres 19, but not fully. 282 00:20:40,066 --> 00:20:44,627 So which means that if you have a partition table, if, and you forgot 283 00:20:44,627 --> 00:20:50,156 VACUUM ANALYZE manually, This is a big operational gap Postgres right now has, 284 00:20:50,436 --> 00:20:54,736 and obviously, like, it's not enough, and I don't want to wait sometimes, right? 285 00:20:54,736 --> 00:20:58,236 Another point is that I just recovered from dump. 286 00:20:58,376 --> 00:21:03,756 I'm checking some query right away, and I see bad performance because 287 00:21:03,836 --> 00:21:09,396 autovacuum is asynchronous by nature. So yeah, and it's like opportunistic, 288 00:21:09,396 --> 00:21:13,816 approach, asynchronous, and in many cases it's not enough. 289 00:21:14,756 --> 00:21:20,736 Thus, we we should have VACUUM ANALYZE in dumps or pg_restore. 290 00:21:21,456 --> 00:21:25,576 Kirk: Absolutely. And I think it, for me, I think it should be a restore option, 291 00:21:25,576 --> 00:21:30,296 okay? Just because the person restoring it knows 292 00:21:30,416 --> 00:21:33,936 what they want it to do and why that restore exists. 293 00:21:34,876 --> 00:21:39,356 So for me, it kind of makes more sense in the restore than telling the dump. 294 00:21:39,356 --> 00:21:43,496 Because also if it's only in the dump, then you can't do it in the restore. 295 00:21:43,496 --> 00:21:47,026 And if you're restoring something else that didn't do that, it doesn't 296 00:21:47,026 --> 00:21:50,946 give control to the person who is what I consider the last user, 297 00:21:50,946 --> 00:21:54,586 the last decision maker. If it's in the restore, the last decision maker 298 00:21:54,586 --> 00:21:56,246 - has that opportunity. - Right. 299 00:21:56,246 --> 00:21:59,766 Kirk: And that's probably the person who needs it the most. 300 00:22:00,406 --> 00:22:06,446 Nik: Should it be on by default? Imagine it existed. Should it be on or no? 301 00:22:07,606 --> 00:22:09,686 Kirk: Ooh, that's always a tough one. 302 00:22:09,686 --> 00:22:14,786 I would say the hackers would argue no, because it would create a lot more work 303 00:22:14,786 --> 00:22:18,446 and demand that never existed before and make things slower, 304 00:22:18,446 --> 00:22:20,386 and that would be their big push. 305 00:22:20,386 --> 00:22:24,186 Personally, I think it should be on by default. 306 00:22:24,366 --> 00:22:30,546 You know, it's, it's such an important piece to have all of the rest of that data 307 00:22:30,606 --> 00:22:33,566 and the visibility maps and everything else. 308 00:22:33,976 --> 00:22:36,396 Not having it just seems like it's lacking. 309 00:22:36,396 --> 00:22:38,460 - Yeah. - It's begging for problems. 310 00:22:38,460 --> 00:22:40,716 Nik: Right. I, I have another tough question. 311 00:22:40,716 --> 00:22:45,096 What if we use a single transaction option, during pg_restore? 312 00:22:45,096 --> 00:22:48,836 Vacuum cannot be included inside a transaction block. 313 00:22:50,026 --> 00:22:52,046 - Ah, yeah. - Yeah. [laughs] 314 00:22:52,046 --> 00:22:56,806 Kirk: But, but I'll be honest, the vacuum concept, 315 00:22:56,866 --> 00:23:02,826 even if you did the single transaction for your entire restore, once that commits, 316 00:23:02,826 --> 00:23:04,532 that's when you would vacuum, 317 00:23:04,580 --> 00:23:06,886 - only if it succeeded without error. - After. 318 00:23:06,886 --> 00:23:07,886 - Right. - Yeah. 319 00:23:07,890 --> 00:23:09,126 - Yeah, but this makes- - Yeah, the vacuum- 320 00:23:09,126 --> 00:23:10,866 - Well- - wouldn't be part of the commit. 321 00:23:10,866 --> 00:23:13,760 Nik: Right. I, I think, this option could just be, like, 322 00:23:13,760 --> 00:23:17,266 incompatible with single transaction. If you chose single transaction, 323 00:23:17,266 --> 00:23:22,046 pg_restore could fail saying, like, you chose two incompatible options. That's it. 324 00:23:22,046 --> 00:23:27,046 Why not? And we know, like, by default, pg_restore is not, ACID basically. 325 00:23:27,046 --> 00:23:31,226 It's not a single transaction. It, it can fail and continue and so on. That's all, 326 00:23:31,226 --> 00:23:35,386 like, there is also a stop on error, option, but it's different. 327 00:23:35,386 --> 00:23:36,646 - Well- - But yeah. 328 00:23:36,646 --> 00:23:40,466 Kirk: Yeah, and the minute you use -j option in pg_restore, 329 00:23:40,466 --> 00:23:46,310 you introduce a problem where the creation order- All right? 330 00:23:46,310 --> 00:23:47,970 Can create a problem, because you 331 00:23:47,970 --> 00:23:52,550 can be in the middle of creating the four dependencies a materialized view needs, 332 00:23:52,550 --> 00:23:57,030 for example, and you're running in five threads, 333 00:23:57,030 --> 00:24:01,230 and the four are running the restore, and now it looks and says, "Oh, the next thing 334 00:24:01,230 --> 00:24:03,180 to do is create the materialized view," 335 00:24:03,180 --> 00:24:07,370 but none of the four tables you need have actually finished being created. 336 00:24:07,370 --> 00:24:10,470 So then your materialized view at the end blows up 337 00:24:10,510 --> 00:24:15,390 because it doesn't have that blocking mechanism of knowing, hey, 338 00:24:15,390 --> 00:24:17,910 I gotta wait till these things finish before I go. 339 00:24:17,910 --> 00:24:21,420 So it's, pg_restore's not perfect, okay? 340 00:24:21,420 --> 00:24:25,470 And running into some of these things was very interesting for me the first time. 341 00:24:25,470 --> 00:24:27,170 Nik: Right. I also think, 342 00:24:27,170 --> 00:24:32,525 hackers might argue that there is already another CLI tool, vacuumdb, which, 343 00:24:32,525 --> 00:24:37,350 supports vacuuming and analyzing, and it can do... Like, by default, it's, 344 00:24:37,350 --> 00:24:43,010 it's vacuuming, but you can say hyphen, hyphen analyze, so it will do both. 345 00:24:43,130 --> 00:24:48,070 And also you can say analyze only, it will do analyze only. And they can say, "Okay, 346 00:24:48,070 --> 00:24:52,190 we have already CLI tool and so on." But the problem is for me right now 347 00:24:52,190 --> 00:24:59,190 in a agentic, era, is that having these, like, in two separate tools requires, 348 00:24:59,230 --> 00:25:03,890 like, memory, or you need to memorize. And we, and we see how platforms, 349 00:25:04,970 --> 00:25:06,410 - That's the takeaway - Yeah 350 00:25:06,410 --> 00:25:10,310 Kirk: Out of 28 platforms, only eight total mentioned either- 351 00:25:10,310 --> 00:25:12,110 - Yeah - analyze, gather statistics- 352 00:25:12,110 --> 00:25:13,110 - Yeah - whatever. 353 00:25:13,110 --> 00:25:15,160 - Yeah. - So 20 of them clearly 354 00:25:15,160 --> 00:25:16,650 - never said anything. - Right. 355 00:25:16,650 --> 00:25:19,380 - So, and this was exactly our experience. - And vacuum only 356 00:25:19,380 --> 00:25:20,850 in three. Vacuum only in three. 357 00:25:20,850 --> 00:25:23,262 - And vacuum only in three. - 25 lack it. 358 00:25:23,810 --> 00:25:27,370 Kirk: and to me, it made no sense I had to vacuum them- 359 00:25:27,370 --> 00:25:29,700 - Mm - because they were just freshly restored. 360 00:25:29,700 --> 00:25:32,230 - Yeah, it's counterintuitive. - It's a clean copy. 361 00:25:32,230 --> 00:25:35,010 - Yeah. - Like, the fact that I had to vacuum 362 00:25:35,010 --> 00:25:38,630 - evaded my thought process. - If you don't know about visibility maps, 363 00:25:38,630 --> 00:25:42,210 I agree. I remember, like, it took time. I, I, for for my mind, 364 00:25:42,210 --> 00:25:46,710 it took years to settle and have, like, muscle memory to type a VACUUM ANALYZE. 365 00:25:46,710 --> 00:25:50,170 And I must admit, I still sometimes, forget about it. 366 00:25:50,530 --> 00:25:57,310 And I know agents, some agents will do forget about it. So I think, like, I, I'm, 367 00:25:57,310 --> 00:26:01,470 I'm, I'm feeling we are preparing already to raise this again in hackers. 368 00:26:01,470 --> 00:26:06,570 And I think we, we must, take into account agentic behavior, because if it's separate 369 00:26:06,570 --> 00:26:10,450 CLI tools, it's easy to forget these things. If there is an option in CLI, 370 00:26:10,450 --> 00:26:14,450 clear option in, in pg_restore, it's easy to, oh, there is a such option. 371 00:26:14,450 --> 00:26:17,910 Even if it's not default, it would be great to have already, right? 372 00:26:17,910 --> 00:26:24,070 Because it will be, like, easy to, easy to use, right? Now you vacuumdb, oh, 373 00:26:24,130 --> 00:26:29,030 something. However, as we know, some dumps are restored through psql. 374 00:26:30,120 --> 00:26:35,151 Kirk: Yes. Absolutely. And guess what? If you're restoring those, they're, 375 00:26:35,271 --> 00:26:38,611 some of that's causing some of these things to actually happen- 376 00:26:38,611 --> 00:26:41,071 - Right - because the transactions are different. 377 00:26:41,071 --> 00:26:42,071 Nik: Right. 378 00:26:42,371 --> 00:26:43,400 - So it's- - Yeah 379 00:26:43,400 --> 00:26:46,100 it's a little different. But the flip side, too, 380 00:26:46,100 --> 00:26:49,651 is, and this is a key point, you mentioned using agents. 381 00:26:49,651 --> 00:26:52,571 - Right. - If, if you are doing agents and you're 382 00:26:52,571 --> 00:26:57,431 actually trying to capture statistics or speed benchmarking, 383 00:26:57,471 --> 00:27:00,151 - and they forget to do this- - Yeah 384 00:27:00,340 --> 00:27:02,991 Kirk: it totally blows up your benchmarking concepts. 385 00:27:02,991 --> 00:27:05,391 Nik: Yeah. You know what would help? 386 00:27:06,170 --> 00:27:11,290 Fully sitting inside an agentic agentic use context. 387 00:27:11,470 --> 00:27:16,250 So I would love to have VACUUM ANALYZE option for pg_restore, 388 00:27:16,250 --> 00:27:21,650 even if it's not default. Although I, I'm, I personally would, like, argue to making, 389 00:27:21,650 --> 00:27:27,770 for making it default. And additionally, in psql, if we restore from dump, 390 00:27:27,770 --> 00:27:34,170 write in dumps, pg_dump, if it included just line, like raise notice- 391 00:27:34,170 --> 00:27:36,390 - Yeah - or something like this for agent. 392 00:27:36,390 --> 00:27:39,070 Raise, raise info, raise, raise something. 393 00:27:39,270 --> 00:27:42,570 Raise warning at least if you don't want, notice- 394 00:27:42,570 --> 00:27:44,050 - Actually, but if you... Yeah - Just- 395 00:27:44,050 --> 00:27:45,770 - If you create the dump- - Don't forget VACUUM ANALYZE 396 00:27:45,770 --> 00:27:46,950 - with the option- - Just one line 397 00:27:46,950 --> 00:27:48,170 Kirk: add the line. 398 00:27:48,310 --> 00:27:52,130 Nik: That's it. Imagine, like, dump, it just includes this line. 399 00:27:52,130 --> 00:27:53,610 - It's easy, cheap. - Yeah. 400 00:27:53,610 --> 00:27:59,510 Nik: And agents would, restoring, it would notice it. It would be in context, 401 00:27:59,510 --> 00:28:01,450 - and that's it, right? - Well, 402 00:28:01,450 --> 00:28:05,150 and not only that, here's what's cool about Postgres. I give it points. 403 00:28:05,150 --> 00:28:10,310 If I'm logged into a specific database and I type VACUUM ANALYZE 404 00:28:10,310 --> 00:28:15,530 semicolon, it understands I wanna VACUUM ANALYZE that entire database. 405 00:28:15,870 --> 00:28:18,370 - Right. - That, that one database that I'm logged 406 00:28:18,370 --> 00:28:22,300 - into, it'll do every table in there. - In single process- 407 00:28:22,300 --> 00:28:23,650 - That's, - single threaded 408 00:28:23,650 --> 00:28:25,230 - I don't even have to load the table - single threaded. 409 00:28:25,230 --> 00:28:26,642 - Is that? - A separate, 410 00:28:26,650 --> 00:28:27,730 Yeah, single-threaded, of course 411 00:28:27,730 --> 00:28:28,140 - Yeah - I 412 00:28:28,140 --> 00:28:28,730 - wish- - Which 413 00:28:28,730 --> 00:28:30,900 - is horrible - I wish would be control over, 414 00:28:30,900 --> 00:28:32,000 parallelization level, 415 00:28:32,000 --> 00:28:35,850 right, at SQL level, but this is another idea for hacking. You know, like- 416 00:28:35,850 --> 00:28:38,630 - Right - I now we can use vacuumdb, 417 00:28:38,630 --> 00:28:44,030 but until Postgres 19, vacuumdb doesn't take care of parent, partition tables, 418 00:28:44,070 --> 00:28:47,830 of parents of partition tables as we discussed, 419 00:28:48,250 --> 00:28:51,910 - and it's a headache actually, right? - Well, and, and again, 420 00:28:51,910 --> 00:28:56,080 I wanna point out that when we moved to Postgres for the first time, we do daily 421 00:28:56,080 --> 00:28:58,330 - restores in multiple systems. - From Oracle 422 00:28:58,330 --> 00:28:59,810 - Just from- - You moved from Oracle. 423 00:28:59,810 --> 00:29:04,130 Kirk: From... Well, well, yeah, we moved over from Oracle to Postgres, 424 00:29:04,130 --> 00:29:09,190 and we have an environment set up that auto restore. We have snapshot pg_dumps. 425 00:29:09,190 --> 00:29:12,250 We restore these into multiple systems 426 00:29:12,250 --> 00:29:15,290 used by different people for different reasons, okay? 427 00:29:15,290 --> 00:29:19,690 And the challenge for us was the performance went to shit. 428 00:29:19,690 --> 00:29:25,210 [laughs] For, oh, forgive the French, but on these systems, it was horrible, and it 429 00:29:25,210 --> 00:29:31,250 literally took me a while to figure out that it was, I was missing the, you know, 430 00:29:31,250 --> 00:29:35,290 the, the stuff that goes along with it, not just statistics. 431 00:29:35,290 --> 00:29:37,230 I was missing the visibility maps. 432 00:29:37,230 --> 00:29:42,394 And the finding out vacuum analyzed fixed the problem. That was great. 433 00:29:42,394 --> 00:29:47,034 And then the next thing we ran into was if you issue VACUUM ANALYZE semicolon, 434 00:29:47,034 --> 00:29:49,874 - it's single-threaded. - Yeah. Exactly. 435 00:29:49,874 --> 00:29:53,534 Kirk: So five large tables just takes forever. 436 00:29:53,534 --> 00:29:54,614 - Yeah, yeah. - Yeah. 437 00:29:54,614 --> 00:29:57,970 So, and then you end up doing vacuumdb with multiple threads. 438 00:29:57,970 --> 00:30:01,774 - Which has issues with partition tables. - Right. Luckily, we weren't using 439 00:30:01,774 --> 00:30:02,794 - partitions- - Okay 440 00:30:02,794 --> 00:30:04,260 - so we were okay. - Okay. 441 00:30:04,260 --> 00:30:05,734 - Okay. - Yeah. But- 442 00:30:05,734 --> 00:30:07,754 - But - it fixed our problems. But I- 443 00:30:07,754 --> 00:30:09,494 - Yeah - blindsided by it, 444 00:30:09,494 --> 00:30:13,240 and apparently so are 80% of the people out there that even talk about 445 00:30:13,240 --> 00:30:14,540 - this. - Yeah. I remember 446 00:30:14,540 --> 00:30:18,290 actually a couple of years ago when I was writing every day about Postgres, 447 00:30:18,290 --> 00:30:22,794 how, like how to Postgres Marathon it was called. I had recipes how to dump, 448 00:30:22,794 --> 00:30:28,634 restore, like at the highest speed, like utilizing all resources, bumping, 449 00:30:28,634 --> 00:30:33,254 to either CPU or disk, saturating them, right? 450 00:30:33,254 --> 00:30:37,914 If you want to just fresh machine, you want dump, restore as fast as possible. 451 00:30:38,094 --> 00:30:43,654 And vacuumdb is of course there, but with a note about partition tables. 452 00:30:43,654 --> 00:30:48,734 And it, it was, I think Laurenz Albe who fixed it for, fixed it for Postgres 19, 453 00:30:48,734 --> 00:30:53,574 so looking forward for upcoming release in November, hopefully, right? 454 00:30:54,234 --> 00:30:55,974 - And yeah, - Nice 455 00:30:55,974 --> 00:31:00,574 Nik: so yeah, so obviously there are some ideas in terms of hacking here, but again, 456 00:31:00,574 --> 00:31:06,774 how do you find the, this idea of simple raise info or something which would go 457 00:31:06,774 --> 00:31:13,774 to stderr, not stdout, just to signal to humans or agents, 458 00:31:13,994 --> 00:31:20,994 like don't forget that, right? Which, like obvious, right? Just include it in dumps. 459 00:31:22,094 --> 00:31:24,074 - Right. - Yeah. Yeah, 460 00:31:24,074 --> 00:31:27,154 - Right. Interesting - let's maybe accumulate some ideas and 461 00:31:27,154 --> 00:31:32,694 raise this discussion. I know it was discussed in 2002, 2014. I, I just... 462 00:31:32,754 --> 00:31:38,924 Our research came out and it this is not a new topic, but it wasn't, 463 00:31:38,924 --> 00:31:44,290 properly revisited. I think it's time to revisit in agentic context, right? Yeah. 464 00:31:44,290 --> 00:31:48,344 Kirk: Exactly. And as these, because the, as the agents are doing these restores, 465 00:31:48,344 --> 00:31:51,210 - they this is a nuanced issue. - Right. 466 00:31:51,210 --> 00:31:52,864 - And they don't see it. - Right. 467 00:31:52,864 --> 00:31:54,864 - And it's not popular. - Right. 468 00:31:54,864 --> 00:31:57,384 - So. - Let's slightly maybe touch, 469 00:31:58,010 --> 00:32:01,064 visibility maps and hint bits, because there are changes. Oh, 470 00:32:01,064 --> 00:32:05,804 actually, let's, let's touch statistics additionally, because as you mentioned- 471 00:32:05,804 --> 00:32:09,144 - Yeah - Postgres 18, implemented 472 00:32:09,404 --> 00:32:14,804 a statistics dump restore, but it's not on by default, right? So you need, an option. 473 00:32:14,804 --> 00:32:18,584 An option, by the way, it was called in a weird way for pg_dump. 474 00:32:19,124 --> 00:32:24,514 All such options start with hyphen, hyphen, with, with. But this- 475 00:32:24,514 --> 00:32:27,134 - Oh - is without. It's just statistics. 476 00:32:28,094 --> 00:32:30,134 Just hyphen, hyphen, statistics. Okay. 477 00:32:30,134 --> 00:32:35,784 And it's not on by default, which means that you need to set it. 478 00:32:36,834 --> 00:32:40,834 It has some limitations, so extended statistics won't be dumped. 479 00:32:41,834 --> 00:32:45,954 But good news for partition tables, partition tables, root tables, for them, 480 00:32:45,954 --> 00:32:49,194 statistics will be dumped, and that's great. 481 00:32:49,534 --> 00:32:52,894 And then we restore it and kind of it's fine, right? 482 00:32:53,454 --> 00:32:57,054 However, well, extended statistics is missing. 483 00:32:57,054 --> 00:33:02,474 And there is an interesting piece, our system DBLab Forge found. 484 00:33:02,754 --> 00:33:06,194 So with statistics, for each 485 00:33:06,414 --> 00:33:12,654 relation, each table, a flag will be... There is a, like a relpages flag, right? 486 00:33:13,304 --> 00:33:14,520 - There is also- - Right 487 00:33:14,520 --> 00:33:16,824 Nik: it's not, it's not a flag, it's a number. 488 00:33:16,824 --> 00:33:19,804 It's, it's an estimate how many pages, how many, 489 00:33:20,644 --> 00:33:26,184 eight kilobyte pages this relation has for a planner to take into account. 490 00:33:26,364 --> 00:33:33,144 And there is, there is also another estimate which is called a relallvisible, 491 00:33:33,844 --> 00:33:37,903 which means how many pages are marked all visible in visibility maps. 492 00:33:38,084 --> 00:33:44,304 So the thing is that this mechanism in Postgres 18 dumps that number, 493 00:33:44,744 --> 00:33:49,784 and our restore will catch it and restore it. And the planner will think that, okay, 494 00:33:49,784 --> 00:33:55,904 we have like, say like 100,000 pages, and all of them are visible, it will say. 495 00:33:55,904 --> 00:33:59,584 For example, ideal case, or half of them are, are visible. 496 00:34:00,103 --> 00:34:05,164 But visibility maps are not yet built. So the planner will choose, for example, 497 00:34:05,164 --> 00:34:10,384 index only scan, and will get the worst possible heap fetches number. 498 00:34:10,384 --> 00:34:16,884 It will inspect every heap, page, like behaving like index scan, 499 00:34:17,344 --> 00:34:22,524 which is very unexpected, and it's like skewed result for a planner, right? 500 00:34:23,684 --> 00:34:27,824 So I actually talked about this to Andrey Borodin this morning, which is I, 501 00:34:27,824 --> 00:34:32,544 I usually go to Andrey Borodin, to talk about planner behavior. 502 00:34:32,564 --> 00:34:36,844 And he said, "Well, yes, somebody needs to implement, 503 00:34:37,024 --> 00:34:41,724 like to split and distinguish different kinds of statistics," right? 504 00:34:42,044 --> 00:34:46,504 And then I thought, this is terrible. This is just a bug. This is a mistake they did. 505 00:34:46,504 --> 00:34:49,024 I, I found a, a discussion in Hackers 506 00:34:49,284 --> 00:34:54,314 where it was decided to keep this number and in like, to preserve it in the dumps, 507 00:34:54,894 --> 00:34:58,434 but I thought like, why? It's obviously a mistake. 508 00:34:58,434 --> 00:35:03,454 You, you have all visible number, but you don't have visibility map. 509 00:35:03,914 --> 00:35:07,855 You fool yourself basically, right? You, you fool your own planner. 510 00:35:08,194 --> 00:35:13,054 But then I realized that this feature was not developed 511 00:35:13,394 --> 00:35:18,174 for dumping and restoring statistics for regular dumps, logical, logical backups, 512 00:35:18,174 --> 00:35:21,754 right? It was developed specifically for pg_upgrade. 513 00:35:22,494 --> 00:35:25,194 - Right. - Where visibility map is preserved. 514 00:35:25,194 --> 00:35:26,494 [laughs] 515 00:35:26,494 --> 00:35:28,134 - Right. Yeah. - Yeah, that's, 516 00:35:28,134 --> 00:35:32,714 that's what... Like this was insight which was provoked by our forge system. 517 00:35:32,714 --> 00:35:36,614 I, I just love how exploring Postgres these days looks like. 518 00:35:36,614 --> 00:35:39,554 You throw some questions, you have experiments. 519 00:35:39,554 --> 00:35:45,334 Our forge, system, it conducts experiments on dedicated machines like 520 00:35:46,024 --> 00:35:48,804 Explore the different kinds of Postgres 521 00:35:49,224 --> 00:35:55,184 versions and also the master branch, everything. And then you actually... 522 00:35:55,184 --> 00:35:59,524 I always have, after first wave, I always have even more, more questions. 523 00:35:59,524 --> 00:36:00,864 - [laughs] - Right. 524 00:36:00,864 --> 00:36:05,804 Nik: And I just, I just love how, like, I, I can throw as many questions as possible, 525 00:36:05,804 --> 00:36:09,564 because I always had those questions, but it took a lot of time. 526 00:36:09,564 --> 00:36:15,084 I, I I, I I didn't have time. Now I do. I just throw it and return in a few hours. 527 00:36:15,084 --> 00:36:19,344 I have more answers and more questions until I'm fully satisfied. 528 00:36:19,524 --> 00:36:23,764 I cannot say I'm fully satisfied on this topic. It's enough for our discussion, 529 00:36:23,764 --> 00:36:26,664 but I think this is important operational topic, right? 530 00:36:26,664 --> 00:36:30,144 It, it basically concerns every Postgres 531 00:36:30,144 --> 00:36:32,484 - setup, because dump restore- - Yeah 532 00:36:32,484 --> 00:36:37,308 Nik: are important tools, right? And also copy. You just copy data from somewhere, 533 00:36:37,424 --> 00:36:40,324 and you, you do need the statistics and visibility map. 534 00:36:40,324 --> 00:36:45,924 So, okay, that's why actually it's disabled by default. Don't use it. 535 00:36:45,924 --> 00:36:47,884 [laughs] Actually, my recommendation, 536 00:36:47,884 --> 00:36:53,054 don't use dump restore of statistics except for pg_upgrade. I mean- 537 00:36:53,054 --> 00:36:56,010 - I completely agree with that. - Yeah. Well, it should- 538 00:36:56,010 --> 00:36:57,414 - In part- - It should, yeah 539 00:36:57,414 --> 00:37:02,234 Kirk: partially because of the number you just pointed out, but also keep in mind 540 00:37:02,234 --> 00:37:08,874 that after you did a pg_restore, you got fresh, raw data all visible, okay? 541 00:37:08,874 --> 00:37:12,474 Like, this, this just came in, and it, it's... 542 00:37:12,474 --> 00:37:16,494 it filled the blocks, and it loaded this stuff. 543 00:37:16,494 --> 00:37:20,934 Everything should feel really clean at this point, okay? 544 00:37:20,934 --> 00:37:25,034 Even the toast tables that you started out empty, right? 545 00:37:25,034 --> 00:37:29,294 So as it created every single one of these rows, and it used a pretty optimized way 546 00:37:29,294 --> 00:37:30,414 - to do- - Well- 547 00:37:30,434 --> 00:37:32,694 - it grew it nicely. - Yeah. 548 00:37:32,694 --> 00:37:34,114 Kirk: But that is completely 549 00:37:34,114 --> 00:37:37,814 different than whatever you were seeing on your old production system. 550 00:37:37,814 --> 00:37:41,594 Nik: Well, this is an interesting topic. So you say, like, for copy itself 551 00:37:41,594 --> 00:37:45,914 should take care of it, not a separate VACUUM ANALYZE at the end of restore 552 00:37:45,914 --> 00:37:49,254 operation, but copy itself could take care of it, right? 553 00:37:49,894 --> 00:37:53,190 - Well, this mode of copy, yes. - So there is such mode. 554 00:37:53,190 --> 00:37:54,840 Kirk: I wouldn't want copy if you 555 00:37:54,840 --> 00:37:57,334 - just added records by using copy. - Yeah. Right. 556 00:37:57,334 --> 00:38:01,800 Kirk: I wouldn't want it to do this. But the pg_restore construct, 557 00:38:01,800 --> 00:38:05,590 right? The... If you're... And they do it in a single transaction, by the way. 558 00:38:05,590 --> 00:38:07,074 - I dug into the code. - Yeah. 559 00:38:07,074 --> 00:38:11,494 Kirk: They truncate the table. They begin a transaction, they truncate the table, 560 00:38:11,494 --> 00:38:14,134 - and then they do the copy data- - Right 561 00:38:14,134 --> 00:38:17,794 Kirk: on the restore, on the pg_restore side, so that if it fails, 562 00:38:17,794 --> 00:38:19,894 - it rolls back to empty- - Right. Right 563 00:38:19,894 --> 00:38:22,480 - which is actually a faster fail. - So my glasses- 564 00:38:22,480 --> 00:38:25,030 - And so it's very happy - also suggests something. 565 00:38:25,030 --> 00:38:29,134 There is COPY FREEZE. I actually learned... I, I, I, I forgot about it. 566 00:38:29,134 --> 00:38:33,294 I learned about it some long, like, time ago, because it's it's an old thing, 567 00:38:33,294 --> 00:38:35,634 - COPY FREEZE. So copy- - Yeah 568 00:38:35,634 --> 00:38:40,034 Nik: can have freeze option, and it'll run vacuum freeze of building visibility 569 00:38:40,034 --> 00:38:44,320 map, for both bits. As I mentioned, there are two bits for every page. 570 00:38:44,320 --> 00:38:48,574 All visible mean, means that all tuples in this page are visible to all transactions, 571 00:38:48,574 --> 00:38:55,424 and all frozen, which means that all transaction IDs, xmin, xmax, xmin, right? 572 00:38:55,424 --> 00:38:59,204 They are in the past. Even if they might look like from the future, 573 00:38:59,204 --> 00:39:03,204 they are in the past. This is freezing. Important job because... 574 00:39:03,204 --> 00:39:08,004 for vacuum because we have four-byte transaction IDs. 575 00:39:08,324 --> 00:39:14,984 So vacuum from time to time needs to process tuples and pages and freeze them. 576 00:39:14,984 --> 00:39:20,564 Like, meaning that don't look at this number in terms of deciding is it... 577 00:39:20,564 --> 00:39:24,604 Like, it looks like big one, but it's from, not from future. It's from the past. 578 00:39:24,604 --> 00:39:26,604 It's to to avoid transaction ID 579 00:39:26,604 --> 00:39:28,770 - wraparound. - Right. Right 580 00:39:28,770 --> 00:39:30,864 - copy freeze- - So that's interesting, though. 581 00:39:30,864 --> 00:39:33,644 - Yeah. - It's not implemented in pg_restore- 582 00:39:33,650 --> 00:39:35,050 - Well- - to have it auto use- 583 00:39:35,050 --> 00:39:36,184 - Right - vacuum freeze. 584 00:39:36,184 --> 00:39:39,724 - I think if you're- - And, or the COPY FREEZE, and it could. 585 00:39:39,724 --> 00:39:43,424 Nik: Well, it, maybe it could be implemented in pg_dump because it's a 586 00:39:43,424 --> 00:39:46,124 - copy option. So pg_dump could generate- - Right 587 00:39:46,124 --> 00:39:49,224 Nik: COPY FREEZE, or maybe you are right. Maybe we could just, 588 00:39:49,224 --> 00:39:54,024 substitute COPY commands with COPY FREEZE. There are limitations for COPY FREEZE. 589 00:39:54,324 --> 00:39:58,564 It, it should be, like, table should be created right here in this transaction, 590 00:39:58,564 --> 00:40:01,064 or truncate should happen this transaction. 591 00:40:01,064 --> 00:40:05,130 So I think, yeah, COPY FREEZE could be used if it's pg_restore 592 00:40:05,130 --> 00:40:09,130 - with single transaction option maybe. - Not even with your single transaction. 593 00:40:09,130 --> 00:40:11,330 - Yeah. - Here's the deal. Because it allows 594 00:40:11,330 --> 00:40:14,304 for the, according to you, the truncate version, 595 00:40:14,304 --> 00:40:20,164 and we know pg_restore does truncate first in the same transaction, then the copy, 596 00:40:20,164 --> 00:40:23,024 I think it, I think you could get it to fly. 597 00:40:23,164 --> 00:40:27,044 Nik: Yeah. As I said, I forgot about this option. Relearned it this morning. 598 00:40:27,044 --> 00:40:30,544 So I cannot say if I see improvements. 599 00:40:30,544 --> 00:40:34,064 I, I feel there should be improvements for modern Postgres 600 00:40:34,584 --> 00:40:37,864 to provide more options maybe to reconsider defaults, 601 00:40:37,884 --> 00:40:42,264 but this is what you wanted. Like, to right after restore, we already in very, 602 00:40:42,264 --> 00:40:48,604 like, clean state, all visible, all frozen. We are good. Performance is great. 603 00:40:48,604 --> 00:40:54,184 Kirk: Oh, yeah, yeah, yeah. So, so maybe it's COPY FREEZE space VM 604 00:40:54,384 --> 00:40:58,144 for visibility map because again, everything's visible. 605 00:40:58,384 --> 00:41:01,484 Nik: Yeah, but... And also don't forget statistics. 606 00:41:01,784 --> 00:41:02,910 - Well- - Yeah, yeah 607 00:41:02,910 --> 00:41:04,980 yeah. Well, yeah. That would have to come after. 608 00:41:04,980 --> 00:41:07,080 - That asynchronous. - And what's cool is then you don't 609 00:41:07,080 --> 00:41:10,384 actually need the vacuum portion of the VACUUM ANALYZE. 610 00:41:10,384 --> 00:41:12,796 You really only need the analyze portion. 611 00:41:13,004 --> 00:41:16,724 Nik: Right, but statistics actually for many index only scans, 612 00:41:16,724 --> 00:41:18,994 it's not super, impor- like, it's important, 613 00:41:18,994 --> 00:41:21,834 but it's not that important as having visibility maps, 614 00:41:21,834 --> 00:41:25,214 because this is a performance killer for index only scans. 615 00:41:25,214 --> 00:41:27,974 - Yeah. - Slow count, slow, like, 616 00:41:28,460 --> 00:41:31,710 slow index only scans basically, right? When, when you need just- 617 00:41:31,710 --> 00:41:32,814 - Right. Right - find- 618 00:41:32,814 --> 00:41:35,167 - And since- - you have some join. Yeah. 619 00:41:35,170 --> 00:41:39,020 And since some of that gets created when the index gets created, right? 620 00:41:39,020 --> 00:41:42,854 'Cause it does some of the analysis while it's doing the index, then- 621 00:41:43,454 --> 00:41:48,594 Nik: When it's doing index, index... Well, yeah. It, it, as we've said, it's, 622 00:41:48,874 --> 00:41:52,710 - building, hint bits This- - Yeah. Okay 623 00:41:52,710 --> 00:41:55,360 Nik: tuple headers. It, it's not building, statistics 624 00:41:55,360 --> 00:41:58,610 - Ah, but now additional statistics. - Right. And in Postgres 19, 625 00:41:58,610 --> 00:42:03,672 there is addition. By the way, interesting thing is that hint bits, 626 00:42:03,832 --> 00:42:08,692 if you don't have data checksums and wal_log_hints enabled, it's old state. 627 00:42:08,692 --> 00:42:12,912 I hope nobody... Like, I hope everyone has data checksums enabled these days. 628 00:42:12,912 --> 00:42:14,930 It's not so, I know it's not so, but 629 00:42:14,930 --> 00:42:18,232 still, modern states should be like data checksums aren't enabled, 630 00:42:18,232 --> 00:42:21,812 so you don't think about that. But as I said, in the, in the past, 631 00:42:21,812 --> 00:42:25,512 we had situations when every standby needs to build, 632 00:42:25,952 --> 00:42:29,572 mark those hint bits for all tuples, separately. 633 00:42:29,572 --> 00:42:31,592 Agent: Excuse me, Kirk and Nik. 634 00:42:32,312 --> 00:42:37,652 We are at 45 minutes. Time to start wrapping up with your closing takeaway. 635 00:42:38,051 --> 00:42:43,762 Nik: Thank you, agent. Every standby should build, needs to build, separately, 636 00:42:43,762 --> 00:42:48,042 and causing writes. It's unexpected sometimes, and select is slow. 637 00:42:48,182 --> 00:42:54,362 And also, if you do it now, if you don't have indexes, indexes who are, will, 638 00:42:54,362 --> 00:43:00,532 will revisit tuples, read them, and cause hint bits to be created. 639 00:43:00,532 --> 00:43:06,192 Pages for heap are dirted, and go, they go to WAL, they flush. This is modern state, 640 00:43:06,192 --> 00:43:11,492 right? But if you, if you don't have indexes and you for example, 641 00:43:11,492 --> 00:43:13,612 read from the table with sequential scan, 642 00:43:13,612 --> 00:43:20,212 first read is terribly slow because of those if in hint bits being built, I mean, 643 00:43:20,212 --> 00:43:26,072 this is caching, so first it's very slow. Subsequent, sequential scans will be fast. 644 00:43:26,632 --> 00:43:31,612 And in Postgres 19, there is optimization. Optimization says that 645 00:43:33,252 --> 00:43:38,432 not only hint bits can be created by select, by reading 646 00:43:38,591 --> 00:43:41,672 at read time, but also visibility maps. 647 00:43:42,912 --> 00:43:44,432 - Oh. - Yes. 648 00:43:44,932 --> 00:43:47,692 Kirk: But that's a lot of writing to do a query. 649 00:43:47,692 --> 00:43:51,652 Nik: It's not a lot of writing. It's just one bit for one page. 650 00:43:52,192 --> 00:43:53,772 - Right. - Eight kilobyte page, 651 00:43:53,772 --> 00:43:57,552 so roughly like if you have eight gigabytes, okay, it's a million bits. 652 00:43:57,552 --> 00:44:03,555 But that's not a lot, very roughly. The limitation, unlike, hint bits 653 00:44:04,532 --> 00:44:09,432 may happen on standby. May happen only on the primary, right? 654 00:44:10,282 --> 00:44:16,082 Postgres 19 feature. And I was thinking about this. Is it helpful? Is it... 655 00:44:16,082 --> 00:44:17,742 Like, in our very context, 656 00:44:18,522 --> 00:44:24,742 can we say we need less this need of VACUUM ANALYZE after restore or, or no? 657 00:44:24,742 --> 00:44:29,762 Maybe yes, but maybe no. The problem is it it, it's only allowed on the primary. 658 00:44:29,802 --> 00:44:34,662 So imagine you have something you loaded, but your reads are on read replicas, 659 00:44:34,662 --> 00:44:39,052 - on standbys. They won't do it, right? - Mm. 660 00:44:39,052 --> 00:44:42,672 Nik: So it's, it's some, some improvement, 661 00:44:43,622 --> 00:44:49,182 but it's like limited. And also it will make your first read slower, like I agree. 662 00:44:49,182 --> 00:44:52,888 It's maybe not that much slower as hint bits, but still slower. 663 00:44:53,392 --> 00:44:57,822 It will, cause additional writes. Yeah. 664 00:44:57,822 --> 00:45:02,082 And index only scan first time will be slow still, even if it's on the primary. 665 00:45:02,082 --> 00:45:05,322 Kirk: Well, that's... Right. All right. Good. 666 00:45:05,322 --> 00:45:07,542 So let's, let's work on wrapping this up. 667 00:45:07,542 --> 00:45:11,366 I think the key takeaways are, first thing I learned, by the way, 668 00:45:11,412 --> 00:45:17,392 only turn on data checksums if your data is important to you. Okay? 669 00:45:17,392 --> 00:45:20,540 - I can... [laughs] - [laughs]- 670 00:45:20,540 --> 00:45:21,930 - Yeah - if you honestly 671 00:45:21,930 --> 00:45:24,440 - don't care about database corruption- - fsync off 672 00:45:24,440 --> 00:45:27,052 that got out there and nobody noticed for years- 673 00:45:27,052 --> 00:45:30,592 Nik: fsync off. Fsync off. Unlock tables. No WAL. 674 00:45:30,592 --> 00:45:32,632 - Ah. Yeah. There you go. - [laughs] 675 00:45:32,632 --> 00:45:35,652 Kirk: Yes. Yes. Only, only for unimportant, 676 00:45:35,652 --> 00:45:39,672 only for unimportant databases should you, should you turn it off. 677 00:45:39,672 --> 00:45:42,192 So otherwise it should be on. And the, 678 00:45:42,192 --> 00:45:44,772 - Means always should be on. - Yes. 679 00:45:44,780 --> 00:45:45,872 - Right? - Absolutely. 680 00:45:45,872 --> 00:45:50,983 Nik: Because- My, my AI told me that some other AIs might be listening 681 00:45:50,983 --> 00:45:52,683 - to our materials- - Uh-huh 682 00:45:52,683 --> 00:45:54,863 - including even podcasts. - Right. 683 00:45:54,863 --> 00:45:57,370 Nik: And I'm not sure if they understand humor. 684 00:45:57,370 --> 00:45:58,470 - Or sarcasm. - Right. 685 00:45:58,470 --> 00:45:59,470 - Right. - Right. 686 00:45:59,470 --> 00:46:00,360 - Right. - So let's- 687 00:46:00,360 --> 00:46:03,120 - Yeah. That could still be sarcasm - clarify. Data checksums should be 688 00:46:03,120 --> 00:46:04,163 enabled. [laughs] 689 00:46:04,163 --> 00:46:08,843 Kirk: Yes. Always. Always. Because you will get data corruption without them, 690 00:46:08,843 --> 00:46:12,243 and worse, you won't notice it until it gets bad. Okay? 691 00:46:12,243 --> 00:46:16,382 So now backing into our side of the conversation here. 692 00:46:16,382 --> 00:46:21,682 Everybody should know that when you do a pg_restore, that there's a, there's a, 693 00:46:21,682 --> 00:46:27,602 a need to do some kind of VACUUM ANALYZE to get everything brought up to date 694 00:46:27,602 --> 00:46:32,602 so that your system runs the way it should run once you turn it on and enable it. 695 00:46:32,602 --> 00:46:36,792 And that, first off, walk away with that. That's your key takeaway. 696 00:46:36,852 --> 00:46:42,192 Second takeaway I think we have is we have an idea to revisit this in the hackers 697 00:46:42,192 --> 00:46:48,112 and maybe add a, an option to pg_restore to do a VACUUM ANALYZE so that it does it 698 00:46:48,112 --> 00:46:52,472 automatically, and the agents start doing it automatically too. 699 00:46:52,532 --> 00:46:57,312 And then we can put this to rest because we're gonna start up in a known state, 700 00:46:57,332 --> 00:46:59,932 - and we can then do tests. - Mm-hmm. 701 00:46:59,932 --> 00:47:02,092 Kirk: So, so this is big. And then third, 702 00:47:02,092 --> 00:47:07,332 we had some secondary and tertiary ideas of maybe we do copy, 703 00:47:07,332 --> 00:47:13,212 Inside of the pg_restore, we can look to say, "Hey, can we do the version of copy 704 00:47:13,212 --> 00:47:18,760 that gets us, so that it includes... I'm sorry, I forgot the word. COPY FREEZE? 705 00:47:18,760 --> 00:47:21,660 - COPY FREEZE. Yeah. - And potentially, you know, 706 00:47:21,660 --> 00:47:26,320 see if there's a way that, hey, maybe we can augment this so it writes a visibility 707 00:47:26,320 --> 00:47:31,560 map at the end of restoring the table. That would eliminate the vacuum portion 708 00:47:31,630 --> 00:47:34,980 of the VACUUM ANALYZE that we do at the end, and then we could just 709 00:47:34,980 --> 00:47:37,820 - do an analyze on the database. - Right. I see what you're saying. 710 00:47:37,820 --> 00:47:42,310 Kirk: And then again, vacuumdb is the better choice because the -j option allows 711 00:47:42,310 --> 00:47:46,470 - you to run multiple concurrently. - And you need to think about partition 712 00:47:46,470 --> 00:47:50,630 tables if you use vacuumdb before Postgres 19. 713 00:47:50,630 --> 00:47:54,110 Kirk: Yes. Yes. And, and that's... And, and let's add that too. 714 00:47:54,110 --> 00:47:56,390 Let's, let's, let's look at correcting 715 00:47:56,390 --> 00:47:59,070 - vacuumdb so that it actually- - Right 716 00:47:59,070 --> 00:48:00,070 - handles- - Right 717 00:48:00,070 --> 00:48:04,120 - the partition tables the correct way. - Well, it's already done by Laurenz Albe 718 00:48:04,120 --> 00:48:05,660 for Postgres 19. Yeah. 719 00:48:05,660 --> 00:48:07,010 - Oh, for vacuumdb? - Yeah. 720 00:48:07,010 --> 00:48:08,070 - Not just- - Yeah. 721 00:48:08,070 --> 00:48:11,170 - Okay. Perfect. - vacuumdb analyze only and analyze 722 00:48:11,170 --> 00:48:14,400 option, they, they take care of parent tables, yeah, in 19. 723 00:48:14,400 --> 00:48:15,750 - Perfect. - But before 19- 724 00:48:15,750 --> 00:48:17,530 - 19 - it's still a problem. Yeah. 725 00:48:17,530 --> 00:48:21,930 Kirk: So I'd like to give a hat tip to Laurenz. He was one of the first people to 726 00:48:21,930 --> 00:48:26,820 help train our team and work with us from CYBERTEC. He's a great guy. 727 00:48:26,820 --> 00:48:29,100 I'm glad to see him in the community, so. 728 00:48:29,100 --> 00:48:35,000 Nik: Yeah. That's great. One thing you forgot, I still think in hacking, hacking, 729 00:48:35,000 --> 00:48:40,000 conclusion, I think pg_dump could have just a single 730 00:48:40,680 --> 00:48:43,560 - notice or warning at the end. - Yeah. 731 00:48:43,560 --> 00:48:45,320 - Yeah, no, thank you for catching that. - Reminder. 732 00:48:45,320 --> 00:48:46,120 - Yeah. - Reminder. 733 00:48:46,120 --> 00:48:51,380 Kirk: pg_dump- should output something that at the end of the pg_restore shows up 734 00:48:51,380 --> 00:48:55,360 on the screen via the error, you know, standard error, 735 00:48:55,360 --> 00:48:59,880 that actually reminds you that, hey, if you didn't run this with, you know, 736 00:48:59,880 --> 00:49:04,820 VACUUM ANALYZE or whatever switch that was, you're missing this, and specifically 737 00:49:04,820 --> 00:49:07,120 - for the SQL version- - Right 738 00:49:07,120 --> 00:49:08,660 - of the restore. - Right. It's- 739 00:49:08,660 --> 00:49:10,920 - Where it will- - not purely Linux way, 740 00:49:10,920 --> 00:49:14,880 because Linux way is just keep to be silent and don't talk too much, right? 741 00:49:14,880 --> 00:49:16,130 - But for agents- - Right 742 00:49:16,130 --> 00:49:18,430 I think it would be helpful. And finally, 743 00:49:18,430 --> 00:49:22,640 I think this is great summary. Thank you so much. I I... Finally, I think we should 744 00:49:22,640 --> 00:49:27,810 say platforms should revisit their docs, right? Docs. 745 00:49:27,810 --> 00:49:30,990 - Platforms should revisit their docs. - Docs. Yeah. 746 00:49:31,630 --> 00:49:32,983 - Yeah. Yeah. - That's- 747 00:49:33,210 --> 00:49:37,430 Kirk: Kudos to the, kudos to the handful, like you mentioned. But the rest of you 748 00:49:37,430 --> 00:49:39,940 - guys who are documenting this stuff- - Including- 749 00:49:39,940 --> 00:49:42,350 this should at least be raised as an issue. 750 00:49:42,350 --> 00:49:47,470 Nik: Yeah. RDS and Cloud SQL, which is a quite big, Postgres managed platforms, 751 00:49:47,470 --> 00:49:49,130 they, they don't have it at all. 752 00:49:49,130 --> 00:49:52,670 Like, they don't mention analyze and vacuum. Yeah. Okay. 753 00:49:52,670 --> 00:49:56,190 - Right. - Finally, I, I, I must admit once again 754 00:49:56,190 --> 00:50:01,750 how I enjoy working, exploring Postgres these days with our new tooling we have. 755 00:50:01,840 --> 00:50:05,990 Which originally we started using only for our, like, work with customers, but now, 756 00:50:05,990 --> 00:50:11,750 like, I, I think I'm looking forward to more findings and new interesting topics. 757 00:50:12,490 --> 00:50:17,410 So yeah. And thank you, Kirk, for joining. That was great, interesting discussion. 758 00:50:17,730 --> 00:50:20,810 - See you next time. - Absolutely. Thank you, Nik. 759 00:50:20,810 --> 00:50:21,890 Goodbye, everyone. 760 00:50:21,920 --> 00:50:27,864 [music] 761 00:50:27,864 --> 00:50:33,808 [music] 762 00:50:33,808 --> 00:50:39,752 [music] 763 00:50:39,752 --> 00:50:45,696 [music] 764 00:50:45,696 --> 00:50:51,640 [music]