Joe Petrich 🟪 on Warpcast

Content pfp

0 reply

0 recast

0 reaction

Joe Petrich 🟪 pfp

Joe Petrich 🟪

An engineer on my team has been working on a migration of some indexed blockchain data to postgres and took our queries from an initial average latency of almost 2 seconds to under 100ms. The process to get there was fascinating, imo. Would anyone else be interested in a writeup of the optimization techniques he used?

11 replies

2 recasts

28 reactions

Royal pfp

I am super curious about it and how it compares to the tech that @notnotstorm is using with Cryo

1 reply

0 recast

2 reactions

Joe Petrich 🟪 pfp

Joe Petrich 🟪

What is Cryo? Have a link?

1 reply

0 recast

1 reaction

Royal pfp

https://github.com/paradigmxyz/cryo

1 reply

0 recast

2 reactions

Joe Petrich 🟪 pfp

Joe Petrich 🟪

Ok interesting, I don't think it could be more performant than what I've written since it uses the same RPC calls. What we were optimizing was our queries of that data after we've indexed it in Postgres. I don't think there's a way, if using RPC, to improve much on the initial query latency.

3 replies

0 recast

1 reaction

Eddy Lazzarin 🟠 pfp

Eddy Lazzarin 🟠

The reason why Shovel https://indexsupply.com/shovel/ is so fast is that it skips the RPC stage altogether.

2 replies

0 recast

1 reaction

Joe Petrich 🟪 pfp

Joe Petrich 🟪

Shovel does look incredible, but it also looks like it uses eth_getLogs and eth_getBlockByNumber from whatever node URL you provide. There's some discussion in their GitHub of using a local node for low latency and it's got it's own jrpc client implementation which is probably more efficient than geth's?

1 reply

0 recast

3 reactions

Joe Petrich 🟪 pfp

Joe Petrich 🟪

@indexsupply - just noticed you're here 🙂 - E2PG seems to be able to use some hybrid of RPC or a local node or something else custom you wrote? I'm in the middle of diving into your GitHub but very interested in learning more.

1 reply

0 recast

2 reactions

Index Supply pfp

Hi. Shovel (fka E2PG) can load data in 1 of 3 ways. It can use JSON RPC (common, easy, not the fastest), the Block API (fka RLPS. uncommon, easy, faster), or it can read directly from Geth's freezer files (needs full node, hard, extremely fast)

1 reply

1 recast

4 reactions

Joe Petrich 🟪 pfp

Joe Petrich 🟪

Amazing! Knowing those name changes made everything clear now. I'll be playing with it this week and hopefully contributing back as soon as possible - thanks so much for building this.

1 reply

0 recast

2 reactions

Index Supply pfp

Nice. The JSON RPC path is the most stable path at this point. Everything in the docs should work well. https://indexsupply.com/shovel/docs/ Ping me if you hit any snags or need something new and I'll get to work.

1 reply

0 recast

1 reaction

Joe Petrich 🟪 pfp

Joe Petrich 🟪

Much appreciated!

0 reply

0 recast

0 reaction