1 00:00:00,060 --> 00:00:02,240 Michael: Hello and welcome to Postgres.FM, a weekly show about 2 00:00:02,240 --> 00:00:03,100 all things PostgreSQL. 3 00:00:03,280 --> 00:00:06,020 I am Michael, founder of pgMustard, and as usual, I'm joined 4 00:00:06,020 --> 00:00:07,580 by Nikolay, founder of Postgres.AI. 5 00:00:07,580 --> 00:00:08,300 Hey, Nikolay. 6 00:00:08,680 --> 00:00:09,440 Nikolay: Hi, Michael. 7 00:00:09,860 --> 00:00:13,780 Michael: And today we have a special guest, Tony from Cybertec, 8 00:00:13,860 --> 00:00:17,560 a Postgres developer there, who is responsible for, among other 9 00:00:17,560 --> 00:00:19,820 things, the pg_squeeze extension. 10 00:00:20,460 --> 00:00:21,760 So, welcome, Tony. 11 00:00:22,240 --> 00:00:23,540 Antonín: Hello, thanks for the invitation. 12 00:00:24,620 --> 00:00:25,700 Michael: It is our pleasure. 13 00:00:26,120 --> 00:00:29,320 So, today we are going to be talking all things pg_squeeze. 14 00:00:29,860 --> 00:00:33,620 Perhaps Tony could give us an overview of what it is. 15 00:00:34,240 --> 00:00:39,440 Antonín: So pg_squeeze is a Postgres extension that does similar 16 00:00:39,480 --> 00:00:43,940 things like the CLUSTER command or the VACUUM with a FULL option, 17 00:00:44,500 --> 00:00:48,280 except that it does not require the exclusive locking of the 18 00:00:48,280 --> 00:00:48,780 table. 19 00:00:48,960 --> 00:00:54,560 In fact, it needs the exclusive lock only for a very short time 20 00:00:54,560 --> 00:00:59,820 at the end of the processing, but it's actually a tool that lets 21 00:00:59,820 --> 00:01:02,940 users get rid of table bloat. 22 00:01:03,740 --> 00:01:06,060 Michael: Yeah, it's hugely powerful, isn't it? 23 00:01:06,060 --> 00:01:09,440 So online bloat reduction of tables. 24 00:01:09,960 --> 00:01:11,060 Antonín: Yes, basically that. 25 00:01:11,060 --> 00:01:14,020 Michael: Yeah, big, big problem and it's a really powerful tool 26 00:01:14,020 --> 00:01:17,980 and I think a lot of our listeners will be familiar with pg_repack, 27 00:01:18,120 --> 00:01:20,460 which I think originated from a... 28 00:01:21,340 --> 00:01:23,560 Nikolay, you can probably tell me what it was originally called, 29 00:01:23,560 --> 00:01:25,240 a reorg or something like that? 30 00:01:25,240 --> 00:01:26,620 Nikolay: Yeah, something like that. 31 00:01:27,260 --> 00:01:30,520 It already doesn't matter, it's an ancient name. 32 00:01:31,060 --> 00:01:34,540 Michael: Yeah, but pg_squeeze, you also have like a bunch of 33 00:01:34,540 --> 00:01:38,200 other features and it's done in a more modern way in terms of 34 00:01:38,200 --> 00:01:38,700 how... 35 00:01:38,920 --> 00:01:41,760 Maybe you could tell us a little bit about how it's implemented 36 00:01:41,880 --> 00:01:43,940 and how long you've been working on it. 37 00:01:44,100 --> 00:01:46,860 Antonín: Yes, the motivation was actually to use... 38 00:01:47,080 --> 00:01:50,960 Well, it was an assignment, it was a task that I got from my employer 39 00:01:51,140 --> 00:01:55,880 and the idea was to adjust pg_repack, the existing pg_repack 40 00:01:56,040 --> 00:01:59,720 extension so it can be scheduled, so it can run according to 41 00:01:59,720 --> 00:02:00,560 schedule automatically. 42 00:02:01,020 --> 00:02:07,040 But when I investigated the pg_repack code, I realized that it's 43 00:02:07,040 --> 00:02:09,440 implemented in 2 parts. 44 00:02:09,520 --> 00:02:13,220 Part of the logic is on the server and the other part is on a 45 00:02:13,220 --> 00:02:13,720 client. 46 00:02:14,440 --> 00:02:18,280 And I realized that the client part is not really simple. 47 00:02:18,280 --> 00:02:20,780 It does some tricky things with connections. 48 00:02:21,280 --> 00:02:23,860 It even terminates connections at some point. 49 00:02:24,140 --> 00:02:29,760 So I realized that if it should be executed from background worker, 50 00:02:29,760 --> 00:02:33,800 it would be quite strange because the background worker would 51 00:02:33,800 --> 00:02:37,540 have to use the client library to connect to server. 52 00:02:37,640 --> 00:02:42,380 So the server would have to connect to itself over client, over 53 00:02:42,380 --> 00:02:43,860 the libpq library. 54 00:02:43,980 --> 00:02:49,240 So The fact that the client is not trivial made it almost impossible 55 00:02:49,240 --> 00:02:53,460 to implement the functionality in the background worker. 56 00:02:54,320 --> 00:02:59,540 So that was 1 reason I thought it's better to start from scratch. 57 00:03:00,360 --> 00:03:04,400 And once I was considering doing everything from scratch, I got 58 00:03:04,400 --> 00:03:07,360 opportunities to do some things differently. 59 00:03:08,300 --> 00:03:11,880 Nikolay: Yeah, just instead of replacing files, you decided to 60 00:03:11,880 --> 00:03:13,980 use logical replication, right? 61 00:03:14,500 --> 00:03:21,020 Because By that time it already became quite mature, unlike the 62 00:03:21,020 --> 00:03:24,160 moment when pg_rework and pg_repack was created originally. 63 00:03:24,720 --> 00:03:27,480 There was no logical replication yet at all. 64 00:03:28,380 --> 00:03:29,240 Is this right? 65 00:03:29,380 --> 00:03:32,380 Antonín: Yes, pg_repack uses triggers. 66 00:03:34,560 --> 00:03:39,360 Maybe I should tell shortly how both extensions work. 67 00:03:39,860 --> 00:03:46,720 The common part is that they copy the useful data from the table 68 00:03:46,720 --> 00:03:49,400 into a new table and then swap the files. 69 00:03:50,080 --> 00:03:55,060 Each table is, the table contents is stored in a file, so the 70 00:03:55,060 --> 00:04:00,480 extensions copy the useful data and then switch the links to 71 00:04:00,480 --> 00:04:01,200 the files. 72 00:04:01,800 --> 00:04:04,900 Nikolay: All Right, also accumulating changes in some Delta table 73 00:04:04,900 --> 00:04:09,360 and applying them this can be a headache itself. 74 00:04:10,380 --> 00:04:16,280 Antonín: This is where pg_repack uses triggers And around the time I 75 00:04:16,280 --> 00:04:21,820 was considering writing such an extension from scratch, there 76 00:04:21,820 --> 00:04:25,340 was a new feature in Postgres and it was the logical decoding. 77 00:04:25,520 --> 00:04:29,120 I think the logical replication was not yet implemented at the 78 00:04:29,120 --> 00:04:34,180 moment, but I thought it could be useful for this purpose, so 79 00:04:34,400 --> 00:04:39,900 I spent quite some time studying how it works, and then I decided 80 00:04:40,360 --> 00:04:41,820 to use the logical decoding. 81 00:04:42,360 --> 00:04:47,680 I considered it more, I will say compact or I didn't like the 82 00:04:47,680 --> 00:04:52,820 idea to construct well the pg_repack somehow has to create the 83 00:04:52,820 --> 00:04:57,340 triggers so it plays with the SQL with the DDL statements it 84 00:04:57,340 --> 00:05:03,900 has to construct the DDL statements to create the temporary table 85 00:05:03,900 --> 00:05:07,700 as well as the triggers and I thought this is quite complex and 86 00:05:08,760 --> 00:05:15,140 error prone So that's why I preferred the logical decoding 87 00:05:16,020 --> 00:05:19,440 Michael: I think logical decoding should be lower impact as well 88 00:05:19,440 --> 00:05:24,020 in terms of overhead, so it feels like a lighter weight way of 89 00:05:24,020 --> 00:05:25,460 doing it as well, to me. 90 00:05:25,680 --> 00:05:26,680 Is that fair? 91 00:05:27,700 --> 00:05:31,740 Antonín: Yes, I think the fact that it works at a lower level 92 00:05:31,740 --> 00:05:35,820 in the stack should even be probably more efficient. 93 00:05:36,900 --> 00:05:41,620 Nikolay: Yeah, because in case of PjRepaq, any write during initialization 94 00:05:42,980 --> 00:05:46,720 of during this massive copy using, I think it uses create table 95 00:05:46,720 --> 00:05:47,420 as select. 96 00:05:47,880 --> 00:05:53,300 When we do it, all the writes to this table also go to this delta 97 00:05:53,300 --> 00:05:54,440 table using triggers. 98 00:05:54,440 --> 00:05:58,000 And basically we write it twice and it goes twice to wall while 99 00:05:58,020 --> 00:06:01,420 if we take logical recording, we just extract it from wall and 100 00:06:01,640 --> 00:06:03,060 it's written once, right? 101 00:06:03,180 --> 00:06:04,980 This is already a big difference. 102 00:06:05,280 --> 00:06:09,220 Antonín: Well, I think with pg_squeeze the insertions into the 103 00:06:09,220 --> 00:06:11,400 new table are actually locked too. 104 00:06:11,840 --> 00:06:13,540 I think there's no way to... 105 00:06:14,220 --> 00:06:19,600 Well, You could avoid the logging if the table was unlocked, 106 00:06:19,600 --> 00:06:21,420 but then it's hard to switch. 107 00:06:22,120 --> 00:06:24,900 I didn't go that far in this direction, but... 108 00:06:26,380 --> 00:06:29,480 Nikolay: I mean, in our case, in pg_squeeze, we just accumulate 109 00:06:29,540 --> 00:06:34,220 changes in the slot, and We don't write twice to 2 tables. 110 00:06:34,460 --> 00:06:39,880 This is the big difference in terms of how this delta is accumulated. 111 00:06:40,080 --> 00:06:41,060 Antonín: I see, I see. 112 00:06:41,060 --> 00:06:42,680 Yes, that's right. 113 00:06:43,260 --> 00:06:44,680 Nikolay: This is obviously a difference. 114 00:06:46,240 --> 00:06:49,960 I didn't realize that the original idea was to be able to schedule 115 00:06:49,960 --> 00:06:53,480 it and scheduling pg_repack means you do it on client using, I 116 00:06:53,480 --> 00:06:55,840 don't know, like regular Kronos or something. 117 00:06:55,840 --> 00:06:59,440 In this case, you brought it to, you brought all the interface 118 00:06:59,440 --> 00:07:03,700 to SQL and we just can schedule it using pg_cron or something, 119 00:07:03,700 --> 00:07:04,200 right? 120 00:07:04,200 --> 00:07:06,700 This is the idea, I didn't realize it. 121 00:07:06,740 --> 00:07:10,540 Or you can schedule it right with pg_squeeze, I don't know this. 122 00:07:10,680 --> 00:07:13,080 Okay, Michael is shaking his head, I understand, okay. 123 00:07:13,080 --> 00:07:15,520 Michael: No, I'm saying you can, I'm saying you can schedule 124 00:07:15,520 --> 00:07:19,700 it within pg_squeeze, it has, like, a pretty much, like, it uses 125 00:07:19,700 --> 00:07:23,540 the same syntax as crontab is that yeah? 126 00:07:23,680 --> 00:07:26,820 Nikolay: I didn't know this I'm not like I use it only a couple 127 00:07:26,820 --> 00:07:31,620 of times manually somewhere very long ago so yeah that's great 128 00:07:31,620 --> 00:07:37,580 and in general be able to run everything at SQL level, it's good. 129 00:07:37,580 --> 00:07:40,440 I mean, not CLI level, but SQL level. 130 00:07:40,680 --> 00:07:42,340 It gives freedom sometimes, right? 131 00:07:42,340 --> 00:07:46,020 You can do it in application or anywhere, in PSQL or something. 132 00:07:46,020 --> 00:07:49,340 Antonín: Yes, and it's good for development and troubleshooting. 133 00:07:51,340 --> 00:07:53,500 Nikolay: Yeah, yeah, yeah, that's great, that's great. 134 00:07:54,640 --> 00:07:55,140 Good. 135 00:07:55,520 --> 00:07:59,220 Michael: Tony, I was reading, I think you have a super fan, previous 136 00:07:59,220 --> 00:08:03,220 Cybertec employee Kaarel, he wrote a really good introduction 137 00:08:03,340 --> 00:08:03,520 post. 138 00:08:03,520 --> 00:08:05,140 I couldn't believe it was 2016. 139 00:08:06,040 --> 00:08:09,100 I mean also it had lots of these features at the beginning. 140 00:08:10,080 --> 00:08:13,440 How's it developed over time and how much have you had to keep 141 00:08:13,440 --> 00:08:14,400 working on it? 142 00:08:15,780 --> 00:08:21,100 Antonín: Well, I think there was not much functionality added 143 00:08:22,120 --> 00:08:24,520 since the initial version. 144 00:08:25,340 --> 00:08:30,020 I did some optimizations to avoid the restriction it puts on 145 00:08:30,020 --> 00:08:36,000 vacuum, because 1 problem is that the xmin horizon, you probably 146 00:08:36,000 --> 00:08:41,920 know what it means, must be prevented from advancing for some 147 00:08:41,920 --> 00:08:42,420 time. 148 00:08:42,980 --> 00:08:48,680 When the data is being copied, then this horizon must stay constant. 149 00:08:49,020 --> 00:08:53,300 It must not advance and the other problem is that... 150 00:08:53,940 --> 00:08:58,300 Nikolay: So, sorry, once again, so what exactly you did, like, 151 00:08:58,340 --> 00:09:02,880 if we create a slot, it already freezes our state in terms of 152 00:09:02,880 --> 00:09:06,780 horizon until we start consuming changes from the slot, right? 153 00:09:06,780 --> 00:09:11,040 Antonín: Yes, and I think the initial version did block this 154 00:09:11,040 --> 00:09:16,480 horizon for the entire processing, but at some point I realized 155 00:09:16,480 --> 00:09:19,920 that it's only necessary for the initial load for the copying 156 00:09:19,920 --> 00:09:26,140 of the data then it can be Then it can be allowed to advance 157 00:09:26,980 --> 00:09:29,360 Nikolay: Yeah When when it's done 158 00:09:31,240 --> 00:09:34,280 Antonín: After the load after the copying is done you only need 159 00:09:34,280 --> 00:09:40,400 to process the changes that were applied concurrently, so you 160 00:09:40,400 --> 00:09:45,260 no longer need to block the xmin horizon for this stage. 161 00:09:45,780 --> 00:09:48,000 Nikolay: This is a huge problem for huge tables. 162 00:09:48,420 --> 00:09:53,360 If we have a 1TB table, it means that this horizon is blocked 163 00:09:53,680 --> 00:09:57,180 for long and it affects the whole cluster because autovacuum 164 00:09:57,240 --> 00:10:00,440 cannot delete freshly dead tuples, as we discussed many times 165 00:10:00,440 --> 00:10:01,300 on this podcast. 166 00:10:02,200 --> 00:10:07,860 In this area, have you considered the idea of speeding up initialization? 167 00:10:08,640 --> 00:10:12,040 If, for example, we have regular int8 or, I don't know, 168 00:10:12,040 --> 00:10:16,320 like some numeric primary key, we could split the table into 169 00:10:16,320 --> 00:10:22,000 some batches or like areas, and then we could use multiple INSERT 170 00:10:22,000 --> 00:10:27,160 processes which would work in parallel, speeding up the initial 171 00:10:27,160 --> 00:10:27,660 load. 172 00:10:27,920 --> 00:10:29,120 Did you consider this? 173 00:10:31,520 --> 00:10:33,360 Antonín: No, technically I didn't have this idea. 174 00:10:34,020 --> 00:10:37,760 Do you mean processing the table in multiple iterations? 175 00:10:40,760 --> 00:10:43,480 No, I didn't get that far. 176 00:10:44,180 --> 00:10:49,060 So you say that with pg_squeeze the xmin horizon is a problem 177 00:10:49,440 --> 00:10:51,340 with the processing or in general? 178 00:10:52,960 --> 00:10:55,620 Nikolay: It's in general when working with logical replication 179 00:10:55,840 --> 00:10:57,740 decoding, it's a problem. 180 00:10:58,140 --> 00:10:59,940 Initialization is a huge problem. 181 00:11:00,040 --> 00:11:04,700 Sometimes we're dealing with huge databases, we avoid initialization 182 00:11:04,760 --> 00:11:05,260 completely. 183 00:11:05,340 --> 00:11:08,800 For example, if we want all tables, sometimes we have a couple 184 00:11:08,800 --> 00:11:12,280 of recipes how to convert physical replica to logical replica, 185 00:11:12,280 --> 00:11:14,680 just because initialization is a headache. 186 00:11:14,680 --> 00:11:16,760 It can take sometimes days. 187 00:11:18,320 --> 00:11:22,120 During this time, xmin horizon is not progressing, and not a 188 00:11:22,120 --> 00:11:23,060 vacuum is blocked. 189 00:11:24,140 --> 00:11:26,100 So I know some tools. 190 00:11:26,760 --> 00:11:27,660 I don't remember. 191 00:11:28,400 --> 00:11:35,240 Maybe PeerDB was doing this, basically, virtual partitioning 192 00:11:35,460 --> 00:11:36,380 of the table. 193 00:11:36,660 --> 00:11:40,880 Not real partitioning, but just kind of partitioning, splitting 194 00:11:40,920 --> 00:11:43,380 it to pieces and then loading. 195 00:11:43,640 --> 00:11:48,840 If we have very fast disks and a lot of CPUs, but of course we 196 00:11:48,840 --> 00:11:50,880 can speed this process up. 197 00:11:51,820 --> 00:11:55,100 And this means that xmin horizon is blocked for a shorter period. 198 00:11:55,760 --> 00:12:00,040 I think, yeah, we could consider in this product, we could consider 199 00:12:00,040 --> 00:12:01,440 this, I think, this approach. 200 00:12:01,840 --> 00:12:02,980 And pg_repack doesn't do it. 201 00:12:02,980 --> 00:12:08,700 It just creates a table as select, so it's a single process and 202 00:12:08,860 --> 00:12:14,160 it can be slow, while here we have an opportunity to speed it 203 00:12:14,160 --> 00:12:14,660 up. 204 00:12:15,660 --> 00:12:21,280 Antonín: This sounds like 1 of the cons we may discuss later, 205 00:12:21,280 --> 00:12:26,060 but from this perspective, the pg_squeeze behaves like a logical 206 00:12:26,060 --> 00:12:26,560 decoding. 207 00:12:28,660 --> 00:12:32,520 Nikolay: Yeah, and If we consider this approach in the future, 208 00:12:32,560 --> 00:12:36,220 if we think how to improve this, it's worth remembering that 209 00:12:36,220 --> 00:12:41,140 we need to synchronize all these sessions, which perform INSERTs 210 00:12:41,400 --> 00:12:44,340 of individual partitions, virtual partitions. 211 00:12:45,060 --> 00:12:48,760 We need to synchronize them using a single snapshot, right? 212 00:12:48,760 --> 00:12:51,240 And so they deal with the same data. 213 00:12:51,420 --> 00:12:52,760 How it's called, I don't remember. 214 00:12:52,760 --> 00:12:55,620 When you start a transaction, you can specify a snapshot. 215 00:12:55,900 --> 00:12:58,840 And there is a function which exports the snapshot name. 216 00:12:59,200 --> 00:13:01,040 So we can... 217 00:13:01,360 --> 00:13:02,200 It's like pg_dump. 218 00:13:02,220 --> 00:13:03,780 pg_dump does this as well. 219 00:13:05,380 --> 00:13:09,520 Antonín: Yes, pg_squeeze also uses the kind of snapshot that is 220 00:13:09,520 --> 00:13:11,020 called historic snapshot. 221 00:13:11,040 --> 00:13:15,640 It takes snapshot of the database as it was at some point in 222 00:13:15,640 --> 00:13:20,820 time, but I'm not sure this, you need for the snapshot to work 223 00:13:20,820 --> 00:13:24,780 you need to make sure that the xmin horizon does not advance 224 00:13:24,800 --> 00:13:29,920 because yeah because otherwise you must not lose you should copy 225 00:13:30,060 --> 00:13:34,180 even the DELETEd tuples that are still visible to the snapshot, 226 00:13:35,320 --> 00:13:38,800 because without that you will not be able to apply UPDATEs. 227 00:13:38,800 --> 00:13:42,340 If the tuple gets updated, you need to copy even the old version. 228 00:13:42,720 --> 00:13:46,180 Nikolay: Right, but if the slot is created already, it should 229 00:13:46,180 --> 00:13:49,420 guarantee that the snapshot is not lost, right? 230 00:13:49,640 --> 00:13:50,540 I think so. 231 00:13:50,540 --> 00:13:53,320 Antonín: Yes, this does guarantee it, but I'm just explaining 232 00:13:53,320 --> 00:13:59,120 why we cannot advance it more than what is correct. 233 00:13:59,120 --> 00:14:02,720 Michael: Also, Nikolay, I don't think this is as big a problem 234 00:14:02,720 --> 00:14:07,080 because I think you're talking about logical rep, like the slow 235 00:14:07,080 --> 00:14:10,640 initialization of a huge database, like the entire database, 236 00:14:10,640 --> 00:14:16,160 whereas it will, yeah, but just 1 table And we can be talking 237 00:14:16,160 --> 00:14:17,860 about partitions here though, right? 238 00:14:18,080 --> 00:14:21,060 If we're talking about, you're 239 00:14:21,060 --> 00:14:21,420 Nikolay: talking about 240 00:14:21,420 --> 00:14:24,440 Michael: a terabyte table, if we could get it down into 100 gigabyte 241 00:14:25,240 --> 00:14:28,940 partitions, pg_squeeze can work on a table by table basis and 242 00:14:28,940 --> 00:14:31,420 only blocking it by partition by partition. 243 00:14:32,620 --> 00:14:35,860 Nikolay: Well, right, but if it's on partition table, this can 244 00:14:35,860 --> 00:14:37,000 be a problem, right? 245 00:14:37,800 --> 00:14:41,320 But on the other side, if we're dealing with huge bloat, usually 246 00:14:41,320 --> 00:14:44,440 a terabyte table means a hundred gigabytes table. 247 00:14:45,100 --> 00:14:49,340 Because while we're copying it, it already squeezes, right? 248 00:14:50,200 --> 00:14:51,180 That's what's happening. 249 00:14:52,240 --> 00:14:55,540 Maybe this explains why this is not a big concern. 250 00:14:56,120 --> 00:14:59,700 And you're right, people should partition huge tables anyway. 251 00:15:00,780 --> 00:15:03,360 Michael: Anything you've had to do in particular around partitioning, 252 00:15:03,740 --> 00:15:04,120 Tony? 253 00:15:04,120 --> 00:15:07,440 Or is, I guess it just works like any normal tables? 254 00:15:08,140 --> 00:15:10,360 Antonín: No, I think it's just normal. 255 00:15:10,680 --> 00:15:16,760 I only checked at the beginning the kind of table must be checked. 256 00:15:16,880 --> 00:15:19,960 I don't think there's any special treatment. 257 00:15:20,360 --> 00:15:22,200 Nikolay: Yeah, unless it's TimescaleDB. 258 00:15:22,960 --> 00:15:27,100 I remember there's an issue with logical. 259 00:15:28,020 --> 00:15:33,140 There are hyper tables which are kind of like partition tables. 260 00:15:33,360 --> 00:15:35,440 Yeah, they're cool and sharp. 261 00:15:36,460 --> 00:15:39,440 Right, and with regular partitioning, we just replicate, logical 262 00:15:39,600 --> 00:15:42,080 decoding works at partition level, that's it. 263 00:15:42,640 --> 00:15:45,140 Not that's it, but basics is this. 264 00:15:45,560 --> 00:15:49,480 And I remember there's some issue with TimescaleDB, so I wonder 265 00:15:49,480 --> 00:15:53,080 if TimescaleDB is compatible with pg_squeeze. 266 00:15:53,560 --> 00:15:55,200 Maybe there are issues there, actually. 267 00:15:55,600 --> 00:15:56,040 Michael: Do you know, Tony? 268 00:15:56,040 --> 00:15:56,680 I don't know. 269 00:15:56,740 --> 00:15:57,240 Sorry. 270 00:15:57,700 --> 00:16:00,900 Antonín: No, it's OK. 271 00:16:01,400 --> 00:16:04,380 Nikolay: So I just recently learned there are issues with logical... 272 00:16:04,900 --> 00:16:08,940 We had a database with TimescaleDB, we needed to move it using 273 00:16:08,940 --> 00:16:13,020 logical, and also on the fly removing bloat, right? 274 00:16:13,080 --> 00:16:15,300 But it didn't work easy. 275 00:16:16,460 --> 00:16:17,540 So yeah. 276 00:16:17,860 --> 00:16:18,800 Michael: Good to know. 277 00:16:18,900 --> 00:16:21,260 Nikolay: I have another question in general. 278 00:16:22,200 --> 00:16:24,240 So this is extension, right? 279 00:16:24,480 --> 00:16:26,420 This is an extension like pg_repack. 280 00:16:27,880 --> 00:16:28,580 Why extension? 281 00:16:29,020 --> 00:16:30,420 It could be not extension, right? 282 00:16:30,420 --> 00:16:32,820 It could be just some bunch of... 283 00:16:33,740 --> 00:16:34,520 I don't know. 284 00:16:34,900 --> 00:16:38,860 Is there anything that requires this project to be a Postgres 285 00:16:38,900 --> 00:16:39,400 extension? 286 00:16:39,800 --> 00:16:43,000 Because if it's not an extension, you can use it anywhere. 287 00:16:45,040 --> 00:16:47,860 There are managed Postgres systems which don't have even pg_repack. 288 00:16:48,840 --> 00:16:49,820 They don't have it. 289 00:16:49,820 --> 00:16:51,140 And people suffer from load. 290 00:16:51,140 --> 00:16:54,360 I mean, not people, databases suffer from load there, and I see 291 00:16:54,360 --> 00:16:54,860 it. 292 00:16:55,200 --> 00:16:58,180 And if they don't have pg_repack, they don't have pg_squeeze, we 293 00:16:58,180 --> 00:17:02,300 cannot use this approach or we need to implement something similar 294 00:17:02,480 --> 00:17:02,980 ourselves. 295 00:17:03,000 --> 00:17:04,780 We're using logical decoding, right? 296 00:17:05,660 --> 00:17:09,520 Antonín: Well, extension is, I consider extension just a means 297 00:17:09,520 --> 00:17:15,360 of packaging, but what is important is that this code is a shared 298 00:17:15,360 --> 00:17:18,580 library because it's C code running on server. 299 00:17:19,220 --> 00:17:23,680 So it must be shared library and the easiest way to get shared 300 00:17:23,680 --> 00:17:26,680 library to the user is to make it an extension. 301 00:17:28,680 --> 00:17:31,200 Does it answer your question? 302 00:17:31,840 --> 00:17:32,760 Nikolay: Yeah, it answers. 303 00:17:33,000 --> 00:17:38,180 Yeah, I just think if for example, there is a managed service 304 00:17:38,180 --> 00:17:41,600 provider, what should they add? 305 00:17:42,040 --> 00:17:44,360 pg_repack, pg_squeeze, or just pg_squeeze? 306 00:17:45,180 --> 00:17:46,780 What's our recommendation to them? 307 00:17:46,780 --> 00:17:51,400 Because I know some managed Postgres service providers which 308 00:17:51,480 --> 00:17:58,140 become quite popular, and they lack this functionality, and they 309 00:17:58,140 --> 00:18:03,740 also don't tune into vacuum by default, which means growing projects 310 00:18:03,740 --> 00:18:07,420 accumulate a lot of load and they need some tools. 311 00:18:07,420 --> 00:18:10,680 So what like what do you think they should just consider 312 00:18:10,680 --> 00:18:14,440 pg_squeeze as an extension because it's convenient because it 313 00:18:14,440 --> 00:18:16,520 has SQL interface, right? 314 00:18:17,000 --> 00:18:20,680 Antonín: Yes, I mean pg_squeeze versus pg_repack would 315 00:18:22,140 --> 00:18:22,900 Nikolay: Well, yeah, yeah. 316 00:18:22,900 --> 00:18:23,160 Yeah. 317 00:18:23,160 --> 00:18:28,200 So so to me a SQL level interface is an ability to schedule, 318 00:18:28,200 --> 00:18:31,600 it's an obvious advantage, right? 319 00:18:31,860 --> 00:18:32,360 Yes. 320 00:18:33,460 --> 00:18:33,960 Yeah. 321 00:18:34,660 --> 00:18:39,440 So, I just said, maybe a rhetorical question, so no need to answer. 322 00:18:40,680 --> 00:18:42,940 Antonín: If you ask me, I would recommend pgsql. 323 00:18:45,040 --> 00:18:50,160 If someone likes pg_repack, then let him use it. 324 00:18:51,660 --> 00:18:53,220 Nikolay: Okay, I have another question. 325 00:18:53,480 --> 00:18:57,740 We touched a little bit this initialization and you mentioned 326 00:18:58,460 --> 00:19:01,780 some pg_repack constructs some DDL and so on. 327 00:19:02,160 --> 00:19:07,800 When pg_squeeze performs this initial load, is it possible, like 328 00:19:07,800 --> 00:19:10,440 theoretically, not right now, I understand like maybe right now 329 00:19:10,440 --> 00:19:13,200 it's not possible, but theoretically, is it possible for example 330 00:19:13,200 --> 00:19:16,800 to, because it's logical decoding, Postgres should obviously 331 00:19:16,800 --> 00:19:22,340 support this, Is it possible to change some Column type, for 332 00:19:22,340 --> 00:19:22,840 example? 333 00:19:23,440 --> 00:19:28,180 Imagine we had the Primary key int4 and we want to migrate 334 00:19:28,180 --> 00:19:29,380 to int8. 335 00:19:29,720 --> 00:19:33,820 It's already not a use case of bloat removal, but it's kind of 336 00:19:33,820 --> 00:19:38,940 like mutation of Schema, not possible, right? 337 00:19:38,940 --> 00:19:44,180 Antonín: No, it cannot, but I remember I was suggested this feature. 338 00:19:44,440 --> 00:19:49,160 It was suggested to me, but I think it would belong to a different 339 00:19:49,160 --> 00:19:49,660 extension. 340 00:19:50,820 --> 00:19:55,360 And 1 thing that makes the pg_squeeze a little bit simpler than 341 00:19:55,520 --> 00:19:59,380 it might be is that it assumes no catalog changes. 342 00:19:59,380 --> 00:20:03,900 So it checks whether anything changed in the catalog and if it 343 00:20:04,160 --> 00:20:10,120 did, then the Transaction is aborted and the processing is not 344 00:20:10,120 --> 00:20:10,620 effective. 345 00:20:13,420 --> 00:20:16,000 So if anything changes in the catalog during the processing, 346 00:20:16,000 --> 00:20:22,000 then the processing ends up with error and everything is a Rollback. 347 00:20:23,200 --> 00:20:27,140 So if it should change the catalog in addition to copying the 348 00:20:27,140 --> 00:20:30,220 data, it will be more complex. 349 00:20:31,560 --> 00:20:33,840 Nikolay: So the main reason is simplicity, right? 350 00:20:33,840 --> 00:20:35,040 For the sake of simplicity. 351 00:20:35,180 --> 00:20:40,020 Antonín: Yes, and I think actually it's different features, so 352 00:20:40,120 --> 00:20:43,760 I would put it into another extension. 353 00:20:44,680 --> 00:20:47,980 Actually, I wrote something similar, it's called pg_rewrite, 354 00:20:48,760 --> 00:20:54,020 and it tries to use this, the same logic or similar logic like 355 00:20:54,020 --> 00:20:59,560 pg_squeeze to partition table, to turn non-partition table into 356 00:20:59,680 --> 00:21:00,180 partitions. 357 00:21:00,360 --> 00:21:04,020 I'm not sure how much it is used, but it's another use case. 358 00:21:04,020 --> 00:21:05,520 Nikolay: Yeah, I didn't know about this. 359 00:21:06,200 --> 00:21:07,900 Michael: No, I didn't know about that either. 360 00:21:08,000 --> 00:21:09,820 I was just going to give you some context. 361 00:21:09,860 --> 00:21:13,380 This came up in a discussion we were having in an episode that 362 00:21:13,380 --> 00:21:16,280 we called Column Tetris, because that's what we've heard it called 363 00:21:16,280 --> 00:21:17,360 by lots of people. 364 00:21:17,360 --> 00:21:22,400 The idea of not just changing a data type but keeping the existing 365 00:21:22,500 --> 00:21:25,800 data, like keeping all the existing columns but changing their 366 00:21:25,800 --> 00:21:30,720 order for alignment purposes to minimize dead space in between 367 00:21:30,720 --> 00:21:31,220 columns. 368 00:21:31,500 --> 00:21:36,880 So putting all of the 16 byte columns together, then the 8, then 369 00:21:36,880 --> 00:21:38,720 the 4, then the 1. 370 00:21:42,200 --> 00:21:45,860 So I'm guessing it's the same answer, but you could use the exact 371 00:21:45,860 --> 00:21:46,600 same mechanism. 372 00:21:47,420 --> 00:21:51,760 Antonín: Yes, the mechanism is another use case for this mechanism 373 00:21:51,860 --> 00:21:55,120 but not implemented in the pg_squeeze. 374 00:21:55,160 --> 00:21:55,660 Yeah. 375 00:21:56,960 --> 00:21:57,380 Nikolay: Yeah. 376 00:21:57,380 --> 00:22:01,680 Is it implemented, like, can we use pg_rewrite for this or it's 377 00:22:01,680 --> 00:22:03,980 only for to make the table partitioned? 378 00:22:04,340 --> 00:22:09,380 Antonín: No, that pg_rewrite extension so far only handles the 379 00:22:09,380 --> 00:22:09,880 partitioning. 380 00:22:12,120 --> 00:22:14,620 Nikolay: But potentially it could be used for this, like table 381 00:22:14,620 --> 00:22:16,900 rewrite for some reorganization schema. 382 00:22:17,080 --> 00:22:17,580 Antonín: Yes. 383 00:22:20,280 --> 00:22:20,780 Nikolay: Interesting. 384 00:22:21,000 --> 00:22:25,960 And also, pg_rewrite, while it's doing this, it eliminates bloat, 385 00:22:25,960 --> 00:22:26,460 right? 386 00:22:27,340 --> 00:22:30,520 Antonín: Well, it basically creates new tables, so yes. 387 00:22:31,320 --> 00:22:32,500 Nikolay: So bloat is removed. 388 00:22:32,500 --> 00:22:37,000 Doesn't this mean that pg_rewrite performs the same job as pg_squeeze? 389 00:22:37,660 --> 00:22:45,040 Antonín: Well, but it always needs 1 non-partitioned table and 390 00:22:45,300 --> 00:22:47,320 it always creates partitions. 391 00:22:48,340 --> 00:22:52,480 So not all users that need to get rid of both also need partitioning. 392 00:22:53,600 --> 00:22:54,840 Nikolay: Yeah, I see, I see. 393 00:22:55,080 --> 00:22:59,360 I'm just noticing that this project also does the job of that 394 00:22:59,360 --> 00:22:59,860 project. 395 00:23:00,380 --> 00:23:03,460 Doesn't that mean that maybe it could be a single project? 396 00:23:03,740 --> 00:23:05,720 This is my idea. 397 00:23:06,180 --> 00:23:07,000 I don't know. 398 00:23:07,080 --> 00:23:09,780 I just see the need in this. 399 00:23:09,800 --> 00:23:13,420 Sometimes we need to convert primary key data type. 400 00:23:13,780 --> 00:23:16,200 Not only int4 to int8. 401 00:23:16,200 --> 00:23:22,040 I have tables right now which were, for example, people decided, 402 00:23:22,160 --> 00:23:27,540 well people, sometimes it was myself, decided to use data type 403 00:23:27,540 --> 00:23:33,960 text to store UUID values And this is not a good decision, also 404 00:23:33,960 --> 00:23:35,880 because of disk space. 405 00:23:36,760 --> 00:23:41,680 And it would be great to convert it to UUID data type easily 406 00:23:41,760 --> 00:23:46,400 without headache, taking care of all the things that are required 407 00:23:46,400 --> 00:23:48,940 to achieve real 0 downtime. 408 00:23:48,940 --> 00:23:54,820 For example, sometimes I see people create quite smart migrations, 409 00:23:55,600 --> 00:23:59,640 but then they forget about autovacuum running in transaction 410 00:23:59,680 --> 00:24:01,960 ID wrap around prevention mode, for example, right? 411 00:24:01,960 --> 00:24:05,040 And it blocks deployment, for example. 412 00:24:05,380 --> 00:24:07,480 There are many things to remember, right? 413 00:24:07,640 --> 00:24:12,980 In such project, to be able to rewrite or to reorganize the order 414 00:24:12,980 --> 00:24:18,340 of columns and get rid of bloat, maybe sometimes clustering as 415 00:24:18,340 --> 00:24:18,480 well. 416 00:24:18,480 --> 00:24:20,640 I know pg_repack supports clustering. 417 00:24:20,740 --> 00:24:22,000 I'm not sure about pg_squeeze. 418 00:24:23,300 --> 00:24:25,900 To reorganize, also supports, right? 419 00:24:25,900 --> 00:24:27,540 Antonín: Yes, you can specify index. 420 00:24:28,580 --> 00:24:32,220 Nikolay: So I mean, there are many things, but they are united 421 00:24:32,240 --> 00:24:36,500 by the single idea that tables should be basically rewritten, 422 00:24:36,680 --> 00:24:37,180 right? 423 00:24:38,360 --> 00:24:40,200 And partitioning is another use case. 424 00:24:40,200 --> 00:24:45,780 Maybe it should be a single, I'm just like thinking. 425 00:24:46,020 --> 00:24:52,940 Antonín: Yes, the problem is that this would end up in many extensions 426 00:24:52,960 --> 00:24:55,860 and someone must maintain it. 427 00:25:00,080 --> 00:25:00,160 I think the ideal case is that this finds its way into the core. 428 00:25:00,900 --> 00:25:02,820 Nikolay: Oh yes, let's discuss this. 429 00:25:04,160 --> 00:25:04,820 Why not? 430 00:25:04,820 --> 00:25:05,380 Why not? 431 00:25:05,380 --> 00:25:06,220 Let's discuss this. 432 00:25:06,220 --> 00:25:07,620 For example, Pidgeot's quiz. 433 00:25:08,100 --> 00:25:13,780 Why we cannot propose something like as an alternative to 434 00:25:13,780 --> 00:25:17,980 VACUUM FULL and CLUSTER commands, Maybe just an option to them like 435 00:25:17,980 --> 00:25:19,020 online, right? 436 00:25:19,220 --> 00:25:19,720 Michael: CONCURRENTLY? 437 00:25:20,740 --> 00:25:21,240 Nikolay: CONCURRENTLY. 438 00:25:21,380 --> 00:25:23,180 It doesn't matter which words. 439 00:25:23,800 --> 00:25:25,820 And everyone already receives this. 440 00:25:25,840 --> 00:25:28,140 Like, what are the obstacles on this path? 441 00:25:28,140 --> 00:25:31,040 Antonín: Well, the obstacle is a review. 442 00:25:31,720 --> 00:25:36,300 I already submitted a patch which modifies the CLUSTER command 443 00:25:36,300 --> 00:25:38,860 and VACUUM with the FULL option. 444 00:25:39,660 --> 00:25:41,020 Let's see what happens. 445 00:25:41,140 --> 00:25:44,280 So the patch is already in the commit fest. 446 00:25:45,100 --> 00:25:46,820 Nikolay: Can you tell more about this patch? 447 00:25:47,900 --> 00:25:51,160 Antonín: So far it does not contain the scheduling feature. 448 00:25:51,420 --> 00:25:56,160 That's something rather easy to add, but otherwise it's almost 449 00:25:56,160 --> 00:25:57,220 identical to... 450 00:25:57,260 --> 00:26:00,840 The functionality is basically identical to the functionality 451 00:26:01,100 --> 00:26:06,340 of pg_squeeze, except that it does not change the visibility of 452 00:26:06,340 --> 00:26:07,000 the data. 453 00:26:07,860 --> 00:26:14,180 If you know the pg_squeeze, when it writes the data into the new 454 00:26:14,180 --> 00:26:17,200 table, it uses new transaction ID. 455 00:26:17,520 --> 00:26:21,860 So, the data kind of moves into the future. 456 00:26:23,080 --> 00:26:26,900 Well, this is similar to what happens with Alter Table commands. 457 00:26:27,440 --> 00:26:31,080 If Alter Table rewrites the table, it also uses new transactions. 458 00:26:31,100 --> 00:26:36,680 So this is a documented anomaly of MVCC. 459 00:26:38,440 --> 00:26:41,420 Nikolay: I'm looking at this commitfest, which is called 460 00:26:41,420 --> 00:26:42,620 VACUUM FULL / CLUSTER CONCURRENTLY 461 00:26:43,500 --> 00:26:45,100 And this is this is great. 462 00:26:45,100 --> 00:26:49,460 Actually, This shows how poorly I'm prepared for this recording. 463 00:26:49,640 --> 00:26:51,820 I'm sorry, I apologize for this. 464 00:26:51,820 --> 00:26:52,980 But this is huge. 465 00:26:53,300 --> 00:26:56,280 I promise to look at this and advertise it on my Twitter and 466 00:26:56,280 --> 00:26:56,780 LinkedIn. 467 00:26:57,180 --> 00:26:57,700 This is great. 468 00:26:57,700 --> 00:26:58,440 This is great. 469 00:26:58,440 --> 00:27:03,040 Like I think so many people would be happy to have this functionality 470 00:27:03,180 --> 00:27:03,840 in core. 471 00:27:04,140 --> 00:27:05,200 This is huge work. 472 00:27:05,860 --> 00:27:10,460 So basically, yeah, yeah, pg_squeeze in core, that's great. 473 00:27:10,900 --> 00:27:15,560 Antonín: I would be happy to because I would not have to maintain 474 00:27:16,080 --> 00:27:17,860 the code anymore. 475 00:27:20,080 --> 00:27:26,420 As I say, if it was incorporated into the core, then this visibility 476 00:27:26,480 --> 00:27:29,560 problem would also be gone. 477 00:27:30,520 --> 00:27:33,160 The extension is kind of limited. 478 00:27:33,960 --> 00:27:37,620 Some things happen lower in the stack. 479 00:27:38,040 --> 00:27:43,080 So the extension needs to, it must use the new transaction to 480 00:27:43,080 --> 00:27:44,580 insert the data. 481 00:27:45,160 --> 00:27:50,680 But if it were incorporated in the core, then it would act just 482 00:27:50,680 --> 00:27:52,660 like CLUSTER and VACUUM FULL. 483 00:27:52,720 --> 00:27:54,900 These commands do not change visibility. 484 00:27:57,800 --> 00:27:59,740 Nikolay: And what is the current status? 485 00:27:59,860 --> 00:28:01,860 It waits for review, I see, right? 486 00:28:03,520 --> 00:28:08,000 So version 5, version 6 already, right? 487 00:28:08,940 --> 00:28:12,040 So there's already some progress, some reviews happened and it 488 00:28:12,040 --> 00:28:14,440 waits for another round of reviews, right? 489 00:28:14,900 --> 00:28:18,480 Antonín: Yes, yes, that seems so, Yes. 490 00:28:19,820 --> 00:28:21,420 Nikolay: Yeah, that's great, that's great. 491 00:28:21,680 --> 00:28:24,840 So well, I'm excited to see this. 492 00:28:24,840 --> 00:28:25,640 I don't know. 493 00:28:26,000 --> 00:28:26,400 Antonín: Okay, thanks. 494 00:28:26,400 --> 00:28:27,180 That's great. 495 00:28:28,580 --> 00:28:29,380 Michael: Yeah, for sure. 496 00:28:29,380 --> 00:28:32,360 I mean, I did a couple of hours of research and didn't find that 497 00:28:32,360 --> 00:28:35,140 Nikolay, sorry don't don't beat yourself up. 498 00:28:35,460 --> 00:28:38,860 Tony though I've got bad news for you, I think you probably are 499 00:28:38,860 --> 00:28:42,780 still going to need to maintain pg_squeeze for a while because 500 00:28:43,080 --> 00:28:46,280 the scheduling stuff looks really useful and I imagine I'm looking 501 00:28:46,280 --> 00:28:50,080 at the list of features and you might have got the wrong impression 502 00:28:50,080 --> 00:28:52,540 listening to this that you value simplicity that it might not 503 00:28:52,540 --> 00:28:54,860 be that powerful and might not have that many features. 504 00:28:55,020 --> 00:28:58,780 But there's so many features to pg_squeeze things that I wasn't 505 00:28:58,780 --> 00:29:03,020 expecting to see like it does it Nicola be happy about this it 506 00:29:03,020 --> 00:29:06,660 does Analyze on the table afterwards by default you can skip 507 00:29:06,660 --> 00:29:10,200 the Analyze, I saw that was a feature but by default it runs 508 00:29:10,200 --> 00:29:14,220 Analyze on it and you can even switch the tablespace, so you 509 00:29:14,220 --> 00:29:17,440 can when rebuilding I know not many people use tablespaces these 510 00:29:17,440 --> 00:29:21,440 days, but that's a feature and I learned something new I didn't 511 00:29:21,440 --> 00:29:25,380 realize the heap of a table could be in a different tablespace 512 00:29:25,380 --> 00:29:29,860 to the indexes that blew my mind But in so I learned quite a 513 00:29:29,860 --> 00:29:32,860 lot reading your very thorough readme, which was great. 514 00:29:33,220 --> 00:29:36,340 Antonín: Well, I'm not sure how many people use these features, 515 00:29:36,340 --> 00:29:44,580 but when I was writing it, I tried to do it in a general way. 516 00:29:47,000 --> 00:29:52,180 And especially the analysis is necessary because it is needed 517 00:29:52,180 --> 00:29:56,880 to evaluate if there is the bloat, if the bloat exists. 518 00:29:57,040 --> 00:30:00,460 So that's basically why it analyzes the table. 519 00:30:05,220 --> 00:30:06,740 Michael: I think it's analyze after. 520 00:30:07,300 --> 00:30:10,060 Antonín: Okay, so sorry, I was wrong. 521 00:30:10,580 --> 00:30:11,940 I don't remember why. 522 00:30:13,480 --> 00:30:15,700 Michael: You don't remember why you added such a good feature. 523 00:30:15,700 --> 00:30:16,560 I like it. 524 00:30:16,620 --> 00:30:20,220 Nikolay: Yeah, Speaking of table spaces, I think this maybe not 525 00:30:20,220 --> 00:30:25,180 huge but there is some potential that this, this is old stuff, 526 00:30:25,180 --> 00:30:28,760 like we used table spaces when, like before cloud basically, 527 00:30:28,900 --> 00:30:35,440 because Some disks were fast and expensive, some disks were slow 528 00:30:35,540 --> 00:30:36,300 and cheap. 529 00:30:36,620 --> 00:30:39,660 So we tried to organize the storage. 530 00:30:40,160 --> 00:30:43,680 What I'm thinking right now in the cloud era, it looks like table 531 00:30:43,680 --> 00:30:47,820 spaces, use of them almost disappeared. 532 00:30:48,940 --> 00:30:53,900 But if we think about tiered storage, which might get popularity 533 00:30:54,440 --> 00:30:55,940 over time quite soon. 534 00:30:56,120 --> 00:31:01,100 For example, if we store a huge heap, a huge table on object 535 00:31:01,100 --> 00:31:07,600 storage on S3, just because it's much cheaper, we might decide, 536 00:31:07,740 --> 00:31:13,140 OK, we want indexes on, or at least some indexes on a regular 537 00:31:13,140 --> 00:31:13,640 disk. 538 00:31:13,680 --> 00:31:16,160 I mean, EBS volume, for example, right? 539 00:31:16,560 --> 00:31:20,780 I don't know, like it's just my fantasy, but it might have some 540 00:31:20,780 --> 00:31:26,740 chances to happen, to be popular in some cases, I don't know. 541 00:31:29,020 --> 00:31:30,080 Michael: Another different extension. 542 00:31:30,460 --> 00:31:35,660 Tony, there's 1 more cool feature of pg_squeeze that I liked. 543 00:31:35,820 --> 00:31:39,220 There's various monitoring tables, there's a log of the jobs 544 00:31:39,220 --> 00:31:43,400 that have run and an errors table, which I thought was extremely 545 00:31:43,640 --> 00:31:43,980 good. 546 00:31:43,980 --> 00:31:46,240 And it does retry it can do retries as well. 547 00:31:46,240 --> 00:31:49,180 I think it doesn't by default but you can set up several retries. 548 00:31:49,940 --> 00:31:54,760 That all seems to me really powerful in terms of, you know, as 549 00:31:54,760 --> 00:31:58,640 you say, if things if if you're unlucky and a DDL command happens 550 00:31:58,640 --> 00:32:03,220 to be 1 on the table just as it was kicking off 1 of these squeeze 551 00:32:03,480 --> 00:32:07,440 jobs and it gets canceled, you might want it to be able to retry 552 00:32:07,440 --> 00:32:08,720 again quite soon. 553 00:32:08,800 --> 00:32:11,720 So these features all seem cool and the idea of an error log 554 00:32:11,720 --> 00:32:17,640 as well in terms of having that as a table and having a SQL interface 555 00:32:17,640 --> 00:32:19,140 to it all seemed really cool. 556 00:32:19,340 --> 00:32:22,660 I think that was all from the beginning, so do you remember much 557 00:32:22,660 --> 00:32:25,860 about implementing those and what you were thinking at the time? 558 00:32:26,740 --> 00:32:31,720 Antonín: The monitoring for me, it was even important for the 559 00:32:31,720 --> 00:32:33,400 development and testing. 560 00:32:34,960 --> 00:32:45,280 I think 1 reason was to to make sure that those concurrent changes, 561 00:32:45,300 --> 00:32:52,060 I mean, that do actually happen during the test. 562 00:32:52,800 --> 00:32:57,680 So when the data is being copied to the news file, some applications 563 00:32:58,980 --> 00:33:00,040 may do changes. 564 00:33:00,900 --> 00:33:02,680 I call these concurrent changes. 565 00:33:02,680 --> 00:33:07,120 And I wanted to make sure that during testing these changes really 566 00:33:07,120 --> 00:33:07,540 happen. 567 00:33:07,540 --> 00:33:10,940 So that was 1 reason I did the monitoring. 568 00:33:11,400 --> 00:33:16,120 Of course it should be useful for the users if for example it 569 00:33:16,120 --> 00:33:21,340 takes too long time it's good to know how many rows were already 570 00:33:21,340 --> 00:33:23,900 copied and what was the progress. 571 00:33:25,280 --> 00:33:25,780 Michael: Yeah. 572 00:33:26,040 --> 00:33:26,920 I loved it. 573 00:33:26,920 --> 00:33:30,020 The only thing I was surprised that you didn't include, I don't 574 00:33:30,020 --> 00:33:33,480 think people necessarily need it, but it shows off how much work 575 00:33:33,480 --> 00:33:37,260 you're doing is it didn't show me a before and after in terms 576 00:33:37,260 --> 00:33:41,200 of like table size I thought there's an opportunity there to 577 00:33:41,200 --> 00:33:45,420 show off and show how much success you're bringing but yeah It 578 00:33:45,420 --> 00:33:50,980 shows the number of tuples on the initialization, or maybe number 579 00:33:50,980 --> 00:33:55,680 of rows, and then number of concurrent inserts, updates and deletes 580 00:33:55,680 --> 00:33:55,920 maybe? 581 00:33:55,920 --> 00:33:57,600 I think there's like 4 columns here. 582 00:33:58,620 --> 00:33:59,120 Cool. 583 00:33:59,540 --> 00:34:02,360 What are your, like what's the future of pg_squeeze? 584 00:34:02,360 --> 00:34:04,700 I guess the core stuff makes sense. 585 00:34:05,200 --> 00:34:06,880 Antonín: I don't really think much. 586 00:34:07,400 --> 00:34:09,560 I don't really think that far. 587 00:34:10,240 --> 00:34:16,920 But then it will be maintained for some time and the next step 588 00:34:17,100 --> 00:34:21,880 then might be to submit a patch for the scheduling. 589 00:34:21,880 --> 00:34:27,340 But I'm not sure if the scheduling is appropriate for the Postgres 590 00:34:27,500 --> 00:34:28,000 core. 591 00:34:29,600 --> 00:34:34,460 I think that if this was merged into the core, then I think people 592 00:34:34,460 --> 00:34:37,320 would bring up many new ideas. 593 00:34:37,680 --> 00:34:41,180 So I didn't really think that far. 594 00:34:41,380 --> 00:34:45,600 I think there is not many features to be added. 595 00:34:46,080 --> 00:34:51,160 I don't have many ideas what else should be added and at this 596 00:34:51,160 --> 00:34:57,040 point I'm not too eager to add new features because it works 597 00:34:57,040 --> 00:35:00,720 and as I hear sometimes that some important customer is using 598 00:35:00,720 --> 00:35:03,580 it then I tend not to touch it anymore. 599 00:35:05,280 --> 00:35:11,880 I did the coding the best way I could do but with the positive 600 00:35:11,880 --> 00:35:15,260 feedback I tend to not add new features. 601 00:35:15,260 --> 00:35:20,400 I'm fine if I'm only supposed to maintain it. 602 00:35:21,740 --> 00:35:25,520 Michael: Yeah, like for new major versions, I'm guessing there's 603 00:35:25,520 --> 00:35:26,180 some work. 604 00:35:27,260 --> 00:35:30,340 Antonín: Yes, for major versions. 605 00:35:31,720 --> 00:35:32,220 Michael: Nice. 606 00:35:32,860 --> 00:35:34,820 Nikolay, anything else you wanted to ask? 607 00:35:34,820 --> 00:35:36,880 Nikolay: No, no, I think we're good. 608 00:35:37,780 --> 00:35:41,940 Thank you so much for coming and explaining how it was created 609 00:35:41,940 --> 00:35:43,380 and how it's going. 610 00:35:43,380 --> 00:35:47,980 And I'm super excited, like, with this surprise that there is 611 00:35:47,980 --> 00:35:53,000 a proposal to have VACUUM FULL CONCURRENTLY, it's really great. 612 00:35:53,200 --> 00:35:54,660 Going to look at it closer. 613 00:35:54,720 --> 00:35:56,820 Antonín: Okay, thanks for this feedback. 614 00:35:58,100 --> 00:36:02,480 Nikolay: This is like, if it succeeded, it's a logical step for 615 00:36:02,480 --> 00:36:08,400 this project I think, right, which can influence every Postgres 616 00:36:08,760 --> 00:36:09,260 instance. 617 00:36:10,440 --> 00:36:11,100 Michael: For sure. 618 00:36:11,460 --> 00:36:11,960 Yeah. 619 00:36:13,340 --> 00:36:16,820 Well best of luck, thank you so much for joining us and I hope 620 00:36:16,820 --> 00:36:17,900 you have a good week. 621 00:36:18,480 --> 00:36:21,540 Antonín: Yes, thanks for the invitation and have a nice day. 622 00:36:22,840 --> 00:36:23,820 Nikolay: Thank you, bye bye.