Skip to content

Instantly share code, notes, and snippets.

@aaronc
Last active November 23, 2021 02:39
Show Gist options
  • Save aaronc/148036659cb033c82da7ce6d4fd012a5 to your computer and use it in GitHub Desktop.
Save aaronc/148036659cb033c82da7ce6d4fd012a5 to your computer and use it in GitHub Desktop.
tendermint/cosmos postgres index
CREATE TABLE chain
(
num smallserial primary key,
chain_id text not null unique
);
CREATE TABLE block
(
chain_num smallint not null references chain,
height BIGINT not null,
data jsonb not null,
time timestamptz not null,
primary key (chain_num, height)
);
CREATE TABLE tx
(
chain_num smallint not null,
block_height bigint not null,
tx_idx smallint not null,
hash bytea unique not null,
data jsonb not null,
primary key (chain_num, block_height, tx_idx),
foreign key (chain_num, block_height) references block
);
CREATE TABLE msg
(
chain_num smallint not null,
block_height bigint not null,
tx_idx smallint not null,
msg_idx smallint not null,
data jsonb not null,
primary key (chain_num, block_height, tx_idx, msg_idx),
foreign key (chain_num, block_height, tx_idx) references tx
);
CREATE INDEX ON msg USING GIN ((data -> '@type'));
CREATE TABLE msg_event
(
chain_num smallint not null,
block_height bigint not null,
tx_idx smallint not null,
msg_idx smallint not null,
type TEXT not null,
primary key (chain_num, block_height, tx_idx, msg_idx, type),
foreign key (chain_num, block_height, tx_idx, msg_idx) references msg
);
CREATE TABLE msg_event_attr
(
chain_num smallint not null,
block_height bigint not null,
tx_idx smallint not null,
msg_idx smallint not null,
type TEXT not null,
key text not null,
value text not null,
value_hash bytea not null, -- used in primary key instead of value because value can potentially be large
primary key (chain_num, block_height, tx_idx, msg_idx, type, key, value_hash),
foreign key (chain_num, block_height, tx_idx, msg_idx) references msg
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment