January 2020

Postgres 12.1

We’ve upgraded the database from postgres 11 to postgres 12.1. This should result in performance improvements across the board.

ERC20 Transfer and Approval tables

You can now query the erc20."ERC20_evt_Transfer" and erc20."ERC20_evt_Approval" tables to get decoded token transfers and approvals. These tables should include all events that could be decoded using the ABI of the ERC20 standard. This means that all transfers from all tokens can be queried through this table. The table is large (240M rows at time of writing), so please let us know your experience of the query performance.
erc20."ERC20_evt_Transfer" schema:
Column
Type
"from"
bytea
"to"
bytea
value
numeric
contract_address
bytea
evt_tx_hash
bytea
evt_index
bigint
erc20."ERC20_evt_Approval" schema:
Column
Type
owner
bytea
spender
bytea
value
numeric
contract_address
bytea
evt_tx_hash
bytea
evt_index
bigint
Here contract_address refers to the contract emmitting the event, i.e. the token address, while evt_tx_hash and evt_index conveniently lets you join with ethereum.logs using a query like
1
SELECT *
2
FROM erc20."ERC20_evt_Approval" apps
3
INNER JOIN ethereum.logs
4
ON apps.evt_tx_hash = logs.tx_hash AND apps.evt_index = logs.index
5
LIMIT 100;
Copied!
Also note that you can join these tables with erc20.tokens to get human readable token symbols and the number of decimals like
1
SELECT value/10^decimals, tr.*
2
FROM erc20."ERC20_evt_Transfer" tr
3
LEFT JOIN erc20.tokens t
4
ON t.contract_address = tr.contract_address
5
WHERE symbol = 'MKR'
6
LIMIT 10
Copied!
Note though that
Examples
Top token holders
1
WITH transfers AS (
2
SELECT
3
evt_tx_hash AS tx_hash,
4
tr."from" AS address,
5
-tr.value AS amount
6
FROM erc20."ERC20_evt_Transfer" tr
7
WHERE contract_address = '\x6810e776880c02933d47db1b9fc05908e5386b96' --GNO
8
UNION ALL
9
SELECT
10
evt_tx_hash AS tx_hash,
11
tr."to" AS address,
12
tr.value AS amount
13
FROM erc20."ERC20_evt_Transfer" tr
14
WHERE contract_address = '\x6810e776880c02933d47db1b9fc05908e5386b96' --GNO
15
)
16
SELECT address, sum(amount/1e18) as balance
17
FROM transfers
18
GROUP BY 1
19
ORDER BY 2 desc
20
LIMIT 10
Copied!
Token Balances
1
WITH transfers AS (
2
SELECT
3
evt_tx_hash AS tx_hash,
4
contract_address AS token_address,
5
-tr.value AS amount
6
FROM erc20."ERC20_evt_Transfer" tr
7
WHERE tr."from" = '\xdeadbeef' -- insert real address here
8
UNION ALL
9
SELECT
10
evt_tx_hash AS tx_hash,
11
contract_address AS token_address,
12
tr.value AS amount
13
FROM erc20."ERC20_evt_Transfer" tr
14
WHERE tr."to" = '\xdeadbeef' -- insert real address here
15
)
16
SELECT token_address, sum(amount/1e18) as balance
17
FROM transfers
18
GROUP BY 1
19
ORDER BY 2 desc;
Copied!

Fallback decoding

The above tables are generated with a new feature we call “fallback decoding”. Essentially it breaks down to being able to decode logs regardless of the events contract address or contract bytecode. If you know other cases where this decoding can be useful feel free to let us know at [email protected]

Misc

    Data for Gnosis sight, safe and dfusion can now be found in gnosis_sight, gnosis_safe and gnosis_dfusion schemas respectively.
    Synthetix token-contracts now have the correct name Synthetix and are found in the synthetix schema
    prices.usd_dai have been renamed to prices.usd_sai, and the symbol changed to SAI - we still don’t have DAI prices unfortunately, as our price provider has not listed it.
    prices.usd_rep now has the correct token address for REP for entries after the migration. Previsouly all entries had the old address.
Last modified 2mo ago