Postgres FM - psql vs GUIs

Episode Date: March 24, 2023

Nikolay and Michael discuss command line and graphical user interfaces for Postgres — what they are, some tips and tricks for learning, and what we each use and prefer. Here are links to a few things we mentioned: psql (docs)psql is awesome! (talk by Lætitia Avrot)psql tips (site by Lætitia Avrot)pgAdminPostico DBeaverDataGripPgManage (new Command Prompt fork of OmniDB) PopSQLpostgres_dbapspgMaterialized views episodepgcli------------------------What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Hello and welcome to PostgresFM, a weekly show about all things PostgresQL. I'm Michael, founder of PGMustard. This is my co-host Nikolai, founder of PostgresAI. Hey Nikolai, what are we talking about today? Hi Michael, is it your choice or mine? Oh, is it boring or not? That's the question. Yeah, it's mine, right? So it shouldn't be boring. For some folks it might sound boring, very boring, because we are going to talk about psql psql command line versus ui versus gui and some people think it's very very boring to work in terminal very like

hacky boring inconvenient in my experience and in my opinion, this is super cool tool. I use it a lot. It's not boring at all. It's super flexible, powerful. It lacks something still. We probably will talk about it. But I find this topic not boring at all. Yeah, so there's P-SQL itself as the default command line interface for Postgres, ships with Postgres, awesome tool. But there's also the higher level discussion

of command line interfaces versus graphical user interfaces. To work with databases in this context, right? Yeah, specifically for Postgres. I was going to say even go as narrow as just talking about the ones that work with Postgres. Right. So PSQL is a command line tool. It's a client program, right? A client program

working in Terminal. And it's installed usually in Ubuntu, for example, you install a client package, a PostgreSQL client. I would like to mention that there are more client programs standard shipped with Postgres distribution. For example, pgdump, right right it's pgrestore or pgbench but in ubuntu somehow pgbench goes to server package yeah it's interesting it's strange sometimes we for example sometimes we have older database installed and we want newer client tools because there is backward compatibility we know that

newer client tools can perfectly work usually with older database so and they have some features for example pgbench has some feature or psql has some feature and we are trying to install client it will be fine for psql pgdump pgrestore it won't be fine for pgbench for pgbench you need to install server package for newer version which is strange just a side note that's a really good starting point though because it is worth noting that sometimes these client features then 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 in many cases so right yeah yeah yeah. Yeah. Of course, not always. Because they work usually at meta level, for example, like we, we can, for example, on

Postgres TV with Andrei Baradin, we're trying to extend backslash watch PC SQL comment to add more options to it. It accepts only one option. It runs some query in infinite loop. And originally, it accepts only one option, the sleep time between loops. So we wanted to add another option, how many loops we want. Should we go back to think of it sometimes from a beginner angle, and sometimes from like the advanced user angle, I see a lot of advanced users in love with P-SQL or PSQL. I think Leticia did a really good talk on Postgres TV and done it at several conferences. Huge number of tips on that website, PSQL tips.

Yes, exactly. She also has that website with, I think you can have it load a random tip or you can go through them one by one. But yeah, well over 100 now that she's got listed there. And that's kind of both great and also 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, of a beginners to know what's even possible. So is there like, where would you recommend getting started for somebody?

I would recommend getting started with Linux Terminal. Learning TMAX and VI. Maybe not VI, but TMAX definitely, because if you're disconnected, you're doing something on server or in some infrastructure. Even if it's RDS and you're working from some machine, it should be closer to RDS and should not be dependent on your connectivity and so on, right? So if your home internet, like mine, very often is not good, T-Max saves you.

This is where I think everyone should start and learn some basics of these great tools, Tmux and VI. And then just consider one particular reason why everyone should use P-SQL at least from time to time. This reason is predictability and repeatability of your steps. If you do something in UI, try to do it once again. You might click a different button and that's it.

You cannot program with and put it to Git. Well, there are tools for UI programming like recorders. I remember 20 plus years ago, there was a rational robot or something. Many things created, Selenium and so on. But when you work in terminal, it's natural for you to code it in form of script and share with colleagues, these are my steps to repeat them, to be very predictable, testable, and so on, deployable.

