Postgres FM

Nikolay and Michael discuss the age-old topic of implementing business logic on the database side versus the application side.

Show Notes

Here are links to a few things we mentioned: 


What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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:

With special thanks to:

Creators & Guests

Michael Christofides
Founder of pgMustard
Nikolay Samokhvalov
Founder of Postgres AI

What is Postgres FM?

A weekly podcast about all things PostgreSQL

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

I am Michael, founder of pgMustard, and this
is my co-host Nikolay, founder of Postgres ai.

Hey, Nicolay, what are we going to be talking about?

Nikolay: Hi, Michael.

Storage, procedures, functions, triggers.

What else?

Michael: Yeah.

So this is the, the age of debate of service side,
Well, Postgres side logic install procedures on front.

Nikolay: database side we can, we can mention other databases as well.

this is very old topic I remember in 2002 I was working on
with Oracle and SQL Server and we had a lot of logic inside.

P pl, SQL or TS SQL code.

Michael: Yeah, I, that even might be a great place to start
with this topic because I think with my experience with those

platforms as well was people used stored procedures a lot more
in SQL Server and Oracle than I've seen them doing in Postgres.

My experience is varied, and I think maybe that was larger
companies and my experience more recently has been with smaller

companies, so maybe that's a difference, but I definitely
saw it a lot more often in the SQL seven, Oracle Worlds.

Nikolay: Right, right.

So this is very old debatable topic similar to oms and there
are many people on both sides big fans of story procedures

and this, like love toto procedures can, can be very acute.

, like, wow, it's so, convenient and so on.

But at the same time, much more people say they have problems.

When we say, when I say store procedures, of course in postal
terminology, I, I involve functions as well because procedure,

procedure work appeared only a couple of years ago when it was added.

And in post 12, I think I

Michael: I read 11.


Nikolay: maybe 11.

I remember my Microsoft SQL server guys laughed at me saying, Oh, only now

Finally you have transaction control.


It was like it was friendly laughing, fortunately, but
in commercial relational databases it was considered

normal to write a lot of code on server side.

And, if you take any bank software, you'll find a lot of such
code, but maybe banks are not a good example because we know, like,

especially in the US, they are so outdated and Actually, I observe
them as a user, And they have issues with software, definitely.

And I considered usually, like, in Russia, banking is
very, well, it's, very good applications and so on.

But since I'm on the opposite side of earth, I see,
I saw recently, I am already not using them, but,

last several years, I saw their downsides as well.

They still have nightly update.

You know, like this approach is still there.

I mean, I mean regular approach when you have a lot of
server site code, but it's quite heavy, so it needs to be

executed only once per day, so you have some window and so on.

It's like, you try to log in at your daytime in the us but
you cannot because you, you need to wait a couple of hours.

You know, it's because it's not there.

So it, it's a sign that software is not modern.

Modern software doesn't, should not do this.

It should be always available.

And uh, of course if you have a lot of code
on server side, be question when it will be a.

. All right.

This is one of the aspects, but before we discuss some details,
again, I think a much more people prefer to avoid storage procedures.

I just observe it much more.

Like I have experience of discussions like, We have today.

And usually a reaction from audience is like, a few
people really support, but a lot of people discuss

downsides and saying like, We, we should try to avoid it.

And uh, that's why I, say I'm not normal.

I'm, I'm a big fan of store procedures, but I know their downsides.

Functions, procedures, triggers.

I know that there are downsides, but I'm huge fan.

I, I can explain why, but I consider myself as not normal.

Normal is to try to avoid them.

This is our normal what's your observ?

Michael: yeah, I'm, I'm similar but with a slight caveat that I don't use
them myself, and we don't use them in a very small database application.

I would say that it would be really interesting to hear.

you would advise others to do.

So whilst you're a fan yourself, if a team came to you and they have
a team of maybe 20 rails developers, and there's a couple of them that

have got some Postgres expertise but they maybe they don't have a huge
application, but it's, it's meaningful maybe millions of transactions.

I so it's, it's meaningful, but not GitLab scale, say.

What would your advice be to them?

Would like, I would be surprised if you would advise

