Michael: Hello and welcome to Postgres Film a Week Share about All Things Postgres.

I'm Michael, founder of PG Mustard.

This is my co-host Nikolai, founder of Postgres ai.

Hey, Nikolai, what are we talking about today?

Nikolay: Hi Michael.

is it your choice or Mine?

Michael: Oh, is it boring or not?

That's the question.

Nikolay: Yeah, it's mine, right?

So it should not be boring.

For some folks it might sound boring.

Very boring because we are going to talk about, psk

common blind versus.

Versus G U I and uh, some people think it's very, very boring to work in terminal.

Very like hack 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 likes something still, we probably will talk about it, but I, I find this topic not boring at all.

Michael: Yeah, so there's psql itself as the com, like 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.

Nikolay: with databases in this context, right?

Michael: Yeah.

Specifically for Post I, I was gonna say even grow as narrow as just talking about the ones that work with Postgre.

Nikolay: Right.

So PS q L is common line tool.

Its client program, right?

Client program in working in terminal.

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.

For example, PG dump, right?

It's PG store or PG bench, but in somehow PG bench goes to server package.

Yeah.

It's strange.

Sometimes we, for example, sometimes we, we have, older database installed and we want newer, client tools because there is a backward compatibility.

We know that newer client tools can perfectly work usually, uh, with older database.

So, and they have some features.

For example, P Bench has, has some feature, or PC has some feature, and we are trying to install client.

It'll be fine.

For PC SQL P store.

It won't be fine for P bench.

For P bench, you need to install server package for newer version, which is strange.

Just a side note.

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,

Nikolay: In many cases, so, right.

Yeah.

Michael: Yeah, of course.

Not

Nikolay: because they work usually on, at meta level.

For example, like we, we can for, for example, on Positive TV with Andre Brad, we're trying to extend backslash watch.

PC comment to add more options to it.

Uh, it, it accepts only one option.

it turns some query in Infinite Loop and originally it accepts only one option.

The, sleep time between loops.

So we wanted to, to add another option, how many loops we want.

Michael: should we go back to I'm thinking of it sometimes from beginner angle and sometimes from like the advanced user angle.

I, I see a lot of advanced users in love with PC Corp or PS Q L.

I think Leticia's doing.

Leticia did a really good talk on post Chris tv, and, has done it at several

Nikolay: Huge number of, of tips on that website.

tips, right?

Mm-hmm.

Michael: Exactly.

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.

And that's kind of both great.

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.

So is there, like, where would you recommend getting started for.

Nikolay: I would recommend getting started with, uh, Linux terminal.

learning, Tmax and vi,

Michael: Yeah.

Nikolay: maybe not vi, but Tmax.

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.

Right.

So if you are home internet, like.

Very often is not good.

Tmax saves you.

This is where you, I think everyone should start and learn some basics of, uh, these great tools, Tmax and vi.

Uh, 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.

Steps.

If you do something in UI , try to do it.

Once again, you might be, you might click different button and that's it.

You cannot program it and put to gi Well, there are tools for UI programming, like, uh, recorders.

I remember 20 plus years ago there was, uh, Rob Rational Robot or something.

I don't like many things.

Created selenium and so on.

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.

Michael: Yeah, I think that's, I think that's a really good reason.

The other, and two more good reasons.

One is it's installed everywhere.

You're gonna have Postgres, there's, you're gonna 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 Postgres.

So I've seen a lot of gooeys have weird bugs or like every now and again, you get

Nikolay: I would say

Michael: What exactly.

So quite often in a Postgre community you might see somebody seeing, seeing some weird behavior.

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?

And if you don't, it's a problem at the editor level, like there's something going on in between, or the interface.

Nikolay: We can claim that P SQL is pores.

All UIs are not pores.

Pores doesn't have ui.

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,

Nikolay: Right.

Unlike any ui, pogs itself doesn't have any UI and PPG admin.

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.

It does.

It's not shipped with pogs.