Yeah, I think that's a really good reason. The other, and two more good reasons. One is it's installed everywhere you're going to have Postgres. You're going to be able to have access to pcq i think that's a really good reason and then another one is not just predictable but reliable it's the most reliable interface i've ever seen to push this so i've seen a lot of guis have weird bugs or like every now and again i would say trust exactly so quite often in a Postgres community, you might see somebody seeing some weird behavior.

And one of the first questions people ask is, do you get that exact same behavior when you run the query in P-SQL? And if you don't, it's a problem at the editor level. There's something going on in between. Or the interface level. We can claim that P-SQL is Postgres. All UIs are not Postgresgres postgres doesn't have ui

yeah so psql i think the code is held to the same standard as the postgres code base which is a very high standard right unlike any ui postgres itself doesn't have any ui and pg admin some people confuse it with like considering it as a standard UI for Postgres but it is advertised on postgresql.org website but it has a different product, it's not shipped with Postgres, it has different

release process, release cycle different team and so on, it's basically under EnterpriseDB EDB is working on it single company I can see where the confusion comes from though under Enterprise DB. EDB is working on it. Single company. I can see where the confusion comes from, though, because if you install Postgres via one of the EDB-maintained installers,

like the Windows installer or the Mac installer that they host, I think, on Postgres.org or PostgresQL.org, it does come with pgAdmin. And I think that's where a lot of the confusion comes from so it's understandable but it is worth uh in case you didn't know it's worth noting that it isn't official and equally it doesn't have as high a market share like when i looked into so my background is in just in gree tools yeah exactly i i was hoping when i came to the postgres world

to be able to make an extension to a popular editor so to make a tool for a popular editor interesting coming from coming from the sql server world or the oracle world were quite a few editors and even my sql were quite a few editors at the time had you know tens of percent of market share in the postgres ecosystem that just isn't the case. A lot of people are using CLIs. A lot of people are using a vast array of different GUIs. So it's much more difficult to pick. Or you'd have to build something that would work with lots. It's open source bazaar, you know.

It's not cathedral in this case. But I wish Postgres had standard UI. I wish it had it. Yeah. It could be cool. But I do understand why they don't. I think it's a, but I do think

it's a challenge for the ecosystem that there isn't one. When some UI, sorry for interrupting, but when some UI, I experienced it many times

in my own projects, when UI is added, it quickly becomes more than 50% of everything. Of development, of code base, of issues, goals, tasks, everything.

So it can be much bigger than engine itself. And it's very different because maybe not all will use it. Well, still, I think Postgres needs UI, standard UI. It's my own opinion. I see how things are right now and i have i think it's very very low chance it will be changed as pg admin is something i never used and don't recommend to anyone i reevaluate it every couple of years and consider it's going the wrong direction having problem since birth like the new pg admin working in browser and so on like nice idea but

implementation i consider it's very poor and cannot recommend to anyone if someone wants ui it's better to consider postico on mac or db or cloud beaver anywhere or the tool from jet brains how's it called data grip right data grip yeah these tools are much better much better yeah i have heard that pg admin 4 is the online version uh or the sorry the web-based version i have heard it's been getting better in later versions but i do still come across quite a few people that have issues. Like, from what I saw, you cannot improve it. You need to destroy it and start from scratch and probably with different approaches. Like, I can be specific, but we need to open screen sharing and I will show you details. For example, you cannot query Postgres all the time in loop. Maybe they fixed it, I don't know. But it's so wrong idea to query my Postgres all the

time in loop to deliver some monitoring information, which I don't need at all. I want to disable it, but I don't need to do like I just want to send my queries. Don't spam my Postgres, right? Yeah. And there are tons of alternatives. I mean, I said that in passing, but you mentioned some great ones there. dBeaver especially. dBeaver maybe is the leading alternative. Yeah, DataGrip definitely has some benefits. dBeaver, I hear a lot of people that work with geodata

love it for the visualizations, and that's one of maybe the main reasons to use a graphical tool is if you're looking at things visually. But I are, I wanted to give a shout out to my others, my personal favorite, and this is actually an admission I should probably have made up the top of the episode. I actually tend to use a GUI for working with Postgres. I don't do that much with Postgres. Partly because I don't do that much with it day to day.