Nikolay: by the way, it almost doesn't use it.

Michael: Well, yeah, I'm probably skipping
ahead mentioning them, so that's good.

But what would your recommendation be to others
versus like what your opinion is for yourself?

Nikolay: Well, my recommendation is like I have very interesting experience.

I wrote a lot of code in LPs, in some code or in pe, Python,
some code in other languages in sometimes a little bit c,

but first of all, we should say POS is very extendable.

This is one of the key places where you can extend progress.

You can write a lot of code, and for example,
you can define some new aggregate function.

You just need to write some function with, according to some rules, and then
say, Okay, my operator, my, my aggregate function will be supported by this.

And so you.

Select your aggregate function, then group by and so on.

This is very, very good thing.

You can, you can define your operator support by some function.

It's also like, there is good, good term server side pogs.

Code coding or programming, server site programming
in terms of positive site programming, right?

It's not only procedures, functions, also triggers, also operators.

You also can extend, define your index type data type.

Many, many, many things.

But first thing when we start, very carefully think about opportunity to use
this approach is our work with data, how strict we should be in terms of data.

How we want clean data.

It should be, how this word, I always forget this word.



com data is,

Michael: Oh, consistent.

Nikolay: con consistent.


So data should be consistent.

It should, We should not have dirty data in most cases.

Sometimes it's okay, but in many cases, yes.

Like for example, some user data and so on.

And in this case we define some structure.

We define some constraints.

And there is no other way to follow constraint.

In a hundred percent of cases except having them inside database because
If you implement your constraint logic inside your application code,

there's no guarantee that tomorrow you will write, you decide to write, or
your management decide to write another application or to work directly.

I mean, user working directly with data and breaking
your constraints because nobody checked that.

So the only way to have good constraints is to have triggers.

Well, something we can define using schema.

Data type or length of text field or something.

This is, or then you can define check, check constraints.

But some complex logic can be defined on the using triggers.

And this is already server site programming, right?

Post site programming.

And it's inevitable if you want a hundred percent guarantee like warranty.

You need someone can guarantee that data is clean,
it should be done on pogs, in, on database side,

Michael: Okay.

Yeah, I like that.

And I've already realized that I think my opinion is a bit of both.

Like it's I don't see myself as one way or the other.

I'm a big fan of database triggers as well.

But most of the teams that I see and work with would
be slowed down by, you know development speed wise.

Not, I'm not talking about database
performance, but speed of development wise.

Nikolay: Good.

Different aspect.

When I discovered Postgres, Which, which is now
very popular, thanks to Super base, for example.



I mean, maybe some people don't realize that they use it, but they use it.

Definitely much more users.

But at that time, when I worked with it quite long
ago, already, maybe like five, six years ago, I, in a

couple of projects, I replaced the whole team myself.

Like Ruby developers, three of developers
was writing some code for some startup.

Management was not satisfied.

I said, We don't need this a lot of ruby code to support some i p development.

We just defined some views.

In pogs and that's it.

But some code was immediately implemented in, in a form
of functions using pl ps sometimes regular sequel because

you can write function using regular sequel people.

People underestimate this, but this is convenient sometimes
and views and some logic and everything like, and I

felt like I'm spending like 10 x less time than they.

it, it's not a joke.

It's really a soul.

Like, it's so easy.

You just install, post dressed.

You, you need to know how to cook it in
terms of authentication, some, some things.

But once you understood it, it's, it's like with any
framework, you need to learn it, of course, right?

But once you understood it, everything is inside pore.

You define functions, you have API
endpoints, and you need some advanced logic.

You write PPG scale function, That's it.

Michael: So do you, do you think you were as
productive as then because of the technology or

Nikolay: Yes.

Technology, technology and the server side programming.

25% of me of my time replaced small team of free
engineers of Ruby developers replaced fully.

And I had tests, I had pipelines, everything like.

But unfortunately then actually my friends came and they
are Python developers and they decided to replace it.

I, I like, we had discussions like, Well, it's good.

They said, Oh, okay, but we don't know Pale Petski.

We need to learn, but we've Python, we will create.

