Postgres FM - Stored procedures

Episode Date: October 21, 2022

Here are links to a few things we mentioned: Extending SQL PostgREST  PL/pgSQL EdgeDB  pgTAP  Sqitch Flyway  Liquibase  client_min_messages  log_min_messagesRAISE DEBUGSimplify: mov...e code into database functions (blog post by Derek Sivers)  PL/sh  Supabase auto_explain.log_nested_statements  Hasura  ------------------------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:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Transcript
Discussion (0)
Starting point is 00:00:00 Hello and welcome to PostgresFM, a weekly show about all things PostgresQL. I am Michael, founder of PGMustard, and this is my co-host Nikolai, founder of PostgresAI. Hey Nikolai, what are we going to be talking about today? Hi Michael, stored procedures, functions, triggers, what else? Yeah, so this is the age-old debate of server-side, well, Postgres-side logic in stored procedures. Well, database-side, we can mention other databases as well. This is a very old topic. I remember in 2002, I was working with Oracle and SQL Server,
Starting point is 00:00:35 and we had a lot of logic inside, be it PLSQL or TSQL code. Yeah, that even might be a great place to start with this topic because I think 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 7 Oracle worlds. Right, right. So this is a very old debatable topic, similar to ORMs.
Starting point is 00:01:13 And there are many people on both sides, big fans of stored procedures. And this love to stored procedures 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 I say store procedures, of course, in Postgres terminology, I involve functions as well. Because procedure word appeared only a couple of years ago when it was added.
Starting point is 00:01:43 In Postgres 12, I think. I remember. I remember... Maybe 11, yeah. Maybe 11. I remember Microsoft SQL Server guys laughed at me saying, oh, only now? Finally, you have transaction control. Okay.
Starting point is 00:01:56 It was friendly laughing, unfortunately. 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 will find a lot of such code. But maybe banks are not a good example because we know, especially in the US, they are so outdated. And actually, I observed them as a user and they have issues with software, definitely. And I considered usually like in russia banking is very well with very good applications and so on but since i'm on the opposite side of earth i see
Starting point is 00:02:32 i saw recently i'm already not using them but last several years i saw their downsides as well they still have nightly updates you know like this approach is still there i mean i mean the regular approach when you have a lot of server-side 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, because it's night there. So it's a sign that software is not modern. Modern software should not do this. It should be always available. And, of course, if you have a lot of code on server side, be question when it will be a bottleneck.
Starting point is 00:03:19 This is one of the aspects. But before we discuss some details, again, I think much more people prefer to avoid storage procedures. I just observed it much more. I have experience of discussions like we have today. And usually, reaction from audience is like a few people really support, but a lot of people discuss downsides and say, like, we should try to avoid it. And that's why i say i'm not normal i'm i'm a big fan of story procedures but i know their downsides functions procedures triggers i know that there are downsides but i'm huge fan i can explain why but i consider myself
Starting point is 00:03:57 as not normal normal is to try to avoid them this is our normal what What's your observation? Yeah, 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 what 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 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 them
Starting point is 00:04:42 by the way it almost doesn't use it. 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? Well, my recommendation is, like I have very interesting experience. I wrote a lot of code in PL, PGS, QL, in some code in PL Python,
Starting point is 00:05:03 some code in other languages, sometimes a little bit C. But first of all, we should say Postgres is very extendable. Like, yeah, this is one of the key places where you can extend Postgres. You can write a lot of code. And for example, you can define some new aggregate function. You just need to write some function according to some rules and then say, okay, my operator, my aggregate function will be supported by this.
Starting point is 00:05:28 And so you can select your aggregate function, then group by and so on. This is a very, very good thing. You can define your operator supported by some function. It's also like there's a good term server-side Postgres coding or programming, server-side programming in terms of Postgres coding or programming, server-side programming in terms of Postgres 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.
Starting point is 00:05:59 But first thing when we start, I think, very carefully think about opportunity to use this approach is our work with data. How strict we should be in terms of data. We want clean data. It should be how this word, I always forget this word, correct relationships. Data is consistent. Exactly. So data should be consistent. We should not have dirty data in most cases. Consistent, exactly. So data should be consistent.
Starting point is 00:06:27 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 constraints in 100% of cases, except having them inside database. Because if you implement your constraint logic inside your application code, there is 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 them. So the only way to have good constraints
Starting point is 00:07:05 is to have triggers. Well, something we can define using schema, data type, or length of text field or something. This is it. Or then we can define check constraints, but some complex logic can be defined only using triggers, and this is already server-side programming, right?
Starting point is 00:07:22 Post-site programming. And it's inevitable if you want 100% guarantee, like warranty. Someone can guarantee that data is clean. It should be done in Postgres, on database side, right? Okay, yeah. I like that. And I've already realized that I think my opinion is a bit of both. Like, I don't see myself as one way or the other.
Starting point is 00:07:44 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 okay good different aspect when i discovered postgres which which is now very popular thanks to Supabase, for example, right? Definitely. I mean, maybe some people don't realize that they use it, but they use it. Definitely. Much more users.
Starting point is 00:08:13 But at that time when I worked with it, quite long ago already, maybe like five, six years ago, 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 API development. We just define some views in Postgres. And that's it. But some code was immediately implemented in the form of functions using PL, PGSQL,
Starting point is 00:08:43 sometimes regular SQL, because you can write function using regular SQL. People underestimate this, but this is convenient sometimes. And views and some logic and everything. And I felt like I'm spending like 10x less time than they spent. It's not a joke. It's
Starting point is 00:09:00 really so. It's so easy. You just install Postgres. You need to know how to cook it in terms of authentication, some things. But once you understood it, it's like with any framework. You need to learn it, of course, right? But once you understood it, everything is inside Postgres. You define functions. You have API endpoints.
Starting point is 00:09:18 You need some advanced logic. You write a JSQL function. That's it. So do you think you were as productive as them because of the technology? Yes, technology, technology. And the server-side programming. 25% of me, of my time,
Starting point is 00:09:33 replaced a small team of three engineers or Ruby developers. Replaced, fully. And I had tests, I had pipelines, everything. 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 okay
Starting point is 00:09:53 but we don't know plpg scale we need to learn but with python we will create so they replaced it and it was in terms of time that it was it's. I mean, again, stay back, but they brought much more on the table, like machine learning and so on. Do you mean they replaced the Postgres side? Oh, okay. This is an interesting project.
Starting point is 00:10:18 They started for Ruby on Rails, then I, sometime, a couple of years, I supported them with Postgres and some PG scale coding. Then they had a lot of needs in terms of years, I supported them with Postgres and some PLTG scale coding. Then they had a lot of needs in terms of Python, and they decided to continue writing Python, writing API using Django and there. I thought you might have meant PL Python, but no. We discussed this opportunity. Exactly.
Starting point is 00:10:40 They could do that. But I don't know. 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 PLPG scale, you need to learn. It's like other language, modified other language, very old and outdated. It has some loops and if and some not super convenient approach to work with exceptions, errors, to catch them.
Starting point is 00:11:07 But it's possible. It feels like Pascal a little bit, right? But main benefit is that you're sitting there, you don't have round-trip time. Yeah. 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.
Starting point is 00:11:29 And you also need good environment where you can try many things and feel it. I mean, you need to like production like environment. I had it already, the database lab. So like, no, that time I didn't have, but now I have it and it's easy, right? You have the same size development environment. So you can quickly see performance of what you wrote, of your trigger, of your function. And it's good. This is another, so this is great. We've covered, you've touched on performance there. And I think that's, if you look at the normal pros and cons list, that's one that people
Starting point is 00:12:03 that are on the side of stored procedures, they often bring up as 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 as probably one of the most compelling reasons for doing it. Well, I would put in 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. For me, it's number one. This is obvious. Well, 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 route trim time.
Starting point is 00:12:56 We all know this extreme example when some Ruby or Python developer pulls whole data to application side and then processes it, right? Yeah. Maybe in loop updating row 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, for all another example, R code, which has some, R has some analogs for group by, for joins, and so on, in memory only. Aggregations, yeah. Aggregation, grouping, joining.
Starting point is 00:13:32 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 Postgres has already built. And algorithms, and so on. Databases are really... Well, Postgres in particular 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,
Starting point is 00:13:58 doing it without having to send that data across saves so much time normally. You still can have a problem if you, for example, use some cursor approach. Like if you, instead of updating all rows using one statement, you might like, okay, I will write PLP SQL code. I will do some for loop. And in loop, I will update each row. It also will be less efficient.
Starting point is 00:14:17 So you can do mistakes using this approach as well, but at least you won't have network there, right? Yeah. I wanted to emphasize, it's not only about run-trip type, but also about capabilities you have. If you pull data to memory, you can do anything with it, right?
Starting point is 00:14:35 But you cannot use indices which are left on server side. You don't have them. So we've covered a couple of the things people often cite as really good reasons for pushing more and more, if not all of the logic that side. The downsides or the criticisms I often hear are less good development. Let's mention one more. SQL in general is very, very powerful for working with large volumes of data.
Starting point is 00:15:00 So many things developed. You can write a few lines instead of hundreds of lines using regular language. And of course, you want to ensure that it will be efficiently executed. That's why you need some understanding experience and good playground. But in general, the code is brief, right? And in PLPG SQL function inside it, you can use regular SQL combined with this PLPG SQL. They are working together without any need, unless you use dynamic SQL, you can avoid using some like execute or query quotes and so on. You don't need them at all. So it's like they are integrated already.
Starting point is 00:15:40 And by the way, there's EdgeDB with their EdgeQL project, which tried to reinvent SQL. It's another topic. But also, maybe in future, more convenient work will be done in using regular languages, JavaScript, for example. So I want just to say that code, much less code, usually, somehow, much less code. You don't need to write loops and so on, like open database connection, close database connection. No, no, no. Everything is automatic there.
Starting point is 00:16:10 So yeah, should we cover some of the downsides? A lot of downsides. As I've said, I consider myself as not normal because normally people say we won't go this path. First of all, they mention, usually they mention that it's not convenient because there is no good developer tooling. IDE or debugger, we usually mention. There is debugger, actually. But people aren't familiar. People are familiar with their existing, you know, backend framework debugging tools.
Starting point is 00:16:38 So it's more familiarity and expertise, I think, when it comes to using those things. Right. I personally, I live in T-Max and VI. I'm not normal already, right? And I prefer avoiding graphical UI. I use it sometimes, but I like when my work is on some remote machine, so I'm not dependent on my laptop or anything. I can continue working in T-Max from anywhere and also working in pairs if needed and so on.
Starting point is 00:17:08 But if we talk about IDE integration, actually, maybe I don't know. Maybe there are some good examples for PLPG scale, which I just don't know. But we switched to PLPG scale discussion only. But again, you can write using SQL, using PLPython, PL other languages Postgres has. But the question is, how you, like, debugger.
Starting point is 00:17:30 Debugger, okay. In my personal opinion, you should have a lot of code, complex, stored in multiple files, to have a need in having debugger. There is a debugger for LPG, PLPG SQL. I never use it. I don't need it. Usually, function, even if it's 100 lines of code, print lining is fine for me. Also, raise debug.
Starting point is 00:17:50 Raise debug is good. What about tests? You mentioned testing. You mentioned when you had that project you were doing, you had tests for it. Database lab. What do you use for testing procedures, making sure they don't break?
Starting point is 00:18:02 Well, first of all, you should have data, enough data to test properly. So my approach is to have production size database same number of rows or close to it so you just spin off your think clone using database lab and do anything there do you use like pg tap or anything like for oh yeah usually i prefer using sketch instead of flyway lookabase or so pg tap and sketch it's from David Wheeler, if I'm not mistaken, same author, right? So Skitch 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 a good point, how you manage your function definitions.
Starting point is 00:18:40 Source control, right? Yes, version control. Let's discuss it in a bit. So I usually try to have some logic in verify the plpg sql has a cert function it's very convenient so you can write another and you you can also have an anonymous do block in plpg it's also in plpg sql so you do and then some string usually people write dollar dollar and then some PLPG scale logic there. And there you can have assert.
Starting point is 00:19:07 So you write anonymous do block inside verify function for sketch just to test something. For example, you have a function which did something. Okay, you executed it, then you ensure that result is as expected. And if you don't have assert,
Starting point is 00:19:24 for example, in SQLql context you need to verify it okay you can use divide by zero or you can write a helper function which will raise exception warning notice or debug anything you want so you can have helper functions and call them using regular sql and you can have some case command 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 use it. I just don't feel it's needed. Never.
Starting point is 00:20:06 I use, usually I use a lot of asserts. And asserts just like, you know, this is not possible. Let's have assert here. And I use usually a lot of raise commands on various levels. And if you check my code, you can find raise debug in many places. So anytime when you're debugging something, you can change client mean log, log mean client level. I always forgot.
Starting point is 00:20:30 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 you have Rails debug, it won't be present anywhere by default. Unless you say, okay, I'm in debugging mode, I change my level in either or both places. There are two places, right? or something, they have migrations built in already,
Starting point is 00:21:06 and they don't have to worry about separate tooling for version control, source control, that kind of thing. Right, but they also have, first of all, they can have functions, they can have triggers, they do it, and they also have quite advanced testing capabilities. So I think everything is doable. If you want to move some logic to database, it's doable.
Starting point is 00:21:29 And I think this is where I've landed. I think you can have a bit of both. It doesn't have to be a holy war, one or the other. I definitely see that more now. Yeah, you can have a mix. But usually people have some discussions, like should we have business logic inside database or in implication? This is a question like a black and white question, right?
Starting point is 00:21:50 So you need to choose one side. Yeah. Did you see there was a post, I looked it up, and it's back in 2015 by Derek Sivers around his approach. He works by himself. He's got his own database. And it's a personal project, himself and he's got his own database and he it's it's a personal project but he also sells his books he's a very popular author and he runs everything himself and he he wrote a post very very strongly in favor of putting everything database side and he's moved
Starting point is 00:22:18 his application he's migrated between different application frameworks and i think actually that's a that's the final point that often gets brought up in the argument, even if it's not super relevant. Well, I don't know. 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.
Starting point is 00:22:36 And people seem to argue against putting logic in the database because they seem to think a migration of the database is more likely than a migration of the database is more likely than a migration of the application. But I don't know if that's necessarily true. A few things here to discuss. First, if you discover these capabilities Postgres has, you're very happy. But then, for example, already you've built a quite complex, sophisticated structure of views for example like here hierarchy of views right and then you need to change some column you need to redefine them it's good that
Starting point is 00:23:12 they they can be a definition can be done inside one transaction so like small interruption like 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 example, for Postgres, and you need to redefine it, a new column created. You really need to recreate on each.
Starting point is 00:23:36 So underlying table changed, you need to recreate view. It's like there is some maintenance cost here. In code, it could be different and it could survive. Like you don't need to change codes. Well, if you added some colon, maybe you need to change code. But you can write it in a way that maybe you will not always need them.
Starting point is 00:23:55 And also, so hierarchy of use can be tricky and a little bit painful 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 them. It can be possible if we have a lot of application nodes, some of them already upgraded, some not. So we need to allow them working with both versions, old and new. People do this,. People do this.
Starting point is 00:24:25 Some people do this. They just use schemas. And they have a bunch of schemas. Right. And you can clean up old versions later. It's quite convenient. But sometimes people say Oracle also has packages. Postgres doesn't have packages.
Starting point is 00:24:41 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 wanted to say, think about one function, and we change it. There is create or replace, but it's limited because if signature of function changed, for example, new parameter appeared,
Starting point is 00:25:03 also it can be overloading involved because you can have the same function name but different functions, for example, new parameter appeared. Also, it can be overloading involved because you can have the same function name, but different functions, multiple ones with different number of parameters, right? But you cannot create or replace function if, for example, some parameter changed its type, you need to drop it and define once again. And also dependencies, it's like quite complex. But my big question here, the biggest question here, is that, consider, okay, signature hasn't changed,
Starting point is 00:25:28 but body of function changed. How do we approach versioning here? Question is, if you have old function version, new function version, where do you store versions? Postgres is not good. Okay, if you have,
Starting point is 00:25:42 if you use namespaces, a very strict approach right you use like version one version two and and so on and so on but inside each version you also might have multiple sub versions like you you're developing right and you want to fix like you you want normal version control with branching commits like probably you put whole function body to git right so yeah what how else would you do i do it also and it's good that i if i change one line i see diff with only one line changed but what about our sketch liquid base flyway or ruben active record migrations database migrations or janga migrations and so on they're not friendly friendly with it. If I include that function,
Starting point is 00:26:26 like, okay, I keep each function in separate file, for example. Good idea because we can have a normal git flow and like pull requests, merge requests, approval process and so on, review. But if we just include it, for example, if we have migrations, we include it into each migration. And imagine if we want to replay several migrations, which version of function will be taken?
Starting point is 00:26:49 It will be the latest, and it's not right. We need to include, like, not just include, we need to include in a correlated way. Yeah, you know, I used to work, I feel like we should probably do a whole topic on database version control, but, you know, I used to work on this. I have something to say about it.
Starting point is 00:27:08 But I have a talk next week at PGConf EU about database branching. It's exactly about this. Yeah, let's definitely discuss that. But if you do things application side, this is easier. This is all handled already. 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
Starting point is 00:27:37 replay them. Because you cannot include files, right? If you include files, you need to include all version of files. So I had some code-related inclusion 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.
Starting point is 00:27:57 So people, if you work in teams, you have issues with that. But finally, to finalize with cons of Postgres programming, the biggest negative side is that at some point it doesn't scale because this code, if you... So imagine you have clusters. Each cluster has a primary node with read-write capabilities. Other nodes are read-only. Many of your functions will be read-write. So you need to write something.
Starting point is 00:28:30 And the number of cores on the primary node is very limited. It's the most, how to say? Scarce? Yeah, yeah. So it's most... It's a kibbutzonek, right? Right. It's a very important resource.
Starting point is 00:28:43 You have very limited resource only, like, for example For example, 96 cores, but that's it. And to scale, you need to either split your database in parts or apply some sharding, or just to to some situation on CPU or on disk. Disk is also a very important resource here. We have only one limit for disk I.O. on primary node. We cannot do writes on standby nodes, right? We need to do writes anyway, even if we have logic on application code. But CPU is spent for some calculations and so on. And especially, not only for calculations, but especially for communication with external world.
Starting point is 00:29:28 The moment when you see that you need to analyze some external web page, some crawling, scrapper or something, or you even need to download some binary file, for example, image and you are doing logic inside database,
Starting point is 00:29:44 many bells should go off. Alarms should ring. Yeah. Yeah. This should ring. Yes. Yeah. It's a great point about scaling up versus scaling out.
Starting point is 00:29:54 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 sub-transaction, like the idea of... No, no, no, no. Let's finish this. Let me finish because i was in this like i i replaced as i said teams of yeah whole small teams of ruby developers for example and i did it twice at least i used this approach in many places of my own projects we've progressed
Starting point is 00:30:17 several projects i loved it i like advertised it also a lot but the moment i started to write some cool logic in pl python or also i discovered plsh from peter it should be a very experimental thing don't do it on production so you can write a shell code a bash code actually oh wow and have it in posgis so i i did i had some logic Curl as well and bash inside Postgres just to download and put some images somewhere. I also had some parsers of external web pages. You know the problem of an idle transaction, right? When application started to work with database, open transaction,
Starting point is 00:31:02 then went to work with some external APIs. And for database, it means some delay, and we see the transaction not doing anything inside database. If you start working with external things from Postgres, this comes to you very quickly. For example, I also had worked with Stripe. Quite convenient. Charge somebody right inside Postgres.
Starting point is 00:31:24 Stripe has one of the best APIs and documentation. work with stripe quite convenient so charge somebody right inside posbios stripe has very great like one of the best apis and documentation i also send some pings to slack when for example user registers immediately sends ping to slack using trigger synchronously so it's not good it's not good because you quickly understand that your cpu on your primary node 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 maybe the best team who can productize postgres and postgres extensions today but i see pl PL, SQL, HTTP, for example, extension.
Starting point is 00:32:05 They have. Yeah, they let you enable it, right? Right, right. In their extensions. Some science that this wall is there for some projects. Don't do this inside primary if you want growth. Do it only as experimental, as prototyping, as quick prototyping. That's's it don't do this i did
Starting point is 00:32:27 it i understood it i did it but just exactly for the speed of development it's great super fast yeah but also secret management there you need like if for example if you need to work with stripe where do you keep your secrets as uh guc variables i mean postgres variables probably not safe right anybody can select current setting or show that's it no no not good also i remember i kept secrets in a table and with permissions nobody can you can yeah there is some approach even super base are big fans they big fans of row level security as well as obviously permission-based. Right. Again, I'm not normal. I'm a big fan of this approach, but downsides are obvious and everyone should understand them before going this path. Exactly. And it's an age-old answer. It depends, right?
Starting point is 00:33:18 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 it can be really important to in a well if you're using auto explain there's 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, the tools are a bit further ahead on this. So there was a project called SQL Server Data Tools, and Redgate, the company I worked at for many years, had lots of tools around letting you version control and test database side,
Starting point is 00:33:57 which I haven't seen equivalence of in terms of the whole framework or the tooling on the Postgres side. The biggest concern for their tools, I think RedGit has it. I mean, regular version control tools, they all rely on Git. Git is not friendly with big data volumes at all. It just says, don't do this. And so we only talk about schema and DML comments. That's it.
Starting point is 00:34:20 We cannot have data version, but we need it. Well, so they do have some. so both of those two do have some, when data is schema, so like lookup tables and things, you can version control that. But yeah, of course, in terms of CICD or testing performance, yeah, if that's what you're talking about. Or regular testing where you need data for testing. If you have only like few rows in a table, you cannot talking about. Or regular testing where you need data for testing. If you have only like few rows in a table, you cannot test properly. Search doesn't work, doesn't return anything and so on. So it's also a problem. But it's a different problem, different topic at all. Definitely we should cover that one. Sure. Okay. So roughly we discussed the big three pros and big three cons, roughly, right?
Starting point is 00:35:05 So I'm still a big fan of this approach. I know the majority of people are not trying to avoid it, but still, like, nothing changed. Pros are still here. And you can mix and match. And interesting to observe further how usage of Postgres site programming will change with projects like Superbase. Because users already use views and some logic and so on.
Starting point is 00:35:30 And triggers are really common. A lot of use cases for triggers. And Hasura as well. Yeah. Well, thank you, everybody. Let us know what you think. Send us your requests as usual. We really appreciate it.
Starting point is 00:35:42 Yeah. Thank you, everyone. And share. Share links. Thank you. Bye. Take care.

There aren't comments yet for this episode. Click on any sentence in the transcript to leave a comment.