Like I'm working on a product for postgres not necessarily doing postgres stuff all day but also when 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 so my personal favorite at the moment is postico that you mentioned very fast to open very reliable but it is mac only there's loads of of others, like TablePlus is really popular. OmniDB was popular, but it's been revived recently by Command Prompt. I think they've called it PG Manage.

There's loads of online alternatives now. There's some cool ones like Popsicle. That's how I think you pronounce it, PopSQL. And then sadly, ArchType got acquired. That was looking promising. The Beaver has CloudBeaver version, which is also interesting. You know what I use more and more? For me, I like to do everything in the browser.

I'm a very strange person, probably. I like terminals, and I like everything in the browser. And this is very strange, but I know it. But still, this is my two maybe conflicting goals. But I find myself using PSQL quite often in the browser. Usually, I know that connecting to some virtual machine, both AWS and GCP allows it, and then I run PSQL there in T-Max. So if I disconnect, I can continue. And that's it. It's the version I want, everything everything as i want and so on and so on i tend

to use cli just because of the reasons i mentioned i want predictability and i don't like to use mouse at all or techpad and so on and so on but i understand the people 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. Copy, paste, easier and so on. Yeah, exactly. On the command line front, though, it's awesome being able to do stuff in the browser or in even various apps like Google Cloud, for example. 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 GUI user, it's good to get comfortable enough with the CLI,

so that in an emergency or if you... So you don't use CLI on mobile phone? No, no, I do. So yeah, what is that? CLI, terminal. SSH to somewhere from phone. I do it all the time. That's what I mean. I think it's useful to be familiar with the CLI for that purpose. If in an emergency, you have to having at least some level of confidence there.

But for everyday use, I don't tend to. Anyway, should we go back to the basics? Yeah, exactly. So, yeah, it allows you to work in an interactive mode and also to work in like fully scripted mode taking data from files right it's super good because you can put those files to git and then to execute them using psql in reliable form one of the terrible mistake you can make here and I did it. You can, for example, forget, for example, you analyze the output of PC SQL somehow in some shell scripting, doesn't matter. And you expect some form of output. But then on server, you might have.psqlrc file.

For example, people like to change prompt or to set timing on or something like that, right? And if your script doesn't expect it, it can fail. I had an Ansible playbook which failed only on production because all lower environments didn't have.psqlrc. But on one of servers on production, someone put PSQLRC. Since then, I always use dash capital X, which says ignore PSQLRC. So any scripted approach should include dash capital X. That's a great tip. Yeah, just keep custom settings and start from default settings and that's it. That takes us to something else though. Have you ever shared, so it's cool that

you can customize that config. Have you ever shared your setup or know any good places? Yeah, I tried to. I remember I tried back in times i grabbed uh setups for t-max vi and also then started psql and started to maintain it and then i ended up to use to work with defaults for example in t-max i originally worked with non-default control a as a control key when you do something in t-X. Then I switched to defaults because sometimes you work with servers clients have and you don't have time to set it up properly. With P-SQL, it's the same for me

because I usually don't connect P-SQL remotely to some distant machine. I work from machine which is close to it in infrastructure, like it can be production infrastructure. And there I don't have time to set it up unfortunately but in some cases i connect through ssh tunnel ssh port forwarding sometimes it happens in this case i i have something on my machine but it's not some big psql rc instead i have a project called Postgres DBA on GitHub, which provides interactive menu inside PSQL. Did you see it or not?

I've seen you share something else, the PSPG pager. Is that what you're going to mention? Oh, that's a great tool, a great addition. When I have an opportunity to customize PSQL, I always use PSPG. It's easy to install it on Brew, on Apto, everywhere. Packages are on all popular platforms. It's developed by Pavel Stechul. I might pronounce it wrong, sorry, from Czech Republic.

And it's great. I like the way it replaces less. So if you have a white table table you can scroll horizontally inside your terminal vertically horizontally similarly to google spreadsheets you can freeze first couple of columns so it's interesting and then you can switch it off temporarily switch back it's everything works and also colorful themes you can adjust many things so like i think it's a must have for those people who can afford it working on with customized psql and who spend a lot of time like myself in psql but i'm talking about something else for quick analysis of health i have postgres dba package which is a set of scripts, SQL scripts, and it provides you a menu so you type

