Michael: Hello and welcome to Postgres Event,
a weekly show about all things PostgreSQL.

I'm Michael, founder of PG Mustard, and this
is my co-host Cola founder of Postgres ai.

Hey, Nicola, what are we talking about today?

Nikolay: Hi, Michael.

I call this topic no backend, but.

how can we call el?

Like it's, it's about the idea of stop writing application
code in Java, Python, ruby, php, doesn't matter.

And using only front end code in your web or mobile
app and only database site code, and that's it.

Nothing in between.

Michael: Yeah, so we had a nice, this is another listener suggestion.

So we had a nice question that was all about good
practices to build APIs directly in the database.

And they specifically mentioned Postgres.

Understandably,

Nikolay: Right, right.

So I, I describe this topic.

First of all, I want to apologize that I'm interrupting you constantly.

I, I, I will, I cannot promise, I I will stop doing it.

But we had a comment in YouTube and I agree.

I do it quite often.

I, I know this problem.

Some people cannot work with me because of it actually.

So,

Michael: well, I don't mind, so please don't worry

Nikolay: But I will try to improve.

I will try it.

So I describe this topic as we 15 or like 20 years ago, it was
normal to say that code on clients should be very lightweight.

It should be some smallish tm.

and so on.

In 2004 or so, plus minus couple of years things changed
with web 2.0 when Gmail, Google Maps and other web

applications started to write much more code on client side.

Right.

And then iPhone and Android edit even more.

So I call it we live in the era of very thick clients
because a lot of code in business logic is coded on the

client side in, in running, in browser or in model app.

And this already ate some, it, it, it not ate.

Like it took some work from backend developers.

and changed everything.

But then the idea why we.

Still have backend code if also SQL is quite powerful today, right?

We have, we have, we can do a lot, we can
extract only the data we need from tables.

We have, we can work with very efficiently and actually we,
we can have again, store procedures or functions with lot of.

On database.

So database can be kind of limited application server itself.

And also additional thing is JS O appeared right?

JS o is a common format of communication for everyone.

This gives us idea, why not eliminate.

The middle of our three tier architecture.

So keeping only client and database.

some, maybe some additional middleware, but very lightweight
and not needing constant modification in between.

Right?

So, so returning to request of our, our listeners, it was like how
to write rest API on top of database easily, or it can be graph q.

Server.

So we want something that will support development of our
applications, client code, and, and it can be just, it can

be swift mobile app, written swift or something else, right?

It's quite natural to think in this direction.

Michael: Yeah, I think from a simplicity
point of view, it makes a lot of sense.

And also that a common one I see is a speed of development perspective.

If I, if we can write the, the client and have the
database and not have to write something else, we can

get to a version one of our application quite a lot.

Nikolay: Also think about data flow here, for example,
we have relational database of course relational.

What else?

Even if it's not relational data can be.

Stored in JS O and relation to database.

I mean, we, we have very good strong js o support in Postgres, for example.

We, we store many parts of our data as Js O, and then data flow, if it's some.

Python or it's some Java application.

We translate this data originally in JS O to some object format, right?

Some, some objects or arrays or some structures, data structures.

And then we translate them and give, give to our client called again in Js O.

It feels very strange.

this like jumping back and forth.

It's called actual data marshing when we translate too much.

So we could take this on and give Jason maybe transforming it somehow.

Why we, why we should, we deal with all those objects and errors.

And People like myself, invested a lot of time into writing,
a lot of cold, following some object oriented design planters.

They now regret it because it's good, like It's beautiful in
terms of some concept, but practical wise it's not that good.

And working with Dron and things like React, which define how
to work with both structure and what to do with this structure.

In, in same way.

It's, it's this, this, these things changed the
world already and the idea of having very light.

Middleware and allow us to focus only on client code, only on database code.

It makes sense, in my opinion.

That's why, yeah, that's why we have Postgres and others.

Michael: Yeah.

Feels like a good time to dive into progress.

Do you wanna give us a little bit of a, a top level
on what, what exactly it is and why it's useful?

Nikolay: Yeah, sure.

Yeah.

First time I, I saw it maybe like eight years ago.

Quite long.

and I implemented I, I used it in several projects including a case
when successfully a small team of like four Ruby developers who are

substituted by like 10 hours of my work per week with same result.

So Postgre is an application written in has.

which sits next to your Postgres database.

