IntersectMBO / IntersectMBO/cardano-db-sync
Remove bech32 data columns (or atleast make them optional)
- Dominant language
- Haskell
- Stars
- 318
- Forks
- 168
- PR merge metrics
- No merged PRs in 30d
Description
**Problem Report**
Currently dbsync stores bech32 encoded strings for various columns across tables.
1. These text fields are indexed as `hash` type, which are sub-optimal , as they cannot be used in parallel. Thus, restoring from a snapshot - ends up waiting for 10-12 hours for index against `tx_out.address` - as it only uses a single CPU/worker for this process (atleast as of Postgres 16, as listed [here](https://www.postgresql.org/docs/16/sql-createindex.html) ).
2. Secondly, the encoding standards are often based on CIP, and a change in CIP (eg: CIP129) would mean dbsync can only use/implement it when changing previous data, which could be quite an unnecessary data operation. Larger the history grows, bigger the resistance would be to add any improvements to naming if it has a large data migration dependency.
The request is for dbsync not to store bech32 data at all, or if they need to be - atleast make them optional. Instead, consumers can use the decoding/encoding on where filter of queries, by making use of extensions (eg: [pg_bech32](https://github.com/cardano-community/pg_bech32) , [pg_base58](https://github.com/Fell-x27/pg_base58), etc.). While hopefully the address field itself is moving to it's own table soon as part of #1820 , it's still a bit of an overkill to store duplicate copy of similar data.
*** Positive Outcomes ***
- Reduces time taken for restore snapshot (especially the additional 8-12 hours it waits for single index) , due to index parallelisation, allowing better utilisation of resources.
- Reduction in overall storage size of dbsync database
- Remove data that is essentially derivable from another column.
- Improve maintenance time to have up to date stats on tables, which in turn helps query planner create better guess (especially helpful when maintaining a materialized view or seperate cache table).
- Consistency across users (users having dependency on bech32-encoded fields, others on raw format).
- Lookup on bytea being faster than text
*** Cons ***
- User having to install postgresql extension and update their SQL queries once to replace current bech32 columns with something like b32_encode(,encode(,'hex') ) and filters to be something like b32_decode() - (additional bits for network indicator on addresses or script indicator on drep can be added to extension itself to make it cardano-specific).
Contributor guide
Assessment
This issue has not been assessed yet.