I have a “temporal table” — a table where there are multiple “versions” of entities, with each version having a distinct timestamp:
CREATE TABLE contract_balance_updates (
block_id bigint NOT NULL,
block_signed_at timestamp(0) without time zone NOT NULL,
contract_address bytea NOT NULL,
holder_address bytea NOT NULL,
start_block_height bigint NOT NULL,
balance numeric NOT NULL
) PARTITION BY RANGE (block_signed_at);
-- one for each partition (applied by pg_partman from a template)
CREATE UNIQUE INDEX contract_balance_updates_pkey
ON contract_balance_updates(
holder_address bytea_ops,
contract_address bytea_ops,
start_block_height int8_ops DESC
);