It can sit on other hosts and, and of course, like It's
stateless, so you can scale it and you can provision more.

Compute nodes it's like proxy, right?

But it allows you to define a structure in database.

So of special format that will define automatically
what rest API your Client code will be, be able to use.

So for example, if you have a table, you can quickly define a
view in, in special sche up, for example, view one, it's our like

API version one, you define an updateable view and automatically
a client code sees endpoint and can read insert, select from it.

And HTP methods like get put post or automatically translate
a patch are automatically translated to four dml statements.

Select, update and search.

Delete.

We have so.

There is a lot of logic for automation, like play
with headers, modification, and so on and so on.

It, it's already very advanced software actively developed for many years.

Version 10, major version 10 is current.

So I, couple of last years I not, I'm not following
very closely it's development, but it's, it's.

Constantly.

And this is the project behind for example, super base.

Super base is based on Postgres plus Postgres plus several more components.

And so this, I, I love this idea, and
actually I've already explained why, right?

So you can focus, you can say we have Postgres.

And for example, we have one POG guy, or half of
it, like, like maybe 20% of a postgre guy per week.

We just need to define our tables and these views and so on.

And that's it.

We whole team can focus on product development, on
client code and go with startup activities, not spending

power on API development in the old fashioned way.

Michael: Yeah, and it's, and it's free to use as well, right?

So even if you're not using something like super base, it, it's when
I checked it out, it's just plain MIT license, which is awesome.

So anybody can very quickly with a Postgres db.

As a, as a backend, fire this up and have a fully
working rest API pretty much immediately, right?

Nikolay: right, right.

It's also very performant.

I remember when I first looked at the collect
some things like for example, you know, no offset.

Offset is very terrible thing.

We know we, most of us know it, but very quick.

Reminder using offset with like, limit.

Offset is usually a bad idea because if you check the plan, execution
plan, you will see that all previous C were fetched and discarded.

So instead of it, you, you should use like How was called

Michael: Ation.

Nikolay: Keyset?

Imagination, I think.

Yeah, yeah, yeah.

And by the way, I've learned from PostGrest a lot.

For example, this term I learned, I learned dealing with Postgres.

I also learned about ski.

I mentioned many times ski tool Alternative to Flyway or, liquid base.

So the tool to control schema changes and to have schema version control.

I also learned from there just reading documentation and chatting with people
in Postgre community, which is also not, not huge, but very active and good.

So You can have very performant api.

So it's written, high scale, very polished.

When, ah, when I joined, they had this offset.

and I don't remember details, but I, I contributed a little bit
to improve how ation is working and to get rid of suboptimal

queries when you have like billions softwares, for example.

So right now Postgres has better proven on large databases under heavy load.

You can use it however, there are of course cons.

We, we haven't touched them in this approach.

We have cons.

Let's, let's first think if we mentioned all pros, right?

Michael: I think another big pro that people bring up is security.

So you can define things in the DA at the
database level and it will inherit those.

So it's quite nice to have that in one place and to be able to trust the
data or to be able to trust Postgres in this specific example to handle that.

So both at the schema level, so.

Which objects are exposed to it, to the Postgres user, and I guess at the,
the role level role level security policies which is something I'd never

seen used much until I looked into how how super base were doing things.

Nikolay: Right, right.

So this approach is an alternative to manual implementation of arrest api.

I think many, maybe most dev backend developers did it at some
point and had feeling that there is some repetitive action, like

some copy pasting involved and so on, and it's performant and secure.

And also it's an alternate maybe.

The, maybe it's also makes sense to think
about it as an alternative to, or om right.

Because it's also, it's also mapping, but there is no objects here.

We mapping usually either relational data to Jason or
Jason in tables to Jason or a mix of them to Jason.

And Jason of course is Also working with Postgre, I learned about,
how about Js o manipulation and postgre how, like a lot, a lot.

Like I, I, I was constantly sitting in the js o
part of Postgres documentation because I needed.

And one, it requires some effort to, to master based on skills
for pogs because there are a lot of operators and functions.

But once you achieve it you.

Like it actually because it's very powerful and a lot of freedom.

You can combine relation that and JS o
and give it to your client code so easily.

like we already discussed this store procedures and database site.

and many people, uh, and we also mentioned that sometimes you, a
few lines in SQL can replace hundred of lines in Python, Java, right?

