I'm currently trying to store a "blockchain" like into TimescaleDB and wanted to leverage the power of compression but without losing the performance on relational queries.
Given this simple schema with millions of blocks and billions of transactions:
blocks
-------------------------
block_number bigint
created_at timestamp
transactions
-------------------------
hash text
block_number bigint
from text
to text
created_at timestamp
Creating hypertables with this design and adding compression on the transactions table gives me terrible performance when I want to query for instance:
SELECT * FROM transactions WHERE transactions.from = lower('xxxx') AND transactions.to = lower('xxxx');
From what I understand, this is expected because data is having index on created_at and segmentby cannot help me there's because it's a "two-dimension" with "incoming transactions" and "outgoing transactions".
Because compression is really effective with TimescaleDB it should not bother to duplicate the data, am I right?
I was thinking if a database design like this could give me good performance and compression:
blocks
-------------------------
block_number bigint
created_at timestamp
transactions
-------------------------
hash text
block_number bigint
from text
to text
created_at timestamp
incoming_transactions
-------------------------
hash text
account text
created_at timestamp
outgoing_transactions
-------------------------
hash text
account text
created_at timestamp
If I compress and segmentby account the outgoing_transactions and incoming_transactions would this query be efficient:
SELECT
transactions.*
FROM
transactions
INNER JOIN outgoing_transactions ON
incoming_transactions.hash = transactions.hash AND account = lower('xxx')
INNER JOIN outgoing_transactions ON
outgoing_transactions.hash = transactions.hash AND account = lower('xxx')
Or am I completely wrong and should stick with indices on transactions.from and transactions.to and without compression?
Thank you