Remove tx_in table as redundant with consumed_by_tx_in_id

[rdlrt]

OS
Your OS:

Versions
The db-sync version (eg cardano-db-sync --version): 13.1.1.3
PostgreSQL version: 15

Build/Install Method
The method you use to build or install cardano-db-sync: Github releases

Run method
The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): systmd

Additional context

Space taken by instance & sync time improvements

Problem Report

With the recent addition of consumed_by_tx_in_id, unless I missed - there is no longer any advantage of leaving behind tx_in table. While this works well, instead of reducing, we end up increasing size of DB since the corresponding tx_in table isnt retired

This could maybe introduced with a new major release getting rid of a lot of duplicated info and improve schema (eg: #1333 , #1138 , #1193, alongwith breaking changes from additions from Conway, etc)

[kderme]

This is indeed possible, though it will cause some breaking changes.

consumed_by_tx_in_id is basically a nullable reference to tx_in. It's currently used on rollbacks: outputs whose inputs are rollbacked should update their consumed_by_tx_in_id value to null. However this can be achieved by using the tx_id instead, so the column could be renamed consumed_by_tx_id.

Another usage of the tx_in table that I can think of is that it associates an outputs that is locked by a script with its redeemer, through tx_in.redeemer_id. This could be achieved alternatively by extending the Redeemer table.

[rdlrt]

Ye - I think query benefits are quite large on new way to be able to work towards breaking change.
Currently everything seems to be getting appended to 13.x avoiding a breaking release, I feel there are really good changes made which also help lighten the DB further (linked issues/PRs in the end of the initial msg)

[kderme]

Done with #1539