Indexing Ethereum: When and How to Build an Indexer by Ryan Smith | Devcon SEA

[Music] how's it going um cool uh okay cool there we go um yeah so hello I wanted to first um talk about some stuff that I saw in Hacker News this morning uh this hard drive here it was pretty cool um it's uh I think it I don't think it has the number on here but it was like 60 terabytes of data and the the part that I saw there was kind of interesting was 12 gigabytes uh per second of uh sequential reads um and that's very exciting and I'm going to come back um to why that may be interesting a little later in the talk here um another thing uh that when I saw that I was reminded does anyone remember um Fusion IO does Fusion iio sound familiar okay like two people maybe three people okay do you know who the guy is up in the corner like raise your hand do you know who that is yeah Steve wnac the Apple guy yeah in 2010 he started a company called or he was co-founder or something he started a company called Fusion IO and they were building um really cool storage technology uh and then that reminded me of uh this thing that I read recently um Dropbox uh I think in 20 uh it might be on there yeah 2022 Dropbox uh had this article where they talked about how they were migrating away from ssds and onto hard drives which I thought was just like a very fascinating thing and in particular like maybe you've been sleeping on hard drives uh but um there's been some recent developments in like the spinning metal uh area of of of databases which is um shingled hard drives uh and that's that was like a a thing that was entirely new to me um and you can see there like if if you Google actually uh Dropbox SSD uh SMR hard drive you'll find this great article that talks about um all the reasons why they went away from ssds and back to hard drives um and it's just a very fascinating article um okay so I've only got 25 minutes and I was thinking like boy there's like a lot of stuff that I'd like to talk about uh when it comes to ethereum indexing and so my first thought was like well I can hit these 25 topics and and maybe do you know one topic a minute but then I was like man but that really doesn't like cover all the stuff that I would excited to talk about and so I was like well there's really like 50 things that we could like explore um that gives us like 30 seconds per topic um but then I realized like this is just a terrible approach um and so I willed it down to three which gives us about 8333 repeating of course minutes per topic uh which I think is much more reasonable um so uh the way that I'm going to break this down is uh I'm going to share kind of how I think about software dependencies in general um and then we'll kind of like also explore like I think some unique characteristics of the ethereum data space uh as it relates to dependencies um and then I'm going to uh I'm going to talk about um uh I'm going to talk about like this pattern I've built like a lot of indexers over the years I've been in the space for a long time and there's been like a couple things that I've seen like kind of just like be like I've done some things like over and over again and I've kind of distilled it down to a pattern so I'm going to share that with you today um and maybe um you know inspire you to build your own indexer and perhaps you could use some of the ideas in this pattern uh and then finally um I love databases I I really enjoy postgress I I like other databases as well but postgress is in particular uh a thing that I enjoy and so I'm going to share some fun postgress tips um and so uh let's see yeah let's get started um oh yes okay so uh I have a project called index Supply it's a company that sells indexing stuff but today like I'm going to convince you to not buy my software uh even though I do this stuff uh I actually think it's like great fun uh to build it yourself and I also think that there are particular situations in which it actually makes sense for you to build it if if you're working on on a company we're going to explore that here in just a second so um I'm I'm gonna I'm not being factious I'm giving this the college try convincing you to build your own indexer do not use uh do not use my stuff um okay so let's get into to kind of how I think about Tendencies um so to start like uh I want to talk about um ethereum in particular uh and so like there's kind of like this base question of like what is you know indexing ethereum and uh and so uh quick quick pull here who's read the yellow paper that's about like I want to say like a third of the audience okay great so in the yellow paper you basically get um and and like the way that I'm kind of like thinking about this is that when when you think about dependency you have to pick some Anchor Point right like at a certain point you're going to say like okay I'm going to go like invent a new kind of hard drive technology like no you're not going to do that you're going to use you're going to buy a hard drive and then you're probably G to like use a file system you're probably going to use an operating system and so like there are these things that we kind of like anchor around and then like on top of those anchors then we start to think about like okay should I build it should I buy it um and so I think in particular for ethereum uh like we we have like these primitive data structures that are built into execution clients and they give us things like blocks and transactions and logs um but the uh the databases that are within these execution clients uh are really optimized for processing the transactions like not necessarily optimized for arbitrary queries uh and and I think that's like a fine thing and perhaps there's like opportunities um to you know blend those two types of workloads together maybe um but we can kind of uh I think it's fine actually um like in particular gu has like these freezer files has anyone worked with a freezer file raise your hand no one has worked with a freezer file I don't see anyone has worked with a freezer file okay these are really cool in gu um like all of the transaction log data is just like packed into a binary file on this file system you can read through it very quickly it's very interesting if you ever if you use Google freezer files you'll see some interesting stuff come up um okay I'm GNA keep going here uh okay so okay so uh one of the interesting things about the ethereum landscape is that you know we we have these cor prives we have the nodes and we have a standard Json RPC API uh and what's really great about this is that um you know if you build as with that as your dependency you have this really nice commoditization effect where you know if you don't like your particular provider uh they're not you they're not doing it for you you can go to like 10 other providers and so you you really do not have hardly any vendor lock in um now there's some disadvantages like if you just work at this level because you have to actually develop software you have to manage databases so there's a little bit of a cost to it um and so you know if we kind of move up if we go to like the other end of the stack and you use something like you know like what I build or there's like a handful of other products out there that give you like a more robust API to access the ethereum data uh and maybe you don't have to use a database but now you're like entirely dependent upon that AP and if that API doesn't work for you you've probably built your application around its abstractions and so uh you want me to move over okay I got a subtle hint that I should move over you want me to keep going okay perfect is this better um wonderful uh so uh yeah so um you know if you build around those types of abstractions you can kind of hamstring yourself uh eventually if if if it doesn't end up working out for you uh and so like another way that I like to look at this is when it comes to like dependencies in general like let's say that you have like a very high value project like you could have a high value project or a low value project right something you're just messing around all the weekend or you could have something that you're trying to build a business on top of and you know within that category you have like you know low skills or high skills uh and you know if you don't know anything about uh you know about interacting with databases or you know uh keeping you know data around and and fat and and easily accessible then you might want to buy something and that's fine um but I think one of the things that I would like to you know kind of pause it today is that um indexing ethereum like it actually isn't that hard I don't think and uh and I I'll throw like a couple things kind of at you uh to kind of encourage you to like if if you're if you happen to be in this category of you're working on a high value project but maybe your skills aren't there I think that there's absolutely stuff that you can do to advance your skills so that you can build something that you're really proud of um and then on the other end of the spectrum it's like okay you've got a high value project you have high skills um and and and here um I think it's like very important to build it actually uh and if it matches your the core competency of the company or the project that you're working on and I think the the most important thing here is that like when it comes to thinking about dependencies is like you don't want to Outsource your thinking you want to kind of come up with your own opinion and like your own motivation for why you're doing a particular thing with your data and you and and when you build it you're forced to think about it and you're forced to lay something out that you think is going to match what the business needs um okay so you got a low value project and you have low skills you know I say just jam it together you know do whatever you know find the quickest path to get the thing online and that's fine um if you've got a low value project but you have high skills I think actually kind of both can work um and the way that I kind of think about this is you know if if you form an opinion of how you like you know you're working in in a particular problem land ape and you come up with your own opinion for how you want to build a solution and then like you maybe do some shopping you say like oh wow there's a couple projects that already exist that are doing the thing that I would do uh and so in this particular case you're not Outsourcing your thinking you're Outsourcing the maintenance of that software which is like a real Advantage um and so even if you're you know even if you you have the skills uh you know it really is a tossup in terms of whether you build versus by okay so part two I'm going to go over this um essential index indexing pattern uh and like I said I've used this for probably I don't know five or six years I've worked on some pretty big websites uh in the ethereum space and this is the pattern that really has worked well for me so I'm going to share it with you today and it's basically um there is this old Fred Brooks quote that says um uh you know show me your flowcharts and I'll continue to be mystified show me your tables and I won't need your flowcharts uh and so I like to start with the table first um and and and so like the the building block here is that you have a table called blocks and you keep track of the number and hash of the block and then you have all these auxiliary tables like Fu which is like your indexed data and in within your index data you have a block number you have a reference to to to a block that you've inserted and then you have you know this column bar which represents just some arbitrary data that you want to index okay so now here's like a wall of code uh but basically like this is like the core function I I won't like go into every painstaking detail here but this is like the core function that uses those two tables to effectively index data on ethereum and uh you know it it it uses some like you can kind of see here there's some RPC code going on there's some database code going on uh in particular this thing down here at the bottom this copy logs uh that's like a really cool postgress technique that allows you to really quickly load data into a table um and so again like I'm not going to go through the whole detail here but but I wanted to show you this because it's actually like I'm I'm I'm saying like this can get you very very far and in fact you can even just kind of copy and paste this uh into a project to kind of help bootstrap you and get you started um and uh at some point like I even throw this on a gist or something you know so that so that you know someone could like copy and paste this and we'll make it work and compile and stuff because right now it doesn't but it's it's more or less there um okay so another thing with pointing out is that like the current like like kind of high level architecture of uh of uh of ethereum indexers kind of fits into this first category um and so basically like I said earlier you know we have these sources of data like an RPC API or an execution client uh but they don't really give us the type of interface that we'd want when it comes to building queries for our applications and so what we do is we read the data from The Source uh we it's like an ETL workload right we extract the data from The Source we transform it and then we stick it in another database that gives us flexible query so that we can build stuff onto our apps it's a fine pattern it's like what the graph uses it's like it's a very common pattern in the space um I think there's another pattern though down here at the bottom which I'm more interested in which is um you know how can we like just continue pushing like our query logic onto the source data and and instead of having ETL uh we just have query to the database um and I think that's a whole interesting path um to explore and I've got some ideas there and would be happy to talk about it but um I just want to kind of call that out okay so part three we're coming in here on the end uh I'm going to go into some postgress tricks um and then real another Poll for the audience here uh who uses postgress okay wow qu quite a bit of people this is by far the biggest um yeah the biggest uh positive affirmation of a poll that we've done so far it was about like I would say like 70% maybe 75% okay great so um so so you've got the essential indexing pattern you've got the architecture what are some like here's like a quick checklist of like really good features to use when it comes to indexing ethereum first one is copy uh show a hands here do who know who uses copy okay few let's say like 10 to 20% okay so if you are inserting more than let's say three rows at a time and you're using insert you are wasting compute Cycles it it it is is massively inefficient There is almost practically zero downside to using copy so if there's one thing that you walk with today and you're a postgress user and if you're ever inserting more than let's say three rows like move over to the binary copy and you will instantly see a Major Performance boost um advisory locks another really cool feature in postgress so uh it's an in-memory uh lock that you can use so it's very cheap you can have thousands of them maybe even hundreds of thousands of them and this is a really nice way if you want to build an an indexer and you want to have two of them and you want to perhaps like run them in a ha configuration but you only have want to have one doing the work at a time and advisory lock is a great feature to use um another feature that I like is table partitioning um and so we'll kind of explore this a little bit in my next tip um but basically um you can lock you can take like a table uh and you can specify a partition key on that table and then you can specify how many partitions you want to run like let's say 10 and post will automatically create 10 child tables for you and then when you query The Logical table it'll automatically find the right partition table to uh to get the data from and this is like completely opaque uh you don't have to do do do any of this stuff in your application uh and it gives you a really nice performance boost when you are bounded by disio uh and and so let me kind of show you how to kind of think about like when you may be uh disio bound um so to to do that I'll quickly introduce a Brin index show hands who who knows about a Brin index even fewer this might be the smallest number but I see you out there and I and bless you um because this is a this is a very fantastic index for some use cases um so basically like when you have a table and you put data in that table uh postgress will put like if you insert a row into a table postgress will take that row and it'll end up in a file on the file system postgress by default uses 8 KOB file sizes so that means that it's going to be spreading your rows across multiple files on your file system uh what a Brin index will do is uh it will uh basically if you have a column that has a high correlation like so for example in ethereum block number would be a high correlation to the way that your data was going to be laid out on disk if you do an insert only a pinned kind of right sou so if you're just like keeping up with the the blockchain and you're writing data and you're putting the block number in those rows then like as you store four stuff on disk it's going to correlate with the block number right and so this is a great use case for a Brin index because what you do is you have this super tiny index much smaller than a b tree and it keeps like we have like this page block up here that says okay in this page block zero I have block numbers 0 to 9 999,000 and those rows are stored in Pages zero and one and so you basically have like this top level like higher level index that like you know for a given block number it can basically redirect you to the right set of pages to read in the operating system um and so let me quickly show uh we've got about two minutes left uh let me quickly show you so here's an example so I have I have this table called logs you can see I got a block num column there um this table is about four gigs uh and then you can see I I I checked the correlation of the block number it said 83% I don't this is like another like you know ancillary database thing I know it's 99% and I cered it like earlier this morning and it was saying 99% and then all of a sudden it just stopped saying 99% % and started saying 83% and so that I became very angry um but that is what it is uh so uh I think it's wrong but that is I'd like to find that stuff out later um okay so here's another cool postgress tip if you're writing queries you got to figure out like the the explain command it's like it is literally life- saving like get to know this command and you will become a great database administrator so you can see here uh we hit a bunch of blocks uh about 500,000 buffers so multiply that by 8 kilobytes that's how much data that we read you see I created the Brin index and you can see that index size is only 320 kilobytes and I had a 4 GB table so a very small index and then the resulting query after that is I only had to read about 42,000 buffers um so like a massive decrease in the amount of IO that was going to happen there um I'm just going to go ahead and show you what a b tree index uh would do which is like your default index so you can see here it's a much bigger uh you know we went from 320 kilobytes uh B tree index is 250 megabytes much bigger index uh but you can see down here it's just you know game over B Tre index is really great um you know much faster much fewer fewer buffers uh but sometimes you don't need um sometimes you don't need to have uh that level of performance and you might want to trade off storage size also it's very quick like if I have a terabyte table and I create a b tree index over a block num it's actually going to take a long time because it's G to seek scan that whole table a Brin index will happen much much much much much faster faster so even if you're in a pinch and you're trying to like find some data in a big table and you don't want to wait to create a b tree creating a Brin might actually be a good stop Gap uh another postard a little bit of you know tidbit of knowledge is that um you know you have a a b tree index uh and and and that is going to have a ton of data in it and you might say like when you're doing a query and you have a huge table and you have a huge index um does does posters have to load the entire index into memory to be able to do the search of the berry to be able to find the right rows to return to you the IDE question is no and I thought this is kind of like a fun fact but uh in the docs it says um you know typically when you have a b tree index so if you have an index that's like you had a table that's a terabyte you got an index it's like 100 gigs 99% of that is going to be the leaves which means that when you want to go search that table uh before like you need to find like the uh the target rows you're actually going to uh be able to keep that that index in memory because you know the majority of the data that you're going to be searching on is going to be very small um okay so we time is about up so I didn't oh man I'm really bummed I really wanted to talk about CDs um and I didn't um but it's really cool because uh whenever you quer I'll just briefly mention it whenever you query a table you can always ask for a setid it's a hidden column on a row and it tells you what page and what offset into the page that particular row is in um and then there's some cool things that you can do with clustering uh to uh you know if you want to rearrange uh rows as they sit on disk for improved IO performance you can do do that with cluster although it's not an online tool you would have to recluster if you wanted to change it um yeah so uh okay Whirlwind tour there uh but that's it uh I wanted to kind of plug a foraster we have a postgress channel and I uh I encourage you if you're interested in any of this stuff check out the postgress channel um there's a lot of good discussion happening on there so thank you very much thank you Ryan um question time so the audience have some questions for you and we're going to go through it the most highest reorgs yes uh so that code that I showed earlier handles reorgs uh it was in there uh and and so um you know maybe you got a photo of it or maybe we can I can post it on on gist or whatever but but there there are very easy ways to handle reorgs it's not that big of a deal don't let it scare you um yeah we can definitely we can definitely solve that problem um so first question one p one p for us was hand blah so five votes it means that the most important question now on the stage so can you answer that one first yeah we just did that one so I think the next one would be uh how to deal with load balance rpcs yeah yeah so it's kind of a also kind of a pain in the ass basically you send a request to an API you say give me the latest block and it's like the latest block is 10 and then and then you say okay great now give me all the logs for Block 10 but it's load balance so it goes to a different node and the the awful thing the the the the sin of the the uh Json RPC API is that if you ask for logs and you give it a block number that is higher than the block that is currently processed you would think it would say hey I don't have that block I don't have these logs you know error it does not do that in fact it'll just give you no data back as if it had that block but had did not have those logs um and so this is a big gotcha and you really have to look out for this there are a couple tricks I'll just go ahead and give you one now um but it's like 50% success rate uh I don't think there's a 100% uh way to deal with this but one hack is the Json RPC API allows you to send batch requests and sometimes there are proxies that will take batch requests and split them up and give them to different nodes but assuming your API provider doesn't do that and it just takes the batch of requests and sends them to one machine you could hack this by putting your git logs request with a G block number request because if you ask for a block number byy a specific number and that block number isn't on that node it will return an error so this is like a one way to trick the node into giving you an error when you ask for logs and you don't have the block so there's a couple other Solutions I think but um yeah we we'll keep Cruis in here yeah do you think okay do you think it makes sense to have an index or interoperability I don't I I don't I don't know about number one that first question I'm going to go on so what what do you think about using click housee or times oh that's interesting yeah so I mean here's the deal if you want to do like a group ey or you want to do like a count or a sum over like a million rows or yeah over a million rows like if if you want one column over a lot of rows you might want a column near database uh and the idea there is that you are wanting to Red like and so so instead of like you know if you don't have a colum in or database and you have to like get the the the value out of a row that means that you have to go scan a whole bunch of operating system table pages to get all of the rows but really you only care about one and so you know for doing like an aggregate or something uh and so uh so that's a lot of wasted IO right and so the idea is can we do better and so for very specific use cases when you want to do aggregated stuff a columnar database might save you from a lot of that IO so you know it is a way to improve performance if what you're trying to do is is uh like more analytical queries that that you know works on aggregated data so not great for olp stuff but yeah if you have aggates fine one minute to go um let's see do you think it makes sense to have an index oh I already did that one I didn't have an answer there can you explain better why a Brin index is so much smaller than a b tree uh yeah because um a b tree is going to have like some knowledge of every Row in the table right like every Row in the table is going to have uh like a some map onto the index structure whereas a Brin is like lossy right so like like in a Brin index I have one node in the index but that one node in the index can actually cover let's say a 100,000 rows right it can say Hey you know you're quering for block number 10 and I and the Bren index says okay well I know that block number 10 is like in this block of operating system Pages uh and so then you you have to then go like basically sequential scan within that set of pages but at least you're not scanning on the entire table right so it's like the Brin index is smaller because it just simply has less information uh but but it can cut you know if you're just trying to get rid of a