 Welcome to NORM, Bridging the Gap Between Applications and Databases. Today, we're going to talk about life without ORM. Is it possible and how can we get there? In this talk, Hedy's going to present the approach developed and implemented at Bravian Holdings. NORM allows building performant and scalable applications and is praised by developers for the ease of use. My name is Lindsay Hooper. I'm one of the Postgres conference organizers. I'm based in New York. And I hope everyone is staying safe and healthy. I'm here with Henrietta Dombrovska. She's a database researcher and developer with over 35 years of academic and industrial experience. She holds a PhD in computer science from the University of St. Petersburg, Russia. And at present, she's the associate director of databases at Bravian Holdings in Chicago. I'm a member and a frequent speaker at Postgres conferences, which is how I know Hedy. And a researcher focused on developing efficient interactions between applications and databases. So with that, I'm going to hand it off to Hedy. Thanks, y'all. Thank you, Lindsay. Thank you for inviting me to talk here. I'm delighted to talk. It had been a long time since I talked to any professional gathering. So this is not the name of the talk now that is on the screen. It's the name of my company. So I'm associate director of databases in Bravian Holdings. And the best thing about this company that the leadership team allows me to do whatever I want. So I have this unique privilege to build a system, exactly how I think it should be. And you know what? Also because it's actually a real company, real industry, finances. If I build it wrong, I will be punished by bottom line. So again, I like being there. And I did lots of cool stuff there. And I want to, today I want to talk about norm. All right. It's not moving here. Okay. Okay. First hiccup because I am not in the right screen. Okay. Here's norm. So that is the name of our talk. So our talk because. So the talk I'm giving today is almost what I was presenting at the academic conference on Cyprus. The name of the conference was SOF SEM 2020. And I think it was the last conference of 2020 last week conference because we presented in the end of January and then everything started. So I was very happy to talk there because I find it very difficult to bring topics like this to academic conferences because nobody believes that it's something worth talking about. And again, they're free offers for this talk. So one is myself. Jeff Shaplevsky is the principal system engineer in Bravian Holdings. And Boris helped us a lot. He actually developed a couple of key functions here, but without these functions, things just won't happen. Okay. And when we first submitted this work, we did not have the word norm. So then while we were getting ready to the conference, we came up with this term norm. So what norm means, it means no ORM because you know what? We have no SQL. And for some reason people believe that no SQL is the database. We know it's not because, okay, we are both with people who know it's not. But anyway, so that based on no SQL, I thought norm is a good idea. Norm is life without ORM and that's normal. Living without ORM is normal. So here comes norm. And because we already had our title slide ready, we put it on the side so that everybody are mystified. What is this? What is this? So we'll find out again, just remember that was presented to academic community who do not really know what industry is doing. So I had to fill them in. Okay. All right. So here comes the first slide. Computer science and industry. So how they are related. They're supposed to be actually very closely connected because computer science is like one of the sciences, which is like most closely connected to the business. However, here are the hard facts. Most of computer science students never saw real databases. And I can tell you when I come to the conferences and like, okay, I have my laptop. I have the real database. Yeah. You know what? They just look around. It's like I'm like the most popular person because she has a real database on her laptop. So normally they do not see real databases, but they just see their like whatever test data sets or whatever you call it. Most, more frightening fact is that most of their professors never saw the real databases either. So they do not know, you know, how the real world looks like. And because of this research papers like this, never make to the academic conferences. So why they never make academic conferences because nobody understands what's about with us. We always had a problem because it's like, okay, it does not look like databases. Why you wouldn't submit it to software engineering conference software engineer said that's about databases. Why you are doing this. So so Sam was actually very interesting confidence. It was combined database and software engineering. That was the only reason we got submitted also we had connections in the program committee, but reviewers still did not understand what we were doing. So before publication we had to cut more significant parts from paper. So, yeah, but still we were very happy to present and so you'll see what we presented and a little bit more because I have a little bit more time here. Now, that is my most recent edition, and hopefully we can actually paste it in the group chat. When I presented this work number of times at postgres conferences, people were asking me, do you have a working example? And I was like, you know what, I should probably write it and never did. So a couple of weeks ago, the question came up again, and I'm like, okay, you know what, I will do this. So if you go to this little report, you can see the working example of what I will be talking about. And later you can like, you know, you can just cloud it, you can create this example, you can see how all these things work so you can see it works for real and that's not so difficult. And you can actually go there right now, it's like very small report. So you might see the whole code because in course of this presentation, I will be only showing like small code snapshots but here is the real thing, real detail, real packages, so whatever we developed. Moving forward, so I am from industry, I am from finances, so thereby I'm the real capitalist shark. I'm all about money. What should interest me, right, because you know the consumer lending online loans, that's evil thing, right, we'll know, right, they're evil, the unhumane, and by the way, that's why we as small business never get government relief because we're evil. And by the way, we are not welcomed in the state of California. Hello, of course we are doing evil business. So, okay, so we are very bad, and they're all about money. So here's the question why I care about something related to application performance. And I think you probably know why, right, because time is money. When we are talking about time as response time, response time really matters, and it's very important for the financial success. So how exactly money by the numbers. Those numbers are actually pretty old. I never saw your numbers. So let's go with the ones which are like four probably years old. Amazon reported that one second page load slowdown results in a loss of 1.6 billion sales, like one second page slowdown. Google reported the slowing search results by 0.4 seconds equivalent of a loss of 8 million searches per day. So you can only imagine how much money is that right. But, you know, certain you saw that not only this financial loss, but also 50% of visitors abandoned the site, which is not loaded within three seconds. And by the way, people have become more impatient because say 15 years ago, it was seven seconds, then it became less and less. So now actually people say they cannot wait more than like one and a half second, two seconds. So people just do not wait till the site is loaded. They abandoned the site. And even worse, 79% of visitors will never return again. So that's why a quick response time is vitally important. And you know what? We do not like it. Forget about Google, forget about Amazon. How often you see this? You see it often and you do not like it. So nobody likes it, nobody likes to wait. So that's why we care about wait time, about response time. Now, why does it happen? Why we see all this wait, wait, wait, wait. You know what application developers are saying, right? They're saying database is slow, but we're database people and we know that database is not, but that's what they think. So let's see why this happens, why it is slow. And now here comes the name of our talk, Connecting Galaxies. So why Galaxies? When we talk about galaxies, we talk about applications and about databases. You know why we call them galaxies? Because they do know nothing about each other. You will not believe it, but that is true. So applications and databases are developed as if they know nothing about how the other galaxy behaves and communications are the real issue. So let's look at communications. So we have application model in one galaxy and we have database model in the other galaxy. And you know what is application model? Object-oriented, obviously, right? Because everybody forgot there was something before object-oriented, so we are object-oriented. So what we have in the database side, especially when the database side is positive, we have object-relational, right? And we have our nice relationships. We think in datasets, we think in bulk and so on. Now they need to talk. This galaxy and that galaxy, they need to talk. You know how they talk? That's how they talk. If you think they just talk to each other, they don't. So that's why this orange line on top is crossed. They talk by means of flat tables. So application disassembles their request to the database into these microscopic pieces in the single rows of the flat tables, and then they move it to the database. And they move it through GDBC, which was developed by previous generation, so they do nothing about complex data types. And you see that between tables and databases, it's just these lines, lines, lines, and we have a little bit of a gap between application and tables. And do you know who sits in this gap? You know, I will tell you. ORM, object-relational map. So that is our evil creature. So why it is evil? Because the idea was that application developers can develop applications knowing nothing about databases. And ORM is supposed to translate their model into the database model. And it does it like in not really efficient way. It's ORM, which disassembles the request from the application into these small individual access requests, and they are transferred from GDBC. And for GDBC, they come to database, is this like meters of small queries, then they are executed in the database separately in their transferred back. And so it's all like goes da, da, da, da, da, da, da, da, da, da, da. That's what happens, right? So yeah, that's our real bottleneck. So that's our camel who cannot get through the nidalee. Okay. So it's nice, cool picture. You can laugh, you know, like, I mean, you are muted, but you can laugh about this camel. Everybody loves this camel. But, okay. So what's, why? Why this is important? Why it's a problem? So yeah, it's funny picture. Yes, but why it's important? Okay. All right. So it's instead of one query, we have 10 queries, 20 queries. Is it a big deal? Actually, it is a big deal because we are talking not about 10 queries, not about 20 queries. So how bad this inefficient interaction can become. The big part of this work, like first phase of this work was done at my previous company at Innova, and there I saw in real time how bad it can become. Because then people actually approached me and said, you know what, we need to do something. So that's how it looked at Innova. Look at this. Select. Select start from loan by ID equals whatever, some constant. Okay. What can be more simple than this? This is executed fast in milliseconds. This can be never performance problem. Yes, except of when the number of executions is 11 and a half million times a day. So with every execution time, less than 10 milliseconds total execution time about two and a half hours. So for two and a half hours during the day, it executes select start from loan. And if you think, but maybe that's what it's needed. Maybe that's how many times users are connected. No, they did not have 11 and a half million sessions. That's why it happened because the same select was executed within one application endpoint multiple times. And multiple means not 10 multiples means not 20. Some applications controllers would have over 1000 database calls for each screen refresh. And you mentioned that it's not that many characters on the screen like it's like five database calls to display one character or something like this. So between the user hits next and something appears is 1000 database calls. So that's how bad it can become. Now what this cannot be improved by anything because when I show these numbers, you know what people are saying, especially software engineers, you need more powerful computers, you need more hardware, people like to have more hardware. Okay, yeah, oh, yeah, I forgot about this. So in this case, with 1000 executions, the first page response time was over 20 seconds. One database query executed for less than 10 milliseconds. The page response time over 20 seconds. Awesome. Okay, so more hardware. This is what people say. So we need more hardware. You know what more hardware doesn't really help because this all of these have to be executed, not in parallel, they have to be executed consecutively. So that time it was five years ago. So people now think, oh, that is like some old stuff. Yes, it's old stuff. But anyway, so that's how the computer looked like. It was not on the cloud, not an Amazon like in-house bare metal. So 500 gigabytes of RAM almost completely used by database disk cache. So with all this, the response time was 20 seconds when we need one. And the engineers at the time told us that even if they will buy most expensive hardware available, they could only make it twice faster. And we needed to do it 20 times faster. And by the way, yeah, hardware is also being developed. So I left this company four years ago. And I talked to their lead debate recently. And that's what he told me that current memory size is not half-terabyte, but four terabytes. And the response time is still slow. There is that end, but you know, they're not interested. We know better ways to do it. Our approach, what we decided to do. So we looked at these interactions in all new way. That is our schematic representation of norm, norm being no ORM, ORM replacement. So what is different in our approach? We are not transferring application model to database model. Our approach is symmetrical. So we map the application model to the transfer model and database model to transfer model. This mapping is similar and application and databases can talk with using the same terms. So the transfer model is represented in JSON because actually object-oriented application thinks in JSON, the objects are JSON objects. And we know how to represent JSON objects in Postgres. So then for transferring purposes, these objects are represented as text. And the reason why, because JDBC does not know that JSONs existed. So JDBC need to transfer them as flat files. It's a flat text, the text, and that's what we're doing. But other than that, no norm, no nothing. So the next slide will be kind of, I mean, application developers love it because they like the concept of contract. Because they use the slider to develop application. So our approach is a contract, not between different parts of applications, but between application and database. So both sides, application and database convert internal representation into complex hierarchical objects. And contract establishes object structure implemented on both sides. So now for any application endpoint, it takes one database call to transfer data to and from. Okay, to be fair, sometimes not one, sometimes two or three, but that's it. So we can say one or two. Now, how exactly we're doing this. So I'm not sure how convenient it would be to look at this norm repo, but I mean, see for yourself, whatever works for you. Because I, what I will be showing now is kind of closely based on this repo or the other way around. Okay, so that is our database schema, it's normal database schema, right? It's normal database objects, because each application has users, right? Each application has user account, anything, anybody selling anything on the web, they have user account. So we have user account, normal user account. We have username, first name, last name, social security, which we're not supposed to have, but we have it anyway, like all the other people, date of birth or whatever. So user account has email address, maybe multiple because people tend to have multiple email addresses. User might have address, maybe multiple might have phones, most likely multiple. And this is how we store it in the database, because we want to build nicely normalized schema. But if you think about this, it's one object user account. So what we are mapping this object on, we are mapping it on C object or transfer object. And we represent it in Postgres by the record type, user defined record type, because thank you, thank you, thank you Postgres, we have this ability to build complex object. Yeah, so for user account, we have some information which is like linear, and it's related to one user account, like username or full name, whatever. And then we have multiple address objects embedded in this user account object, multiple phone objects, and multiple email objects. And all this is represented by the record types, and I will show you in a second how exactly it is represented. So that is implementation detail. So now actually there will be stuff from this report kind of. So we want to get all information from the database to the application in one database call. So theoretically we want to have one select, start from basic select. And if you think that you think you cannot go wrong with this one. So if you can imagine if people are using ORM to build their applications, there may be four different database calls. One select username and other select assets, then the select database, because that's how we're in books. They have methods they do not know what's inside. So at the minimum, we want to have one database call to select everything. Now, what we're doing next, we are creating a type. So that is our user account record user account username, social security data of wealth. And when we select, we convert this result to the user account record. That is all good. But as we said, we need to have complex objects inside. So we create the address record, the phone record, the email record, and then we have composite type. So now user account has some scalar values and some sets of records inside. So Postgres allows this because Postgres is cool and awesome. And Postgres actually stores information about all these embedded types correctly internally externally, it actually keeps it to itself. So if you want to build a function which returns this type, just like I said, if you want to do subselects and subselects will return these sets, there will be something very interesting. So if you will try to do this, you'll see that it will actually return these record sets as some like text strings with lots of like internal interesting characters. So that's where all my previous attempts to communicate with applications and application developers would fail because like complex record types are not returned properly. So that's what first we did. So first, okay, what we wanted to do first, we had this function, okay, so we returned these record sets from the function. And when we were returning the definitions of the nested types were lost so the application could not process it the way how they were defined. That was the first take of what we can do because before that we were not even thinking about JSONs and that's okay. We now have JSON types in Postgres and we can build JSON objects. So here what we're doing by building address inside user account and we can return and everything actually looks perfect because now application can read. This object correctly and it doesn't have any web symbols. So then we said, okay, maybe we can wrap the whole thing in JSON and read it. Okay, and we started to return nice and beautiful JSON. So that's how it looks like. And if you kind of recall what we talked about like five minutes ago about models. So this way we mapped the object or database object into T object or like transfer objects. So what we had remaining problem. So when if we return JSON, not the record type from the function, we lose strong types and strong types are important. And not only for us application developers think they are not important but actually they also do not like to receive a field, which was not in their contract. So actually they want us database to check strong types on the other side. So we still wanted to be able to define the strong record types, not like some return some JSON like then anybody can return any JSON you cannot distinguish between user account, user application, user loan, user document, whatever. So the other problem is, and when I talk about this or we, we return JSON, but returning JSON is slow. Yes, if you build JSON in select statement, it is slow. And especially it is slow, not when you select one user, not when you select Henrietta Dombrovskaya, which may be one in the universe. But if you are selecting John Smith from your database, and even in small database you can easily have like a 1500 John Smith, then you have a problem because 1500 JSON will be built slowly. So we like in attempt to resolve these problems, we came up with different approach. So this is like schematic representation of this approach and I promise there will be something clearer. And actually, again, if you look at this get report you'll see how we build this, because it looks a little bit horrifying as a schema, but what we're saying is simple thing. So that's master detail like relation. So if master is a user account and details say address. So what we're doing, we are taking the master record, and then we are doing array aggregate from the detail type. So that's what is presented on the top. So one to M master detail. And that's how we group them. So we use array aggregate and everything is looking pretty good. That's how it looks in Postgres. So any select in this situation any function can return potentially multiple objects because they're such results. And we aggregate the embedded types is separate aggregates and then we aggregate in array the master type. So now, okay, we have all the object structure, and there is no JSON yet, because building JSON, while the go is slow and we do not want it. And now, like, it's the final touch. That is like our So if you want to replicate this approach, you can create whatever functions you want, but you must have this function, because what it does, it builds JSON when it's already built. So the object structure is already built in the function, then you just take this function output. And then you look at this as a text basically, and then you build JSON from this result, and then you convert it to the text, and these text strings can be shipped between database and application through to the VC. It looks super easy when I'm telling you now. So obvious. And this function is so funny, easy, like why nobody came up with this. It was actually a struggle now I'm not going to lie. We ran into so many hurdles while we were developing it. There's so many things which did not work. So I am giving huge credit to our amazing application development team because they were super patient, and we did so, so so many things back and forth, just making sure that it works perfectly. Now, about updates. So again, when I first started to present it, the question was, okay, that is cool about selects, but what you're doing with updates, how you are living without ORM, and how you're doing updates. Again, now there are examples in this ORM report, but in a nutshell, we do the same thing. So when we are updating objects, we are using the same JSON for the object, same contract idea. So here, for example, we are doing some deletes and some updates. And look, here we have four different tables involved. We still can send one object. So what are we doing here? We are just disassembling what was sent. So here, for example, we are updating address, so why are we updating address because we have address ID, so no, address already exists. And we need to update this ID. And we are deleting phone because we have this special JSON key command, which says delete. So we're deleting the phone with this ID. And the second example, we update the name, why we're updating it, because there is name, and we have a new value for the name, and the user account is already there. So we know it's not new user account, it's like updating existing. And then we are inserting email address, so why we know that we're inserting because this email address comes without ID. So that means we do not have it yet, we need to insert it, generate ID, and assign it to this user account. So again, have examples there. Now, going to the final stage of this presentation, it works. So what I mean is it works. It works that it actually produces wonderful performance. And performance-wise, I had this very old slide and I tried to make yesterday some newer slides. Okay, so this one I created like very long time ago, so the idea was what we were showing here, that through the day, the number of executions or functions increases, naturally. But through the day, so the low portion of the slides represents that the response time for all of these functions is the same for the day, no matter what. And this picture, this graph represents that the database is growing and whatever database is growing, still the response time of major functions is the same. So what I tried to put here yesterday, okay, like, I did not edit it, so I'm just honestly leaving the speak, you know what the speak was, there was like this one huge utility function working at 7am, and this kind of like spiked with performance. But forget about the screen spike. So what you can see here through the day, the number of executions actually vary, but the performance time stays stable except of the stupid spike, which I will need to deal with. And we have the same picture for all the functions. And by the way, if you look at how long is the execution time, you can see we have like 50, like 150 milliseconds. So that is our like normal execution time. So we are very proud about this because, because, so remember about like three seconds right, not more than one second. So our, like, vast majority of our functions are executed under 100 milliseconds, no matter what. And the update functions are executed like under two 300 milliseconds, that maybe some spikes maximum a second. And you know why we were able to achieve this performance because we have such an awesome principal engineer, and he just decided that they're putting hot time out for application for 10 seconds because nobody can wait for 10 seconds. And if you want to avoid spikes for 10 seconds, you need to make sure that nothing is executed over one seconds, because spikes happen because of like whatever. So basically, our optimization goal of whenever we are developing everything, everything, everything, everything should run under one second. And again, most of the time it runs under 100 milliseconds. So I'm like really proud of this. Okay. So, so that is what we had to show. And now kind of starting to go to conclusion. So it was academic presentation. So academic presentation has to have related in related well so related and unrelated. So question which is always asked. So why not to use manga. If you want to have Jason's why not use manga. No, okay, we are for this people so we know why we're not using manga because it's manga because it's not database right privately. Like, okay, this statement aside, norm allows flexible hierarchies manga does not allow flexible hierarchies hierarchies are like preset. The other thing is that a manga does not have the relational search engine capabilities and we have so we store it in postgres and we can have this super fast super optimized postgres search. And second, why not standard Jason build function. And I've shown that at some point we used standard Jason build. Again, the reason is because as we build Jason as we go it just slower. So that's easy. We go to store Jason. So that's another question because thank you people thank you and the desktop thank you other people, we can store Jason's and we can index Jason's and we can search by Jason attributes. Again, it is slower. It's not, it's not saying it's slow slow probably is like, you know, little bit too hard. It's very slower than searching by linear attributes. So we just, you know, we are going towards extremely performant application and other things which I wanted to mention so authority be. I actually, I talked to a lot of multiple times because I really wanted to project with him because he literally did the other side of this. So what they're doing, they were converting in the opposite direction so they were taking stuff from manga, converting them into postgres executing queries and postgres and sending it back. I really would love some point of time to collaborate with him and kind of like have these two pieces together. Postgres. So that's another thing which people often ask, so how you are different from postgres because postgres is mostly interface and it doesn't allow to put all this complexity inside as we can again you can see examples in the normal repo. So, what's next. I want to do a lot with this and one of the reasons why I was so happy for this opportunity to talk here because I hope that people will be interested in this and maybe somebody will be interested in working with us. Many of these things can be automated like generation of lots of stuff now that we wrote several hundred packages I think we know exactly how they should look like. We should be able to use JSON schema as a source of contract. And then you can automate types generation, you can automate three quarters of the functions. There is always room inside for the art of writing superficial queries, but every single object needs to have insert update basic search in this already wrote so many of them we just take previous and make some updates this definitely needs to be automated. So extension note. Again, I put it here because I got lots of questions. Do you want to do extensions or even more I got or I want to write extension out of this like please and that was it. So I'm not sure how extension would look like, but I think that it would be super cool because you know that is the most cleanest way of interaction between application databases. We do not need any or M this one has the least possible overhead is going directly from database to application in the format which application developers love. So conclusion. So response time is critical. We know why we need a short response time time is money. Diminating technologies do not support interactions in terms of respective models. So if you think about this there are complex objects in application that are complex objects and databases. They do not talk. So current technologies disassembled here disassembled here and nothing would come out of it, we can do better. The enormous technology based on the concept of contract application developers love contracts. It allows to use the power of the BMS engine and still preserve the complex object structures. It can be easily adopted by application developers and all my previous experiences. It's too complicated. It's too complicated. So you know what this technology they love it. They love it because also they can start developing without waiting for the database because we have a contract they know what to expect from us. So we actually integrate. We can integrate on the very last stages and everything works so they're happy and well so happy. And also this help develop high performance applications without maxing out on hardware resources so it's like great source of cost reduction. Okay, alright you know what I'm actually done because I just saw first question and but I'm actually done so I can answer questions like because next slide is basically questions questions so we can finish with this presentation and I can go ahead with the questions I can actually stop sharing if I will figure out how. Right. Okay. All right. Okay, cool. Okay, so what is the question. Okay. What have you observed about the size of the data transported for Jason versus standard result processing. Okay, it doesn't. So there's the thing is that we are not transporting Jason's we are transporting. Text. So it's does not matter what is the format. I mean, yes, transferring bigger data volumes, it will be slow. So, for example, I can tell you our application now times out on one occasion on one occasion when our customer apps accidentally put in the search. State of Texas and nothing else. So when they search for everybody in the state of Texas, then yes it times out. So other than that. Yeah. So, again, complex objects are built on the database side and returning everything in one selected all this faster than returning multiples. The other thing is that you will never display this all these people from the state of Texas right so when we're talking about will to be application that means we're talking about some like reasonable amount of data which needs to be done. So, again, there are lots of techniques how to kind of you know work with what actually needs to be retrieved versus what potentially can be retrieved. But yeah, otherwise, yeah, that's that's the beauty that it's no difference whether it's complex or not complex transfer time is the same. Yeah, I know. So, do you want to like continue with chat because technically again, I'm done with presenting you can like unmute people. Hi Andrea. Lindsey here. I see a question that was submitted about seven minutes ago. I don't think you've answered it yet are further optimizations possible and if so what is the next step. Okay. Okay, in the water with the paper so yeah so the paper case so the paper is the publication so I do have a link, but there's you need to pay to get the paper because there's publication for the optimizations okay so the part of this is how you optimize the actual database query right because I mean if you use this approach it's kind of the optimal interaction between application database I am pretty confident you cannot have things more optimal but how you run query on database it's a separate story because now again the beauty of this approach is that you can optimize the query based on based on the query itself like say account search you can search by like something can address you can search something on the phone you can search something called like last name first name whatever and by the way again if you look at this repo there is search function so that two functions because I did not want people to start reading this horrific generic search before they even get from the rest of this so there is one basic search which you do not need to have it I just have it so for the ease of reading but then there is like complex search and you can search there by email or by phone or by something and you can see that what I'm doing there I'm generating different queries based on what is the search criteria so there's a beauty of it because for the application it does not matter they just send you the search JSON and then you use your own optimization powers because you know what all the optimizers are cool and awesome but still humans are better than any optimizer I mean optimizers are written by humans anyway there is for any optimizer there is a situation when humans can do it better back-end code okay so the question how about two techniques used by GraphScore to do back-end code meaning what the application code what was the question not sure can whoever asked it to clarify can somebody clarify what was about the back-end code means in this question okay to generate to generate database functions yes so that's what I said that we want to have the mechanism to generate some database functions again I work for the small company so everything what I'm doing here is out of my work hours so that is something which I would gladly gladly have somebody partnering with me and generating some basic functions as I said generating types it can be automated generating basic results basic updates definitely can be automated there are some things which cannot be automated it's some non standard searches but again 80% to 90% of searches can be automated that's something what I'm working for yes again I do not know whether it can be ever packaged as an extension but I will embrace this opportunity I have a question anything about do we have to worry about concurrency and locking not it does not matter it's no difference between this approach and like embedded SQL or whatever because the thing is that we are talking about the realm of all TP systems so that small queries that a large number of short queries so we do not anticipate any lockings and that is select anyway so again the thing is that each of those functions is executed kind of the same time as single select that's why I'm doing it like you're right it is no different than the traditional way I was just wondering when you write a function and you are passing back say a JSON and asking for some deletes yes somebody beat me to it somebody already deleted those records it's not faster again it's same locking mechanism as for single deletes because if you look at the execution time so the most complex and this I mean in my github it's like simple version but we have it like actual huge master don't function where we have lots of embedded objects and everything is executed as one function it's still under half a second yeah we love it yeah I love it so now that's that's the thing that because when we can package several queries which executed on the server the extra time for like 10 queries instead of fun is way way less than shipping 10 separate queries between the application and database so that was the goal that was my goal for many years but like here Breven Holdings it was the first time when I had enough support from the application development team and from the tech leadership to actually see how it will work okay one other question do we do we also have to worry about in some cases somebody sending back a very large object like when you say you're selecting and say just for the sake of our example that we have here you're bringing back a user object and he may have maybe hundreds of addresses that he moved across and hundreds of phone numbers and at the end of the day when you bring all that back you have a huge object do we have to worry about no I mean it will time it will time out like as I said you know because we have like once a week we have one sale one customer rep who does select the state equals TX period and accidentally press okay so this time is out because not because of the data volumes I mean the data volumes I mean whatever we have whatever like our through output allows it will just unfortunately time out because it will take time to bring all this complex object for the state of Texas because we have lots of customers in the state of Texas right and that one here it will it will break on the database side no it won't break no it won't break I mean if it won't be timing out it will not I mean we have some utilities which are also built on the same thing like we load huge universe files we load like like big files which we are preparing for my smailing we have millions of records and I mean they are great for them because the way the way I look at it is you are building this whole object on the server side yes and you are whereas the other way around if you use ORM you will be basically buffering the records across the wire as they select yeah yes no I mean this does not make it long I mean we are not like exhausting the capacity of transfer there again we are not speaking about I don't know bringing Wikipedia or the virus some of these technologies can be actually used for reporting we use some of these technologies for the for our data warehouse but they are kind of you know modified to be used on the data warehouse environment now the good part about using some of this in data warehouse because often we need to have reporting kind of like in the application and what I experienced previously in my previous places of work like something is calculated in the application using some Ruby on Rails methods like 70 database holes they produce one number and then the reporting team wants to have this number in their reports like I cannot give it to you I don't have it I have this Ruby method so it is now everything is in database so we can you know couple of years ago I think I presented my little trick how to call functions remotely which you cannot do in Postgres but you can if you try so yeah we are using some of these little metrics okay so you know what I checked actually with my co-oper we need to check whether yeah so whether I can email the text I will check about this because I am not sure whether we can email the text but you know what again for the practical purposes I think the report like works better because as I said that was academic paper and first we had to reformat it and rewrite it because for these countries they had different formatting requirements so we had to reduce it like to like one like a little bit over the half and then at the end because nobody understood what it was they said okay this will go with a short paper reduce it like to yet another so we just love having the publication but honestly I don't think it's that much in it I mean for somebody who actually wants to try but I will check whether we can I think we can privately send it by email I think yeah you know I I don't know I created this report like two weeks ago and I was like why I never thought about this because people ask for so many times and I presented again this paper and the Chicago bug because first time we went online and I just did not want to subject anybody for this suffers I decided I will do it myself and then people ask so like if I want to start what we will do and I'm like why I never did this so I'm very glad I created this report so I hope that this will be kind of you know this getting started finger in the I really will really really appreciate the any feedbacks and anything because I'm I want this technology to become more common when we came up with the term norm I started to use it all the time because I just want people to use it I want people to know what we mean because for several years it was this Jason finger like literally I was saying this so our Jason finger now at least we have a word we call it norm and I do not like to be a person who have this like you know like possess this sacred knowledge and nobody else can do it because if we are talking about technology we need to have a technology we can be easily used by other people and I really think that the one of the biggest hurdle for performance now is using programs even the best of programs you know even like the ones which are decent so I really really want people to like take a look learn this technology love this technology and use it okay transaction isolation okay which transaction isolation level you know what whatever we have because functions are atomic so that's the thing that that is something which I was very unhappy when I started with postgres like about 10 years ago because I came like from being Oracle person for a very long time and before Cybers person for a very long time and I like so I've said functions are atomic and we cannot build anything but actually here it works for us does not matter what the selection level we never had to I mean let me see no there's one place in application where we had to play with isolation level but it does not depend on the functions we have only one place where potentially can have the race condition but other than that that's another beauty function is atomic no matter what the selection level function is one operator you cannot like execute half of the function it will never be committed no matter of selection level okay I understand the comment post okay you know what no okay this is probably maybe I do not know something about postgres but what I looked at because people asked me several months ago like take a look why you are not doing it so my understanding is that from what I read that they can map tables they can map views I think they can map like results of the function but again we are using functions exclusively for returning the complex types so that is something because for me that's the only reason why I'm doing all this we are able to return complex nasty types and that is something which I did not see there and if I did not look close enough I apologize but that's like that was my research okay anything else? like so weird to talk and everybody is silent you know that's what I do not like about assumed that everybody is typing nobody is talking we thank you very much yeah very much we enjoyed your talk very much and are you gonna be sharing the I mean I already have a link to your to your GitHub yes actually the version of this presentation is already there exactly the one which I gave in Cyprus in January this one is a little bit more expanded because like I knew I have a little bit more time here so I just added a couple of slides I can put it but like 90% of slides are already there I made it as accessible as possible thank you very much yeah very welcome we'd like to have you in Texas someday thank you alright you know what you're sometimes maybe you know when we will be able to travel again I hope so I hope it will happen yeah absolutely yes so yes please reach out like I would really love somebody trying using GitHub would really love to hear how your experience goes you know with my other report EPG by Temporal people started to put like issues there I'm so happy because now that's another thing which is developing against all odds for people are cloning and there I have lots of people actually posting questions and asking me to explain how things work and so I but that one is like several years old now so I hope that you know I will have some feedback in this report and yes please let me know how I can improve it and how I can like make life better we are hoping to have some like applications there so a couple of people told me they will write easy like small Java app or small Ruby app so that we can show how these functions can be used by the application developers again somebody will be interested in doing this that would be awesome thank you thanks so much wonderful thank you so much for joining us I hope to see you soon alright great presentation thank you thank you