So they replaced it and it was, in terms of
time that it was, it's less, less productive.

I mean, again, stay back, but they, they, they bring brought much more on.

Like machine learning and so on.

Michael: do you mean?

They replaced the se, the Postgres side.


Nikolay: with Django, this is is interesting project.

It's, they started for Ruby on Rails when I, sometime a couple of years.

I supported them with PostgREST and some coding.

Then they had a lot of needs in terms of Python and they decided to continue
write writing Python in, writing api using Jungle and, and, and there.


Michael: thought you might, I thought you might have meant pl python.

But no,

Nikolay: we discussed this opportunity.


They could do that, but I don't know.

Like this is like decision, like what you learn, what you know and so on.

Of course if you need to scale this is a little bit more
difficult to scale because LP scale you need to learn.

It's like other language, modify other language, very old and outdated.

It has some Loops and eve and some not super convenient
approach to work with exception sellers location.

But it's possible.

Like, it feels like Pascal a little bit, right?

but main benefit is that you're sitting there, you don't have round trip.

Michael: Yep.

Nikolay: you can be very efficient, but you need to understand how data flow.

With database is organized and you need to understand how
to troubleshoot slow plans, how to use explain command.

How to optimize queries.

You need to understand that.

And you also need good environment where you can try many things and feel it.

I, I mean, you need to like production, like the environment.

I, I had it already that.


So like, no, at that time didn't have, But
now I have it, and it's, it's easy, right?

Like you have same size development environment, so you can quickly
see performance of what you wrote, of your trigger, of your function.

And it's good.

Michael: This is another, so this is great.

We, we've covered, you've, you've touched on performance there
and I think that's, if, if you look at the normal pros and cons

lists, that's one that people that are on the side of stored
procedures, they often bring up as if you are trying to get comp.

You know, if you're trying to avoid those round trip times, if you're
trying to do some logic on your data that's in the database, doing it

database side saves you a lot of effort or can save a lot of effort there.

So that's normally cited, That's probably one
of the most compelling reasons for doing it.

Nikolay: Well, I would put on first place this logic that only
database can guarantee that your guard code checking consistency,

data, clearness and so on, it won't be bypassed by anyone.

This is, for me, it's number one.


Well, you can, if you can put some proxy and guarantee that all people and
applications will go through it and you code some logic there, it's okay.

But usually it's not.

So, So this is number one for me.

Second, yes.

Performance and round trip time.

We, we all know this extreme example when
some Ruby or Python developer pulls whole data.

Application side and then processes it, right?

Maybe in loop updating one by another.

This pull it and then analyze it.

This is very bad approach.

Database can do it much more efficiently because it has indexes
and you could all, Another example, our R code, which has some r,

has some Analog for group buy for joints and so on in memory only.

So they, the aggregation grouping joining.

So they pull whole data in memory.

At some point, this will be like memory will be saturated,
but it's not efficient because they don't have indexes.

POGS has already built, right, and algorithms and so on.

Michael: databases are really, well Postgres in
particular, it has a really good query planner.

It has, you know, it's really designed to do that on large amounts of data.

And doing it without having to send it backwards and forwards is
always good, even if it wasn't strictly better at those things.

Doing it without having to send that data across saves so much time.


Nikolay: You still can have a problem if
you, for example, use some CORs or approach.

Like if you, instead of updating all roles, you're using
one statement, you might like, okay, I will write P code.

I will do some for loop, and then in loop I will update each row.

It also will be less efficient.

So you can, you can do mistakes using this approach
as well, but at least you won't have network.


I wanted to emphasize it's not only about round trip
type, but also about capabilities you have if you pull

data to memory, you can do anything with it, right?

But you cannot uses, which left on, on server site, you don't have them.

Michael: so we've covered like a couple of the, the things
people often cite as really good reasons for put pushing more

and more, if not all of the logic that side The downsides I
of, or the, cy criticisms I often here are less good develop.

Oh, go on.

Nikolay: let's mention one, one more.

SQL in general is very, very it's very, powerful
for working with large volumes of data.

So many things developed.