It has different release process, release cycle, different team and so on.

It's basically under, enter.

E GB is, is working on it.

Single

company.

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.

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.

Like when I looked into, so my background is in, is in Guidos.

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, uh, to make a tool for a popular editor.

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.

That just isn't the case.

A lot of people are using cls, a lot of people are using a vast array of different, gooeys.

So it's, it's much more difficult to pick or you'd have to build something that

Nikolay: It's open source, bazaar.

You know, it's not cathedral in this case, but I wish POCUS had standard ui.

I wish it had.

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

one

Nikolay: some ui, sorry for interrupting, but when some UI I experience 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, uh, goals, tasks, everything.

So it, it can be much bigger than pro, uh, than engine itself.

So, so, because, and it's very different.

Different because, maybe not all will use it.

Well, still, I think POS needs ui, standard ui.

It's, it's my own opinion.

I, I see how things are right now and.

, I think it's very, very low chance it'll be changed.

Yes.

Pja mean is something I never used and don't recommend to anyone.

I reevaluate every couple of years and consider it's going the wrong direction.

Having problem since birth, like the new pja mean working in browser and so on, like nice idea about implementation.

I consider it's very poor and cannot recommend to anyone if someone.

, 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,

Michael: That grip?

Yep.

Nikolay: These tools are much better, much better.

Michael: Yeah, I have heard that PPG Admin four 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

and it's.

Nikolay: 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 POGS 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 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, don't spam my pauses.

Right?

Michael: Yeah.

And there are tons of alternatives.

I meant I said that in, in passing, but you mentioned some great ones there.

Um, DB versus especially

Nikolay: beaver maybe is the leading alternative.

Michael: Well, yeah, data Grip definitely has some benefits.

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.

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 gooey 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, 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.

So my personal favorite at the moment is Pasta Co.

That you mentioned.

Very fast, open, very reliable, but it is Mac only.

There's loads of others.

Like Table Plus is really popular.

DB was popular but has been revived recently by Command Prompt, I think they've called it, PG Manage.

there's loads of.

Alternatives.

Now there's some cool ones like Popsicle, that's how I think you pronounce it.

Pop Sqr.

And then sadly archetype got acquired.

That that was looking

Nikolay: The Beaver has Cloud Beaver version like which, yeah.

Also interesting.

You know what I use more and more I like, for me, I like to do everything in the browser.

Like I'm, I'm very strange person, probably.

I like terminals and I, I like everything in the browser and this is very strange, but I know it.

But still, this is my tool, like maybe conflicting, goals.

But I, I find myself using p SQL quite often in the browser.

using, no, like connecting to some virtual.

both AWS and allows it, and, and then I run ps sql, they're in Tmax.

So if I disconnect, I can continue and that's it.

It's version.

I want everything as I, as I want, and so on and so on.

I tend to use, uh, CLI just because of the reasons I mentioned.

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.

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.

Michael: Yeah, exactly.

Uh, on the command line front, though, it is.

It's awesome being able to do stuff in the browser in even various apps like Google Cloud, for example.

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

Nikolay: you don't use CLI on mobile phone.

Michael: No, no, I do.

So yeah.

What, what, is

Nikolay: CLI terminal.

SSH to some, somewhere from phone.

I do it all the, time.

Michael: that's what I mean.

I think it's useful to be familiar with the c l I for that purpose.

Like if, 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 kind of the basics?

Yeah, exactly.

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.

Right.

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.

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.

Somehow in, in some shell scripting, I doesn't matter, and you expect some form of output.

But, then on server you might have dot, p sql RC file, for example.

People like to to, 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 answerable playbook, which failed only on production because.

All lower environments didn't have, PSK rrc, but on one of servers on production, someone put PSK rrc.

Since then, I always use dash capital X, which says ignore psk rrc.

So any scripted approach should include dash capital X

Michael: That's a great tip.

Nikolay: yeah, just keep, uh, custom setting.

And start from default setting.

