Subgraph Indexing & Querying Optimization: 4 Best Practices

[00:00:00]hey everyone my name is Marcus I work developer relations for Edge and node building the graph and I'm super excited to go into today's Workshop that's going to be going over four best practices with subgraph and really subgraph development that can speed up indexing and querying so uh before we get into going into those four best practices I want to go through a quick announcement uh right now there is a sunrise uh going on in the graph ecosystem where there is a cutoff date of June 12th where the host of service is eventually going to be going away and that means if you have a subgraph on the hosted service upgrade to the graph Network and with that upgrade process there is 4 million GRT available to reward your upgrade process as well as if you do any of these certain missions so what I'll do is go ahead and share my screen briefly here and just go through the different details about the sunrise upgrade program we have June 12th as a cut off date for this if you enroll in the sunrise upgrade program and upgrade to the graph Network by June 12th uh as well as participate in these missions that you will see in here especially the Builder track uh there's a good amount of GRT in here if you simply compete uh complete these uh uh tracks here the web 3 builder track about upgrading subgraphs coding and querying building a repo and then also just simply querying subgraphs that are on the graph Network can get you GRT and get you live on the graph Network which is very exciting so uh with that all being said that is it for the announcements today uh let's go into the workshop today this is subgraph best practices how to optimize indexing and querying speed so uh what I want to do also is uh highlight the graph Builder newsletter if anyone here is interested in building on the graph I write this newsletter every single month to summarize all of the news that pertains specifically to builders such as new updates uh new features and uh this is the QR code for that so what I will do is say that is everything for the announcements let's get into it so success is the sum of small efforts repeated day in and day out Robert CER who is an author um little simple quote here but I do like it because essentially when we are optimizing anything we're trying to find a little bit more success small efforts small efforts all the time and that's really what optimization is about the sum of small efforts uh adds up to a lot and we're going to go through four of those efforts that are relatively simple that can provide a lot of uh return so really quick review on uh sub graph and how it is shaped there's going to be a database uh and in that postgress SQL database we've got graph node and that's talking to data consumers all through graphql as the query interface so this database right here if it ever expands greatly essentially graph node has to do more work and that is having to go through a larger database we're going to see uh that theme come up regularly in this optimization talk if we can keep this database of manageable size in the subgraph that makes a big difference now also not only subgraph a database size but subgraph processing uh we already talked about this database making sure that database is small as uh uh reasonable as optimized as possible and then also we're looking at this entire workflow essentially how quick can this occur how quick can this entire workflow of a subgraph go um and how can we optimize that and so those are two themes that we're going to go back to regularly uh in this presentation so the first best practice that uh was brought to the attention of uh myself and many other developers within the edge and node team uh this is a relatively new feature uh pruning with indexer hints now in terms of what this means let's go into it because this is a relatively new feature and for those of you have older subgraphs might not know about this one let's see what happens when we have best practice one uh pruning with index or hints as essentially we have happy dap users because we get faster query response times and happy indexers because with pring our subgraphs database uh it's a smaller database and it's easier to go through that data that's in the database easier to retrieve the data it just is faster it's just much much faster with the query response time with that uh smaller subgraph database now what does this look like how do we actually integrate pruning with our subgraphs sometimes there's an older subgraph out there uh that's okay before spec for version 1.0.0 uh our spec version has to be at version 1.0.0 for this to work cuz it's a more recent update uh but once you've upgraded your spec version dropping in index or hints into your subgraph Doo is the way to go ahead and prune automatically and then there's a few different settings for prune and we'll go over those uh in a little bit here um but really what I want to say is this is actually quite a big deal because I've seen uh members of graph node at times and team members saying there's some subgraphs with uh way way too many entities that are never being quered and it's just weighing down the subgraph to the place where it's just having issues and with a simple two lines of code here uh the person who's developing that subgraph can really greatly change their query performance just by uh having it more optimally uh built so here's another option for prune uh for pruning if we want to to have a certain number of blocks to retain just to have that data and be accurate with that let's say we want to have six months of blocks uh always stored but anything past six months we don't necessarily care about we can do the math and see how many blocks six months is and then from there go ahead and put that number in there and always have that or a week or two weeks or a year whatever it is um it's just making that number clear in terms of what we are Desiring in our subgraph is really going to improve our experience with responsiveness and the last option here is prune never so this is used when we want all data and the subgraphs uh database to be quite uh full with every bit of data that uh has been uh emitted uh through the uh either through an event or E call and from there we have that in our subgraph and it's never going to be prudent ever so those are the three options it's either Auto and that or put a specific number uh or never and real quick review of Auto um in terms of the specifics on that that number is dictated by the indexer themselves uh so that's going to be have to be something that um I'm not enti and this is actually a good point graph the graph node team I just confirmed with them today they are going to be coming on for another session of this uh uh this this presentation about the best practices 1 through 4 and talk more in detail about kind of how they built it why they built it my job is just to explain that this is available to everyone out there so don't worry if you want more detail about prune Auto that's coming as well so uh let's go into considerations before pruning of course when we are uh taking our database and making a little bit smaller we have to think okay well what could this be affecting are we using time travel queries uh we can't query what's been pruned if we've deleted that from our database it's it's gone so ideally it would be best to use prune number rock blocks to retain so we have the exact number of weeks or months that we would like or prune just never and just always have that to be able to use time drop queries are we grafting if we want to graft to a block height that has been pruned that's not possible so it's best to use prune a number of blocks to retain once again or prune never uh those are the two good considerations to make before pruning so with that new feature go ahead and try using indexer hits It's actually uh deployed automatic Al uh with every new CLI deployment so upgrade your CLI and get playing with that one so let's go into subgraph uh best practice number two this is use derived from to manage arrays so using derived from is a is a pretty solid technique to manage relationships and this is what results if you use drive from uh once again we get happy dap users uh there's going to be faster career response times and also there's faster indexing speed uh there's going to be faster processing of that data too so we're smaller subgraph database size and then also processing can be faster if we're using deriv from uh we also once again have happy indexers smaller subgraph database and faster processing easier to store and retrieve data so what does this look like well this is a potentially Troublesome array that is not using derived from we have a post here that potentially could have an infinite amount of comments and if we use this type of relationship here this one to many we have a potential abely infinite double storage of comments because it's going to be stored this uh comment will be stored in the actual entity that is a comment and then also it'll be stored in the field comments in an array so we double store and that really becomes an issue and just can cause a lot of bloat if we're double storing data in our subgraphs database so this right here is a proper use of derived from where as we could see here we once again store uh all of the comments here but we're deriving these uh this these comments from this field post which refers back to here and we have a basic Loop going on right now to have this link up but essentially this only stores this data once we don't double store it if we use this technique and this literally can take an entity size and chop it in half which is a big deal whenever we are dealing with crazy amounts of data that potentially have millions of entities um that's a really large deal so uh once again again uh faster processing too just because there's just less data to go through um there are even more advantages with using derived from whenever we are linking entities uh we can query the post and see all of its comments that's just good one to many but that's also reverse lookups also are brought up query to comment to see which a post it comes from that's also just one to many but this is the real unique one the derived field loaders directly access and manipulate data from virtual relationships in the mapping so so essentially we have a relationship here and we don't necessarily see the relationship but it's a virtual relationship if we want to actually access this virtual relationship we can do so with derived field loaders and go ahead and play with that data in the mapping so uh there is information of that in a repo uh out there and I will put that in the comments uh when I am able to get that in here but yes uh we will be putting that in the docs very soon too we do have derived field loaders which is pretty cool so um those are so far two uh best practices uh of subgraph development another uh oh before we go into that this is a fantastic blog from uh devel Kevin Jones that goes into more detail about managing arrays because arrays can be potentially problematic if uh they get out of hand go through this one if you want to go into more of a deep dive into how to manage arrays and subgraphs really understand what they can do to to your subgraph and just optimize it and a few other techniques in this blog great blog go ahead and find it on the gra.com blog um so next this is best practice number three and this is a big one um use bytes his IDs and immutable entities um these are basically a two for one best practice and these have uh shown some significant performance increase in terms of percentages uh that we'll talk over too uh results of using as IDs and immutable entities we have once again happy dap users and happy indexers uh faster indexing speed nearly a 50% Improvement in indexing speed with bises IDs and mutable entities that is crazy jump in indexing speed um and then also query responsiveness uh 20 just over 25% Improvement uh in just over a quarter percent quarter quarter percent 25% Improvement in query responsiveness so um that's a big big big deal to have just with these little changes a huge jump in indexing speed and query responsiveness so uh go ahead and make these changes if you do not have byes IDs and immutable entities we'll go into what those are in just a second um and then finally of course we have happy indexers smaller subgraph database and faster processing with both of these easier to store and retrieve data so um this is a problematic entity we have uh an entity that is not an IM mutable entity as we do not see immutable on The Entity and the ID is of the type ID um it could also be of type string and also I believe uh int8 I believe or I think those are the there's a few different ones that could be possible the best one is btes and the reason for this is just simply we just have just faster processing because bytes is a lower level language we don't have to actually go ahead and have more compute on a string on top of that so this is much better uh this is an entity that is immutable and this is a ID that is bite so very quick changes here um and there's some considerations when making an entity immutable and making an ID bites the first consideration is well what is a mutable entity basically it's an entity that we do not plan to change in further further versioning of our subgraph um and a good entity to make IM mutable is simply an entity that is logging event data without any Transformations that means we're just simply taking the event from chain and bringing it right into our subgraphs database and we're not going to be taking uh time and effort to change those entities uh kind of shapes and that's just just saying hey we won't change this in the future this is the way it's going to be and that's it so that's a good candidate for an immutable entity um there's other reasons to use immutable entities but essentially the second we make it immutable on further versioning we won't be able to change it so that's okay the next one here is ID of bites and there's uh little considerations that we have to make with that if we have IDs of other kinds uh such as string uh let's go into uh those uh considerations the first one that we need to do is if we are going to pass the ID into an entity um it's best if we're going to be making a random uh entity or somewhat random entities ID uh of the type of bytes we use this conat i32 to concatenate two different uh pieces of data and if we just use that it results in a bytes uh object that we can go ahead and use is our ID now having this little feature right here that is part of of graph TS is uh quite helpful um being able to concatenate is also helpful you don't have to use it you can just go ahead and use the event transaction hash but concatenation for an ID is even better if possible because it creates such a unique ID uh that it is uh very unique to that entity is to that entity itself so um that is a consideration to transition over to byes as IDs if you're you know using a string here another different styles of concatenation this is the way to result in a bites object um another consideration is sorting so if we want to go ahead and query uh the transfers entity and we want ID from to and value and we sort an order by ID it's not necessarily going to be in order as we see here if we sort by ID as it is in a bytes it's this format here and we're getting kind of an odd return on the data it's not going to be sequential if we want to sort by ID if we would like to do that um there's a something that we can do as a Technique we could simply just add in a big int uh and somewhere in our entity this one I just added a big INT in the database as a token ID that is a big int and just simply made it a counter and so anytime a new entity was created it was one two or three and very easy to do that so um if you would like to have some organization with your byes as IDs go ahead and have a counter in there in some way to have sequential order that is uh quite helpful uh in your sorting so some considerations as bis IDs there's a few other reasons to not use byes IDs the mutable entities um number one human readable IDs are needed let's say we want IDs for whatever reason to be human readable um then string is the way to go ID is the way to go um integrating you subgraphs data with another models data that does not use bytes as ID so if we have data uh datas from different locations all trying to come together that and then finally performance improvements are not desired if you're okay with just hacking around and poking around you don't need to um but if you are eventually planning to publish the graph network uh and want a production ready subgraph it's best if possible to use byes IDs and immutable entities wherever possible so um so um also if there's any questions about this at any time feel free to launch them into chat I will go uh there'll be a Q&A at the end um for anybody who has a question about this so let's go in uh to this blog here this was written a little while ago by David Lort uh he is a graph node developer two simple subgraph performance improvements he goes into using bytes as IDs and immutable entities at length and does tests and has uh data on uh comparing using B IDs and immutable entities versus not using bites IDs and immutable entities so that's something to take a look at if you want to dive in a little bit further into this concept of why it's a best practice so finally best practice number four ideally we avoid eth calls now uh really quickly we went over this in the beginning an high level overview of data processing um this without this eth node right here um let's just delete this supposed just say this isn't here all right that's gone um this is how subgraph processes data all right but then if we add in a eth call we have to make that call to an eth node and uh to an ethereum node and that potentially can take you know maybe 100 milliseconds all the way up to a few seconds and that can really slow down subgraphs indexing where uh if we're going to be making e calls and there's a lot of them even just a few of them it can really add in processing time and and also just waiting just the runtime overhead just just adds up so we don't like that so what do we do about this well ideally we go ahead um well if we avoid eth calls that's great there's happy dap users we have faster indexing speed we have to wait for that ethereum node to return that call uh and happy index or subgraph indexing speed is optimized we're really more likely to stay at chain head if we can avoid using those eth calls now uh how do we identify if our subgraph or if a subgraph out there is using eth call go ahead and look for bind uh that string right there bind anywhere in the code indicates that there is a eth call that is following that bind and we can see that right here where this instance right here was created by binding to the event address and then from there we're able to go ahead and call that eth call right there so just be aware if you're looking through subgraphs that are not your own look for that bind and try to get rid of those um and and this kind of leads into you know do we have control of the smart contract are we indexing uh a smart contract that we can design uh could we emit data on that smart contract and have it properly give us the data that we need that would be ideal um so if we do have control of that smart contract yes uh have it be a vent data if possible now if uh we do not have control of that smart contract that's okay because a new feature that uh I'm very excited to announce here I think the first time publicly is that uh we will soon in the next literal day to three days to five days be able to declare eth calls in the Manifest and uh this is actually a uh sentence that I got from one of the graph node developers if unable to avoid eth calls declare them in the Manifest so graph node can perform them in parallel before handlers run which minimizes total time spent in calls declared e calls uh declared calls are Ed in parallel and cached in memory allowing handlers to retrieve results from this cache rather than making actual RPC calls so check that out um update your CLI to have the most recent version so spec version 1.2.0 and give this a try uh right now I believe this is available in hosted service which funny enough is sunsetting in a few days now um so get it while it's hot there but this will soon be rolling out to Studio any day now and be able to be played with so pretty cool news that we could actually declare these eth calls in our subgraph doyo and from there go ahead and optimize those eth calls very cool stuff so if we want to read more about avoiding eth calls and eth call management to just minimize their use as much as possible check this blog out this is by Simone Emanuel Schmid uh who wrote this blog and it's a solid uh Deep dive into eth calls and different strategies on managing them so um there will be more best practices stay tuned for those this was 1 through 4 uh I'm working with uh between five and 10 people at any given time internally just debating what these best practices might be talking with many people on the graph node team many different developers in edge of node Squad that we're trying to find the best way to get this information out there so stay tuned for more but um for now just use these if possible uh with your subgraphs Tinker around with them see what performance increases you can get with your subgraph and um make subgraphs on uh just really just optimized for you as much as possible so uh that is uh quite exciting once again this is the graph Builder newsletter if you haven't signed up just yet I'll put this in chat uh so uh you can sign up to this I write this every single month for Builders out there to really stay a breast of what's going on in the graph ecosystem and thank you for your time out there uh today uh this uh was I'm really happy to be giving a high level overview of this there will be a second segment like I said with the graph node team I'm trying to get that scheduled with them and uh they will be here to go over these best practices in a little bit more technical detail about uh what specific performance increases can be expected uh with these changes so with that all being said and I'm wondering are there any questions out there about subgraph uh best practices any any questions whatever fair enough all right well thank you everybody for for coming to the graph Builder office hours on May 23rd 2024 uh stay tuned for next week's graph Builders office hours uh where we'll be having uh members from the Semitic team talking about some AI uh cool stuff that's coming down the pipeline for them so uh thank you everyone for joining and uh good luck with those best practices bye