It can happen easily when you talk about managing data.

So we have it here as well, right?

In this is our approach.

We write a database site code, and.

Produce JS O and we can consume json easily.

And, that's why it gives speed of development actually, right?

Because it's so you, Get rid of all like fat.

I would say in quoting, you don't need to think about this.

object design patterns.

You just take raw data, do what you need on database side,
and, and give js o very, very efficiently in, in, in develop.

Michael: Yeah, not, I guess it's not directly related to this subject,
but while we're talking about them, I think I'll, I'll include links

to a lot of the JS O functions as a good list in the Postgres docs.

it's fun to read, even if you're not planning to
use it anytime soon, just to see what is possible.

And the other one was, You mentioned the no offset, and I think Marcus Wynand
deserves a shout out for all of the work he's doing publicizing this movement.

But he also think gives out stickers and
he, he's got a great page on his modern.

Yeah.

Great.

He's got a great page on his modern SQR website that I'll link to as well.

Nikolay: Yeah, modern scale and use the index loop.com as well.

Like I, I

Michael: Very good one.

Nikolay: like, brilliant websites for, for those who
are interested in relational performance optimization.

Also Few more words about efficiency of development.

If you think about all rest APIs, most of them are repeating some logic and
it's natural to use some declarative form to declare new endpoints and so on.

Right?

So SQL is declarative language and post rest can be
considered as a declar of way to build rest api, right?

Michael: Yeah.

Nikolay: That's why it's so good.

Js O declarative way and a lot of things are automated that
most of projects need enough pro maybe switch to cons.

What

do you think?

Michael: Yeah, for sure.

And I, I guess is it worth saying that a lot of these
same benefits could be said of similar projects?

Like in the GraphQL space we have things like post graph Azure, if it
feels for me, I mean, I might be wrong, but logically it's very similar.

Nikolay: Right.

right.

So both open source projects you mentioned, And
companies, we mentioned super base and you mentioned ura.

The difference is Postgre is like rest api.

There are several attempts to build.

I like, it's not my thing GraphQL, but I, I know a lot of front end
developers have the idea of GraphQL especially those who work with React.

And so nice idea, but it's just, I, I like experience in, in this area.

, there were attempts to build ability to create GraphQL APIs on top of sre.

But also there is, has now somehow it's also Haskill.

They use has scale as well.

Water incidents.

Right.

And also very successful.

So I, I like to see these successful projects, but it's interesting
that I, I, I'm not an expert in, in history, but I know that there

are a couple of projects pars.com and one more, I forgot the name.

One was acquired, was acquired by Facebook, another was
acquired by PayPal, and they it was more than 10 years.

Or roughly 10 years ago.

And they, they, ideas of those companies which were
both acquired and then shut, shut down somehow.

The idea was to provide service and cloud to mobile
application developers to avoid the need to, to fill

the need, to fulfill the need in backend development.

So these class of projects were, was called, or services was
called M B a SBA BUS, MD Bus, mobile database as a service, right?

So not database as a service like rds, but mobile database as
a service, meaning that database for mobile app developers.

I.

Right.

So you have not only database, but also some kind of application
server equipped with database and somehow they are close.

I don't know why, interestingly, right, right.

So like this, it looks like super base and has, are a
new wave of, of this idea to simplify backend development

for those who want to focus only on front end develop.

Right.

And they are much more successful and and a lot of users and we see it.

Michael: Yeah.

And the other big successful one in that space feels like fire base.

I know it's Closed source, but it, it feels
worth mentioning that that has been a success.

So it seems likely that these, these other ones could be too.

Nikolay: Right?

So fire brass is no, no sequel, like key value or Document Store

Michael: Is, yeah.

Nikolay: on Google Cloud.

And it's very successful because for example, imagine you're an app.

An app, mobile app development.

You want database, but also if something changes on database,
you want your application to receive the change somehow.

Like automatically probably using, like, I would
implement it using like web sockets for example, right?

So I, I pushed the change to my application and this is what Firebase offer.

So it's fully managed database with this.

Synchronization capability, like push the change to to client side code.

And it's, it's, I think it's still successful in super
bases positioned as open source alternative to fire best.

It's based on post past guest plus additional competence.

by the way, I'm not sure how it's implemented in detail, but
I guess it's based on maybe on logical decoding and Web sos.

Maybe one day we should invite them to, to describe it in detail.