you can write a few lines instead of
hundreds of lines and using regular language.

And of course, you want to ensure that it'll be efficiently executed.

That's why you need like, some understanding, experience and good playground.

But in general The code is brief, right?

and in PE P Gs QL function inside it, you
can use regular SQL combined with these pelp.

Like, they are working together without any need.

Unless you use dynamic sql, you can avoid using some.

Like, like execute or query quotes and so on.

You don't need them at all.

So like they are integrated already and by the way, there's
HDB with their hql project, which try to reinvent sql.

It's another topic, but also maybe in future more convenient work
will be done in using regular languages for JavaScript, for example.

so I want just to say that quote much less.

So usually somehow much less code.

You don't need to write loops.

And so like open database connection, close database connection.

No, no, no.

Everything is automatic.

Michael: So yeah.

Should we cover some of the downsides?

Nikolay: A lot of downsides.

As I've said, I consider myself as not normal
because normally people said we won't go this path.

First of all, they mention, usually they mention that it's not
convenient cause there is no good developer tooling, IgE or

debugger we usually mentioned there, there is debugger actually.

Michael: But people aren't familiar.

People are familiar with their existing, you
know with backend framework, debugging tools.

So it's more familiarity and expertise, I think, when it comes to using those

Nikolay: right.

I personally, I live in tmux and.

with, I'm not normal already.


And I, I prefer avoiding graphical ui.

I use it sometimes, but sometimes, But I pref, I, I
like when my work is on some remote remote machine.

So I, I'm not dependent on my laptop or anything.

I can continue working in Tmox from anywhere and
also working in pairs that have needed and so on.

But if we talk about idea Integr.

. Actually, maybe I'm, I don't know, maybe there are some
good examples for pl PPG scale, which I just don't know.

But we switch to PL p scale discussion only.

But again, you can write using sql, using pl,
python, pl, other, other languages Postgres has.

But question is how you like debugger.



In my personal opinion you should have a lot of code.

Complex stored in multiple files to have an
need in having debugger, there is debugger.

For page p, just go.

I, I never use it.

I don't need it usually function, even if it's hundred lines of code.

Print lining is fine for me in my also raise, debug raise bag is good.

Michael: What about test?

You mentioned testing.

You mentioned when you had that project you were doing, you had tests for it.

Nikolay: lab.

Michael: What did you, what do you use for
testing the procedures, making sure they

Nikolay: Well, first of all, you, you should
have data, enough data to test properly.

So my approach is to have production size
database, same number of roles or, or close to it.

So you just spin off your think loan using database lab and do anything there.

Michael: Do you use like pgTAP or anything?


Nikolay: Oh, yeah, I usually I prefer using
Sqitch instead of flyway liquibase or so.

P tap and ski sheets from David Wheeler, if I'm not mistaken.

Same, same author, right?

So Ski has verify, it, has deploy, revert and verify.

You need to define three functions for each database migration you have.

By the way, this is good point, how you manage your function definitions.

Michael: Source control, right.

Nikolay: yes.

Version control.

Let's discuss it in, in a bit.

So I usually try to, or have some logic in verify.

PG scale has a short function.

It's very convenient.

So you can write another, and you, you
can also have anonymous do block in plp.

It's also in PLPs scale, so we do.

And then some string, Usually people write dollar, dollar and then some
pill logic there and there you can have a sort, so you, you write anonymous,

do blog inside, verify functional for Sqitch just to test something.

For example, you have a function which did something.

Okay, you executed it.

Then you ensure that the result is as and if you don't have a
sort, for example, if in SQL context, you need to verify it, okay?

You can use divide by zero or you can write he helper function, which
will raise exception warning, notice or debug anything you want.

So you can have heer functions and call them using regular sequel.

And you you can have some case.

If something like you check something using regular SQL and write
your helper function to report some error, for example, And

it can be soft, like warning or hard, like hard stop, anything.

So it's possible you can have tests, definitely.

But the problem is that debugger exists, I don't use it.

I, just don't feel it's needed.


I use, usually I use a lot of.

And I sort of just like, you know, this is not possible.

Let's have a shot here.

