E2PG: Ethereum to Postgres Indexer

[ryandotsmith]

E2PG is now called Shovel See the Shovel website for more detail.

Announcing v0.0.1 of E2PG!

An open source indexer that reads data from an Ethereum node and transforms it into Postgres.

                         Blocks
                         Transactions
             ┌───────────Receipts─────────────┐
             │           Logs                 │
             ▼                                │
┌────────────────────────┐       ┌────────────────────────┐       ┌────────────────────────┐
│                        │       │                        │       │                        │
│                        │       │                        │       │                        │
│                        │       │                        │       │                        │
│                        │       │                        │       │                        │
│        Eth Node        │       │          E2PG          │       │        Postgres        │
│                        │       │                        │       │                        │
│                        │       │                        │       │                        │
│                        │       │                        │       │                        │
│                        │       │                        │       │                        │
└────────────────────────┘       └────────────────────────┘       └────────────────────────┘
                                              │                                ▲
                                              │        From: 0xa...            │
                                              └────────To:   0xb...────────────┘
                                                       NFT:  0xc.../123

E2PG can index every NFT transfer in under 5 minutes.

E2PG is a Go program that runs alongside an Ethereum node (ie Geth, Reth, etc). It transforms blocks/transactions/logs into rows in a Postgres table. The transformation happens through Integrations. Each Integration takes an array blocks and is given a postgres connection for arbitrary insertions. Here is a pseudo-code example of an integration indexing a batch of blocks:

func Index(pg e2pg.PG, blocks []e2pg.Block) {
        // iterate over all the data in the blocks array
	d := DecodeTransfer(blocks[x].Receipts.At(y).Logs.At(z))
	q := "insert into transfers(from, to, token_id) values ($1, $2, $3)"
	pg.Exec(q, d.From, d.To, d.TokenID)
}

See the ERC721 integration for a complete example.

You can backfill a single integration:

e2pg -i erc721 -begin 1 -end 17500000

Or you can continuously run all integrations on new blocks as they are created (no flags):

e2pg

Once E2PG has indexed a block, the indexed data can be queried in Postgres. E2PG can write to a dedicated Postgres database or it can be connected to your web app’s database so that you can join the indexed data with your application data.

select token_id from transfers where from = '\x...';

---

The main idea behind E2PG is to copy the decoded event logs into Postgres tables. Once the decoded logs are in tables, you can query the tables directly from your app or build higher level tables based on the integration tables. This is ideal because once the decoded logs are in Postgres, you can easily and quickly operate on the data –as opposed to having the canonical decoded logs behind an API or even worse: on a hosted 3rd party. By co-locating your application data and the Ethereum data, you have given your team the best chance of building a fast and reliable Ethereum app.

Quickstart

The Quickstart doesn’t require a full node. Instead, it uses RLPS —a product created by Index Supply that provides access to raw blockchain data. It isn’t as fast as a local full node, but it’s faster than using a hosted JSON RPC provider. Learn more about RLPS here.

# linux/amd64, darwin/arm64, darwin/amd64, windows/amd64
curl -LO https://indexsupply.net/bin/main/darwin/arm64/e2pg
chmod +x e2pg
# install postgres if needed. https://postgresapp.com/
createdb e2pg
export PG_URL=postgres:///e2pg 
export RLPS_URL=https://c.rlps.indexsupply.net
./e2pg -reset -e $RLPS_URL -pg $PG_URL -begin 16000000 -end 16001000
psql e2pg

Learn More

See the README for more details on construction and operation.

Visit: Index Supply Vision for more details on Index Supply, Co.

[rflihxyz]

emailed you :)