Michael: Yep.

Nikolay: Right.

But it everything, like, everything is super successful, especially
if you're a small team of some startup and you need to quickly

change your, your interfaces and check various ideas very fast.

And you don't want to keep whole team of rub Java developers constantly.

Yeah.

But let's,

Michael: good.

How about,

yep.

Nikolay: yeah.

So, sorry I interrupted you.

Sorry.

Michael: No, that's my um, how about some of
the downsides, I guess is what you're gonna go

next?

Is that what

you're thinking?

Nikolay: exactly.

So if, if you're a small team, it's good,
but, but what if you're a bigger team?

I observe cases when even in bigger companies
things like or Supervisor ura also work well.

I remember some talk about I don't remember the company exactly, but it
was a big company and they decided to stop developing their own APIs.

It was REST API originally, and a lot of
development in house, and they switched.

It was uh, a.

One step switch to GraphQL and ura and they liked it so much so
they, they talked about it with and how to shift it in a big company.

So it definitely makes sense for big companies also to consider this approach.

Definitely.

But what are downsides, first of all?

Like maybe not the main, definitely not the main
downside is that these somehow these middle where uh,

CS are written in HU skill if you want to adjust it.

Well, HUS skill is interesting language, I would say.

I tried to contribute there.

It will, I maybe something I, some things I did, but it's
definitely you need to some mind shift to, to work with high skill.

And I have huge respect to people who, who like it and continue.

So this is

probably some,

Michael: a,

Nikolay: yeah,

Michael: all I was gonna say is a functional programming language.

And for full disclosure, PG mustard is written enclosure, so I can't

Nikolay: disclosure.

Written closure.

Right.

, full disclosure.

Written closure.

Closure is also, it's like also functional language.

It's, it's good.

Like these languages are great, but their adoption is very, It's, they are not
super popular compared to, I dunno, like goal language for example, or Python.

And another problem like possible downside is that following this route,
you will route, you will have a tendency to use more database site code.

and if communication with external.

It's this problem.

We, we discussed this problem at in our episode about store procedures.

if you need to communicate with external world call some rest a external api,
for example, or to download some binary, for example, a picture and save it.

It's not good to consume CPU cycles On your

Michael: On your primary database?

Nikolay: especially on primary.

Exactly.

If it's a function, which rights to databases can be
only primary, so you consume cpu, which like very limited

resource, the most limited resource in the Nepo cluster.

This can be dangerous, and I remember one day I used even pssh, which I
wouldn't recommend for normal use at all to save a picture from Postgre.

I had the call, like if they called as I remember, maybe still like rpc.

So you have post slash rpc slash your function name.

And this is a function in Postgres.

And it was SSH function, which is like
store procedure, store function using batch.

And it, it then I had some cool calls there and stored the, sold
my primary PO's primary node downloaded image and then called

some additional small microservice to, it can be s3, for example.

You can now save it on three.

It's the approach.

You never should you never would like to follow unless it's some very.

Quick and dirty prototype of something.

It's not on normal service because why?

Because CPU on the primary is very limited and you shouldn't do it at all.

Michael: and if you're, if you're a new listener, welcome.

But also we have a previous episode all about this on stop procedures.

Nikolay: Right, right.

So this is second cons, second item in our cons list.

What?

What else?

Michael: Good question.

I, well, I've got some cons from developers actually on Reddit.

I think the first one they said.

When this, in one of the places where this was shared when
I think probably when it first came out, but I didn't check.

The top comment on that is, thanks.

I'm unemployed now.

So that's, maybe that's a con losing developer
jobs, could that be considered a downside?

Nikolay: Well, yes.

Maybe, maybe, But also to balance it.

I, I saw not once I saw people who say it's not right to, to use SQL for it.

SQL is okay.

SQL is okay language, but I would love rather
to use my Goal or Python or Java for that.

Michael: Oh yeah, by the way, I was definitely joking.

I think there's plenty of developer jobs to go
around and definitely still more demand than supply.

Nikolay: you're touching very sensitive topic.

So yeah, but, so maybe we could say like, A new item on
the Cons list is a lack of libraries probably, right?

Because if you use Python or even go, which is newer language,
you have so many libraries already written, so you have

so huge power to extend your API with additional things.

For example, if you want machine learning and you, you use
Jungle and you decided to to, this is, by the way, when I.