colon DBA and see a menu in P-SQL. It can choose from menu like for example zero is some basic cluster information, B1 is heap bloat estimate analysis, B2 is index blowout estimate analysis with preformat like quite good formatted for PC cool, with some understanding that we live in console. So yeah, it even has column tatters analysis, you know, this alignment padding, padding alignment problem when, when we want to reorder columns and save some space it also has this this report and it exists for several years on github some people use it i know it's convenient sometimes quickly check the health of this node you're connected to but you need to have it on your machine so you need to install it additionally yeah in terms of beginner things i think this website you talked about from leticia

is great for people to discover new things that you can do with psql the other place i would recommend going is the postgres docs there's there's just a whole host of those back what would you call them backslash commands of different things i would recommend remembering, memorizing only backslash question mark. Backslash question mark. For help, right? For help on psql commands. All psql commands start with backslash. And I don't remember all of them. I always check them.

Always. And also then... Just to give people an idea of the kinds of things they can do, the ones that I see 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 there's whole hosts of these queries that are quite complex to write in terms of the cats like pg like the postgres catalog data but they vastly simplify those so you can quickly quickly do them and you also can learn even if you are not heavy PSQL user, you can learn how to deal with

catalogs if you enable something like echo, hidden, I don't remember, hidden something. You can enable, it's easy to find, but you can enable printing SQL that is behind those comments. So when you say backslash D plus some table, you can see how PSQL collected that info from Postgres. So you can repeat it in your program, for example, if you need it. Also, a good tip here, if you forgot some function, standard or your custom function,

for example, I always forget how to check last replay, last replay commit. I use it all the time, many years. I always forget how to check like last replay lsn, last replay commit I use it all the time many years, I always forget and I don't need to remember it because what I do usually backslash df means describe functions asterisk, lsn, asterisk

and I see all the functions so I have a mask like kind of simple regexp search and I see all functions functions. So I have a mask, like kind of simple regexp search, and I see all functions that have lsn or timestamp, and I choose, that's it, like simple. Yeah, very cool. And also backslash question mark, it's to see all comments that PSQL supports,

but also there is important different thing, backslash H, which is a help tool for SQL itself. For example, you forgot syntax. You forgot syntax for delete comment, for example. It's maybe one of the easiest comments, but you forgot, right? You can

write backslash H, delete, and see it. So you see synopsis, you understand which keywords to use and in which order, and see it. So you see like synopsis, you understand which keywords to use and in which order and so on. So backlash, question mark, help for P-SQL itself, see all the commands that are possible.

Backlash H is help for SQL. Right, these are two main, most important backslash commands you need to remember. All others you will find from there. Yeah, I like it. I know there's lots we could talk about for P-SQL. Variables.

Oh, yeah, yeah. I mean, even I've used variables, right? The explain, analyze syntax is quite long, so that can be really helpful. So, yes, you can define that in your P-QL RC or just on the fly as you're going?

You can do a lot of things. If you look inside my Postgres DBA project, you will find interesting things. For example, backslash if, backslash else, variables, and so on and so on. Like many things. It's basically like some kind of macro language you can script. You can do scripts for PSQL. I usually, when I write a script for PSQL itself, I create a file.psql, something.psql. And this is how I understand it's not just PSQL,

it's something else. It's, of course, it's a combination of regular SQL and PSQL. Right? Nice. We need to remember context in Postgres. I had a

problem yesterday. I worked with PLPG scale function and it had select something into variable and you can

select something from table into variable. You declare variable in the declare section and then I wanted to repeat the same

steps in P SQL. So I started to copy paste one query after another, and I copy paste it as is, select something into variable name. Guess what happened? It worked. First, my thought was, is it inserting result to P SQL variable? No, it was not related to pSQL. It was related to regular SQL Postgres version of SQL, Postgres dialect of SQL. You can select something into blah, blah. And this blah, blah, it creates a table. It's a DDL. Yeah.

Wow. Wait, it creates a permanent table or temporary table? Permanent. It's like create table as select. Wow. It starts with select, ends with into something, and it creates a table. It exists from 90s, so it's very old. I would remove it, actually. It's dangerous.