And that's it.

Michael: That takes us to something else though.

Have you ever shared?

So it is cool that you can customize that, um, that, that config.

Have you ever shared your setup or know any good re places

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.

And then I ended up to use to work with defaults, for example, in Tmax.

I, I originally worked with non-default, uh, control a.

As a control, uh, key.

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.

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.

And there I don't have time to set it up unfortunately.

but in some cases I connect, through a tunnel, a sage port forwarding Sometimes it happens.

In this case, I, I have something on my machine, but it's not, uh, some big psrc.

Instead, I have a project.

POCUS DBA on GitHub, which provides interactive menu inside p.

Did you see it?

Michael: I've seen you share something else.

The P S P G pager, is that, is

that what

Nikolay: no, no.

Oh, that's great tool, great addition.

I, I, when I have opportunity to customize pc, I always use PS p g.

It's easy to install it from on, on Brew, on Apple everywhere.

Packages have on all popular platforms, it's developed by p.

, I might pronounce it wrong, sorry, from Czech Republic.

And, uh, it's great.

I followed.

I like that.

The way it, like it, it replaces less.

So if you have a white 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 I, I, I think it's a must have for those people who who can afford.

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.

I have postpositive DBA package, which is a, a, set of scripts, uh, SQL scripts, and it provides you a menu.

So you type, uh, column DBA and see a menu in P sql.

It can and can choose from menu.

Like for example, zero is.

Uh, basic cluster information.

B one is, hip bloat estimate analysis.

B2 is index blo estimate analysis with preformat, like quite good format for physical with some understanding that, uh, we live in console.

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.

It also has this, this report, and, uh, 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 connect it to, but you need to have it on your machine, so you need to install.

Michael: Yeah.

Nikolay: Additionally.

Yeah.

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.

The other place I would recommend going is the Postgres docs.

There's, there's just a whole host of those.

What would you call them?

Back slash commands of different

Nikolay: I would recommend remembering, memorizing all the backslash, uh, question mark.

Michael: yeah.

Fair, fair help.

Right.

Nikolay: for help on PC SQL commands of the all PC SQL commands.

Start with backslash and, uh, I don't remember all of them all.

I always check them always.

And

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.

There's whole host of these.

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.

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.

It's easy to find, but you can enable print.

a sequel that is behind those comments.

So when you say backslash, d plus somet table.

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.

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.

I use it all the time.

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.

Uh, asterisk Ellison asterisk, and I see all the functions.

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.

Michael: Yeah.

Very cool.

Nikolay: And so backslash, question mark.

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.

for example, you forgot syntax, you forgot syntax for delete comment, for example.

It maybe one of easiest comments, but you forgot, right?

You can write, uh, back slash h delete and see it.

So you see like synopsis.

I understand how, which keywords to use and which in which order, and so on.

Michael: So backlash question mark.

Help for PC SQL itself.

See all the commands that are possible.

Backlash H is help for

Nikolay: All right.

All right.

These are two main important, most important backslash comments.

You need to remember all others you will find from there

Michael: Yeah, I like it.

I know there's lots we could talk about for pc sql,

um,

Nikolay: I

variables

Michael: Oh yeah.

Yeah, we, I mean I've even, I've used variables, right?

The explain analyze syntax is quite, uh, is quite long.

So that can be, could be really helpful.

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?

Nikolay: You can do a lot of things.

If you look, uh, at inside my Posts DBA project, you'll find interesting things.

For example, backslash if slash ELs.

variables and so on and so on.

Like many things, it's basically like some kind of macro language.

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.

And, uh, this is how I understand, not just, it's not just sql, it's something else.

It's of course, it's a combination of regular SQL and, and P sql.

Michael: Nice.

Nikolay: Yeah, we need to remember context in positives.

I had a problem yesterday.

I worked with, uh, PLP P Scale function, and it had select something into variable and vari.

Like you can select something from table into variable.

You declare variable in the declare section.

