1 00:00:00,050 --> 00:00:03,650 Michael: Hello and welcome to Postgres Film a Week Share about All Things Postgres. 2 00:00:03,950 --> 00:00:05,449 I'm Michael, founder of PG Mustard. 3 00:00:05,449 --> 00:00:07,850 This is my co-host Nikolai, founder of Postgres ai. 4 00:00:07,870 --> 00:00:09,610 Hey, Nikolai, what are we talking about today? 5 00:00:10,380 --> 00:00:11,010 Nikolay: Hi Michael. 6 00:00:11,335 --> 00:00:12,685 is it your choice or Mine? 7 00:00:13,017 --> 00:00:14,407 Michael: Oh, is it boring or not? 8 00:00:14,407 --> 00:00:15,247 That's the question. 9 00:00:15,557 --> 00:00:17,258 Nikolay: Yeah, it's mine, right? 10 00:00:17,258 --> 00:00:18,338 So it should not be boring. 11 00:00:18,438 --> 00:00:20,538 For some folks it might sound boring. 12 00:00:20,538 --> 00:00:24,226 Very boring because we are going to talk about, psk 13 00:00:25,061 --> 00:00:26,119 common blind versus. 14 00:00:26,964 --> 00:00:35,829 Versus G U I and uh, some people think it's very, very boring to work in terminal. 15 00:00:35,859 --> 00:00:44,229 Very like hack boring, inconvenient in my experience and in my opinion this is super cool tool. 16 00:00:45,099 --> 00:00:46,269 I use it a lot. 17 00:00:46,499 --> 00:00:48,229 It's not boring at all. 18 00:00:48,719 --> 00:00:51,369 It's super flexible, powerful. 19 00:00:51,739 --> 00:00:57,969 It likes something still, we probably will talk about it, but I, I find this topic not boring at all. 20 00:00:58,455 --> 00:01:05,341 Michael: Yeah, so there's psql itself as the com, like the default command line interface for Postgres ships with Postgres. 21 00:01:05,351 --> 00:01:06,301 Awesome tool. 22 00:01:06,465 --> 00:01:12,445 but there's also the higher level discussion of command line interfaces versus graphical user interfaces. 23 00:01:12,460 --> 00:01:14,020 Nikolay: with databases in this context, right? 24 00:01:14,445 --> 00:01:14,935 Michael: Yeah. 25 00:01:14,935 --> 00:01:20,975 Specifically for Post I, I was gonna say even grow as narrow as just talking about the ones that work with Postgre. 26 00:01:21,244 --> 00:01:21,729 Nikolay: Right. 27 00:01:22,139 --> 00:01:24,865 So PS q L is common line tool. 28 00:01:24,865 --> 00:01:26,705 Its client program, right? 29 00:01:26,705 --> 00:01:28,825 Client program in working in terminal. 30 00:01:29,485 --> 00:01:43,054 And, uh, it's installed usually in . For example, you install client package, Client, uh, I would like to mention that there is, there are more client programs, standard shipped with, POS distribution. 31 00:01:43,054 --> 00:01:45,934 For example, PG dump, right? 32 00:01:45,934 --> 00:01:53,254 It's PG store or PG bench, but in somehow PG bench goes to server package. 33 00:01:53,934 --> 00:01:54,424 Yeah. 34 00:01:55,369 --> 00:01:56,139 It's strange. 35 00:01:56,139 --> 00:02:06,089 Sometimes we, for example, sometimes we, we have, older database installed and we want newer, client tools because there is a backward compatibility. 36 00:02:06,089 --> 00:02:10,889 We know that newer client tools can perfectly work usually, uh, with older database. 37 00:02:10,889 --> 00:02:12,409 So, and they have some features. 38 00:02:12,409 --> 00:02:18,289 For example, P Bench has, has some feature, or PC has some feature, and we are trying to install client. 39 00:02:18,417 --> 00:02:19,297 It'll be fine. 40 00:02:19,297 --> 00:02:21,017 For PC SQL P store. 41 00:02:21,017 --> 00:02:22,777 It won't be fine for P bench. 42 00:02:22,957 --> 00:02:28,017 For P bench, you need to install server package for newer version, which is strange. 43 00:02:28,047 --> 00:02:28,937 Just a side note. 44 00:02:29,872 --> 00:02:42,002 Michael: That's a really good starting point though, because it is worth noting that sometimes these client features, they're, they're new, they seem like new features in a new version of Postgres, but actually they can work on older versions, so that's, 45 00:02:42,312 --> 00:02:43,862 Nikolay: In many cases, so, right. 46 00:02:43,862 --> 00:02:44,262 Yeah. 47 00:02:44,472 --> 00:02:45,242 Michael: Yeah, of course. 48 00:02:45,242 --> 00:02:45,442 Not 49 00:02:45,632 --> 00:02:48,102 Nikolay: because they work usually on, at meta level. 50 00:02:48,102 --> 00:02:56,782 For example, like we, we can for, for example, on Positive TV with Andre Brad, we're trying to extend backslash watch. 51 00:02:57,309 --> 00:03:01,109 PC comment to add more options to it. 52 00:03:01,109 --> 00:03:03,109 Uh, it, it accepts only one option. 53 00:03:03,583 --> 00:03:09,845 it turns some query in Infinite Loop and originally it accepts only one option. 54 00:03:10,145 --> 00:03:11,880 The, sleep time between loops. 55 00:03:13,005 --> 00:03:16,515 So we wanted to, to add another option, how many loops we want. 56 00:03:17,939 --> 00:03:23,892 Michael: should we go back to I'm thinking of it sometimes from beginner angle and sometimes from like the advanced user angle. 57 00:03:24,002 --> 00:03:28,628 I, I see a lot of advanced users in love with PC Corp or PS Q L. 58 00:03:29,050 --> 00:03:30,380 I think Leticia's doing. 59 00:03:30,550 --> 00:03:34,472 Leticia did a really good talk on post Chris tv, and, has done it at several 60 00:03:34,802 --> 00:03:38,252 Nikolay: Huge number of, of tips on that website. 61 00:03:39,012 --> 00:03:39,492 tips, right? 62 00:03:39,492 --> 00:03:39,772 Mm-hmm. 63 00:03:40,112 --> 00:03:40,432 Michael: Exactly. 64 00:03:40,432 --> 00:03:48,952 She also has that website with, um, I think you can, you can have it load a random tip or you can go through them one by one, but yeah, well over a hundred now that she's got listed there. 65 00:03:49,052 --> 00:03:51,192 And that's kind of both great. 66 00:03:51,192 --> 00:04:02,912 And also the, the issue with pc, I think it's so powerful, it can do so much, but it's also quite intimidating as a new user, I think offer beginners to know what's even possible. 67 00:04:03,162 --> 00:04:06,112 So is there, like, where would you recommend getting started for. 68 00:04:06,622 --> 00:04:09,192 Nikolay: I would recommend getting started with, uh, Linux terminal. 69 00:04:11,132 --> 00:04:12,252 learning, Tmax and vi, 70 00:04:13,127 --> 00:04:13,617 Michael: Yeah. 71 00:04:13,652 --> 00:04:15,292 Nikolay: maybe not vi, but Tmax. 72 00:04:15,292 --> 00:04:30,104 And, uh, definitely because you don't want, if you're disconnected, uh, you're doing something server or in some infrastructure, if even if it's RRGs and you're working from some, uh, machine, it should be closer to RRGs and should not be dependent on your connect connectivity and so on. 73 00:04:30,104 --> 00:04:30,264 Right. 74 00:04:30,264 --> 00:04:32,264 So if you are home internet, like. 75 00:04:32,802 --> 00:04:34,366 Very often is not good. 76 00:04:34,830 --> 00:04:35,702 Tmax saves you. 77 00:04:35,882 --> 00:04:43,264 This is where you, I think everyone should start and learn some basics of, uh, these great tools, Tmax and vi. 78 00:04:43,613 --> 00:04:52,090 Uh, and then, just consider one particular reason why everyone should use P sql, at least from time to time. 79 00:04:52,320 --> 00:04:55,992 This reason is predictability and repeatability of. 80 00:04:56,472 --> 00:04:57,032 Steps. 81 00:04:57,282 --> 00:05:00,832 If you do something in UI , try to do it. 82 00:05:00,832 --> 00:05:04,712 Once again, you might be, you might click different button and that's it. 83 00:05:04,732 --> 00:05:11,741 You cannot program it and put to gi Well, there are tools for UI programming, like, uh, recorders. 84 00:05:11,741 --> 00:05:17,541 I remember 20 plus years ago there was, uh, Rob Rational Robot or something. 85 00:05:17,541 --> 00:05:19,341 I don't like many things. 86 00:05:19,341 --> 00:05:21,781 Created selenium and so on. 87 00:05:23,186 --> 00:05:29,756 When you work in terminal, it's natural for you to code it in form of script and share with colleagues. 88 00:05:29,756 --> 00:05:35,653 These are my steps to repeat them, to be very predictable, testable, and so on. 89 00:05:35,843 --> 00:05:36,693 Deployable. 90 00:05:37,678 --> 00:05:39,928 Michael: Yeah, I think that's, I think that's a really good reason. 91 00:05:39,928 --> 00:05:42,008 The other, and two more good reasons. 92 00:05:42,008 --> 00:05:43,688 One is it's installed everywhere. 93 00:05:43,688 --> 00:05:47,088 You're gonna have Postgres, there's, you're gonna be able to have access to Pcq. 94 00:05:47,088 --> 00:05:48,128 I think that's a really good reason. 95 00:05:48,128 --> 00:05:51,568 And then another one is not just predictable, but reliable. 96 00:05:52,242 --> 00:05:55,092 It's the most reliable interface I've ever seen to Postgres. 97 00:05:55,092 --> 00:05:59,452 So I've seen a lot of gooeys have weird bugs or like every now and again, you get 98 00:05:59,972 --> 00:06:00,622 Nikolay: I would say 99 00:06:00,772 --> 00:06:01,492 Michael: What exactly. 100 00:06:01,822 --> 00:06:07,412 So quite often in a Postgre community you might see somebody seeing, seeing some weird behavior. 101 00:06:07,512 --> 00:06:14,292 And one of the first questions people ask is, can you get, do you get that exact same behavior when you run the query in peace, sql? 102 00:06:14,292 --> 00:06:20,092 And if you don't, it's a problem at the editor level, like there's something going on in between, or the interface. 103 00:06:20,797 --> 00:06:23,147 Nikolay: We can claim that P SQL is pores. 104 00:06:23,527 --> 00:06:25,787 All UIs are not pores. 105 00:06:25,787 --> 00:06:26,867 Pores doesn't have ui. 106 00:06:27,982 --> 00:06:34,592 Michael: Yeah, so PC Equal, I think the code is held to the same standard as the Postgres code base, which is a very high standard, 107 00:06:35,522 --> 00:06:36,002 Nikolay: Right. 108 00:06:36,002 --> 00:06:40,282 Unlike any ui, pogs itself doesn't have any UI and PPG admin. 109 00:06:40,312 --> 00:06:52,642 Some people confuse it with, like considering it as a standard UI for pocus, but it, it's, it is advertised on pogs scale.org website, but it has, it's a different product. 110 00:06:52,812 --> 00:06:53,322 It does. 111 00:06:53,322 --> 00:06:54,762 It's not shipped with pogs. 112 00:06:54,852 --> 00:06:59,642 It has different release process, release cycle, different team and so on. 113 00:06:59,642 --> 00:07:01,627 It's basically under, enter. 114 00:07:02,497 --> 00:07:04,347 E GB is, is working on it. 115 00:07:04,417 --> 00:07:05,027 Single 116 00:07:05,027 --> 00:07:05,427 company. 117 00:07:06,202 --> 00:07:25,417 Michael: I can see where the confusion comes from though, because if you install one of like, Postgres via one of the, like EDB maintained installers, like the Windows installer or the Mac installer that they, that they host, I think on postgres.org or postgres.org , it does come with PG admin, and I think that's where a lot of the confusion comes from. 118 00:07:25,547 --> 00:07:34,697 So it's understandable, but it is worth, uh, in case you didn't know, it's worth noting that it, it isn't official and equally it doesn't have as higher market share. 119 00:07:34,697 --> 00:07:38,697 Like when I looked into, so my background is in, is in Guidos. 120 00:07:38,697 --> 00:07:39,417 Yeah, exactly. 121 00:07:39,417 --> 00:07:45,577 I, I was hoping when I came to the Postgres world to be able to make an extension to a popular editor. 122 00:07:45,707 --> 00:07:48,457 So, uh, to make a tool for a popular editor. 123 00:07:48,917 --> 00:08:01,807 Coming from, coming from the SQL Server world or the Oracle world, were quite a few editors, and even my MyQ were quite, a few editors at the time had, you know, tens of percent of market share in the Postgres ecosystem. 124 00:08:01,807 --> 00:08:02,887 That just isn't the case. 125 00:08:02,997 --> 00:08:09,416 A lot of people are using cls, a lot of people are using a vast array of different, gooeys. 126 00:08:09,426 --> 00:08:12,936 So it's, it's much more difficult to pick or you'd have to build something that 127 00:08:13,051 --> 00:08:14,461 Nikolay: It's open source, bazaar. 128 00:08:14,461 --> 00:08:19,602 You know, it's not cathedral in this case, but I wish POCUS had standard ui. 129 00:08:19,602 --> 00:08:20,242 I wish it had. 130 00:08:20,745 --> 00:08:29,795 Michael: Yeah, it, it could be cool, but I'd also, I do understand why they don't, I think it's a, but I do think it's a challenge for the ecosystem that, uh, that there isn't 131 00:08:29,795 --> 00:08:30,035 one 132 00:08:30,350 --> 00:08:37,147 Nikolay: some ui, sorry for interrupting, but when some UI I experience it many times in my own, projects. 133 00:08:37,257 --> 00:08:41,427 When UI is added, it quickly becomes more than 50% of everything. 134 00:08:42,692 --> 00:08:48,178 of development, of code, base, of, issues, uh, goals, tasks, everything. 135 00:08:48,178 --> 00:08:52,338 So it, it can be much bigger than pro, uh, than engine itself. 136 00:08:52,348 --> 00:08:55,258 So, so, because, and it's very different. 137 00:08:55,258 --> 00:08:57,832 Different because, maybe not all will use it. 138 00:08:57,832 --> 00:09:01,712 Well, still, I think POS needs ui, standard ui. 139 00:09:02,182 --> 00:09:03,512 It's, it's my own opinion. 140 00:09:03,512 --> 00:09:06,712 I, I see how things are right now and. 141 00:09:07,327 --> 00:09:10,047 , I think it's very, very low chance it'll be changed. 142 00:09:10,387 --> 00:09:10,807 Yes. 143 00:09:10,867 --> 00:09:14,887 Pja mean is something I never used and don't recommend to anyone. 144 00:09:15,357 --> 00:09:20,047 I reevaluate every couple of years and consider it's going the wrong direction. 145 00:09:20,557 --> 00:09:28,601 Having problem since birth, like the new pja mean working in browser and so on, like nice idea about implementation. 146 00:09:28,601 --> 00:09:32,761 I consider it's very poor and cannot recommend to anyone if someone. 147 00:09:34,001 --> 00:09:45,431 , uh, ui, it's better to consider postal on Mac or GB or Cloud Beaver, anywhere, or the tool from Gel Brains, how it's called Data Group, 148 00:09:45,461 --> 00:09:46,271 Michael: That grip? 149 00:09:46,271 --> 00:09:46,631 Yep. 150 00:09:46,961 --> 00:09:50,281 Nikolay: These tools are much better, much better. 151 00:09:51,066 --> 00:09:57,963 Michael: Yeah, I have heard that PPG Admin four is the online version, uh, or the, sorry, the web-based, version. 152 00:09:57,963 --> 00:10:03,603 I have heard it's been getting better in later versions, but I do still come across quite a few people that have issues 153 00:10:03,863 --> 00:10:04,603 and it's. 154 00:10:05,033 --> 00:10:07,403 Nikolay: from what I saw, you cannot improve it. 155 00:10:07,403 --> 00:10:09,683 You need to destroy it and start from scratch and. 156 00:10:10,593 --> 00:10:12,273 Probably with different approaches. 157 00:10:12,303 --> 00:10:17,553 Like I can be specific, but we need to open screen sharing and I will show you details. 158 00:10:17,553 --> 00:10:21,873 For example, you cannot query POGS all the time in Loop. 159 00:10:22,153 --> 00:10:31,633 Maybe they fixed it, I don't know, but it's so wrong idea to query my Postgres all the time in time in Loop to deliver some monitoring information, which I don't need at all. 160 00:10:32,063 --> 00:10:38,953 I want to disable it, but I don't need to do like, I just want to send my queries Don't, don't spam my pauses. 161 00:10:38,953 --> 00:10:39,153 Right? 162 00:10:40,548 --> 00:10:41,038 Michael: Yeah. 163 00:10:41,038 --> 00:10:42,718 And there are tons of alternatives. 164 00:10:42,718 --> 00:10:45,998 I meant I said that in, in passing, but you mentioned some great ones there. 165 00:10:46,168 --> 00:10:48,078 Um, DB versus especially 166 00:10:48,183 --> 00:10:50,303 Nikolay: beaver maybe is the leading alternative. 167 00:10:51,063 --> 00:10:53,753 Michael: Well, yeah, data Grip definitely has some benefits. 168 00:10:53,753 --> 00:11:07,913 Uh, DB before I hear a lot of people that work with geo data, uh, love it for the visualizations and that's one of the, one of the, maybe the main reasons to use a graphical tool is if you've, if you're looking at things visually, uh, but there are, I want you to give a shout out to others. 169 00:11:08,003 --> 00:11:12,553 My personal favorite, and this is actually an admission I should probably have made up the top of the episode. 170 00:11:12,943 --> 00:11:16,033 I actually tend to use a gooey for working with Postgres. 171 00:11:16,033 --> 00:11:17,633 I don't do that much with Postgres. 172 00:11:18,628 --> 00:11:35,350 Partly because I don't do that much with it, day to day, like I'm working on a, a product for Postgres, not necessarily doing Postgres stuff all day, but, Also when I, I'm, sometimes what I'm doing is trying to show people how to do things and sometimes that is easier where, what they're with, what they're currently using. 173 00:11:35,760 --> 00:11:38,630 So my personal favorite at the moment is Pasta Co. 174 00:11:38,630 --> 00:11:39,230 That you mentioned. 175 00:11:39,230 --> 00:11:42,550 Very fast, open, very reliable, but it is Mac only. 176 00:11:42,550 --> 00:11:43,710 There's loads of others. 177 00:11:43,710 --> 00:11:45,230 Like Table Plus is really popular. 178 00:11:46,177 --> 00:11:51,259 DB was popular but has been revived recently by Command Prompt, I think they've called it, PG Manage. 179 00:11:51,983 --> 00:11:52,623 there's loads of. 180 00:11:53,788 --> 00:11:54,768 Alternatives. 181 00:11:54,768 --> 00:11:58,514 Now there's some cool ones like Popsicle, that's how I think you pronounce it. 182 00:11:58,514 --> 00:11:59,474 Pop Sqr. 183 00:11:59,974 --> 00:12:02,554 And then sadly archetype got acquired. 184 00:12:02,554 --> 00:12:03,234 That that was looking 185 00:12:03,544 --> 00:12:07,284 Nikolay: The Beaver has Cloud Beaver version like which, yeah. 186 00:12:07,524 --> 00:12:08,374 Also interesting. 187 00:12:09,234 --> 00:12:14,494 You know what I use more and more I like, for me, I like to do everything in the browser. 188 00:12:14,604 --> 00:12:16,654 Like I'm, I'm very strange person, probably. 189 00:12:16,654 --> 00:12:20,694 I like terminals and I, I like everything in the browser and this is very strange, but I know it. 190 00:12:20,694 --> 00:12:24,214 But still, this is my tool, like maybe conflicting, goals. 191 00:12:24,434 --> 00:12:28,614 But I, I find myself using p SQL quite often in the browser. 192 00:12:29,994 --> 00:12:32,874 using, no, like connecting to some virtual. 193 00:12:33,662 --> 00:12:38,428 both AWS and allows it, and, and then I run ps sql, they're in Tmax. 194 00:12:38,598 --> 00:12:41,548 So if I disconnect, I can continue and that's it. 195 00:12:41,818 --> 00:12:42,948 It's version. 196 00:12:43,018 --> 00:12:45,788 I want everything as I, as I want, and so on and so on. 197 00:12:45,905 --> 00:12:49,641 I tend to use, uh, CLI just because of the reasons I mentioned. 198 00:12:49,641 --> 00:12:56,081 I won't predictability and I don't like to use mouse at all, or tech pad and so on and so on, but I, I understand the. 199 00:12:56,696 --> 00:13:08,476 Who like ui, I understand this because if you do it not, not every day, it's easier to understand what to do because it has some menus and you can choose what to do and so on and so on. 200 00:13:08,476 --> 00:13:10,516 Copy paste easier and so on. 201 00:13:11,646 --> 00:13:12,576 Michael: Yeah, exactly. 202 00:13:13,266 --> 00:13:15,176 Uh, on the command line front, though, it is. 203 00:13:16,396 --> 00:13:22,086 It's awesome being able to do stuff in the browser in even various apps like Google Cloud, for example. 204 00:13:22,496 --> 00:13:35,531 If, if I'm on my mobile, I don't have my laptop with me and I'd need to do something, it's really cool that I can, so I think it is good to get, even if you are a GOI user, it's good to get comfortable enough with the CLI so that in an emergency or 205 00:13:35,646 --> 00:13:37,766 Nikolay: you don't use CLI on mobile phone. 206 00:13:38,371 --> 00:13:39,281 Michael: No, no, I do. 207 00:13:39,281 --> 00:13:39,961 So yeah. 208 00:13:39,961 --> 00:13:40,961 What, what, is 209 00:13:40,986 --> 00:13:41,926 Nikolay: CLI terminal. 210 00:13:42,726 --> 00:13:45,006 SSH to some, somewhere from phone. 211 00:13:45,647 --> 00:13:46,657 I do it all the, time. 212 00:13:46,755 --> 00:13:47,554 Michael: that's what I mean. 213 00:13:47,554 --> 00:13:50,474 I think it's useful to be familiar with the c l I for that purpose. 214 00:13:50,474 --> 00:13:55,218 Like if, if in an emergency you have to, having at least some level of confidence there. 215 00:13:55,598 --> 00:13:58,183 But for, everyday use, I don't tend to, anyway. 216 00:13:58,253 --> 00:14:01,063 Should we go back to the kind of the basics? 217 00:14:01,133 --> 00:14:01,943 Yeah, exactly. 218 00:14:02,618 --> 00:14:11,823 Nikolay: So, yeah, it, it, it allows you to work in an interactive mode and also to work in like, fully scripted, uh, mode, taking data from files. 219 00:14:12,373 --> 00:14:12,933 Right. 220 00:14:13,243 --> 00:14:20,935 It's, it's super good because, uh, you can put those files to get and then to execute them using PC SQL in, uh, reliable form. 221 00:14:21,435 --> 00:14:32,295 One of the terrible mistake, uh, you can make here, and I did it, uh, you can, for example, forget for, for example, utilize the output of PC sql. 222 00:14:32,755 --> 00:14:38,985 Somehow in, in some shell scripting, I doesn't matter, and you expect some form of output. 223 00:14:38,985 --> 00:14:46,106 But, then on server you might have dot, p sql RC file, for example. 224 00:14:46,106 --> 00:14:51,746 People like to to, to change prompt or to set timing on or something like that, right? 225 00:14:52,326 --> 00:14:55,986 And if your script doesn't expect it, it can fail. 226 00:14:56,296 --> 00:15:00,127 I had an answerable playbook, which failed only on production because. 227 00:15:00,637 --> 00:15:08,330 All lower environments didn't have, PSK rrc, but on one of servers on production, someone put PSK rrc. 228 00:15:08,730 --> 00:15:15,448 Since then, I always use dash capital X, which says ignore psk rrc. 229 00:15:16,098 --> 00:15:21,368 So any scripted approach should include dash capital X 230 00:15:21,903 --> 00:15:23,033 Michael: That's a great tip. 231 00:15:23,118 --> 00:15:25,848 Nikolay: yeah, just keep, uh, custom setting. 232 00:15:26,543 --> 00:15:29,363 And start from default setting. 233 00:15:29,363 --> 00:15:30,083 And that's it. 234 00:15:30,533 --> 00:15:32,343 Michael: That takes us to something else though. 235 00:15:32,343 --> 00:15:33,503 Have you ever shared? 236 00:15:33,593 --> 00:15:37,863 So it is cool that you can customize that, um, that, that config. 237 00:15:37,863 --> 00:15:40,983 Have you ever shared your setup or know any good re places 238 00:15:41,073 --> 00:15:51,588 Nikolay: I, yeah, I tried to, uh, yeah, I try, I remember I tried back in times to, I grabbed, uh, set ups for Tmax VI and also then started PC SQL and started to maintain it. 239 00:15:51,588 --> 00:15:56,148 And then I ended up to use to work with defaults, for example, in Tmax. 240 00:15:56,258 --> 00:15:59,468 I, I originally worked with non-default, uh, control a. 241 00:16:00,118 --> 00:16:02,228 As a control, uh, key. 242 00:16:02,228 --> 00:16:11,108 When you do something in Tmax, then I switch to defaults because sometimes you work with servers, clients have, and you don't have time to set it up properly. 243 00:16:11,178 --> 00:16:24,458 With P sql, it's the same for me because I usually don't connect p SQL remotely to some distant machine I work Machine, which is close to in infrastructure, like it can be production infrastructure. 244 00:16:24,798 --> 00:16:27,298 And there I don't have time to set it up unfortunately. 245 00:16:27,834 --> 00:16:33,532 but in some cases I connect, through a tunnel, a sage port forwarding Sometimes it happens. 246 00:16:33,822 --> 00:16:40,172 In this case, I, I have something on my machine, but it's not, uh, some big psrc. 247 00:16:40,422 --> 00:16:41,772 Instead, I have a project. 248 00:16:42,537 --> 00:16:47,912 POCUS DBA on GitHub, which provides interactive menu inside p. 249 00:16:48,505 --> 00:16:49,325 Did you see it? 250 00:16:50,220 --> 00:16:51,990 Michael: I've seen you share something else. 251 00:16:51,990 --> 00:16:54,430 The P S P G pager, is that, is 252 00:16:54,430 --> 00:16:54,710 that what 253 00:16:54,870 --> 00:16:55,190 Nikolay: no, no. 254 00:16:55,400 --> 00:16:57,510 Oh, that's great tool, great addition. 255 00:16:57,510 --> 00:17:02,830 I, I, when I have opportunity to customize pc, I always use PS p g. 256 00:17:03,220 --> 00:17:07,150 It's easy to install it from on, on Brew, on Apple everywhere. 257 00:17:07,150 --> 00:17:11,670 Packages have on all popular platforms, it's developed by p. 258 00:17:13,045 --> 00:17:15,445 , I might pronounce it wrong, sorry, from Czech Republic. 259 00:17:15,445 --> 00:17:16,605 And, uh, it's great. 260 00:17:16,605 --> 00:17:17,125 I followed. 261 00:17:17,635 --> 00:17:18,645 I like that. 262 00:17:18,645 --> 00:17:20,965 The way it, like it, it replaces less. 263 00:17:21,455 --> 00:17:26,285 So if you have a white table, you can scroll horizontally inside your terminal. 264 00:17:27,045 --> 00:17:27,885 Vertically, horizontally. 265 00:17:28,076 --> 00:17:35,436 Similarly to Google spreadsheets, you can freeze first couple of columns, so it's interesting. 266 00:17:35,436 --> 00:17:37,836 And then you can switch it off temporarily, switch back. 267 00:17:37,836 --> 00:17:38,716 It's everything works. 268 00:17:38,716 --> 00:17:40,676 And also colorful themes. 269 00:17:41,216 --> 00:17:43,516 You can adjust many things. 270 00:17:43,806 --> 00:17:48,780 So I, I, I think it's a must have for those people who who can afford. 271 00:17:49,153 --> 00:17:59,211 working on with customized pc SQL and who spend a lot of time like myself in Pesco, but I'm talking about something else for, quick analysis of health. 272 00:17:59,761 --> 00:18:07,281 I have postpositive DBA package, which is a, a, set of scripts, uh, SQL scripts, and it provides you a menu. 273 00:18:07,411 --> 00:18:11,041 So you type, uh, column DBA and see a menu in P sql. 274 00:18:11,291 --> 00:18:12,961 It can and can choose from menu. 275 00:18:12,961 --> 00:18:14,641 Like for example, zero is. 276 00:18:15,236 --> 00:18:17,146 Uh, basic cluster information. 277 00:18:17,776 --> 00:18:21,175 B one is, hip bloat estimate analysis. 278 00:18:21,745 --> 00:18:31,535 B2 is index blo estimate analysis with preformat, like quite good format for physical with some understanding that, uh, we live in console. 279 00:18:32,216 --> 00:18:43,183 So yeah, it even has, column data analysis, you know, this, uh, alignment, paging, paging alignment problem when, when we want to reorder columns and save some space. 280 00:18:43,183 --> 00:18:48,863 It also has this, this report, and, uh, it exists for several years on GitHub. 281 00:18:48,863 --> 00:18:50,383 Some people use it. 282 00:18:50,413 --> 00:18:52,343 I know it's convenient. 283 00:18:52,343 --> 00:18:59,703 Sometimes quickly check the health of this node you connect it to, but you need to have it on your machine, so you need to install. 284 00:19:00,413 --> 00:19:00,903 Michael: Yeah. 285 00:19:00,948 --> 00:19:01,148 Nikolay: Additionally. 286 00:19:01,148 --> 00:19:01,628 Yeah. 287 00:19:02,543 --> 00:19:10,586 Michael: In terms of beginner things, I, I think this website you talked about from Leticia is, is great for people to discover new things that you can do with PC Equal. 288 00:19:10,586 --> 00:19:14,066 The other place I would recommend going is the Postgres docs. 289 00:19:14,066 --> 00:19:17,066 There's, there's just a whole host of those. 290 00:19:17,726 --> 00:19:18,646 What would you call them? 291 00:19:18,646 --> 00:19:20,526 Back slash commands of different 292 00:19:20,611 --> 00:19:24,597 Nikolay: I would recommend remembering, memorizing all the backslash, uh, question mark. 293 00:19:25,468 --> 00:19:25,958 Michael: yeah. 294 00:19:25,958 --> 00:19:26,878 Fair, fair help. 295 00:19:26,878 --> 00:19:27,158 Right. 296 00:19:27,348 --> 00:19:30,768 Nikolay: for help on PC SQL commands of the all PC SQL commands. 297 00:19:30,768 --> 00:19:35,288 Start with backslash and, uh, I don't remember all of them all. 298 00:19:35,358 --> 00:19:37,088 I always check them always. 299 00:19:37,628 --> 00:19:38,048 And 300 00:19:38,098 --> 00:19:49,878 Michael: But there, there are, just to give people an idea of the kinds of things they can do, the ones that I see make people most commonly using are things like to get the schema of a table or to see all the indexes in a table like you can do. 301 00:19:49,878 --> 00:19:51,078 There's whole host of these. 302 00:19:51,793 --> 00:20:03,143 Queries that are quite complex to write in terms of the ca like, like the Postgres catalog data, but they vastly simplify those so you can quickly, quickly do them. 303 00:20:03,293 --> 00:20:15,686 Nikolay: and you also can learn even if you are not heavy Pascal user, you can learn how to deal with catalogs if you enable this like some like echo hidden com, I don't remember, hidden Something you can enable. 304 00:20:15,756 --> 00:20:19,298 It's easy to find, but you can enable print. 305 00:20:19,375 --> 00:20:22,025 a sequel that is behind those comments. 306 00:20:22,025 --> 00:20:24,745 So when you say backslash, d plus somet table. 307 00:20:25,145 --> 00:20:32,365 You can see how PS SQL collected that and four from positives, so you can repeat it in your program, for example, if you need it. 308 00:20:32,715 --> 00:20:44,165 Also, a good tip here if, if you forgot some function standard or your custom function, for example, I always forget, uh, how to check like last trip. 309 00:20:47,145 --> 00:20:48,275 I use it all the time. 310 00:20:48,275 --> 00:20:57,355 Many years I always forget and I don't need to to remember it because I, what I do usually, backslash df means, means describe functions. 311 00:20:58,045 --> 00:21:01,635 Uh, asterisk Ellison asterisk, and I see all the functions. 312 00:21:01,845 --> 00:21:12,715 So I have a mask, uh, uh, like kind of simple search, and I see all functions that have lsn or timestamp, and I choose, that's it like. 313 00:21:14,075 --> 00:21:14,565 Michael: Yeah. 314 00:21:14,565 --> 00:21:15,125 Very cool. 315 00:21:15,870 --> 00:21:18,229 Nikolay: And so backslash, question mark. 316 00:21:18,229 --> 00:21:28,617 It's to see all comments that, uh, PSK supports, but also there is important different thing, backslash h which, is a help, tool for, uh, SQL itself. 317 00:21:29,242 --> 00:21:33,982 for example, you forgot syntax, you forgot syntax for delete comment, for example. 318 00:21:34,152 --> 00:21:37,342 It maybe one of easiest comments, but you forgot, right? 319 00:21:37,562 --> 00:21:41,062 You can write, uh, back slash h delete and see it. 320 00:21:42,352 --> 00:21:44,502 So you see like synopsis. 321 00:21:44,502 --> 00:21:48,702 I understand how, which keywords to use and which in which order, and so on. 322 00:21:49,692 --> 00:21:51,362 Michael: So backlash question mark. 323 00:21:51,362 --> 00:21:53,442 Help for PC SQL itself. 324 00:21:53,442 --> 00:21:54,842 See all the commands that are possible. 325 00:21:55,122 --> 00:21:56,762 Backlash H is help for 326 00:21:56,867 --> 00:21:57,407 Nikolay: All right. 327 00:21:57,407 --> 00:21:57,887 All right. 328 00:21:57,887 --> 00:22:00,927 These are two main important, most important backslash comments. 329 00:22:00,927 --> 00:22:03,567 You need to remember all others you will find from there 330 00:22:03,952 --> 00:22:05,082 Michael: Yeah, I like it. 331 00:22:05,349 --> 00:22:07,879 I know there's lots we could talk about for pc sql, 332 00:22:08,249 --> 00:22:08,599 um, 333 00:22:08,864 --> 00:22:08,944 Nikolay: I 334 00:22:09,384 --> 00:22:09,744 variables 335 00:22:11,369 --> 00:22:11,879 Michael: Oh yeah. 336 00:22:11,879 --> 00:22:14,439 Yeah, we, I mean I've even, I've used variables, right? 337 00:22:14,439 --> 00:22:18,719 The explain analyze syntax is quite, uh, is quite long. 338 00:22:18,769 --> 00:22:20,834 So that can be, could be really helpful. 339 00:22:21,694 --> 00:22:28,674 Str uh, so yes, you can, you can, you can define, do you define that in your piece of glass C or just on the fly as you're going? 340 00:22:29,024 --> 00:22:30,204 Nikolay: You can do a lot of things. 341 00:22:30,204 --> 00:22:35,444 If you look, uh, at inside my Posts DBA project, you'll find interesting things. 342 00:22:35,444 --> 00:22:38,093 For example, backslash if slash ELs. 343 00:22:39,463 --> 00:22:40,943 variables and so on and so on. 344 00:22:40,943 --> 00:22:44,103 Like many things, it's basically like some kind of macro language. 345 00:22:44,103 --> 00:22:54,044 You can script, uh, you can do scripts for pc sql, I usually, when I write a script for PC SQL itself, I, create a file dot pc, sql something dot pc. 346 00:22:54,824 --> 00:22:59,324 And, uh, this is how I understand, not just, it's not just sql, it's something else. 347 00:22:59,514 --> 00:23:02,724 It's of course, it's a combination of regular SQL and, and P sql. 348 00:23:04,499 --> 00:23:04,989 Michael: Nice. 349 00:23:05,294 --> 00:23:08,024 Nikolay: Yeah, we need to remember context in positives. 350 00:23:08,024 --> 00:23:09,184 I had a problem yesterday. 351 00:23:09,214 --> 00:23:17,653 I worked with, uh, PLP P Scale function, and it had select something into variable and vari. 352 00:23:17,653 --> 00:23:20,573 Like you can select something from table into variable. 353 00:23:20,573 --> 00:23:23,413 You declare variable in the declare section. 354 00:23:24,073 --> 00:23:28,093 And then I, I wanted to repeat the sta the same steps in p. 355 00:23:28,563 --> 00:23:36,593 So I started to copy paste, uh, one query after another, and I copy pasted as, as is select something into some, into variable name. 356 00:23:36,713 --> 00:23:37,793 Guess what happened? 357 00:23:38,953 --> 00:23:44,584 It worked, First, my thought was, is it inserting result to P SQL variable? 358 00:23:44,594 --> 00:23:44,944 No. 359 00:23:44,994 --> 00:23:52,344 It, it was not, it was not related to PC sql, it was related to regular sql, uh, pogs version of SQL Postgres. 360 00:23:52,344 --> 00:23:53,144 Dialect of sql. 361 00:23:53,364 --> 00:23:58,204 You can select something into blah, blah and this 362 00:23:58,204 --> 00:23:58,644 blah, blah. 363 00:23:59,414 --> 00:24:00,724 It creates a table. 364 00:24:00,724 --> 00:24:02,004 It's a DDL comment. 365 00:24:02,549 --> 00:24:02,969 Michael: Wow. 366 00:24:03,399 --> 00:24:03,889 Wait. 367 00:24:03,889 --> 00:24:05,729 It creates a permanent table or a 368 00:24:05,889 --> 00:24:06,609 Nikolay: Permanent. 369 00:24:06,609 --> 00:24:07,249 It's like create 370 00:24:07,249 --> 00:24:08,049 table as select. 371 00:24:08,539 --> 00:24:15,109 It starts with select ends with into something and it creates a table. 372 00:24:15,119 --> 00:24:17,789 It, it exists from from nineties, so it's very old. 373 00:24:19,069 --> 00:24:20,239 I would remove it actually. 374 00:24:20,559 --> 00:24:20,799 . It's 375 00:24:21,169 --> 00:24:23,599 Michael: Is it SQL Standard I'm guessing, or is it 376 00:24:23,639 --> 00:24:24,669 Nikolay: in sequel standard. 377 00:24:25,474 --> 00:24:29,504 So in lp PPG scale, it's dml, it's data modification language. 378 00:24:29,504 --> 00:24:30,664 We just select something. 379 00:24:30,664 --> 00:24:33,704 We memorize it in, in variable of type of record. 380 00:24:33,704 --> 00:24:43,584 For example, all good in S SQL context, including in P sql, without any the anonymous DU blocks and so on, it's G D L. 381 00:24:43,584 --> 00:24:44,584 It creates a table. 382 00:24:44,584 --> 00:24:47,104 Second attempt will fail saying table exists. 383 00:24:48,554 --> 00:24:52,664 in standard sequel as PO's documentation says, I didn't check. 384 00:24:53,624 --> 00:24:55,038 The, standard itself. 385 00:24:55,038 --> 00:24:58,958 But documentation says in standard sql, it's also different. 386 00:24:59,072 --> 00:25:05,522 it's used to get data and uh, store it in, in client side somehow. 387 00:25:05,532 --> 00:25:06,722 So it's dml again, 388 00:25:07,343 --> 00:25:08,103 Michael: Makes sense. 389 00:25:08,203 --> 00:25:10,053 Nikolay: it's very, very confusing. 390 00:25:10,158 --> 00:25:17,992 part of sql and I know when I learned about it, my final thought was I want to unsee and. 391 00:25:19,202 --> 00:25:22,982 . And then a second idea was maybe I did it already in the past 392 00:25:23,422 --> 00:25:26,822 That's why it surprises me again, so don't go there. 393 00:25:27,062 --> 00:25:29,862 Actually selecting to is dangerous and yeah. 394 00:25:30,128 --> 00:25:30,788 Use create 395 00:25:30,788 --> 00:25:31,028 table. 396 00:25:31,430 --> 00:25:33,560 Michael: yeah, I've got a whole episode on that. 397 00:25:33,560 --> 00:25:33,720 Right. 398 00:25:34,000 --> 00:25:35,040 Materialized views episode. 399 00:25:35,713 --> 00:25:49,923 A couple of other things I wanted to make sure we, or at least get your thoughts on, were I feel like there's a, a natural trade off if you are either a tool maker or you're trying to choose between, like, which tools to recommend to your team or which tool you should be using generally day to day. 400 00:25:50,273 --> 00:25:54,243 I do think there's almost like a power versus discoverability trade off. 401 00:25:54,243 --> 00:25:57,990 Like generally speaking, more will be possible via the c. 402 00:25:58,730 --> 00:26:06,025 . But in terms of discoverability, in terms of working out what you can even do, I think Gooeys have like an advantage on that side at least. 403 00:26:06,935 --> 00:26:09,505 Well, maybe not once you've discovered, uh, back slash 404 00:26:09,855 --> 00:26:11,285 Nikolay: uh, yeah, yeah, yeah. 405 00:26:11,285 --> 00:26:17,805 Well, well, I need to also, to learn each ui you need to learn and remember the interface. 406 00:26:18,345 --> 00:26:28,377 For me, Common line interface is easier because I need to remember some comments and from there I can go in many directions and so on, but it's, it's a matter of taste maybe. 407 00:26:28,807 --> 00:26:35,507 But I would, I would, for serious work, I would always use CLI because I need to automate everything. 408 00:26:36,137 --> 00:26:39,667 I need to put everything to get, I need to share my actions. 409 00:26:40,137 --> 00:26:41,827 I need to be able to repeat them. 410 00:26:42,447 --> 00:26:44,827 And with UI, it's very difficult. 411 00:26:45,427 --> 00:26:46,227 Scripting. 412 00:26:46,227 --> 00:26:47,667 Scripting is is very important. 413 00:26:48,912 --> 00:26:50,202 Michael: Yeah, for sure. 414 00:26:50,202 --> 00:26:51,402 With some, some UIs. 415 00:26:51,472 --> 00:26:56,642 I think, like for example, the data group one, I think allows you to do a lot of stuff from like saved files. 416 00:26:57,274 --> 00:26:59,954 but yeah, I do, I think they have some other advantages as well. 417 00:26:59,954 --> 00:27:11,216 Like I saw you were trying to get the, uh, some functionality into Postgres at the like, I'm not even sure actually what level, but for example, if you did a delete or an update without a wear clause, 418 00:27:11,473 --> 00:27:11,963 Nikolay: Yeah. 419 00:27:12,083 --> 00:27:20,483 Michael: feature, a feature of some graphical user interfaces is that they will prompt you and set up, like send you a wa like put a warning into the UI before. 420 00:27:20,753 --> 00:27:21,243 Yeah. 421 00:27:21,693 --> 00:27:30,767 So, I feel like you could implement that at the CLI level, but it's, it feels like more of a, it feels like more getting in somebody's way than a gooey that's there to help somebody. 422 00:27:30,857 --> 00:27:31,637 Nikolay: you know what? 423 00:27:31,637 --> 00:27:38,637 I think, uh, since pauses is very extendable in, it's in its philosophy, I think it's a good idea. 424 00:27:39,587 --> 00:27:44,277 I, I think it's not a new, new idea, but I, I, I'm still like releasing this idea. 425 00:27:44,607 --> 00:27:48,417 It would be great to have plugins for physical, right? 426 00:27:48,547 --> 00:27:54,240 So for example, it could be a plugin that, uh, reminds you like, that maybe even blocks you from sending. 427 00:27:54,935 --> 00:28:00,375 Dangerous, delete or update to POCUS itself and asks for confirm confirmation or something. 428 00:28:00,485 --> 00:28:01,055 I dunno. 429 00:28:01,395 --> 00:28:15,855 But uh, I also see things like, for example, we had a small project and it caught, caught some attention on our website that will, like you are in piece, you run explain lies buffer. 430 00:28:16,525 --> 00:28:31,555 Buffers, buffers always buffers, and you, you see the plan, but also the same plan is sent to explain the com or explain d com or somewhere else or master.com and, and then you, you as a result. 431 00:28:31,555 --> 00:28:33,635 You see right in physical, you see the link. 432 00:28:33,805 --> 00:28:39,835 Usually, for example, in item two on my mark, I can, uh, use either control or, or comment. 433 00:28:39,835 --> 00:28:41,635 I don't remember and click on the link. 434 00:28:41,635 --> 00:28:45,555 And in my browser, I already see visualization, right In this case. 435 00:28:46,210 --> 00:28:47,340 Yeah, it's easier. 436 00:28:47,340 --> 00:28:50,940 You are like working in normal way, but sometimes you want visualization. 437 00:28:51,090 --> 00:28:51,860 Here it is. 438 00:28:52,510 --> 00:28:57,980 So it could be a plugin that would work even better from your, from cli. 439 00:28:58,160 --> 00:29:02,660 And this could be some connection between these two worlds, right? 440 00:29:02,850 --> 00:29:04,500 Some plugins could implement it. 441 00:29:05,505 --> 00:29:09,755 Michael: Yeah, if making it extensible would be awesome, uh, for sure. 442 00:29:10,542 --> 00:29:12,853 there is, there are alternative CII tools, right? 443 00:29:12,853 --> 00:29:13,093 Like 444 00:29:13,093 --> 00:29:15,493 the, the, the only, yes. 445 00:29:15,753 --> 00:29:17,253 And I think sh uh, it's 446 00:29:17,603 --> 00:29:19,013 Nikolay: It's certain Python, right? 447 00:29:19,733 --> 00:29:20,293 Irina? 448 00:29:20,938 --> 00:29:22,028 Michael: Yeah, absolutely. 449 00:29:22,459 --> 00:29:29,249 so a Ukrainian engineer has done an excellent job maintaining that and it, I've not used it myself, but I've heard 450 00:29:29,324 --> 00:29:32,294 Nikolay: auto complete colorful everything. 451 00:29:32,294 --> 00:29:34,814 Right, so it's like more French tool For, 452 00:29:34,814 --> 00:29:35,134 For, 453 00:29:36,389 --> 00:29:37,289 Michael: I'm quite smart. 454 00:29:37,289 --> 00:29:41,049 Autocomplete, quite like smarter auto complete than I've seen in some GOI tools. 455 00:29:41,299 --> 00:29:42,289 So yeah. 456 00:29:42,289 --> 00:29:42,609 Really? 457 00:29:42,699 --> 00:29:43,329 Uh, nice. 458 00:29:43,854 --> 00:29:52,584 Nikolay: It's not only auto complete for like in physical, you has have it, but you need to double tap twice. 459 00:29:53,124 --> 00:29:55,304 Tab, for example, double tap on tab. 460 00:29:55,990 --> 00:29:57,590 there it has dropdowns, right? 461 00:29:57,590 --> 00:29:59,030 And you can choose or so yeah. 462 00:29:59,030 --> 00:29:59,630 That's great. 463 00:30:00,548 --> 00:30:01,708 Michael: Anything else you wanted to make sure we. 464 00:30:02,688 --> 00:30:03,578 Nikolay: I think that's it. 465 00:30:03,578 --> 00:30:06,900 Uh, of course, uh, such thing as, interface. 466 00:30:06,900 --> 00:30:12,630 It, it'll be good to watch, there is a number of good videos on YouTube. 467 00:30:12,630 --> 00:30:14,150 For example, some presentations. 468 00:30:14,150 --> 00:30:20,030 We can collect some ideas to continue for those who are interested in our show notes. 469 00:30:20,370 --> 00:30:24,030 But what we try to do is to discuss princip. 470 00:30:24,745 --> 00:30:26,325 And starting points, right? 471 00:30:26,325 --> 00:30:30,325 Because this is how we can help without sharing screen and so on. 472 00:30:30,610 --> 00:30:31,100 Michael: Yeah. 473 00:30:31,130 --> 00:30:37,900 I'll include links to all the visual ones that we mentioned, just so people can see the, each of them will have screenshots on their homepage, I'm sure. 474 00:30:38,160 --> 00:30:38,638 Nikolay: Good. 475 00:30:38,792 --> 00:30:42,512 thanks to all people who share feedback. 476 00:30:42,512 --> 00:30:47,632 We had, again, good wave of feedback on Twitter and uh, ideas. 477 00:30:47,712 --> 00:30:49,112 Thank you for all idea. 478 00:30:50,097 --> 00:30:56,568 and thank you for listening us While you are walking your dog or running or just walking, maybe swimming. 479 00:30:56,688 --> 00:30:57,248 Right. 480 00:30:58,488 --> 00:31:01,448 Raise your hand if you, you are currently swimming. 481 00:31:01,718 --> 00:31:05,848 I would like to know because I, I also do it sometimes with special headphones. 482 00:31:06,558 --> 00:31:07,048 Yeah. 483 00:31:07,903 --> 00:31:08,733 Michael: Oh, that's funny. 484 00:31:08,733 --> 00:31:10,133 Alright, well take care of everybody. 485 00:31:10,498 --> 00:31:11,168 Nikolay: Bye-Bye.