And I use usually a lot of raise commands on various levels.

And you can, if you check my code, you can find raised bug in many places.

So anytime when you debugging something, you
can change client mean log, log mean, client.

Level, I, I always forgot you.

You can, you can change in two places.

Log level in client connection and in log so you
can have messages printed to your output or in logs.

It's quite convenient and if, if you have raised dac, it won't be present
anywhere by default, unless you say, Okay, I'm in the bugging mode.

I change my.

In either or both places.

There are two places, right?

So, and you have those debugging messages immediately.

Quite convenient.

Michael: Nice.

But then on the flip side, I think people that are using Rails
or Janga or something, they have migrations built in already

and they don't have to worry about a set, separate tooling
for Version control, source control, that kind of thing.

Nikolay: Right.

But they also have uh, first of all, they can have functions.

They can have triggers.

They do it, they do it.

And they also have quite advanced testing capabilities.

So I think it's, everything is doable if
you want to move some logic to database.

So it's doable.

Michael: And this is, I think this is where I've landed.

I think it's, you can have a bit of both.

You don't, it doesn't have to be, you know, a Holy War one or the other.

I definitely see that more now.

Nikolay: yeah, You can have a mix.

but usually people, you have some discussions like should
we have business logic inside database or an implication?

this is question we get like a black, black and white question.


So you need to choose one of one side

Michael: Do you?


Did you see there was a post, I looked it up and it
was back in 2015 by Derek Silvers around his approach.

He's a, he works by himself, he's got his own database, and
he, it's, it's a personal project, but he also sells his books.

He's very popular author and he runs everything him.

He, he wrote a post very, very strongly in
favor of putting everything database aside.

And he's, moved his application, he's migrated between different application
frameworks and I think actually that's a, that's a final point that

often gets brought up in the argument, even if it's not super relevant.

Well, I dunno, I'd be interested in your opinion on how
relevant it is, but ease of migration of application and

ease of migration of database often come up as arguments.

People seem to argue against putting logic in the database because they,
they seem to think a migration of the database is more likely than a

migration of the application, but I don't know if that's necessarily true.

Nikolay: a few, a few things here to discuss.

First if you, first you discover these capabilities,
Postgres has, you are very, like, happy.

But then for example, already, you have built
quite complex sophisticated structure of views.

For example, like here, hierarchy of views, right?

And then you need to change some.

you need to redefine them.

It's good that they, they can be, a definition
can be done and inside one transaction.

So like small interruption, like a fraction of second, and that's it.

Everything is transactional good, but it's not convenient at all.

So even if you.

for example, you have select star from some table, some view
for, for example, for a postgre and you need to redefine it.

A new column created you, you really need to recreate on
each so underlying table change, you need to recreate view.

It's like there is some maintenance cost here.

In code, it could be different and it could survive it.

Like you don't need to change codes.

Well, if you edit some column, maybe you need to change
code, but you can write it in a way that maybe you will.

Not always need them.

And also, so, so Iraq of use can be tricky and
a little bit painful in, in, in maintenance.

This is first thing.

Second thing is more important.

Where do you store your functions and how do you approach versioning?

For example, sometimes we need two versions, both of.

It can, it can be possible if you have a lot of application notes.

Some of them already upgraded, some not.

So we need to have we need to allow them working with both versions.

All on you.

People do this, some people do this.

They just use ches.

And they

have bunch of soft ches right.

And you, you can clean up old versions later.

It's quite convenient.

But sometimes people say like, Oracle also
has packages, POS doesn't have packages.

It's like, I already forgot everything
about Oracle, so I cannot comment on this.

But it feels like we are missing something.

Definitely not my topic though.

But I would, I wanted to say, Think about one function and we change it.

Uh, There is create replace, but it's limited because if you, if
signature of function changed, for example, new parameter, period.

Also it can be overloading involved because you can have
same function name, but different functions, Multiple

ones with different number of parameters, right?

But you cannot create or replace functionally,
for example some parameter change to its type.

You need to drop it and, and define ones again, and also dependencies.

It's like quite.

But my big question here, the biggest question here is that consider,
okay, signature hasn't changed, but body of function changed.