Ruby developers in that project.

Then later, I already, it was fine, but they, they pivoted
this company and they then hired Python developers.

One of them is my, one of my best friends in university.

He, he told me We are not going to use Postgres anymore.

We know John, we are going to use a lot of
machine learning stuff, so we are going to.

Switch from your stuff to John.

Go API development and we will be using a lot of machine learning.

Of course, I could say there is machine learning in pogs.

There is a Med leap of this pogo, something new, right?

So it's possible.

And there is P Python.

You can use a lot of Python code inside pogs.

But I felt there, I mean, I, I not fail, I lost, I lost that battle.

Why?

I'm not sure by the way why, like they just said, this is what we use.

This is easier for us, more convenient.

Michael: I think generally people do have a bit more familiarity with
their backend services that they've got used to than they do with Postgres.

So one of the, the downsides is that we are pushing
a bit more responsibility and work into the database.

So like, for example, around those security things, I wonder if that's part
of it, familiarity with how to do that and how to debug things if things go.

Nikolay: Oh, debugger.

Debugger is a good thing.

Yes, IDE and so on.

So the same, same issues as for store procedures
when we cover them in that episode, actually.

Yeah.

Right, right.

But if it's quite simple api, for example, if you have a table, you
define a view, as I said, if you just need simple inserts, select,

and so on, and some kind of logic related to which columns should
be available for users, everything can be done at the view level.

You don't need the to write a function and use p gsk at all for this, right?

So in this case, Probably like, it's good, it's
enough and, and it's faster in terms of development.

And actually people should know SQL at in general.

but if you need some advanced logic, maybe you'll have issues.

Again, it's possible, but.

Yeah.

So it depends.

And the, constant pros are not very strict to me.

Like, for example, if I take some project, I
would, am I, going to use Postgre one more time?

Or, I will say, okay, a team of Python developers, you, you do it.

So I have personal preference to use Postgres, of course.

And at Post, we use it for our platform as well, by the way.

Michael: Oh, cool.

I didn't realize that makes sense.

Nikolay: So if, if you, if we extend something, it's
it's, this is written in React and post, that's it.

Michael: Are there any, this might be a stupid question, but
are there any downsides or is, is it, is it easy enough to

do complex qu, like maybe a quite a complex analytical query?

Is that easy enough to do?

Nikolay: Well, any query can be there.

Wow.

So it can be a view, it can be a store procedure like

Michael: But it has to be on the Postgres side rather than

Nikolay: right?

But

Michael: Okay.

Nikolay: it should be always on database side queries should be there.

It, it's not good to load them like our
developers do and do everything in memory.

So I don't like this approach.

It should be in

Michael: Makes sense.

Nikolay: right?

Oh, by the way, I think one more.

it won't be very like strictly defined, but one more item to the cons list.

It's more like about progress, not about progressed lack
of tools for a synchronous nature of work with data.

We discussed it also several times, so for example, you want something.

But you don't want to do this heavy work right now.

You want to schedule it and then return to processing.

And this encourages you to implement some, some kind of
queue inside database or to use external queueing mechanism.

And there are ways to do it, but it it,
like from a SQL context that's slightly.

Difficult, especially because unfortunately, the
world of developers lost very good, in my opinion.

Tool called PG Q from Skype developed many, many years ago.

Unfortunately, humanity lost this quite good dinosaur I would say.

I liked it because of Its performance.

And it was easy to work with.

It was good, it had good performance, some kind of
maintenance needed, but much less than for Kafka, for example.

And since then we observe a lot of attempts to reinvent that wheel.

and most of them are failing to do it in, in a way
that I would be able to use them in large project.

So most of attempt to implement Q in
database cannot scale well, unfortunately.

But this is maybe another another topic to discuss.

So, and if you use postgre, you build api.

, for example, to save pictures on some cold, I would
think about how to have a cue mechanism close to it.

What would be your cue mechanism?

A synchronous processing in, in this case, you, in this
case you'll be fine because for most languages, of course,

we have libraries to work with, like , anything like that.

Michael: Yeah, good point.

Wonderful.

Well, I think that's probably a wrap then.

Thank you so much, Nicola.

Thanks everyone for listening and see you next week.

Nikolay: Yeah, I hope it was helpful.

Don't forget to share links and provide new ideas to discuss.

Thank you so much.

Till next time, bye bye.