Is it SQL standard, I'm guessing, or is it... In SQL standard. So in PLPG SQL, it's DML. It's data modification language. We just select something, we memorize it in variable of type of record, for example. In Postgres SQL context, including in PSQL without any anonymous do blocks and so on, it's DDL. It creates a table. Second attempt will fail saying table exists.

In standard SQL, as Postgres documentation says, I didn't check the standard itself, but Postgres documentation says in standard SQL, it's also different. It's used to get data and store it in client side somehow. So it's DML again. It's very, very confusing part of SQL. And I know when I learned about it,

my final thought was, I want to unsee and forget it. And then a second idea was, maybe I did it already in the past. That's why it surprises me again. So don't go there actually. Selecting to is dangerous. And yeah, use create table.

Yeah. Got a whole episode on that, right? Materialized views episode. The couple of other things I wanted to make sure we, or at least get your thoughts on were, I felt like there's a natural trade-off if you're 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.

I do think there's almost like a power versus discoverability trade-off. Like generally speaking, more will be possible via the CLI. But in terms of discoverability, in terms of working out what you can even do, think guis have like an advantage on that side at least well maybe not once you've discovered backslash yeah yeah yeah well i need to also to learn each ui you need to learn and remember the interface for me command 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 a matter of taste maybe. But for serious work I would always use CLI because I need to automate everything, I need to put everything to git, I need to share my actions, I need to be able to repeat them, And with UI, it's very difficult.

Scripting is very important. Yeah, for sure. With some UIs, I think, like, for example, the Datacrit one, I think, allows you to do a lot of stuff from, like, saved files. But yeah, I think they have some other advantages as well. Like, I saw you were trying to get 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 where clause,

a feature of some graphical user interfaces is that they will prompt you and put a warning into the UI before. Yeah. So I feel like you could implement that at the CLI level, but it feels like more getting in somebody's way than a GUI that's there to help somebody. You know what? I think since Postgres is very extendable in its philosophy, I think it's a good idea. I think it's not a new idea, but I'm still revising this idea.

It would be great to have plugins for pc cool right so for example it could be a plugin that reminds you like that maybe even blocks you from sending a dangerous delete or update to postgres itself and asks for confirmation or something i don't know but i also see things for example, we had a small project and it caught some attention on our website. Like you are in PSQL, you run explain, analyze, buffers, buffers, buffers, always buffers. And you see the plan, but also the same plan is sent to explain.dish.com or explain dalibo.com or somewhere else or pgmaster.com yeah and and then you you as a result you see right in psql you see the link usually for example in iterm2 on my mac i can use either control or command i don't remember and

click on the link and in my browser i already see visualization right in this case it's easier you're like working in normal way but sometimes you want visualization here it is so it could be a plugin that would work even better from you from cli and this could be some connection between these two worlds right some? Some plugins could implement it. Yeah. Making it extensible would be awesome, for sure. There are alternative CLI tools, right? Yeah, PG-CLI.

Yes. And I think it's... It's in Python, right? Yes. Yeah, absolutely. A Ukrainian engineer has done an excellent job maintaining that. I've not used it myself, but I've heard some people saying it's awesome. Autocomplete, colorful, everything, right? So it's like more fancy tool for... Yeah, and quite smart autocomplete, quite like smarter autocomplete than I've seen in some GUI tools. So yeah, really nice tool. It's not only autocomplete for like in PSQl you have it, but you need to double tap twice tab, for example, double tap on tab.

There it has dropdowns, right? And you can choose or something. Yeah, that's great. Anything else you wanted to make sure we covered? I think that's it. Of course, such thing as interface, it would be good to watch. There is a number of good videos on YouTube, for example, some presentations. We can collect some ideas to continue for those who are interested in our show notes. But what we try to do is to discuss principles and starting points, right? Because this is how we can help without sharing screen and so on. Yeah, I'll include links to all the visual ones that we mentioned just so people can see there.

Each of them will have screenshots on their homepage, I'm sure. Good. Thanks to all people who share feedback. We had, again, good wave of feedback on twitter and ideas thank you for all ideas and thank you for listening us while you're walking your dog or running or just walking maybe swimming right raise your hand if you you are currently swimming i would like to know because I also do it sometimes with special headphones yeah good take care everybody

bye bye