And then I, I wanted to repeat the sta the same steps in p.

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.

Guess what happened?

It worked, First, my thought was, is it inserting result to P SQL variable?

No.

It, it was not, it was not related to PC sql, it was related to regular sql, uh, pogs 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 comment.

Michael: Wow.

Wait.

It creates a permanent table or a

Nikolay: Permanent.

It's like create

table as select.

It starts with select ends with into something and it creates a table.

It, it exists from from nineties, so it's very old.

I would remove it actually.

. It's

Michael: Is it SQL Standard I'm guessing, or is it

Nikolay: in sequel standard.

So in lp PPG scale, it's dml, it's data modification language.

We just select something.

We memorize it in, in variable of type of record.

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.

It creates a table.

Second attempt will fail saying table exists.

in standard sequel as PO's documentation says, I didn't check.

The, standard itself.

But documentation says in standard sql, it's also different.

it's used to get data and uh, store it in, in client side somehow.

So it's dml again,

Michael: Makes sense.

Nikolay: 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.

. 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.

Michael: yeah, I've got a whole episode on that.

Right.

Materialized views episode.

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.

I do think there's almost like a power versus discoverability trade off.

Like generally speaking, more will be possible via the c.

. 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.

Well, maybe not once you've discovered, uh, back slash

Nikolay: uh, yeah, yeah, yeah.

Well, well, I need to also, to learn each ui you need to learn and remember the interface.

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.

But I would, I would, for serious work, I would always use CLI because I need to automate everything.

I need to put everything to get, I need to share my actions.

I need to be able to repeat them.

And with UI, it's very difficult.

Scripting.

Scripting is is very important.

Michael: Yeah, for sure.

With some, some UIs.

I think, like for example, the data group one, I think allows you to do a lot of stuff from like saved files.

but yeah, I do, I think they have some other advantages as well.

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,

Nikolay: Yeah.

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.

Yeah.

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.

Nikolay: you know what?

I think, uh, since pauses is very extendable in, it's in its philosophy, I think it's a good idea.

I, I think it's not a new, new idea, but I, I, I'm still like releasing this idea.

It would be great to have plugins for physical, right?

So for example, it could be a plugin that, uh, reminds you like, that maybe even blocks you from sending.

Dangerous, delete or update to POCUS itself and asks for confirm confirmation or something.

I dunno.

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.

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.

You see right in physical, you see the link.

Usually, for example, in item two on my mark, I can, uh, use either control or, or comment.

I don't remember and click on the link.

And in my browser, I already see visualization, right In this case.

Yeah, it's easier.

You are 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 your, from cli.

And this could be some connection between these two worlds, right?

Some plugins could implement it.

Michael: Yeah, if making it extensible would be awesome, uh, for sure.

there is, there are alternative CII tools, right?

Like

the, the, the only, yes.

And I think sh uh, it's

Nikolay: It's certain Python, right?

Irina?

Michael: Yeah, absolutely.

so a Ukrainian engineer has done an excellent job maintaining that and it, I've not used it myself, but I've heard

Nikolay: auto complete colorful everything.

Right, so it's like more French tool For,

For,

Michael: I'm quite smart.

Autocomplete, quite like smarter auto complete than I've seen in some GOI tools.

So yeah.

Really?

Uh, nice.

Nikolay: It's not only auto complete for like in physical, you has 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 so yeah.

That's great.

Michael: Anything else you wanted to make sure we.

Nikolay: I think that's it.

Uh, of course, uh, such thing as, interface.

It, it'll 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 princip.

And starting points, right?

Because this is how we can help without sharing screen and so on.

Michael: Yeah.

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.

Nikolay: Good.

thanks to all people who share feedback.

We had, again, good wave of feedback on Twitter and uh, ideas.

Thank you for all idea.

and thank you for listening us While you are 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, I also do it sometimes with special headphones.

Yeah.

Michael: Oh, that's funny.

Alright, well take care of everybody.

Nikolay: Bye-Bye.