How do you approach versioning here.

Question is if you have old function version, new
function version, where do you store versions?

Post this is not good voice.


If you have uh, if, if you use name spaces, a very strict approach, right?


Use like version one, version two, and, and so on.

And so, but inside each version you also might have
multiple sub versions like you, you developing, right?

And you want to fix, like you, you want normal version control With
branching uh, meets like probably you put whole function budget to get right.

Michael: So, yeah.

What, How else would you do it?

Nikolay: I do it.

And it's good that I, if I change one
line, I see D with only one line changed.

But what about our skew?

Liquid based flyaway or rub, active record migrations,
database migrations, or jungle migrations and so on.

They're not friendly with it.

If you, if I include that function like, like, okay, I,
I keep each function in, in separate file, for example.

Good idea.

Because we can have normal GI flow and like pull request,
merge request, approval process and so on review.

But if we just included for example, if we have
migrations, we include it into each migration.

And imagine if we want to replace several.

Which version of function will, will be taken.

It'll the latest and it's not right.

We need to include like, not just include,
we need to include in a correlated way.

Michael: Yeah.

You know, I used to work like, I feel like we should probably do a whole
topic on database version control, but you know, I used to work on this.

Nikolay: I have, I have something to say about it, but may I have,
I have a talk next week and for you about database branching.

It's exactly about this.

Michael: Yeah.

Let's, let's definitely discuss that.

But in, if you do things application side, this is, this is easier.

It's, this is all handled already.

Nikolay: Yeah, you don't have this problem normally, so,
but with database, you need to do something about it.

For example, in many cases I see people abandon the idea to have this very.

Big chain sequence of database migrations and ability
to replay them because you cannot include files, right?

If you include files, you need to include all version of files.

So I, I had some code like related conclusion
for rails maybe, or for Sketch, I don't remember.

But it's like you need to develop additionally.

. So there is a gap here, and this is not convenient.

This doesn't kill, well, not convenient.

So people, if you, if you work in teams you have issues with that.

But finally like we, we let to finalize with cons of pog site programming.

The biggest negative side is that at some point
doesn't scale because this code, if you so imagine you.

Clusters and each cluster ha has a primary note
with read, write, read, write capabilities.

Other nodes are read only.

Many of your functions will be read, write, so you need to write something.

And a number of course of on the primary node is very limited.

It's the most most to say.



So, so it's most,

Michael: It's like a but neck, right?

Nikolay: Right.

It's, it's very important resource.

You, you have very limited resource only, like, for example, okay.

96 course, but that's it.

And to scale you need either to split your database in parts or
to apply some charting or just to scale vertically better, cpu.

with more course but.


if they don't realize it in the very beginning, if putting
a lot of logic inside database leads to some situation on

CPU or on disc, this calls a very, very important resource.

Here we have only one limit for disc io on primary note.

We cannot do rights on standby notes, right?

We need to do rights anyway, even if we have logical
application code, but CPU spent for some calculations and so on.

And especially not only to for calculations, but
especially for communication with external world.

The moment when you see that you need to analyze some
external webpage from some crowling scraper or, or something.

Or you even need to download some binary like file, for example image.

And you, you are doing logic inside database.

this is the, like many bells.

Should, should, should uh,

Michael: Go off.

Alarms should ring.


Nikolay: Yeah.


It should drink.


Michael: yeah, It's a great point about scaling up versus scaling out.

It's easier to horizontally scale that on the application side.

while we're on the last topics, I wondered if you
wanted to bring up SubT transaction, like the idea

Nikolay: No, no, no, no.

Let, let finish this, let finish, let me finish because
I was in this, like, I, I placed as I said, teams of

whole, whole small teams of Ruby developers, for example.

And I did it twice at least.

I used the this approach in many places of my own projects.

We've past grasp several projects.

I loved it.

I like advertised it also a lot.

the moment I started to write some cool logic in
PL python or also I discovered pls H from Peter



should be a very experimental thing.

Don't do it on production.

So you can write shell code, bash code.


And have it in pos.

So I, I did, I had some logic with COOL as well and bar had
said Povi to just to download and put some images somewhere.

I also had some parcels of external webpages.

You know, the problem of I don't transaction, right.

When an application started to work with database open
transaction, then go, went to work with some external api.

and for database it means some delay and we sit in
transaction, not not doing anything inside database if

you start working for, with external things from pogs.

this comes to you very quickly.

For example, I, I also had uh, work with Stripe.

Quite convenient.

Charge somebody right inside . Stripe has very
great, like one of the best APIs in documentation.

I also sent some Ps to Slack when, for example, user registrars
immediately sends p to Slack using trigger synchronously.

So it's not good.

It's not good because you, quickly understand that your CPU on your primary.

Does a lot of things it shouldn't do and
I see as I mentioned, super base, right?

I mentioned super base and I see they have already some science.

They go this path.

they are growing like rocket.

A lot of users, I consider them as the best team
who can productize pogs and POGS extensions.

, but I see PL s sql, http, for example, extension, they

Michael: Yeah.

They've enable, they, they let you enable it right?


Nikolay: Right?


So some signs that this like wool is, this is there for some products.

Don't do this inside primary.

If you want growth, do it only as experimental, as prototyping.

As like,

quick, quick quick prototyping.

That's it.

Don't do this.

I did it, I understood it, I did it.

But just exactly for the speed of development, it's great.

Super fast, but also secret management.

There you need, like, for example, if you need to work with.

where do you keep your secrets as uh, do you see variables?

I mean, post variables probably not safe, right?

Anybody can select current setting or show.

That's it.

No, no.

Not good.


I, I remember I kept secrets in a table and with permissions, nobody can.


there is some, some approach.

Michael: even super base are big fans.

They, big fans of row level security as well as obviously permission based.

Nikolay: Right.

Again, I'm, I'm not normal.

I'm big fan of this approach, but downsides are obvious
and everyone should understood them before going this path.

Michael: Exactly, and it it age odd.

Answer it depends,



So probably should have got that in up front.

Last couple of things I wanted to mention that I forgot to,
while you were talking about them on the query plan front.

It can be really helpful this uh, using.

Functions, nested functions uh, can be really important
to enable, well, if you're using it or to explain as a log

nested statements parameter, and putting that to true can be
really helpful for debugging or for performance debugging.

And then on the SQL server side, The tools are a bit further ahead on this.

So there was a, a project called SQL Server Data Tools and Redgate, the
company I worked at for many years had lots of tools around letting version

control and test database side, which I don't, I haven't seen equivalence of
in terms of the whole, whole framework or the, the tooling on the Postgres.

Nikolay: The biggest concern for, for their tools?

I think Red GI has it.

I mean, I mean, regular version control tools.

They all rely on gi.

GI is not friendly with big data volumes at all.

They just say, don't do this.

And so we, we only talk about schema and DML comments.

That's it.

We don't, we cannot have data version, but we need also.

Michael: Well, so they do have some, So both of those two do
have some when data is schema, so like look up tables and things.

You can version control that.

But yeah, of, of course, in terms of C I C D or testing performance.

You do?


If that, if that's what you're

Nikolay: Or regular testing where you, you need data for testing.

If you have all like few rows in the table, you cannot test properly.

Search doesn't work, that doesn't return anything and so on.

So it's also a problem.


But it's different problem, different topic at all, like

Michael: definitely.

We should cover that one.

Nikolay: Sure, sure.

Okay, so we, we roughly, we discussed big
three pros and big three cons roughly.


So I still big fan of this approach.

I know majority people are, are not trying to avoid it, but.

Still like nothing changed.

The pros are still here.

Michael: And you can mix and match.

Nikolay: and interesting to observe further how usage of pog side
programming will change with projects like super base because

users already use views and some logic and so

Michael: And triggers are really common.

A lot of use cases for triggers.

Nikolay: And URA as well.

Michael: Yeah.

Well, thank you everybody.

Let us know what you think.

Send us your requests as usual.

We really appreciate it.

Nikolay: Yeah.

Thank you everyone.

And share, Share links.

Thank you.


Michael: Take care.